private void refresh()
{
comboBoxWareHouseEditPrice.Items.Clear();
comboBoxAddProd.Items.Clear();
comboBoxViewWareHouseItem.Items.Clear();
comboBoxIdSupplier.Items.Clear();
comboBoxIdAffiliate.Items.Clear();
comboBoxSelectType.Items.Clear();
comboBoxIdAffiliateTable.Items.Clear();
comboBoxAffiliateAddProd.Items.Clear();
string com = "SELECT * FROM ProductSize";
MyConnection.comboBoxLoading(comboBoxWareHouseEditPrice, com, "Продукт с размером"); //Price
MyConnection.comboBoxLoading(comboBoxAddProd, com, "Продукт с размером"); //NumProd
MyConnection.comboBoxLoading(comboBoxViewWareHouseItem, com, "Продукт с размером"); //TableProd
com = "SELECT [NameCompany] FROM Supplier";
MyConnection.comboBoxLoading(comboBoxIdSupplier, com, "NameCompany"); //Supplier
com = "SELECT [NameAffiliate] FROM Affiliate";
MyConnection.comboBoxLoading(comboBoxIdAffiliate, com, "NameAffiliate"); //Affiliate
com = "SELECT [Type] FROM ProductType";
MyConnection.comboBoxLoading(comboBoxSelectType, com, "Type"); //TypeProd
com = "SELECT [NameAffiliate] FROM Affiliate";
MyConnection.comboBoxLoading(comboBoxIdAffiliateTable, com, "NameAffiliate");
com = "SELECT [NameAffiliate] FROM Affiliate";
MyConnection.comboBoxLoading(comboBoxAffiliateAddProd, com, "NameAffiliate"); //Affiliate
com = $"SELECT [Id_Position], Affiliate.[NameAffiliate], [ProductName], [Type], [NameCompany], [PriceOfUnit], [NumberOfProduct] FROM WarehouseInfo join Affiliate on WarehouseInfo.[Id_Affiliate] = Affiliate.[Id_AffiliateKA]";
MyConnection.loadingDataGridView(dataGridViewxViewWareHouseItem, com, "WarehouseInfo");
}
private void buttonViewTable_Click(object sender, EventArgs e)
{
int idProd, idAffiliate;
string com;
if (comboBoxViewWareHouseItem.SelectedIndex == -1 && comboBoxIdAffiliateTable.SelectedIndex == -1)
{
MessageBox.Show("Не одно поле не выбранно!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (comboBoxViewWareHouseItem.SelectedIndex == -1)
{
idAffiliate = comboBoxIdAffiliateTable.SelectedIndex + 1;
com = $"SELECT [Id_Position],[ProductName],[Type],[NameCompany],[PriceOfUnit],[NumberOfProduct] FROM WarehouseInfo WHERE [Id_Affiliate] = {idAffiliate}";
MyConnection.loadingDataGridView(dataGridViewxViewWareHouseItem, com, "WarehouseInfo");
}
else if (comboBoxIdAffiliateTable.SelectedIndex == -1)
{
idProd = comboBoxViewWareHouseItem.SelectedIndex + 1;
com = $"SELECT [Id_Position], Affiliate.[NameAffiliate], [ProductName],[Type],[NameCompany],[PriceOfUnit],[NumberOfProduct] FROM WarehouseInfo join Affiliate on WarehouseInfo.[Id_Affiliate] = Affiliate.[Id_AffiliateKA] WHERE [Id_ProductKA] = {idProd}";
MyConnection.loadingDataGridView(dataGridViewxViewWareHouseItem, com, "WarehouseInfo");
}
else
{
idProd = comboBoxViewWareHouseItem.SelectedIndex + 1;
idAffiliate = comboBoxIdAffiliateTable.SelectedIndex + 1;
com = $"SELECT [Id_Position],[ProductName],[Type],[NameCompany],[PriceOfUnit],[NumberOfProduct] FROM WarehouseInfo WHERE [Id_Affiliate] = {idAffiliate} AND [Id_ProductKA] = {idProd}";
MyConnection.loadingDataGridView(dataGridViewxViewWareHouseItem, com, "WarehouseInfo");
}
}
private void buttonResetComboBox_Click(object sender, EventArgs e)
{
comboBoxIdAffiliateTable.SelectedIndex = -1;
comboBoxViewWareHouseItem.SelectedIndex = -1;
string com = $"SELECT [Id_Position], Affiliate.[NameAffiliate], [ProductName], [Type], [NameCompany], [PriceOfUnit], [NumberOfProduct] FROM WarehouseInfo join Affiliate on WarehouseInfo.[Id_Affiliate] = Affiliate.[Id_AffiliateKA]";
MyConnection.loadingDataGridView(dataGridViewxViewWareHouseItem, com, "WarehouseInfo");
}
private void refresh()
{
com = "SELECT CONCAT(Buyer.[Surname], ' ', Buyer.[Name]) AS 'Ф.И.' FROM Buyer WHERE Buyer.[Id_Buyer] != 1";
MyConnection.comboBoxLoading(comboBoxOrdersUserChoose, com, "Ф.И.");
MyConnection.comboBoxLoading(comboBoxDeliveryChoose, com, "Ф.И.");
com = "SELECT COUNT(*) FROM Purchase";
MyConnection.textBoxLoading(textBoxOrders, com);
com = "SELECT * FROM PurchaseView";
MyConnection.loadingDataGridView(dataGridViewOrdersProducts, com, "PurchaseView");
com = "SELECT [Номер доставки], [Ф.И.], [Адрес доставки], [Дата доставки], [Статус доставки] FROM DeliveryToBuyerView";
MyConnection.loadingDataGridView(dataGridViewOrdersDelivery, com, "DeliveryToBuyerView");
}
private void buttonDeliveryToBuyer_Click(object sender, EventArgs e)
{
if (comboBoxDeliveryChoose.SelectedIndex == -1)
{
MessageBox.Show("Ничего не выбранно!", "Ошибка.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
idBuyer = comboBoxDeliveryChoose.SelectedIndex + 2;
com = $"SELECT [Номер доставки], [Ф.И.], [Адрес доставки], [Дата доставки], [Статус доставки] FROM DeliveryToBuyerView WHERE DeliveryToBuyerView.[Id_Buyer] = {idBuyer}";
MyConnection.loadingDataGridView(dataGridViewOrdersDelivery, com, "DeliveryToBuyerView");
comboBoxDeliveryChoose.SelectedIndex = -1;
}
}
private void buttonShowBuyer_Click(object sender, EventArgs e)
{
if (comboBoxOrdersUserChoose.SelectedIndex == -1)
{
MessageBox.Show("Ничего не выбранно!", "Ошибка.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
idBuyer = comboBoxOrdersUserChoose.SelectedIndex + 2;
com = $"SELECT * FROM PurchaseView WHERE PurchaseView.[Номер покупателя] = {idBuyer}";
MyConnection.loadingDataGridView(dataGridViewOrdersProducts, com, "PurchaseView");
comboBoxOrdersUserChoose.SelectedIndex = -1;
}
}
public void refrch()
{
com = "SELECT DISTINCT [Id_UserKA], [Login], [Password], [Surname], [Name] FROM Users join Buyer on Users.[Id_UserKA] = Buyer.[Id_User]";
MyConnection.loadingDataGridView(dataGridViewUsers, com, "Users");
}
private void buttonResetOrder_Click(object sender, EventArgs e)
{
com = "SELECT * FROM PurchaseView";
MyConnection.loadingDataGridView(dataGridViewOrdersProducts, com, "Buyer");
}
private void buttonResetDelivery_Click(object sender, EventArgs e)
{
com = "SELECT [Номер доставки], [Ф.И.], [Адрес доставки], [Дата доставки], [Статус доставки] FROM DeliveryToBuyerView";
MyConnection.loadingDataGridView(dataGridViewOrdersDelivery, com, "DeliveryToBuyerView");
}
private void buttonBuy_Click(object sender, EventArgs e)
{
try
{
if (radioButtonNAL.Checked == true)
{
if (radioButtonPickup.Checked == true)
{
com = $" DECLARE @DATE DATE SET @DATE = GETDATE() EXEC NewPurchaseNALNotDelivery {Class.IdAffiliate}, '{Class.numPos}', {Class.amount}, @DATE";
workCom(com);
com = " INSERT INTO CheckPurchase ([Id_Role], [Surname], [Name], [Products], [Amount], [DatePayment]) VALUES " +
"( 2, " +
$"(SELECT TOP 1 [Surname] FROM Buyer WHERE [Id_User] = {Class.idUser}), " +
$"(SELECT TOP 1 [Name] FROM Buyer WHERE [Id_User] = {Class.idUser}), " +
$"'{Class.nameProds}', " +
$"{Class.amount}, " +
$"(SELECT TOP 1 Purchase.[DateOfPurchase] FROM Purchase ORDER BY Purchase.[Id_PurchaseNumberKA] DESC))";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
int result = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
com = "UPDATE Purchase SET [Id_Check] = (SELECT TOP 1 [Id_CheckKA] FROM CheckPurchase ORDER BY [Id_CheckKA] DESC) WHERE Purchase.[Id_PurchaseNumberKA] = (SELECT TOP 1 Purchase.[Id_PurchaseNumberKA] FROM Purchase ORDER BY [Id_PurchaseNumberKA] DESC)";
workCom(com);
}
else
{
com = $" DECLARE @DATE DATE SET @DATE = GETDATE() EXEC NewPurchaseNALDelivery '{textBoxAddressDelovery.Text}', '{maskedTextBoxDateDelivery.Text}', {Class.IdAffiliate}, '{Class.numPos}', {Class.amount}, @DATE";
workCom(com);
com = " INSERT INTO CheckPurchase ([Id_Role], [Surname], [Name], [Products], [Amount], [DatePayment], [AddressDelivery], [DateDelivery]) VALUES " +
"( 2, " +
$"(SELECT TOP 1 [Surname] FROM Buyer WHERE [Id_User] = {Class.idUser}), " +
$"(SELECT TOP 1 [Name] FROM Buyer WHERE [Id_User] = {Class.idUser}), " +
$"'{Class.nameProds}', " +
$"{Class.amount}, " +
$"(SELECT TOP 1 Purchase.[DateOfPurchase] FROM Purchase ORDER BY Purchase.[Id_PurchaseNumberKA] DESC), " +
$" '{textBoxAddressDelovery.Text}', " +
$"@QWER )";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
sqlCommand.Parameters.Add("@QWER", SqlDbType.VarChar).Value = maskedTextBoxDateDelivery.Text;
int result = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
com = "UPDATE Purchase SET [Id_Check] = (SELECT TOP 1 [Id_CheckKA] FROM CheckPurchase ORDER BY [Id_CheckKA] DESC) WHERE Purchase.[Id_PurchaseNumberKA] = (SELECT TOP 1 Purchase.[Id_PurchaseNumberKA] FROM Purchase ORDER BY [Id_PurchaseNumberKA] DESC)";
workCom(com);
}
}
else
{
if (radioButtonPickup.Checked == true)
{
com = $" DECLARE @DATE DATE SET @DATE = GETDATE() EXEC NewPurchaseCARDNotDelivery {Class.IdAffiliate}, '{Class.numPos}', {Class.amount}, {textBoxCARD.Text}, @DATE";
workCom(com);
com = " INSERT INTO CheckPurchase ([Id_Role], [Surname], [Name], [Products], [Amount], [CardPayment], [DatePayment]) VALUES " +
"( 2, " +
$"(SELECT TOP 1 [Surname] FROM Buyer WHERE [Id_User] = {Class.idUser}), " +
$"(SELECT TOP 1 [Name] FROM Buyer WHERE [Id_User] = {Class.idUser}), " +
$"'{Class.nameProds}', " +
$"{Class.amount}, " +
$"(SELECT TOP 1 Purchase.[CardPayment] FROM Purchase ORDER BY Purchase.[Id_PurchaseNumberKA] DESC), " +
$"(SELECT TOP 1 Purchase.[DateOfPurchase] FROM Purchase ORDER BY Purchase.[Id_PurchaseNumberKA] DESC))";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
int result = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
com = "UPDATE Purchase SET [Id_Check] = (SELECT TOP 1 [Id_CheckKA] FROM CheckPurchase ORDER BY [Id_CheckKA] DESC) WHERE Purchase.[Id_PurchaseNumberKA] = (SELECT TOP 1 Purchase.[Id_PurchaseNumberKA] FROM Purchase ORDER BY [Id_PurchaseNumberKA] DESC)";
workCom(com);
}
else
{
com = $" DECLARE @DATE DATE SET @DATE = GETDATE() EXEC NewPurchaseCARDDelivery '{textBoxAddressDelovery.Text}', '{maskedTextBoxDateDelivery.Text}', {Class.IdAffiliate}, '{Class.numPos}', {Class.amount}, '{textBoxCARD.Text}', @DATE";
workCom(com);
com = "INSERT INTO CheckPurchase VALUES " +
$"(2, (SELECT TOP 1 [Surname] FROM Buyer WHERE [Id_User] = {Class.idUser}), " +
$"(SELECT TOP 1 [Name] FROM Buyer WHERE [Id_User] = {Class.idUser}), " +
$"'{Class.nameProds}', " +
$"{Class.amount}, " +
$"(SELECT TOP 1 Purchase.[CardPayment] FROM Purchase ORDER BY Purchase.[Id_PurchaseNumberKA] DESC), " +
$"(SELECT TOP 1 Purchase.[DateOfPurchase] FROM Purchase ORDER BY Purchase.[Id_PurchaseNumberKA] DESC), " +
$"'{textBoxAddressDelovery.Text}', " +
$"@QWER)";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
sqlCommand.Parameters.Add("@QWER", SqlDbType.VarChar).Value = maskedTextBoxDateDelivery.Text;
int result = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
com = "UPDATE Purchase SET [Id_Check] = (SELECT TOP 1 [Id_CheckKA] FROM CheckPurchase ORDER BY [Id_CheckKA] DESC) WHERE Purchase.[Id_PurchaseNumberKA] = (SELECT TOP 1 Purchase.[Id_PurchaseNumberKA] FROM Purchase ORDER BY [Id_PurchaseNumberKA] DESC)";
workCom(com);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
{
com = $"select Buyer.[Id_PurchaseNumber] from Buyer where Buyer.[Id_Buyer] = {Class.idUser}";
string res;
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
res = Convert.ToString(sqlCommand.ExecuteScalar());
sqlConnection.Close();
}
if (res == "")
{
com = $"UPDATE Buyer SET [Id_PurchaseNumber] = (SELECT TOP 1 Purchase.[Id_PurchaseNumberKA] FROM Purchase ORDER BY Purchase.[Id_PurchaseNumberKA] DESC) WHERE [Id_User] = {Class.idUser}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
res = Convert.ToString(sqlCommand.ExecuteNonQuery());
sqlConnection.Close();
}
}
else
{
com = "INSERT INTO Buyer VALUES " +
$"( { Class.idUser}, ( " +
"SELECT TOP 1[Surname] FROM Buyer " +
$"WHERE[Id_User] = { Class.idUser} " +
"), ( " +
"SELECT TOP 1[Name] FROM Buyer " +
$"WHERE[Id_User] = { Class.idUser} " +
"), ( " +
"SELECT TOP 1[Email] FROM Buyer " +
$"WHERE[Id_User] = { Class.idUser} " +
"), ( " +
"SELECT TOP 1 Purchase.[Id_PurchaseNumberKA] FROM Purchase " +
"ORDER BY Purchase.[Id_PurchaseNumberKA] DESC ))";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
res = Convert.ToString(sqlCommand.ExecuteNonQuery());
sqlConnection.Close();
}
}
MessageBox.Show("Покупка совершена!", "Уведомление.", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (radioButtonNAL.Checked == true)
{
if (radioButtonPickup.Checked == true)
{
com = "select top 1 RoleUsers.[Role], " +
"CheckPurchase.[Surname], " +
"CheckPurchase.[Name], " +
"CheckPurchase.[Products], " +
"CheckPurchase.[Amount], " +
"CheckPurchase.[DatePayment] " +
"from CheckPurchase " +
"join RoleUsers on CheckPurchase.[Id_Role] = RoleUsers.[Id_UsersRole] " +
"order by CheckPurchase.[Id_CheckKA] desc";
MyConnection.loadingDataGridView(dataGridView1, com, "CheckPurchase");
}
else
{
com = "select top 1 RoleUsers.[Role], " +
"CheckPurchase.[Surname], " +
"CheckPurchase.[Name], " +
"CheckPurchase.[Products], " +
"CheckPurchase.[Amount], " +
"CheckPurchase.[AddressDelivery], " +
"CheckPurchase.[DateDelivery] " +
"from CheckPurchase " +
"join RoleUsers on CheckPurchase.[Id_Role] = RoleUsers.[Id_UsersRole] " +
"order by CheckPurchase.[Id_CheckKA] desc";
MyConnection.loadingDataGridView(dataGridView1, com, "CheckPurchase");
}
}
else
{
if (radioButtonPickup.Checked == true)
{
com = "select top 1 RoleUsers.[Role], " +
"CheckPurchase.[Surname], " +
"CheckPurchase.[Name], " +
"CheckPurchase.[Products], " +
"CheckPurchase.[Amount], " +
"CheckPurchase.[CardPayment], " +
"CheckPurchase.[DatePayment] " +
"from CheckPurchase " +
"join RoleUsers on CheckPurchase.[Id_Role] = RoleUsers.[Id_UsersRole] " +
"order by CheckPurchase.[Id_CheckKA] desc";
MyConnection.loadingDataGridView(dataGridView1, com, "CheckPurchase");
}
else
{
com = "select top 1 RoleUsers.[Role], " +
"CheckPurchase.[Surname], " +
"CheckPurchase.[Name], " +
"CheckPurchase.[Products], " +
"CheckPurchase.[Amount], " +
"CheckPurchase.[CardPayment], " +
"CheckPurchase.[DatePayment], " +
"CheckPurchase.[AddressDelivery], " +
"CheckPurchase.[DateDelivery] " +
"from CheckPurchase " +
"join RoleUsers on CheckPurchase.[Id_Role] = RoleUsers.[Id_UsersRole] " +
"order by CheckPurchase.[Id_CheckKA] desc";
MyConnection.loadingDataGridView(dataGridView1, com, "CheckPurchase");
}
}
Class.flag = true;
buttonCheck.Enabled = true;
buttonBuy.Enabled = false;
}
}
}