public DataTable GetEmergencyRRFReport(int storeId, int fromYear, int fromMonth, int toYear, int toMonth)
        {
            Balance 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
            }
            DataTable dtbl = balance.GetSOHForProgramRRF(storeId, fromMonth, fromYear);
            DataTable dtbl2 = balance.GetSOHForProgramRRF(storeId, toMonth, toYear);

            DateTime dt1 = new DateTime(fromYear, fromMonth, DateTime.DaysInMonth(fromYear, fromMonth));
            DateTime dt2 = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));

            string query = string.Format("select distinct Items.ID, 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.ID = A.ItemID", dt1, dt2, storeId);
            this.LoadFromRawSql(query);
            DataTable received = this.DataTable;

            query = string.Format("select distinct Items.ID, 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.ID = A.ItemID", dt1, dt2, storeId);
            this.LoadFromRawSql(query);
            DataTable issued = this.DataTable;

            query = string.Format("select distinct Items.ID, 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.ID = A.ItemID", dt1, dt2, storeId);
            this.LoadFromRawSql(query);
            DataTable 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");
            this.LoadFromRawSql(query);
            DataTable preferredPackSizetbl = DataTable;

            BLL.Items itm = new Items();
            System.Data.DataTable daysOutOfStock = this.GetItemsWithLastIssuedOrDisposedDate();

            //query=string.Format("select ")
            var theFilter = "Below EOP";

            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
                     select new
                     {
                         ID = y["ID"],
                         FullItemName = y["FullItemName"],
                         Unit = y["Unit"],
                         StockCode = y["StockCode"],
                         BeginingBalance = Convert.ToInt32(y["SOH"]),
                         SOH = Convert.ToInt32(z["SOH"]),
                         Max = Convert.ToInt32(z["Max"]),
                         QtyPerPack = Convert.ToInt32(p["QtyPerPack"]),
                         StockCodeDACA = p["StockCodeDACA"],
                         Status = z["Status"].ToString()
                     }).Distinct().ToArray();

            var u = (from z in x where theFilter.Contains(z.Status) select z).Distinct().ToArray();

            var m = (from n in u
                     join z in received.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,
                         QtyPerPack = n.QtyPerPack,
                         StockCodeDACA = n.StockCodeDACA,
                         Received = z["Quantity"]
                     }).ToArray();

            var l = (from n in m
                     join z in issued.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 = Convert.ToInt32(z["Quantity"]) * 2,
                             StockCodeDACA = n.StockCodeDACA,
                             QtyPerPack = n.QtyPerPack,
                             Received = n.Received,
                             Issued = Convert.ToInt32(z["Quantity"]),

                         }).ToArray();

            var t = (from n in l
                     join z in lost.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 = z["Quantity"],
                         Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH
                     }).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,

                              Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
                              DaysOutOfStock = Builder.CalculateStockoutDays(Convert.ToInt32(n.ID), 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
                          {
                              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,

                              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(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
            DataTable 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(int));
            value.Columns.Add("SOH", typeof(int));
            value.Columns.Add("Max", typeof(int));
            value.Columns.Add("StockCodeDACA", typeof(string));
            value.Columns.Add("QtyPerPack", typeof(int));
            value.Columns.Add("Issued", typeof(int));
            value.Columns.Add("Received", typeof(int));
            value.Columns.Add("LossAdj", typeof(int));
            value.Columns.Add("Quantity", typeof(int));
            value.Columns.Add("DaysOutOfStock", typeof(int));
            value.Columns.Add("MaxStockQty", typeof(int));
            value.Columns.Add("ProgramID", 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["DaysOutOfStock"] = Builder.CalculateStockoutDays(Convert.ToInt32(drv["ID"]), storeId, startDate, endDate);
                drv["MaxStockQty"] = v.MaxStockQty;

            }

            return value;
        }
        public DataTable GetRRFReportWithOutUnit(int storeId, int fromYear, int fromMonth, int toYear, int toMonth)
        {
            var balance = new Balance();
            var issue = new IssueDoc();
            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;
            }
            var dtbl = balance.GetSOHForProgramRRF(storeId, fromMonth, fromYear);
            var dtbl2 = balance.GetSOHForProgramRRF(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 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.ID = A.ItemID", dt1, dt2, storeId);
            //var query = string.Format("SELECT  ItemID ID,SUM(Quantity) AS Quantity FROM ReceiveDoc rd WHERE   [Date] BETWEEN '{0}' AND '{1}'AND StoreID = {2} GROUP BY ItemID ", dt1, dt2, storeId);
            this.LoadFromRawSql(query);
            var received = this.DataTable;

            query = string.Format("select distinct Items.ID, isnull(Quantity,0) as Quantity " +
                                  "from Items left join (select ItemID,sum(Quantity) Quantity " +
                                  "from IssueDoc " +
                                  "where [Date] between '{0}' and '{1}' and StoreID = {2} and IsTransfer = 0 " +
                                  "group by ItemID ) as A on Items.ID = A.ItemID "
                                  , dt1, dt2, storeId);

            //query = string.Format(" SELECT ItemID ID ,SUM(Quantity) Quantity FROM IssueDoc WHERE   [Date] BETWEEN '{0}' AND '{1}' AND StoreID = {2} AND IsTransfer = 0 GROUP BY ItemID", dt1, dt2, storeId);
            this.LoadFromRawSql(query);
            var issued = this.DataTable;

            query = string.Format("select distinct Items.ID, 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.ID = A.ItemID "
                                 , dt1, dt2, storeId);

            //query = string.Format("select ItemID ID ,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", 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");
            this.LoadFromRawSql(query);
            var preferredPackSizetbl = DataTable;
            var daysOutOfStock = this.GetItemsWithLastIssuedOrDisposedDate();

            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
                     select new
                     {
                         ID = y["ID"],
                         FullItemName = y["FullItemName"],
                         Unit = y["Unit"],
                         StockCode = y["StockCode"],
                         BeginingBalance = Convert.ToDouble(y["SOH"]),
                         ProgramID = y["ProgramID"],
                         SOH = Convert.ToDouble(z["SOH"]),
                         USOH = Convert.ToDouble(z["Dispatchable"]),
                         Max = Convert.ToInt32(z["Max"]),
                         QtyPerPack = Convert.ToDouble(p["QtyPerPack"]),
                         StockCodeDACA = p["StockCodeDACA"],
                         Status=y["Status"],
                         TypeID=y["TypeID"]
                     }).Distinct().ToArray();

            var m = (from n in x
                     join z in received.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,
                         USOH = n.USOH,
                         Max = n.Max,
                         QtyPerPack = n.QtyPerPack,
                         StockCodeDACA = n.StockCodeDACA,
                         ProgramID = n.ProgramID,
                         Received = z["Quantity"],
                         Status = n.Status,
                         TypeID = n.TypeID
                        }).ToArray();

            var l = (from n in m
                     join z in issued.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,
                             USOH = n.USOH ,
                             Max = Convert.ToInt32(z["Quantity"]) * 2,
                             StockCodeDACA = n.StockCodeDACA,
                             QtyPerPack = n.QtyPerPack,
                             Received = n.Received,
                             ProgramID = n.ProgramID,
                             Status=n.Status,
                             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"]
                     select new
                     {
                         ID = n.ID,
                         FullItemName = n.FullItemName,
                         Unit = n.Unit,
                         StockCode = n.StockCode,
                         BeginingBalance = n.BeginingBalance,
                         SOH = n.SOH,
                         USOH = n.USOH ,
                         Max = n.Max,
                         StockCodeDACA = n.StockCodeDACA,
                         QtyPerPack = n.QtyPerPack,
                         Received = n.Received,
                         ProgramID = n.ProgramID,
                         Issued = n.Issued,
                         Status =n.Status,
                         LossAdj = z["Quantity"],
                         Quantity = (n.Max - n.SOH < 0) ? 0 : n.Max - n.SOH,
                         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,
                                      USOH = n.USOH,
                                      Max = n.Max,
                                      StockCodeDACA = n.StockCodeDACA,
                                      QtyPerPack = n.QtyPerPack,
                                      Received = n.Received,
                                      Issued = n.Issued,
                                      LossAdj = 0,
                                      ProgramID = n.ProgramID,
                                      Status = n.Status,
                                      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();

                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("Status", typeof (string));
                value.Columns.Add("TypeID", typeof (int));
                value.Columns.Add("LastDUSoh", typeof (decimal));
                value.Columns.Add("USOH", typeof (decimal));
                value.Columns.Add("TotalSOH", typeof (decimal));

                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["USOH"] = v.USOH;
                    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["Status"] = v.Status;
                    drv["TypeID"] = v.TypeID;
                    drv["LastDUSoh"] = issue.GetDULastSOH1(Convert.ToInt32(v.ID), dt1, dt2);
                    drv["TotalSOH"] = v.USOH + issue.GetDULastSOH1(Convert.ToInt32(v.ID), dt1, dt2);
                }

                return value;
            }
            else
            {
                var t2 = (from n in t
                          select
                              new
                              {
                                  ID = n.ID,
                                  FullItemName = n.FullItemName,
                                  Unit = n.Unit,
                                  StockCode = n.StockCode,
                                  BeginingBalance = n.BeginingBalance,
                                  SOH = n.SOH,
                                  USOH = n.USOH,
                                  Max = n.Max,
                                  StockCodeDACA = n.StockCodeDACA,
                                  QtyPerPack = n.QtyPerPack,
                                  Received = n.Received,
                                  Issued = n.Issued,
                                  LossAdj = n.LossAdj,
                                  ProgramID = n.ProgramID,
                                  Status = n.Status,
                                  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();

                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("Status", typeof(string));
                value.Columns.Add("TypeID", typeof(int));
                value.Columns.Add("LastDUSoh", typeof(decimal));
                value.Columns.Add("USOH", typeof(decimal));
                value.Columns.Add("TotalSOH", typeof(decimal));

                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["USOH"] = v.USOH;
                    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["Status"] = v.Status;
                    drv["TypeID"] = v.TypeID;
                    drv["LastDUSoh"] = issue.GetDULastSOH1(Convert.ToInt32(v.ID), dt1, dt2);
                    drv["TotalSOH"] = v.USOH + issue.GetDULastSOH1(Convert.ToInt32(v.ID), dt1, dt2);
                }

                return value;
            }
        }