public function index($id) { if (Auth::user()['role'] == 'Accounting' or Auth::user()['role'] == 'General Manager') { $client = Client::find($id); $overdue = SalesInvoice::where('client_id', $id)->where('status', 'Overdue')->count(); if ($overdue != 0) { $overdues = SalesInvoice::where('client_id', $id)->where('status', 'Overdue')->get(); } $delivered = SalesInvoice::where('client_id', $id)->where('status', 'Delivered')->count(); if ($delivered != 0) { $delivereds = SalesInvoice::where('client_id', $id)->where('status', 'Delivered')->get(); } $pending = SalesInvoice::where('client_id', $id)->where('status', 'Pending')->count(); if ($pending != 0) { $pendings = SalesInvoice::where('client_id', $id)->where('status', 'Pending')->get(); } $check = SalesInvoice::where('client_id', $id)->where('status', 'Check on Hand')->count(); if ($check != 0) { $checks = SalesInvoice::where('client_id', $id)->where('status', 'Check on Hand')->get(); } $collection_logs = CollectionLog::where('client_id', $id)->orderBy('date', 'asc')->orderBy('status', 'desc')->paginate(10); // $collection_logs= CollectionLog::where('client_id', $id)->orderBy('status', 'desc', 'date', 'asc')->paginate(10); // $collection_logs = DB::SELECT("SELECT * FROM collection_logs WHERE client_id = '$id' ORDER BY date desc, status desc"); // $collection_logs= CollectionLog::where('client_id', $id)->orderBy('date', 'desc')->paginate(10); $salesinvoices = new SalesInvoiceCollectionLog(); return view('collection_logs.index', compact('collection_logs', 'client', 'overdue', 'delivered', 'check', 'overdues', 'delivereds', 'checks')); } }
public function index() { //collected - sale invoices where due date = this week and status is collected if (Auth::user()->role == 'General Manager' || Auth::user()->role == 'Accounting') { $currentCollected = DB::SELECT("SELECT sum(total_amount) as 'total', count(*) as 'num' FROM sales_invoices\n \t\t\t\t\t\t\t\t\t\t\tWHERE week(date_collected) = week(now())\n \t\t\t\t\t\t\t\t\t\t\tAND status='collected'"); if ($currentCollected[0]->num == 0) { $currentAmount = 0; $currentCount = 0; } else { $currentAmount = $currentCollected[0]->total; $currentCount = $currentCollected[0]->num; } //current collectibles - sale invoices where due date = this week and status is delivered $currentCollectibles = DB::SELECT("SELECT sum(total_amount) as 'total', count(*) as 'num' FROM sales_invoices\n \t\t\t\t\t\t\t\t\t\t\tWHERE week(due_date) = week(now())\n \t\t\t\t\t\t\t\t\t\t\tAND status='delivered'"); if ($currentCollectibles[0]->num == 0) { $currentCollectibleAmount = 0; $currentCollectibleCount = 0; } else { $currentCollectibleAmount = $currentCollectibles[0]->total; $currentCollectibleCount = $currentCollectibles[0]->num; } //upcoming collectibles - sale invoices where due date = next week and status is delivered $upcomingCollectibles = DB::SELECT("SELECT sum(total_amount) as 'total', count(*) as 'num' FROM sales_invoices\n \t\t\t\t\t\t\t\t\t\t\tWHERE week(due_date) - week(now()) = 1\n \t\t\t\t\t\t\t\t\t\t\tAND status='delivered'"); if ($upcomingCollectibles[0]->num == 0) { $upcomingCollectibleAmount = 0; $upcomingCollectibleCount = 0; } else { $upcomingCollectibleAmount = $upcomingCollectibles[0]->total; $upcomingCollectibleCount = $upcomingCollectibles[0]->num; } //overdue collectibles - sale invoices where date difference of due date and today is > 7 and status is delivered $overdueCollectibles = DB::SELECT("SELECT sum(total_amount) as 'total', count(*) as 'num' FROM sales_invoices\n \t\t\t\t\t\t\t\t\t\t\tWHERE week(now()) - week(due_date) >= 1\n \t\t\t\t\t\t\t\t\t\t\tAND status='overdue'"); if ($overdueCollectibles[0]->num == 0) { $overdueCollectibleAmount = 0; $overdueCollectibleCount = 0; } else { $overdueCollectibleAmount = $overdueCollectibles[0]->total; $overdueCollectibleCount = $overdueCollectibles[0]->num; } $dailySales = DB::SELECT("SELECT DATE_FORMAT(date,'%m-%d-%y') as 'date', sum(total_amount) as 'total' FROM sales_invoices\n \t\t\t\t\t\t\t\tWHERE YEAR(date) = YEAR(NOW()) AND status != 'draft'\n \t\t\t\t\t\t\t\tGROUP BY date"); //FOR DONUT CHART. MONTH OVERVIEW OF COLLECTED / COLLETIBLES $currentCollectedMonth = DB::SELECT("SELECT sum(total_amount) as 'total', count(*) as 'num' FROM sales_invoices\n WHERE MONTH(date_collected) = MONTH(now())\n AND status='collected'"); if ($currentCollectedMonth[0]->num == 0) { $currentAmountMonth = 0; //$currentCountMonth = 0; } else { $currentAmountMonth = $currentCollectedMonth[0]->total; //$currentCountMonth = $currentCollected[0]->num; } //current collectibles - sale invoices where due date = this week and status is delivered $currentCollectiblesMonth = DB::SELECT("SELECT sum(total_amount) as 'total', count(*) as 'num' FROM sales_invoices\n WHERE MONTH(due_date) = MONTH(now())\n AND status='delivered'"); if ($currentCollectiblesMonth[0]->num == 0) { $currentCollectibleAmountMonth = 0; //$currentCollectibleCountMonth = 0; } else { $currentCollectibleAmountMonth = $currentCollectiblesMonth[0]->total; //$currentCollectibleCountMonth = $currentCollectibles[0]->num; } $overdueCollectiblesMonth = DB::SELECT("SELECT sum(total_amount) as 'total', count(*) as 'num' FROM sales_invoices\n WHERE MONTH(due_date) = MONTH(now())\n AND status='overdue'"); if ($overdueCollectiblesMonth[0]->num == 0) { $overdueCollectibleAmountMonth = 0; //$overdueCollectibleCountMonth = 0; } else { $overdueCollectibleAmountMonth = $overdueCollectiblesMonth[0]->total; //$overdueCollectibleCountMonth = $overdueCollectibles[0]->num; } //assumes general manager is logged in $activities = Activity::where('user_id', '!=', Auth::user()['id'])->orderBy('created_at', 'desc')->take(10)->get(); // $activities = DB::SELECT("SELECT text, user_id, DATE_FORMAT(created_at, '%b %d, %Y %h:%i %p') as created_at FROM activity_log ORDER BY created_at desc LIMIT 10"); // $activities = DB::table('activity_log')->orderby('created_at', 'desc')->limit(10)->get(); $users = User::where('role', '!=', 'General Manager')->lists('username', 'id'); // $collection_logs = CollectionLog::where('week(follow_up_date'), '=', 'week(now())', 'AND', 'status', '=', 'pending'); $collection_logs = DB::SELECT("SELECT c.id, name, action, date, note, c.client_id FROM collection_logs c\n JOIN clients cl on c.client_id = cl.id\n WHERE DATE(now()) = date\n AND c.status='To Do'"); $collection_logs_all = CollectionLog::where('status', 'To Do')->get(); return view('pages.home', compact('currentAmount', 'currentCount', 'currentCollectibleCount', 'currentCollectibleAmount', 'upcomingCollectibleCount', 'upcomingCollectibleAmount', 'overdueCollectibleCount', 'overdueCollectibleAmount', 'dailySales', 'currentAmountMonth', 'currentCollectibleAmountMonth', 'overdueCollectibleAmountMonth', 'activities', 'users', 'collection_logs', 'collection_logs_all')); // return $collection_logs_all; //return $currentCollectiblesMonth[0]->total; } else { if (Auth::user()->role == 'Sales') { // $sales_invoices = SalesInvoice::where('user_id', Auth::user()['id'])->paginate(10); // $dates = SalesInvoice::all()->lists('due_date','due_date'); // return view('sales_invoices.index', compact('sales_invoices','dates')); return redirect()->action('SalesInvoicesController@index'); } } }