<?php

namespace App\Http\Controllers;

use App\Models\Clients;
use App\Models\EditSalesOne;
use App\Models\SalesItemOne;
use App\Models\SalesOne;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class EditController extends Controller
{

    function editReportViewItemUsingedit($client, $editid, $bill)
    {
        $cli = session('LoggedUser')['userid'];
        $data = [

            // 'client' => $client,
            'client' => session('LoggedUser')['userid'],
            'clientname' => Clients::where('ClientID', '=', $cli)->pluck('ClientName'),
            'area' => Clients::where('ClientID', '=', $cli)->pluck('Area'),
            'location' => Clients::where('ClientID', '=', $cli)->pluck('Location'),
            'editid' => $editid,
            'billno' => EditSalesOne::where('Edit_ID', '=', $editid)
                ->where('Bill_No', '=', $bill)
                ->pluck('Bill_No'),


            'total' => DB::select("select es.bill_no as bno,Item_description,quantity,batch,Min(es.edit_id) as minid,Rate,amount,grand_total from Edit_Sales1 es
			left join Edit_Sales_Item1 esi on es.Bill_No = esi.bill_no and es.edit_id = esi.edit_id where es.ClientID ='" . $cli . "'
			and esi.ClientID ='" . $cli . "'
			and es.edit_id = '" . $editid . "' and esi.edit_id = '" . $editid . "'
			group by Item_description,quantity,batch,grand_total,Rate,amount
			order by 5"),

        ];

        // return $data;
        return view('Admin.editReportDetailItems', $data);
    }

    function editReportsaleDetail($client, $bill)
    {

        $clien = session('LoggedUser')['userid'];

        $data = [

            'client' => $clien,
            'clientname' => Clients::where('ClientID', '=', $clien)->pluck('ClientName'),
            'area' => Clients::where('ClientID', '=', $clien)->pluck('Area'),
            'location' => Clients::where('ClientID', '=', $clien)->pluck('Location'),
            'type' => 'Sales',
            'billno' => $bill,

            'total' => DB::select("select LoginID,Edit_Date,Min(es.edit_id) as minid, es.bill_no as bno,count(es.edit_id) as cnt from Edit_Sales1 es
				left join Edit_Sales_Item1 esi on es.Bill_No = esi.bill_no and es.edit_id = esi.edit_id where es.ClientID ='" . $clien . "'
				and esi.ClientID ='" . $clien . "'
				and es.Bill_No = '" . $bill . "' and esi.Bill_No =  '" . $bill . "'
				group by es.Edit_ID,esi.Edit_ID"),

        ];

        // return $data;

        $loadcontent = '';

        $num = 1;

        foreach ($data['total'] as $cli) {

            $loadcontent .= '<div class="accordion-item">
            <h2 class="accordion-header" id="headingOne">

                <button class="accordion-button collapsed" type="button" data-bs-toggle="collapse" 
                data-bs-target="#collapse' . $num . '" aria-expanded="false" aria-controls="collapse' . $num . '">

                    <div class="row col-12">
                    
                        <div class="col-3">
                            ' . $num . '
                        </div>

                        <div class="col-3">
                            ' . $cli->bno . ' 
                        </div>

  
                        <div class="col-3">
                            ' . $cli->LoginID . '
                        </div>
  
                        <div class="col-3">
                        ' . MainAcontroller::convertDateandTime($cli->Edit_Date)  . '
                        </div>
  
                    </div>
  
                </button>
  
            </h2>
  
            <div id="collapse' . $num . '" class="accordion-collapse collapse " aria-labelledby="headingOne" 
            >
                <div class="accordion-body">
  
                <table id="" class="table table-striped table-bordered" style="width:100%">
								<thead>
									<tr>
										<th>Sl No</th>
										<th>Item</th>
										<th>Batch</th>
										<th>Quantity</th>
										<th>Rate</th>
										<th>Amount</th>
										
									</tr>
								</thead>
								<tbody>';
            $loadcontent .= $this->addTableRows($cli->minid, $data['client']);
            $loadcontent .= '</tbody>
								
							</table>
  
                </div>
            </div>
        </div>';
            $num++;
        }

        $finalSale = DB::select("select item_description,batch,quantity,rate,amount from sales_item1
        where sales_item1.ClientID = '" . $data['client'] . "' and sales_item1.bill_no = '" . $data['billno'] . "' ");


        $myArr = [
            'content' => $loadcontent,
            'data' => $data,
            'finalsale' => $finalSale,

            'billed_by' => DB::select("select LoginID from sales1
            where sales1.ClientID = '" . $data['client'] . "' and sales1.bill_no = '" . $data['billno'] . "' "),

            'finalsaleTotal' => DB::select("select SUM(amount) as sumAmnt from sales_item1
            where sales_item1.ClientID = '" . $data['client'] . "' and sales_item1.bill_no = '" . $data['billno'] . "' "),
        ];

        return view('Admin.editReportDetail', $myArr);
    }

    function addTableRows($edit, $client)
    {

        $clien = session('LoggedUser')['userid'];

        $resArray = DB::select("select es.bill_no as bno,Item_description,quantity,batch,Min(es.edit_id) as minid,Rate,amount,grand_total from Edit_Sales1 es
       left join Edit_Sales_Item1 esi on es.Bill_No = esi.bill_no and es.edit_id = esi.edit_id where es.ClientID ='" . $clien . "'
       and esi.ClientID ='" . $clien . "'
       and es.edit_id = '" . $edit . "' and esi.edit_id = '" . $edit . "'
       group by Item_description,quantity,batch,grand_total,Rate,amount
       order by 5");

        $load = '';

        $slno = 1;
        foreach ($resArray as $res) {
            $load .= '<tr>
        <td>' . $slno . '</td>
        <td> ' . $res->Item_description . ' </td>
        <td>' . $res->batch . '</td>
        <td>' . $res->quantity . '</td>
        <td>' . $res->Rate . '</td>
        <td>' . $res->amount . '</td>
    </tr>';
            $slno++;
        }

        return $load;
    }


    function editReportsaleDetailAjax($client, $bill)
    {

        $clien = session('LoggedUser')['userid'];

        $data = [

            'client' => $client,
            'clientname' => Clients::where('ClientID', '=', $clien)->pluck('ClientName'),
            'area' => Clients::where('ClientID', '=', $clien)->pluck('Area'),
            'location' => Clients::where('ClientID', '=', $clien)->pluck('Location'),
            'type' => 'Sales',
            'billno' => $bill,

            'total' => DB::select("select Min(es.edit_id) as minid, es.bill_no as bno,count(es.edit_id) as cnt from Edit_Sales1 es
				left join Edit_Sales_Item1 esi on es.Bill_No = esi.bill_no and es.edit_id = esi.edit_id where es.ClientID ='50'
				and esi.ClientID ='" . $clien . "'
				and es.Bill_No = '" . $bill . "' and esi.Bill_No =  '" . $bill . "'
				group by es.Edit_ID,esi.Edit_ID"),

        ];

        $loadcontent = '';

        $num = 1;

        foreach ($data['total'] as $cli) {

            $loadcontent .= '<div class="accordion-item">
            <h2 class="accordion-header" id="headingOne">

                <button class="accordion-button accordion1" type="button">
                    <div class="row col-12">
                        <div class="col-3">
                            ' . $num . '
                        </div>

                        <div class="col-3">
                            ' . $cli->bno . ' 
                        </div>

  
                        <div class="col-3">
                            ' . $cli->minid . '
                        </div>
  
                        <div class="col-3">
                            Edit ' . $num . '
                        </div>
  
                    </div>
  
                </button>
  
            </h2>
  
            <div id="collapseOne" class="accordion-collapse collapse " aria-labelledby="headingOne" data-bs-parent="#accordionExample">
                <div class="accordion-body">
  
                   
  
                </div>
            </div>
        </div>';
        }

        return $loadcontent;
        //  return response()->json($loadcontent);
    }


    function viewEditReport(Request $request)
    {

        $client = session('LoggedUser')['userid'];
        $type = $request->rptType;
        $from = $request->from;
        $to = $request->to;


        if ($type == 'sale') {

            $data = [

                'client' => $client,
                'clientname' => Clients::where('ClientID', '=', $client)->pluck('ClientName'),
                'area' => Clients::where('ClientID', '=', $client)->pluck('Area'),
                'location' => Clients::where('ClientID', '=', $client)->pluck('Location'),
                'from' => $from,
                'to' => $to,
                'type' => 'Sales',


                'total' => DB::select("
                 select 'EDIT' as Flag,edit_id,edit_date,es.bill_no,es.grand_total as esGT,cs.grand_total as CurSGT,'' as UserName FROM sales1 cs 

            left join Edit_Sales1 es on cs.bill_no=es.bill_no
            and COALESCE(cs.clientid,'') = COALESCE(es.clientid,'')

            WHERE es.clientid = '" . $client . "' and cs.clientid = '" . $client . "'
            and round(COALESCE(es.Grand_Total,0),2) <>round(COALESCE(cs.Grand_Total,0),2)
            and Edit_Flag='e'
            and edit_date >='" . $from . "'
            and edit_date <='" . $to . "'
            group by es.bill_no

            UNION

            SELECT 'DELETE' as Flag,edit_id,edit_date,bill_no,grand_total as esGT,0 as CurSGT,'' as UserName FROM Edit_Sales1  
            WHERE clientid = '" . $client . "'  
            and Edit_Flag='d'
            and edit_date >='" . $from . "'
            and edit_date <='" . $to . "'
            group by bill_no
            order by edit_date
                 ")


            ];
        } else {


            $data = [

                'client' => $client,
                'clientname' => Clients::where('ClientID', '=', $client)->pluck('ClientName'),
                'area' => Clients::where('ClientID', '=', $client)->pluck('Area'),
                'location' => Clients::where('ClientID', '=', $client)->pluck('Location'),
                'from' => $from,
                'to' => $to,
                'type' => 'Purchase',


                'total' => DB::select("select edit_id,edit_date,ep.purchase_id as bill_no,ep.grand_total as esGT,cp.curtotal as CurSGT FROM `Edit_Purchase1` ep
                left join ( select distinct purchase_id as curbno,grand_total as curtotal,ClientID from purchase1 )cp on ep.purchase_id=cp.curbno
                and ep.clientid = cp.clientid
                WHERE ep.clientid = '" . $client . "' and cp.clientid = '" . $client . "'
                    and edit_date >='" . $from . "'
                and edit_date <='" . $to . "'
                and Round (COALESCE(ep.grand_total,0),2) <> Round(COALESCE(cp.curtotal,0),2)
                order by edit_date,ep.purchase_id
                 ")


            ];
        }

        return view('Admin.editReport', $data);
        // return $data;
    }
}
