public IQueryable <WIPViewModel> GetWIPReport(DateTime?date, int offset)
{
DateTime Date = date == null ? new DateTime(1970, 1, 1) : (DateTime)date;
string Dates = Date.ToString("yyyy-MM-dd");
List <WIPViewModel> wipData = new List <WIPViewModel>();
try
{
string connectionString = APIEndpoint.ConnectionString;
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(" select * from ( select Kode, komoditi,Satuan,sum(WIP) WIP from ( SELECT c.ComodityCode Kode, ComodityName komoditi ,'PCS' Satuan,sum(JumlahCutting) WIP from Factcutting j join comodity c on j.ComodityCode=c.ComodityCode where ProcessDate <='" + Dates + "' group by ro,c.ComodityCode,ComodityName union all SELECT c.ComodityCode Kode, ComodityName komoditi ,'PCS' Satuan,sum(-JumlahFinish) WIP from FactFinishing j join comodity c on j.ComodityCode=c.ComodityCode where ProcessDate <='" + Dates + "' group by ro,c.ComodityCode,ComodityName ) as data where wip<>0 group by Kode,komoditi,Satuan ) as data where WIP>0 union all select itemcode,ArticleNo,satuan,SUM(WIP) WIP from( SELECT ItemCode, ArticleNo ,'MTR' Satuan,round(sum(JumlahPrepare),3) WIP from factprepare j where ProcessDate <='" + Dates + "' group by ro,ItemCode,ArticleNo ) d2 where WIP>0.01 group by ItemCode,ArticleNo,satuan ", conn))
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dataAdapter.Fill(dSet);
foreach (DataRow data in dSet.Tables[0].Rows)
{
WIPViewModel view = new WIPViewModel
{
Kode = data["Kode"].ToString(),
Comodity = data["komoditi"].ToString(),
UnitQtyName = data["Satuan"].ToString(),
WIP = String.Format("{0:n}", (double)data["WIP"])
};
wipData.Add(view);
}
}
conn.Close();
}
}
catch (SqlException ex)
{
}
return(wipData.AsQueryable());
}
public IQueryable <WIPViewModel> GetWIPReport(DateTime?date, int offset)
{
DateTime Date = date == null ? new DateTime(1970, 1, 1) : (DateTime)date;
string Dates = Date.ToString("yyyy-MM-dd");
List <WIPViewModel> wipData = new List <WIPViewModel>();
try
{
string connectionString = APIEndpoint.ConnectionString;
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(" SELECT Kode,ComodityName as komoditi, Satuan, case when SUM(JumlahCutting-jumlahFinish)<0 then 0 else SUM(JumlahCutting-jumlahFinish)end AS WIP FROM (SELECT HO.No, HO.Qty, HO.Kode, KOM.ComodityName, LI.SizeNumber,LI.JumlahCutting, ISNULL(FOUT.JumlahFinish, 0) AS JumlahFinish, 'PCS' AS Satuan FROM HOrder AS HO INNER JOIN Comodity AS KOM ON HO.Kode = KOM.ComodityCode INNER JOIN (SELECT ro,ComodityCode,SizeId,SizeNumber,sum(JumlahCutting)JumlahCutting from Factcutting where ProcessDate <='" + Dates + "' group by ro,ComodityCode,SizeId,SizeNumber) AS LI ON HO.No = LI.RO and ho.kode = li.ComodityCode LEFT OUTER JOIN (SELECT ro,ComodityCode,SizeId,SizeNumber,sum(JumlahFinish) JumlahFinish from FactFinishing where ProcessDate <='" + Dates + "' group by ro,ComodityCode,SizeId,SizeNumber) AS FOUT ON LI.RO = FOUT.RO AND LI.SizeId = FOUT.SizeId and LI.ComodityCode=FOUT.ComodityCode ) AS HASIL GROUP BY Kode, ComodityName, Satuan ORDER BY Kode, komoditi ", conn))
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dataAdapter.Fill(dSet);
foreach (DataRow data in dSet.Tables[0].Rows)
{
WIPViewModel view = new WIPViewModel
{
Kode = data["Kode"].ToString(),
Comodity = data["komoditi"].ToString(),
UnitQtyName = data["Satuan"].ToString(),
WIP = String.Format("{0:n}", (double)data["WIP"])
};
wipData.Add(view);
}
}
conn.Close();
}
}
catch (SqlException ex)
{
}
return(wipData.AsQueryable());
}