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