<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\LenseModel;
class LenseController extends Controller
{
    function stockIn(){
        return view('/lense/lense-stock-in');
    }

    function vendorLists(){
        $result = LenseModel::select('VedName')->distinct()->get();
        $output = "<option selected='' value=''>...</option>";
        foreach($result as $data){
        $output.= "<option value='".$data->VedName."'>".$data->VedName."</option>";
        }
        return $output;
    }

    function iolCompanyLists(){
        $result = LenseModel::select('IolCompName')->distinct()->get();
        $output = "<option selected='' value=''>...</option>";
        foreach($result as $data){
        $output.= "<option value='".$data->IolCompName."'>".$data->IolCompName."</option>";
        }
        return $output;
    }

    function companyLists(){
        $result = LenseModel::select('Cname')->distinct()->where('Cname','!=','')->get();
        $output = "<option selected='' value=''>...</option>";
        foreach($result as $data){
        $output.= "<option value='".$data->Cname."'>".$data->Cname."</option>";
        }
        return $output;
    }

    function modelLists(Request $req){
        $Cname = $req->Cname;
        $result = LenseModel::select('Model')->distinct()->where('Cname',$Cname)->where('Model','!=','')->get();
        $output = "<option selected='' value=''>...</option>";
        foreach($result as $data){
            $output.= "<option value='".$data->Model."'>".$data->Model."</option>";
        }
        return $output;
    }

    function modelLists2(Request $req){
        $Cname = $req->Cname;
        $result = LenseModel::select('Model')->distinct()->where('Cname',$Cname)->where('Model','!=','')->get();
        $output = "<option selected='' value=''>...</option>";
        foreach($result as $data){
            $output.= "<option value='".$data->Model."'>".$data->Model."</option>";
        }
        return $output;
    }

    function srNoLists(Request $req){
        $Cname = $req->Cname;
        $Model = $req->IolType;
        $Power = $req->Power;
        $result = LenseModel::select('SrNo')->distinct()->where(['Cname'=>$Cname,'Model'=>$Model,'Power'=>$Power])->where('SrNo','!=','')->get();
        $output = "<option selected='selected' value=''>...</option>";
        //$output = "";
        foreach($result as $data){
            $output.= "<option value='".$data->SrNo."'>".$data->SrNo."</option>";
        }
        return $output;
    }

    function powerLists(Request $req){
        $Cname = $req->Cname;
        $Model = $req->Model;
        $result = LenseModel::select('Power')->distinct()->where(['Cname'=>$Cname,'Model'=>$Model])->get();
        $output = "<option selected='' value=''>...</option>";
        foreach($result as $data){
            $output.= "<option value='".$data->Power."'>".$data->Power."</option>";
        }
        return $output;
    }

    function powerLists2(Request $req){
        $Cname = $req->Cname;
        $Model = $req->Model;
        $result = LenseModel::select('Power')->distinct()->where(['Model'=>$Model])->get();
        $output = "<option selected='' value=''>...</option>";
        foreach($result as $data){
            $output.= "<option value='".$data->Power."'>".$data->Power."</option>";
        }
        return $output;
    }

    function saveLense(Request $req){
        if($req->action == 'Save'){
            $Lense = new LenseModel;
            $cnt = LenseModel::where(['Cname'=>$req->Cname,'Model'=>$req->Model,'Power'=>$req->Power])->count();
            if($cnt > 0){
                $TransType = 'PR';
            }
            else{
                $TransType = 'OB';
            }
            
        }
        else{
            $Lense = LenseModel::find($req->id);
            $TransType = $req->TransType;
        }
        $Lense->Cname = $req->Cname;
        $Lense->Model = $req->Model;
        $Lense->Power = $req->Power;
        $Lense->ExpMonth = $req->ExpMonth;
        $Lense->ExpYear = $req->ExpYear;
        $Lense->StockDate = $req->StockDate;
        $Lense->Quantity = $req->Quantity;
        $Lense->Narration = $req->Narration;
        $Lense->TransType = $TransType;
        $Lense->VedName = $req->VedName;
        $Lense->IolCompName = $req->IolCompName;
        $Lense->ExpiryDate = $req->ExpiryDate;
        $Lense->Amount = $req->Amount;
        $Lense->SrNo = $req->SrNo;
        $Lense->BillNo = $req->BillNo;
        $Lense->BillDate = $req->BillDate;
        $Lense->CrDr = 'D';
        $Lense->OtId = 0;
        $Lense->Status = 1;
        $Lense->CreatedDate = date('Y-m-d');
        $Lense->CreatedTime = date('h:i a');
        $Lense->save();
        return 1;
    }

    function viewLenseStockList(Request $req){
        $FromDate = $req->FromDate;
        $ToDate = $req->ToDate;
        $CrDr = $req->CrDr;
        $result = LenseModel::whereBetween('StockDate', array($FromDate, $ToDate))->where('CrDr',$CrDr)->get();
        return view('/lense/view-lense-stock',['data'=>$result,'CrDr'=>$CrDr]);
    }

    function editLense(Request $req){
        $id = $req->id;
        $result = LenseModel::find($id);
        return $result;
    }

    function deleteLense(Request $req){
        LenseModel::find($req->id)->delete();
        return 1;
    }

    function stockOut(){
        return view('/lense/lense-stock-out');
    }

    function saveLenseOut(Request $req){
        if($req->action == 'Save'){
            $Lense = new LenseModel;
        }
        else{
            $Lense = LenseModel::find($req->id);
        }
        $Lense->Cname = $req->Cname;
        $Lense->Model = $req->Model;
        $Lense->Power = $req->Power;
        $Lense->ExpMonth = $req->ExpMonth;
        $Lense->ExpYear = $req->ExpYear;
        $Lense->StockDate = $req->StockDate;
        $Lense->Quantity = $req->Quantity;
        $Lense->Narration = $req->Narration;
        $Lense->VedName = $req->VedName;
        $Lense->IolCompName = $req->IolCompName;
        $Lense->ExpiryDate = $req->ExpiryDate;
        $Lense->Amount = $req->Amount;
        $Lense->TransType = 'OP';
        $Lense->CrDr = 'C';
        $Lense->OtId = 0;
        $Lense->CreatedDate = date('Y-m-d');
        $Lense->CreatedTime = date('h:i a');
        $Lense->save();
        return 1;
    }

    function company_list(){
        $result = LenseModel::select('Cname')->distinct()->get();
        return $result;
    }

    function model_list(Request $req){
        $Cname = $req->Cname;
        if($Cname == 'all'){
        $result = LenseModel::select('Model')->distinct()->get();
        }
        else{
        $result = LenseModel::select('Model')->distinct()->where('Cname',$Cname)->get();  
        }
        $output = "";
        $output.="<option value='all' selected=''>All</option>";
        foreach($result as $data){
        $output.= "<option value='".$data->Model."'>".$data->Model."</option>";
        }
        return $output;
    }

    function power_list(Request $req){
        $Cname = $req->Cname;
        $Model = $req->Model;
        if($Cname == 'all' && $Model == 'all'){
        $result = LenseModel::select('Power')->distinct()->get();
        }
        else if($Cname == 'all' && $Model != 'all'){
        $result = LenseModel::select('Power')->distinct()->where(['Model'=>$Model])->get();
        }
        else if($Cname != 'all' && $Model == 'all'){
        $result = LenseModel::select('Power')->distinct()->where(['Cname'=>$Cname])->get();
        }
        else{
        $result = LenseModel::select('Power')->distinct()->where(['Cname'=>$Cname,'Model'=>$Model])->get();
        }
        $output = "";
        $output.="<option value='all' selected=''>All</option>";
        foreach($result as $data){
        $output.= "<option value='".$data->Power."'>".$data->Power."</option>";
        }
        return $output;
    }
    
    function lenseReport(){
        $companyList = $this->company_list();
        $powerlist = LenseModel::select('Power')->distinct()->get();
        $modellist = LenseModel::select('Model')->distinct()->get();
        $expyearlist = LenseModel::select('ExpYear')->distinct()->get();
        $vendorlist = LenseModel::select('VedName')->distinct()->get();
        $comp_name_list = LenseModel::select('IolCompName')->distinct()->get();
        
        return view('/lense/view-lense-report',['comp_name_list'=>$comp_name_list,'vendorlist'=>$vendorlist,'companyList'=>$companyList,'powerlist'=>$powerlist,'modellist'=>$modellist,'expyearlist'=>$expyearlist]);
    }

    function viewLenseStockReport(Request $req){
        $Cname = $req->Cname;
        $Model = $req->Model;
        $Power = $req->Power;
        $VedName = $req->VedName;
        $IolCompName = $req->IolCompName;
        $FromDate = $req->FromDate;
        $ToDate = $req->ToDate;
        $sql = "SELECT IolCompName,VedName,Cname,Model, Power, SUM(debitsm) - SUM(creditsm) as balqty FROM 
                (SELECT IolCompName,VedName,Cname,Model, Power, (CASE WHEN crdr = 'D' THEN SUM(Quantity) ELSE '0' END) AS debitsm,
                 (CASE WHEN crdr = 'C' THEN SUM(Quantity) Else '0' END) AS creditsm  From tbl_lense_ledger WHERE Status=1 ";
        if($Cname != 'all'){
            $sql.= " AND Cname = '$Cname'";
        }
        if($Model != 'all'){
            $sql.= " AND Model = '$Model'";
        }
        if($Power != 'all'){
            $sql.= " AND Power = '$Power'";
        }
        if($VedName != 'all'){
            $sql.= " AND VedName = '$VedName'";
        }
        if($IolCompName != 'all'){
            $sql.= " AND IolCompName = '$IolCompName'";
        }
        if($FromDate != 'all'){
            $sql.= " AND BillDate>= '$FromDate'";
        }
        if($ToDate != 'all'){
            $sql.= " AND BillDate <= '$ToDate'";
        }
        $sql.=" GROUP BY IolCompName,VedName,Cname,Model, Power, crdr) A GROUP BY IolCompName,VedName,Cname,Model, Power";
        //return $sql;
        $result = DB::select($sql);
        $sql2 = "SELECT SUM(balqty) as balqty FROM ($sql) as a";
        //return $sql2;
        $result2 = DB::select($sql2);
        $totqty = $result2[0]->balqty;
        return view('/lense/view-stock-lense-report',['data'=>$result,'totqty'=>$totqty]);
    }

    function viewLenseStockReport2(Request $req){
        $Cname = $req->Cname;
        $Model = $req->Model;
        $Power = $req->Power;
        $VedName = $req->VedName;
        $IolCompName = $req->IolCompName;
        $ExpMonth = $req->ExpMonth;
        $ExpYear = $req->ExpYear;
        $sql = "SELECT * FROM tbl_lense_ledger WHERE Status=1 AND CrDr='D'";
        if($Cname != 'all'){
            $sql.= " AND Cname = '$Cname'";
        }
        if($Model != 'all'){
            $sql.= " AND Model = '$Model'";
        }
        if($Power != 'all'){
            $sql.= " AND Power = '$Power'";
        }
        if($VedName != 'all'){
            $sql.= " AND VedName = '$VedName'";
        }
        if($IolCompName != 'all'){
            $sql.= " AND IolCompName = '$IolCompName'";
        }
        if($ExpMonth != ''){
            $sql.= " AND ExpMonth = '$ExpMonth'";
        }
        if($ExpYear != ''){
            $sql.= " AND ExpYear = '$ExpYear'";
        }
        //return $sql;
        $result = DB::select($sql);
        return view('/lense/view-stock-lense-report2',['data'=>$result]);
    }

    function viewLenseStockReport3(Request $req){
        $Cname = $req->Cname;
        $Model = $req->Model;
        $Power = $req->Power;
        $VedName = $req->VedName;
        $IolCompName = $req->IolCompName;
        $FromDate = $req->FromDate;
        $ToDate = $req->ToDate;
        $sql = "SELECT * From tbl_lense_ledger WHERE Status=1 AND OtId!=0";
        if($Cname != 'all'){
            $sql.= " AND Cname = '$Cname'";
        }
        if($Model != 'all'){
            $sql.= " AND Model = '$Model'";
        }
        if($Power != 'all'){
            $sql.= " AND Power = '$Power'";
        }
        if($VedName != 'all'){
            $sql.= " AND VedName = '$VedName'";
        }
        if($IolCompName != 'all'){
            $sql.= " AND IolCompName = '$IolCompName'";
        }
        if($FromDate != ''){
            $sql.= " AND StockDate >= '$FromDate'";
        }
        if($ToDate != ''){
            $sql.= " AND StockDate <= '$ToDate'";
        }
        $sql.=" ORDER BY StockDate DESC";
        $result = DB::select($sql);
        // $sql2 = "SELECT SUM(balqty) as balqty FROM ($sql) as a";
        // $result2 = DB::select($sql2);
        // $totqty = $result2[0]->balqty;
        return view('/lense/view-stock-lense-report3',['data'=>$result]);
    }
}
