pos-gis/app/Models/statustrxModel.php

<?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 statustrxModel extends Model
{
    protected $primaryKey = 'response_uid';
    protected $table = 'event';
    public $timestamps = false;


    public function initData($request,$route)
    {
        // INIT DB
        $data['title'] = 'Laporan Status Transaksi';
        $data['actButton'] = [];
        $data['tableHead'] = 
        array(
            ["ID","all","id"],
            ["Message","all","msg"],
            ["Frekuensi","all","frek"],
            ["Presentase","all","presentase"],
            ["Act","all","action"],
        ); 
        
        $data['db'] = $this->table;
        $data['db_key'] = $this->primaryKey;
        $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 event.response_uid,event.response_uid as id, responsecode.msg as msg,
        count(event.response_uid) as frek, concat(
        round((round(count(event.response_uid),2) / (SELECT round(count(response_uid),2) as zz from event))*100,2) ,' %' ) as presentase
        from event
        join responsecode on responsecode.response_uid = event.response_uid
        where responsecode.service_id = '0' and responsecode.lang in ('id','in') 
        and event.crtdt::date between :startDate and :endDate 
        GROUP BY event.response_uid,msg
        order by event.response_uid,presentase
       ", ['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('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';

            switch ($act) {
                case 'listDetail':

                    $data = \DB::select("SELECT event.service_id as id,service.service_name as msg,
                    count(event.service_id) as frek,
                    concat(round((round(count(event.service_id),2) / (SELECT round(count(service_id),2) as zz from event where event.response_uid = :id and event.crtdt::date between :startDate and :endDate ))*100,2) ,' %' ) as presentase
                    from event
                    join service on service.service_id = event.service_id
                    where event.response_uid = :id and event.crtdt::date between :startDate and :endDate
                    GROUP BY event.service_id,service_name
                    order by event.service_id,presentase
                    ",['id' => $request->id,'startDate' => $startDate,'endDate' => $endDate]);
                      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()
            ]);
        }
    }

}