private void LoadDetailsFromOrderMaster()
{
try
{
ProductLine CurrProductLine = CommonFunctions.ListProductLines[CommonFunctions.SelectedProductLineIndex];
DataTable dtProductMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("ItemMaster", CommonFunctions.MasterFilePath, "*");
DataTable dtPriceGroupMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("PriceGroupMaster", CommonFunctions.MasterFilePath, "*");
DataTable dtHSNMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("HSNMaster", CommonFunctions.MasterFilePath, "*");
CurrProductLine.LoadProductMaster(dtProductMaster, dtPriceGroupMaster, dtHSNMaster);
lblStatus.Text = "Completed loading Product details";
ReportProgressFunc(25);
DataTable dtDiscountGroupMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("DiscountGroupMaster", CommonFunctions.MasterFilePath, "*");
DataTable dtSellerMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("SellerMaster", CommonFunctions.MasterFilePath, "*");
CurrProductLine.LoadSellerMaster(dtSellerMaster, dtDiscountGroupMaster);
lblStatus.Text = "Completed loading Seller details";
ReportProgressFunc(50);
DataTable dtVendorMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("VendorMaster", CommonFunctions.MasterFilePath, "*");
CurrProductLine.LoadVendorMaster(dtVendorMaster, dtDiscountGroupMaster);
lblStatus.Text = "Completed loading Vendor details";
ReportProgressFunc(75);
CommonFunctions.SelectProductLine(CommonFunctions.SelectedProductLineIndex);
ReportProgressFunc(100);
lblStatus.Text = "Completed loading details from OrderMaster file";
MessageBox.Show(this, "Completed loading details from OrderMaster file", "Order Master", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("OrderMasterForm.LoadDetailsFromOrderMaster()", ex);
}
}
private void UpdateProductData(Excel.Application xlApp, DataRow[] drSellers, DateTime SummaryCreationDate)
{
try
{
ProductMaster ObjProductMaster = CommonFunctions.ObjProductMaster;
lblStatus.Text = "Loading Product Inventory file";
DataTable dtProductInventory = CommonFunctions.ReturnDataTableFromExcelWorksheet("Inventory", ProductInventoryFilePath, "*");
DataRow[] drProductsInventory = dtProductInventory.DefaultView.ToTable().Select("", "[StockName] asc");
ObjProductMaster.LoadProductInventoryFile(drProductsInventory);
lblStatus.Text = "Processing Product Inventory file";
for (int i = 0; i < drSellers.Length; i++)
{
lblStatus.Text = "Updating Product Inventory file for Seller " + (i + 1) + " of " + drSellers.Length;
String SheetName = drSellers[i]["Seller Name"].ToString().Trim().Replace(":", "").
Replace("\\", "").Replace("/", "").
Replace("?", "").Replace("*", "").
Replace("[", "").Replace("]", "");
//DataTable dtSellerInvoice = CommonFunctions.ReturnDataTableFromExcelWorksheet(SheetName, SellerSummaryFilePath, "*", "A6:F100000");
ReportType EnumReportType = (CommonFunctions.ObjGeneralSettings.SummaryLocation == 0 ? ReportType.INVOICE : ReportType.QUOTATION);
Invoice ObjInvoice = CommonFunctions.GetInvoiceTemplate(EnumReportType);
DataTable dtSellerInvoice = ObjInvoice.LoadInvoice(SheetName, SellerSummaryFilePath);
if (dtSellerInvoice == null)
{
continue;
}
dtSellerInvoice.DefaultView.RowFilter = "IsNull([Sl No], 0) > 0";
DataRow[] drProducts = dtSellerInvoice.DefaultView.ToTable().Select("", "[Sl No] asc");
ObjProductMaster.UpdateProductInventoryDataFromInvoice(drProducts);
}
ObjProductMaster.ComputeStockNetData("Sale");
lblStatus.Text = "Updating Product Inventory file";
ObjProductMaster.UpdateProductInventoryFile(xlApp, SummaryCreationDate, ProductInventoryFilePath);
lblStatus.Text = "Completed updating Product Inventory file";
lblStatus.Text = "Updating Product Stock History file";
ObjProductMaster.UpdateProductStockHistoryFile(xlApp, SummaryCreationDate, "Sale", ProductStockHistoryFilePath);
lblStatus.Text = "Completed updating Product Stock History file";
CommonFunctions.ObjProductMaster.ResetStockProducts();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("UpdateOrderMasterForm.UpdateProductData()", ex);
throw;
}
}
public override DataTable LoadInvoice(String SheetName, String ExcelWorkbookPath)
{
try
{
DataTable dtSellerInvoice = CommonFunctions.ReturnDataTableFromExcelWorksheet(SheetName, ExcelWorkbookPath, "*", "A6:F100000");
if (dtSellerInvoice == null)
{
return(null);
}
dtSellerInvoice.Columns.Add(new DataColumn("TotalTax", Type.GetType("System.Double"), "0"));
dtSellerInvoice.Columns.Add(new DataColumn("Discount", Type.GetType("System.Double"), "0"));
dtSellerInvoice.DefaultView.RowFilter = "IsNull([Sl No], 0) > 0";
return(dtSellerInvoice);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("InvoiceVAT.LoadInvoice()", ex);
throw;
}
}
public override DataTable LoadInvoice(String SheetName, String ExcelWorkbookPath)
{
try
{
DataTable dtSellerInvoice = CommonFunctions.ReturnDataTableFromExcelWorksheet(SheetName, ExcelWorkbookPath, "*", "A8:P100000");
if (dtSellerInvoice == null)
{
return(null);
}
dtSellerInvoice.Rows.RemoveAt(0);
dtSellerInvoice.Columns["Description of Goods"].ColumnName = "Item Name";
dtSellerInvoice.Columns["Ord Qty"].ColumnName = "Order Quantity";
dtSellerInvoice.Columns["Sale Qty"].ColumnName = "Sales Quantity";
dtSellerInvoice.Columns.Add("TotalTax", Type.GetType("System.Double"), "Convert([F12], 'System.Double') + Convert([F14], 'System.Double')");
dtSellerInvoice.DefaultView.RowFilter = "IsNull([Sl No], 0) > 0";
return(dtSellerInvoice);
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("InvoiceGST.LoadInvoice()", ex);
throw;
}
}
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 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 UpdateVendorHistoryFile(Excel.Application xlApp, DataRow[] drVendors, DateTime SummaryCreationDate)
{
try
{
ReportProgressFunc(0);
Excel.Workbook xlVendorHistoryWorkbook;
Excel.Worksheet xlVendorHistoryWorksheet;
Int32 ProgressBarCount = drVendors.Length, CurrVendorCount = 0;
List <String> ListVendorKeys;
String[] Header = new String[] { "Create Date", "Update Date", "Bill#", "Vendor Name", "Sale", "Cancel", "Return", "Discount", "Total Tax", "Net Sale", "Cash" };
if (!File.Exists(VendorHistoryFile))
{
xlVendorHistoryWorkbook = xlApp.Workbooks.Add();
xlVendorHistoryWorksheet = xlVendorHistoryWorkbook.Worksheets.Add();
xlVendorHistoryWorksheet.Name = "Vendor History";
for (int i = 0; i < Header.Length; i++)
{
xlVendorHistoryWorksheet.Cells[1, i + 1].Value = Header[i];
}
Excel.Range xlRange1 = xlVendorHistoryWorksheet.Range[xlVendorHistoryWorksheet.Cells[1, 1], xlVendorHistoryWorksheet.Cells[1, Header.Length]];
xlRange1.Font.Bold = true;
SellerInvoiceForm.SetAllBorders(xlRange1);
xlVendorHistoryWorkbook.SaveAs(VendorHistoryFile);
Excel.Worksheet xlSheet = CommonFunctions.GetWorksheet(xlVendorHistoryWorkbook, "Sheet1");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlVendorHistoryWorkbook, "Sheet2");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlVendorHistoryWorkbook, "Sheet3");
if (xlSheet != null)
{
xlSheet.Delete();
}
ListVendorKeys = new List <String>();
}
else
{
DataTable dtVendorHistory = CommonFunctions.ReturnDataTableFromExcelWorksheet("Vendor History", VendorHistoryFile, "[Create Date], [Bill#], [Vendor Name]");
ListVendorKeys = dtVendorHistory.AsEnumerable().Select(s => s.Field <DateTime>("Create Date").ToString("dd-MMM-yyyy")
+ "||" + s.Field <Double>("Bill#").ToString()
+ "||" + s.Field <String>("Vendor Name").Trim().ToUpper()).Distinct().ToList();
xlVendorHistoryWorkbook = xlApp.Workbooks.Open(VendorHistoryFile);
xlVendorHistoryWorksheet = CommonFunctions.GetWorksheet(xlVendorHistoryWorkbook, "Vendor History");
}
Int32 RowCount = xlVendorHistoryWorksheet.UsedRange.Rows.Count;
Int32 ColumnCount = xlVendorHistoryWorksheet.UsedRange.Columns.Count;
Int32 StartRow = RowCount, StartColumn = 1, LastRow = 0;
for (int i = 0; i < drVendors.Length; i++)
{
CurrVendorCount++;
DataRow dtRow = drVendors[i];
if (dtRow[0] == DBNull.Value)
{
continue;
}
if (String.IsNullOrEmpty(dtRow[0].ToString()))
{
continue;
}
if (ListVendorKeys.Contains(SummaryCreationDate.ToString("dd-MMM-yyyy")
+ "||" + dtRow["Bill#"].ToString().Trim().ToUpper()
+ "||" + dtRow["Vendor Name"].ToString().Trim().ToUpper()))
{
continue;
}
LastRow++;
xlVendorHistoryWorksheet.Cells[StartRow + LastRow, StartColumn].Value = SummaryCreationDate.ToString("dd-MMM-yyyy");
xlVendorHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1].Value = DateTime.Now.ToString("dd-MMM-yyyy");
for (int j = 1; j < dtRow.ItemArray.Length; j++)
{
xlVendorHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1 + j].Value = dtRow[j].ToString();
if (j >= 3)
{
xlVendorHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1 + j].NumberFormat = "#,##0.00";
}
}
ReportProgressFunc((CurrVendorCount * 100) / ProgressBarCount);
lblStatus.Text = "Updated " + CurrVendorCount + " of " + ProgressBarCount + " Vendors data in Vendor History";
}
ReportProgressFunc(100);
Excel.Range xlRange = xlVendorHistoryWorksheet.Range[xlVendorHistoryWorksheet.Cells[StartRow, StartColumn], xlVendorHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + Header.Length - 1]];
SellerInvoiceForm.SetAllBorders(xlRange);
xlVendorHistoryWorkbook.Save();
xlVendorHistoryWorkbook.Close();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("UpdateProductPurchasesForm.UpdateVendorHistoryFile()", ex);
throw;
}
}
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);
}
}
public SellerListForm(SellerInvoiceForm ObjForm)
{
InitializeComponent();
ObjCreateSellerInvoice = ObjForm;
dtSellerMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("SellerMaster", ObjCreateSellerInvoice.MasterFilePath, "SellerName,Line");
}
public VendorListForm(VendorPurchaseOrderForm ObjForm)
{
InitializeComponent();
ObjVendorPurchaseOrderForm = ObjForm;
dtVendorMaster = CommonFunctions.ReturnDataTableFromExcelWorksheet("VendorMaster", CommonFunctions.MasterFilePath, "VendorName,Line");
}
private void UpdateSellerHistory(Excel.Application xlApp, DataRow[] drSellers, DateTime SummaryCreationDate)
{
try
{
ReportProgressFunc(0);
Excel.Workbook xlSellerHistoryWorkbook;
Excel.Worksheet xlSellerHistoryWorksheet;
Int32 ProgressBarCount = drSellers.Length, CurrSellerCount = 0;
Boolean SellerHistoryFileExists = File.Exists(SellerHistoryFilePath);
List <String> ListSellerKeys;
String[] Header = new String[] { "Create Date", "Update Date", "Bill#", "Seller Name", "Sale", "Cancel", "Return", "Discount", "Total Tax", "Net Sale", "OB", "Cash", "Balance" };
if (!SellerHistoryFileExists)
{
xlSellerHistoryWorkbook = xlApp.Workbooks.Add();
xlSellerHistoryWorksheet = xlSellerHistoryWorkbook.Worksheets.Add();
xlSellerHistoryWorksheet.Name = "Seller History";
for (int i = 0; i < Header.Length; i++)
{
xlSellerHistoryWorksheet.Cells[1, i + 1].Value = Header[i];
}
Excel.Range xlRange1 = xlSellerHistoryWorksheet.Range[xlSellerHistoryWorksheet.Cells[1, 1], xlSellerHistoryWorksheet.Cells[1, Header.Length]];
xlRange1.Font.Bold = true;
SellerInvoiceForm.SetAllBorders(xlRange1);
xlSellerHistoryWorkbook.SaveAs(SellerHistoryFilePath);
Excel.Worksheet xlSheet = CommonFunctions.GetWorksheet(xlSellerHistoryWorkbook, "Sheet1");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlSellerHistoryWorkbook, "Sheet2");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlSellerHistoryWorkbook, "Sheet3");
if (xlSheet != null)
{
xlSheet.Delete();
}
ListSellerKeys = new List <String>();
}
else
{
DataTable dtSellerHistory = CommonFunctions.ReturnDataTableFromExcelWorksheet("Seller History", SellerHistoryFilePath, "[Create Date], [Bill#], [Seller Name]");
ListSellerKeys = dtSellerHistory.AsEnumerable().Select(s => s.Field <DateTime>("Create Date").ToString("dd-MMM-yyyy")
+ "||" + s.Field <Double>("Bill#").ToString()
+ "||" + s.Field <String>("Seller Name").Trim().ToUpper()).Distinct().ToList();
xlSellerHistoryWorkbook = xlApp.Workbooks.Open(SellerHistoryFilePath);
xlSellerHistoryWorksheet = CommonFunctions.GetWorksheet(xlSellerHistoryWorkbook, "Seller History");
}
Int32 RowCount = xlSellerHistoryWorksheet.UsedRange.Rows.Count;
Int32 ColumnCount = xlSellerHistoryWorksheet.UsedRange.Columns.Count;
Int32 StartRow = RowCount + 1, StartColumn = 1, LastRow = 0;
Int32 OBColumnIndex = drSellers[0].Table.Columns.IndexOf("OB");
Boolean InsertRecord = false;
for (int i = 0; i < drSellers.Length; i++)
{
CurrSellerCount++;
InsertRecord = true;
DataRow dtRow = drSellers[i];
if (dtRow[0] == DBNull.Value)
{
continue;
}
if (String.IsNullOrEmpty(dtRow[0].ToString()))
{
continue;
}
if (ListSellerKeys.Contains(SummaryCreationDate.ToString("dd-MMM-yyyy")
+ "||" + dtRow["Bill#"].ToString().Trim().ToUpper()
+ "||" + dtRow["Seller Name"].ToString().Trim().ToUpper()))
{
InsertRecord = false;
}
if (InsertRecord)
{
InsertRecord = false;
for (int j = 4; j < dtRow.ItemArray.Length - 1; j++)
{
if (OBColumnIndex == j)
{
continue;
}
if (dtRow[j] != DBNull.Value && !String.IsNullOrEmpty(dtRow[j].ToString().Trim()) &&
Double.Parse(dtRow[j].ToString().Trim()) > 1E-4)
{
InsertRecord = true;
break;
}
}
}
if (InsertRecord)
{
xlSellerHistoryWorksheet.Cells[StartRow + LastRow, StartColumn].Value = SummaryCreationDate.ToString("dd-MMM-yyyy");
xlSellerHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1].Value = DateTime.Now.ToString("dd-MMM-yyyy");
for (int j = 1; j < dtRow.ItemArray.Length; j++)
{
xlSellerHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1 + j].Value = dtRow[j].ToString();
if (j >= 3)
{
xlSellerHistoryWorksheet.Cells[StartRow + LastRow, StartColumn + 1 + j].NumberFormat = "#,##0.00";
}
}
LastRow++;
}
ReportProgressFunc((CurrSellerCount * 100) / ProgressBarCount);
lblStatus.Text = "Updated " + CurrSellerCount + " of " + ProgressBarCount + " Sellers data in Seller History";
}
ReportProgressFunc(100);
if (LastRow > 0)
{
Excel.Range xlRange = xlSellerHistoryWorksheet.Range[xlSellerHistoryWorksheet.Cells[StartRow, StartColumn], xlSellerHistoryWorksheet.Cells[StartRow + LastRow - 1, StartColumn + Header.Length - 1]];
SellerInvoiceForm.SetAllBorders(xlRange);
}
xlSellerHistoryWorkbook.Save();
xlSellerHistoryWorkbook.Close();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("UpdateOrderMasterForm.UpdateSellerHistory()", ex);
throw ex;
}
}
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);
}
}