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;
}
}
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);
}
}