<?php

namespace myretail\models;

use Yii;
use myretail\models\SyncConfig;
use myretail\models\ImportProduct;
use myretail\models\ImportCustomer;
use myretail\models\ImportSupplier;
use myretail\models\ImportWarehouse;

/**
 * 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'=> 'retail'],
        ];
    }

    /**
     * {@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');
        $connMyRetail = SyncConfig::getConnection('MyRetail');
        $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 id_record FROM sync_to_jurnal WHERE tipe_tabel = 'inv_warehouse' AND type_con = '$type_con' ORDER BY id_record DESC LIMIT 1");
        $row = mysqli_fetch_assoc($stmt);
        if ($row != NULL) {$last_key_ware = $row['id_record'];}else{$last_key_ware = 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 = 'inv_item' 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 last_update FROM sync_to_jurnal WHERE tipe_tabel = 'inv_item' 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';}

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

        // BEGIN - Proses mengambil jumlah data yang akan di sinkron
        $stmt = mysqli_query($connMyRetail, "SELECT fpkey FROM bp_customer WHERE fpkey > $last_key_cust");
        $num = mysqli_num_rows($stmt);
        $jumlah['customer'] = $num;

        $stmt = mysqli_query($connMyRetail, "SELECT fmodifieddt FROM bp_customer WHERE fmodifieddt > '$last_update_cust'");
        $num = mysqli_num_rows($stmt);
        $jumlah['updateCustomer'] = $num;

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

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

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

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

        $stmt = mysqli_query($connMyRetail, "SELECT fpkey FROM inv_warehouse WHERE fpkey > $last_key_ware");
        $num = mysqli_num_rows($stmt);
        $jumlah['warehouse'] = $num;

        $stmt = mysqli_query($connMyRetail, "SELECT fmodifieddt FROM inv_warehouse WHERE fmodifieddt > '$last_update_ware'");
        $num = mysqli_num_rows($stmt);
        $jumlah['updateWarehouse'] = $num;

        return $jumlah;
    }

    public static function getTotalSyncDataTransaction($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;}

        // BEGIN - Proses mengambil jumlah data yanga akan disinkron
        $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 static function anyFailed() {
        $count = self::find()
            ->where(['status_sinkron' => 'failed'])
            ->andWhere(['type_con' => 'retail'])
            ->andWhere(['in', 'tipe_tabel', [
                ImportProduct::$tipe_tabel,
                ImportCustomer::$tipe_tabel,
                ImportSupplier::$tipe_tabel,
                ImportWarehouse::$tipe_tabel
            ]])
            ->count();

        return $count;
    }

    public static function canSalesSync() {
        $total = self::getTotalSyncDataMaster();
        $anyFailed = self::anyFailed();

        if (
            $total['product'] ||
            $total['customer'] ||
            $total['supplier'] ||
            $total['warehouse'] ||
            $total['updateProduct'] ||
            $total['updateCustomer'] ||
            $total['updateSupplier'] ||
            $total['updateWarehouse'] ||
            $anyFailed
        )
            return false;
        else
            return true;
    }
}
