Untitled

mail@pastecode.io avatar
unknown
plain_text
7 months ago
13 kB
0
Indexable
Never
<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Models\QCModel;
use App\Collections\QCModelCollection;
use Illuminate\Support\Facades\DB;

class QCModelRepository
{
    public function getDataQCMerchant($filter)
    {
        $subQuery = DB::table('merchant_data')->select('mid');

        $query = DB::table('app_merchant as am')
            ->select(
                DB::raw('am.mid'),
                DB::raw('aps.status as status'),
                DB::raw('aps.nama_merchant'),
                DB::raw('am.jenis_merchant'),
                DB::raw('am.group_merchant'),
                DB::raw('am.tipe_merchant'),
                DB::raw('am.alamat_merchant as alamat_merchant'),
                DB::raw('am.kelurahan_merchant'),
                DB::raw('am.kecamatan_merchant'),
                DB::raw('am.kota_perusahaan'),
                DB::raw('am.provinsi_merchant'),
                DB::raw('am.kodepos_merchant'),
                DB::raw('am.channel'),
                DB::raw('am.sub_channel')
            )
            ->join('app_status as aps', 'aps.app_id', '=', 'am.app_id')
            ->whereNotNull('am.mid')
            ->whereIn('aps.app_jenis', ['1', '2', '3'])
            ->whereNotIn('am.mid', $subQuery)
            ->where('aps.status', '=', 30);

        $query = $query->when($filter['mid'], function ($query) use ($filter) {
            return $query->where('am.mid', '=', $filter['mid']);
        });

        $query = $query->when($filter['nama-merchant'], function ($query) use ($filter) {
            return $query->where('am.nama_merchant', 'like', $filter['nama-merchant'] . '%');
        });

        $query = $query->groupBy('am.mid');
        $query->orderBy('am.mid', 'desc');

        $dataMerchant = $query->paginate();

        return $dataMerchant;
    }

    public function getDetailQCMerchant($filter)
    {

        $dataMerchant = DB::table('app_merchant as am')
            ->select(
                DB::raw('am.app_id'),
                DB::raw('am.mid'),
                DB::raw('aps.nama_merchant'),
                DB::raw('am.dba'),
                DB::raw('alamat_merchant'),
                DB::raw('kodepos_merchant'),
                DB::raw('kelurahan_merchant'),
                DB::raw('kecamatan_merchant'),
                DB::raw('kota_merchant'),
                DB::raw('provinsi_merchant'),
                DB::raw('pic_merchant'),
                DB::raw('tlp_merchant_1'),
                DB::raw('tlp_merchant_2'),
                DB::raw('group_merchant'),
                DB::raw('program_merchant'),
                DB::raw('no_rekening'),
                DB::raw('pemilik_rekening'),
                DB::raw('tipe_merchant'),
                DB::raw('program_merchant'),
                DB::raw('mcc_visa'),
                DB::raw('mcc_master'),
                DB::raw('mcc_npg'),
                DB::raw('mcc_unik'),
                DB::raw('mcc_ewallet'),
                DB::raw('salesvol_per_trans'),
                DB::raw('jam_buka'),
                DB::raw('jam_tutup'),
                DB::raw('program_merchant'),
                DB::raw('program_merchant'),
                DB::raw('aps.status as status'),
                DB::raw('am.jenis_merchant'),
                DB::raw('am.group_merchant'),
                DB::raw('am.tipe_merchant'),
                DB::raw('am.alamat_merchant as alamat_merchant'),
                DB::raw('am.kelurahan_merchant'),
                DB::raw('am.kecamatan_merchant'),
                DB::raw('am.kota_perusahaan'),
                DB::raw('am.provinsi_merchant'),
                DB::raw('am.kodepos_merchant'),
                DB::raw('am.channel'),
                DB::raw('am.sub_channel'),
                DB::raw('aps.app_jenis')
            )
            ->leftJoin('app_status as aps', 'aps.app_id', '=', 'am.app_id')
            // ->whereNotNull('am.mid')
            // ->whereIn('aps.app_jenis', ['1', '2', '3'])
            // ->where('aps.status', '=', 30)
            ->where('am.mid', '=', $filter['mid'])
            ->first();

        $mdr = DB::table("app_merchant_mdr")->where('app_id', $dataMerchant->app_id)->get();

        // GET EXISTING MDR

        $mdr_cicilan = [];
        $mdr_on_tr = '';
        $mdr_off_tr = '';
        $mdr_debit_mc = '';
        $mdr_debit_pl = '';
        $mdr_debit_npg = '';
        $mdr_unik = '';
        if (isset($mdr)) {
            foreach ($mdr->all() as $dr) {
                if ($dr->plan == 0) {
                    if ($dr->on_off == 1) {
                        $mdr_on_tr .= $dr->mdr;
                    } elseif ($dr->on_off == 0) {
                        $mdr_off_tr .= $dr->mdr;
                    } elseif ($dr->on_off == 2) {
                        $mdr_debit_mc .= $dr->mdr;
                    } elseif ($dr->on_off == 3) {
                        $mdr_debit_pl .= $dr->mdr;
                    } elseif ($dr->on_off == 4) {
                        $mdr_debit_npg .= $dr->mdr;
                    } elseif ($dr->on_off == 5) {
                        $mdr_unik .= $dr->mdr;
                    }
                } else {
                    $new_mdr_plan = [
                        'mdr_term' => $dr->term,
                        'mdr_plan' => $dr->plan,
                        'mdr' => $dr->mdr
                    ];

                    array_push($mdr_cicilan, $new_mdr_plan);
                }
            }
        }

        $dataMerchant->mdr_onus            = $mdr_on_tr;
        $dataMerchant->mdr_offus            = $mdr_off_tr;
        $dataMerchant->mdr_debit            = $mdr_debit_mc;
        $dataMerchant->mdr_debit_pl        = $mdr_debit_pl;
        $dataMerchant->mdr_debit_npg        = $mdr_debit_npg;
        $dataMerchant->mdr_unik            = $mdr_unik;
        $dataMerchant->mdr_plan            = $mdr_cicilan;

        return $dataMerchant;
    }

    public function getMerchant($app_id)
    {
        $dataMerchant = DB::table('app_merchant as am')
            ->select(
                DB::raw('app_id'),
                DB::raw('mid'),
                DB::raw('nama_merchant'),
                DB::raw('dba'),
                DB::raw('alamat_merchant'),
                DB::raw('kodepos_merchant as kodepos'),
                DB::raw('kelurahan_merchant as kelurahan'),
                DB::raw('kecamatan_merchant as kecamatan'),
                DB::raw('kota_merchant as kabupaten'),
                DB::raw('provinsi_merchant as provinsi'),
                DB::raw('pic_merchant as pic'),
                DB::raw('tlp_merchant_1'),
                DB::raw('tlp_merchant_2'),
                DB::raw('jenis_merchant'),
                DB::raw('group_merchant'),
                DB::raw('program_merchant'),
                DB::raw('no_rekening as no_rek'),
                DB::raw('pemilik_rekening as pemilik_rek'),
                DB::raw('tipe_merchant as merchant_tipe'),
                DB::raw('mcc_visa'),
                DB::raw('mcc_master'),
                DB::raw('mcc_npg'),
                DB::raw('mcc_unik'),
                DB::raw('salesvol_per_trans as sales_volume'),
                DB::raw('jam_buka'),
                DB::raw('jam_tutup')
            )
            ->where('app_id', '=', $app_id)
            ->first();

        return $dataMerchant;
    }

    public function getStatusInboxLevel($user_level, $param_mcc, $action)
    {
        $datainbox = DB::table('map_mcs')->where('modul', '=', 'QUALITYCONTROL')
            ->where('user_level_action', '=', $user_level)
            ->where('param', '=', $param_mcc)
            ->where('action', '=', $action)
            ->first();

        return $datainbox;
    }

    public function getMdr($app_id)
    {
        $data_mdr = DB::table('app_merchant_mdr')->select('on_off', 'term as mdr_term', 'plan as mdr_plan', 'mdr')->where('app_id', '=', $app_id)->orderBy('id', 'ASC')->get();

        return $data_mdr->all();
    }

    public function Insert($data)
    {
        $success = false;
        DB::transaction(function () use ($data, &$success) {
            $success = DB::table('qc_data')->insertGetId($data);
        });

        return $success;
    }

    public function getDataQc($id_qc)
    {
        $query = DB::table('qc_data')
            ->where('id_qc', $id_qc);

        $result = $query->first();

        return $result;
    }

    function insert_data($table_name, $data_ins)
    {
        $query = DB::table($table_name)
            ->insert($data_ins);
        return $query;
    }

    function select_data($table_name, $wheres = array(), $select_str = '', $order_by = '', $group_by = '')
    {
        $query = DB::table($table_name);
        if (!empty($select_str)) {
            $query->select($select_str);
        }

        if (count($wheres) > 0) {
            $query->where($wheres);
        }

        if (!empty($order_by))
            $query->order_by($order_by);

        if (!empty($group_by))
            $query->group_by($group_by);

        $data =  $query->get()->toArray();
        return $data;
    }

    function update_data($table_name, $data_upd, $wheres)
    {
        $query = DB::table($table_name)
            ->where(function ($sub_query) use ($wheres) {
                foreach ($wheres as $key => $value) {
                    if (!empty($value)) {
                        $sub_query->Where($key, $value);
                    };
                };
            })
            ->update($data_upd);

        return $query;
    }

    function get_map_merchant_inisiasi($field_ubah)
    {
        $query = DB::table('merchant_data_param')
            ->whereIn('app_merchant_field', $field_ubah)
            ->where('inisiasi_field', '<>', null);

        $list_param = $query->get()->toArray();
        return $list_param;
    }

    function insert_qc_log($data)
    {
        $query = DB::table('qc_log')
            ->insert($data);
        return $query;
    }

    function delete_data_mdr($wheres)
    {
        $query = DB::table('app_merchant_mdr')
            ->where(function ($sub_query) use ($wheres) {
                foreach ($wheres as $key => $value) {
                    if (!empty($value)) {
                        $sub_query->Where($key, $value);
                    };
                };
            })
            ->where('plan', '>', '0')
            ->delete();
        return $query;
    }

    function delete_qc_data($id_qc)
    {
        $query = DB::table('qc_data')->where('id_qc', '=', $id_qc)->delete();

        return $query;
    }

    function delete_qc_log($id_qc)
    {
        $query = DB::table('qc_log')->where('id_qc', '=', $id_qc)->delete();

        return $query;
    }

    function input_mdr_cicilan($mdr_cicilan)
    {
        $query = DB::table('qc_mdr_cicilan_ubah')->insert($mdr_cicilan);
        return $query;
    }

    function get_qc_detail($data)
    {
        $result = DB::table('qc_interface')
            ->select('qc_interface.*', 'qc_data.data_ubah', 'qc_data.is_mdr_cicilan_ubah')
            ->join('qc_data', 'qc_data.id_qc', '=', 'qc_interface.id_qc')
            ->when($data['id_qc'], function ($query, $id_qc) {
                return $query->where('qc_data.id_qc', $id_qc);
            })
            ->when($data['app_jenis'], function ($query, $app_jenis) {
                return $query->where('qc_data.app_jenis', $app_jenis);
            })
            ->when($data['app_id'], function ($query, $app_id) {
                return $query->where('qc_data.app_id', $app_id);
            })
            ->when($data['mid'], function ($query, $mid) {
                return $query->where('qc_data.mid', $mid);
            })
            ->limit(1)
            ->get();


        return $result;
    }

    function interface_mdr($data_upd, $mdr_app_id)
    {
        $cl = DB::connection('cl_sqlsrv');
        $count = $cl->table('APP_MERCHANT_MDR')->where('MDR_APP_ID', $mdr_app_id)->count();
        if ($count > 0) {
            $cl->table('APP_MERCHANT_MDR')->where('MDR_APP_ID', $mdr_app_id)->update($data_upd);
        } else {
            $data_ins = $data_upd;
            $data_ins['MDR_APP_ID'] = $mdr_app_id;
            $data_ins['MDR_DATE_CREATED'] = $data_ins['MDR_DATE_MODIFIED'];
            $data_ins['MDR_CREATED_BY'] = $data_ins['MDR_MODIFIED_BY'];
            $cl->table('APP_MERCHANT_MDR')->insert($data_ins);
        }

        $result = $cl->table('APP_MERCHANT_MDR')->where('MDR_APP_ID', $mdr_app_id)->count();
        return $result;
    }
}