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