private void buttonSupplierOrder_Click(object sender, EventArgs e)
{
double priceProd = Double.Parse(textBoxPriceProd.Text);
string dateDelivery = maskedTextBoxDateDelivery.Text;
int numProd = Int32.Parse(textBoxNumProd.Text);
string desProd = richTextBoxDescriprion.Text;
string cmd = $"DECLARE @QWE varbinary(MAX)" +
$"SET @QWE = (SELECT * FROM OpenRowSet (BULK '{path}', SINGLE_BLOB) AS Файл)" +
$"EXEC NewProduct '{textBoxNameProd.Text}', '{textBoxSizeProd.Text}', {comboBoxSelectType.SelectedIndex + 1}, {priceProd}, @QWE, @DESPROD;" +
$"EXEC NewDeliveryNewProduct @QWER, {comboBoxIdSupplier.SelectedIndex + 1}, {comboBoxIdAffiliate.SelectedIndex + 1}, {numProd}" +
$"INSERT INTO TableProdImg VALUES ( {comboBoxSelectType.SelectedIndex + 1}, '{textBoxNameProd.Text}', @QWE)";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(cmd, sqlConnection);
sqlCommand.Parameters.Add("@DESPROD", SqlDbType.VarChar).Value = desProd;
sqlCommand.Parameters.Add("@QWER", SqlDbType.VarChar).Value = dateDelivery;
int result = sqlCommand.ExecuteNonQuery();
if (!result.Equals(0))
{
MessageBox.Show("Товар внесен.");
refresh();
}
sqlConnection.Close();
}
textBoxNameProd.Clear();
textBoxSizeProd.Clear();
textBoxPriceProd.Clear();
maskedTextBoxDateDelivery.Clear();
textBoxNumProd.Clear();
richTextBoxDescriprion.Clear();
}
private static void getImageBinaryFromDb(ComboBox comboBox1, string comboBox2)
{
List <byte[]> iScreen = new List <byte[]>(); // сделав запрос к БД мы получим множество строк в ответе, поэтому мы их сможем загнать в массив/List
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = $"SELECT [img] FROM TableProdImg WHERE [Id_Type] = {comboBox1.SelectedIndex + 1} AND [ProductName] = '{comboBox2}'"; // наша запись в БД под id=1, поэтому в запросе "WHERE [id] = 1"
SqlDataReader sqlReader = sqlCommand.ExecuteReader();
byte[] iTrimByte = null;
//string cmd = $"UPDATE Product SET [Img] = {imgByte} WHERE [Id_ProductKA] = {idProd}";
while (sqlReader.Read()) // считываем и вносим в лист результаты
{
iTrimByte = (byte[])sqlReader["img"]; // читаем строки с изображениями
iScreen.Add(iTrimByte);
}
sqlConnection.Close();
}
// конвертируем бинарные данные в изображение
byte[] imageData = iScreen[0]; // возвращает массив байт из БД. Так как у нас SQL вернёт одну запись и в ней хранится нужное нам изображение, то из листа берём единственное значение с индексом '0'
MemoryStream ms = new MemoryStream(imageData);
newImage = Image.FromStream(ms);
}
private void User_Load(object sender, EventArgs e)
{
com = $"SELECT TOP 1 CONCAT([Surname], ' ', [Name]) FROM Buyer WHERE [Id_User] = {Class.idUser}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
labelName.Text = Convert.ToString(sqlCommand.ExecuteScalar());
sqlConnection.Close();
}
com = $"select count(*) from Buyer where [Id_User] = {Class.idUser} AND [Id_PurchaseNumber] IS NOT NULL";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
textBoxNumOrder.Text = Convert.ToString(sqlCommand.ExecuteScalar());
sqlConnection.Close();
}
com = "SELECT [Type] FROM ProductType";
MyConnection.comboBoxLoading(comboBoxChooseType, com, "Type");
com = "select [ProductName] from TableProdImg";
MyConnection.comboBoxLoading(comboBoxChooseProd, com, "ProductName");
com = "SELECT [NameAffiliate] FROM Affiliate";
MyConnection.comboBoxLoading(comboBoxAffiliate, com, "NameAffiliate");
}
private void btnWareHouseEditPrice_Click(object sender, EventArgs e)
{
try
{
double price = Double.Parse(textBoxWareHouseEditPrice.Text);
int index = comboBoxWareHouseEditPrice.SelectedIndex + 1;
string com = $"EXEC UpdatePrice {price}, {index}";
int result;
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
result = sqlCommand.ExecuteNonQuery();
if (!result.Equals(0))
{
MessageBox.Show("Цена изменена!", "Уведомление", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
sqlConnection.Close();
}
comboBoxWareHouseEditPrice.SelectedIndex = -1;
textBoxWareHouseEditPrice.Text = null;
refresh();
}
catch
{
MessageBox.Show("Что-то не заполненно!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void comboBoxBuyer_SelectedIndexChanged(object sender, EventArgs e)
{
int id = comboBoxBuyer.SelectedIndex + 2;
com = $"SELECT TOP 1 [MessageB] FROM MessageBuyer join Buyer on MessageBuyer.[Id_Buyer] = Buyer.[Id_Buyer] WHERE Buyer.[Id_User] = {id} ORDER BY MessageBuyer.[Id_BuyerMessKA] DESC";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
try
{
richTextBoxMessBuyer.Text = Convert.ToString(sqlCommand.ExecuteScalar());
richTextBoxMessAdmin.Enabled = true;
buttonSendMess.Enabled = true;
com = $"SELECT TOP 1 MessageBuyer.[Id_BuyerMessKA] FROM MessageBuyer join Buyer on MessageBuyer.[Id_Buyer] = Buyer.[Id_Buyer] WHERE Buyer.[Id_User] = {comboBoxBuyer.SelectedIndex + 2} ORDER BY [MessageB] DESC";
sqlCommand.CommandText = com;
idMess = Convert.ToInt32(sqlCommand.ExecuteScalar());
}
catch
{
MessageBox.Show("Сообщений нет!");
}
sqlConnection.Close();
}
}
private void buttonConfirmationOrder_Click(object sender, EventArgs e)
{
if (textBoxIdBuyerOrder.Text != null && checkBoxOrdersStatus.Checked == true)
{
idBuyer = Int32.Parse(textBoxIdBuyerOrder.Text);
com = $"UPDATE PurchaseView SET PurchaseView.[Статус оплаты] = '{checkBoxOrdersStatus.Checked}' WHERE PurchaseView.[Номер покупателя] = {idBuyer}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
result = sqlCommand.ExecuteNonQuery();
if (!result.Equals(0))
{
MessageBox.Show("Оплачено!", "Уведомление.", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Покупатель не найден!", "Ошибка.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
sqlConnection.Close();
}
textBoxIdBuyerOrder.Text = null;
checkBoxOrdersStatus.Checked = false;
refresh();
}
else
{
MessageBox.Show("Что-то не заполнено!", "Ошибка.", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBoxIdBuyerOrder.Text = null;
checkBoxOrdersStatus.Checked = false;
}
}
private void RegUser(string cmd)
{
using (SqlConnection connection = new MyConnection().GetConnection())
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand(cmd, connection);
if (sqlCommand.ExecuteNonQuery() == 2)
{
MessageBox.Show("Аккаунт зарегистрирован!\nНажмите на кнопку 'Войти', чтобы продолжить.", "Уведомление!", MessageBoxButtons.OK, MessageBoxIcon.Information);
textBoxRegLogin.Clear();
textBoxRegPassword.Clear();
textBoxRegSurname.Clear();
textBoxRegName.Clear();
textBoxRegEmail.Clear();
textBoxRegConfirmPassword.Clear();
using (FormMain formMain = new FormMain())
{
formMain.btnFormSignup.Enabled = false;
formMain.btnFormRegistration.Enabled = true;
}
}
else
{
MessageBox.Show("Нет..");
}
connection.Close();
}
}
private void workCom(string cmd)
{
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
int res;
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
res = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
private void AccountLogin(string cmd, string login, string pass)
{
using (SqlConnection connection = new MyConnection().GetConnection())
{
connection.Open();
DataTable table = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter();
SqlCommand sqlCommand = new SqlCommand(cmd, connection);
sqlCommand.Parameters.Add("@uL", SqlDbType.VarChar).Value = login;
sqlCommand.Parameters.Add("@uP", SqlDbType.VarChar).Value = pass;
if (textBoxLoginUser.Text == "" || textBoxPasswordUser.Text == "")
{
MessageBox.Show("Заполните поля");
}
else
{
dataAdapter.SelectCommand = sqlCommand;
dataAdapter.Fill(table);
if (table.Rows.Count > 0)
{
this.Hide();
}
else
{
MessageBox.Show("Пользователь не найден или некорректно введены данные!");
}
}
connection.Close();
}
com = "SELECT Buyer.[Id_User] FROM Buyer join Users on Buyer.[Id_User] = Users.[Id_UserKA] WHERE [Login] = @uL AND [Password] = @uP";
using (SqlConnection connection = new MyConnection().GetConnection())
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand(com, connection);
sqlCommand.Parameters.Add("@uL", SqlDbType.VarChar).Value = login;
sqlCommand.Parameters.Add("@uP", SqlDbType.VarChar).Value = pass;
Class.idUser = Convert.ToInt32(sqlCommand.ExecuteScalar());
connection.Close();
}
com = "SELECT Buyer.[Id_Buyer] FROM Buyer join Users on Buyer.[Id_User] = Users.[Id_UserKA] WHERE [Login] = @uL AND [Password] = @uP";
using (SqlConnection connection = new MyConnection().GetConnection())
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand(com, connection);
sqlCommand.Parameters.Add("@uL", SqlDbType.VarChar).Value = login;
sqlCommand.Parameters.Add("@uP", SqlDbType.VarChar).Value = pass;
Class.idBuyer = Convert.ToInt32(sqlCommand.ExecuteScalar());
connection.Close();
}
}
private void buttonSendMess_Click(object sender, EventArgs e)
{
string str = richTextBoxMessBuyer.Text;
com = $"INSERT INTO MessageBuyer ([Id_Buyer],[MessageB]) VALUES ( {Class.idBuyer}, '{str}')";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
int qw;
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
qw = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
private int RoleUser(string cmd, string login, string pass)
{
using (SqlConnection connection = new MyConnection().GetConnection())
{
connection.Open();
int temp;
SqlCommand sqlCommand = new SqlCommand(cmd, connection);
sqlCommand.Parameters.Add("@uL", SqlDbType.VarChar).Value = login;
sqlCommand.Parameters.Add("@uP", SqlDbType.VarChar).Value = pass;
temp = Convert.ToInt32(sqlCommand.ExecuteScalar()); // Роль
connection.Close();
return(temp);
}
}
private void buttonDelete_Click(object sender, EventArgs e)
{
com = $"EXEC DeleteUser {Int32.Parse(textBoxIdUser.Text)}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
res = sqlCommand.ExecuteNonQuery();
MessageBox.Show("Пользователь удален!", "Уведомление.", MessageBoxButtons.OK, MessageBoxIcon.Information);
sqlConnection.Close();
}
textBoxIdUser.Clear();
refrch();
}
public static void comboBoxLoading(ComboBox comboBox, string cmd, string column)
{
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(cmd, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
comboBox.Items.Add(sqlDataReader[column]);
}
sqlConnection.Close();
}
}
public static void listBoxOrder(ListBox listBox, string cmd)
{
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(cmd, sqlConnection);
SqlDataReader rd;
rd = sqlCommand.ExecuteReader();
while (rd.Read())
{
listBox.Items.Add(rd[0].ToString());
}
sqlConnection.Close();
}
}
public static void richTextBoxLoading(RichTextBox richTextBox, string cmd)
{
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(cmd, sqlConnection);
SqlDataReader rd;
rd = sqlCommand.ExecuteReader();
while (rd.Read())
{
richTextBox.Text = rd[0].ToString();
}
sqlConnection.Close();
}
}
public void OpenForm(Form form)
{
form.FormClosed += (object s, FormClosedEventArgs args) =>
{
this.Show();
if (Class.flag == true)
{
foreach (string item in positionProd)
{
tempStr = item.Substring(0, item.Length - 2);
tempInt = Int32.Parse(tempStr);
com = $"UPDATE Warehouse SET [NumberOfProduct] = [NumberOfProduct] - 1 WHERE [Id_Position] = {tempInt}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
result = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
comboBoxAffiliate.SelectedIndex = -1;
comboBoxChooseType.SelectedIndex = -1;
comboBoxChooseProd.SelectedIndex = -1;
comboBoxSizeBuy.SelectedIndex = -1;
richTextBox.Clear();
textBoxAmount.Clear();
textBoxPriceProd.Clear();
listBoxProds.Items.Clear();
positionProd.Clear();
nameProd.Clear();
Class.nameProds = "";
Class.numPos = "";
sizeList.Clear();
amount = 0;
tempDouble = 0;
affiliateId = 0;
indexItem = 0;
result = 0;
tempInt = 0;
i = 0;
com = "";
prodName = "";
tempStr = "";
pictureBoxViewProd.Image = null;
}
};
}
private void buttonAddBasket_Click(object sender, EventArgs e)
{
if (comboBoxChooseType.Text.Length == 0 || comboBoxChooseProd.Text.Length == 0 || comboBoxSizeBuy.Text.Length == 0)
{
MessageBox.Show("Ничего не выбранно!", "Ошибка.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
com = "SELECT Product.[ProductName] " +
"FROM Product " +
"join ProductType on Product.[Id_ProductType] = ProductType.[Id_ProductTypeKA] " +
"join TableProdImg on ProductType.[Id_ProductTypeKA] = TableProdImg.[Id_Type] " +
$"WHERE TableProdImg.[Id_Type] = {comboBoxChooseType.SelectedIndex + 1} AND TableProdImg.[ProductName] = '{comboBoxChooseProd.Text}' " +
$"AND Product.[ProductName] = '{comboBoxChooseProd.Text}' " +
$"AND Product.[Size] = '{comboBoxSizeBuy.Text}'";
MyConnection.listBoxOrder(listBoxProds, com);
com = "SELECT Product.[PriceOfUnit] " +
"FROM Product " +
"join ProductType on Product.[Id_ProductType] = ProductType.[Id_ProductTypeKA] " +
"join TableProdImg on ProductType.[Id_ProductTypeKA] = TableProdImg.[Id_Type] " +
$"WHERE TableProdImg.[Id_Type] = {comboBoxChooseType.SelectedIndex + 1} AND TableProdImg.[ProductName] = '{comboBoxChooseProd.Text}' " +
$"AND Product.[ProductName] = '{comboBoxChooseProd.Text}' " +
$"AND Product.[Size] = '{comboBoxSizeBuy.Text}'";
MyConnection.textBoxLoading(textBoxAmount, com);
com = "SELECT Warehouse.[Id_Position] FROM Product " +
"join Warehouse on Product.[Id_ProductKA] = Warehouse.[Id_Product] " +
"join ProductType on Product.[Id_ProductType] = ProductType.[Id_ProductTypeKA] " +
"join TableProdImg on ProductType.[Id_ProductTypeKA] = TableProdImg.[Id_Type] " +
$"WHERE TableProdImg.[Id_Type] = {comboBoxChooseType.SelectedIndex + 1} AND TableProdImg.[ProductName] = '{comboBoxChooseProd.Text}' " +
$"AND Product.[ProductName] = '{comboBoxChooseProd.Text}' " +
$"AND Product.[Size] = '{comboBoxSizeBuy.Text}' " +
$"AND Warehouse.[Id_Affiliate] = {affiliateId}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
tempInt = Convert.ToInt32(sqlCommand.ExecuteScalar());
sqlConnection.Close();
}
positionProd.Add(tempInt.ToString() + ", ");
sizeList.Add(comboBoxSizeBuy.Text + ", ");
amount += Double.Parse(textBoxAmount.Text);
textBoxAmount.Text = amount.ToString();
i++;
}
}
private void LoadingFormUser(string login, string pass)
{
com = "SELECT [Id_UserKA] FROM Users WHERE [Login] = @uL AND [Password] = @uP";
using (SqlConnection connection = new MyConnection().GetConnection())
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand(com, connection);
sqlCommand.Parameters.Add("@uL", SqlDbType.VarChar).Value = login;
sqlCommand.Parameters.Add("@uP", SqlDbType.VarChar).Value = pass;
Class.idUser = Convert.ToInt32(sqlCommand.ExecuteScalar()); // Роль
connection.Close();
}
User formUser = new User();
formUser.Owner = this;
OpenFormMain(formUser);
formUser.Show();
}
private void buttonSendMess_Click(object sender, EventArgs e)
{
if (richTextBoxMessAdmin.Text.Length != 0)
{
com = $"INSERT INTO MessageAdmin VALUES ({idMess}, '{richTextBoxMessAdmin.Text}')";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
res = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
else
{
MessageBox.Show("Вы ничего не написали!", "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void buttonRemoveListProd_Click(object sender, EventArgs e)
{
indexItem = listBoxProds.SelectedIndex;
prodName = listBoxProds.Items[indexItem].ToString();
sizeList.RemoveAt(indexItem);
positionProd.RemoveAt(indexItem);
i--;
listBoxProds.Items.RemoveAt(indexItem);
com = "SELECT Product.[PriceOfUnit] " +
"FROM Product " +
$"WHERE [ProductName] = '{prodName}'";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
tempDouble = Convert.ToDouble(sqlCommand.ExecuteScalar());
result = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
amount -= tempDouble;
textBoxAmount.Text = amount.ToString();
}
private void MessBuyer_Load(object sender, EventArgs e)
{
MessageBox.Show("Администратор, как и вы, видит \nтолько последнее сообщение. Старайтесь уместить свои мысли в одно сообщение.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
com = $"SELECT TOP 1 [Message] FROM MessageAdmin join MessageBuyer on MessageAdmin.[Id_BuyerMess] = MessageBuyer.[Id_BuyerMessKA] join Buyer on MessageBuyer.[Id_Buyer] = Buyer.[Id_Buyer] WHERE Buyer.[Id_User] = {Class.idUser} ORDER BY MessageAdmin.[Id_MessAdmin] DESC";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
try
{
richTextBoxMessBuyer.Enabled = true;
richTextBoxMessAdmin.Text = Convert.ToString(sqlCommand.ExecuteScalar());
}
catch
{
}
sqlConnection.Close();
}
com = $"SELECT TOP 1 [MessageB] FROM MessageBuyer join Buyer on MessageBuyer.[Id_Buyer] = Buyer.[Id_Buyer] WHERE Buyer.[Id_User] = {Class.idUser} ORDER BY MessageBuyer.[Id_BuyerMessKA] DESC";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
try
{
richTextBoxMessBuyer.Text = Convert.ToString(sqlCommand.ExecuteScalar());
buttonSendMess.Enabled = true;
com = $"SELECT TOP 1 MessageBuyer.[Id_BuyerMessKA] FROM MessageBuyer join Buyer on MessageBuyer.[Id_Buyer] = Buyer.[Id_Buyer] WHERE Buyer.[Id_User] = {Class.idUser} ORDER BY [MessageB] DESC";
sqlCommand.CommandText = com;
}
catch
{
}
sqlConnection.Close();
}
}
private void buttonAddProd_Click(object sender, EventArgs e)
{
int idProd, idAffiliate, numProd, result = 0;
string com;
try
{
idProd = comboBoxAddProd.SelectedIndex + 1;
idAffiliate = comboBoxAffiliateAddProd.SelectedIndex + 1;
numProd = Int32.Parse(labelWareHouseNumProduct.Text);
com = $"select * from Warehouse Where [Id_Product] = {idProd} AND [Id_Affiliate] = {idAffiliate}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(com, sqlConnection);
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet, "Warehouse");
DataTable dataTable = dataSet.Tables["Warehouse"];
result = dataTable.Rows.Count;
sqlConnection.Close();
}
if (result > 0)
{
com = $"UPDATE Warehouse SET [NumberOfProduct] = [NumberOfProduct] + {numProd} WHERE [Id_Product] = {idProd} AND [Id_Affiliate] = {idAffiliate}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
result = sqlCommand.ExecuteNonQuery();
if (!result.Equals(0))
{
MessageBox.Show("Доставлено!", "Уведомление.", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
sqlConnection.Close();
}
}
else
{
com = $"declare @DATE DATE SET @DATE = GETDATE() EXEC NewDelivery @DATE, {idProd}, {idAffiliate}, {numProd}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
result = sqlCommand.ExecuteNonQuery();
if (!result.Equals(0))
{
MessageBox.Show("Доставлено!", "Уведомление.", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
sqlConnection.Close();
}
}
}
catch
{
MessageBox.Show("Не одно поле не выбранно!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
count = 0;
comboBoxAddProd.SelectedIndex = -1;
comboBoxAffiliateAddProd.SelectedIndex = -1;
labelWareHouseNumProduct.Text = count.ToString();
numProd = 0;
refresh();
}
}
private void buttonBuy_Click(object sender, EventArgs e)
{
int j = 0;
List <int> tempList = new List <int>();
bool flag = true;
foreach (string item in positionProd)
{
tempStr1 = item.Substring(0, item.Length - 2);
tempInt = Int32.Parse(tempStr1);
com = $"SELECT Warehouse.[NumberOfProduct] FROM Warehouse WHERE [Id_Position] = {tempInt}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
result = Convert.ToInt32(sqlCommand.ExecuteScalar());
sqlConnection.Close();
}
if (result == 0)
{
tempList.Add(j);
}
j++;
}
foreach (int item in tempList)
{
tempStr1 = positionProd[item].Substring(0, positionProd[item].Length - 2);
tempInt = Int32.Parse(tempStr1);
com = $"SELECT Product.[PriceOfUnit] FROM Product join Warehouse on Product.[Id_ProductKA] = Warehouse.[Id_Product] WHERE Warehouse.[Id_Position] = {tempInt}";
using (SqlConnection sqlConnection = new MyConnection().GetConnection())
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(com, sqlConnection);
result = Convert.ToInt32(sqlCommand.ExecuteScalar());
sqlConnection.Close();
}
amount -= result;
}
foreach (int item in tempList)
{
positionProd.RemoveAt(item);
sizeList.RemoveAt(item);
listBoxProds.Items.RemoveAt(item);
flag = false;
}
if (flag == false)
{
MessageBox.Show("Вещи, которых нет на складе, \nбыли удалены.", "Уведомление.", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
if (listBoxProds.Items.Count == 0)
{
MessageBox.Show("Ничего невыбрано.", "Ошибка.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
foreach (string item in listBoxProds.Items)
{
nameProd.Add(item + ", ");
}
foreach (string item in nameProd)
{
Class.nameProds += item;
}
foreach (string item in positionProd)
{
tempStr += item;
}
Class.numPos = tempStr;
Class.IdAffiliate = affiliateId;
Class.amount = amount;
Payment payment = new Payment();
payment.Owner = this;
OpenForm(payment);
payment.Show();
}
}
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;
}
}
}
