public void UpdateProductInventoryDataFromInvoice(DataRow[] drProducts) { try { foreach (DataRow dr in drProducts) { ProductDetails ObjProductDetails = GetProductDetails(dr["Item Name"].ToString().Trim()); if (ObjProductDetails == null) { continue; } StockProductDetails ObjStockProductDetails = ListStockProducts[ObjProductDetails.StockProductIndex]; ObjStockProductDetails.OrderQty += (Double.Parse(dr["Order Quantity"].ToString().Trim()) * ObjProductDetails.Units); if (dr["Sales Quantity"] == DBNull.Value) { continue; } ObjStockProductDetails.RecvdQty += (Double.Parse(dr["Sales Quantity"].ToString().Trim()) * ObjProductDetails.Units); if (dr["Total"] != DBNull.Value) { ObjStockProductDetails.TotalCost += Double.Parse(dr["Total"].ToString().Trim()); ObjStockProductDetails.TotalTax += Double.Parse(dr["TotalTax"].ToString().Trim()); ObjStockProductDetails.TotalDiscount += Double.Parse(dr["Discount"].ToString().Trim()); } ObjStockProductDetails.IsUpdated = true; } } catch (Exception ex) { CommonFunctions.ShowErrorDialog("ProductMaster.UpdateProductInventoryDataFromInvoice()", ex); throw; } }
public void LoadProductInventoryFile(DataRow[] drProductInventory) { try { foreach (DataRow dr in drProductInventory) { StockProductDetails ObjStockProductDetails = GetStockProductDetails(dr["StockName"].ToString().Trim()); if (ObjStockProductDetails == null) { continue; } if (dr["Stock"] != DBNull.Value) { ObjStockProductDetails.Inventory = Double.Parse(dr["Stock"].ToString()); } if (dr["Units"] != DBNull.Value) { ObjStockProductDetails.Units = Double.Parse(dr["Units"].ToString()); } } } catch (Exception ex) { CommonFunctions.ShowErrorDialog("ProductMaster.LoadProductInventoryFile()", ex); throw; } }
public void ResetStockProducts(Boolean Flag = false) { try { for (int i = 0; i < ListStockProducts.Count; i++) { StockProductDetails ObjStockProductDetails = ListStockProducts[i]; ObjStockProductDetails.IsUpdated = Flag; ObjStockProductDetails.IsStockOverride = false; ObjStockProductDetails.NetQty = 0; ObjStockProductDetails.SaleQty = 0; ObjStockProductDetails.OrderQty = 0; ObjStockProductDetails.RecvdQty = 0; ObjStockProductDetails.Inventory = 0; ObjStockProductDetails.NetCost = 0; ObjStockProductDetails.TotalCost = 0; ObjStockProductDetails.TotalDiscount = 0; ObjStockProductDetails.TotalTax = 0; } } catch (Exception ex) { CommonFunctions.ShowErrorDialog("ProductMaster.ResetStockProducts()", ex); } }
public void ComputeStockNetData(String TransactionType) { try { for (int i = 0; i < ListStockProducts.Count; i++) { StockProductDetails ObjStockProductDetails = ListStockProducts[i]; if (ObjStockProductDetails.IsUpdated) { if (ObjStockProductDetails.IsStockOverride) { ObjStockProductDetails.NetQty = ObjStockProductDetails.RecvdQty; ObjStockProductDetails.RecvdQty -= ObjStockProductDetails.Inventory; } else { switch (TransactionType.Trim().ToUpper()) { case "SALE": ObjStockProductDetails.RecvdQty = -1 * ObjStockProductDetails.RecvdQty; break; case "PURCHASE": default: break; } ObjStockProductDetails.NetQty = ObjStockProductDetails.Inventory + ObjStockProductDetails.RecvdQty; } ObjStockProductDetails.NetCost = Math.Round(ObjStockProductDetails.TotalCost - ObjStockProductDetails.TotalDiscount + ObjStockProductDetails.TotalTax, 0); } } } catch (Exception ex) { CommonFunctions.ShowErrorDialog("ProductMaster.ComputeStockNetData()", ex); } }
public void AddProductToCache(ProductDetails ObjProductDetails) { try { ObjProductDetails.ItemName = ObjProductDetails.ItemName.Trim(); if (String.IsNullOrEmpty(ObjProductDetails.StockName)) { ObjProductDetails.StockName = ObjProductDetails.ItemName; } ObjProductDetails.StockName = ObjProductDetails.StockName.Trim(); StockProductDetails ObjStockProductDetails = new StockProductDetails(); ObjStockProductDetails.StockName = ObjProductDetails.StockName; AddStockProduct(ObjStockProductDetails); Int32 ProductIndex = ListProducts.BinarySearch(ObjProductDetails, ObjProductDetails); if (ProductIndex < 0) { ListProducts.Insert(~ProductIndex, ObjProductDetails); ObjProductDetails.FillMissingPricesForPriceGroups(ListPriceGroups); } } catch (Exception ex) { CommonFunctions.ShowErrorDialog("ProductMaster.AddProductToCache()", ex); } }
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; } }
public void UpdateProductInventoryDataFromPO(DataRow[] drProducts, Boolean IsStockOverride) { try { foreach (DataRow dr in drProducts) { StockProductDetails ObjStockProductDetails = GetStockProductDetails(dr["Item Name"].ToString().Trim()); if (ObjStockProductDetails.IsStockOverride && IsStockOverride == false) { continue; } else if (IsStockOverride) { ObjStockProductDetails.OrderQty = Double.Parse(dr["Order Quantity"].ToString().Trim()); if (dr["Received Quantity"] == DBNull.Value) { continue; } ObjStockProductDetails.RecvdQty = (Double.Parse(dr["Received Quantity"].ToString().Trim()) * ObjStockProductDetails.Units); if (dr["Total"] != DBNull.Value) { ObjStockProductDetails.TotalCost = Double.Parse(dr["Total"].ToString().Trim()); ObjStockProductDetails.TotalTax = (Double.Parse(dr["Total"].ToString().Trim()) * Double.Parse(CommonFunctions.ObjPurchaseOrderSettings.VATPercent) / 100); } } else { ObjStockProductDetails.OrderQty += Double.Parse(dr["Order Quantity"].ToString().Trim()); if (dr["Received Quantity"] == DBNull.Value) { continue; } ObjStockProductDetails.RecvdQty += (Double.Parse(dr["Received Quantity"].ToString().Trim()) * ObjStockProductDetails.Units); if (dr["Total"] != DBNull.Value) { ObjStockProductDetails.TotalCost += Double.Parse(dr["Total"].ToString().Trim()); ObjStockProductDetails.TotalTax += (Double.Parse(dr["Total"].ToString().Trim()) * Double.Parse(CommonFunctions.ObjPurchaseOrderSettings.VATPercent) / 100); } } ObjStockProductDetails.IsUpdated = true; ObjStockProductDetails.IsStockOverride |= IsStockOverride; } } catch (Exception ex) { CommonFunctions.ShowErrorDialog("ProductMaster.UpdateProductInventoryDataFromPO()", ex); throw; } }
void AddStockProduct(StockProductDetails ObjStockProductDetails) { try { //Add StockProduct to ListStockProducts Int32 ProductIndex = ListStockProducts.BinarySearch(ObjStockProductDetails, ObjStockProductDetails); if (ProductIndex < 0) { ObjStockProductDetails.ListProductIndexes = new List <Int32>(); ListStockProducts.Insert(~ProductIndex, ObjStockProductDetails); } } catch (Exception ex) { CommonFunctions.ShowErrorDialog("ProductMaster.AddStockProduct()", ex); } }
public StockProductDetails GetStockProductDetails(String StockName) { try { StockProductDetails ObjStockProduct = new StockProductDetails(); ObjStockProduct.StockName = StockName.Trim(); Int32 Index = ListStockProducts.BinarySearch(ObjStockProduct, ObjStockProduct); if (Index < 0) { return(null); } return(ListStockProducts[Index]); } catch (Exception ex) { CommonFunctions.ShowErrorDialog("ProductMaster.GetStockProductDetails()", ex); } return(null); }
public void UpdateStockProductIndexes() { try { for (int i = 0; i < ListProducts.Count; i++) { StockProductDetails ObjStockProductDetails = new StockProductDetails(); ObjStockProductDetails.StockName = ListProducts[i].StockName; Int32 StockProductIndex = ListStockProducts.BinarySearch(ObjStockProductDetails, ObjStockProductDetails); if (StockProductIndex >= 0) { ListStockProducts[StockProductIndex].ListProductIndexes.Add(i); ListProducts[i].StockProductIndex = StockProductIndex; } } DefaultPriceGroupIndex = ListPriceGroups.FindIndex(e => e.IsDefault); } catch (Exception ex) { CommonFunctions.ShowErrorDialog("ProductMaster.UpdateStockProductIndexes()", ex); } }
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; } }
public void LoadProductPastSalesFromStockHistoryFile(DataTable dtSalePurchaseHistory, DateTime AsOnDate, Int32 PeriodValue, TimePeriodUnits PeriodUnits) { try { #region Compute Date Range List <DateTime> ListPODates = dtSalePurchaseHistory.AsEnumerable().Select(s => s.Field <DateTime>("PO Date")).Distinct().ToList(); ListPODates.Sort(); DateTime FromDate = AsOnDate, ToDate = AsOnDate; switch (PeriodUnits) { case TimePeriodUnits.Days: Int32 ToDateIndex = -1; for (int i = ListPODates.Count - 1; i >= 0; i--) { if (ListPODates[i] <= AsOnDate) { ToDate = ListPODates[i]; ToDateIndex = i; break; } } if (ToDateIndex < 0) { return; } FromDate = ListPODates[((ToDateIndex - PeriodValue + 1) >= 0) ? (ToDateIndex - PeriodValue + 1) : 0]; break; case TimePeriodUnits.Weeks: ToDate = AsOnDate; FromDate = AsOnDate.AddDays(PeriodValue * -7); break; case TimePeriodUnits.Months: ToDate = AsOnDate; FromDate = AsOnDate.AddMonths(PeriodValue * -1); break; case TimePeriodUnits.Years: ToDate = AsOnDate; FromDate = AsOnDate.AddYears(PeriodValue * -1); break; case TimePeriodUnits.None: default: break; } #endregion DataRow[] drSalesPurchaseHistory = dtSalePurchaseHistory.Select("", "[Stock Name] asc"); StockProductDetails ObjStockProductDetails = null; String PreviousStockName = ""; for (int i = 0; i < drSalesPurchaseHistory.Length; i++) { DataRow dr = drSalesPurchaseHistory[i]; if (DateTime.Parse(dr["PO Date"].ToString()) < FromDate || DateTime.Parse(dr["PO Date"].ToString()) > ToDate) { continue; } if (!PreviousStockName.Trim().Equals(dr["Stock Name"].ToString().Trim(), StringComparison.InvariantCultureIgnoreCase)) { ObjStockProductDetails = GetStockProductDetails(dr["Stock Name"].ToString().Trim()); PreviousStockName = ObjStockProductDetails.StockName; if (ObjStockProductDetails == null) { continue; } } String TransactionType = dr["Type"].ToString().Trim().ToUpper(); if (TransactionType.Equals("SALE")) { ObjStockProductDetails.SaleQty += (-1 * Double.Parse(dr["Receive Qty"].ToString())); } else if (TransactionType.Equals("PURCHASE")) { ObjStockProductDetails.RecvdQty += Double.Parse(dr["Receive Qty"].ToString()); } } } catch (Exception ex) { CommonFunctions.ShowErrorDialog("ProductMaster.LoadProductPastSalesFromStockHistoryFile()", ex); throw; } }