public IQueryable <FinishedGoodViewModel> GetMachineMutationReport(DateTime?dateFrom, DateTime?dateTo, int offset)
{
DateTime Datefrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime Dateto = dateTo == null ? new DateTime(1970, 1, 1) : (DateTime)dateTo;
string DateFrom = Datefrom.ToString("yyyy-MM-dd");
string DateTo = Dateto.ToString("yyyy-MM-dd");
List <FinishedGoodViewModel> machine = new List <FinishedGoodViewModel>();
try
{
string connectionString = APIEndpoint.ConnectionString;
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("declare @TglLaporan datetime='" + DateFrom + "'" +
"declare @Tgl2 datetime = '" + DateTo + "'" +
"select B.KodeBarang, B.NamaBarang, B.Sat,convert(float, B.SaldoAwal) as SaldoAwal,convert(float, B.Pemasukan) as Pemasukan,convert(float, B.Pengeluaran) as Pengeluaran,convert(float,B.Penyesuaian) as Penyesuaian,convert(float, (B.SaldoAwal + (B.Pemasukan - B.Pengeluaran))) as SaldoBuku,convert(float, B.StockOpname) as StockOpname, convert(float,B.Selisih) as Selisih, B.ActivateDate, B.CloseDate from " +
"(select A.KodeBarang, A.NamaBarang, A.Sat,(A.SaldoAwal + (select isnull(SUM(TransactionAmount), 0) from dbo.MachineMutation where MachineID = A.KodeBarang and TransactionType = 'IN' and TransactionDate < @TglLaporan) - (select isnull(SUM(TransactionAmount), 0) from dbo.MachineMutation where MachineID = A.KodeBarang and TransactionType = 'OUT' and TransactionDate < @TglLaporan)) as SaldoAwal, (select isnull(SUM(TransactionAmount), 0) from dbo.MachineMutation where MachineID = A.KodeBarang and TransactionType = 'IN' and TransactionDate>= @TglLaporan and TransactionDate<DATEADD(DAY,1,@Tgl2)) as Pemasukan, "+
"(select isnull(SUM(TransactionAmount), 0) from dbo.MachineMutation where MachineID = A.KodeBarang and TransactionType = 'OUT' and TransactionDate>= @TglLaporan and TransactionDate<DATEADD(DAY,1,@Tgl2)) as Pengeluaran, " +
"A.Penyesuaian, A.StockOpname, A.Selisih, A.ActivateDate, A.CloseDate from view_machinereport A where A.ActivateDate <= @Tgl2)B " +
"where B.SaldoAwal > 0 or(B.SaldoAwal + (B.Pemasukan - B.Pengeluaran)) > 0 order by B.KodeBarang ", conn))
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dataAdapter.Fill(dSet);
foreach (DataRow data in dSet.Tables[0].Rows)
{
FinishedGoodViewModel view = new FinishedGoodViewModel
{
KodeBarang = data["KodeBarang"].ToString(),
NamaBarang = data["NamaBarang"].ToString(),
Pemasukan = String.Format("{0:n}", (double)data["Pemasukan"]),
Pengeluaran = String.Format("{0:n}", (double)data["Pengeluaran"]),
SaldoAwal = String.Format("{0:n}", (double)data["SaldoAwal"]),
SaldoBuku = String.Format("{0:n}", (double)data["SaldoBuku"]),
UnitQtyName = data["Sat"].ToString(),
Penyesuaian = String.Format("{0:n}", (double)data["Penyesuaian"]),
StockOpname = String.Format("{0:n}", (double)data["StockOpname"]),
Selisih = String.Format("{0:n}", (double)data["Selisih"])
};
machine.Add(view);
}
}
conn.Close();
}
}
catch (SqlException ex)
{
//Log exception
//Display Error message
}
return(machine.AsQueryable());
}
public IQueryable <FinishedGoodViewModel> GetFinishedGoodReport(DateTime?dateFrom, DateTime?dateTo, int offset)
{
DateTime Datefrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime Dateto = dateTo == null ? new DateTime(1970, 1, 1) : (DateTime)dateTo;
string DateFrom = Datefrom.ToString("yyyy-MM-dd");
string DateTo = Dateto.ToString("yyyy-MM-dd");
List <FinishedGoodViewModel> wipData = new List <FinishedGoodViewModel>();
try
{
string connectionString = APIEndpoint.ConnectionString;
//"// select ComodityId,0 as SaldoQtyFin, SUM(Qty) as QtyFin,0 as FinishingTransfer,0 as AdjFin, 0 as Retur ,0 as QtyExport, 0 as QtySample, 0 as QtyOther ,0 as sewingretur, 0 as QtyFinSub, 0 as cuttingSubkon ,0 as finoutSubkon from (select ReferenceFinishingInDetailId ,ProcessDate,ComodityId,UnitCode,UnitCodeTo,Qty from FactFinishingDetailNotPembelian where ProcessDate between @DateFrom and @DateTo and FinishingOutTo = 'GUDANG JADI' and UnitCode= UnitCodeTo ) data group by ComodityId " +
// "//union all "
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(
"declare @DateFrom datetime = '" + DateFrom + "' " +
"declare @DateTo datetime = '" + DateTo + "' " +
" select ComodityId, (case when ProcessDate < @DateFrom then -Qty else 0 end) as SaldoQtyFin,0 as QtyFin,0 as FinishingTransfer,(case when ProcessDate >= @DateFrom then Qty else 0 end) as AdjFin, 0 as Retur ,0 as QtyExport, 0 as QtySample, 0 as QtyOther,0 as sewingretur ,0 as QtyFinSub,0 as cuttingSubkon,0 as finoutSubkon into #adjust from FactAdjustment where ProcessDate <= @DateTo and AdjustType = 'FINISHING' " +
" select * into #returexpend from ( select ComodityId, sum(case when ProcessDate < @DateFrom then Qty else 0 end) as SaldoQtyFin, 0 as QtyFin, 0 as FinishingTransfer, 0 as AdjFin, sum(case when ProcessDate >= @DateFrom then Qty else 0 end) as Retur, 0 as QtyExport, 0 as QtySample, 0 as QtyOther, 0 as sewingretur, 0 as QtyFinSub, 0 as cuttingSubkon, 0 as finoutSubkon from factreturExpend where ProcessDate <= @DateTo group by ComodityId) as data where retur > 0 or SaldoQtyFin >0 " +
" select * into #factexpend from ( select ComodityId,sum(case when ProcessDate < @DateFrom then (-1*Qty) else 0 end) as SaldoQtyFin,0 as QtyFin,0 as FinishingTransfer,0 as AdjFin,0 as Retur,sum(case when ProcessDate >= @DateFrom and ExpenditureType = 'E001' then Qty else 0 end) as QtyExport, sum(case when ProcessDate >= @DateFrom and ExpenditureType = 'E003' then Qty else 0 end) as QtySample,sum (case when ProcessDate >= @DateFrom and ExpenditureType = 'E002' then Qty else 0 end) as QtyOther ,0 as sewingretur ,0 as QtyFinSub,0 as cuttingSubkon,0 as finoutSubkon from ( select ComodityId, Qty, ProcessDate, ExpenditureType from FactExpenditureGoods where ProcessDate <= @DateTo and ExpenditureType IN ('E001','E003') )data group by ComodityId ) as data where saldoqtyfin<>0 or QtyExport <>0 or QtySample <>0 or QtyOther <> 0 " +
" select * into #finishingbarangjadi from ( select ComodityId,sum(case when ProcessDate < @DateFrom then Qty else 0 end) as SaldoQtyFin, sum(case when ProcessDate >= @DateFrom then Qty else 0 end) as QtyFin,0 as FinishingTransfer,0 as AdjFin, 0 as Retur ,0 as QtyExport, 0 as QtySample, 0 as QtyOther ,0 as sewingretur, 0 as QtyFinSub, 0 as cuttingSubkon ,0 as finoutSubkon from ( select ProcessDate, ComodityId, UnitCode, UnitCodeTo, Qty from FactFinishingDetailBarangJadi where ProcessDate <= @DateTo and FinishingOutTo = 'GUDANG JADI') data group by comodityid ) as data where saldoqtyfin > 0 or qtyfin >0 "+
" select * into #factsewing from ( select ComodityId, 0 as SaldoQtyFin, 0 as QtyFin, 0 as FinishingTransfer, 0 as AdjFin, 0 as Retur, 0 as QtyExport, 0 as QtySample, 0 as QtyOther, (case when ProcessDate >= @DateFrom and SewingOutTo = 'CUTTING' and UnitCode = UnitCodeTo then Qty else 0 end) as sewingretur,0 as QtyFinSub ,0 as cuttingSubkon, 0 as finoutSubkon from factsewing where ProcessDate <= @DateTo ) as data where sewingretur >0 " +
" select * into #factfinishingsubkon from ( select ComodityId, (case when ProcessDate < @DateFrom then Qty else 0 end) as SaldoQtyFin, 0 as QtyFin ,0 as FinishingTransfer,0 as AdjFin, 0 as Retur ,0 as QtyExport, 0 as QtySample, 0 as QtyOther ,0 as sewingretur,0 as QtyFinSub ,0 as cuttingSubkon, (case when ProcessDate >= @DateFrom then Qty else 0 end) as finoutSubkon from FactFinishingOutSubkon where ProcessDate <= @DateTo ) as data where finoutSubkon>0 or saldoqtyfin<>0 " +
" select ComodityId,sum (case when ProcessDate < @DateFrom then Qty else 0 end) as SaldoQtyFin,0 as QtyFin,0 as FinishingTransfer,0 as AdjFin,sum(case when ProcessDate >= @DateFrom then Qty else 0 end) as Retur,0 as QtyExport, 0 as QtySample, 0 as QtyOther ,0 as sewingretur ,0 as QtyFinSub,0 as cuttingSubkon,0 as finoutSubkon into #returQtyFin from factreturExpend where ProcessDate <= @DateTo group by ComodityId " +
" select * into #factnotsewingIn from ( select ComodityId, 0 as SaldoQtyFin,0 as QtyFin, isnull(( select case when ProcessDate >= @DateFrom and UnitCode <> UnitCodeTo and SewingOutTo = 'FINISHING' then Qty else 0 end),0) as FinishingTransfer,0 as AdjFin, 0 as Retur ,0 as QtyExport, 0 as QtySample, 0 as QtyOther ,0 as sewingretur,0 as QtyFinSub,0 as cuttingSubkon,0 as finoutSubkon from[FactSewingNotInSewingIn] where ProcessDate <= @DateTo ) as data2 where FinishingTransfer > 0 " +
" select * from (select comodityCode KodeBarang,comodityname NamaBarang,sum(saldoqtyFin ) SaldoAwal ,sum(finoutSubkon +retur +QtyFin) Pemasukan,sum(QtyExport+QtySample+QtyOther+AdjFin) Pengeluaran,'PCS' UnitQtyName,0 as Penyesuaian,0 as StockOpname, sum(saldoqtyFin )+sum(finoutSubkon +retur +QtyFin)-sum(FinishingTransfer+QtyExport+QtySample+QtyOther+AdjFin) SaldoBuku from ( " +
" select * from #adjust " +
" union all " +
" select * from #returexpend " +
" union all " +
" select * from #factexpend " +
" union all " +
" select * from #finishingbarangjadi " +
" union all " +
" select * from #factsewing " +
" union all " +
" select* from #factfinishingsubkon " +
" union all " +
" select * from #factnotsewingIn) as data join( select comodityId, comodityName, comodityCode from comodity ) comodity on data.comodityid = comodity.comodityId " +
" group by comodityname,comodityCode) as data where saldoawal <> 0 or pemasukan <>0 or pengeluaran <>0 or Penyesuaian <>0 or StockOpname <>0 or SaldoBuku <>0 drop table #adjust drop table #returexpend drop table #factexpend drop table #finishingbarangjadi drop table #factsewing " +
" drop table #factfinishingsubkon drop table #returQtyFin drop table #factnotsewingIn "
, conn))
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dataAdapter.Fill(dSet);
foreach (DataRow data in dSet.Tables[0].Rows)
{
var a = data["Pemasukan"];
var b = data["Pengeluaran"];
var c = data["SaldoAwal"];
var g = data["SaldoBuku"];
var j = data["UnitQtyName"];
var o = data["Penyesuaian"];
var u = data["StockOpname"];
FinishedGoodViewModel view = new FinishedGoodViewModel
{
KodeBarang = data["KodeBarang"].ToString(),
NamaBarang = data["NamaBarang"].ToString(),
Pemasukan = String.Format("{0:n}", data["Pemasukan"]),
Pengeluaran = String.Format("{0:n}", data["Pengeluaran"]),
SaldoAwal = String.Format("{0:n}", data["SaldoAwal"]),
SaldoBuku = String.Format("{0:n}", data["SaldoBuku"]),
UnitQtyName = data["UnitQtyName"].ToString(),
Penyesuaian = String.Format("{0:n}", data["Penyesuaian"]),
StockOpname = String.Format("{0:n}", data["StockOpname"]),
Selisih = String.Format("{0:n}", 0)
};
wipData.Add(view);
}
}
conn.Close();
}
}
catch (SqlException ex)
{
}
return(wipData.AsQueryable());
}
public IQueryable <FinishedGoodViewModel> GetFinishedGoodReport(DateTime?dateFrom, DateTime?dateTo, int offset)
{
DateTime Datefrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime Dateto = dateTo == null ? new DateTime(1970, 1, 1) : (DateTime)dateTo;
string DateFrom = Datefrom.ToString("yyyy-MM-dd");
string DateTo = Dateto.ToString("yyyy-MM-dd");
List <FinishedGoodViewModel> wipData = new List <FinishedGoodViewModel>();
try
{
string connectionString = APIEndpoint.ConnectionString;
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("declare @tglBalance datetime set @tglBalance=(select top 1 BalanceDate from BalanceStockProduction where BalanceDate<'" + DateFrom + "' order by BalanceDate desc) " +
"if @tglBalance is null set @tglBalance = CAST(GETDATE() as datetime) "+
"select UnitCode, RO, ComodityID, SizeId, Quantity, UnitQuantity, TotalPrice into #tmp from BalanceStockProduction where BalanceDate=@tglBalance select HASIL.KodeBarang, HASIL.NamaBarang, HASIL.Satuan,convert(float, SUM(HASIL.SaldoAwal)) as SaldoAwal, convert(float,SUM(HASIL.Pemasukan)) as Pemasukan, convert(float,SUM(HASIL.Pengeluaran)) as Pengeluaran,convert(float,SUM(HASIL.Penyesuaian)) as Penyesuaian, convert(float,SUM(HASIL.SaldoBuku)) as SaldoBuku, convert(float,SUM(HASIL.StockOpname)) as StockOpname, convert(float,SUM(HASIL.Selisih) ) as Selisih from (select DATA.KodeBarang, DATA.NamaBarang, DATA.Satuan, SUM(DATA.SaldoAwal) as SaldoAwal, SUM(DATA.Pemasukan) as Pemasukan, SUM(DATA.Pengeluaran) as Pengeluaran, SUM(DATA.Penyesuaian) as Penyesuaian, SUM(DATA.SaldoBuku) as SaldoBuku, SUM(DATA.StockOpname) as StockOpname, SUM(DATA.Selisih) as Selisih from (select TERIMA.KodeBarang, TERIMA.NamaBarang, TERIMA.Satuan, TERIMA.SaldoAwal, TERIMA.Pemasukan, TERIMA.Pengeluaran, TERIMA.Penyesuaian,(TERIMA.SaldoAwal + (TERIMA.Pemasukan - TERIMA.Pengeluaran) + TERIMA.Penyesuaian) as SaldoBuku, 0 as StockOpname, 0 as Selisih from (select KodeBarang, NamaBarang, 'PCS' as Satuan, 0 as SaldoAwal,QuantityFinOut, QuantityStockHistory, QuantityStockHistory as Pemasukan, 0 as Pengeluaran, 0 as Penyesuaian " +
"from FactFinishingOutDetail where ProcessDate >= '" + DateFrom + "' and ProcessDate <= '" + DateTo + "' " +
"union all " +
"select KodeBarang, NamaBarang, 'PCS' as Satuan, (QuantityStockHistory) as SaldoAwal, 0, 0, 0 as Pemasukan, 0 as Pengeluaran, 0 as Penyesuaian " +
"from FactFinishingOutDetail " +
"where ProcessDate >= DATEADD(day, 1, @tglBalance) and ProcessDate <= DATEADD(day, -1, '" + DateFrom + "') " +
"union all " +
"select b.ComodityCode as KodeBarang, b.ComodityName as NamaBarang, 'PCS' as Satuan, a.Quantity as SaldoAwal, 0, 0, 0 as Pemasukan, 0 as Pengeluaran, 0 as Penyesuaian " +
"from #tmp a inner join Comodity b on a.ComodityID=b.ComodityID " +
")TERIMA)DATA group by DATA.KodeBarang, DATA.NamaBarang, DATA.Satuan " +
"union all " +
"select DATA2.KodeBarang, DATA2.NamaBarang, DATA2.Satuan, SUM(DATA2.SaldoAwal) as SaldoAwal, SUM(DATA2.Pemasukan) as Pemasukan, SUM(DATA2.Pengeluaran) as Pengeluaran, " +
"SUM(DATA2.Penyesuaian) as Penyesuaian, SUM(DATA2.SaldoBuku) as SaldoBuku, SUM(DATA2.StockOpname) as StockOpname, SUM(DATA2.Selisih) as Selisih " +
"from(select KELUAR.KodeBarang, KELUAR.NamaBarang, KELUAR.Satuan, KELUAR.SaldoAwal, KELUAR.Pemasukan, KELUAR.Pengeluaran, KELUAR.Penyesuaian,(KELUAR.SaldoAwal + (KELUAR.Pemasukan - KELUAR.Pengeluaran) + KELUAR.Penyesuaian) as SaldoBuku, 0 as StockOpname, 0 as Selisih " +
"from(select KodeBarang, NamaBarang, 'PCS' as Satuan, 0 as SaldoAwal, Qty, 0 as Pemasukan, (Qty) as Pengeluaran, 0 as Penyesuaian " +
"from FactExpenditureGood " +
"where ProcessDate >= '" + DateFrom + "' and ProcessDate <= '" + DateTo + "' " +
"union all " +
"select KodeBarang, NamaBarang, 'PCS' as Satuan, -(Qty) as SaldoAwal, 0, 0 as Pemasukan, 0 as Pengeluaran, 0 as Penyesuaian " +
"from FactExpenditureGood where ProcessDate >= DATEADD(day, 1, @tglBalance) and ProcessDate <= DATEADD(day, -1, '" + DateFrom + "'))KELUAR )DATA2 group by DATA2.KodeBarang, DATA2.NamaBarang, DATA2.Satuan )HASIL group by HASIL.KodeBarang, HASIL.NamaBarang, HASIL.Satuan " +
"order by HASIL.KodeBarang drop table #tmp ", conn))
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dataAdapter.Fill(dSet);
foreach (DataRow data in dSet.Tables[0].Rows)
{
FinishedGoodViewModel view = new FinishedGoodViewModel
{
KodeBarang = data["KodeBarang"].ToString(),
NamaBarang = data["NamaBarang"].ToString(),
Pemasukan = String.Format("{0:n}", (double)data["Pemasukan"]),
Pengeluaran = String.Format("{0:n}", (double)data["Pengeluaran"]),
SaldoAwal = String.Format("{0:n}", (double)data["SaldoAwal"]),
SaldoBuku = String.Format("{0:n}", (double)data["SaldoBuku"]),
UnitQtyName = data["Satuan"].ToString(),
Penyesuaian = String.Format("{0:n}", (double)data["Penyesuaian"]),
StockOpname = String.Format("{0:n}", (double)data["StockOpname"]),
Selisih = String.Format("{0:n}", (double)data["Selisih"])
};
wipData.Add(view);
}
}
conn.Close();
}
}
catch (SqlException ex)
{
}
return(wipData.AsQueryable());
}
