@extends('backend.header')
@section('index-content')
<style type="text/css">
  .table-responsive {
        width: 100%;
        height: 76vh;
    }
    /*th,
    td {
        white-space: nowrap;
    }*/
    table tr td:first-child {
        position: sticky;
        width: 50px;
        left: 0;
        background: #fff;
        color: #000;
        z-index: 9;
    }
    /*table tr td:nth-child(2) {
        position: sticky;
        left: 67px;
        background: #fff;
        color: #000;
        z-index: 9;
    }*/
    table tr th:first-child {
        position: sticky;
        width: 50px;
        left: 0;
        /*   background: #fff;*/
        color: #000;
        z-index: 10;
    }
    /*table tr th:nth-child(2) {
        position: sticky;
        left: 67px;
        background: #fff !important;
        color: #000;
        z-index: 10;
    }*/
    tr.table-success {
        position: sticky;
        left: 0;
        z-index: 10;
        background: #fff !important;
    }
    tr.table-success {
        top: 0px !important;
        position: sticky !important;
    }
    .table-success,
    .table-success > td,
    .table-success > th {
        background-color: #c3e6cb;
    }

</style>
<div class="row px-3">
  
  
  <section role="main" class="content-body">
    <!-- <header class="page-header">
      <h2>District Wise Approved Report</h2>
    </header>
    <div class="row">
      <div class="col-xs-12">
        <div class="right-wrapper">
          <ol class="breadcrumbs">
            <li>
              <a href="{{url('index')}}">
                <i class="fa fa-home"></i>
              </a>
            </li>
            <li><span class="active">Districts</span></li>
          </ol>
        </div>
      </div>
    </div> -->
    <header class="page-header">
      <div class="row px-4">
        <div class="col-lg-5 pl-0">
          <h2>District Wise Approved Report</h2> 
        </div>
      
        <div class="col-lg-5">
          <ol class="breadcrumbs">
            <li>
              <a href="{{url('index')}}">
                <i class="fa fa-home"></i>
              </a>
            </li>
            <li><span class="active">Districts</span></li>
          </ol>
        </div>
      </div>
    </header>

    <!-- start: page -->
    <section class="panel">
      <!-- <header class="panel-heading">
        <h4 class="my-0">District Wise Approved Report</h4>   
        <div class="panel-actions">
          <a href="#" class="fa fa-caret-down"></a>
          <a href="#" class="fa fa-times"></a>
        </div>

        
        
      </header> -->
      <div class="col-md-12 mt-3 mb-3 px-0">
        <form>
        <div class="col-sm-1 px-0 my-auto" style="padding-top: 5px;">
          From date : 
        </div>

        <div class="col-sm-2 my-auto">
           <input type="text" class="form-control datepicker" name="selected_date" placeholder="Select Date"  value="{{Request::get('selected_date')}}" required> 
        </div>
         <div class="col-sm-1 my-auto" style="padding-top: 5px;">
          To date : 
        </div>
         <div class="col-sm-2 my-auto">
           <input type="text" class="form-control datepicker" name="to_date" placeholder="Select Date" id="to_date" value="{{Request::get('to_date')}}" required> 
        </div>
        <div class="col-sm-1 my-auto">
           <button type="submit" class="btn btn-primary">Submit</button> 
        </div>
        </form>
        <div class="col-sm-5 my-auto">
           <a href="{{url('/approved_districtwise_report')}}?with=makername&selected_date={{Request::get('selected_date')}}&to_date={{Request::get('to_date')}}" class="btn btn-primary">With Make Name</a>
           <a href="{{url('/approved_districtwise_report')}}?selected_date={{Request::get('selected_date')}}&to_date={{Request::get('to_date')}}" class="btn btn-primary">Without Make Name</a> 
        </div>
      </div>
     
      @if($with_makername)


      <div class="table-responsive my-3">

     

      <table border="1" class="my-0">
       <thead>
         <tr class="table-success">
          <th>S.No</th>
           <th>District Name</th>
           <th>Make Name</th>
           <th>18 W</th>
           <th>35 W</th>
           <th>70 W</th>
           <th>110 W</th>
           <th>190 W</th>
           <th>Total</th>
            <th>25 mm</th>
           <th>32 mm</th>
           <th>40 mm</th>
            <th>Total</th>
             <th>CCMS</th>
            <th>Cable  (1c x 10 sq.mm)</th>

           
         </tr>
       </thead>
       <tbody>
        <?php $tvw1=0;$tvw2=0;$tvw3=0;$tvw4=0;$tvw5=0;$tvw6=0;$tvw7=0;$tvw8=0;$tvw9=0;$tvw10=0; $tcountvw=0;$tcountarmvw=0; $i=0;?>
        @foreach($districtwise_report as $report)
        <?php $ww1=0;$ww2=0;$ww3=0;$ww4=0;$ww5=0; ?>
        <?php  
        //$list=[]; $i++;  $arms=["18 W","35 W","70 W","110 W","190 W"]; $rowspan = []; $data = [];
        
        $query="";
        if(Request::get('selected_date')){
          $query = " and DATE(s.created_at) >='".date('Y-m-d',strtotime(Request::get('selected_date')))."' and DATE(s.created_at) <='".date('Y-m-d',strtotime(Request::get('to_date')))."'";
        }?>
                        <?php $counts= DB::select("SELECT count(*) as count , pole_maker_name,s.created_at, 
count(case pole_led_wattage  when '18 W'then 1 else null end)as w_18 ,
count(case pole_led_wattage  when '35 W'then 1 else null end)as w_35 ,
count(case pole_led_wattage  when '70 W'then 1 else null end)as w_70 ,
count(case pole_led_wattage  when '110 W'then 1 else null end)as w_110 ,
count(case pole_led_wattage  when '190 W'then 1 else null end)as w_190, 
count(case pole_type_of_arms  when '25mm Dia 1.2mt'then 1 else null end)as arm_25 ,
count(case pole_type_of_arms  when '32mm Dia 1.2mt'then 1 else null end)as arm_32 ,
count(case pole_type_of_arms  when '40mm Dia 1.2mt'then 1 else null end)as arm_40
FROM `pole_details` p INNER join survey_infos s on s.id=p.pole_id   where approve_status=1 ".$query." and jilla_id=".$report->id." GROUP by pole_maker_name order by pole_maker_name");

                       
                        
                $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.district_id  where ccms_types.district_id=".$report->id);
         
                              $cable_used3_count=array_sum(array_column($cable_used3,'instaaa'));
                                $cable_used4_count=array_sum(array_column($cable_used3,'cbl_used'));
        

        $c = 1 ; $i++; $vw1=0;$vw2=0;$vw3=0;$vw4=0;$vw5=0;$vw6=0;$vw7=0;$vw8=0;$vw9=0;$vw10=0;$j=0;

                        ?>  
         <tr> 
            <?php $c++; $tr=1;?>
                            <td rowspan="{{count($counts)+1}}">{{$i}}</td> 
                            <td rowspan="{{count($counts)+1}}"><a href="{{url('approved_mandalwise_report')}}?with=makername&id={{$report->id}}&selected_date={{Request::get('selected_date')}}&to_date={{Request::get('to_date')}}">{{$report->area_name}}</a></td>
                          </tr>
                          
                         @foreach($counts as $value) 
         
      <?php $sum=$value->w_18+$value->w_35+$value->w_70+$value->w_110+$value->w_190;
             $sumarm=$value->w_18+$value->w_35+$value->w_70+$value->w_110+$value->w_190; 
       ?><tr>
           
                            <td>{{$value->pole_maker_name}}</td>
                            <td><?php $w1=isset($value->w_18)?$value->w_18:0?>{{$w1}}</td>
                            <td><?php $w2=isset($value->w_35)?$value->w_35:0?>{{$w2}}</td>
                            <td><?php $w3=isset($value->w_70)?$value->w_70:0?>{{$w3}}</td>
                            <td><?php $w4=isset($value->w_110)?$value->w_110:0?>{{$w4}}</td>
                            <td><?php $w5=isset($value->w_190)?$value->w_190:0?>{{$w5}}</td>             
                            <td><b><?php $sumofmake=$w1+$w2+$w3+$w4+$w5;?>{{$sumofmake}}</b></td>
                            <td><?php $w6=isset($value->arm_25)?$value->arm_25:0?>{{$w6}}</td>
                            <td><?php $w7=isset($value->arm_32)?$value->arm_32:0?>{{$w7}}</td>
                            <td><?php $w8=isset($value->arm_40)?$value->arm_40:0?>{{$w8}}</td>
                            <td><b><?php $sumarmofmake=$w6+$w7+$w8;?>{{$sumarmofmake}}</b></td>
                            @if($tr==1)
                             <td rowspan="{{count($counts)+1}}"><?php $w9=isset($cable_used3_count)?$cable_used3_count:0?>{{$w9}}</td>
                            <td rowspan="{{count($counts)+1}}"><?php $w10=isset($cable_used4_count)?$cable_used4_count:0?>{{$w10}}</td>
                            <?php $vw9=$w9+$vw9;
                                 $vw10=$w10+$vw10; ?>
                            @endif
                            
                          </tr>

                            <?php $tr++;
                                $vw1=$w1+$vw1;
                                $vw2=$w2+$vw2;
                                $vw3=$w3+$vw3;
                                $vw4=$w4+$vw4;
                                $vw5=$w5+$vw5;
                                $vw6=$w6+$vw6;
                                $vw7=$w7+$vw7;
                                $vw8=$w8+$vw8;
                               
                              
                               

                                 
                            ?>
                          
                        @endforeach

                        <tr>
                       
                          </tr>
                        <?php   $countvw= $vw1+$vw2+$vw3+$vw4+$vw5;
                                $countarmvw= $vw6+$vw7+$vw8;

                                $tvw1=$vw1+$tvw1;
                                $tvw2=$vw2+$tvw2;
                                $tvw3=$vw3+$tvw3;
                                $tvw4=$vw4+$tvw4;
                                $tvw5=$vw5+$tvw5;
                                $tcountvw= $tvw1+$tvw2+$tvw3+$tvw4+$tvw5;
                                $tvw6=$vw6+$tvw6;
                                $tvw7=$vw7+$tvw7;
                                $tvw8=$vw8+$tvw8;
                                $tvw9=$vw9+$tvw9;
                                $tvw10=$vw10+$tvw10;
                                $tcountarmvw= $tvw6+$tvw7+$tvw8; ?>
                        <tr>

                            <td></td>
                            <td></td>
                            <td><b>Total</b></td>
                            <td><b>{{$vw1}}</b></td>
                            <td><b>{{$vw2}}</b></td>
                            <td><b>{{$vw3}}</b></td>
                            <td><b>{{$vw4}}</b></td>
                            <td><b>{{$vw5}}</b></td>
                            <td><b>{{$countvw}}</b></td> 
                            <td><b>{{$vw6}}</b></td>
                            <td><b>{{$vw7}}</b></td>
                            <td><b>{{$vw8}}</b></td>
                            <td><b>{{$countarmvw}}</b></td> 
                             <td><b>{{$vw9}}</b></td>
                            <td><b>{{$vw10}}</b></td> 

                          </tr>

                       
                        
                       
                                
       @endforeach
       <tr>
          <td></td>
          <td></td>
          <td><b>Total</b></td>
          <td><b>{{$tvw1}}</b></td>
          <td><b>{{$tvw2}}</b></td>
          <td><b>{{$tvw3}}</b></td>
          <td><b>{{$tvw4}}</b></td>
          <td><b>{{$tvw5}}</b></td>
          <td><b>{{$tcountvw}}</b></td>
          <td><b>{{$tvw6}}</b></td>
          <td><b>{{$tvw7}}</b></td>
          <td><b>{{$tvw8}}</b></td>
          <td><b>{{$tcountarmvw}}</b></td>
           <td><b>{{$tvw9}}</b></td>
          <td><b>{{$tvw10}}</b></td>
          
       </tr>
     </tbody>
   </table>
 </div>

</div>
   @else


   <div class="table-responsive my-4">
   <table border="1" class="my-0">


       <thead>
         <tr class="table-success">
          <th>S.No</th>
           <th>District Name</th>
           <th>18 W</th>
           <th>35 W</th>
           <th>70 W</th>
           <th>110 W</th>
           <th>190 W</th>
           <th>Total</th>
           <th>25 mm</th>
           <th>32 mm</th>
           <th>40 mm</th>
           <th>Total</th>
           <th>CCMS</th>
           <th>Cable (1c x 10 sq.mm)</th>
           
         </tr>
       </thead>
       <tbody>
        <?php $tw1=0;$tw2=0;$tw3=0;$tw4=0;$tw5=0;$tw6=0;$tw7=0;$tw8=0;$tw9=0;$tw10=0; $sum=0; $sum1=0; $i=0;?>
        @foreach($districtwise_report as $report)
        <?php  $list=[]; $i++;  $arms=["18 W","35 W","70 W","110 W","190 W"];
        $query="";
        if(Request::get('selected_date')){
          $query = " and DATE(s.created_at) >='".date('Y-m-d',strtotime(Request::get('selected_date')))."' and DATE(s.created_at) <='".date('Y-m-d',strtotime(Request::get('to_date')))."'";
        }?>
                      

                          <?php $counts= DB::select("SELECT count(*) as count , pole_maker_name,s.created_at, 
count(case pole_led_wattage  when '18 W'then 1 else null end)as w_18 ,
count(case pole_led_wattage  when '35 W'then 1 else null end)as w_35 ,
count(case pole_led_wattage  when '70 W'then 1 else null end)as w_70 ,
count(case pole_led_wattage  when '110 W'then 1 else null end)as w_110 ,
count(case pole_led_wattage  when '190 W'then 1 else null end)as w_190, 
count(case pole_type_of_arms  when '25mm Dia 1.2mt'then 1 else null end)as arm_25 ,
count(case pole_type_of_arms  when '32mm Dia 1.2mt'then 1 else null end)as arm_32 ,
count(case pole_type_of_arms  when '40mm Dia 1.2mt'then 1 else null end)as arm_40
FROM `pole_details` p INNER join survey_infos s on s.id=p.pole_id   where approve_status=1 ".$query." and jilla_id=".$report->id." GROUP by jilla_id order by jilla_id");


         $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.district_id  where ccms_types.district_id=".$report->id);
         
                              $cable_used3_count=array_sum(array_column($cable_used3,'instaaa'));
                                $cable_used4_count=array_sum(array_column($cable_used3,'cbl_used'));

            
                        

        ?>
         @foreach($counts as $value) 
         
      <?php   $sum=$value->w_18+$value->w_35+$value->w_70+$value->w_110+$value->w_190;
             $sumarm=$value->w_18+$value->w_35+$value->w_70+$value->w_110+$value->w_190; 
       ?> 
        <tr>
             <td>{{$i}}</td>
         <td><a href="{{url('approved_mandalwise_report')}}?id={{$report->id}}&selected_date={{Request::get('selected_date')}}&to_date={{Request::get('to_date')}}">{{$report->area_name}}</a></td>
          <td><?php $w1=isset($value->w_18)?$value->w_18:0?>{{$w1}}</td>
                            <td><?php $w2=isset($value->w_35)?$value->w_35:0?>{{$w2}}</td>
                            <td><?php $w3=isset($value->w_70)?$value->w_70:0?>{{$w3}}</td>
                            <td><?php $w4=isset($value->w_110)?$value->w_110:0?>{{$w4}}</td>
                            <td><?php $w5=isset($value->w_190)?$value->w_190:0?>{{$w5}}</td>             
                            <td><b><?php $sumofmake=$w1+$w2+$w3+$w4+$w5;?>{{$sumofmake}}</b></td>
                            <td><?php $w6=isset($value->arm_25)?$value->arm_25:0?>{{$w6}}</td>
                            <td><?php $w7=isset($value->arm_32)?$value->arm_32:0?>{{$w7}}</td>
                            <td><?php $w8=isset($value->arm_40)?$value->arm_40:0?>{{$w8}}</td>
                            <td><b><?php $sumarmofmake=$w6+$w7+$w8;?>{{$sumarmofmake}}</b></td>
                           <td><?php $w9=isset($cable_used3_count)?$cable_used3_count:0?>{{$w9}}</td>
                            <td><?php $w10=isset($cable_used4_count)?$cable_used4_count:0?>{{$w10}}</td>
                          </tr>
                         
       <?php 
       $tw1=$w1+$tw1;
       $tw2=$w2+$tw2;
       $tw3=$w3+$tw3;
       $tw4=$w4+$tw4;
       $tw5=$w5+$tw5;
       $tw6=$w6+$tw6;
       $tw7=$w7+$tw7;
       $tw8=$w8+$tw8;
       $tw9=$w9+$tw9;
       $tw10=$w10+$tw10;
       $sum=$tw1+$tw2+$tw3+$tw4+$tw5;
       $sum1=$tw6+$tw7+$tw8; ?>
         @endforeach
                                
       @endforeach
       <tr>
        <td></td>
         <td><b>Total</b></td>
          <td><b>{{$tw1}}</b></td>
          <td><b>{{$tw2}}</b></td>
          <td><b>{{$tw3}}</b></td>
          <td><b>{{$tw4}}</b></td>
          <td><b>{{$tw5}}</b></td>
          <td><b>{{$sum}}</b></td>
          <td><b>{{$tw6}}</b></td>
          <td><b>{{$tw7}}</b></td>
          <td><b>{{$tw8}}</b></td>
          <td><b>{{$sum1}}</b></td>
         <td><b>{{$tw9}}</b></td>
          <td><b>{{$tw10}}</b></td>
          
       </tr>
     </tbody>
   </table>
 </div>

   @endif
 </section>
</section>
<script src="https://code.jquery.com/jquery-3.6.0.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.13.2/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.13.2/jquery-ui.js"></script>

<script type="text/javascript">
   

$('.datepicker').datepicker({
  format:"YYYY-MM-DD"
});

$('#from_date').change(function(){
  window.location.href= "{{url('approved_districtwise_report')}}?selected_date="+$(this).val();
})

</script>
@stop
