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 void LoadProductMaster(DataTable dtProductMaster, DataTable dtPriceGroupMaster, DataTable dtHSNMaster)
{
try
{
ObjProductMaster = new ProductMaster();
ObjProductMaster.Initialize();
for (int i = 0; i < dtPriceGroupMaster.Rows.Count; i++)
{
DataRow dtRow = dtPriceGroupMaster.Rows[i];
//PriceGroup Discount DiscountType Description Default
PriceGroupDetails ObjPriceGroupDetails = new PriceGroupDetails();
ObjPriceGroupDetails.Name = dtRow["PriceGroup"].ToString().Trim();
ObjPriceGroupDetails.Discount = Double.Parse(dtRow["Discount"].ToString().Trim());
ObjPriceGroupDetails.DiscountType = PriceGroupDetails.GetDiscountType(dtRow["DiscountType"].ToString().Trim());
ObjPriceGroupDetails.IsDefault = (Int32.Parse(dtRow["Default"].ToString().Trim()) == 1);
ObjPriceGroupDetails.Description = dtRow["Description"].ToString();
ObjProductMaster.AddPriceGroupToCache(ObjPriceGroupDetails);
}
LoadHSNMasterData(dtHSNMaster);
for (int i = 0; i < dtProductMaster.Rows.Count; i++)
{
DataRow dtRow = dtProductMaster.Rows[i];
//SlNo ItemName VendorName PurchasePrice SellingPrice Wholesale Retail StockName HSNCode UnitsOfMeasurement
ProductDetails ObjProductDetails = new ProductDetails();
ObjProductDetails.ItemName = dtRow["ItemName"].ToString();
ObjProductDetails.StockName = dtRow["StockName"].ToString();
ObjProductDetails.VendorName = dtRow["VendorName"].ToString();
ObjProductDetails.Units = Double.Parse(dtRow["Units"].ToString());
ObjProductDetails.PurchasePrice = Double.Parse(dtRow["PurchasePrice"].ToString());
ObjProductDetails.SellingPrice = Double.Parse(dtRow["SellingPrice"].ToString());
ObjProductDetails.HSNCode = dtRow["HSNCode"].ToString();
ObjProductDetails.UnitsOfMeasurement = dtRow["UnitOfMeasurement"].ToString();
ObjProductDetails.ListPrices = new Double[ObjProductMaster.ListPriceGroups.Count];
for (int j = 0; j < ObjProductDetails.ListPrices.Length; j++)
{
ObjProductDetails.ListPrices[j] = Double.NaN;
if (!dtRow.Table.Columns.Contains(ObjProductMaster.ListPriceGroups[j].Name))
{
continue;
}
if (dtRow[ObjProductMaster.ListPriceGroups[j].Name] == DBNull.Value)
{
continue;
}
if (String.IsNullOrEmpty(dtRow[ObjProductMaster.ListPriceGroups[j].Name].ToString().Trim()))
{
continue;
}
ObjProductDetails.ListPrices[j] = Double.Parse(dtRow[ObjProductMaster.ListPriceGroups[j].Name].ToString().Trim());
}
ObjProductMaster.AddProductToCache(ObjProductDetails);
}
ObjProductMaster.UpdateStockProductIndexes();
ObjProductMaster.UpdateHSNProductIndexes();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("ProductLine.LoadProductMaster()", ex);
}
}
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);
}
}
public void UpdateProductStockHistoryFile(Excel.Application xlApp, DateTime SummaryCreationDate, String TransactionType, String ProductStockHistoryFile)
{
try
{
Excel.Workbook xlProductStockHistory;
Excel.Worksheet xlStockHistoryWorksheet;
String[] Header = new String[] { "PO Date", "Update Date", "Type", "Stock Name", "Order Qty", "Receive Qty", "Net Qty", "Total Cost", "Total Discount", "Total Tax", "Net Cost" };
if (!File.Exists(ProductStockHistoryFile))
{
xlProductStockHistory = xlApp.Workbooks.Add();
xlStockHistoryWorksheet = xlProductStockHistory.Worksheets.Add();
xlStockHistoryWorksheet.Name = "Stock History";
for (int i = 0; i < Header.Length; i++)
{
xlStockHistoryWorksheet.Cells[1, i + 1].Value = Header[i];
}
Excel.Range xlRange1 = xlStockHistoryWorksheet.Range[xlStockHistoryWorksheet.Cells[1, 1], xlStockHistoryWorksheet.Cells[1, Header.Length]];
xlRange1.Font.Bold = true;
SellerInvoiceForm.SetAllBorders(xlRange1);
xlProductStockHistory.SaveAs(ProductStockHistoryFile);
Excel.Worksheet xlSheet = CommonFunctions.GetWorksheet(xlProductStockHistory, "Sheet1");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlProductStockHistory, "Sheet2");
if (xlSheet != null)
{
xlSheet.Delete();
}
xlSheet = CommonFunctions.GetWorksheet(xlProductStockHistory, "Sheet3");
if (xlSheet != null)
{
xlSheet.Delete();
}
}
else
{
xlProductStockHistory = xlApp.Workbooks.Open(ProductStockHistoryFile);
xlStockHistoryWorksheet = CommonFunctions.GetWorksheet(xlProductStockHistory, "Stock History");
}
ProductMaster ObjProductMaster = CommonFunctions.ObjProductMaster;
Int32 RowCount = xlStockHistoryWorksheet.UsedRange.Rows.Count, ColumnCount = xlStockHistoryWorksheet.UsedRange.Columns.Count;
Int32 StartRow = RowCount + 1, PODateColPos = 1, UpdateDateColPos = 2, TypeColPos = 3, StockNameColPos = 4,
OrderQtyColPos = 5, ReceiveQtyColPos = 6, NetQtyColPos = 7, TotalCostColPos = 8, TotalDiscountColPos = 9,
TotalTaxColPos = 10, NetCostColPos = 11;
//PO Date\tUpdate Date\tType\tStock Name\tOrder Qty\tReceive Qty\tNet Qty\tTotal Cost\tTotal Discount\tTotal Tax\tNet Cost
for (int i = 0; i < ColumnCount; i++)
{
if (xlStockHistoryWorksheet.Cells[1, 1 + i].Value == null)
{
break;
}
String ColName = xlStockHistoryWorksheet.Cells[1, 1 + i].Value.ToString().Trim().ToUpper();
switch (ColName)
{
case "PO DATE": PODateColPos = i + 1; break;
case "UPDATE DATE": UpdateDateColPos = i + 1; break;
case "TYPE": TypeColPos = i + 1; break;
case "STOCK NAME": StockNameColPos = i + 1; break;
case "ORDER QTY": OrderQtyColPos = i + 1; break;
case "RECEIVE QTY": ReceiveQtyColPos = i + 1; break;
case "NET QTY": NetQtyColPos = i + 1; break;
case "TOTAL COST": TotalCostColPos = i + 1; break;
case "TOTAL DISCOUNT": TotalDiscountColPos = i + 1; break;
case "TOTAL TAX": TotalTaxColPos = i + 1; break;
case "NET COST": NetCostColPos = i + 1; break;
default: break;
}
}
Int32 StockCounter = 0;
for (int i = 0, j = 0; i < ObjProductMaster.ListStockProducts.Count; i++)
{
StockProductDetails ObjStockProduct = ObjProductMaster.ListStockProducts[i];
if (!ObjStockProduct.IsUpdated)
{
continue;
}
xlStockHistoryWorksheet.Cells[StartRow + j, PODateColPos].Value = SummaryCreationDate.ToString("dd-MMM-yyyy");
xlStockHistoryWorksheet.Cells[StartRow + j, UpdateDateColPos].Value = DateTime.Now.Date.ToString("dd-MMM-yyyy");
xlStockHistoryWorksheet.Cells[StartRow + j, TypeColPos].Value = TransactionType;
xlStockHistoryWorksheet.Cells[StartRow + j, StockNameColPos].Value = ObjStockProduct.StockName;
xlStockHistoryWorksheet.Cells[StartRow + j, OrderQtyColPos].Value = ObjStockProduct.OrderQty;
xlStockHistoryWorksheet.Cells[StartRow + j, ReceiveQtyColPos].Value = ObjStockProduct.RecvdQty;
xlStockHistoryWorksheet.Cells[StartRow + j, NetQtyColPos].Value = ObjStockProduct.NetQty;
xlStockHistoryWorksheet.Cells[StartRow + j, TotalCostColPos].Value = ObjStockProduct.TotalCost;
xlStockHistoryWorksheet.Cells[StartRow + j, TotalDiscountColPos].Value = ObjStockProduct.TotalDiscount;
xlStockHistoryWorksheet.Cells[StartRow + j, TotalTaxColPos].Value = ObjStockProduct.TotalTax;
xlStockHistoryWorksheet.Cells[StartRow + j, NetCostColPos].Value = ObjStockProduct.NetCost;
StockCounter = j;
j++;
}
Excel.Range xlRange = xlStockHistoryWorksheet.Range[xlStockHistoryWorksheet.Cells[StartRow, PODateColPos], xlStockHistoryWorksheet.Cells[StartRow + StockCounter, NetCostColPos]];
SellerInvoiceForm.SetAllBorders(xlRange);
xlProductStockHistory.Save();
xlProductStockHistory.Close();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("ProductMaster.UpdateProductStockHistoryFile()", ex);
throw;
}
}
