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');
}
