public function actionReportsaldostockbarang()
{
$dataTorender = [];
$formModel = new \app\models\ReportBalanceOfItemStock();
$param = Yii::$app->request->get();
if ($formModel->load($param)) {
}
$dataTorender['dataProvider'] = new \yii\data\SqlDataProvider(['sql' => "with r_c(id,pn,rf,src,st,pr,uom,srl,slc,dlc,dd,scdd,sts,mst,qty,qty1) as\n\t\t\t\t\t(\n\t\t\t\t\tselect \n\t\t\t\t\t\t\t stv.id \n\t\t\t\t\t\t\t,stv.name \n\t\t\t\t\t\t\t,sp.name \n\t\t\t\t\t\t\t,stv.origin \n\t\t\t\t\t\t\t,spt.type\n\t\t\t\t\t\t\t,pp.name_template \n\t\t\t\t\t\t\t,po.name \n\t\t\t\t\t\t\t,spl.company_id \n\t\t\t\t\t\t\t,sls.complete_name \n\t\t\t\t\t\t\t,sld.complete_name \n\t\t\t\t\t\t\t,stv.date \n\t\t\t\t\t\t\t,stv.create_date \n\t\t\t\t\t\t\t,stv.state\n\t\t\t\t\t\t\t,case when sls.complete_name = (select complete_name from stock_location as sl where sl.id = 12) then 'keluar' else 'masuk' end as move_status\n\t\t\t\t\t\t\t,case when sls.complete_name = (select complete_name from stock_location as sl where sl.id = 12) then -(stv.product_qty) else stv.product_qty end\n\t\t\t\t\t\t\t,sum(stv.product_qty) \n\n\t\t\t\t\tfrom \n\t\t\t\t\tstock_move as stv\n\t\t\t\t\tJOIN stock_picking sp ON sp.id = stv.picking_id\n\t\t\t\t\tJOIN stock_picking spt ON spt.id = stv.picking_id\n\t\t\t\t\tJOIN product_product pp ON pp.id = stv.product_id\n\t\t\t\t\tJOIN product_uom po ON po.id = stv.product_uom\n\t\t\t\t\tJOIN stock_production_lot spl on spl.id = stv.prodlot_id\n\t\t\t\t\tJOIN stock_location sls on sls.id = stv.location_id\n\t\t\t\t\tJOIN stock_location sld on sld.id = stv.location_dest_id\n\n\t\t\t\t\twhere stv.state in('done') group by stv.id,sp.id,spt.id,pp.id,po.id,spl.id,sls.id,sld.id order by date\n\t\t\t\t\t)\n\n\t\t\t\t\tSELECT\n\n\t\t\t\t\t pn as product_name\n\t\t\t\t\t,rf as referensi\n\t\t\t\t\t,src as source\n\t\t\t\t\t,st as shipping_type\n\t\t\t\t\t,pr as product\n\t\t\t\t\t,uom as unit_of_measure\n\t\t\t\t\t,srl as serial\n\t\t\t\t\t,slc as source_location\n\t\t\t\t\t,dlc as destination_location\n\t\t\t\t\t,dd as date\n\t\t\t\t\t,scdd as schedule_date\n\t\t\t\t\t,sts as status\n\t\t\t\t\t,mst as move_status\n\t\t\t\t\t,qty as quantity\n\t\t\t\t\t,sum(qty) over (order by id asc) as saldo\n\t\t\t\t\tfrom r_c", 'params' => [':warelct' => $formModel->warelct]]);
$dataToRender['warelct'] = $formModel->warelct;
$dataToRender['formModel'] = $formModel;
// var_dump($dataProvider);
return $this->render('reportsaldostockbarang', $dataToRender);
}
public function actionReportSaldo()
{
$dataToRender = [];
$formModel = new \app\models\ReportBalanceOfItemStock();
$param = Yii::$app->request->get();
if ($formModel->load($param)) {
}
$dataToRender['dataProvider'] = new \yii\data\SqlDataProvider(['sql' => "with r_c(id,pn,rf,src,st,pr,uom,srl,slc,dlc,dd,scdd,sts,mst,qty,lbm_no,cust_doc_ref,po_name,dn_name,im_name,origin,rp_name) as\r\n (select \r\n stv.id \r\n ,stv.name \r\n ,sp.name \r\n ,stv.origin \r\n ,sp.type\r\n ,pp.name_template \r\n ,pu.name \r\n ,spl.company_id \r\n ,sls.complete_name \r\n ,sld.complete_name \r\n ,stv.date \r\n ,stv.create_date \r\n ,stv.state\r\n ,case when sls.complete_name = (select complete_name from stock_location as sl where sl.id = :warelct) then 'keluar' else 'masuk' end as move_status\r\n ,case when sls.complete_name = (select complete_name from stock_location as sl where sl.id = :warelct) then -(stv.product_qty) else stv.product_qty end\r\n ,lbm_no\r\n ,cust_doc_ref\r\n ,po.name as po_name\r\n ,dn.name as dn_name\r\n ,im.name as im_name\r\n ,stv.origin\r\n ,rp.display_name as rp_name\r\n\r\n from \r\n stock_move as stv\r\n \r\n JOIN product_product pp ON pp.id = stv.product_id\r\n JOIN product_uom pu ON pu.id = stv.product_uom\r\n JOIN stock_location sls on sls.id = stv.location_id\r\n JOIN stock_location sld on sld.id = stv.location_dest_id\r\n LEFT JOIN stock_picking sp ON sp.id = stv.picking_id\r\n LEFT JOIN stock_production_lot spl on spl.id = stv.prodlot_id\r\n LEFT JOIN purchase_order po on po.id = sp.purchase_id\r\n LEFT JOIN internal_move im on im.id = sp.internal_move_id\r\n LEFT JOIN res_partner rp on rp.id = sp.partner_id\r\n LEFT JOIN delivery_note dn on dn.id = sp.note_id\r\n\r\n where stv.state in('done') \r\n and stv.product_id = :product_id\r\n AND(\r\n stv.location_id=:warelct\r\n OR stv.location_dest_id=:warelct\r\n )\r\n --order by date\r\n )\r\n\r\n SELECT\r\n\r\n pn as product_name\r\n ,rf as referensi\r\n ,src as source\r\n ,st as shipping_type\r\n ,pr as product\r\n ,uom as unit_of_measure\r\n ,srl as serial\r\n ,slc as source_location\r\n ,dlc as destination_location\r\n ,dd as date\r\n ,scdd as schedule_date\r\n ,sts as status\r\n ,mst as move_status\r\n ,qty as quantity\r\n ,sum(qty) over (order by dd asc) as saldo\r\n ,lbm_no\r\n ,cust_doc_ref\r\n ,po_name as purchase_order\r\n ,dn_name as delivery_note\r\n ,im_name as internal_move\r\n ,origin\r\n ,rp_name as partner\r\n\r\n from r_c", 'params' => [':product_id' => $formModel->product_id, ':warelct' => $formModel->warelct], 'pagination' => false, 'sort' => ['attributes' => ['date'], 'defaultOrder' => ['date' => SORT_DESC]]]);
$dataToRender[':product_id'] = $formModel->product_id;
$dataToRender[':warelct'] = $formModel->warelct;
$dataToRender['formModel'] = $formModel;
// var_dump($dataProvider);
return $this->render('reportsaldo', $dataToRender);
}