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 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 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; }