<?php

namespace App\Http\Controllers;

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Session;
use Illuminate\Http\Request;
use App\Models\survey_info;
use App\Models\area_master;
use App\Models\varnan;
use App\Models\deleted_survey;
use App\Models\signedfile;
use App\Models\gramuserallot;
use App\Models\phase_Model;
use App\Models\ccms_types;
use App\Models\inward;
use App\Models\outward;
use App\Models\armsclamps;
use App\Models\armsclamps_vendor;
use App\Models\inwardccmstypes;
use App\Models\inwards_vendor;
use App\Models\outward_armsclamps;
use App\Models\outward_ccms;
use App\Models\pole_details;
use DB;	
use Validator;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\SurveyExport;
use App\Exports\ReportExcel;
use App\Exports\Reportexcelone;
use App\Exports\Reportexceltwo;
use Maatwebsite\Excel\Exceptions\NoTypeDetectedException;

class Survey_infoController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
		
		$jilla=
		           DB::table('area_masters')
					->where('parent_id','=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->whereIn('id',$request->session()->get('districts'))
					->orderby('area_name','ASC')
					->get();

		$userlist=DB::table('gramusers')
					->where('status','=','active')
					->select('*')
					->get();
		return view('backend.survey_view',compact('userlist','jilla'));    
    }
	public function installation_format(Request $request)
	{
		
        $survey_i=DB::table('area_masters')
		            ->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					
					->get();
		$jilla_i=DB::table('area_masters')
					->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$gram_i=DB::table('area_masters')
					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$jillanamesss=array();
		foreach($survey_i as $d)
			{
				$jillaaname=DB::table('area_masters')
							->where('id','=',$d->jilla_id)
							->get();
				$tname=$jillaaname[0]->area_name;
				array_push($jillanamesss,$tname);
				//echo $areanamesss;
			}
		
		$areanamesss=array();
		foreach($survey_i as $d)
		{
			$talukaname=DB::table('area_masters')
					->where('id','=',$d->taluka_id)
					->get();
			$tname=$talukaname[0]->area_name;
			array_push($areanamesss,$tname);
			//echo $areanamesss;
		}
		
		$gramnamesss=array();
		foreach($survey_i as $d)
		{
			$gramname=DB::table('area_masters')
							->where('id','=',$d->gram_id)
							->get();
			$tname=$gramname[0]->area_name;
			array_push($gramnamesss,$tname);
			//echo $areanamesss;
		}
		
		//echo $gramid;
		//exit();
		$demo="";
		
		
		
		//var_dump($demo);
		//exit();
		$jilla=DB::table('area_masters')
					->where('parent_id','=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->get();
		$talukadata=DB::table('area_masters')
		        ->where('parent_id','!=','0')
		        ->where('super_id','=','0')
		        ->where('status','=','active')
		        ->select('*')
		        ->get();
		
		$taluka=DB::table('area_masters')
					->where('parent_id','!=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->get();
		$gram=DB::table('area_masters')
					->where('parent_id','!=','0')
					->where('super_id','!=','0')
					->where('status','=','active')
					->get();
		$jillaselect=DB::table('area_masters')
					->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$talukaselect=DB::table('area_masters')
					->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$gramselect=DB::table('area_masters')
					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		return view('backend.installation_format',compact('survey_i','demo','jilla','talukadata','taluka','gram','jillaselect','talukaselect','gramselect','areanamesss','jillanamesss','gramnamesss'));
         
        
       
	}
	public function survey_format(Request $request)
	{
		
        $survey_i=DB::table('area_masters')
		            ->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					
					->get();
		$jilla_i=DB::table('area_masters')
					->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$gram_i=DB::table('area_masters')
					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$jillanamesss=array();
		foreach($survey_i as $d)
			{
				$jillaaname=DB::table('area_masters')
							->where('id','=',$d->jilla_id)
							->get();
				$tname=$jillaaname[0]->area_name;
				array_push($jillanamesss,$tname);
				//echo $areanamesss;
			}
		
		$areanamesss=array();
		foreach($survey_i as $d)
		{
			$talukaname=DB::table('area_masters')
					->where('id','=',$d->taluka_id)
					->get();
			$tname=$talukaname[0]->area_name;
			array_push($areanamesss,$tname);
			//echo $areanamesss;
		}
		
		$gramnamesss=array();
		foreach($survey_i as $d)
		{
			$gramname=DB::table('area_masters')
							->where('id','=',$d->gram_id)
							->get();
			$tname=$gramname[0]->area_name;
			array_push($gramnamesss,$tname);
			//echo $areanamesss;
		}
		
		//echo $gramid;
		//exit();
		$demo="";
		
		
		
		//var_dump($demo);
		//exit();
		$jilla=DB::table('area_masters')
					->where('parent_id','=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->get();
		$talukadata=DB::table('area_masters')
		        ->where('parent_id','!=','0')
		        ->where('super_id','=','0')
		        ->where('status','=','active')
		        ->select('*')
		        ->get();
		
		$taluka=DB::table('area_masters')
					->where('parent_id','!=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->get();
		$gram=DB::table('area_masters')
					->where('parent_id','!=','0')
					->where('super_id','!=','0')
					->where('status','=','active')
					->get();
		$jillaselect=DB::table('area_masters')
					->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$talukaselect=DB::table('area_masters')
					->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$gramselect=DB::table('area_masters')
					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		return view('backend.survey_format',compact('survey_i','demo','jilla','talukadata','taluka','gram','jillaselect','talukaselect','gramselect','areanamesss','jillanamesss','gramnamesss'));
         
        
       
	}
	public function survey_gram_info(Request $request)
	{
	
		
        $survey_i=DB::table('area_masters')
		            ->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
		            ->join('gramuserallots','gramuserallots.gram','=','area_masters.id')
		            ->where('gramuserallots.user_id','=',$login)
					->select('*','area_masters.id as aid','gramuserallots.id as gramid','survey_infos.id as survey_id')
					->orderByRaw('cast(survey_infos.tax_number as unsigned)')
					->get();
		$jilla_i=DB::table('area_masters')
					->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$gram_i=DB::table('area_masters')
					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$jillanamesss=array();
		foreach($survey_i as $d)
			{
				$jillaaname=DB::table('area_masters')
							->where('id','=',$d->jilla_id)
							->get();
				$tname=$jillaaname[0]->area_name;
				array_push($jillanamesss,$tname);
				//echo $areanamesss;
			}
		
		$areanamesss=array();
		foreach($survey_i as $d)
		{
			$talukaname=DB::table('area_masters')
					->where('id','=',$d->taluka_id)
					->get();
			$tname=$talukaname[0]->area_name;
			array_push($areanamesss,$tname);
			//echo $areanamesss;
		}
		
		$gramnamesss=array();
		foreach($survey_i as $d)
		{
			$gramname=DB::table('area_masters')
							->where('id','=',$d->gram_id)
							->get();
			$tname=$gramname[0]->area_name;
			array_push($gramnamesss,$tname);
			//echo $areanamesss;
		}
		
		//echo $gramid;
		//exit();
		$demo="";
		
		foreach($survey_i as $k)
		{
			$sur=$k->details;
			//echo $sur;
			$decode=json_decode($sur);
			//var_dump($decode);
			$demo=$decode;
			
		
		}
		
		//var_dump($demo);
		//exit();
     	$jilla=DB::table('area_masters')
					->join(\DB::raw("(select distinct jilla from gramuserallots) as jname"),'jname.jilla','=','area_masters.id')
					->join('gramuserallots','gramuserallots.jilla','=','area_masters.id')
					->where('gramuserallots.user_id','=',$login)
					->select('gramuserallots.*','area_masters.*')
					->get();
		$talukadata=DB::table('area_masters')
		        ->where('parent_id','!=','0')
		        ->where('super_id','=','0')
		        ->where('status','=','active')
		        ->select('*')
		        ->get();
		
		$taluka=DB::table('area_masters')
					->where('parent_id','!=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->get();
		$gram=DB::table('area_masters')
					->where('parent_id','!=','0')
					->where('super_id','!=','0')
					->where('status','=','active')
					->get();
		$jillaselect=DB::table('area_masters')
					->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$talukaselect=DB::table('area_masters')
					->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		$gramselect=DB::table('area_masters')
					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
					->select('*','area_masters.id as aid')
					->get();
		return view('backend.survey_view_gram',compact('survey_i','demo','jilla','talukadata','taluka','gram','jillaselect','talukaselect','gramselect','areanamesss','jillanamesss','gramnamesss','login'));
       
	}
    public function displayallsearch(Request $request)
    {

    	 if($request->url!='dashboard'){
            Session::forget('dashboardurl');

          }
          
        $jilla=$request->get('jillase');
        $taluka=$request->get('talukase');
        $gram=$request->get('gramse');
        if(isset($jilla) && $jilla=="" || $taluka=="" || $gram=="")
        {
           return redirect(url('survey_info'))->with('error',"Please Select Values");
        }
        else
        {
        DB::enableQueryLog();
        $survey=DB::table('area_masters')
		            ->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->where('survey_infos.jilla_id','=',$jilla)
					->where('survey_infos.taluka_id','=',$taluka)
					->where('survey_infos.gram_id','=',$gram)
					 ->join('pole_details','pole_details.pole_id','=','survey_infos.id');

					
					

					if(!empty($request->get('from_date'))){
						$survey->whereDate('survey_infos.created_at',date('Y-m-d',strtotime($request->get('from_date'))));						
					}
					if(!empty($request->get('phase_no'))){
						$survey->where('survey_infos.phase_no',$request->get('phase_no'));						
					}

				$survey_i=$survey->select('*','survey_infos.created_at as screated_at','survey_infos.id as sid','area_masters.id as aid',DB::Raw('substring(`pole_num`,7,4) as pole'),DB::Raw('SUBSTRING_INDEX(`survey_infos`.`switch_point`,"/",1) as port'))

					->orderByRaw('CAST(port AS UNSIGNED),SUBSTR(pole FROM 1 FOR 1), CAST(SUBSTR(pole FROM 2) AS UNSIGNED)')
					->where(function ($query) {
                       $query->where('approve_status', '=', 1)
                             ->orWhere('approve_status', '=', 0);
                             })
					->get();
					//dd($survey_i);


					 $rejectedreport= survey_info::where('jilla_id',$jilla)->where('taluka_id',$taluka)->where('gram_id',$gram)->where(function ($query) use ($request) {
                       $query->where('phase_no',$request->get('phase_no'));
                             })->first();
					 // /dd($rejectedreport);
					
		$r=sizeof($survey_i);
			$jillanamesss=array();
			$jillaaname=DB::table('area_masters')
							->where('id','=',$jilla)
							->get();
				$tname=$jillaaname[0]->area_name;
				array_push($jillanamesss,$tname);
		$areanamesss=array();
		$talukaname=DB::table('area_masters')
					->where('id','=',$taluka)
					->get();
			$tname=$talukaname[0]->area_name;
			array_push($areanamesss,$tname);
		
		$gramnamesss=array();
		$gramname=DB::table('area_masters')
							->where('id','=',$gram)
							->get();
			$tname=$gramname[0]->area_name;
			array_push($gramnamesss,$tname);
			
     $enabledates= survey_info::where('jilla_id',$jilla)->where('taluka_id',$taluka)->where('gram_id',$gram)->pluck('created_at');
		$edates=[];
		foreach ($enabledates as $key => $value) {
			$edate= date('d-m-Y',strtotime($value));
			if(!in_array($edate,$edates)){
				$edates[]=$edate;
			}
		}

		$phase = survey_info::where(['jilla_id'=>$jilla,'taluka_id'=>$taluka,'gram_id'=>$gram])->orderby('phase_no',"ASC")->groupby('phase_no')->get();
		

		return view('backend.display_info',compact('survey_i','jilla','taluka','gram','areanamesss','jillanamesss','gramnamesss','request','edates','phase','rejectedreport'));
		
        }
    }

 

	public function displaysearchallot(Request $request)
	{
		
		$userid=$request->get('user_id');
        $grams=$request->get('grams');	
        $survey_i=DB::table('area_masters')
					->join('gramuserallots','gramuserallots.gram','=','area_masters.id')
					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
					->where('gramuserallots.user_id','=',$userid)
					->where('gramuserallots.gram','=',$grams)
					->select('*','area_masters.id as aid')
					->get();	
			$details = area_master::with(['parent_details','super_details'])->where("id",$survey_i[0]->gram_id??0)->first();
		return view('backend.display_info_user',compact('survey_i','details'));
		
	}
	public function displaysearchallotusers(Request $request)
	{
		$userid=$request->get('user_id');
        	$survey_i=DB::table('area_masters')
					->join('gramuserallots','gramuserallots.gram','=','area_masters.id')
					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
					->where('gramuserallots.user_id','=',$userid)
					->select('*','area_masters.id as aid')
					->orderby('pole_num')
					->get();
				$details = area_master::with(['parent_details','super_details'])->where("id",$survey_i[0]->gram_id??0)->first();
			
		return view('backend.display_info_user',compact('survey_i','details'));
		

	}
    public function displayallsearchgram(Request $request)
    {
        
        $jilla=$request->get('jillase');
        $taluka=$request->get('talukase');
        $gram=$request->get('gramse');
        if(isset($jilla) && $jilla=="" || $taluka=="" || $gram=="")
        {
            echo "<script>alert('Please Select Values')</script>";
            echo "<script>window.location.href='/survey_info'</script>";
        }
        else
        {
            $jid=DB::table('area_masters')
                    ->where('id','=',$jilla)
                    ->get();
            $tid=DB::table('area_masters')
                    ->where('id','=',$taluka)
                    ->get();
            $gid=DB::table('area_masters')
                    ->where('id','=',$gram)
                    ->get();
            $jjid=$jid[0]->id;
            $ttid=$tid[0]->id;
            $ggid=$gid[0]->id;
            //var_dump($jjid);
            /*echo "$jjid $ttid $ggid";
            exit();*/
            $survey_i=DB::table('area_masters')
    		            ->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
    					->where('survey_infos.jilla_id','=',$jjid)
    					->where('survey_infos.taluka_id','=',$ttid)
    					->where('survey_infos.gram_id','=',$ggid)
    					->select('*','area_masters.id as aid')
    					->orderByRaw('cast(survey_infos.tax_number as unsigned)')
    					->get();
    		$r=sizeof($survey_i);
    		/*echo $r;
    		exit();*/
    		/*var_dump($survey_i);
    		exit();*/
    		if($r === "")
    		{
    		    /*echo "inside if";
    		    exit();*/
        		    $jilla_i=DB::table('area_masters')
        					->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
        					->select('*','area_masters.id as aid')
        					->get();
        		$gram_i=DB::table('area_masters')
        					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
        					->select('*','area_masters.id as aid')
        					->get();
        		$jillanamesss=array();
        		foreach($survey_i as $d)
        			{
        				$jillaaname=DB::table('area_masters')
        							->where('id','=',$d->jilla_id)
        							->get();
        				$tname=$jillaaname[0]->area_name;
        				array_push($jillanamesss,$tname);
        				//echo $areanamesss;
        			}
        		
        		$areanamesss=array();
        		foreach($survey_i as $d)
        		{
        			$talukaname=DB::table('area_masters')
        					->where('id','=',$d->taluka_id)
        					->get();
        			$tname=$talukaname[0]->area_name;
        			array_push($areanamesss,$tname);
        			//echo $areanamesss;
        		}
        		
        		$gramnamesss=array();
        		foreach($survey_i as $d)
        		{
        			$gramname=DB::table('area_masters')
        							->where('id','=',$d->gram_id)
        							->get();
        			$tname=$gramname[0]->area_name;
        			array_push($gramnamesss,$tname);
        			//echo $areanamesss;
        		}
        		//var_dump($survey_i);
        		//exit();
        		//echo $gramid;
        		//exit();
        		$demo="";
        		
        		foreach($survey_i as $k)
        		{
        			$sur=$k->details;
        			//echo $sur;
        			$decode=json_decode($sur);
        			//var_dump($decode);
        			$demo=$decode;
        			
        		
        		}
        		
        		//var_dump($demo);
        		//exit();
        		$jilla=DB::table('area_masters')
        					->where('parent_id','=','0')
        					->where('super_id','=','0')
        					->where('status','=','active')
        					->get();
        		$talukadata=DB::table('area_masters')
        		        ->where('parent_id','!=','0')
        		        ->where('super_id','=','0')
        		        ->where('status','=','active')
        		        ->select('*')
        		        ->get();
        		
        		$taluka=DB::table('area_masters')
        					->where('parent_id','!=','0')
        					->where('super_id','=','0')
        					->where('status','=','active')
        					->get();
        		$gram=DB::table('area_masters')
        					->where('parent_id','!=','0')
        					->where('super_id','!=','0')
        					->where('status','=','active')
        					->get();
        		$jillaselect=DB::table('area_masters')
        					->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
        					->select('*','area_masters.id as aid')
        					->get();
        		$talukaselect=DB::table('area_masters')
        					->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
        					->select('*','area_masters.id as aid')
        					->get();
        		$gramselect=DB::table('area_masters')
        					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
        					->select('*','area_masters.id as aid')
        					->get();
							$userlist=DB::table('gramusers')
							->where('status','=','active')
							->select('*')
							->get();
        		return view('backend.survey_search_empty_gram',compact('userlist','survey_i','demo','jilla','talukadata','taluka','gram','jillaselect','talukaselect','gramselect','areanamesss','jillanamesss','gramnamesss','login'));
        		    
    		}
    		else if($r>0){
    		    /*echo "inside else";
    		    exit();*/
        		$jilla_i=DB::table('area_masters')
        					->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
        					->select('*','area_masters.id as aid')
        					->get();
        		$gram_i=DB::table('area_masters')
        					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
        					->select('*','area_masters.id as aid')
        					->get();
        		$jillanamesss=array();
        		foreach($survey_i as $d)
        			{
        				$jillaaname=DB::table('area_masters')
        							->where('id','=',$d->jilla_id)
        							->get();
        				$tname=$jillaaname[0]->area_name;
        				array_push($jillanamesss,$tname);
        				//echo $areanamesss;
        			}
        		
        		$areanamesss=array();
        		foreach($survey_i as $d)
        		{
        			$talukaname=DB::table('area_masters')
        					->where('id','=',$d->taluka_id)
        					->get();
        			$tname=$talukaname[0]->area_name;
        			array_push($areanamesss,$tname);
        			//echo $areanamesss;
        		}
        		
        		$gramnamesss=array();
        		foreach($survey_i as $d)
        		{
        			$gramname=DB::table('area_masters')
        							->where('id','=',$d->gram_id)
        							->get();
        			$tname=$gramname[0]->area_name;
        			array_push($gramnamesss,$tname);
        			//echo $areanamesss;
        		}
        		
        		//echo $gramid;
        		//exit();
        		$demo="";
        		
        		foreach($survey_i as $k)
        		{
        			$sur=$k->details;
        			//echo $sur;
        			$decode=json_decode($sur);
        			//var_dump($decode);
        			$demo=$decode;
        			
        		
        		}
        		
        		//var_dump($demo);
        		//exit();
            	$jilla=DB::table('area_masters')
            					->join(\DB::raw("(select distinct jilla from gramuserallots) as jname"),'jname.jilla','=','area_masters.id')
            					->join('gramuserallots','gramuserallots.jilla','=','area_masters.id')
            					->where('gramuserallots.user_id','=',$login)
            					->select('gramuserallots.*','area_masters.*')
            					->get();
        		$talukadata=DB::table('area_masters')
        		        ->where('parent_id','!=','0')
        		        ->where('super_id','=','0')
        		        ->where('status','=','active')
        		        ->select('*')
        		        ->get();
        		
        		$taluka=DB::table('area_masters')
        					->where('parent_id','!=','0')
        					->where('super_id','=','0')
        					->where('status','=','active')
        					->get();
        		$gram=DB::table('area_masters')
        					->where('parent_id','!=','0')
        					->where('super_id','!=','0')
        					->where('status','=','active')
        					->get();
        		$jillaselect=DB::table('area_masters')
        					->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
        					->select('*','area_masters.id as aid')
        					->get();
        		$talukaselect=DB::table('area_masters')
        					->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
        					->select('*','area_masters.id as aid')
        					->get();
        		$gramselect=DB::table('area_masters')
        					->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
        					->select('*','area_masters.id as aid')
        					->get();
							$userlist=DB::table('gramusers')
							->where('status','=','active')
							->select('*')
							->get();
        		return view('backend.print_search_details_gram',compact('userlist','survey_i','demo','jilla','talukadata','taluka','gram','jillaselect','talukaselect','gramselect','areanamesss','jillanamesss','gramnamesss','login'));
    		}
    		else{
    		    echo "<script>alert('No Details Found')</script>";
                echo "<script>window.location.href='/survey_info'</script>";
    		}
        }
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create(Request $request)
    {
		
			$jilla=DB::table('area_masters')
					->where('parent_id','=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->orderBy('area_name', 'ASC')
					->get();
		/*$taluka=DB::table('area_masters')
					->where('parent_id','=','0')
					->get();*/
		$varanan=DB::table('varnans')
					->where('status','=','active')
					->orderby('row_id','asc')
                    ->get();
					
		return view('backend.survey_info',compact('jilla','varanan'));
        
        
    }
	public function survey_info_form(Request $request)
	{
		
			$gramallot=DB::table('gramusers')
					->join('gramuserallots','gramuserallots.user_id','=','gramusers.id')
					->get();
			$jilla=DB::table('area_masters')
					->join(\DB::raw("(select distinct jilla from gramuserallots) as jname"),'jname.jilla','=','area_masters.id')
					->join('gramuserallots','gramuserallots.jilla','=','area_masters.id')
					->where('gramuserallots.user_id','=',$login)
					->select('gramuserallots.*','area_masters.*')
					->get();
			
			
			$taluka=DB::table('area_masters')
					->join('gramuserallots','gramuserallots.taluka','area_masters.id')
					->get();
			$gram=DB::table('area_masters')
					->join('gramuserallots','gramuserallots.gram','area_masters.id')
					->get();
			$varanan=DB::table('varnans')
					->where('status','=','active')
					->orderby('row_id','asc')
                    ->get();
					
		return view('backend.survey_gramform',compact('jilla','taluka','gram','varanan','gramallot','login'));
        
       
	}

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
	public function getgram_id(Request $request)
	{
		// $gram_name=DB::table('area_masters')
		// 			->where('parent_id','=',$name)
		// 			->where('status','=','active')
		// 			->get();
		$id=$request->get('name');
		$sessionid=$request->get('loginid');
		$jillaid=DB::table('area_masters')
					->join('gramuserallots','gramuserallots.gram','=','area_masters.id')
					->where('gramuserallots.user_id','=',$sessionid)
					->where('gramuserallots.taluka','=',$id)
					->distinct()
					->select('gramuserallots.gram','area_masters.*')
					->get();
		//return view('backend.display_taluka_id',compact('jillaid'));
		$resp = "";
		//var_dump($jillaid);
		foreach($jillaid as $gm)
		{
			$resp .="".$gm->id."_".$gm->area_name;
			$resp .="-";
			
		}
		echo $resp;
	}
	public function getgram(Request $request,$name)
	{
		$gram_name=DB::table('area_masters')
					->where('parent_id','=',$name)
					
					->get();
		$resp = "";
		foreach($gram_name as $gm)
		{
			$resp .="".$gm->id."_".$gm->area_name;
			$resp .="-";
			
		}
		echo $resp;
	}
	public function getgramsearch(Request $request,$name)
	{
	    $aname=DB::table('area_masters')
	                ->where('area_name','=',$name)
	                ->get();
	    $arid=$aname[0]->id;
	    
	    $gram_name=DB::table('area_masters')
					->where('parent_id','=',$arid)
					->get();
		$resp = "";
		foreach($gram_name as $gm)
		{
			$resp .="".$gm->id."_".$gm->area_name;
			$resp .="-";
			
		}
		echo $resp;
	    
	}
	public function gramstoredata(Request $request)
	{
		$gram=$request->get('gram_name');
        $tax_num=$request->get('tax_number');
        $surveygram=DB::table('survey_infos')
                        ->where('gram_id','=',$gram)
                        ->where('tax_number','=',$tax_num)
                        ->count();
        if($surveygram==1)
        {
            echo "<script>alert('Already Added This Tax Number To This Gram');</script>";
            echo "<script>window.location.href='/survey_info/create'</script>";
        }
        else
        {
            $varanan=DB::table('varnans')
    					->where('status','=','active')
    					->count();
    		$demo=array();
    		for($i=1; $i<=1; $i++)
    		{
    			for($j=$i; $j<=$varanan; $j++)
    			{
    				$data=array(
    				"varnana_name"=>$request->get('varnan'.$j),
    				"property_name"=>$request->get('property_name'.$j),
    				"yercon"=>$request->get('yercon'.$j),
    				"conyerage"=>$request->get('conyerage'.$j),
    				"floor"=>$request->get('floor'.$j),
    				"ch_fu"=>$request->get('chf'.$j),
    				"ch_me"=>$request->get('chm'.$j)
    				
    				);
    				array_push($demo,$data);
    			}
    		}
    		
    		$d=json_encode($demo);
    		$mimg=$request->hasFile('img');
    		$mapimg=$request->hasFile('mapimg');
    	
    	    $survey_name = array();
    	    $n=$request->get('survey_name');
    	    array_push($survey_name,$n);
    	    array_push($survey_name,$request->get('wife_mobile_number'));
    	    array_push($survey_name,$request->get('old_tax_number'));
    	    array_push($survey_name,$request->get('s_s_number'));
    	    array_push($survey_name,$request->get('s_s_s_m'));
    	    array_push($survey_name,$request->get('group_no'));
    	    array_push($survey_name,$request->get('plot_no'));
    	    array_push($survey_name,$request->get('gate'));
    	    array_push($survey_name,$request->hasFile('img'));
    		array_push($survey_name,$request->hasFile('mapimg'));
    			if($mimg==1 && $mapimg==1)
        		{
        			$files = $request->file('img');
        			$file_name1 = $files->getClientOriginalName();
        			$mapfiles = $request->file('mapimg');
        			$file_name2 = $mapfiles->getClientOriginalName();
        			$survey_info=new survey_info([
        			'jilla_id'=>$request->get('jilla_id'),
        			'taluka_id'=>$request->get('taluka_name'),
        			'gram_id'=>$request->get('gram_name'),
        			'anu_karmank'=>$request->get('anu_kra'),
        			'name'=>$request->get('self_declaration_name'),
        			'gram_name'=>$request->get('self_declaration_gram_name'),
        			'name2'=>$request->get('bhogwatdar_name'),
        			'survey_name'=>$request->get('survey_name'),
        			'mbl'=>$request->get('mobile_number'),
        			'adhar'=>$request->get('adhar_number'),
        			'old_tax_no'=>$request->get('wife_mobile_number'),
        			's_s_no'=>$request->get('s_s_number'),
        			's_s_m'=>$request->get('s_s_s_m'),
        			'group_no'=>$request->get('group_no'),
        			'plot_no'=>$request->get('plot_no'),
        			'total_sqft'=>$request->get('tolsqft'),
        			'north_south'=>$request->get('north_south'),
        			'east_west'=>$request->get('east_west'),
        			'details'=>$d,
        			'north'=>$request->get('north'),
        			'south'=>$request->get('south'),
        			'east'=>$request->get('east'),
        			'west'=>$request->get('west'),
        			'gate'=>$request->get('gate'),
        			'wife_mbl'=>$request->get('wife_mobile_number'),
        			'wife_adhar'=>$request->get('wife_adhar_number'),
        			'img'=>$file_name1,
        			'mapimg'=>$file_name2,
        			'nal'=>$request->get('nalcon'),
        			'washroom'=>$request->get('washroom'),
        			'tax_number'=>$request->get('tax_number'),
        			'status'=>'active',
    				'added_by'=>'gramuser'
        			]);
        			$survey_info->save();
        		   $files->move('image/',$file_name1);
        		   $mapfiles->move('image/',$file_name2);
        		    //exit();
        			return redirect('/survey_gram_info');
        		}else if($request->hasFile('img'))
        		{
        			$files = $request->file('img');
        			$file_name1 = $files->getClientOriginalName();
        			$survey_info=new survey_info([
        			'jilla_id'=>$request->get('jilla_id'),
        			'taluka_id'=>$request->get('taluka_name'),
        			'gram_id'=>$request->get('gram_name'),
        			'anu_karmank'=>$request->get('anu_kra'),
        			'name'=>$request->get('self_declaration_name'),
        			'gram_name'=>$request->get('self_declaration_gram_name'),
        			'name2'=>$request->get('bhogwatdar_name'),
        			'survey_name'=>$request->get('survey_name'),
        			'mbl'=>$request->get('mobile_number'),
        			'adhar'=>$request->get('adhar_number'),
        			'old_tax_no'=>$request->get('old_tax_number'),
        			's_s_no'=>$request->get('s_s_number'),
        			's_s_m'=>$request->get('s_s_s_m'),
        			'group_no'=>$request->get('group_no'),
        			'plot_no'=>$request->get('plot_no'),
        			'total_sqft'=>$request->get('tolsqft'),
        			'north_south'=>$request->get('north_south'),
        			'east_west'=>$request->get('east_west'),
        			'details'=>$d,
        			'north'=>$request->get('north'),
        			'south'=>$request->get('south'),
        			'east'=>$request->get('east'),
        			'west'=>$request->get('west'),
        			'gate'=>$request->get('gate'),
        			'wife_mbl'=>$request->get('wife_mobile_number'),
        			'wife_adhar'=>$request->get('wife_adhar_number'),
        			'img'=>$file_name1,
        			'mapimg'=>'null',
        			'nal'=>$request->get('nalcon'),
        			'washroom'=>$request->get('washroom'),
        			'tax_number'=>$request->get('tax_number'),
        			'status'=>'active',
    				'added_by'=>'gramuser'
        			]);
        			$survey_info->save();
        			$files->move('image/',$file_name1);
        			return redirect('/survey_gram_info');
        		}else if($request->hasFile('mapimg'))
        		{
        			$files = $request->file('mapimg');
        			$file_name1 = $files->getClientOriginalName();
        			$survey_info=new survey_info([
        			'jilla_id'=>$request->get('jilla_id'),
        			'taluka_id'=>$request->get('taluka_name'),
        			'gram_id'=>$request->get('gram_name'),
        			'anu_karmank'=>$request->get('anu_kra'),
        			'name'=>$request->get('self_declaration_name'),
        			'gram_name'=>$request->get('self_declaration_gram_name'),
        			'name2'=>$request->get('bhogwatdar_name'),
        			'survey_name'=>$request->get('survey_name'),
        			'mbl'=>$request->get('mobile_number'),
        			'adhar'=>$request->get('adhar_number'),
        		    'old_tax_no'=>$request->get('old_tax_number'),
        			's_s_no'=>$request->get('s_s_number'),
        			's_s_m'=>$request->get('s_s_s_m'),
        			'group_no'=>$request->get('group_no'),
        			'plot_no'=>$request->get('plot_no'),
        			'total_sqft'=>$request->get('tolsqft'),
        			'north_south'=>$request->get('north_south'),
        			'east_west'=>$request->get('east_west'),
        			'details'=>$d,
        			'north'=>$request->get('north'),
        			'south'=>$request->get('south'),
        			'east'=>$request->get('east'),
        			'west'=>$request->get('west'),
        			'gate'=>$request->get('gate'),
        			'wife_mbl'=>$request->get('wife_mobile_number'),
        			'wife_adhar'=>$request->get('wife_adhar_number'),
        			'img'=>'null',
        			'mapimg'=>$file_name1,
        			'nal'=>$request->get('nalcon'),
        			'washroom'=>$request->get('washroom'),
        			'tax_number'=>$request->get('tax_number'),
        			'status'=>'active',
    				'added_by'=>'gramuser'
        			]);
        			$survey_info->save();
        	        $files->move('image/',$file_name1);
        			return redirect('/survey_gram_info');
        		} 
        		else
        		{
        			$survey_info=new survey_info([
        				'jilla_id'=>$request->get('jilla_id'),
        				'taluka_id'=>$request->get('taluka_name'),
        				'gram_id'=>$request->get('gram_name'),
        				'anu_karmank'=>$request->get('anu_kra'),
        				'name'=>$request->get('self_declaration_name'),
        				'gram_name'=>$request->get('self_declaration_gram_name'),
        				'name2'=>$request->get('bhogwatdar_name'),
        				'survey_name'=>$request->get('survey_name'),
        				'mbl'=>$request->get('mobile_number'),
        				'adhar'=>$request->get('adhar_number'),
                		'old_tax_no'=>$request->get('old_tax_number'),
            			's_s_no'=>$request->get('s_s_number'),
            			's_s_m'=>$request->get('s_s_s_m'),
            			'group_no'=>$request->get('group_no'),
            			'plot_no'=>$request->get('plot_no'),
        				'total_sqft'=>$request->get('tolsqft'),
        				'north_south'=>$request->get('north_south'),
        				'east_west'=>$request->get('east_west'),
        				'details'=>$d,
        				'north'=>$request->get('north'),
        				'south'=>$request->get('south'),
        				'east'=>$request->get('east'),
        				'west'=>$request->get('west'),
        				'gate'=>$request->get('gate'),
        			    'wife_mbl'=>$request->get('wife_mobile_number'),
        				'wife_adhar'=>$request->get('wife_adhar_number'),
        				'img'=>'null',
        				'mapimg'=>'null',
        				'nal'=>$request->get('nalcon'),
        				'washroom'=>$request->get('washroom'),
        				'tax_number'=>$request->get('tax_number'),
        				'status'=>'active',
    					'added_by'=>'gramuser'
        				]);
        			$survey_info->save();
        			return redirect('/survey_gram_info');
        		}
        }
	}
    public function store(Request $request)
    {
        // $gram=$request->get('gram_name');
        // $tax_num=$request->get('tax_number');
        // $surveygram=DB::table('survey_infos')
        //                 ->where('gram_id','=',$gram)
        //                 ->where('tax_number','=',$tax_num)
        //                 ->count();
        // if($surveygram==1)
        // {
        //     echo "<script>alert('Already Added This Tax Number To This Gram');</script>";
        //     echo "<script>window.location.href='/survey_info/create'</script>";
        // }
        // else
        // {
        //     $varanan=DB::table('varnans')
    	// 				->where('status','=','active')
    	// 				->count();
    	// 	$demo=array();
    	// 	for($i=1; $i<=1; $i++)
    	// 	{
    	// 		for($j=$i; $j<=$varanan; $j++)
    	// 		{
    	// 			$data=array(
    	// 			"varnana_name"=>$request->get('varnan'.$j),
    	// 			"property_name"=>$request->get('property_name'.$j),
    	// 			"yercon"=>$request->get('yercon'.$j),
    	// 			"conyerage"=>$request->get('conyerage'.$j),
    	// 			"floor"=>$request->get('floor'.$j),
    	// 			"ch_fu"=>$request->get('chf'.$j),
    	// 			"ch_me"=>$request->get('chm'.$j)
    				
    	// 			);
    	// 			array_push($demo,$data);
    	// 		}
    	// 	}
    		
    	// 	$d=json_encode($demo);
    	// 	$mimg=$request->hasFile('img');
    	// 	$mapimg=$request->hasFile('mapimg');
    	
    	//     $survey_name = array();
    	//     $n=$request->get('survey_name');
    	//     array_push($survey_name,$n);
    	//     array_push($survey_name,$request->get('wife_mobile_number'));
    	//     array_push($survey_name,$request->get('old_tax_number'));
    	//     array_push($survey_name,$request->get('s_s_number'));
    	//     array_push($survey_name,$request->get('s_s_s_m'));
    	//     array_push($survey_name,$request->get('group_no'));
    	//     array_push($survey_name,$request->get('plot_no'));
    	//     array_push($survey_name,$request->get('gate'));
    	//     array_push($survey_name,$request->hasFile('img'));
    	// 	array_push($survey_name,$request->hasFile('mapimg'));
    	// 		if($mimg==1 && $mapimg==1)
        // 		{
        			// $files = $request->file('img');
        			// $file_name1 = $files->getClientOriginalName();
        			// $mapfiles = $request->file('mapimg');
        			// $file_name2 = $mapfiles->getClientOriginalName();

    	
       
		$arrt=array();
		for($i=1; $i<=3; $i++)
		{
			$r=$request->get('type'.$i);
			array_push($arrt,$r);
		}
		$list=implode(',',$arrt);
		$arrk=array();
		for($i=1; $i<=5; $i++)
		{
			$kp=$request->get('led_wattage'.$i);
			array_push($arrk,$kp);
		}
       
                  
         $pole_explode= explode('-', $request->get('pole_num'));
         if(count($pole_explode)>1){

         $switch_point = $request->get('switch_point');

		$switch_point_array=['A'=>1,'B'=>2,'C'=>3,'D'=>4,'E'=>5,'F'=>6,'G'=>7,'H'=>8,'I'=>9,'J'=>10,'K'=>11,'L'=>12,'M'=>13,'N'=>14,'O'=>15,'P'=>16,'Q'=>17,'R'=>18,'S'=>19,'T'=>20,'U'=>21,'V'=>22,'W'=>23,'X'=>24,'Y'=>25,'Z'=>26];
		$explode_val= '-';
		foreach ($switch_point_array as $key => $value) {
			
			if($switch_point=="$value/ 1-Ph")
        	{
        		$explode_val= $key;
        	}	
		}
                                         
		$last_pole_num = survey_info::where('switch_point',$request->get('switch_point'))->where('gram_id',$request->get('panchayat_name'))->where('pole_num', 'not like', "%-%")->orderby('id',"desc")->first();

			if(!empty($last_pole_num)){
				$pole_array = explode($explode_val,$last_pole_num->pole_num);

				if(count($pole_array) > 1){
					$new_pole = $pole_explode[0].$explode_val.((int)$pole_array[1]+1);
				}else{
					$new_pole =$pole_explode[0].$explode_val."1";
				}
			}else{
				$new_pole =$pole_explode[0].$explode_val."1";
			}
		}else{
			$new_pole =$request->get('pole_num');
		}


            

		$lpk=implode(',',$arrk);
        			$survey_info=new survey_info([
					's_no'=>$request->get('s_no'),
        			'jilla_id'=>$request->get('district_name'),
        			'taluka_id'=>$request->get('mandal_name'),
        			'gram_id'=>$request->get('panchayat_name'),
        			'location'=>$request->get('location'),
        			'landmark'=>$request->get('landmark'),
        			'maker_name'=>is_array($request->get('maker_name'))?implode('||',$request->get('maker_name')):$request->get('maker_name'),
					'pole_num'=>$new_pole,
        			'road_width'=>$request->get('road_width'),
        			'road_confi'=>$request->get('road_confi'),
        			'switch_point'=>$request->get('switch_point'),
        			'pole_type'=>$request->get('pole_type'),
        			'pole_other'=>$request->get('pole_other'),
        			'pole_side'=>$request->get('pole_side'),
        			'pole_height'=>$request->get('pole_height'),
        			'pole_spacing'=>$request->get('pole_spacing'),
        			'type_of_fixture'=>is_array($request->get('type_of_fixtures'))?implode('||',$request->get('type_of_fixtures')):$request->get('type_of_fixtures'),
        			'fixture_wattage'=>is_array($request->get('fixture_wattage'))?implode('||',$request->get('fixture_wattage')):$request->get('fixture_wattage'),
        			'warranty_existing'=>is_array($request->get('warranty_existing'))?implode('||',$request->get('warranty_existing')):$request->get('warranty_existing'),
        			'Condition_existing'=>$request->get('condition_existing'),
        			'earth_condition'=>$request->get('earth_condition'),
        			'arms_clamps_required'=>$request->get('arms'),
        			'dedicated_available'=>$request->get('dedicated'),
        			'netword_issue_1'=>$request->get('led_lights'),
        			'netword_issue_2'=>$request->get('tree_obstruction'),
        			'LED_wattage'=>$lpk,
        			'remark'=>$request->get('remark_data'),
					'cable_user'=>$request->get('cable_user'),
					'types_of_arms'=>$list,
					'reducer_used'=>$request->get('reducer_used'),
					'minimast_clamp_used'=>$request->get('minimast_clamp_used'),
        			'status'=>'active',
    				'added_by'=>'admin'
        			]);
        			$survey_info->save();
        		  	return redirect('/survey_info');
        			
        		
        
	}

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
		$gram_name=DB::table('area_masters')
					->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->where('survey_infos.id','=',$id)
					->select('*','area_masters.id as aid')
					->get();
		$jilla=DB::table('area_masters')
					->where('parent_id','=','0')
					->where('super_id','=','0')
					->get();
		$taluka=DB::table('area_masters')
					->where('parent_id','!=','0')
					->where('super_id','=','0')
					->get();
		$survey_i=DB::table('area_masters')
					->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->where('survey_infos.id','=',$id)
					->select('*','area_masters.id as aid')
					->get();
		$data="";
		foreach($survey_i as $r)
		{
			$data=$r->details;
		}
		$k=json_decode($data);
		$jillaselect=DB::table('area_masters')
						->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
						->select('*','area_masters.id as aid')
						->where('survey_infos.id','=',$id)
						->get();
		$talukaselect=DB::table('area_masters')
						->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
						->select('*','area_masters.id as aid')
						->where('survey_infos.id','=',$id)
						->get();
		$gramselect=DB::table('area_masters')
						->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
						->select('*','area_masters.id as aid')
						->where('survey_infos.id','=',$id)
						->get();
        return view('backend.survey_show',compact('taluka','gram_name','jilla','k','jillaselect','talukaselect','gramselect'));
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
		
        $gram_name=DB::table('area_masters')
					->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->where('survey_infos.id','=',$id)
					->select('*','area_masters.id as aid')
					->get();
		$jilla=DB::table('area_masters')
					->where('parent_id','=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->get();
		$taluka=DB::table('area_masters')
					->where('parent_id','!=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->get();
		$survey_i=DB::table('survey_infos')
				// 	->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->where('survey_infos.id','=',$id)
					->select('*')
					->get();
		// $data="";
		// foreach($survey_i as $r)
		// {
		// 	$data=$r->details;
		// }
		// $rty=json_decode($data);
		//var_dump($v);
		//exit();
		$jillaselect=DB::table('area_masters')
						->join('survey_infos','survey_infos.jilla_id','=','area_masters.id')
						->select('*','area_masters.id as aid')
						->where('survey_infos.id','=',$id)
						->get();
		$talukaselect=DB::table('area_masters')
						->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
						->select('*','area_masters.id as aid')
						->where('survey_infos.id','=',$id)
						->get();
		$gramselect=DB::table('area_masters')
						->join('survey_infos','survey_infos.gram_id','=','area_masters.id')
						->select('*','area_masters.id as aid')
						->where('survey_infos.id','=',$id)
						->get();
		$varanan=DB::table('varnans')
					->where('status','=','active')
					->orderby('row_id','asc')
                    ->get();
		return view('backend.survey_edit',compact('taluka','gram_name','jilla','jillaselect','talukaselect','gramselect','varanan'));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        
		$arrt=array();
		for($i=1; $i<=3; $i++)
		{
			$r=$request->get('type'.$i);
			array_push($arrt,$r);
		}
		$list=implode(',',$arrt);
		$arrk=array();
		for($i=1; $i<=5; $i++)
		{
			$kp=$request->get('led_wattage'.$i);
			array_push($arrk,$kp);
		}
		$lpk=implode(',',$arrk);
		$survey_i=survey_info::find($id);
        $survey_i->s_no=$request->get('s_no');
		$survey_i->jilla_id=$request->get('district_name');
		$survey_i->taluka_id=$request->get('mandal_name');
		$survey_i->gram_id=$request->get('panchayat_name');
		$survey_i->location=$request->get('location');
		$survey_i->landmark=$request->get('landmark');
		$survey_i->maker_name=$request->get('maker_name');
		$survey_i->pole_num=$request->get('pole_num');
		$survey_i->road_width=$request->get('road_width');
		$survey_i->road_confi=$request->get('road_confi');
		$survey_i->switch_point=$request->get('switch_point');
		$survey_i->pole_type=$request->get('pole_type');
		$survey_i->pole_other=$request->get('pole_other');
		$survey_i->pole_side=$request->get('pole_side');
		$survey_i->pole_height=$request->get('pole_height');
		$survey_i->pole_spacing=$request->get('pole_spacing');
		$survey_i->type_of_fixture=$request->get('type_of_fixtures');
		$survey_i->fixture_wattage=$request->get('fixture_wattage');
		$survey_i->warranty_existing=$request->get('warranty_existing');
		$survey_i->Condition_existing=$request->get('condition_existing');
		$survey_i->earth_condition=$request->get('earth_condition');
		$survey_i->arms_clamps_required=$request->get('arms');
		$survey_i->dedicated_available=$request->get('dedicated');
		$survey_i->netword_issue_1=$request->get('led_lights');
		$survey_i->netword_issue_2=$request->get('tree_obstruction');
		$survey_i->LED_wattage=$lpk;
		$survey_i->remark=$request->get('remark_data');
		$survey_i->cable_user=$request->get('cable_user');
		$survey_i->types_of_arms=$list;
		$survey_i->reducer_used=$request->get('reducer_used');
		$survey_i->minimast_clamp_used=$request->get('minimast_clamp_used');
		$survey_i->status='active';
		$survey_i->added_by='admin';
        $survey_i->update();
        return redirect('/survey_info');
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
       $survey_i=survey_info::find($id);
		$survey_i->delete();
		return redirect('/survey_info');
    }
	public function apistore(Request $request)
	{
		 $validation = Validator::make(
            $request->all(),
            [
                'type1' => 'required',
                'type2' => 'required',
                'type3' => 'required',
                'type4' => 'required',
                'type5' => 'required',
                'type6' => 'required',
                'suggested_led_wattage1' => 'required',
                'suggested_led_wattage2' => 'nullable',
                'suggested_led_wattage3' => 'nullable',
                'suggested_led_wattage4' => 'nullable',
                'suggested_led_wattage5' => 'nullable',
                'suggested_led_wattage6' => 'nullable',
                'type_of_fixtures1' => 'required',
                'type_of_fixtures2' => 'required',
                'type_of_fixtures3' => 'required',               
                'type_of_fixtures4' =>  'required',
                'type_of_fixtures5' => 'required',
                'type_of_fixtures6'=>'required',
                'fixture_wattage1' => 'required',
                'fixture_wattage2' => 'required',
                'fixture_wattage3' => 'required',               
                'fixture_wattage4' =>  'required',
                'fixture_wattage5' => 'required',
                'fixture_wattage6'=>'required',
                'warranty_existing1' => 'required',
                'warranty_existing2' => 'required',
                'warranty_existing3' => 'required',               
                'warranty_existing4' =>  'required',
                'warranty_existing5' => 'required',
                'warranty_existing6'=>'required',
                'maker_name1' => 'required',
                'maker_name2' => 'required',
                'maker_name3' => 'required',               
                'maker_name4' =>  'required',
                'maker_name5' => 'required',
                'maker_name6'=>'required',
                'no_of_lights' => 'required',
                
                's_no'=>'required',
                'district_name' => 'required',
                 'mandal_name' => 'required',
                'panchayat_name' => 'required',
                'location' => 'required',
                'landmark' => 'required',
                'road_width' => 'required',
                'road_confi' => 'required',
                'switch_point' => 'required',
                'pole_type' => 'required',
                'pole_other' => 'required',
                 'pole_side' => 'required',
                 'pole_height' => 'required',
                'pole_spacing' => 'required',
                'condition_existing' => 'required',
                'earth_condition' => 'required',
                'arms' => 'required',
                'dedicated' => 'required',
                'led_lights' => 'required',
                'tree_obstruction' => 'required',
                'remark_data' => 'required',
                'cable_user' => 'required',
                'reducer_used' => 'required',
                'minimast_clamp_used' => 'required',

                'user_id' => 'required'
            ]);

        if ($validation->fails()) {
            $params = request()->all();

           $data=["error"=>true,"status"=>'failed',"error_msg"=>$validation->getMessageBag()->first(),"request"=>$params];
                return  json_encode($data);
         }
		$demo=array();
		$arrt=array();
		$arr=array();
		for($i=1; $i<=6; $i++)
		{
			$r=$request->get('type'.$i);
			array_push($arrt,$r);
		}
		$list=implode('||',$arrt);
		$suggested_led_wattage=[];
		$led_series_with_make=[];
		$type_of_fixture=[];$fixture_wattage=[];$warranty_existing=[];
		$maker_name=[];$led_light_serial=[]; $temp_led_wattages=[];
		for($i=1;$i<=6;$i++){

			$type_of_fixture[]=$request->get('type_of_fixtures'.$i);
			$fixture_wattage[]=$request->get('fixture_wattage'.$i);
			$warranty_existing[]=$request->get('warranty_existing'.$i);
			$maker_name[]=$request->get('maker_name'.$i);
			$suggested_led_wattage[]=$request->get('suggested_led_wattage'.$i);

			$newmake = str_replace(" ","_",$request->get('maker_name'.$i))."_".str_replace(" ","",$request->get('suggested_led_wattage'.$i))."_";

			$deleted_number = deleted_survey::where(['led_wattage'=>$request->get('suggested_led_wattage'.$i),'maker_name'=>$request->get('maker_name'.$i)])->first();

			if(!empty($deleted_number)){
				$newmake = str_replace(" ","_",$request->get('maker_name'.$i))."_".str_replace(" ","",$request->get('suggested_led_wattage'.$i))."_";
				$led_light_serial[]=$deleted_number->serial_num;
				$led_series_with_make[]=$newmake.$deleted_number->serial_num;
				$deleted_number->delete();
			}else{

			if($request->get('suggested_led_wattage'.$i) != "N/A" && $request->get('suggested_led_wattage'.$i) != "-NA-"){

				$lastwattage=survey_info::where('LED_wattage', 'like', '%' . $request->get('suggested_led_wattage'.$i) . '%')->where('maker_name', 'like', '%' . $request->get('maker_name'.$i) . '%')->orderby('id',"desc")->first();

				if(!empty($lastwattage)){
					$single_wattage_array=explode('||',$lastwattage->LED_wattage);
					$single_maker_array=explode('||',$lastwattage->maker_name);
						$serial_key="N/A";


						for($k=0;$k<count($single_wattage_array);$k++){
							if($single_wattage_array[$k]==$request->get('suggested_led_wattage'.$i) && $single_maker_array[$k] ==$request->get('maker_name'.$i)){
								$serial_key = $k;
							}
						}

						// if($i==2){
						// 	dd($serial_key,$single_wattage_array,$single_maker_array);
						// }
						
					$single_serial_array = explode('||',$lastwattage->led_serial_num);
					if(isset($single_serial_array[$serial_key])){
						if($single_serial_array[$serial_key] !="N/A"&& empty($temp_led_wattages)){
							$led_light_serial[]=sprintf("%02d",(int)$single_serial_array[$serial_key]+1);
							$led_series_with_make[]=$newmake.(sprintf("%02d",(int)$single_serial_array[$serial_key]+1));
							$temp_led_wattages[$newmake.$single_wattage_array[$serial_key]]=sprintf("%02d",(int)$single_serial_array[$serial_key]+1);
						}elseif(!empty($temp_led_wattages)){
							if(isset($temp_led_wattages[$newmake.$single_wattage_array[$serial_key]])){
								//dd("madhu");
							$led_light_serial[]=sprintf("%02d",$temp_led_wattages[$newmake.$single_wattage_array[$serial_key]]+1);
							$led_series_with_make[]=$newmake.(sprintf("%02d",$temp_led_wattages[$newmake.$single_wattage_array[$serial_key]]+1));
							$temp_led_wattages[$newmake.$single_wattage_array[$serial_key]]=sprintf("%02d",$temp_led_wattages[$newmake.$single_wattage_array[$serial_key]]+1);
							}else{								
							$led_light_serial[]=sprintf("%02d",(int)$single_serial_array[$serial_key]+1);
							$led_series_with_make[]=$newmake.(sprintf("%02d",(int)$single_serial_array[$serial_key]+1));
							//dd((int)$single_serial_array[$serial_key]);
							$temp_led_wattages[$newmake.$single_wattage_array[$serial_key]]=sprintf("%02d",(int)$single_serial_array[$serial_key]+1);
							}
						}
						else{
							$led_light_serial[]=1;
							$led_series_with_make[]=$newmake."01";
							$temp_led_wattages[$newmake.$single_wattage_array[$serial_key]]=sprintf("%02d",(int)$single_serial_array[$serial_key]+1);
						}
					}else{
						if(!empty($temp_led_wattages)){
							//dd($i);
							if(isset($single_wattage_array[$serial_key])){
							if(isset($temp_led_wattages[$newmake.$single_wattage_array[$serial_key]])){
							$led_light_serial[]=sprintf("%02d",$temp_led_wattages[$newmake.$single_wattage_array[$serial_key]]+1);
							$led_series_with_make[]=$newmake.(sprintf("%02d",(int)$single_serial_array[$serial_key]+1));
							$temp_led_wattages[$newmake.$single_wattage_array[$serial_key]]=sprintf("%02d",$temp_led_wattages[$newmake.$single_wattage_array[$serial_key]]+1);
							}else{
								$led_light_serial[]=01;
								$led_series_with_make[]=$newmake."01";
								$temp_led_wattages[$newmake.$request->get('suggested_led_wattage'.$i)]=1;
								}
							}
						}else{
							$led_light_serial[]=01;
							$led_series_with_make[]=$newmake."01";
							$temp_led_wattages[$newmake.$request->get('suggested_led_wattage'.$i)]=01;
						}
						
					}
					
					}else{
						$led_light_serial[]=01;
						$led_series_with_make[]=$newmake."01";
						$temp_led_wattages[$newmake.$request->get('suggested_led_wattage'.$i)]=01;
					}
				}else{
					$led_light_serial[]="N/A";
					$led_series_with_make[]="N/A";
				}
			}
			
		}
		//dd($led_light_serial);
		
		$gid = $request->get('panchayat_name');
		
		$grm = area_master::find($gid);
		//dd(gram);
		
		$gcode = $grm->code;

	
		$psize = survey_info::where('gram_id','=',$gid)->count();
		$p_num = $gcode."-".($psize+1);
		
		$pole_explode= explode('-', $p_num);
         if(count($pole_explode)>1){

         $switch_point = explode("/",$request->get('switch_point'))[0];

		$switch_point_array=['A'=>1,'B'=>2,'C'=>3,'D'=>4,'E'=>5,'F'=>6,'G'=>7,'H'=>8,'I'=>9,'J'=>10,'K'=>11,'L'=>12,'M'=>13,'N'=>14,'O'=>15,'P'=>16,'Q'=>17,'R'=>18,'S'=>19,'T'=>20,'U'=>21,'V'=>22,'W'=>23,'X'=>24,'Y'=>25,'Z'=>26,"AA"=>27,"AB"=>28,"AC"=>29,"AD"=>30,"AE"=>31,"AF"=>32,"AG"=>33,'AH'=>34,'AI'=>35,'AJ'=>36,'AK'=>37,'AL'=>38,'AM'=>39,'AN'=>40,'AO'=>41,'AP'=>42,'AQ'=>43,'AR'=>44,'AS'=>45,'AT'=>46,'AU'=>47,'AV'=>48,'AW'=>49,'AX'=>50,'AY'=>51,'AZ'=>52,"BA"=>53,"BB"=>54,"BC"=>55,"BD"=>56,"BE"=>57,"BF"=>58,"BG"=>59,'BH'=>60,'BI'=>61,'BJ'=>62,'BK'=>63,'BL'=>64,'BM'=>65,'BN'=>66,'BO'=>67,'BP'=>68,'BQ'=>69,'BR'=>70,'BS'=>71,'BT'=>72,'BU'=>73,'BV'=>74,'BW'=>75,'BX'=>76,'BY'=>77,'BZ'=>78,'CA'=>79,'CB'=>80,'CC'=>81,'CD'=>82,'CE'=>83,'CF'=>84,'CG'=>85,'CH'=>86,'CI'=>87,'CJ'=>88,'CK'=>89,'CL'=>90,'CM'=>91,'CN'=>92,'CO'=>93,'CP'=>94,'CQ'=>95,'CR'=>96,'CS'=>97,'CT'=>98,'CU'=>99];
		$explode_val= '-';$selected_val=0;
		foreach ($switch_point_array as $key => $value) {
			
			if($switch_point==$value)
        	{
        		$explode_val= $key;
        		$selected_val = $value;
        	}	
		}
           //dd($explode_val);  

		if($selected_val < 10){

        	$last_pole_num = survey_info::select('*',DB::Raw('substring(`pole_num`,7,4) as pole'))->whereRaw("SUBSTRING(`switch_point`,1,1) = $switch_point")->where('gram_id',$request->get('panchayat_name'))->where('pole_num', 'not like', "%-%")->orderByRaw('SUBSTR(pole FROM 1 FOR 1), CAST(SUBSTR(pole FROM 2) AS UNSIGNED) DESC')->first();

        }elseif($selected_val < 27){

        	$last_pole_num = survey_info::select('*',DB::Raw('substring(`pole_num`,7,4) as pole'))->whereRaw("SUBSTRING(`switch_point`,1,2) = $switch_point")->where('gram_id',$request->get('panchayat_name'))->where('pole_num', 'not like', "%-%")->orderByRaw('SUBSTR(pole FROM 1 FOR 1), CAST(SUBSTR(pole FROM 2) AS UNSIGNED) DESC')->first();

        }else{

        	$last_pole_num = survey_info::select('*',DB::Raw('substring(`pole_num`,8,4) as pole'))->whereRaw("SUBSTRING(`switch_point`,1,2) = $switch_point")->where('gram_id',$request->get('panchayat_name'))->where('pole_num', 'not like', "%-%")->orderByRaw('SUBSTR(pole FROM 1 FOR 1), CAST(SUBSTR(pole FROM 2) AS UNSIGNED) DESC')->first();
        }

			if(!empty($last_pole_num)){
				$pole_array = explode($explode_val,$last_pole_num->pole_num);

				if(count($pole_array) > 1){
					$new_pole = $pole_explode[0].$explode_val.sprintf("%02d",(int)$pole_array[1]+1);
				}else{
					$new_pole =$pole_explode[0].$explode_val."01";
				}
			}else{
				$new_pole =$pole_explode[0].$explode_val."01";
			}
		}else{
			$new_pole =$p_num;
		}

		$phase = survey_info::where(['jilla_id'=>$request->district_name,'taluka_id'=>$request->mandal_name,'gram_id'=>$request->panchayat_name])->orderby('phase_no',"DESC")->first();

        if($phase){
            if($phase->approve_status ==1){
                $phase_no =$phase->phase_no+1;
            }else{
                $phase_no =$phase->phase_no;
            }
        }else{
            $phase_no =1;
        }

		
        			$survey_info=new survey_info([
					's_no'=>$request->get('s_no'),
        			'jilla_id'=>$request->get('district_name'),
        			'taluka_id'=>$request->get('mandal_name'),
        			'gram_id'=>$request->get('panchayat_name'),
        			'location'=>$request->get('location'),
        			'landmark'=>$request->get('landmark'),
        			'maker_name'=>is_array($maker_name)?implode('||',$maker_name):"N/A||N/A||N/A||N/A||N/A||N/A",
					'pole_num'=>$new_pole,
        			'road_width'=>$request->get('road_width'),
        			'road_confi'=>$request->get('road_confi'),
        			'switch_point'=>$request->get('switch_point'),
        			'pole_type'=>$request->get('pole_type'),
        			'pole_other'=>$request->get('pole_other'),
        			'pole_side'=>$request->get('pole_side'),
        			'pole_height'=>$request->get('pole_height'),
        			'pole_spacing'=>$request->get('pole_spacing'),
        			'type_of_fixture'=>is_array($type_of_fixture)?implode('||',$type_of_fixture):"N/A||N/A||N/A||N/A||N/A||N/A",
        			'fixture_wattage'=>is_array($fixture_wattage)?implode('||',$fixture_wattage):"N/A||N/A||N/A||N/A||N/A||N/A",
        			'warranty_existing'=>is_array($warranty_existing)?implode('||',$warranty_existing):"N/A||N/A||N/A||N/A||N/A||N/A",
        			'Condition_existing'=>$request->get('condition_existing'),
        			'earth_condition'=>$request->get('earth_condition'),
        			'arms_clamps_required'=>$request->get('arms'),
        			'dedicated_available'=>$request->get('dedicated'),
        			'netword_issue_1'=>$request->get('led_lights'),
        			'netword_issue_2'=>$request->get('tree_obstruction'),
        			'LED_wattage'=>is_array($suggested_led_wattage)?implode('||',$suggested_led_wattage):"N/A||N/A||N/A||N/A||N/A||N/A",
        			'led_series_with_make'=>is_array($led_series_with_make)?implode('||',$led_series_with_make):"N/A||N/A||N/A||N/A||N/A||N/A",
        			'remark'=>$request->get('remark_data'),
					'cable_user'=>$request->get('cable_user'),
					'types_of_arms'=>$list,
					'reducer_used'=>$request->get('reducer_used'),
					'minimast_clamp_used'=>$request->get('minimast_clamp_used'),
        			'status'=>'active',
    				'added_by'=>''.$request->get('user_id'),
    				'led_light_num'=>$request->get('no_of_lights'),
    				'led_serial_num'=>implode('||',$led_light_serial),
    				'phase_no'=>$phase_no

        			]);

        	
        	$params = request()->all();		
		$data['status']="success";
		$data['msg']="data inserted";
				$data['req']=$params;


		array_push($arr,$data);
		$survey_info->save();

		echo json_encode($arr);
		
	}
	public function surveydisplay(Request $request)
	{
		
		if(!$request->token){
		    return response()->json(['msg'=>"unautherized access"]);
		}
		$uid = $request->get('uid');
		$gid = $request->get('gid');
		$survey_i=DB::table('area_masters')
		            ->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
		            ->where('added_by','=',$uid)
		            ->where('gram_id','=',$gid)
					->select('*','area_masters.id as aid')
					->get();
					//dd(array_column($survey_i, 'pole_num'));
					foreach ($survey_i as $key => $value) {
						$survey_i[$key]->type_of_fixture=str_replace('||N/A',"",$value->type_of_fixture);
						$survey_i[$key]->fixture_wattage=str_replace('||N/A',"",$value->fixture_wattage);
						$survey_i[$key]->warranty_existing=str_replace('||N/A',"",$value->warranty_existing);
						$survey_i[$key]->LED_wattage=str_replace('||N/A',"",$value->LED_wattage);
						$survey_i[$key]->types_of_arms=str_replace('||N/A',"",$value->types_of_arms);
						$survey_i[$key]->maker_name=str_replace('||N/A',"",$value->maker_name);
						$survey_i[$key]->led_series_with_make=str_replace('||N/A',"",$value->led_series_with_make);
						$survey_i[$key]->led_serial_num=str_replace('||N/A',"",$value->led_serial_num);
					}
					//dd($survey_i);
		return response()->json($survey_i);
		
	}
	public function survey_list_by_swith_point(Request $request)
	{
		$uid = $request->get('uid');
		$gid = $request->get('gid');
		$List = survey_info::where('added_by','=',$uid)->where('gram_id','=',$gid)->groupby('switch_point')->orderby('switch_point')->get();
		$row=[];
		foreach ($List as $key => $values) {
			$wattages=["18 W","35 W","70 W","110 W","190 W"];
				$pole = substr($values->pole_num, 6,1);
				
				$point = $values->switch_point;
                      foreach($wattages as $value){ 
                       $counts= DB::select("SELECT created_at,LED_wattage,    
                        ROUND (   
                        (
                        LENGTH(LED_wattage)
                        - LENGTH( REPLACE ( LED_wattage, '".$value."', '') ) 
                        ) / LENGTH('".$value."')        
                        ) AS count    
                        FROM survey_infos where gram_id=$gid and switch_point='".$point."'");
                      $count=0;
                      foreach ($counts as $key => $values) {
                        $count=(int)$values->count+$count;
                    }
                    $r[str_replace(' ',"_",$value)]=$count;
                }
                $row[$pole]=$r;
                //dd($r);
		}
		return response()->json($row);
		//dd($row);
	}
	public function statusinfo($id)
	{
		$area=survey_info::find($id);
		$s=$area->status;
		if($s=='active')
		{
			$area=survey_info::find($id);
			$area->status='inactive';
			$area->update();
			return redirect('/survey_info');
		}
		else if($s=='inactive')
		{
			$area=survey_info::find($id);
			$area->status='active';
			$area->update();
			return redirect('/survey_info');
		}
	}
	public function printdetails($id)
	{
		$arr=DB::table('survey_infos')
				->where('id','=',$id)
				->get();
	    

		$jillaname=DB::table('area_masters')
					->where('id','=',$arr[0]->jilla_id)
					->get();
		$talukaname=DB::table('area_masters')
					->where('id','=',$arr[0]->taluka_id)
					->get();
		$gramname=DB::table('area_masters')
					->where('id','=',$arr[0]->gram_id)
					->get();
					

		$data="";
		foreach($arr as $testsurvey)
		{
			$data=$testsurvey->details;
		}
		$survey=json_decode($data);
		$readyreknals=DB::table('readyreknals')
					->where('jilla','=',$arr[0]->jilla_id)
					->where('taluka','=',$arr[0]->taluka_id)
					->where('gram','=',$arr[0]->gram_id)
					->get();

		$bharank=DB::table('bharanks')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->get();
		$gha=DB::table('ghasaras')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->get();
		$additional=DB::table('additional_details')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->get();
		$additional12=DB::table('additional_details')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->count();
		$varnan=DB::table('varnans')
					->where('status','=','active')
					->orderby('row_id','asc')
					->get();
		$arvr=array();
		foreach($varnan as $v)
		{
		    array_push($arvr,$v->name);
		}
		if($additional12==0)
		{
		    echo "<script>alert('Print Namuna Number 10 Form Not Available')</script>";
		    echo "<script>window.location.href='/survey_info'</script>"; 
		}
		else
		{
		    return view('backend.printdetails',compact('arr','varnan','survey','readyreknals','gha','jillaname','talukaname','gramname','additional','bharank','arvr'));
		}
	}
	public function printall(Request $request)
	{

        $pn=$request->get('printall');
        $ninepn=$request->get('printnamunanine');
        
            $jid=$request->get('jillaname');
        	$tid=$request->get('talukaname');
        	$gid=$request->get('gramname');
        	
            $jid=DB::table('area_masters')
                    ->where('area_name','=',$jid)
                    ->get();
            $idjilla=$jid[0]->id;
            
            $tid=DB::table('area_masters')
                    ->where('area_name','=',$tid)
                    ->where('parent_id','<>','0')
                    ->get();
            $idtaluka=$tid[0]->id;
            /*echo $idtaluka;
        	exit();*/
            $gid=DB::table('area_masters')
                    ->where('area_name','=',$gid)
                    ->where('parent_id','<>','0')
                    ->where('super_id','<>','0')
                    ->get();
            $idgram=$gid[0]->id;
            
            $sur=DB::table('survey_infos')
                    ->where('jilla_id','=',$idjilla)
                    ->where('taluka_id','=',$idtaluka)
                    ->where('gram_id','=',$idgram)
                    ->orderByRaw('cast(tax_number as unsigned)')
                    ->get();
            /*var_dump($sur);
            exit();*/
            $ard=array();
            foreach($sur as $d)
            {
                $id=$d->id;
        	    $arr=DB::table('survey_infos')
        	        ->where('id','=',$id)
        	        ->select('*')
        	       	->get();
    			array_push($ard,$arr);
            }
           // var_dump($ard);
        //    exit();
        
    		$varnan=varnan::get();
    			
        		$jid=$request->get('jillaname');
        		$tid=$request->get('talukaname');
        		$gid=$request->get('gramname');
        		$jillaname=DB::table('area_masters')
        					->where('area_name','=',$jid)
        					->get();
        		$talukaname=DB::table('area_masters')
        					->where('area_name','=',$tid)
        					->get();
        		$gramname=DB::table('area_masters')
        					->where('area_name','=',$gid)
        					->get();
        	    
            //echo $arrjilla[0]['area_name'];
    		//var_dump($arrjilla);
    		//exit();
            
    		$data=array();
    		foreach($ard as $testsurvey)
    		{
    			$data1=$testsurvey[0]->details;
    			$dp=json_decode($data1);
    		//	echo $dp[0]->varnana_name;
    			//var_dump($dp);
    			//array_push($ard,$dp);
    		}
            
    		   // exit();
    		   // echo $data[0]->varnana_name;
    		
    		//$survey=json_decode($data);
    		//var_dump($ard);
    	//	exit();
    $readyreknals=array();
    $bharank=array();
    // 		$gha=array();
    //$additional1=0;
    /*var_dump($ard);
    exit();*/
    $additional=array();
    		foreach($ard as $a)
    		{
    		$readyreknals1=DB::table('readyreknals')
    					->where('jilla','=',$a[0]->jilla_id)
    					->where('taluka','=',$a[0]->taluka_id)
    					->where('gram','=',$a[0]->gram_id)
    					->get();
            array_push($readyreknals,$readyreknals1);
    		
    		$bharank1=DB::table('bharanks')
    				->where('jilla','=',$a[0]->jilla_id)
    				->where('taluka','=',$a[0]->taluka_id)
    				->where('gram','=',$a[0]->gram_id)
    				->get();
    		array_push($bharank,$bharank1);
    		
    		$additional123=DB::table('additional_details')
    				->where('jilla','=',$a[0]->jilla_id)
    				->where('taluka','=',$a[0]->taluka_id)
    				->where('gram','=',$a[0]->gram_id)
    				->get();
    	    $additional1=DB::table('additional_details')
    				->where('jilla','=',$a[0]->jilla_id)
    				->where('taluka','=',$a[0]->taluka_id)
    				->where('gram','=',$a[0]->gram_id)
    				->count();
    		array_push($additional,$additional123);
    		}
    		if(sizeof($additional)==0)
    		{
    		    echo "<script>alert('Print Namuna Number 10 Form Not Available')</script>";
		        echo "<script>window.location.href='/survey_info'</script>"; 
    		}
    		else{
            return view('backend.print_all_details',compact('arr','ard','jillaname','talukaname','gramname','readyreknals','bharank','additional'));
    		}


	}
	
		public function printformnine(Request $request)
	    {
	        
	    	$jid=$request->get('jillaname');
        	$tid=$request->get('talukaname');
        	$gid=$request->get('gramname');
        	/*$jid=DB::table('area_masters')
                    ->where('area_name','=',$jid)
                    ->get();
            $idjilla=$jid[0]->id;
            $tid=DB::table('area_masters')
                    ->where('area_name','=',$tid)
                    ->get();
            $idtaluka=$tid[0]->id;
            $gid=DB::table('area_masters')
                    ->where('area_name','=',$gid)
                    ->get();
            $idgram=$gid[0]->id;*/
            
            $jid=DB::table('area_masters')
                    ->where('area_name','=',$jid)
                    ->get();
            $idjilla=$jid[0]->id;
            
            $tid=DB::table('area_masters')
                    ->where('area_name','=',$tid)
                    ->where('parent_id','<>','0')
                    ->get();
            $idtaluka=$tid[0]->id;
            /*echo $idtaluka;
        	exit();*/
            $gid=DB::table('area_masters')
                    ->where('area_name','=',$gid)
                    ->where('parent_id','<>','0')
                    ->where('super_id','<>','0')
                    ->get();
            $idgram=$gid[0]->id;
            
            
    		$arr=DB::table('survey_infos')
                        ->where('jilla_id','=',$idjilla)
                        ->where('taluka_id','=',$idtaluka)
                        ->where('gram_id','=',$idgram)
                        ->select('*')
        	            ->orderByRaw('cast(tax_number as unsigned)')
                        ->get();
    		$varnan=varnan::get();
    
    		$jillaname=DB::table('area_masters')
    					->where('id','=',$arr[0]->jilla_id)
    					->get();
    		$talukaname=DB::table('area_masters')
    					->where('id','=',$arr[0]->taluka_id)
    					->get();
    		$gramname=DB::table('area_masters')
    					->where('id','=',$arr[0]->gram_id)
    					->get();
    					
    
    		$data="";
    		foreach($arr as $testsurvey)
    		{
    			$data=$testsurvey->details;
    		}
    		$survey=json_decode($data);
    		$readyreknals=DB::table('readyreknals')
    					->where('jilla','=',$arr[0]->jilla_id)
    					->where('taluka','=',$arr[0]->taluka_id)
    					->where('gram','=',$arr[0]->gram_id)
    					->get();
    
    		$bharank=DB::table('bharanks')
    				->where('jilla','=',$arr[0]->jilla_id)
    				->where('taluka','=',$arr[0]->taluka_id)
    				->where('gram','=',$arr[0]->gram_id)
    				->get();
    		$gha=DB::table('ghasaras')
    				->where('jilla','=',$arr[0]->jilla_id)
    				->where('taluka','=',$arr[0]->taluka_id)
    				->where('gram','=',$arr[0]->gram_id)
    				->get();
    		$additional=DB::table('additional_details')
    				->where('jilla','=',$arr[0]->jilla_id)
    				->where('taluka','=',$arr[0]->taluka_id)
    				->where('gram','=',$arr[0]->gram_id)
    				->get();
    		$additional1=DB::table('additional_details')
    				->where('jilla','=',$arr[0]->jilla_id)
    				->where('taluka','=',$arr[0]->taluka_id)
    				->where('gram','=',$arr[0]->gram_id)
    				->count();
    			if($additional1==0)
        		{
        		      echo "<script>alert('Print Namuna Number 9 Form Not Available')</script>";
        		      echo "<script>window.location.href='/survey_info'</script>"; 
                }
        		else
        		{
    		       return view('backend.printdetailsnine',compact('arr','varnan','survey','readyreknals','gha','jillaname','talukaname','gramname','additional','bharank'));
        		}
            
	}
	public function printformten(Request $request)
	{
	    	$jid=$request->get('jillaname');
        	$tid=$request->get('talukaname');
        	$gid=$request->get('gramname');
        	/*$jid=DB::table('area_masters')
                    ->where('area_name','=',$jid)
                    ->get();
            $idjilla=$jid[0]->id;
            $tid=DB::table('area_masters')
                    ->where('area_name','=',$tid)
                    ->get();
            $idtaluka=$tid[0]->id;
            $gid=DB::table('area_masters')
                    ->where('area_name','=',$gid)
                    ->get();
            $idgram=$gid[0]->id;*/
            
            $jid=DB::table('area_masters')
                    ->where('area_name','=',$jid)
                    ->get();
            $idjilla=$jid[0]->id;
            
            $tid=DB::table('area_masters')
                    ->where('area_name','=',$tid)
                    ->where('parent_id','<>','0')
                    ->get();
            $idtaluka=$tid[0]->id;
            /*echo $idtaluka;
        	exit();*/
            $gid=DB::table('area_masters')
                    ->where('area_name','=',$gid)
                    ->where('parent_id','<>','0')
                    ->where('super_id','<>','0')
                    ->get();
            $idgram=$gid[0]->id;
    		$arr=DB::table('survey_infos')
                        ->where('jilla_id','=',$idjilla)
                        ->where('taluka_id','=',$idtaluka)
                        ->where('gram_id','=',$idgram)
                        ->select('*')
        	            //->orderByRaw('tax_number')
        	            ->orderByRaw('cast(tax_number as unsigned)')
                        ->get();
    		$varnan=varnan::get();
    
    		$jillaname=DB::table('area_masters')
    					->where('id','=',$arr[0]->jilla_id)
    					->get();
    		$talukaname=DB::table('area_masters')
    					->where('id','=',$arr[0]->taluka_id)
    					->get();
    		$gramname=DB::table('area_masters')
    					->where('id','=',$arr[0]->gram_id)
    					->get();
    					
    
    		$data="";
    		foreach($arr as $testsurvey)
    		{
    			$data=$testsurvey->details;
    		}
    		$survey=json_decode($data);
    		$readyreknals=DB::table('readyreknals')
    					->where('jilla','=',$arr[0]->jilla_id)
    					->where('taluka','=',$arr[0]->taluka_id)
    					->where('gram','=',$arr[0]->gram_id)
    					->get();
    
    		$bharank=DB::table('bharanks')
    				->where('jilla','=',$arr[0]->jilla_id)
    				->where('taluka','=',$arr[0]->taluka_id)
    				->where('gram','=',$arr[0]->gram_id)
    				->get();
    		$gha=DB::table('ghasaras')
    				->where('jilla','=',$arr[0]->jilla_id)
    				->where('taluka','=',$arr[0]->taluka_id)
    				->where('gram','=',$arr[0]->gram_id)
    				->get();
    		$additional=DB::table('additional_details')
    				->where('jilla','=',$arr[0]->jilla_id)
    				->where('taluka','=',$arr[0]->taluka_id)
    				->where('gram','=',$arr[0]->gram_id)
    				->get();
    		$additional1=DB::table('additional_details')
    				->where('jilla','=',$arr[0]->jilla_id)
    				->where('taluka','=',$arr[0]->taluka_id)
    				->where('gram','=',$arr[0]->gram_id)
    				->count();
    		if($additional1==0)
    		{
    		    echo "<script>alert('Print Namuna Number १० Form Not Available')</script>";
		        echo "<script>window.location.href='/survey_info'</script>"; 
    		}
    		else{
		       return view('backend.printdetailsten',compact('arr','varnan','survey','readyreknals','gha','jillaname','talukaname','gramname','additional','bharank'));
    		}
	}
	public function printdetailsnineform($id)
	{
	    $arr=DB::table('survey_infos')
				->where('id','=',$id)
				->get();
		$varnan=varnan::get();

		$jillaname=DB::table('area_masters')
					->where('id','=',$arr[0]->jilla_id)
					->get();
		$talukaname=DB::table('area_masters')
					->where('id','=',$arr[0]->taluka_id)
					->get();
		$gramname=DB::table('area_masters')
					->where('id','=',$arr[0]->gram_id)
					->get();
					

		$data="";
		foreach($arr as $testsurvey)
		{
			$data=$testsurvey->details;
		}
		$survey=json_decode($data);
		$readyreknals=DB::table('readyreknals')
					->where('jilla','=',$arr[0]->jilla_id)
					->where('taluka','=',$arr[0]->taluka_id)
					->where('gram','=',$arr[0]->gram_id)
					->get();

		$bharank=DB::table('bharanks')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->get();
		$gha=DB::table('ghasaras')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->get();
		$additional=DB::table('additional_details')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->get();
		$additional1=DB::table('additional_details')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->count();
	    if($additional1==0)
		{
		    echo "<script>alert('Print Namuna Number 9 Form Not Available')</script>";
		    echo "<script>window.location.href='/survey_info'</script>"; 
		}
		else
		{
		    return view('backend.printdetailsninesingle',compact('arr','varnan','survey','readyreknals','gha','jillaname','talukaname','gramname','additional','bharank'));
		}
	}
	public function printdetailstenform($id)
	{
	    $arr=DB::table('survey_infos')
				->where('id','=',$id)
				->get();
		$varnan=varnan::get();

		$jillaname=DB::table('area_masters')
					->where('id','=',$arr[0]->jilla_id)
					->get();
		$talukaname=DB::table('area_masters')
					->where('id','=',$arr[0]->taluka_id)
					->get();
		$gramname=DB::table('area_masters')
					->where('id','=',$arr[0]->gram_id)
					->get();
					

		$data="";
		foreach($arr as $testsurvey)
		{
			$data=$testsurvey->details;
		}
		$survey=json_decode($data);
		$readyreknals=DB::table('readyreknals')
					->where('jilla','=',$arr[0]->jilla_id)
					->where('taluka','=',$arr[0]->taluka_id)
					->where('gram','=',$arr[0]->gram_id)
					->get();

		$bharank=DB::table('bharanks')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->get();
		$gha=DB::table('ghasaras')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->get();
		$additional=DB::table('additional_details')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->get();
		$additional1=DB::table('additional_details')
				->where('jilla','=',$arr[0]->jilla_id)
				->where('taluka','=',$arr[0]->taluka_id)
				->where('gram','=',$arr[0]->gram_id)
				->count();
	    if($additional1==0)
		{
		    echo "<script>alert('Print Namuna Number 10 Form Not Available')</script>";
		    echo "<script>window.location.href='/survey_info'</script>"; 
		}
		else
		{
		    return view('backend.printdetailstensingle',compact('arr','varnan','survey','readyreknals','gha','jillaname','talukaname','gramname','additional','bharank'));
		}
	}
	public function add_thankbaki($id)
	{
	    $survey_i=DB::table('survey_infos')
				// 	->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->where('survey_infos.id','=',$id)
					->select('*')
					->get();
	    return view('backend.add_thankbaki',compact('id','survey_i'));
	}
	public function editsurvey(Request $request,$id)
	{
	    $gharpatti=$request->get('gharatti');
	    $vijkar=$request->get('vijkar');
	    $arogy=$request->get('arogy');
	    $jpani=$request->get('jpani');
	    $spani=$request->get('spani');
	    $survey_info=survey_info::find($id);
		$survey_info->gharpatti=$gharpatti;
		$survey_info->vijkar=$vijkar;
		$survey_info->arogy=$arogy;
		$survey_info->jpani=$jpani;
		$survey_info->spani=$spani;
		$survey_info->update();
		return redirect('/survey_info');
	}
	public function material()
	{
		return view('backend.materialform');
	}
	public function surveyuppdate(Request $request,$id)
	{
		$updatearray=array();
		$arrt=array();
		for($i=1; $i<=3; $i++)
		{
			$r=$request->get('type'.$i);
			array_push($arrt,$r);
		}
		$list=implode('||',$arrt);
		$arrk=array();
		for($i=1; $i<=5; $i++)
		{
			$kp=$request->get('led_wattage'.$i);
			if(!empty($kp)){
			array_push($arrk,$kp);
			}
		}
		
		$lpk=implode('||',$arrk);
		$survey_i=survey_info::find($id);
        $survey_i->s_no=$request->get('s_no');
		$survey_i->jilla_id=$request->get('district_name');
		$survey_i->taluka_id=$request->get('mandal_name');
		$survey_i->gram_id=$request->get('panchayat_name');
		$survey_i->location=$request->get('location');
		$survey_i->landmark=$request->get('landmark');
		$survey_i->maker_name=$request->get('maker_name');
		$survey_i->pole_num=$request->get('pole_num');
		$survey_i->road_width=$request->get('road_width');
		$survey_i->road_confi=$request->get('road_confi');
		$survey_i->switch_point=$request->get('switch_point');
		$survey_i->pole_type=$request->get('pole_type');
		$survey_i->pole_other=$request->get('pole_other');
		$survey_i->pole_side=$request->get('pole_side');
		$survey_i->pole_height=$request->get('pole_height');
		$survey_i->pole_spacing=$request->get('pole_spacing');
		$survey_i->type_of_fixture=$request->get('type_of_fixtures');
		$survey_i->fixture_wattage=$request->get('fixture_wattage');
		$survey_i->warranty_existing=$request->get('warranty_existing');
		$survey_i->Condition_existing=$request->get('condition_existing');
		$survey_i->earth_condition=$request->get('earth_condition');
		$survey_i->arms_clamps_required=$request->get('arms');
		$survey_i->dedicated_available=$request->get('dedicated');
		$survey_i->netword_issue_1=$request->get('led_lights');
		$survey_i->netword_issue_2=$request->get('tree_obstruction');
		$survey_i->LED_wattage=$lpk;
		$survey_i->remark=$request->get('remark_data');
		$survey_i->cable_user=$request->get('cable_user');
		$survey_i->types_of_arms=$list;
		$survey_i->reducer_used=$request->get('reducer_used');
		$survey_i->minimast_clamp_used=$request->get('minimast_clamp_used');
		$survey_i->status='active';
		$survey_i->added_by='admin';
        $survey_i->update();
		$data['status']="success";
		$data['msg']="data updated";
		array_push($updatearray,$data);
		echo json_encode($updatearray);
        //return redirect('/survey_info');
	}
	public function surveyremove($id)
	{
		$arr=array();
		$survey=survey_info::find($id);
		$survey->delete();
		$data['status']="success";
		$data['msg']="Data Deleted";
		array_push($arr,$data);
		echo json_encode($arr);
	}

public function dic_view(Request $request)
    {


		$jilla=DB::table('area_masters')
					->where('parent_id','=','0')
					->where('super_id','=','0')
					->whereIn('id',$request->session()->get('districts'))
					->where('status','=','active')
					->orderby('area_name','ASC')
					->get();
					//dd($jilla);
	
		$userlist=DB::table('gramusers')
					->where('status','=','active')
					->select('*')
					->get();

		return view('backend.dic_view',compact('userlist','jilla'));
         
        
       
       
    }
    public function dic_installation(Request $request)
    {
        $jilla=$request->get('jillase');
        $taluka=$request->get('talukase');
        $gram=$request->get('gramse');
        if(isset($jilla) && $jilla=="" || $taluka=="" || $gram=="")
        {
            echo "<script>alert('Please Select Values')</script>";
            echo "<script>window.location.href='/survey_info'</script>";
        }
        else
        {
        DB::enableQueryLog();
        $survey=DB::table('area_masters')
		            ->join('survey_infos','survey_infos.taluka_id','=','area_masters.id')
					->where('survey_infos.jilla_id','=',$jilla)
					->where('survey_infos.taluka_id','=',$taluka)
					->where('survey_infos.gram_id','=',$gram)
					->join('pole_details','pole_details.pole_id','=','survey_infos.id');
					if(!empty($request->get('from_date'))){
						$survey->whereDate('survey_infos.created_at',date('Y-m-d',strtotime($request->get('from_date'))));
						
					}
					if(!empty($request->get('phase_no'))){
						$survey->where('survey_infos.phase_no',$request->get('phase_no'));						
					}
				$survey_i=$survey->select('*','pole_details.id as pdid','area_masters.id as aid',DB::Raw('substring(`pole_num`,7,4) as pole'),DB::Raw('SUBSTRING_INDEX(`survey_infos`.`switch_point`,"/",1) as port'))
					->orderByRaw('CAST(port AS UNSIGNED),SUBSTR(pole FROM 1 FOR 1), CAST(SUBSTR(pole FROM 2) AS UNSIGNED)')
					->get();
				//dd($survey_i);	
		//$r=sizeof($survey_i);
			$jillanamesss=array();
			$jillaaname=DB::table('area_masters')
							->where('id','=',$jilla)
							->get();
				$tname=$jillaaname[0]->area_name;
				array_push($jillanamesss,$tname);
		$areanamesss=array();
		$talukaname=DB::table('area_masters')
					->where('id','=',$taluka)
					// ->orderby('area_name','ASC')
					->get();
			$tname=$talukaname[0]->area_name;
			array_push($areanamesss,$tname);
		
		$gramnamesss=array();
		$gramname=DB::table('area_masters')
							->where('id','=',$gram)
							->get();
			$tname=$gramname[0]->area_name;
			array_push($gramnamesss,$tname);

		$enabledates= survey_info::where('jilla_id',$jilla)->where('taluka_id',$taluka)->where('gram_id',$gram)->pluck('created_at');
		$edates=[];
		foreach ($enabledates as $key => $value) {
			$edate= date('d-m-Y',strtotime($value));
			if(!in_array($edate,$edates)){
				$edates[]=$edate;
			}
		}
		//dd($edates);
		$phase = survey_info::where(['jilla_id'=>$jilla,'taluka_id'=>$taluka,'gram_id'=>$gram])->orderby('phase_no',"ASC")->groupby('phase_no')->get();
		return view('backend.dic_installation',compact('survey_i','jilla','taluka','gram','areanamesss','jillanamesss','gramnamesss','request','edates','phase'));
		
        }
    }

   public function dic_edit($id)
    {
     $survey_info=survey_info::find($id)->toArray();
     return json_encode($survey_info);     
	}

public function dic_update(Request $request)
	{
		$id=$request->get('id');
		$updatearray=array();
		$arrt=array();
		for($i=1; $i<=3; $i++)
		{
			$r=$request->get('type'.$i);
			array_push($arrt,$r);
		}
		$list=implode('||',$arrt);
		
		$arrk=array();
		for($i=1; $i<=5; $i++)
		{
			$kp=$request->get('led_wattage'.$i);
			if(!empty($kp) && $kp !="N/A" && $kp != "undefined" ){
			array_push($arrk,$kp);
			}
		}


		$lpk=implode('||',$arrk);
		$survey_i=survey_info::find($id);

		$switch_points = explode("/",$request->get('switch_point'))[0];
		$switch_point_array=['A'=>1,'B'=>2,'C'=>3,'D'=>4,'E'=>5,'F'=>6,'G'=>7,'H'=>8,'I'=>9,'J'=>10,'K'=>11,'L'=>12,'M'=>13,'N'=>14,'O'=>15,'P'=>16,'Q'=>17,'R'=>18,'S'=>19,'T'=>20,'U'=>21,'V'=>22,'W'=>23,'X'=>24,'Y'=>25,'Z'=>26,"AA"=>27,"AB"=>28,"AC"=>29,"AD"=>30,"AE"=>31,"AF"=>32,"AG"=>33,'AH'=>34,'AI'=>35,'AJ'=>36,'AK'=>37,'AL'=>38,'AM'=>39,'AN'=>40,'AO'=>41,'AP'=>42,'AQ'=>43,'AR'=>44,'AS'=>45,'AT'=>46,'AU'=>47,'AV'=>48,'AW'=>49,'AX'=>50,'AY'=>51,'AZ'=>52,"BA"=>53,"BB"=>54,"BC"=>55,"BD"=>56,"BE"=>57,"BF"=>58,"BG"=>59,'BH'=>60,'BI'=>61,'BJ'=>62,'BK'=>63,'BL'=>64,'BM'=>65,'BN'=>66,'BO'=>67,'BP'=>68,'BQ'=>69,'BR'=>70,'BS'=>71,'BT'=>72,'BU'=>73,'BV'=>74,'BW'=>75,'BX'=>76,'BY'=>77,'BZ'=>78,'CA'=>79,'CB'=>80,'CC'=>81,'CD'=>82,'CE'=>83,'CF'=>84,'CG'=>85,'CH'=>86,'CI'=>87,'CJ'=>88,'CK'=>89,'CL'=>90,'CM'=>91,'CN'=>92,'CO'=>93,'CP'=>94,'CQ'=>95,'CR'=>96,'CS'=>97,'CT'=>98,'CU'=>99];
		$explode_val= '-';
		foreach ($switch_point_array as $key => $value) {
			if($switch_points==$value)
        	{
        		$explode_val= $key;
        	}
        }   

		if($request->get('switch_point') != $survey_i->switch_point){	
        $newswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".$request->get('pole_serial_number')."' and SUBSTRING(pole_num,7,1)='".$explode_val."' and SUBSTRING(pole_num,8)>='".$request->get('pole_num')."' order by pole_num");
        //dd($newswitch_point_poles,$explode_val);
	        foreach ($newswitch_point_poles as $key => $poles) {
	        	$split_pole = explode($explode_val,$poles->pole_num);
	        	$newpolenumber= $split_pole[0].$explode_val.sprintf("%02d",((int)$split_pole[1]+1));

	        	survey_info::where('id',$poles->id)->update(['pole_num'=>$newpolenumber]);
	        	
	        }
	        $oldpolesplit = explode(preg_replace('/[0-9]+/', '', $survey_i->pole_num),$survey_i->pole_num);
	        $oldswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".$oldpolesplit[0]."' and SUBSTRING(pole_num,7,1)='".preg_replace('/[0-9]+/', '', $survey_i->pole_num)."' and SUBSTRING(pole_num,8)>".$oldpolesplit[1]." order by pole_num");

	        foreach ($oldswitch_point_poles as $key => $poles) {
	        	$split_pole = explode(preg_replace('/[0-9]+/', '', $poles->pole_num),$poles->pole_num);
	        	$newpolenumber= $split_pole[0].preg_replace('/[0-9]+/', '', $poles->pole_num).sprintf("%02d",((int)$split_pole[1]-1));
	        	survey_info::where('id',$poles->id)->update(['pole_num'=>$newpolenumber]);
	        }
	        //dd('hai');
	        $survey_i->switch_point=$request->get('switch_point');
	        $survey_i->pole_num=$request->get('pole_serial_number').$explode_val.sprintf("%02d",$request->get('pole_num'));
		}
		//dd('madhu');

		$split_old_pole = explode(preg_replace('/[0-9]+/', '', $survey_i->pole_num),$survey_i->pole_num);
		if($request->get('switch_point') == $survey_i->switch_point && $request->get('pole_num') != $split_old_pole[1]){
			//dd('test');
			
		 $newswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".$request->get('pole_serial_number')."' and SUBSTRING(pole_num,7,1)='".$explode_val."' and SUBSTRING(pole_num,8)>=".min($request->get('pole_num'),$split_old_pole[1])."  and SUBSTRING(pole_num,8)<=".max($request->get('pole_num'),$split_old_pole[1])." order by pole_num");
		 //dd($newswitch_point_poles);

	        foreach ($newswitch_point_poles as $key => $poles) {
	        	$split_pole = explode($explode_val,$poles->pole_num);

	        	if($split_old_pole[1] <= $request->get('pole_num') ){

	        		$newpolenumber= $split_pole[0].$explode_val.((int)$split_pole[1]-1);
	        	}else{
	        		$newpolenumber= $split_pole[0].$explode_val.((int)$split_pole[1]+1);
	        	}
	        	//dd($newpolenumber);
	        	survey_info::where('id',$poles->id)->update(['pole_num'=>$newpolenumber]);
	        }
	         $survey_i->switch_point=$request->get('switch_point');
	        $survey_i->pole_num=$split_pole[0].$explode_val.$request->get('pole_num');
		}
		//dd('hai');
		$survey_i->location=$request->get('location');
		$survey_i->landmark=$request->get('landmark');
		$survey_i->maker_name=is_array($request->get('maker_name'))?implode('||',$request->get('maker_name')):$request->get('maker_name');			
		$survey_i->led_light_num=$request->get('no_of_lights');			
		$survey_i->type_of_fixture=is_array($request->get('type_of_fixtures'))?implode('||',$request->get('type_of_fixtures')):$request->get('type_of_fixtures');			
		$survey_i->fixture_wattage=is_array($request->get('fixture_wattage'))?implode('||',$request->get('fixture_wattage')):$request->get('fixture_wattage');			
		$survey_i->warranty_existing=is_array($request->get('warranty_existing'))?implode('||',$request->get('warranty_existing')):$request->get('warranty_existing');			
		$survey_i->LED_wattage=is_array($request->get('LED_wattage'))?implode('||',$request->get('LED_wattage')):$request->get('LED_wattage');			
		$survey_i->arms_clamps_required=$request->get('arms');		
		$survey_i->netword_issue_1=$request->get('led_lights');		
		$survey_i->remark=$request->get('remark_data');
		$survey_i->cable_user=$request->get('cable_user');
		$survey_i->types_of_arms=is_array($request->get('type'))?implode('||',$request->get('type')):$request->get('type');
		$survey_i->reducer_used=$request->get('reducer_used');
		$survey_i->minimast_clamp_used=$request->get('minimast_clamp_used');		
        $survey_i->update();
		$data['status']="success";
		$data['msg']="data updated";
		array_push($updatearray,$data);
		echo json_encode($updatearray);
        return redirect(url('dic_installation').'?jillase='.$survey_i->jilla_id.'&talukase='.$survey_i->taluka_id.'&gramse='.$survey_i->gram_id);
	}


	public function survey_instal_edit($id)
    {
     $survey_info=survey_info::with('pole_details')->find($id)->toArray();

    // join('pole_details')->where('survey_infos.id','=','pole_details.pole_id')->
      //dd($survey_info['pole_details']);

     return json_encode($survey_info);
     //dd(json_encode($survey_info));

         }



	public function survey_install_update(Request $request)
	{
		$id=$request->get('id');
		$updatearray=array();
		$arrt=array();
		for($i=1; $i<=3; $i++)
		{
			$r=$request->get('type'.$i);
			array_push($arrt,$r);
		}
		$list=implode(',',$arrt);
		$arrk=array();
		for($i=1; $i<=5; $i++)
		{
			$kp=$request->get('led_wattage'.$i);
			array_push($arrk,$kp);
		}
		$lpk=implode(',',$arrk);
		$survey_i=survey_info::find($id);
		$switch_points = explode("/",$request->get('switch_point'))[0];
		$switch_point_array=['A'=>1,'B'=>2,'C'=>3,'D'=>4,'E'=>5,'F'=>6,'G'=>7,'H'=>8,'I'=>9,'J'=>10,'K'=>11,'L'=>12,'M'=>13,'N'=>14,'O'=>15,'P'=>16,'Q'=>17,'R'=>18,'S'=>19,'T'=>20,'U'=>21,'V'=>22,'W'=>23,'X'=>24,'Y'=>25,'Z'=>26,"AA"=>27,"AB"=>28,"AC"=>29,"AD"=>30,"AE"=>31,"AF"=>32,"AG"=>33,'AH'=>34,'AI'=>35,'AJ'=>36,'AK'=>37,'AL'=>38,'AM'=>39,'AN'=>40,'AO'=>41,'AP'=>42,'AQ'=>43,'AR'=>44,'AS'=>45,'AT'=>46,'AU'=>47,'AV'=>48,'AW'=>49,'AX'=>50,'AY'=>51,'AZ'=>52,"BA"=>53,"BB"=>54,"BC"=>55,"BD"=>56,"BE"=>57,"BF"=>58,"BG"=>59,'BH'=>60,'BI'=>61,'BJ'=>62,'BK'=>63,'BL'=>64,'BM'=>65,'BN'=>66,'BO'=>67,'BP'=>68,'BQ'=>69,'BR'=>70,'BS'=>71,'BT'=>72,'BU'=>73,'BV'=>74,'BW'=>75,'BX'=>76,'BY'=>77,'BZ'=>78,'CA'=>79,'CB'=>80,'CC'=>81,'CD'=>82,'CE'=>83,'CF'=>84,'CG'=>85,'CH'=>86,'CI'=>87,'CJ'=>88,'CK'=>89,'CL'=>90,'CM'=>91,'CN'=>92,'CO'=>93,'CP'=>94,'CQ'=>95,'CR'=>96,'CS'=>97,'CT'=>98,'CU'=>99];
		$explode_val= '-';$selected_val=0;
		foreach ($switch_point_array as $key => $value) {
			if($switch_points==$value)
        	{
        		$selected_val=$value;
        		$explode_val= $key;
        	}
        }   

		if($request->get('switch_point') != $survey_i->switch_point){	

		if($selected_val < 27){
        $newswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".$request->get('pole_serial_number')."' and SUBSTRING(pole_num,7,1)='".$explode_val."' and SUBSTRING(pole_num,8)>=".$request->get('pole_num')." order by pole_num");
    	}else{
    		$newswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".$request->get('pole_serial_number')."' and SUBSTRING(pole_num,7,2)='".$explode_val."' and SUBSTRING(pole_num,9)>=".$request->get('pole_num')." order by pole_num");
    	}
        //dd($newswitch_point_poles,$explode_val);
	        foreach ($newswitch_point_poles as $key => $poles) {
	        	$split_pole = explode($explode_val,$poles->pole_num);
	        	$newpolenumber= $split_pole[0].$explode_val.sprintf("%02d",((int)$split_pole[1]+1));

	        	survey_info::where('id',$poles->id)->update(['pole_num'=>$newpolenumber]);
	        	
	        }
	        $oldpolesplit = explode(preg_replace('/[0-9]+/', '', $survey_i->pole_num),$survey_i->pole_num);
	        if($selected_val < 27){
	        $oldswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".$oldpolesplit[0]."' and SUBSTRING(pole_num,7,1)='".preg_replace('/[0-9]+/', '', $survey_i->pole_num)."' and SUBSTRING(pole_num,8)>".$oldpolesplit[1]." order by pole_num");
		    }else{
		    	$oldswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".$oldpolesplit[0]."' and SUBSTRING(pole_num,7,2)='".preg_replace('/[0-9]+/', '', $survey_i->pole_num)."' and SUBSTRING(pole_num,9)>".$oldpolesplit[1]." order by pole_num");
		    }

	        foreach ($oldswitch_point_poles as $key => $poles) {
	        	$split_pole = explode(preg_replace('/[0-9]+/', '', $poles->pole_num),$poles->pole_num);
	        	$newpolenumber= $split_pole[0].preg_replace('/[0-9]+/', '', $poles->pole_num).sprintf("%02d",((int)$split_pole[1]-1));
	        	survey_info::where('id',$poles->id)->update(['pole_num'=>$newpolenumber]);
	        }
	        //dd('hai');
	        $survey_i->switch_point=$request->get('switch_point');
	        $survey_i->pole_num=$request->get('pole_serial_number').$explode_val.$request->get('pole_num');
		}
		//dd('madhu');

		$split_old_pole = explode(preg_replace('/[0-9]+/', '', $survey_i->pole_num),$survey_i->pole_num);
		if($request->get('switch_point') == $survey_i->switch_point && $request->get('pole_num') != $split_old_pole[1]){
			//dd('test');
			
			if($selected_val < 27){
		 $newswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".$request->get('pole_serial_number')."' and SUBSTRING(pole_num,7,1)='".$explode_val."' and SUBSTRING(pole_num,8)>=".min($request->get('pole_num'),$split_old_pole[1])."  and SUBSTRING(pole_num,8)<=".max($request->get('pole_num'),$split_old_pole[1])." order by pole_num");
		}else{
			$newswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".$request->get('pole_serial_number')."' and SUBSTRING(pole_num,7,2)='".$explode_val."' and SUBSTRING(pole_num,9)>=".min($request->get('pole_num'),$split_old_pole[1])."  and SUBSTRING(pole_num,9)<=".max($request->get('pole_num'),$split_old_pole[1])." order by pole_num");
		}
		 

	        foreach ($newswitch_point_poles as $key => $poles) {
	        	$split_pole = explode($explode_val,$poles->pole_num);

	        	if($split_old_pole[1] <= $request->get('pole_num') ){

	        		$newpolenumber= $split_pole[0].$explode_val.sprintf("%02d",((int)$split_pole[1]-1));
	        	}else{
	        		$newpolenumber= $split_pole[0].$explode_val.sprintf("%02d",((int)$split_pole[1]+1));
	        	}
	        	//dd($newpolenumber);
	        	survey_info::where('id',$poles->id)->update(['pole_num'=>$newpolenumber]);
	        }
	         $survey_i->switch_point=$request->get('switch_point');
	        $survey_i->pole_num=$split_pole[0].$explode_val.$request->get('pole_num');
		}
		$led_wattage = explode('||', $survey_i->LED_wattage);
		$maker_name = explode('||', $survey_i->maker_name);
			$led_light_num = explode('||', $survey_i->led_serial_num);
			$led_light_serial=[];$led_series_with_make=[];$temp_array=[];
			for ($i=0; $i < 6; $i++) { 
				if(isset($led_wattage[$i]) && isset($led_light_num[$i]) && isset($maker_name[$i]) && $led_wattage[$i] != 'N/A' && $led_wattage[$i] !="" && $led_wattage[$i] !=null && $led_light_num[$i] !='N/A'&& $led_light_num[$i] !="" && isset($request->get('maker_name')[$i])){

					if($maker_name[$i] !=$request->get('maker_name')[$i] || $led_wattage[$i] !=$request->get('LED_wattage')[$i]){

						deleted_survey::create(['jilla_id'=>$survey_i->jilla_id, 'taluka_id'=>$survey_i->taluka_id, 'gram_id'=>$survey_i->gram_id, 'pole_number'=>$survey_i->pole_num, 'led_wattage'=>$led_wattage[$i], 'maker_name'=>$maker_name[$i], 'serial_num'=>$led_light_num[$i], 'status'=>1]);

						
						$newmake = str_replace(" ","_",$request->get('maker_name')[$i])."_".str_replace(" ","",$request->get('LED_wattage')[$i])."_";

						$deleted_number = deleted_survey::where(['led_wattage'=>$request->get('LED_wattage')[$i],'maker_name'=>$request->get('maker_name')[$i]])->first();

						if(!empty($deleted_number)){
							
							$led_light_serial[]=$deleted_number->serial_num;
							$led_series_with_make[]=$newmake.$deleted_number->serial_num;
							$deleted_number->delete();
						}else{
							$last_number = DB::select('SELECT MAX(GREATEST(led_serial_num,0)) as serial_number FROM `survey_infos` WHERE LED_wattage LIKE "%'.$request->get('LED_wattage')[$i].'%"  and maker_name LIKE "%'.$request->get('maker_name')[$i].'%" and led_series_with_make LIKE "%'.$newmake.'%"');
							
							if($last_number[0]->serial_number != NULL){
								if(empty($temp_array[$newmake])){
									$number = (int)$last_number[0]->serial_number;
									$led_light_serial[]=sprintf("%02d",((int)$number+1));
									$led_series_with_make[]=$newmake.sprintf("%02d",((int)$number+1));
									$temp_array[$newmake]=sprintf("%02d",((int)$number+1));
								}else{
									$led_light_serial[]=$temp_array[$newmake]+1;
									$led_series_with_make[]=$newmake.$temp_array[$newmake]+01;
									$temp_array[$newmake]=$temp_array[$newmake]+1;
								}

							}else{

								$led_light_serial[]="01";
								$led_series_with_make[]=$newmake."01";
								$temp_array[$newmake]="01";
							}
						}
					}else{
						$led_light_serial[]=explode('||',$survey_i->led_serial_num)[$i];
						$led_series_with_make[]=explode('||',$survey_i->led_series_with_make)[$i];
					}
				}else{
					$led_light_serial[]="N/A";
					$led_series_with_make[]="N/A";
				}
			}
			//dd($led_light_serial,$led_series_with_make);
		$survey_i->led_series_with_make=implode('||',$led_series_with_make);
		$survey_i->led_serial_num=implode('||',$led_light_serial);
		$survey_i->location=$request->get('location');
		$survey_i->landmark=$request->get('landmark');	
		$survey_i->maker_name=is_array($request->get('maker_name'))?implode('||',$request->get('maker_name')):$request->get('maker_name');			
		$survey_i->led_light_num=$request->get('no_of_lights');				
		$survey_i->type_of_fixture=is_array($request->get('type_of_fixtures'))?implode('||',$request->get('type_of_fixtures')):$request->get('type_of_fixtures');			
		$survey_i->fixture_wattage=is_array($request->get('fixture_wattage'))?implode('||',$request->get('fixture_wattage')):$request->get('fixture_wattage');			
		$survey_i->warranty_existing=is_array($request->get('warranty_existing'))?implode('||',$request->get('warranty_existing')):$request->get('warranty_existing');	
		$survey_i->LED_wattage=is_array($request->get('LED_wattage'))?implode('||',$request->get('LED_wattage')):$request->get('LED_wattage');
		$survey_i->arms_clamps_required=$request->get('arms');		
		$survey_i->netword_issue_1=$request->get('led_lights');		
		
		$survey_i->remark=$request->get('remark_data');
		//$survey_i->cable_user=$request->get('cable_user');
		$survey_i->types_of_arms=is_array($request->get('type'))?implode('||',$request->get('type')):$request->get('type');
		$survey_i->arms_clamps_required=$request->get('arms');
		//$survey_i->reducer_used=$request->get('reducer_used');
		//$survey_i->minimast_clamp_used=$request->get('minimast_clamp_used');
		$survey_i->road_width=$request->get('road_width');
	    $survey_i->pole_type=$request->get('pole_type');
	   $survey_i->pole_other=$request->get('pole_other');
	   $survey_i->pole_side=$request->get('pole_side');
	   $survey_i->pole_spacing=$request->get('pole_spacing');
	   $survey_i->pole_height=$request->get('pole_height');
       $survey_i->netword_issue_1=$request->get('led_lights');
       $survey_i->netword_issue_2=$request->get('tree_obstruction');
       $survey_i->move_data=1;
		
        $survey_i->update();
		$data['status']="success";
		$data['msg']="data updated";
		array_push($updatearray,$data);
		echo json_encode($updatearray);
        return redirect(url('displayallsearch').'?jillase='.$survey_i->jilla_id.'&talukase='.$survey_i->taluka_id.'&gramse='.$survey_i->gram_id);
	}

	public function districtwise_report(Request $request)
	{

		 if($request->url!='dashboard'){
            Session::forget('dashboardurl');

          } 
         $with_makername = false;
		if(isset($request->with) && $request->with == 'makername'){
           $with_makername = true;
		}
		$districtwise_report=area_master::whereHas('districts_survey', function($q){
    $q->where('approve_status', 1);
    $q->orwhere('approve_status', 0);
})->whereIn('id',$request->session()->get('districts'))->where(['super_id'=>0,'parent_id'=>0])->orderby('area_name','ASC')->get();

		//dd($districtwise_report);
		return view('backend.reports.districtwise_report',compact('districtwise_report','with_makername'));
	}
	public function mandalwise_report(Request $request)
	{
		// $mandalwise_report=area_master::where(['super_id'=>0,'parent_id'=>$request->id])->orderby('area_name','ASC')->get();

		$with_makername = false;
		if(isset($request->with) && $request->with == 'makername'){
           $with_makername = true;
		}

		$mandalwise_report=area_master::whereHas('mandals_survey', function($q){

    $q->where('approve_status', 1);
    $q->orwhere('approve_status', 0);
})->where(['super_id'=>0,'parent_id'=>$request->id])->orderby('area_name','ASC')->get();


		 $district  = area_master::where('id',$request->id)->first();
		return view('backend.reports.mandalwise_report',compact('mandalwise_report','district','with_makername'));
	}

	public function gramwise_report(Request $request)
	{
		//dd($request->all());
		// $gramwise_report=survey_info::with('gram_details')->where(['jilla_id'=>$request->id,'taluka_id'=>$request->mandalid])->groupby('gram_id','phase_no')->get();
		//dd($gramwise_report);
		 //$district  = area_master::with('parent_details')->where('id',$request->mandalid)->first();


		// $gramwise_report=area_master::with(['phases'])->with(['signedfiles'])->where(['super_id'=>$request->id,'parent_id'=>$request->mandalid])->orderby('area_name','ASC')->get();
		$with_makername = false;
		if(isset($request->with) && $request->with == 'makername'){
           $with_makername = true;
		}

		$gramwise_report=DB::table('pole_details')->select()->join('area_masters',function($q){
                    	$q->on('pole_details.p_gram_id','=','area_masters.id');
                    })->leftjoin('phases',function($q){
                    	$q->on('phases.gram_id','=','pole_details.p_gram_id');
                    	$q->on('phases.phase_no','=','pole_details.p_phase_no');
                    })->where('pole_details.p_mandal_id','=',$request->mandalid)->groupby(['pole_details.p_phase_no','pole_details.p_gram_id'])->orderby('area_masters.area_name')->get();

                    //dd($gramwise_report);

		

		// $gramwise_report = DB::Select('select p.mandal_wise_gram,p.created_at,p.jilla_id,p.mandal_id,p.gram_id,p.phase_no,p.status,p.view,p.signature_date,p.mandal_wise_gramse,a.id,a.area_name,a.super_id,a.parent_id	 from phases p inner join area_masters a on a.id=p.gram_id where p.mandal_id='.$request->mandalid.' order by a.area_name');

		 $district  = area_master::with('parent_details')->where('id',$request->mandalid)->first();

		 //dd($gramwise_report);

		return view('backend.reports.gramwise_report',compact('gramwise_report','district','with_makername'));
	}
	public function approved_districtwise_report(Request $request)
	{
		$with_makername = false;
		if(isset($request->with) && $request->with == 'makername'){
           $with_makername = true;
		}

		$districtwise_report=area_master::whereHas('districts_survey', function($q){
    $q->where('approve_status', 1);
})->whereIn('id',$request->session()->get('districts'))->where(['super_id'=>0,'parent_id'=>0])->orderby('area_name','ASC')->get();
		//dd($districtwise_report);
		return view('backend.reports.approved_districtwise_report',compact('districtwise_report', 'with_makername'));
	}
	public function approved_mandalwise_report(Request $request)
	{


		$with_makername = false;
		if(isset($request->with) && $request->with == 'makername'){
           $with_makername = true;
		}

		$mandalwise_report=area_master::with(['phases'])->whereHas('mandals_survey', function($q){

    $q->where('approve_status', 1);
})->where(['super_id'=>0,'parent_id'=>$request->id])->orderby('area_name','ASC')->get();
		//dd($mandalwise_report);
		 $district  = area_master::where('id',$request->id)->first();
		return view('backend.reports.approved_mandalwise_report',compact('mandalwise_report','district', 'with_makername'));
	}

	public function approved_gramwise_report(Request $request)
	{
		$with_makername = false;
		if(isset($request->with) && $request->with == 'makername'){
           $with_makername = true;
		}

		//dd($request->all());
 // 		$gramwise_report  = phase_Model::with(['gram_details'=> function($q){
 // 			$q->select('gram_details.area_name');
 //     $q->orderby('area_name','ASC');
 // }])->where('mandal_id',$request->mandalid)->orderby('gram_details.area_name','asc')->get();

		$gramwise_report = DB::Select('select p.mandal_wise_gram,p.created_at,p.jilla_id,p.mandal_id,p.gram_id,p.phase_no,p.status,p.view,p.signature_date,p.mandal_wise_gramse,a.id,a.area_name,a.super_id,a.parent_id	 from phases p inner join area_masters a on a.id=p.gram_id where p.mandal_id='.$request->mandalid.' order by a.area_name');
		//dd($gramwise_report);
			
		$district  = area_master::with('parent_details')->where('id',$request->mandalid)->first();
		 return view('backend.reports.approved_gramwise_report',compact('gramwise_report','district', 'with_makername'));
	}

	public function updatephaseSignature(Request $request)
	{
		$a =  explode(',',$request->phaseids);
		if($request->phase){
			foreach($a as $r){
				if(in_array($r,$request->phase)){
					DB::table('phases')->where('id', $r)->update(['view' =>'1']);
				}else{
					DB::table('phases')->where('id', $r)->update(['view' => '']);
				}
			}
		}
		return redirect()->back();
	}
	public function update_gramwise_report(Request $request)
	{
		//dd($request->all());
//        $gramwise_report=area_master::with(['phases'])->whereHas('grams_survey', function($q){
//     $q->where('approve_status', 1);
// })->where(['super_id'=>$request->id,'parent_id'=>$request->mandalid])->orderby('area_name','ASC')->get();
		//dd($request->id);
		 $gramwise_report  = phase_Model::where('id',$request->id)->first();


        $gramwise_report->signature_date=$request->get('signature_date');
        $gramwise_report->save();

		 return redirect()->back();
	}


	public function update_survey_status(Request $request)
	{
		/*$phase = phase_Model::where(['jilla_id'=>$request->jilla,'mandal_id'=>$request->taluka,'gram_id'=>$request->gram])->orderby('phase_no',"DESC")->first();
		if($phase){
			$phase_no =$phase->phase_no+1;
		}else{
			$phase_no =1;
		}*/
		if(!$request->phase_no){
 			return redirect(url('displayallsearch').'?jillase='.$request->jilla.'&talukase='.$request->taluka.'&gramse='.$request->gram.'&from_date='.$request->selecteddate)->with('failed',"Phase Number is missing");
		}
		$phase_no =$request->phase_no;
		survey_info::where(['jilla_id'=>$request->jilla,'taluka_id'=>$request->taluka,'gram_id'=>$request->gram,'phase_no'=>$phase_no])->update(['approve_status'=>$request->status]);

		if($request->status==1){
			gramuserallot::where('gram',$request->gram)->update(['status'=>0]);
			phase_Model::create(['jilla_id'=>$request->jilla,'mandal_id'=>$request->taluka,'gram_id'=>$request->gram,'phase_no'=>$phase_no]);
		}
		if($request->type==1){
		 return redirect(url('displayallsearch').'?jillase='.$request->jilla.'&talukase='.$request->taluka.'&gramse='.$request->gram.'&from_date='.$request->selecteddate);
		}else{
			 return redirect(url('dic_installation').'?jillase='.$request->jilla.'&talukase='.$request->taluka.'&gramse='.$request->gram.'&from_date='.$request->selecteddate);
		}
	}
	//->groupby(DB::Raw('DATE(created_at)'))
	public function approve_reject_report(Request $request)
	{
		 if($request->url!='dashboard'){
            Session::forget('dashboardurl');

          }
          $list=survey_info::with(['jilla_details','taluka_details','gram_details'])->whereIn('jilla_id',$request->session()->get('districts'))->where(function($q) use ($request){
			if(isset($request->status)){
				$q->where('approve_status',$request->status);
			}
			if(isset($request->date)){
				$q->whereDate('created_at','>=',date('Y-m-d',strtotime($request->date)));
			}
			if(isset($request->to_date)){
				$q->whereDate('created_at','<=',date('Y-m-d',strtotime($request->to_date)));
			}
		})->groupby('gram_id')->orderby('created_at',"desc")->get()->toArray();
		$enabledates= survey_info::pluck('created_at');
		$edates=[];
		foreach ($enabledates as $key => $value) {
			$edate= date('d-m-Y',strtotime($value));
			if(!in_array($edate,$edates)){
				$edates[]=$edate;
			}
		}
		return view('backend.approve_reject_report',compact('list','edates'));
	}
	public function survey_map(Request $request)
	{
		$list=survey_info::select('id','pole_num',"location",'landmark',DB::raw("SUBSTRING(location, 1,11) as latitude,SUBSTRING(location, 14,12) as longitude"))->where('gram_id',$request->gramse)->get()->toArray();
		$total=[];
		foreach ($list as $key => $value) {
			$location=explode(',', $value['location']);
			if(count($location) > 1){
			$total[]=[$value['pole_num'],str_replace(" ","",$location[0]),str_replace(" ","",$location[1]),$value['id']];
			}
		}
		$latittude = isset($total[0])?$total[0][1]:"17.4822801";
		$longitude = isset($total[0])?$total[0][2]:"78.4183459";
		return view('backend.survey_map',compact('total',"latittude","longitude"));
	}
	public function delete_survey(Request $request)
	{
		$list=survey_info::where('id',$request->id)->first();
		if($list){
			
			$led_wattage = explode('||', $list->LED_wattage);
			$maker_name = explode('||', $list->maker_name);
			$led_light_num = explode('||', $list->led_serial_num);
			//dd($led_light_num);
			for ($i=0; $i < 6; $i++) { 
				if(isset($led_wattage[$i]) && isset($led_light_num[$i]) && $led_wattage[$i] != 'N/A' && $led_wattage[$i] !="" && $led_wattage[$i] !=null && $led_light_num[$i] !='N/A'&& $led_light_num[$i] !=""){
			deleted_survey::create(['jilla_id'=>$list->jilla_id, 'taluka_id'=>$list->taluka_id, 'gram_id'=>$list->gram_id, 'pole_number'=>$list->pole_num, 'led_wattage'=>$led_wattage[$i], 'maker_name'=>$maker_name[$i], 'serial_num'=>$led_light_num[$i], 'status'=>1]);
				}
			}

			$switch_points = explode("/",$list->switch_point)[0];
		$switch_point_array=['A'=>1,'B'=>2,'C'=>3,'D'=>4,'E'=>5,'F'=>6,'G'=>7,'H'=>8,'I'=>9,'J'=>10,'K'=>11,'L'=>12,'M'=>13,'N'=>14,'O'=>15,'P'=>16,'Q'=>17,'R'=>18,'S'=>19,'T'=>20,'U'=>21,'V'=>22,'W'=>23,'X'=>24,'Y'=>25,'Z'=>26,"AA"=>27,"AB"=>28,"AC"=>29,"AD"=>30,"AE"=>31,"AF"=>32,"AG"=>33,'AH'=>34,'AI'=>35,'AJ'=>36,'AK'=>37,'AL'=>38,'AM'=>39,'AN'=>40,'AO'=>41,'AP'=>42,'AQ'=>43,'AR'=>44,'AS'=>45,'AT'=>46,'AU'=>47,'AV'=>48,'AW'=>49,'AX'=>50,'AY'=>51,'AZ'=>52,"BA"=>53,"BB"=>54,"BC"=>55,"BD"=>56,"BE"=>57,"BF"=>58,"BG"=>59,'BH'=>60,'BI'=>61,'BJ'=>62,'BK'=>63,'BL'=>64,'BM'=>65,'BN'=>66,'BO'=>67,'BP'=>68,'BQ'=>69,'BR'=>70,'BS'=>71,'BT'=>72,'BU'=>73,'BV'=>74,'BW'=>75,'BX'=>76,'BY'=>77,'BZ'=>78,'CA'=>79,'CB'=>80,'CC'=>81,'CD'=>82,'CE'=>83,'CF'=>84,'CG'=>85,'CH'=>86,'CI'=>87,'CJ'=>88,'CK'=>89,'CL'=>90,'CM'=>91,'CN'=>92,'CO'=>93,'CP'=>94,'CQ'=>95,'CR'=>96,'CS'=>97,'CT'=>98,'CU'=>99];
			$explode_val= '-';$selected_val=0;
			foreach ($switch_point_array as $key => $value) {
				if($switch_points==$value)
	        	{
	        		$selected_val=$value;
	        		$explode_val= $key;
	        	}
	        }   
	        if($selected_val < 27){
			$newswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".substr($list->pole_num, 0,6)."' and SUBSTRING(pole_num,7,1)='".$explode_val."' and SUBSTRING(pole_num,8)> '".substr($list->pole_num, 7)."' order by id");
			}else{
				$newswitch_point_poles = DB::select("SELECT id,pole_num FROM survey_infos WHERE SUBSTRING(pole_num,1,6)='".substr($list->pole_num, 0,6)."' and SUBSTRING(pole_num,7,2)='".$explode_val."' and SUBSTRING(pole_num,9)> '".substr($list->pole_num, 8)."' order by id");
			}
		 

	        foreach ($newswitch_point_poles as $key => $poles) {
	        	$split_pole = explode($explode_val,$poles->pole_num);
	        	
	        	$newpolenumber= substr($list->pole_num, 0,6).$explode_val.sprintf("%02d",((int)$split_pole[1]-1));
	        	//dd($newpolenumber);
	        	survey_info::where('id',$poles->id)->update(['pole_num'=>$newpolenumber,'move_data'=>1]);
	        }
	        	$list->delete();

	        	pole_details::where('pole_id',$list->id)->delete();


	         return redirect(url('displayallsearch').'?jillase='.$list->jilla_id.'&talukase='.$list->taluka_id.'&gramse='.$list->gram_id)->with('success',"deleted Successfully");

		}
		return redirect()->back()->withErrors(['msg' => 'Id not Available']);

	}

	public function uploadfilegram(Request $request )
	{
		$uploadfilegram = new signedfile();
		$uploadfilegram->district_id = $request->input('district_id');
		$uploadfilegram->mandal_id = $request->input('mandal_id');
		$uploadfilegram->gram_id = $request->input('gram_id');
		$uploadfilegram->signature_date = $request->input('signature_date');
		 $insert=[];
         if($request->hasFile('files') )
         {
            $path=$request->file('files')->store('public');  
            $insert[]=$path;
         }else{
            $name="pdf".rand(11111111, 99999999).".pdf"; 
            Storage::disk('public')->put($name,base64_decode($request->input('files')));
            $insert[]="public/".$name;
         }
         $uploadfilegram->signed_files = implode(',', $insert);

           $uploadfilegram->save();
           if($request->type=="approved"){
           	 return redirect(url('approved_gramwise_report')."?id=".$request->input('district_id')."&&mandalid=".$request->input('mandal_id'));
           }
           return redirect(url('gramwise_report')."?id=".$request->input('district_id')."&&mandalid=".$request->input('mandal_id'));
     }
	


	public function update_old_survey_pole(Request $request)
    {
    	    	$survey=survey_info::where('pole_num', 'like', "%-%")->get();
    	
    	foreach ($survey as $list) {
    	
    	$pole_explode= explode('-', $list->pole_num);
         if(count($pole_explode)>1){

         $switch_point = explode('/', $list->switch_point)[0];
         $gram_id = $list->gram_id;

		$switch_point_array=['A'=>1,'B'=>2,'C'=>3,'D'=>4,'E'=>5,'F'=>6,'G'=>7,'H'=>8,'I'=>9,'J'=>10,'K'=>11,'L'=>12,'M'=>13,'N'=>14,'O'=>15,'P'=>16,'Q'=>17,'R'=>18,'S'=>19,'T'=>20,'U'=>21,'V'=>22,'W'=>23,'X'=>24,'Y'=>25,'Z'=>26,"AA"=>27,"AB"=>28,"AC"=>29,"AD"=>30,"AE"=>31,"AF"=>32,"AG"=>33,'AH'=>34,'AI'=>35,'AJ'=>36,'AK'=>37,'AL'=>38,'AM'=>39,'AN'=>40,'AO'=>41,'AP'=>42,'AQ'=>43,'AR'=>44,'AS'=>45,'AT'=>46,'AU'=>47,'AV'=>48,'AW'=>49,'AX'=>50,'AY'=>51,'AZ'=>52,"BA"=>53,"BB"=>54,"BC"=>55,"BD"=>56,"BE"=>57,"BF"=>58,"BG"=>59,'BH'=>60,'BI'=>61,'BJ'=>62,'BK'=>63,'BL'=>64,'BM'=>65,'BN'=>66,'BO'=>67,'BP'=>68,'BQ'=>69,'BR'=>70,'BS'=>71,'BT'=>72,'BU'=>73,'BV'=>74,'BW'=>75,'BX'=>76,'BY'=>77,'BZ'=>78,'CA'=>79,'CB'=>80,'CC'=>81,'CD'=>82,'CE'=>83,'CF'=>84,'CG'=>85,'CH'=>86,'CI'=>87,'CJ'=>88,'CK'=>89,'CL'=>90,'CM'=>91,'CN'=>92,'CO'=>93,'CP'=>94,'CQ'=>95,'CR'=>96,'CS'=>97,'CT'=>98,'CU'=>99];
		$explode_val= '-';$selectedvalue=0;
		foreach ($switch_point_array as $key => $value) {

			if($switch_point==$value)
        	{
        		$explode_val= $key;
        		$selectedvalue = $value;
        	}
        }
        	
		
              //$switch_point= sprintf('%02d',$switch_point);   
        if($selectedvalue < 10){

        	$last_pole_num = survey_info::select('*',DB::Raw('substring(`pole_num`,7,4) as pole'))->whereRaw("SUBSTRING(`switch_point`,1,1) = $switch_point")->where('gram_id',$gram_id)->where('pole_num', 'not like', "%-%")->orderByRaw('SUBSTR(pole FROM 1 FOR 1), CAST(SUBSTR(pole FROM 2) AS UNSIGNED) DESC')->first();

        }elseif($selectedvalue < 27){

        	$last_pole_num = survey_info::select('*',DB::Raw('substring(`pole_num`,7,4) as pole'))->whereRaw("SUBSTRING(`switch_point`,1,2) = $switch_point")->where('gram_id',$gram_id)->where('pole_num', 'not like', "%-%")->orderByRaw('SUBSTR(pole FROM 1 FOR 1), CAST(SUBSTR(pole FROM 2) AS UNSIGNED) DESC')->first();

        }else{

        	$last_pole_num = survey_info::select('*',DB::Raw('substring(`pole_num`,8,4) as pole'))->whereRaw("SUBSTRING(`switch_point`,1,2) = $switch_point")->where('gram_id',$gram_id)->where('pole_num', 'not like', "%-%")->orderByRaw('SUBSTR(pole FROM 1 FOR 1), CAST(SUBSTR(pole FROM 2) AS UNSIGNED) DESC')->first();
        }
        //dd($last_pole_num);
		

			if(!empty($last_pole_num)){
				
				$pole_array = explode($explode_val,$last_pole_num->pole_num);
				
				if(count($pole_array) > 1){
					$new_pole = $pole_explode[0].$explode_val.sprintf("%02d",((int)$pole_array[1]+1));
				}else{
					$new_pole =$pole_explode[0].$explode_val."01";
				}
			}else{
				$new_pole =$pole_explode[0].$explode_val."01";
				}
					
		}else{
			$new_pole =$list->pole_num;
		}
		survey_info::where('id',$list->id)->update(['pole_num'=>$new_pole]);
		

		}
    }
    public function approved_report(Request $request)
	{
		$list=survey_info::has(['jilla_details','taluka_details','gram_details'])->with(['jilla_details','taluka_details','gram_details'])->where(function($q) use ($request){
			
				$q->where('approve_status',1);
			
			if(isset($request->date)){
				$q->whereDate('created_at','>=',date('Y-m-d',strtotime($request->date)));
			}
			if(isset($request->to_date)){
				$q->whereDate('created_at','<=',date('Y-m-d',strtotime($request->to_date)));
			}
		})->groupby('gram_id')->orderby('created_at',"desc")->get()->toArray();
		$enabledates= survey_info::pluck('created_at');
		$edates=[];
		foreach ($enabledates as $key => $value) {
			$edate= date('d-m-Y',strtotime($value));
			if(!in_array($edate,$edates)){
				$edates[]=$edate;
			}
		}
		return view('backend.approved_report',compact('list','edates'));
	}

	public function admin_update_survey_status(Request $request)
	{
		$jilla=DB::table('area_masters')
					->where('parent_id','=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->get();
		return view('backend.reports.admin_update_survey_status',compact('jilla'));
	}
	public function admin_update_survey(Request $request)
	{
		//dd($request->all());
		survey_info::where(['jilla_id'=>$request->jilla,'taluka_id'=>$request->taluka,'gram_id'=>$request->gram,'phase_no'=>$request->phase_no])->update(['approve_status'=>$request->status,"phase_no"=>$request->phase_no]);

		if($request->status==1){
			gramuserallot::where('gram',$request->gram)->update(['status'=>0]);
			phase_Model::create(['jilla_id'=>$request->jilla,'mandal_id'=>$request->taluka,'gram_id'=>$request->gram,'phase_no'=>$request->phase_no]);
		}
		if($request->status==0){
			phase_Model::where(['jilla_id'=>$request->jilla,'mandal_id'=>$request->taluka,'gram_id'=>$request->gram,'phase_no'=>$request->phase_no])->delete();
		}
		return redirect(url('status_update_by_admin'))->with('success',"Status Updated Successfully");
	}
	public function admin_update_invoice_status(Request $request)
	{
	if($request->type==1){

		//inward::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);
          $inwardchec=inward::where('id',$request->invoice_number)->first();
          
        if(!empty($inwardchec) && $request->status==1)
           {

             outward::create(['jilla_id'=>$inwardchec->jilla_id, 'maker_name'=>$inwardchec->maker_name,'vendor_id'=>$inwardchec->vendor_id,'invoice_number'=>$inwardchec->invoice_number,'date_of_receiving'=>$inwardchec->date_of_receiving,

                     'rcv_qty_18w'=>$inwardchec->rcv_qty_18w,
                     'rcv_qty_35w'=>$inwardchec->rcv_qty_35w,
                     'rcv_qty_70w'=>$inwardchec->rcv_qty_70w,
                     'rcv_qty_110w'=>$inwardchec->rcv_qty_110w,
                     'rcv_qty_190w'=>$inwardchec->rcv_qty_190w,
                     'bufstck_rcv_qty'=>$inwardchec->bufstck_rcv_qty, 
                     'bufferstck_received_qty_18w'=>$inwardchec->bufferstck_received_qty_18w,
                     'bufferstck_received_qty_35w'=>$inwardchec->bufferstck_received_qty_35w,
                     'bufferstck_received_qty_70w'=>$inwardchec->bufferstck_received_qty_70w, 
                     'bufferstck_received_qty_110w'=>$inwardchec->bufferstck_received_qty_110w,
                     'bufferstck_received_qty_190w'=>$inwardchec->bufferstck_received_qty_190w,

                     'num_drv_rcv_qty'=>$inwardchec->num_drv_rcv_qty,
                     'drivers_received_qty_18w'=>$inwardchec->drivers_received_qty_18w, 
                     'drivers_received_qty_35w'=>$inwardchec->drivers_received_qty_35w,
                     'drivers_received_qty_70w'=>$inwardchec->drivers_received_qty_70w,
                     'drivers_received_qty_110w'=>$inwardchec->drivers_received_qty_110w,
                     'drivers_received_qty_190w'=>$inwardchec->drivers_received_qty_190w, 

                     'num_spd_rcv_qty'=>$inwardchec->num_spd_rcv_qty,
                     'spd_received_qty_18w'=>$inwardchec->spd_received_qty_18w,
                     'spd_received_qty_35w'=>$inwardchec->spd_received_qty_35w,
                     'spd_received_qty_70w'=>$inwardchec->spd_received_qty_70w,
                     'spd_received_qty_110w'=>$inwardchec->spd_received_qty_110w,
                     'spd_received_qty_190w'=>$inwardchec->spd_received_qty_190w,
                      'files'=>$inwardchec->files,
                      'tiltable_adapters'=>$inwardchec->tiltable_adapters,
                      'tiltadap_rcv_qty'=>$inwardchec->tiltadap_rcv_qty,
                      'approve_status'=>$request->status
                     // 'total_rcv_qty'=>$inwardchec->total_rcv_qty,
                   ]);


             inwards_vendor::create(['jilla_id'=>$inwardchec->jilla_id, 'maker_name'=>$inwardchec->maker_name,'vendor_id'=>$inwardchec->vendor_id,'invoice_number'=>$inwardchec->invoice_number,'date_of_receiving'=>$inwardchec->date_of_receiving,

                     'rcv_qty_18w'=>$inwardchec->rcv_qty_18w,
                     'rcv_qty_35w'=>$inwardchec->rcv_qty_35w,
                     'rcv_qty_70w'=>$inwardchec->rcv_qty_70w,
                     'rcv_qty_110w'=>$inwardchec->rcv_qty_110w,
                     'rcv_qty_190w'=>$inwardchec->rcv_qty_190w,
                     'bufstck_rcv_qty'=>$inwardchec->bufstck_rcv_qty, 
                     'bufferstck_received_qty_18w'=>$inwardchec->bufferstck_received_qty_18w,
                     'bufferstck_received_qty_35w'=>$inwardchec->bufferstck_received_qty_35w,
                     'bufferstck_received_qty_70w'=>$inwardchec->bufferstck_received_qty_70w, 
                     'bufferstck_received_qty_110w'=>$inwardchec->bufferstck_received_qty_110w,
                     'bufferstck_received_qty_190w'=>$inwardchec->bufferstck_received_qty_190w,

                     'num_drv_rcv_qty'=>$inwardchec->num_drv_rcv_qty,
                     'drivers_received_qty_18w'=>$inwardchec->drivers_received_qty_18w, 
                     'drivers_received_qty_35w'=>$inwardchec->drivers_received_qty_35w,
                     'drivers_received_qty_70w'=>$inwardchec->drivers_received_qty_70w,
                     'drivers_received_qty_110w'=>$inwardchec->drivers_received_qty_110w,
                     'drivers_received_qty_190w'=>$inwardchec->drivers_received_qty_190w, 

                     'num_spd_rcv_qty'=>$inwardchec->num_spd_rcv_qty,
                     'spd_received_qty_18w'=>$inwardchec->spd_received_qty_18w,
                     'spd_received_qty_35w'=>$inwardchec->spd_received_qty_35w,
                     'spd_received_qty_70w'=>$inwardchec->spd_received_qty_70w,
                     'spd_received_qty_110w'=>$inwardchec->spd_received_qty_110w,
                     'spd_received_qty_190w'=>$inwardchec->spd_received_qty_190w,
                      'files'=>$inwardchec->files,
                      'tiltable_adapters'=>$inwardchec->tiltable_adapters,
                      'tiltadap_rcv_qty'=>$inwardchec->tiltadap_rcv_qty,
                      'approve_status'=>$request->status
                     // 'total_rcv_qty'=>$inwardchec->total_rcv_qty,
                   ]);
              $inward=inward::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);
            
        	}

        elseif(!empty($inwardchec) && $request->status==2){
        	//dd($request->status==2);
        	 $inward=inward::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);
        	 //dd($inward);
        	outward::where('invoice_number',$inwardchec->invoice_number)->delete();
            inwards_vendor::where('invoice_number',$inwardchec->invoice_number)->delete();
    		}

        elseif(!empty($inwardchec) && $request->status==0){
        	 $inward=inward::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);
        	 outward::where('invoice_number',$inwardchec->invoice_number)->delete();
        	 inwards_vendor::where('invoice_number',$inwardchec->invoice_number)->delete();
        	}

	}
	elseif($request->type==2){
			//armsclamps::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);

			 $armsclamps_info=armsclamps::where('id',$request->invoice_number)->first();
			 //dd($armsclamps_info);

        if(!empty($armsclamps_info) && $request->status==1)
        	{

             outward_armsclamps::create(['jilla_id'=>$armsclamps_info->jilla_id,'invoice_number'=>$armsclamps_info->invoice_number,'date_of_receiving'=>$armsclamps_info->date_of_receiving,
                    'received_arms_dia_25'=>$armsclamps_info->received_arms_dia_25,
                    'received_arms_dia_inner_32'=>$armsclamps_info->received_arms_dia_inner_32,
                    'received_arms_dia_inner_40'=>$armsclamps_info->received_arms_dia_inner_40,
                    'received_clams_set_25'=>$armsclamps_info->received_clams_set_25,
                    'received_clams_set_40_2'=>$armsclamps_info->received_clams_set_40_2, 
                    'received_clams_set_40_3'=>$armsclamps_info->received_clams_set_40_3, 
                    'received_aluminium_cable'=>$armsclamps_info->received_aluminium_cable,
                     'remarks'=>$armsclamps_info->remarks,
                     'approve_status'=>$request->status,'files'=>$armsclamps_info->files,
                     

                    ]);
              armsclamps_vendor::create(['jilla_id'=>$armsclamps_info->jilla_id,'invoice_number'=>$armsclamps_info->invoice_number,'date_of_receiving'=>$armsclamps_info->date_of_receiving,
                    'received_arms_dia_25'=>$armsclamps_info->received_arms_dia_25,
                    'received_arms_dia_inner_32'=>$armsclamps_info->received_arms_dia_inner_32,
                    'received_arms_dia_inner_40'=>$armsclamps_info->received_arms_dia_inner_40,
                    'received_clams_set_25'=>$armsclamps_info->received_clams_set_25,
                    'received_clams_set_40_2'=>$armsclamps_info->received_clams_set_40_2, 
                    'received_clams_set_40_3'=>$armsclamps_info->received_clams_set_40_3, 
                    'received_aluminium_cable'=>$armsclamps_info->received_aluminium_cable,
                     'remarks'=>$armsclamps_info->remarks,
                     'approve_status'=>$request->status,'files'=>$armsclamps_info->files,
                     

                    ]);
               
              $armsclamps=armsclamps::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);
            }

        elseif(!empty($armsclamps_info) && $request->status==2)
            {
        	//dd($request->status==2);
        	 $armsclamps=armsclamps::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);
        	 //dd($inward);
        	outward_armsclamps::where('invoice_number',$armsclamps_info->invoice_number)->delete();
        	armsclamps_vendor::where('invoice_number',$armsclamps_info->invoice_number)->delete();
      
            }

        elseif(!empty($armsclamps_info && $request->status==0))
            {
        	$armsclamps=armsclamps::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);

        	outward_armsclamps::where('invoice_number',$armsclamps_info->invoice_number)->delete();
        	armsclamps_vendor::where('invoice_number',$armsclamps_info->invoice_number)->delete();
            }

		}

	elseif($request->type==3){
			//inwardccmstypes::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);

             $ccms_info=inwardccmstypes::where('id',$request->invoice_number)->first();

              if(!empty($ccms_info) && $request->status==1)
        	{

            
			 outward_ccms::create(['jilla_id'=>$ccms_info->jilla_id,'invoice_number'=>$ccms_info->invoice_number,' date_of_receiving'=>$ccms_info->date_of_transfer,

                    'ccms_ph_receive_quantity'=>$ccms_info->ccms_ph_receive_quantity,
                    'maker_name'=>$ccms_info->maker_name,
                    'remarks'=>$ccms_info->remarks,
                    'files'=>$ccms_info->files,
                    'approve_status'=>$ccms_info->status,
                     ]);
            

              $inward=inwardccmstypes::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);
            
        	}

        elseif(!empty($ccms_info) && $request->status==2){
        	//dd($request->status==2);
        	 $inward=inwardccmstypes::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);
        	 //dd($inward);
        	outward_ccms::where('invoice_number',$ccms_info->invoice_number)->delete();
        
    		}

        elseif(!empty($ccms_info) && $request->status==0){
        	 $inward=inwardccmstypes::where('id',$request->invoice_number)->update(['approve_status'=>$request->status]);
        	 outward_ccms::where('invoice_number',$ccms_info->invoice_number)->delete();
        	}
		}

		return redirect(url('admin_update_survey_status'))->with('success',"Invoice Status Updated Successfully");
	}
	public function get_invoice_list(Request $request)
	{
		if($request->type==1){
		$data = inward::where('jilla_id',$request->id)->get();
		}
		elseif($request->type==2){
		$data=armsclamps::where('jilla_id',$request->id)->get();
		}
		elseif($request->type==3){
		$data=inwardccmstypes::where('jilla_id',$request->id)->get();
		}
		$html = "<option value=''>Select Invoice Number</option>";
		foreach ($data as $key => $value) {
			$html.= "<option value='".$value->id."'>".$value->invoice_number."</option>";
		}
		return $html;
	}
	public function get_invoice_status(Request $request)
	{
		if($request->type==1){

		$data=inward::where('id',$request->id)->first();

		}
		elseif($request->type==2){
			$data=armsclamps::where('id',$request->id)->first();
		}
		elseif($request->type==3){
			$data=inwardccmstypes::where('id',$request->id)->first();
		}
		return json_encode($data);
	}
	public function gram_survey_status($id='',$phase_no)
	{
		$survey= survey_info::where('gram_id',$id)->where('phase_no',$phase_no)->orderby('id',"desc")->first();
		
		return json_encode($survey);
	}
	public function gram_survey_phase_status($id='')
	{
		$phases=survey_info::where('gram_id',$id)->groupby("phase_no")->get()->toArray();
		return json_encode($phases);
	}
	 public function survey_exceldownload(Request $request) 
    {
    	$gramnameexport=area_master::where('id',$request->gram)->get();
    	$phase_no="";
    	if(!empty($request->phase_no)){
    		$phase_no="-Phase-".$request->phase_no;
    	}
    	$name= "SurveyExport".$gramnameexport[0]->area_name."-".$phase_no.".xlsx";
    	Excel::store(new SurveyExport($request), $name, 'public');
       // return Excel::download(new SurveyExport($request), 'SurveyExport.xlsx');
    	 return Excel::download(new SurveyExport($request), $gramnameexport[0]->area_name.$phase_no."-SurveyExport.xlsx");
    }


	public function pending_districtwise_report(Request $request)
	{
		$with_makername = false;
		if(isset($request->with) && $request->with == 'makername'){
           $with_makername = true;
		}

		$districtwise_report=area_master::whereHas('districts_survey', function($q){
    $q->where('approve_status', 0);
})->whereIn('id',$request->session()->get('districts'))->where(['super_id'=>0,'parent_id'=>0])->orderby('area_name','ASC')->get();
		return view('backend.reports.pending_districtwise_report',compact('districtwise_report','with_makername'));
	}
	public function pending_mandalwise_report(Request $request)
	{
		$with_makername = false;
		if(isset($request->with) && $request->with == 'makername'){
           $with_makername = true;
		}


		$mandalwise_report=area_master::whereHas('mandals_survey', function($q){
    $q->where('approve_status', 0);
})->where(['super_id'=>0,'parent_id'=>$request->id])->orderby('area_name','ASC')->get();
		 $district  = area_master::where('id',$request->id)->first();
		return view('backend.reports.pending_mandalwise_report',compact('mandalwise_report','district','with_makername'));
	}

	public function pending_gramwise_report(Request $request)
	{

		$with_makername = false;
		if(isset($request->with) && $request->with == 'makername'){
           $with_makername = true;
		}

		$gramwise_report=DB::table('pole_details')->select()->join('area_masters',function($q){
                    	$q->on('pole_details.p_gram_id','=','area_masters.id');
                    })->where('pole_details.p_mandal_id','=',$request->mandalid)
		            ->join('survey_infos',function($q){
                    	$q->on('survey_infos.gram_id','=','pole_details.p_gram_id');
                    	$q->on('survey_infos.phase_no','=','pole_details.p_phase_no');
                    	
                    })->where(function($q) use ($request) {
                         $q->where('survey_infos.approve_status',0);

                        if(!empty($request->get('selected_date'))){
						$q->where('survey_infos.created_at','>=',date('Y-m-d',strtotime($request->get('selected_date'))));
					     }
					if(!empty($request->get('to_date'))){
						$q->whereDate('survey_infos.created_at','<=',date('Y-m-d',strtotime($request->get('to_date'))));
					     }
				   })->groupby(['pole_details.p_phase_no','pole_details.p_gram_id'])->orderby('area_masters.area_name')->get();

                    //dd($gramwise_report);


		//dd($request->all());
		// $gramwise_report=area_master::with(['phases'])->whereHas('grams_survey', function($q){
        // $q->where('approve_status', 0);
        // })->where(['super_id'=>$request->id,'parent_id'=>$request->mandalid])->orderby('area_name','ASC')->get();
        //dd($gramwise_report);
        

		$district  = area_master::with('parent_details')->where('id',$request->mandalid)->first();

		return view('backend.reports.pending_gramwise_report',compact('gramwise_report','district','with_makername'));
	}



public function signature_date(Request $request )
	{
		 $signature_date->signature_date = $request->input('signature_date');

		 // 'pole_side'=>$request->get('pole_side'),

           $signature_date->update();
           if($request->type=="approved"){
           	 return redirect(url('approved_gramwise_report')."?id=".$request->input('district_id')."&&mandalid=".$request->input('mandal_id'));
           }
           return redirect(url('gramwise_report')."?id=".$request->input('district_id')."&&mandalid=".$request->input('mandal_id'));
     }

 public function uploadfilegram1(Request $request ){
		$uploadfilegram = new signedfile();
		$uploadfilegram->district_id = $request->input('district_id');
		$uploadfilegram->mandal_id 	 = $request->input('mandal_id');
		$uploadfilegram->gram_id 	 = $request->input('gram_id');
		
		// $insert=[];
         if($request->hasFile('files')){
            $path=$request->file('files')->store('public');
			$imports=new UsersImport;
			Excel::import($imports,$path);
			$collection = $imports->data;
			//dd($collection);
			if(count($collection) > 0){
				$polno =array();$l=0;

				//Making list of pole number 
				$idList = [];
				foreach($collection as $k=>$value){
					if($k >= '7'){
						$idList[] = $value[5];
					}
					
				}
				//Get data from DB to match
				
				foreach($collection as $k=>$value){

			$survey_i =DB::table('survey_infos')->where('pole_num',$value[5])->first();
				$poleDataList[$value[5]] = $survey_i;
					

					if($k >= '7'){
						//dd($value);
						$id= $value[5];
						if($value[8] =="5/ 1-Ph"){
							$id = str_ireplace( array( '\'', '"',',' , ';', '<', '>','.' ), ' ', $value[5]);
						}						
						$survey_is=array();
						if(isset($poleDataList[$id])){
							$z=0;
							if(array_key_exists($id,$polno)){
								$polno[$id] = $polno[$id]+1;
							}else{
								$polno[$id] = $z;
							}
							$type_of_fixture = explode('||',$poleDataList[$id]->type_of_fixture);
							if(is_array($type_of_fixture)){
								$zs = $polno[$id];
								if(isset($value[14]) && $value[14]!= "-NA-"){
									$type_of_fixtures = isset($value[14])?$value[14]:'';
								}else{
									$type_of_fixtures ='N/A';
								}
								$type_of_fixture[$zs] = $type_of_fixtures;
							}
							$fixturewattage = explode('||',$poleDataList[$id]->fixture_wattage);
							if(is_array($fixturewattage)){
								$zs = $polno[$id];
								$fixturewattage[$zs] = isset($value[15])?$value[15]:'';
							}
							$warrantyexisting = explode('||',$poleDataList[$id]->warranty_existing);
							if(is_array($warrantyexisting)){
								$zs = $polno[$id];
								$warrantyexisting[$zs] = isset($value[16])?$value[16]:'';
							}
							$typesofarms = explode('||',$poleDataList[$id]->types_of_arms);
							if(is_array($typesofarms)){
								$zs = $polno[$id];
								$type = explode(',',$value[19]);
								$is_required = $type[0];
								$typesofarms[$zs] = isset($type[1])?$type[1]:'N/A';
							}
							$LEDwattage = explode('||',$poleDataList[$id]->LED_wattage);
							if(is_array($LEDwattage)){
								$zs = $polno[$id];
								$LEDwattage[$zs] = isset($value[23])?$value[23]:'N/A';
							}

							$maker_name = explode('||',$poleDataList[$id]->maker_name);
							if(is_array($maker_name)){
								$zs = $polno[$id];
								$maker_name[$zs] = isset($value[25])?$value[25]:'N/A';
							}


							
							$res1 = str_ireplace( array( '\'', '"',',' , ';', '<', '>' ), ' ', $value[3]);
							$res2 = str_ireplace( array( '\'', '"',',' , ';', '<', '>' ), ' ', $value[4]);
							
							$survey_is['landmark']= isset($value[2])?$value[2]:'';
							$survey_is['location']= trim($res1).','.trim($res2);
							//$survey_is['road_width']=is_array($value[6])?$value[6]:$value[6];
							//$survey_is['road_confi']=is_array($value[7])?$value[7]:$value[7];
							$survey_is['switch_point']=is_array($value[8])?$value[8]:$value[8];
							//$survey_is['pole_type']= is_array($value[9])?$value[9]:$value[9];
							//$survey_is['pole_other']= is_array($value[10])?$value[10]:$value[10];
							//$survey_is['pole_side']= is_array($value[11])?$value[11]:$value[11];
							//$survey_is['pole_height']= is_array($value[12])?$value[12]:$value[12];
							//$survey_is['pole_spacing']= is_array($value[13])?$value[13]:$value[13];
							$survey_is['type_of_fixture']= (implode('||',$type_of_fixture))?implode('||',$type_of_fixture):'N/A||N/A||N/A||N/A||N/A||N/A';
							$survey_is['fixture_wattage']= (implode('||',$fixturewattage))?implode('||',$fixturewattage):'N/A||N/A||N/A||N/A||N/A||N/A';
							$survey_is['warranty_existing']= (implode('||',$warrantyexisting))?implode('||',$warrantyexisting):'N/A||N/A||N/A||N/A||N/A||N/A';
							$survey_is['arms_clamps_required']= ($is_required)?$is_required:"No";
							//$survey_is['Condition_existing']= ($value[17])?$value[17]:$value[17];
							//$survey_is['earth_condition']= is_array($value[18])?$value[18]:$value[18];
							$survey_is['types_of_arms']= (implode('||',$typesofarms))?implode('||',$typesofarms):'N/A||N/A||N/A||N/A||N/A||N/A';
							//$survey_is['dedicated_available']= is_array($value[20])?$value[20]:$value[20];
							//$survey_is['netword_issue_1']= is_array($value[21])?$value[21]:$value[21];
							//$survey_is['netword_issue_2']= is_array($value[22])?$value[22]:$value[22];
							$survey_is['LED_wattage']= (implode('||',$LEDwattage))?implode('||',$LEDwattage):'N/A||N/A||N/A||N/A||N/A||N/A';
							$survey_is['maker_name']= (implode('||',$maker_name))?implode('||',$maker_name):'N/A||N/A||N/A||N/A||N/A||N/A';
							$survey_is['remark']= is_array($value[24])?$value[24]:$value[24];
							$survey_is['updated_at']= date('Y-m-d H:i:s');
							$survey_is['move_data']= 1;
							
							$survey = survey_info::limit(1)->updateOrCreate(['pole_num'=>$id],$survey_is);
							$l++;
						}
					}					
				}
				
				return redirect(url('displayallsearch').'?jillase='.$request->input('district_id').'&talukase='.$request->input('mandal_id').'&gramse='.$request->input('gram_id'))->with('success',"Excel update Successfully");
			}
         }
	}
  public function delete_reject_report(Request $request)

	{    //dd($request->id);
		$data=survey_info::where('gram_id',$request->gram_id)->where('phase_no',$request->phase_no)->delete();
		//dd($data);
		
		 return redirect()->back();
	}
 
	

	public function report_exceldownload(Request $request) 

    {
		 $name= "ReportExport".date('mdYhis').".csv";
    	 Excel::store(new ReportExcel($request), $name, 'public');
         return Excel::download(new ReportExcel($request), 'ReportExcel.csv');
    }

    public function update_light_numbers_based_on_village(Request $request)
    {
    	$maker_name = $request->get('maker_name');
    	$lists = survey_info::where('gram_id',$request->gram_id)->get();
    	foreach ($lists as $key => $list) {

    		if(empty($list->maker_name) && !empty($list->LED_wattage)){

    		$last_number = DB::select('SELECT MAX(GREATEST(led_serial_num,0)) as serial_number FROM `survey_infos` WHERE LED_wattage LIKE "%'.$list->LED_wattage.'%"  and maker_name LIKE "%'.$maker_name.'%"');
    			
    			$number = (int)$last_number[0]->serial_number+1;
    		$newmake = str_replace(" ","_",$maker_name)."_".str_replace(" ","",$list->LED_wattage)."_".$number;
    		survey_info::where('id',$list->id)->update(['maker_name'=>$maker_name,"led_series_with_make"=>$newmake,"led_serial_num"=>$number,"led_light_num"=>1]);
    		}
    	}
    }
    	
	public function report_mandal_exceldownload(Request $request) 
    {
		 $name= "ReportMandalExport".date('mdYhis').".csv";
    	 Excel::store(new Reportexcelone($request), $name, 'public');
         return Excel::download(new Reportexcelone($request), 'ReportMandalExport.csv');
    }
	public function report_gram_exceldownload(Request $request) 
    {
		 $name= "ReportGramExport".date('mdYhis').".csv";
    	 Excel::store(new Reportexceltwo($request), $name, 'public');
         return Excel::download(new Reportexceltwo($request), 'ReportGramExport.csv');
    }
    public function phase_survey_report(Request $request)
    {
    	$gram_details  = area_master::with('parent_details','super_details')->where('id',$request->id)->first();
    	$phaselist = phase_Model::where('gram_id',$request->id)->where(function($q) use($request){
    		if($request->phase_no != 1 and $request->phase_no != 2){
    		$q->where('phase_no',"!=",$request->phase_no);
    		}
    	} )->get();

    	$ccms_ty=ccms_types::has('area_gram')->with(['area_gram'=> function ($query) {
		$query->select('id', 'area_name');
		}])->select(DB::raw("count(ccms_type) as ccms_t"),DB::raw("SUM(survey_qty) as survey"),DB::raw("SUM(supply_qty) as supply"),DB::raw("SUM(install_qty) as insta"),DB::raw("SUM(balance_qty_install) as balance_qty"),DB::raw("SUM(balance_qty_supply) as balance_qty_s"),DB::raw("SUM(cable_used) as cabler"),'ccms_type')->where('gram_id',$request->id)->groupby('ccms_type')->first();


		return view('backend.reports.phase_survey_report',compact('gram_details','phaselist','request','ccms_ty'));
    }

    public function mandal_wise_report_grams(Request $request)
    {
    	$update = phase_Model::where('gram_id',$request->id)->update(['mandal_wise_gram'=>$request->status]);
    	//->where('phase_no',$request->phase)
    	return $update;
    }
    public function mandal_wise_report_list(Request $request)
    {
    	$lists = phase_Model::where('mandal_id',$request->id)->with('gram_details','taluka_details')->where('mandal_wise_gram',1)->groupby('gram_id')->get();

    	$mandal = area_master::with('parent_details')->where('id',$request->id)->first();
    	//dd($mandal);

    	return view('backend.reports.mandal_wise_report_list',compact('lists','mandal'));
    }

      public function mandal_wise_report_gramse(Request $request)
    {
    	$update = phase_Model::where('mandal_id',$request->id)->update(['mandal_wise_gramse'=>$request->status]);
    	//->where('phase_no',$request->phase)
    	return $update;
    }
    public function mandal_wise_report_liste(Request $request)
    {
    	$lists = phase_Model::where('jilla_id',$request->id)->with('taluka_details','jilla_details')->where('mandal_wise_gramse',1)->where('mandal_wise_gram',1)->groupby('mandal_id')->get();
    	//dd($lists);

    	$mandal = area_master::with('parent_details')->where('id',$request->id)->first();
    	//dd($mandal);

    	return view('backend.reports.mandal_wise_report_list1',compact('lists'));
    }


    public function admin_abstract_districtnames(Request $request)
	{
	$jilla=DB::table('area_masters')
					->where('parent_id','=','0')
					->where('super_id','=','0')
					->where('status','=','active')
					->whereIn('id',$request->session()->get('districts'))
					->get();
		$taluka=DB::table('area_masters')
					->where('parent_id','!=','0')
					->where('super_id','=','0')	
					->where('parent_id',$request->get('jillase'))
					->get();
		
			$users = DB::table('area_masters as A')
                    ->join('area_masters as B', 'A.parent_id', '=', 'B.id')
                    ->join('area_masters as C','A.super_id','=','C.id');
                    if($request->get('jillase')){
                        $users=$users->where('A.super_id',$request->get('jillase'));
                    }
                    if($request->get('talukase')&& $request->get('talukase')!='select'){
                        //dd('hi');
                        $users=$users->where('A.parent_id',$request->get('talukase'));
                    }

            $users=$users->select('C.area_name as jilla', 'B.area_name as taluka','A.area_name as gram','C.code as code','C.id as gid','C.status as gstatus')->whereIn('A.super_id',$request->session()->get('districts'))
                    ->groupby('C.code')
                    ->get();

		return view('backend.reports.admin_abstract_districtnames',compact('jilla','taluka','users','request'));
	}

public function abstarct_districtwise_completion(Request $request)
	{

		$area_list = area_master::with(['ccms_types' => function($q) use($request) {
			  if($request->phase_no){
				$q->where('phase_no', $request->phase_no); 
			}
			}])->with(['parent_details','super_details'])->where('id',$request->id)->first();

	$lists = DB::select("SELECT s.jilla_id,m.area_name as mandal_name,g.area_name as gram_name,s.gram_id,s.created_at,
SUM(ROUND ( ( LENGTH(LED_wattage) - LENGTH( REPLACE ( LED_wattage, '35 W', '') ) ) / LENGTH('35 W') )) AS count_35,
SUM(ROUND ( ( LENGTH(LED_wattage) - LENGTH( REPLACE ( LED_wattage, '70 W', '') ) ) / LENGTH('70 W') )) AS count_70,
SUM(ROUND ( ( LENGTH(LED_wattage) - LENGTH( REPLACE ( LED_wattage, '110 W', '') ) ) / LENGTH('110 W') )) AS count_110,
SUM(ROUND ( ( LENGTH(LED_wattage) - LENGTH( REPLACE ( LED_wattage, '190 W', '') ) ) / LENGTH('190 W') )) AS count_190,
SUM(ROUND ( ( LENGTH(LED_wattage) - LENGTH( REPLACE ( LED_wattage, '18 W', '') ) ) / LENGTH('18 W') )) as count_18,
SUM(ROUND ( ( LENGTH(types_of_arms) - LENGTH( REPLACE ( types_of_arms, '25mm', '') ) ) / LENGTH('25mm') )) AS count_25mm,
SUM(ROUND ( ( LENGTH(types_of_arms) - LENGTH( REPLACE ( types_of_arms, '32mm', '') ) ) / LENGTH('32mm') )) AS count_32mm,
SUM(ROUND ( ( LENGTH(types_of_arms) - LENGTH( REPLACE ( types_of_arms, '40mm', '') ) ) / LENGTH('40mm') )) AS count_40mm

FROM survey_infos s inner JOIN area_masters g on g.id=s.gram_id inner JOIN area_masters m on m.id=s.taluka_id WHERE s.jilla_id = '".$request->id."' AND s.approve_status = '1'  GROUP BY `gram_id`");
	//dd($lists);

	return view('backend.reports.abstarct_districtwise_completion',compact('lists','area_list'));		

	}
}

