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);
}