<?php

namespace App\Models;

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


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

        return DB::select("SELECT  `AccCode` as AccCode, `TenantCode` as AccSubCode, `Name` as AccName FROM `tenants` where `Code`='$code'");
    }
    public static function selectTenantsByFloor($code, $from_floor, $to_floor)
    {
        return DB::select("
            SELECT `AccCode` as AccCode, `Address` as Address, `TenantCode` as AccSubCode, `Name` as AccName, `Contact`, `Email`, `Floor`,`rent_amount`, `Rooms`, `Space`
            FROM `tenants`
            WHERE `Code`='$code'
            AND `Floor` BETWEEN '$from_floor' AND '$to_floor'
        ");
    }

    public static function selectTenants1($code,  $AccSubCode )
    {

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

    public static function selectglCashBook($code)
    {

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

    public static function selectglCashBookBYCode($code,$AccCode)
    {

        return DB::select("SELECT  * FROM cashbook  where cashbook.Code='$code' and AccCode='$AccCode'");
    }
    public static function selectCustomer($code)
    {

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

        return DB::select("SELECT  `AccCode` as AccCode, `CustomeCode` as AccSubCode, `Name` as AccName FROM `customers` where `Code`='$code' and CustomeCode='$AccSubCode'");
    }

    public static function selectSupplier($code)
    {

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

    public static function selectSupplier1($code, $AccSubCode)
    {

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

    public static function selectTrans1($code)
    {

        return DB::select("SELECT  * FROM `transactions` where `Code`='$code' and ID='0'");
    }

    public static function selectTransTax($code, $AccCode,$AccSubCode,$curr, $from , $to, $Type,$pfrom,$pto,$tax)
    {
        if($tax=="All")
        {
        return DB::select("SELECT `ID`, `Code`, `EntryDate`, `Narration`, `Refference`, `Accode`, `Source`,`AccSubCode`, `ContraCode`, `ContraSubCode`, (CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))as DR, (CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)) as CR, `Addedby`, `Source`, `Qnty`, (CONVERT(Price, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)) as Price, `Tax`, `TaxID`, `Discount`, `Total`, `Batch`,`Source`,`TType`, `Currency`, `Rate`, '0000' as CName FROM `transactions` WHERE transactions.TaxID !='0' and Code='$code'  and (transactions.EntryDate between '$from' and '$to') and (transactions.EntryDate between '$pfrom' and '$pto')");
        }
        else
        {
        return DB::select("SELECT `ID`, `Code`, `EntryDate`, `Narration`, `Refference`, `Source`,`Accode`,`AccSubCode`, `ContraCode`, `ContraSubCode`, (CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))as DR, (CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)) as CR, `Addedby`, `Source`, `Qnty`,  (CONVERT(Price, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)) as Price, `Tax`, `TaxID`, `Discount`, `Total`, `Batch`,`Source`,`TType`, `Currency`, `Rate`, '0000' as CName FROM `transactions` WHERE transactions.TaxID='$tax' and Code='$code'  and (transactions.EntryDate between '$from' and '$to') and (transactions.EntryDate between '$pfrom' and '$pto')");
        }
    }

public static function selectTrans2($code, $AccCode, $AccSubCode, $curr, $from, $to, $Type, $ContraCode)
{
    if ($Type == "Tenant") {
        $additionalCondition = ($ContraCode == "1000")
            ? "transactions.ContraCode = '$ContraCode' OR transactions.ContraCode = '6000'"
            : "transactions.ContraCode = '$ContraCode' OR transactions.ContraCode = '6100'";

        return DB::select("
            SELECT
                MIN(EntryDate) AS EntryDate,
                MIN(MNarration) AS MNarration,
                Batch,
                SUM((CONVERT(DR, DECIMAL(10,2)) * (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                    FROM rates
                    WHERE rates.EntryDate <= transactions.EntryDate
                    AND rates.Code = transactions.Code
                    AND rates.Currency = '$curr'
                    ORDER BY rates.ID DESC LIMIT 1))
                ) AS DR,
                SUM((CONVERT(CR, DECIMAL(10,2)) * (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                    FROM rates
                    WHERE rates.EntryDate <= transactions.EntryDate
                    AND rates.Code = transactions.Code
                    AND rates.Currency = '$curr'
                    ORDER BY rates.ID DESC LIMIT 1))
                ) AS CR,
                MIN((SELECT Name
                     FROM tenants
                     WHERE tenants.TenantCode = transactions.AccSubCode
                     AND Code = transactions.Code)
                ) AS CName
            FROM `transactions`
            WHERE transactions.Source = 'Tenant'
            AND transactions.Accode = '$AccCode'
            AND transactions.AccSubCode = '$AccSubCode'
            AND Code = '$code'
            AND ($additionalCondition)
            AND (transactions.EntryDate BETWEEN '$from' AND '$to')
            GROUP BY Batch

            UNION

            SELECT
                MIN(EntryDate) AS EntryDate,
                MIN(Narration) AS MNarration,
                Refference AS Batch,
                SUM((CONVERT(CR, DECIMAL(10,2)) * (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                    FROM rates
                    WHERE rates.EntryDate <= transactions.EntryDate
                    AND rates.Code = transactions.Code
                    AND rates.Currency = '$curr'
                    ORDER BY rates.ID DESC LIMIT 1))
                ) AS DR,
                SUM((CONVERT(DR, DECIMAL(10,2)) * (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                    FROM rates
                    WHERE rates.EntryDate <= transactions.EntryDate
                    AND rates.Code = transactions.Code
                    AND rates.Currency = '$curr'
                    ORDER BY rates.ID DESC LIMIT 1))
                ) AS CR,
                MIN((SELECT Name
                     FROM tenants
                     WHERE tenants.TenantCode = transactions.ContraSubCode
                     AND Code = transactions.Code)
                ) AS CName
            FROM `transactions`
            WHERE transactions.Source = 'CashBook'
            AND transactions.ContraCode = '$AccCode'
            AND transactions.ContraSubCode = '$AccSubCode'
            AND Code = '$code'
            AND ($additionalCondition)
            AND (transactions.EntryDate BETWEEN '$from' AND '$to')
            GROUP BY Refference
        ");
    }
}

    public static function selectTrans($code, $AccCode, $AccSubCode, $curr, $from, $to, $Type)
{
    if ($Type == "Tenant") {
        return DB::select("
            SELECT
                MIN(EntryDate) AS EntryDate,
                MIN(MNarration) AS MNarration,
                Batch,
                SUM((CONVERT(DR, DECIMAL(10,2)) * (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                    FROM rates
                    WHERE rates.EntryDate <= transactions.EntryDate
                    AND rates.Code = transactions.Code
                    AND rates.Currency = '$curr'
                    ORDER BY rates.ID DESC LIMIT 1))
                ) AS DR,
                SUM((CONVERT(CR, DECIMAL(10,2)) * (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                    FROM rates
                    WHERE rates.EntryDate <= transactions.EntryDate
                    AND rates.Code = transactions.Code
                    AND rates.Currency = '$curr'
                    ORDER BY rates.ID DESC LIMIT 1))
                ) AS CR,
                MIN((SELECT Name
                     FROM tenants
                     WHERE tenants.TenantCode = transactions.AccSubCode
                     AND Code = transactions.Code)
                ) AS CName
            FROM `transactions`
            WHERE transactions.Source = 'Tenant'
            AND transactions.Accode = '$AccCode'
            AND transactions.AccSubCode = '$AccSubCode'
            AND transactions.Code = '$code'
            AND transactions.ContraCode = 1100
            AND (transactions.EntryDate BETWEEN '$from' AND '$to')
            GROUP BY Batch

            UNION

            SELECT
                MIN(EntryDate) AS EntryDate,
                MIN(Narration) AS MNarration,
                Refference AS Batch,
                SUM((CONVERT(CR, DECIMAL(10,2)) * (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                    FROM rates
                    WHERE rates.EntryDate <= transactions.EntryDate
                    AND rates.Code = transactions.Code
                    AND rates.Currency = '$curr'
                    ORDER BY rates.ID DESC LIMIT 1))
                ) AS DR,
                SUM((CONVERT(DR, DECIMAL(10,2)) * (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                    FROM rates
                    WHERE rates.EntryDate <= transactions.EntryDate
                    AND rates.Code = transactions.Code
                    AND rates.Currency = '$curr'
                    ORDER BY rates.ID DESC LIMIT 1))
                ) AS CR,
                MIN((SELECT Name
                     FROM tenants
                     WHERE tenants.TenantCode = transactions.ContraSubCode
                     AND Code = transactions.Code)
                ) AS CName
            FROM `transactions`
            WHERE transactions.Source = 'CashBook'
            AND transactions.ContraCode = '$AccCode'
            AND transactions.ContraSubCode = '$AccSubCode'
            AND transactions.Code = '$code'
            AND transactions.ContraCode = 1100
            AND (transactions.EntryDate BETWEEN '$from' AND '$to')
            GROUP BY Refference
        ");
    }

  elseif ($Type == "Supplier") {
    return DB::select("
        SELECT
            MIN(EntryDate) AS EntryDate,
            MIN(MNarration) AS MNarration,
            Batch,
            SUM((CONVERT(DR, DECIMAL(10,2)) *
                (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                 FROM rates
                 WHERE rates.EntryDate <= transactions.EntryDate
                 AND rates.Code = transactions.Code
                 AND rates.Currency = '$curr'
                 ORDER BY rates.ID DESC LIMIT 1)
            )) AS DR,
            SUM((CONVERT(CR, DECIMAL(10,2)) *
                (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                 FROM rates
                 WHERE rates.EntryDate <= transactions.EntryDate
                 AND rates.Code = transactions.Code
                 AND rates.Currency = '$curr'
                 ORDER BY rates.ID DESC LIMIT 1)
            )) AS CR,
            MIN((SELECT Name
                 FROM suppliers
                 WHERE suppliers.SupplierCode = transactions.AccSubCode
                 AND Code = transactions.Code)) AS CName
        FROM `transactions`
        WHERE transactions.Source = 'Supplier'
        AND transactions.Accode = '$AccCode'
        AND transactions.AccSubCode = '$AccSubCode'
        AND Code = '$code'
        AND (transactions.EntryDate BETWEEN '$from' AND '$to')
        GROUP BY Batch
        UNION
        SELECT
            MIN(EntryDate) AS EntryDate,
            MIN(Narration) AS MNarration,
            Refference AS Batch,
            SUM((CONVERT(CR, DECIMAL(10,2)) *
                (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                 FROM rates
                 WHERE rates.EntryDate <= transactions.EntryDate
                 AND rates.Code = transactions.Code
                 AND rates.Currency = '$curr'
                 ORDER BY rates.ID DESC LIMIT 1)
            )) AS DR,
            SUM((CONVERT(DR, DECIMAL(10,2)) *
                (SELECT CONVERT(Rate, DECIMAL(10,2)) AS Rate
                 FROM rates
                 WHERE rates.EntryDate <= transactions.EntryDate
                 AND rates.Code = transactions.Code
                 AND rates.Currency = '$curr'
                 ORDER BY rates.ID DESC LIMIT 1)
            )) AS CR,
            MIN((SELECT Name
                 FROM suppliers
                 WHERE suppliers.SupplierCode = transactions.ContraSubCode
                 AND Code = transactions.Code)) AS CName
        FROM `transactions`
        WHERE transactions.Source = 'CashBook'
        AND transactions.ContraCode = '$AccCode'
        AND transactions.ContraSubCode = '$AccSubCode'
        AND Code = '$code'
        AND (transactions.EntryDate BETWEEN '$from' AND '$to')
        GROUP BY Refference
    ");
}

        elseif($Type=="Customer")
        {

            return DB::select("SELECT  MIN(EntryDate) AS EntryDate, MIN(MNarration) AS MNarration, Batch,sum( (CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)))as DR, sum((CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))) as CR, MIN( (select Name from customers where customers.CustomeCode=transactions.AccSubCode and Code=transactions.Code)) as CName FROM `transactions` WHERE transactions.Source='Customer' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate between '$from' and '$to')  group by Batch
             UNION SELECT  MIN(EntryDate) AS EntryDate, MIN(Narration) AS MNarration, Refference as Batch,sum( (CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)))as DR, sum((CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))) as CR, MIN( (select Name from customers where customers.CustomeCode=transactions.ContraSubCode and Code=transactions.Code)) as CName FROM `transactions` WHERE transactions.Source='CashBook' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate between '$from' and '$to')  group by Refference");

        }


        elseif($Type=="CashBook")
        {
            return DB::select("SELECT `ID`, `Code`, `EntryDate`, `Narration`, `Refference`, `Accode`, `AccSubCode`, `ContraCode`, `ContraSubCode`, (CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))as DR, (CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)) as CR, `Addedby`, `Source`, `Qnty`, `Price`, `Tax`, `TaxID`, `Discount`, `Total`, `Batch`, `Currency`, `Rate`, '0000' as CName FROM `transactions` WHERE transactions.Source='CashBook' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate between '$from' and '$to')");

        }

        elseif($Type=="GL")        {
            return DB::select("SELECT sum(CR-DR) as Total from (SELECT `ID`, `Code`, `EntryDate`, `Narration`, `Refference`, `Accode`, `AccSubCode`, `ContraCode`, `ContraSubCode`, (CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))as DR, (CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)) as CR, `Addedby`, `Source`, `Qnty`, `Price`, `Tax`, `TaxID`, `Discount`, `Total`, `Batch`, `Currency`, `Rate`, '0000' as CName FROM `transactions` WHERE transactions.TType='Invoice' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate between '$from' and '$to') ) as total");

        }

    }


    public static function selectTransOpeningBalance($code, $AccCode,$AccSubCode,$curr, $from , $to, $Type)
    {
        if($Type=="Tenant")
        {
            return DB::select("SELECT MIN(total) as total
FROM (SELECT sum((CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)))-sum((CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))) as total FROM `transactions` WHERE transactions.Source='Tenant' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate<'$from')  UNION
SELECT sum((CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)))-sum((CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))) as total FROM `transactions` WHERE transactions.Source='CashBook' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate<'$from') ) as total");

        }
        elseif($Type=="Customer")
        {

            return DB::select("SELECT MIN(total) as total
FROM (SELECT sum((CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)))-sum((CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))) as total FROM `transactions` WHERE transactions.Source='Customer' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate<'$from') UNION
SELECT sum((CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)))-sum((CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))) as total FROM `transactions` WHERE transactions.Source='CashBook' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate<'$from')  ) as total");

        }
        elseif($Type=="Supplier")
        {
            return DB::select("SELECT MIN(total) as total
FROM (SELECT sum((CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)))-sum((CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))) as total FROM `transactions` WHERE transactions.Source='Supplier' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate<'$from') UNION
SELECT sum((CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)))-sum((CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))) as total FROM `transactions` WHERE transactions.Source='CashBook' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate<'$from') ) as total");


        }

        elseif($Type=="CashBook")
        {
            return DB::select("SELECT MIN(total) as total
FROM (SELECT sum((CONVERT(DR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1)))-sum((CONVERT(CR, DECIMAL(10,2))*(select CONVERT(Rate, DECIMAL(10,2)) as Rate  from rates where rates.EntryDate<=transactions.EntryDate and rates.Code=transactions.Code and rates.Currency='$curr' order by rates.ID desc LIMIT 1))) as total FROM `transactions` WHERE transactions.Source='CashBook' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code'  and (transactions.EntryDate<'$from') ) as total");

        }

    }

    public static function selectglByType($code , $type)
    {

        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, mainaccounts.AccName AS Mname, subaccounts.SubName AS Sname FROM mainaccounts LEFT JOIN subaccounts ON mainaccounts.AccCode = subaccounts.AccCode and mainaccounts.Code=subaccounts.Code where mainaccounts.Code='$code' and  mainaccounts.Acctype='$type' ");
    }
    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, mainaccounts.AccName AS Mname, subaccounts.SubName AS Sname FROM mainaccounts LEFT JOIN subaccounts ON mainaccounts.AccCode = subaccounts.AccCode and mainaccounts.Code=subaccounts.Code where mainaccounts.Code='$code'");
    }

    public static function selectglByCode($code,$AccCode)
    {

        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' and mainaccounts.AccCode='$AccCode'");
    }

    public static function selectglByCode1($code,$AccCode,$AccSubCode)
    {

        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'   and mainaccounts.AccCode='$AccCode'  and subaccounts.SubCode='$AccSubCode'");
    }

    public static function selectAccountsTypes($code)
    {

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

        return DB::select("SELECT * FROM `accountstypes` where `Code`='$code' and ID='$id'");
    }

    public static function selectHomeTransTax($code, $AccCode,$AccSubCode,$curr, $from , $to, $Type,$pfrom,$pto,$tax)
    {


        if($tax=="All")
        {
            return DB::select("SELECT *, '0000' as CName FROM `transactions` WHERE transactions.TaxID !='0'  and Code='$code' and (transactions.EntryDate between '$from' and '$to') and (transactions.EntryDate between '$pfrom' and '$pto')");

        }
        else
        {
            return DB::select("SELECT *, '0000' as CName FROM `transactions` WHERE transactions.TaxID='$tax'  and Code='$code' and (transactions.EntryDate between '$from' and '$to') and (transactions.EntryDate between '$pfrom' and '$pto')");
        }
    }
    public static function selectHomeTrans2($code, $AccCode, $AccSubCode, $curr, $from, $to, $Type, $ContraCode)
{
    if ($Type == "Tenant") {
        return DB::select("
            SELECT
                MIN(EntryDate) AS EntryDate,
                MIN(MNarration) AS MNarration,
                Batch,
                SUM(DR) AS DR,
                SUM(CR) AS CR,
                MIN(
                    (SELECT Name
                     FROM tenants
                     WHERE tenants.TenantCode = transactions.AccSubCode
                     AND Code = transactions.Code)
                ) AS CName
            FROM transactions
            WHERE
                transactions.Source = 'Tenant'
                AND transactions.Accode = '$AccCode'
                AND transactions.AccSubCode = '$AccSubCode'
                AND Code = '$code'
                AND transactions.ContraCode = '$ContraCode'
                AND (transactions.EntryDate BETWEEN '$from' AND '$to')
            GROUP BY Batch
            UNION
            SELECT
                MIN(EntryDate) AS EntryDate,
                MIN(Narration) AS MNarration,
                Refference AS Batch,
                SUM(CR) AS DR,
                SUM(DR) AS CR,
                MIN(
                    (SELECT Name
                     FROM tenants
                     WHERE tenants.TenantCode = transactions.ContraSubCode
                     AND Code = transactions.Code)
                ) AS CName
            FROM transactions
            WHERE
                transactions.TType = 'CashBook'
                AND transactions.Batch = 'CashBook'
                AND transactions.Source = 'CashBook'
                AND transactions.ContraCode = '$AccCode'
                AND transactions.ContraSubCode = '$AccSubCode'
                AND Code = '$code'
                AND transactions.ContraCode = '$ContraCode'
                AND (transactions.EntryDate BETWEEN '$from' AND '$to')
            GROUP BY Refference
        ");
    }
}

    public static function selectHomeTrans($code, $AccCode,$AccSubCode,$curr, $from , $to, $Type)
    {

        if($Type=="Tenant")
        {
            return DB::select("SELECT  MIN(EntryDate) AS EntryDate, MIN(MNarration) AS MNarration, Batch,sum(DR) as DR, sum(CR) as CR , MIN((select Name from tenants where tenants.TenantCode=transactions.AccSubCode and Code=transactions.Code)) as CName FROM `transactions` WHERE transactions.Source='Tenant' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate between '$from' and '$to') group by Batch
            UNION SELECT  MIN(EntryDate) AS EntryDate, MIN(Narration) AS MNarration, Refference as Batch,sum(CR) as DR, sum(DR) as CR , MIN((select Name from tenants where tenants.TenantCode=transactions.ContraSubCode and Code=transactions.Code)) as CName FROM `transactions` WHERE transactions.TType='CashBook' and transactions.Batch='CashBook' and transactions.Source='CashBook' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate between '$from' and '$to') group by Refference");

        }
        elseif($Type=="Customer")
        {
            return DB::select("SELECT  MIN(EntryDate) AS EntryDate, MIN(MNarration) AS MNarration, Batch,sum(DR) as DR, sum(CR) as CR ,  MIN((select Name from customers where customers.CustomeCode=transactions.AccSubCode and Code=transactions.Code)) as CName FROM `transactions` WHERE transactions.Source='Customer' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate between '$from' and '$to') group by Batch
            UNION SELECT  MIN(EntryDate) AS EntryDate, MIN(Narration) AS MNarration, Refference as Batch,sum(CR) as DR, sum(DR) as CR ,  MIN((select Name from customers where customers.CustomeCode=transactions.ContraSubCode and Code=transactions.Code)) as CName FROM `transactions` WHERE transactions.TType='CashBook' and transactions.Batch='CashBook' and transactions.Source='CashBook' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate between '$from' and '$to') group by Refference");

        }
        elseif($Type=="Supplier")
        {
            return DB::select("SELECT  MIN(EntryDate) AS EntryDate, MIN(MNarration) AS MNarration, Batch,sum(DR) as DR, sum(CR) as CR , MIN( (select Name from suppliers where suppliers.SupplierCode=transactions.AccSubCode and Code=transactions.Code)) as CName FROM `transactions` WHERE transactions.Source='Supplier' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate between '$from' and '$to') group by Batch UNION
SELECT  MIN(EntryDate) AS EntryDate, MIN(Narration) AS MNarration, Refference as Batch,sum(CR) as DR, sum(DR) as CR , MIN( (select Name from suppliers where suppliers.SupplierCode=transactions.ContraSubCode and Code=transactions.Code)) as CName FROM `transactions` WHERE transactions.Source='CashBook' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate between '$from' and '$to') group by Refference");


        }
        elseif($Type=="CashBook")
        {
            return DB::select("SELECT *, '0000' as CName FROM `transactions` WHERE transactions.Source='CashBook' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate between '$from' and '$to')");


        }

        else  if($Type=="GL")
        {
            return DB::select("SELECT SUM(CR-DR) as Total from (SELECT *, '0000' as CName FROM `transactions` WHERE transactions.TType='Invoice' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate between '$from' and '$to')) as total");


        }




    }
    public static function selectHomeTransOpening2($code, $AccCode, $AccSubCode, $curr, $from, $to, $Type, $contraCode)
{
    if($Type == "Tenant")
    {
        return DB::select("
            SELECT MIN(total) as total
            FROM (
                SELECT  sum(CR) - sum(DR) as total
                FROM `transactions`
                WHERE transactions.Source='Tenant'
                AND transactions.Accode='$AccCode'
                AND transactions.AccSubCode='$AccSubCode'
                AND Code='$code'
                AND transactions.EntryDate<'$from'
                AND transactions.ContraCode='$contraCode'
                UNION
                SELECT  sum(CR) - sum(DR) as total
                FROM `transactions`
                WHERE transactions.Source='CashBook'
                AND transactions.ContraCode='$AccCode'
                AND transactions.ContraSubCode='$AccSubCode'
                AND Code='$code'
                AND transactions.EntryDate<'$from'
                AND transactions.ContraCode='$contraCode'
            ) as total
        ");
    }
}



    public static function selectHomeTransOpening($code, $AccCode,$AccSubCode,$curr, $from , $to, $Type)
    {

        if($Type=="Tenant")
        {
            return DB::select("SELECT MIN(total) as total
FROM (SELECT  sum(CR) - sum(DR) as total FROM `transactions` WHERE transactions.Source='Tenant' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate<'$from') UNION
SELECT  sum(CR) - sum(DR) as total FROM `transactions` WHERE transactions.Source='CashBook' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate<'$from')) as total ");

        }
        elseif($Type=="Customer")
        {
            return DB::select("SELECT MIN(total) as total
FROM (SELECT  sum(CR) - sum(DR) as total FROM `transactions` WHERE transactions.Source='Customer' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate<'$from') UNION
SELECT  sum(CR) - sum(DR) as total FROM `transactions` WHERE transactions.Source='CashBook' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate<'$from')) as total");

        }
        elseif($Type=="Supplier")
        {
            return DB::select("SELECT MIN(total) as total
FROM (SELECT  sum(DR) - sum(CR) as total FROM `transactions` WHERE transactions.Source='Supplier' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate<'$from') UNION
SELECT  sum(CR) - sum(DR) as total FROM `transactions` WHERE transactions.Source='CashBook' and transactions.ContraCode='$AccCode' and transactions.ContraSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate<'$from')) as total");


        }
        elseif($Type=="CashBook")
        {
            return DB::select("SELECT MIN(total) as total
FROM (SELECT  sum(CR) - sum(DR) as total FROM `transactions` WHERE transactions.Source='CashBook' and transactions.Accode='$AccCode' and transactions.AccSubCode='$AccSubCode' and Code='$code' and (transactions.EntryDate<'$from')) as total");


        }

    }
    public static function selectcurrencyz()
    {
        return DB::select("SELECT * FROM currencys");

    }

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

    }
    public static function selectcurrencyName($code,$curr)
    {
        return DB::select("SELECT * FROM currencys where Code='$code' and ID='$curr' limit 1");

    }

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

    }
    public static function selectRate($code,$currency,$AccCode,$AccSubCode, $from, $to)
    {
        return DB::select("SELECT * FROM `rates` WHERE Code='$code' and Currency='$currency'");

    }

    public static function selectTax($code)
    {

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

    public static function selectTaxByID($code,$id)
    {

        return DB::select("SELECT * FROM `tax` where `Code`='$code' and ID='$id'");
    }

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


        }
        elseif($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='$AccSubCode' LIMIT 1 ");

        }
        elseif($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='$AccSubCode' LIMIT 1 ");

        }

   }

   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' ");
   }

   // Define the relationship with the Transaction model


}
