<html>
<head>
	<title>Mandal Wise Report</title>
	<meta name="csrf-token" content="{{ csrf_token() }}" />
	<link href="https://fonts.googleapis.com/css2?family=Lato&display=swap" rel="stylesheet">
	<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
	<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
	<script src="https://cdn.jsdelivr.net/npm/popper.js@1.12.9/dist/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
	<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
  <style>
    body{
        font-family: 'Times New Roman';
        font-size: 20px;
    }
    @media print {
      @page {
        size: A4 potrait;
    }
                /*.head_class{
                    position: fixed;
                    }*/
                    .print_action{display: none;}
                    .page-break
                    {
                        page-break-before:always;
                    } 
                }


                button#dashboard {
                    background-color: #008CBA;;
                    padding:10px 24px;
                    border:none;
                    font-weight: normal;
                    margin-top : 10px;
                }


                button#print {
                    margin-top: 10px;
                    background-color: #008CBA;;
                    padding:10px 24px;
                    border:none;
                    text-decoration:none;
                    color:white;
                    cursor:pointer;
                    font-weight: normal;
                }

                .vertical-text {
                    writing-mode: vertical-rl;
                    transform: rotate(-180deg);
                }

                p{
                    font-size: 20px !important;
                    margin-bottom: 0px;
                    font-weight: 500;
                    text-align: justify; /* For Edge */
                    -moz-text-align-last: justify; /* For Firefox prior 58.0 */
                }

                .table-bordered td, .table-bordered th, .table-bordered thead td, .table-bordered thead th{
                    vertical-align: middle;
                    font-size: 15px !important;
                }

                .table{
                  text-align: center;
                }

                .table thead th {
                    border-bottom: 1px solid #000000 !important;
                }

                .table-bordered td, .table-bordered th {
                    border: 1px solid #000000 !important;
                }

                .table-bordered {
                    border: 1px solid #000000 !important;
                }
                .table td, .table th {
               padding: 5px;
                  }

            </style>

        </head>

        <body>
        

            <div class="container pt-5">
                <div class="row px-5">
                    <div class="col-6 pl-5">
                        <div>
                            <button class="btn btn-primary print_action" id="dashboard"><a href="javascript:history.back()" style="text-decoration:none;color:white;">Back To Dashboard</a></button>
                        </div>
                    </div>
                    <div class="col-6 pr-5">
                        <div class="text-right pr-5 mr-5">
                            <button onclick="window.print();return false;" class="btn btn-primary print_action" id="print">Print</button> 
                        </div>
                    </div>


                    @foreach($lists as $data)
                    
                                <div style="break-before:page;"></div>
                
                    <div class="col-12 mt-5">
                        <h3 align="center" class="font-weight-bold"><u>LED PROJECT WORK</u></h3>
                        <h3 align="center" class="font-weight-bold"><u>COMPLETION REPORT</u></h3>
                      
                    </div>


            <div class="container mt-5">
                <div class="row pr-5 mr-5">
                    <div class="col-12">
                     
                        <div class="row pl-5 pr-4">
                           
                            <div class="col-12 pl-0">
                                <p >This is to certify that in {{$data->taluka_details->area_name??''}} Mandal of {{$data->jilla_details->area_name??''}} district fixing of LED lights and CCMS
works are completed. The cumulative total no. of LED lights and CCMS are installed as per the
requirement and as per the Survey conducted.</p>
                            </div>
                        </div>
                        <div class="row px-5 mt-4">
                           
                            <div class="col-12 pl-0">
                                <p >The details of the lights and CCMS installed in the {{$data->taluka_details->area_name??''}} Mandal is as follows:</p>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <div class="container py-5">
                <div class="ml-4 mr-5 pr-3">
                    <div class="col-12">
                        <table class="table table-bordered mb-0" style="width:96%;">
                            <thead>
                                <tr ><th colspan="15">Completion Certificate Qty. </th></tr>

                                <tr>
                                   <th  rowspan="2">S.No</th>
                                   <th rowspan="2"> Mandal Name</th>

                                   <th rowspan="2"> Village Name</th>
                                   <th colspan="6">LED Street Lights</th>
                                   <th colspan="3">IDC Items</th>
                                   <th colspan="2">CCMS Data</th>
                                   <th rowspan="2"> Date of Completion (DD/MM/YYYY) </th>
                                  
                               </tr>
                               <tr>
                                   <th >18 W</th>
                                   <th >35 W</th>
                                   <th >70 W</th>
                                   <th >110 W</th>
                                   <th >190 W</th>
                                   <th >Total </th>
                                   <th >25mm dia arm </th>
                                   <th >32mm dia arm</th>
                                   <th >40mm dia arm</th>
                                   <th >CCMS </th>
                                   <th >Cable (1c x 10 sq.mm)</th>
                                  
                               </tr>
                           </thead>
                           <?php $i=1;$tw1=0;$tw2=0;$tw3=0;$tw4=0;$tw5=0; $sum3=0; $ta1=0;$ta2=0;$ta3=0;$tc1=0;$tc2=0;  
                           $grams = App\Models\phase_Model::where('mandal_id',$data->taluka_details->id)->with('gram_details')->where('mandal_wise_gramse',1)->where('mandal_wise_gram',1)->groupby('gram_id')->get();
                          

?>
                           @foreach($grams as $list)




                            <?php $counts= DB::select("SELECT pole_num,    
                            SUM(ROUND ((LENGTH(LED_wattage)- LENGTH( REPLACE ( LED_wattage, '18 W', '') ) ) / LENGTH('18 W'))) AS w_18,  
                            SUM(ROUND ((LENGTH(LED_wattage)- LENGTH( REPLACE ( LED_wattage, '35 W', '') ) ) / LENGTH('35 W'))) AS w_35,  
                            SUM(ROUND ((LENGTH(LED_wattage)- LENGTH( REPLACE ( LED_wattage, '70 W', '') ) ) / LENGTH('70 W'))) AS w_70,  
                            SUM(ROUND ((LENGTH(LED_wattage)- LENGTH( REPLACE ( LED_wattage, '110 W', '') ) ) / LENGTH('110 W'))) AS w_110,
                            SUM(ROUND ((LENGTH(LED_wattage)- LENGTH( REPLACE ( LED_wattage, '190 W', '') ) ) / LENGTH('190 W'))) AS w_190,
                            SUM(ROUND ((LENGTH(types_of_arms)- LENGTH( REPLACE ( types_of_arms, '25mm', '') ) ) / LENGTH('25mm'))) AS mm25,
                            SUM(ROUND ((LENGTH(types_of_arms)- LENGTH( REPLACE ( types_of_arms, '32mm', '') ) ) / LENGTH('32mm'))) AS mm32,
                            SUM(ROUND ((LENGTH(types_of_arms)- LENGTH( REPLACE ( types_of_arms, '40mm', '') ) ) / LENGTH('40mm'))) AS mm40
                            
                            FROM survey_infos  where approve_status=1  and gram_id=".$list->gram_id); 
                              $countees= DB::select("SELECT created_at  FROM survey_infos  where approve_status=1  and gram_id=".$list->gram_id."  ORDER BY created_at  DESC");

                            $cable_used = DB::select("SELECT install_qty  
                                AS instaaa,cable_used AS cbl_used   
                                FROM ccms_types  inner JOIN area_masters m on m.id=ccms_types.mandal_id  where gram_id=".$list->gram_id." ORDER BY m.area_name  ASC");
                              $ccms=array_sum(array_column($cable_used,'instaaa'));
                              $cable_used_count=array_sum(array_column($cable_used,'cbl_used'));


                             ?>  

                           <tr>
                                   <td >{{$i}}</td>
                                    <td >{{$list->taluka_details->area_name??''}}</td>
                                   <td >{{$list->gram_details->area_name??''}}</td>
                                   <td >{{$w1=$counts[0]->w_18??0}}</td>
                                   <td >{{$w2=$counts[0]->w_35??0}}</td>
                                   <td >{{$w3=$counts[0]->w_70??0}}</td>
                                   <td >{{$w4=$counts[0]->w_110??0}}</td>
                                   <td >{{$w5=$counts[0]->w_190??0}}</td>
                                   <td >{{$total = $w1+$w2+$w3+$w4+$w5}}</td>
                                    <td >{{$a1=$counts[0]->mm25??0}}</td>
                                    <td >{{$a2=$counts[0]->mm32??0}}</td>
                                    <td >{{$a3=$counts[0]->mm40??0}}</td>
                                    <td >{{$c1=$ccms??'0'}}</td>
                                    <td >{{$c2=$cable_used_count??'0'}}</td>
                                    <td >{{date('d/m/Y',strtotime($countees[0]->created_at??'0'))}}</td>
                                  
                                  
                                  
                               </tr>
                               <?php 
                               $tw1=$w1+$tw1;
                                $tw2=$w2+$tw2;
                                $tw3=$w3+$tw3;
                                $tw4=$w4+$tw4;
                                $tw5=$w5+$tw5; 
                                $ta1=$a1+$ta1;
                                $ta2=$a2+$ta2;
                                $ta3=$a3+$ta3;
                                $tc1=$c1+$tc1;
                                $tc2=$c2+$tc2;                 

                                $sum3 = $sum3+$total;
                                $i++; ?>
                           @endforeach
                              <tr>
                                <td></td>
                                 <td></td>
                                <td>Total</td>
                                <td>{{$tw1??'0'}}</td>
                                <td>{{$tw2??'0'}}</td>
                                <td>{{$tw3??'0'}}</td>
                                <td>{{$tw4??'0'}}</td>
                                <td>{{$tw5??'0'}}</td>
                                <td>{{$sum3??'0'}}</td>
                                 <td>{{$ta1??'0'}}</td>
                                <td>{{$ta2??'0'}}</td>
                                <td>{{$ta3??'0'}}</td>
                                  <td>{{$tc1??'0'}}</td>
                                <td>{{$tc2??'0'}}</td>
                            </tr>

                            <tbody>


                               
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>

             <div class="container ">
                <div class="row px-5 mr-5">
                    <div class="col-12 pl-0 ">
                        <p>The above-mentioned quantity is with reference to the completion certificates received from Panchayat Secretaries. </p>
                    </div>
                    <div class="col-12 mt-5 px-0 pt-5">

                        <table class="mt-4">
                            <tr>
                                <td width="330px;">MPDO</td>
                                <td width="330px;">DLPO</td>
                                <td width="330px;">DPO</td>
                            </tr>
                            <tr>
                                <td>{{$data->taluka_details->area_name??''}}</td>
                                <td>{{$data->taluka_details->area_name??''}}</td>
                                <td>{{$data->jilla_details->area_name??''}}</td>
                            </tr>
                        </table>
                    </div>
                </div>
            </div>
              <div class="container mt-5 pt-5">
               
            </div>
            @endforeach

        </div>
    </div>
</body>
</html>