<?php

namespace App\Exports;

use DB;
use App\Models\Employees;
use App\Models\survey_info;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class Reportexceltwo implements FromCollection,WithHeadings
{
	
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
		//dd($_GET);
		$i=0;$arms=["18 W","35 W","70 W","110 W","190 W"];$list=[];$idc=["25mm","32mm","40mm"];$lists=[];$q=[];$query='';
		if($_GET['selected_date']!=""){
		  $query = " and DATE(updated_at) >='".date('Y-m-d',strtotime($_GET['selected_date']))."' and DATE(updated_at) <='".date('Y-m-d',strtotime($_GET['to_date']))."'";
		}
		$gramwise_report=DB::table('area_masters')->where(['super_id'=>$_GET['id'],'parent_id'=>$_GET['mandalid']])->orderby('area_name','ASC')->get();
		$mandal  = DB::table('area_masters')->where('id',$_GET['mandalid'])->first();
		$district  = DB::table('area_masters')->where('id',$_GET['id'])->first();
		if($gramwise_report){
			foreach($gramwise_report as $j=>$vs){
				$i++;
				foreach($arms as $value){
					$counts= DB::select("SELECT LED_wattage,    
					ROUND (
						(
							LENGTH(LED_wattage)
							- LENGTH( REPLACE ( LED_wattage, '".$value."', '') )
						) / LENGTH('".$value."')       
					 ) AS count
					 FROM survey_infos where gram_id = ".$vs->id."".$query);
					 $count=array_sum(array_column($counts,'count'));
					 $list[$i][]=$count;					 
					 $q[$i]['id'] 			= $i;
					 $q[$i]['disname'] 		= ($district->area_name)?$district->area_name:'';
					 $q[$i]['mandal'] 		= ($mandal->area_name)?$mandal->area_name:'';
					 $q[$i]['gram'] 		= $vs->area_name;
					 $q[$i]['18'] 			= $w1=isset($list[$i][0])?$list[$i][0]:'0';
					 $q[$i]['35'] 			= $w2=isset($list[$i][1])?$list[$i][1]:'0';
					 $q[$i]['70'] 			= $w3=isset($list[$i][2])?$list[$i][2]:'0';
					 $q[$i]['110'] 			= $w4=isset($list[$i][3])?$list[$i][3]:'0';
					 $q[$i]['190'] 			= $w5=isset($list[$i][4])?$list[$i][4]:'0';
					 $q[$i]['tot'] 			= $counts=$w1+$w2+$w3+$w4+$w5;
				 }	

				 foreach($idc as $val){
					$countings= DB::select("SELECT types_of_arms,    
					ROUND (
						(
							LENGTH(types_of_arms)
							- LENGTH( REPLACE ( types_of_arms, '".$val."', '') )
						) / LENGTH('".$val."')       
					 ) AS count
					 FROM survey_infos where gram_id = ".$vs->id."".$query);
					 $counting=array_sum(array_column($countings,'count'));
					 $lists[$i][]=$counting;
					 $q[$i]['25mm'] 			= $a1=isset($lists[$i][0])?$lists[$i][0]:'0';
					 $q[$i]['32mm'] 			= $a2=isset($lists[$i][1])?$lists[$i][1]:'0';
					 $q[$i]['40mm'] 			= $a3=isset($lists[$i][2])?$lists[$i][2]:'0';
					
					 $q[$i]['total'] 			= $countings=$a1+$a2+$a3;

					  $cable_used3 = DB::select("SELECT install_qty  
                                AS instaaa,cable_used AS cbl_used   
                                FROM ccms_types inner JOIN phases p on p.gram_id=ccms_types.gram_id and p.phase_no=ccms_types.phase_no inner JOIN area_masters m on m.id=ccms_types.mandal_id  where ccms_types.gram_id=".$vs->id."".$query." ORDER BY m.area_name  ASC");
					           $cable_used3_count=array_sum(array_column($cable_used3,'instaaa'));
                              $cable_used4_count=array_sum(array_column($cable_used3,'cbl_used'));
                             
                              // var_dump($cable_used3_count);	
                              // exit();
                               $q[$i]['ccms_inst'] 			= $w7=isset($cable_used3_count)?$cable_used3_count:0;
                               $q[$i]['ccms_used'] 			= $w8=isset($cable_used4_count)?$cable_used4_count:0;			 

				 }
			 }	
		}
		//dd($q);
		return collect($q);		
    }
	
	public function headings(): array
    {
		return [
			'S.No',
			'District Name',
			'Mandal Name',
			'Gram Name',
			'18 W',
			'35 W',
			'70 W',
			'110 W',
			'190 W',
			'Total',
			'25mm',
			'32mm',
			'40mm',
			'total',
			'ccms_inst',
			'ccms_used',
		];
	}
}

