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;
}
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);
}
/**
* 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'));
}
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('postcode', function (Blueprint $table) {
$table->increments('id');
$table->string('postcode')->index();
$table->smallInteger('pnum')->unsigned();
$table->char('pchar');
$table->mediumInteger('minnumber')->index()->unsigned();
$table->mediumInteger('maxnumber')->index()->unsigned();
$table->enum('numbertype', array('', 'mixed', 'even', 'odd'));
$table->string('street');
$table->string('city');
$table->string('municipality');
$table->smallInteger('municipality_id');
$table->decimal('lat', 15, 13);
$table->decimal('lon', 15, 13);
$table->decimal('rd_x', 31, 20);
$table->decimal('rd_y', 31, 20);
$table->enum('location_detail', array('', 'exact', 'postcode', 'pnum', 'city'));
$table->timestamp('changed_date');
DB::raw('UPDATE postcode
INNER JOIN municipalities ON municipalities.name = postcode.municipality
SET postcode.municipality_id = municipalities.id');
$table->dropColumn('municipality');
});
}
/**
* Get all objects and their related variants for a section
* @param $where
* @param array $selectedColumns
* @return mixed
* @throws Exception
* @since 08-01-2016
* @author Dinanath Thakur <*****@*****.**>
*/
public function getAllObjectsAndVariantsOfASectionWhere($where, $selectedColumns = ['*'])
{
if (func_num_args() > 0) {
$where = func_get_arg(0);
$cacheKey = $this->table . "::" . implode('-', array_flatten($where));
if (cacheGet($cacheKey)) {
return cacheGet($cacheKey);
}
// die("no cache");
DB::statement('SET SESSION group_concat_max_len = 10000');
$result = DB::table($this->table)->join('settings_sections', 'settings_sections.section_id', '=', 'settings_objects.section_id')->join('settings_descriptions', 'settings_descriptions.object_id', '=', 'settings_objects.object_id')->leftJoin('settings_variants', 'settings_variants.object_id', '=', 'settings_objects.object_id')->leftJoin('settings_descriptions as sd', function ($join) {
$join->on('sd.object_id', '=', 'settings_variants.variant_id');
})->whereRaw($where['rawQuery'], isset($where['bindParams']) ? $where['bindParams'] : array())->select(DB::raw('settings_objects.object_id ,
settings_objects.*,
settings_sections.name AS section_name,
settings_descriptions.value AS setting_name,
settings_descriptions.tooltip,
GROUP_CONCAT(DISTINCT(settings_variants.variant_id) ORDER BY settings_variants.position) AS variant_ids,
GROUP_CONCAT(DISTINCT(BINARY settings_variants.name) ORDER BY settings_variants.position SEPARATOR "____") AS variant_names,
GROUP_CONCAT(CASE sd.object_type WHEN "V" THEN sd.value END ORDER BY settings_variants.position SEPARATOR "____") AS var_names'))->orderBy('settings_objects.position')->groupBy('settings_objects.object_id')->get();
cachePut($cacheKey, $result, $this->minutes);
return $result;
} else {
throw new Exception('Argument Not Passed');
}
}
public function getReport(Request $request)
{
$this->validate($request, ['person_type' => 'required']);
$person_type = $request->person_type;
if ($person_type == 1) {
$persons = DB::table('personal_accounts')->select('personal_accounts.*')->where('due', '>', 0)->get();
foreach ($persons as &$person) {
if ($person->person_type == Config::get('common.person_type_employee')) {
$employee = DB::table('employees')->where('id', $person->person_id)->first();
$person->person_name = $employee->name;
$person->person_contact = $employee->mobile;
} elseif ($person->person_type == Config::get('common.person_type_supplier')) {
$supplier = DB::table('suppliers')->where('id', $person->person_id)->first();
$person->person_name = $supplier->company_name;
$person->person_contact = $supplier->company_office_phone;
} elseif ($person->person_type == Config::get('common.person_type_customer')) {
$customer = DB::table('customer')->where('id', $person->person_id)->first();
$person->person_name = $customer->name;
$person->person_contact = $customer->mobile;
} elseif ($person->person_type == Config::get('common.person_type_provider')) {
$provider = DB::table('providers')->where('id', $person->person_id)->first();
$person->person_name = $provider->name;
$person->person_contact = $provider->mobile;
}
}
} elseif ($person_type == 2) {
$persons = DB::table('personal_accounts')->select('personal_accounts.*')->where('balance', '>', 0)->get();
$key = 0;
foreach ($persons as &$person) {
if ($person->person_type == Config::get('common.person_type_employee')) {
$employee = DB::table('employees')->where('id', $person->person_id)->first();
$person->person_name = $employee->name;
$person->person_contact = $employee->mobile;
$dueSalary = DB::table('salaries')->select(DB::raw('SUM(net_due) as sum_net_due'), DB::raw('SUM(over_time_due) as sum_over_time_due'), DB::raw('SUM(bonus_due) as sum_bonus_due'))->where('employee_id', $person->person_id)->first();
$sumDueSalary = $dueSalary->sum_net_due + $dueSalary->sum_over_time_due + $dueSalary->sum_bonus_due;
$person->balance = $person->balance + $person->overtime_balance + $person->bonus_balance - $sumDueSalary;
if ($person->balance == 0) {
unset($persons[$key]);
}
} elseif ($person->person_type == Config::get('common.person_type_supplier')) {
$supplier = DB::table('suppliers')->where('id', $person->person_id)->first();
$person->person_name = $supplier->company_name;
$person->person_contact = $supplier->company_office_phone;
} elseif ($person->person_type == Config::get('common.person_type_customer')) {
$customer = DB::table('customer')->where('id', $person->person_id)->first();
$person->person_name = $customer->name;
$person->person_contact = $customer->mobile;
} elseif ($person->person_type == Config::get('common.person_type_provider')) {
$provider = DB::table('providers')->where('id', $person->person_id)->first();
$person->person_name = $provider->name;
$person->person_contact = $provider->mobile;
}
$key++;
}
}
//dd($persons);
$persons = array_values($persons);
$ajaxView = view('reports.debtorsCreditors.view', compact('persons', 'person_type'))->render();
return response()->json($ajaxView);
}
/**
* Retourne la moyenne d'age des acteurs
* 1er mode de Laravel pour construire mes requetes
* Cela me permet de conserver une syntaxe pure en Mysql.
*/
public function getAvgActors()
{
//1ere methode: Utilisation de MYSQL
// marche mais n'est peu souple
/*
* $results = DB::select('
SELECT ROUND(AVG(TIMESTAMPDIFF(YEAR,dob, NOW()))) as age
FROM actors
');
*/
// 2nd méthode: PHP & MYSQL
// Query Builder: Le constructeur de Requête en Laravel
// DB::table => correspond FROM actors en MYSQL
// select() => corresponds a mon SELECT en MYSQL
// DB::raw() => permet d'utiliser les fonctions MYSQL
// comme ROUND() AVG() NOW()...
// first() => corresponds LIMIT 1 en MYSQL
// first() => l'equivalent de fetch()
// get() => l'équivalent de fetchAll()
// MAITRISE
$results = DB::table('actors')->select(DB::raw('ROUND(AVG(TIMESTAMPDIFF(YEAR,dob, NOW()))) as age'))->first();
/*
$results = Actors::select(DB::raw('ROUND(AVG(TIMESTAMPDIFF(YEAR,dob, NOW()))) as age'))
->first();
*/
//3eme methode: Eloquant ORM
// Model Actors
// <=> SELECT AVG(dob) FROM actors
//$results = Actors::avg('dob');
// je retourne le resultat de ma requete executé
return $results;
}
/**
*
*
* @return void
*/
protected function getStats()
{
$jobs = Job::select(\Illuminate\Support\Facades\DB::raw('count(*) as jobs_count, status'))->groupBy('status')->get();
$o = "";
foreach ($jobs as $j) {
switch ($j->status) {
case Job::STATUS_OPEN:
$o .= 'Open - ';
break;
case Job::STATUS_WAITING:
$o .= 'Waiting - ';
break;
case Job::STATUS_STARTED:
$o .= 'Started - ';
break;
case Job::STATUS_FINISHED:
$o .= 'Done - ';
break;
case Job::STATUS_FAILED:
$o .= 'Failed - ';
break;
}
$o .= $j->jobs_count . " \n";
}
return $o;
}
public function Navsave(Request $request)
{
$ret = false;
$rethtml = "";
$data = array();
$ID = $request->input('ID');
$NAME = $request->input('NAME');
$URL = $request->input('URL');
$thumbnail = $request->file('client_upload');
$fileThumb = "";
$pathThunb = getenv('THUMB_PATH');
$FILE_PATH = "http://measvp.mea.or.th:8081/contents/" . $fileThumb;
$chk = "SELECT COUNT(ID) As total FROM TBL_EXT_LINK WHERE ID = " . $ID;
$all = DB::select(DB::raw($chk));
$total = $all[0]->total;
if ($total > 0) {
$rethtml = "รหัส ที่ท่านเลือกมีอยู่ในระบบแล้ว";
} else {
$data = array('ID' => $ID, 'NAME' => $NAME, 'FILE_PATH' => $FILE_PATH, 'URL' => $URL);
if ($thumbnail != null) {
$fileThumb = "client_" . $ID . ".png";
$thumbnail->move(public_path() . $pathThunb, $fileThumb);
//file_put_contents( 'C:\FileSharing\fund_file\contents', $fileThumb);
}
$ret = DB::table('TBL_EXT_LINK')->insert($data);
}
return redirect()->to('admin/con1')->with('message', 'ok');
//
// return response()->json(array('success' => $ret, 'html'=>$rethtml));
}
/**
* Get client details.
*
* @param int $clientId
* @return array
*/
public function getClient($clientId)
{
$response = new AjaxResponse();
// Get client
$client = Client::where('clients.id', $clientId)->where('clients.user_id', Auth::user()->id)->join('bills', 'clients.id', '=', 'bills.client_id')->select('clients.*', DB::raw('COUNT(bills.id) as total_bills'))->first();
// Make sure client exists
if (!$client->id) {
$response->setFailMessage(trans('clients.client_not_found'));
$response->addExtraFields(['redirect_to' => url('/clients')]);
return response($response->get(), $response->getDefaultErrorResponseCode());
}
// Get client last unpaid bills
$client->last_unpaid_bills = Clients::lastUnpaidBills($clientId);
// Get client last paid bills
$client->last_paid_bills = Clients::lastPaidBills($clientId);
// Get client statistics
$client->statistics = ClientStatistics::all($clientId);
$client->money_generated = trans('clients.money_generated', ['money' => $client->statistics['earnings']]);
$client->money_generated_in_current_year = trans('clients.money_generated_by_this_client_in_this_year_more_details', ['money' => $client->statistics['earnings_in_current_year']]);
$client->number_of_products_sold = trans('clients.number_of_products_sold', ['number' => $client->statistics['number_of_products_ordered']]);
$client->number_of_products_sold_this_year = trans('clients.number_of_products_sold_this_year', ['number' => $client->statistics['number_of_products_ordered_this_year']]);
// Money user has to receive from this client
$client->money_user_has_to_receive = 0;
if ($client->statistics['money_user_has_to_receive'] > 0) {
$client->money_user_has_to_receive = trans('clients.client_has_to_pay', ['sum' => $client->statistics['money_user_has_to_receive']]);
}
// Money client owes
$client->money_owed_due_passed_payment_term = 0;
if ($client->statistics['money_owed_due_passed_payment_term'] > 0) {
$client->money_owed_due_passed_payment_term = trans('clients.client_has_to_pay_due_passed_payment_term', ['sum' => $client->statistics['money_owed_due_passed_payment_term']]);
}
$response->setSuccessMessage('');
$response->addExtraFields(['data' => $client]);
return response($response->get());
}
/**
* Show the form for editing the specified resource.
*
* @param int $id
*
* @return Response
*/
public function edit($id)
{
$newsletters_offer = NewslettersOffer::findOrFail($id);
$hoteluri = Hoteluri::join('geo_orase', 'geo_orase.AidaID', '=', 'hoteluri.City')->select('hoteluri.id', DB::raw("CONCAT(hoteluri.Name,'-',hoteluri.Stars,'-',geo_orase.Name) AS Nume"))->lists('Nume', 'id')->all();
$newsletters_offer = NewslettersOffer::findOrFail($id);
return view('backend.newsletters_offers.edit', compact('newsletters_offer'))->with('hoteluri', $hoteluri);
}
/**
* @param $query
* @param $group
* @param int $n
*
* sets the related grouping limit to query
*/
public function scopeNPerGroup($query, $group, $n = 10)
{
// queried table
$table = $this->getTable();
// initialize MySQL variables inline
$query->from(DB::raw("(SELECT @rank:=0, @group:=0) as vars, {$table}"));
// if no columns already selected, let's select *
if (!$query->getQuery()->columns) {
$query->select("{$table}.*");
}
// make sure column aliases are unique
$groupAlias = 'group_' . md5(time());
$rankAlias = 'rank_' . md5(time());
// apply mysql variables
$query->addSelect(DB::raw("@rank := IF(@group = {$group}, @rank+1, 1) as {$rankAlias}, @group := {$group} as {$groupAlias}"));
// make sure first order clause is the group order
$query->getQuery()->orders = (array) $query->getQuery()->orders;
array_unshift($query->getQuery()->orders, ['column' => $group, 'direction' => 'asc']);
// prepare subquery
$subQuery = $query->toSql();
// prepare new main base Query\Builder
$newBase = $this->newQuery()->from(DB::raw("({$subQuery}) as {$table}"))->mergeBindings($query->getQuery())->where($rankAlias, '<=', $n)->getQuery();
// replace underlying builder to get rid of previous clauses
$query->setQuery($newBase);
}
public function postLogin(LoginRequest $request)
{
if (!Auth::attempt(['email' => $request->get('email'), 'password' => $request->get('password')], true)) {
session()->flash('errorMessages', ['You have entered an invalid email address or password.', 'Please try again.']);
return back()->withInput();
}
$user = Auth::user();
// if the user has a plant, set welcome message
if (count($user->plants)) {
$plant = $user->plants()->where('isHarvested', '=', false)->orderBy(DB::raw('RAND()'))->first();
if ($plant) {
// set welcome message to need water or need fertilizer if the plant needs it
$lastTimeWatered = $plant->getLastTimeWatered();
$lastTimeFertilized = $plant->getLastTimeFertilized();
$random = round(rand(1, 2));
if (!$lastTimeWatered && $plant->created_at->addDay() < Carbon::now()) {
$status = $plant->plantCharacter['need_water_phrase_' . $random];
} elseif (!$lastTimeFertilized && $plant->created_at->addDay() < Carbon::now()) {
$status = $plant->plantCharacter['need_fertilizer_phrase_' . $random];
} elseif ($lastTimeWatered && Carbon::createFromFormat('Y-m-d H:i:s', $lastTimeWatered->pivot->date)->addDay() < Carbon::now()) {
$status = $plant->plantCharacter['need_water_phrase_' . $random];
} elseif ($lastTimeFertilized && Carbon::createFromFormat('Y-m-d H:i:s', $lastTimeFertilized->pivot->date)->addDay() < Carbon::now()) {
$status = $plant->plantCharacter['need_fertilizer_phrase_' . $random];
} else {
$status = $plant->plantCharacter['welcome_phrase_' . $random];
}
session()->flash('message', $plant->plantCharacter->name . ': ' . $status);
}
}
return redirect()->route('dashboard');
}
/**
* Determine the current website data.
*
* Returns null if the web site is not found in the websites table.
*
* @return array
*/
public static function currentWebsiteData()
{
static $current_data;
$BASE_URL = static::currentServerName();
$cache_key = 'website-data.' . $BASE_URL;
// Get the current ID from the cache if it is present.
if (empty($current_data)) {
if (Cache::has($cache_key)) {
return Cache::get($cache_key);
}
}
// If the cache doesn't have it then get it from the database.
if (empty($current_data)) {
// Have to do this using a raw query because Laravel doesn't INSTR.
try {
/** @var Website $result */
$result = static::whereRaw("INSTR('" . $BASE_URL . "', `http_host`) > 0")->orderBy(DB::raw('LENGTH(`http_host`)'), 'desc')->first();
if (empty($result)) {
$current_data = null;
} else {
$current_data = $result->toArray();
}
Cache::put($cache_key, $current_data, 60);
} catch (\Exception $e) {
$current_data = null;
}
}
return $current_data;
}
/**
* Returns the sum of all values a metric has by the hour.
*
* @param int $hour
*
* @return int
*/
public function getValuesByHour($hour)
{
$dateTimeZone = SettingFacade::get('app_timezone');
$dateTime = (new Date())->setTimezone($dateTimeZone)->sub(new DateInterval('PT' . $hour . 'H'));
$hourInterval = $dateTime->format('YmdH');
if (Config::get('database.default') === 'mysql') {
if (!isset($this->calc_type) || $this->calc_type == self::CALC_SUM) {
$value = (int) $this->points()->whereRaw('DATE_FORMAT(created_at, "%Y%m%d%H") = ' . $hourInterval)->groupBy(DB::raw('HOUR(created_at)'))->sum('value');
} elseif ($this->calc_type == self::CALC_AVG) {
$value = (int) $this->points()->whereRaw('DATE_FORMAT(created_at, "%Y%m%d%H") = ' . $hourInterval)->groupBy(DB::raw('HOUR(created_at)'))->avg('value');
}
} else {
// Default metrics calculations.
if (!isset($this->calc_type) || $this->calc_type == self::CALC_SUM) {
$queryType = 'sum(metric_points.value)';
} elseif ($this->calc_type == self::CALC_AVG) {
$queryType = 'avg(metric_points.value)';
} else {
$queryType = 'sum(metric_points.value)';
}
$query = DB::select("select {$queryType} as aggregate FROM metrics JOIN metric_points ON metric_points.metric_id = metrics.id WHERE metric_points.metric_id = {$this->id} AND to_char(metric_points.created_at, 'YYYYMMDDHH24') = :timestamp GROUP BY to_char(metric_points.created_at, 'H')", ['timestamp' => $hourInterval]);
if (isset($query[0])) {
$value = $query[0]->aggregate;
} else {
$value = 0;
}
}
if ($value === 0 && $this->default_value != $value) {
return $this->default_value;
}
return $value;
}
public function fromRequest($request)
{
$this->DataSourceId = $request->input("DataSourceId");
$this->DataId = $request->input("DataId");
$this->ReferenceUrl = $request->input("ReferenceUrl");
$this->PhotoUrls = $request->input("PhotoUrls");
$this->MLSNumber = $request->input("MLSNumber");
$this->Status = $request->input("Status");
$this->StatusEnum = $request->input("StatusEnum");
$this->SaleType = $request->input("SaleType");
$this->SaleTypeEnum = $request->input("SaleTypeEnum");
$this->PropertyType = $request->input("PropertyType");
$this->PropertyTypeEnum = $request->input("PropertyTypeEnum");
$this->ListPrice = $request->input("ListPrice");
$this->SalePrice = $request->input("SalePrice");
$this->State = $request->input("State");
$this->County = $request->input("County");
$this->City = $request->input("City");
$this->Address = $request->input("Address");
$this->PostalCode = $request->input("PostalCode");
$this->Area = $request->input("Area");
$this->CrossStreets = $request->input("CrossStreets");
$this->Location = DB::raw($request->input("Location"));
$this->Description = $request->input("Description");
$this->YearBuilt = $request->input("YearBuilt");
$this->LotSqFt = $request->input("LotSqFt");
$this->StructureSqFt = $request->input("StructureSqFt");
$this->Bedrooms = $request->input("Bedrooms");
$this->BathsFull = $request->input("BathsFull");
$this->BathsHalf = $request->input("BathsHalf");
}
static function findContentByYear($selectedYear)
{
return DB::table('article')->select('article.article_id', 'article.name', 'journal.prefix', 'journal_edition.issue_year', 'journal_edition.number_in_year', 'article.sort_order')->join('journal_edition', 'journal_edition.journal_edition_id', '=', 'article.journal_edition_id')->join('journal', 'journal.journal_id', '=', 'journal_edition.journal_id')->where('journal_edition.issue_year', $selectedYear)->where(function ($query) {
$query->where('article.topic_id', '<>', 1)->orWhere(DB::raw('LENGTH(article.name)'), '>', 70);
// some useful articles may not have topic
})->orderby('article.name')->get();
}
/**
* Display a listing of the resource.
*
* @return Response
*/
public function index()
{
$pro = new Product();
$adSlotObj = new Adslot();
$catObj = new Category();
$brandObj = new Brand();
/*getting all products for all slots(currently we have 7 slots)*/
$adSlot_data = $adSlotObj->with(['products'])->get();
/*t1-t7*/
// dd($adSlot_data[4]['products'][0]);
$category_temp_data = $catObj->orderBy('created_at')->take(10)->get();
/*f1-f10*/
$brand_data = $brandObj->with(['products'])->get();
$category_data = [];
foreach ($category_temp_data as $cat_id) {
$cat_latest_product = $pro->where('category_id', '=', $cat_id['id'])->orderBy('created_at')->take(1)->pluck('photo_1');
$cat_latest_product_id = $pro->where('category_id', '=', $cat_id['id'])->orderBy('created_at')->take(1)->pluck('id');
$cat_random_product = $pro->where('category_id', '=', $cat_id['id'])->orderBy(DB::raw('RAND()'))->take(6)->get();
$cat_brands = $pro->with(['brand'])->where('category_id', '=', $cat_id['id'])->take(5)->get();
$cat_products_random_photos = [];
foreach ($cat_random_product as $photo) {
$cat_products_random_photos[] = $photo;
}
$category_data[] = ['color' => $cat_id['color'], 'floor' => $cat_id['floor'], 'name' => $cat_id['name'], 'desc' => $cat_id['description'], 'logo' => $cat_id['logo'], 'latest_photo_id' => $cat_latest_product_id, 'latest_photo' => $cat_latest_product, 'random_photos' => $cat_products_random_photos, 'brands' => $cat_brands];
}
return view('landing_page', compact(['adSlot_data', 'category_data']));
}
