/**
* 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"]);
}
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');
}
public function delivered($id)
{
$salesInvoice = SalesInvoice::find($id);
if ($salesInvoice->Client->payment_terms == "PDC" || $salesInvoice->Client->payment_terms == "PDC 30" || $salesInvoice->Client->payment_terms == "PDC 45" || $salesInvoice->Client->payment_terms == "PDC 60" || $salesInvoice->Client->payment_terms == "PDC 90") {
$salesInvoice->update(['status' => "Check on Hand", 'date_delivered' => Carbon::now()]);
} else {
$salesInvoice->update(['status' => "Delivered", 'date_delivered' => Carbon::now()]);
if ($salesInvoice->Client->payment_terms == "Cash") {
$salesInvoice->update(['due_date' => Carbon::now()]);
} else {
if ($salesInvoice->Client->payment_terms == "7 Days") {
$salesInvoice->update(['due_date' => Carbon::now()->addDays(7)]);
} else {
if ($salesInvoice->Client->payment_terms == "15 Days") {
$salesInvoice->update(['due_date' => Carbon::now()->addDays(15)]);
} else {
if ($salesInvoice->Client->payment_terms == "30 Days") {
$salesInvoice->update(['due_date' => Carbon::now()->addDays(30)]);
} else {
if ($salesInvoice->Client->payment_terms == "45 Days") {
$salesInvoice->update(['due_date' => Carbon::now()->addDays(45)]);
} else {
if ($salesInvoice->Client->payment_terms == "60 Days") {
$salesInvoice->update(['due_date' => Carbon::now()->addDays(60)]);
} else {
if ($salesInvoice->Client->payment_terms == "75 Days") {
$salesInvoice->update(['due_date' => Carbon::now()->addDays(75)]);
} else {
if ($salesInvoice->Client->payment_terms == "90 Days") {
$salesInvoice->update(['due_date' => Carbon::now()->addDays(90)]);
}
}
}
}
}
}
}
}
}
//create 2 collection logs
$DueDate_Components = DB::SELECT("SELECT YEAR(due_date) as Year, MONTH(due_date) as Month, DAYOFMONTH(due_date) as Day FROM sales_invoices\n WHERE sales_invoices.id = '{$id}'");
$clientId = $salesInvoice->client_id;
$status = $salesInvoice->status;
$mondayOf = Carbon::create($DueDate_Components[0]->Year, $DueDate_Components[0]->Month, $DueDate_Components[0]->Day)->startOfWeek()->subweek();
$thusOf = Carbon::create($DueDate_Components[0]->Year, $DueDate_Components[0]->Month, $DueDate_Components[0]->Day)->startOfWeek()->addDays(3);
$siclsId = SalesInvoiceCollectionLog::where('sales_invoice_id', $id)->lists('collection_log_id');
foreach ($siclsId as $collectionlogIDs) {
$cLog = CollectionLog::find($collectionlogIDs)->delete();
}
$cLog = new CollectionLog();
$cLog->date = $mondayOf;
$cLog->action = 'Call and Send SOA';
$cLog->client_id = $salesInvoice->client_id;
$cLog->status = 'To Do';
$cLog->save();
$sicl = new SalesInvoiceCollectionLog();
$sicl->sales_invoice_id = $id;
$sicl->client_id = $cLog->client_id;
$sicl->collection_log_id = $cLog->id;
$sicl->save();
// $collectionToDo = new SalesInvoiceCollectionLog;
$cLog2 = new CollectionLog();
$cLog2->date = $thusOf;
$cLog2->action = 'Confirm Collection';
$cLog2->client_id = $salesInvoice->client_id;
$cLog2->status = 'To Do';
$cLog2->save();
$sicl2 = new SalesInvoiceCollectionLog();
$sicl2->sales_invoice_id = $id;
$sicl2->client_id = $cLog2->client_id;
$sicl2->collection_log_id = $cLog2->id;
$sicl2->save();
Activity::log('Sales Invoice ' . $salesInvoice['si_no'] . ' was delivered');
return redirect()->action('SalesInvoicesController@index');
}
public function update($id)
{
$cLog = CollectionLog::find($id);
$cLog->update(['status' => 'done']);
return redirect()->action('DashboardController@index');
}
/**
* Remove the specified resource from storage.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function destroy($id, $client_id)
{
$sicls = SalesInvoiceCollectionLog::where('sales_invoice_collection_logs.collection_log_id', $id)->delete();
$cLog = CollectionLog::find($id);
$cLog->delete();
return redirect()->action('CollectionLogsController@index', [$client_id]);
}
