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 Microsoft.Office.Interop.Excel;


namespace PackingControlSystem
{
    public partial class frmReportNG : Form
    {
        private List<List<string>> Settings = new List<List<string>>();
        System.Data.DataTable dtHistory = new System.Data.DataTable();
        System.Data.DataTable dtHistorySelect = new System.Data.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 frmReportNG()
        {
            InitializeComponent();
        }

        private void frmReportNG_Load(object sender, EventArgs e)
        {
            List<string> tmp = null;
            dgvReportNG.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();

            //loadTable();
        }

        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;
            txtCustomerPartNO.Text = string.Empty;
            txtPartSerial.Text = string.Empty;
            dtpStart.Value = DateTime.Now;
            dtpTo.Value = DateTime.Now;
            dgvReportNG.DataSource = DBNull.Value;
        }

        private void loadTable()
        {
            dtHistorySelect = new System.Data.DataTable();
            clsUtil_MSSQL.GetDataFromTBHistoryNGSelect(RtnDbCon(), "TB_HISTORY_LOG", ref dtHistorySelect, Convert.ToInt32(dtpStart.Value.ToString("yyyyMMdd")), Convert.ToInt32(dtpTo.Value.ToString("yyyyMMdd")), txtPartID.Text, txtPartNO.Text, txtCustomerPartNO.Text, txtPartSerial.Text);
            dgvReportNG.DataSource = dtHistorySelect;
            dgvReportNG.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders);
            //dgvReportNG.RowHeadersWidth = 70;

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

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

                }
                else
                {


                }

            }

        }

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

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

        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":

                        WriteToExcel(dtHistorySelect, sfd.FileName);

                        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[8].NumberFormat = "@";
                        //WsObj.Columns[9].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 dgvReportNG_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            // Add row number
            (sender as DataGridView).Rows[e.RowIndex].HeaderCell.Value = (e.RowIndex + 1).ToString();

            int idx = e.RowIndex;
            DataGridViewRow row = dgvReportNG.Rows[idx];
            row.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
        }
    }
}
