<?php

namespace App\Models;

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

class modelTenants extends Model
{
    public static function selecttenants($code)
    {
        return DB::table('tenants')
                 ->where('Code', $code)
                 ->orderBy('Name', 'asc')
                 ->get();
    }


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

        if($type=="Tenant")
        {
            return DB::select("SELECT ID, TenantCode as AccCode, Name as AccName FROM `tenants` where `Code`='$code' order by AccName asc");
        }
        else if($type=="Customer")
        {
            return DB::select("SELECT ID, CustomeCode as AccCode, Name as AccName FROM `customers` where `Code`='$code' order by AccName asc");
        }
        else if($type=="Supplier")
        {
            return DB::select("SELECT ID, SupplierCode as AccCode, Name as AccName FROM `suppliers` where `Code`='$code' order by AccName asc");
        }

    }
    public static function selectAffiliatesByID($code, $type,$from,$to)
    {

        if($type=="Tenant")
        {
            return DB::select("SELECT * FROM `tenants` where `Code`='$code' and ID>='$from' and ID<='$to'");
        }
        else if($type=="Customer")
        {
            return DB::select("SELECT `ID`, `Code`, `AccCode`, `CustomeCode`  as AccCodes, `Name`, `ContactPerson`, `Contact1`, `Contact2`, `Email`, `Address`, `EntryDate` FROM `customers` where `Code`='$code' and ID>='$from' and ID<='$to' order by Name asc");
        }
        else if($type=="Supplier")
        {
            return DB::select("SELECT  `ID`, `Code`, `AccCode`, `SupplierCode` as AccCodes, `Name`, `ContactPerson`, `Contact1`, `Contact2`, `Address`, `Email`, `EntryDate` FROM `suppliers` where `Code`='$code' and ID>='$from' and ID<='$to' order by Name asc");
        }

    }
    public static function selectFloors($code)
    {

        return DB::select("SELECT * FROM `floors` where Code='$code' order by FloorNumber asc");

    }
    public static function selectAccounts($code)
    {

        return DB::select("SELECT * FROM `otheraccounts` where `Code`='$code' and  `Name`='Tenants' ");
    }

    public static function savetenants($Regno,$Pno,$AccCode, $code,$name,$contactperson, $contact1, $vat,$tin,$email, $Address,$floor,$space,$rooms, $rent_amount)
    {
        return DB::insert("
        INSERT INTO `tenants` (`Code`, `AccCode`, `TenantCode`, `Name`, `ContactPerson`, `Contact`, `Email`, `Address`, `VAT`, `TIN`, `Floor`, `Space`, `Rooms`, `rent_amount`)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?,?,?)", [$Pno,$AccCode, $Regno.$code,$name,$contactperson, $contact1,$email, $Address, $vat,$tin,$floor,$space,$rooms, $rent_amount] );
    }

    public static function updatetenants($id, $Regno, $Pno, $AccCode, $code, $name, $contactperson, $contact1,  $email, $Address,$vat,$tin,$floor,$space,$rooms, $rent_amount)
    {
        return DB::update("
            UPDATE `tenants`
            SET  `TenantCode` = ?, `Name` = ?, `ContactPerson` = ?, `Contact` = ?, `Email` = ?, `Address` = ?, `VAT` = ?, `TIN` = ?, `Floor` = ?, `Space` = ?, `Rooms` = ?, `rent_amount` = ?
            WHERE `ID` = ? and `Code` = ?", [ $Regno.$code, $name, $contactperson, $contact1, $email, $Address,$vat,$tin,$floor,$space,$rooms,$rent_amount, $id,$Pno]
        );
    }

    public static function Deletetenants($id,$code)
    {
        return DB::delete("delete from `tenants` where `Code`='$code' and `ID`='$id'");
    }
}
