public void UpdateProductInventoryFile(Excel.Application xlApp, DateTime SummaryCreationDate, String ProductInventoryFile)
{
try
{
Excel.Workbook xlProductInventory = xlApp.Workbooks.Open(ProductInventoryFile);
Excel.Worksheet xlInventoryWorksheet = CommonFunctions.GetWorksheet(xlProductInventory, "Inventory");
ProductMaster ObjProductMaster = CommonFunctions.ObjProductMaster;
Int32 RowCount = xlInventoryWorksheet.UsedRange.Rows.Count, ColumnCount = xlInventoryWorksheet.UsedRange.Columns.Count;
Int32 StartRow = 1, StockNameColPos = 2, StockColPos = 4, LastPODateColPos = 5, LastUpdateDateColPos = 6;
for (int i = 0; i < ColumnCount; i++)
{
if (xlInventoryWorksheet.Cells[1, 1 + i].Value == null)
{
break;
}
String ColName = xlInventoryWorksheet.Cells[1, 1 + i].Value.ToString().Trim().ToUpper();
switch (ColName)
{
case "STOCKNAME": StockNameColPos = i + 1; break;
case "STOCK": StockColPos = i + 1; break;
case "LASTUPDATEDATE": LastUpdateDateColPos = i + 1; break;
case "LASTPODATE": LastPODateColPos = i + 1; break;
default: break;
}
}
for (int i = 1; i < RowCount; i++)
{
String StockName = xlInventoryWorksheet.Cells[StartRow + i, StockNameColPos].Value.ToString().Trim();
StockProductDetails ObjStockProductDetails = ObjProductMaster.GetStockProductDetails(StockName);
if (ObjStockProductDetails == null || !ObjStockProductDetails.IsUpdated)
{
continue;
}
xlInventoryWorksheet.Cells[StartRow + i, StockColPos].Value = ObjStockProductDetails.NetQty;
xlInventoryWorksheet.Cells[StartRow + i, LastPODateColPos].Value = SummaryCreationDate.ToString("dd-MMM-yyyy");
xlInventoryWorksheet.Cells[StartRow + i, LastUpdateDateColPos].Value = DateTime.Now.Date.ToString("dd-MMM-yyyy");
}
xlProductInventory.Save();
xlProductInventory.Close();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("ProductMaster.UpdateProductInventoryFile()", ex);
throw;
}
}
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);
}
}