public function rolledit($id, Request $request) { try { // set database $database = Auth::user()->getDatabase(); //change database $property = new Property(); $property->changeConnection($database); // search on street name $query = Property::on($database)->where('strStreetName', $id)->orderby('strStreetName', 'ASC')->orderby('strStreetNo', 'ASC')->get(); $properties = Property::on($database)->where('strStreetName', $id)->orderby('strStreetName', 'ASC')->orderby('numStreetNo', 'ASC')->simplePaginate(1); // get streets and prepend selected street $streets = Street::on($database)->orderBy('strStreetName', 'ASC')->lists('strStreetName', 'strStreetName'); // $streets->prepend(['selected' => $streetname]); // get relationship data $properties->load('owner', 'note'); // get total records as simplepagination does not do this $count = $query->count(); $search = $id; } catch (exception $e) { dd($e->getMessage()); } //dd($properties,$count); return view('property', compact('properties', 'count', 'search', 'streets')); }
/** * Process datatables ajax request. * * @return \Illuminate\Http\JsonResponse */ public function anyData() { $database = Auth::user()->getDatabase(); $property = new Property(); $property->changeConnection($database); $d = Property::on($database)->with('note', 'owner')->select('*'); //$d = Property::on($database) // ->leftjoin('owners', 'owners.strIDNumber', '=', 'properties.strIdentity') // ->leftjoin('notes', 'notes.strKey', '=', 'properties.strKey') // ->select('*') // ->get(); //dd($d); // dd(Datatables::of($d)->make(true)); //$d->load('owner', 'note'); return Datatables::of($d)->make(true); }
/** * 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 newMultiUnit($updatesA, $unit, $database) { $echo = "Entering newMultiUnit ........................................ "; File::append($this->logfilename, $echo . "\r\n"); //dd($updatesA, $unit); // check if multiple units // get sqM for specific unit $a = explode(" & ", $updatesA['strComplexNo']); $sqM = explode(" & ", $updatesA['strSqMeters']); $found = in_array($unit, $a); if ($found) { $place = array_search($unit, $a); $updatesA['strSqMeters'] = $sqM[$place]; } $now = \Carbon\Carbon::now('Africa/Johannesburg')->toDateTimeString(); $updatesA['id'] = null; $updatesA['strKey'] = $updatesA['strKey']; $updatesA['numStreetNo'] = $updatesA['strStreetNo']; $updatesA['numComplexNo'] = $updatesA['strComplexNo']; $updatesA['created_at'] = $now; // dd($updatesA, $a, $found, $place, $a[$place], $sqM[$place]); // delete older ones $delOld = Property::on($database)->where('strKey', $updatesA['strKey'])->where(DB::raw("STR_TO_DATE(dtmRegDate, '%Y-%m-%d')"), '<', Date($updatesA['dtmRegDate']))->delete(); $echo = "..DELETE OLD ............ " . $updatesA['strKey'] . " - " . $delOld; File::append($this->logfilename, $echo . "\r\n"); // insert new property record Property::on($database)->insert($updatesA); // add owner record if not existing // $hascontact = Owner::on($database)->select('id')->where('strIDNumber', '=', $updatesA['strIdentity']); if ($hascontact->count() == 0) { // update details from admin contacts $admin_contacts = "farmbook_admin"; $otf = new \App\Database\OTF(['database' => $admin_contacts]); $db = DB::connection($admin_contacts); $owner_details = Contact::on($admin_contacts)->select('*')->where('strIDNumber', $updatesA['strIdentity'])->first(); // contact details found in admin contacts if (sizeof($owner_details) == 1) { $uid = $owner_details->strIDNumber; // set database back $dbname = $database; $otf = new \App\Database\OTF(['database' => $dbname]); $db = DB::connection($dbname); // update contact details $owner = Owner::on($database)->insert(array('strIDNumber' => $owner_details->strIDNumber, 'NAME' => $owner_details->NAME, 'TITLE' => $owner_details->TITLE, 'INITIALS' => $owner_details->INITIALS, 'strSurname' => $owner_details->strSurname, 'strFirstName' => $owner_details->strFirstName, 'strHomePhoneNo' => $owner_details->strHomePhoneNo, 'strWorkPhoneNo' => $owner_details->strWorkPhoneNo, 'strCellPhoneNo' => $owner_details->strCellPhoneNo, 'EMAIL' => $owner_details->EMAIL, 'created_at' => $now, 'updated_at' => $now)); // update from prop rec } else { $dbname = $database; $otf = new \App\Database\OTF(['database' => $dbname]); $db = DB::connection($dbname); $owner = Owner::on($database)->insert(array('strIDNumber' => $updatesA['strIdentity'], 'NAME' => $updatesA['strOwners'], 'created_at' => $now)); } } else { // dont wipe old details } // add note // $hasnote = Note::on($database)->select('id')->where('strKey', '=', $updatesA['strKey'])->get(); if ($hasnote->count() == 0) { //$note = Note::on($database)->insert(array('strKey' => $updatesA[$x]['strKey'], 'numErf' => $updatesA[$x]['numErf'], 'memNotes' => "\n" . $now . ' ' . $updatesA[$x]['strOwners'] . ' - New Owner.', 'created_at' => $now)); $note = Note::on($database)->insert(array('strKey' => $updatesA['strKey'], 'numErf' => $updatesA['numErf'], 'created_at' => $now)); } else { $note = Note::on($database)->where('strKey', '=', $updatesA['strKey'])->update(array('memNotes' => DB::raw('concat(memNotes, " \\n' . $now . ' ' . $updatesA['strOwners'] . ' - New Owner.")'), 'updated_at' => $now)); } $echo = "Finishing newMultiUnit ........................................ "; File::append($this->logfilename, $echo . "\r\n"); }
public function update(Request $request) { // set database $database = Auth::user()->getDatabase(); //change database $owner = new Owner(); $owner->changeConnection($database); //change database $note = new Note(); $note->changeConnection($database); // get logged in user $user = Auth::user()->name; $now = Carbon\Carbon::now('Africa/Cairo')->toDateTimeString(); // get inputs $strKey = $request->input('strKey'); $strIdentity = $request->input('strIdentity'); $strOwners = $request->input('strOwners'); $homePhone = $request->input('strHomePhoneNo'); $workPhone = $request->input('strWorkPhoneNo'); $cellPhone = $request->input('strCellPhoneNo'); $email = $request->input('EMAIL'); $note = $request->input('note'); $newnote = $request->input('newnote'); $strStreetNo = $request->input('strStreetNo'); $strStreetName = $request->input('strStreetName'); $followup = $request->input('followup'); $date = ""; if (strLen($followup) > 0) { $date = Carbon\Carbon::createFromFormat('Y-m-d', $followup); } try { // update personal details // $owner = Owner::on( $database )->where('strIDNumber', $strIdentity)->update(array('strCellPhoneNo' => $cellPhone, // 'strHomePhoneNo' => $homePhone, // 'strWorkPhoneNo' => $workPhone, // 'EMAIL' => $email, // 'updated_at'=> $now // )); $properties = Property::on($database)->where('strKey', $strKey)->update(array('strStreetNo' => $strStreetNo, 'numStreetNo' => $strStreetNo, 'strStreetName' => $strStreetName)); //dd($properties); //update owner details $owner = Owner::on($database)->where('strIDNumber', $strIdentity)->first(); $owner->strHomePhoneNo = $homePhone; $owner->strCellPhoneNo = $cellPhone; $owner->strWorkPhoneNo = $workPhone; $owner->EMAIL = $email; $owner->save(); // check if there is a new note if (strlen($newnote) > 0) { // if a previous note exists add a carrige return and new note if (strlen($note) > 0) { $updatednote = ltrim(rtrim($note)) . "\n" . $now . " " . $user . " wrote: " . "\n" . $newnote; } else { // add just the new note $updatednote = $now . " " . $user . " wrote: " . "\n" . $newnote; } // update the note $affected = Note::on($database)->where('strKey', $strKey)->update(array('memNotes' => $updatednote, 'followup' => $date, 'updated_at' => $now)); } Note::on($database)->where('strKey', $strKey)->update(array('followup' => $date, 'updated_at' => $now)); } catch (exception $e) { Session::flash('flash_message', 'Error ' . $e->getMessage()); Session::flash('flash_type', 'alert-danger'); return Redirect::back(); } Session::flash('flash_message', 'Updated ' . $strOwners . ' at ' . $now); Session::flash('flash_type', 'alert-success'); return Redirect::back(); }
public function printupdates() { // set database $database = Auth::user()->getDatabase(); $email = Auth::user()->email; //change database $property = new Property(); $property->changeConnection($database); // $properties = Property::on($database)->get(); // get relationship data $properties->load('owner', 'note'); echo $properties->count() . '<br>'; $t = $properties->where('note->memNotes', 'like', '%wrote%'); echo $t->count() . '<br>'; // convert to array // //$props = $properties->toArray(); //for ($x = 0; $x <= sizeof($props); $x++) { foreach ($properties as $prop) { if ($prop->note) { if (strpos($prop->note->memNotes, 'wrote') !== false) { // echo $prop->note->memNotes . '<br>'; $m = $prop->note->memNotes; $my_string = preg_replace(array('/\\n/'), '#PH#', $m); $m = explode('#PH#', $my_string); if ($prop->strKey == '49091-0') { dd($m); } } } else { echo $prop->strKey . '<br>'; // echo key($prop); } } dd("print updates - reportcontroller", $properties); }
/** * 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'); }