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