<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\QueueManagement;
use App\Http\Controllers\SpecInstController;
use App\Models\VisitModel;
use App\Models\OptoModel;
use App\Models\PatientModel;
use App\Models\ConsultantModel;
use App\Models\TreatmentModel;
use App\Http\Controllers\MasterController;
use App\Http\Controllers\TreatmentController;
use App\Models\ConsultantTreatmentModel;
use App\Models\OTModel;
use App\Models\UserMaster;
use App\Models\SpecialityModel;

class UtilityController extends Controller
{
    function Cashless(){
        //$result = DB::table('tbl_ot_notes')->get();
        return view('/utility/view-cashless');
    }

    function viewCashlessPatients(Request $req){
        if(!empty($req->FromDate))
            {   
                $result = DB::table('tbl_ot_notes')->where('Category','Cashless')->whereBetween('CreatedDate', array($req->FromDate, $req->ToDate))->get();
                
            }
            else{
                $result = DB::table('tbl_ot_notes')->where('Category','Cashless')->get();
            }
            //return $result;
        return view('/utility/inc-cashless-patient-lists',['data'=>$result]);
    }

    function AvgPtStay(){
        return view('/utility/view-average-patient-stay');
    }

    function viewAvgPatients(Request $req){
        if(!empty($req->FromDate))
            {   
                $sql = "SELECT MrdNo,PatientName,Age,Gender,InDate,OutTime,InTime,TIME_FORMAT(TIMEDIFF(OutTime,InTime),'%H:%i:%s') AS AvgTime FROM tbl_queue_managements WHERE Checked=1 AND InDate>='$req->FromDate' AND InDate<='$req->ToDate'";
                $result = DB::select($sql);
                
            }
        return view('/utility/inc-average-patient-lists',['data'=>$result]);
    }

    function SpecInstReport(){
        $pomspecinstList = (new SpecInstController)->SpecInstList('PlanOfMang');
        $remarkspecinstList = (new SpecInstController)->SpecInstList('Remark');
        return view('/utility/view-spec-inst-report',['remarkspecinstList'=>$remarkspecinstList,'pomspecinstList'=>$pomspecinstList]);
    }

    function viewSpecInstReport(Request $req){
        if(!empty($req->FromDate))
            {   
                $sql = "SELECT tc.*,tp.Phone,tp.Address FROM tbl_consultants tc LEFT JOIN tbl_patients tp ON tp.MrdNo=tc.MrdNo WHERE tc.VisitDate>='$req->FromDate' AND tc.VisitDate<='$req->ToDate'";
                if($req->CmbPlanOfMang!=''){
                    $sql.= " AND tc.CmbPlanOfMang='".$req->CmbPlanOfMang."'";
                }
                if($req->CmbRemark!=''){
                    $sql.= " AND tc.CmbRemark='".$req->CmbRemark."'";
                }
                //return $sql;
                $result = DB::select($sql);
                
            }
        return view('/utility/inc-spec-inst-report-lists',['data'=>$result]);
    }

    function ProjectReport(){
        $projectList = DB::select("SELECT DISTINCT(Project) AS Project FROM tbl_ot_notes WHERE Project!=''");
        return view('/utility/view-project-report',['projectList'=>$projectList]);
    }

    function viewProjectReport(Request $req){
        if(!empty($req->FromDate))
            {   
                $sql = "SELECT tc.*,tp.Name AS OptoName FROM tbl_ot_notes tc LEFT JOIN tbl_users tp ON tp.id=tc.ConfirmOpto WHERE tc.CreatedDate>='$req->FromDate' AND tc.CreatedDate<='$req->ToDate' AND tc.Project!=''";
                if($req->Project!=''){
                    $sql.= " AND tc.Project='".$req->Project."'";
                }
                //return $sql;
                $result = DB::select($sql);
                
            }
        return view('/utility/inc-project-report-lists',['data'=>$result]);
    }

    function calAvgTime(Request $req){
        if(!empty($req->FromDate))
            {   
                $sql = "SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( AvgTime) ) ) AS AvgTime FROM (SELECT TIME_FORMAT(TIMEDIFF(OutTime,InTime),'%H:%i:%s') AS AvgTime FROM `tbl_queue_managements` WHERE Checked=1 AND InDate>='$req->FromDate' AND InDate<='$req->ToDate') as a;";
                $result = DB::select($sql);
                
                $count = DB::table('tbl_queue_managements')->where('Checked','1')->whereBetween('InDate', array($req->FromDate, $req->ToDate))->count();
               
            }
            $time = $result[0]->AvgTime;
            $time = explode(':', $time);
            $avgtime = ($time[0]*60) + ($time[1]) + ($time[2]/60);
            return round($avgtime/$count);
    }

    
    function SpeedTest(){
        $totalPatient = (new QueueController)->totalPatient();
        $totalCheckPatient = (new QueueController)->totalCheckPatient();
        //$queuelist = $this->queuelist();
        $consultantList = (new MasterController)->consultantList();
        $cityList = (new MasterController)->cityList();
        $stateList = (new MasterController)->stateList();
        $patientList = (new MasterController)->patientList();
        $totalUncheckPatient = $totalPatient - $totalCheckPatient;
        $NewMrdNo = (new QueueController)->maxMrdNo();
        return view('opd/speedqueuemanagement',['NewMrdNo'=>$NewMrdNo,'patientList'=>$patientList,'stateList'=>$stateList,'cityList'=>$cityList,'consultantList'=>$consultantList,'totalPatient'=>$totalPatient,'totalCheckPatient'=>$totalCheckPatient,'totalUncheckPatient'=>$totalUncheckPatient]);
    }


    function viewSpeedQueueList(Request $request){
        $CurrDate = date('Y-m-d');
        if(!empty($request->FromDate))
            {
                $result =  DB::table('tbl_queue_managements as tq')
                ->leftJoin('tbl_users as tu', 'tu.id', '=', 'tq.Consultant')
                ->leftJoin('tbl_patients as tp', 'tp.MrdNo', '=', 'tq.MrdNo')
                ->leftJoin('tbl_users as tu2', 'tu2.id', '=', 'tq.ConfirmConsult')
                ->leftJoin('tbl_users as tu3', 'tu3.id', '=', 'tq.ConfirmOpto')
                ->where('InDate',$request->FromDate)
                ->select('tq.*','tp.Phone','tu2.Name As Consultant','tu3.Name As Opto')
                ->orderBy('tq.InTime','asc')->get();
            }
            else{
        $result = DB::select("SELECT tq.*,tp.Phone,tu2.Name As Consultant,tu3.Name As Opto FROM tbl_queue_managements tq 
                    LEFT JOIN tbl_users tu ON tq.Consultant=tu.id 
                    LEFT JOIN tbl_patients tp ON tp.MrdNo=tq.MrdNo 
                    LEFT JOIN tbl_users tu2 ON tq.ConfirmConsult=tu2.id 
                    LEFT JOIN tbl_users tu3 ON tq.ConfirmOpto=tu3.id 
                    WHERE Checked=0 AND tq.InDate='$CurrDate' ORDER BY tq.InTime");
            }
        //return $result;
       
        return view('opd/viewspeedqueuelists',['data'=>$result]);
    }

    function speedoptoform($mrdno,$date,$visitno){
        $ptdata = PatientModel::select()->where(['MrdNo'=>$mrdno])->get();
        $PatientName = $ptdata[0]->PatientName;
        //return $ptdata;exit();
        
        $maxid = VisitModel::select()->where(['MrdNo'=>$mrdno])->max('VisitNo');
         //$VisitNo = $maxid+1;
        $VisitNo = $visitno;
        $rncnt = VisitModel::select()->where(['MrdNo'=>$mrdno,'VisitDate'=>$date,'VisitNo'=>$VisitNo])->count();
        if($rncnt > 0){}
        else{
          $Visit = new VisitModel;
          $Visit->MrdNo = $mrdno;
          $Visit->PatientName = $PatientName;
          $Visit->VisitDate = $date;
          $Visit->VisitNo = $VisitNo;
          $Visit->save();
        }

        $result4 = VisitModel::select()->where(['MrdNo'=>$mrdno,'VisitDate'=>$date,'VisitNo'=>$VisitNo])->first();
        $findvisitno = $result4->VisitNo;
        //$rncnt2 = VisitModel::select()->where(['MrdNo'=>$mrdno])->count();
        
        
        $compDays = (new MasterController)->compDays();
        $cnt = OptoModel::select()->where(['MrdNo'=>$mrdno,'VisitDate'=>$date,'VisitNo'=>$VisitNo])->count();
        if($cnt > 0){}
        else{
            $OptoModel = new OptoModel;
            $OptoModel->MrdNo = $mrdno; $OptoModel->VisitNo = $VisitNo;
            $OptoModel->VisitDate = $date; $OptoModel->PatientName = $PatientName;
            $OptoModel->AllergyHistory = 'No history of allergy';
            $OptoModel->CreatedBy = 19;
            $OptoModel->ModifiedBy = 19;
            $OptoModel->save();
            //QueueManagement::where(['MrdNo'=>$mrdno,'InDate'=>$date,'Checked'=>0])->update(['VisitNo'=>$VisitNo]);
        }

        $cnt2 = ConsultantModel::select()->where(['MrdNo'=>$mrdno,'VisitDate'=>$date,'VisitNo'=>$VisitNo])->count();
        if($cnt2 > 0){}
        else{
            $ConsultantModel = new ConsultantModel;
            $ConsultantModel->MrdNo = $mrdno; $ConsultantModel->VisitNo = $VisitNo;
            $ConsultantModel->VisitDate = $date; $ConsultantModel->PatientName = $PatientName;
            $ConsultantModel->CreatedBy = 19;
            $ConsultantModel->ModifiedBy = 19;
            $ConsultantModel->save();
            $PostId = $ConsultantModel->id;
    }

    $cnt3 = ConsultantTreatmentModel::select()->where(['MrdNo'=>$mrdno,'VisitDate'=>$date,'VisitNo'=>$VisitNo])->count();
        if($cnt3 > 0){}
        else{
            $ConsultantTreatmentModel = new ConsultantTreatmentModel;
            //$ConsultantTreatmentModel->PostId = $PostId;
            $ConsultantTreatmentModel->MrdNo = $mrdno; $ConsultantTreatmentModel->VisitNo = $VisitNo;
            $ConsultantTreatmentModel->VisitDate = $date;
            $ConsultantTreatmentModel->save();
            
        }
        $data4 = QueueManagement::select('Amount')->where(['MrdNo'=>$mrdno,'InDate'=>$date,'VisitNo'=>$VisitNo])->get();
        $cntrow4 = $data4->count();   
        if($cntrow4 > 0){
        $fees = $data4[0]->Amount;
        }
        else{
        $data5 =  QueueManagement::select('Amount')->where(['MrdNo'=>$mrdno,'InDate'=>$date])->get();
        $cntrow5 = $data5->count(); 
        if($cntrow5 > 0){
            $fees = $data5[0]->Amount;
        }  
        else{
            $fees = 0;   
        }
       
        }
        $PtBal = (new QueueController)->getPatientBal($mrdno);
        $totalPatient = (new QueueController)->totalPatient();
        $totalCheckPatient = (new QueueController)->totalCheckPatient();
        $totalUncheckPatient = $totalPatient - $totalCheckPatient;
        $visitList = (new MasterController)->getPatientVisit($mrdno);

        return view('opd/speededitoptoform',['visitList'=>$visitList,'totalPatient'=>$totalPatient,'totalCheckPatient'=>$totalCheckPatient,'totalUncheckPatient'=>$totalUncheckPatient,'PtBal'=>$PtBal,'fees'=>$fees,'ptdata'=>$ptdata,'MrdNo'=>$mrdno,'VisitDate'=>$date,'VisitNo'=>$VisitNo]); 
      
        
       
    }

    function SendBrochures(){
        $brochurelist = (new MasterController)->getBrochures();
        return view('/utility/send-brochures',['brochurelist'=>$brochurelist]);
    }

    function SaveBrochure(Request $req){
        $BrochureId = $req->BrochureId;
        $result = DB::select("SELECT * FROM tbl_brochures WHERE id='$BrochureId'");
        $filename = $result[0]->File;
        $url = $result[0]->Url;
        //return $url;
        $sms_text = strip_tags($req->Message);
        $api_url = "https://smswaapi.in/api/send?number=91".$req->Phone."&type=media&message=".$sms_text."&media_url=".$url."&filename=".$filename."&instance_id=64B0F93ED3A13&access_token=649d339dbf9f2";
        //return $api_url;
        $response = file_get_contents($api_url);
        return 1;
        // 
        // $character = json_decode($response,true);
        // $status = $character['status'];
        //return $status;
        //return 1;
        // if($status == 'success'){
        //     return 1;
        // }
        // else{
        //     return 0;
        // }
    }

    function investigationReport(){
        return view('/utility/view-investigation-report');
    }

    function viewInvestigationReport(Request $req)
    {
        $query = DB::table('tbl_opd_investigations as tc')
            ->select(
                'tu.Name',
                'tu2.Name as MoneyRecName',
                'tc.InvStName',
                'tc.Amount',
                'tc.VisitDate',
                'tc.PatientName',
                'tc.MrdNo',
                'tp.Phone',
                'tp.Address'
            )
            ->leftJoin('tbl_patients as tp', 'tp.MrdNo', '=', 'tc.MrdNo')
            ->leftJoin('tbl_users as tu', 'tc.CreatedBy', '=', 'tu.id')
            ->leftJoin('tbl_users as tu2', 'tc.InvMoneyReceivedBy', '=', 'tu2.id')
            ->where('tc.InvStName', '!=', '')
            ->where('tc.MrdNo', '!=', '')
            ->where('tc.InvMoneyReceived', '=', 1);

        if (!empty($req->FromDate)) {
            $query->whereBetween('tc.VisitDate', [$req->FromDate, $req->ToDate]);
        }

        if (!empty($req->Consultant) && $req->Consultant !== 'all') {
            $query->where('tc.CreatedBy', $req->Consultant);
        }

        if (!empty($req->ConfirmOpto) && $req->ConfirmOpto !== 'all') {
            $query->where('tc.InvMoneyReceivedBy', $req->ConfirmOpto);
        }

        $result = $query->get();
        return view('/utility/inc-investigation-report-lists', ['data' => $result]);
    }
}
