/// <summary> /// Окно авторизации /// </summary> public AuthentificateWindow() { InitializeComponent(); try { if (File.Exists(ProgramSettings.WorkFolder + "settings.plg")) { using (FileStream fs = new FileStream(ProgramSettings.WorkFolder + "settings.plg", FileMode.Open)) { BinaryFormatter serializer = new BinaryFormatter(); AuthentificateSettings auth = (AuthentificateSettings)serializer.Deserialize(fs); tbLogin.Text = Criptex.UnCript(auth.UserName); } pbPassword.Focus(); } else { tbLogin.Focus(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
/// <summary> /// Аннулирование единицы измерения /// </summary> /// <returns>возвращает TRUE, если единица измерения аннулирована успешно</returns> public bool AnnulateUnit() { bool result = false; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = String.Format(@"UPDATE `UNIT` SET `IS_ANNULATED` = '1' WHERE `UNIT`.`ID` = {0}", ID); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); } Journal.CreateJournal("аннулировал", "единицу измерения " + Nomination, connection); connection.Close(); IsAnnulated = true; } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка аннулирования единицы измерения", ex.Message); } return(result); }
/// <summary> /// Установить значение нумератора /// </summary> /// <param name="value">значение</param> /// <returns>возвращает TRUE, если значение изменено успешно</returns> public bool SetValue(int value) { bool result = false; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = String.Format(@"UPDATE `NUMERATOR` SET `VALUE` = '{0}' WHERE `NUMERATOR`.`ID` = {1}", value, ID); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); } Journal.CreateJournal("переустановил", "значение нумератора " + Nomination, connection); connection.Close(); Value = value; } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка изменения значения нумератора", ex.Message); } return(result); }
/// <summary> /// Установить значение нумератора /// </summary> /// <param name="value">значение</param> /// <param name="isPurchase">приходная накладная</param> /// <returns>возвращает TRUE, если значение нумератора изменено успешно</returns> public static bool SetNumeratorValue(int value, bool isPurchase) { bool result = false; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string nomination = (isPurchase) ? "Приходная накладная" : "Расходная накладная"; string sql = String.Format(@"UPDATE `NUMERATOR` SET `VALUE` = '{0}' WHERE `NUMERATOR`.`NOMINATION` = '{1}'", value, nomination); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); } Journal.CreateJournal("переустановил", "значение нумератора " + nomination, connection); connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка изменения значения нумератора", ex.Message); } return(result); }
/// <summary> /// Получить значение нумератора /// </summary> /// <param name="isPurchase">приходная накладная</param> /// <returns>возвращает текущее значение нумератора, или 0, если данные не получены</returns> public static int GetNumeratorValue(bool isPurchase) { int value = 0; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string nomination = (isPurchase) ? "Приходная накладная" : "Расходная накладная"; string sql = String.Format(@"SELECT `VALUE` FROM `NUMERATOR` WHERE `NUMERATOR`.`NOMINATION` = '{0}'", nomination); using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { value = Conversion.ToInt(reader["VALUE"].ToString()); } } } connection.Close(); } } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения значения нумератора", ex.Message); } return(value); }
/// <summary> /// Создание единицы измерения /// </summary> /// <returns>возвращает TRUE, если единица измерения создана успешно</returns> public bool CreateUnit() { bool result = false; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = String.Format(@"INSERT INTO `UNIT` (`NOMINATION`) VALUES ('{0}')", Nomination); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); ID = (int)command.LastInsertedId; } Journal.CreateJournal("создал", "единицу измерения " + Nomination, connection); connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка создания единицы измерения", ex.Message); } return(result); }
/// <summary> /// Удаление элемента накладной /// </summary> /// <param name="id">ID элемента накладной</param> /// <param name="isPurchase"></param> /// <returns></returns> public static bool DeleteItem(int id, bool isPurchase) { bool result = false; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string table = (isPurchase) ? "PURCHASE_INVOICE_UNIT" : "SALES_INVOICE_UNIT"; string sql = String.Format(@"DELETE FROM `{0}` WHERE`{0}`.`ID` = {1}", table, id); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); } connection.Close(); result = true; } } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка удаления позиции накладной", ex.Message); } return(result); }
/// <summary> /// Создать запись в журнале событий /// </summary> /// <param name="verb">глагол выполненного действия</param> /// <param name="message">сообщение о событии</param> /// <returns>возвращает TRUE, если запись создана успешно</returns> public static bool CreateJournal(string verb, string message) { bool result = false; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string act = GetHeader(verb) + message; string sql = String.Format(@"INSERT INTO `JOURNAL` (`DATE`, `ID_ACCOUNT`, `ACT`) VALUES ('{0}', '{1}', '{2}')", CurrentDate(), ProgramSettings.CurrentUser.ID, act); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); } connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка сохранения записи в журнал событий", ex.Message); } return(result); }
/// <summary> /// Создание накладной /// </summary> /// <returns>возвращает TRUE, если накладная создана успешно</returns> public bool CreateInvoice() { bool result = false; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string table = (IsPurchase) ? "PURCHASE_INVOICE" : "SALES_INVOICE"; string sql = String.Format(@"INSERT INTO `{0}` (`ID_ACCOUNT`, `ID_COUNTERPARTY`, `COST`, `COUPON`, `CREATE_DATE`) VALUES ('{1}', '{2}', '{3}', '{4}', '{5}')", table, _id_account, _id_counterparty, Conversion.ToString(_cost), Conversion.ToString(_coupon), Conversion.ToString(DateTime.Today)); CreateDate = DateTime.Today; using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); ID = (int)command.LastInsertedId; } connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка создания накладной", ex.Message); } return(result); }
/// <summary> /// Создать товар /// </summary> /// <returns>возвращает TRUE если товар создан успешно</returns> public bool CreateProduct() { bool result = false; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = String.Format(@"INSERT INTO `PRODUCT` (`PRODUCT_CODE`, `NOMINATION`, `COUNT_IN_PACKAGE`, `COST`, `COUPON`, `ID_UNIT`, `COST_PURCHASE`, `COUPON_PURCHASE`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')", _productCode, _nomination, _countInPackage, Conversion.ToString(_cost), Conversion.ToString(_coupon), _id_unit, Conversion.ToString(_costPurchase), Conversion.ToString(_couponPurchase)); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); ID = (int)command.LastInsertedId; } Journal.CreateJournal("создал", "товар " + ProductCode, connection); connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка создания товара", ex.Message); } return(result); }
/// <summary> /// Создать новый аккаунт /// </summary> /// <returns>возвращает TRUE, если аккаунт успешно создан</returns> public bool CreateAccount() { bool result = false; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string newPass = Criptex.Cript("12345"); string sql = String.Format(@"INSERT INTO `ACCOUNT` (`LOGIN`, `PASSWORD`, `SURNAME`, `NAME`, `PATRONYMIC`, `ADRESS`, `PHONE`, `SEX`, `PURCHASE_INVOICE`, `SALES_INVOICE`, `REPORTS`, `REPORT_PURCHASE_INVOICE`, `REPORT_SALES_INVOICE`, `REPORT_INCOME_EXPENSE`, `COUNTERPARTY`, `COUNTERPARTY_PROVIDER`, `COUNTERPARTY_PROVIDER_ADD`, `COUNTERPARTY_PROVIDER_EDIT`, `COUNTERPARTY_PROVIDER_DELETE`, `COUNTERPARTY_CUSTOMER`, `COUNTERPARTY_CUSTOMER_ADD`, `COUNTERPARTY_CUSTOMER_EDIT`, `COUNTERPARTY_CUSTOMER_DELETE`, `WAREHOUSE`, `WAREHOUSE_ADD_PRODUCT`, `WAREHOUSE_EDIT_PRODUCT`, `WAREHOUSE_ANNULATE_PRODUCT`, `WAREHOUSE_EDIT_COUNT`, `ADMIN_PANEL`, `ADMIN_PANEL_ADD_ACCOUNT`, `ADMIN_PANEL_EDIT_ACCOUNT`, `ADMIN_PANEL_DELETE_ACCOUNT`, `ADMIN_PANEL_SET_NUMERATOR`, `ADMIN_PANEL_JOURNAL`, `IS_ANNULATED`, `REPORT_UN_ISSUE_PURCHASE_INVOICE`, `REPORT_UN_ISSUE_SALES_INVOICE`, `CODE`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}', '{22}', '{23}', '{24}', '{25}', '{26}', '{27}', '{28}', '{29}', '{30}', '{31}', '{32}', '{33}', '{34}', '{35}', '{36}', '{37}')", Login, newPass, PersonalData.Surname, PersonalData.Name, PersonalData.Patronymic, Criptex.Cript(PersonalData.Adress), Criptex.Cript(PersonalData.Phone), Conversion.ToString(PersonalData.Sex), Conversion.ToString(InvoiceData.PurchaseInvoice), Conversion.ToString(InvoiceData.SalesInvoice), Conversion.ToString(ReportsData.Reports), Conversion.ToString(ReportsData.ReportPurchaseInvoice), Conversion.ToString(ReportsData.ReportSalesInvoice), Conversion.ToString(ReportsData.ReportIncomeExpense), Conversion.ToString(CounterpartyData.Counterparty), Conversion.ToString(CounterpartyData.CounterpartyProvider), Conversion.ToString(CounterpartyData.CounterpartyProviderAdd), Conversion.ToString(CounterpartyData.CounterpartyProviderEdit), Conversion.ToString(CounterpartyData.CounterpartyProviderDelete), Conversion.ToString(CounterpartyData.CounterpartyCustomer), Conversion.ToString(CounterpartyData.CounterpartyCustomerAdd), Conversion.ToString(CounterpartyData.CounterpartyCustomerEdit), Conversion.ToString(CounterpartyData.CounterpartyCustomerDelete), Conversion.ToString(WarehouseData.Warehouse), Conversion.ToString(WarehouseData.WarehouseAddProduct), Conversion.ToString(WarehouseData.WarehouseEditProduct), Conversion.ToString(WarehouseData.WarehouseAnnulateProduct), Conversion.ToString(WarehouseData.WarehouseEditCount), Conversion.ToString(AdminPanelData.AdminPanel), Conversion.ToString(AdminPanelData.AdminPanelAddAccount), Conversion.ToString(AdminPanelData.AdminPanelEditAccount), Conversion.ToString(AdminPanelData.AdminPanelDeleteAccount), Conversion.ToString(AdminPanelData.AdminPanelSetNumerator), Conversion.ToString(AdminPanelData.AdminPanelJournal), 0, Conversion.ToString(ReportsData.ReportUnIssuePurchaseInvoice), Conversion.ToString(ReportsData.ReportUnIssueSalesInvoice), PersonalData.Code); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); ID = (int)command.LastInsertedId; } Journal.CreateJournal("создал", "пользователя " + _login, connection); connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка создания пользователя", ex.Message); } return(result); }
/// <summary> /// Создать контрагента /// </summary> /// <returns>возвращает TRUE, если контрагент успешно создан</returns> public bool CreateCounterparty() { bool result = false; try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = String.Format(@"INSERT INTO `COUNTERPARTY` (`SURNAME`, `NAME`, `PATRONYMIC`, `ADRESS`, `PHONE`, `NOMINATION`, `IS_PROVIDER`, `IS_ANNULATED`, `CODE`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')", Surname, Name, Patronymic, Conversion.ToCriptString(Adress), Conversion.ToCriptString(Phone), Nomination, Conversion.ToString(IsProvider), 0, Code); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); ID = (int)command.LastInsertedId; } string name = (Nomination != "") ? Nomination : Surname + " " + Name; Journal.CreateJournal("создал", "контрагента " + name, connection); connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка создания контрагента", ex.Message); } return(result); }
/// <summary> /// Получить данные по ID /// <para>возвращает TRUE, если данные успешно получены</para> /// </summary /// <param name="isPurchase">TRUE если приходная накладная, FALSE если расходная</param> /// <returns>возвращает TRUE, если данные успешно получены</returns> public bool GetDataByID(bool isPurchase) { _is_purchase = isPurchase; bool result = false; if (_id != 0) { try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string table = (isPurchase) ? "PURCHASE_INVOICE" : "SALES_INVOICE"; string sql = String.Format(@"SELECT * FROM `{0}` WHERE `{0}`.`ID` = {1}", table, ID); using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Number = Conversion.ToInt(reader["NUMBER"].ToString()); ID_Account = Conversion.ToInt(reader["ID_ACCOUNT"].ToString()); ID_Counterparty = Conversion.ToInt(reader["ID_COUNTERPARTY"].ToString()); Date = Conversion.ToDateTime(reader["DATE"].ToString()); CreateDate = Conversion.ToDateTime(reader["CREATE_DATE"].ToString()); Cost = Conversion.ToDouble(reader["COST"].ToString()); Coupon = Conversion.ToDouble(reader["COUPON"].ToString()); IsIssued = Conversion.ToBool(reader["IS_ISSUED"].ToString()); IsLocked = Conversion.ToBool(reader["IS_LOCKED"].ToString()); } } } Items = InvoiceItem.GetInvoiceItemList(_id, _is_purchase, connection); connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения данных накладной", ex.Message); } } else { Dialog.ErrorMessage(null, "Не указан ID накладной"); } return(result); }
private void btnConnect_Click(object sender, RoutedEventArgs e) { // Получение настроек подключения //ProgramSettings.Server = tbServer.Text; //ProgramSettings.DB = tbDB.Text; //ProgramSettings.AdminLogin = tbLogin.Text; //ProgramSettings.AdminPassword = Criptex.Cript(pbPassword.Password); // Проверка подключения MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString)); bool connected = true; try { connection.Open(); connection.Close(); } catch (Exception ex) { connected = false; Dialog.ErrorMessage(this, "Ошибка подключения", ex.Message); } connection = null; if (!connected) { return; } // Сохранение настроек try { SaveConnectionData saveData = new SaveConnectionData(); //saveData.Server = Criptex.Cript(ProgramSettings.Server); //saveData.DB = Criptex.Cript(ProgramSettings.DB); //saveData.AdminLogin = Criptex.Cript(ProgramSettings.AdminLogin); //saveData.AdminPassword = ProgramSettings.AdminPassword; using (FileStream fs = new FileStream(ProgramSettings.WorkFolder + "conncfg.plg", FileMode.Create)) { BinaryFormatter serializer = new BinaryFormatter(); serializer.Serialize(fs, saveData); } this.DialogResult = true; } catch (Exception ex) { Dialog.ErrorMessage(this, "Ошибка сохранения настроек подключения", ex.Message); return; } }
/// <summary> /// Получить список пользователей с ролью /// </summary> /// <param name="isPurchase">роль "Создание приходной накладной"</param> public static List <Account> GetAccountsByRoles(bool isPurchase) { List <Account> list = new List <Account>(); try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = ""; if (isPurchase) { sql = @"SELECT `ID`, `LOGIN`, `CODE`, `SURNAME`, `NAME`, `PATRONYMIC` FROM `ACCOUNT` WHERE `ACCOUNT`.`IS_ANNULATED` = '0' AND `ACCOUNT`.`PURCHASE_INVOICE` = '1'"; } else { sql = @"SELECT `ID`, `LOGIN`, `CODE`, `SURNAME`, `NAME`, `PATRONYMIC` FROM `ACCOUNT` WHERE `ACCOUNT`.`IS_ANNULATED` = '0' AND `ACCOUNT`.`SALES_INVOICE` = '1'"; } using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Account account = new Account(); account.ID = Conversion.ToInt(reader["ID"].ToString()); account.Login = reader["LOGIN"].ToString(); account.PersonalData.Code = reader["CODE"].ToString(); account.PersonalData.Surname = reader["SURNAME"].ToString(); account.PersonalData.Name = reader["NAME"].ToString(); account.PersonalData.Patronymic = reader["PATRONYMIC"].ToString(); list.Add(account); } } } connection.Close(); } } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения списка пользователей", ex.Message); } return(list); }
/// <summary> /// Получить не защищенные данные по ID /// <para>возвращает TRUE, если данные успешно получены</para> /// </summary> /// <returns>возвращает TRUE, если данные успешно получены</returns> public bool GetPublicDataByID() { bool result = false; if (_id != 0) { try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = "SELECT * FROM `COUNTERPARTY` WHERE `COUNTERPARTY`.`ID` = " + ID.ToString(); using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Code = reader["CODE"].ToString(); Surname = reader["SURNAME"].ToString(); Name = reader["NAME"].ToString(); Patronymic = reader["PATRONYMIC"].ToString(); Nomination = reader["NOMINATION"].ToString(); Adress = string.Empty; Phone = string.Empty; IsProvider = Conversion.ToBool(reader["IS_PROVIDER"].ToString()); IsAnnulated = Conversion.ToBool(reader["IS_ANNULATED"].ToString()); } } } connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения данных о контрагенте", ex.Message); } } else { Dialog.ErrorMessage(null, "Не указан ID контрагента"); } return(result); }
/// <summary> /// Получить данные по ID /// <para>возвращает TRUE, если данные успешно получены</para> /// </summary /// <param name="isPurchase">TRUE если элемент приходной накладной, FALSE если расходной</param> /// <returns>возвращает TRUE, если данные успешно получены</returns> public bool GetDataByID(bool isPurchase) { bool result = false; if (_id != 0) { try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string table = (isPurchase) ? "PURCHASE_INVOICE_UNIT" : "SALES_INVOICE_UNIT"; string sql = String.Format(@"SELECT * FROM `{0}` WHERE `{0}`.`ID` = {1}", table, ID); using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { ID_Invoice = Conversion.ToInt(reader["ID_INVOICE"].ToString()); ID_Product = Conversion.ToInt(reader["ID_PRODUCT"].ToString()); Count = Conversion.ToDouble(reader["COUNT"].ToString()); Cost = Conversion.ToDouble(reader["COST"].ToString()); Coupon = Conversion.ToDouble(reader["COUPON"].ToString()); ProductCost = Conversion.ToDouble(reader["PRODUCT_COST"].ToString()); ProductCoupon = Conversion.ToDouble(reader["PRODUCT_COUPON"].ToString()); } } } connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения данных элемента накладной", ex.Message); } } else { Dialog.ErrorMessage(null, "Не указан ID элемента накладной"); } return(result); }
/// <summary> /// Получить данные по ID /// <para>возвращает TRUE, если данные успешно получены</para> /// </summary> /// <returns></returns> public bool GetDataByID() { bool result = false; if (_id != 0) { try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = "SELECT * FROM `PRODUCT` WHERE `PRODUCT`.`ID` = " + ID.ToString(); using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { ProductCode = reader["PRODUCT_CODE"].ToString(); Nomination = reader["NOMINATION"].ToString(); CountInPackage = Conversion.ToDouble(reader["COUNT_IN_PACKAGE"].ToString()); Cost = Conversion.ToDouble(reader["COST"].ToString()); Coupon = Conversion.ToDouble(reader["COUPON"].ToString()); CostPurchase = Conversion.ToDouble(reader["COST_PURCHASE"].ToString()); CouponPurchase = Conversion.ToDouble(reader["COUPON_PURCHASE"].ToString()); Count = Conversion.ToDouble(reader["COUNT"].ToString()); LockedCount = Conversion.ToDouble(reader["LOCKED_COUNT"].ToString()); ID_Unit = Conversion.ToInt(reader["ID_UNIT"].ToString()); IsAnnulated = Conversion.ToBool(reader["IS_ANNULATED"].ToString()); } } } connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения данных", ex.Message); } } return(result); }
/// <summary> /// Получить список аккаунтов с минимальными данными согласно контексту поиска /// </summary> /// <param name="search">контекст поиска по логину, коду, фамилии, имени, отчеству</param> public static List <Account> GetActualAccountList(string search = "") { List <Account> result = new List <Account>(); try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = String.Format(@"SELECT `ID`, `LOGIN`, `CODE`, `SURNAME`, `NAME`, `PATRONYMIC` FROM `ACCOUNT` WHERE `ACCOUNT`.`IS_ANNULATED` = '0' AND `ACCOUNT`.`LOGIN` <> 'admin'"); if (search != "") { sql += String.Format(@" AND (`ACCOUNT`.`LOGIN` LIKE '%{0}%' OR `ACCOUNT`.`CODE` LIKE '%{0}%' OR `ACCOUNT`.`SURNAME` LIKE '%{0}%' OR `ACCOUNT`.`NAME` LIKE '%{0}%' OR `ACCOUNT`.`PATRONYMIC` LIKE '%{0}%')", search); } using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Account account = new Account(); account.ID = Conversion.ToInt(reader["ID"].ToString()); account.Login = reader["LOGIN"].ToString(); account.PersonalData.Code = reader["CODE"].ToString(); account.PersonalData.Surname = reader["SURNAME"].ToString(); account.PersonalData.Name = reader["NAME"].ToString(); account.PersonalData.Patronymic = reader["PATRONYMIC"].ToString(); result.Add(account); } } } connection.Close(); } } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения списка пользователей", ex.Message); } return(result); }
/// <summary> /// Удалить накладную /// </summary> /// <returns>возвращает TRUE, если накладная удалена успешно</returns> public bool DeleteInvoice() { bool result = false; if (_id != 0) { try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string table = (IsPurchase) ? "PURCHASE_INVOICE" : "SALES_INVOICE"; string sql = String.Format(@"DELETE FROM `{0}` WHERE `{0}`.`ID` = {1}", table, _id); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); // Удаление элементов накладной table = (IsPurchase) ? "PURCHASE_INVOICE_UNIT" : "SALES_INVOICE_UNIT"; sql = String.Format(@"DELETE FROM `{0}` WHERE `{0}`.`ID_Invoice` = {1}", table, ID); using (MySqlCommand cmd = new MySqlCommand(sql, connection)) { cmd.ExecuteNonQuery(); } } connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка удаления накладной", ex.Message); } } else { Dialog.ErrorMessage(null, "Не указан ID накладной"); } return(result); }
/// <summary> /// Возвращает список товаров, включая аннулированные /// </summary> public static List <Product> GetAllProductList() { List <Product> Products = new List <Product>(); try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = "SELECT * FROM `PRODUCT`"; using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Product item = new Product(); item.ID = Conversion.ToInt(reader["ID"].ToString()); item.ProductCode = reader["PRODUCT_CODE"].ToString(); item.Nomination = reader["NOMINATION"].ToString(); item.CountInPackage = Conversion.ToDouble(reader["COUNT_IN_PACKAGE"].ToString()); item.Cost = Conversion.ToDouble(reader["COST"].ToString()); item.Coupon = Conversion.ToDouble(reader["COUPON"].ToString()); item.CostPurchase = Conversion.ToDouble(reader["COST_PURCHASE"].ToString()); item.CouponPurchase = Conversion.ToDouble(reader["COUPON_PURCHASE"].ToString()); item.Count = Conversion.ToDouble(reader["COUNT"].ToString()); item.LockedCount = Conversion.ToDouble(reader["LOCKED_COUNT"].ToString()); item.ID_Unit = Conversion.ToInt(reader["ID_UNIT"].ToString()); item.IsAnnulated = Conversion.ToBool(reader["IS_ANNULATED"].ToString()); Products.Add(item); } } } connection.Close(); } } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения списка товара", ex.Message); } return(Products.OrderBy(p => p.ProductCode).ToList()); }
/// <summary> /// Получить список контрагентов /// </summary> /// <param name="provider_value">значение поля IS_PROVIDER</param> /// <returns></returns> private static List <Counterparty> GetContragentList(int provider_value) { List <Counterparty> Counterparties = new List <Counterparty>(); try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = "SELECT * FROM `COUNTERPARTY` WHERE `COUNTERPARTY`.`IS_PROVIDER` = \'" + provider_value + "\' AND `COUNTERPARTY`.`IS_ANNULATED` = \'0\'"; using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Counterparty item = new Counterparty(); item.ID = Conversion.ToInt(reader["ID"].ToString()); item.Code = reader["CODE"].ToString(); item.Surname = reader["SURNAME"].ToString(); item.Name = reader["NAME"].ToString(); item.Patronymic = reader["PATRONYMIC"].ToString(); item.Nomination = reader["NOMINATION"].ToString(); item.Adress = Conversion.ToUncriptString(reader["ADRESS"].ToString()); item.Phone = Conversion.ToUncriptString(reader["PHONE"].ToString()); item.IsProvider = Conversion.ToBool(reader["IS_PROVIDER"].ToString()); Counterparties.Add(item); } } } connection.Close(); } } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения данных о контрагенте", ex.Message); } return(Counterparties); }
/// <summary> /// Получение настроек программы из БД /// </summary> private static void GetSettings() { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { string sql = @"SELECT * FROM `SETTINGS`"; connection.Open(); using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string nomination = string.Empty; string value = string.Empty; try { nomination = reader["NOMINATION"].ToString(); } catch { nomination = ""; } try { value = reader["VALUE"].ToString(); } catch { value = string.Empty; } if (nomination != "") { ProgramSettings.Settings.Add(nomination, value); } } } } connection.Close(); } }
/// <summary> /// Возвращает список элементов накладной /// </summary> /// <param name="id_invoice">ID накладной</param> /// <param name="isPurchase">TRUE если приходная накладная, FALSE если расходная</param> public static List <InvoiceItem> GetInvoiceItemList(int id_invoice, bool isPurchase) { List <InvoiceItem> Items = new List <InvoiceItem>(); try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string table = (isPurchase) ? "PURCHASE_INVOICE_UNIT" : "SALES_INVOICE_UNIT"; string sql = String.Format(@"SELECT * FROM `{0}` WHERE `{0}`.`ID_INVOICE` = {1}", table, id_invoice); using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { InvoiceItem item = new InvoiceItem(); item.ID_Invoice = id_invoice; item.ID = Conversion.ToInt(reader["ID"].ToString()); item.ID_Product = Conversion.ToInt(reader["ID_PRODUCT"].ToString()); item.Count = Conversion.ToDouble(reader["COUNT"].ToString()); item.ProductCost = Conversion.ToDouble(reader["PRDUCT_COST"].ToString()); item.ProductCoupon = Conversion.ToDouble(reader["PRODUCT_COUPON"].ToString()); item.Cost = Conversion.ToDouble(reader["COST"].ToString()); item.Coupon = Conversion.ToDouble(reader["COUPON"].ToString()); Items.Add(item); } } } connection.Close(); } } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения списка элементов накладной", ex.Message); } return(Items); }
/// <summary> /// Получить защищаемые данные по ID /// </summary> /// <returns>возвращает TRUE, если данные успешно получены</returns> public bool GetProtectedDataByID() { bool result = false; if (_id != 0) { try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = "SELECT `ADRESS`, `PHONE` FROM `ACCOUNT` WHERE `ACCOUNT`.`ID` = " + ID.ToString(); using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { PersonalData.Adress = Conversion.ToUncriptString(reader["ADRESS"].ToString()); PersonalData.Phone = Conversion.ToUncriptString(reader["PHONE"].ToString()); } } } connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения защищаемых данных о пользователе", ex.Message); } } else { Dialog.ErrorMessage(null, "Не указан ID пользователя"); } return(result); }
/// <summary> /// Получить список не аннулированных аккаунтов с незащищенными персональными данными /// </summary> public static List <Account> GetNotAnnuledAccountsPersonalData() { List <Account> result = new List <Account>(); try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = @"SELECT `ID`, `LOGIN`, `CODE`, `SURNAME`, `NAME`, `PATRONYMIC` FROM `ACCOUNT` WHERE `ACCOUNT`.`IS_ANNULATED` = '0'"; using (MySqlCommand command = new MySqlCommand(sql, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Account account = new Account(); account.ID = Conversion.ToInt(reader["ID"].ToString()); account.Login = reader["LOGIN"].ToString(); account.PersonalData.Code = reader["CODE"].ToString(); account.PersonalData.Surname = reader["SURNAME"].ToString(); account.PersonalData.Name = reader["NAME"].ToString(); account.PersonalData.Patronymic = reader["PATRONYMIC"].ToString(); result.Add(account); } } } connection.Close(); } } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка получения данных о пользователе", ex.Message); } return(result); }
/// <summary> /// Аннулировать контрагента /// </summary> /// <returns>возвращает TRUE, если контрагент успешно аннулирован</returns> public bool AnnuateCounterparty() { bool result = false; if (_id != 0) { try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = @"UPDATE `COUNTERPARTY` SET `IS_ANNULATED` = '1' WHERE `COUNTERPARTY`.`ID` = " + ID.ToString(); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); } IsAnnulated = true; string name = (Nomination != "") ? Nomination : Surname + " " + Name; Journal.CreateJournal("аннулировал", "контрагента " + name, connection); connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка аннулирования контрагента", ex.Message); } } else { Dialog.ErrorMessage(null, "Не указан ID контрагента"); } return(result); }
/// <summary> /// Реадктировать аккаунт /// </summary> /// <returns>возвращает TRUE, если аккаунт успешно отредактирован</returns> public bool EditAccount() { bool result = false; if (_id != 0) { try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = String.Format(@"UPDATE `ACCOUNT` SET `LOGIN` = '{0}', `SURNAME` = '{1}', `NAME` = '{2}', `PATRONYMIC` = '{3}', `ADRESS` = '{4}', `PHONE` = '{5}', `SEX` = '{6}', `PURCHASE_INVOICE` = '{7}', `SALES_INVOICE` = '{8}', `REPORTS` = '{9}', `REPORT_PURCHASE_INVOICE` = '{10}', `REPORT_SALES_INVOICE` = '{11}', `REPORT_INCOME_EXPENSE` = '{12}', `COUNTERPARTY` = '{13}', `COUNTERPARTY_PROVIDER` = '{14}', `COUNTERPARTY_PROVIDER_ADD` = '{15}', `COUNTERPARTY_PROVIDER_EDIT` = '{16}', `COUNTERPARTY_PROVIDER_DELETE` = '{17}', `COUNTERPARTY_CUSTOMER` = '{18}', `COUNTERPARTY_CUSTOMER_ADD` = '{19}', `COUNTERPARTY_CUSTOMER_EDIT` = '{20}', `COUNTERPARTY_CUSTOMER_DELETE` = '{21}', `WAREHOUSE` = '{22}', `WAREHOUSE_ADD_PRODUCT` = '{23}', `WAREHOUSE_EDIT_PRODUCT` = '{24}', `WAREHOUSE_ANNULATE_PRODUCT` = '{25}', `WAREHOUSE_EDIT_COUNT` = '{26}', `ADMIN_PANEL` = '{27}', `ADMIN_PANEL_ADD_ACCOUNT` = '{28}', `ADMIN_PANEL_EDIT_ACCOUNT` = '{29}', `ADMIN_PANEL_DELETE_ACCOUNT` = '{30}', `ADMIN_PANEL_SET_NUMERATOR` = '{31}', `ADMIN_PANEL_JOURNAL` = '{32}', `REPORT_UN_ISSUE_PURCHASE_INVOICE` = '{33}', `REPORT_UN_ISSUE_SALES_INVOICE` = '{34}', `CODE` = '{35}' WHERE `ACCOUNT`.`ID` = {36}", Login, PersonalData.Surname, PersonalData.Name, PersonalData.Patronymic, Criptex.Cript(PersonalData.Adress), Criptex.Cript(PersonalData.Phone), Conversion.ToString(PersonalData.Sex), Conversion.ToString(InvoiceData.PurchaseInvoice), Conversion.ToString(InvoiceData.SalesInvoice), Conversion.ToString(ReportsData.Reports), Conversion.ToString(ReportsData.ReportPurchaseInvoice), Conversion.ToString(ReportsData.ReportSalesInvoice), Conversion.ToString(ReportsData.ReportIncomeExpense), Conversion.ToString(CounterpartyData.Counterparty), Conversion.ToString(CounterpartyData.CounterpartyProvider), Conversion.ToString(CounterpartyData.CounterpartyProviderAdd), Conversion.ToString(CounterpartyData.CounterpartyProviderEdit), Conversion.ToString(CounterpartyData.CounterpartyProviderDelete), Conversion.ToString(CounterpartyData.CounterpartyCustomer), Conversion.ToString(CounterpartyData.CounterpartyCustomerAdd), Conversion.ToString(CounterpartyData.CounterpartyCustomerEdit), Conversion.ToString(CounterpartyData.CounterpartyCustomerDelete), Conversion.ToString(WarehouseData.Warehouse), Conversion.ToString(WarehouseData.WarehouseAddProduct), Conversion.ToString(WarehouseData.WarehouseEditProduct), Conversion.ToString(WarehouseData.WarehouseAnnulateProduct), Conversion.ToString(WarehouseData.WarehouseEditCount), Conversion.ToString(AdminPanelData.AdminPanel), Conversion.ToString(AdminPanelData.AdminPanelAddAccount), Conversion.ToString(AdminPanelData.AdminPanelEditAccount), Conversion.ToString(AdminPanelData.AdminPanelDeleteAccount), Conversion.ToString(AdminPanelData.AdminPanelSetNumerator), Conversion.ToString(AdminPanelData.AdminPanelJournal), Conversion.ToString(ReportsData.ReportUnIssuePurchaseInvoice), Conversion.ToString(ReportsData.ReportUnIssueSalesInvoice), PersonalData.Code, ID); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); } if (ProgramSettings.CurrentUser.ID != _id) { Journal.CreateJournal("изменил", "данные пользователя " + _login, connection); } connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка редактирования данных пользователя", ex.Message); } } else { Dialog.ErrorMessage(null, "Не указан ID пользователя"); } return(result); }
/// <summary> /// Отменить аннулирование товара /// </summary> /// <returns>возвращает TRUE если для товара аннулирование отменено успешно</returns> public bool UnAnnulateProduct() { bool result = false; if (_id != 0) { try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); IsAnnulated = false; string sql = String.Format(@"UPDATE `PRODUCT` SET `IS_ANNULATED` = '0' WHERE `PRODUCT`.`ID` = {0}", _id); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); } Journal.CreateJournal("отменил", "аннулирование товара " + ProductCode, connection); connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка отмены аннулирования товара", ex.Message); } } else { Dialog.ErrorMessage(null, "Не указан ID товара"); } return(result); }
/// <summary> /// Редактировать данные контрагента /// </summary> /// <returns>возвращает TRUE, если контрагент успешно отредактирован</returns> public bool EditCounterparty() { bool result = false; if (_id != 0) { try { using (MySqlConnection connection = new MySqlConnection(Criptex.UnCript(ProgramSettings.ConnectionString))) { connection.Open(); string sql = String.Format(@"UPDATE `COUNTERPARTY` SET `SURNAME` = '{0}', `NAME` = '{1}', `PATRONYMIC` = '{2}', `ADRESS` = '{3}', `PHONE` = '{4}', `NOMINATION` = '{5}', `IS_PROVIDER` = '{6}', `CODE` = '{7}' WHERE `COUNTERPARTY`.`ID` = {8}", Surname, Name, Patronymic, Conversion.ToCriptString(Adress), Conversion.ToCriptString(Phone), Nomination, Conversion.ToString(IsProvider), Code, ID); using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.ExecuteNonQuery(); } string name = (Nomination != "") ? Nomination : Surname + " " + Name; Journal.CreateJournal("изменил", "данные контрагента " + name, connection); connection.Close(); } result = true; } catch (Exception ex) { Dialog.ErrorMessage(null, "Ошибка редактирования контрагента", ex.Message); } } else { Dialog.ErrorMessage(null, "Не указан ID контрагента"); } return(result); }