<?php namespace myretail\controllers;use Yii;use myretail\models\SyncToJurnal;use myretail\models\SyncToJurnalSearch;use yii\web\Controller;use yii\web\NotFoundHttpException;use yii\filters\VerbFilter;use myretail\models\SyncConfig;class SyncToJurnalController extends Controller{public function behaviors(){return [ 'verbs'=>[ 'class'=>VerbFilter::className(),'actions'=>[ 'delete'=>['POST'],],],];}public function actionIndex(){$searchModel=new SyncToJurnalSearch();$dataProvider=$searchModel->search(Yii::$app->request->queryParams);return $this->render('index',[ 'searchModel'=>$searchModel,'dataProvider'=>$dataProvider,]);}public function actionView($id){return $this->render('view',[ 'model'=>$this->findModel($id),]);}public function actionCreate(){$model=new SyncToJurnal();if($model->load(Yii::$app->request->post())&&$model->save()){return $this->redirect(['view','id'=>$model->id]);}return $this->render('create',[ 'model'=>$model,]);}public function actionUpdate($id){$model=$this->findModel($id);if($model->load(Yii::$app->request->post())&&$model->save()){return $this->redirect(['view','id'=>$model->id]);}return $this->render('update',[ 'model'=>$model,]);}public function actionDelete($id){$this->findModel($id)->delete();return $this->redirect(['index']);}protected function findModel($id){if(($model=SyncToJurnal::findOne($id))!==null){return $model;}throw new NotFoundHttpException('The requested page does not exist.');}public static function actionGetTotalSyncDataTransaction($json=false,$start='',$end=''){$conn=SyncConfig::getConnection('MyConnection');$connMyRetail=SyncConfig::getConnection('MyRetail');$type_con=SyncConfig::$type_con;$jumlah=array();if(!empty($start))$filterStart=" AND fdate >= '$start'";else $filterStart="";if(!empty($end))$filterEnd=" AND fdate <= '$end'";else $filterEnd="";$stmt=mysqli_query($conn,"SELECT id_record FROM sync_to_jurnal WHERE tipe_tabel = 'rtl_fc_trans_header' AND type_con = '$type_con' ORDER BY id_record DESC LIMIT 1");$row=mysqli_fetch_assoc($stmt);if($row!=NULL){$last_key_tran=$row['id_record'];}else{$last_key_tran=0;}$stmt=mysqli_query($conn,"SELECT id_record FROM sync_to_jurnal WHERE tipe_tabel = 'rtl_fc_payment' AND type_con = '$type_con' ORDER BY id_record DESC LIMIT 1");$row=mysqli_fetch_assoc($stmt);if($row!=NULL){$last_key_pay=$row['id_record'];}else{$last_key_pay=0;}$stmt=mysqli_query($conn,"SELECT id_record FROM sync_to_jurnal WHERE tipe_tabel = 'sales_return' AND type_con = '$type_con' ORDER BY id_record DESC LIMIT 1");$row=mysqli_fetch_assoc($stmt);if($row!=NULL){$last_key_salret=$row['id_record'];}else{$last_key_salret=0;}$stmt=mysqli_query($connMyRetail,"SELECT fpkey FROM rtl_fc_trans_header WHERE fpkey > $last_key_tran".$filterStart.$filterEnd);$num=mysqli_num_rows($stmt);$jumlah['transaction']=$num;$stmt=mysqli_query($connMyRetail,"SELECT fpkey FROM rtl_fc_payment WHERE fpkey > $last_key_pay".$filterStart.$filterEnd);$num=mysqli_num_rows($stmt);$jumlah['payment']=$num;$stmt=mysqli_query($connMyRetail,"SELECT fpkey FROM rtl_fc_trans_header WHERE fpkey > $last_key_salret AND fvoiddt != '0000-00-00 00:00:00'".$filterStart.$filterEnd);$num=mysqli_num_rows($stmt);$jumlah['salesReturn']=$num;if($json)$jumlah=json_encode($jumlah);return $jumlah;}public function actionInsertHeaderLog(){$conn=SyncConfig::getConnection();$post=Yii::$app->request->post();$jenis_sinkron=$post['jenis_sinkron'];$jenis_data=$post['jenis_data'];$type_con=$post['type_con'];$query="INSERT INTO sync_to_jurnal_header (jenis_sinkron, jenis_data, type_con) VALUES ('$jenis_sinkron', '$jenis_data', '$type_con')";mysqli_query($conn,$query);return mysqli_insert_id($conn);}public function actionCheckLog(){$conn=SyncConfig::getConnection('MyConnection');$connMyRetail=SyncConfig::getConnection('MyRetail');$dbNameMyConnection=SyncConfig::getDbNameMyConnection();$post=Yii::$app->request->post();$idHeaderLog=$post['idHeaderLog'];$type_con=$post['type_con'];$query="
            SELECT
                a.tipe_tabel,
                a.id_record,
                CASE
                    WHEN a.tipe_tabel = 'inv_item' THEN product.fname
                    WHEN a.tipe_tabel = 'bp_customer' THEN customer.fname
                    WHEN a.tipe_tabel = 'bp_supplier' THEN supplier.fname
                    WHEN a.tipe_tabel = 'inv_warehouse' THEN warehouse.fname
                    WHEN a.tipe_tabel = 'rtl_fc_trans_header' THEN transaction.fcode
                    WHEN a.tipe_tabel = 'rtl_fc_payment' THEN transactionPayment.fcode
                    WHEN a.tipe_tabel = 'sales_return' THEN transaction.fcode
                    ELSE product.fname
                END AS fname,
                a.keterangan
            FROM $dbNameMyConnection.sync_to_jurnal a
            LEFT JOIN inv_item product ON a.id_record = product.fpkey
            LEFT JOIN bp_customer customer ON a.id_record = customer.fpkey
            LEFT JOIN bp_supplier supplier ON a.id_record = supplier.fpkey
            LEFT JOIN inv_warehouse warehouse ON a.id_record = warehouse.fpkey
            LEFT JOIN rtl_fc_trans_header transaction ON a.id_record = transaction.fpkey
            LEFT JOIN rtl_fc_payment payment ON a.id_record = payment.fpkey
            LEFT JOIN rtl_fc_trans_header transactionPayment ON payment.ftranskey = transactionPayment.fpkey
            WHERE a.id_header = $idHeaderLog AND a.type_con = '$type_con' AND a.status_sinkron != 'success'";$result=mysqli_query($connMyRetail,$query);$result=mysqli_fetch_all($result,MYSQLI_ASSOC);foreach($result as $i=>$key){$id=$key['id_record'];$keterangan="";$ket=json_decode($key['keterangan'],TRUE);if(isset($ket['name'])){$keterangan.="Product name had already been used<br>";}if(isset($ket['product_code'])){$keterangan.="Product code had already been used<br>";}if(preg_match("/transaction_id or transaction_no/i",$key['keterangan'])){$keterangan.="Transaction is not available";}if(preg_match("/product in transaction_lines_attributes on row/i",$key['keterangan'])){$arrp=array();$arr=explode(",",str_replace("{","",$key['keterangan']));foreach($arr as $x){$angka1=substr($x,48,1);$angka1-=1;$detail1=mysqli_query($connMyRetail,"SELECT b.fname, a.fprice, a.fnotespricesubtotal, a.fnotes, a.fqty, a.fdisctype, a.fdiscvalue, a.fdiscount, fcomboheader, fnotesglobal
                        FROM rtl_fc_trans_detail a
                        JOIN inv_item b ON a.fmenukey = b.fpkey
                        WHERE a.ftranskey = '$id' AND fiscomboheader != 1");$detail1=mysqli_fetch_all($detail1,MYSQLI_ASSOC);$detail2=mysqli_query($connMyRetail,"SELECT a.fnotes fname, SUM(a.fqty) AS jml
                        FROM rtl_fc_trans_detail_modifier a
                        JOIN rtl_fc_trans_detail b ON a.ftranskeyd = b.fpkey
                        WHERE a.fpkey > 0 AND ftype = '4' AND ftranskeyh = '$id' AND fcancelby = 0 GROUP BY a.fnotes");$detail2=mysqli_fetch_all($detail2,MYSQLI_ASSOC);$detail=array_merge($detail1,$detail2);$pn=$detail[$angka1]['fname'];$keterangan.="Product $pn is not available in the transaction<br>";}}if($keterangan!=""){$result[$i]['keterangan']=$keterangan;}}$query="SELECT * FROM sync_to_jurnal_header WHERE id_header = $idHeaderLog AND type_con = '$type_con'";$resultHeader=mysqli_query($conn,$query);$resultHeader=mysqli_fetch_all($resultHeader,MYSQLI_ASSOC);$data=[ 'header'=>$resultHeader[0],'detail'=>$result,];return json_encode($data);}}