public function index()
{
$provinceids = Branch::where('isheadquarter', true)->distinct()->lists('provinceid');
$provinces = Province::whereIn('id', $provinceids)->orderBy('name', 'asc')->get(['id', 'name']);
$provinceselectlist = array();
array_push($provinceselectlist, ':เลือกจังหวัด');
foreach ($provinces as $item) {
array_push($provinceselectlist, $item->id . ':' . $item->name);
}
$carmodels = CarModel::orderBy('name', 'asc')->get(['id', 'name']);
$carmodelselectlist = array();
array_push($carmodelselectlist, ':เลือกแบบ');
foreach ($carmodels as $item) {
array_push($carmodelselectlist, $item->id . ':' . $item->name);
}
$carsubmodelids = Car::distinct()->lists('carsubmodelid');
$carsubmodels = CarSubModel::whereIn('id', $carsubmodelids)->orderBy('name', 'asc')->get(['id', 'name']);
$carsubmodelselectlist = array();
array_push($carsubmodelselectlist, ':เลือกรุ่น');
foreach ($carsubmodels as $item) {
array_push($carsubmodelselectlist, $item->id . ':' . $item->name);
}
$colorids = Car::distinct()->lists('colorid');
$colors = Color::whereIn('id', $colorids)->orderBy('code', 'asc')->get(['id', 'code', 'name']);
$colorselectlist = array();
array_push($colorselectlist, ':เลือกสี');
foreach ($colors as $item) {
array_push($colorselectlist, $item->id . ':' . $item->code . ' - ' . $item->name);
}
$defaultProvince = '';
if (Auth::user()->isadmin == false) {
$defaultProvince = Auth::user()->branchid == null ? '' : Auth::user()->branch->provinceid;
}
return view('car', ['provinceselectlist' => implode(";", $provinceselectlist), 'carmodelselectlist' => implode(";", $carmodelselectlist), 'carsubmodelselectlist' => implode(";", $carsubmodelselectlist), 'colorselectlist' => implode(";", $colorselectlist), 'defaultProvince' => $defaultProvince]);
}
public function index()
{
/*$branchs = Branch::orderBy('name', 'asc')->get(['id', 'name']);
$branchselectlist = array();
array_push($branchselectlist,':เลือกสาขา');
foreach($branchs as $item){
array_push($branchselectlist,$item->id.':'.$item->name);
}*/
$provinces = Province::orderBy('name', 'asc')->get(['id', 'name']);
$provinceselectlist = array();
array_push($provinceselectlist, ':เลือกจังหวัด');
foreach ($provinces as $item) {
array_push($provinceselectlist, $item->id . ':' . $item->name);
}
$occupations = Occupation::orderBy('name', 'asc')->get(['id', 'name']);
$occupationselectlist = array();
array_push($occupationselectlist, ':เลือกอาชีพ');
foreach ($occupations as $item) {
array_push($occupationselectlist, $item->id . ':' . $item->name);
}
$amphurids = Customer::distinct()->lists('amphurid');
$amphurs = Amphur::whereIn('id', $amphurids)->orderBy('name', 'asc')->get(['id', 'name']);
$amphurselectlist = array();
array_push($amphurselectlist, ':เลือกเขต/อำเภอ');
foreach ($amphurs as $item) {
array_push($amphurselectlist, $item->id . ':' . $item->name);
}
$districtids = Customer::distinct()->lists('districtid');
$districts = District::whereIn('id', $districtids)->orderBy('name', 'asc')->get(['id', 'name']);
$districtselectlist = array();
array_push($districtselectlist, ':เลือกตำบล/แขวง');
foreach ($districts as $item) {
array_push($districtselectlist, $item->id . ':' . $item->name);
}
$employees = Employee::all(['id', 'firstname', 'lastname']);
$employeeselectlist = array();
array_push($employeeselectlist, ':เลือกพนักงาน');
foreach ($employees as $emp) {
array_push($employeeselectlist, $emp->id . ':' . $emp->firstname . ' ' . $emp->lastname);
}
$carmodels = CarModel::all(['id', 'name']);
$carmodelselectlist = array();
array_push($carmodelselectlist, ':เลือกแบบ');
foreach ($carmodels as $cm) {
array_push($carmodelselectlist, $cm->id . ':' . $cm->name);
}
$defaultProvince = '';
if (Auth::user()->isadmin == false) {
$defaultProvince = Auth::user()->branchid == null ? '' : Auth::user()->branch->provinceid;
}
return view('customer', ['provinceselectlist' => implode(";", $provinceselectlist), 'amphurselectlist' => implode(";", $amphurselectlist), 'districtselectlist' => implode(";", $districtselectlist), 'carmodelselectlist' => implode(";", $carmodelselectlist), 'employeeselectlist' => implode(";", $employeeselectlist), 'occupationselectlist' => implode(";", $occupationselectlist), 'defaultProvince' => $defaultProvince]);
}
public function index()
{
if (!$this->hasPermission($this->menuPermissionName)) {
return view($this->viewPermissiondeniedName);
}
$provinces = Province::whereHas('branchs', function ($q) {
$q->where('isheadquarter', true);
})->orderBy('name', 'asc')->get(['id', 'name']);
$provinceselectlist = array();
array_push($provinceselectlist, ':เลือกจังหวัด');
foreach ($provinces as $item) {
array_push($provinceselectlist, $item->id . ':' . $item->name);
}
if (Auth::user()->isadmin) {
$customers = Customer::has('redLabels')->orderBy('firstname', 'asc')->orderBy('lastname', 'asc')->get(['id', 'title', 'firstname', 'lastname']);
} else {
$customers = Customer::where('provinceid', Auth::user()->provinceid)->has('redLabels')->orderBy('firstname', 'asc')->orderBy('lastname', 'asc')->get(['id', 'title', 'firstname', 'lastname']);
}
$customerselectlist = array();
foreach ($customers as $item) {
array_push($customerselectlist, $item->id . ':' . $item->title . ' ' . $item->firstname . ' ' . $item->lastname);
}
if (Auth::user()->isadmin) {
$cars = Car::has('redLabel')->orderBy('chassisno', 'asc')->orderBy('engineno', 'asc')->get(['id', 'chassisno', 'engineno']);
} else {
$cars = Car::where('provinceid', Auth::user()->provinceid)->has('redLabel')->orderBy('chassisno', 'asc')->orderBy('engineno', 'asc')->get(['id', 'chassisno', 'engineno']);
}
$carselectlist = array();
array_push($carselectlist, ':เลือกรถ');
foreach ($cars as $item) {
array_push($carselectlist, $item->id . ':' . $item->chassisno . '/' . $item->engineno);
}
if (Auth::user()->isadmin) {
$carpreemptions = CarPreemption::has('redlabelhistories')->orderBy('bookno', 'asc')->orderBy('no', 'asc')->get(['id', 'bookno', 'no', 'buyercustomerid', 'salesmanemployeeid']);
} else {
$carpreemptions = CarPreemption::where('provinceid', Auth::user()->provinceid)->has('redlabelhistories')->orderBy('bookno', 'asc')->orderBy('no', 'asc')->get(['id', 'bookno', 'no', 'buyercustomerid', 'salesmanemployeeid']);
}
$carpreemptionselectlist = array();
array_push($carpreemptionselectlist, ':เลือกการจอง');
foreach ($carpreemptions as $item) {
$buyercustomer = Customer::find($item->buyercustomerid);
$buyercustomername = $buyercustomer->title . ' ' . $buyercustomer->firstname . ' ' . $buyercustomer->lastname;
$salesmanemployee = Employee::find($item->salesmanemployeeid);
$salesmanemployeename = $salesmanemployee->title . ' ' . $salesmanemployee->firstname . ' ' . $salesmanemployee->lastname;
array_push($carpreemptionselectlist, $item->id . ':' . str_pad($item->bookno . '/' . $item->no, strlen($item->bookno . '/' . $item->no) + 15, " ") . str_pad($buyercustomername, strlen($buyercustomername) + 15, " ") . $salesmanemployeename);
}
$defaultProvince = '';
if (Auth::user()->isadmin == false) {
$defaultProvince = Auth::user()->provinceid;
}
return view('redlabel', ['provinceselectlist' => implode(";", $provinceselectlist), 'customerselectlist' => implode(";", $customerselectlist), 'carselectlist' => implode(";", $carselectlist), 'carpreemptionselectlist' => implode(";", $carpreemptionselectlist), 'defaultProvince' => $defaultProvince]);
}
public function index()
{
if (!$this->hasPermission($this->menuPermissionName)) {
return view($this->viewPermissiondeniedName);
}
$provinces = Province::orderBy('name', 'asc')->get(['id', 'name']);
$provinceselectlist = array();
array_push($provinceselectlist, ':เลือกจังหวัด');
foreach ($provinces as $item) {
array_push($provinceselectlist, $item->id . ':' . $item->name);
}
$amphurs = Amphur::has('branchs')->orderBy('name', 'asc')->get(['id', 'name']);
$amphurselectlist = array();
array_push($amphurselectlist, ':เลือกเขต/อำเภอ');
foreach ($amphurs as $item) {
array_push($amphurselectlist, $item->id . ':' . $item->name);
}
$districts = District::has('branchs')->orderBy('name', 'asc')->get(['id', 'name']);
$districtselectlist = array();
array_push($districtselectlist, ':เลือกตำบล/แขวง');
foreach ($districts as $item) {
array_push($districtselectlist, $item->id . ':' . $item->name);
}
$taxamphurs = Amphur::has('taxBranchs')->orderBy('name', 'asc')->get(['id', 'name']);
$taxamphurselectlist = array();
array_push($taxamphurselectlist, ':เลือกเขต/อำเภอ');
foreach ($taxamphurs as $item) {
array_push($taxamphurselectlist, $item->id . ':' . $item->name);
}
$taxdistricts = District::has('taxBranchs')->orderBy('name', 'asc')->get(['id', 'name']);
$taxdistrictselectlist = array();
array_push($taxdistrictselectlist, ':เลือกตำบล/แขวง');
foreach ($taxdistricts as $item) {
array_push($taxdistrictselectlist, $item->id . ':' . $item->name);
}
return view('settings.branch', ['provinceselectlist' => implode(";", $provinceselectlist), 'amphurselectlist' => implode(";", $amphurselectlist), 'districtselectlist' => implode(";", $districtselectlist), 'taxprovinceselectlist' => implode(";", $provinceselectlist), 'taxamphurselectlist' => implode(";", $taxamphurselectlist), 'taxdistrictselectlist' => implode(";", $taxdistrictselectlist)]);
}
public function index()
{
$provinces = Province::orderBy('name', 'asc')->get(['id', 'name']);
$provinceselectlist = array();
array_push($provinceselectlist, ':เลือกจังหวัด');
foreach ($provinces as $item) {
array_push($provinceselectlist, $item->id . ':' . $item->name);
}
$amphurids = Branch::distinct()->lists('amphurid');
$amphurs = Amphur::whereIn('id', $amphurids)->orderBy('name', 'asc')->get(['id', 'name']);
$amphurselectlist = array();
array_push($amphurselectlist, ':เลือกเขต/อำเภอ');
foreach ($amphurs as $item) {
array_push($amphurselectlist, $item->id . ':' . $item->name);
}
$districtids = Branch::distinct()->lists('districtid');
$districts = District::whereIn('id', $districtids)->orderBy('name', 'asc')->get(['id', 'name']);
$districtselectlist = array();
array_push($districtselectlist, ':เลือกตำบล/แขวง');
foreach ($districts as $item) {
array_push($districtselectlist, $item->id . ':' . $item->name);
}
return view('settings.branch', ['provinceselectlist' => implode(";", $provinceselectlist), 'amphurselectlist' => implode(";", $amphurselectlist), 'districtselectlist' => implode(";", $districtselectlist)]);
}
public function view($id)
{
if (!$this->hasPermission($this->menuPermissionName)) {
return view($this->viewPermissiondeniedName);
}
$model = CarPayment::find($id);
$carpreemption = CarPreemption::find($model->carpreemptionid);
$carpreemptionselectlist = array();
$carpreemptionselectlist[$carpreemption->id] = $carpreemption->bookno . '/' . $carpreemption->no;
$model->bookno = $carpreemption->bookno;
$model->no = $carpreemption->no;
$model->purchasetype = $carpreemption->purchasetype;
if ($carpreemption->purchasetype == 0) {
$purchasetype0 = true;
$purchasetype1 = false;
} elseif ($carpreemption->purchasetype == 1) {
$purchasetype0 = false;
$purchasetype1 = true;
}
if ($carpreemption->carobjectivetype == 0) {
$carobjectivetype0 = true;
$carobjectivetype1 = false;
} elseif ($carpreemption->carobjectivetype == 1) {
$carobjectivetype0 = false;
$carobjectivetype1 = true;
}
if ($carpreemption->registrationtype == 0) {
$registrationtype0 = true;
$registrationtype1 = false;
$registrationtype2 = false;
} elseif ($carpreemption->registrationtype == 1) {
$registrationtype0 = false;
$registrationtype1 = true;
$registrationtype2 = false;
} elseif ($carpreemption->registrationtype == 2) {
$registrationtype0 = false;
$registrationtype1 = false;
$registrationtype2 = true;
}
$customer = Customer::find($carpreemption->buyercustomerid);
$model->customer = $customer->title . ' ' . $customer->firstname . ' ' . $customer->lastname;
$model->customer2 = $customer->title . ' ' . $customer->firstname . ' ' . $customer->lastname;
$carmodel = CarModel::find($carpreemption->carmodelid);
$carsubmodel = CarSubModel::find($carpreemption->carsubmodelid);
$model->carmodel = $carmodel->name . '/' . $carsubmodel->name;
$color = Color::find($carpreemption->colorid);
$model->carcolor = $color->code . ' - ' . $color->name;
$pricelist = Pricelist::find($carpreemption->pricelistid);
$model->carprice = $pricelist->sellingpricewithaccessories + $carpreemption->colorprice;
$carselectlist = array();
$item = Car::find($model->carid);
$carselectlist[$item->id] = $item->chassisno . '/' . $item->engineno;
$model->installments = $carpreemption->installments;
$model->interest = $carpreemption->interest;
if ($carpreemption->purchasetype == 1) {
$finacecompany = FinaceCompany::find($carpreemption->finacecompanyid);
$model->finacecompany = $finacecompany->name;
}
if ($carpreemption->purchasetype == 0) {
$model->down = number_format($model->carprice - $carpreemption->discount, 2, '.', '');
$model->yodjud = number_format(0, 2, '.', '');
$model->yodjudwithinsurancepremium = number_format(0, 2, '.', '');
if ($model->overrideopenbill != null && $model->overrideopenbill != '') {
$model->openbill = $model->overrideopenbill;
} else {
$model->openbill = number_format($model->carprice - $carpreemption->discount, 2, '.', '');
}
$model->realprice = number_format($model->carprice - $carpreemption->discount, 2, '.', '');
$model->payinadvanceamount = number_format(0, 2, '.', '');
} else {
$model->down = $carpreemption->down;
$model->yodjud = number_format($model->carprice - $carpreemption->discount - $model->down + $carpreemption->accessories + $model->accessoriesfeeincludeinyodjud, 2, '.', '');
$model->yodjudwithinsurancepremium = number_format($model->yodjud + $model->insurancepremium, 2, '.', '');
$model->openbill = number_format($model->yodjud + $model->down, 2, '.', '');
$model->realprice = number_format($model->carprice - $carpreemption->discount - $carpreemption->subdown, 2, '.', '');
if ($model->firstinstallmentpay) {
$model->firstinstallmentpayamount = number_format($model->amountperinstallment, 2, '.', '');
} else {
$model->firstinstallmentpayamount = number_format(0, 2, '.', '');
}
$model->payinadvanceamount = number_format($model->installmentsinadvance * $model->amountperinstallment, 2, '.', '');
if ($carpreemption->subsidisefree) {
$model->subsidise = number_format(0, 2, '.', '');
} else {
$model->subsidise = $carpreemption->subsidise;
}
}
$model->accessoriesfee = $carpreemption->accessoriesfee;
$insurancecompanies = InsuranceCompany::orderBy('name', 'asc')->get(['id', 'name']);
$insurancecompanyselectlist = array();
$insurancecompanyselectlist[null] = 'เลือกบริษัท';
foreach ($insurancecompanies as $item) {
$insurancecompanyselectlist[$item->id] = $item->name;
}
if ($carpreemption->insurancefeefree) {
$model->insurancefee = number_format(0, 2, '.', '');
} else {
$model->insurancefee = $carpreemption->insurancefee;
}
if ($carpreemption->compulsorymotorinsurancefeefree) {
$model->compulsorymotorinsurancefee = number_format(0, 2, '.', '');
} else {
$model->compulsorymotorinsurancefee = $carpreemption->compulsorymotorinsurancefee;
}
if ($carpreemption->carobjectivetype == 0) {
$registerprovince = Province::find($carpreemption->registerprovinceid);
$model->registerprovince = $registerprovince->name;
} else {
$model->registerprovince = null;
}
$model->registrationtype = $carpreemption->registrationtype;
if ($carpreemption->registrationfeefree) {
$model->registrationfee = number_format(0, 2, '.', '');
} else {
$model->registrationfee = $carpreemption->registrationfee;
}
if ($carpreemption->carobjectivetype == 0) {
$redlabelhistory = Redlabelhistory::where('carpreemptionid', $carpreemption->id)->first();
if ($redlabelhistory != null) {
$redlabel = Redlabel::find($redlabelhistory->redlabelid);
$model->redlabel = $redlabel->no;
} else {
$model->redlabel = "ไม่มีป้าย";
}
} else {
$model->redlabel = null;
}
$model->cashpledgeredlabel = $carpreemption->cashpledgeredlabel;
if ($carpreemption->implementfeefree) {
$model->implementfee = number_format(0, 2, '.', '');
} else {
$model->implementfee = $carpreemption->implementfee;
}
$model->giveawaywithholdingtax = $carpreemption->giveawaywithholdingtax;
$model->otherfee = $carpreemption->otherfee;
$model->otherfeedetail = $carpreemption->otherfeedetail;
$model->otherfee2 = $carpreemption->otherfee2;
$model->otherfeedetail2 = $carpreemption->otherfeedetail2;
$model->otherfee3 = $carpreemption->otherfee3;
$model->otherfeedetail3 = $carpreemption->otherfeedetail3;
if ($model->firstinstallmentpay) {
$model->total = number_format($model->down + $model->amountperinstallment + $model->payinadvanceamount + $model->accessoriesfeeactuallypaid + $model->insurancefee + $model->compulsorymotorinsurancefee + $model->registrationfee + $model->cashpledgeredlabel + $model->implementfee + $model->giveawaywithholdingtax + $model->otherfee + $model->otherfee2 + $model->otherfee3, 2, '.', '');
} else {
$model->total = number_format($model->down + $model->payinadvanceamount + $model->accessoriesfeeactuallypaid + $model->insurancefee + $model->compulsorymotorinsurancefee + $model->registrationfee + $model->cashpledgeredlabel + $model->implementfee + $model->giveawaywithholdingtax + $model->otherfee + $model->otherfee2 + $model->otherfee3, 2, '.', '');
}
$model->subdown = $carpreemption->subdown;
$model->cashpledge = $carpreemption->cashpledge;
$model->oldcarprice = $carpreemption->oldcarprice;
$model->totalpayments = number_format($model->total - $model->subdown - $model->cashpledge - $model->oldcarprice, 2, '.', '');
$salesmanemployee = Employee::find($carpreemption->salesmanemployeeid);
$model->salesmanemployee = $salesmanemployee->title . ' ' . $salesmanemployee->firstname . ' ' . $salesmanemployee->lastname;
$approversemployee = Employee::find($carpreemption->approversemployeeid);
$model->approversemployee = $approversemployee->title . ' ' . $approversemployee->firstname . ' ' . $approversemployee->lastname;
$payeeemployeeselectlist = array();
if ($model->payeeemployeeid != null && $model->payeeemployeeid != '') {
$item = Employee::find($model->payeeemployeeid);
$payeeemployeeselectlist[$item->id] = $item->title . ' ' . $item->firstname . ' ' . $item->lastname;
} else {
$payeeemployeeselectlist[null] = '';
}
$model->date = date('d-m-Y', strtotime($model->date));
if ($model->overdueinstallmentdate1 != null && $model->overdueinstallmentdate1 != '') {
$model->overdueinstallmentdate1 = date('d-m-Y', strtotime($model->overdueinstallmentdate1));
}
if ($model->overdueinstallmentdate2 != null && $model->overdueinstallmentdate2 != '') {
$model->overdueinstallmentdate2 = date('d-m-Y', strtotime($model->overdueinstallmentdate2));
}
if ($model->overdueinstallmentdate3 != null && $model->overdueinstallmentdate3 != '') {
$model->overdueinstallmentdate3 = date('d-m-Y', strtotime($model->overdueinstallmentdate3));
}
if ($model->overdueinstallmentdate4 != null && $model->overdueinstallmentdate4 != '') {
$model->overdueinstallmentdate4 = date('d-m-Y', strtotime($model->overdueinstallmentdate4));
}
if ($model->overdueinstallmentdate5 != null && $model->overdueinstallmentdate5 != '') {
$model->overdueinstallmentdate5 = date('d-m-Y', strtotime($model->overdueinstallmentdate5));
}
if ($model->overdueinstallmentdate6 != null && $model->overdueinstallmentdate6 != '') {
$model->overdueinstallmentdate6 = date('d-m-Y', strtotime($model->overdueinstallmentdate6));
}
if ($model->oldcarpaydate != null && $model->oldcarpaydate != '') {
$model->oldcarpaydate = date('d-m-Y', strtotime($model->oldcarpaydate));
}
if ($model->deliverycardate != null && $model->deliverycardate != '') {
$model->deliverycardate = date('d-m-Y', strtotime($model->deliverycardate));
}
return view('carpaymentform', ['oper' => 'view', 'pathPrefix' => '../../', 'carpayment' => $model, 'carpreemptionselectlist' => $carpreemptionselectlist, 'carselectlist' => $carselectlist, 'insurancecompanyselectlist' => $insurancecompanyselectlist, 'payeeemployeeselectlist' => $payeeemployeeselectlist, 'purchasetype0' => $purchasetype0, 'purchasetype1' => $purchasetype1, 'carobjectivetype0' => $carobjectivetype0, 'carobjectivetype1' => $carobjectivetype1, 'registrationtype0' => $registrationtype0, 'registrationtype1' => $registrationtype1, 'registrationtype2' => $registrationtype2]);
}
public function carstock(Request $request)
{
$this->validate($request, ['date' => 'required'], ['date.required' => 'รายงาน สต็อครถ - วันที่ จำเป็นต้องเลือก']);
$input = $request->all();
$provinceid = $input['provinceid'];
$orderbytype = $input['orderbytype'];
$date = date('Y-m-d', strtotime($input['date']));
$province = Province::find($provinceid);
if ($orderbytype == 1) {
$orderbytext = 'วันที่รับรถ';
} else {
$orderbytext = 'แบบ/รุ่นและวันที่รับรถ';
}
$filename = 'สต็อครถ' . $province->name . '_เรียงตาม' . $orderbytext . '_' . date("d/m/Y");
Excel::create($filename, function ($excel) use($province, $orderbytype, $orderbytext, $date) {
// Set the title
$excel->setTitle('no title');
$excel->setCreator('no no creator')->setCompany('no company');
$excel->setDescription('report file');
$excel->sheet('sheet1', function ($sheet) use($province, $orderbytype, $orderbytext, $date) {
$results = DB::select('CALL report_carstock(' . $province->id . ',' . "'" . $date . "'" . ',' . $orderbytype . ')');
$carRequired = DB::select('CALL report_carstock_carrequired(' . $province->id . ',' . "'" . $date . "'" . ')');
$carTestDrives = DB::select('select * from car_test_drive where provinceid = ' . $province->id . ' and dodate <= ' . "'" . $date . "'");
$carTestUses = DB::select('select * from car_test_use where provinceid = ' . $province->id . ' and dodate <= ' . "'" . $date . "'");
$rsCount = count($results);
$matchedCount = 0;
$carRequiredCount = count($carRequired);
$carTestDriveCount = count($carTestDrives);
$carTestUseCount = count($carTestUses);
$maxRowCarTest = $carTestDriveCount;
if ($carTestUseCount > $carTestDriveCount) {
$maxRowCarTest = $carTestUseCount;
}
$sheet->setAutoSize(true);
$rowIndex = 0;
$rowIndex++;
$sheet->cells('A' . $rowIndex . ':R' . $rowIndex, function ($cells) {
$cells->setFontWeight('bold');
$cells->setFontSize(14);
});
$sheet->mergeCells('A' . $rowIndex . ':R' . $rowIndex);
$sheet->row($rowIndex, array('บริษัทสยามนิสสัน' . $province->name));
$rowIndex++;
$sheet->cells('A' . $rowIndex . ':R' . $rowIndex, function ($cells) {
$cells->setFontWeight('bold');
$cells->setFontSize(14);
});
$sheet->mergeCells('A' . $rowIndex . ':R' . $rowIndex);
$sheet->row($rowIndex, array('สต็อครถ ประจำวันที่ ' . date("d/m/Y")));
$rowIndex++;
$sheet->cells('A' . $rowIndex . ':R' . $rowIndex, function ($cells) {
$cells->setFontWeight('bold');
$cells->setFontSize(14);
});
$sheet->mergeCells('A' . $rowIndex . ':R' . $rowIndex);
$sheet->row($rowIndex, array('เรียงตาม' . $orderbytext));
$rowIndex++;
$sheet->mergeCells('A' . $rowIndex . ':R' . $rowIndex);
$rowIndex++;
$sheet->row($rowIndex, array('ลำดับ', 'คันที่', 'วันที่ออก Do', 'วันที่รับรถเข้า', 'จำนวนวัน', 'เลขเครื่อง', 'เลขตัวถัง', 'กุญแจ', 'แบบ', 'รุ่น', 'สี', 'จอด', 'วันที่แจ้งขาย', 'ชื่อลูกค้า', 'SALE', 'FN/สด', 'สถานะ', 'วันที่คาดส่ง'));
$sheet->cells('A' . $rowIndex . ':R' . $rowIndex, function ($cells) {
$cells->setFontWeight('bold');
$cells->setAlignment('center');
});
$sheet->setBorder('A' . $rowIndex . ':R' . $rowIndex, 'thin');
$rowNum = 1;
$modelGroup = '';
foreach ($results as $item) {
if ($item->custname != null && $item->custname != '') {
$matchedCount++;
}
if ($orderbytype == 2 && $item->model != $modelGroup) {
$modelGroup = $item->model;
$rowIndex++;
$sheet->cells('A' . $rowIndex . ':R' . $rowIndex, function ($cells) {
$cells->setFontWeight('bold');
});
$sheet->mergeCells('A' . $rowIndex . ':R' . $rowIndex);
$sheet->row($rowIndex, array($modelGroup));
}
$rowIndex++;
if ($item->receiveddate != null && $item->receiveddate != '') {
$item->receiveddate = date('d/m/Y', strtotime($item->receiveddate));
}
if ($item->datewantgetcar != null && $item->datewantgetcar != '') {
$item->datewantgetcar = date('d/m/Y', strtotime($item->datewantgetcar));
}
if ($item->notifysolddate != null && $item->notifysolddate != '') {
$item->notifysolddate = date('d/m/Y', strtotime($item->notifysolddate));
}
$sheet->row($rowIndex, array($rowNum, $item->no, date('d/m/Y', strtotime($item->dodate)), $item->receiveddate, $item->days, $item->engineno, $item->chassisno, $item->keyno, $item->model, $item->submodel, $item->color, $item->parklocation, $item->notifysolddate, $item->custname, $item->empname, $item->fn, $item->documentstatus, $item->datewantgetcar));
$sheet->cells('A' . $rowIndex . ':H' . $rowIndex, function ($cells) {
$cells->setAlignment('center');
});
if ($item->days > 360) {
$sheet->cell('E' . $rowIndex, function ($cell) {
$cell->setBackground('#ff99ff');
});
} elseif ($item->days > 180 && $item->days <= 360) {
$sheet->cell('E' . $rowIndex, function ($cell) {
$cell->setBackground('#00ff00');
});
} elseif ($item->days >= 90 && $item->days <= 180) {
$sheet->cell('E' . $rowIndex, function ($cell) {
$cell->setBackground('#ffff66');
});
}
$sheet->cell('K' . $rowIndex, function ($cell) {
$cell->setAlignment('center');
});
$sheet->cells('M' . $rowIndex . ':R' . $rowIndex, function ($cells) {
$cells->setAlignment('center');
});
$sheet->setBorder('A' . $rowIndex . ':R' . $rowIndex, 'thin');
$rowNum++;
}
//car test
$rowIndex += 2;
$sheet->row($rowIndex, array(null, 'ลำดับ', 'รถ TEST DRIVE', null, null, null, null, null, null, null, 'ลำดับ', 'รถ TEST ใช้งาน'));
$sheet->mergeCells('C' . $rowIndex . ':G' . $rowIndex);
$sheet->mergeCells('L' . $rowIndex . ':Q' . $rowIndex);
$sheet->cells('A' . $rowIndex . ':R' . $rowIndex, function ($cells) {
$cells->setFontWeight('bold');
$cells->setAlignment('center');
});
$sheet->setBorder('B' . $rowIndex . ':G' . $rowIndex, 'thin');
$sheet->setBorder('K' . $rowIndex . ':Q' . $rowIndex, 'thin');
$rowNum = 0;
for ($i = 0; $i < $maxRowCarTest; $i++) {
$rowIndex++;
$rowNum++;
if ($rowNum <= count($carTestDrives) && $rowNum <= count($carTestUses)) {
$carTestDrive = $carTestDrives[$i];
$carTestUse = $carTestUses[$i];
$sheet->row($rowIndex, array(null, $rowNum, $carTestDrive->model, null, $carTestDrive->submodel, $carTestDrive->color, null, null, null, null, $rowNum, $carTestUse->model, null, null, $carTestUse->submodel, $carTestUse->color));
$sheet->setBorder('B' . $rowIndex . ':G' . $rowIndex, 'thin');
$sheet->mergeCells('C' . $rowIndex . ':D' . $rowIndex);
$sheet->setBorder('K' . $rowIndex . ':Q' . $rowIndex, 'thin');
$sheet->mergeCells('L' . $rowIndex . ':N' . $rowIndex);
} elseif ($rowNum <= count($carTestDrives)) {
$carTestDrive = $carTestDrives[$i];
$sheet->row($rowIndex, array(null, $rowNum, $carTestDrive->model, null, $carTestDrive->submodel, $carTestDrive->color));
$sheet->setBorder('B' . $rowIndex . ':G' . $rowIndex, 'thin');
$sheet->mergeCells('C' . $rowIndex . ':D' . $rowIndex);
} else {
$carTestUse = $carTestUses[$i];
$sheet->row($rowIndex, array(null, null, null, null, null, null, null, null, null, null, $rowNum, $carTestUse->model, null, null, $carTestUse->submodel, $carTestUse->color));
$sheet->setBorder('K' . $rowIndex . ':Q' . $rowIndex, 'thin');
$sheet->mergeCells('L' . $rowIndex . ':N' . $rowIndex);
}
$sheet->cells('A' . $rowIndex . ':R' . $rowIndex, function ($cells) {
$cells->setAlignment('center');
});
}
$rowIndex += 2;
$sheet->row($rowIndex, array(null, null, null, null, null, null, null, null, 'สีชมพู คือ เกิน 1ปีขึ้นไป'));
$sheet->cell('i' . $rowIndex, function ($cell) {
$cell->setBackground('#ff99ff');
$cell->setAlignment('center');
});
$rowIndex++;
$sheet->row($rowIndex, array(null, null, null, null, null, null, null, null, 'สีเขียว คือ 6-12 เดือน'));
$sheet->cell('i' . $rowIndex, function ($cell) {
$cell->setBackground('#00ff00');
$cell->setAlignment('center');
});
$rowIndex++;
$sheet->row($rowIndex, array(null, null, null, null, null, null, null, null, 'สีเหลือง คือ 90-180 วัน'));
$sheet->cell('i' . $rowIndex, function ($cell) {
$cell->setBackground('#ffff66');
$cell->setAlignment('center');
});
$rowIndex += 2;
$sheet->row($rowIndex, array(null, null, null, null, 'STOCK', $rsCount, null, null, 'จับคู่', null, $matchedCount));
$sheet->cells('E' . $rowIndex . ':F' . $rowIndex, function ($cells) {
$cells->setFontWeight('bold');
$cells->setFontSize(14);
$cells->setAlignment('center');
});
$sheet->cell('I' . $rowIndex, function ($cell) {
$cell->setFontWeight('bold');
$cell->setFontSize(14);
});
$sheet->cell('K' . $rowIndex, function ($cell) {
$cell->setFontWeight('bold');
$cell->setFontSize(14);
$cell->setAlignment('center');
});
$rowIndex++;
$sheet->row($rowIndex, array(null, null, null, null, 'ฝากจอด', 0, null, null, 'ว่าง', null, $rsCount - $matchedCount));
$sheet->cells('E' . $rowIndex . ':F' . $rowIndex, function ($cells) {
$cells->setFontWeight('bold');
$cells->setFontSize(14);
$cells->setAlignment('center');
});
$sheet->cell('I' . $rowIndex, function ($cell) {
$cell->setFontWeight('bold');
$cell->setFontSize(14);
});
$sheet->cell('F' . $rowIndex, function ($cell) {
$cell->setBorder('none', 'none', 'thin', 'none');
});
$sheet->cell('K' . $rowIndex, function ($cell) {
$cell->setFontWeight('bold');
$cell->setFontSize(14);
$cell->setAlignment('center');
$cell->setBorder('none', 'none', 'thin', 'none');
});
$rowIndex++;
$sheet->row($rowIndex, array(null, null, null, null, null, $rsCount, null, null, null, null, $rsCount));
$sheet->cell('F' . $rowIndex, function ($cell) {
$cell->setFontColor('#ff0000');
$cell->setFontWeight('bold');
$cell->setFontSize(14);
$cell->setAlignment('center');
$cell->setBorder('none', 'none', 'double', 'none');
});
$sheet->cell('K' . $rowIndex, function ($cell) {
$cell->setFontColor('#ff0000');
$cell->setFontWeight('bold');
$cell->setFontSize(14);
$cell->setAlignment('center');
$cell->setBorder('none', 'none', 'double', 'none');
});
$rowIndex += 2;
$sheet->cells('A' . $rowIndex . ':R' . $rowIndex, function ($cells) {
$cells->setFontWeight('bold');
$cells->setFontSize(14);
});
$sheet->mergeCells('A' . $rowIndex . ':R' . $rowIndex);
$sheet->row($rowIndex, array('รถที่ต้องการหา'));
$rowIndex++;
$sheet->row($rowIndex, array('ลำดับ', 'แบบ', null, 'รุ่น', 'สี', 'ชื่อลูกค้า', 'SALE', 'FN/สด', 'สถานะ', 'ทำสัญญา', 'วันที่คาดส่ง', 'หมายเหตุ', null));
$sheet->mergeCells('B' . $rowIndex . ':C' . $rowIndex);
$sheet->mergeCells('L' . $rowIndex . ':N' . $rowIndex);
$sheet->cells('A' . $rowIndex . ':N' . $rowIndex, function ($cells) {
$cells->setFontWeight('bold');
$cells->setAlignment('center');
});
$sheet->setBorder('A' . $rowIndex . ':N' . $rowIndex, 'thin');
$rowNum = 0;
foreach ($carRequired as $item) {
$rowIndex++;
$rowNum++;
if ($item->contractdate != null && $item->contractdate != '') {
$item->contractdate = date('d/m/Y', strtotime($item->contractdate));
}
$sheet->row($rowIndex, array($rowNum, $item->model, null, $item->submodel, $item->color, $item->custname, $item->empname, $item->fn, $item->documentstatus, $item->contractdate, date('d/m/Y', strtotime($item->datewantgetcar)), $item->remark));
$sheet->mergeCells('B' . $rowIndex . ':C' . $rowIndex);
$sheet->mergeCells('L' . $rowIndex . ':N' . $rowIndex);
$sheet->cell('A' . $rowIndex, function ($cell) {
$cell->setAlignment('center');
});
$sheet->cells('E' . $rowIndex . ':K' . $rowIndex, function ($cells) {
$cells->setAlignment('center');
});
$sheet->setBorder('A' . $rowIndex . ':N' . $rowIndex, 'thin');
}
/*$data = array(
array('header1', 'header2','header3','header4','header5','header6','header7'),
array('data1', 'data2', 300, 400, 500, 0, 100),
array('data1', 'data2', 300, 400, 500, 0, 100),
array('data1', 'data2', 300, 400, 500, 0, 100),
array('data1', 'data2', 300, 400, 500, 0, 100),
array('data1', 'data2', 300, 400, 500, 0, 100),
array('data1', 'data2', 300, 400, 500, 0, 100)
);
$sheet->fromArray($data, null, 'A1', false, false);
$sheet->cells('A1:G1', function($cells) {
$cells->setBackground('#AAAAFF');
});*/
});
})->download('xlsx');
//return Redirect::to('/reporting');
}
public function import()
{
try {
$file = Input::file('file');
//$path = Input::file('pricelist')->getRealPath();
$temp = null;
Excel::load($file, function ($reader) use($temp) {
//$reader->dump();
$reader->skip(1);
// Loop through all rows
$reader->each(function ($row) {
$carBrand = CarBrand::where('name', 'NISSAN')->first();
$color = Color::where('code', trim($row->p))->first();
$carModel = CarModel::firstOrCreate(['name' => trim($row->c) . ' ' . trim($row->d), 'cartypeid' => $row->b, 'carbrandid' => $carBrand->id]);
$carSubModel = CarSubModel::firstOrCreate(['code' => trim($row->f), 'name' => trim($row->e), 'carmodelid' => $carModel->id]);
$car = Car::firstOrNew(['engineno' => trim($row->m), 'chassisno' => trim($row->n)]);
$car->datatype = 0;
$car->provinceid = trim($row->a);
$car->carmodelid = $carModel->id;
$car->carsubmodelid = $carSubModel->id;
$car->receivetype = trim($row->g);
$car->dealername = trim($row->h);
$car->no = trim($row->i);
$car->dodate = trim($row->j);
$car->dono = trim($row->k);
if ($row->l != null && $row->l != '') {
$car->receiveddate = trim($row->l);
}
if ($row->o != null && $row->o != '') {
$car->keyno = trim($row->o);
}
$car->colorid = $color->id;
$car->objective = trim($row->q);
if ($row->r != null && $row->r != '') {
$car->parklocation = trim($row->r);
}
if ($row->s != null && $row->s != '') {
$car->notifysolddate = trim($row->s);
}
if ($row->u != null && $row->u != '' && $row->v != null && $row->v != '') {
$customer = Customer::firstOrNew(['provinceid' => trim($row->a), 'title' => trim($row->u), 'firstname' => trim($row->v), 'lastname' => trim($row->w)]);
$customer->isreal = true;
if ($row->x != null && $row->x != '') {
$customer->phone1 = trim($row->x);
}
if ($row->y != null && $row->y != '') {
$customer->occupationid = trim($row->y);
}
if ($row->z != null && $row->z != '') {
$customer->birthdate = trim($row->z);
}
if ($row->aa != null && $row->aa != '') {
$customer->address = trim($row->aa);
}
$district = District::where('name', trim($row->ab))->first();
$amphur = Amphur::where('name', trim($row->ac))->first();
$province = Province::where('name', trim($row->ad))->first();
if ($district != null) {
$customer->districtid = $district->id;
}
if ($amphur != null) {
$customer->amphurid = $amphur->id;
}
if ($province != null) {
$customer->addprovinceid = $province->id;
}
if ($row->ad != null && $row->ad != '') {
$customer->zipcode = trim($row->ae);
}
$customer->save();
$car->issold = true;
$car->buyercustomerid = $customer->id;
if ($row->t != null && $row->t != '') {
$car->isdelivered = true;
}
}
$car->save();
});
});
} catch (Exception $e) {
return 'Message: ' . $e->getMessage();
}
return redirect()->action('CarController@index');
}
public function getbyid($id)
{
if (!$this->hasPermission($this->menuPermissionName)) {
return view($this->viewPermissiondeniedName);
}
$model = CarPreemption::find($id);
$customer = Customer::find($model->buyercustomerid);
$model->customer = $customer->title . ' ' . $customer->firstname . ' ' . $customer->lastname;
$carmodel = CarModel::find($model->carmodelid);
$carsubmodel = CarSubModel::find($model->carsubmodelid);
$model->carmodel = $carmodel->name . '/' . $carsubmodel->name;
$color = Color::find($model->colorid);
$model->carcolor = $color->code . ' - ' . $color->name;
$pricelist = Pricelist::find($model->pricelistid);
$model->carprice = $pricelist->sellingpricewithaccessories + $model->colorprice;
if (Auth::user()->isadmin) {
if ($model->carobjectivetype == 0) {
$cars = Car::doesntHave('carPayment')->where('objective', 0)->where('carmodelid', $model->carmodelid)->where('carsubmodelid', $model->carsubmodelid)->where('colorid', $model->colorid)->orderBy('chassisno', 'asc')->orderBy('engineno', 'asc')->get(['id', 'chassisno', 'engineno']);
} else {
$cars = Car::doesntHave('carPayment')->where('objective', '!=', 0)->where('carmodelid', $model->carmodelid)->where('carsubmodelid', $model->carsubmodelid)->where('colorid', $model->colorid)->orderBy('chassisno', 'asc')->orderBy('engineno', 'asc')->get(['id', 'chassisno', 'engineno']);
}
} else {
if ($model->carobjectivetype == 0) {
$cars = Car::where('provinceid', Auth::user()->provinceid)->doesntHave('carPayment')->where('objective', 0)->where('carmodelid', $model->carmodelid)->where('carsubmodelid', $model->carsubmodelid)->where('colorid', $model->colorid)->orderBy('chassisno', 'asc')->orderBy('engineno', 'asc')->get(['id', 'chassisno', 'engineno']);
} else {
$cars = Car::where('provinceid', Auth::user()->provinceid)->doesntHave('carPayment')->where('objective', '!=', 0)->where('carmodelid', $model->carmodelid)->where('carsubmodelid', $model->carsubmodelid)->where('colorid', $model->colorid)->orderBy('chassisno', 'asc')->orderBy('engineno', 'asc')->get(['id', 'chassisno', 'engineno']);
}
}
$model->cars = $cars;
if ($model->finacecompanyid != null && $model->finacecompanyid != '') {
$finacecompany = FinaceCompany::find($model->finacecompanyid);
$model->finacecompany = $finacecompany->name;
}
if ($model->interestratetypeid != null && $model->interestratetypeid != '') {
$interestratetype = InterestRateType::find($model->interestratetypeid);
$model->interestratetype = $interestratetype->name;
}
if ($model->purchasetype == 0) {
$model->yodjud = 0;
$model->realprice = $model->carprice - $model->discount;
} else {
$model->yodjud = $model->carprice - $model->discount - $model->down + $model->accessories;
$model->realprice = $model->carprice - $model->discount - $model->subdown;
}
$salesmanemployee = Employee::find($model->salesmanemployeeid);
$model->salesmanemployee = $salesmanemployee->title . ' ' . $salesmanemployee->firstname . ' ' . $salesmanemployee->lastname;
$approversemployee = Employee::find($model->approversemployeeid);
$model->approversemployee = $approversemployee->title . ' ' . $approversemployee->firstname . ' ' . $approversemployee->lastname;
if ($model->carobjectivetype == 0) {
$registerprovince = Province::find($model->registerprovinceid);
$model->registerprovince = $registerprovince->name;
$redlabelhistory = Redlabelhistory::where('carpreemptionid', $id)->first();
if ($redlabelhistory != null) {
$redlabel = Redlabel::find($redlabelhistory->redlabelid);
$model->redlabel = $redlabel->no;
} else {
$model->redlabel = "ไม่มีป้าย";
}
} else {
$model->redlabel = null;
}
//if($cust->birthdate != null)
//$cust->birthdate = date('d-m-Y', strtotime($cust->birthdate));
return $model;
}
public function getsubmodelandcolorbyid($id, $registrationtype, $registerprovinceid)
{
if (!$this->hasPermission($this->menuPermissionName)) {
return view($this->viewPermissiondeniedName);
}
$carmodel = CarModel::find($id);
$carmodelregister = CarModelRegister::where('carmodelid', $id)->where('provinceid', $registerprovinceid)->first();
if ($carmodelregister == null) {
$registercost = 0;
} else {
if ($registrationtype == 0) {
$registercost = $carmodelregister->individualregistercost;
} else {
if ($registrationtype == 1) {
$registercost = $carmodelregister->companyregistercost;
} else {
if ($registrationtype == 2) {
$registercost = $carmodelregister->governmentregistercost;
} else {
$registercost = null;
}
}
}
}
$actcharged = $carmodel->cartype->actcharged;
$carsubmodels = CarSubModel::where('carmodelid', $id)->orderBy('name', 'asc')->get(['id', 'name']);
$colors = Color::with(['carModelColors' => function ($query) use($id) {
$query->where('carmodelid', $id);
}])->whereHas('carModelColors', function ($q) use($id) {
$q->where('carmodelid', $id);
})->orderBy('code', 'asc')->get(['id', 'code', 'name']);
$provinces = Province::with(['carModelRegisters' => function ($query) use($id) {
$query->where('carmodelid', $id);
}])->whereHas('carModelRegisters', function ($q) use($id) {
$q->where('carmodelid', $id);
})->orderBy('name', 'asc')->get(['id', 'name']);
return ['carsubmodels' => $carsubmodels, 'colors' => $colors, 'actcharged' => $actcharged, 'registercost' => $registercost, 'registerprovinces' => $provinces];
}
public function index()
{
if (!$this->hasPermission($this->menuPermissionName)) {
return view($this->viewPermissiondeniedName);
}
$provinces = Province::whereHas('branchs', function ($q) {
$q->where('isheadquarter', true);
})->orderBy('name', 'asc')->get(['id', 'name']);
$provinceselectlist = array();
array_push($provinceselectlist, ':เลือกจังหวัด');
foreach ($provinces as $item) {
array_push($provinceselectlist, $item->id . ':' . $item->name);
}
$occupations = Occupation::orderBy('name', 'asc')->get(['id', 'name']);
$occupationselectlist = array();
array_push($occupationselectlist, ':เลือกอาชีพ');
foreach ($occupations as $item) {
array_push($occupationselectlist, $item->id . ':' . $item->name);
}
$addprovinces = Province::orderBy('name', 'asc')->get(['id', 'name']);
$addprovinceselectlist = array();
array_push($addprovinceselectlist, ':เลือกจังหวัด');
foreach ($addprovinces as $item) {
array_push($addprovinceselectlist, $item->id . ':' . $item->name);
}
$amphurs = Amphur::has('customers')->orderBy('name', 'asc')->get(['id', 'name']);
$amphurselectlist = array();
array_push($amphurselectlist, ':เลือกเขต/อำเภอ');
foreach ($amphurs as $item) {
array_push($amphurselectlist, $item->id . ':' . $item->name);
}
$districts = District::has('customers')->orderBy('name', 'asc')->get(['id', 'name']);
$districtselectlist = array();
array_push($districtselectlist, ':เลือกตำบล/แขวง');
foreach ($districts as $item) {
array_push($districtselectlist, $item->id . ':' . $item->name);
}
if (Auth::user()->isadmin) {
$employees = Employee::orderBy('firstname', 'asc')->orderBy('lastname', 'asc')->get(['id', 'firstname', 'lastname']);
} else {
$provinceid = Auth::user()->provinceid;
$employees = Employee::whereHas('branch', function ($q) use($provinceid) {
$q->where('provinceid', $provinceid);
})->orderBy('firstname', 'asc')->orderBy('lastname', 'asc')->get(['id', 'firstname', 'lastname']);
}
$employeeselectlist = array();
array_push($employeeselectlist, ':เลือกพนักงาน');
foreach ($employees as $emp) {
array_push($employeeselectlist, $emp->id . ':' . $emp->firstname . ' ' . $emp->lastname);
}
$carmodels = CarModel::whereHas("carbrand", function ($q) {
$q->where('ismain', true);
})->orderBy('name', 'asc')->get(['id', 'name']);
$carmodelselectlist = array();
array_push($carmodelselectlist, ':เลือกแบบ');
foreach ($carmodels as $cm) {
array_push($carmodelselectlist, $cm->id . ':' . $cm->name);
}
$colors = Color::all(['id', 'code', 'name']);
$colorselectlist = array();
array_push($colorselectlist, ':เลือกสี');
foreach ($colors as $item) {
array_push($colorselectlist, $item->id . ':' . $item->code . ' - ' . $item->name);
}
$defaultProvince = '';
if (Auth::user()->isadmin == false) {
$defaultProvince = Auth::user()->provinceid;
}
return view($this->viewname, ['colorselectlist' => implode(";", $colorselectlist), 'provinceselectlist' => implode(";", $provinceselectlist), 'addprovinceselectlist' => implode(";", $addprovinceselectlist), 'amphurselectlist' => implode(";", $amphurselectlist), 'districtselectlist' => implode(";", $districtselectlist), 'carmodelselectlist' => implode(";", $carmodelselectlist), 'employeeselectlist' => implode(";", $employeeselectlist), 'occupationselectlist' => implode(";", $occupationselectlist), 'defaultProvince' => $defaultProvince]);
}
