<?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;

/**
 * This is the model class for table "sync_to_jurnal".
 *
 * @property int $id
 * @property string $last_update
 * @property string $tipe_tabel
 * @property int $id_record
 * @property string $status_sinkron
 * @property string $keterangan
 * @property double $id_jurnal
 * @property string $tipe_proses
 */
class SyncToJurnal extends \yii\db\ActiveRecord
{
    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return 'sync_to_jurnal';
    }

    /**
     * {@inheritdoc}
     */
    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'],
        ];
    }

    /**
     * {@inheritdoc}
     */
    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');
        $type_con = SyncConfig::$type_con;
        $jumlah = array();
        
        $stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE tipe_tabel = 'bp_customer' AND type_con = '$type_con' ORDER BY id_record DESC LIMIT 1");
        $row = mysqli_fetch_assoc($stmt);
        if ($row != NULL) {$last_key_cust = $row['id_record'];}else{$last_key_cust = 0;}

        $stmt = mysqli_query($conn, "SELECT last_update FROM sync_to_jurnal WHERE tipe_tabel = 'bp_customer' AND type_con = '$type_con' ORDER BY last_update DESC LIMIT 1");
        $row = mysqli_fetch_assoc($stmt);
        if ($row != NULL) {$last_update_cust = $row['last_update'];}else{$last_update_cust = '0000-00-00 00:00:00';}

        $stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE tipe_tabel = 'rst_menu' AND type_con = '$type_con' ORDER BY id_record DESC LIMIT 1");
        $row = mysqli_fetch_assoc($stmt);
        if ($row != NULL) {$last_key_prod = $row['id_record'];}else{$last_key_prod = 0;}

        $stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE tipe_tabel = 'rst_menu_notes2' AND type_con = '$type_con' ORDER BY id_record DESC LIMIT 1");
        $row = mysqli_fetch_assoc($stmt);
        if ($row != NULL) {$last_key_mod = $row['id_record'];}else{$last_key_mod = 0;}

        $stmt = mysqli_query($conn, "SELECT last_update FROM sync_to_jurnal WHERE tipe_tabel = 'rst_menu' AND type_con = '$type_con' ORDER BY last_update DESC LIMIT 1");
        $row = mysqli_fetch_assoc($stmt);
        if ($row != NULL) {$last_update_prod = $row['last_update'];}else{$last_update_prod = '0000-00-00 00:00:00';}

        $stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE tipe_tabel = 'bp_supplier' AND type_con = '$type_con' ORDER BY id_record DESC LIMIT 1");
        $row = mysqli_fetch_assoc($stmt);
        if ($row != NULL) {$last_key_supp = $row['id_record'];}else{$last_key_supp = 0;}

        $stmt = mysqli_query($conn, "SELECT last_update FROM sync_to_jurnal WHERE tipe_tabel = 'bp_supplier' AND type_con = '$type_con' ORDER BY last_update DESC LIMIT 1");
        $row = mysqli_fetch_assoc($stmt);
        if ($row != NULL) {$last_update_supp = $row['last_update'];}else{$last_update_supp = '0000-00-00 00:00:00';}
        // END - Proses mengambil id terakhir yang ada pada sync to jurnal

        // BEGIN - Proses mengambil jumlah data yang akan di sinkron
        $stmt = mysqli_query($connMyResto, "SELECT fmodifieddt FROM bp_customer WHERE fmodifieddt > '$last_update_cust'");
        $num = mysqli_num_rows($stmt);
        $jumlah['updateCustomer'] = $num;

        $stmt = mysqli_query($connMyResto, "SELECT fmodifieddt FROM rst_menu WHERE fmodifieddt > '$last_update_prod'");
        $num = mysqli_num_rows($stmt);
        $jumlah['updateProduct'] = $num;

        $stmt = mysqli_query($connMyResto, "SELECT fmodifieddt FROM bp_supplier WHERE fmodifieddt > '$last_update_supp' AND fpkey < 9999");
        $num = mysqli_num_rows($stmt);
        $jumlah['updateSupplier'] = $num;

        $stmt = mysqli_query($connMyResto, "SELECT fpkey FROM bp_customer WHERE fpkey > $last_key_cust");
        $num = mysqli_num_rows($stmt);
        $jumlah['customer'] = $num;

        $stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE status_sinkron = 'proses' AND tipe_tabel = 'bp_customer' AND type_con = '$type_con'");
        $num = mysqli_num_rows($stmt);
        $jumlah['customer'] += $num;

        $stmt = mysqli_query($connMyResto, "SELECT fpkey, fcode, fnotes, fprice FROM rst_menu_notes2 WHERE fpkey > '$last_key_mod'");
        $num = mysqli_num_rows($stmt);
        $jumlah['modifier'] = $num;

        $stmt = mysqli_query($connMyResto, "SELECT fpkey FROM rst_menu WHERE fpkey > $last_key_prod");
        $num = mysqli_num_rows($stmt);
        $jumlah['product'] = $num;

        $stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE status_sinkron = 'proses' AND tipe_tabel = 'rst_menu' AND type_con = '$type_con'");
        $num = mysqli_num_rows($stmt);
        $jumlah['product'] += $num;

        $stmt = mysqli_query($connMyResto, "SELECT fpkey FROM bp_supplier WHERE fpkey > $last_key_supp AND fpkey < 9999");
        $num = mysqli_num_rows($stmt);
        $jumlah['supplier'] = $num;

        $stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE status_sinkron = 'proses' AND tipe_tabel = 'bp_supplier' AND type_con = '$type_con'");
        $num = mysqli_num_rows($stmt);
        $jumlah['supplier'] += $num;

        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 = " AND fdate >= '$start'";
        else
            $filterStart = "";
        if (!empty($end))
            $filterEnd = " AND fdate <= '$end'";
        else
            $filterEnd = "";

        $stmt = mysqli_query($conn, "SELECT last_update FROM sync_to_jurnal WHERE tipe_tabel = 'rst_fc_trans_header' AND type_con = '$type_con' ORDER BY last_update DESC LIMIT 1");
        $row = mysqli_fetch_assoc($stmt);
        if ($row != NULL) {$last_key_tran = $row['last_update'];}else{$last_key_tran = '0000-00-00 00:00:00';}

        $stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE tipe_tabel = 'rst_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($conn, "SELECT id_record FROM sync_to_jurnal WHERE tipe_tabel = 'bp_customer_deposit' AND type_con = '$type_con' ORDER BY id_record DESC LIMIT 1");

        $row = mysqli_fetch_assoc($stmt);
        if ($row != NULL) {$last_key_depo = $row['id_record'];}else{$last_key_depo = 0;}
        // END - Proses mengambil id terakhir yang ada pada sync to jurnal

        // BEGIN - Proses mengambil jumlah data yanga akan disinkron
        $stmt = mysqli_query($connMyResto, "SELECT fpkey FROM rst_fc_trans_header WHERE concat(fco_date, ' ', fco_time) > '$last_key_tran'" . $filterStart . $filterEnd);
        $num = mysqli_num_rows($stmt);
        $jumlah['transaction'] = $num;

        $stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE (status_sinkron = 'failed' OR status_sinkron = 'proses') AND tipe_tabel = 'rst_fc_trans_header' AND type_con = '$type_con'");
        $num = mysqli_num_rows($stmt);
        $jumlah['transaction'] += $num;

        $stmt = mysqli_query($connMyResto, "SELECT fpkey FROM rst_fc_payment WHERE fpkey > $last_key_pay" . $filterStart . $filterEnd);
        $num = mysqli_num_rows($stmt);
        $jumlah['payment'] = $num;

        /*$stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE (status_sinkron = 'failed' OR status_sinkron = 'proses') AND tipe_tabel = 'rst_fc_payment' AND type_con = '$type_con'");
        $num = mysqli_num_rows($stmt);
        $jumlah['payment'] += $num;*/

        $stmt = mysqli_query($connMyResto, "SELECT fpkey FROM rst_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;

        $stmt = mysqli_query($conn, "SELECT id_record FROM sync_to_jurnal WHERE (status_sinkron = 'failed' OR status_sinkron = 'proses') AND tipe_tabel = 'sales_return' AND type_con = '$type_con'");
        $num = mysqli_num_rows($stmt);
        $jumlah['salesReturn'] += $num;

        $stmt = mysqli_query($connMyResto, "SELECT fpkey FROM bp_customer_deposit WHERE fpkey > $last_key_depo");
        $num = mysqli_num_rows($stmt);
        $jumlah['deposit'] = $num;

        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;
    }
}
