<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\ItemModel;
use App\Models\SaleBillModel;
use App\Models\OpticalReceiptModel;

class OpticalController extends Controller
{
    function itemMaster(){
        $itemlist = $this->itemList();
        return view('/optical/item-master',['itemlist'=>$itemlist]);
    }
    
    function itemList(){
        $result = DB::select('SELECT DISTINCT(Name) As FrameName FROM tbl_item_masters');
        return $result;
    }

    function viewItemList(Request $req){
        // $result = DB::table('tbl_item_masters as tim')
        // ->join('tbl_item_type as tit', 'tim.ItemType', '=', 'tit.id')
        // ->select('tim.*','tit.Name As Item_Type')
        // ->get();
        $result = ItemModel::all();
        return view('optical/view-item-list',['data'=>$result]);
    }
    function itemCode(Request $req){
        $maxid =  ItemModel::max('srno');
        if($maxid == '' || $maxid == 0){
            $Code = "PI1";
            $srno = 1;
        }
        else{
            $Code = "PI".$maxid+1;
            $srno = $maxid+1;
        }
        return json_encode(array('Code'=>$Code,'srno'=>$srno));
    }

    function getFrameDetails(Request $req){
        $Name = $req->Name;
        $result = ItemModel::where('Name',$Name)->orderBy('id','desc')->limit(1)->get();
        $count = $result->count();
        if($count > 0){
            $status = 1;
            $Code = $result[0]->Code;
            $Rate = $result[0]->Rate;
            $srno = $result[0]->srno;
        }
        else{
            $status = 0;
            $Code = "";
            $Rate = "";
            $srno = "";
        }
        echo json_encode(array('srno'=>$srno,'Code'=>$Code,'Rate'=>$Rate,'status'=>$status));
    }

    function saveItem(Request $req){
        $CreatedDate = date('Y-m-d');
        if($req->action == 'Save'){
            $Item = new ItemModel;
        }
        else{
            $Item = ItemModel::find($req->id);
        }
        $Item->Code = $req->Code; 
        $Item->ItemType = 0;
        $Item->srno = $req->srno;
        $Item->Name = $req->Name;
        $Item->Qty = $req->Qty;
        $Item->Rate = $req->Rate;
        $Item->AddMinus = $req->AddMinus;
        $Item->Status = $req->Status;
        $Item->CreatedDate = $CreatedDate;
        $Item->CreatedBy = 27;
        $Item->save();
        $PostId = $Item->id;
        if($req->AddMinus == '+'){
            $Narration = "Stock Added";
            $Type = "Frame";
            $CrDr = "Dr";
            $BillNo = null;
            $MrdNo = 0;
            $FrameType = 0;
            $Power = null;
            $GlassName = null;
            $GlassRate = null;
            $TotalAmount = null;
            $sql = DB::insert("INSERT INTO tbl_item_ledger SET PostId='$PostId',FrameName='$req->Name',
            Qty='$req->Qty',Rate='$req->Rate',CrDr='$CrDr',Status='$req->Status',CreatedBy='27',
            CreatedDate='$CreatedDate',Narration='$Narration',Type='$Type',BillNo='$BillNo',MrdNo='$MrdNo',
            FrameType='$FrameType',Power='$Power',GlassName='$GlassName',GlassRate='$GlassRate',TotalAmount='$TotalAmount'");
        }
        else{
            $Narration = "Stock Sell";
            $Type = "Frame";
            $CrDr = "Cr";
            $BillNo = null;
            $MrdNo = 0;
            $FrameType = 0;
            $Power = null;
            $GlassName = null;
            $GlassRate = null;
            $TotalAmount = null;
            $sql = DB::insert("INSERT INTO tbl_item_ledger SET PostId='$PostId',FrameName='$req->Name',
            Qty='$req->Qty',Rate='$req->Rate',CrDr='$CrDr',Status='$req->Status',CreatedBy='27',
            CreatedDate='$CreatedDate',Narration='$Narration',Type='$Type',BillNo='$BillNo',MrdNo='$MrdNo',
            FrameType='$FrameType',Power='$Power',GlassName='$GlassName',GlassRate='$GlassRate',TotalAmount='$TotalAmount'");
        }
        return 1;
    }

    function saleBillNo(){
        $maxid =  SaleBillModel::max('id');
        if($maxid == '' || $maxid == 0){
            $Code = "SB1";
            $srno = 1;
        }
        else{
            $Code = "SB".$maxid+1;
            $srno = $maxid+1;
        }
        return $Code;
    }

    function saleBill(){
        $itemlist = $this->itemList();
        $BillNo = $this->saleBillNo();
        DB::table('tbl_temp_item_ledger')->where(['BillNo'=>$BillNo])->delete();
        return view('/optical/create-sale-bill',['itemlist'=>$itemlist,'BillNo'=>$BillNo]);
    }

    function editSaleBill($billno){
        $itemlist = $this->itemList();
        $result = SaleBillModel::where('BillNo',$billno)->first();
        DB::table('tbl_temp_item_ledger')->where(['BillNo'=>$billno])->delete();
        DB::select("INSERT INTO tbl_temp_item_ledger(PostId,FrameName,Qty,Rate,CrDr,Status,CreatedBy,CreatedDate,
                    Narration,BillNo,MrdNo,FrameType,GlassName,GlassRate,TotalAmount)
                  SELECT '0',FrameName,Qty,Rate,CrDr,Status,CreatedBy,CreatedDate,Narration,
                  '$billno',MrdNo,FrameType,GlassName,GlassRate,TotalAmount FROM `tbl_item_ledger`
                   WHERE BillNo='$billno'");
        return view('/optical/edit-sale-bill',['itemlist'=>$itemlist,'data'=>$result]);
    }

    function getBillNoList(){
        $result = SaleBillModel::where('SaleDate','>=','2020-01-01')->get();
        return view('/optical/inc-bill-no-lists',['data'=>$result]);
    }

    function checkCustDetails(Request $req){
        $Phone = $req->Phone;
        $result = SaleBillModel::where('MobileNo',$Phone)->orderBy('id','desc')->first();
        return json_encode($result);
    }

    function addItem(Request $req){
        $data = array(
        // 'MrdNo' => $req->MrdNo,
        'BillNo' => $req->BillNo,
        'FrameName' => $req->FrameName,
        'Qty' => $req->Qty,
        'Rate' => $req->FrameRate,
        'GlassName' => $req->GlassName,
        'GlassRate' => $req->GlassRate,
        'TotalAmount' => $req->Amount,
        'CreatedDate' => date('Y-m-d'),
        'CreatedBy' => 27,
        'PostId' => 0,
        'Status' => 1,
        'Narration' => null,
        'FrameType' => 0,
        'CrDr' => 'Cr',
    );
    //return $data;
    $insert_data[] = $data; 
    DB::table('tbl_temp_item_ledger')->where(['BillNo'=>$req->BillNo,'FrameName'=>$req->FrameName])->delete();
    DB::table('tbl_temp_item_ledger')->insert($insert_data);
    }
    function itemLists(Request $req){
        $BillNo = $req->BillNo;
        $result = DB::table('tbl_temp_item_ledger')->where(['BillNo'=>$req->BillNo])->get();
        return view('/optical/inc-temp-item-list',['data'=>$result]);
    }

    function deleteTempFrameItem(Request $req){
        DB::table('tbl_temp_item_ledger')->where(['id'=>$req->id])->delete();
        return 1;
    }

    function getItemSubTotal(Request $req){
        $BillNo = $req->BillNo;
        $result = DB::select("SELECT SUM(TotalAmount) As TotalAmount FROM tbl_temp_item_ledger WHERE BillNo='$BillNo'");
        $TotalAmount = $result[0]->TotalAmount;
        return $TotalAmount;
    }

    function getSubRefract(Request $req){
        $result = DB::select("SELECT * FROM tbl_optometrist WHERE id=(SELECT MAX(id) AS LastId FROM tbl_optometrist WHERE MrdNo='$req->mrdno' ORDER by VisitDate DESC LIMIT 1)");
        return json_encode(array('SrSphRe'=>$result[0]->SrSphRe,'SrCylRe'=>$result[0]->SrCylRe,
        'SrAxisRe'=>$result[0]->SrAxisRe,'SrNearAddRe'=>$result[0]->SrNearAddRe,
        'SrSphLe'=>$result[0]->SrSphLe,'SrCylLe'=>$result[0]->SrCylLe,
        'SrAxisLe'=>$result[0]->SrAxisLe,'SrNearAddLe'=>$result[0]->SrNearAddLe));
    }

    function saveSaleBill(Request $req){
        if($req->action == 'edit'){
            $SaleBillModel = SaleBillModel::firstWhere('BillNo',$req->BillNo);
        }
        else{
        $SaleBillModel = new SaleBillModel;
        }

    $SaleBillModel->BillNo = $req->BillNo;
    $SaleBillModel->SaleDate = $req->SaleDate;
    // $SaleBillModel->MrdNo = $req->MrdNo;
    $SaleBillModel->PatientName = $req->PatientName;
    $SaleBillModel->MobileNo = $req->MobileNo;
    $SaleBillModel->AgeGender = $req->AgeGender;
    $SaleBillModel->SrSphRe = $req->SrSphRe;
    $SaleBillModel->SrCylRe = $req->SrCylRe;
    $SaleBillModel->SrAxisRe = $req->SrAxisRe;
    $SaleBillModel->SrNearAddRe = $req->SrNearAddRe;
    $SaleBillModel->SrVaGlassRe = $req->SrVaGlassRe;
    $SaleBillModel->SrNvRe = $req->SrNvRe;
    $SaleBillModel->SrSphLe = $req->SrSphLe;
    $SaleBillModel->SrCylLe = $req->SrCylLe;
    $SaleBillModel->SrAxisLe = $req->SrAxisLe;
    $SaleBillModel->SrNearAddLe = $req->SrNearAddLe;
    $SaleBillModel->SrVaGlassLe = $req->SrVaGlassLe;
    $SaleBillModel->SrNvLe = $req->SrNvLe;
    $SaleBillModel->SubTotal = $req->SubTotal;
    $SaleBillModel->Advance = $req->Advance;
    $SaleBillModel->NetAmount = $req->NetAmount;
    $SaleBillModel->PayMode = $req->PayMode;
    $SaleBillModel->DeliveryDate = $req->DeliveryDate;
    $SaleBillModel->Narration = $req->Narration;
    $SaleBillModel->WorkComplete = $req->WorkComplete;
    $SaleBillModel->MakeOrder = $req->MakeOrder;
    $SaleBillModel->Address = $req->Address;
    $SaleBillModel->CreatedDate = date('Y-m-d');
    $SaleBillModel->ModifiedBy = 0;
    $SaleBillModel->Status = 1;
    $SaleBillModel->ModifiedDate = null;
    $SaleBillModel->save();
    $PostId = $SaleBillModel->id;
    DB::table('tbl_item_ledger')->where(['BillNo'=>$req->BillNo])->delete();
    DB::select("INSERT INTO tbl_item_ledger(PostId,FrameName,Qty,Rate,CrDr,Status,CreatedBy,CreatedDate,Narration,Type,BillNo,MrdNo,FrameType,Power,GlassName,GlassRate,TotalAmount)
    SELECT '$PostId',FrameName,Qty,Rate,CrDr,Status,CreatedBy,CreatedDate,Narration,'null',BillNo,MrdNo,FrameType,'null',GlassName,GlassRate,TotalAmount FROM `tbl_temp_item_ledger` WHERE BillNo='$req->BillNo'");
    DB::table('tbl_temp_item_ledger')->where(['BillNo'=>$req->BillNo])->delete();
    //return redirect('/printOpticalReceipt/'.$req->BillNo);
    return $req->BillNo;
    }

    function opticalReceipt(){
        return view('/optical/create-optical-receipt');
    }
    function getBillNoDetails(Request $req){
        $BillNo = $req->BillNo;
        $count = DB::table('tbl_optical_receipts')->where('BillNo',$BillNo)->count();
        $count2 = DB::table('tbl_sale_bills')->where('BillNo',$BillNo)->count();
        if($count > 0){
            $result = DB::table('tbl_optical_receipts')->where('BillNo',$BillNo)->first();
            return json_encode(array('result'=>$result,'Status'=>1));
        }
        else if($count2 > 0){
            $result = DB::table('tbl_sale_bills')->where('BillNo',$BillNo)->first();
            return json_encode(array('result'=>$result,'Status'=>2));
        }
        else{
            return json_encode(array('Status'=>3));
        }

    }

    function printOpticalReceipt($id){
        $result = SaleBillModel::where('BillNo',$id)->first();
        $result2 = DB::table('tbl_item_ledger')->where('BillNo',$id)->get();
        return view('/optical/print-optical-receipt',['data'=>$result,'data2'=>$result2]);
    }

    function opticalStockLedger(){
         $itemlist = $this->itemList();
        return view('/optical/optical-stock-ledger',['itemlist'=>$itemlist]);
    }
    
    function viewOpticalStockLedger(Request $req){
        $FrameName = $req->FrameName;
        $FromDate = $req->FromDate;
        $sql = "SELECT FrameName, SUM(TotDebit) As TotDebit,SUM(TotCredit) As TotCredit FROM 
        (SELECT FrameName, (Case when CrDr='Dr' then SUM(Qty) ELSE 0 END) AS TotDebit,(Case when CrDr='Cr' then SUM(Qty) ELSE 0 END) AS TotCredit 
        FROM `tbl_item_ledger` WHERE Status=1";
        if($req->FrameName!= ''){
            if($req->FrameName == 'all'){
                $sql.="";
            }
            else{
                $sql.=" AND FrameName='$FrameName'";
            }
        }
        if($req->FromDate!= ''){
            $sql.=" AND CreatedDate<='$FromDate'";
        }
        $sql.= " GROUP BY FrameName,CrDr) as a GROUP BY FrameName";
        //return $sql;
        $result = DB::select($sql);
        return view('/optical/view-stock-report',['data'=>$result]);
    }

    function saveOpticalReceipt(Request $req){
        if($req->action == 'Save'){
        $OpticalReceipt = new OpticalReceiptModel;
        }
        else{
            $OpticalReceipt = OpticalReceiptModel::find($req->receiptid);    
        }
        $OpticalReceipt->BillNo = $req->BillNo;
    $OpticalReceipt->ReceiptDate = $req->ReceiptDate;
    $OpticalReceipt->MrdNo = $req->MrdNo;
    $OpticalReceipt->PatientName = $req->PatientName;
    $OpticalReceipt->MobileNo = $req->MobileNo;
    $OpticalReceipt->SubTotal = $req->SubTotal;
    $OpticalReceipt->Advance = $req->Advance;
    $OpticalReceipt->Balance = $req->Balance;
    $OpticalReceipt->PaidAmount = $req->PaidAmount;
    $OpticalReceipt->NetAmount = $req->NetAmount;
    $OpticalReceipt->PayMode = $req->PayMode;
    $OpticalReceipt->Narration = $req->Narration;
    $OpticalReceipt->CreatedDate = date('Y-m-d');
    $OpticalReceipt->CreatedBy = 27;
    $OpticalReceipt->save();
    if($req->action == 'Save'){
    $PostId = $OpticalReceipt->id;
    $ReceiptNo = "SBR".$PostId;
    OpticalReceiptModel::where(['id'=>$PostId])->update(['ReceiptNo'=>$ReceiptNo]);
    }
    return 1;
    }

    function opticalSaleLedger(){
        return view('/optical/optical-sale-ledger');
    }

    function viewOpticalSaleLedger(Request $req){
        $ToDate = $req->ToDate;
        $FromDate = $req->FromDate;
        $sql = "SELECT id,BillNo,SaleDate,PatientName,MobileNo,SubTotal,Advance,NetAmount,PaidAmount,
                Balance,ReceiptDate,DeliveryDate,PayMode FROM `tbl_sale_bills` WHERE Status=1";
        if($req->FromDate!= ''){
            $sql.=" AND SaleDate>='$FromDate'";
        }
        if($req->ToDate!= ''){
            $sql.=" AND SaleDate<='$ToDate'";
        }
        $sql.= " ORDER BY SaleDate DESC";
        $result = DB::select($sql);
        return view('/optical/view-sale-report',['data'=>$result]);
    }

    function opticalCollectionReport(){
        return view('/optical/optical-collection-ledger');
    }

    function viewOpticalCollectionLedger(Request $req){
        $ToDate = $req->ToDate;
        $FromDate = $req->FromDate;
        $sql = "SELECT id,BillNo,SaleDate,PatientName,MobileNo,SubTotal,Advance,NetAmount,PaidAmount,
        Balance,ReceiptDate,DeliveryDate,PayMode,1 as Roll FROM `tbl_sale_bills` 
        WHERE SaleDate>='$FromDate' AND SaleDate<='$ToDate'
        UNION ALL SELECT id,BillNo,ReceiptDate as SaleDate,PatientName,MobileNo,SubTotal,Advance,
        NetAmount,PaidAmount,Balance,ReceiptDate,ReceiptDate as DeliveryDate,PayMode,2 as Roll 
        FROM `tbl_optical_receipts` WHERE ReceiptDate>='$FromDate' AND ReceiptDate<='$ToDate'";
        $result = DB::select($sql);

        $sql2 = SaleBillModel::whereBetween('SaleDate', array($FromDate, $ToDate))->sum('Advance');
        $sql3 = OpticalReceiptModel::whereBetween('ReceiptDate', array($FromDate, $ToDate))->sum('PaidAmount');
        $Total = $sql2+$sql3;
        return view('/optical/view-collection-report',['data'=>$result,'Total'=>$Total]);
    }

    function makeOrder(){
        return view('/optical/view-make-order');
    }

    function viewMakeOrder(Request $req){
        $ToDate = $req->ToDate;
        $FromDate = $req->FromDate;
        $result = SaleBillModel::whereBetween('SaleDate', array($FromDate, $ToDate))->where(['Status'=>1,'MakeOrder'=>0])->get();
        return view('/optical/view-make-order-list',['data'=>$result]);
    }

    function saveMakeOrder(Request $req){
        $BillId = $req->BillId;
        $result = SaleBillModel::whereIn('id',$BillId)->update(['MakeOrder'=>1]);
        return redirect('/makeOrder');
    }

    function opticalSellReport(){
        return view('/optical/optical-sell-report');
    }
    function viewOpticalSellReport(Request $req){
        $ToDate = $req->ToDate;
        $FromDate = $req->FromDate;
        $sql = "SELECT id,BillNo,SaleDate,PatientName,MobileNo,SubTotal,Advance,NetAmount,PaidAmount,
        Balance,ReceiptDate,DeliveryDate,PayMode,1 as Roll FROM `tbl_sale_bills` 
        WHERE Status=1 AND SaleDate>='$FromDate' AND SaleDate<='$ToDate'";
        $result = DB::select($sql);
        $sql2 = SaleBillModel::whereBetween('SaleDate', array($FromDate, $ToDate))->sum('Advance');
        
        $sql3 = "SELECT id,BillNo,ReceiptDate as SaleDate,PatientName,MobileNo,SubTotal,Advance,
        NetAmount,PaidAmount,Balance,ReceiptDate,ReceiptDate as DeliveryDate,PayMode,2 as Roll 
        FROM `tbl_optical_receipts` WHERE Status=1 AND ReceiptDate>='$FromDate' AND ReceiptDate<='$ToDate'";
        $result2 = DB::select($sql3);
        $sql4 = OpticalReceiptModel::whereBetween('ReceiptDate', array($FromDate, $ToDate))->sum('PaidAmount');
        $Total = $sql2+$sql4;
        return view('/optical/view-optical-sale-report',['data'=>$result,'data2'=>$result2,'TotalSaleBill'=>$sql2,
        'TotalReceiptBill'=>$sql4,'Total'=>$Total]);
    }
    function viewOpticalSummaryReport(Request $req){
        $ToDate = $req->ToDate;
        $FromDate = $req->FromDate;
        $result = DB::select("SELECT count(*) AS TotSale,SUM(Advance) AS Amount,SaleDate FROM `tbl_sale_bills` WHERE SaleDate>='$req->FromDate' AND SaleDate<='$req->ToDate' GROUP BY SaleDate");
        $TotSaleAmt = SaleBillModel::whereBetween('SaleDate', array($FromDate, $ToDate))->sum('Advance');
        $TotSale = SaleBillModel::whereBetween('SaleDate', array($FromDate, $ToDate))->count();
        return view('/optical/optical-summary-report',['data'=>$result,'TotSaleAmt'=>$TotSaleAmt,'TotSale'=>$TotSale]);
    }

    function viewReceiptSummaryReport(Request $req){
        $ToDate = $req->ToDate;
        $FromDate = $req->FromDate;
        $result = DB::select("SELECT count(*) AS TotReceipt,SUM(PaidAmount) AS Amount,ReceiptDate FROM `tbl_optical_receipts` WHERE ReceiptDate>='$req->FromDate' AND ReceiptDate<='$req->ToDate' GROUP BY ReceiptDate");
        $TotReceiptAmt = OpticalReceiptModel::whereBetween('ReceiptDate', array($FromDate, $ToDate))->sum('PaidAmount');
        $TotReceipt = OpticalReceiptModel::whereBetween('ReceiptDate', array($FromDate, $ToDate))->count();
        return view('/optical/optical-receipt-summary-report',['data'=>$result,'TotReceiptAmt'=>$TotReceiptAmt,'TotReceipt'=>$TotReceipt]);
    }

    function opticalOutstandingReport(){
        return view('/optical/optical-outstanding-ledger');
    }

    function viewOpticalOutstandingLedger(Request $req){
        $FromDate = $req->FromDate;
        $sql = "SELECT id,BillNo,SaleDate,PatientName,MobileNo,SubTotal,Advance,NetAmount,PaidAmount,Balance,ReceiptDate,DeliveryDate,PayMode,1 as Roll 
        FROM `tbl_sale_bills` WHERE Status=1 AND SaleDate<='$FromDate' AND ReceiptDate is null ORDER BY DeliveryDate,BillNo";
        $result = DB::select($sql);
        return view('/optical/optical-outstamding-report',['data'=>$result]);
    }
    
}