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; }
/* public Int64 GetBBalance(int year, int storeId, int itemId) * { * this.FlushData(); * this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}",storeId,itemId,year)); * * Int64 bb = ( this.DataTable.Rows.Count > 0)? Convert.ToInt64(this.DataTable.Rows[0]["BBalance"]):0; * * return bb; * }*/ public double GetBBalanceAmount(int year, int storeId, int itemId, int month) { //CALENDAR: this.FlushData(); double bb = 0; EthiopianDate.EthiopianDate ethioDate = new EthiopianDate.EthiopianDate(year, month, 30); //int bYear = ((month > 10) ? year : year - 1); int bYear = ethioDate.StartOfFiscalYear.Year; string query = String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId, itemId, bYear); this.LoadFromRawSql(query); if (this.DataTable.Rows.Count > 0) { bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"]) : 0); } else { string queryString = String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId, itemId, year - 1); this.LoadFromRawSql(queryString); if (this.DataTable.Rows.Count > 0) { bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"]) : 0); } else if (((year + 8) > DateTime.Now.Year) || (month > 10 && (year + 8) == DateTime.Now.Year)) // to check if it is different year from current { double 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; //} double ReceivedAmount = rec.GetReceivedAmountTillMonth(itemId, storeId, month, year); double AdjustedAmount = dis.GetAdjustedAmountTillMonth(itemId, storeId, month, year); double IssuedAmount = iss.GetIssuedAmountTillMonth(itemId, storeId, month, year); double LostAmount = dis.GetLossesAmountTillMonth(itemId, storeId, month, year); cons = ((ReceivedAmount + AdjustedAmount - IssuedAmount) - LostAmount); bb = cons; } } if (bb < 0) { } return(bb); //this.FlushData(); //this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId, itemId, year-1)); //double bb = (this.DataTable.Rows.Count > 0) ? ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"]) : 0) : 0; //return bb; }
/* public Int64 GetBBalance(int year, int storeId, int itemId) { this.FlushData(); this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}",storeId,itemId,year)); Int64 bb = ( this.DataTable.Rows.Count > 0)? Convert.ToInt64(this.DataTable.Rows[0]["BBalance"]):0; return bb; }*/ public double GetBBalanceAmount(int year, int storeId, int itemId, int month) { //CALENDAR: this.FlushData(); double bb = 0; EthiopianDate.EthiopianDate ethioDate = new EthiopianDate.EthiopianDate(year, month, 30); //int bYear = ((month > 10) ? year : year - 1); int bYear = ethioDate.StartOfFiscalYear.Year; string query = String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId, itemId, bYear); this.LoadFromRawSql(query); if (this.DataTable.Rows.Count > 0) { bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"]) : 0); } else { string queryString = String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId, itemId, year - 1); this.LoadFromRawSql(queryString); if (this.DataTable.Rows.Count > 0) { bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"]) : 0); } else if (((year + 8) > DateTime.Now.Year) || (month > 10 && (year + 8) == DateTime.Now.Year)) // to check if it is different year from current { double 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; //} double ReceivedAmount = rec.GetReceivedAmountTillMonth(itemId, storeId, month, year); double AdjustedAmount = dis.GetAdjustedAmountTillMonth(itemId, storeId, month, year); double IssuedAmount = iss.GetIssuedAmountTillMonth(itemId, storeId, month, year); double LostAmount = dis.GetLossesAmountTillMonth(itemId, storeId, month, year); cons = ((ReceivedAmount + AdjustedAmount - IssuedAmount) - LostAmount); bb = cons; } } if (bb < 0) { } return bb; //this.FlushData(); //this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId, itemId, year-1)); //double bb = (this.DataTable.Rows.Count > 0) ? ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"]) : 0) : 0; //return bb; }