private void CreateItemSummarySheet(DataRow[] drItems, Excel.Workbook xlWorkbook, ReportSettings CurrReportSettings)
{
try
{
lblStatus.Text = "Creating Item Summary Sheet";
#region Print Item Summary Sheet
Int32 SummaryStartRow = 0;
Double Total = 0;
Excel.Worksheet xlSummaryWorkSheet = xlWorkbook.Worksheets.Add(xlWorkbook.Sheets[1]);
xlSummaryWorkSheet.Name = "Item Summary";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 1].Value = "Sl.No.";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 2].Value = "Stock Name";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 3].Value = "Vendor Name";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 4].Value = "Quantity";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 5].Value = "Price";
xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 6].Value = "Total";
Excel.Range xlRange1 = xlSummaryWorkSheet.Range[xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 1], xlSummaryWorkSheet.Cells[SummaryStartRow + 1, 6]];
xlRange1.Font.Bold = true;
HashSet <String> ListItems = new HashSet <String>();
for (int i = 0, j = 0; i < drItems.Length; i++)
{
if (ListItems.Contains(drItems[i]["StockName"].ToString()))
{
continue;
}
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 1].Value = (j + 1);
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 2].Value = drItems[i]["StockName"].ToString();
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 3].Value = drItems[i]["VendorName"].ToString();
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 4].Value = drItems[i]["Quantity"].ToString();
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 5].Value = drItems[i]["PurchasePrice"].ToString();
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 5].NumberFormat = "#,##0.00";
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 6].Value = Double.Parse(drItems[i]["Quantity"].ToString()) * Double.Parse(drItems[i]["PurchasePrice"].ToString());
xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 6].NumberFormat = "#,##0.00";
Total += Double.Parse(xlSummaryWorkSheet.Cells[i + SummaryStartRow + 2, 6].Value.ToString());
j++;
ListItems.Add(drItems[i]["StockName"].ToString());
}
Excel.Range tmpxlRange = xlSummaryWorkSheet.Cells[drItems.Length + SummaryStartRow + 2, 5];
tmpxlRange.Value = "Total";
tmpxlRange.Font.Bold = true;
tmpxlRange = xlSummaryWorkSheet.Cells[drItems.Length + SummaryStartRow + 2, 6];
tmpxlRange.Value = Total;
tmpxlRange.Font.Bold = true;
tmpxlRange.NumberFormat = "#,##0.00";
xlSummaryWorkSheet.UsedRange.Columns.AutoFit();
xlApp.DisplayAlerts = false;
SellerInvoiceForm.AddPageHeaderAndFooter(ref xlSummaryWorkSheet, "Itemwise Summary", CurrReportSettings);
xlApp.DisplayAlerts = true;
#endregion
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorPurchaseOrderForm.CreateItemSummarySheet()", ex);
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
private void CreateVendorSummarySheet(DataRow[] drVendors, Excel.Workbook xlWorkbook, ReportSettings CurrReportSettings)
{
try
{
lblStatus.Text = "Creating Vendor Summary Sheet";
#region Print Vendor Summary Sheet
Int32 SummaryStartRow = 0, CurrRow = 0, CurrCol = 0;
Excel.Worksheet xlVendorSummaryWorkSheet = xlWorkbook.Worksheets.Add(xlWorkbook.Sheets[1]);
xlVendorSummaryWorkSheet.Name = "Vendor Summary";
SummaryStartRow++;
Excel.Range xlRange1 = xlVendorSummaryWorkSheet.Cells[SummaryStartRow, 1];
xlRange1.Value = "Date";
xlRange1.Font.Bold = true;
xlRange1 = xlVendorSummaryWorkSheet.Cells[SummaryStartRow, 2];
xlRange1.Value = DateTime.Today.ToString("dd-MMM-yyyy");
xlRange1 = xlVendorSummaryWorkSheet.Range[xlVendorSummaryWorkSheet.Cells[SummaryStartRow, 2], xlVendorSummaryWorkSheet.Cells[SummaryStartRow, 3]];
xlRange1.Merge();
xlRange1.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
CurrRow = SummaryStartRow + 1;
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Sl#";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Bill#";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Vendor Name";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Purchase";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Cancel";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Return";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Discount";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Total Tax";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Net Sale";
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = "Cash";
Int32 LastCol = CurrCol;
xlRange1 = xlVendorSummaryWorkSheet.Range[xlVendorSummaryWorkSheet.Cells[CurrRow, 1], xlVendorSummaryWorkSheet.Cells[CurrRow, LastCol]];
xlRange1.Font.Bold = true;
Int32 VendorsCount = 0;
for (int i = 0; i < drVendors.Length; i++)
{
if (String.IsNullOrEmpty(drVendors[i]["PONumber"].ToString().Trim()))
{
continue;
}
VendorsCount++;
CurrRow = VendorsCount + SummaryStartRow + 1;
CurrCol = 0;
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = VendorsCount;
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = drVendors[i]["PONumber"].ToString();
CurrCol++; xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol].Value = drVendors[i]["VendorName"].ToString();
CurrCol++; Excel.Range xlRangePurchase = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
xlRangePurchase.Formula = drVendors[i]["Total"].ToString();
xlRangePurchase.NumberFormat = "#,##0.00";
CurrCol++; Excel.Range xlRangeCancel = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
CurrCol++; Excel.Range xlRangeReturn = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
CurrCol++; Excel.Range xlRangeDiscount = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
CurrCol++; Excel.Range xlRangeTotalTax = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
CurrCol++; Excel.Range xlRangeNetSale = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
xlRangeDiscount.Formula = drVendors[i]["TotalDiscount"].ToString();
if (CommonFunctions.ObjGeneralSettings.SummaryLocation == 0)
{
xlRangeTotalTax.Formula = drVendors[i]["TotalTax"].ToString();
}
xlRangeNetSale.Formula = "=Round(" + xlRangePurchase.Address[false, false]
+ "-" + xlRangeCancel.Address[false, false]
+ "-" + xlRangeReturn.Address[false, false]
+ "-" + xlRangeDiscount.Address[false, false]
+ "+" + xlRangeTotalTax.Address[false, false] + ", 0)";
xlRangeNetSale.NumberFormat = "#,##0.00";
CurrCol++; Excel.Range xlRangeCash = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
xlRangeCash.NumberFormat = "#,##0.00";
}
CurrRow = VendorsCount + SummaryStartRow + 2;
Excel.Range xlRange = xlVendorSummaryWorkSheet.Cells[CurrRow, 3];
xlRange.Value = "Total";
xlRange.Font.Bold = true;
xlRange.Font.Color = Color.Red;
for (int i = 4; i <= LastCol; i++)
{
CurrCol = i;
Excel.Range xlRangeTotal = xlVendorSummaryWorkSheet.Cells[CurrRow, CurrCol];
Excel.Range xlRangeTotalFrom = xlVendorSummaryWorkSheet.Cells[SummaryStartRow + 2, CurrCol];
Excel.Range xlRangeTotalTo = xlVendorSummaryWorkSheet.Cells[CurrRow - 1, CurrCol];
xlRangeTotal.Formula = "=Sum(" + xlRangeTotalFrom.Address[false, false] + ":" + xlRangeTotalTo.Address[false, false] + ")";
xlRangeTotal.NumberFormat = "#,##0.00";
xlRangeTotal.Font.Bold = true;
}
xlRange = xlVendorSummaryWorkSheet.Range[xlVendorSummaryWorkSheet.Cells[SummaryStartRow + 1, 1], xlVendorSummaryWorkSheet.Cells[CurrRow + 1, LastCol]];
SellerInvoiceForm.SetAllBorders(xlRange);
xlVendorSummaryWorkSheet.UsedRange.Columns.AutoFit();
xlRange = xlVendorSummaryWorkSheet.Columns["B"];
xlRange.ColumnWidth = 7;
xlRange = xlVendorSummaryWorkSheet.Columns["C"];
xlRange.ColumnWidth = 24;
SellerInvoiceForm.AddPageHeaderAndFooter(ref xlVendorSummaryWorkSheet, "Vendorwise Summary", CurrReportSettings);
#endregion
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorPurchaseOrderForm.CreateVendorSummarySheet()", ex);
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
private void CreatePurchaseOrders()
{
xlApp = new Excel.Application();
try
{
DataTable dtItemMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("ItemMaster", MasterFilePath, "*");
DataTable dtVendorMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("VendorMaster", MasterFilePath, "*");
String[] SelectedLine = new String[chkListBoxLine.CheckedItems.Count];
chkListBoxLine.CheckedItems.CopyTo(SelectedLine, 0);
if (SelectedLine.Length == 0 && CommonFunctions.ListSelectedVendors.Count == 0)
{
btnClose.Enabled = true;
btnCreatePurchaseOrder.Enabled = true;
MessageBox.Show(this, "No Line/Vendors are selected\nUnable to create Purchase Orders", "Status", MessageBoxButtons.OK, MessageBoxIcon.Warning);
lblStatus.Text = "Select any Line/Vendor";
return;
}
dtItemMaster.Columns.Add("Quantity", Type.GetType("System.Double"));
DataRow[] drItems = dtItemMaster.Select("", "SlNo asc");
for (int i = 0; i < drItems.Length; i++)
{
drItems[i]["Quantity"] = 0;
}
dtVendorMaster.Columns.Add("Quantity", Type.GetType("System.String"));
dtVendorMaster.Columns.Add("Total", Type.GetType("System.String"));
dtVendorMaster.Columns.Add("PONumber", Type.GetType("System.Int32"));
dtVendorMaster.Columns.Add("TotalDiscount", Type.GetType("System.String"));
dtVendorMaster.Columns.Add("TotalTax", Type.GetType("System.String"));
DataRow[] drVendors = dtVendorMaster.Select("", "SlNo asc");
String SelectedDateTimeString = dateTimePO.Value.ToString("dd-MM-yyyy");
String VendorOrderFile = txtBoxVendorOrderSheet.Text;
Excel.Workbook xlPOWorkbook = xlApp.Workbooks.Open(VendorOrderFile);
Excel.Worksheet xlPOWorksheet = CommonFunctions.GetWorksheet(xlPOWorkbook, SelectedDateTimeString);
if (xlPOWorksheet == null)
{
MessageBox.Show(this, "Please provide Correct Vendor Order file with given Date sheet!!!", "Vendor Purchase Order", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Provide Correct Vendor Order file";
xlPOWorkbook.Close(false);
return;
}
Int32 StartRow = 7, StartColumn = 1, DetailsCount = 5;
#region Identify StockItems in PurchaseOrderSheet
List <Int32> ListItemIndexes = new List <Int32>();
Int32 ColumnCount = xlPOWorksheet.UsedRange.Columns.Count;
for (int i = StartColumn + DetailsCount; i <= ColumnCount; i++)
{
String ItemName = xlPOWorksheet.Cells[StartRow, i].Value;
Int32 ItemIndex = -1;
for (int j = 0; j < drItems.Length; j++)
{
if (drItems[j]["StockName"].ToString().Equals(ItemName, StringComparison.InvariantCultureIgnoreCase))
{
ItemIndex = j;
break;
}
}
ListItemIndexes.Add(ItemIndex);
}
#endregion
#region Identify Vendors in SalesOrderSheet
List <Int32> ListVendorIndexes = new List <Int32>();
Int32 RowCount = xlPOWorksheet.UsedRange.Rows.Count + 1;
for (int i = StartRow + 1; i <= RowCount; i++)
{
if (xlPOWorksheet.Cells[i, StartColumn + 1].Value == null)
{
continue;
}
if (xlPOWorksheet.Cells[i, StartColumn + 2].Value == null)
{
continue;
}
String VendorName = xlPOWorksheet.Cells[i, StartColumn + 2].Value;
Int32 VendorIndex = -1;
for (int j = 0; j < drVendors.Length; j++)
{
if (drVendors[j]["VendorName"].ToString().Equals(VendorName, StringComparison.InvariantCultureIgnoreCase))
{
VendorIndex = j;
break;
}
}
if (VendorIndex < 0)
{
continue;
}
String Line = drVendors[VendorIndex]["Line"].ToString().Replace("<", "").Replace(">", "").ToUpper();
if (Line.Trim().Length == 0)
{
Line = "<Blanks>";
}
if (!SelectedLine.Contains(Line) && !CommonFunctions.ListSelectedVendors.Contains(VendorName))
{
ListVendorIndexes.Add(-1);
continue;
}
Excel.Range CountCell = xlPOWorksheet.Cells[i, StartColumn + 1];
Double CountItems = Double.Parse(CountCell.Value.ToString());
if (CountItems <= 1E-6)
{
ListVendorIndexes.Add(-1);
continue;
}
ListVendorIndexes.Add(VendorIndex);
}
#endregion
Excel.Workbook xlWorkbook = null;
xlPOWorksheet.Copy();
xlWorkbook = xlApp.Workbooks[2];
CreateVendorPurchaseOrder(ReportType.PURCHASEORDER, drItems, drVendors, SelectedDateTimeString,
StartRow, StartColumn, ListItemIndexes, ListVendorIndexes, xlWorkbook);
xlPOWorkbook.Close(false);
MessageBox.Show(this, "Purchase Order generated successfully", "Status", MessageBoxButtons.OK);
lblStatus.Text = "Click \"Close Window\" to close this window";
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorPurchaseOrderForm.CreatePurchaseOrders()", ex);
}
finally
{
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
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);
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
Excel.Application xlApp = new Excel.Application();
try
{
DataTable dtItemMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("ItemMaster", MasterFilePath, "*");
DataTable dtSellerMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("SellerMaster", MasterFilePath, "*");
List <String> ListVendors = dtItemMaster.AsEnumerable().Select(s => s.Field <String>("VendorName")).Distinct().ToList();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Add(); //.Open(MasterFilePath);
Excel.Worksheet xlWorkSheet = xlWorkbook.Worksheets.Add(); //xlWorkbook.Worksheets[xlWorkbook.Worksheets.Count - 1];
xlWorkSheet.Name = dateTimeOrderSheet.Value.ToString("dd-MM-yyyy");
#region Print Header
List <String> HeaderItems = new List <String>();
HeaderItems.Add("Sl.No.");
HeaderItems.Add("Total Items");
HeaderItems.Add("Name");
HeaderItems.Add("Line");
HeaderItems.Add("Contact Details");
DataRow[] drItems = dtItemMaster.Select("", "SlNo asc");
Int32 ProgressBarCount = HeaderItems.Count + (drItems.Length * 2) + dtSellerMaster.Rows.Count;
Int32 StartRow = 5, StartCol = 1, Counter = 0;
for (int i = 0; i < HeaderItems.Count; i++)
{
Excel.Range xlRange = xlWorkSheet.Cells[StartRow, StartCol + i];
xlRange.Value = HeaderItems[i];
if (!(HeaderItems[i].Equals("Name") || HeaderItems[i].Equals("Contact Details") || HeaderItems[i].Equals("Line")))
{
xlRange.Orientation = 90;
}
xlRange.Font.Bold = true;
xlRange.Interior.Color = Color.FromArgb(242, 220, 219);
Counter++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
for (int i = 0; i < drItems.Length; i++)
{
Excel.Range xlRange = xlWorkSheet.Cells[StartRow, StartCol + HeaderItems.Count + i];
xlRange.Value = drItems[i]["ItemName"].ToString();
xlRange.Orientation = 90;
xlRange.Font.Bold = true;
if (chkBoxMarkVendors.Checked)
{
xlRange.Interior.Color = ListColors[ListVendors.IndexOf(drItems[i]["VendorName"].ToString()) % ListColors.Count];
}
else
{
xlRange.Interior.Color = Color.FromArgb(242, 220, 219);
}
Counter++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
#endregion
#region Print Sellers
DataRow[] drSellers = dtSellerMaster.Select("", "SlNo asc");
for (int i = 0; i < drSellers.Length; i++)
{
xlWorkSheet.Cells[StartRow + i + 1, StartCol].Value = (i + 1);
Excel.Range xlRange1 = xlWorkSheet.Cells[StartRow + i + 1, StartCol + HeaderItems.Count];
Excel.Range xlRange2 = xlWorkSheet.Cells[StartRow + i + 1, StartCol + HeaderItems.Count + drItems.Length - 1];
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 1].Formula = "=Count(" + xlRange1.Address[false, false] + ":" + xlRange2.Address[false, false] + ")";
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 2].Value = drSellers[i]["SellerName"].ToString();
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 3].Value = drSellers[i]["Line"].ToString();
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 4].Value = ((drSellers[i]["Phone"] == DBNull.Value) ? "" : drSellers[i]["Phone"].ToString());
Counter++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
#endregion
#region Print Total Quantity & Price
xlWorkSheet.Cells[StartRow - 3, StartCol + 2].Value = "Price";
Excel.Range tmpxlRange = xlWorkSheet.Cells[StartRow - 2, StartCol + 2];
tmpxlRange.Value = "Total Quantity";
tmpxlRange.Font.Bold = true;
tmpxlRange.Interior.Color = Color.FromArgb(141, 180, 226);
for (int i = 0; i < HeaderItems.Count; i++)
{
xlWorkSheet.Cells[StartRow - 2, StartCol + i].Interior.Color = Color.FromArgb(141, 180, 226);
}
//xlWorkSheet.Cells[StartRow - 2, StartCol].Interior.Color = Color.FromArgb(141, 180, 226);
//xlWorkSheet.Cells[StartRow - 2, StartCol + 1].Interior.Color = Color.FromArgb(141, 180, 226);
//xlWorkSheet.Cells[StartRow - 2, StartCol + 3].Interior.Color = Color.FromArgb(141, 180, 226);
for (int i = 0; i < drItems.Length; i++)
{
Excel.Range xlRange1 = xlWorkSheet.Cells[StartRow + 1, StartCol + HeaderItems.Count + i];
Excel.Range xlRange2 = xlWorkSheet.Cells[StartRow + drSellers.Length, StartCol + HeaderItems.Count + i];
Excel.Range xlRange = xlWorkSheet.Cells[StartRow - 2, StartCol + HeaderItems.Count + i];
xlRange.Formula = "=Sum(" + xlRange1.Address[false, false] + ":" + xlRange2.Address[false, false] + ")";
xlRange.Font.Bold = true;
xlRange.Interior.Color = Color.FromArgb(141, 180, 226);
xlRange = xlWorkSheet.Cells[StartRow - 3, StartCol + HeaderItems.Count + i];
xlRange.Value = drItems[i]["SellingPrice"].ToString();
xlRange.NumberFormat = "#,##0.00";
Counter++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
#endregion
xlWorkSheet.UsedRange.Columns.AutoFit();
ReportProgressFunc(((ProgressBarCount - 1) * 100) / ProgressBarCount);
xlWorkbook.SaveAs(txtBoxOutputFolder.Text + "\\SalesOrder_" + xlWorkSheet.Name + ".xlsx");
xlWorkbook.Close();
CommonFunctions.ReleaseCOMObject(xlWorkbook);
ReportProgressFunc(100);
MessageBox.Show(this, "Created Sales Order Sheet Successfully", "Sales Order Sheet", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("SellerOrderSheetForm.backgroundWorker1_DoWork()", ex);
}
finally
{
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
private void UpdateDetailsFromVendorPOFile()
{
xlApp = new Excel.Application();
try
{
if (txtBoxVendorPOFile.Text.Trim().Length == 0)
{
MessageBox.Show(this, "Vendor Purchase Orders file cannot be blank!!!\nPlease choose Vendor PO File.", "Error!!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
#region Check Vendor History file
if (chkBoxUpdVendorHistory.Checked)
{
if (!CommonFunctions.ValidateFile_Overwrite_TakeBackup(this, Path.GetDirectoryName(VendorPOFile), ref VendorHistoryFile, "VendorHistory.xlsx", "Vendor History"))
{
return;
}
txtBoxVendorHistoryFile.Text = VendorHistoryFile;
}
#endregion
#region Check Product Inventory file
if (chkBoxUpdProductInventory.Checked)
{
if (ProductInventoryFile.Length == 0)
{
ProductInventoryFile = Path.GetDirectoryName(VendorPOFile) + @"\ProductInventory.xlsx";
txtBoxProductInventoryFile.Text = ProductInventoryFile;
}
if (!File.Exists(ProductInventoryFile))
{
MessageBox.Show(this, "Cannot find \"" + ProductInventoryFile + "\" file.\nPlease Provide Valid Product Inventory file.",
"Product Inventory", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
#endregion
#region Check Product Stock History file
if (chkBoxUpdStockHistory.Checked)
{
if (!CommonFunctions.ValidateFile_Overwrite_TakeBackup(this, Path.GetDirectoryName(VendorPOFile), ref ProductStockHistoryFile, "ProductStockHistory.xlsx", "Stock History"))
{
return;
}
txtBoxProductStockHistoryFile.Text = ProductStockHistoryFile;
}
#endregion
ReportProgressFunc(0);
lblStatus.Text = "Reading Vendor Summary...";
DataTable dtVendorSummary = CommonFunctions.ReturnDataTableFromExcelWorksheet("Vendor Summary", VendorPOFile, "*", "A2:K100000");
if (dtVendorSummary == null)
{
MessageBox.Show(this, "Provided Vendor PO file doesn't contain \"Vendor Summary\" Sheet.\nPlease provide correct file.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please provide file with \"Vendor Summary\" sheet";
return;
}
dtVendorSummary.DefaultView.RowFilter = "IsNull([Sl#], 0) > 0";
DataRow[] drVendors = dtVendorSummary.DefaultView.ToTable().Select("", "[Sl#] asc");
Excel.Workbook xlVendorSummaryWorkbook = xlApp.Workbooks.Open(VendorPOFile);
Excel.Worksheet xlVendorSummaryWorksheet = CommonFunctions.GetWorksheet(xlVendorSummaryWorkbook, "Vendor Summary");
DateTime SummaryCreationDate = DateTime.Parse(xlVendorSummaryWorksheet.Cells[1, 2].Value.ToString());
xlVendorSummaryWorkbook.Close(false);
String Message = "";
if (chkBoxUpdVendorHistory.Checked)
{
lblStatus.Text = "Updating Vendor History file";
UpdateVendorHistoryFile(xlApp, drVendors, SummaryCreationDate);
lblStatus.Text = "Completed updating Vendor History file";
Message += "\nVendor History";
}
if (chkBoxUpdStockHistory.Checked || chkBoxUpdProductInventory.Checked)
{
ProductMaster ObjProductMaster = CommonFunctions.ObjProductMaster;
lblStatus.Text = "Loading Product Inventory file";
DataTable dtProductInventory = CommonFunctions.ReturnDataTableFromExcelWorksheet("Inventory", ProductInventoryFile, "*");
DataRow[] drProductsInventory = dtProductInventory.DefaultView.ToTable().Select("", "[StockName] asc");
ObjProductMaster.LoadProductInventoryFile(drProductsInventory);
lblStatus.Text = "Processing Product Inventory file";
for (int i = 0; i < drVendors.Length; i++)
{
lblStatus.Text = "Updating Product Inventory file for Vendor " + (i + 1) + " of " + drVendors.Length;
String SheetName = drVendors[i]["Vendor Name"].ToString().Replace(":", "").
Replace("\\", "").Replace("/", "").
Replace("?", "").Replace("*", "").
Replace("[", "").Replace("]", "");
DataTable dtVendorPO = CommonFunctions.ReturnDataTableFromExcelWorksheet(SheetName, VendorPOFile, "*", "A6:F100000");
dtVendorPO.DefaultView.RowFilter = "IsNull([Sl#No#], 0) > 0";
DataRow[] drProducts = dtVendorPO.DefaultView.ToTable().Select("", "[Sl#No#] asc");
if (SheetName.Equals("Stock", StringComparison.InvariantCultureIgnoreCase))
{
ObjProductMaster.UpdateProductInventoryDataFromPO(drProducts, true);
}
else
{
ObjProductMaster.UpdateProductInventoryDataFromPO(drProducts, false);
}
}
ObjProductMaster.ComputeStockNetData("Purchase");
if (chkBoxUpdProductInventory.Checked)
{
lblStatus.Text = "Updating Product Inventory file";
ObjProductMaster.UpdateProductInventoryFile(xlApp, SummaryCreationDate, ProductInventoryFile);
lblStatus.Text = "Completed updating Product Inventory file";
Message += "\nProduct Inventory";
}
if (chkBoxUpdStockHistory.Checked)
{
lblStatus.Text = "Updating Product Stock History file";
ObjProductMaster.UpdateProductStockHistoryFile(xlApp, SummaryCreationDate, "Purchase", ProductStockHistoryFile);
lblStatus.Text = "Completed updating Product Stock History file";
Message += "\nProduct Stock History";
}
CommonFunctions.ObjProductMaster.ResetStockProducts();
}
MessageBox.Show(this, "Updated following details successfully:" + Message, "Update Purchases", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("UpdateProductPurchasesForm.UpdateDetailsFromVendorPOFile()", ex);
}
finally
{
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
private void UpdateSellerMaster()
{
Excel.Application xlApp = new Excel.Application();
try
{
if (txtBoxSellerSummaryFile.Text.Trim().Length == 0)
{
MessageBox.Show(this, "Seller Summary file cannot be blank!!!\nPlease choose Seller Summary File.", "Error!!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (chkBoxUpdSellerHistory.Checked)
{
if (!CommonFunctions.ValidateFile_Overwrite_TakeBackup(this, Path.GetDirectoryName(SellerSummaryFilePath), ref SellerHistoryFilePath, "SellerHistory.xlsx", "Seller History"))
{
return;
}
txtBoxSellerHistoryFile.Text = SellerHistoryFilePath;
}
ReportProgressFunc(0);
lblStatus.Text = "Reading Seller Summary...";
DataTable dtSellerSummary = CommonFunctions.ReturnDataTableFromExcelWorksheet("Seller Summary", SellerSummaryFilePath, "*", "A2:K100000");
if (dtSellerSummary == null)
{
MessageBox.Show(this, "Provided Seller Summary file doesn't contain \"Seller Summary\" Sheet.\nPlease provide correct file.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please provide file with \"Seller Summary\" sheet";
return;
}
DataColumn BalanceColumn = new DataColumn("Balance", Type.GetType("System.Double"), "IsNull([Net Sale], 0) + IsNull([OB], 0) - IsNull([Cash], 0)");
BalanceColumn.DefaultValue = 0;
dtSellerSummary.Columns.Add(BalanceColumn);
dtSellerSummary.DefaultView.RowFilter = "IsNull([Sl#], 0) > 0";
DataRow[] drSellers = dtSellerSummary.DefaultView.ToTable().Select("", "[Sl#] asc");
Excel.Workbook xlSellerSummaryWorkbook = xlApp.Workbooks.Open(SellerSummaryFilePath);
Excel.Worksheet xlSellerSummaryWorksheet = CommonFunctions.GetWorksheet(xlSellerSummaryWorkbook, "Seller Summary");
DateTime SummaryCreationDate = DateTime.Parse(xlSellerSummaryWorksheet.Cells[1, 2].Value.ToString());
xlSellerSummaryWorkbook.Close(false);
String Message = "";
if (chkBoxUpdSellerMaster.Checked)
{
lblStatus.Text = "Updating Seller Master file";
UpdateSellerMasterData(xlApp, drSellers);
lblStatus.Text = "Completed updating Seller Master file";
Message += "\nSeller Master";
}
if (chkBoxUpdSellerHistory.Checked)
{
lblStatus.Text = "Updating Seller History file";
UpdateSellerHistory(xlApp, drSellers, SummaryCreationDate);
lblStatus.Text = "Completed updating Seller History file";
Message += "\nSeller History";
}
if (chkBoxUpdProductSales.Checked)
{
lblStatus.Text = "Updating Product Sales file";
UpdateProductData(xlApp, drSellers, SummaryCreationDate);
lblStatus.Text = "Completed updating Product Sales file";
Message += "\nProduct Inventory\nProduct Stock History";
}
MessageBox.Show(this, "Updated following details successfully:" + Message, "Update Sales", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("UpdateOrderMasterForm.UpdateSellerMaster()", ex);
}
finally
{
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
private void CreateVendorOrderSheet()
{
Excel.Application xlApp = new Excel.Application();
try
{
ProductMaster ObjProductMaster = CommonFunctions.ObjProductMaster;
ObjProductMaster.ResetStockProducts();
lblStatus.Text = "Loading Product Inventory file";
DataTable dtProductInventory = CommonFunctions.ReturnDataTableFromExcelWorksheet("Inventory", ProductInventoryFile, "*");
if (dtProductInventory == null)
{
MessageBox.Show(this, "Product Inventory does not contain \"Inventory\" sheet", "Create Vendor Order Sheet", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
DataRow[] drProductsInventory = dtProductInventory.DefaultView.ToTable().Select("", "[StockName] asc");
ObjProductMaster.LoadProductInventoryFile(drProductsInventory);
lblStatus.Text = "Loading Product Sales from Stock History file";
DataTable dtProductStockHistory = CommonFunctions.ReturnDataTableFromExcelWorksheet("Stock History", ProductStockHistoryFile, "[PO Date], [Type], [Stock Name], [Receive Qty]");
if (dtProductStockHistory == null)
{
MessageBox.Show(this, "Product Stock History does not contain \"Stock History\" sheet", "Create Vendor Order Sheet", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
ObjProductMaster.LoadProductPastSalesFromStockHistoryFile(dtProductStockHistory, DateTime.Today, (Int32)numUpDownTimePeriod.Value, ReportSettings.GetTimePeriodUnits(cmbBoxTimePeriodUnits.SelectedItem.ToString()));
lblStatus.Text = "Creating Vendor Order Sheet";
DataTable dtItemMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("ItemMaster", MasterFilePath, "*");
DataTable dtVendorMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("VendorMaster", MasterFilePath, "*");
List <String> ListVendors = dtItemMaster.AsEnumerable().Select(s => s.Field <String>("VendorName")).Distinct().ToList();
DataRow[] drItems = dtItemMaster.Select("", "SlNo asc");
Excel.Workbook xlWorkbook = xlApp.Workbooks.Add();
Excel.Worksheet xlWorkSheet = xlWorkbook.Worksheets.Add();
xlWorkSheet.Name = dateTimePickerVendorOrderDate.Value.ToString("dd-MM-yyyy");
#region Print Header
List <String> HeaderItems = new List <String>();
HeaderItems.Add("Sl.No.");
HeaderItems.Add("Total Items");
HeaderItems.Add("Name");
HeaderItems.Add("Line");
HeaderItems.Add("Contact Details");
Int32 ProgressBarCount = HeaderItems.Count + (drItems.Length * 2) + dtVendorMaster.Rows.Count;
Int32 StartRow = 7, StartCol = 1, Counter = 0;
for (int i = 0; i < HeaderItems.Count; i++)
{
Excel.Range xlRange = xlWorkSheet.Cells[StartRow, StartCol + i];
xlRange.Value = HeaderItems[i];
if (!(HeaderItems[i].Equals("Name") || HeaderItems[i].Equals("Contact Details") || HeaderItems[i].Equals("Line")))
{
xlRange.Orientation = 90;
}
xlRange.Font.Bold = true;
xlRange.Interior.Color = Color.FromArgb(242, 220, 219);
Counter++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
Dictionary <String, DataRow> ListProducts = new Dictionary <String, DataRow>();
for (int i = 0, j = 0; i < drItems.Length; i++)
{
if (ListProducts.ContainsKey(drItems[i]["StockName"].ToString().Trim().ToUpper()))
{
continue;
}
Excel.Range xlRange = xlWorkSheet.Cells[StartRow, StartCol + HeaderItems.Count + j];
xlRange.Value = drItems[i]["StockName"].ToString();
xlRange.Orientation = 90;
xlRange.Font.Bold = true;
xlRange.Interior.Color = Color.FromArgb(242, 220, 219);
Counter++;
j++;
ListProducts.Add(drItems[i]["StockName"].ToString().Trim().ToUpper(), drItems[i]);
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
#endregion
#region Print Vendors
DataRow[] drVendors = dtVendorMaster.Select("", "SlNo asc");
for (int i = 0; i < drVendors.Length; i++)
{
xlWorkSheet.Cells[StartRow + i + 1, StartCol].Value = (i + 1);
Excel.Range xlRange1 = xlWorkSheet.Cells[StartRow + i + 1, StartCol + HeaderItems.Count];
Excel.Range xlRange2 = xlWorkSheet.Cells[StartRow + i + 1, StartCol + HeaderItems.Count + ListProducts.Count - 1];
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 1].Formula = "=Count(" + xlRange1.Address[false, false] + ":" + xlRange2.Address[false, false] + ")";
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 2].Value = drVendors[i]["VendorName"].ToString();
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 3].Value = drVendors[i]["Line"].ToString();
xlWorkSheet.Cells[StartRow + i + 1, StartCol + 4].Value = ((drVendors[i]["Phone"] == DBNull.Value) ? "" : drVendors[i]["Phone"].ToString());
Counter++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
#endregion
#region Print Price, Last N Day Sale, Current stock & TotalQuantity
Int32 PriceRowNum = StartRow - 5, PastSaleRowNum = StartRow - 4, CurrStockRowNum = StartRow - 3, TotalQtyRowNum = StartRow - 2;
xlWorkSheet.Cells[PriceRowNum, StartCol + 2].Value = "Price";
xlWorkSheet.Cells[PastSaleRowNum, StartCol + 2].Value = "Last " + (Int32)numUpDownTimePeriod.Value + " " + cmbBoxTimePeriodUnits.SelectedItem + " of Sales";
xlWorkSheet.Cells[CurrStockRowNum, StartCol + 2].Value = "Current Stock";
Excel.Range tmpxlRange = xlWorkSheet.Cells[TotalQtyRowNum, StartCol + 2];
tmpxlRange.Value = "Total Quantity";
tmpxlRange.Font.Bold = true;
tmpxlRange.Interior.Color = Color.FromArgb(141, 180, 226);
for (int i = 0; i < HeaderItems.Count; i++)
{
xlWorkSheet.Cells[TotalQtyRowNum, StartCol + i].Interior.Color = Color.FromArgb(141, 180, 226);
}
Int32 ProductCount = 0;
foreach (KeyValuePair <String, DataRow> item in ListProducts)
{
Excel.Range xlRange1 = xlWorkSheet.Cells[StartRow + 1, StartCol + HeaderItems.Count + ProductCount];
Excel.Range xlRange2 = xlWorkSheet.Cells[StartRow + drVendors.Length, StartCol + HeaderItems.Count + ProductCount];
Excel.Range xlRange = xlWorkSheet.Cells[TotalQtyRowNum, StartCol + HeaderItems.Count + ProductCount];
xlRange.Formula = "=Sum(" + xlRange1.Address[false, false] + ":" + xlRange2.Address[false, false] + ")";
xlRange.Font.Bold = true;
xlRange.Interior.Color = Color.FromArgb(141, 180, 226);
xlWorkSheet.Cells[PriceRowNum, StartCol + HeaderItems.Count + ProductCount].Value = item.Value["PurchasePrice"].ToString();
StockProductDetails ObjStockProductDetails = ObjProductMaster.GetStockProductDetails(item.Key.Trim());
if (ObjStockProductDetails != null)
{
xlWorkSheet.Cells[PastSaleRowNum, StartCol + HeaderItems.Count + ProductCount].Value = ObjStockProductDetails.SaleQty;
xlWorkSheet.Cells[CurrStockRowNum, StartCol + HeaderItems.Count + ProductCount].Value = ObjStockProductDetails.Inventory;
}
Counter++;
ProductCount++;
ReportProgressFunc((Counter * 100) / ProgressBarCount);
}
#endregion
xlWorkSheet.UsedRange.Columns.AutoFit();
ReportProgressFunc(((ProgressBarCount - 1) * 100) / ProgressBarCount);
xlWorkbook.SaveAs(txtBoxSaveFolderPath.Text + "\\VendorOrder_" + xlWorkSheet.Name + ".xlsx");
xlWorkbook.Close();
CommonFunctions.ReleaseCOMObject(xlWorkbook);
ReportProgressFunc(100);
lblStatus.Text = "Completed creation of Vendor Order Sheet";
MessageBox.Show(this, "Created Vendor Order Sheet Successfully", "Status", MessageBoxButtons.OK);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("VendorOrderSheetForm.CreateVendorOrderSheet()", ex);
}
finally
{
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}
private void CreateSellerReport()
{
Excel.Application xlApp = new Excel.Application();
try
{
ReportProgressFunc(0);
if (String.IsNullOrEmpty(SellerHistoryFilePath))
{
MessageBox.Show(this, "Please select Seller History file!!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please select Seller History file";
return;
}
if (String.IsNullOrEmpty(SaveFolderPath))
{
MessageBox.Show(this, "Please select Save Folder path!!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please select Save folder";
return;
}
if (cmbBoxSellerList.SelectedItem == null || String.IsNullOrEmpty(cmbBoxSellerList.SelectedItem.ToString()))
{
MessageBox.Show(this, "Please select valid Seller name from list!!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please select valid Seller";
return;
}
String SelectedSellerName = cmbBoxSellerList.SelectedItem.ToString().Trim();
if (ListSellerNames.FindIndex(e => e.Trim().Equals(SelectedSellerName, StringComparison.InvariantCultureIgnoreCase)) < 0)
{
MessageBox.Show(this, "Unable to find the Seller!!!\nPlease select valid Seller name from list.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please select valid Seller name from list";
return;
}
ReportProgressFunc(1);
lblStatus.Text = "Reading Seller History.....";
DataTable dtSellerSummary = CommonFunctions.ReturnDataTableFromExcelWorksheet("Seller History", SellerHistoryFilePath, "*");
if (dtSellerSummary == null)
{
MessageBox.Show(this, "Provided Seller History file doesn't contain \"Seller History\" Sheet.\nPlease provide correct file.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblStatus.Text = "Please provide file with \"Seller History\" sheet";
return;
}
ReportProgressFunc(10);
dtSellerSummary.CaseSensitive = false;
dtSellerSummary.DefaultView.RowFilter = "Trim([Seller Name]) = '" + SelectedSellerName + "'";
if (chkBoxDateFilter.Checked)
{
dtSellerSummary.DefaultView.RowFilter += " and [Create Date] >= '" + dateTimePickerStart.Value.ToString("dd-MMM-yyyy")
+ "' and [Create Date] <= '" + dateTimePickerEnd.Value.ToString("dd-MMM-yyyy") + "'";
}
lblStatus.Text = "Filtering Seller History.....";
DataRow[] drSellerRecords = dtSellerSummary.DefaultView.ToTable().Select("", "[Create Date] asc, [Bill#] asc");
if (drSellerRecords == null || drSellerRecords.Length == 0)
{
MessageBox.Show(this, "No data found for the given filters in Seller History.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
lblStatus.Text = "No data found";
return;
}
//Create Date Update Date Bill# Seller Name Sale Cancel Return Discount Total Tax Net Sale OB Cash Balance
Int32 ProgressBarCount = drSellerRecords.Length;
lblStatus.Text = "Creating Seller Report.....";
Excel.Workbook xlSellerReportWorkbook = xlApp.Workbooks.Add();
Excel.Worksheet xlSellerReportWorksheet = xlSellerReportWorkbook.Worksheets[1];
Excel.Worksheet xlSheet = CommonFunctions.GetWorksheet(xlSellerReportWorkbook, "Sheet2");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlSellerReportWorkbook, "Sheet3");
if (xlSheet != null)
{
xlSheet.Delete();
}
Int32 StartRow = 1, StartCol = 1, LastCol = 0, SellerNameColIndex = -1;
String SheetName = SelectedSellerName.Replace(":", "").
Replace("\\", "").Replace("/", "").
Replace("?", "").Replace("*", "").
Replace("[", "").Replace("]", "").
Replace("<", "").Replace(">", "").
Replace("|", "").Replace("\"", "");
xlSellerReportWorksheet.Name = ((SheetName.Length > 30) ? SheetName.Substring(0, 30) : SheetName);
for (int i = 0; i < dtSellerSummary.Columns.Count; i++)
{
if (dtSellerSummary.Columns[i].ColumnName.Equals("Seller Name"))
{
SellerNameColIndex = i;
continue;
}
xlSellerReportWorksheet.Cells[StartRow, StartCol + LastCol].Value = dtSellerSummary.Columns[i].ColumnName;
LastCol++;
}
Excel.Range xlRange = xlSellerReportWorksheet.Range[xlSellerReportWorksheet.Cells[StartRow, StartCol], xlSellerReportWorksheet.Cells[StartRow, LastCol]];
xlRange.Font.Bold = true;
for (int i = 0; i < drSellerRecords.Length; i++)
{
DataRow dtRow = drSellerRecords[i];
for (int j = 0, k = 0; j < dtRow.ItemArray.Length; j++)
{
if (j == SellerNameColIndex)
{
continue;
}
xlSellerReportWorksheet.Cells[StartRow + 1 + i, StartCol + k].Value = dtRow[j];
if (k >= 3)
{
xlSellerReportWorksheet.Cells[StartRow + 1 + i, StartCol + k].NumberFormat = "#,##0.00";
}
k++;
}
ReportProgressFunc(10 + ((i + 1) * 90 / ProgressBarCount));
}
xlRange = xlSellerReportWorksheet.Range[xlSellerReportWorksheet.Cells[StartRow, StartCol], xlSellerReportWorksheet.Cells[StartRow + drSellerRecords.Length, LastCol]];
SellerInvoiceForm.SetAllBorders(xlRange);
xlSellerReportWorksheet.UsedRange.Columns.AutoFit();
xlSellerReportWorkbook.SaveAs(SaveFolderPath + @"\SellerReport_" + SheetName.Replace(" ", "_") + ".xlsx");
xlSellerReportWorkbook.Close();
ReportProgressFunc(100);
MessageBox.Show(this, "Created Seller Report for \"" + SelectedSellerName + "\"", "Status", MessageBoxButtons.OK, MessageBoxIcon.Information);
lblStatus.Text = "Created Seller Report";
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("SellerHistoryReportForm.CreateSellerReport()", ex);
}
finally
{
xlApp.Quit();
CommonFunctions.ReleaseCOMObject(xlApp);
}
}