BookClubKMA/СУБД Книжный клуб/FormOrder.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static СУБД_Книжный_клуб.FormMain;
using Excel = Microsoft.Office.Interop.Excel;
 
namespace СУБД_Книжный_клуб
{
    public partial class FormOrder : Form
    {
        public FormOrder()
        {
            InitializeComponent();
        }
        SqlConnection Con = new SqlConnection(FormMain.TxtCon);

        /// <summary>
        /// Вывод списка товаров в заказе
        /// </summary>
        void FillListProduct()
        {
            DgvProductList.Rows.Clear();
            
            for (int i = 0; i <= LstOrder.Count-1; i++)
            {
                string TxtQuery = @"SELECT Product.IdProduct, Product.Photo,
Product.NameProduct, Manufacturer.NameManufacturer, Product.Cost,
Product.Discount, Product.CountProduct, Product.Description
FROM Product INNER JOIN Manufacturer ON Product.IdManufacturer = 
Manufacturer.IdManufacturer
where IdProduct =" + LstOrder[i].IdProduct;
                SqlCommand Query = new SqlCommand(TxtQuery, Con);
                Con.Open();
                SqlDataReader Res = Query.ExecuteReader();
                
                Res.Read();
                string Info = $@"Товар: {Res["NameProduct"]}
Производитель: {Res["NameManufacturer"]}
Описание: {Res["Description"]}
Цена: {Res["Cost"]}
Скидка: {Res["Discount"]}
Кол-во на складе: {Res["CountProduct"]}";
                try
                {
                    DgvProductList.Rows.Add(Image.FromFile(Application.StartupPath + "\\Фото\\" + Res["Photo"]), Info, LstOrder[i].CountInOrder);
                }
                catch
                {
                    DgvProductList.Rows.Add( Image.FromFile(Application.StartupPath + "\\Фото\\blank.jpg" + Res["Photo"]), Info, LstOrder[i].CountInOrder);
                    
                }
                Con.Close();

            }
            

            

        }
        public List<FormMain.OrderItem> LstOrder = new List<FormMain.OrderItem>();

        private void BtnCheckOut_Click(object sender, EventArgs e)
        {
           if (LstOrder.Count == 0)
            {
                MessageBox.Show("Нет товаров в заказе!","Внимание!",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
                return;
            }
           
            
            Random Rnd = new Random();
            int Code = Rnd.Next(100, 1000);
            int DeliveryDay = 3;
            for (int i = 0; i <= LstOrder.Count - 1; i++)
                if (LstOrder[i].CountInStorage <= 3)
                {
                    DeliveryDay = 6;
                    break;

                }
            Con.Open();
            string TxtQuery = $@"insert into [Order] (IdUser, IdPickupPoint,DateOrder, Code,IdOrderStatus)
Values (4,'{CmbPickUpPoint.SelectedValue.ToString()}',GetDate(),{Code},1)
Select SCOPE_Identity()";
            SqlCommand Com = new SqlCommand(TxtQuery,Con);
            string IdOrder = Com.ExecuteScalar().ToString();
            for (int i = 0; i<= LstOrder.Count-1; i++)
            {
                TxtQuery = $@"insert into OrderProduct (IdOrder, IdProduct, CountProduct)
Values ({IdOrder},{LstOrder[i].IdProduct},{LstOrder[i].CountInOrder})";
                Com = new SqlCommand(TxtQuery, Con);
                Com.ExecuteNonQuery();


                int NewCount = LstOrder[i].CountInStorage - LstOrder[i].CountInOrder;
                
                if (NewCount < 0 )
                    NewCount= 0;
                    TxtQuery = $@"Update Product Set CountProduct = {NewCount}
where IdProduct ={LstOrder[i].IdProduct}";
//              
                Com = new SqlCommand(TxtQuery, Con);
                Com.ExecuteNonQuery();
               
            }
            Con.Close();
            FillListProduct();

            MessageBox.Show("Заказ сформирован. Код для получения: "+ Code,"Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Information) ;
            if (SaveToPDF.ShowDialog() != DialogResult.OK)
            {
                LstOrder.Clear();
                Close();
            }
                

            Excel.Application ExApp = new Excel.Application();
            //ExApp.Visible= true;
            ExApp.Workbooks.Open(Application.StartupPath + "\\Заказ.xlsx",null,true);
            Excel.Worksheet MyList = ExApp.Worksheets.get_Item(1);

            double Sum = 0, DiscountSum = 0;
            CalcOrder(ref Sum, ref DiscountSum);

            MyList.Cells[1, 2] = DateTime.Now.ToString("dd.MM.yyyy");
            MyList.Cells[2, 2] = IdOrder;
            MyList.Cells[3, 2] =Sum ;
            MyList.Cells[4, 2] =  DiscountSum ;
            MyList.Cells[5, 2] = CmbPickUpPoint.Text;
            MyList.Cells[6, 2] = DeliveryDay + "д.";
            MyList.Cells[7, 2] = Code;
            for (int i = 0; i <= LstOrder.Count - 1; i++)
                MyList.Cells[i+9, 1] = $"{LstOrder[i].NameProduct} - {LstOrder[i].CountInOrder}шт.";
            MyList.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, SaveToPDF.FileName);
            ExApp.DisplayAlerts = false;
            ExApp.Quit();
            Process.Start(SaveToPDF.FileName);
            LstOrder.Clear();
            Close();
        }

        private void FormOrder_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'bookClubKMADataSet1.PickupPoint' table. You can move, or remove it, as needed.
            this.pickupPointTableAdapter.Fill(this.bookClubKMADataSet1.PickupPoint);
            FillListProduct();
            OutPutInfo();
        }

        private void DgvProductList_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (DgvProductList.CurrentRow == null)
                return;
            

            int n = DgvProductList.CurrentRow.Index;
            int OldNum = LstOrder[n].CountInOrder;
            try
            {
                LstOrder[n].CountInOrder = int.Parse(DgvProductList.CurrentRow.Cells[2].Value.ToString());
            }
            catch
            {
                DgvProductList.CurrentRow.Cells[2].Value = OldNum;
                MessageBox.Show("Произошла ошибка. Кол-во товара должно быть числом.", "Внимаение!", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            if (LstOrder[n].CountInOrder < 0)
            {
                DgvProductList.CurrentRow.Cells[2].Value = OldNum;
                MessageBox.Show("Кол-во не должно быть отрицательным!", "Внимаение!", MessageBoxButtons.OK, MessageBoxIcon.Error);
               
            }

            if (LstOrder[n].CountInOrder == 0)
            {
                if (MessageBox.Show("Вы точно хотите удалить товар?", "Внимание!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
                {
                    DgvProductList.CurrentRow.Cells[2].Value = OldNum;
                    return;
                }
                else
                {
                    LstOrder.RemoveAt(e.RowIndex);
                    FillListProduct();
                }
               
            }
            OutPutInfo();
        }
        /// <summary>
        /// Вывод информации о сумме заказа и сумме скидки
        /// </summary>
        public void OutPutInfo()
        {
            double Sum = 0, DiscountSum = 0;
            FormMain.CalcOrder(ref Sum, ref DiscountSum);
            LblSum.Text = $"Сумма заказа (с учетом скидки): {Sum}";
            LblDiscount.Text = $"Скидка: {DiscountSum}";
        }
        private void FormOrder_FormClosing(object sender, FormClosingEventArgs e)
        {
            DgvProductList.EndEdit();
            DgvProductList.Update();
            FillListProduct();
        }

        private void BtnDell_Click(object sender, EventArgs e)
        {
            if (LstOrder.Count == 0)
            {
                MessageBox.Show("Нет товаров в заказе!", "Внимаение!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            int n = DgvProductList.CurrentRow.Index;
            if (MessageBox.Show("Вы точно хотите удалить товар?", "Внимание!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                LstOrder.RemoveAt(n);
                FillListProduct();
            }
            OutPutInfo();

               
        }

        private void CmbPickUpPoint_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.Text = CmbPickUpPoint.SelectedValue.ToString();
        }
    }
}