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');
}
}
}
