public InvoiceCompleted(int invoiceID)
{
InitializeComponent();
invoice = new Invoice(invoiceID);
invoiceContentsList = InvoiceContentsDatabase.GetInvoiceContents(invoiceID);
this.customerID = invoice.customer.StoreID;
Customer c = CustomerDatabase.SearchCustomersByID(customerID);
ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(c.Province);
if (provinceTax.pst == 0)
{
PST = false;
}
else
{
PST = true;
}
panel1.Location = new Point(30, 135);
panel1.Size = new Size(900, 360);
panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
panel1.AutoScroll = true;
panel1.BackColor = Color.DarkGray;
this.Controls.Add(panel1);
AddLabels(customerID);
AddTotalBoxes(customerID);
AddItemBoxes();
}
public InvoiceForm(int customerID)
{
InitializeComponent();
finished = false;
this.customerID = customerID;
customer = CustomerDatabase.SearchCustomersByID(customerID);
ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(customer.Province);
if (provinceTax.pst == 0)
{
PST = false;
}
else
{
PST = true;
}
panel1.Location = new Point(30, 145);
panel1.Size = new Size(800, 330);
panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
panel1.AutoScroll = true;
panel1.BackColor = Color.DarkGray;
this.Controls.Add(panel1);
AddLabels(customerID);
AddTotalBoxes(customerID);
AddFirstRow();
}
internal static List <ProvinceTax> GetAllProvinces()
{
List <ProvinceTax> provinceTaxList = new List <ProvinceTax>();
MySqlConnection conn = new MySqlConnection(LoginInfo.LoginCreds);
try
{
conn.Open();
MySqlCommand cmd;
MySqlDataReader rdr;
string sql;
sql = "SELECT * FROM ProvinceTax";
cmd = new MySqlCommand(sql, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ProvinceTax temp = new ProvinceTax(rdr[0].ToString(), Int32.Parse(rdr[1].ToString()), Int32.Parse(rdr[2].ToString()));
provinceTaxList.Add(temp);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
return(provinceTaxList);
}
internal static ProvinceTax GetProvinceByName(string province)
{
MySqlConnection conn = new MySqlConnection(LoginInfo.LoginCreds);
try
{
conn.Open();
MySqlCommand cmd;
MySqlDataReader rdr;
string sql;
sql = "SELECT * FROM ProvinceTax WHERE Province = '" + province + "';";
cmd = new MySqlCommand(sql, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ProvinceTax temp = new ProvinceTax(rdr[0].ToString(), Int32.Parse(rdr[1].ToString()), Int32.Parse(rdr[2].ToString()));
conn.Close();
return(temp);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
return(null);
}
private void SubtotalAmount_TextChanged(object sender, EventArgs e)
{
Customer c = CustomerDatabase.SearchCustomersByID(customerID);
ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(c.Province);
float gstRate = (float)provinceTax.gst / 100;
this.Controls["gst"].Text = (Single.Parse(this.Controls["subTotalAmount"].Text) * gstRate).ToString("0.00");
this.Controls["invoiceTotal"].Text = (Single.Parse(this.Controls["subTotalAmount"].Text) * (1 + gstRate)).ToString("0.00");
if (PST)
{
float pstRate = (float)provinceTax.pst / 100;
this.Controls["pst"].Text = (Single.Parse(this.Controls["subTotalAmount"].Text) * pstRate).ToString("0.00");
this.Controls["invoiceTotal"].Text = (Single.Parse(this.Controls["subTotalAmount"].Text) * (1 + gstRate + pstRate)).ToString("0.00");
}
}
private void AddLabels(int customerID)
{
int x = 30;
int y = 120;
Customer cust = CustomerDatabase.SearchCustomersByID(customerID);
ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(cust.Province);
//customer labels
Label storeNameLabel = new Label();
if (cust.StoreDetails.Length != 0)
{
storeNameLabel.Text = "Store name: " + cust.StoreName + " - " + cust.StoreDetails;
}
else
{
storeNameLabel.Text = "Store name: " + cust.StoreName;
}
storeNameLabel.Location = new Point(30, 10);
storeNameLabel.AutoSize = true;
this.Controls.Add(storeNameLabel);
Label officeLabel = new Label();
officeLabel.Text = "Billing Address: " + cust.BillingAddress;
officeLabel.Location = new Point(30, 25);
officeLabel.AutoSize = true;
this.Controls.Add(officeLabel);
Label shippingLabel = new Label();
shippingLabel.Text = "Shipping Address: " + cust.ShippingAddress;
shippingLabel.Location = new Point(30, 40);
shippingLabel.AutoSize = true;
this.Controls.Add(shippingLabel);
Label contactLabel = new Label();
contactLabel.Text = "Store Contact: " + cust.StoreContact;
contactLabel.Location = new Point(30, 55);
contactLabel.AutoSize = true;
this.Controls.Add(contactLabel);
Label emailLabel = new Label();
emailLabel.Text = "Email: " + cust.Email;
emailLabel.Location = new Point(30, 70);
emailLabel.AutoSize = true;
this.Controls.Add(emailLabel);
Label phoneLabel = new Label();
phoneLabel.Text = "Phone: " + cust.PhoneNumber;
phoneLabel.Location = new Point(500, 10);
phoneLabel.AutoSize = true;
this.Controls.Add(phoneLabel);
Label provinceLabel = new Label();
provinceLabel.Text = "Province Tax: " + cust.Province + " - GST/PST(" + provinceTax.gst + "%/" + provinceTax.pst + "%)";
provinceLabel.Location = new Point(500, 25);
provinceLabel.AutoSize = true;
this.Controls.Add(provinceLabel);
Label paymentLabel = new Label();
paymentLabel.Text = "Payment Terms: " + cust.PaymentTerms;
paymentLabel.Location = new Point(500, 40);
paymentLabel.AutoSize = true;
this.Controls.Add(paymentLabel);
Label shippingInstructionsLabel = new Label();
shippingInstructionsLabel.Text = "Shipping Instructions: " + cust.ShippingInstructions;
shippingInstructionsLabel.Location = new Point(500, 55);
shippingInstructionsLabel.AutoSize = true;
this.Controls.Add(shippingInstructionsLabel);
Label invoiceIDLabel = new Label();
invoiceIDLabel.Text = "Local Invoice ID: " + invoice.InvoiceID;
invoiceIDLabel.Location = new Point(500, 70);
invoiceIDLabel.AutoSize = true;
this.Controls.Add(invoiceIDLabel);
Label purchaseOrderLabel = new Label();
purchaseOrderLabel.Text = "PO#:" + invoice.PurchaseOrder;
purchaseOrderLabel.Location = new Point(30, 85);
purchaseOrderLabel.AutoSize = true;
this.Controls.Add(purchaseOrderLabel);
Label invoiceSpecialNotesLabel = new Label();
invoiceSpecialNotesLabel.Text = "Special Notes: " + invoice.SpecialNotes;
invoiceSpecialNotesLabel.Location = new Point(180, 85);
invoiceSpecialNotesLabel.AutoSize = true;
this.Controls.Add(invoiceSpecialNotesLabel);
Label invoiceNumberLabel = new Label();
invoiceNumberLabel.Text = "Invoice #: " + invoice.InvoiceNo;
invoiceNumberLabel.Location = new Point(700, 10);
invoiceNumberLabel.AutoSize = true;
this.Controls.Add(invoiceNumberLabel);
Label backorderInvoiceNotesLabel = new Label();
backorderInvoiceNotesLabel.Text = "Backorder Invoice Notes: " + invoice.BackorderNotes;
backorderInvoiceNotesLabel.Location = new Point(30, 100);
backorderInvoiceNotesLabel.AutoSize = true;
this.Controls.Add(backorderInvoiceNotesLabel);
//Invoice column headers
Label qtyLabel = new Label();
qtyLabel.Text = "Qty";
qtyLabel.Location = new Point(x, y);
qtyLabel.AutoSize = true;
qtyLabel.BackColor = System.Drawing.Color.LightGray;
this.Controls.Add(qtyLabel);
Label itemNoLabel = new Label();
itemNoLabel.Text = "Item Number";
itemNoLabel.Location = new Point(x + 50, y);
itemNoLabel.AutoSize = true;
itemNoLabel.BackColor = System.Drawing.Color.LightGray;
this.Controls.Add(itemNoLabel);
Label locLabel = new Label();
locLabel.Text = "Location";
locLabel.Location = new Point(x + 170, y);
locLabel.AutoSize = true;
locLabel.BackColor = System.Drawing.Color.LightGray;
this.Controls.Add(locLabel);
Label descLabel = new Label();
descLabel.Text = "Description";
descLabel.Location = new Point(x + 240, y);
descLabel.AutoSize = true;
descLabel.BackColor = System.Drawing.Color.LightGray;
this.Controls.Add(descLabel);
Label cartonLabel = new Label();
cartonLabel.Text = "Pack";
cartonLabel.Location = new Point(x + 460, y);
cartonLabel.AutoSize = true;
cartonLabel.BackColor = System.Drawing.Color.LightGray;
this.Controls.Add(cartonLabel);
Label costLabel = new Label();
costLabel.Text = "Cost";
costLabel.Location = new Point(x + 510, y);
costLabel.AutoSize = true;
costLabel.BackColor = System.Drawing.Color.LightGray;
this.Controls.Add(costLabel);
Label amountLabel = new Label();
amountLabel.Text = "Amount";
amountLabel.Location = new Point(x + 580, y);
amountLabel.AutoSize = true;
amountLabel.BackColor = System.Drawing.Color.LightGray;
this.Controls.Add(amountLabel);
Label specialNotesLabel = new Label();
specialNotesLabel.Text = "Special Notes";
specialNotesLabel.Location = new Point(x + 640, y);
specialNotesLabel.AutoSize = true;
specialNotesLabel.BackColor = System.Drawing.Color.LightGray;
this.Controls.Add(specialNotesLabel);
Label backorderLabel = new Label();
backorderLabel.Text = "B.O.";
backorderLabel.Location = new Point(x + 790, y);
backorderLabel.AutoSize = true;
backorderLabel.BackColor = System.Drawing.Color.LightGray;
this.Controls.Add(backorderLabel);
Button cancelButton = new Button();
cancelButton.Location = new Point(720, 620);
cancelButton.Size = new Size(50, 25);
cancelButton.Text = "Cancel";
cancelButton.Click += CancelButton_Click;
this.Controls.Add(cancelButton);
Button okButton = new Button();
okButton.Location = new Point(780, 620);
okButton.Size = new Size(50, 25);
okButton.Text = "OK";
okButton.Click += OkButton_Click;
this.Controls.Add(okButton);
Button printButton = new Button();
printButton.Location = new Point(665, 620);
printButton.Size = new Size(50, 25);
printButton.Text = "Print";
printButton.Click += PrintButton_Click;
this.Controls.Add(printButton);
}
private void AddTotalBoxes(int customerID)
{
Customer cust = CustomerDatabase.SearchCustomersByID(customerID);
ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(cust.Province);
Label subtotalLabel = new Label();
subtotalLabel.Text = "Subtotal";
subtotalLabel.Location = new Point(562, 500);
subtotalLabel.AutoSize = true;
this.Controls.Add(subtotalLabel);
TextBox subtotalAmount = new TextBox();
subtotalAmount.Location = new Point(610, 500);
subtotalAmount.Size = new Size(50, 25);
subtotalAmount.ReadOnly = true;
subtotalAmount.Name = "subtotalAmount";
subtotalAmount.AccessibleName = "subtotalAmount";
subtotalAmount.TextChanged += SubtotalAmount_TextChanged;
this.Controls.Add(subtotalAmount);
Label gstLabel = new Label();
gstLabel.Text = "GST " + provinceTax.gst + "%";
gstLabel.Location = new Point(560, 530);
gstLabel.Size = new Size(50, 25);
gstLabel.TextAlign = ContentAlignment.TopRight;
this.Controls.Add(gstLabel);
TextBox gst = new TextBox();
gst.Location = new Point(610, 530);
gst.Size = new Size(50, 25);
gst.ReadOnly = true;
gst.Name = "gst";
gst.AccessibleName = "gst";
this.Controls.Add(gst);
if (PST)
{
Label pstLabel = new Label();
pstLabel.Text = "PST " + provinceTax.pst + "%";
pstLabel.Location = new Point(560, 560);
pstLabel.Size = new Size(50, 25);
pstLabel.TextAlign = ContentAlignment.TopRight;
this.Controls.Add(pstLabel);
TextBox pst = new TextBox();
pst.Location = new Point(610, 560);
pst.Size = new Size(50, 25);
pst.ReadOnly = true;
pst.Name = "pst";
pst.AccessibleName = "pst";
this.Controls.Add(pst);
Label invoiceTotalLabel = new Label();
invoiceTotalLabel.Text = "Invoice Total";
invoiceTotalLabel.Location = new Point(540, 590);
invoiceTotalLabel.AutoSize = true;
this.Controls.Add(invoiceTotalLabel);
TextBox invoiceTotal = new TextBox();
invoiceTotal.Location = new Point(610, 590);
invoiceTotal.Size = new Size(50, 25);
invoiceTotal.ReadOnly = true;
invoiceTotal.Name = "invoiceTotal";
invoiceTotal.AccessibleName = "invoiceTotal";
this.Controls.Add(invoiceTotal);
}
else //no pst
{
Label invoiceTotalLabel = new Label();
invoiceTotalLabel.Text = "Invoice Total";
invoiceTotalLabel.Location = new Point(540, 560);
invoiceTotalLabel.AutoSize = true;
this.Controls.Add(invoiceTotalLabel);
TextBox invoiceTotal = new TextBox();
invoiceTotal.Location = new Point(610, 560);
invoiceTotal.Size = new Size(50, 25);
invoiceTotal.ReadOnly = true;
invoiceTotal.Name = "invoiceTotal";
invoiceTotal.AccessibleName = "invoiceTotal";
this.Controls.Add(invoiceTotal);
}
}
private void PrintInvoiceProgress_Load(object sender, EventArgs e)
{
// Creating File directory & save files
Warning[] warnings;
string[] streamids;
string mimeType, encoding, extension;
string ExcelFileName = @"C:\Invoices\Excel\" + _invoice.InvoiceID + ".xlsx";
string PDFFileName = @"C:\Invoices\PDF\" + _invoice.InvoiceID + ".pdf";
Directory.CreateDirectory(Path.GetDirectoryName(ExcelFileName));
Directory.CreateDirectory(Path.GetDirectoryName(PDFFileName));
//Init data source for tables
InvoiceItemDetailBindingSource.DataSource = _list;
String address = _invoice.CustomerAddress;
String[] words = address.Split(',');
String customerStreet = words[0].TrimStart();
String customerProvince = words[1].TrimStart();
String customerPostal = words[2].TrimStart();
ProvinceTax provinceTax = ProvinceTaxDatabase.GetProvinceByName(_invoice.customer.Province);
// Create & Set report parameter data
Microsoft.Reporting.WinForms.ReportParameter[] p = new Microsoft.Reporting.WinForms.ReportParameter[]
{
new Microsoft.Reporting.WinForms.ReportParameter("pCompanyName", _invoice.CompanyName),
new Microsoft.Reporting.WinForms.ReportParameter("pCompanyAddress", _invoice.CompanyAddress),
new Microsoft.Reporting.WinForms.ReportParameter("pCompanyPhoneNumber", _invoice.CompanyPhoneNumber),
new Microsoft.Reporting.WinForms.ReportParameter("pCompanyFax", _invoice.CompanyFax),
new Microsoft.Reporting.WinForms.ReportParameter("pCompanyTollFree", _invoice.CompanyTollFree),
new Microsoft.Reporting.WinForms.ReportParameter("pInvoiceNumber", _invoice.InvoiceNo.ToString()),
new Microsoft.Reporting.WinForms.ReportParameter("pInvoiceID", _invoice.InvoiceID.ToString()),
new Microsoft.Reporting.WinForms.ReportParameter("pStoreName", _invoice.CustomerName),
new Microsoft.Reporting.WinForms.ReportParameter("pStoreContact", _invoice.CustomerContact),
new Microsoft.Reporting.WinForms.ReportParameter("pTerms", _invoice.CustomerTerms),
new Microsoft.Reporting.WinForms.ReportParameter("pShippingTerms", _invoice.CustomerShippingTerms),
new Microsoft.Reporting.WinForms.ReportParameter("pPurchaseOrder", _invoice.PurchaseOrder),
new Microsoft.Reporting.WinForms.ReportParameter("pSpecialNotes", _invoice.SpecialNotes),
new Microsoft.Reporting.WinForms.ReportParameter("pStoreStreet", customerStreet),
new Microsoft.Reporting.WinForms.ReportParameter("pStoreProvince", customerProvince),
new Microsoft.Reporting.WinForms.ReportParameter("pStorePostal", customerPostal),
new Microsoft.Reporting.WinForms.ReportParameter("pStorePhone", _invoice.CustomerPhone),
new Microsoft.Reporting.WinForms.ReportParameter("pSubtotal", _invoice.SubTotal.ToString()),
new Microsoft.Reporting.WinForms.ReportParameter("pGSTPercent", provinceTax.gst.ToString()),
new Microsoft.Reporting.WinForms.ReportParameter("pGST", _invoice.Gst.ToString()),
new Microsoft.Reporting.WinForms.ReportParameter("pPSTPercent", provinceTax.pst.ToString()),
new Microsoft.Reporting.WinForms.ReportParameter("pPST", _invoice.Pst.ToString()),
new Microsoft.Reporting.WinForms.ReportParameter("pTotal", _invoice.NetTotal.ToString()),
new Microsoft.Reporting.WinForms.ReportParameter("pGSTno", _invoice.GSTNo),
new Microsoft.Reporting.WinForms.ReportParameter("pBackorderNotes", _invoice.BackorderNotes),
new Microsoft.Reporting.WinForms.ReportParameter("pSalesRep", _invoice.customer.Rep),
new Microsoft.Reporting.WinForms.ReportParameter("pBackorderNotes", _invoice.BackorderNotes),
new Microsoft.Reporting.WinForms.ReportParameter("pFreight", _invoice.freight.ToString()),
};
this.reportViewer1.LocalReport.SetParameters(p);
this.reportViewer1.RefreshReport();
// Create Excel
FileStream newFile = new FileStream(ExcelFileName, FileMode.Create);
string renderFormat = (ExcelFileName.EndsWith(".xlsx") ? "EXCELOPENXML" : "Excel");
byte[] bytes = this.reportViewer1.LocalReport.Render(renderFormat, null, out mimeType, out encoding, out extension, out streamids, out warnings);
newFile.Write(bytes, 0, bytes.Length);
newFile.Close();
// Create PDF
Byte[] mybytes = this.reportViewer1.LocalReport.Render("PDF");
using (FileStream fs = File.Create(PDFFileName))
{
fs.Write(mybytes, 0, mybytes.Length);
}
}
internal void SaveToExcel()
{
int rows = 0;
int tallRow = 14;
int medRow = 12;
IWorkbook wb = new XSSFWorkbook();
IRow row;
ICell cell;
wsheet = wb.CreateSheet("Sheet1");
IFont font;
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
ICellStyle DefaultStyle = wb.CreateCellStyle();
DefaultStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
DefaultStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 14;
font.FontName = "Arial";
font.IsBold = true;
ICellStyle TitleStyle = wb.CreateCellStyle();
TitleStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 12;
font.FontName = "Arial";
font.IsBold = true;
ICellStyle InvoiceStyle = wb.CreateCellStyle();
InvoiceStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 8;
font.FontName = "Arial";
ICellStyle SmallStyle = wb.CreateCellStyle();
SmallStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
ICellStyle MoneyStyle = wb.CreateCellStyle();
MoneyStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
MoneyStyle.SetFont(font);
MoneyStyle.DataFormat = 2;
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
font.IsBold = true;
ICellStyle MoneyBoldStyle = wb.CreateCellStyle();
MoneyBoldStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
MoneyBoldStyle.SetFont(font);
MoneyBoldStyle.DataFormat = 2;
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
font.IsBold = true;
ICellStyle BoldStyle = wb.CreateCellStyle();
BoldStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
font.IsBold = true;
ICellStyle RightAlignBoldStyle = wb.CreateCellStyle();
RightAlignBoldStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
RightAlignBoldStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
ICellStyle RightAlignStyle = wb.CreateCellStyle();
RightAlignStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
RightAlignStyle.SetFont(font);
wsheet.SetColumnWidth(0, CharW * 5);
wsheet.SetColumnWidth(1, CharW * 3);
wsheet.SetColumnWidth(2, CharW * 3);
wsheet.SetColumnWidth(3, CharW * 8);
wsheet.SetColumnWidth(4, CharW * 7);
wsheet.SetColumnWidth(5, CharW * 20);
wsheet.SetColumnWidth(6, CharW * 2 + 100);
wsheet.SetColumnWidth(7, CharW * 2 + 100);
wsheet.SetColumnWidth(8, CharW * 6);
wsheet.SetColumnWidth(9, CharW * 9);
wsheet.SetColumnWidth(10, CharW);
wsheet.DefaultRowHeight = 250;
row = wsheet.CreateRow(rows++);
row.HeightInPoints = 15;
cell = row.CreateCell(0);
cell.CellStyle = TitleStyle;
cell.SetCellValue("GREAT WEST WHOLESALE LTD");
cell = row.CreateCell(8);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(InvoiceID);
cell = row.CreateCell(9);
cell.CellStyle = SmallStyle;
cell.SetCellValue("gst no. R102186178");
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(0);
cell.CellStyle = BoldStyle;
cell.SetCellValue("1670 PANDORA ST. VANCOUVER, BC V5L 1L6");
cell = row.CreateCell(9);
cell.CellStyle = InvoiceStyle;
cell.SetCellValue("INVOICE");
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(0);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("604-255-9588 fax 604-255-9589 1-800-901-9588 [email protected]");
rows++;
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(0);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("Ship to:");
cell = row.CreateCell(3);
cell.CellStyle = BoldStyle;
cell.SetCellValue(customer.StoreName);
cell = row.CreateCell(6);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("INVOICE NO.");
cell = row.CreateCell(9);
cell.CellStyle = BoldStyle;
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
if (customer.StoreDetails.Length != 0)
{
cell.SetCellValue(customer.StoreDetails);
}
else
{
cell.SetCellValue(customer.StreetAddress);
}
cell = row.CreateCell(6);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("DATE:");
cell = row.CreateCell(9);
cell.CellStyle = DefaultStyle;
//Char[] delim = { ' ' };
//String[] date = System.DateTime.Today.ToLongDateString().Split(delim, 2);
//String zz = date[1].ToUpper();
cell.SetCellValue(System.DateTime.Today.ToLongDateString().ToUpper());
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
if (customer.StoreDetails.Length != 0)
{
cell.SetCellValue(customer.StreetAddress);
}
else
{
cell.SetCellValue(customer.CityAddress + ", " + customer.ProvinceAddress + " " + customer.PostalCodeAddress);
}
cell = row.CreateCell(6);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("TERMS:");
cell = row.CreateCell(9);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(customer.PaymentTerms);
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
if (customer.StoreDetails.Length != 0)
{
cell.SetCellValue(customer.CityAddress + ", " + customer.ProvinceAddress + " " + customer.PostalCodeAddress);
}
else
{
cell.SetCellValue("tel: " + customer.PhoneNumber);
}
cell = row.CreateCell(6);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("SHIP:");
cell = row.CreateCell(9);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(customer.ShippingInstructions);
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
if (customer.StoreDetails.Length != 0)
{
cell.SetCellValue("tel: " + customer.PhoneNumber);
}
else
{
if (customer.Rep.Length != 0)
{
cell.CellStyle = BoldStyle;
cell.SetCellValue("rep: " + customer.Rep);
}
}
cell = row.CreateCell(6);
cell.CellStyle = BoldStyle;
cell.SetCellValue("PURCHASE ORDER: PO " + PurchaseOrder);
if (customer.StoreDetails.Length != 0)
{
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
if (customer.Rep.Length != 0)
{
cell = row.CreateCell(3);
cell.CellStyle = BoldStyle;
cell.SetCellValue("rep: " + customer.Rep);
}
}
else
{
rows++;
}
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(1);
cell.CellStyle = InvoiceStyle;
cell.SetCellValue(" ** " + InvoiceTemplateNoteDatabase.GetNote());
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(1);
cell.CellStyle = InvoiceStyle;
cell.SetCellValue(" " + customer.StoreSpecialNotes);
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(0);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("QUANTITY");
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("ITEM NO.");
cell = row.CreateCell(5);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("DESCRIPTION");
cell = row.CreateCell(8);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("COST");
cell = row.CreateCell(9);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("AMOUNT");
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(0);
cell.CellStyle = BoldStyle;
cell.SetCellValue("-------------------------------------------------------------------------------------------------------------------");
int itemCount = Items.Count;
/////////////////////////////////////////////////////////////////////////////////////////////////////////
Items = Items.OrderBy(o => o.Location).ToList();
for (int i = 0; i < itemCount; i++)
{
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(0);
cell.CellStyle = RightAlignStyle;
cell.SetCellValue(Items[i].Quantity);
cell = row.CreateCell(9);
cell.CellStyle = MoneyStyle;
cell.SetCellFormula("ROUND(A" + rows + "*I" + rows + ",2)");
if (Items[i].Quantity / Items[i].PerCarton >= 1)
{
cell = row.CreateCell(2);
cell.CellStyle = DefaultStyle;
//cell.SetCellFormula("ROUND(" + Items[i].Quantity + "/" + Items[i].PerCarton + ",2)");
cell.SetCellValue(((Items[i].Quantity * 1.0) / (Items[i].PerCarton * 1.0)).ToString("0.0"));
}
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(Items[i].ItemNo);
cell = row.CreateCell(4);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(Items[i].Location);
cell = row.CreateCell(5);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(Items[i].ItemDesc);
cell = row.CreateCell(7);
cell.CellStyle = RightAlignStyle;
cell.SetCellValue(Items[i].PerCarton);
cell = row.CreateCell(8);
cell.CellStyle = MoneyStyle;
cell.SetCellValue(Items[i].SellPrice);
if (Items[i].SpecialNotes.Length != 0)
{
cell = row.CreateCell(11);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(Items[i].SpecialNotes);
}
}
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(9);
cell.CellStyle = RightAlignBoldStyle;
cell.SetCellValue("---------------");
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(9);
cell.CellStyle = MoneyBoldStyle;
cell.SetCellFormula(String.Format("SUM(J15:J" + (rows - 2) + ")"));
ProvinceTax pt = ProvinceTaxDatabase.GetProvinceByName(customer.Province);
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(5);
cell.CellStyle = BoldStyle;
cell.SetCellValue("GST " + pt.gst + "%");
cell = row.CreateCell(9);
cell.CellStyle = MoneyBoldStyle;
cell.SetCellFormula("ROUND(J" + (rows - 1) + "*" + (pt.gst / 100.0) + ",2)");
//cell.SetCellFormula(String.Format("J" + (rows - 1) + " * " + (pt.gst/ 100.0)));
if (pt.pst != 0)
{
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(5);
cell.CellStyle = BoldStyle;
cell.SetCellValue("PST " + pt.pst + "%");
cell = row.CreateCell(9);
cell.CellStyle = MoneyBoldStyle;
cell.SetCellFormula("ROUND(J" + (rows - 2) + "*" + (pt.pst / 100.0) + ",2)");
//cell.SetCellFormula(String.Format("J" + (rows - 2) + " * " + (pt.pst / 100.0)));
}
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(9);
cell.CellStyle = RightAlignBoldStyle;
cell.SetCellValue("---------------");
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(5);
cell.CellStyle = BoldStyle;
cell.SetCellValue("INVOICE TOTAL");
cell = row.CreateCell(9);
cell.CellStyle = MoneyBoldStyle;
if (pt.pst != 0)
{
cell.SetCellFormula(String.Format("SUM(J" + (rows - 4) + ":J" + (rows - 2) + ")"));
}
else
{
cell.SetCellFormula(String.Format("SUM(J" + (rows - 3) + ":J" + (rows - 2) + ")"));
}
int rowEnd = 400;
int lastColumn = 25;
IRow r;
for (int rowNum = 0; rowNum < rows; rowNum++)
{
if ((r = wsheet.GetRow(rowNum)) != null)
{
for (int cn = 0; cn < lastColumn; cn++)
{
if ((cell = r.GetCell(cn)) == null)
{
cell = r.CreateCell(cn);
cell.CellStyle = DefaultStyle;
}
}
}
else
{
r = wsheet.CreateRow(rowNum); //getRow(rowNum);
r.HeightInPoints = medRow;
for (int cn = 0; cn < lastColumn; cn++)
{
cell = r.CreateCell(cn);
cell.CellStyle = DefaultStyle;
}
}
}
for (int rowNum = rows; rowNum < rowEnd; rowNum++)
{
r = wsheet.CreateRow(rowNum); //getRow(rowNum);
r.HeightInPoints = medRow;
for (int cn = 0; cn < lastColumn; cn++)
{
cell = r.CreateCell(cn);
cell.CellStyle = DefaultStyle;
}
}
string deskPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
FileStream stream = new FileStream(@"" + deskPath + "\\Invoices\\Progress\\" + InvoiceID + customer.StoreName + ".xlsx", FileMode.Create, FileAccess.Write);
wb.Write(stream);
FileStream stream2 = new FileStream(@"" + deskPath + "\\Invoices\\Original\\" + InvoiceID + customer.StoreName + ".xlsx", FileMode.Create, FileAccess.Write);
wb.Write(stream2);
PrintExcel();
}