コード例 #1
0
 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);
 }
コード例 #2
0
ファイル: SiteController.php プロジェクト: hendri30/OpenPrint
 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);
 }