protected function authenticated()
{
/* UPDATING OVERDUE */
//all collectibles.. if the due date and current year is the same, then if it is a week past the due date week, it is overdue
$invoices = SalesInvoice::whereRaw('(YEAR(now()) = YEAR(due_date)) AND (week(now()) - week(due_date) >= 1) AND (status != "Collected" AND status != "Pending" AND status != "Cancelled")');
//all collectibles.. if the due date and current year is NOT the same, then if the week of due date is greater than current week (meaning next year na), it is overdue
$invoicesYears = SalesInvoice::whereRaw('(YEAR(now()) - YEAR(due_date) >= 1) AND (wEEK(due_Date) - WEEK(now()) >= 1) AND (status != "Collected" AND status != "Pending" AND status != "Cancelled")');
//if client has a sales invoice overdue for 4 months or 120 days.. make the client blacklisted.
$invoicesForClient = SalesInvoice::whereRaw('(datediff(now(), due_date) >= 120) and (status != "Collected" AND status != "Pending" AND status != "Cancelled")')->get();
$invoices->update(['status' => "Overdue"]);
$invoicesYears->update(['status' => "Overdue"]);
foreach ($invoicesForClient as $invoice) {
$client = Client::find($invoice->client_id);
$client->update(['status' => "Blacklisted"]);
}
// $overdues = \DB::table('sales_invoices')->whereRaw('status = "Overdue"');
$OverdueClients = DB::SELECT("SELECT DISTINCT c.id as 'id' FROM clients c JOIN sales_invoices si on si.client_id = c.id WHERE si.status = 'Overdue'");
$today = Carbon::today();
$mondayOf = Carbon::now()->startOfWeek();
// if($today == $mondayOf)
// {
foreach ($OverdueClients as $OverdueClient) {
$collectionLogs = DB::SELECT("SELECT COUNT(id) as id FROM collection_logs WHERE client_id='{$OverdueClient->id}' AND date = '{$mondayOf}' AND action = 'Call and Send SOA Overdue'");
// foreach ($collectionLogs as $collectionLog)
// {
if ($collectionLogs[0]->id == 0) {
$cLog = new CollectionLog();
$cLog->date = $mondayOf;
$cLog->action = 'Call and Send SOA Overdue';
$cLog->client_id = $OverdueClient->id;
$cLog->status = 'To Do';
$cLog->save();
$overdues = DB::SELECT("SELECT id FROM sales_invoices where status= 'Overdue' AND client_id = '{$OverdueClient->id}'");
foreach ($overdues as $overdue) {
$sicl = new SalesInvoiceCollectionLog();
$sicl->sales_invoice_id = $overdue->id;
$sicl->client_id = $cLog->client_id;
$sicl->collection_log_id = $cLog->id;
$sicl->save();
}
}
//}
}
//}
return redirect()->action('DashboardController@index');
}
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
//\DB::table('sales_invoices')->whereRaw('(week(now()) - week(due_date) >= 1) and (status != "Collected")')->update(['status' => "Overdue"]);
// $invoices = DB::table('sales_invoices')->whereRaw('(week(now()) - week(due_date) >= 1) and (status != "Collected")');
// $invoices = DB::SELECT("SELECT * FROM sales_invoices WHERE (week(now()) - week(due_Date) >= 1) and (status != 'Collected')");
$invoices = SalesInvoice::whereRaw('(week(now()) - week(due_date) >= 1) and (status != "Collected")');
$invoicesForClient = SalesInvoice::whereRaw('(week(now()) - week(due_date) >= 1) and (status != "Collected")')->get();
$invoices->update(['status' => "Overdue"]);
foreach ($invoicesForClient as $invoice) {
$client = Client::find($invoice->client_id);
$client->update(['status' => "Blacklisted"]);
}
// $overdues = \DB::table('sales_invoices')->whereRaw('status = "Overdue"');
$overdues = DB::SELECT("SELECT * FROM sales_invoices where status = 'Overdue'");
$today = Carbon::today();
$mondayOf = Carbon::now()->startOfWeek();
if ($today == $mondayOf) {
foreach ($overdues as $overdue) {
$cLog = new CollectionLog();
$cLog->date = $mondayOf;
$cLog->action = 'Call and Send SOA Overdue';
$cLog->client_id = $overdue->client_id;
$cLog->status = 'To Do';
$cLog->save();
$sicl = new SalesInvoiceCollectionLog();
$sicl->sales_invoice_id = $overdue->id;
$sicl->client_id = $cLog->client_id;
$sicl->collection_log_id = $cLog->id;
$sicl->save();
}
}
// $reason = new Reason;
// $reason->reason = 'WEW';
// $reason->save();
//problem is if saturday, it will be 6-7 so it will subtract
//\DB::table('sales_invoices')->whereRaw('((date_add(due_date,interval 6-dayofweek(due_date) day)) < (date_add(date(now()),interval 6-dayofweek(date(now())) day))) and (status != "Collected")')->update(['status'=>"Overdue"]);
}
public function viewOverdue()
{
$sales_invoices = SalesInvoice::whereRaw("week(now()) - week(due_date) >= 1 AND sales_invoices.status='overdue'")->paginate(10);
return view('sales_invoices.index', compact('sales_invoices'));
}
