public static decimal GetAccrualPaidBalance(int id)
{
string sql = "SELECT SUM(money) as balance FROM (" +
"SELECT SUM(count * price) as money FROM accrual_pays WHERE accrual_id = @id " +
"UNION ALL SELECT -(sum) as money FROM credit_slips WHERE accrual_id = @id ) as sumtable";
decimal balance;
try
{
MySqlCommand cmd = new MySqlCommand(sql, QSMain.connectionDB);
cmd.Parameters.AddWithValue("@id", id);
balance = (decimal) cmd.ExecuteScalar ();
sql = "UPDATE accrual SET paid = @paid WHERE id = @id";
cmd = new MySqlCommand(sql, QSMain.connectionDB);
cmd.Parameters.AddWithValue("@id", id);
if(balance <= 0)
cmd.Parameters.AddWithValue("@paid", true);
else
cmd.Parameters.AddWithValue("@paid", false);
cmd.ExecuteNonQuery ();
return balance;
}
catch (Exception ex)
{
logger.Warn(ex, "Ошибка вычисления баланса!");
}
return 0m;
}