pos-gis/app/Models/laporanmarketModel.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 laporanmarketModel extends Model
{
    protected $primaryKey = 'transaction_id';
    protected $table = 'pasar_transaction';
    public $timestamps = false;


    public function initData($request,$route)
    {
        // INIT DB
        $data['title'] = 'Laporan Market';
        $data['actButton'] = ['edit'];
        $data['tableHead'] = 
        array(
            ["Transaction Time","all","transaction_time"],
            ["Company ID","all","mid"],
            ["Company Name","all","merchant_nm"],
            ["Toko","all","toko_nm"],
            ["Toko Code","all","toko_kd"],
            ["Keterangan","all","keterangan"],
            ["Amount","all","amount"],
            ["No Resi","all","no_resi"],
            ["Status","all","transaction_status_desc"]
        ); 
        
        $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 pt.mid = '".$request->merchant."' ";
          }
        }

        $dtable = \DB::select("SELECT
         transaction_id,
        transaction_time,
        pt.mid,
        m.merchant_nm ,
        pt.toko_id,
        t.toko_nm ,
        t.toko_kd ,
        rtt.keterangan ,
        amount ,
        no_resi ,
        pt.transaction_status_id,
        rts.transaction_status_desc,
        pt.berita
      from
        pasar_transaction pt
      left join merchant m on
        m.mid = pt.mid
      left join reff_transaction_status rts on
        pt.transaction_status_id = rts.transaction_status_id
      left join toko t on
        pt.toko_id = t.toko_id
      left join merchant_toko_type mtt on
        mtt.merchant_ttid = t.merchant_ttid left join reff_toko_type rtt on mtt.ttid = mtt.ttid
        where transaction_time >= ? and transaction_time <= ? ".$filterMerchant,[$startDate,$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_time',function($data) {
            return date('d-m-Y H:i:s',strtotime($data->transaction_time));
        });
        $dt->editColumn('transaction_id',function($data) {
            return substr($data->transaction_id, 0, 18);
        });

        $dt->editColumn('amount',function($data) {
            return number_format($data->amount,0,",",".");
        });

        return $dt;
    }

}