/// <summary> /// /// </summary> /// <param name="storeId"></param> /// <param name="fromYear">In Ethiopian Calendar</param> /// <param name="fromMonth">In Ethiopian Calendar</param> /// <param name="toYear">In Ethiopian Calendar</param> /// <param name="toMonth">In Ethiopian Calendar</param> /// <returns></returns> public DataTable GetRRFReportByUnit(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.GetSOHByUnitForRRF(storeId,fromMonth, fromYear); var dtbl2 = balance.GetSOHByUnitForRRF(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.ID, isnull(Quantity,0) as Quantity ,IsNull(A.UnitID ,0) from" + // " Items left join (select ItemID,UnitID, sum(Quantity) as Quantity from ReceiveDoc rd " + // "where [Date] between '{0}' and '{1}' and" + " StoreID = {2} group by ItemID,UnitID,SubProgramID) as" + // " A on Items.ID = A.ItemID left join ItemUnit as iu on A.UnitID =iu.ID AND A.ItemID = iu.ItemID LEFT JOIN dbo.ProgramProduct AS pp ON pp.ItemID =A.ItemID", dt1, dt2, storeId); var query = string.Format(@"SELECT rd.ItemID ID, rd.UnitID ,SUM(Quantity) AS Quantity FROM ReceiveDoc rd JOIN ItemUnit AS iu ON rd.UnitID = iu.ID AND rd.ItemID = iu.ItemID WHERE [Date] BETWEEN '{0}'AND '{1}' AND StoreID = {2} GROUP BY rd.ItemID , rd.UnitID ,rd.SubProgramID", dt1, dt2, storeId); this.LoadFromRawSql(query); var received = this.DataTable; //query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity ,IsNull(A.UnitID ,0) " + // "from Items left join (select ItemID, UnitID,sum(Quantity) Quantity " + // "from IssueDoc rd where [Date] between '{0}' and '{1}' and " + // "StoreID = {2} group by ItemID,UnitID) as A on Items.ID = A.ItemID " + // "left join ItemUnit as iu on A.UnitID =iu.ID AND A.ItemID = iu.ItemID LEFT JOIN dbo.ProgramProduct AS pp ON pp.ItemID = A.ItemID", dt1, dt2, storeId); query = string.Format(@"SELECT id.ItemID ID,id.UnitID,SUM(Quantity) AS Quantity FROM dbo.IssueDoc id JOIN ItemUnit AS iu ON id.UnitID = iu.ID AND id.ItemID = iu.ItemID WHERE [Date] BETWEEN '{0}' AND '{1}' AND StoreID = {2} GROUP BY id.ItemID , id.UnitID ", dt1, dt2, storeId); this.LoadFromRawSql(query); var issued = this.DataTable; //query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity,IsNull(A.UnitID ,0) from " + // "Items left join (select ItemID, UnitID,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,UnitID) as A on Items.ID = A.ItemID " + // "left join ItemUnit as iu on A.UnitID =iu.ID AND A.ItemID = iu.ItemID LEFT JOIN dbo.ProgramProduct AS pp ON pp.ItemID = A.ItemID", dt1, dt2, storeId); query = string.Format(@"SELECT d.ItemID ID ,d.UnitID ,SUM(CASE WHEN Losses = 1 THEN -Quantity ELSE Quantity END) Quantity FROM Disposal d JOIN ItemUnit AS iu ON d.UnitID = iu.ID WHERE [Date] BETWEEN '{0}' AND '{1}'AND StoreID = {2} GROUP BY d.ItemID ,d.UnitID", dt1, dt2, storeId); this.LoadFromRawSql(query); var lost = this.DataTable; query = string.Format(@"SELECT DISTINCT Items.ID , Items.StockCodeDACA , Items.Cost , CASE Items.Cost WHEN 0 THEN 1 ELSE ISNULL(Items.Cost, 1) END AS QtyPerPack FROM Items"); // JOIN dbo.ItemUnit iu ON dbo.Items.ID = iu.ItemID this.LoadFromRawSql(query); var preferredPackSizetbl = DataTable; var itm = new Items(); var daysOutOfStock = this.GetItemsWithLastIssuedOrDisposedDateForUnitBased(); //query=string.Format("select ") var x = (from y in dtbl.AsEnumerable() join z in dtbl2.AsEnumerable() on y["ID"] equals z["ID"] join p in preferredPackSizetbl.AsEnumerable() on y["ID"] equals p["ID"] where Convert.ToInt32(y["EverReceived"]) == 1 && Convert.ToInt32(y["UnitID"]) == Convert.ToInt32(z["UnitID"]) && Convert.ToInt32(y["ProgramID"]) == Convert.ToInt32(z["ProgramID"]) select new { ID = y["ID"], FullItemName = y["FullItemName"], Unit = y["Unit"], StockCode = y["StockCode"], BeginingBalance = Convert.ToDouble(y["SOH"]), ProgramID=y["ProgramID"], UnitID =y["UnitID"], SOH = Convert.ToDouble(z["SOH"]), Max = Convert.ToInt32(z["Max"]), QtyPerPack = Convert.ToDouble(p["QtyPerPack"]), StockCodeDACA = p["StockCodeDACA"], TypeID=y["TypeID"] }).ToArray(); var m = (from n in x join z in received.AsEnumerable() on n.ID equals z["ID"] where Convert.ToInt32(n.UnitID) == Convert.ToInt32(z["UnitID"]) select new { ID = n.ID, 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"] , UnitID =n.UnitID, TypeID=n.TypeID }).ToArray(); var l = (from n in m join z in issued.AsEnumerable() on n.ID equals z["ID"] where Convert.ToInt32(n.UnitID) == Convert.ToInt32(z["UnitID"]) select new { ID = n.ID, 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, UnitID =n.UnitID, Issued = Convert.ToInt32(z["Quantity"]), TypeID=n.TypeID }).ToArray(); var t = (from n in l join z in lost.AsEnumerable() on n.ID equals z["ID"] where Convert.ToInt32(n.UnitID) == Convert.ToInt32(z["UnitID"]) select new { ID = n.ID, 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, UnitID = n.UnitID, TypeID=n.TypeID }).ToArray(); var t1 = (from n in t join z in daysOutOfStock.AsEnumerable() on n.ID equals z["ID"] select new { ID = n.ID, 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, UnitID = n.UnitID, ProgramID = n.ProgramID, Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH, DaysOutOfStock = Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate), TypeID = n.TypeID }).ToArray(); if (t.Length == 0) { var t2 = (from n in l select new { ID = n.ID, 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 = 0, ProgramID = n.ProgramID, UnitID = n.UnitID, Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH, DaysOutOfStock = Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), 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(Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate)))), TypeID = n.TypeID }).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("ID", 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("TypeID", typeof(int)); foreach (var v in t2) { DataRowView drv = value.DefaultView.AddNew(); drv["ID"] = v.ID; 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"] = v.DaysOutOfStock; drv["MaxStockQty"] = v.MaxStockQty; drv["UnitID"] = v.UnitID; drv["TypeID"] = v.TypeID; } return value; } else { var t2 = (from n in t1 select new { ID = n.ID, 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, UnitID = n.UnitID, Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH, DaysOutOfStock = Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), 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(Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), storeId, startDate, endDate)))), TypeID = n.TypeID }).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("ID", 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("TypeID", typeof (int)); foreach (var v in t2) { DataRowView drv = value.DefaultView.AddNew(); drv["ID"] = v.ID; 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"] = v.DaysOutOfStock; drv["MaxStockQty"] = v.MaxStockQty; drv["UnitID"] = v.UnitID; drv["TypeID"] = v.TypeID; } return value; } }