<?php

namespace App\Models;

use Datatables, DB;
use Exception;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Http\Request;
use Yajra\DataTables\DataTables as DT;
use Illuminate\Support\Facades\Http;
use App\Http\Controllers\Controller;

use Illuminate\Foundation\Auth\User as Authenticatable;

class rekaptrxModel extends Model
{
    protected $primaryKey = 'transaction_id';
    protected $table = 'transaction';
    public $timestamps = false;


    public function initData($request,$route)
    {
        // INIT DB
        $data['title'] = 'Rekap Transaksi';
        $data['actButton'] = [];
        $data['tableHead'] = 
        array(
            ["Jenis Transaksi","all","jenis_transaksi"],
            ["Berhasil","all","berhasil"],
            ["Gagal","all","gagal"],
            ["Pending","all","pending"],
            ["Batal","all","batal"],
            ["Total","all","total"],
            ["Persentase","all","prosentase_keberhasilan"],
            ["Act","all","action"],
        ); 
        
        $data['db'] = $this->table;
        $data['db_key'] = 'jenis_transaksi';
        $data['route'] = $route;

        $cGlobal = new Controller();

        $startDate = date('Y-m-d',strtotime($request->startDate)).' 00:00:00';
        $endDate = date('Y-m-d',strtotime($request->endDate)).' 24:00:00';

        $filterMerchant = '';
        
        if ($request->merchant != null) {
          if ($request->merchant == 'all') {
            $filterMerchant = '';
          }else{
            $filterMerchant = " and t.mid = '".$request->merchant."' ";
          }
        }

        $dtable = DB::select("SELECT 
        (case
          when s.service_group in ('antarbank', 'transfer') then 'trf'
          else s.service_group
        end) as jenis_transaksi,
        count(t.transaction_id) as total,
        count(case when t.transaction_status_id = 0 then 1 else null end) as berhasil,
        count(case when t.transaction_status_id = 1 then 1 else null end) as gagal,
        count(case when t.transaction_status_id = 2 then 1 else null end) as pending,
        count(case when t.transaction_status_id = 3 then 1 else null end) as batal,
        count(case when t.transaction_status_id = 0 then 1 else null end)* 100 / count(t.transaction_id) as prosentase_keberhasilan
      from
        transaction t
      join service s on
        t.service_id = s.service_id
      left join users u on
        t.user_uid = u.user_uid
      left join merchant m on
        m.mid = t.mid
      where t.transaction_time::date between :startDate and :endDate ".$filterMerchant."
      group by jenis_transaksi", ['startDate' => $startDate,'endDate' => $endDate]);
     
        // LIST DATA TABLE
        $data['data_table'] = $dtable;

        //  FORM FIELD FOR STORE
        $data['set_field'] = [
           
        ];

        foreach($data['tableHead'] as $v){
            $arrHead[] = $v[2];
        }
        $data['head'] = implode(",",$arrHead);

        return $data;
    }

    public function getDT($data,$init)
    {
        $dt = DT::of($data);

        $dt->editColumn('prosentase_keberhasilan', function ($data) {
          return number_format($data->prosentase_keberhasilan,2,",",".").'%';
        });
      
        //     $dt->editColumn('transaction_id',function($data) {
        //     return substr($data->transaction_id, 0, 18);
        // });

        return $dt;
    }

    public function storeCustom($act,Request $request)
    {
        DB::beginTransaction();
        try{

            $cGlobal = new Controller();
            $titleTrail = 'Status Transaksi';

            $startDate = date('Y-m-d',strtotime($request->startDate)).' 00:00:00';
            $endDate = date('Y-m-d',strtotime($request->endDate)).' 24:00:00';
    
            $filterMerchant = '';
            
            if ($request->merchant != null) {
              if ($request->merchant == 'all') {
                $filterMerchant = '';
              }else{
                $filterMerchant = " and t.mid = '".$request->merchant."' ";
              }
            }

            $data = null;

            switch ($act) {
                 case 'listDetail':

                  switch ($request->type) {
                    case 'trf':
                          $data = \DB::select("SELECT
                          s.service_group as product_group_id,
                          s.service_group as jenis_transaksi,
                          count(t.transaction_id) as jumlah_transaksi,
                          count(case when t.transaction_status_id = 0 then 1 else null end) as transaksi_sukses,
                          count(case when t.transaction_status_id = 1 then 1 else null end) as transaksi_gagal,
                          count(case when t.transaction_status_id = 2 then 1 else null end) as transaksi_pending,
                          count(case when t.transaction_status_id = 3 then 1 else null end) as transaksi_batal,
                          count(case when t.transaction_status_id = 0 then 1 else null end)* 100 / count(t.transaction_id) as prosentase_keberhasilan
                        from
                          transaction t
                        join service s on
                          t.service_id = s.service_id
                        left join users u on
                          t.user_uid = u.user_uid
                        left join merchant m on
                          m.mid = t.mid
                        where
                          t.transaction_time::date between :startDate and :endDate ".$filterMerchant."
                          and s.service_group in ('antarbank', 'transfer')
                        group by
                          jenis_transaksi",['startDate' => $startDate,'endDate' => $endDate]);
            
                      // ['branchCode' => $request->branchCode,'startDate' => $startDate,'endDate' => $endDate]
            
                    break;
                    case 'payment':
                      $data = \DB::select("SELECT
                      s.service_group as product_group_id,
                      s.service_group as jenis_transaksi,
                      count(t.transaction_id) as jumlah_transaksi,
                      count(case when t.transaction_status_id = 0 then 1 else null end) as transaksi_sukses,
                      count(case when t.transaction_status_id = 1 then 1 else null end) as transaksi_gagal,
                      count(case when t.transaction_status_id = 2 then 1 else null end) as transaksi_pending,
                      count(case when t.transaction_status_id = 3 then 1 else null end) as transaksi_batal,
                      count(case when t.transaction_status_id = 0 then 1 else null end)* 100 / count(t.transaction_id) as prosentase_keberhasilan
                    from
                      transaction t
                    join service s on
                      t.service_id = s.service_id
                    left join users u on
                      t.user_uid = u.user_uid
                    left join merchant m on
                      m.mid = t.mid
                    where
                      t.transaction_time::date between :startDate and :endDate ".$filterMerchant."
                      and s.service_group in ('payment')
                    group by
                      jenis_transaksi",['startDate' => $startDate,'endDate' => $endDate]);
            
             
                    break;
                    case 'purchase':
            
                      $data = \DB::select("SELECT
                      s.service_group as product_group_id,
                      s.service_group as jenis_transaksi,
                      count(t.transaction_id) as jumlah_transaksi,
                      count(case when t.transaction_status_id = 0 then 1 else null end) as transaksi_sukses,
                      count(case when t.transaction_status_id = 1 then 1 else null end) as transaksi_gagal,
                      count(case when t.transaction_status_id = 2 then 1 else null end) as transaksi_pending,
                      count(case when t.transaction_status_id = 3 then 1 else null end) as transaksi_batal,
                      count(case when t.transaction_status_id = 0 then 1 else null end)* 100 / count(t.transaction_id) as prosentase_keberhasilan
                    from
                      transaction t
                    join service s on
                      t.service_id = s.service_id
                    left join users u on
                      t.user_uid = u.user_uid
                    left join merchant m on
                      m.mid = t.mid
                    where
                      t.transaction_time::date between :startDate and :endDate ".$filterMerchant."
                      and s.service_group in ('purchase')
                    group by
                      jenis_transaksi",['startDate' => $startDate,'endDate' => $endDate]);
            
                      // ['branchCode' => $request->branchCode,'startDate' => $startDate,'endDate' => $endDate]
                      
                    break;

                    case 'sale':
                      $data = \DB::select("SELECT
                      s.service_group as product_group_id,
                      s.service_group as jenis_transaksi,
                      count(t.transaction_id) as jumlah_transaksi,
                      count(case when t.transaction_status_id = 0 then 1 else null end) as transaksi_sukses,
                      count(case when t.transaction_status_id = 1 then 1 else null end) as transaksi_gagal,
                      count(case when t.transaction_status_id = 2 then 1 else null end) as transaksi_pending,
                      count(case when t.transaction_status_id = 3 then 1 else null end) as transaksi_batal,
                      count(case when t.transaction_status_id = 0 then 1 else null end)* 100 / count(t.transaction_id) as prosentase_keberhasilan
                    from
                      transaction t
                    join service s on
                      t.service_id = s.service_id
                    left join users u on
                      t.user_uid = u.user_uid
                    left join merchant m on
                      m.mid = t.mid
                    where
                      t.transaction_time::date between :startDate and :endDate ".$filterMerchant."
                      and s.service_group in ('sale')
                    group by
                      jenis_transaksi",['startDate' => $startDate,'endDate' => $endDate]);

                    break;
                    case 'market':
                      $data = \DB::select("SELECT
                        s.service_group as product_group_id,
                        s.service_group as jenis_transaksi,
                        count(t.transaction_id) as jumlah_transaksi,
                        count(case when t.transaction_status_id = 0 then 1 else null end) as transaksi_sukses,
                        count(case when t.transaction_status_id = 1 then 1 else null end) as transaksi_gagal,
                        count(case when t.transaction_status_id = 2 then 1 else null end) as transaksi_pending,
                        count(case when t.transaction_status_id = 3 then 1 else null end) as transaksi_batal,
                        count(case when t.transaction_status_id = 0 then 1 else null end)* 100 / count(t.transaction_id) as prosentase_keberhasilan
                      from
                        transaction t
                      join service s on
                        t.service_id = s.service_id
                      left join users u on
                        t.user_uid = u.user_uid
                      left join merchant m on
                        m.mid = t.mid
                      where
                        t.transaction_time::date between :startDate and :endDate ".$filterMerchant."
                        and s.service_group in ('market')
                      group by
                        jenis_transaksi",['startDate' => $startDate,'endDate' => $endDate]);
                    break;
                    case 'parkir':
                      $data = \DB::select("SELECT
                      s.service_group as product_group_id,
                      s.service_group as jenis_transaksi,
                      count(t.transaction_id) as jumlah_transaksi,
                      count(case when t.transaction_status_id = 0 then 1 else null end) as transaksi_sukses,
                      count(case when t.transaction_status_id = 1 then 1 else null end) as transaksi_gagal,
                      count(case when t.transaction_status_id = 2 then 1 else null end) as transaksi_pending,
                      count(case when t.transaction_status_id = 3 then 1 else null end) as transaksi_batal,
                      count(case when t.transaction_status_id = 0 then 1 else null end)* 100 / count(t.transaction_id) as prosentase_keberhasilan
                    from
                      transaction t
                    join service s on
                      t.service_id = s.service_id
                    left join users u on
                      t.user_uid = u.user_uid
                    left join merchant m on
                      m.mid = t.mid
                    where
                      t.transaction_time::date between :startDate and :endDate ".$filterMerchant."
                      and s.service_group in ('parkir')
                    group by
                      jenis_transaksi",['startDate' => $startDate,'endDate' => $endDate]);
                    break;
                      
                  }
                    return response()->json([
                        'rc' => 0,
                        'rm' => "sukses",
                        'data' => $data
                    ]);
                break;

                case 'listDetailGagal':
                  switch ($request->type) {
                    case 'trf':
                          $data = \DB::select("SELECT
                          e.response_uid ,
                          (case
                            when r.msg_id is null then (
                            select
                              r1.msg
                            from
                              responsecode r1
                            where
                              r1.service_id = '0'
                              and r1.response_uid = e.response_uid
                              and lang = 'in')
                            else r.msg
                          end ) as penyebab_gagal,
                          count(t.transaction_id) as jumlah,
                          100 * count(t.transaction_id) / sum(count(t.transaction_id)) over () as pct
                        from
                          transfer_transaction t
                        join service s on
                          t.service_id = s.service_id
                        left join event e on
                          t.event_uid = e.event_uid
                        left join responsecode r on
                          t.service_id = r.service_id
                          and e.response_uid = r.response_uid
                          and r.lang = 'in'
                        where
                          e.response_uid <> '00'
                          and s.service_group = :service_group and t.transaction_time::date between :startDate and :endDate
                        group by
                          e.response_uid ,
                          penyebab_gagal
                        order by
                          jumlah desc",['service_group' => $request->service_group,'startDate' => $startDate,'endDate' => $endDate]);
            
                      // ['service_group' => $request->service_group,'startDate' => $startDate,'endDate' => $endDate]
            
                    break;
                    case 'payment':
                      $data = \DB::select("SELECT
                      e.response_uid ,
                      (case
                        when r.msg_id is null then (
                        select
                          r1.msg
                        from
                          responsecode r1
                        where
                          r1.service_id = '0'
                          and r1.response_uid = e.response_uid
                          and lang = 'in')
                        else r.msg
                      end ) as penyebab_gagal,
                      count(t.transaction_id) as jumlah,
                      100 * count(t.transaction_id) / sum(count(t.transaction_id)) over () as pct
                      from
                        payment_pln_transaction t
                      left join event e on
                        t.event_uid = e.event_uid
                      left join responsecode r on
                        t.service_id = r.service_id
                        and e.response_uid = r.response_uid
                        and r.lang = 'in'
                      where
                        e.response_uid <> '00'
                        and t.product_group_id = :service_group and t.transaction_time::date between :startDate and :endDate
                      group by
                        e.response_uid ,
                        penyebab_gagal
                      order by
                        jumlah desc",['service_group' => $request->service_group,'startDate' => $startDate,'endDate' => $endDate]);
            
                          // ['service_group' => $request->service_group,'startDate' => $startDate,'endDate' => $endDate]
            
                    break;
                    case 'purchase':
            
                      $data = \DB::select("SELECT
                            e.response_uid ,
                            (case
                              when r.msg_id is null then (
                              select
                                r1.msg
                              from
                                responsecode r1
                              where
                                r1.service_id = '0'
                                and r1.response_uid = e.response_uid
                                and lang = 'in')
                              else r.msg
                            end ) as penyebab_gagal,
                            count(t.transaction_id) as jumlah,
                            100 * count(t.transaction_id) / sum(count(t.transaction_id)) over () as pct
                          from
                            purchase_transaction t
                          left join event e on
                            t.event_uid = e.event_uid
                          left join responsecode r on
                            t.service_id = r.service_id
                            and e.response_uid = r.response_uid
                            and r.lang = 'in'
                          where
                            e.response_uid <> '00'
                            and t.product_group_id = :service_group and t.transaction_time::date between :startDate and :endDate
                          group by
                            e.response_uid ,
                            penyebab_gagal
                          order by
                            jumlah desc",['service_group' => $request->service_group,'startDate' => $startDate,'endDate' => $endDate]);
            
                      // ['service_group' => $request->service_group,'startDate' => $startDate,'endDate' => $endDate]
                      
                    break;
                      
                  }

                  return response()->json([
                    'rc' => 0,
                    'rm' => "sukses",
                    'data' => $data
                ]);
            
                break;
            }

        DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => "sukses"
            ]);
        }
        catch (QueryException $e){

            if($e->getCode() == '23505'){
                $response = "Terjadi Duplikasi Data, Data Gagal Disimpan !";
            }else{
                $response = "Terjadi Kesalahan, Data Tidak Sesuai !";
            }

            DB::rollback();

            return response()->json([
                'rc' => 99,
                'rm' => $response,
                // 'msg' => $e->getMessage()
            ]);
        }
    }

}