/// <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;
}
}