@php
    use Illuminate\Support\Facades\DB;
    $distypes               =  [];
    $grnd_dttl_count_sum     =   0;
    $grnd_act_count_sum      =   0;
    $grnd_net_sale_sum       =   0;
@endphp
<table border="1" style="border: #0c0c0c 1px solid; background-color:#ffffff">
        <thead>
        <tr>
            <td colspan="6">
                <b>Product Sales Report</b>
            </td>

        </tr>
        <tr>
            <td colspan="2"><b>Period From:</b></td>
            <td>{{date('M-Y',strtotime($start_date))}}</td>
            <td colspan="2"><b>Period To:</b></td>
            <td>{{date('M-Y',strtotime($end_date))}}</td>
        </tr>
        </thead>
</table>
<table border="1" style="border: #0c0c0c 1px solid; background-color:#ffffff">
    <thead>
    <tr>
        <th rowspan="2" style="background-color: #F9F9F9"><b>SN</b></th>
        <th rowspan="2" style="background-color: #F9F9F9"><b>Zone</b></th>
        <th rowspan="2" style="background-color: #F9F9F9"><b>Region</b></th>
        @foreach($disttypes as $row)
            <th colspan="3" style="background-color: #F9F9F9;text-align: center"><b>{{@$row->distributor_type_name}}</b></th>
        @endforeach
        <th colspan="3" style="background-color: #F9F9F9;text-align: center"><b>Overall</b></th>
    </tr>
    <tr>
        @foreach($disttypes as $row)
            <th style="background-color: #F9F9F9"><b>TTL.Count</b></th>
            <th style="background-color: #F9F9F9"><b>ACT.Count</b></th>
            <th style="background-color: #F9F9F9"><b>Net Sales</b></th>
        @endforeach
            <th style="background-color: #F9F9F9"><b>TTL.Count</b></th>
            <th style="background-color: #F9F9F9"><b>ACT.Count</b></th>
            <th style="background-color: #F9F9F9"><b>Net Sales</b></th>
    </tr>
    </thead>
    <tbody>
        @foreach($areas as $key=>$area)
         @php
             $ttl_count_sum    =   0;
             $act_count_sum    =   0;
             $net_sale_sum     =   0;
         @endphp
        <tr>
            <td>{{ @$key+1 }}</td>
            <td>{{ @$area->area_name }} </td>
            <td>{{ @$area->region_name }} </td>
            @foreach($disttypes as $disttype)
                @php
                    $ttl_count          =   DB::table('distributor_details')->where('distributor_type_id',$disttype->id)->where('distributor_area_id',$area->id)->count();
                    $ttl                =   DB::table('orders_dist')
                                                 ->join('order_contents_dist', 'order_contents_dist.order_id', '=', 'orders_dist.id')
                                                 ->selectRaw("SUM(parent_qty_sold) as total_qty_sold, COUNT(DISTINCT(orders_dist.distributor_id)) as act_dist_count")
                                                 ->whereRaw("orders_dist.distributor_type_id = '{$disttype->id}' AND orders_dist.booking_area= '{$area->id}' AND DATE(orders_dist.completed_at) >= '{$start_date}' AND DATE(orders_dist.completed_at) <= '{$end_date}' AND lower(orders_dist.status)      =   'completed' ")
                                                 ->first();
                    $act_count          =   $ttl->act_dist_count;
                    $net_sale           =   $ttl->total_qty_sold;
                    @$distypes[$disttype->id]['ttl_count'] +=  $ttl_count;
                    @$distypes[$disttype->id]['act_count'] +=  $act_count;
                    @$distypes[$disttype->id]['net_sale']  +=  $net_sale;

                    $ttl_count_sum          +=  $ttl_count;
                    $act_count_sum          +=  $act_count;
                    $net_sale_sum           +=  $net_sale;

                    $grnd_dttl_count_sum    +=  $ttl_count;
                    $grnd_act_count_sum     +=  $act_count;
                    $grnd_net_sale_sum      +=  $net_sale;
                @endphp
                <td>{{spencerRound(@(int)$ttl_count)}}</td>
                <td>{{spencerRound(@(int)$act_count)}}</td>
                <td>{{spencerRound(@(int)$net_sale)}}</td>
            @endforeach
            <td> {{spencerRound(@(int)$ttl_count_sum)}}</td>
            <td> {{spencerRound(@(int)$act_count_sum)}}</td>
            <td> {{spencerRound(@(int)$net_sale_sum)}}</td>
        </tr>
        @endforeach
        <tr>
            <th colspan="3" style="background-color: #F9F9F9;text-align: center"><b>Total</b></th>
            @foreach($disttypes as $disttype)
                <th style="background-color: #F9F9F9"><b>{{spencerRound(@(int)@$distypes[$disttype->id]['ttl_count'])}}</b></th>
                <th style="background-color: #F9F9F9"><b>{{spencerRound(@(int)@$distypes[$disttype->id]['act_count'])}}</b></th>
                <th style="background-color: #F9F9F9"><b>{{spencerRound(@(int)@$distypes[$disttype->id]['net_sale'])}}</b></th>
            @endforeach
            <th style="background-color: #F9F9F9"><b> {{spencerRound(@(int)$grnd_dttl_count_sum)}}</b></th>
            <th style="background-color: #F9F9F9"><b> {{spencerRound(@(int)$grnd_act_count_sum)}}</b></th>
            <th style="background-color: #F9F9F9"><b> {{spencerRound(@(int)$grnd_net_sale_sum)}}</b></th>
        </tr>
    </tbody>
</table>