private void createSummary(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Set named range summary values
Summary summary = global::Argix.Finance.Globals.Summary;
//Header
summary.SumBillToName.Value = invoiceTable[0].BillToName.Trim();
summary.SumBillToAddress.Value = invoiceTable[0].BillToAddressline1.Trim() + ((!invoiceTable[0].IsBillToAddressline2Null() && invoiceTable[0].BillToAddressline2.Trim().Length > 0) ? invoiceTable[0].BillToAddressline2.Trim() : "");
summary.SumBillToCityStateZip.Value = invoiceTable[0].BillToCity.Trim() + ", " + invoiceTable[0].BillToState.Trim() + " " + invoiceTable[0].BillToZip.Trim();
summary.SumInvoice.Value = "Invoice #: " + invoiceTable[0].InvoiceNumber.Trim();
summary.SumManifests.Value = "Manifest #: " + invoiceTable[0].ManifestList.Trim();
summary.SumInvoiceDate.Value = "Invoice Date: " + invoiceTable[0].InvoiceDate.ToString("MMMM dd, yyyy");
//Body
int cartons = 0, weight = 0;
decimal charges = 0;
for (int i = 0; i < invoiceTable.Rows.Count; i++)
{
cartons += invoiceTable[i].CtnQty;
weight += invoiceTable[i].Weight;
charges += invoiceTable[i].DeliveryTotal;
}
summary.SumReleaseDate.Value = invoiceTable[0].ReleaseDate.ToShortDateString();
summary.SumCartons.Value = cartons;
summary.SumWeight.Value = weight;
summary.SumTotalCharge.Value = charges;
//Remit To
summary.SumRemitToName.Value = invoiceTable[0].RemitToName.Trim();
summary.SumRemitToAddress.Value = invoiceTable[0].RemitToAddressLine1.Trim();
summary.SumRemitToCityStateZip.Value = invoiceTable[0].RemitToCity.Trim() + ", " + invoiceTable[0].RemitToState.Trim() + " " + invoiceTable[0].RemitToZip.Trim();
}
private void showDetail(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Get worksheet
Sheet1 ws = global::Argix.Finance.Globals.Sheet1;
Application.ScreenUpdating = false;
//Insert a row at row0 + 1 (pushes down) for every row of data
int rowCount = invoiceTable.Rows.Count;
Excel.Range row0 = ws.Range[ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, 104]].EntireRow;
for (int i = 0; i < rowCount - 1; i++)
{
row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
}
//Populate entire data table into a range of worksheet cells
object[,] values = new object[rowCount, 104];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < 104; j++)
{
values[i, j] = invoiceTable[i][j + 1];
}
}
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 104]].Value2 = values;
Application.ScreenUpdating = true;
}
private void createDetailTotals(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Get worksheet
Detail ws = global::Argix.Finance.Globals.Detail;
//Totals
int rowCount = invoiceTable.Rows.Count;
object[,] totals = new object[1, 12];
int pcs = 0, weight = 0;
decimal total = 0.0M, charges = 0.0M, fuel = 0.0M, delivery = 0.0M;
bool start = false; //Flag to skip linehaul charge (i.e. first row); turned on per Maryann 07/24/13
for (int i = 0; i < rowCount; i++)
{
//if(start) {
pcs += !invoiceTable[i].IsPCSNull() ? invoiceTable[i].PCS : 0;
weight += !invoiceTable[i].IsWEIGHTNull() ? invoiceTable[i].WEIGHT : 0;
total += !invoiceTable[i].IsTotalNull() ? invoiceTable[i].Total : 0.0M;
charges += !invoiceTable[i].IsSpecialChargesNull() ? invoiceTable[i].SpecialCharges : 0.0M;
fuel += !invoiceTable[i].IsFuelNull() ? invoiceTable[i].Fuel : 0.0M;
delivery += !invoiceTable[i].IsDeliveryNull() ? invoiceTable[i].Delivery : 0.0M;
//}
if (!start)
{
start = invoiceTable[i].IsShipDateNull();
}
}
totals[0, 0] = totals[0, 1] = totals[0, 2] = "";
totals[0, 3] = pcs;
totals[0, 4] = weight;
totals[0, 5] = totals[0, 6] = "";
totals[0, 7] = total;
totals[0, 8] = charges;
totals[0, 9] = fuel;
totals[0, 10] = delivery;
totals[0, 11] = "";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 1], ws.Cells[ROW0_DETAIL + rowCount + 0, 12]].Value2 = totals;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 4], ws.Cells[ROW0_DETAIL + rowCount + 0, 4]].NumberFormat = "#,###_);(#,###)";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 4], ws.Cells[ROW0_DETAIL + rowCount + 0, 4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 4], ws.Cells[ROW0_DETAIL + rowCount + 0, 4]].Font.Bold = true;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 5], ws.Cells[ROW0_DETAIL + rowCount + 0, 5]].NumberFormat = "#,##0_);(#,##0)";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 5], ws.Cells[ROW0_DETAIL + rowCount + 0, 5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 5], ws.Cells[ROW0_DETAIL + rowCount + 0, 5]].Font.Bold = true;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 8], ws.Cells[ROW0_DETAIL + rowCount + 0, 8]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 8], ws.Cells[ROW0_DETAIL + rowCount + 0, 8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 8], ws.Cells[ROW0_DETAIL + rowCount + 0, 8]].Font.Bold = true;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 9], ws.Cells[ROW0_DETAIL + rowCount + 0, 9]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 9], ws.Cells[ROW0_DETAIL + rowCount + 0, 9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 9], ws.Cells[ROW0_DETAIL + rowCount + 0, 9]].Font.Bold = true;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 10], ws.Cells[ROW0_DETAIL + rowCount + 0, 10]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 10], ws.Cells[ROW0_DETAIL + rowCount + 0, 10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 10], ws.Cells[ROW0_DETAIL + rowCount + 0, 10]].Font.Bold = true;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 11], ws.Cells[ROW0_DETAIL + rowCount + 0, 11]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 11], ws.Cells[ROW0_DETAIL + rowCount + 0, 11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 0, 11], ws.Cells[ROW0_DETAIL + rowCount + 0, 11]].Font.Bold = true;
}
private void createDetailBody(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Create body of detail worksheet
Detail ws = global::Argix.Finance.Globals.Detail;
Application.ScreenUpdating = false;
//Insert a row at row0 + 1 (pushes down) for every row of data
int rowCount = invoiceTable.Rows.Count;
Excel.Range row0 = ws.Range[ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, 12]].EntireRow;
for (int i = 0; i < rowCount - 1; i++)
{
row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
}
//Populate entire data table into a range of worksheet cells
object[,] values = new object[rowCount, 12];
for (int i = 0; i < rowCount; i++)
{
if (!invoiceTable[i].IsShipDateNull())
{
values[i, 0] = (!invoiceTable[i].IsShipDateNull() ? invoiceTable[i].ShipDate.ToString("MM/dd/yyyy") : "");
values[i, 1] = "'" + (!invoiceTable[i].IsNoColumnNameNull() ? invoiceTable[i].NoColumnName.ToString() : "");
values[i, 2] = "'" + (!invoiceTable[i].IsPRONull() ? invoiceTable[i].PRO.Trim() : "");
values[i, 3] = (!invoiceTable[i].IsPCSNull() ? invoiceTable[i].PCS : 0);
values[i, 4] = (!invoiceTable[i].IsWEIGHTNull() ? invoiceTable[i].WEIGHT : 0);
values[i, 5] = "'" + (!invoiceTable[i].IsCONSIGNEENull() ? invoiceTable[i].CONSIGNEE.Trim() : "");
values[i, 6] = (!invoiceTable[i].Is_CITY_STNull() ? invoiceTable[i]._CITY_ST.Trim() : "");
values[i, 7] = (!invoiceTable[i].IsTotalNull() ? invoiceTable[i].Total : 0);
values[i, 8] = (!invoiceTable[i].IsSpecialChargesNull() ? invoiceTable[i].SpecialCharges : 0);
values[i, 9] = (!invoiceTable[i].IsFuelNull() ? invoiceTable[i].Fuel : 0);
values[i, 10] = (!invoiceTable[i].IsDeliveryNull() ? invoiceTable[i].Delivery : 0);
values[i, 11] = "'" + (!invoiceTable[i].IsPoolNull() ? invoiceTable[i].Pool.Trim() : "");
}
}
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 12]].Value2 = values;
ws.Range[ws.Cells[ROW0_DETAIL, 4], ws.Cells[ROW0_DETAIL + rowCount - 1, 4]].NumberFormat = "#,###_);(#,###)";
ws.Range[ws.Cells[ROW0_DETAIL, 4], ws.Cells[ROW0_DETAIL + rowCount - 1, 4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 5], ws.Cells[ROW0_DETAIL + rowCount - 1, 5]].NumberFormat = "#,###_);(#,###)";
ws.Range[ws.Cells[ROW0_DETAIL, 5], ws.Cells[ROW0_DETAIL + rowCount - 1, 5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 8], ws.Cells[ROW0_DETAIL + rowCount - 1, 8]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL, 8], ws.Cells[ROW0_DETAIL + rowCount - 1, 8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 9], ws.Cells[ROW0_DETAIL + rowCount - 1, 9]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL, 9], ws.Cells[ROW0_DETAIL + rowCount - 1, 9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 10], ws.Cells[ROW0_DETAIL + rowCount - 1, 10]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL, 10], ws.Cells[ROW0_DETAIL + rowCount - 1, 10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 11], ws.Cells[ROW0_DETAIL + rowCount - 1, 11]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL, 11], ws.Cells[ROW0_DETAIL + rowCount - 1, 11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
Application.ScreenUpdating = true;
}
private void createDetailTotals(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Get worksheet
Detail ws = global::Argix.Finance.Globals.Detail;
//Totals
int rowCount = invoiceTable.Rows.Count;
object[,] totals = new object[1, 16];
int qty = 0, weight = 0;
decimal fs = 0.0M, dt = 0.0M;
for (int i = 0; i < rowCount; i++)
{
qty += invoiceTable[i].CtnQty;
weight += invoiceTable[i].Weight;
fs += invoiceTable[i].FuelSurcharge;
dt += invoiceTable[i].DeliveryTotal;
}
totals[0, 0] = "TOTAL " + rowCount.ToString() + " DELIVERIES";
totals[0, 1] = totals[0, 2] = totals[0, 3] = "";
totals[0, 4] = qty;
totals[0, 5] = totals[0, 6] = totals[0, 7] = "";
totals[0, 8] = weight;
totals[0, 9] = totals[0, 10] = totals[0, 11] = totals[0, 12] = totals[0, 13] = "";
totals[0, 14] = fs;
totals[0, 15] = dt;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 1, 1], ws.Cells[ROW0_DETAIL + rowCount + 1, 16]].Value2 = totals;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 1, 5], ws.Cells[ROW0_DETAIL + rowCount + 1, 5]].NumberFormat = "#,###_);(#,###)";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 1, 5], ws.Cells[ROW0_DETAIL + rowCount + 1, 5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 1, 9], ws.Cells[ROW0_DETAIL + rowCount + 1, 9]].NumberFormat = "#,##0_);(#,##0)";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 1, 9], ws.Cells[ROW0_DETAIL + rowCount + 1, 9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 1, 15], ws.Cells[ROW0_DETAIL + rowCount + 1, 15]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 1, 15], ws.Cells[ROW0_DETAIL + rowCount + 1, 15]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 1, 16], ws.Cells[ROW0_DETAIL + rowCount + 1, 16]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 1, 16], ws.Cells[ROW0_DETAIL + rowCount + 1, 16]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//Footer
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 3, 1], ws.Cells[ROW0_DETAIL + rowCount + 3, 16]].Value2 = new object[1, 16] {
{ "PLEASE REFERENCE INVOICE# " + invoiceTable[0].InvoiceNumber + " WHEN REMITTING PAYMENT I.C.C. REGULATIONS REQUIRE THAT THIS BILL BE PAID WITHIN " + invoiceTable[0].PaymentDays + " DAYS", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" }
};
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 4, 1], ws.Cells[ROW0_DETAIL + rowCount + 4, 16]].Value2 = new object[1, 16] {
{ "", "", "A SERVICE CHARGE OF 1.5% PER MONTH IS ADDED TO ALL PAST DUE INVOICES", "", "", "", "", "", "", "", "", "", "", "", "", "" }
};
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 5, 1], ws.Cells[ROW0_DETAIL + rowCount + 5, 16]].Value2 = new object[1, 16] {
{ "", "", "REMIT TO: " + invoiceTable[0].RemitToName.Trim() + " " + invoiceTable[0].RemitToAddressLine1.Trim() + " " + invoiceTable[0].RemitToAddressLine2.Trim() + " " + invoiceTable[0].RemitToCity.Trim() + ", " + invoiceTable[0].RemitToState + " " + invoiceTable[0].RemitToZip + "-" + invoiceTable[0].RemitToZip4, "", "", "", "", "", "", "", "", "", "", "", "", "" }
};
}
private void createDetailBody(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Create body of detail worksheet
//Excel.Worksheet ws = (Excel.Worksheet)this.Worksheets["Detail"];
Detail ws = global::Argix.Finance.Globals.Detail;
Application.ScreenUpdating = false;
//Insert a row at row0 + 1 (pushes down) for every row of data
int rowCount = invoiceTable.Rows.Count;
Excel.Range row0 = ws.Range[ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, 23]].EntireRow;
for (int i = 0; i < rowCount - 1; i++)
{
row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
}
//Populate entire data table into a range of worksheet cells
object[,] values = new object[rowCount, 23];
for (int i = 0; i < rowCount; i++)
{
values[i, 0] = "'" + invoiceTable[i].InvoiceNumber.Trim();
values[i, 1] = invoiceTable[i].InvoiceDate.ToString("MM/dd/yyyy");
values[i, 2] = "'" + invoiceTable[i].PRONumber.Trim();
values[i, 3] = "'" + (!invoiceTable[i].IsStoreNumberNull() ? invoiceTable[i].StoreNumber.ToString() : "");
values[i, 4] = "'" + invoiceTable[i].StoreName.Trim();
values[i, 5] = "'" + (!invoiceTable[i].IsStoreAddressLine1Null() ? invoiceTable[i].StoreAddressLine1.ToString() : "");
values[i, 6] = "'" + (!invoiceTable[i].IsStoreCityNull() ? invoiceTable[i].StoreCity.ToString() : "");
values[i, 7] = "'" + invoiceTable[i].StoreState.Trim();
values[i, 8] = "'" + invoiceTable[i].StoreZip.Trim();
values[i, 9] = invoiceTable[i].CtnQty;
values[i, 10] = invoiceTable[i].CartonRate;
values[i, 11] = invoiceTable[i].PltQty;
values[i, 12] = invoiceTable[i].PalletRate;
values[i, 13] = invoiceTable[i].Weight;
values[i, 14] = invoiceTable[i].RatedWeight;
values[i, 15] = invoiceTable[i].WeightRate;
values[i, 16] = invoiceTable[i].Surcharge;
values[i, 17] = invoiceTable[i].IsConsolidationChargeNull() ? 0.0m : invoiceTable[i].ConsolidationCharge;
values[i, 18] = invoiceTable[i].FuelRate;
values[i, 19] = invoiceTable[i].FuelSurcharge;
values[i, 20] = invoiceTable[i].DeliveryTotal;
values[i, 21] = "'" + invoiceTable[i].StoreBLNumber.Trim();
values[i, 22] = "'" + invoiceTable[i].MasterBLNumber.Trim();
}
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 23]].Value2 = values;
Application.ScreenUpdating = true;
}
private void createDetailBody(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Create body of detail worksheet
Excel.Worksheet ws = (Excel.Worksheet) this.Worksheets["Detail"];
Detail d = global::Argix.Finance.Globals.Detail;
Application.ScreenUpdating = false;
//Insert a row at row0 + 1 (pushes down) for every row of data
int rowCount = invoiceTable.Rows.Count;
Excel.Range row0 = ws.Range[ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, 21]].EntireRow;
for (int i = 0; i < rowCount - 1; i++)
{
row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
}
//Populate entire data table into a range of worksheet cells
object[,] values = new object[rowCount, 21];
for (int i = 0; i < rowCount; i++)
{
values[i, 0] = "'" + invoiceTable[i].LocationCode.Trim();
values[i, 1] = invoiceTable[i].ReleaseDate;
values[i, 2] = !invoiceTable[i].IsMercPCSNull() ? invoiceTable[i].MercPCS : 0;
values[i, 3] = !invoiceTable[i].IsMercWeightNull() ? invoiceTable[i].MercWeight : 0;
values[i, 4] = !invoiceTable[i].IsMercDeliveryChargeNull() ? invoiceTable[i].MercDeliveryCharge : 0;
values[i, 5] = !invoiceTable[i].IsMercFSCNull() ? invoiceTable[i].MercFSC : 0;
values[i, 6] = !invoiceTable[i].IsMercTotalDeliveryChargeNull() ? invoiceTable[i].MercTotalDeliveryCharge : 0;
values[i, 7] = !invoiceTable[i].IsMercCostPerCartonNull() ? invoiceTable[i].MercCostPerCarton : 0;
values[i, 8] = !invoiceTable[i].IsSupplyPCSNull() ? invoiceTable[i].SupplyPCS : 0;
values[i, 9] = !invoiceTable[i].IsSupplyWeightNull() ? invoiceTable[i].SupplyWeight : 0;
values[i, 10] = !invoiceTable[i].IsSupplyDeliveryChargeNull() ? invoiceTable[i].SupplyDeliveryCharge : 0;
values[i, 11] = !invoiceTable[i].IsSupplyFSCNull() ? invoiceTable[i].SupplyFSC : 0;
values[i, 12] = !invoiceTable[i].IsSupplyTotalDeliveryChargeNull() ? invoiceTable[i].SupplyTotalDeliveryCharge : 0;
values[i, 13] = !invoiceTable[i].IsSupplyCostPerCartonNull() ? invoiceTable[i].SupplyCostPerCarton : 0;
values[i, 14] = !invoiceTable[i].IsPCSNull() ? invoiceTable[i].PCS : 0;
values[i, 15] = !invoiceTable[i].IsWeightNull() ? invoiceTable[i].Weight : 0;
values[i, 16] = !invoiceTable[i].IsDeliveryChargeNull() ? invoiceTable[i].DeliveryCharge : 0;
values[i, 17] = !invoiceTable[i].IsFuelSurchargeNull() ? invoiceTable[i].FuelSurcharge : 0;
values[i, 18] = !invoiceTable[i].IsTotalDeliveryChargeNull() ? invoiceTable[i].TotalDeliveryCharge : 0;
values[i, 19] = !invoiceTable[i].IsLineHaulChargeNull() ? invoiceTable[i].LineHaulCharge : 0;
values[i, 20] = !invoiceTable[i].IsTotalChargeNull() ? invoiceTable[i].TotalCharge : 0;
}
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 21]].Value2 = values;
Application.ScreenUpdating = true;
}
private void createDetailBody(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Create body of detail worksheet
//Excel.Worksheet ws = (Excel.Worksheet)this.Worksheets["Detail"];
Detail ws = global::Argix.Finance.Globals.Detail;
Application.ScreenUpdating = false;
//Insert a row at row0 + 1 (pushes down) for every row of data
int rowCount = invoiceTable.Rows.Count;
Excel.Range row0 = ws.Range[ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, 14]].EntireRow;
for (int i = 0; i < rowCount - 1; i++)
{
row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
}
//Populate entire data table into a range of worksheet cells
object[,] values = new object[rowCount, 14];
for (int i = 0; i < rowCount; i++)
{
values[i, 0] = "'" + invoiceTable[i].InvoiceNumber.Trim();
values[i, 1] = "'" + invoiceTable[i].PRONumber;
values[i, 2] = "'" + invoiceTable[i].StoreNumber.ToString();
values[i, 3] = "'" + invoiceTable[i].StoreZip.Trim();
values[i, 4] = "'" + invoiceTable[i].Zone.Trim();
values[i, 5] = "'" + invoiceTable[i].TLNumber.Trim();
values[i, 6] = invoiceTable[i].CtnQty;
values[i, 7] = invoiceTable[i].PltQty;
values[i, 8] = invoiceTable[i].Weight;
values[i, 9] = invoiceTable[i].RatedWeight;
values[i, 10] = invoiceTable[i].WeightRate;
values[i, 11] = invoiceTable[i].FuelSurcharge;
values[i, 12] = invoiceTable[i].DeliveryTotal;
values[i, 13] = "'" + invoiceTable[i].RateNote;
}
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 14]].Value2 = values;
Application.ScreenUpdating = true;
}
private void createDetailTotals(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Get worksheet
Detail ws = global::Argix.Finance.Globals.Detail;
//Totals
int rowCount = invoiceTable.Rows.Count;
object[,] totals = new object[1, 1];
totals[0, 0] = "TOTAL " + rowCount.ToString() + " DELIVERIES";
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 1, 1], ws.Cells[ROW0_DETAIL + rowCount + 1, 1]].Value2 = totals;
//Footer
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 3, 1], ws.Cells[ROW0_DETAIL + rowCount + 3, 16]].Value2 = new object[1, 16] {
{ "PLEASE REFERENCE INVOICE# " + invoiceTable[0].InvoiceNumber + " WHEN REMITTING PAYMENT I.C.C. REGULATIONS REQUIRE THAT THIS BILL BE PAID WITHIN " + invoiceTable[0].PaymentDays + " DAYS", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" }
};
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 4, 1], ws.Cells[ROW0_DETAIL + rowCount + 4, 16]].Value2 = new object[1, 16] {
{ "", "", "A SERVICE CHARGE OF 1.5% PER MONTH IS ADDED TO ALL PAST DUE INVOICES", "", "", "", "", "", "", "", "", "", "", "", "", "" }
};
ws.Range[ws.Cells[ROW0_DETAIL + rowCount + 5, 1], ws.Cells[ROW0_DETAIL + rowCount + 5, 16]].Value2 = new object[1, 16] {
{ "", "", "REMIT TO: " + invoiceTable[0].RemitToName.Trim() + " " + invoiceTable[0].RemitToAddressLine1.Trim() + " " + invoiceTable[0].RemitToAddressLine2.Trim() + " " + invoiceTable[0].RemitToCity.Trim() + ", " + invoiceTable[0].RemitToState + " " + invoiceTable[0].RemitToZip + "-" + invoiceTable[0].RemitToZip4, "", "", "", "", "", "", "", "", "", "", "", "", "" }
};
}
private void createDetailBody(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Get worksheet
const int COLS = 17;
Detail ws = global::Argix.Finance.Globals.Detail;
Application.ScreenUpdating = false;
//Insert a row at row0 + 1 (pushes down) for every row of data
int rowCount = invoiceTable.Rows.Count;
Excel.Range row0 = ws.Range[ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, COLS]].EntireRow;
for (int i = 0; i < rowCount - 1; i++)
{
row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
}
//Populate entire data table into a range of worksheet cells
object[,] values = new object[rowCount, COLS];
for (int i = 0; i < rowCount; i++)
{
values[i, 0] = "'" + invoiceTable[i].StoreName.Trim();
values[i, 1] = "'" + invoiceTable[i].StoreState;
values[i, 2] = "'" + invoiceTable[i].StoreZip;
values[i, 3] = "'" + invoiceTable[i].LocationCode;
values[i, 4] = invoiceTable[i].CtnQty;
values[i, 5] = invoiceTable[i].CartonRate;
values[i, 6] = invoiceTable[i].PltQty;
values[i, 7] = invoiceTable[i].PalletRate;
values[i, 8] = invoiceTable[i].Weight;
values[i, 9] = invoiceTable[i].RatedWeight;
values[i, 10] = invoiceTable[i].WeightRate;
values[i, 11] = invoiceTable[i].Surcharge;
values[i, 12] = invoiceTable[i].ConsolidationCharge;
values[i, 13] = invoiceTable[i].FuelRate;
values[i, 14] = invoiceTable[i].FuelSurcharge;
values[i, 15] = invoiceTable[i].DeliveryTotal;
values[i, 16] = invoiceTable[i].ManifestList;
}
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, COLS]].Value2 = values;
#region Column Formats
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ws.Range[ws.Cells[ROW0_DETAIL, 2], ws.Cells[ROW0_DETAIL + rowCount - 1, 2]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ws.Range[ws.Cells[ROW0_DETAIL, 3], ws.Cells[ROW0_DETAIL + rowCount - 1, 3]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ws.Range[ws.Cells[ROW0_DETAIL, 4], ws.Cells[ROW0_DETAIL + rowCount - 1, 4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ws.Range[ws.Cells[ROW0_DETAIL, 5], ws.Cells[ROW0_DETAIL + rowCount - 1, 5]].NumberFormat = "#,###_);(#,###)";
ws.Range[ws.Cells[ROW0_DETAIL, 5], ws.Cells[ROW0_DETAIL + rowCount - 1, 5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 6], ws.Cells[ROW0_DETAIL + rowCount - 1, 6]].NumberFormat = "#,###.##_);(#,###.##);_(* _)";
ws.Range[ws.Cells[ROW0_DETAIL, 6], ws.Cells[ROW0_DETAIL + rowCount - 1, 6]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 7], ws.Cells[ROW0_DETAIL + rowCount - 1, 7]].NumberFormat = "#,###_);(#,###)";
ws.Range[ws.Cells[ROW0_DETAIL, 7], ws.Cells[ROW0_DETAIL + rowCount - 1, 7]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 8], ws.Cells[ROW0_DETAIL + rowCount - 1, 8]].NumberFormat = "#,###.##_);(#,###.##);_(* _)";
ws.Range[ws.Cells[ROW0_DETAIL, 8], ws.Cells[ROW0_DETAIL + rowCount - 1, 8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 9], ws.Cells[ROW0_DETAIL + rowCount - 1, 9]].NumberFormat = "#,##0_);(#,##0)";
ws.Range[ws.Cells[ROW0_DETAIL, 9], ws.Cells[ROW0_DETAIL + rowCount - 1, 9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 10], ws.Cells[ROW0_DETAIL + rowCount - 1, 10]].NumberFormat = "#,##0_);(#,##0)";
ws.Range[ws.Cells[ROW0_DETAIL, 10], ws.Cells[ROW0_DETAIL + rowCount - 1, 10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 11], ws.Cells[ROW0_DETAIL + rowCount - 1, 11]].NumberFormat = "#,##0.00_);(#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL, 11], ws.Cells[ROW0_DETAIL + rowCount - 1, 11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 12], ws.Cells[ROW0_DETAIL + rowCount - 1, 12]].NumberFormat = "$#,##0.00_);($#,##0.00);_(* _)";
ws.Range[ws.Cells[ROW0_DETAIL, 12], ws.Cells[ROW0_DETAIL + rowCount - 1, 12]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 13], ws.Cells[ROW0_DETAIL + rowCount - 1, 13]].NumberFormat = "$#,##0.00_);($#,##0.00);_(* _)";
ws.Range[ws.Cells[ROW0_DETAIL, 13], ws.Cells[ROW0_DETAIL + rowCount - 1, 13]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 14], ws.Cells[ROW0_DETAIL + rowCount - 1, 14]].NumberFormat = "#,##0.0000_);(#,##0.0000)";
ws.Range[ws.Cells[ROW0_DETAIL, 14], ws.Cells[ROW0_DETAIL + rowCount - 1, 14]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 15], ws.Cells[ROW0_DETAIL + rowCount - 1, 15]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL, 15], ws.Cells[ROW0_DETAIL + rowCount - 1, 15]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 16], ws.Cells[ROW0_DETAIL + rowCount - 1, 16]].NumberFormat = "$#,##0.00_);($#,##0.00)";
ws.Range[ws.Cells[ROW0_DETAIL, 16], ws.Cells[ROW0_DETAIL + rowCount - 1, 16]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ws.Range[ws.Cells[ROW0_DETAIL, 17], ws.Cells[ROW0_DETAIL + rowCount - 1, 17]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
#endregion
Application.ScreenUpdating = true;
}
private void createDetailBody(InvoiceDataset.ClientInvoiceTableDataTable invoiceTable)
{
//Get worksheet
Detail ws = global::Argix.Finance.Globals.Detail;
Application.ScreenUpdating = false;
//Insert a row at row0 + 1 (pushes down) for every row of data
int rowCount = invoiceTable.Rows.Count;
Excel.Range row0 = ws.Range[ws.Cells[ROW0_DETAIL + 1, 1], ws.Cells[ROW0_DETAIL + 1, 38]].EntireRow;
for (int i = 0; i < rowCount - 1; i++)
{
row0.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
}
//Calculate totals
int cartonTotal = 0;
int weightTotal = 0;
decimal invoiceTotal = 0.0M;
for (int i = 0; i < rowCount; i++)
{
cartonTotal += invoiceTable[i].CtnQty;
weightTotal += invoiceTable[i].Weight;
invoiceTotal += invoiceTable[i].DeliveryTotal;
}
//Populate entire data table into a range of worksheet cells
object[,] values = new object[rowCount, 38];
for (int i = 0; i < rowCount; i++)
{
values[i, 0] = "'" + invoiceTable[i].StoreName.Trim();
values[i, 1] = "'" + invoiceTable[i].StoreAddressLine1.Trim();
values[i, 2] = "'" + invoiceTable[i].StoreCity.Trim();
values[i, 3] = "'" + invoiceTable[i].StoreState;
values[i, 4] = "'" + invoiceTable[i].StoreZip;
values[i, 5] = "'" + invoiceTable[i].LocationCode;
values[i, 6] = invoiceTable[i].CtnQty;
values[i, 7] = cartonTotal;
values[i, 8] = invoiceTable[i].CartonRate;
values[i, 9] = invoiceTable[i].PltQty;
values[i, 10] = invoiceTable[i].PalletRate;
values[i, 11] = invoiceTable[i].Weight;
values[i, 12] = weightTotal;
values[i, 13] = invoiceTable[i].RatedWeight;
values[i, 14] = invoiceTable[i].WeightRate;
values[i, 15] = invoiceTable[i].FreightCharge;
values[i, 16] = invoiceTable[i].Surcharge;
values[i, 17] = invoiceTable[i].ConsolidationCharge;
values[i, 18] = invoiceTable[i].FuelRate;
values[i, 19] = invoiceTable[i].FuelSurcharge;
values[i, 20] = invoiceTable[i].DeliveryTotal;
values[i, 21] = invoiceTotal;
values[i, 22] = "'" + invoiceTable[i].ShipperNumber.ToString();
values[i, 23] = "'" + invoiceTable[i].ShipperName.Trim();
values[i, 24] = "'" + invoiceTable[i].ShipperAddressLine1.Trim();
values[i, 25] = "'" + invoiceTable[i].ShipperCity.Trim();
values[i, 26] = "'" + invoiceTable[i].ShipperState.Trim();
values[i, 27] = "'" + invoiceTable[i].ShipperZip;
values[i, 28] = "'" + invoiceTable[i].RateNote;
values[i, 29] = "'" + invoiceTable[i].BillToName;
values[i, 30] = "'" + invoiceTable[i].BillToAddressline1;
values[i, 31] = "'" + invoiceTable[i].BillToCity;
values[i, 32] = "'" + invoiceTable[i].BillToState;
values[i, 33] = "'" + invoiceTable[i].BillToZip;
values[i, 34] = "'" + invoiceTable[i].InvoiceNumber;
values[i, 35] = "'" + invoiceTable[i].InvoiceDate.ToString("MM/dd/yyyy");
values[i, 36] = "'" + invoiceTable[i].StoreBLNumber;
values[i, 37] = "'" + invoiceTable[i].MasterBLNumber;
}
ws.Range[ws.Cells[ROW0_DETAIL, 1], ws.Cells[ROW0_DETAIL + rowCount - 1, 38]].Value2 = values;
#region Column Formats
//ws.Range[ws.Cells[ROW0_DETAIL,1],ws.Cells[ROW0_DETAIL + rowCount - 1,1]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//ws.Range[ws.Cells[ROW0_DETAIL,2],ws.Cells[ROW0_DETAIL + rowCount - 1,2]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//ws.Range[ws.Cells[ROW0_DETAIL,3],ws.Cells[ROW0_DETAIL + rowCount - 1,3]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//ws.Range[ws.Cells[ROW0_DETAIL,4],ws.Cells[ROW0_DETAIL + rowCount - 1,4]].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//ws.Range[ws.Cells[ROW0_DETAIL,5],ws.Cells[ROW0_DETAIL + rowCount - 1,5]].NumberFormat = "#,###_);(#,###)";
//ws.Range[ws.Cells[ROW0_DETAIL,5],ws.Cells[ROW0_DETAIL + rowCount - 1,5]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,6],ws.Cells[ROW0_DETAIL + rowCount - 1,6]].NumberFormat = "#,###.##_);(#,###.##);_(* _)";
//ws.Range[ws.Cells[ROW0_DETAIL,6],ws.Cells[ROW0_DETAIL + rowCount - 1,6]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,7],ws.Cells[ROW0_DETAIL + rowCount - 1,7]].NumberFormat = "#,###_);(#,###)";
//ws.Range[ws.Cells[ROW0_DETAIL,7],ws.Cells[ROW0_DETAIL + rowCount - 1,7]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,8],ws.Cells[ROW0_DETAIL + rowCount - 1,8]].NumberFormat = "#,###.##_);(#,###.##);_(* _)";
//ws.Range[ws.Cells[ROW0_DETAIL,8],ws.Cells[ROW0_DETAIL + rowCount - 1,8]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,9],ws.Cells[ROW0_DETAIL + rowCount - 1,9]].NumberFormat = "#,##0_);(#,##0)";
//ws.Range[ws.Cells[ROW0_DETAIL,9],ws.Cells[ROW0_DETAIL + rowCount - 1,9]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,10],ws.Cells[ROW0_DETAIL + rowCount - 1,10]].NumberFormat = "#,##0_);(#,##0)";
//ws.Range[ws.Cells[ROW0_DETAIL,10],ws.Cells[ROW0_DETAIL + rowCount - 1,10]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,11],ws.Cells[ROW0_DETAIL + rowCount - 1,11]].NumberFormat = "#,##0.00_);(#,##0.00)";
//ws.Range[ws.Cells[ROW0_DETAIL,11],ws.Cells[ROW0_DETAIL + rowCount - 1,11]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,12],ws.Cells[ROW0_DETAIL + rowCount - 1,12]].NumberFormat = "$#,##0.00_);($#,##0.00);_(* _)";
//ws.Range[ws.Cells[ROW0_DETAIL,12],ws.Cells[ROW0_DETAIL + rowCount - 1,12]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,13],ws.Cells[ROW0_DETAIL + rowCount - 1,13]].NumberFormat = "$#,##0.00_);($#,##0.00);_(* _)";
//ws.Range[ws.Cells[ROW0_DETAIL,13],ws.Cells[ROW0_DETAIL + rowCount - 1,13]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,14],ws.Cells[ROW0_DETAIL + rowCount - 1,14]].NumberFormat = "#,##0.0000_);(#,##0.0000)";
//ws.Range[ws.Cells[ROW0_DETAIL,14],ws.Cells[ROW0_DETAIL + rowCount - 1,14]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,15],ws.Cells[ROW0_DETAIL + rowCount - 1,15]].NumberFormat = "$#,##0.00_);($#,##0.00)";
//ws.Range[ws.Cells[ROW0_DETAIL,15],ws.Cells[ROW0_DETAIL + rowCount - 1,15]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//ws.Range[ws.Cells[ROW0_DETAIL,16],ws.Cells[ROW0_DETAIL + rowCount - 1,16]].NumberFormat = "$#,##0.00_);($#,##0.00)";
//ws.Range[ws.Cells[ROW0_DETAIL,16],ws.Cells[ROW0_DETAIL + rowCount - 1,16]].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
#endregion
Application.ScreenUpdating = true;
}
