public function create($id)
{
$client = Client::find($id);
//created new route /projects/create/{id}
//$clients = Client::lists('first_name', 'id'); //sends an array of 'first_name' => 'id'
return view('projects.create', compact('client'));
}
public function handleAction(Request $request)
{
$action = $request->input('_action');
if ($action == 'createClient') {
//Creation :
Client::create($request->all());
// FLash messaging :
flash()->success('Opération réussie!', 'Client créé avec succès.');
} else {
if ($_POST['_action'] == 'getClientByID') {
$id = $_POST['_uid'];
$client = Client::where('id', $id)->with('files')->first();
return response(['status' => 'success', 'client' => $client], 200);
} else {
if ($_POST['_action'] == 'editClient') {
$id = $_POST['id'];
$client = Client::find($id);
$client->lastname = $_POST['lastname'];
$client->firstname = $_POST['firstname'];
$client->email = $_POST['email'];
$client->street = $_POST['street'];
$client->postal_code = $_POST['postal_code'];
$client->city = $_POST['city'];
$client->vat = $_POST['tva'];
$client->mobile = $_POST['mobile'];
$client->office = $_POST['office'];
$client->fax = $_POST['fax'];
$client->save();
flash()->success('Opération réussie!', 'Client modifé avec succès.');
} else {
}
}
}
return redirect('/clients');
}
public function destroy($id)
{
$client = Client::find($id);
$client->delete();
$clients = Client::all();
return view('client.list')->with(['success' => 'Cliente excluído com sucesso!', 'clients' => $clients]);
}
/**
* Display a listing of client's albums.
*
* @return Response
*/
public function index($id)
{
$client = Client::find($id);
$albums = $client->albums;
$title = $client->first_name . ' ' . $client->last_name . ' Albums';
return view('admin.client_albums_index')->with('title', $title)->with('id', $id)->with('albums', $albums);
}
/**
* Display the specified resource.
*
* @param RentalAgreement $agreement
* @return Response
* @internal param int $id
*/
public function show(RentalAgreement $agreement)
{
$address = Address::find($agreement->property_id);
$client = Client::find($agreement->client_id);
$owner = Client::find($agreement->owner_id);
return view('agreement.showAgreement', compact('agreement', 'address', 'client', 'owner'));
}
/**
* Get the name of the user
*/
public function name()
{
if ($this->Type == 1) {
return Worker::find($this->TypeId)->Name;
}
return Client::find($this->TypeId)->Name;
}
/**
* Get the validation rules that apply to the request.
*
* @return array
*/
public function rules()
{
$client = false;
if ($this->method() == 'PATCH') {
$routeAction = $this->route()->getAction();
$routeParameters = $this->route()->parameters();
$cid = false;
if (isset($routeParameters['clientId'])) {
$cid = $routeParameters['clientId'];
} else {
if (isset($routeParameters['one'])) {
$cid = $routeParameters['one'];
}
}
$client = \App\Client::find($cid);
if (!$client) {
dd('error');
}
}
switch ($this->method()) {
case 'GET':
case 'DELETE':
return [];
case 'PUT':
return ['name' => 'required|unique:clients,name'];
case 'PATCH':
return ['name' => 'required|unique:clients,name,' . $client->id];
default:
return [];
break;
}
}
/**
* Show the form for creating a new resource.
*
* @return Response
*/
public function create(Request $request, $client_id = null)
{
$project_client = Client::find($client_id);
$clients = $request->user()->clients;
$view = $request->ajax() ? 'projects.create-modal' : 'projects.create';
return view($view, compact('clients', 'client_id'))->with(['client' => @$project_client]);
}
/**
* Display the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function show($id)
{
$client = Client::findOrFail($id);
$projects = Client::find($id)->projects()->orderBy('id', 'desc')->get();
$invoices = Client::find($id)->invoices()->orderBy('id', 'desc')->get();
$quotes = Client::find($id)->quotes()->orderBy('id', 'desc')->get();
$client->client_id = $client->id + 999;
foreach ($invoices as $invoice) {
$issue_date = Carbon::parse($invoice->issue_date);
$due_date = Carbon::parse($invoice->due_date);
$now = Carbon::now();
if ($now > $due_date) {
$invoice->is_overdue = true;
} else {
$invoice->is_overdue = false;
}
$invoice->readable_specific_id = $invoice->client_specific_id;
if ($invoice->client_specific_id < 10) {
$invoice->readable_specific_id = sprintf("%02d", $invoice->client_specific_id);
}
$invoice->terms_diff = $issue_date->diffInDays($due_date);
}
foreach ($quotes as $quote) {
if ($quote->client_specific_id < 10) {
$quote->client_specific_id = sprintf("%02d", $quote->client_specific_id);
}
}
foreach ($projects as $project) {
$project->latest_activity = $project->project_activity()->latest()->first();
}
$totalPaid = $client->invoices()->paid()->sum('amount');
$totalOutstanding = $client->invoices()->active()->sum('amount');
return view('app.client', ['client' => $client, 'projects' => $projects, 'invoices' => $invoices, 'quotes' => $quotes, "total_paid" => $totalPaid, 'total_outstanding' => $totalOutstanding]);
}
public function edit(Request $request, $id)
{
$client = \App\Client::find($id);
$client->name = $request->input('name');
$client->email = $request->input('email');
$client->save();
return redirect()->route('clients.details', $id);
}
public function comments()
{
$client_data = Client::find(Auth::user()->id);
$branch_data = $client_data->branch()->first();
//get the client branch information
$comments = RestaurantFeedback::where('comment', '!=', '')->orderBy('id', 'desc')->take(6)->get();
return view('client.comments', compact('branch_data', 'comments'));
}
/**
* Update the specified resource in storage.
*
* @param \Illuminate\Http\Request $request
* @param int $id
* @return \Illuminate\Http\Response
*/
public function update(ClientRequest $request, $id)
{
$Client = Client::find($id);
$Client->fill($request->all());
$Client->save();
Flash::success('Se ha editado a ' . $Client->nombre . '.');
return redirect()->route('clientes.index');
}
/**
* delete client
* @param $id
* @return \Illuminate\Http\JsonResponse
*/
public function delete($id)
{
$client = Client::find($id);
if ($client == null) {
return response()->json(['error' => "Client not found"]);
}
$client->delete();
return response()->json(['success' => "Client deleted"]);
}
/**
* Remove the specified resource from storage.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function destroy($id)
{
$client = Client::find($id);
if ($client->campaigns()->count()) {
return redirect()->back()->with('error', "{$client->name} has active campaigns and cannot be deleted");
}
$client->delete();
return redirect()->route('admin.clients.index')->with('message', "{$client->name} deleted.");
}
public function deleteDelete($clientId)
{
$client = \App\Client::find($clientId);
if (!$client) {
\Flash::error('Client not found');
return redirect('clients');
}
$client->delete();
\Flash::success('Client Deleted');
return redirect('clients');
}
function lookHistory()
{
if (isset($_SESSION['authorization'])) {
$client_d = ClientDetail::where('client_id', Input::get('id'))->orderBy('id', 'DESC')->get();
$client = Client::find(Input::get('id'));
$name = $client->name . ' ' . $client->last_name;
return view('clients.historyAppointment', ['client_d' => $client_d, 'name' => $name]);
} else {
return Redirect::to('./sayfa-bulunamadi');
}
}
/**
* Store a newly created resource in storage.
*
* @return Response
*/
public function store()
{
if (\Input::get('id')) {
$client = Client::find(\Input::get('id'));
$client->fill(\Input::all());
} else {
$client = new Client(\Input::all());
}
if ($client->save()) {
return $client;
}
}
/**
* Display the specified resource.
*
* @param int $id
* @return Response
*/
public function show($id)
{
$property = array();
$owner = Client::find($id);
$address = $owner->addresses()->get();
$bankDetails = $owner->bankDetails()->get();
$propertyd = $owner->property()->get();
foreach ($propertyd as $item) {
array_push($property, $item->addresses()->get());
}
return view('showOwner', compact('owner', 'address', 'bankDetails', 'property'));
}
function reservationName($reservation)
{
if ($reservation->CreditorType == 2) {
return Institution::find($reservation->CreditorId)->Name;
} else {
if ($reservation->CreditorId != 0) {
return Client::find($reservation->CreditorId)->Name;
} else {
return 'No declarado';
}
}
}
/**
* Update the specified resource in storage.
*
* @param \Illuminate\Http\Request $request
* @param int $id
* @return \Illuminate\Http\Response
*/
public function update($id)
{
//Actualiza un registro por id
$client = Client::find($id);
$client->name = Request::input('name');
$client->lastname = Request::input('lastname');
$client->phone = Request::input('phone');
$client->ofPhone = Request::input('ofPhone');
$client->mensaje = Request::input('mensaje');
$client->subject = Request::input('subject');
$client->enterprise = Request::input('enterprise');
$client->save();
return $client;
}
protected function authenticated()
{
/* UPDATING OVERDUE */
//all collectibles.. if the due date and current year is the same, then if it is a week past the due date week, it is overdue
$invoices = SalesInvoice::whereRaw('(YEAR(now()) = YEAR(due_date)) AND (week(now()) - week(due_date) >= 1) AND (status != "Collected" AND status != "Pending" AND status != "Cancelled")');
//all collectibles.. if the due date and current year is NOT the same, then if the week of due date is greater than current week (meaning next year na), it is overdue
$invoicesYears = SalesInvoice::whereRaw('(YEAR(now()) - YEAR(due_date) >= 1) AND (wEEK(due_Date) - WEEK(now()) >= 1) AND (status != "Collected" AND status != "Pending" AND status != "Cancelled")');
//if client has a sales invoice overdue for 4 months or 120 days.. make the client blacklisted.
$invoicesForClient = SalesInvoice::whereRaw('(datediff(now(), due_date) >= 120) and (status != "Collected" AND status != "Pending" AND status != "Cancelled")')->get();
$invoices->update(['status' => "Overdue"]);
$invoicesYears->update(['status' => "Overdue"]);
foreach ($invoicesForClient as $invoice) {
$client = Client::find($invoice->client_id);
$client->update(['status' => "Blacklisted"]);
}
// $overdues = \DB::table('sales_invoices')->whereRaw('status = "Overdue"');
$OverdueClients = DB::SELECT("SELECT DISTINCT c.id as 'id' FROM clients c JOIN sales_invoices si on si.client_id = c.id WHERE si.status = 'Overdue'");
$today = Carbon::today();
$mondayOf = Carbon::now()->startOfWeek();
// if($today == $mondayOf)
// {
foreach ($OverdueClients as $OverdueClient) {
$collectionLogs = DB::SELECT("SELECT COUNT(id) as id FROM collection_logs WHERE client_id='{$OverdueClient->id}' AND date = '{$mondayOf}' AND action = 'Call and Send SOA Overdue'");
// foreach ($collectionLogs as $collectionLog)
// {
if ($collectionLogs[0]->id == 0) {
$cLog = new CollectionLog();
$cLog->date = $mondayOf;
$cLog->action = 'Call and Send SOA Overdue';
$cLog->client_id = $OverdueClient->id;
$cLog->status = 'To Do';
$cLog->save();
$overdues = DB::SELECT("SELECT id FROM sales_invoices where status= 'Overdue' AND client_id = '{$OverdueClient->id}'");
foreach ($overdues as $overdue) {
$sicl = new SalesInvoiceCollectionLog();
$sicl->sales_invoice_id = $overdue->id;
$sicl->client_id = $cLog->client_id;
$sicl->collection_log_id = $cLog->id;
$sicl->save();
}
}
//}
}
//}
return redirect()->action('DashboardController@index');
}
function update()
{
$id = Input::get('id');
$client = array("name" => Input::get('ad'), "last_name" => Input::get('soyad'), "email" => Input::get('email'), "tel" => Input::get('tel'), "app_date" => Input::get('tarih'), "hour_id" => Input::get('saat'), "doctor_id" => Input::get('doktor'), "message" => Input::get('mesaj'), "confirmation" => 1);
$update = Client::where('id', $id)->update($client);
echo $update;
if ($update) {
$client = Client::find($id);
$_SESSION['mesaj'] = '<div class="form-signin" style="background-color:pink;"><center><p style="color:green;">Randevu Onaylandı/Değisiklik yapıldı</p></center></div>';
return view('clients.appointment', ['client' => $client]);
} else {
$client = Client::find($id);
$_SESSION['mesaj'] = '<div class="form-signin" style="background-color:pink;"><center><p style="color:red;">Randevu Onaylanmadı/Değisiklik yapılmadı.</p></center></div>';
return view('clients.appointment', ['client' => $client]);
}
return view('clients.appointment', ['client' => $client]);
}
/**
* Get the validation rules that apply to the request.
*
* @return array
*/
public function rules()
{
switch ($this->method()) {
case 'POST':
return ['name' => 'required', 'telephone_number' => 'required', 'address' => 'required', 'tin' => 'required|numeric|unique:clients', 'credit_limit' => 'required'];
case 'PATCH':
$client = Client::find($this->segment(2));
//this gets the second segment in the url which is the id of the client
if ($this->get('tin') == $client['tin']) {
return ['name' => 'required', 'telephone_number' => 'required', 'address' => 'required', 'tin' => 'required|numeric|unique:clients,tin,' . $this->segment(2), 'credit_limit' => 'required'];
} else {
return ['name' => 'required', 'telephone_number' => 'required', 'address' => 'required', 'tin' => 'required|numeric|unique:clients', 'credit_limit' => 'required'];
}
default:
break;
}
}
public function index($id)
{
$client_id = $id;
$client = Client::find($client_id);
if (is_null($client)) {
return redirect()->back()->with('status', 'Could not locate reference to Inquiry');
}
$inquiry_id = $client->inquiry_id;
$demographics = DB::table('demographics')->where('eie', '!=', '1')->where('client_id', '=', $client_id)->orderBy('id', 'desc')->get();
$payors = DB::table('payors')->where('eie', '!=', '1')->where('client_id', '=', $client_id)->orderBy('id', 'desc')->get();
$contacts = DB::table('contacts')->where('eie', '!=', '1')->where('client_id', '=', $client_id)->orderBy('id', 'desc')->get();
$refsrcs = DB::table('contacts')->where('eie', '!=', '1')->where('isrefsrc', '=', '1')->where('client_id', '=', $client_id)->orderBy('id', 'desc')->get();
$assessments = DB::table('assessments')->where('eie', '!=', '1')->where('client_id', '=', $client_id)->orderBy('id', 'desc')->get();
$admissions = DB::table('admissions')->where('eie', '!=', '1')->where('client_id', '=', $client_id)->orderBy('id', 'desc')->get();
//$ice = Ice::find($episode_id);
//$ices = DB::table('ice')->where('eie','!=','1')->where('inquiry_id','=',$inquiry_id)->orderBy('id','desc')->get();
return view('client/index')->with('client', $client)->with('demographics', $demographics)->with('payors', $payors)->with('contacts', $contacts)->with('refsrcs', $refsrcs)->with('assessments', $assessments)->with('admissions', $admissions);
}
public function add(Request $request)
{
$user = \Session::get('user');
if (!$user->can('项目签约')) {
abort(401);
}
$sub = SubProduct::find($request->input('product_id'));
$client = Client::find($request->input('client_id'));
$project = new Project();
$project->product()->associate($sub);
$project->client()->associate($client);
$project->vip = (bool) ($request->input('vip') == 'on');
$project->signed_status = $request->input('signed_status');
$project->ref_no = $request->input('ref_no');
$project->name = $request->input('name');
$project->seller = $request->input('seller');
$project->contact_user = $request->input('contact_user');
$signed_time = $request->input('signed_time');
if (!$signed_time) {
$signed_time = null;
} else {
$signed_time = \Carbon\Carbon::createFromFormat('Y/m/d', $signed_time)->format('Y-m-d H:i:s');
}
$project->signed_time = $signed_time;
// 签约时间
$service_unit = $request->input('service_unit');
$service_value = $request->input('service_value');
# 维保时间
$project->service_unit = $service_unit;
$project->service_value = $service_value;
$project->description = $request->input('description');
if ($project->save()) {
foreach ($sub->modules as $module) {
$project->modules()->save($module);
}
foreach ($sub->params as $param) {
$project->params()->save($param, ['value' => $param->pivot->value]);
}
Clog::add($project, '签约项目');
Clog::add($project->client, '签约项目', [$project->name], Clog::LEVEL_WARNING);
\Log::notice(strtr('客户项目签约: 用户(%name[%id]) 签约了项目: (%project_name[%project_id]), 客户: (%client_name[%client_id], 子产品: (%product_name[%product_id])', ['%name' => $user->name, '%id' => $user->id, '%project_name' => $project->name, '%project_id' => $project->id, '%client_name' => $client->name, '%client_id' => $client->id, '%product_name' => $sub->name, '%product_id' => $sub->id]));
return redirect(route('project.profile', ['id' => $project->id]))->with('message_content', '签约成功!')->with('message_type', 'info');
}
}
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
//\DB::table('sales_invoices')->whereRaw('(week(now()) - week(due_date) >= 1) and (status != "Collected")')->update(['status' => "Overdue"]);
// $invoices = DB::table('sales_invoices')->whereRaw('(week(now()) - week(due_date) >= 1) and (status != "Collected")');
// $invoices = DB::SELECT("SELECT * FROM sales_invoices WHERE (week(now()) - week(due_Date) >= 1) and (status != 'Collected')");
$invoices = SalesInvoice::whereRaw('(week(now()) - week(due_date) >= 1) and (status != "Collected")');
$invoicesForClient = SalesInvoice::whereRaw('(week(now()) - week(due_date) >= 1) and (status != "Collected")')->get();
$invoices->update(['status' => "Overdue"]);
foreach ($invoicesForClient as $invoice) {
$client = Client::find($invoice->client_id);
$client->update(['status' => "Blacklisted"]);
}
// $overdues = \DB::table('sales_invoices')->whereRaw('status = "Overdue"');
$overdues = DB::SELECT("SELECT * FROM sales_invoices where status = 'Overdue'");
$today = Carbon::today();
$mondayOf = Carbon::now()->startOfWeek();
if ($today == $mondayOf) {
foreach ($overdues as $overdue) {
$cLog = new CollectionLog();
$cLog->date = $mondayOf;
$cLog->action = 'Call and Send SOA Overdue';
$cLog->client_id = $overdue->client_id;
$cLog->status = 'To Do';
$cLog->save();
$sicl = new SalesInvoiceCollectionLog();
$sicl->sales_invoice_id = $overdue->id;
$sicl->client_id = $cLog->client_id;
$sicl->collection_log_id = $cLog->id;
$sicl->save();
}
}
// $reason = new Reason;
// $reason->reason = 'WEW';
// $reason->save();
//problem is if saturday, it will be 6-7 so it will subtract
//\DB::table('sales_invoices')->whereRaw('((date_add(due_date,interval 6-dayofweek(due_date) day)) < (date_add(date(now()),interval 6-dayofweek(date(now())) day))) and (status != "Collected")')->update(['status'=>"Overdue"]);
}
/**
* Store a newly created resource in storage.
*
* @param StoreAddressPostRequest $request
* @return Response
*/
public function store(StoreAddressPostRequest $request)
{
$address = new Address(array('unit' => $request->unit, 'street' => $request->street, 'postCode' => $request->postCode, 'city' => $request->city, 'state' => $request->state, 'country' => $request->country));
switch (Session::get('AddRole')) {
case "tenant":
$client = Client::find(Session::get('ClientInsertedId'));
$client->addresses()->save($address);
Session::flash('flash_message', 'Address successfully added! ');
return redirect()->action('ClientController@index');
break;
case "owner":
$client = Client::find(Session::get('ClientInsertedId'));
$client->addresses()->save($address);
Session::flash('flash_message', 'Address successfully added! ');
return redirect()->action('PropertyController@store', Session::get('ClientInsertedId'));
break;
case "property":
$property = Property::find(Session::get('PropertyInsertedId'));
$property->addresses()->save($address);
return redirect()->action('OwnerController@index');
break;
}
return redirect('home');
}
/**
* Remove the specified client from storage.
*
* @param int $id
* @return Response
*/
public function destroy($id)
{
$client = Client::find($id);
if (count($client->orders)) {
return redirect()->back()->with('message', FlashMessage::DisplayAlert('Cannot delete a client that has an order.', 'success'));
} else {
$albums = $client->albums;
foreach ($albums as $album) {
//delete folder and Photos from server
$this->dispatch(new DeleteFolderFileCommand(public_path("assets/images/client_albums/" . $album->id)));
unlink(public_path($album->album_cover_photo));
//delete from database
ClientAlbumPhoto::where("client_album_id", "=", $album->id)->delete();
$album->delete();
}
if ($client->profile_photo) {
unlink(public_path($client->profile_photo));
}
$user = $client->user;
$client->delete();
$user->delete();
return redirect('/admin/dashboard/clients');
}
}
public function generate()
{
$input = Request::all();
$counter = 1;
$reportType = $input['report_type'];
$salesCounter = 0;
$counterClientBreakdown = 0;
$counterClientLabel = 0;
$counterClientData = 0;
if ($reportType == "sales") {
$monthStart = $input['select_monthFrom'];
$monthEnd = $input['select_monthTo'];
$yearStart = $input['yearFrom'];
$yearEnd = $input['yearTo'];
$monthStartName = date("F", mktime(0, 0, 0, $monthStart, 10));
$monthEndName = date("F", mktime(0, 0, 0, $monthEnd, 10));
$results = DB::select("SELECT SUM(total_amount) as 'total' FROM sales_invoices si\n WHERE MONTH(si.date) >= '{$monthStart}' AND MONTH(si.date) <= '{$monthEnd}'\n AND YEAR(si.date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}'");
$resultsClient = DB::select("SELECT name, SUM(total_amount) as 'total' FROM clients c\n JOIN sales_invoices si ON si.client_id = c.id\n WHERE MONTH(si.date) >= '{$monthStart}' AND MONTH(si.date) <= '{$monthEnd}'\n AND YEAR(si.date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}' GROUP BY c.id");
$resultsSales = DB::select("SELECT username, SUM(total_amount) as 'total' FROM users u\n JOIN clients c ON c.user_id = u.id\n JOIN sales_invoices si ON si.client_id = c.id \n WHERE MONTH(si.date) >= '{$monthStart}' AND MONTH(si.date) <= '{$monthEnd}'\n AND YEAR(si.date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}' GROUP BY u.id");
Excel::create('Sales Report ' . \Carbon\Carbon::today()->format('m-d-y'), function ($excel) use($results, $resultsClient, $resultsSales, $counter, $monthStartName, $monthEndName, $salesCounter, $counterClientBreakdown, $counterClientLabel, $counterClientData, $yearStart, $yearEnd) {
$excel->sheet('Data', function ($sheet) use($results, $resultsClient, $resultsSales, $counter, $monthEndName, $monthStartName, $salesCounter, $counterClientBreakdown, $counterClientLabel, $counterClientData, $yearStart, $yearEnd) {
$sheet->row($counter, array('Sales Report'));
$counter++;
$sheet->row($counter, array('For ' . $monthStartName . ' ' . $yearStart . ' To ' . $monthEndName . ' ' . $yearEnd));
$counter++;
$sheet->row($counter, array('Total Sales: ', 'P' . $results[0]->total));
$counter++;
$counter++;
$sheet->row($counter, array('Sale Employee Breakdown:'));
//styling for 'Sales Employee Breakdown'
$sheet->cells('A5', function ($cells) {
$cells->setFontSize(14);
$cells->setFontWeight('bold');
});
$counter++;
$sheet->row($counter, array('Username', 'Total Amount'));
$counter++;
foreach ($resultsSales as $sales) {
$sheet->row($counter, array($sales->username, $sales->total));
$counter++;
}
$salesCounter = $counter - 1;
$counter += 10;
$sheet->row($counter, array('Client Breakdown: '));
$counterClientBreakdown = $counter;
$counter++;
$sheet->row($counter, array('Client Name', 'Total Amount'));
$counterClientLabel = $counter;
//$counter++;
$counterClientData = $counter + 1;
foreach ($resultsClient as $client) {
$counter++;
$sheet->row($counter, array($client->name, $client->total));
//$counter++;
}
//styling 'Sales Report' -> bold
$sheet->mergeCells('A1:B1');
$sheet->cells('A1', function ($cells) {
$cells->setFontSize(16);
$cells->setFontWeight('bold');
});
//styling for total sales
$sheet->cells('A3:B3', function ($cells) {
$cells->setFontSize(14);
$cells->setFontWeight('bold');
});
//styling for 'Client Breakdown'
$sheet->cells('A' . $counterClientBreakdown, function ($cells) {
$cells->setFontSize(14);
$cells->setFontWeight('bold');
});
//create Sales Employee chart
if (Count($resultsSales) > 0) {
//Data Series Label
$dsl = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$B$6', NULL, 1));
$xal = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$A$7:$A$' . $salesCounter, NULL, count($resultsSales)));
$dsv = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$B$7:$B$' . $salesCounter, NULL, count($resultsSales)));
$ds = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_BARCHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv) - 1), $dsl, $xal, $dsv);
$pa = new \PHPExcel_Chart_PlotArea(NULL, array($ds));
$legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
$title = new \PHPExcel_Chart_Title('Sales by Employee');
$chart = new \PHPExcel_Chart('chart1', $title, $legend, $pa, true, 0, NULL, NULL);
$chart->setTopLeftPosition('E5');
$chart->setBottomRightPosition('L16');
$sheet->addChart($chart);
}
//create Client Breakdown chart
if (Count($resultsClient) > 0) {
//Data Series Label
$dsl2 = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$B$' . $counterClientLabel, NULL, 1));
$xal2 = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$A$' . $counterClientData . ':$A$' . $counter, NULL, count($resultsClient)));
$dsv2 = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$B$' . $counterClientData . ':$B$' . $counter, NULL, count($resultsClient)));
$ds2 = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_BARCHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv2) - 1), $dsl2, $xal2, $dsv2);
$pa2 = new \PHPExcel_Chart_PlotArea(NULL, array($ds2));
$legend2 = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
$title2 = new \PHPExcel_Chart_Title('Sales by Client');
$chart = new \PHPExcel_Chart('chart2', $title2, $legend2, $pa2, true, 0, NULL, NULL);
$chart->setTopLeftPosition('E19');
$chart->setBottomRightPosition('L30');
$sheet->addChart($chart);
}
});
})->export('xlsx');
} else {
if ($reportType == "collection") {
$monthStart = $input['select_monthFrom'];
$monthEnd = $input['select_monthTo'];
$yearStart = $input['yearFrom'];
$yearEnd = $input['yearTo'];
$monthStartName = date("F", mktime(0, 0, 0, $monthStart, 10));
$monthEndName = date("F", mktime(0, 0, 0, $monthEnd, 10));
$counter = 1;
$results = DB::select("SELECT sum(total_amount) as 'total', count(*) as 'numCount' FROM sales_invoices si\n WHERE (si.status = 'delivered' OR si.status = 'overdue')\n AND MONTH(si.due_date) >= '{$monthStart}' AND MONTH(si.due_date) <= '{$monthEnd}'\n AND YEAR(si.due_date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}'");
$resultsCollected = DB::select("SELECT sum(total_amount) as 'total', count(*) as 'numCount' FROM sales_invoices si\n WHERE si.status = 'collected'\n AND MONTH(si.due_date) >= '{$monthStart}' AND MONTH(si.due_date) <= '{$monthEnd}'\n AND YEAR(si.due_date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}'");
$resultsInvoices = DB::select("SELECT name, si_no, date, due_date, total_amount, si.status FROM sales_invoices si\n JOIN clients c ON si.client_id = c.id\n WHERE MONTH(si.due_date) >= '{$monthStart}' AND MONTH(si.due_date) <= '{$monthEnd}'\n AND YEAR(si.due_date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}' ORDER BY name, si.status");
Excel::create('Collection Report ' . \Carbon\Carbon::today()->format('m-d-y'), function ($excel) use($results, $counter, $monthStartName, $monthEndName, $yearStart, $yearEnd, $resultsCollected, $resultsInvoices) {
$excel->sheet('Data', function ($sheet) use($results, $counter, $monthStartName, $monthEndName, $yearStart, $yearEnd, $resultsCollected, $resultsInvoices) {
$sheet->row($counter, array('Collection Report'));
$counter++;
$sheet->row($counter, array('For ' . $monthStartName . ' ' . $yearStart . ' To ' . $monthEndName . ' ' . $yearEnd));
$counter++;
$sheet->row($counter, array('Total Amount Collected: ', $resultsCollected[0]->total));
$counter++;
$sheet->row($counter, array('Total Collectibles: ', $results[0]->total));
$counter += 2;
$sheet->row($counter, array('List of Collectibles: '));
$counter++;
$sheet->row($counter, array('Client Name', 'Sales Invoice Number', 'Date', 'Due Date', 'Total Amount', 'Status'));
$counter++;
foreach ($resultsInvoices as $result) {
$sheet->row($counter, array($result->name, $result->si_no, $result->date, $result->due_date, $result->total_amount, $result->status));
$counter++;
}
// foreach($results as $result)
// {
// $sheet->row($counter, array('Total Collectibles: ', $result->total));
// }
//style
//style for item name
$sheet->mergeCells('A1:B1');
$sheet->cells('A1', function ($cells) {
$cells->setFontSize(14);
$cells->setFontWeight('bold');
});
//style for total amount collected
$sheet->cells('A3:B3', function ($cells) {
$cells->setFontSize(12);
$cells->setFontWeight('bold');
});
//style for total collectibles
$sheet->cells('A4:B4', function ($cells) {
$cells->setFontSize(12);
$cells->setFontWeight('bold');
});
});
})->export('xlsx');
} else {
if ($reportType == "item") {
$itemID = $input['item'];
$item = Item::find($itemID);
$itemName = $item->name;
$monthStart = $input['select_monthFrom'];
$monthEnd = $input['select_monthTo'];
$yearStart = $input['yearFrom'];
$yearEnd = $input['yearTo'];
$monthStartName = date("F", mktime(0, 0, 0, $monthStart, 10));
$monthEndName = date("F", mktime(0, 0, 0, $monthEnd, 10));
$counterLabel = 0;
$counterData = 0;
$results = DB::select("SELECT name, supplier_id FROM price_logs pl\n JOIN suppliers s ON pl.supplier_id = s.id \n WHERE item_id = '{$itemID}' AND MONTH(pl.date) >= '{$monthStart}' \n AND MONTH(pl.date) <= '{$monthEnd}' AND YEAR(pl.date) >= '{$yearStart}' AND YEAR(pl.date) <= '{$yearEnd}'\n GROUP BY pl.supplier_id");
$itemResults = DB::select("SELECT name, sum(quantity) as 'totalSold', sum(total_price) as 'totalAmount' FROM invoice_items ii\n JOIN items i ON ii.item_id = i.id\n WHERE ii.item_id = '{$itemID}'");
Excel::create('Item Report-' . $itemName . ' ' . \Carbon\Carbon::today()->format('m-d-y'), function ($excel) use($results, $counter, $itemID, $monthStart, $monthStartName, $monthEnd, $monthEndName, $yearStart, $yearEnd, $itemResults, $itemName, $counterLabel, $counterData) {
$excel->sheet('Data', function ($sheet) use($results, $counter, $itemID, $monthStart, $monthStartName, $monthEnd, $monthEndName, $yearStart, $yearEnd, $itemResults, $itemName, $counterLabel, $counterData) {
$sheet->row($counter, array('Item Report'));
//style for Item Report Title
$sheet->cells('A1', function ($cells) {
$cells->setFontSize(16);
$cells->setFontWeight('bold');
});
$counter++;
$sheet->row($counter, array('For ' . $monthStartName . ' ' . $yearStart . ' To ' . $monthEndName . ' ' . $yearEnd));
$counter++;
$sheet->row($counter, array('Item Name: ' . $itemName));
//style for item name
$sheet->mergeCells('A1:B1');
$sheet->cells('A' . $counter, function ($cells) {
$cells->setFontSize(14);
$cells->setFontWeight('bold');
});
$counter++;
$sheet->row($counter, array('Total Quantity Sold: ', $itemResults[0]->totalSold));
//style for quantity sold
$sheet->cells('A' . $counter . ':B' . $counter, function ($cells) {
$cells->setFontSize(12);
$cells->setFontWeight('bold');
});
$counter++;
$sheet->row($counter, array('Total Revenue Generated: ', 'P' . $itemResults[0]->totalAmount));
//style for revenue generated
$sheet->cells('A' . $counter . ':B' . $counter, function ($cells) {
$cells->setFontSize(12);
$cells->setFontWeight('bold');
});
$counter += 2;
foreach ($results as $result) {
$sheet->row($counter, array($result->name));
//style for quantity sold
$sheet->cells('A' . $counter, function ($cells) {
$cells->setFontWeight('bold');
});
$counter++;
$sheet->row($counter, array('Date', 'Unit Price'));
$counterLabel = $counter;
$counterData = $counter + 1;
$logs = DB::select("SELECT date, price FROM price_logs pl \n WHERE item_id = '{$itemID}' AND supplier_id = '{$result->supplier_id}'\n AND MONTH(pl.date) >= '{$monthStart}' \n AND MONTH(pl.date) <= '{$monthEnd}' AND YEAR(pl.date) >= '{$yearStart}' AND YEAR(pl.date) <= '{$yearEnd}'");
foreach ($logs as $log) {
$counter++;
$sheet->row($counter, array($log->date, $log->price));
}
//Create chart
if (count($logs) > 0) {
//Data Series Label
$dsl = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$B$' . $counterLabel, NULL, 1));
$xal = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$A$' . $counterData . ':$A$' . $counter, NULL, count($logs)));
$dsv = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$B$' . $counterData . ':$B$' . $counter, NULL, count($logs)));
$ds = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_LINECHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv) - 1), $dsl, $xal, $dsv);
$pa = new \PHPExcel_Chart_PlotArea(NULL, array($ds));
$legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
$title = new \PHPExcel_Chart_Title('Price History');
$chart = new \PHPExcel_Chart('chart1', $title, $legend, $pa, true, 0, NULL, NULL);
$counter += 5;
$chart->setTopLeftPosition('E' . $counterLabel);
$chart->setBottomRightPosition('K' . $counter);
$sheet->addChart($chart);
}
$counter += 2;
}
});
})->export('xlsx');
} else {
if ($reportType == "client") {
//$client = Client::all();
$clientid = $input['client'];
$clientName = Client::find($clientid)->name;
$results = DB::select("SELECT c.name AS 'clientName', i.name, SUM(quantity) AS 'total' FROM invoice_items ii\n JOIN items i on ii.item_id = i.id \n JOIN sales_invoices si ON ii.sales_invoice_id = si.id\n JOIN clients c ON si.client_id = c.id\n WHERE c.id = '{$clientid}'\n GROUP BY item_id ORDER BY sum(quantity) DESC LIMIT 3");
//$client = DB::table('clients')->get();
Excel::create('Client Report ' . \Carbon\Carbon::today()->format('m-d-y'), function ($excel) use($results, $counter, $clientName) {
$excel->sheet('Data', function ($sheet) use($results, $counter, $clientName) {
$sheet->row($counter, array('Client Report'));
$counter++;
//$sheet->row($counter, array('Client Name', $results[0]->clientName));
$sheet->row($counter, array('Client Name', $clientName));
$counter++;
$counter++;
//add space
$sheet->row($counter, array('Most Bought Items:'));
$counter++;
$sheet->row($counter, array('Item Name', 'Total Quantity'));
$counter++;
foreach ($results as $result) {
$sheet->row($counter, array($result->name, $result->total));
$counter++;
}
//Add the styling
$sheet->mergeCells('A1:B1');
$sheet->cells('A1', function ($cells) {
$cells->setFontSize(16);
$cells->setFontWeight('bold');
});
$sheet->cells('A2:B2', function ($cells) {
$cells->setFontSize(16);
});
$sheet->cells('A4', function ($cells) {
$cells->setFontWeight('bold');
});
//Create chart
if (count($results) > 0) {
//Data Series Label
$dsl = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$B$5', NULL, 1));
$xal = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$A$6:$A$' . $counter, NULL, 3));
$dsv = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$B$6:$B$' . $counter, NULL, 3));
$ds = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_BARCHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv) - 1), $dsl, $xal, $dsv);
$pa = new \PHPExcel_Chart_PlotArea(NULL, array($ds));
$legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
$title = new \PHPExcel_Chart_Title('Most Bought');
$chart = new \PHPExcel_Chart('chart1', $title, $legend, $pa, true, 0, NULL, NULL);
$chart->setTopLeftPosition('A10');
$chart->setBottomRightPosition('C25');
$sheet->addChart($chart);
}
});
})->export('xlsx');
}
}
}
}
}
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
// Get contracts that are still active.
$contracts = Contract::where('State', '!=', 'used')->where('State', '!=', 'cancelled')->where('State', '!=', 'paid')->get();
foreach ($contracts as $contract) {
// Check if we have visited this month.
if ($contract->Visited) {
// Check if we should reset this.
if ($contract->QuotaInterval == 'mensuales') {
if ('01' == date('d')) {
$contract->Visited = false;
$contract->save();
}
} else {
if ($contract->QuotaInterval == 'quincenales') {
if ('01' == date('d')) {
$contract->Visited = false;
$contract->save();
} else {
if ('16' == date('d')) {
$contract->Visited = false;
$contract->save();
}
}
} else {
if (date('D') == 'Mon') {
$contract->Visited = false;
$contract->save();
}
}
}
}
// Check if we need to organize a visit for this contract.
if (!$contract->Visited) {
if ($contract->StartDate < date('Y-m-d')) {
$visitDates = explode(',', $contract->PaymentDates);
foreach ($visitDates as $date) {
if ($date == date('d', strtotime("+3 days"))) {
// Check if we already have a visit programmed for this contract.
$transports = Transport::where('Type', '=', '8')->where('ReasonId', '=', $contract->Id)->where('Date', '=', date('Y-m-d', strtotime("+3 days")))->get();
if (count($transports) == 0) {
// Get an appropriate vehicle for this.
$vehicles = Vehicle::where('BranchId', '=', $contract->BranchId)->where('Repair', '=', false)->get();
$vehicle = null;
foreach ($vehicles as $v) {
if (!$vehicle) {
$vehicle = $v;
}
// In this case the smaller the vehicle the more suitable it should be.
// TODO: This could be improved.
if ($v->Type < $vehicle->Type) {
$vehicle = $v;
}
}
if (!$vehicle) {
// In this case we don't have a vehicle and the provider doesn't provide delivery so we must notify an administrator.
$users = User::where('UserLevel', '=', 1)->get();
foreach ($users as $admin) {
Notification::create(array('UserId' => $admin->Id, 'Created' => date('Y-m-d H:i:s'), 'Reason' => 'Aergia no fue capaz de organizar el cobro para el contrato ' . $contract->Code . '. Por favor revisar y organizar el cobro de la cuota de este contrato.', 'Url' => '/contract/' . $contract->Id, 'Seen' => false));
}
} else {
// Get location of client.
$client = Client::find($contract->ClientId);
$location = Location::find($client->LocationId);
$transport = Transport::create(array('Date' => date('Y-m-d', strtotime("+3 day")), 'Time' => '00:00:00', 'VehicleId' => $vehicle->Id, 'DriverId' => 0, 'StartLatitude' => 0, 'StartLongitude' => 0, 'Journey' => '[]', 'EndLatitude' => $location->Latitude, 'EndLongitude' => $location->Longitude, 'EndAddress' => $client->Address, 'Distance' => 0, 'ReasonId' => $contract->Id, 'Type' => 8, 'State' => 2, 'Order' => 0, 'Depreciation' => 0));
// Inform creditor of when you shall collect payment.
if ($client->Email != '') {
Mail::send('emails.ai.contractReminder', ['contract' => $contract, 'transport' => $transport], function ($message) use($contract, $client) {
$message->to($client->Email);
$message->subject('Cobro de Cuota de Contrato: ' . $contract->Code);
});
}
}
}
}
}
}
}
// TODO: Not sure if I want to do something in these cases.
switch ($contract->State) {
case 'usedpending':
break;
case 'late':
break;
}
}
}
