public function index(Request $request)
{
// set database
$database = Auth::user()->getDatabase();
//change database for Street
$street = new Complex();
$street->changeConnection($database);
//change database for Street
$property = new Property();
$property->changeConnection($database);
// get inputs
$Input = $request->input('input');
$Select = $request->input('selected');
//dd( $Input,$Select,$request);
// check if input or select
// if input ignore select
if (strlen($Input) > 0) {
// search
$search = $Input;
$properties = Property::on($database)->like('strComplexName', $search)->orderby('strComplexName', 'ASC')->orderby('strStreetName', 'ASC')->orderby('numComplexNo', 'ASC')->get();
} else {
// search
$complex = Complex::on($database)->where('id', $Select)->first();
$search = $complex->strComplexName;
$properties = Property::on($database)->where('strComplexName', $search)->orderby('strComplexName', 'ASC')->orderby('strStreetName', 'ASC')->orderby('numComplexNo', 'ASC')->get();
}
Session::put('search', $Select);
Session::put('controllerroute', '/complex');
// view properties
// return with error if no result
if ($properties->count()) {
return view('complexes', compact('properties', 'search'));
} else {
Session::flash('flash_message', '' . "No properties matching search criteria.");
Session::flash('flash_type', 'alert-danger');
return Redirect::back();
}
}
/**
* Show the application dashboard.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
// get user id
// connect to farmbook and get default database
$database = Auth::user()->getDatabase();
//change database dynamically to user set database
$street = new Street();
$street->changeConnection($database);
$streets = Street::on($database)->orderBy('strStreetName', 'ASC')->lists('strStreetName', 'id');
$complexes = Complex::on($database)->orderBy('strComplexName', 'ASC')->lists('strComplexName', 'id');
// $owners = Owner::on($database )->orderBy('NAME','DESC')->lists('NAME', 'id');
$owners = Property::on($database)->orderBy('strOwners', 'DESC')->groupBy('strOwners')->lists('strOwners', 'id');
$properties = Property::on($database)->orderBy('strKey', 'ASC')->lists('strkey', 'id');
$erfs = Note::on($database)->orderBy('numErf', 'ASC')->lists('numErf', 'id');
return view('search', compact('streets', 'complexes', 'owners', 'properties', 'erfs'));
}
public function getFormlevel()
{
$risk = 0;
$risk = Input::get('risk');
$level = Level::where('level', '=', Input::get('level'));
$assets = array();
reset($assets);
$assets = DB::Select(DB::raw("SELECT a.*,level.description as level_desc,\n\t\t\t\tm1.name as create_name,m2.name as update_name,t.description as type,\n c.description as category\n FROM asset_registers a\n\t\t\t\tLEFT JOIN level\n\t\t\t\tON a.level = level.level\n\t\t\t\tLEFT JOIN members m1\n\t\t\t\tON a.created_by = m1.id\n\t\t\t\tLEFT JOIN members m2\n\t\t\t\tON a.updated_by = m2.id\n LEFT JOIN ref_types t\n ON a.type_id = t.id\n LEFT JOIN ref_categories c\n ON a.cat_id = c.id\n\t\t\t\tWHERE a.active = 1\n\t\t\t\tAND a.id = " . Input::get('id') . "\n\t\t"))[0];
// Query ดึงข้อมูลรายละเอียดของแต่ละ node
$unders = array();
$unders = DB::Select(DB::raw("\n \tSELECT ag.*,l.type_name FROM asset_registers ag\n \tLEFT JOIN level l\n \tON ag.level = l.level\n\t\t\tWHERE ag.level > " . Input::get('level') . "\n\t\t\tAND ag.id > " . Input::get('id') . " AND ag.active = 1\n\t\t\tAND ag.project_id = " . Session::get('project_id') . "\n\t\t\tORDER BY ag.parent asc"));
if (Input::get('level') == 3) {
$com_detail = array();
$complex_detail = array();
$color = array();
$bss_unit = array();
$bss_unit = DB::Select(DB::raw("select * from asset_registers where active = 1 and id = " . $assets->parent))[0];
$business_unit = $bss_unit->business_unit_type_colums;
$color = DB::Select(DB::raw("SELECT * FROM complex_detail_default WHERE active = 1 and type = 5 GROUP BY description"));
$com_detail = DB::Select(DB::raw("\n\t\t\t\tselect ac.id,ac.node,ac.complex_id,c.name as 'complex_name'\n\t\t\t\tfrom asset_complex_detail ac\n\t\t\t\tleft join complex c\n\t\t\t\ton ac.complex_id = c.id\n\t\t\t\twhere ac.active = 1\n\t\t\t\tand ac.project_id = " . Session::get('project_id') . "\n\t\t\t\tand ac.node = " . Input::get('id')));
$status = 0;
if (!empty($com_detail)) {
//Status = 1 ต่อเมื่อ มีการบันทึกค่า ลง asset_complex_detail แล้ว
$status = 1;
$complex_detail = DB::Select(DB::raw("\n\t\t\t\tselect ac.id,ac.node,ac.complex_id,c.name as 'complex_name'\n\t\t\t\tfrom asset_complex_detail ac\n\t\t\t\tleft join complex c\n\t\t\t\ton ac.complex_id = c.id\n\t\t\t\twhere ac.active = 1\n\t\t\t\tand ac.project_id = " . Session::get('project_id') . "\n\t\t\t\tand ac.node = " . Input::get('id')))[0];
}
foreach (Complex::where('active', '=', 1)->get() as $com) {
$complexs[$com->id] = $com->name;
}
return View::make('asset_register.level_three')->with(compact('complexs'))->with(compact('assets'))->with(compact('complex_detail'))->with('status', $status)->with('color', $color)->with('business_unit', $business_unit)->with('node', Input::get('id'));
} elseif (Input::get('level') == 8) {
$category = array();
$cat = DB::Select(DB::raw("SELECT be.id,be.category_id,c.description,be.type_id,be.part_id\n\t\t\t\t\tFROM basic_equipments be\n\t\t\t\t\tLEFT JOIN ref_categories c\n\t\t\t\t\tON be.category_id = c.id\n\t\t\t\t\tWHERE be.active = 1\n\t\t\t\t\tAND be.project_id = " . Session::get('project_id')));
foreach ($cat as $c) {
$category[$c->category_id] = $c->description;
}
$asset_basic_failure = array();
$asset_basic_failure = DB::Select(DB::raw("SELECT * FROM asset_basic_failure\n\t\t\t\t\tWHERE active = 1 AND project_id = " . Session::get('project_id')));
// $parantdata = array();
// $parantdata = DB::Select(DB::raw("
// SELECT * FROM asset_registers WHERE active = 1
// AND level = 7 AND id = ".$assets->parent."
// AND project_id = ".Session::get('project_id')
// ))[0];
$equip_part = array();
$equip = array();
$equip = DB::Select(DB::raw("\n\t\t\t\tselect e.*,c.description from basic_equipments e\n\t\t\t\tleft join ref_parts c\n\t\t\t\ton e.part_id = c.id\n\t\t\t\twhere e.active = 1 AND e.category_id = " . $assets->cat_id . "\n\t\t\t\tAND e.type_id = " . $assets->type_id . " AND\n\t\t\t\te.project_id = " . Session::get('project_id')));
foreach ($equip as $c) {
$equip_part[$c->part_id] = $c->description;
}
$failuremode = array();
$mode = DB::Select(DB::raw("select fm.id,fm.cause_id,fm.mode_id,m.description from basic_failures fm\n\t\t\t\tleft join ref_failure_mode m\n\t\t\t\ton fm.mode_id=m.id\n\t\t\t\twhere fm.active = 1\n\t\t\t\tAND fm.project_id = " . Session::get('project_id')));
foreach ($mode as $c) {
$failuremode[$c->mode_id] = $c->description;
}
return View::make('asset_register.level_eight')->with('asset_basic_failure', $asset_basic_failure)->with('node_id', Input::get('id'))->with(compact('equip_part'))->with(compact('failuremode'))->with(compact('assets'))->with(compact('category'));
} elseif (Input::get('level') == 7) {
$unders = array();
$unders = DB::table('asset_registers')->select('*')->where('id', '>', Input::get('id'))->where('level', '>', Input::get('level'))->where('active', '=', 1)->where('project_id', '=', Session::get('project_id'))->orderBy('parent', 'asc')->get();
return View::make('asset_register.level_seven')->with('unders', $unders)->with(compact('assets'));
} else {
return View::make('asset_register.form_other')->with('unders', $unders)->with(compact('assets'));
}
}
/**
* Show the application dashboard.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
// set database
$database = Auth::user()->getDatabase();
//change database
$property = new Property();
$property->changeConnection($database);
$query1 = Property::on($database)->select([DB::raw('cast(dtmRegDate as date) as dd'), DB::raw('count(dtmRegDate) as sales'), DB::raw('max(strAmount) as high'), DB::raw('avg(strAmount) as avg')])->groupBy(DB::raw('Year(dd)'))->get();
$min = $query1->min('dd');
$max = $query1->max('dd');
$dateStart = Carbon::createFromFormat('Y-m-d', $min);
$dateEnd = Carbon::createFromFormat('Y-m-d', $max);
$diffInYears = $dateStart->diffInYears($dateEnd, false);
$Amin = Property::on($database)->min('strAmount');
$Amax = Property::on($database)->max('strAmount');
$sum = Property::on($database)->sum('strAmount');
$avg = Property::on($database)->where('strAmount', '>', 0)->avg('strAmount');
// dd($min,$max,$Amin,$Amax,$sum,$avg,$dateStart,$dateEnd,$diffInYears);
$stocksTable = Lava::DataTable();
// Lava::DataTable() if using Laravel
$stocksTable->addStringColumn('Date')->addNumberColumn('Registered');
// ->addNumberColumn('Bond');
// Random Data For Example
foreach ($query1 as &$q) {
// echo substr($q->dd,0,4).' - '. $q->sales;
// echo "<br>";
$mdate = intval(substr($q->dd, 0, 4));
if ($mdate > 2004) {
// dd($q->dd);
$stocksTable->addRow([$mdate, $q->sales]);
}
}
// chart - price
//-------------------------------------------------------------------
$priceTable = Lava::DataTable();
// Lava::DataTable() if using Laravel
$priceTable->addStringColumn('Date')->addNumberColumn('Avg Price');
foreach ($query1 as $key => $q) {
// echo substr($q->dd,0,4).' - '. $q->sales;
// echo "<br>";
$mdate = intval(substr($q->dd, 0, 4));
if ($mdate > 2004) {
$priceTable->addRow([$mdate, intval($q->avg)]);
}
}
$c1 = Street::on($database)->count('id');
$c2 = Complex::on($database)->count('id');
$c3 = Property::on($database)->select('id')->groupBy('strKey')->get()->count();
$c4 = Property::on($database)->count('id');
$votes = Lava::DataTable();
$votes->addStringColumn('Food Poll')->addNumberColumn('Count')->addRow(['Streets', $c1])->addRow(['Complexes', $c2])->addRow(['Properties', $c3])->addRow(['Owners', $c4]);
// chart -age
//-------------------------------------------------------------------
$queryAge = Property::on($database)->select('strKey', 'strSellers', 'dtmRegDate')->distinct('strIdentity')->OrderBy('dtmRegDate')->GroupBy('dtmRegDate')->get();
$queryAge = $queryAge->toArray();
// dd($queryAge);
$ageTable = Lava::DataTable();
// Lava::DataTable() if using Laravel
$ageTable->addStringColumn('Date')->addNumberColumn('Avg Age');
$agedate = array();
for ($x = 0; $x <= sizeof($queryAge) - 1; $x++) {
$seller = substr($queryAge[$x]['strSellers'], -13);
if (is_numeric($seller) && strlen(ltrim(rtrim($seller))) == 13) {
$born = substr($seller, 0, 2);
$age = 116 - $born;
$mdate = intval(substr($queryAge[$x]['dtmRegDate'], 0, 4));
// echo $queryAge[$x]['strKey'] . " - " . $mdate . " - " . $seller . ' - ' . $age . " <br>";
$agegate = array_push($agedate, ['date' => $mdate, 'age' => $age]);
}
}
$collection = collect($agedate);
$coll = $collection->groupBy('date');
//dd($coll);
$ageTable->addRow([$mdate, $age]);
// dd($queryAge, $queryAge->count(), $seller);
$agechart = Lava::LineChart('Ages', $ageTable, ['title' => "Sellers Age ", 'colors' => ['Cyan']]);
$chart = Lava::LineChart('Registrations', $stocksTable, ['title' => "Properties registered ", 'colors' => ['blue']]);
$chart2 = Lava::LineChart('Prices', $priceTable, ['title' => "Average price ", 'colors' => ['green', 'red'], 'vAxis' => ['format' => 'R###,###,###,###']]);
$chart3 = Lava::BarChart('Votes', $votes, ['title' => "Totals ", 'colors' => ['DeepSkyBlue']]);
//$chart3 = Lava::BarChart('Votes', $votes);
return view('dashboard');
}
