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