Untitled
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; } }