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