<?php

namespace App\Models;

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

class modelTransactionslistions 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 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 selectSupplier($code)
    {

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

    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`, `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`, `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`, `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`, `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 selectTrans($code, $AccCode,$AccSubCode,$curr, $from , $to, $Type)
    {
        if($Type=="Tenant")
        {
            return DB::select("SELECT  EntryDate AS EntryDate, Narration AS MNarration, Batch,(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, (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')
             UNION SELECT  EntryDate AS EntryDate, Narration AS MNarration, Refference as Batch, (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, (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, (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 (transactions.EntryDate between '$from' and '$to')
             ");

        }
        else   if($Type=="Customer")
        {

            return DB::select("SELECT EntryDate AS EntryDate, Narration AS MNarration, Batch,(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,  (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')
             UNION SELECT  EntryDate AS EntryDate, Narration AS MNarration, Refference as Batch,(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, (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,  (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')  ");

        }
        else  if($Type=="Supplier")
        {
            return DB::select("SELECT   EntryDate AS EntryDate, Narration AS MNarration, Batch,(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, (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')   UNION
            SELECT   EntryDate AS EntryDate, Narration AS MNarration, Refference as Batch,(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, (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,  (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')  ");


        }

        else  if($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')");

        }

        else  if($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");

        }
        else   if($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");

        }
        else  if($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");


        }

        else  if($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 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 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 selectHomeTrans($code, $AccCode,$AccSubCode,$curr, $from , $to, $Type)
    {

        if($Type=="Tenant")
        {
            return DB::select("SELECT  EntryDate AS EntryDate, Narration AS MNarration, Batch, DR,  CR , (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')
            UNION SELECT  EntryDate AS EntryDate, Narration AS MNarration, Refference as Batch,CR as DR, DR as CR ,(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') ");

        }
        else   if($Type=="Customer")
        {
            return DB::select("SELECT  EntryDate AS EntryDate, Narration AS MNarration, Batch,DR as DR, CR as CR ,  (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')
            UNION SELECT  EntryDate AS EntryDate, Narration AS MNarration, Refference as Batch,CR as DR, DR as CR ,  (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') ");

        }
        else  if($Type=="Supplier")
        {
            return DB::select("SELECT  EntryDate AS EntryDate, Narration AS MNarration, Batch,DR as DR, CR as CR ,  (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')  UNION
SELECT  EntryDate AS EntryDate, Narration AS MNarration, Refference as Batch,CR as DR, DR as CR , (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') ");


        }
        else  if($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 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 ");

        }
        else   if($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");

        }
        else  if($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");


        }
        else  if($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 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'");
    }



}
