public void AddDiscountGroupToCache(DiscountGroupDetails ObjDiscountGroupDetails)
{
try
{
Int32 Index = ListDiscountGroups.BinarySearch(ObjDiscountGroupDetails, ObjDiscountGroupDetails);
if (Index < 0)
{
ListDiscountGroups.Insert(~Index, ObjDiscountGroupDetails);
DefaultDiscountGroupIndex = ListDiscountGroups.FindIndex(e => e.IsDefault);
}
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorMaster.AddDiscountGroupToCache()", ex);
}
}
public Double GetVendorDiscount(String VendorName, Double Amount)
{
try
{
DiscountGroupDetails ObjDiscountGroupDetails = GetVendorDiscount(VendorName);
if (ObjDiscountGroupDetails == null)
{
return(-1);
}
return(ObjDiscountGroupDetails.GetDiscountAmount(Amount));
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorMaster.GetVendorDiscount()", ex);
}
return(-1);
}
private void CreateVendorPurchaseOrder(ReportType EnumReportType, DataRow[] drItems, DataRow[] drVendors,
String SelectedDateTimeString, Int32 StartRow, Int32 StartColumn,
List <Int32> ListItemIndexes, List <Int32> ListVendorIndexes, Excel.Workbook xlWorkbook)
{
try
{
Boolean PrintVATPercent = false;
ReportSettings CurrReportSettings = null;
String ReportTypeName = "", BillNumberText = "", SaveFileName = "";
switch (EnumReportType)
{
case ReportType.PURCHASEORDER:
CurrReportSettings = CommonFunctions.ObjPurchaseOrderSettings;
ReportTypeName = "Purchase Order";
PrintVATPercent = true;
BillNumberText = "PO#";
SaveFileName = txtBoxOutputFolder.Text + "\\PurchaseOrder_" + SelectedDateTimeString + ".xlsx";
break;
default:
return;
}
Excel.Worksheet xlPOWorksheet = xlWorkbook.Sheets[1];
#region Print PO Sheet for each Vendor
Double Quantity;
Int32 POHeaderStartRow = 0;
Int32 POStartRow = POHeaderStartRow + 5;
Int32 PONumber = CurrReportSettings.LastNumber;
Int32 ValidVendorCount = ListVendorIndexes.Where(s => (s >= 0)).ToList().Count;
Int32 ValidItemCount = ListItemIndexes.Where(s => (s >= 0)).ToList().Count;
Int32 ProgressBarCount = (ValidVendorCount * ValidItemCount);
Int32 Counter = 0;
Int32 SlNoColNum = 1, ItemNameColNum = 2, OrdQtyColNum = 3, RecdQtyColNum = 4, PriceColNum = 5, TotalColNum = 6;
Int32 ReportAppendRowsAtBottom = CommonFunctions.ObjApplicationSettings.ReportAppendRowsAtBottom;
Int32 OrderTotalRowOffset = 1 + ReportAppendRowsAtBottom, DiscountRowOffset = 2 + ReportAppendRowsAtBottom, OldBalanceRowOffset = 3 + ReportAppendRowsAtBottom, TotalCostRowOffset = 4 + ReportAppendRowsAtBottom;
Int32 VendorCount = 0, PODetailsCount = 5;
for (int i = 0; i < ListVendorIndexes.Count; i++)
{
if (ListVendorIndexes[i] < 0)
{
continue;
}
VendorCount++;
lblStatus.Text = "Creating " + ReportTypeName + " for Vendor " + VendorCount + " of " + ValidVendorCount;
Excel.Worksheet xlWorkSheet = xlWorkbook.Worksheets.Add(Type.Missing, xlWorkbook.Sheets[xlWorkbook.Sheets.Count]);
String SheetName = drVendors[ListVendorIndexes[i]]["VendorName"].ToString().Replace(":", "").
Replace("\\", "").Replace("/", "").
Replace("?", "").Replace("*", "").
Replace("[", "").Replace("]", "");
xlWorkSheet.Name = ((SheetName.Length > 30) ? SheetName.Substring(0, 30) : SheetName);
VendorDetails ObjCurrentVendor = CommonFunctions.ObjVendorMaster.GetVendorDetails(drVendors[ListVendorIndexes[i]]["VendorName"].ToString());
#region Print PO Items
Int32 SlNo = 0;
#region Print PO Header
Excel.Range xlRange = xlWorkSheet.Cells[1 + POHeaderStartRow, 1];
Int32 CustDetailsStartRow = 1 + POHeaderStartRow;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow, SlNoColNum];
xlRange.Value = "Name";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow, SlNoColNum + 1].Value = drVendors[ListVendorIndexes[i]]["VendorName"].ToString();
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum];
xlRange.Value = "Address";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum + 1].Value = drVendors[ListVendorIndexes[i]]["Address"].ToString();
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum + 1];
xlRange.WrapText = true;
if (drVendors[ListVendorIndexes[i]]["Address"].ToString().Length >= 25)
{
xlRange.EntireColumn.ColumnWidth = 25;
}
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum];
xlRange.Value = "TIN#";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum + 1].Value = drVendors[ListVendorIndexes[i]]["TINNumber"].ToString();
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 3, SlNoColNum];
xlRange.Value = "Phone";
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 3, SlNoColNum + 1].Value = drVendors[ListVendorIndexes[i]]["Phone"].ToString();
xlRange = xlWorkSheet.Cells[CustDetailsStartRow, TotalColNum - 1];
xlRange.Value = "Date";
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow, TotalColNum].Value = DateTime.Today.ToString("dd-MMM-yyyy");
PONumber++;
xlRange = xlWorkSheet.Cells[1 + CustDetailsStartRow, TotalColNum - 1];
xlRange.Value = BillNumberText;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 1, TotalColNum].Value = PONumber;
drVendors[ListVendorIndexes[i]]["PONumber"] = PONumber;
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow, 1], xlWorkSheet.Cells[CustDetailsStartRow + 3, TotalColNum]];
SellerInvoiceForm.SetAllBorders(xlRange);
#endregion
xlWorkSheet.Cells[POStartRow + 1, SlNoColNum].Value = "Sl.No.";
xlWorkSheet.Cells[POStartRow + 1, ItemNameColNum].Value = "Item Name";
xlWorkSheet.Cells[POStartRow + 1, OrdQtyColNum].Value = "Order Quantity";
xlWorkSheet.Cells[POStartRow + 1, RecdQtyColNum].Value = "Received Quantity";
xlWorkSheet.Cells[POStartRow + 1, PriceColNum].Value = "Price";
xlWorkSheet.Cells[POStartRow + 1, TotalColNum].Value = "Total";
xlWorkSheet.Range[xlWorkSheet.Cells[POStartRow + 1, SlNoColNum], xlWorkSheet.Cells[POStartRow + 1, TotalColNum]].Font.Bold = true;
for (int j = 0; j < ListItemIndexes.Count; j++)
{
if (ListItemIndexes[j] < 0)
{
continue;
}
Counter++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
if (xlPOWorksheet.Cells[StartRow + 1 + i, StartColumn + PODetailsCount + j].Value == null)
{
continue;
}
if (String.IsNullOrEmpty(xlPOWorksheet.Cells[StartRow + 1 + i, StartColumn + PODetailsCount + j].Value.ToString()))
{
continue;
}
Quantity = Double.Parse(xlPOWorksheet.Cells[StartRow + 1 + i, StartColumn + PODetailsCount + j].Value.ToString());
drItems[ListItemIndexes[j]]["Quantity"] = Double.Parse(drItems[ListItemIndexes[j]]["Quantity"].ToString()) + Quantity;
SlNo++;
xlWorkSheet.Cells[SlNo + POStartRow + 1, SlNoColNum].Value = SlNo;
xlWorkSheet.Cells[SlNo + POStartRow + 1, ItemNameColNum].Value = drItems[ListItemIndexes[j]]["StockName"].ToString();
xlWorkSheet.Cells[SlNo + POStartRow + 1, OrdQtyColNum].Value = Quantity;
if (chkBoxUseOrdQty.Checked == true)
{
xlWorkSheet.Cells[SlNo + POStartRow + 1, RecdQtyColNum].Value = Quantity;
}
xlWorkSheet.Cells[SlNo + POStartRow + 1, PriceColNum].Value = CommonFunctions.ObjProductMaster.GetPriceForProduct(drItems[ListItemIndexes[j]]["ItemName"].ToString(), ObjCurrentVendor.PriceGroupIndex);
xlWorkSheet.Cells[SlNo + POStartRow + 1, PriceColNum].NumberFormat = "#,##0.00";
Excel.Range xlRangeSaleQty = xlWorkSheet.Cells[SlNo + POStartRow + 1, RecdQtyColNum];
Excel.Range xlRangePrice = xlWorkSheet.Cells[SlNo + POStartRow + 1, PriceColNum];
Excel.Range xlRangeTotal = xlWorkSheet.Cells[SlNo + POStartRow + 1, TotalColNum];
xlRangeTotal.Formula = "=(" + xlRangeSaleQty.Address[false, false] + "*" + xlRangePrice.Address[false, false] + ")";
xlWorkSheet.Cells[SlNo + POStartRow + 1, TotalColNum].NumberFormat = "#,##0.00";
}
Excel.Range xlRangeSaleQtyFrom = xlWorkSheet.Cells[1 + POStartRow + 1, RecdQtyColNum];
Excel.Range xlRangeSaleQtyTo = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset - 1, RecdQtyColNum];
Excel.Range xlRangeTotalCost = xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, TotalColNum];
Excel.Range xlRangeSaleTotal = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum];
drVendors[ListVendorIndexes[i]]["Total"] = "='" + xlWorkSheet.Name + "'!" + xlRangeSaleTotal.Address[false, false];
drVendors[ListVendorIndexes[i]]["Quantity"] = "=Sum('" + xlWorkSheet.Name + "'!" + xlRangeSaleQtyFrom.Address[false, false] + ":" + xlRangeSaleQtyTo.Address[false, false] + ")";
#region Sales Total Row
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum - 1];
xlRange.Value = "Sales Total";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum];
Excel.Range xlRangeSalesTotalFrom = xlWorkSheet.Cells[1 + POStartRow + 1, TotalColNum];
Excel.Range xlRangeSalesTotalTo = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset - 1, TotalColNum];
xlRange.Formula = "=Sum(" + xlRangeSalesTotalFrom.Address[false, false] + ":" + xlRangeSalesTotalTo.Address[false, false] + ")";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, 1], xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
#region Discount Row
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, TotalColNum - 1];
xlRange.Value = "Discount";
xlRange.Font.Bold = true;
DiscountGroupDetails ObjDiscountGroup = CommonFunctions.ObjVendorMaster.GetVendorDiscount(ObjCurrentVendor.VendorName);
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, TotalColNum];
Excel.Range xlSalesTotal1 = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum];
if (ObjDiscountGroup.DiscountType == DiscountTypes.PERCENT)
{
xlRange.Formula = "=" + xlSalesTotal1.Address[false, false] + "*" + ObjDiscountGroup.Discount + "/100";
}
else if (ObjDiscountGroup.DiscountType == DiscountTypes.ABSOLUTE)
{
xlRange.Value = ObjDiscountGroup.Discount;
}
else
{
xlRange.Formula = "=" + xlSalesTotal1.Address[false, false];
}
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
drVendors[ListVendorIndexes[i]]["TotalDiscount"] = "='" + xlWorkSheet.Name + "'!" + xlRange.Address[false, false];
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, 1], xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
if (PrintVATPercent)
{
#region VAT Percent Row
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1];
xlRange.Value = "VAT Percent " + CurrReportSettings.VATPercent + "%";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OldBalanceRowOffset, TotalColNum];
Excel.Range xlSalesTotal = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum];
Excel.Range xlDiscount = xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, TotalColNum];
xlRange.Formula = "=(" + xlSalesTotal.Address[false, false] + "-" + xlDiscount.Address[false, false] + ")*" + CurrReportSettings.VATPercent + "/100";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
drVendors[ListVendorIndexes[i]]["TotalTax"] = "='" + xlWorkSheet.Name + "'!" + xlRange.Address[false, false];
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + POStartRow + 1 + OldBalanceRowOffset, 1], xlWorkSheet.Cells[SlNo + POStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
}
#region Total Cost Row
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, TotalColNum - 1];
xlRange.Value = "Total";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, TotalColNum];
Excel.Range xlRangeSalesTotal = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OrderTotalRowOffset, TotalColNum];
Excel.Range xlRangeDiscount = xlWorkSheet.Cells[SlNo + POStartRow + 1 + DiscountRowOffset, TotalColNum];
Excel.Range xlRangeOldBalance = xlWorkSheet.Cells[SlNo + POStartRow + 1 + OldBalanceRowOffset, TotalColNum];
xlRange.Formula = "=Round(" + xlRangeSalesTotal.Address[false, false] + "+" + xlRangeOldBalance.Address[false, false] + "-" + xlRangeDiscount.Address[false, false] + ", 0)";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, 1], xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[POStartRow + 1, 1], xlWorkSheet.Cells[SlNo + POStartRow + 1 + TotalCostRowOffset, TotalColNum]];
SellerInvoiceForm.SetAllBorders(xlRange);
#endregion
xlWorkSheet.UsedRange.Columns.AutoFit();
SellerInvoiceForm.AddPageHeaderAndFooter(ref xlWorkSheet, CurrReportSettings.HeaderSubTitle, CurrReportSettings);
}
#endregion
if (chkBoxCreateSummary.Checked)
{
CreateVendorSummarySheet(drVendors, xlWorkbook, CurrReportSettings);
}
xlApp.DisplayAlerts = false;
xlWorkbook.Sheets[SelectedDateTimeString].Delete();
xlApp.DisplayAlerts = true;
Excel.Worksheet FirstWorksheet = xlWorkbook.Sheets[1];
FirstWorksheet.Select();
#region Write PONumber to Settings File
CurrReportSettings.LastNumber = PONumber;
#endregion
ReportProgressFunc(100);
xlWorkbook.SaveAs(SaveFileName);
xlWorkbook.Close();
lblStatus.Text = "Completed creation of " + ReportTypeName + "s for all Vendors";
CommonFunctions.ReleaseCOMObject(xlWorkbook);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorPurchaseOrderForm.CreateVendorPurchaseOrder", ex);
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
public override void CreateInvoice(Excel.Worksheet xlWorkSheet)
{
try
{
String SheetName = ObjSellerDetails.Name.Replace(":", "").
Replace("\\", "").Replace("/", "").
Replace("?", "").Replace("*", "").
Replace("[", "").Replace("]", "");
xlWorkSheet.Name = ((SheetName.Length > 30) ? SheetName.Substring(0, 30) : SheetName);
Int32 InvoiceHeaderStartRow = 0;
Int32 InvoiceStartRow = InvoiceHeaderStartRow + 5;
Excel.Range xlRange = null;
#region Print Invoice Items
Int32 SlNo = 0;
Int32 SlNoColNum = 1, ItemNameColNum = 2, OrdQtyColNum = 3, SalQtyColNum = 4, PriceColNum = 5, TotalColNum = 6;
Int32 ReportAppendRowsAtBottom = CommonFunctions.ObjApplicationSettings.ReportAppendRowsAtBottom;
Int32 SalesTotalRowOffset = 1 + ReportAppendRowsAtBottom, DiscountRowOffset = 2 + ReportAppendRowsAtBottom, OldBalanceRowOffset = 3 + ReportAppendRowsAtBottom, TotalCostRowOffset = 4 + ReportAppendRowsAtBottom;
#region Print Invoice Header
xlRange = xlWorkSheet.Cells[1 + InvoiceHeaderStartRow, 1];
Int32 CustDetailsStartRow = 1 + InvoiceHeaderStartRow;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow, SlNoColNum];
xlRange.Value = "Name";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow, SlNoColNum + 1].Value = ObjSellerDetails.Name;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum];
xlRange.Value = "Address";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum + 1].Value = ObjSellerDetails.Address;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 1, SlNoColNum + 1];
xlRange.WrapText = true;
if (ObjSellerDetails.Address.Length >= 25)
{
xlRange.EntireColumn.ColumnWidth = 25;
}
if (CurrReportSettings.Type == ReportType.INVOICE)
{
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum];
xlRange.Value = "TIN#";
xlRange.WrapText = true;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum + 1].Value = ObjSellerDetails.TINNumber;
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 3, SlNoColNum];
xlRange.Value = "Phone";
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 3, SlNoColNum + 1].Value = ObjSellerDetails.Phone;
}
else
{
xlRange = xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum];
xlRange.Value = "Phone";
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 2, SlNoColNum + 1].Value = ObjSellerDetails.Phone;
}
xlRange = xlWorkSheet.Cells[CustDetailsStartRow, TotalColNum - 1];
xlRange.Value = "Date";
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow, TotalColNum].Value = DateTime.Today.ToString("dd-MMM-yyyy");
xlRange = xlWorkSheet.Cells[1 + CustDetailsStartRow, TotalColNum - 1];
xlRange.Value = InvoiceNumberText;
xlRange.Font.Bold = true;
xlWorkSheet.Cells[CustDetailsStartRow + 1, TotalColNum].Value = SerialNumber;
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[CustDetailsStartRow, 1], xlWorkSheet.Cells[CustDetailsStartRow + 3, TotalColNum]];
SellerInvoiceForm.SetAllBorders(xlRange);
#endregion
xlWorkSheet.Cells[InvoiceStartRow + 1, SlNoColNum].Value = "Sl No";
xlWorkSheet.Cells[InvoiceStartRow + 1, ItemNameColNum].Value = "Item Name";
xlWorkSheet.Cells[InvoiceStartRow + 1, OrdQtyColNum].Value = "Order Quantity";
xlWorkSheet.Cells[InvoiceStartRow + 1, SalQtyColNum].Value = "Sales Quantity";
xlWorkSheet.Cells[InvoiceStartRow + 1, PriceColNum].Value = "Price";
xlWorkSheet.Cells[InvoiceStartRow + 1, TotalColNum].Value = "Total";
xlWorkSheet.Range[xlWorkSheet.Cells[InvoiceStartRow + 1, SlNoColNum], xlWorkSheet.Cells[InvoiceStartRow + 1, TotalColNum]].Font.Bold = true;
for (int i = 0; i < ListProducts.Count; i++)
{
ProductDetailsForInvoice CurrProd = ListProducts[i];
SlNo++;
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, SlNoColNum].Value = SlNo;
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, ItemNameColNum].Value = CurrProd.Description;
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, OrdQtyColNum].Value = CurrProd.OrderQuantity;
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, SalQtyColNum].Value = CurrProd.SaleQuantity;
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, PriceColNum].Value = CurrProd.Rate * (1 + CurrProd.CGSTDetails.TaxRate + CurrProd.SGSTDetails.TaxRate);
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, PriceColNum].NumberFormat = "#,##0.00";
Excel.Range xlRangeSaleQty = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, SalQtyColNum];
Excel.Range xlRangePrice = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, PriceColNum];
Excel.Range xlRangeTotal = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, TotalColNum];
xlRangeTotal.Formula = "=(" + xlRangeSaleQty.Address[false, false] + "*" + xlRangePrice.Address[false, false] + ")";
xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1, TotalColNum].NumberFormat = "#,##0.00";
}
Excel.Range xlRangeSaleQtyFrom = xlWorkSheet.Cells[1 + InvoiceStartRow + 1, SalQtyColNum];
Excel.Range xlRangeSaleQtyTo = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset - 1, SalQtyColNum];
Excel.Range xlRangeTotalCost = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, TotalColNum];
Excel.Range xlRangeSaleTotal = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum];
if (CurrReportSettings.Type == ReportType.INVOICE)
{
TotalSalesValue = "='" + xlWorkSheet.Name + "'!" + xlRangeSaleTotal.Address[false, false];
}
else if (CurrReportSettings.Type == ReportType.QUOTATION)
{
TotalSalesValue = "='" + xlWorkSheet.Name + "'!" + xlRangeSaleTotal.Address[false, false];
}
#region Sales Total Row
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum - 1];
xlRange.Value = "Sales Total";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum];
Excel.Range xlRangeSalesTotalFrom = xlWorkSheet.Cells[1 + InvoiceStartRow + 1, TotalColNum];
Excel.Range xlRangeSalesTotalTo = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset - 1, TotalColNum];
xlRange.Formula = "=Sum(" + xlRangeSalesTotalFrom.Address[false, false] + ":" + xlRangeSalesTotalTo.Address[false, false] + ")";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
#region Discount Row
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, TotalColNum - 1];
xlRange.Value = "Discount";
xlRange.Font.Bold = true;
DiscountGroupDetails ObjDiscountGroup = CommonFunctions.ObjSellerMaster.GetSellerDiscount(ObjSellerDetails.Name);
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, TotalColNum];
Excel.Range xlSalesTotal1 = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum];
if (ObjDiscountGroup.DiscountType == DiscountTypes.PERCENT)
{
xlRange.Formula = "=" + xlSalesTotal1.Address[false, false] + "*" + ObjDiscountGroup.Discount + "/100";
}
else if (ObjDiscountGroup.DiscountType == DiscountTypes.ABSOLUTE)
{
xlRange.Value = ObjDiscountGroup.Discount;
}
else
{
xlRange.Formula = "=" + xlSalesTotal1.Address[false, false];
}
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
TotalDiscount = "='" + xlWorkSheet.Name + "'!" + xlRange.Address[false, false];
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
if (PrintOldBalance)
{
#region Old Balance Row
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1];
xlRange.Value = "Old Balance";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum];
xlRange.Value = ObjSellerDetails.OldBalance;
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
}
if (CurrReportSettings.Type == ReportType.INVOICE)
{
#region VAT Percent Row
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1];
xlRange.Value = "VAT Percent " + CurrReportSettings.VATPercent + "%";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum];
Excel.Range xlSalesTotal = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum];
Excel.Range xlDiscount = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, TotalColNum];
xlRange.Formula = "=(" + xlSalesTotal.Address[false, false] + "-" + xlDiscount.Address[false, false] + ")*" + CurrReportSettings.VATPercent + "/100";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
TotalTax = "='" + xlWorkSheet.Name + "'!" + xlRange.Address[false, false];
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
}
#region Total Cost Row
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, TotalColNum - 1];
xlRange.Value = "Total";
xlRange.Font.Bold = true;
xlRange = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, TotalColNum];
Excel.Range xlRangeSalesTotal = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + SalesTotalRowOffset, TotalColNum];
Excel.Range xlRangeDiscount = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + DiscountRowOffset, TotalColNum];
Excel.Range xlRangeOldBalance = xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + OldBalanceRowOffset, TotalColNum];
xlRange.Formula = "=Round(" + xlRangeSalesTotal.Address[false, false] + "+" + xlRangeOldBalance.Address[false, false] + "-" + xlRangeDiscount.Address[false, false] + ", 0)";
xlRange.Font.Bold = true;
xlRange.NumberFormat = "#,##0.00";
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, TotalColNum - 1]];
xlRange.Font.Bold = true;
xlRange.Merge();
xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
#endregion
xlRange = xlWorkSheet.Range[xlWorkSheet.Cells[InvoiceStartRow + 1, 1], xlWorkSheet.Cells[SlNo + InvoiceStartRow + 1 + TotalCostRowOffset, TotalColNum]];
SellerInvoiceForm.SetAllBorders(xlRange);
#endregion
xlWorkSheet.UsedRange.Columns.AutoFit();
SellerInvoiceForm.AddPageHeaderAndFooter(ref xlWorkSheet, CurrReportSettings.HeaderSubTitle, CurrReportSettings);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("InvoiceVAT.CreateInvoice()", ex);
throw;
}
}
public void LoadVendorMaster(DataTable dtVendorMaster, DataTable dtDiscountGroupMaster)
{
try
{
ObjVendorMaster = new VendorMaster();
ObjVendorMaster.Initialize();
#region Load Line from Vendor Master
CommonFunctions.ListVendorLines = new List <String>();
Boolean ContainsBlanks = false;
for (int i = 0; i < dtVendorMaster.Rows.Count; i++)
{
DataRow dtRow = dtVendorMaster.Rows[i];
String Line = dtRow["Line"].ToString().Replace("<", "").Replace(">", "").ToUpper();
if (Line.Trim().Length == 0)
{
ContainsBlanks = true;
}
else if (!CommonFunctions.ListVendorLines.Contains(Line))
{
CommonFunctions.ListVendorLines.Add(Line);
}
}
CommonFunctions.ListVendorLines.Sort();
CommonFunctions.ListVendorLines.Insert(0, "<All>");
if (ContainsBlanks)
{
CommonFunctions.ListVendorLines.Add("<Blanks>");
}
#endregion
#region Load Discount Groups
for (int i = 0; i < dtDiscountGroupMaster.Rows.Count; i++)
{
DataRow dtRow = dtDiscountGroupMaster.Rows[i];
//DiscountGroup Discount DiscountType Default Description
DiscountGroupDetails ObjDiscountGroupDetails = new DiscountGroupDetails();
ObjDiscountGroupDetails.Name = dtRow["DiscountGroup"].ToString().Trim();
ObjDiscountGroupDetails.Discount = Double.Parse(dtRow["Discount"].ToString().Trim());
ObjDiscountGroupDetails.DiscountType = PriceGroupDetails.GetDiscountType(dtRow["DiscountType"].ToString().Trim());
ObjDiscountGroupDetails.IsDefault = (Int32.Parse(dtRow["Default"].ToString().Trim()) == 1);
ObjDiscountGroupDetails.Description = dtRow["Description"].ToString().Trim();
ObjVendorMaster.AddDiscountGroupToCache(ObjDiscountGroupDetails);
}
#endregion
#region Load Vendor Details
for (int i = 0; i < dtVendorMaster.Rows.Count; i++)
{
DataRow dtRow = dtVendorMaster.Rows[i];
//SlNo VendorName Address TINNumber Phone Line OldBalance PriceGroup DiscountGroup
VendorDetails ObjVendorDetails = new VendorDetails();
ObjVendorDetails.VendorName = dtRow["VendorName"].ToString().Trim();
ObjVendorDetails.Address = dtRow["Address"].ToString().Trim();
ObjVendorDetails.TINNumber = dtRow["TINNumber"].ToString().Trim();
ObjVendorDetails.Phone = dtRow["Phone"].ToString().Trim();
ObjVendorDetails.Line = dtRow["Line"].ToString().Trim();
ObjVendorDetails.PriceGroup = "";
ObjVendorDetails.DiscountGroup = "";
if (dtRow["PriceGroup"] != DBNull.Value && dtRow["PriceGroup"].ToString().Trim().Length > 0)
{
ObjVendorDetails.PriceGroup = dtRow["PriceGroup"].ToString().Trim();
}
if (dtRow["DiscountGroup"] != DBNull.Value && dtRow["DiscountGroup"].ToString().Trim().Length > 0)
{
ObjVendorDetails.DiscountGroup = dtRow["DiscountGroup"].ToString().Trim();
}
ObjVendorMaster.AddVendorToCache(ObjVendorDetails, ObjProductMaster.ListPriceGroups);
}
#endregion
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("ProductLine.LoadVendorMaster()", ex);
}
}