using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Formats.Asn1;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Drawing.Imaging;
using PackingControlSystem.lib;
using Microsoft.VisualBasic.ApplicationServices;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.VisualBasic.Logging;
using excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Runtime.InteropServices;
using System.Net.Sockets;
using System.Linq.Expressions;
using System.Net.NetworkInformation;

namespace PackingControlSystem
{
    public partial class frmReportReturn : Form
    {
        private List<List<string>> Settings = new List<List<string>>();
        DataTable dtHistory = new DataTable();
        DataTable dtHistorySelect = new DataTable();
        DataTable dtFRMQRKANBANSelect = new DataTable();
        DataTable dtPartSelect = new DataTable();
        Microsoft.Office.Interop.Excel.Application excel;
        Microsoft.Office.Interop.Excel.Workbook excelworkBook;
        Microsoft.Office.Interop.Excel.Worksheet excelSheet;
        Microsoft.Office.Interop.Excel.Range excelCellrange;

        public frmReportReturn()
        {
            InitializeComponent();
        }

        private void frmReportReturn_Load(object sender, EventArgs e)
        {
            List<string> tmp = null;
            dgvReportReturn.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            //listBox1.HorizontalScrollbar = true;
            Settings = new List<List<string>>();
            tmp = null;
            using (var csv = new CsvReader(System.IO.Directory.GetCurrentDirectory() + "\\Settings.csv"))
            {
                while ((tmp = csv.ReadRow()) != null)
                {
                    Settings.Add(tmp);

                }

            }//using end

            setStart();




        }

        public string RtnDbCon()
        {
            return "Data Source=" + this.Settings[0][0] + ";Initial Catalog=" + this.Settings[0][1] + ";User Id=sa;Password=" + this.Settings[0][2] + ";";

        }

        private void setStart()
        {
            txtPartID.Text = string.Empty;
            txtPartNO.Text = string.Empty;
            txtCustomerPartID.Text = string.Empty;
            txtCustomerPartNO.Text = string.Empty;
            txtKBSerial.Text = string.Empty;
            txtPartSerial.Text = string.Empty;
            txtPKLNO.Text = string.Empty;
            dtpProductStart.Text = "01/01/2022";
            //dtpProductTo.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dtpProductTo.Value = DateTime.Now;
            dtpDeliveryStart.Text = "01/01/2022";
            //dtpPrintTo.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dtpDeliveryTo.Value = DateTime.Now;
            dgvReportReturn.DataSource = DBNull.Value;

        }

        private void loadTable()
        {
            dtHistorySelect = new DataTable();
            clsUtil_MSSQL.GetDataFromTBHistoryReturnSelect(RtnDbCon(), "TT_KANBAN_H", ref dtHistorySelect, Convert.ToInt32(dtpProductStart.Value.ToString("yyyyMMdd")), Convert.ToInt32(dtpProductTo.Value.ToString("yyyyMMdd")), Convert.ToInt32(dtpDeliveryStart.Value.ToString("yyyyMMdd")), Convert.ToInt32(dtpDeliveryTo.Value.ToString("yyyyMMdd")), txtPartID.Text, txtPartNO.Text, txtCustomerPartID.Text, txtCustomerPartNO.Text, txtKBSerial.Text, txtPartSerial.Text, txtPKLNO.Text);
            dgvReportReturn.DataSource = dtHistorySelect;
            dgvReportReturn.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders);
            //dgvReportOK.RowHeadersWidth = 70;

            for (int i = 0; i < dgvReportReturn.Rows.Count; i++)
            {
                if (i % 2 == 0)
                {

                    dgvReportReturn.Rows[i].DefaultCellStyle.BackColor = Color.WhiteSmoke;

                }
                else
                {


                }

            }

        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            Cursor = Cursors.WaitCursor;
            loadTable();
            Cursor = Cursors.Default;
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Excel File|*.xls";
            if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string ext = System.IO.Path.GetExtension(sfd.FileName);
                switch (ext)
                {
                    case ".xls":
                        Cursor = Cursors.WaitCursor;
                        WriteToExcel(dtHistorySelect, sfd.FileName);
                        Cursor = Cursors.Default;
                        break;
                }
            }
        }

        private void WriteToExcel(System.Data.DataTable dt, string location)
        {


            //instantiate excel objects (application, workbook, worksheets)
            excel.Application XlObj = new excel.Application();
            XlObj.Visible = false;
            excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add(""));
            excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet;



            //run through datatable and assign cells to values of datatable
            try
            {
                int row = 1; int col = 1;
                foreach (DataColumn column in dt.Columns)
                {
                    //adding columns
                    WsObj.Cells[row, col] = column.ColumnName;

                    WsObj.Cells[1, col].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);

                    col++;
                }
                //reset column and row variables
                col = 1;
                row++;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //adding data
                    foreach (var cell in dt.Rows[i].ItemArray)
                    {
                        WsObj.Cells[row, col] = cell;
                        //WsObj.Columns[6].NumberFormat = "@";
                        //WsObj.Columns[8].NumberFormat = "@";
                        //WsObj.Columns[9].NumberFormat = "@";
                        //WsObj.Columns[12].NumberFormat = "@";
                        //WsObj.Columns[13].NumberFormat = "@";
                        //WsObj.Columns[14].NumberFormat = "@";
                        //WsObj.Columns[15].NumberFormat = "@";
                        //WsObj.Columns[16].NumberFormat = "@";
                        col++;
                    }
                    col = 1;
                    row++;
                }
                WsObj.Columns.AutoFit();
                WbObj.SaveAs(location);
            }
            catch (COMException x)
            {
                //ErrorHandler.Handle(x);
            }
            catch (Exception ex)
            {
                //ErrorHandler.Handle(ex);
            }
            finally
            {
                WbObj.Close();
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            setStart();
        }
    }
}
