/**
* Query scope for adding searching based on $filterArgs
*
* @param [type] $query Query builder object
*
* @return [type] [description]
*/
public static function scopeSearch($query, $input = null)
{
if ($input == null) {
$input = Input::all();
}
// sort
$sortField = isset($_GET['sort']) ? $_GET['sort'] : false;
$sortField = static::isSortable($sortField);
if ($sortField) {
$direction = $input['direction'];
$direction = $direction == 'asc' ? 'asc' : 'desc';
$query->orderBy($sortField, $direction);
}
$data = [];
foreach (array_keys(static::$filterArgs) as $key) {
if (isset($input[$key])) {
$data[$key] = $input[$key];
}
}
foreach ($data as $key => $value) {
$filter = static::$filterArgs[$key];
if (is_null($value) || strlen($value) == 0) {
continue;
}
$fieldName = isset($filter['field']) ? $filter['field'] : $key;
switch ($filter['type']) {
case 'value':
$query->where($fieldName, '=', $value);
break;
case 'like':
$query->where($fieldName, 'LIKE', '%' . $value . '%');
break;
case 'custom':
$query->where($fieldName, $filter['custom'], $value);
break;
case 'gt':
$query->where($fieldName, '>', $value);
break;
case 'lt':
$query->where($fieldName, '<', $value);
break;
case 'scope':
// $query = static::{'scope' . $filter['method']}($query);
break;
default:
break;
}
}
return $query;
}
/**
* Filter For UpperHouse
* @param Illuminate\Http\Request $request
* @return LengthAwarePaginator
*/
protected function filter($request)
{
if ($st_name = $request->input('st_name')) {
$this->model = $this->model->like('properties.ST', $st_name);
}
if ($st_pcode = $request->input('st_pcode')) {
$this->model = $this->model->where('properties.ST_PCODE', $st_pcode);
}
if ($noGeo = $request->input('no_geo')) {
if ($noGeo == 'true') {
return $this->model->paginate(['type', 'properties']);
}
}
return $this->model->paginate();
}
/**
* Persist the login token
*
* @param LoginToken $token
* @return bool
*/
public function refresh(LoginToken $token)
{
$attributes = $token->getAttributes();
if (array_key_exists('attachments', $attributes)) {
$attributes['attachments'] = serialize($attributes['attachments']);
}
if ($token->id) {
return $this->table->where('id', $token->id)->update($attributes);
}
$token->id = $this->table->insertGetId($attributes);
return $token;
}
/**
* Filter For LowerHouse
* @param Illuminate\Http\Request $request
* @return LengthAwarePaginator
*/
protected function filter($request)
{
if ($st_name = $request->input('st_name')) {
$this->model = $this->model->like('properties.ST', $st_name);
}
if ($dt_name = $request->input('dt_name')) {
$this->model = $this->model->like('properties.DT', $dt_name);
}
if ($ts_name = $request->input('ts_name')) {
$this->model = $this->model->like('properties.TS', $ts_name);
}
if ($object_id = $request->input('object_id')) {
$this->model = $this->model->where('properties.OBJECTID', (int) $object_id);
}
if ($st_pcode = $request->input('st_pcode')) {
$this->model = $this->model->where('properties.ST_PCODE', $st_pcode);
}
if ($dt_pcode = $request->input('dt_pcode')) {
$this->model = $this->model->where('properties.DT_PCODE', $dt_pcode);
}
if ($ts_pcode = $request->input('ts_pcode')) {
$this->model = $this->model->where('properties.TS_PCODE', $ts_pcode);
}
if ($by_party = $request->input('by_party')) {
$this->model = $this->model->where('properties.Summary', strtoupper($by_party));
}
if ($noGeo = $request->input('no_geo')) {
if ($noGeo == 'true') {
return $this->model->paginate(['type', 'properties']);
}
}
return $this->model->paginate();
}
/**
* Filter For FAQ
* @param Illuminate\Http\Request $request
* @return LengthAwarePaginator
*/
protected function filter($request)
{
if ($type = $request->input('type')) {
switch ($type) {
case 'yes_no':
$type = 'Yes/No';
break;
case 'open_ended':
$type = 'Open-ended';
break;
}
$this->model = $this->model->where('question_type', $type);
}
if ($section = $request->input('section')) {
$this->model = $this->model->like('article_or_section', $section);
}
if ($category = $request->input('category')) {
$this->model = $this->model->like('question_category', $category);
}
if ($law = $request->input('source')) {
$this->model = $this->model->like('law_or_source', $law);
}
return $this->model->paginate();
}
/**
* [scopeLevelUnderEqual description]
* @param [type] $query [description]
* @param [type] $level [description]
* @return [type] [description]
*/
public function scopeLevelUnderEqual($query, $level)
{
return $query->where('level', '<=', $level);
}
/**
* [scopeParent description]
* @param [type] $query [description]
* @param [type] $id [description]
* @return [type] [description]
*/
public function scopeParent($query, $id)
{
$query->where('parent_id', $id);
}
/**
* [scopeCommentsOfPosition description]
* @param [type] $query [description]
* @param [type] $position [description]
* @return [type] [description]
*/
public function scopeOnCommentsOfPosition($query, $position)
{
return $query->where('comment_position', '=', $position);
}
/**
* scopeFull
* Retrieve the total of products contained in a category
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopeFull($query)
{
return $query->where(\DB::raw(0), '<', function ($sql) {
$sql->select(\DB::raw('COUNT(products.id)'))->from('products')->whereRaw('categories.id=products.category_id');
});
}
/**
* [info_com 公共的查询方法]
* @param [type] $model [模型]
* @param array $where [条件]
* @return [status] [默认是空 查询所有的，1是查询一条]
*/
public function info_com($model, $where = array(), $order = array(), $status = '')
{
if (empty($status)) {
$result = $model->where($where)->order($order)->select();
} else {
$result = $model->where($where)->find();
}
return $result;
}
/**
* Scope queries to articles that have ben published
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopePublished($query)
{
$query->where('published_at', '<=', Carbon::now());
}
/**
* Get only active applications
* @method scopeActive
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopeActive($query)
{
return $query->where('inactive_flag', 0);
}
/**
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopeIsExecuted($query)
{
$query->where(DB::raw('32&Status'), '=', 32);
}
/**
* [scopeImage description]
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopeImage($query)
{
$query->where('is_image', 1);
}
/**
* Get pending outages (i.e. not complete)
* @method scopePending
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopePending($query)
{
return $query->where('complete_flag', 0);
}
/**
* Scope queries to assignments that haven't been finished
*
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopeUnfinished($query)
{
$query->where('finished_on', NULL);
}
/**
* export to excel.
*
* @return
*/
public function dailyexport(Request $request)
{
$date = $request->get('date', date('Y-m-d'));
$shift_id = $request->get('shift_id', 0);
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator($this->user->fullname)->setLastModifiedBy($this->user->fullname)->setTitle("Laporan Harian " . $this->user->pool->pool_name . '-' . date('d-m-Y'))->setSubject("Laporan Harian " . $this->user->pool->pool_name . '-' . date('d-m-Y'))->setDescription("Laporan harian operasi pool" . $this->user->pool->pool_name)->setKeywords("Laporan Harian");
$styleArray = array('font' => array('bold' => true, 'color' => array('rgb' => 'FF0000'), 'size' => 16));
$sheet_active = 0;
foreach ($this->fleetmodel->where('actived', 1)->get() as $model) {
$model_id = $model->id;
$checkins = $this->checkins->join('fleets', 'fleets.id', '=', 'checkins.fleet_id')->where('checkins.operasi_time', $date)->where('checkins.pool_id', $this->user->pool_id)->where('checkins.shift_id', $shift_id)->where('fleets.fleet_model_id', $model_id);
if ($checkins->count() > 0) {
$objPHPExcel->createSheet(NULL, $sheet_active);
$objPHPExcel->setActiveSheetIndex($sheet_active);
$objPHPExcel->getActiveSheet()->mergeCells('A2:J2');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'LAPORAN PENDAPATAN HARIAN TANGGAL ' . $date);
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->mergeCells('A5:A6');
$objPHPExcel->getActiveSheet()->mergeCells('B5:B6');
$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');
$objPHPExcel->getActiveSheet()->mergeCells('E5:E6');
$objPHPExcel->getActiveSheet()->mergeCells('F5:G5');
$objPHPExcel->getActiveSheet()->mergeCells('H5:H6');
$objPHPExcel->getActiveSheet()->mergeCells('I5:I6');
$objPHPExcel->getActiveSheet()->mergeCells('J5:J6');
$objPHPExcel->getActiveSheet()->mergeCells('K5:K6');
$objPHPExcel->getActiveSheet()->mergeCells('L5:O5');
$objPHPExcel->getActiveSheet()->mergeCells('P5:R5');
//$objPHPExcel->getActiveSheet()->mergeCells('R5:R6');
$objPHPExcel->getActiveSheet()->mergeCells('S5:S6');
$objPHPExcel->getActiveSheet()->mergeCells('T5:T6');
$objPHPExcel->getActiveSheet()->mergeCells('U5:U6');
$objPHPExcel->getActiveSheet()->mergeCells('V5:V6');
$objPHPExcel->getActiveSheet()->mergeCells('W5:W6');
$objPHPExcel->getActiveSheet()->mergeCells('X5:X6');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'NO');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'BAPAK ASUH');
$objPHPExcel->getActiveSheet()->setCellValue('C5', 'PENGEMUDI');
$objPHPExcel->getActiveSheet()->setCellValue('C6', 'NIP');
$objPHPExcel->getActiveSheet()->setCellValue('D6', 'NAMA');
$objPHPExcel->getActiveSheet()->setCellValue('E5', 'BODY');
$objPHPExcel->getActiveSheet()->setCellValue('F5', 'STATUS');
$objPHPExcel->getActiveSheet()->setCellValue('F6', 'OPS');
$objPHPExcel->getActiveSheet()->setCellValue('G6', 'BS');
$objPHPExcel->getActiveSheet()->setCellValue('H5', 'SETORAN MURNI');
$objPHPExcel->getActiveSheet()->setCellValue('I5', 'TAB SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('J5', 'DENDA JAM');
$objPHPExcel->getActiveSheet()->setCellValue('K5', 'DP SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('L5', 'BAYAR CICILAN');
$objPHPExcel->getActiveSheet()->setCellValue('L6', 'KS');
$objPHPExcel->getActiveSheet()->setCellValue('M6', 'S-PART');
$objPHPExcel->getActiveSheet()->setCellValue('N6', 'DP-KSO');
$objPHPExcel->getActiveSheet()->setCellValue('O6', 'HUT-LAMA');
$objPHPExcel->getActiveSheet()->setCellValue('P5', 'BAYAR');
$objPHPExcel->getActiveSheet()->setCellValue('P6', 'STIKER BANDARA & KEAMANAN');
$objPHPExcel->getActiveSheet()->setCellValue('Q6', 'CUCI');
$objPHPExcel->getActiveSheet()->setCellValue('R6', 'LAKA');
$objPHPExcel->getActiveSheet()->setCellValue('S5', 'HARUS SETOR');
$objPHPExcel->getActiveSheet()->setCellValue('T5', 'POTONGAN');
$objPHPExcel->getActiveSheet()->setCellValue('U5', 'SETOR CASH');
$objPHPExcel->getActiveSheet()->setCellValue('V5', 'KETEKORAN');
$objPHPExcel->getActiveSheet()->setCellValue('W5', 'SETORAN OPS');
$objPHPExcel->getActiveSheet()->setCellValue('X5', 'SHIFT');
$no = 1;
$starline = 8;
$checkins = $checkins->orderBy('fleets.taxi_number')->get(['checkins.*', 'fleets.taxi_number']);
foreach ($checkins as $finan) {
$driver = $finan->driver;
$fleet = $finan->fleet;
$status = $finan->status;
$financial = $finan->financial;
$bapakasuh = $fleet->bapakasuh()->wherePivot('status', 1)->first();
$namabapakasuh = $bapakasuh ? $bapakasuh->first_name . ' ' . $bapakasuh->last_name : 'TIDAK ADA BAPAK ASUH';
$financialdata = [];
//set default 0
foreach ($this->label as $key => $value) {
$financialdata[$value] = 0;
}
if ($financial) {
foreach ($financial as $mony) {
$financialdata[$this->label[$mony->financial_type_id]] = $mony->amount;
}
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $starline, $no);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $starline, $namabapakasuh);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $starline, $driver->nip);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $starline, $driver->name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $starline, $fleet->taxi_number);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $starline, $status->kode);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $starline, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $starline, $financialdata['setoran_wajib']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $starline, $financialdata['tabungan_sparepart']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $starline, $financialdata['denda']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $starline, $financialdata['hutang_dp_sparepart']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $starline, $financialdata['cicilan_ks']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $starline, $financialdata['cicilan_sparepart']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $starline, $financialdata['cicilan_dp_kso']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $financialdata['cicilan_hutang_lama']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15, $starline, $financialdata['cicilan_lain']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(16, $starline, $financialdata['biaya_cuci']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(17, $starline, $financialdata['iuran_laka']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(18, $starline, '=SUM(H' . $starline . ':R' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(19, $starline, $financialdata['potongan']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $starline, $financialdata['setoran_cash']);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $starline, $finan->ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(21, $starline, '=(U' . $starline . '-(S' . $starline . '-T' . $starline . '))');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(22, $starline, '=(U' . $starline . '-(Q' . $starline . '+R' . $starline . '))');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(23, $starline, $finan->shift_id);
//col X
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(24, $starline, $finan->shift_id);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $finan->cicilan_lain);
//hidden coloumn status operasi
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(25, $starline, $finan->operasi_status_id);
//col Z
$no++;
$starline++;
}
$objPHPExcel->getActiveSheet()->mergeCells('A' . ($starline + 1) . ':G' . ($starline + 1) . '');
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($starline + 1), 'TOTAL SETORAN ');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 1), '=SUM(H8:H' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 1), '=SUM(I8:I' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('J' . ($starline + 1), '=SUM(J8:J' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 1), '=SUM(K8:K' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 1), '=SUM(L8:L' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('M' . ($starline + 1), '=SUM(M8:M' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('N' . ($starline + 1), '=SUM(N8:N' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('O' . ($starline + 1), '=SUM(O8:O' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('P' . ($starline + 1), '=SUM(P8:P' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('Q' . ($starline + 1), '=SUM(Q8:Q' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('R' . ($starline + 1), '=SUM(R8:R' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('S' . ($starline + 1), '=SUM(S8:S' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('T' . ($starline + 1), '=SUM(T8:T' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('U' . ($starline + 1), '=SUM(U8:U' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('V' . ($starline + 1), '=SUM(V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('W' . ($starline + 1), '=SUM(W8:W' . $starline . ')');
$objPHPExcel->getActiveSheet()->getStyle('A5:X' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_HAIR);
$objPHPExcel->getActiveSheet()->getStyle('A5:X6')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A5:X' . ($starline + 1))->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($starline + 1) . ':X' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
/* Rekap Pendapatan */
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 3), 'Total Setoran :');
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 5), 'Disetor ke Bank :');
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 6), 'Disetor ke KKBD :');
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 7), 'Disetor ke Peduli Laka :');
$objPHPExcel->getActiveSheet()->setCellValue('E' . ($starline + 3), '=SUM(U8:U' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('E' . ($starline + 5), '=SUM(W8:W' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('E' . ($starline + 6), '=SUM(Q8:Q' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('E' . ($starline + 7), '=SUM(R8:R' . $starline . ')');
/* Rekap Unit Operasi */
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 3), 'Unit Sirkulasi :');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 5), 'Unit Operasi :');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 6), 'Status B P :');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 7), 'Status B L :');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 8), 'Status T D O (Lain-Lain):');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 3), '=COUNT(Z8:Z' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 5), '=COUNTIF(Z8:Z' . $starline . ', 1)');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 6), '=COUNTIF(Z8:Z' . $starline . ', 3)');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 7), '=COUNTIF(Z8:Z' . $starline . ', 7)');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 8), '=I' . ($starline + 3) . '-(I' . ($starline + 5) . '+ I' . ($starline + 6) . '+ I' . ($starline + 7) . ')');
/* Rekap KETEKORAN */
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 3), 'Total Ketekoran :');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 5), 'KS Murni :');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 6), 'KS BP:');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 7), 'KS BL :');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 8), 'KS TDO (Lain-Lain):');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 3), '=V' . ($starline + 1));
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 5), '=SUMIF(Z8:Z' . $starline . ',1,V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 6), '=SUMIF(Z8:Z' . $starline . ',3,V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 7), '=SUMIF(Z8:Z' . $starline . ',7,V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 8), '=L' . ($starline + 3) . '-(L' . ($starline + 5) . '+ L' . ($starline + 6) . '+ L' . ($starline + 7) . ')');
$objPHPExcel->getActiveSheet()->setCellValue('B' . ($starline + 10), 'Tanggal Unduh');
$objPHPExcel->getActiveSheet()->setCellValue('C' . ($starline + 10), ':');
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 10), date('Y-m-d H:i:s'));
//$objPHPExcel->getSecurity()->setLockWindows(true);
//$objPHPExcel->getSecurity()->setLockStructure(true);
//$objPHPExcel->getSecurity()->setWorkbookPassword("FreeBlocking");
//$objPHPExcel->getActiveSheet()->getProtection()->setPassword('FreeBlocking');
//$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // This should be enabled in order to enable any of the following!
//$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
//$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
//$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
$objPHPExcel->getActiveSheet()->setTitle('Laporan ' . $model->fleet_model . ' - ' . $date);
$sheet_active++;
}
}
$shift = [1 => 'Reguler', 2 => 'Kalong'];
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(storage_path('excels/') . 'L' . $this->user->pool_id . $shift_id . $date . '.xls');
return response()->download(storage_path('excels/') . 'L' . $this->user->pool_id . $shift_id . $date . '.xls', 'Laporan-Harian-' . Str::slug($this->user->pool->pool_name, '-') . '-' . $shift[$shift_id] . '-Tanggal-' . $date . '.xls');
}
/**
* [deleteRepeatData deleteRepeatData 与数据库的数据进行比对，去除待写入数据中重复的数据]
* @param array $data [description]
* @param string $table [description]
* @param [type] $db [description]
* @return [type] [description]
*/
function deleteRepeatData($data = array(), $table = '', $fieldname = '', $db = '')
{
/* 去重复，并按序单条插入
foreach ($data as $key => $value) {
$this->db->where('rolename', $value['rolename']);
$q = $this->db->get($table);
if ($q->num_rows() > 0) {
$this->db->where('user_id', $id);
$this->db->update('profile', $data);
} else {
$this->db->set('user_id', $id);
$this->db->insert('profile', $data);
}
}
//print_R($db);
*/
foreach ($data as $key => $value) {
$db->where($fieldname, $value[$fieldname]);
$q = $db->get($table);
if ($q->num_rows() > 0) {
unset($data[$key]);
}
}
return array_values($data);
}
/**
* Get only non-production servers
* @method scopeNonroduction
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopeNonproduction($query)
{
return $query->where('production_flag', 0);
}
/**
* Checks if the published at is less than now.
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopePublished($query)
{
$query->where([['published_at', '<=', Carbon::now()], ['is_published', true]]);
}
/**
* [scopeInCategory description]
*
* @param [type] $query [description]
* @param [type] $categoryId [description]
*
* @return [type] [description]
*/
public function scopeInCategory($query, $categoryId)
{
$query->where('category_id', '=', $categoryId);
}
/**
* Get the active rows
* @param [type] $query Query Object
* @return [type] Query object
*/
public function scopeActive($query)
{
return $query->where('active', '=', true);
}
/**
* Get only those posts which has manually filled excerpts
*
* @param [type] $query [description]
* @return Query
*/
public function scopeExcerpt($query)
{
return $query->where('post_excerpt', '<>', '');
}
/**
* Query
*
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopePublic($query)
{
return $query->where('public', true);
}
/**
* [scopeForKey description].
*
* @param [type] $query [description]
* @param [type] $key [description]
*
* @return [type] [description]
*/
public function scopeForKey($query, $key)
{
return $query->where('key', $key);
}
/**
* Filtros por fecha
* @param [type] $query [description]
* @return [type] [description]
*/
public function scopeShowYear($query)
{
$show = date("Y");
$date = $show . '-01-01 00:00:00';
return $query->where('created_at', '>=', $date);
}
/**
* countingProductsByCategory
* Products total by category collection
* @param [type] $all_products refine products
* @param [type] $categories refine categories
* @return [array] filter used in the product list view menu
*/
public static function countingProductsByCategory($all_products, $categories)
{
$filters = ['category' => []];
foreach ($categories as $value) {
$category_id = $value['id'];
$childs = \Cache::remember('progeny_of_' . $category_id, 15, function () use($category_id) {
Category::progeny($category_id, $childs, ['id']);
return $childs;
});
$all = $childs;
$childs = [];
foreach ((array) $all as $val) {
$childs[] = $val['id'];
}
$qty = 0;
if ($all_products) {
$qty = $all_products->where('category_id', $category_id)->count();
$qty += $all_products->filter(function ($item) use($childs) {
return in_array($item->category_id, $childs);
})->count();
}
if ($qty) {
$filters['category'][$category_id]['id'] = $category_id;
$filters['category'][$category_id]['name'] = $value['name'];
$filters['category'][$category_id]['qty'] = $qty;
}
}
//Order by qty
if (isset($filters['category'])) {
$filters['category'] = collect($filters['category'])->sortByDesc('qty');
}
return $filters;
}
/**
* Scope not of user - get record locks
* not belonging to a particular user
* @param [type] $query [description]
* @param [type] $user_id [description]
* @return [type] [description]
*/
public function scopeNotOfUser($query, $user_id)
{
return $query->where('user_id', '<>', $user_id);
}
/**
*
* enforce user cannot save a task that's not user's
* @param [type] $query [description
* @return [type] [description]
*/
public function scopeOwn($query)
{
$query->where('user_id', Auth::id());
}
/**
* Query scope to return information about the current project
* @param [type] $query [description]
* @param [type] $id [description]
* @return [type] [description]
*/
public function scopeProject($query, $id)
{
return $query->where('project_id', $id);
}