public DataTable GetRRFReportForOldSite(int storeId, int fromYear, int fromMonth, int toYear, int toMonth) { var balance = new Balance(); var startDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 1, fromMonth, fromYear)); var endDate = EthiopianDate.EthiopianDate.EthiopianToGregorian(String.Format("{0}/{1}/{2}", 30, toMonth, toYear)); if (fromMonth != 1) fromMonth--; else { fromYear--; fromMonth = 12; //Because SOH returns stock until the end of the month } var dtbl = balance.GetSOHForRRF(storeId, fromMonth, fromYear); var dtbl2 = balance.GetSOHForRRF(storeId, toMonth, toYear); var dt1 = new DateTime(fromYear, fromMonth, DateTime.DaysInMonth(fromYear, fromMonth)); var dt2 = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth)); var query = string.Format("select distinct Items.DSItemID, isnull(Quantity,0) as Quantity from Items left join " + "(select ItemID, sum(Quantity) as Quantity from ReceiveDoc rd where [Date] between '" + "{0}'and '{1}' and StoreID = '{2}' group by ItemID) as A on Items.DSItemID = A.ItemID", dt1, dt2, storeId); this.LoadFromRawSql(query); var received = this.DataTable; query = string.Format("select distinct Items.DSItemID, isnull(Quantity,0) as Quantity " + "from Items left join (select ItemID,sum(Quantity) Quantity " + "from IssueDoc rd where [Date] between '{0}' and '{1}' and " + "StoreID = {2} group by ItemID) as A on Items.DSItemID = A.ItemID ", dt1, dt2, storeId); this.LoadFromRawSql(query); var issued = this.DataTable; query = string.Format("select distinct Items.DSItemID, isnull(Quantity,0) as Quantity from " + "Items left join (select ItemID,sum(case when Losses = 1 then - Quantity else " + "Quantity end) Quantity from Disposal where [Date] between '{0}' and '{1}' " + "and StoreID = {2} group by ItemID) as A on Items.DSItemID = A.ItemID ", dt1, dt2, storeId); this.LoadFromRawSql(query); var lost = this.DataTable; query = string.Format("select distinct Items.DSItemID,Items.StockCodeDACA,Items.Cost, case Items.Cost " + "when 0 then 1 else isnull(Items.Cost,1) end as QtyPerPack from Items"); this.LoadFromRawSql(query); var preferredPackSizetbl = DataTable; var itm = new Items(); var daysOutOfStock = this.GetItemsWithLastIssuedOrDisposedDate(); var x = (from y in dtbl.AsEnumerable() join z in dtbl2.AsEnumerable() on y["DSItemID"] equals z["DSItemID"] join p in preferredPackSizetbl.AsEnumerable() on y["DSItemID"] equals p["DSItemID"] where Convert.ToInt32(y["EverReceived"]) == 1 select new { DSItemID = y["DSItemID"], FullItemName = y["FullItemName"], Unit = y["Unit"], StockCode = y["StockCode"], BeginingBalance = Convert.ToDouble(y["SOH"]), ProgramID = y["ProgramID"], SOH = Convert.ToDouble(z["SOH"]), Max = Convert.ToInt32(z["Max"]), QtyPerPack = Convert.ToDouble(p["QtyPerPack"]), StockCodeDACA = p["StockCodeDACA"], Status = y["Status"], TypeID = y["TypeID"], EverReceived =y["EverReceived"] }).Distinct().ToArray(); var m = (from n in x join z in received.AsEnumerable() on n.DSItemID equals z["DSItemID"] select new { DSItemID = n.DSItemID, FullItemName = n.FullItemName, Unit = n.Unit, StockCode = n.StockCode, BeginingBalance = n.BeginingBalance, SOH = n.SOH, Max = n.Max, QtyPerPack = n.QtyPerPack, StockCodeDACA = n.StockCodeDACA, ProgramID = n.ProgramID, Received = z["Quantity"], Status = n.Status, TypeID=n.TypeID, EverReceived =n.EverReceived }).ToArray(); var l = (from n in m join z in issued.AsEnumerable() on n.DSItemID equals z["DSItemID"] select new { DSItemID = n.DSItemID, FullItemName = n.FullItemName, Unit = n.Unit, StockCode = n.StockCode, BeginingBalance = n.BeginingBalance, SOH = n.SOH, Max = Convert.ToInt32(z["Quantity"]) * 2, StockCodeDACA = n.StockCodeDACA, QtyPerPack = n.QtyPerPack, Received = n.Received, ProgramID = n.ProgramID, Issued = Convert.ToInt32(z["Quantity"]), Status = n.Status, TypeID = n.TypeID, EverReceived =n.EverReceived }).ToArray(); var t = (from n in l join z in lost.AsEnumerable() on n.DSItemID equals z["DSItemID"] select new { DSItemID = n.DSItemID, FullItemName = n.FullItemName, Unit = n.Unit, StockCode = n.StockCode, BeginingBalance = n.BeginingBalance, SOH = n.SOH, Max = n.Max, StockCodeDACA = n.StockCodeDACA, QtyPerPack = n.QtyPerPack, Received = n.Received, ProgramID = n.ProgramID, Issued = n.Issued, LossAdj = z["Quantity"], Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH, Status=n.Status, TypeID = n.TypeID, EverReceived =n.EverReceived }).ToArray(); var t1 = (from n in t join z in daysOutOfStock.AsEnumerable() on n.DSItemID equals z["DSItemID"] select new { DSItemID = n.DSItemID, FullItemName = n.FullItemName, Unit = n.Unit, StockCode = n.StockCode, BeginingBalance = n.BeginingBalance, SOH = n.SOH, Max = n.Max, StockCodeDACA = n.StockCodeDACA, QtyPerPack = n.QtyPerPack, Received = n.Received, Issued = n.Issued, LossAdj = n.LossAdj, ProgramID = n.ProgramID, Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH, Status=n.Status, TypeID=n.TypeID, EverReceived =n.EverReceived, DaysOutOfStock = Builder.CalculateStockoutDays(Convert.ToInt32(n.DSItemID), storeId, startDate, endDate)//Builder.CalculateStockoutDays(Convert.ToInt32(ID), storeId, startDate,endDate) DBNull.Value ? 0 : (Convert.ToInt32(z["DaysOutOfStock"]) < 60 ? z["DaysOutOfStock"] : 0) }).ToArray(); var t2 = (from n in t1 select new { DSItemID = n.DSItemID, FullItemName = n.FullItemName, Unit = n.Unit, StockCode = n.StockCode, BeginingBalance = n.BeginingBalance, SOH = n.SOH, Max = n.Max, StockCodeDACA = n.StockCodeDACA, QtyPerPack = n.QtyPerPack, Received = n.Received, Issued = n.Issued, LossAdj = n.LossAdj, ProgramID = n.ProgramID, Status =n.Status, TypeID=n.TypeID, EverReceived =n.EverReceived, Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH, DaysOutOfStock = Builder.CalculateStockoutDays(Convert.ToInt32(n.DSItemID), storeId, startDate, endDate),//TODO: This is a quick fix. We need to take stock status from the last three months. //TODO: This is a quick fix. We need to take stock status from the last three months. MaxStockQty = ((120 * n.Issued) / (60 - Convert.ToInt32(n.DaysOutOfStock))), }).ToArray(); //return t; // Converting shit into antoher shit. // Just because i was not able to read the elemntes of the anonymus type in another method var value = new DataTable(); value.Columns.Add("DSItemID", typeof(int)); value.Columns.Add("FullItemName"); value.Columns.Add("Unit"); value.Columns.Add("StockCode"); value.Columns.Add("BeginingBalance", typeof(double)); value.Columns.Add("SOH", typeof(double)); value.Columns.Add("Max", typeof(double)); value.Columns.Add("StockCodeDACA", typeof(string)); value.Columns.Add("QtyPerPack", typeof(double)); value.Columns.Add("Issued", typeof(double)); value.Columns.Add("Received", typeof(double)); value.Columns.Add("LossAdj", typeof(double)); value.Columns.Add("Quantity", typeof(double)); value.Columns.Add("DaysOutOfStock", typeof(int)); value.Columns.Add("MaxStockQty", typeof(double)); value.Columns.Add("ProgramID", typeof(int)); value.Columns.Add("UnitID", typeof(int)); value.Columns.Add("Status", typeof(string)); value.Columns.Add("TypeID", typeof(int)); value.Columns.Add("EverReceived", typeof(bool)); foreach (var v in t2) { DataRowView drv = value.DefaultView.AddNew(); drv["DSItemID"] = v.DSItemID; drv["FullItemName"] = v.FullItemName; drv["Unit"] = v.Unit; drv["StockCode"] = v.StockCode; drv["BeginingBalance"] = v.BeginingBalance; drv["SOH"] = v.SOH; drv["Max"] = v.Max; drv["StockCodeDACA"] = v.StockCodeDACA; drv["QtyPerPack"] = v.QtyPerPack; drv["Issued"] = v.Issued; drv["Received"] = v.Received; drv["LossAdj"] = v.LossAdj; drv["Quantity"] = v.Quantity; drv["ProgramID"] = v.ProgramID; drv["DaysOutOfStock"] = Builder.CalculateStockoutDays(Convert.ToInt32(drv["DSItemID"]), storeId, startDate, endDate); drv["MaxStockQty"] = v.MaxStockQty; drv["Status"] = v.Status; drv["TypeID"] = v.TypeID; drv["EverReceived"] = v.EverReceived; } return value; }