PHP DB::raw方法代码示例

 public function showCompanyManagement()
 {
     /*
     $result = DB::table('manager_company_tbl')
     		  ->JOIN('manager_tbl','manager_company_tbl.manager_company_id', '=' , 'manager_tbl.manager_company_id_fk')
     	      ->leftJOIN('manager_account_tbl','manager_tbl.manager_idx','=','manager_account_tbl.manager_idx_fk','WHERE','manager_account_tbl.account_status','=','Active') // ON/AND
     		  ->Paginate(10);
     */
     $search = Input::get('search');
     $search_manager_status = Input::get('manager_status');
     /*
     $query = DB::table('manager_company_tbl')
     	  ->JOIN('manager_tbl','manager_company_tbl.manager_company_id', '=' , 'manager_tbl.manager_company_id_fk')
           ->leftJOIN('manager_account_tbl',function($joins)
     			{
     					$joins->on('manager_tbl.manager_idx','=','manager_account_tbl.manager_idx_fk');
     					$joins->on('manager_account_tbl.account_status','=',DB::raw("'Active'"));
     			}) // LEFT JOIN ON,WHERE
           ->WHERE('manager_tbl.manager_name','LIKE', '%' . $search . '%')
     	  ->orWHERE('manager_company_tbl.manager_company_name','LIKE', '%' . $search . '%');
     */
     $query = DB::table('manager_company_tbl')->JOIN('manager_tbl', 'manager_company_tbl.manager_company_id', '=', 'manager_tbl.manager_company_id_fk')->leftJOIN('manager_account_tbl', function ($joins) {
         $joins->on('manager_tbl.manager_idx', '=', 'manager_account_tbl.manager_idx_fk');
         $joins->on('manager_account_tbl.account_status', '=', DB::raw("'Active'"));
     })->WHERE(function ($query) {
         $search = Input::get('search');
         $search_manager_status = Input::get('manager_status');
         $query->WHERE('manager_tbl.manager_name', 'LIKE', '%' . $search . '%')->orWHERE('manager_company_tbl.manager_company_name', 'LIKE', '%' . $search . '%');
     })->WHERE('manager_tbl.manager_status', 'LIKE', $search_manager_status . '%');
     $result = $query->Paginate(10);
     //print_r($result);die;
     return view('company_management')->with("manager", $result);
 }

 public function total()
 {
     $result = Trainer::select(DB::raw('sum(full_time_male) as male,
                                         sum(full_time_female) as female,
                                         sum(full_time_male) + sum(full_time_female) as total'))->where('report_date_id', $this->report_date_id)->where('institution_id', $this->institution_id)->get();
     return $result;
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     if (isset($model->workend)) {
         $end = $model->workend;
     } else {
         $end = 'now';
     }
     $prefix_person = 'hrps_';
     //env('DB_PREFIX_HR_PERSON');
     $prefix_empl = 'hres_';
     //env('DB_PREFIX_HR_EMPLOYMENT');
     $prefix_org = 'hrom_';
     //env('DB_PREFIX_HR_ORGANISATION');
     $builder->selectraw('CONCAT(' . $prefix_org . 'charts.name, " cabang ", ' . $prefix_org . 'branches.name) as newest_position')->selectraw($prefix_org . 'branches.organisation_id as organisation_id')->selectraw($prefix_org . 'charts.department as newest_department')->selectraw($prefix_empl . 'works.id as newest_work_id')->selectraw($prefix_empl . 'works.nik as newest_nik')->selectraw($prefix_empl . 'works.status as newest_status')->selectraw($prefix_empl . 'works.start as newest_work_start')->selectraw($prefix_empl . 'works.end as newest_work_end')->join(DB::raw($prefix_empl . 'works'), function ($join) use($end, $prefix_empl, $prefix_person) {
         $join->on(DB::raw($prefix_person . 'persons.id'), '=', DB::raw($prefix_empl . 'works.person_id'))->where(function ($query) use($end, $prefix_empl) {
             $query->where(function ($query) use($end, $prefix_empl) {
                 $query;
                 // $query->wherenull( DB::raw($prefix_empl.'works.end'))
                 // ->orwhere( DB::raw($prefix_empl.'works.end'), '>=', date('Y-m-d H:i:s', strtotime($end)));
             })->wherenull(DB::raw($prefix_empl . 'works.deleted_at'));
         });
     })->join(DB::raw($prefix_org . 'charts'), function ($join) use($prefix_empl, $prefix_org) {
         $join->on(DB::raw($prefix_empl . 'works.chart_id'), '=', DB::raw($prefix_org . 'charts.id'))->wherenull(DB::raw($prefix_org . 'charts.deleted_at'));
     })->join(DB::raw($prefix_org . 'branches'), function ($join) use($prefix_org) {
         $join->on(DB::raw($prefix_org . 'charts.branch_id'), '=', DB::raw($prefix_org . 'branches.id'))->wherenull(DB::raw($prefix_org . 'branches.deleted_at'));
     })->groupby(DB::raw($prefix_person . 'persons.id'));
 }

 public static function winners($p)
 {
     $winners = Votes::whereHas('image', function ($q) use($p) {
         $q->where('created_at', '>', $p['start'])->where('created_at', '<=', $p['end']);
     })->with('image.author')->select('image_id', DB::raw('COUNT(image_id) as count'))->groupBy('image_id')->orderBy('count', 'desc')->take(3)->get();
     return $winners;
 }

 /**
  * Store the job in the database.
  *
  * Returns the id of the job.
  *
  * @param string $job
  * @param mixed  $data
  * @param int    $delay
  *
  * @return int
  */
 public function storeJob($job, $data, $delay = 0)
 {
     $payload = $this->createPayload($job, $data);
     $database = Config::get('database');
     if ($database['default'] === 'odbc') {
         $row = DB::select(DB::raw("SELECT laq_async_queue_seq.NEXTVAL FROM DUAL"));
         $id = $row[0]->nextval;
         $job = new Job();
         $job->id = $id;
         $job->status = Job::STATUS_OPEN;
         $job->delay = $delay;
         $job->payload = $payload;
         $job->save();
     } else {
         if ($database['default'] === 'mysql') {
             $payload = $this->createPayload($job, $data);
             $job = new Job();
             $job->status = Job::STATUS_OPEN;
             $job->delay = $delay;
             $job->payload = $payload;
             $job->save();
             $id = $job->id;
         }
     }
     return $id;
 }

 public function excel()
 {
     $table = with(new Customer())->getTable();
     $data = DB::select(DB::raw("SELECT * FROM {$table}"));
     $data = json_encode($data);
     SELF::data2excel('Excel', 'Sheet1', json_decode($data, true));
 }

 public function scopeGetTodayPoints($query)
 {
     $user = Auth::user();
     $task_total = $users = DB::table('point_audits')->join('tasks', 'point_audits.task_id', '=', 'tasks.id')->select(DB::raw('SUM(point*value) as total'))->where('date', '=', DB::raw('CURDATE()'))->where('user_id', '=', $user->id)->where('task_id', '=', $this->id)->groupBy('user_id')->first();
     return empty($task_total->total) ? '0 pts' : $task_total->total . ' pts';
     //return "(0)";
 }

 public function postAddIntrare()
 {
     $rules = array('expeditor' => 'required', 'destinatar' => 'required');
     $errors = array('required' => 'Campul este obligatoriu.');
     $validator = Validator::make(Input::all(), $rules, $errors);
     if ($validator->fails()) {
         return Redirect::back()->with('message', 'Eroare validare formular!')->withErrors($validator)->withInput();
     } else {
         DB::beginTransaction();
         try {
             $numar_inregistrare = DB::table('registru_intrare')->select(DB::raw('max(numar_inregistrare) AS numar_inregistrare'))->where('logical_delete', 0)->get();
             $urmatorul_numar_inregistrare = 0;
             if ($numar_inregistrare[0]->numar_inregistrare > 0) {
                 $urmatorul_numar_inregistrare = $numar_inregistrare[0]->numar_inregistrare;
             }
             $urmatorul_numar_inregistrare++;
             DB::table('registru_intrare')->insertGetId(array('numar_inregistrare' => $urmatorul_numar_inregistrare, 'expeditor' => Input::get('expeditor'), 'numar_inregistrare_expeditor' => Input::get('numar_inregistrare_expeditor'), 'numar_anexe' => Input::get('numar_anexe'), 'continut' => Input::get('continut'), 'destinatar' => Input::get('destinatar'), 'observatii' => Input::get('observatii')));
         } catch (Exception $e) {
             DB::rollback();
             return Redirect::back()->with('message', 'Eroare salvare date: ' . $e)->withInput();
         }
         DB::commit();
         return Redirect::back()->with('message', 'Salvare realizata cu succes!');
     }
 }

 public function rawSum($filter)
 {
     //dd(__METHOD__."(".__LINE__.")",compact('filter'));
     /*
      * Note: Group by should really be groupBy(['year(dateStamp)', 'month(dateStamp)', 'day(dateStamp)', 'hour(dateStamp)'])
      * but eloquent places backticks (`) around the field names, and MYSQL does not like
      * group by `year(dateStamp)`, `month(dateStamp)`, `day(dateStamp)`, `hour(dateStamp)`;
      * as it thinks that `year(dateStamp)` refers to a field name, not a date function on field dateStamp.
      *
      * Therefor we are trying to get away with group by dateStamp. Hoping that the populating functions do not add minutes, seconds into the dateStamp.
      */
     if (isset($filter['groupBy']) and $filter['groupBy'] == 'dateStamp') {
         $query = PerformanceTally::groupBy(DB::raw('year(dateStamp), month(dateStamp), day(dateStamp), hour(dateStamp)'))->orderBy('dateStamp', 'asc')->selectRaw('date_format(dateStamp,"%Y-%m-%d %H:%i") as dateStamp, sum(receivedUnits) as receivedUnits, sum(putAwayRec) as putAwayRec, sum(putAwayRplComb) as putAwayRplComb, sum(putAwayRplSngl) as putAwayRplSngl, sum(putAwayReserve) as putAwayReserve, sum(replenTotes) as replenTotes');
     } elseif (isset($filter['groupBy']) and $filter['groupBy'] == 'userName') {
         $query = PerformanceTally::groupBy('userName')->orderBy('userName', 'asc')->selectRaw('userName, sum(receivedUnits) as receivedUnits, sum(putAwayRec) as putAwayRec, sum(putAwayRplComb) as putAwayRplComb, sum(putAwayRplSngl) as putAwayRplSngl, sum(putAwayReserve) as putAwayReserve, sum(replenTotes) as replenTotes');
     } else {
         $query = PerformanceTally::selectRaw('sum(receivedUnits) as receivedUnits, sum(putAwayRec) as putAwayRec, sum(putAwayRplComb) as putAwayRplComb, sum(putAwayRplSngl) as putAwayRplSngl, sum(putAwayReserve) as putAwayReserve, sum(replenTotes) as replenTotes');
     }
     if (isset($filter['fromDate']) && strlen($filter['fromDate']) > 4) {
         $query = $query->where('dateStamp', '>=', $filter['fromDate']);
     }
     if (isset($filter['toDate']) && strlen($filter['toDate']) > 4) {
         $query = $query->where('dateStamp', '<=', $filter['toDate']);
     }
     if (isset($filter['userName']) && strlen($filter['userName']) > 2) {
         $query = $query->where('userName', $filter['userName']);
     }
     return $query;
 }

 /**
  * phEstadisticasController constructor.
  */
 public function index()
 {
     $days = Input::get('days', 7);
     $range = \Carbon\Carbon::now()->subDays($days);
     $stats = User::where('created_at', '>=', $range)->groupBy('date')->orderBy('date', 'DESC')->remember(1440)->get([DB::raw('Date(created_at) as date'), DB::raw('COUNT(*) as value')])->toJSON();
     $this->layout->content = View::make('home', compact('stats'));
 }

 public function importdata(Request $request)
 {
     $results = null;
     //        $results = $reader->get();
     //
     //        $ret = $results->toArray();
     $file = $request->file('exelimport');
     $request->file('exelimport')->move(storage_path() . '/public/import/', 'import.xlsx');
     //$request->file('exelimport')
     //        $results =    Excel::load($request->file('exelimport'))->toArray();
     $ret = Excel::filter('chunk')->load(storage_path('/public/import/import.xlsx'))->chunk(250, function ($results) {
         $data = array();
         //            $results = $reader->toArray();
         foreach ($results as $index => $value) {
             $EMP_ID = $value["emp_id"];
             $PERIOD = $value["period"];
             //                $user = DB::table('TBL_MEMBER_BENEFITS')->where('EMP_ID', $EMP_ID)->where('PERIOD', $PERIOD)->count();
             $allquery = "SELECT COUNT(EMP_ID) AS total FROM TBL_MEMBER_BENEFITS  WHERE EMP_ID= '" . $EMP_ID . "' AND (PERIOD='" . $PERIOD . "' OR PERIOD IS NULL)";
             $all = DB::select(DB::raw($allquery));
             $total = $all[0]->total;
             //                array_push($data,'asd','asda');
             if ($total == 0) {
                 array_push($data, array('EMP_ID' => $value["emp_id"], 'FULL_NAME' => $value["full_name"], 'PATH_CODE' => $value["path_code"], 'DEP_CODE' => $value["dep_code"], 'DIV_CODE' => $value["div_code"], 'SEC_CODE' => $value["sec_code"], 'PATH_NAME' => $value["path_name"], 'DEP_NAME' => $value["dep_name"], 'DIV_NAME' => $value["div_name"], 'SEC_NAME' => $value["sec_name"], 'HIRE_DATE' => $value["hire_date"], 'END_DATE' => $value["end_date"], 'POSITION_CODE' => $value["position_code"], 'POSITION_NAME' => $value["position_name"], 'JOB_LINE' => $value["job_line"], 'LEVEL_CODE' => $value["level_code"], 'EXE_NAME' => $value["exe_name"], 'EXE1_NAME' => $value["exe1_name"], 'AGE_YEAR' => $value["age_year"], 'AGE_DAY' => $value["age_day"], 'JOB_YEAR' => $value["job_year"], 'JOB_DAY' => $value["job_day"], 'EMPLOYER_CONTRIBUTION_1' => $value["employer_contribution_1"], 'EMPLOYER_EARNING_2' => $value["employer_earning_2"], 'MEMBER_CONTRIBUTION_3' => $value["member_contribution_3"], 'MEMBER_EARNING_4' => $value["member_earning_4"], 'TAX_1' => $value["tax_1"], 'TAX_12' => $value["tax_12"], 'TAX_124' => $value["tax_124"], 'TAX_1234' => $value["tax_1234"], 'GRATUITY' => $value["gratuity"], 'GRATUITY_TAX' => $value["gratuity_tax"], 'RECORD_DATE' => $value["record_date"], 'PERIOD' => $value["period"]));
             }
         }
         //            var_dump($data);
         DB::table('TBL_MEMBER_BENEFITS')->insert($data);
         //DB::insert(DB::raw($insert));
     });
     return response()->json(array('success' => true, 'html' => $ret));
 }

 /**
  * Show the application dashboard to the user.
  *
  * @return Response
  */
 public function index()
 {
     $articles = Article::with('author')->orderBy('position', 'DESC')->orderBy('created_at', 'DESC')->limit(4)->get();
     $photoAlbums = PhotoAlbum::select(array('photo_albums.id', 'photo_albums.name', 'photo_albums.description', 'photo_albums.folder_id', DB::raw('(select filename from photos WHERE album_cover=TRUE and photos.photo_album_id=photo_albums.id LIMIT 1) AS album_image'), DB::raw('(select filename from photos WHERE photos.photo_album_id=photo_albums.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
     $videoAlbums = VideoAlbum::select(array('video_albums.id', 'video_albums.name', 'video_albums.description', 'video_albums.folder_id', DB::raw('(select youtube from videos WHERE album_cover=TRUE and videos.video_album_id=video_albums.id LIMIT 1) AS album_image'), DB::raw('(select youtube from videos WHERE videos.video_album_id=video_albums.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
     return view('pages.home', compact('articles', 'videoAlbums', 'photoAlbums'));
 }

 public function importdata(Request $request)
 {
     $results = null;
     $type = $request->input('type');
     $retdate = Excel::load($request->file('exelimport'), function ($reader) use($type) {
         $results = $reader->get();
         $ret = $results->toArray();
         //            var_dump($ret);
         foreach ($ret as $index => $value) {
             $EMP_ID = $value["emp_id"];
             $PLAN_ID = $value["plan_id"];
             $EQUITY_RATE = $value["equity_rate"];
             $DEBT_RATE = $value["debt_rate"];
             $MODIFY_DATE = $value["modify_date"];
             $EFFECTIVE_DATE = $value["effective_date"];
             $MODIFY_COUNT = $value["modify_count"];
             $MODIFY_COUNT_TIMESTAMP = $value["modify_count_timestamp"];
             $MODIFY_BY = $value["modify_by"];
             $insert = "INSERT INTO TBL_USER_FUND_CHOOSE (PLAN_ID,EMP_ID,EQUITY_RATE,DEBT_RATE,MODIFY_DATE,EFFECTIVE_DATE,MODIFY_COUNT,MODIFY_COUNT_TIMESTAMP,MODIFY_BY) VALUES(" . $PLAN_ID . ",'" . $EMP_ID . "'," . $EQUITY_RATE . "," . $DEBT_RATE . ",'" . $MODIFY_DATE . "','" . $EFFECTIVE_DATE . "'," . $MODIFY_COUNT . ",'" . $MODIFY_COUNT_TIMESTAMP . "','" . $MODIFY_BY . "')";
             DB::insert(DB::raw($insert));
         }
         $staturet = true;
         $data = "ok";
     });
     return response()->json(array('success' => true, 'html' => $retdate));
 }

 public function overTime(Request $request)
 {
     $num = 10;
     $username = $request->session()->get("username");
     $borrows = Borrow::where("date-should-return", "<", DB::raw("curdate()"))->paginate($num);
     return view("/admin/OverTime", ['username' => $username, "borrows" => $borrows]);
 }

 public function GetView()
 {
     if (Auth::check()) {
         if (in_array('VIEW_GEOSEGMENTLIST', $this->permission)) {
             $geosegment_obj = array();
             if (User::isSuperAdmin()) {
                 $geosegment_obj = GeoSegmentList::with(['getGeoEntries' => function ($q) {
                     $q->select(DB::raw('*,count(geosegmentlist_id) as geosegment_count'))->groupBy('geosegmentlist_id');
                 }])->with(['getAdvertiser' => function ($q) {
                     $q->with('GetClientID');
                 }])->get();
             } else {
                 $usr_company = $this->user_company();
                 $geosegment_obj = GeoSegmentList::with(['getGeoEntries' => function ($q) {
                     $q->select(DB::raw('*,count(geosegmentlist_id) as geosegment_count'))->groupBy('geosegmentlist_id');
                 }])->whereHas('getAdvertiser', function ($q) use($usr_company) {
                     $q->whereHas('GetClientID', function ($p) use($usr_company) {
                         $p->whereIn('user_id', $usr_company);
                     });
                 })->get();
             }
             return view('geosegment.list')->with('geosegment_obj', $geosegment_obj);
         }
         return Redirect::back()->withErrors(['success' => false, 'msg' => "You don't have permission"]);
     }
     return Redirect::to(url('/user/login'));
 }