public function index()
{
// total_income, billed_clients, invoice_sent and active_clients
$select = DB::raw('COUNT(DISTINCT CASE WHEN invoices.id IS NOT NULL THEN clients.id ELSE null END) billed_clients,
SUM(CASE WHEN invoices.invoice_status_id >= ' . INVOICE_STATUS_SENT . ' THEN 1 ELSE 0 END) invoices_sent,
COUNT(DISTINCT clients.id) active_clients');
$metrics = DB::table('accounts')->select($select)->leftJoin('clients', 'accounts.id', '=', 'clients.account_id')->leftJoin('invoices', 'clients.id', '=', 'invoices.client_id')->where('accounts.id', '=', Auth::user()->account_id)->where('clients.is_deleted', '=', false)->where('invoices.is_deleted', '=', false)->where('invoices.is_recurring', '=', false)->where('invoices.is_quote', '=', false)->groupBy('accounts.id')->first();
$select = DB::raw('SUM(clients.paid_to_date) as value, clients.currency_id as currency_id');
$paidToDate = DB::table('accounts')->select($select)->leftJoin('clients', 'accounts.id', '=', 'clients.account_id')->where('accounts.id', '=', Auth::user()->account_id)->where('clients.is_deleted', '=', false)->groupBy('accounts.id')->groupBy(DB::raw('CASE WHEN clients.currency_id IS NULL THEN CASE WHEN accounts.currency_id IS NULL THEN 1 ELSE accounts.currency_id END ELSE clients.currency_id END'))->get();
$select = DB::raw('AVG(invoices.amount) as invoice_avg, clients.currency_id as currency_id');
$averageInvoice = DB::table('accounts')->select($select)->leftJoin('clients', 'accounts.id', '=', 'clients.account_id')->leftJoin('invoices', 'clients.id', '=', 'invoices.client_id')->where('accounts.id', '=', Auth::user()->account_id)->where('clients.is_deleted', '=', false)->where('invoices.is_deleted', '=', false)->where('invoices.is_quote', '=', false)->where('invoices.is_recurring', '=', false)->groupBy('accounts.id')->groupBy(DB::raw('CASE WHEN clients.currency_id IS NULL THEN CASE WHEN accounts.currency_id IS NULL THEN 1 ELSE accounts.currency_id END ELSE clients.currency_id END'))->get();
$select = DB::raw('SUM(clients.balance) as value, clients.currency_id as currency_id');
$balances = DB::table('accounts')->select($select)->leftJoin('clients', 'accounts.id', '=', 'clients.account_id')->where('accounts.id', '=', Auth::user()->account_id)->where('clients.is_deleted', '=', false)->groupBy('accounts.id')->groupBy(DB::raw('CASE WHEN clients.currency_id IS NULL THEN CASE WHEN accounts.currency_id IS NULL THEN 1 ELSE accounts.currency_id END ELSE clients.currency_id END'))->get();
$activities = Activity::where('activities.account_id', '=', Auth::user()->account_id)->where('activity_type_id', '>', 0)->orderBy('created_at', 'desc')->take(50)->get();
$pastDue = DB::table('invoices')->leftJoin('clients', 'clients.id', '=', 'invoices.client_id')->leftJoin('contacts', 'contacts.client_id', '=', 'clients.id')->where('invoices.account_id', '=', Auth::user()->account_id)->where('clients.deleted_at', '=', null)->where('contacts.deleted_at', '=', null)->where('invoices.is_recurring', '=', false)->where('invoices.balance', '>', 0)->where('invoices.is_deleted', '=', false)->where('invoices.deleted_at', '=', null)->where('contacts.is_primary', '=', true)->where('invoices.due_date', '<', date('Y-m-d'))->select(['invoices.due_date', 'invoices.balance', 'invoices.public_id', 'invoices.invoice_number', 'clients.name as client_name', 'contacts.email', 'contacts.first_name', 'contacts.last_name', 'clients.currency_id', 'clients.public_id as client_public_id', 'is_quote'])->orderBy('invoices.due_date', 'asc')->take(50)->get();
$upcoming = DB::table('invoices')->leftJoin('clients', 'clients.id', '=', 'invoices.client_id')->leftJoin('contacts', 'contacts.client_id', '=', 'clients.id')->where('invoices.account_id', '=', Auth::user()->account_id)->where('clients.deleted_at', '=', null)->where('contacts.deleted_at', '=', null)->where('invoices.deleted_at', '=', null)->where('invoices.is_recurring', '=', false)->where('invoices.balance', '>', 0)->where('invoices.is_deleted', '=', false)->where('contacts.is_primary', '=', true)->where('invoices.due_date', '>=', date('Y-m-d'))->orderBy('invoices.due_date', 'asc')->take(50)->select(['invoices.due_date', 'invoices.balance', 'invoices.public_id', 'invoices.invoice_number', 'clients.name as client_name', 'contacts.email', 'contacts.first_name', 'contacts.last_name', 'clients.currency_id', 'clients.public_id as client_public_id', 'is_quote'])->get();
$payments = DB::table('payments')->leftJoin('clients', 'clients.id', '=', 'payments.client_id')->leftJoin('contacts', 'contacts.client_id', '=', 'clients.id')->leftJoin('invoices', 'invoices.id', '=', 'payments.invoice_id')->where('payments.account_id', '=', Auth::user()->account_id)->where('clients.deleted_at', '=', null)->where('contacts.deleted_at', '=', null)->where('contacts.is_primary', '=', true)->select(['payments.payment_date', 'payments.amount', 'invoices.public_id', 'invoices.invoice_number', 'clients.name as client_name', 'contacts.email', 'contacts.first_name', 'contacts.last_name', 'clients.currency_id', 'clients.public_id as client_public_id'])->orderBy('payments.id', 'desc')->take(50)->get();
$hasQuotes = false;
foreach ([$upcoming, $pastDue] as $data) {
foreach ($data as $invoice) {
if ($invoice->is_quote) {
$hasQuotes = true;
}
}
}
$data = ['account' => Auth::user()->account, 'paidToDate' => $paidToDate, 'balances' => $balances, 'averageInvoice' => $averageInvoice, 'invoicesSent' => $metrics ? $metrics->invoices_sent : 0, 'activeClients' => $metrics ? $metrics->active_clients : 0, 'activities' => $activities, 'pastDue' => $pastDue, 'upcoming' => $upcoming, 'payments' => $payments, 'title' => trans('texts.dashboard'), 'hasQuotes' => $hasQuotes];
return View::make('dashboard', $data);
}
/**
* @param Invoice $invoice
* @return bool
*/
public function autoBillInvoice(Invoice $invoice)
{
/** @var \App\Models\Client $client */
$client = $invoice->client;
/** @var \App\Models\Account $account */
$account = $client->account;
/** @var \App\Models\Invitation $invitation */
$invitation = $invoice->invitations->first();
if (!$invitation) {
return false;
}
if ($credits = $client->credits->sum('balance')) {
$balance = $invoice->balance;
$amount = min($credits, $balance);
$data = ['payment_type_id' => PAYMENT_TYPE_CREDIT, 'invoice_id' => $invoice->id, 'client_id' => $client->id, 'amount' => $amount];
$payment = $this->paymentRepo->save($data);
if ($amount == $balance) {
return $payment;
}
}
$paymentDriver = $account->paymentDriver($invitation, GATEWAY_TYPE_TOKEN);
if (!$paymentDriver) {
return false;
}
$customer = $paymentDriver->customer();
if (!$customer) {
return false;
}
$paymentMethod = $customer->default_payment_method;
if ($paymentMethod->requiresDelayedAutoBill()) {
$invoiceDate = \DateTime::createFromFormat('Y-m-d', $invoice->invoice_date);
$minDueDate = clone $invoiceDate;
$minDueDate->modify('+10 days');
if (date_create() < $minDueDate) {
// Can't auto bill now
return false;
}
if ($invoice->partial > 0) {
// The amount would be different than the amount in the email
return false;
}
$firstUpdate = Activity::where('invoice_id', '=', $invoice->id)->where('activity_type_id', '=', ACTIVITY_TYPE_UPDATE_INVOICE)->first();
if ($firstUpdate) {
$backup = json_decode($firstUpdate->json_backup);
if ($backup->balance != $invoice->balance || $backup->due_date != $invoice->due_date) {
// It's changed since we sent the email can't bill now
return false;
}
}
if ($invoice->payments->count()) {
// ACH requirements are strict; don't auto bill this
return false;
}
}
return $paymentDriver->completeOnsitePurchase(false, $paymentMethod);
}
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('activities', function ($table) {
$table->boolean('is_system')->default(0);
});
$activities = Activity::where('message', 'like', '%<i>System</i>%')->get();
foreach ($activities as $activity) {
$activity->is_system = true;
$activity->save();
}
Schema::table('activities', function ($table) {
$table->dropColumn('message');
});
}
public function index()
{
// total_income, billed_clients, invoice_sent and active_clients
$select = DB::raw('COUNT(DISTINCT CASE WHEN invoices.id IS NOT NULL THEN clients.id ELSE null END) billed_clients,
SUM(CASE WHEN invoices.invoice_status_id >= ' . INVOICE_STATUS_SENT . ' THEN 1 ELSE 0 END) invoices_sent,
COUNT(DISTINCT clients.id) active_clients');
$metrics = DB::table('accounts')->select($select)->leftJoin('clients', 'accounts.id', '=', 'clients.account_id')->leftJoin('invoices', 'clients.id', '=', 'invoices.client_id')->where('accounts.id', '=', Auth::user()->account_id)->where('clients.is_deleted', '=', false)->where('invoices.is_deleted', '=', false)->where('invoices.is_recurring', '=', false)->where('invoices.is_quote', '=', false)->groupBy('accounts.id')->first();
$select = DB::raw('SUM(clients.paid_to_date) as value, clients.currency_id as currency_id');
$paidToDate = DB::table('accounts')->select($select)->leftJoin('clients', 'accounts.id', '=', 'clients.account_id')->where('accounts.id', '=', Auth::user()->account_id)->where('clients.is_deleted', '=', false)->groupBy('accounts.id')->groupBy(DB::raw('CASE WHEN clients.currency_id IS NULL THEN CASE WHEN accounts.currency_id IS NULL THEN 1 ELSE accounts.currency_id END ELSE clients.currency_id END'))->get();
$select = DB::raw('AVG(invoices.amount) as invoice_avg, clients.currency_id as currency_id');
$averageInvoice = DB::table('accounts')->select($select)->leftJoin('clients', 'accounts.id', '=', 'clients.account_id')->leftJoin('invoices', 'clients.id', '=', 'invoices.client_id')->where('accounts.id', '=', Auth::user()->account_id)->where('clients.is_deleted', '=', false)->where('invoices.is_deleted', '=', false)->groupBy('accounts.id')->groupBy(DB::raw('CASE WHEN clients.currency_id IS NULL THEN CASE WHEN accounts.currency_id IS NULL THEN 1 ELSE accounts.currency_id END ELSE clients.currency_id END'))->get();
$activities = Activity::where('activities.account_id', '=', Auth::user()->account_id)->where('activity_type_id', '>', 0)->orderBy('created_at', 'desc')->take(14)->get();
$pastDue = Invoice::scope()->where('due_date', '<', date('Y-m-d'))->where('balance', '>', 0)->where('is_recurring', '=', false)->where('is_quote', '=', false)->where('is_deleted', '=', false)->orderBy('due_date', 'asc')->take(6)->get();
$upcoming = Invoice::scope()->where('due_date', '>=', date('Y-m-d'))->where('balance', '>', 0)->where('is_recurring', '=', false)->where('is_quote', '=', false)->where('is_deleted', '=', false)->orderBy('due_date', 'asc')->take(6)->get();
$data = ['paidToDate' => $paidToDate, 'averageInvoice' => $averageInvoice, 'invoicesSent' => $metrics ? $metrics->invoices_sent : 0, 'activeClients' => $metrics ? $metrics->active_clients : 0, 'activities' => $activities, 'pastDue' => $pastDue, 'upcoming' => $upcoming];
return View::make('dashboard', $data);
}
public function activities($accountId, $userId, $viewAll)
{
$activities = Activity::where('activities.account_id', '=', $accountId)->where('activities.activity_type_id', '>', 0);
if (!$viewAll) {
$activities = $activities->where('activities.user_id', '=', $userId);
}
return $activities->orderBy('activities.created_at', 'desc')->with('client.contacts', 'user', 'invoice', 'payment', 'credit', 'account', 'task')->take(50)->get();
}
public function viewGrades($id, $index = null, Subject $subject, Setting $setting, Activity $activity, Request $request)
{
$page_data = array('subject_id' => $id, 'subject' => $subject->find($id));
$subject_settings = $setting->where('subject_id', '=', $id)->first();
$name_cell = $subject_settings->name_cell;
$grade_cell = $subject_settings->grade_cell;
$header_row = $subject_settings->header_row;
$start_row = $subject_settings->start_row;
$end_row = $subject_settings->end_row;
$sheet_number = $subject_settings->sheet_number;
$cell_range = $subject_settings->cell_range;
$lec_xlsx = $subject_settings->lec_xlsx;
$lab_xlsx = $subject_settings->lab_xlsx;
$page_data['start_row'] = $start_row;
$page_data['end_row'] = $end_row;
$grades = $this->loadGrades($sheet_number, $cell_range, $lec_xlsx, $lab_xlsx);
if ($request->has('name')) {
$name = strtolower($request->input('name'));
$lec = $grades[0];
$filtered_lec = array_filter($lec, function ($row) use($name, $name_cell) {
if (preg_match('/' . $name . '/', strtolower($row[$name_cell]))) {
return $row;
}
});
if (!empty($filtered_lec)) {
$lec_key = key($filtered_lec);
$filtered_lec = $filtered_lec[$lec_key];
}
$lab = $grades[1];
$filtered_lab = array_filter($lab, function ($row) use($name, $name_cell) {
if (preg_match('/' . $name . '/', strtolower($row[$name_cell]))) {
return $row;
}
});
if (!empty($filtered_lab)) {
$lab_key = key($filtered_lab);
$filtered_lab = $filtered_lab[$lab_key];
}
$index = $lec_key;
}
if (!is_null($index)) {
$lec = $grades[0];
$filtered_lec = $lec[$index];
if (!empty($grades[1])) {
$lab = $grades[1];
$filtered_lab = $lab[$index];
}
}
if (!empty($filtered_lec)) {
$student_name = $filtered_lec[$name_cell];
$page_data['student_name'] = $student_name;
$page_data['lec_grades'] = array();
$lec_activities = $activity->where('subject_id', '=', $id)->where('component', '=', 'lec')->get();
foreach ($lec_activities as $al) {
$cell = $al['cell'];
$page_data['lec_grades'][] = $al['title'] . ' - ' . $filtered_lec[$cell] . '/' . $lec[$header_row][$cell];
}
$page_data['lec_grade'] = $filtered_lec[$grade_cell];
if (!empty($grades[1])) {
$lab_activities = $activity->where('subject_id', '=', $id)->where('component', '=', 'lab')->get();
$page_data['lab_grades'] = array();
foreach ($lab_activities as $al) {
$cell = $al['cell'];
$page_data['lab_grades'][] = $al['title'] . ' - ' . $filtered_lab[$cell] . '/' . $lab[$header_row][$cell];
}
$page_data['lab_grade'] = $filtered_lab[$grade_cell];
}
$page_data['has_result'] = true;
$page_data['prev_index'] = $index -= 1;
$page_data['next_index'] = $index += 2;
}
return view('grades', $page_data);
}
