public void Populate_Parts()
        {
            this.Part_List = new Dictionary <int, List <Part> >();

            #region Get die parts (not including extra charges)
            ExcoODBC instance = ExcoODBC.Instance;
            //string query = "select line, qty, description, CONVERT(varchar, cast(baseprice as money), 1) as amt, dienumber as die# from d_orderitem where ordernumber = '" + Order_Number + "' order by line asc";
            string query = "select a.line, a.qty, a.description, CONVERT(varchar, cast(a.baseprice as money), 1) as amt, a.dienumber as die#, b.price as subtotal from d_orderitem as a, d_orderitem as b where a.description = b.description and a.ordernumber = b.ordernumber and a.ordernumber = '" + Order_Number + "' order by a.line asc";
            instance.Open(RefDB);
            OdbcDataReader reader = instance.RunQuery(query);
            while (reader.Read())
            {
                this.Part_List.Add(Convert.ToInt32(reader[0].ToString().Trim()), new List <Part>()
                {
                    new Part()
                    {
                        Part_Desc     = reader[2].ToString().Trim(),
                        Part_Quantity = reader[1].ToString().Trim(),
                        Part_Amount   = reader[3].ToString().Trim(),
                        Part_Number   = reader[4].ToString().Trim(),
                        Part_SubTotal = reader[5].ToString().Trim()
                    }
                });
            }
            #endregion

            #region Get extra charges
            instance = ExcoODBC.Instance;
            query    = "select line, chargename, CONVERT(varchar, cast(price as money), 1), qty from d_orderitemcharges where ordernumber = '" + Order_Number + "' order by line asc";
            instance.Open(RefDB);
            reader = instance.RunQuery(query);
            while (reader.Read())
            {
                try
                {
                    Part_List[Convert.ToInt32(reader[0].ToString().Trim())].Add(
                        new Part()
                    {
                        Part_Desc     = reader[1].ToString().Trim(),
                        Part_Quantity = reader[3].ToString().Trim(),
                        Part_Amount   = reader[2].ToString().Trim(),
                        Part_Number   = "",
                        Part_SubTotal = ""
                    }
                        );
                }
                catch
                {
                }
            }
            #endregion

            // Add variable line for the end
            Part_List.Add(999, new List <Part>());

            #region Get freight, surcharge, taxes, fasttrack
            instance = ExcoODBC.Instance;
            query    = "select freight as Freight, CONVERT(varchar, cast(total as money), 1) as Total, gst as GST, fasttrackcharge as FastTrack, discountamount as Discount, CONVERT(varchar, cast(total-gst-freight-fasttrack+discountamount as money), 1) as subtotal, steelsurcharge as surcharge from d_order where ordernumber = '" + Order_Number + "'";
            instance.Open(RefDB);
            reader = instance.RunQuery(query);
            while (reader.Read())
            {
                // Freight
                if (Convert.ToDouble(reader[0].ToString().Trim()) > 0)
                {
                    Part_List[999].Add(
                        new Part()
                    {
                        Part_Desc     = "FREIGHT",
                        Part_Quantity = "",
                        Part_Amount   = reader[0].ToString().Trim(),
                        Part_Number   = "",
                        Part_SubTotal = ""
                    }
                        );
                }
                // GST
                if (Convert.ToDouble(reader[2].ToString().Trim()) > 0)
                {
                    Part_List[999].Add(
                        new Part()
                    {
                        Part_Desc     = "GST",
                        Part_Quantity = "",
                        Part_Amount   = reader[2].ToString().Trim(),
                        Part_Number   = "",
                        Part_SubTotal = ""
                    }
                        );
                }
                // Fast track
                if (Convert.ToDouble(reader[3].ToString().Trim()) > 0)
                {
                    Part_List[999].Add(
                        new Part()
                    {
                        Part_Desc     = "FAST TRACK",
                        Part_Quantity = "",
                        Part_Amount   = reader[3].ToString().Trim(),
                        Part_Number   = "",
                        Part_SubTotal = ""
                    }
                        );
                }
                // Discount
                if (Convert.ToDouble(reader[4].ToString().Trim()) > 0)
                {
                    Part_List[999].Add(
                        new Part()
                    {
                        Part_Desc     = "DISCOUNT",
                        Part_Quantity = "",
                        Part_Amount   = "-" + reader[4].ToString().Trim(),
                        Part_Number   = "",
                        Part_SubTotal = ""
                    }
                        );
                }
                // Discount
                if (Convert.ToDouble(reader[6].ToString().Trim()) > 0)
                {
                    Part_List[999].Add(
                        new Part()
                    {
                        Part_Desc     = "STEEL SURCHARGE",
                        Part_Quantity = "",
                        Part_Amount   = reader[6].ToString().Trim(),
                        Part_Number   = "",
                        Part_SubTotal = ""
                    }
                        );
                }
                // Total
                Part_List[999].Add(
                    new Part()
                {
                    Part_Desc     = "TOTAL",
                    Part_Quantity = "",
                    Part_Amount   = reader[1].ToString().Trim(),
                    Part_Number   = "",
                    Part_SubTotal = ""
                }
                    );
            }
            #endregion
            reader.Close();
        }
        public void Generate_Invoice_Register()
        {
            Price_List   = new Dictionary <string, string>();
            Invoice_List = new List <Invoice>();
            excel        = new ExcelWriter.Application();
            misValue     = System.Reflection.Missing.Value;

            string to_date = DateTime.DaysInMonth(Convert.ToInt32(to_year.Text), Convert.ToInt32(to_month.Text)).ToString();
            //if (to_month.Text == from_month.Text) to_date = "31";
            ExcoODBC instance = ExcoODBC.Instance;

            /*
             * string query = "select a.ordernumber, a.invoicedate, a.invoicenumber, a.roughweight, a.nitrideweight, c.cmsNumber, b.name, total, pricelist " +
             *              "from d_order as a, d_customer as b, d_customermapping as c where (a.note not like '%cancel%' or a.note is null) and a.customerpo" +
             *              " not like '%cancel%' and invoicedate between '" + from_year.Text + "-" + from_month.Text + "-" + "01' and '" + to_year.Text + "-" + to_month.Text + "-" + to_date +
             *              "' and a.customercode = b.customercode and a.customercode = c.decadecode and c.cmsNumber like '%S%' order by accountset desc, a.customercode asc";*/

            string query = "select a.ordernumber, a.invoicedate, a.invoicenumber, a.roughweight, a.nitrideweight, (select top 1 cmsNumber from d_customermapping as c where " +
                           "a.customercode = c.decadecode order by cmsnumber desc) , b.name, total, pricelist from d_order as a, d_customer as b " +
                           "where (a.note not like '%cancel%' or a.note is null) and (a.customerpo not like '%cancel%' or a.customerpo is null) and invoicedate between '" +
                           from_year.Text + "-" + from_month.Text + "-" + "01' and '" + to_year.Text + "-" + to_month.Text + "-" + to_date + "' " +
                           "and a.customercode = b.customercode" + (bycust.Checked ? " and b.name like '%" + cust_box.Text + "%'" : "") + " order by accountset desc, a.customercode asc";

            instance.Open(masterDB);
            OdbcDataReader reader = instance.RunQuery(query);

            while (reader.Read())
            {
                Invoice inv = new Invoice();
                inv.Order_Number    = reader[0].ToString().Trim();
                inv.InvoiceDate     = reader[1].ToString().Trim();
                inv.Invoice_Number  = reader[2].ToString().Trim();
                inv.SLB             = reader[3].ToString().Trim();
                inv.NLB             = reader[4].ToString().Trim();
                inv.Customer_Number = reader[5].ToString().Trim().Trim();
                inv.Customer_Name   = reader[6].ToString().Trim();
                inv.Total_Amount    = reader[7].ToString().Trim();
                inv.Customer_Class  = reader[8].ToString().Trim();
                //inv.SubTotal = reader[9].ToString().Trim();
                inv.Plant_Code          = plant_name == "MARKHAM" ? "001" : plant_name == "MICHIGAN" ? "002" : plant_name == "TEXAS" ? "005" : "004";
                inv.Invoice_Line_Number = "1";
                inv.RefDB = masterDB;

                Invoice_List.Add(inv);
            }

            reader.Close();

            foreach (Invoice inv3 in Invoice_List)
            {
                inv3.Populate_Parts();
            }


            ExcoODBC solarsoft2 = ExcoODBC.Instance;
            string   query23    = "select * from cmsdat.code where A2 = 'KK'";

            solarsoft2.Open(Database.CMSDAT);
            OdbcDataReader reader23 = solarsoft2.RunQuery(query23);

            // get main data
            while (reader23.Read())
            {
                try
                {
                    Price_List.Add(reader23["A9"].ToString().Trim(), reader23["A30"].ToString().Trim());
                }
                catch
                {
                }
            }
            reader23.Close();

            foreach (Invoice inv2 in Invoice_List)
            {
                inv2.Customer_Class_Desc = Price_List[inv2.Customer_Class];
            }

            /*
             * foreach (Invoice inv2 in Invoice_List)
             * {
             *  ExcoODBC solarsoft = ExcoODBC.Instance;
             *  string query2 = "select * from cmsdat.CODE where A2 = 'KK' and A9 like '%" + inv2.Customer_Class + "%' and A9 not like '%X%'";
             *  solarsoft.Open(Database.CMSDAT);
             *  OdbcDataReader reader2 = solarsoft.RunQuery(query2);
             *  // get main data
             *  while (reader2.Read())
             *  {
             *      inv2.Customer_Class_Desc = reader2["A30"].ToString().Trim();
             *  }
             *  reader2.Close();
             * }*/

            foreach (Invoice inv4 in Invoice_List)
            {
                ExcoODBC solarsoft = ExcoODBC.Instance;
                string   query2    = "select * from cmsdat.cust where BVCUST = '" + inv4.Customer_Number + "'";
                solarsoft.Open(Database.CMSDAT);
                OdbcDataReader reader2 = solarsoft.RunQuery(query2);
                // get main data
                while (reader2.Read())
                {
                    inv4.Currency_Code   = reader2["BVCURR"].ToString().Trim();
                    inv4.Terms_Code      = reader2["BVTERM"].ToString().Trim();
                    inv4.FOB_Code        = reader2["BVFOBC"].ToString().Trim();
                    inv4.Salesman_Code   = reader2["BVSALM"].ToString().Trim();
                    inv4.Prov_State_Code = reader2["BVPRCD"].ToString().Trim();
                    inv4.Country         = reader2["BVCTRY"].ToString().Trim();
                    //inv4.Customer_Class_Desc = reader2["A30"].ToString().Trim();
                }
                reader2.Close();
            }

            workBook      = excel.Workbooks.Add(misValue);
            Invoices      = workBook.Worksheets.Add();
            Invoices.Name = "Invoice Register for " + plant_name;

            int excel_row = 1;

            // title
            excel_row = WriteSheetHeader("Invoice Register for " + plant_name, Invoices);
            excel_row++;
            excel_row = WriteHeader(Invoices, excel_row);
            excel_row++;
            excel_row = WriteLines(Invoices, excel_row);
            excel_row++;
            AdjustSheetStyle(Invoices, excel_row);

            // write to file
            string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Invoice Register for " + plant_name + ".xlsx");

            File.Delete(path);
            workBook.SaveAs(path, ExcelWriter.XlFileFormat.xlOpenXMLWorkbook);
            _MARSHAL_KILL_EXCEL(path);
        }