/**
* 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]);
}
