<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;

class Payment extends Model
{
    function province()
        {
            $results = app('db')->select("SELECT `id`,`name_en` from `provinces`");

            return $results;
        }
        
        function alldistricts()
        {
            $results = app('db')->select("SELECT `id`,`name_en`,`province_id` from `districts`");

            return $results;
        }
        
        function districts($pro)
        {
            $results = app('db')->select("SELECT `id`,`name_en` from `districts` where `province_id`='$pro'");

            return $results;
        }
        
        function cities($dis)
        {
            $results = app('db')->select("SELECT `id`,`name_en` from `cities` where `district_id`='$dis'");

            return $results;
        }
        
    function allcities()
        {
            $results = app('db')->select("SELECT `id`,`name_en`,`district_id` from `cities`");

            return $results;
        }
        
            
    
    function cust_bal($id){
        $tot_inv = app('db')->select("SELECT SUM(`po_final_tot`) as sum from `mes_qto` where `po_status`='1' and `po_auth`='1' and `po_sup_id`='$id'");
        $tot_return = app('db')->select("SELECT SUM(`ret_amount`) as sum from `inv_return` LEFT JOIN `mes_qto` ON `inv_return`.`ret_invnum` = `mes_qto`.`po_id` where `inv_return`.`ret_stetus`='1' and `inv_return`.`ret_proceed`='1' and `mes_qto`.`po_sup_id`='$id'");
        $tot_pay = app('db')->select("SELECT SUM(`ret_amount`) as sum from `inv_return` LEFT JOIN `mes_qto` ON `inv_return`.`ret_invnum` = `mes_qto`.`po_id` where `inv_return`.`ret_stetus`='1' and `inv_return`.`ret_proceed`='1' and `mes_qto`.`po_sup_id`='$id'");
        
        
    }
    
    function cal_credit_limit($custom){
        $cust = app('db')->select("SELECT cust_id,cust_creditper from `mes_customer` where `cust_status`='1' and `cust_id`='$custom'");
        $init_updt = app('db')->update("UPDATE `mes_customer` SET `credit_limit`='pass' WHERE `cust_id` = '$custom'");
        
        for($i=0; $i<count($cust); $i++){
           // $date = $cust[0]->cust_id;
                  //  date_sub($date,date_interval_create_from_date_string($cust[$i]->cust_creditper.' days'));
                   // $old_date = date_format($date,"Y-m-d");
                  $old_date = Carbon::now()->subDays($cust[$i]->cust_creditper);
                   $nw =  $old_date->toDateString();
                   
            $data = app('db')->select("SELECT * from `mes_qto` where `po_sup_id`='$custom' and `po_cdate` <= '$nw' and `po_payment`='0'");
            
            if(count($data)>0){
                for($i=0; $i<count($data); $i++){
                           $bal = $this->inv_balance($data[$i]->po_id);
                            if($bal>0){
                                $result = app('db')->update("UPDATE `mes_customer` SET `credit_limit`='fail' WHERE `cust_id` = '$custom'");
                            }
                        }
            }else{
                    $result = app('db')->update("UPDATE `mes_customer` SET `credit_limit`='pass' WHERE `cust_id` = '$custom'");
        
            }
        }
        
    }
    
    function cal_credit_limit1($custom){
        $cust = app('db')->select("SELECT cust_id,cust_creditper from `mes_customer` where `cust_status`='1' and `cust_id`='$custom'");
        $init_updt = app('db')->update("UPDATE `mes_customer` SET `credit_limit`='pass' WHERE `cust_id` = '$custom'");
        
        for($i=0; $i<count($cust); $i++){
           // $date = $cust[0]->cust_id;
                  //  date_sub($date,date_interval_create_from_date_string($cust[$i]->cust_creditper.' days'));
                   // $old_date = date_format($date,"Y-m-d");
                  $old_date = Carbon::now()->subDays($cust[$i]->cust_creditper);
                   $nw =  $old_date->toDateString();
                   
            $data = app('db')->select("SELECT * from `mes_qto` where `po_sup_id`='$custom' and `po_cdate` <= '$nw' and `po_payment`='0'");
            
            if(count($data)>0){
                for($i=0; $i<count($data); $i++){
                           echo $bal = $this->inv_balance($data[$i]->po_id);
                            if($bal>0){
                                $result = app('db')->update("UPDATE `mes_customer` SET `credit_limit`='fail' WHERE `cust_id` = '$custom'");
                            }
                        }
            }else{
                    $result = app('db')->update("UPDATE `mes_customer` SET `credit_limit`='pass' WHERE `cust_id` = '$custom'");
        
            }
        }
    }
    
    
    function inv_balance($inv){
        $sdata = app('db')->select("SELECT SUM(`po_final_tot`) as tot,SUM(`return_amnt`) as ret from `mes_qto` where `po_status`='1' and `po_id`='$inv'");
        $pdata = app('db')->select("SELECT SUM(`pay_amount`) as totp from `mes_payment` where `pay_invid`='$inv' and `pay_stetus`='1'");
        $retdata = app('db')->select("SELECT SUM(`ret_amount`) as unret from `inv_return` where `ret_invnum`='$inv' and `ret_proceed`='0' and `ret_stetus`='1'");
        
        $outinv = $sdata[0]->tot - ($sdata[0]->ret + $pdata[0]->totp +$retdata[0]->unret);          
            return $outinv;
        
    }
}    