public function Chart() { $yesterday = Carbon::now(); $start = $yesterday->copy()->addDays(-7); $end = $yesterday->copy(); // for query $startBetween = $start->format('Y-m-d'); $endBetween = $end->format('Y-m-d'); $dates = []; while ($start->lte($end)) { $dates[] = $start->copy(); $start->addDay(); } $dates = collect($dates)->forPage(1, 7); $reports = \App\Order::ReportGroup("(orders.`tanggal` BETWEEN '{$startBetween}' AND '{$endBetween}')", "GROUP BY tanggal"); $reports = ConvertRawQueryToArray($reports); $dataLastWeek = []; $dataLabelLastWeek = []; foreach ($dates as $date) { $idx = array_search($date->format("Y-m-d"), array_column($reports, "tanggal")); $val = 0; if (false !== $idx) { $d = $reports[$idx]; $val = $d['jumlah']; } $dataLabelLastWeek[] = $date->format('d M Y'); $dataLastWeek[] = $val; } return ['label' => $dataLabelLastWeek, 'data' => $dataLastWeek]; }
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 _jurnalBank($request) { $bank = $request->get('bank') ? $request->get('bank') : Bank::where('active', 1)->first()->id; $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'); $banks = Bank::where('active', 1)->lists('nama_bank', 'id')->toArray(); $start = $CTanggal->copy(); $end = $CToTanggal->copy(); $dates = []; while ($start->lte($end)) { $dates[] = $start->copy(); $start->addDay(); } // 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 = '{$bank}' 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::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'); })->where('relation_id', $bank)->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')]; foreach ($acs as $as) { $bankName = $as['bank'] != null ? $as['bank']['nama_bank'] : ''; if ($as['type'] == 'debet') { $saldo -= $as['nominal']; array_push($tableTemp, ['tanggal' => $date->format('Y-m-d'), 'keterangan' => $as['nama_akun'] . ' ' . $bankName, 'debet' => '', 'kredit' => $as['nominal'], 'saldo' => $saldo]); } else { // kredit $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'); return ['tanggal' => $CTanggal, 'to_tanggal' => $CToTanggal, 'bank' => $bank, 'banks' => $banks, 'table' => $tableTemp]; }