/** * Create a new event instance. * * @param $c_id * @param $nom_responsable * @param $nom_enfant * @param $date_inscription * @param $date_prochain_paiement * @param $pseudo_compte_famille * @param $responsable * @param $mot_de_pass_temporaire_compte_famille */ public function __construct($c_id, $nom_responsable, $nom_enfant, $date_inscription, $date_prochain_paiement, $pseudo_compte_famille, $responsable, $mot_de_pass_temporaire_compte_famille) { $this->child_id = $c_id; $this->nom_responsable = $nom_responsable; $this->nom_enfant = $nom_enfant; $this->date_inscription = $date_inscription; $this->date_prochain_paiement = $date_prochain_paiement; $this->pseudo_compte_famille = $pseudo_compte_famille; $this->responsable = $responsable; $this->mot_de_pass_temporaire_compte_famille = $mot_de_pass_temporaire_compte_famille; $user = new User(); $user->nom_responsable = $this->nom_responsable; $user->name = $this->nom_responsable; $user->type = 'famille'; $user->email = $this->pseudo_compte_famille; $user->password = \Hash::make($this->mot_de_pass_temporaire_compte_famille); if ($this->responsable == 1) { $user->sexe = 'homme'; } else { $user->sexe = 'femme'; } $user->save(); if ($user) { $child = Child::findOrFail($this->child_id); $child->f_id = $user->id; $child->save(); $bill = Bill::where('child_id', $this->child_id)->first(); $bill->f_id = $user->id; $bill->save(); $info = ['responsable' => $this->nom_responsable, 'nom_enfant' => $this->nom_enfant, 'date_inscription' => $this->date_inscription, 'date_pro_paim' => $this->date_prochain_paiement, 'pseudo_email' => $this->pseudo_compte_famille, 'respons' => $this->responsable, 'pass' => $this->mot_de_pass_temporaire_compte_famille]; Mail::queue('emails.test', $info, function ($message) { $message->to($this->pseudo_compte_famille, 'ok')->from('*****@*****.**')->subject('Bienvenue !'); }); } }
/** * Auto determine order number for given client. * * @param object $campaign * @param int $clientId * @return int */ public static function autoDetermineOrderNumber($campaign, $clientId) { $query = Bill::where('campaign_id', $campaign->id)->where('client_id', $clientId)->max('campaign_order'); if (!$query) { return 1; } return $query + 1; }
/** * Normal user tries to delete all user bills. */ public function test_normal_user_delete_all_user_bills() { $billIds = ['user_id' => $this->user->id, 'client_id' => $this->client->id]; // Generate paid bills factory(\App\Bill::class, 'paid', 4)->create($billIds); // Generate unpaid bills factory(\App\Bill::class, 4)->create($billIds); $this->actingAs(factory(\App\User::class)->create())->post('/admin-center/users-manager/user/' . $this->user->id . '/delete-all-bills')->assertEquals(8, \App\Bill::where('user_id', $this->user->id)->where('client_id', $this->client->id)->count()); }
/** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { // $company = Company::find($id); $branches = Branch::where("company_id", "=", $company->id)->get(); $stacks = Invoicingstack::where("company_id", "=", $company->id)->where("status", "=", 0)->get(); $stack2s = Invoicingstack::where("company_id", "=", $company->id)->get(); $invoice = Bill::where("company_id", "=", $company->id)->get(); $invoiceGroup = Bill::where("company_id", "=", $company->id)->groupBy("invoice_date")->get(); return View("company.companydetail", ['invGp' => $invoiceGroup, 'stacks' => $stacks, 'stack2s' => $stack2s, 'invoices' => $invoice, 'company' => $company, 'branches' => $branches, 'title' => 'Company Detail']); }
/** * Get clients of given user. * * @param bool $userId * @return mixed */ public static function getClients($userId = false) { // If an user id is not given use the id of current logged in user if (!$userId) { $userId = Auth::user()->id; } $clients = Client::where('user_id', $userId)->paginate(); foreach ($clients as &$client) { $client->orders = Bill::where('client_id', $client->id)->count(); } return response($clients)->header('Content-Type', 'application/json'); }
/** * Return array with bill ids and question marks to be used in prepared sql statement. * * @param array $config * @param array $clientIds * @return mixed */ protected static function performGetBillDataQuery($config, $clientIds) { $billIdsQuery = Bill::where('paid', $config['onlyPaidBills'])->whereIn('client_id', $clientIds)->get(); // Build string with question marks and remove last comma $questionMarks = ''; foreach ($billIdsQuery as $result) { $questionMarks .= '?,'; } $questionMarks = substr($questionMarks, 0, -1); // Build array with values $billIds = []; $stop = 2; for ($i = 1; $i <= $stop; $i++) { foreach ($billIdsQuery as $result) { $billIds[] = $result->id; } } return ['billIds' => $billIds, 'questionMarks' => $questionMarks]; }
/** * Make unpaid all user bills. * * @param int $userId * @param MakeAllUserBillsUnpaidRequest $request * @return mixed */ public function makeAllUserBillsUnpaid($userId, MakeAllUserBillsUnpaidRequest $request) { $response = new AjaxResponse(); // Make sure user exists in database if (!User::where('id', $userId)->count()) { $response->setFailMessage(trans('users_manager.user_not_found')); return response($response->get(), $response->badRequest())->header('Content-Type', 'application/json'); } // Make user bills as unpaid Bill::where('user_id', $userId)->update(['paid' => 0]); $response->setSuccessMessage(trans('users_manager.all_user_bills_are_unpaid')); return response($response->get())->header('Content-Type', 'application/json'); }
private function billsFromUser() { return Bill::where('user_id', auth()->user()->id); }
/** * @param $ids */ public function exportPdf($ids = null) { $ids = explode(',', substr($ids, 0, -1)); $ids = array_unique($ids); $model = Child::whereIn('id', $ids)->where('user_id', \Auth::user()->id)->get(['id', 'nom_enfant', 'created_at']); Excel::create('La liste des Elèves', function ($excel) use($model, $ids) { $excel->sheet('La liste des Elèves', function ($sheet) use($model, $ids) { foreach ($model as $child) { $child->time = $child->created_at->toDateString(); $count = Bill::where('user_id', \Auth::user()->id)->where('child_id', $child->id)->where('status', 0)->count(); if ($count == 0) { $child->status = 'Réglée'; } else { $child->status = 'Non Réglée'; } foreach ($child->classrooms as $cr) { $child->classe = $cr->nom_classe; } unset($child->created_at); unset($child->id); } // $sheet->setWidth('D',20); $sheet->fromModel($model); $sheet->setAllBorders('thin'); $sheet->setFontFamily('OpenSans'); $sheet->setFontSize(13); $sheet->setFontBold(false); for ($i = 1; $i <= count($ids) + 1; $i++) { $sheet->setHeight($i, 25); $sheet->row($i, function ($rows) { $rows->setFontColor('#556b7b'); $rows->setAlignment('center'); }); $sheet->cells('A' . $i . ':' . 'D' . $i, function ($cells) { $cells->setValignment('middle'); $cells->setFontColor('#556b7b'); $cells->setFont(array('family' => 'OpenSans', 'size' => '13', 'bold' => false)); }); } // normal header $sheet->cells('A1:D1', function ($cells) { $cells->setBackground('#e9f1f3'); $cells->setFontColor('#556b7b'); $cells->setFont(array('family' => 'OpenSans', 'size' => '15', 'bold' => true)); }); $sheet->row(1, array('Nom Elève', 'Date d\'inscription', 'Statut de Paiement', 'Classe')); }); })->export('pdf'); }
/** * Render unpaid bills page of given client. * * @param int $clientId * @return \Illuminate\Http\RedirectResponse|\Illuminate\Routing\Redirector */ public function unpaidBillsOfThisClient($clientId) { $client = Client::where('id', $clientId)->where('user_id', Auth::user()->id)->first(); // Make sure client exists if (!$client) { return redirect('/clients'); } $totalUnpaidBills = Bill::where('paid', 0)->where('client_id', $clientId)->where('user_id', Auth::user()->id)->count(); return view('client-unpaid-bills')->with('clientId', $clientId)->with('name', $client->name)->with('totalUnpaidBills', $totalUnpaidBills); }
/** * Return number of cashed money in given campaign. * * @param int $campaignNumber * @param int $campaignYear * @return float */ public static function cashedMoney($campaignNumber, $campaignYear) { $billIdsQuery = Bill::where('user_id', Auth::user()->id)->where('campaign_id', Campaign::where('number', $campaignNumber)->where('year', $campaignYear)->first()->id)->where('paid', 1)->get(); // Build question marks string $questionMarks = ''; foreach ($billIdsQuery as $result) { $questionMarks .= '?,'; } // Remove last comma $questionMarks = substr($questionMarks, 0, -1); // Build bill ids array $billIds = []; $stop = 2; for ($i = 1; $i <= $stop; $i++) { foreach ($billIdsQuery as $result) { $billIds[] = $result->id; } } if (count($billIds) < 1) { return 0; } $query = "SELECT SUM(bills.cashed_money) as cashed_money FROM (SELECT bill_products.final_price as cashed_money FROM bill_products WHERE bill_products.bill_id IN ({$questionMarks}) "; $query .= "UNION ALL SELECT bill_application_products.final_price as cashed_money FROM bill_application_products WHERE bill_application_products.bill_id IN ({$questionMarks})) bills"; $result = DB::select($query, $billIds); // Make sure result was returned if (isset($result[0]->cashed_money)) { return $result[0]->cashed_money; } return 0.0; }
/** * Common code used by productSoldPieces() and productTotalPrice() methods. * * @param int $productId * @param bool $isCustomProduct * @return int */ private static function productSoldPiecesAndTotalPriceCommons($productId, $isCustomProduct = false) { $paidBillIds = []; $paidBills = Bill::where('user_id', Auth::user()->id)->where('paid', 1)->get(); if (!$paidBills) { return 0; } // Build array with ids of paid bills foreach ($paidBills as $paidBill) { $paidBillIds[] = $paidBill->id; } if ($isCustomProduct) { return BillProduct::where('product_id', $productId)->whereIn('bill_id', $paidBillIds); } return BillApplicationProduct::where('product_id', $productId)->whereIn('bill_id', $paidBillIds); }
/** * Get number of user bills. * * @return int */ public static function numberOfBills() { return Bill::where('user_id', Auth::user()->id)->count(); }
public static function lastBills($clientId, $limit = 5, $paid = false) { if ($paid) { $paid = 1; } else { $paid = 0; } // Do a first query to get bill ids $billIdsQuery = Bill::where('client_id', $clientId)->where('paid', $paid)->get(); if (!count($billIdsQuery)) { return 0; } // Build string with question marks $billIdsQuestionMarks = ''; foreach ($billIdsQuery as $result) { $billIdsQuestionMarks .= "?,"; } // Remove last comma from generated string $billIdsQuestionMarks = substr($billIdsQuestionMarks, 0, -1); // Build array with values $stop = 2; $billIds = []; for ($i = 1; $i <= $stop; $i++) { foreach ($billIdsQuery as $result) { $billIds[] = $result->id; } } // Select total price, number of products and bill id to be used in group statement $query = "SELECT SUM(bill_products.final_price) as total, SUM(bill_products.quantity) as number_of_products, bill_products.bill_id as bill_id, "; $query .= "bill_products.payment_term as payment_term, bill_products.campaign_order as campaign_order, "; $query .= "bill_products.campaign_year as campaign_year, bill_products.campaign_number as campaign_number FROM "; // Select other required columns $query .= "(SELECT final_price, bill_id, quantity, bills.created_at as created_at, bills.payment_term as payment_term, campaigns.year as campaign_year, "; $query .= "campaigns.number as campaign_number, bills.campaign_order as campaign_order "; $query .= "FROM bill_products LEFT JOIN bills ON bills.id = bill_id LEFT JOIN campaigns ON bills.campaign_id = campaigns.id WHERE bill_id IN ({$billIdsQuestionMarks}) "; // Do the same for other table $query .= "UNION ALL SELECT final_price, bill_id, quantity, bills.created_at as created_at, bills.payment_term as payment_term, campaigns.year as campaign_year, "; $query .= "campaigns.number as campaign_number, bills.campaign_order as campaign_order "; $query .= "FROM bill_application_products LEFT JOIN bills ON bills.id = bill_id LEFT JOIN campaigns ON bills.campaign_id = campaigns.id WHERE bill_id IN ({$billIdsQuestionMarks})) bill_products "; $query .= "GROUP BY bill_products.bill_id ORDER BY bill_products.created_at DESC"; if ($limit !== 'all') { $query .= " LIMIT {$limit}"; } $results = DB::select($query, $billIds); // Loop trough results and set an appropriate message when a bill has no payment term set if (!count($results)) { return 0; } foreach ($results as $result) { if ($result->payment_term === '0000-00-00') { $result->payment_term = trans('bill.not_set'); } } return $results; }
public function statusindexef() { if (\Request::ajax()) { $status = \Input::get('status'); if ($status == 0) { $bills = Bill::where('status', 0)->where('f_id', \Auth::user()->id)->get(); foreach ($bills as $bill) { if ($bill->status == 0) { $class = "label-danger"; $message = "Non réglée"; } else { $class = "label-success"; $message = "réglée"; } if ($bill->child->photo) { $photo = asset('uploads/' . $bill->child->photo); } else { $photo = asset('images/' . 'avatar4.jpg'); } echo ' <tr> <td><div class="minimal single-row"> </div></td> <td>' . $bill->id . '</td> <td>' . $bill->start->format('d-m-Y') . '</td> <td>' . $bill->somme . ' Dhs</td> <td><span class="label ' . $class . ' label-mini"> ' . $message . ' </span> </td> <td> ' . $bill->child->nom_enfant . '</td> <td><a href="' . action('BillsController@detailsef', [$bill->id]) . '"><div class="btn_details">Détails</div></a></td> </tr>'; } } else { $bills = Bill::where('status', 1)->where('f_id', \Auth::user()->id)->get(); foreach ($bills as $bill) { if ($bill->status == 0) { $class = "label-danger"; $message = "Non réglée"; } else { $class = "label-success"; $message = "réglée"; } if ($bill->child->photo) { $photo = asset('uploads/' . $bill->child->photo); } else { $photo = asset('images/no_avatar.jpg'); } echo ' <tr> <td><div class="minimal single-row"> </div></td> <td>' . $bill->id . '</td> <td>' . $bill->start->format('d-m-Y') . '</td> <td>' . $bill->somme . ' Dhs</td> <td><span class="label ' . $class . ' label-mini"> ' . $message . ' </span> </td> <td> ' . $bill->child->nom_enfant . '</td> <td><a href="' . action('BillsController@detailsef', [$bill->id]) . '"><div class="btn_details">Détails</div></a></td> </tr>'; } } } }
/** * Normal user tries to delete all user paid bills. */ public function test_normal_user_delete_all_user_paid_bills() { // Generate paid bills factory(App\Bill::class, 'paid', 4)->create(['user_id' => $this->user->id, 'client_id' => $this->client->id]); $this->actingAs(factory(App\User::class)->create())->post('/admin-center/users-manager/user/' . $this->user->id . '/delete-paid-bills')->seeInDatabase('bills', ['user_id' => $this->user->id, 'paid' => 1])->assertEquals(4, \App\Bill::where('user_id', $this->user->id)->where('paid', 1)->count()); }
/** * Execute the console command. * * @return mixed */ public function handle() { if (Carbon::now()->toDateString() == Carbon::now()->startOfMonth()->toDateString()) { $users = User::where('type', 'ecole')->get(); foreach ($users as $user) { $sc = SchoolYear::where('user_id', $user->id)->where('current', 1)->first(); if ($sc->type == 'Semis' && Carbon::now()->between($sc->startch1, $sc->endch2)) { foreach ($user->children as $child) { foreach ($child->bills as $bill) { $getChild = Bill::where('child_id', $bill->child_id)->where('reduction', 0)->where('school_year_id', $sc->id)->where('nbrMois', 1)->orderBy('id', 'desc')->first(); if ($getChild) { $facture = new Bill(); $facture->start = $getChild->end->toDateString(); $facture->end = $getChild->end->addMonth()->toDateString(); $facture->status = 0; $facture->user_id = $getChild->user_id; $enfant = Child::where('user_id', $getChild->user_id)->where('id', $getChild->child_id)->first(); $taman = ''; $transportStatus = $enfant->transport; foreach ($enfant->levels as $level) { $getPriceOfLevel = PriceBill::where('niveau', $level->id)->where('user_id', $getChild->user_id)->first(); $taman = $getPriceOfLevel->prix; } $transportStatus == 0 ? $facture->somme = $taman : ($facture->somme = $taman + Transport::where('user_id', $getChild->user_id)->first()->somme); $facture->nbrMois = $getChild->nbrMois; $facture->reductionPrix = null; $facture->school_year_id = $getChild->school_year_id; $facture->reduction = 0; $facture->child_id = $getChild->child_id; $facture->f_id = $getChild->f_id; $facture->save(); break; } } } } elseif ($sc->type == 'Trim' && Carbon::now()->between($sc->startch1, $sc->endch3)) { foreach ($user->children as $child) { foreach ($child->bills as $bill) { $getChild = Bill::where('child_id', $bill->child_id)->where('reduction', 0)->where('school_year_id', $sc->id)->where('nbrMois', 1)->orderBy('id', 'desc')->first(); if ($getChild) { $facture = new Bill(); $facture->start = $getChild->end->toDateString(); $facture->end = $getChild->end->addMonth()->toDateString(); $facture->status = 0; $facture->user_id = $getChild->user_id; $enfant = Child::where('user_id', $getChild->user_id)->where('id', $getChild->child_id)->first(); $taman = ''; $transportStatus = $enfant->transport; foreach ($enfant->levels as $level) { $getPriceOfLevel = PriceBill::where('niveau', $level->id)->where('user_id', $getChild->user_id)->first(); $taman = $getPriceOfLevel->prix; } $transportStatus == 0 ? $facture->somme = $taman : ($facture->somme = $taman + Transport::where('user_id', $getChild->user_id)->first()->somme); $facture->nbrMois = $getChild->nbrMois; $facture->reductionPrix = null; $facture->school_year_id = $getChild->school_year_id; $facture->reduction = 0; $facture->child_id = $getChild->child_id; $facture->f_id = $getChild->f_id; $facture->save(); break; } } } } } /* $enfants = Child::has('bills')->get(); foreach($enfants as $e) { foreach($e->bills as $b) { $d = Bill::where('child_id',$b->child_id)->orderBy('id','desc')->first(); $bill = new Bill(); $bill->start =$d->end->toDateString(); $nextMonth7 =$d->end->addMonth()->toDateString(); if(Carbon::parse($nextMonth7)->month == 7) { $bill->end = Carbon::parse($nextMonth7)->addMonths(2)->toDateString(); }else{ $bill->end = $nextMonth7; } $bill->status = 0; $bill->user_id = $d->user_id; $bill->somme = $d->somme; $bill->child_id =$d->child_id; $bill->f_id = $d->f_id; $bill->save(); break; } }*/ } }
public function index(Request $request) { Carbon::setLocale('ru'); $clients_filter = Client::whereHas('deliveries', function ($query) { $query->where('status', '=', 'Профинансирована'); })->get(); if ($request->ajax()) { $bills = Bill::where('id', '>', 0); if (Input::get('year') != Null) { $bills = $bills->whereYear('bill_date', '=', Input::get('year')); } if (Input::get('month') != Null) { $bills = $bills->whereMonth('bill_date', '=', Input::get('month')); } if (Input::get('client_id') != 'all') { $bills = $bills->where('client_id', '=', Input::get('client_id')); } $sum = array(); $sum['without_nds'] = $bills->sum('without_nds'); $sum['nds'] = $bills->sum('nds'); $sum['with_nds'] = $bills->sum('with_nds'); $bills = $bills->get(); $clients = Client::All(); $debts_full = array(); $monthRepayment = array(); $bill_date_first_day = Carbon::createFromDate(Input::get('year'), Input::get('month'), 1); foreach ($clients as $client) { foreach ($client->agreements as $agreement) { $debt = 0; foreach ($agreement->relations as $relation) { if ($agreement->account == FALSE) { foreach ($relation->deliveries as $delivery) { if ($delivery->status == 'Профинансирована') { //echo $client->name.": долг перед месяцем:"; $pred_with_nds = $delivery->dailyChargeCommission()->where('handler', false)->whereDate('created_at', '<', $bill_date_first_day)->sum('with_nds'); // var_dump($pred_with_nds); // echo $client->name.": погашения:"; $repayments = $delivery->dailyChargeCommission()->where('handler', true)->sum('with_nds'); // var_dump($repayments);echo $client->name.": начисленные комиссии:"; $with_nds_delivery = $delivery->dailyChargeCommission()->where('handler', false)->whereYear('created_at', '=', Input::get('year'))->whereMonth('created_at', '=', Input::get('month'))->sum('with_nds'); // var_dump($with_nds_delivery); if ($repayments > $pred_with_nds) { if ($repayments >= $with_nds_delivery + $pred_with_nds) { $debt += 0; } else { $debt += $with_nds_delivery - ($repayments - $pred_with_nds); } } else { $debt += $with_nds_delivery; } // echo $client->name.": текущий долг:"; // var_dump($debt); // echo "\n"; } } } else { $debt = 0; } } // var_dump($debt); $monthRepayment[$agreement->id] = $debt; } } // var_dump($monthRepayment); $stop = Delivery::where('stop_commission', '=', true)->get(); return view('invoicing.indexAjax', ['stop' => $stop, 'bills' => $bills, 'debts_full' => $debts_full, 'monthRepayment' => $monthRepayment, 'sum' => $sum]); } else { $dt = Carbon::now()->startOfMonth(); $dates = DailyChargeCommission::select('created_at')->whereDate('created_at', '<', $dt)->orderBy('created_at', 'desc')->groupBy('created_at')->get(); $year = ''; $month = ''; $i_month = 0; $i_year = 0; $dates_for_filter = array(); foreach ($dates as $date) { $dates_for_filter[$date->created_at->year][$date->created_at->month] = $date->created_at->month; } return view('invoicing.index', ['clients' => $clients_filter, 'dt' => $dates_for_filter]); } }
public function statusindex() { if (\Request::ajax()) { $status = \Input::get('status'); if ($status == 0) { $bills = Bill::where('status', 0)->where('user_id', \Auth::user()->id)->get(); foreach ($bills as $bill) { $photo = asset('uploads/' . $bill->child->photo); echo ' <tr> <td> ' . $bill->id . '</td> <td><img class="avatar" src="' . $photo . '"></td> <td>' . $bill->child->nom_enfant . '</td> <td> ' . $bill->start->format('d-m-Y') . ' </td> <td> ' . $bill->somme . ' Dhs</td> <td><span class="label label-danger label-mini"> Non réglée </span> </td> <td> <a href="' . action('BillsController@delete', [$bill->id]) . '" class="actions_icons delete-bill"> <i class="fa fa-trash-o liste_icons"></i></a> <a class="archive-bill" href="' . action('BillsController@archive', [$bill->id]) . '"><i class="fa fa-archive liste_icons"></i> </a> </td> <td><a href="' . action('BillsController@details', [$bill->id]) . '"><div class="btn_details">Détails</div></a></td> </tr>'; } } else { $bills = Bill::where('status', 1)->where('user_id', \Auth::user()->id)->get(); foreach ($bills as $bill) { $photo = asset('uploads/' . $bill->child->photo); echo ' <tr> <td> ' . $bill->id . '</td> <td><img class="avatar" src="' . $photo . '"></td> <td>' . $bill->child->nom_enfant . '</td> <td> ' . $bill->start->format('d-m-Y') . ' </td> <td> ' . $bill->somme . ' Dhs</td> <td><span class="label label-success label-mini"> réglée </span> </td> <td> <a href="' . action('BillsController@delete', [$bill->id]) . '" class="actions_icons delete-bill"> <i class="fa fa-trash-o liste_icons"></i></a> <a class="archive-bill" href="' . action('BillsController@archive', [$bill->id]) . '"><i class="fa fa-archive liste_icons"></i> </a> </td> <td><a href="' . action('BillsController@details', [$bill->id]) . '"><div class="btn_details">Détails</div></a></td> </tr>'; } } } }
/** * Money that client owes to the user because payment term was passed. * * @param int $clientId * @return int */ public static function moneyOwedDuePassedPaymentTerm($clientId) { $billIdsQuery = Bill::where('client_id', $clientId)->where('payment_term', '<', date('Y-m-d'))->where('paid', 0)->get(); // Check if query returned something if (!count($billIdsQuery)) { return 0; } $questionMarks = ''; // Build question marks string foreach ($billIdsQuery as $result) { $questionMarks .= '?,'; } // Remove last comma $questionMarks = substr($questionMarks, 0, -1); // Build array with ids $billIds = []; $stop = 2; for ($i = 1; $i <= $stop; $i++) { foreach ($billIdsQuery as $result) { $billIds[] = $result->id; } } $query = "SELECT SUM(bill_products.final_price) as total FROM(SELECT final_price, bill_id FROM bill_products WHERE bill_id IN ({$questionMarks}) "; $query .= "UNION ALL SELECT final_price, bill_id FROM bill_application_products WHERE bill_id IN ({$questionMarks})) bill_products"; $result = DB::select($query, $billIds); if (isset($result[0]->total)) { return $result[0]->total; } return 0; }
/** * Mark bill as unpaid. * * @param int $billId * @return \Illuminate\Contracts\Routing\ResponseFactory|\Symfony\Component\HttpFoundation\Response */ public static function markAsUnpaid($billId) { $response = new AjaxResponse(); // Make sure bill exists if (!Bill::where('id', $billId)->where('user_id', Auth::user()->id)->count()) { $response->setFailMessage(trans('bill.bill_not_found')); return response($response->get(), 404)->header('Content-Type', 'application/json'); } Auth::user()->bills()->where('id', $billId)->update(['paid' => 0]); $response->setSuccessMessage(trans('bill.marked_as_unpaid')); $response->addExtraFields(['paid' => 0]); return response($response->get()); }
public function generateInvoice(Request $request, $id = "") { $input = $request->all(); $company = !empty($id) ? Company::find($id) : Company::find($input['company_id']); $ArrMain = []; // print_r($input); if (count($input) > 0) { //allow room for invoice to be processed for recent uploaded and date range $stacks = Invoicingstack::where("company_id", "=", $company->id)->where("status", "=", 0)->get(); // var_dump("ok ok"); } else { $stacks = Invoicingstack::where("company_id", "=", $company->id)->where("status", "=", 0)->get(); } //$input['id']=$id; $total = 0; $bill = new Bill(); $invoiceno = ""; if (count($stacks) > 0) { $bill_no = $bill->genInvoiceNo($company->id) > 0 ? $bill->genInvoiceNo($company->id) : "01"; $invoiceno = !empty($input['inv_no']) && $input['inv_no'] != "" ? "RJLEX/" . strtoupper(substr($company->name, 0, 3)) . "/" . $input['inv_no'] : "RJLEX/" . strtoupper(substr($company->name, 0, 3)) . "/" . str_pad($bill_no, 3, "0", STR_PAD_LEFT); $bill->invoice_no = $invoiceno; $bill->created_at = date("Y-m-d H:i:s"); $bill->updated_at = date("Y-m-d H:i:s"); $bill->company_id = $company->id; $bill->invoice_date = date("Y-m-d H:i:s"); $check = Bill::where("invoice_no", "=", $invoiceno)->pluck("invoice_no"); if (!empty($check)) { echo "Invoice number already existing in database"; exit; } $bill->save(); //$invoiceno = "RJLEX/".strtoupper(substr($company->name,0,3))."/".$bill->genInvoiceNo(); } $dateFrom = "2000-01-01"; $dateTo = "2000-01-01"; $objPHPExcel = new \PHPExcel(); $m = 0; $s = 0; foreach ($stacks as $stack) { // Create a new worksheet called “My Data” $s++; $sheetName = "RJLEX_" . strtoupper(substr($company->name, 0, 3)) . "_" . $bill_no; $siteName = Branch::where("id", "=", $stack->site_id)->pluck("name"); $myWorkSheet = new \PHPExcel_Worksheet($objPHPExcel, $siteName); $objPHPExcel->addSheet($myWorkSheet, $m); /**/ $objPHPExcel->setActiveSheetIndex($m); $m++; $site = ""; $siteName = ""; $description = ""; $printNo = 0; $copyNo = 0; /** * For each loop of the invoicing table, * compute the cost of each print job and populate/create * the invoice detail table */ $a3Arr = []; $a4Arr = []; $sumNoPagesA3Color = 0; //+= $minvoice->number_of_pages; $sumAmountA3Color = 0; //$amount; $sumNoPagesA4Color = 0; // $minvoice->number_of_pages; $sumAmountA4Color = 0; //$amount; $sumNoPagesA3Mono = 0; // $minvoice->number_of_pages; $sumAmountA3Mono = 0; $sumNoPagesA4Mono = 0; //0 $minvoice->number_of_pages; $sumAmountA4Mono = 0; //$amount; $sumTotal = 0; $unitCostA3Mono = 0; $unitCostA4Mono = 0; $unitCostA3Color = 0; $unitCostA4Color = 0; $x = 1; $p = 1; if (count($input) > 0) { $invoicing = DB::table("invoicing")->where("stack_id", "=", $stack->id)->get(); //->where("final_date",">=",$input['date_from'])->where("final_date","<=","date_to") } else { $invoicing = DB::table("invoicing")->where("stack_id", "=", $stack->id)->get(); } //$dateFrom = DB::table("invoicing")->where("stack_id","=",$stack->id)->max('final_date'); //DB::table("invoicing")->where() //$dateTo = DB::table("invoicing")->where("stack_id","=",$stack->id)->min('final_date'); foreach ($invoicing as $minvoice) { //One Location in loop $froDate = new \DateTime("2000-01-01"); $toDate = new \DateTime("2000-01-01"); if ($x == 1) { //$dateFrom = new \DateTime($minvoice->final_date); //$dateTo = new \DateTime($minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('A' . $x, "SITE"); $objPHPExcel->getActiveSheet()->setCellValue('B' . $x, "User ID"); $objPHPExcel->getActiveSheet()->setCellValue('C' . $x, "Submit IP"); $objPHPExcel->getActiveSheet()->setCellValue('D' . $x, "Print Job Name"); $objPHPExcel->getActiveSheet()->setCellValue('E' . $x, "Submit Date"); $objPHPExcel->getActiveSheet()->setCellValue('F' . $x, "Final Date"); $objPHPExcel->getActiveSheet()->setCellValue('G' . $x, "Final Action"); $objPHPExcel->getActiveSheet()->setCellValue('H' . $x, "Final Site"); $objPHPExcel->getActiveSheet()->setCellValue('I' . $x, "Number of Pages"); $objPHPExcel->getActiveSheet()->setCellValue('J' . $x, "Cost Per Page"); $objPHPExcel->getActiveSheet()->setCellValue('K' . $x, "Total Cost"); $objPHPExcel->getActiveSheet()->setCellValue('L' . $x, "Release IP"); $objPHPExcel->getActiveSheet()->setCellValue('M' . $x, "Release User"); $objPHPExcel->getActiveSheet()->setCellValue('N' . $x, "Release Method"); $objPHPExcel->getActiveSheet()->setCellValue('O' . $x, "Print Job Color"); $objPHPExcel->getActiveSheet()->setCellValue('P' . $x, "Print Job Duplex"); $objPHPExcel->getActiveSheet()->setCellValue('Q' . $x, "Print Job Paper Size"); $objPHPExcel->getActiveSheet()->setCellValue('R' . $x, "Release Model"); $objPHPExcel->getActiveSheet()->setCellValue('S' . $x, "Release Model Type"); $objPHPExcel->getActiveSheet()->setCellValue('T' . $x, "Rrelease Host Name"); } //$date = date_create_from_format("Y-m-d H:i:s",$minvoice->final_date); if (isset($input['date_from'])) { $dateFrom = $input['date_from']; } else { /*$froDate = $dateFrom; if( (int)$date->getTimestamp() <= (int)$froDate->getTimestamp() ){ $dateFrom = new \DateTime($minvoice->final_date); }*/ } if (isset($input['date_to'])) { $dateTo = $input['date_to']; } else { /*$toDate = $dateTo; if( (int)$date->getTimestamp() >= (int)$toDate->getTimestamp() ){ $dateTo = new \DateTime($minvoice->final_date); }*/ } $site = Branch::where("id", "=", $minvoice->site)->pluck("name"); $a3Arr[] = ""; $a4Arr[] = ""; // if($minvoice->job_paper_size == "A3"){} $paperid = DB::table("papers")->where("name", $minvoice->job_paper_size)->pluck("id"); if (strtolower($minvoice->final_action) == "p" || strtolower($minvoice->final_action) == "c") { //check if action type is print /** * check if colored or mono */ $p++; if (strtolower($minvoice->device_type) == "c") { if (strtolower($minvoice->job_color) == "y") { if ($minvoice->job_paper_size == "A3" || ($minvoice->job_paper_size == "Tabloid" || $minvoice->job_paper_size == "Ledger")) { if (strtolower($minvoice->device_name) == 'lexmark x925' || strtolower($minvoice->device_name) == 'lexmark x950' || strtolower($minvoice->device_name) == 'lexmark x950de' || strtolower($minvoice->device_name) == 'lexmark x792de' || strtolower($minvoice->device_name) == 'lexmark cx510de') { $cost = Price::where("job_id", 1)->where("job_type", "color")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA3Color += $minvoice->number_of_pages; $sumAmountA3Color += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA3Color = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "Y"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, $minvoice->job_paper_size); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } elseif (strtolower($minvoice->device_name) == 'lexmark x860') { # else if Lexmark X860 (a mono A3 printer) //|| strtolower($minvoice->device_name) == 'lexmark x860de' $cost = Price::where("job_id", 1)->where("job_type", "mono")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA3Mono += $minvoice->number_of_pages; $sumAmountA3Mono += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA3Mono = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "N"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, $minvoice->job_paper_size); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } else { # otherwise its an A4 job for color not A3 as may be indicated in datafile but A4 $cost = Price::where("job_id", 1)->where("job_type", "color")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA4Color += $minvoice->number_of_pages; $sumAmountA4Color += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA4Color = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "Y"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, $minvoice->job_paper_size); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } } else { $cost = Price::where("job_id", 1)->where("job_type", "color")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA4Color += $minvoice->number_of_pages; $sumAmountA4Color += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA4Color = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "Y"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, $minvoice->job_paper_size); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } } elseif (strtolower($minvoice->job_color) == "n") { if ($minvoice->job_paper_size == "A3" || ($minvoice->job_paper_size == "Tabloid" || $minvoice->job_paper_size == "Ledger")) { //&& if (strtolower($minvoice->device_name) == 'lexmark mx860') { //de $cost = Price::where("job_id", 1)->where("job_type", "mono")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA3Mono += $minvoice->number_of_pages; $sumAmountA3Mono += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA3Mono = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "N"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, $minvoice->job_paper_size); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } elseif (strtolower($minvoice->device_name) == 'lexmark x925' || strtolower($minvoice->device_name) == 'lexmark x950' || strtolower($minvoice->device_name) == 'lexmark x950de' || strtolower($minvoice->device_name) == 'lexmark x792de' || strtolower($minvoice->device_name) == 'lexmark cx510de') { $cost = Price::where("job_id", 1)->where("job_type", "mono")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA3Mono += $minvoice->number_of_pages; $sumAmountA3Mono += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA3Mono = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "N"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, $minvoice->job_paper_size); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } else { # otherwise its an A4 job for mono not A3 as may be indicated in datafile /** Print is a mono device*/ $cost = Price::where("job_id", 1)->where("job_type", "mono")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA4Mono += $minvoice->number_of_pages; $sumAmountA4Mono += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA4Mono = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "N"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, $minvoice->job_paper_size); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } } else { $cost = Price::where("job_id", 1)->where("job_type", "mono")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA4Mono += $minvoice->number_of_pages; $sumAmountA4Mono += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA4Mono = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "N"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, $minvoice->job_paper_size); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } } } elseif (strtolower($minvoice->device_type) == "m") { if ($minvoice->job_paper_size == "A3" || ($minvoice->job_paper_size == "Tabloid" || $minvoice->job_paper_size == "Ledger")) { #if device type is a mono and paper is A3 if (strtolower($minvoice->device_name) == 'lexmark mx860') { #de we need to be sure its an lexmark mx860de device $cost = Price::where("job_id", 1)->where("job_type", "mono")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA3Mono += $minvoice->number_of_pages; $sumAmountA3Mono += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA3Mono = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "N"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, $minvoice->job_paper_size); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } elseif (strtolower($minvoice->device_name) == 'lexmark x654de' || strtolower($minvoice->device_name) == 'lexmark x654' || (strtolower($minvoice->device_name) == 'lexmark t656' || strtolower($minvoice->device_name) == 'lexmark t656de')) { # otherwise its an A4 job for mono not A3 as may be indicated in script $paperid = DB::table("papers")->where("name", "A4")->pluck("id"); $cost = Price::where("job_id", 1)->where("job_type", "mono")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA4Mono += $minvoice->number_of_pages; $sumAmountA4Mono += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA4Mono = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "N"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, "A4"); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } } else { $cost = Price::where("job_id", 1)->where("job_type", "mono")->where("paper_id", $paperid)->pluck("price"); $amount = $cost * $minvoice->number_of_pages; $sumNoPagesA4Mono += $minvoice->number_of_pages; $sumAmountA4Mono += $amount; //not longer neccessory because cumulated pages could be multiplied by the unit cost $unitCostA4Mono = $cost; $objPHPExcel->getActiveSheet()->setCellValue('A' . $p, $site); $objPHPExcel->getActiveSheet()->setCellValue('B' . $p, $minvoice->user); $objPHPExcel->getActiveSheet()->setCellValue('C' . $p, $minvoice->ip); $objPHPExcel->getActiveSheet()->setCellValue('D' . $p, $minvoice->job_title); $objPHPExcel->getActiveSheet()->setCellValue('E' . $p, $minvoice->submit_date); $objPHPExcel->getActiveSheet()->setCellValue('F' . $p, $minvoice->final_date); $objPHPExcel->getActiveSheet()->setCellValue('G' . $p, $minvoice->final_action); $objPHPExcel->getActiveSheet()->setCellValue('H' . $p, $minvoice->final_site); $objPHPExcel->getActiveSheet()->setCellValue('I' . $p, $minvoice->number_of_pages); $objPHPExcel->getActiveSheet()->setCellValue('J' . $p, $cost); $objPHPExcel->getActiveSheet()->setCellValue('K' . $p, $amount); $objPHPExcel->getActiveSheet()->setCellValue('L' . $p, $minvoice->release_ip); $objPHPExcel->getActiveSheet()->setCellValue('M' . $p, $minvoice->release_user); $objPHPExcel->getActiveSheet()->setCellValue('N' . $p, $minvoice->release_method); $objPHPExcel->getActiveSheet()->setCellValue('O' . $p, "N"); // $objPHPExcel->getActiveSheet()->setCellValue('P' . $p, $minvoice->print_job_duplex); //duplex $objPHPExcel->getActiveSheet()->setCellValue('Q' . $p, $minvoice->job_paper_size); $objPHPExcel->getActiveSheet()->setCellValue('R' . $p, $minvoice->device_name); $objPHPExcel->getActiveSheet()->setCellValue('S' . $p, $minvoice->device_type); $objPHPExcel->getActiveSheet()->setCellValue('T' . $p, $minvoice->device_host); } } $printNo++; } $x++; } //End of invoicing loop $b = $p + 5; $sumAmountA3Color = $sumNoPagesA3Color * $unitCostA3Color; $sumAmountA3Mono = $sumNoPagesA3Mono * $unitCostA3Mono; $sumAmountA4Color = $sumNoPagesA4Color * $unitCostA4Color; $sumAmountA4Mono = $sumNoPagesA4Mono * $unitCostA4Mono; /** * Sum total of all net * amounts */ $sumTotal += $sumAmountA4Mono; $sumTotal += $sumAmountA3Mono; $sumTotal += $sumAmountA4Color; $sumTotal += $sumAmountA3Color; $a3Arr = ["a3mono" => ["nopages" => $sumNoPagesA3Mono, "unitcost" => $unitCostA3Mono, "netamount" => $sumAmountA3Mono], "a3color" => ["nopages" => $sumNoPagesA3Color, "unitcost" => $unitCostA3Color, "netamount" => $sumAmountA3Color]]; if ($sumNoPagesA3Mono > 0) { DB::table("invoice_detail")->insert(['invoice_id' => $bill->id, 'branch_id' => $minvoice->site, 'location' => $site, 'un_identity' => 'A3 Mono', 'description' => "Print / Copy Mono A3", 'no_of_pages' => $sumNoPagesA3Mono, 'cost_per_page' => $unitCostA3Mono, 'paper_size' => "A3", 'amount' => $sumAmountA3Mono, 'created_at' => date("Y-m-d H:i:s"), 'updated_at' => date("Y-m-d H:i:s")]); } if ($sumNoPagesA3Color > 0) { DB::table("invoice_detail")->insert(['invoice_id' => $bill->id, 'branch_id' => $minvoice->site, 'location' => $site, 'un_identity' => 'A3 Color', 'description' => "Print / Copy Color A3", 'no_of_pages' => $sumNoPagesA3Color, 'cost_per_page' => $unitCostA3Color, 'paper_size' => "A3", 'amount' => $sumAmountA3Color, 'created_at' => date("Y-m-d H:i:s"), 'updated_at' => date("Y-m-d H:i:s")]); } if ($sumNoPagesA4Mono > 0) { DB::table("invoice_detail")->insert(['invoice_id' => $bill->id, 'branch_id' => $minvoice->site, 'location' => $site, 'un_identity' => 'A4 Mono', 'description' => "Print / Copy Mono A4", 'no_of_pages' => $sumNoPagesA4Mono, 'cost_per_page' => $unitCostA4Mono, 'paper_size' => "A4", 'amount' => $sumAmountA4Mono, 'created_at' => date("Y-m-d H:i:s"), 'updated_at' => date("Y-m-d H:i:s")]); } if ($sumNoPagesA4Color > 0) { DB::table("invoice_detail")->insert(['invoice_id' => $bill->id, 'branch_id' => $minvoice->site, 'location' => $site, 'un_identity' => 'A4 Color', 'description' => "Print / Copy Color A4", 'no_of_pages' => $sumNoPagesA4Color, 'cost_per_page' => $unitCostA4Color, 'paper_size' => "A4", 'amount' => $sumAmountA4Color, 'created_at' => date("Y-m-d H:i:s"), 'updated_at' => date("Y-m-d H:i:s")]); } $a4Arr = ["a4mono" => ["nopages" => $sumNoPagesA4Mono, "unitcost" => $unitCostA4Mono, "netamount" => $sumAmountA4Mono], "a4color" => ["nopages" => $sumNoPagesA4Color, "unitcost" => $unitCostA4Color, "netamount" => $sumAmountA4Color]]; array_push($ArrMain, ["site" => $site, "a4" => $a4Arr, "a3" => $a3Arr, "sumTotal" => $sumTotal]); $total += $sumTotal; $stack->status = 1; $stack->update(); } // End of stack loop if (isset($input['date_from']) && isset($input['date_to'])) { $duration = date_format(date_create($input['date_from']), "d M. Y") . " - " . date_format(date_create($input['date_to']), "d M. Y"); } else { // $duration = date_format($dateFrom ,"d M. Y") ." - ". date_format($dateTo,"d M. Y"); } define('BUDGETS_DIR', public_path() . "/invoicexlxs/"); // I define this in a constants.php file $pdfPath = ""; if (!is_dir(BUDGETS_DIR)) { mkdir(BUDGETS_DIR, 0755, true); } $fileurl = "RJLEX_" . strtoupper(substr($company->name, 0, 3)) . "_" . date("Y-M-d") . "_" . time(); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007"); $objWriter->save(BUDGETS_DIR . $fileurl . ".xlsx"); $outputName = $fileurl . ".pdf"; // str_random is a [Laravel helper](http://laravel.com/docs/helpers#strings) $pdfPath = BUDGETS_DIR . $outputName; $bill->pdf_url = $outputName; // File::put($pdfPath, PDF::loadView($pdf, 'A4', 'portrait')->output()); $bill->subtotal = $total; $bill->tax = 5 / 100 * $total; $bill->total = $total + $bill->tax; $bill->invoice_date = date("Y-m-d H:i:s"); $bill->duration = $duration; $bill->file_url = $fileurl . ".xlsx"; $bill->update(); $input["invoice"] = $bill; $invoiceItems = DB::table("invoice_detail")->where("invoice_id", $bill->id)->groupBy("location")->get(); $input["invoiceItems"] = $invoiceItems; //$pdf = new PDF($dompdf, $config, $app['files'], $app['view']); $pdf = App::make('dompdf.wrapper'); //$pdf->set_base_path(public_path()."/bootstrap");->download($outputName) $pdf->loadView('invoicing.invoicepdf', $input)->setPaper('a4')->setOrientation('landscape')->save($pdfPath); //return $pdf->download($outputName); return View("invoicing.invoice", ["companies" => Company::all(), 'invoice' => $bill, "total" => $total, "company" => $company, "branches" => Branch::where("company_id", $company->id)->get(), "invoiceDatum" => $ArrMain, "title" => "Invoice"]); }
/** * Check if payment term is passed for given bill. * * @param int $billId * @return bool */ public static function paymentTermPassed($billId) { if (Bill::where('id', $billId)->where('payment_term', '!=', '0000-00-00')->where('payment_term', '<', date('Y-m-d'))->count()) { return true; } return false; }
public function editTrips($id) { $trip = Trip::findOrFail($id); $bookings = Booking::where('trip_id', $id)->get(); $bills = Bill::where('trip_id', $id)->get(); $billSum = Bill::where('trip_id', $id)->sum('amountCHF'); $countPassenger = Booking::where('trip_id', $id)->sum('countPasanger'); $income = $countPassenger * $trip->preis; return view('adminTripEdit', ['trip' => $trip, 'bookings' => $bookings, 'bills' => $bills, 'billSum' => $billSum, 'income' => $income]); }