/*вывод товаров корзины*/
public void Write_Admin_Basket(string client_id)
{
Admin_Basket_ListView.ItemsSource = null;
tmp = client_id;
string sqlExpression;
SqlCommand command;
SqlParameter client_id_param;
SqlDataReader reader;
List <basket_goods> goods_list = new List <basket_goods>();
final_price = 0;
try {
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
/* получаем итогувую цену */
sqlExpression = "SELECT dbo.Basket.count_goods, dbo.Goods.price, dbo.Goods.price * dbo.Basket.count_goods AS Expr1 FROM dbo.Basket INNER JOIN dbo.Goods ON dbo.Basket.product_id = dbo.Goods.product_id WHERE(dbo.Basket.client_id = @client_id_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
price_of_goods og = new price_of_goods();
og.price = reader.GetValue(2).ToString();
final_price += double.Parse(og.price);
}
}
reader.Close();
Final_price_textblock.Text = String.Format("{0:C}", final_price.ToString() + " руб");
/* получаем данные о товарах в корзине */
sqlExpression = "SELECT dbo.basket.product_id, dbo.basket.count_goods, dbo.Goods.product_name, dbo.Goods.image, dbo.Goods.price FROM dbo.Basket INNER JOIN dbo.Goods ON dbo.basket.product_id = dbo.Goods.product_id WHERE client_id = @client_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
basket_goods st = new basket_goods();
st.product_id = reader.GetInt32(0);
st.client_id = client_id;
st.count_goods = reader.GetInt32(1);
st.product_name = reader.GetString(2);
st.image = reader[3] as byte[];
st.price = reader.GetValue(4).ToString();
goods_list.Add(st);
}
Admin_Basket_ListView.ItemsSource = goods_list;
reader.Close();
}
else
{
reader.Close();
}
}
catch (SqlException er) {
MessageBox.Show(er.Message);
}
}
public void Write_Admin_Basket(string client_id)
{
Admin_Basket_ListView.ItemsSource = null;
tmp = client_id;
string sqlExpression;
SqlCommand command;
SqlParameter client_id_param;
SqlDataReader reader;
List <basket_goods> goods_list = new List <basket_goods>();
final_price = 0;
try {
SqlConnection connection = new SqlConnection(@"Data Source=DESKTOP-52L8N5J\SQLEXPRESS02;;Initial Catalog=Pharmacy;" + "Integrated Security=True;Connect Timeout=15;Encrypt=False;" + "TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
connection.Open();
sqlExpression = "SELECT dbo.Basket.count_goods, dbo.Goods.price, dbo.Goods.price * dbo.Basket.count_goods AS Expr1 FROM dbo.Basket INNER JOIN dbo.Goods ON dbo.Basket.product_id = dbo.Goods.product_id WHERE(dbo.Basket.client_id = @client_id_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
price_of_goods og = new price_of_goods();
og.price = reader.GetValue(2).ToString();
final_price += double.Parse(og.price);
}
}
reader.Close();
Final_price_textblock.Text = String.Format("{0:C}", final_price.ToString() + " руб");
sqlExpression = "SELECT dbo.basket.product_id, dbo.basket.count_goods, dbo.Goods.product_name, dbo.Goods.image, dbo.Goods.price FROM dbo.Basket INNER JOIN dbo.Goods ON dbo.basket.product_id = dbo.Goods.product_id WHERE client_id = @client_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
basket_goods st = new basket_goods();
st.product_id = reader.GetInt32(0);
st.client_id = client_id;
st.count_goods = reader.GetInt32(1);
st.product_name = reader.GetString(2);
st.image = reader[3] as byte[];
st.price = reader.GetValue(4).ToString(); ///
goods_list.Add(st);
}
Admin_Basket_ListView.ItemsSource = goods_list;
reader.Close();
}
else
{
reader.Close();
}
}
catch (SqlException er) {
MessageBox.Show(er.Message);
}
}
// Чтобы совершить операцию нам надо записать данные из таблицы Basket в таблицы Operation(1 раз) и Operations_content(несколько раз), от того и такая каша
private void Operation_Add_Button(object sender, RoutedEventArgs e)
{
InitializeComponent();
string client_id = tmp; // получаем код клиента
string current_time = "";
string sqlExpression;
SqlCommand command;
SqlParameter product_id_param;
SqlParameter client_id_param;
SqlDataReader reader;
final_price = 0;
int res = 0;
try {
SqlConnection connection = new SqlConnection(@"Data Source=DESKTOP-52L8N5J\SQLEXPRESS02;;Initial Catalog=Pharmacy;" + "Integrated Security=True;Connect Timeout=15;Encrypt=False;" + "TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
connection.Open();
// проверяем есть ли у клиента в корзине товары
sqlExpression = "SELECT * FROM dbo.Basket WHERE(dbo.Basket.client_id = @client_id_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
res = 0;
}
else
{
res = -1;
}
reader.Close();
if (res == 0)
{
// если есть, то получаем их общую стоимость
sqlExpression = "SELECT dbo.Basket.count_goods, dbo.Goods.price, dbo.Goods.price * dbo.Basket.count_goods AS Expr1 FROM dbo.Basket INNER JOIN dbo.Goods ON dbo.Basket.product_id = dbo.Goods.product_id WHERE(dbo.Basket.client_id = @client_id_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
price_of_goods og = new price_of_goods();
og.price = reader.GetValue(2).ToString();
final_price += float.Parse(og.price);
}
}
reader.Close();
// и выбираем из корзины данные, чтобы записать в др. таблицы
sqlExpression = "SELECT * FROM Basket WHERE client_id = @client_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
current_time = DateTime.Now.ToString("yyyy:dd:M:HH:mm:ss:fffffff");
reader.Close();
// записываем в таблицу операций новую операцию
sqlExpression = "INSERT INTO Operation (client_id, date_time, final_price) VALUES (@client_id_value, @date_time_value, @final_price_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
SqlParameter date_time_param = new SqlParameter("@date_time_value", current_time); command.Parameters.Add(date_time_param);
SqlParameter final_price_param = new SqlParameter("@final_price_value", final_price); command.Parameters.Add(final_price_param);
command.ExecuteNonQuery();
reader.Close();
// и далее записываем каждую строку из корзины в детали операций
sqlExpression = "SELECT * FROM Basket WHERE client_id = @client_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
while (reader.Read())
{
operation_goods og = new operation_goods();
og.client_id = reader.GetString(0);
og.date_time = current_time;
og.product_id = reader.GetInt32(1);
og.count = reader.GetInt32(2);
reader.Close();
// вычитаем из таблицы товаров количество купленных товаров
sqlExpression = "UPDATE Goods SET [count]=([count]-(SELECT count_goods FROM Basket WHERE product_id=@product_id_value AND client_id = @client_id_value )) " +
"WHERE product_id = @product_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
product_id_param = new SqlParameter("@product_id_value", og.product_id); command.Parameters.Add(product_id_param);
command.ExecuteNonQuery();
// заполнаем детали заказа
sqlExpression = "INSERT INTO Operations_content (client_id, date_time, product_id, count) VALUES (@client_id_value, @date_time_value, @product_id_value, @count_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", og.client_id); command.Parameters.Add(client_id_param);
date_time_param = new SqlParameter("@date_time_value", og.date_time); command.Parameters.Add(date_time_param);
product_id_param = new SqlParameter("@product_id_value", og.product_id); command.Parameters.Add(product_id_param);
SqlParameter count_param = new SqlParameter("@count_value", og.product_id); command.Parameters.Add(count_param);
command.ExecuteNonQuery();
// удаляем из корзины
sqlExpression = "DELETE FROM Basket WHERE client_id = @client_id_value AND product_id=@product_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", og.client_id); command.Parameters.Add(client_id_param);
product_id_param = new SqlParameter("@product_id_value", og.product_id); command.Parameters.Add(product_id_param);
command.ExecuteNonQuery();
// танцы с бубном
sqlExpression = "SELECT * FROM Basket WHERE client_id = @client_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
}
reader.Close();
}
else
{
reader.Close();
}
Write_Admin_Basket(client_id);
try {
StreamWriter p = new StreamWriter("test.txt", append: true);
p.WriteLine(" " + current_time + " " + client_id);
/*
* "Звездный Подорожничек"
* Чек Дата 2021:11:5:19:45:20
* Аскорбинка ... 24 руб Х 10 шт
* Стоимость ... 240 руб
* ИТОГ = 240 руб.
*/
p.Close();
} catch (Exception ex) {
MessageBox.Show("Во время выполнения произошла ошибка!", ex.Message);
}
MessageBox.Show("Поздравляю! Вы совершили оплату. Ваш чек сохранен в файл");
}
else
{
MessageBox.Show("Тут слишком пусто");
}
} catch (SqlException er) {
MessageBox.Show(er.Message);
}
}
/*Совершение покупки. Чтобы совершить операцию нам надо записать данные из таблицы Basket в таблицы Operation(1 раз) и Operations_content(несколько раз), от того и такая каша*/
private void Operation_Add_Button(object sender, RoutedEventArgs e)
{
InitializeComponent();
string client_id = tmp; // получаем код клиента
string current_time = "";
string sqlExpression;
SqlCommand command;
SqlParameter product_id_param;
SqlParameter client_id_param;
SqlParameter date_time_param;
SqlDataReader reader;
final_price = 0;
int res = 0;
string fname = "cheque" + DateTime.Now.ToString("yyffffff") + ".txt";
try {
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
// проверяем есть ли у клиента в корзине товары
sqlExpression = "SELECT * FROM dbo.Basket WHERE(dbo.Basket.client_id = @client_id_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
res = 0;
}
else
{
res = -1;
}
reader.Close();
if (res == 0)
{
// если есть, то получаем их общую стоимость
sqlExpression = "SELECT dbo.Basket.count_goods, dbo.Goods.price, dbo.Goods.price * dbo.Basket.count_goods AS Expr1 FROM dbo.Basket INNER JOIN dbo.Goods ON dbo.Basket.product_id = dbo.Goods.product_id WHERE(dbo.Basket.client_id = @client_id_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
price_of_goods og = new price_of_goods();
og.price = reader.GetValue(2).ToString();
final_price += float.Parse(og.price);
}
}
reader.Close();
// и выбираем из корзины данные, чтобы записать в др. таблицы
sqlExpression = "SELECT * FROM Basket WHERE client_id = @client_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
current_time = DateTime.Now.ToString("yyyy:dd:M:HH:mm:ss:fffffff");
reader.Close();
// записываем в таблицу операций новую операцию
sqlExpression = "INSERT INTO Operation (client_id, date_time, final_price) VALUES (@client_id_value, @date_time_value, @final_price_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
date_time_param = new SqlParameter("@date_time_value", current_time); command.Parameters.Add(date_time_param);
SqlParameter final_price_param = new SqlParameter("@final_price_value", final_price); command.Parameters.Add(final_price_param);
command.ExecuteNonQuery();
reader.Close();
// и далее записываем каждую строку из корзины в детали операций
sqlExpression = "SELECT * FROM Basket WHERE client_id = @client_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
while (reader.Read())
{
operation_goods og = new operation_goods();
og.client_id = reader.GetString(0);
og.date_time = current_time;
og.product_id = reader.GetInt32(1);
og.count = reader.GetInt32(2);
reader.Close();
// вычитаем из таблицы товаров количество купленных товаров
sqlExpression = "UPDATE Goods SET [count]=([count]-(SELECT count_goods FROM Basket WHERE product_id=@product_id_value AND client_id = @client_id_value )) " +
"WHERE product_id = @product_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
product_id_param = new SqlParameter("@product_id_value", og.product_id); command.Parameters.Add(product_id_param);
command.ExecuteNonQuery();
// заполнаем детали заказа
sqlExpression = "INSERT INTO Operations_content (client_id, date_time, product_id, count) VALUES (@client_id_value, @date_time_value, @product_id_value, @count_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", og.client_id); command.Parameters.Add(client_id_param);
date_time_param = new SqlParameter("@date_time_value", og.date_time); command.Parameters.Add(date_time_param);
product_id_param = new SqlParameter("@product_id_value", og.product_id); command.Parameters.Add(product_id_param);
SqlParameter count_param = new SqlParameter("@count_value", og.product_id); command.Parameters.Add(count_param);
command.ExecuteNonQuery();
// удаляем из корзины
sqlExpression = "DELETE FROM Basket WHERE client_id = @client_id_value AND product_id=@product_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", og.client_id); command.Parameters.Add(client_id_param);
product_id_param = new SqlParameter("@product_id_value", og.product_id); command.Parameters.Add(product_id_param);
command.ExecuteNonQuery();
// танцы с бубном
sqlExpression = "SELECT * FROM Basket WHERE client_id = @client_id_value";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
reader = command.ExecuteReader();
}
reader.Close();
}
else
{
reader.Close();
}
Write_Admin_Basket(client_id);
try { // записываем чек в файл
StreamWriter p = new StreamWriter(fname, append: true);
p.WriteLine(" Звездный Подорожничек");
p.WriteLine(" Чек Дата " + current_time);
/*получаем информацию о купленных товарах*/
sqlExpression = "SELECT dbo.Operations_content.client_id, dbo.Operations_content.date_time, dbo.Operations_content.product_id, dbo.Operations_content.count, dbo.Goods.product_name, dbo.Goods.price " +
" FROM dbo.Operations_content LEFT OUTER JOIN dbo.Goods ON dbo.Operations_content.product_id = dbo.Goods.product_id " +
" WHERE (dbo.Operations_content.client_id = @client_id_value) AND (dbo.Operations_content.date_time = @date_time_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
date_time_param = new SqlParameter("@date_time_value", current_time); command.Parameters.Add(date_time_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
p.WriteLine(reader[4].ToString() + " ... " + reader[5].ToString() + " руб X " + reader[3].ToString() + " шт");
}
}
reader.Close();
/*получаем итоговую цену*/
sqlExpression = "SELECT * FROM dbo.Operation WHERE (dbo.Operation.client_id = @client_id_value) AND (dbo.Operation.date_time = @date_time_value)";
command = new SqlCommand(sqlExpression, connection);
client_id_param = new SqlParameter("@client_id_value", client_id); command.Parameters.Add(client_id_param);
date_time_param = new SqlParameter("@date_time_value", current_time); command.Parameters.Add(date_time_param);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
p.WriteLine(" ИТОГ = " + reader[2].ToString() + " руб");
}
}
reader.Close();
p.Close();
} catch (Exception ex) {
MessageBox.Show("Во время выполнения произошла ошибка!", ex.Message);
}
MessageBox.Show("Поздравляю! Вы совершили оплату. Ваш чек сохранен в файл " + fname);
}
else
{
MessageBox.Show("Тут слишком пусто");
}
} catch (SqlException er) {
MessageBox.Show(er.Message);
}
}