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 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 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);
}
}
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 UpdateSellerMasterData(Excel.Application xlApp, DataRow[] drSellers)
{
try
{
Excel.Workbook xlOrderMasterWorkbook = xlApp.Workbooks.Open(OrderMasterFilePath);
Excel.Worksheet xlSellerMasterWorksheet = CommonFunctions.GetWorksheet(xlOrderMasterWorkbook, "SellerMaster");
Int32 RowCount = xlSellerMasterWorksheet.UsedRange.Rows.Count + 1;
Int32 ColumnCount = xlSellerMasterWorksheet.UsedRange.Columns.Count;
Int32 StartRow = 1, StartColumn = 1, OldBalanceColIndex = -1;
for (int i = 0; i < ColumnCount; i++)
{
if (xlSellerMasterWorksheet.Cells[StartRow, StartColumn + i].Value == null)
{
continue;
}
String ColName = xlSellerMasterWorksheet.Cells[StartRow, StartColumn + i].Value;
if (ColName.Equals("OldBalance", StringComparison.InvariantCultureIgnoreCase))
{
OldBalanceColIndex = StartColumn + i;
break;
}
}
if (OldBalanceColIndex < 0)
{
xlSellerMasterWorksheet.Cells[StartRow, ColumnCount + 1].Value = "OldBalance";
ColumnCount++;
OldBalanceColIndex = ColumnCount;
}
lblStatus.Text = "Processing SellerMaster...";
Int32 ProgressBarCount = drSellers.Length, CurrSellerCount = 0;
for (int i = StartRow + 1; i <= RowCount; i++)
{
if (xlSellerMasterWorksheet.Cells[i, StartColumn + 1].Value == null)
{
continue;
}
String SellerName = xlSellerMasterWorksheet.Cells[i, StartColumn + 1].Value;
Int32 SellerIndex = -1;
for (int j = 0; j < drSellers.Length; j++)
{
if (drSellers[j]["Seller Name"].ToString().Trim().Equals(SellerName.Trim(), StringComparison.InvariantCultureIgnoreCase))
{
SellerIndex = j;
break;
}
}
if (SellerIndex < 0)
{
continue;
}
CurrSellerCount++;
xlSellerMasterWorksheet.Cells[i, OldBalanceColIndex].Value = drSellers[SellerIndex]["Balance"];
ReportProgressFunc((CurrSellerCount * 100) / ProgressBarCount);
lblStatus.Text = "Updated " + CurrSellerCount + " of " + ProgressBarCount + " Sellers data in OrderMaster";
}
ReportProgressFunc(100);
xlOrderMasterWorkbook.Save();
xlOrderMasterWorkbook.Close();
}
catch (Exception ex)
{
CommonFunctions.ShowErrorDialog("UpdateOrderMasterForm.UpdateSellerMasterData()", ex);
throw;
}
}
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 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);
}
}
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;
}
}