<?php namespace myresto\models;use Yii;use myresto\models\SyncConfig;use myresto\models\ImportProduct;use myresto\models\ImportModifier;use myresto\models\ImportCustomer;use myresto\models\ImportSupplier;class SyncToJurnal extends \yii\db\ActiveRecord{public static function tableName(){return 'sync_to_jurnal';}public function rules(){return [ [['last_update'],'safe'],[['id','id_record'],'integer'],[['keterangan'],'string'],[['id_jurnal'],'number'],[['tipe_tabel'],'string','max'=>255],[['status_sinkron'],'string','max'=>10],[['tipe_proses'],'string','max'=>20],[['type_con'],'default','value'=>'resto'],[['tipe_tabel','status_sinkron','keterangan','tipe_proses'],'trim'],];}public function attributeLabels(){return [ 'id'=>'ID','last_update'=>'Last Update','tipe_tabel'=>'Tipe Tabel','id_record'=>'Id Record','status_sinkron'=>'Status Sinkron','keterangan'=>'Keterangan','id_jurnal'=>'Id Jurnal','tipe_proses'=>'Tipe Proses',];}public static function getTotalSyncDataMaster(){$conn=SyncConfig::getConnection('MyConnection');$connMyResto=SyncConfig::getConnection('MyResto');$dbNameMyConnection=SyncConfig::getDbNameMyConnection();$type_con=SyncConfig::$type_con;$jumlah=array();$stmt=mysqli_query($conn,"SELECT fpkey FROM sync_data WHERE tipe_tabel = 'rst_menu' ORDER BY fpkey DESC LIMIT 1");if($row=mysqli_fetch_assoc($stmt)){$last_key=$row['fpkey'];}else{$last_key=0;}$stmt=mysqli_query($connMyResto,"SELECT * FROM rst_menu WHERE fpkey > '$last_key'");$jumlah['product']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT * FROM sync_data WHERE tipe_tabel = 'rst_menu' AND is_sync = '0'");$jumlah['product']+=mysqli_num_rows($stmt);$stmt=mysqli_query($connMyResto,"SELECT a.fpkey, a.fcode, fname, fprice, fdesc, b.fcategory category, c.fcategory sub_category, d.id_jurnal, a.fmodifieddt, a.fdeleted
            FROM rst_menu a
            LEFT JOIN rst_menu_category b ON a.fcategory = b.fpkey
            LEFT JOIN rst_menu_sub_category c ON a.fcategory2 = c.fpkey
            JOIN $dbNameMyConnection.sync_data d ON a.fpkey = d.fpkey
            WHERE tipe_tabel = 'rst_menu' AND fmodifieddt > last_update");$jumlah['updateProduct']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT fpkey FROM sync_data WHERE tipe_tabel = 'bp_customer' ORDER BY fpkey DESC LIMIT 1");if($row=mysqli_fetch_assoc($stmt)){$last_key=$row['fpkey'];}else{$last_key=0;}$stmt=mysqli_query($connMyResto,"SELECT * FROM bp_customer WHERE fpkey > '$last_key'");$jumlah['customer']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT * FROM sync_data WHERE tipe_tabel = 'bp_customer' AND is_sync = '0'");$jumlah['customer']+=mysqli_num_rows($stmt);$stmt=mysqli_query($connMyResto,"SELECT * FROM bp_customer a
            JOIN $dbNameMyConnection.sync_data b ON a.fpkey = b.fpkey
            WHERE tipe_tabel = 'bp_customer' AND fmodifieddt > last_update");$jumlah['updateCustomer']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT fpkey FROM sync_data WHERE tipe_tabel = 'rst_menu_notes2' ORDER BY fpkey DESC LIMIT 1");if($row=mysqli_fetch_assoc($stmt)){$last_key=$row['fpkey'];}else{$last_key=0;}$stmt=mysqli_query($connMyResto,"SELECT * FROM rst_menu_notes2 WHERE fpkey > '$last_key'");$jumlah['modifier']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT * FROM sync_data WHERE tipe_tabel = 'rst_menu_notes2' AND is_sync = '0'");$jumlah['modifier']+=mysqli_num_rows($stmt);$stmt=mysqli_query($connMyResto,"SELECT * FROM rst_menu_notes2 a
            JOIN $dbNameMyConnection.sync_data b ON a.fpkey = b.fpkey
            WHERE tipe_tabel = 'rst_menu_notes2' AND fmodifieddt > last_update");$jumlah['updateModifier']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT fpkey FROM sync_data WHERE tipe_tabel = 'bp_supplier' ORDER BY fpkey DESC LIMIT 1");if($row=mysqli_fetch_assoc($stmt)){$last_key=$row['fpkey'];}else{$last_key=0;}$stmt=mysqli_query($connMyResto,"SELECT * FROM bp_supplier WHERE fpkey > '$last_key' AND fpkey != 9999");$jumlah['supplier']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT * FROM sync_data WHERE tipe_tabel = 'bp_supplier' AND is_sync = '0'");$jumlah['supplier']+=mysqli_num_rows($stmt);$stmt=mysqli_query($connMyResto,"SELECT * FROM bp_supplier a
            JOIN $dbNameMyConnection.sync_data b ON a.fpkey = b.fpkey
            WHERE tipe_tabel = 'bp_supplier' AND fmodifieddt > last_update");$jumlah['updateSupplier']=mysqli_num_rows($stmt);return $jumlah;}public static function getTotalSyncDataTransaction($json=false,$start='',$end=''){$conn=SyncConfig::getConnection('MyConnection');$connMyResto=SyncConfig::getConnection('MyResto');$type_con=SyncConfig::$type_con;$jumlah=array();if(!empty($start))$filterStart="fdate >= '$start'";else $filterStart="";if(!empty($end))$filterEnd="fdate <= '$end'";else $filterEnd="";if($filterStart!=""&&$filterEnd!=""){$and="AND";}else{$and="";}$stmt=mysqli_query($conn,"SELECT last_update FROM sync_data WHERE tipe_tabel = 'rst_fc_trans_header' ORDER BY last_update DESC LIMIT 1");if($row=mysqli_fetch_assoc($stmt)){$last_update=$row['last_update'];}else{$last_update="0000-00-00 00:00:00";}if(empty($start)&&empty($end)){$tambah="fdate > '$last_update'";}else{$tambah="$filterStart $and $filterEnd";}$stmt=mysqli_query($connMyResto,"SELECT * FROM rst_fc_trans_header WHERE $tambah AND fco_date != '0000-00-00'");$jumlah['transaction']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT * FROM sync_data WHERE tipe_tabel = 'rst_fc_trans_header' AND is_sync = '0'");$jumlah['transaction']+=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT fpkey FROM sync_data WHERE tipe_tabel = 'rst_fc_payment' ORDER BY fpkey DESC LIMIT 1");if($row=mysqli_fetch_assoc($stmt)){$last_key=$row['fpkey'];}else{$last_key=0;}if(empty($start)&&empty($end)){$tambah="fpkey > '$last_key'";}else{$tambah="$filterStart $and $filterEnd";}$stmt=mysqli_query($connMyResto,"SELECT * FROM rst_fc_payment WHERE $tambah");$jumlah['payment']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT * FROM sync_data WHERE tipe_tabel = 'rst_fc_payment' AND is_sync = '0'");$jumlah['payment']+=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT last_update FROM sync_data WHERE tipe_tabel = 'sales_return' ORDER BY last_update DESC LIMIT 1");if($row=mysqli_fetch_assoc($stmt)){$last_update=$row['last_update'];}else{$last_update="0000-00-00 00:00:00";}if(empty($start)&&empty($end)){$tambah="fvoiddt > '$last_update'";}else{if(!empty($start))$voidstart="fvoidclosingdate >= '$start'";else $voidstart="";if(!empty($end))$voidend="fvoidclosingdate <= '$end'";else $voidend="";if($voidstart!=""&&$voidend!=""){$and2="AND";}else{$and2="";}$tambah="$voidstart $and2 $voidend";}$stmt=mysqli_query($connMyResto,"SELECT * FROM rst_fc_trans_header WHERE $tambah");$jumlah['salesReturn']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT * FROM sync_data WHERE tipe_tabel = 'sales_return' AND is_sync = '0'");$jumlah['salesReturn']+=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT last_update FROM sync_data WHERE tipe_tabel = 'bp_customer_deposit' ORDER BY last_update DESC LIMIT 1");if($row=mysqli_fetch_assoc($stmt)){$last_update=$row['last_update'];}else{$last_update="0000-00-00 00:00:00";}if(empty($start)&&empty($end)){$tambah="fdate > '$last_update'";}else{$tambah="$filterStart $and $filterEnd";}$stmt=mysqli_query($connMyResto,"SELECT * FROM bp_customer_deposit WHERE $tambah");$jumlah['deposit']=mysqli_num_rows($stmt);$stmt=mysqli_query($conn,"SELECT * FROM sync_data WHERE tipe_tabel = 'bp_customer_deposit' AND is_sync = '0'");$jumlah['deposit']+=mysqli_num_rows($stmt);if($json)$jumlah=json_encode($jumlah);return $jumlah;}public static function anyFailed(){$count=self::find()->where(['status_sinkron'=>'failed'])->andWhere(['type_con'=>'resto'])->andWhere(['in','tipe_tabel',[ ImportProduct::$tipe_tabel,ImportModifier::$tipe_tabel,ImportCustomer::$tipe_tabel,ImportSupplier::$tipe_tabel ]])->count();return $count;}public static function canSalesSync(){$data=self::getLastSync('master','manual');if($data['status']=='Success')return true;else return false;}public static function getLastSync($dataType='master',$syncType='manual'){$conn=SyncConfig::getConnection('MyConnection');$connMyResto=SyncConfig::getConnection('MyResto');$dbNameMyConnection=SyncConfig::getDbNameMyConnection();$type_con='resto';$query="SELECT * FROM sync_to_jurnal_header WHERE type_con = '$type_con' AND jenis_data = '$dataType' AND jenis_sinkron = '$syncType' ORDER BY id_header DESC LIMIT 1";$resultHeader=mysqli_query($conn,$query);$resultHeader=mysqli_fetch_all($resultHeader,MYSQLI_ASSOC);if($resultHeader){$resultHeader=$resultHeader[0];$idHeaderLog=$resultHeader['id_header'];$query="
                SELECT
                    a.tipe_tabel,
                    a.id_record,
                    CASE
                        WHEN a.tipe_tabel = 'rst_menu' 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 = 'rst_menu_notes2' THEN modifier.fcode
                        ELSE product.fname
                    END AS fname,
                    a.keterangan
                FROM $dbNameMyConnection.sync_to_jurnal a
                LEFT JOIN rst_menu 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 rst_menu_notes2 modifier ON a.id_record = modifier.fpkey
                WHERE a.id_header = $idHeaderLog AND a.type_con = '$type_con' AND a.status_sinkron != 'success'";$result=mysqli_query($connMyResto,$query);$result=mysqli_fetch_all($result,MYSQLI_ASSOC);$count=count($result);if($count>0)$status='Fail';else $status='Success';$data=[ 'date'=>$resultHeader['date_created'],'status'=>$status,];}else{$data=false;}return $data;}}