public Int64 GetBBalance(int year, int storeId, int itemId, int month) { //CALENDAR: this.FlushData(); Int64 bb = 0; //int bYear = ((month > 10) ? year : year - 1); EthiopianDate.EthiopianDate ethioDate = new EthiopianDate.EthiopianDate(year, month, 30); int bYear = ethioDate.FiscalYear; string query = String.Format( "Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2} and (AutomaticallyEntered = 0 or AutomaticallyEntered is null) order by PhysicalInventory desc", storeId, itemId, bYear); this.LoadFromRawSql(query); if (this.DataTable.Rows.Count > 0 && this.DataTable.Rows[0]["PhysicalInventory"] != DBNull.Value) { bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]); } else { this.LoadFromRawSql( String.Format( "Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2} and (AutomaticallyEntered = 0 or AutomaticallyEntered is null)", storeId, itemId, year - 1)); if (this.DataTable.Rows.Count > 0) { bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]); } else if (((year + 8) > DateTime.Now.Year) || (month > 10 && (year + 8) == DateTime.Now.Year)) // to check if it is different year from current { Int64 cons = 0; IssueDoc iss = new IssueDoc(); ReceiveDoc rec = new ReceiveDoc(); Disposal dis = new Disposal(); if ((year + 8) > DateTime.Now.Year) // to check if it is hamle and Nehase { year = year - 1; } month = 10; //} long RecievedQuantity = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year); long AdjustedQuantity = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year); long IssuedQuantity = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year); long LossQuantity = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year); // long BalanceQuantity = yEnd.GetBBalance(year, storeId, itemId, month); cons = (RecievedQuantity + AdjustedQuantity - IssuedQuantity - LossQuantity); //cons = (rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year) + dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year) - iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year) - dis.GetLossesQuantityTillMonth(itemId, storeId, month, year)); bb = cons; } } return(bb); }
public DataTable BalanceAllItems(int storeId, int year, int month, string selectedType) { IssueDoc iss = new IssueDoc(); ReceiveDoc rec = new ReceiveDoc(); Disposal dis = new Disposal(); YearEnd yEnd = new YearEnd(); Items itm = new Items(); DataTable dtResult = new DataTable(); string[] col = { "FullItemName", "BBalance", "BBAmount", "ReceivedQty", "ReceivedAmount", "IssuedQty", "IssuedAmount", "LossesQty", "LossesAmount", "AdjustedQty", "AdjustedAmount", "SOH", "SOHAmount", "Received", "ID", "CategoryId", "SubCategoryID" }; int i = 0; foreach (string s in col) { if (i > 0) { dtResult.Columns.Add(s, typeof(double)); } else { dtResult.Columns.Add(s); } i++; } DataTable dtItem = ((selectedType == "Drug") ? itm.GetAllItems(1) : itm.GetAllSupply()); foreach (DataRow dr in dtItem.Rows) { string itemName = dr["FullItemName"].ToString();// +" - " + dr["DosageForm"].ToString() + " - " + dr["Strength"].ToString(); int itemId = Convert.ToInt32(dr["ID"]); Int64 bb = yEnd.GetBBalance(year, storeId, itemId, month); double bbAmount = yEnd.GetBBalanceAmount(year, storeId, itemId, month); Int64 recQuant = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year); double recPrice = rec.GetReceivedAmountTillMonth(itemId, storeId, month, year); Int64 issuedQuant = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year); double issPrice = iss.GetIssuedAmountTillMonth(itemId, storeId, month, year); Int64 lossQuant = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year); double lossAmount = dis.GetLossesAmountTillMonth(itemId, storeId, month, year); Int64 adjQuant = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year); double adjAmount = dis.GetAdjustedAmountTillMonth(itemId, storeId, month, year); Int64 SOH = bb + recQuant + adjQuant - issuedQuant - lossQuant; double SOHAmount = (bbAmount + recPrice + adjAmount - issPrice) - lossAmount; if (SOHAmount < 0) { ; } int Isrec = ((bb == 0 && recQuant == 0) ? 0 : 1); object[] obj = { itemName, bb, bbAmount, recQuant, recPrice, issuedQuant, issPrice, lossQuant, lossAmount, adjQuant, adjAmount, SOH, SOHAmount, Isrec, itemId, Convert.ToInt32(dr["CategoryId"]), Convert.ToInt32(dr["SubCategoryID"]) }; dtResult.Rows.Add(obj); } return dtResult; }
/// <summary> /// Gets SOH of a specific item for the end of the specified Ethipian Fiscal Month Item /// </summary> /// <param name="itemId"></param> /// <param name="storeId"></param> /// <param name="month"></param> /// <param name="year"></param> /// <returns></returns> public Int64 GetSOHOld(int itemId, int storeId, int month, int year) { //ask by normal u get soh on normal month IssueDoc iss = new IssueDoc(); ReceiveDoc rec = new ReceiveDoc(); Disposal dis = new Disposal(); YearEnd yEnd = new YearEnd(); //int month = (mon > 2) ? mon - 2 : ((mon == 1) ? 11 : 12); //int year = (mon > 2) ? yr : yr - 1; Int64 cons = 0; long recievedQuantity = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year); long adjustedQuantity = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year); long issuedQuantity = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year); long lossQuantity = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year); long balanceQuantity = yEnd.GetBBalance(year, storeId, itemId, month); cons = (balanceQuantity + recievedQuantity + adjustedQuantity - issuedQuantity - lossQuantity); this.FlushData(); // this.LoadFromRawSql(String.Format("SELECT SUM(QuantityLeft) AS Quantity FROM ReceiveDoc WHERE (ItemID = {0}) AND (StoreID = {1} AND ((Month(Date) <= {2} AND Year(Date) = {3}) OR (Month(Date) > 10 AND Year(Date) = {4})))", itemId, storeId, month, year, year - 1)); // cons = (this.DataTable.Rows[0]["Quantity"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["Quantity"]) : 0; return cons; }
/// <summary> /// /// </summary> /// <param name="itemId"></param> /// <param name="storeId"></param> /// <param name="mon"></param> /// <param name="yr"></param> /// <returns></returns> public Int64 CalculateSOH(int itemId, int storeId, int mon, int yr) { //ask by normal u get soh on fiscal month IssueDoc iss = new IssueDoc(); ReceiveDoc rec = new ReceiveDoc(); Disposal dis = new Disposal(); YearEnd yEnd = new YearEnd(); int month = (mon > 2) ? mon - 2 : ((mon == 1) ? 11 : 12); int year = (mon > 2) ? yr : yr - 1; Int64 cons = 0; cons = (rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year) + dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year) - iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year) - dis.GetLossesQuantityTillMonth(itemId, storeId, month, year)); return cons; }
public Int64 GetBBalance(int year, int storeId, int itemId, int month) { //CALENDAR: this.FlushData(); Int64 bb = 0; //int bYear = ((month > 10) ? year : year - 1); EthiopianDate.EthiopianDate ethioDate = new EthiopianDate.EthiopianDate(year, month, 30); int bYear = ethioDate.FiscalYear; string query = String.Format( "Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2} and (AutomaticallyEntered = 0 or AutomaticallyEntered is null) order by PhysicalInventory desc", storeId, itemId, bYear); this.LoadFromRawSql(query); if (this.DataTable.Rows.Count > 0 && this.DataTable.Rows[0]["PhysicalInventory"] != DBNull.Value) { bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]); } else { this.LoadFromRawSql( String.Format( "Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2} and (AutomaticallyEntered = 0 or AutomaticallyEntered is null)", storeId, itemId, year - 1)); if (this.DataTable.Rows.Count > 0) { bb = Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventory"]); } else if (((year + 8) > DateTime.Now.Year) || (month > 10 && (year + 8) == DateTime.Now.Year)) // to check if it is different year from current { Int64 cons = 0; IssueDoc iss = new IssueDoc(); ReceiveDoc rec = new ReceiveDoc(); Disposal dis = new Disposal(); if ((year + 8) > DateTime.Now.Year) // to check if it is hamle and Nehase year = year - 1; month = 10; //} long RecievedQuantity = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year); long AdjustedQuantity = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year); long IssuedQuantity = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year); long LossQuantity = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year); // long BalanceQuantity = yEnd.GetBBalance(year, storeId, itemId, month); cons = (RecievedQuantity + AdjustedQuantity - IssuedQuantity - LossQuantity); //cons = (rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year) + dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year) - iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year) - dis.GetLossesQuantityTillMonth(itemId, storeId, month, year)); bb = cons; } } return bb; }