/**
* Execute the job.
*
* @return void
*/
public function handle()
{
//Log::info("In Handle ".$this->tanggal);
$tanggal = $this->tanggal;
$reports = Order::ReportByDate($tanggal);
$reports = collect($reports)->groupBy('_type_bayar');
$totalCash = isset($reports['tunai']) ? collect($reports['tunai'])->sum('jumlah') : 0;
$mergeDebitCcard = [];
$mergeDebitCcard = isset($reports['debit']) ? array_merge($mergeDebitCcard, $reports['debit']->toArray()) : array_merge($mergeDebitCcard, []);
$mergeDebitCcard = isset($reports['credit_card']) ? array_merge($mergeDebitCcard, $reports['credit_card']->toArray()) : array_merge($mergeDebitCcard, []);
$bayarBank = collect($mergeDebitCcard)->groupBy('_bank_id');
$accountSaldo = AccountSaldo::join('accounts', 'account_saldos.account_id', '=', 'accounts.id')->leftJoin('banks', 'account_saldos.relation_id', '=', 'banks.id')->where('tanggal', $tanggal)->where('account_id', 2)->select(['account_saldos.*', DB::raw('IFNULL(account_saldos.relation_id, "cash")_relation_id')])->get()->groupBy("_relation_id");
$totals = [];
if ($totalCash > 0) {
$totals['cash'] = $totalCash;
}
foreach ($bayarBank as $key => $val) {
$totals[$key] = $val->sum('jumlah') - $val->sum('pajak_pembayaran');
}
$actions = [];
$totalKeys = array_keys($totals);
foreach ($totalKeys as $bank_id) {
$nominal = $totals[$bank_id];
if (!isset($accountSaldo[$bank_id])) {
// create new
$inputs = ['tanggal' => $tanggal, 'account_id' => 2, 'nominal' => $nominal];
if ($bank_id != 'cash') {
$inputs += ['type' => 'kredit', 'relation_id' => $bank_id];
} else {
$inputs += ['type' => 'debet'];
}
AccountSaldo::create($inputs);
//$actions[$bank_id] = $inputs;
} else {
// update value
$row = $accountSaldo[$bank_id][0];
$row = AccountSaldo::find($row['id']);
$row->update(['nominal' => $nominal]);
//$actions[$bank_id] = $row;
}
}
//return $actions;
//Log::info('Queue End @'.Carbon::now('Asia/Jakarta'));
}
protected function _labaRugiPertahun(Request $request)
{
$tahun = $request->get('tahun') ? $request->get('tahun') : date('Y');
$penjualans = Order::ReportGroup("SUBSTRING(orders.`tanggal`, 1, 4) = '{$tahun}'", "GROUP BY SUBSTRING(tanggal, 1, 4)");
$penjualans = ConvertRawQueryToArray($penjualans);
$accountSaldo = \App\AccountSaldo::join('accounts', 'account_saldos.account_id', '=', 'accounts.id')->leftJoin(DB::raw("(SELECT accounts.`id` AS account_id, accounts.`nama_akun`, reports.display\n FROM accounts\n INNER JOIN account_report ON accounts.`id` = account_report.`account_id`\n INNER JOIN reports ON account_report.`report_id` = reports.id\n WHERE reports.key = 'labarugi')temp_report"), function ($join) {
$join->on('accounts.id', '=', 'temp_report.account_id');
})->where(DB::raw('SUBSTRING(account_saldos.tanggal, 1, 4)'), $tahun)->whereNull('account_saldos.relation_id')->whereNotNull('temp_report.account_id')->groupBy('accounts.id')->select(['accounts.nama_akun', DB::raw('SUM(account_saldos.nominal)total'), 'account_saldos.type'])->get()->groupBy('type');
$tableTemp = $this->buildLabaRugiTable(['penjualans' => $penjualans, 'account_saldo' => $accountSaldo]);
return ['tanggal' => Carbon::createFromFormat('Y', $tahun), 'tableTemp' => $tableTemp];
}
protected function _jurnal(Request $request)
{
$type = $request->get('type') ? $request->get('type') : 'cash';
$tanggal = $request->get('tanggal') ? $request->get('tanggal') : date('Y-m-d');
$CTanggal = Carbon::createFromFormat('Y-m-d', $tanggal);
$to_tanggal = $request->get('to_tanggal') ? $request->get('to_tanggal') : $tanggal;
$CToTanggal = Carbon::createFromFormat('Y-m-d', $to_tanggal);
$CYesterday = $CTanggal->copy()->addDays(-1);
$yesterday = $CYesterday->format('Y-m-d');
$start = $CTanggal->copy();
$end = $CToTanggal->copy();
$dates = [];
while ($start->lte($end)) {
$dates[] = $start->copy();
$start->addDay();
}
if ($type == 'cash') {
// Account Saldo for sisa saldo
$totalAccountSaldo = 0;
$firstAccountSaldo = AccountSaldo::orderBy('tanggal')->limit(1)->first();
if ($firstAccountSaldo) {
if ($firstAccountSaldo->tanggal->lte($CYesterday)) {
$firstDate = $firstAccountSaldo->tanggal->format('Y-m-d');
$where = "(account_saldos.`tanggal` BETWEEN '{$firstDate}' AND '{$yesterday}')\n AND account_saldos.account_id != '2' OR ( account_saldos.account_id = '2'\n AND account_saldos.`type` = 'debet' AND (account_saldos.`tanggal`\n BETWEEN '{$firstDate}' AND '{$yesterday}') ) AND";
$column = "IF(account_saldos.`type` = 'debet', account_saldos.`nominal`, -ABS(account_saldos.`nominal`))";
$totalAccountSaldo = ConvertRawQueryToArray(Account::TotalAccountSaldo($column, $where, 'jurnal'))[0]['total'];
}
}
$tableTemp = [];
// Sisa Saldo Pertanggal $tanggal (-1)
$sisaSaldo = $totalAccountSaldo;
$saldo = $sisaSaldo;
array_push($tableTemp, ['tanggal' => $CYesterday->format('Y-m-d'), 'keterangan' => 'Sisa Saldo ' . $CYesterday->format('d M Y'), 'debet' => '', 'kredit' => '', 'saldo' => $sisaSaldo]);
// Account Saldo range $tanggal s/d $to_tanggal
$accountSaldos = AccountSaldo::with(['bank'])->join('accounts', 'account_saldos.account_id', '=', 'accounts.id')->leftJoin(DB::raw("(SELECT accounts.`id` AS account_id, accounts.`nama_akun`, reports.display\n FROM accounts\n INNER JOIN account_report ON accounts.`id` = account_report.`account_id`\n INNER JOIN reports ON account_report.`report_id` = reports.id\n WHERE reports.key = 'jurnal')temp_report"), function ($join) {
$join->on('accounts.id', '=', 'temp_report.account_id');
})->whereBetween('tanggal', [$tanggal, $to_tanggal])->whereNotNull('temp_report.account_id')->where('account_saldos.account_id', '!=', 2)->orWhere(function ($query) use($tanggal, $to_tanggal) {
$query->where('account_saldos.account_id', 2)->where('account_saldos.type', 'debet')->whereBetween('tanggal', [$tanggal, $to_tanggal]);
})->select(['account_saldos.*', DB::raw('accounts.nama_akun as nama_akun'), DB::raw('tanggal as _date')])->get()->groupBy('_date');
foreach ($dates as $date) {
// Account Saldo
if (isset($accountSaldos[$date->format('Y-m-d')])) {
$acs = $accountSaldos[$date->format('Y-m-d')];
foreach ($acs as $as) {
$bankName = $as['bank'] != null ? $as['bank']['nama_bank'] : '';
if ($as['type'] == 'kredit') {
$saldo -= $as['nominal'];
array_push($tableTemp, ['tanggal' => $date->format('Y-m-d'), 'keterangan' => $as['nama_akun'] . ' ' . $bankName, 'debet' => '', 'kredit' => $as['nominal'], 'saldo' => $saldo]);
} else {
// debet
$saldo += $as['nominal'];
array_push($tableTemp, ['tanggal' => $date->format('Y-m-d'), 'keterangan' => $as['nama_akun'] . ' ' . $bankName, 'debet' => $as['nominal'], 'kredit' => '', 'saldo' => $saldo]);
}
}
}
}
$tableTemp = collect($tableTemp)->groupBy('tanggal');
} else {
// Account Saldo for sisa saldo
$totalAccountSaldo = 0;
$firstAccountSaldo = AccountSaldo::orderBy('tanggal')->limit(1)->first();
if ($firstAccountSaldo) {
if ($firstAccountSaldo->tanggal->lte($CYesterday)) {
$firstDate = $firstAccountSaldo->tanggal->format('Y-m-d');
$where = "(account_saldos.`tanggal` BETWEEN '{$firstDate}' AND '{$yesterday}') and relation_id is not null AND";
$column = "IF(account_saldos.`type` = 'kredit', account_saldos.`nominal`, -ABS(account_saldos.`nominal`))";
$totalAccountSaldo = ConvertRawQueryToArray(Account::TotalAccountSaldo($column, $where, 'jurnal'))[0]['total'];
}
}
$tableTemp = [];
// Sisa Saldo Pertanggal $tanggal (-1)
$sisaSaldo = $totalAccountSaldo;
$saldo = $sisaSaldo;
array_push($tableTemp, ['tanggal' => $CYesterday->format('Y-m-d'), 'keterangan' => 'Sisa Saldo ' . $CYesterday->format('d M Y'), 'debet' => '', 'kredit' => '', 'saldo' => $sisaSaldo]);
// Account Saldo range $tanggal s/d $to_tanggal
$accountSaldos = AccountSaldo::join('accounts', 'account_saldos.account_id', '=', 'accounts.id')->leftJoin(DB::raw("(SELECT accounts.`id` AS account_id, accounts.`nama_akun`, reports.display\n FROM accounts\n INNER JOIN account_report ON accounts.`id` = account_report.`account_id`\n INNER JOIN reports ON account_report.`report_id` = reports.id\n WHERE reports.key = 'jurnal')temp_report"), function ($join) {
$join->on('accounts.id', '=', 'temp_report.account_id');
})->whereNotNull('account_saldos.relation_id')->whereBetween('tanggal', [$tanggal, $to_tanggal])->whereNotNull('temp_report.account_id')->select(['account_saldos.*', DB::raw('accounts.nama_akun as nama_akun'), DB::raw('tanggal as _date')])->get()->groupBy('_date');
foreach ($dates as $date) {
// Account Saldo
if (isset($accountSaldos[$date->format('Y-m-d')])) {
$acs = $accountSaldos[$date->format('Y-m-d')];
#New Algorithm
$acsGroupType = $acs->groupBy('type');
foreach ($acsGroupType as $key => $val) {
$agtTotal = $val->sum('nominal');
if ($key == 'debet') {
$saldo -= $agtTotal;
array_push($tableTemp, ['tanggal' => $date->format('Y-m-d'), 'keterangan' => 'Ambil Di Bank', 'debet' => '', 'kredit' => $agtTotal, 'saldo' => $saldo]);
} else {
// kredit
$saldo += $agtTotal;
array_push($tableTemp, ['tanggal' => $date->format('Y-m-d'), 'keterangan' => 'Simpan Di Bank', 'debet' => $agtTotal, 'kredit' => '', 'saldo' => $saldo]);
}
}
}
}
$tableTemp = collect($tableTemp)->groupBy('tanggal');
}
return ['tanggal' => $CTanggal, 'to_tanggal' => $CToTanggal, 'type' => $type, 'types' => ['cash' => 'Kas', 'bank' => 'Bank'], 'table' => $tableTemp];
}
