<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class modelInvoice extends Model
{
    public static function selectTenants($code)
    {

        return DB::select("SELECT  `AccCode`, `TenantCode` as Code, `Name` FROM `tenants` where `Code`='$code'");
    }

    public static function selectTax($code)
    {

        return DB::select("SELECT * FROM `tax` where `Code`='$code'");
    }
    public static function Genbatch($code,$user)
    {

        return DB::insert("INSERT INTO `invoicegenaration`(`Code`, `Status`, `AddedBy`) Values('$code','0','$user');");
    }
    public static function selectbatch($code,$user)
    {

        return DB::select(" SELECT count(*) as ID FROM `invoicegenaration` where `Code`='$code' and `Status`='1'");
    }



    public static function cGenbatch($code,$user)
    {

        return DB::insert("INSERT INTO `customerinvoicegenaration`(`Code`, `Status`, `AddedBy`) Values('$code','0','$user');");
    }
    public static function cselectbatch($code,$user)
    {

        return DB::select(" SELECT count(*) as ID FROM `customerinvoicegenaration` where `Code`='$code' and `Status`='1'");
    }
    public static function selectgl($code)
    {

        return DB::select("SELECT  IF(subaccounts.SubCode IS NOT NULL, CONCAT(mainaccounts.AccCode, '/', subaccounts.SubCode), mainaccounts.AccCode) AS AccCode, IF(subaccounts.SubName IS NOT NULL, CONCAT(mainaccounts.AccName, '/', subaccounts.SubName), mainaccounts.AccName) AS AccName FROM mainaccounts LEFT JOIN subaccounts ON mainaccounts.AccCode = subaccounts.AccCode and mainaccounts.Code=subaccounts.Code where mainaccounts.Code='$code'");
    }

    public static function selectcurrency($code)
    {
        return DB::select("SELECT * FROM currencys where Code='$code'");

    }
    public static function selectHomeCurrency($code)
    {
        return DB::select("SELECT * FROM homecurrency where Code='$code'");

    }
    public static function selectSpace($code,$AccSubCode)
    {
        return DB::select("select (select propertydetails.Space from propertydetails where propertydetails.Code='$code' LIMIT 1 ) as PropertySpace, (select tenants.Space from tenants where tenants.Code='$code' and tenants.TenantCode='$AccSubCode'  LIMIT 1 ) as TenantSpace");

    }
    public static function selectRate($code,$currency)
    {
        return DB::select("SELECT * FROM `rates` WHERE Code='$code' and Currency='$currency'  order by EntryDate desc limit 1");

    }

    public static function selectJournalTransactions($code,$user,$src)
    {

        return DB::select("SELECT  *, (select Symbol from currencys where currencys.ID=transactionsjob.Currency) as Symbol FROM `transactionsjob` where `Code`='$code' and Addedby='$user' and Source='$src' ");
    }

    public static function selectInvoiceTransactions($code,$type,$invoice)
    {

        return DB::select("SELECT  *, (select Symbol from currencys where currencys.ID=transactions.Currency) as Symbol FROM `transactions` where `Code`='$code' and Source='$type' and Batch='$invoice' ");
    }
    public static function selectCustomers($code)
    {

        return DB::select("SELECT  `AccCode`, `CustomeCode` as Code, `Name` FROM `customers` where `Code`='$code'");
    }
    public static function selectSuppliers($code)
    {

        return DB::select("SELECT  `AccCode`, `SupplierCode` as Code, `Name` FROM `suppliers` where `Code`='$code'");
    }

    public static function saveTransaction($Code,$Entrydate,$AccCode,$AccSubCode,$ContraCode,$ContraSubCode,$narr,$reff, $DR,$CR,$user,$src,$tax,$taxid,$disc,$qty,$price,$total,$batch,$curr,$Rate,$mdes,$ttype)
    {
        return DB::insert("INSERT INTO `transactionsjob`(`Code`, `EntryDate`, `Narration`, `Refference`, `Accode`, `AccSubCode`, `ContraCode`, `ContraSubCode`, `DR`, `CR`, `Addedby`, `Source`, `Qnty`, `Price`, `Tax`, `TaxID`, `Discount`, `Total`, `Batch`,Currency,Rate,MNarration,TType)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?)", [$Code, $Entrydate, $narr, $reff, $AccCode, $AccSubCode, $ContraCode, $ContraSubCode, $DR, $CR, $user, $src,$qty,$price,$tax,$taxid,$disc,$total,$batch,$curr,$Rate,$mdes,$ttype] );
    }

    public static function updateTransactionMdes($Code,$batch,$mdes,$type)
    {
        return DB::insert("UPDATE  `transactionsjob` SET MNarration='$mdes' where `Code`='$Code' and  `Batch`='$batch' and `Source`='$type'" );
    }



    public static function processTransaction($type,$inv,$Code)
    {
        return DB::insert("INSERT INTO transactions (Code, EntryDate, Narration, Refference, Accode, AccSubCode, ContraCode, ContraSubCode, DR, CR, Addedby, Source, Qnty, Price, Tax, TaxID, Discount, Total, Batch,Currency,Rate,MNarration,TType)
SELECT Code, EntryDate, Narration, Refference, Accode, AccSubCode, ContraCode, ContraSubCode, DR, CR, Addedby, Source, Qnty, Price, Tax, TaxID, Discount, Total, Batch,Currency,Rate,MNarration,TType
FROM transactionsjob where Code='$Code' and Batch='$inv' and Source='$type'" );
    }

    public static function updateTransaction($id, $Code, $Entrydate, $AccCode, $AccSubCode, $ContraCode, $ContraSubCode, $narr, $reff, $DR, $CR, $user, $src, $tax, $taxid, $disc, $qty, $price, $total, $batch,$curr,$Rate,$mdes,$ttype)
{
    return DB::update("UPDATE `transactionsjob`
        SET `Code` = ?, `EntryDate` = ?, `Narration` = ?, `Refference` = ?, `Accode` = ?, `AccSubCode` = ?, `ContraCode` = ?, `ContraSubCode` = ?, `DR` = ?, `CR` = ?, `Addedby` = ?, `Source` = ?, `Qnty` = ?, `Price` = ?, `Tax` = ?, `TaxID` = ?, `Discount` = ?, `Total` = ?, `Batch` = ?,Currency=?,Rate=?,MNarration=?,TType=?
        WHERE `ID` = ?",
        [$Code, $Entrydate, $narr, $reff, $AccCode, $AccSubCode, $ContraCode, $ContraSubCode, $DR, $CR, $user, $src, $qty, $price, $tax, $taxid, $disc, $total, $batch,$curr,$Rate,$mdes,$ttype, $id]
    );
}

    public static function ValidateTransactions($code,$user,$src,$inv)
    {


        return DB::select("SELECT  * FROM `transactionsjob` where `Code`='$code' and Addedby='$user' and Source='$src' and Batch='$inv' ");
    }

    public static function deleteTransaction($ID)
    {
        return DB::insert("DELETE from `transactionsjob` where `ID`='$ID'");
    }

    public static function UpdateGenInvoice($type,$code)
    {
        if($type=='Tenant')
        {
            return DB::insert("UPDATE  `invoicegenaration` set `Status`='1' where `Code`='$code'");
        }
        else if($type=='Customer')
        {
            return DB::insert("UPDATE  `customerinvoicegenaration` set `Status`='1' where `Code`='$code'");
        }

    }
    public static function deleteTransactionJobs($code,$type,$invoice)
    {
        return DB::insert("DELETE FROM `transactionsjob` where `Code`='$code' and Source='$type' and Batch='$invoice' ");
    }
    public static function selectInvoiceProperty($code,$user)
    {
            return DB::select("SELECT  *, (select Phone from usersdetails where usersdetails.UserCode=propertydetails.Ownerreg) as Phone,(select Email from usersdetails where usersdetails.UserCode=propertydetails.Ownerreg) as Email FROM `propertydetails` where `Code`='$code' and `Ownerreg`='$user' ");
    }


    public static function selectInvoiceCustomer($code,$type,$invoice)
    {
        if($type=="Tenant")
        {
            return DB::select("SELECT  * FROM `tenants` where `Code`='$code' and TenantCode=(SELECT  AccSubCode FROM `transactions` where `Code`='$code' and Source='$type' and Batch='$invoice' LIMIT 1) ");


        }
        else if($type=="Customer")
        {
            return DB::select("SELECT  `ID`, `Code`, `AccCode`, `CustomeCode`, `Name`, `ContactPerson`, `Contact1` as Contact, `Contact2`, `Email`, `Address`, `EntryDate` FROM `customers` where `Code`='$code' and CustomeCode=(SELECT  AccSubCode FROM `transactions` where `Code`='$code' and Source='$type' and Batch='$invoice' LIMIT 1) ");

        }
        else if($type=="Supplier")
        {
            return DB::select("SELECT   `ID`, `Code`, `AccCode`, `SupplierCode`, `Name`, `ContactPerson`, `Contact1` as Contact, `Contact2`, `Address`, `Email`, `EntryDate` FROM `suppliers` where `Code`='$code' and SupplierCode=(SELECT  AccSubCode FROM `transactions` where `Code`='$code' and Source='$type' and Batch='$invoice' LIMIT 1) ");

        }


   }

   public static function selectBanks($code)
   {

       return DB::select("SELECT * FROM `banks` where `Code`='$code'");
   }


}
