ПЗ (1218909), страница 13
Текст из файла (страница 13)
catch { }
}
private void FullDemandsFullList()
//Заполнить список заявок на согласование
{
DemandsFulllistView.Items.Clear();//Очистим список заявок
string sqlText = @"SELECT ID_demand,DocDate,WarehouseName FROM demands
JOIN warehouses ON warehouses.ID_warehouse=demands.ID_warehouse WHERE demands.DemandState=0";//Текст SQL запроса
MySqlConnection MySQLConnection = new MySqlConnection(ConnectionString);//Создадим соединение
try
{
MySQLConnection.Open();//Откроем соединение с БД
MySqlCommand MySQLCommand = new MySqlCommand(sqlText, MySQLConnection);
MySqlDataReader Reader = MySQLCommand.ExecuteReader();
while (Reader.Read())//Построчно перебираем результат запроса
{
ListViewItem Demand_Item = new ListViewItem(Convert.ToString(Reader.GetInt32(0)));
Demand_Item.SubItems.Add(Reader.GetDateTime(1).ToShortDateString());
Demand_Item.SubItems.Add(Reader.GetString(2));
DemandsFulllistView.Items.Add(Demand_Item);
}
}
catch { }
}
private void FullLessThenMinimunList()
//Заполнить список товаров, у которых на остатке меньше минимума
{
LessThenMinimumlistView.Items.Clear();
string sqlText = @"SELECT warehouses.WarehouseName
, goods.GoodName
, minimals.GoodAmount
, goods_amount.Amount
FROM
minimals
JOIN warehouses
ON warehouses.ID_warehouse = minimals.ID_warehouse
JOIN goods
ON goods.ID_good = minimals.ID_good
LEFT JOIN goods_amount
ON goods_amount.ID_good = minimals.ID_good AND goods_amount.ID_warehouse = minimals.ID_warehouse
WHERE
minimals.GoodAmount > goods_amount.Amount OR goods_amount.Amount IS NULL";//Текст SQL запроса
MySqlConnection MySQLConnection = new MySqlConnection(ConnectionString);//Создадим соединение
try
{
MySQLConnection.Open();//Откроем соединение с БД
MySqlCommand MySQLCommand = new MySqlCommand(sqlText, MySQLConnection);
MySqlDataReader Reader = MySQLCommand.ExecuteReader();
while (Reader.Read())//Построчно перебираем результат запроса
{
ListViewItem LessThenMinimum_Item = new ListViewItem(Reader.GetString(1));
LessThenMinimum_Item.SubItems.Add(Reader.GetString(0));
LessThenMinimum_Item.SubItems.Add(Convert.ToString(Reader.GetInt32(2)));
try
{
LessThenMinimum_Item.SubItems.Add(Convert.ToString(Reader.GetInt32(3)));
}
catch
{
LessThenMinimum_Item.SubItems.Add("0");
}
LessThenMinimumlistView.Items.Add(LessThenMinimum_Item);
}
}
catch { }
}
private void FullDemandData(string ID_Demand)
//Заполнить данные заявки
{
DemandsToDecideDatalistView.Items.Clear();
string sqlText = @"SELECT GoodName,GoodAmount FROM demands_data
JOIN goods ON goods.ID_good=demands_data.ID_good WHERE demands_data.ID_demand=" + ID_Demand;//Текст SQL запроса
MySqlConnection MySQLConnection = new MySqlConnection(ConnectionString);//Создадим соединение
try
{
MySQLConnection.Open();//Откроем соединение с БД
MySqlCommand MySQLCommand = new MySqlCommand(sqlText, MySQLConnection);
MySqlDataReader Reader = MySQLCommand.ExecuteReader();
while (Reader.Read())//Построчно перебираем результат запроса
{
ListViewItem DemandData_Item = new ListViewItem(Reader.GetString(0));
DemandData_Item.SubItems.Add(Convert.ToString(Reader.GetInt32(1)));
DemandsToDecideDatalistView.Items.Add(DemandData_Item);
}
}
catch { }
}
private void UpdateSales()
//Обновлить список "Продажи"
{
ID_sales_doc_listBox.Items.Clear();
Sales_doc_listView.Items.Clear();
string sqlText = @"SELECT ID_sale
, DocNumber
, DocDate
, WarehouseName
, ClientName
, ID_price_list
FROM
sales
LEFT JOIN warehouses
ON warehouses.ID_warehouse = sales.ID_warehouse
LEFT JOIN clients
ON clients.ID_client = sales.ID_client";//Текст SQL запроса
MySqlConnection MySQLConnection = new MySqlConnection(ConnectionString);//Создадим соединение
try
{
MySQLConnection.Open();//Откроем соединение с БД
MySqlCommand MySQLCommand = new MySqlCommand(sqlText, MySQLConnection);
MySqlDataReader Reader = MySQLCommand.ExecuteReader();
while (Reader.Read())//Построчно перебираем результат запроса
{
ID_sales_doc_listBox.Items.Add(Convert.ToString(Reader.GetInt32(0)));
ListViewItem Sale_doc_Item = new ListViewItem(Reader.GetString(1));
Sale_doc_Item.SubItems.Add(Reader.GetDateTime(2).ToShortDateString());
Sale_doc_Item.SubItems.Add(Reader.GetString(3));
Sale_doc_Item.SubItems.Add(Reader.GetString(4));
if (Reader.GetInt32(5) == 0)
{
Sale_doc_Item.SubItems.Add("Оптовый");
}
else
{
Sale_doc_Item.SubItems.Add("Розничный");
}
Sales_doc_listView.Items.Add(Sale_doc_Item);
}
}
catch { }
}
private void FullMinimals()
//Заполнить таблицу минимальных остатков
{
ID_MinimalslistBox.Items.Clear();
Minimals_listView.Items.Clear();
string sqlText = @"SELECT ID,WarehouseName,GoodName,GoodAmount FROM minimals
JOIN warehouses ON warehouses.ID_warehouse=minimals.ID_warehouse
JOIN goods ON goods.ID_good=minimals.ID_good";//Текст SQL запроса
MySqlConnection MySQLConnection = new MySqlConnection(ConnectionString);//Создадим соединение
try
{
MySQLConnection.Open();//Откроем соединение с БД
MySqlCommand MySQLCommand = new MySqlCommand(sqlText, MySQLConnection);
MySqlDataReader Reader = MySQLCommand.ExecuteReader();
while (Reader.Read())//Построчно перебираем результат запроса
{
ID_MinimalslistBox.Items.Add(Convert.ToString(Reader.GetInt32(0)));
ListViewItem Minimal_Item = new ListViewItem(Reader.GetString(2));
Minimal_Item.SubItems.Add(Reader.GetString(1));
Minimal_Item.SubItems.Add(Convert.ToString(Reader.GetInt32(3)));
Minimals_listView.Items.Add(Minimal_Item);
}
}
catch { }
}
private void Acceptbutton_Click(object sender, EventArgs e)
//Нажатие кнопки "Согласовать"
{
//Изменим запись в БД
string sqlText = "UPDATE demands SET DemandState=1 WHERE ID_demand=" + DemandsToDecidelistView.SelectedItems[0].Text;//Текст SQL запроса
MySqlConnection MySQLConnection = new MySqlConnection(ConnectionString);//Создадим соединение
try
{
MySQLConnection.Open();//Откроем соединение с БД
MySqlCommand MySQLCommand = new MySqlCommand(sqlText, MySQLConnection);
MySQLCommand.ExecuteNonQuery();
}
catch { }
FullDemandsList();
}
private void Declinebutton_Click(object sender, EventArgs e)
//Нажатие кнопки "Отклонить"
{
//Изменим запись в БД
string sqlText = "UPDATE demands SET DemandState=2 WHERE ID_demand=" + DemandsToDecidelistView.SelectedItems[0].Text;//Текст SQL запроса
MySqlConnection MySQLConnection = new MySqlConnection(ConnectionString);//Создадим соединение
try
{
MySQLConnection.Open();//Откроем соединение с БД
MySqlCommand MySQLCommand = new MySqlCommand(sqlText, MySQLConnection);
MySQLCommand.ExecuteNonQuery();
}
catch { }
FullDemandsList();
}
private void ReportSalesbutton_Click(object sender, EventArgs e)
//Нажатие кнопки "Журнал продаж"
{
//Формируем Exel файл
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ObjWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
//Создаем книгу из шаблона. Шаблон лежит в той же папке, что и текущее приложение.
ObjWorkBook = ObjExcel.Workbooks.Open(Path.Combine(System.Windows.Forms.Application.StartupPath, "sales.xltm"));
//Лист.
ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
int ExelRowNumber = 0;
string sqlText = @"SELECT goods.GoodName
, warehouses.WarehouseName
, goods_amount.Amount
FROM
goods_amount
JOIN goods
ON goods.ID_good = goods_amount.ID_good
JOIN warehouses
ON warehouses.ID_warehouse = goods_amount.ID_warehouse";//Текст SQL запроса
MySqlConnection MySQLConnection = new MySqlConnection(ConnectionString);//Создадим соединение
try
{
MySQLConnection.Open();//Откроем соединение с БД
MySqlCommand MySQLCommand = new MySqlCommand(sqlText, MySQLConnection);
MySqlDataReader Reader = MySQLCommand.ExecuteReader();
while (Reader.Read())//Построчно перебираем результат запроса
{
Microsoft.Office.Interop.Excel.Range cel = (Microsoft.Office.Interop.Excel.Range)ObjWorkSheet.Cells[5 + ExelRowNumber, 1];
Microsoft.Office.Interop.Excel.Range row = cel.EntireRow;
row.Select();
Microsoft.Office.Interop.Excel.Range cel1 = (Microsoft.Office.Interop.Excel.Range)ObjWorkSheet.Cells[4 + ExelRowNumber, 1];
Microsoft.Office.Interop.Excel.Range row1 = cel.EntireRow;
row1.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
Microsoft.Office.Interop.Excel.Range CurrentCell = (Microsoft.Office.Interop.Excel.Range)ObjWorkSheet.Cells[5 + ExelRowNumber, 1];
CurrentCell.Value2 = Reader.GetString(0);
CurrentCell = (Microsoft.Office.Interop.Excel.Range)ObjWorkSheet.Cells[5 + ExelRowNumber, 2];
CurrentCell.Value2 = Reader.GetString(1);
CurrentCell = (Microsoft.Office.Interop.Excel.Range)ObjWorkSheet.Cells[5 + ExelRowNumber, 3];
CurrentCell.Value2 = Convert.ToString(Reader.GetInt32(2));
ExelRowNumber++;
}
}
catch { }
//Делаем Exel файл видимым пользователю
ObjExcel.Visible = true;
ObjExcel.UserControl = true;
}
private void MinimalsAddbutton_Click(object sender, EventArgs e)
//Нажатие "Добавить" (Мин. остатки)
{
MinimalsGoodcomboBox.Visible = true;
MinimalsGoodlabel.Visible = true;
MinimalsRestlabel.Visible = true;
MinimalsResttextBox.Visible = true;
MinimalsWarehousecomboBox.Visible = true;
MinimalsWarehouselabel.Visible = true;
MinimalsSavebutton.Visible = true;
MinimalsCancelbutton.Visible = true;
MinimalsUpdateFlag = false;
}
private void button22_Click(object sender, EventArgs e)
{
MinimalsResttextBox.Text = Minimals_listView.Items[Minimals_listView.SelectedIndices[0]].SubItems[2].Text;
MinimalsGoodcomboBox.SelectedIndex = -1;
MinimalsWarehousecomboBox.SelectedIndex = -1;
MinimalsGoodcomboBox.Visible = true;
MinimalsGoodlabel.Visible = true;
MinimalsRestlabel.Visible = true;
MinimalsResttextBox.Visible = true;
MinimalsWarehousecomboBox.Visible = true;
MinimalsWarehouselabel.Visible = true;
MinimalsSavebutton.Visible = true;
MinimalsCancelbutton.Visible = true;
MinimalsUpdateFlag = true;
}
private void MinimalsCancelbutton_Click(object sender, EventArgs e)
{
MinimalsGoodcomboBox.Visible = false;
MinimalsGoodlabel.Visible = false;
MinimalsRestlabel.Visible = false;
MinimalsResttextBox.Visible = false;
MinimalsWarehousecomboBox.Visible = false;
MinimalsWarehouselabel.Visible = false;
MinimalsSavebutton.Visible = false;
MinimalsCancelbutton.Visible = false;
}
private void DictionaryDeletebutton_Click(object sender, EventArgs e)
{
}
private void MinimalsSavebutton_Click(object sender, EventArgs e)
{
if(MinimalsUpdateFlag==false)
{
//Добавим запись в БД
string sqlText = @"INSERT INTO minimals(ID_good,ID_warehouse,GoodAmount) VALUES(" + ID_goods_listBox.Items[MinimalsGoodcomboBox.SelectedIndex] + "," + ID_warehouse_listBox.Items[MinimalsWarehousecomboBox.SelectedIndex] + ","+MinimalsResttextBox.Text+")";//Текст SQL запроса
MySqlConnection MySQLConnection = new MySqlConnection(ConnectionString);//Создадим соединение
try
{
MySQLConnection.Open();//Откроем соединение с БД
MySqlCommand MySQLCommand = new MySqlCommand(sqlText, MySQLConnection);
MySQLCommand.ExecuteNonQuery();
}
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
