public static function get_locations($sid, $state, $did = false, $tid = false) { $data = Village::select([DB::raw('AsText(geoPoint) AS location,district,taluk,village,population,malepopulation,femalepopulation')]); $data->join('village_sector', 'village_id', '=', 'village.id', 'left'); $data->join('taluk', 'taluk.id', '=', 'village.taluk_id', 'left'); $data->join('district', 'district.id', '=', 'taluk.district_id', 'left'); $data->join('state', 'state.id', '=', 'district.state_id', 'left'); $data->where('state_id', $state); if ($did) { $data->where('taluk.district_id', $did); } if ($tid) { $data->where('taluk_id', $tid); } $data->where('sector_id', $sid); return $data->get(); }
/** * static function to read the xls and store * * @param int $id * @return Response*/ public static function import_xls($pathname, $state_id) { Excel::load($pathname, function ($reader) { // Getting all results $results = $reader->get(); foreach ($results as $result) { //checking existing Ditrict in db if ($result->district != '') { $state = State::where('state', $result->state)->first(); $statesave['state'] = $result->state; if ($state) { $state->fill($statesave); $state->save(); $stateid = $state->id; } else { $state = State::create($statesave); $stateid = $state->id; } // $save['district'] = $result->district; $save[$state_id] = $stateid; $district = District::where($state_id, $stateid)->where('district', $result->district)->first(); if ($district) { $district->fill($save); $district->save(); $districtid = $district->id; } else { $district = District::create($save); $districtid = $district->id; } $taluk = Taluk::where('district_id', $districtid)->where('taluk', $result->taluk)->first(); // $savet['taluk'] = $result->taluk; $savet['district_id'] = $districtid; $savevillage['district_id'] = $districtid; if ($taluk) { $taluk->fill($savet); $taluk->save(); $lastid = $taluk->id; } else { $taluk = Taluk::create($savet); $lastid = $taluk->id; } $village = Village::where('district_id', $districtid)->where('taluk_id', $lastid)->where('village', $result->village)->first(); $savevillage['taluk_id'] = $lastid; $savevillage['village'] = $result->village; $savevillage['stcode'] = $result->stcode; $savevillage['dtcode'] = $result->dtcode; $savevillage['sector_id'] = $result->sector_id; $savevillage['sdtcode'] = $result->sdtcode; $savevillage['tvcode'] = $result->tvcode; $savevillage['population'] = $result->population; $savevillage['malepopulation'] = $result->malepopulation; $savevillage['femalepopulation'] = $result->femalepopulation; $latlong = static::geotag($result); if (!empty(@$latlong->results[0]->geometry->location->lat)) { $lat = $latlong->results[0]->geometry->location->lat; $lng = $latlong->results[0]->geometry->location->lng; $savevillage['geoPoint'] = DB::raw("GeomFromText('POINT({$lat} {$lng})' )"); } if ($village) { $village->fill($savevillage); $village->save(); } else { $village = Village::create($savevillage); } static::map_sector($result, $village); } unset($savevillage); unset($latlong); } }); }
public function ajaxDistrict() { $data = $new = array(); $data = false; $new = array(); if (!empty(Input::get('values')) && !empty(Input::get('sector'))) { $data = Village::get_locations(Input::get('sector'), Input::get('state'), Input::get('values'), Input::get('taluk')); if ($data) { $i = 0; foreach ($data as $value) { $location = explode(" ", str_replace(')', '', str_replace('POINT(', '', $value->location))); $new[$i]['location'] = $value->location; $new[$i]['name'] = $value->village; $new[$i]['taluk'] = $value->taluk; $new[$i][$this->district] = $value->district; $new[$i]['population'] = $value->population; $new[$i]['malepopulation'] = $value->malepopulation; $new[$i]['femalepopulation'] = $value->femalepopulation; $new[$i]['latitude'] = $location[0]; $new[$i]['longtitude'] = $location[1]; $i++; } } } print_r(json_encode($new)); }