public void LoadDataFromDB(int id)
{
if (usedTable.Fields.Count < 1)
{
logger.Info("Нет полей для загрузки.");
return;
}
logger.Info("Загружаем данные настраиваемых полей для id={0}", id);
this.id = id;
DBWorks.SQLHelper sql = new DBWorks.SQLHelper("SELECT ");
foreach (CFFieldInfo field in usedTable.Fields)
{
sql.AddAsList(String.Format("{0}.{1}", usedTable.DBName, field.ColumnName));
}
;
sql.Add(" FROM {0} WHERE {0}.id = @id ", usedTable.DBName);
logger.Debug(sql.Text);
try
{
MySqlCommand cmd = new MySqlCommand(sql.Text, (MySqlConnection)QSMain.ConnectionDB);
cmd.Parameters.AddWithValue("@id", id);
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();
foreach (CFFieldInfo field in usedTable.Fields)
{
switch (field.FieldType)
{
case FieldTypes.TString:
Entry stringWid = (Entry)FieldWidgets[field.ID];
stringWid.Text = DBWorks.GetString(rdr, field.ColumnName, "");
break;
case FieldTypes.TCurrency:
SpinButton moneyWid = (SpinButton)FieldWidgets[field.ID];
moneyWid.Value = DBWorks.GetDouble(rdr, field.ColumnName, 0);
break;
}
}
}
logger.Info("Ок");
}catch (Exception ex)
{
string mes = "Ошибка чтения данных для настраиваемых полей!";
logger.Error(ex, mes);
throw new ApplicationException(mes, ex);
}
}
void PreparedReport()
{
isDupSet = printTwo.Active;
if (RegisteredEquipment == null)
{
return;
}
string ReportPath = System.IO.Path.Combine(Directory.GetCurrentDirectory(), "Reports", "Equipment" + ".rdl");
DBWorks.SQLHelper Parameters = new DBWorks.SQLHelper("dup={0}&equipment_id=", printTwo.Active ? 1 : 0);
Parameters.StartNewList("", ",");
foreach (var equipment in RegisteredEquipment)
{
Parameters.AddAsList(equipment.Id.ToString());
}
reportviewer2.LoadReport(new Uri(ReportPath), Parameters.Text, QSMain.ConnectionString);
}
public void SaveToDB(MySqlTransaction trans)
{
if (usedTable.Fields.Count < 1)
{
return;
}
logger.Info("Сохраняем данные настраиваемых полей для id={0}", id);
DBWorks.SQLHelper sql = new DBWorks.SQLHelper("UPDATE {0} SET ", usedTable.DBName);
foreach (CFFieldInfo field in usedTable.Fields)
{
sql.AddAsList(String.Format("{0} = @{0}", field.ColumnName));
}
;
sql.Add(" WHERE id = @id ");
try
{
MySqlCommand cmd = new MySqlCommand(sql.Text, (MySqlConnection)QSMain.ConnectionDB, trans);
cmd.Parameters.AddWithValue("@id", id);
foreach (CFFieldInfo field in usedTable.Fields)
{
switch (field.FieldType)
{
case FieldTypes.TString:
Entry stringWid = (Entry)FieldWidgets[field.ID];
cmd.Parameters.AddWithValue(field.ColumnName, DBWorks.ValueOrNull(stringWid.Text != "", stringWid.Text));
break;
case FieldTypes.TCurrency:
SpinButton moneyWid = (SpinButton)FieldWidgets[field.ID];
cmd.Parameters.AddWithValue(field.ColumnName, moneyWid.Value);
break;
}
}
cmd.ExecuteNonQuery();
logger.Info("Записано.");
}catch (Exception ex)
{
string mes = "Ошибка чтения данных для настраиваемых полей!";
logger.Error(ex, mes);
throw new ApplicationException(mes, ex);
}
}
void UpdateJournal()
{
if (!canUpdate)
return;
logger.Info("Получаем журнал изменений...");
MySqlCommand cmd = (MySqlCommand)QSMain.ConnectionDB.CreateCommand ();
DBWorks.SQLHelper sql = new DBWorks.SQLHelper("SELECT history_changeset.*, users.name as username FROM history_changeset " +
"LEFT JOIN users ON history_changeset.user_id = users.id " );
if(entrySearch.Text != "" || comboProperty.SelectedItem is HistoryFieldDesc)
sql.Add ("LEFT JOIN history_changes ON history_changeset.id = history_changes.changeset_id ");
sql.StartNewList (" WHERE ", " AND ");
if(datacomboObject.SelectedItem is HistoryObjectDesc)
{
sql.AddAsList ("history_changeset.object_name = @object_name");
cmd.Parameters.AddWithValue ("object_name", (datacomboObject.SelectedItem as HistoryObjectDesc).ObjectName);
}
if(ComboWorks.GetActiveId (comboUsers) > 0)
{
sql.AddAsList (" history_changeset.user_id = @user_id ");
cmd.Parameters.AddWithValue ("user_id", ComboWorks.GetActiveId (comboUsers));
}
if (comboAction.SelectedItem is ChangeSetType) {
sql.AddAsList (" history_changeset.operation = @operation");
cmd.Parameters.AddWithValue ("operation", ((ChangeSetType)comboAction.SelectedItem).ToString("G"));
}
if(entrySearch.Text != "")
{
sql.AddAsList ("(history_changes.old_value LIKE @searchtext OR history_changes.new_value LIKE @searchtext)");
cmd.Parameters.AddWithValue ("searchtext", String.Format ("%{0}%", entrySearch.Text));
}
if(comboProperty.SelectedItem is HistoryFieldDesc)
{
sql.AddAsList ("history_changes.path LIKE @property");
cmd.Parameters.AddWithValue ("property", String.Format ("%{0}%", (comboProperty.SelectedItem as HistoryFieldDesc).FieldName));
}
if (!selectperiod.IsAllTime) {
sql.AddAsList ("history_changeset.datetime BETWEEN @startdate AND @enddate");
cmd.Parameters.AddWithValue ("startdate", selectperiod.DateBegin.ToUniversalTime ());
cmd.Parameters.AddWithValue ("enddate", selectperiod.DateEnd.AddDays (1).ToUniversalTime ());
}
logger.Debug (sql.Text);
cmd.CommandText = sql.Text;
using(MySqlDataReader rdr = cmd.ExecuteReader())
{
ChangeSets = new List<HistoryChangeSet> ();
while (rdr.Read())
{
var changeset = new HistoryChangeSet () {
Id = rdr.GetInt32 ("id"),
UserId = DBWorks.GetInt (rdr, "user_id", -1),
UserName = DBWorks.GetString (rdr, "username", "Неизвестный"),
ChangeTime = rdr.GetDateTime ("datetime").ToLocalTime (),
Operation = (ChangeSetType)Enum.Parse (typeof(ChangeSetType), rdr.GetString ("operation")),
ObjectName = rdr.GetString ("object_name"),
ItemId = rdr.GetInt32 ("object_id"),
ItemTitle = DBWorks.GetString (rdr, "object_title", String.Empty)
};
ChangeSets.Add (changeset);
}
}
datatreeChangesets.ItemsDataSource = ChangeSets;
logger.Info(RusNumber.FormatCase (ChangeSets.Count, "Загружен {0} набор изменений.", "Загружено {0} набора изменений.", "Загружено {0} наборов изменений."));
}
public void SaveChanges(MySqlTransaction trans)
{
logger.Info("Записывам изменения в списке файлов...");
string sql = String.Format("INSERT INTO {0} (name, item_group, item_id, size, file) " +
"VALUES (@name, @item_group, @item_id, @size, @file)", TableName);
MySqlCommand cmd = new MySqlCommand(sql, (MySqlConnection)QSMain.ConnectionDB, trans);
cmd.Parameters.AddWithValue("@name", "");
cmd.Parameters.AddWithValue("@item_group", AttachToTable);
cmd.Parameters.AddWithValue("@item_id", ItemId);
cmd.Parameters.AddWithValue("@size", 0);
cmd.Parameters.AddWithValue("@file", null);
cmd.Prepare();
foreach (object[] row in FilesStore)
{
if ((int)row [(int)FilesCol.id] > 0)
{
continue;
}
logger.Info("Отправляем {0}...", row [(int)FilesCol.name]);
byte[] file = (byte[])row [(int)FilesCol.file];
cmd.Parameters ["@name"].Value = row [(int)FilesCol.name];
cmd.Parameters ["@size"].Value = file.LongLength;
cmd.Parameters ["@file"].Value = file;
try {
cmd.ExecuteNonQuery();
} catch (MySqlException ex) {
if (ex.Number == 1153)
{
logger.Warn(ex, "Превышен максимальный размер пакета для передачи на сервер.");
string Text = String.Format("При передачи файла {0}, " +
"превышен максимальный размер пакета для передачи на сервер базы данных. " +
"Файл не будет сохранен. " +
"Это значение настраивается на сервере, по умолчанию для MySQL оно равняется 1Мб. " +
"Максимальный размер файла поддерживаемый программой составляет 16Мб, мы рекомендуем " +
"установить в настройках сервера параметр <b>max_allowed_packet=16M</b>. Подробнее о настройке здесь " +
"http://dev.mysql.com/doc/refman/5.6/en/packet-too-large.html", row [(int)FilesCol.name]);
MessageDialog md = new MessageDialog((Gtk.Window) this.Toplevel, DialogFlags.Modal,
MessageType.Error,
ButtonsType.Ok, Text);
md.Run();
md.Destroy();
}
else if (ex.Number == 1118)
{
logger.Warn(ex, "Ошибка: превышена допустимая длина строки.");
string Text = String.Format("При передачи файла {0} была превышена максимально допустимая " +
"длина строки в БД MySQL. Файл не будет сохранен. Также эта ошибка может " +
"возникать при отсутствии возможности записи в log файл. В качестве возможного решения данной " +
"проблемы мы рекомендуем установить в настройках сервера следующие параметры: " +
"<b>\ninnodb_log_file_size = 499M" +
"\ninnodb_log_buffer_size = 499M</b>.",
row [(int)FilesCol.name]);
MessageDialog md = new MessageDialog((Gtk.Window) this.Toplevel, DialogFlags.Modal,
MessageType.Error,
ButtonsType.Ok, Text);
md.Run();
md.Destroy();
}
else
{
throw ex;
}
}
}
if (deletedItems.Count > 0)
{
logger.Info("Удаляем удаленные файлы на сервере...");
DBWorks.SQLHelper sqld = new DBWorks.SQLHelper("DELETE FROM {0} WHERE id IN ", TableName);
sqld.QuoteMode = DBWorks.QuoteType.SingleQuotes;
sqld.StartNewList("(", ", ");
deletedItems.ForEach(delegate(int obj) {
sqld.AddAsList(obj.ToString());
});
sqld.Add(")");
cmd = new MySqlCommand(sqld.Text, (MySqlConnection)QSMain.ConnectionDB, trans);
cmd.ExecuteNonQuery();
}
}
public override void Execute(DeleteCore core)
{
var sql = new DBWorks.SQLHelper ("UPDATE {0} SET ", TableName);
sql.Add ("{0} = NULL ", CleanField);
sql.Add (WhereStatment);
core.ExcuteDlg.AddExcuteOperation(String.Format("Очищаем ссылки в таблице {0}", TableName));
core.ExecuteSql(sql.Text, ItemId);
}
public void SaveChanges(MySqlTransaction trans)
{
logger.Info ("Записывам изменения в списке файлов...");
string sql = String.Format ("INSERT INTO {0} (name, item_group, item_id, size, file) " +
"VALUES (@name, @item_group, @item_id, @size, @file)", TableName);
MySqlCommand cmd = new MySqlCommand (sql, (MySqlConnection)QSMain.ConnectionDB, trans);
cmd.Prepare ();
cmd.Parameters.AddWithValue ("@name", "");
cmd.Parameters.AddWithValue ("@item_group", AttachToTable);
cmd.Parameters.AddWithValue ("@item_id", ItemId);
cmd.Parameters.AddWithValue ("@size", 0);
cmd.Parameters.AddWithValue ("@file", null);
foreach (object[] row in FilesStore) {
if ((int)row [(int)FilesCol.id] > 0)
continue;
logger.Info ("Отправляем {0}...", row [(int)FilesCol.name]);
byte[] file = (byte[])row [(int)FilesCol.file];
cmd.Parameters ["@name"].Value = row [(int)FilesCol.name];
cmd.Parameters ["@size"].Value = file.LongLength;
cmd.Parameters ["@file"].Value = file;
try {
cmd.ExecuteNonQuery ();
} catch (MySqlException ex) {
if (ex.Number == 1153) {
logger.Warn (ex, "Превышен максимальный размер пакета для передачи на сервер.");
string Text = String.Format ("При передачи файла {0}, " +
"превышен максимальный размер пакета для передачи на сервер базы данных. " +
"Файл не будет сохранен. " +
"Это значение настраивается на сервере, по умолчанию для MySQL оно равняется 1Мб. " +
"Максимальный размер файла поддерживаемый программой составляет 16Мб, мы рекомендуем " +
"установить в настройках сервера параметр <b>max_allowed_packet=16M</b>. Подробнее о настройке здесь " +
"http://dev.mysql.com/doc/refman/5.6/en/packet-too-large.html", row [(int)FilesCol.name]);
MessageDialog md = new MessageDialog ((Gtk.Window)this.Toplevel, DialogFlags.Modal,
MessageType.Error,
ButtonsType.Ok, Text);
md.Run ();
md.Destroy ();
} else if (ex.Number == 1118) {
logger.Warn (ex, "Ошибка: превышена допустимая длина строки.");
string Text = String.Format ("При передачи файла {0} была превышена максимально допустимая " +
"длина строки в БД MySQL. Файл не будет сохранен. Также эта ошибка может " +
"возникать при отсутствии возможности записи в log файл. В качестве возможного решения данной " +
"проблемы мы рекомендуем установить в настройках сервера следующие параметры: " +
"<b>\ninnodb_log_file_size = 499M" +
"\ninnodb_log_buffer_size = 499M</b>.",
row [(int)FilesCol.name]);
MessageDialog md = new MessageDialog ((Gtk.Window)this.Toplevel, DialogFlags.Modal,
MessageType.Error,
ButtonsType.Ok, Text);
md.Run ();
md.Destroy ();
} else
throw ex;
}
}
if (deletedItems.Count > 0) {
logger.Info ("Удаляем удаленные файлы на сервере...");
DBWorks.SQLHelper sqld = new DBWorks.SQLHelper ("DELETE FROM {0} WHERE id IN ", TableName);
sqld.QuoteMode = DBWorks.QuoteType.SingleQuotes;
sqld.StartNewList ("(", ", ");
deletedItems.ForEach (delegate(int obj) {
sqld.AddAsList (obj.ToString ());
});
sqld.Add (")");
cmd = new MySqlCommand (sqld.Text, (MySqlConnection)QSMain.ConnectionDB, trans);
cmd.ExecuteNonQuery ();
}
}
void SaveAllDocs()
{
TreeIter iter, imageiter;
bool HasIncomplete = false;;
int CountDoc = 0;
if(!ImageList.GetIterFirst(out iter))
return;
do
{
if((string) ImageList.GetValue(iter, 8) != DocIconGood)
HasIncomplete = true;
CountDoc++;
}
while(ImageList.IterNext(ref iter));
if(HasIncomplete)
{
string Message = "Не во всех документах полностью заполнены необходимые поля. В базе будут сохранены только готовые к записи документы.";
MessageDialog md = new MessageDialog ( this, DialogFlags.DestroyWithParent,
MessageType.Info,
ButtonsType.OkCancel,
Message);
ResponseType result = (ResponseType)md.Run ();
md.Destroy();
if(result == ResponseType.Cancel)
return;
}
logger.Info ("Проверяем на уникальность номера...");
QSMain.CheckConnectionAlive();
MySqlCommand cmd = QSMain.connectionDB.CreateCommand ();
DBWorks.SQLHelper sqlhelp =
new DBWorks.SQLHelper("SELECT number, date, create_date, type_id FROM docs WHERE YEAR(date) = YEAR(CURDATE()) AND number IN (");
int ix = 0;
ImageList.Foreach( delegate(TreeModel model, TreePath path, TreeIter iter2)
{
Document TempDoc = (Document)model.GetValue(iter2, 3);
if(TempDoc != null)
{
string paramName = String.Format ("@num{0}", ix);
sqlhelp.AddAsList(paramName);
cmd.Parameters.AddWithValue (paramName, TempDoc.DocNumber);
ix++;
}
return false;
});
sqlhelp.Add(")");
cmd.CommandText = sqlhelp.Text;
logger.Debug ("SQL: " + sqlhelp.Text);
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
string conflicts = "";
while(rdr.Read())
{
string number = rdr["number"].ToString();
int type_id = rdr.GetInt32("type_id");
ImageList.Foreach( delegate(TreeModel model, TreePath path, TreeIter iter2)
{
Document TempDoc = (Document)model.GetValue(iter2, 3);
if(TempDoc != null && TempDoc.DocNumber == number && TempDoc.TypeId == type_id)
{
conflicts += String.Format("{0} {1} от {2:d} загружен {3}\n", TempDoc.TypeName, number, rdr["date"], rdr["create_date"]);
return true;
}
return false;
});
}
if(conflicts != "")
{
string Message = String.Format("Документы со следующими номерами уже существуют в базе:\n{0}" +
"Все равно записать документы?", conflicts);
MessageDialog md = new MessageDialog ( this, DialogFlags.DestroyWithParent,
MessageType.Warning,
ButtonsType.YesNo,
Message);
ResponseType result = (ResponseType)md.Run ();
md.Destroy();
if(result == ResponseType.No)
return;
}
}
//Записываем
List<TreeIter> ForRemove = new List<TreeIter>();
progresswork.Text = "Записываем документы в базу данных...";
progresswork.Adjustment.Upper = CountDoc;
ImageList.GetIterFirst(out iter);
do
{
if((string) ImageList.GetValue(iter, 8) != DocIconGood)
continue;
Document doc = (Document)ImageList.GetValue(iter, 3);
QSMain.CheckConnectionAlive();
MySqlTransaction trans = QSMain.connectionDB.BeginTransaction();
try
{
string sql = "INSERT INTO docs(number, date, create_date, user_id, type_id) " +
"VALUES(@number, @date, @create_date, @user_id, @type_id)";
cmd = new MySqlCommand(sql, QSMain.connectionDB, trans);
cmd.Parameters.AddWithValue("@number", doc.DocNumber);
cmd.Parameters.AddWithValue("@date", doc.DocDate);
cmd.Parameters.AddWithValue("@create_date", DateTime.Now);
cmd.Parameters.AddWithValue("@user_id", QSMain.User.Id);
cmd.Parameters.AddWithValue("@type_id", doc.TypeId);
cmd.ExecuteNonQuery();
long docid = cmd.LastInsertedId;
if(doc.CountExtraFields > 0)
{
cmd = new MySqlCommand();
cmd.Connection = QSMain.connectionDB;
cmd.Transaction = trans;
string sqlinsert = "INSERT INTO extra_" + doc.DBTableName + "(doc_id";
string sqlvalues = "VALUES (@doc_id";
cmd.Parameters.AddWithValue("@doc_id", docid);
foreach(DocFieldInfo field in doc.FieldsList)
{
sqlinsert += ", " + field.DBName;
sqlvalues += ", @" + field.DBName;
cmd.Parameters.AddWithValue(field.DBName, doc.FieldValues[field.ID]);
}
sqlinsert += ") ";
sqlvalues += ")";
cmd.CommandText = sqlinsert + sqlvalues;
cmd.ExecuteNonQuery();
}
Console.WriteLine("doc");
sql = "INSERT INTO images(order_num, doc_id, type, size, image) " +
"VALUES(@order_num, @doc_id, @type, @size, @image)";
int order = 1;
ImageList.IterChildren(out imageiter, iter);
do
{
Console.WriteLine("image");
cmd = new MySqlCommand(sql, QSMain.connectionDB, trans);
cmd.Parameters.AddWithValue("@doc_id", docid);
cmd.Parameters.AddWithValue("@order_num", order);
cmd.Parameters.AddWithValue("@type", "jpg");
Pixbuf pix = (Pixbuf) ImageList.GetValue(imageiter, 5);
byte[] rawdata = pix.SaveToBuffer("jpeg", new string[] {"quality"}, new string[] {"10"});
cmd.Parameters.AddWithValue("@size", rawdata.LongLength);
cmd.Parameters.AddWithValue("@image", rawdata);
cmd.ExecuteNonQuery();
}
while(ImageList.IterNext(ref imageiter));
trans.Commit ();
progresswork.Adjustment.Value++;
MainClass.WaitRedraw();
ForRemove.Add(iter);
logger.Info("Ok");
}
catch (Exception ex)
{
trans.Rollback ();
QSMain.ErrorMessageWithLog(this, "Ошибка сохранения документов!", logger, ex);
return;
}
}
while(ImageList.IterNext(ref iter));
//удаляем записаное
foreach(TreeIter rowiter in ForRemove)
{
TreeIter temp = rowiter;
ImageList.Remove(ref temp);
}
progresswork.Text = "Ок";
progresswork.Fraction = 0;
}
private void ChangeContracts(MySqlTransaction trans)
{
progressbarMain.Adjustment.Value = 0;
progressbarMain.Adjustment.Upper = 2;
progressbarMain.Text = "Изменение договоров...";
QSMain.WaitRedraw ();
DBWorks.SQLHelper sql = new DBWorks.SQLHelper ("UPDATE contracts SET ");
if (checkSign.Active)
sql.AddAsList ("sign_date = @sign_date");
if(checkStart.Active)
sql.AddAsList ("start_date = @start_date");
if(checkEnd.Active)
sql.AddAsList ("end_date = @end_date");
sql.Add (" WHERE id IN (");
sql.StartNewList ();
foreach(object[] row in ContractsListStore)
{
if((bool)row[0] && !((BadContractChecks)row[7]).Bad)
sql.AddAsList (row [1].ToString ());
}
sql.Add (")");
progressbarMain.Adjustment.Value++;
QSMain.WaitRedraw ();
MySqlCommand cmd = new MySqlCommand(sql.Text, QSMain.connectionDB, trans);
cmd.Parameters.AddWithValue("@sign_date", dateSign.Date);
cmd.Parameters.AddWithValue("@start_date", dateStart.Date);
cmd.Parameters.AddWithValue("@end_date", dateEnd.Date);
cmd.ExecuteNonQuery ();
progressbarMain.Adjustment.Value++;
QSMain.WaitRedraw ();
}
public void LoadDataFromDB(int id)
{
if(usedTable.Fields.Count < 1)
{
logger.Info ("Нет полей для загрузки.");
return;
}
logger.Info ("Загружаем данные настраиваемых полей для id={0}", id);
this.id = id;
DBWorks.SQLHelper sql = new DBWorks.SQLHelper ("SELECT ");
foreach(CFFieldInfo field in usedTable.Fields)
{
sql.AddAsList (String.Format ("{0}.{1}", usedTable.DBName, field.ColumnName));
};
sql.Add (" FROM {0} WHERE {0}.id = @id ", usedTable.DBName);
logger.Debug (sql.Text);
try
{
MySqlCommand cmd = new MySqlCommand(sql.Text, (MySqlConnection)QSMain.ConnectionDB);
cmd.Parameters.AddWithValue("@id", id);
using (MySqlDataReader rdr = cmd.ExecuteReader ())
{
rdr.Read ();
foreach(CFFieldInfo field in usedTable.Fields)
{
switch (field.FieldType) {
case FieldTypes.TString :
Entry stringWid = (Entry)FieldWidgets[field.ID];
stringWid.Text = DBWorks.GetString(rdr, field.ColumnName, "");
break;
case FieldTypes.TCurrency :
SpinButton moneyWid = (SpinButton)FieldWidgets[field.ID];
moneyWid.Value = DBWorks.GetDouble(rdr, field.ColumnName, 0);
break;
}
}
}
logger.Info ("Ок");
}catch (Exception ex)
{
string mes = "Ошибка чтения данных для настраиваемых полей!";
logger.Error(ex, mes);
throw new ApplicationException (mes, ex);
}
}
public void SaveToDB(MySqlTransaction trans)
{
if(usedTable.Fields.Count < 1)
return;
logger.Info ("Сохраняем данные настраиваемых полей для id={0}", id);
DBWorks.SQLHelper sql = new DBWorks.SQLHelper ("UPDATE {0} SET ", usedTable.DBName);
foreach(CFFieldInfo field in usedTable.Fields)
{
sql.AddAsList (String.Format ("{0} = @{0}", field.ColumnName));
};
sql.Add (" WHERE id = @id ");
try
{
MySqlCommand cmd = new MySqlCommand(sql.Text, (MySqlConnection)QSMain.ConnectionDB, trans);
cmd.Parameters.AddWithValue("@id", id);
foreach(CFFieldInfo field in usedTable.Fields)
{
switch (field.FieldType) {
case FieldTypes.TString :
Entry stringWid = (Entry)FieldWidgets[field.ID];
cmd.Parameters.AddWithValue(field.ColumnName, DBWorks.ValueOrNull (stringWid.Text != "", stringWid.Text));
break;
case FieldTypes.TCurrency :
SpinButton moneyWid = (SpinButton)FieldWidgets[field.ID];
cmd.Parameters.AddWithValue(field.ColumnName, moneyWid.Value);
break;
}
}
cmd.ExecuteNonQuery ();
logger.Info ("Записано.");
}catch (Exception ex)
{
string mes = "Ошибка чтения данных для настраиваемых полей!";
logger.Error(ex, mes);
throw new ApplicationException (mes, ex);
}
}
void LoadChanges()
{
logger.Debug("Получаем набор изменений...");
MySqlCommand cmd = (MySqlCommand)QSMain.ConnectionDB.CreateCommand ();
DBWorks.SQLHelper sql = new DBWorks.SQLHelper("SELECT history_changes.* FROM history_changes " +
"WHERE changeset_id = @changeset_id" );
cmd.CommandText = sql.Text;
cmd.Parameters.AddWithValue ("changeset_id", Id);
using(MySqlDataReader rdr = cmd.ExecuteReader())
{
changes = new List<FieldChange> ();
while (rdr.Read())
{
var change = new FieldChange {
Id = rdr.GetInt32 ("id"),
Path = rdr.GetString ("path"),
Type = (FieldChangeType)Enum.Parse (typeof(FieldChangeType), rdr.GetString ("type")),
OldValue = rdr.GetString ("old_value"),
NewValue = rdr.GetString ("new_value")
};
changes.Add (change);
}
}
logger.Debug(RusNumber.FormatCase (Changes.Count, "Загружен набор из {0} изменения.", "Загружен набор из {0} изменений.", "Загружен набор из {0} изменений."));
}
void UpdateAccrual()
{
if (!accrualPrepared)
return;
logger.Info("Получаем таблицу начислений...");
TreeIter iter;
string cashClause = "";
if(comboAccrualCash.GetActiveIter(out iter) && comboAccrualCash.Active > 0)
{
cashClause = String.Format ("cash_id = '{0}'", ComboWorks.GetActiveId (comboAccrualCash));
}
bool legacyPayments = true;
string incomeItemClause = "";
if (comboAccrualItem.GetActiveIter (out iter) && comboAccrualItem.Active > 0)
{
incomeItemClause = String.Format ("income_id = '{0}'", ComboWorks.GetActiveId (comboAccrualItem));
legacyPayments = false;
}
string clause = ((cashClause.Length > 0) && (incomeItemClause.Length > 0)) ? cashClause +" AND "+ incomeItemClause : cashClause + incomeItemClause ;
string whereClause = (clause.Length > 0) ? "WHERE " + clause : "";
string paymentsTableName = legacyPayments ? "credit_slips ": "accrual_pays LEFT JOIN payment_details ON accrual_pays.id=payment_details.accrual_pay_id ";
DBWorks.SQLHelper sql = new DBWorks.SQLHelper(
"SELECT accrual.id as id, month, year, contracts.number as contract_no, no_complete, contracts.lessee_id as lessee_id, " +
"lessees.name as lessee, sumtable.sum as sum, paidtable.sum as paidsum FROM accrual " +
"LEFT JOIN contracts ON contracts.id = accrual.contract_id " +
"LEFT JOIN lessees ON contracts.lessee_id = lessees.id " +
"LEFT JOIN (SELECT accrual_id, SUM(count * price) as sum FROM accrual_pays "+
"LEFT JOIN services ON accrual_pays.service_id = services.id " +
whereClause + " GROUP BY accrual_id) as sumtable " +
"ON sumtable.accrual_id = accrual.id " +
"LEFT JOIN ("+
"SELECT accrual_id, SUM(sum) as sum FROM " + paymentsTableName +
whereClause + " GROUP BY accrual_id) as paidtable " +
"ON paidtable.accrual_id = accrual.id");
sql.StartNewList (" WHERE ", " AND ");
if (comboAccuralYear.ActiveText == NameOf2YearOption)
sql.AddAsList (String.Format("year IN ('{0:yyyy}','{1:yyyy}')", DateTime.Today, DateTime.Today.AddYears (-1)));
else if (comboAccuralYear.ActiveText != NameOfAllOption)
sql.AddAsList (String.Format("year = '{0}'", comboAccuralYear.ActiveText));
if(comboAccrualMonth.Active > 0)
{
sql.AddAsList("accrual.month = '" + comboAccrualMonth.Active + "' ");
}
if(comboAccrualOrg.GetActiveIter(out iter) && comboAccrualOrg.Active > 0)
{
sql.AddAsList ("contracts.org_id = '" + comboAccrualOrg.Model.GetValue(iter,1) + "' ");
}
if(checkNotComplete.Active)
{
sql.AddAsList("no_complete = TRUE");
}
if(checkOnlyNotPaid.Active)
{
sql.AddAsList("IFNULL(paidtable.sum, 0) < IFNULL(sumtable.sum,0)");
}
MySqlCommand cmd = new MySqlCommand(sql.Text, QSMain.connectionDB);
MySqlDataReader rdr = cmd.ExecuteReader();
var Column = new {
Sum = rdr.GetOrdinal ("sum"),
Paidsum = rdr.GetOrdinal ("paidsum"),
Id = rdr.GetOrdinal ("id"),
Month = rdr.GetOrdinal ("month"),
Year = rdr.GetOrdinal("year"),
ContractNumber = rdr.GetOrdinal ("contract_no"),
NotComplete = rdr.GetOrdinal ("no_complete"),
LesseeId = rdr.GetOrdinal ("lessee_id"),
LesseeName = rdr.GetOrdinal("lessee")
};
AccrualList = new List<AccrualListEntryDTO> ();
while (rdr.Read())
{
decimal rowsum, rowpaidsum;
rowsum = DBWorks.GetDecimal (rdr, Column.Sum, 0m);
rowpaidsum = DBWorks.GetDecimal (rdr, Column.Paidsum, 0m);
AccrualList.Add (new AccrualListEntryDTO {
Id = rdr.GetInt32 (Column.Id),
MonthText = String.Format ("{0:MMMM yyyy}", new DateTime (rdr.GetInt32 (Column.Year), rdr.GetInt32 (Column.Month), 1)),
Month = rdr.GetInt32 (Column.Month),
ContractNumber = rdr [Column.ContractNumber].ToString (),
LesseeId = rdr.GetInt32 (Column.LesseeId),
Lessee = rdr [Column.LesseeName].ToString (),
SumText = String.Format ("{0:C}", rowsum),
Sum = rowsum,
PaidSumText = String.Format ("{0:C}", rowpaidsum),
PaidSum = rowpaidsum,
DebtText = String.Format ("{0:C}", rowsum - rowpaidsum),
Debt = rowsum - rowpaidsum,
NotComplete = rdr.GetBoolean (Column.NotComplete)
});
}
rdr.Close ();
treeviewAccrual.ItemsDataSource = AccrualList;
Refilter ();
logger.Info("Ok");
CalculateAccrualSum();
OnTreeviewAccrualCursorChanged (null, EventArgs.Empty);
}
void SaveAllDocs()
{
TreeIter iter, imageiter;
bool HasIncomplete = false;;
int CountDoc = 0;
if (!ImageList.GetIterFirst(out iter))
{
return;
}
do
{
if ((string)ImageList.GetValue(iter, 8) != DocIconGood)
{
HasIncomplete = true;
}
CountDoc++;
}while(ImageList.IterNext(ref iter));
if (HasIncomplete)
{
string Message = "Не во всех документах полностью заполнены необходимые поля. В базе будут сохранены только готовые к записи документы.";
MessageDialog md = new MessageDialog(this, DialogFlags.DestroyWithParent,
MessageType.Info,
ButtonsType.OkCancel,
Message);
ResponseType result = (ResponseType)md.Run();
md.Destroy();
if (result == ResponseType.Cancel)
{
return;
}
}
logger.Info("Проверяем на уникальность номера...");
QSMain.CheckConnectionAlive();
MySqlCommand cmd = QSMain.connectionDB.CreateCommand();
DBWorks.SQLHelper sqlhelp =
new DBWorks.SQLHelper("SELECT number, date, create_date, type_id FROM docs WHERE YEAR(date) = YEAR(CURDATE()) AND number IN (");
int ix = 0;
ImageList.Foreach(delegate(TreeModel model, TreePath path, TreeIter iter2)
{
Document TempDoc = (Document)model.GetValue(iter2, 3);
if (TempDoc != null)
{
string paramName = String.Format("@num{0}", ix);
sqlhelp.AddAsList(paramName);
cmd.Parameters.AddWithValue(paramName, TempDoc.DocNumber);
ix++;
}
return(false);
});
sqlhelp.Add(")");
cmd.CommandText = sqlhelp.Text;
logger.Debug("SQL: " + sqlhelp.Text);
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
string conflicts = "";
while (rdr.Read())
{
string number = rdr["number"].ToString();
int type_id = rdr.GetInt32("type_id");
ImageList.Foreach(delegate(TreeModel model, TreePath path, TreeIter iter2)
{
Document TempDoc = (Document)model.GetValue(iter2, 3);
if (TempDoc != null && TempDoc.DocNumber == number && TempDoc.TypeId == type_id)
{
conflicts += String.Format("{0} {1} от {2:d} загружен {3}\n", TempDoc.Name, number, rdr["date"], rdr["create_date"]);
return(true);
}
return(false);
});
}
if (conflicts != "")
{
string Message = String.Format("Документы со следующими номерами уже существуют в базе:\n{0}" +
"Все равно записать документы?", conflicts);
MessageDialog md = new MessageDialog(this, DialogFlags.DestroyWithParent,
MessageType.Warning,
ButtonsType.YesNo,
Message);
ResponseType result = (ResponseType)md.Run();
md.Destroy();
if (result == ResponseType.No)
{
return;
}
}
}
//Записываем
List <TreeIter> ForRemove = new List <TreeIter>();
progresswork.Text = "Записываем документы в базу данных...";
progresswork.Adjustment.Upper = CountDoc;
ImageList.GetIterFirst(out iter);
do
{
if ((string)ImageList.GetValue(iter, 8) != DocIconGood)
{
continue;
}
Document doc = (Document)ImageList.GetValue(iter, 3);
QSMain.CheckConnectionAlive();
MySqlTransaction trans = QSMain.connectionDB.BeginTransaction();
try
{
string sql = "INSERT INTO docs(number, date, create_date, user_id, type_id) " +
"VALUES(@number, @date, @create_date, @user_id, @type_id)";
cmd = new MySqlCommand(sql, QSMain.connectionDB, trans);
cmd.Parameters.AddWithValue("@number", doc.DocNumber);
cmd.Parameters.AddWithValue("@date", doc.DocDate);
cmd.Parameters.AddWithValue("@create_date", DateTime.Now);
cmd.Parameters.AddWithValue("@user_id", QSMain.User.Id);
cmd.Parameters.AddWithValue("@type_id", doc.TypeId);
cmd.ExecuteNonQuery();
long docid = cmd.LastInsertedId;
if (doc.CountExtraFields > 0)
{
cmd = new MySqlCommand();
cmd.Connection = QSMain.connectionDB;
cmd.Transaction = trans;
string sqlinsert = "INSERT INTO extra_" + doc.DBTableName + "(doc_id";
string sqlvalues = "VALUES (@doc_id";
cmd.Parameters.AddWithValue("@doc_id", docid);
foreach (DocFieldInfo field in doc.FieldsList)
{
sqlinsert += ", " + field.DBName;
sqlvalues += ", @" + field.DBName;
cmd.Parameters.AddWithValue(field.DBName, doc.FieldValues[field.ID]);
}
sqlinsert += ") ";
sqlvalues += ")";
cmd.CommandText = sqlinsert + sqlvalues;
cmd.ExecuteNonQuery();
}
Console.WriteLine("doc");
sql = "INSERT INTO images(order_num, doc_id, type, size, image) " +
"VALUES(@order_num, @doc_id, @type, @size, @image)";
int order = 1;
ImageList.IterChildren(out imageiter, iter);
do
{
Console.WriteLine("image");
cmd = new MySqlCommand(sql, QSMain.connectionDB, trans);
cmd.Parameters.AddWithValue("@doc_id", docid);
cmd.Parameters.AddWithValue("@order_num", order);
cmd.Parameters.AddWithValue("@type", "jpg");
Pixbuf pix = (Pixbuf)ImageList.GetValue(imageiter, 5);
byte[] rawdata = pix.SaveToBuffer("jpeg", new string[] { "quality" }, new string[] { "10" });
cmd.Parameters.AddWithValue("@size", rawdata.LongLength);
cmd.Parameters.AddWithValue("@image", rawdata);
cmd.ExecuteNonQuery();
}while(ImageList.IterNext(ref imageiter));
trans.Commit();
progresswork.Adjustment.Value++;
MainClass.WaitRedraw();
ForRemove.Add(iter);
logger.Info("Ok");
}
catch (Exception ex)
{
trans.Rollback();
QSMain.ErrorMessageWithLog(this, "Ошибка сохранения документов!", logger, ex);
return;
}
}while(ImageList.IterNext(ref iter));
//удаляем записаное
foreach (TreeIter rowiter in ForRemove)
{
TreeIter temp = rowiter;
ImageList.Remove(ref temp);
}
progresswork.Text = "Ок";
progresswork.Fraction = 0;
}
