bool SaveAccountable()
{
TreeIter iter;
long NewAccrual_id = 0;
logger.Info("Запись начисления...");
try
{
// Проверка нет ли уже начисления по этому договору
string sql = "SELECT id FROM accrual WHERE contract_id = @contract AND month = @month AND year = @year";
MySqlCommand cmd = new MySqlCommand(sql, QSMain.connectionDB);
comboContract.GetActiveIter ( out iter);
cmd.Parameters.AddWithValue("@contract", comboContract.Model.GetValue (iter, 1));
cmd.Parameters.AddWithValue("@month", comboAccrualMonth.Active);
cmd.Parameters.AddWithValue("@year", comboAccuralYear.ActiveText);
MySqlDataReader rdr = cmd.ExecuteReader();
if(rdr.Read() && rdr["id"].ToString () != entryNumber.Text)
{
logger.Warn("Начисление уже существует!");
MessageDialog md = new MessageDialog( this, DialogFlags.Modal,
MessageType.Error,
ButtonsType.Ok,"ошибка");
md.UseMarkup = false;
md.Text = "Начисление на указанный месяц по этому договору уже произведено. Начисление имеет номер " + rdr["id"].ToString ();
md.Run ();
md.Destroy();
rdr.Close();
return false;
}
rdr.Close();
// записываем
if(NewAccrual)
{
sql = "INSERT INTO accrual (contract_id, month, year, user_id, no_complete, comments) " +
"VALUES (@contract_id, @month, @year, @user_id, @no_complete, @comments)";
}
else
{
sql = "UPDATE accrual SET contract_id = @contract_id, month = @month, year = @year, " +
"no_complete = @no_complete, paid = @paid, comments = @comments " +
"WHERE id = @id";
}
cmd = new MySqlCommand(sql, QSMain.connectionDB);
cmd.Parameters.AddWithValue("@id", entryNumber.Text);
comboContract.GetActiveIter ( out iter);
cmd.Parameters.AddWithValue("@contract_id", comboContract.Model.GetValue (iter, 1));
cmd.Parameters.AddWithValue("@month", comboAccrualMonth.Active);
cmd.Parameters.AddWithValue("@year", comboAccuralYear.ActiveText);
cmd.Parameters.AddWithValue("@user_id", QSMain.User.Id);
if(textviewComments.Buffer.Text != "")
cmd.Parameters.AddWithValue ("@comments", textviewComments.Buffer.Text);
else
cmd.Parameters.AddWithValue ("@comments", DBNull.Value);
cmd.Parameters.AddWithValue("@no_complete", NotComplete);
if(AccrualTotal - IncomeTotal > 0)
cmd.Parameters.AddWithValue("@paid", false);
else
cmd.Parameters.AddWithValue("@paid", true);
cmd.ExecuteNonQuery();
if(NewAccrual)
{
NewAccrual_id = cmd.LastInsertedId;
entryNumber.Text = NewAccrual_id.ToString ();
NewAccrual = false;
}
//записываем таблицу услуг
ServiceListStore.GetIterFirst(out iter);
do
{
if(!ServiceListStore.IterIsValid (iter))
break;
if((int)ServiceListStore.GetValue(iter, (int)ServiceCol.service_id) < 1)
break; // не указано название услуги
if((long)ServiceListStore.GetValue(iter, (int)ServiceCol.id) > 0)
sql = "UPDATE accrual_pays SET accrual_id = @accrual_id, service_id = @service_id, " +
"cash_id = @cash_id, count = @count, price = @price " +
"WHERE id = @id";
else
sql = "INSERT INTO accrual_pays (accrual_id, service_id, cash_id, count, price) " +
"VALUES (@accrual_id, @service_id, @cash_id, @count, @price)";
cmd = new MySqlCommand(sql, QSMain.connectionDB);
if(NewAccrual)
cmd.Parameters.AddWithValue("@accrual_id", NewAccrual_id);
else
cmd.Parameters.AddWithValue("@accrual_id", entryNumber.Text);
cmd.Parameters.AddWithValue("@service_id", ServiceListStore.GetValue(iter, (int)ServiceCol.service_id));
if((int)ServiceListStore.GetValue(iter, (int)ServiceCol.cash_id) > 0)
cmd.Parameters.AddWithValue("@cash_id", ServiceListStore.GetValue(iter, (int)ServiceCol.cash_id));
else
cmd.Parameters.AddWithValue("@cash_id", DBNull.Value);
cmd.Parameters.AddWithValue("@count", ServiceListStore.GetValue(iter, (int)ServiceCol.count));
cmd.Parameters.AddWithValue("@price", ServiceListStore.GetValue(iter, (int)ServiceCol.price));
cmd.Parameters.AddWithValue("@id", ServiceListStore.GetValue(iter, (int)ServiceCol.id));
cmd.ExecuteNonQuery();
List<PendingMeterReading> pendingReadings;
if(allPendingMeterReadings.TryGetValue(iter,out pendingReadings)){
long accrualPayId = Convert.ToInt32 (ServiceListStore.GetValue (iter, (int)ServiceCol.id));
if(accrualPayId==0) accrualPayId = cmd.LastInsertedId;
foreach(PendingMeterReading unsavedReading in pendingReadings){
unsavedReading.accrualPayId=accrualPayId;
unsavedReading.Save();
}
}
if((long)ServiceListStore.GetValue(iter, (int)ServiceCol.id) <= 0)
ServiceListStore.SetValue(iter, (int)ServiceCol.id, (object) cmd.LastInsertedId);
}
while(ServiceListStore.IterNext(ref iter));
allPendingMeterReadings.Clear();
//Удаляем удаленные строки из базы данных
sql = "DELETE FROM accrual_pays WHERE id = @id";
foreach( long id in DeletedRowId)
{
cmd = new MySqlCommand(sql, QSMain.connectionDB);
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
}
DeletedRowId.Clear ();
logger.Info("Ok");
return true;
}
catch (Exception ex)
{
QSMain.ErrorMessageWithLog(this, "Ошибка записи начисления!", logger, ex);
return false;
}
}