<?php
Class General extends CI_Model {
    
    public function index() {
            //$this->load->database();
           // $this->load->library('email');
    
    }
    
    function chk_dup($tablename = '',$column_ = '',$where_ = array() ){
    
            $this->db->select($column_);
            $this->db->from($tablename);
            if (!empty($where_arr)) {
                $this->db->where($where_arr);
            }
            
            $detailtax = $this->db->get();
            return $detailtax->result();
                
    
    }
    
    function getData($tablename = '', $columns_arr = array(), $where_arr = array(), $limit = 0, $offset = 0, $orderby = array()) {

        $limit = ($limit == 0) ? Null : $limit;

        if (!empty($columns_arr)) {
            $this->db->select(implode(',', $columns_arr), FALSE);
        }

        if ($tablename == '') {
            return array();
        } else {
            $this->db->from($tablename);

            if (!empty($where_arr)) {
                $this->db->where($where_arr);
            }

            if ($limit > 0 AND $offset > 0) {
                $this->db->limit($limit, $offset);
            } elseif ($limit > 0 AND $offset == 0) {
                $this->db->limit($limit);
            }

            if (count($orderby) > 0) {
                $orderbyString = '';

                foreach ($orderby as $orderclause) {

                    $orderbyString .= $orderclause["field"] . ' ' . $orderclause["order"] . ', ';
                }
                if (strlen($orderbyString) > 2) {
                    $orderbyString = substr($orderbyString, 0, strlen($orderbyString) - 2);
                    var_dump($orderbyString);
                }
                $this->db->order_by($orderbyString);
            }

            $query = $this->db->get();

            return $query->result();
        }
    }
    
    function insertData($tbl_name,$dataArr) {

        $result = $this->db->insert($tbl_name, $dataArr);
        return $result;
    }
    
    function updateData($tbl_name,$dataSet,$whereArr){
        $result=$this->db->update($tbl_name,$dataSet,$whereArr);
        return $result;
    }
  
    
    function get_joint($tablename, $table2name, $join, $j_on, $columns) {
        
            
            $this->db->from($tablename);
            $this->db->join($table2name, $join, $j_on);
            $this->db->select($columns);
            $query = $this->db->get();

            return $query->result();
            
        
    }

    function cal_stock($id,$qty,$cal) {
       $this->db->select("avl_qty");
       $this->db->from('stock');
       $this->db->where("book_id",$id);
       $hasil=$this->db->get();
        $dtt = $hasil->result();  
        
        if($cal=="plus"){
            $new_qty = $dtt[0]->avl_qty + $qty;
        }else{
            $new_qty = $dtt[0]->avl_qty - $qty;
        }
            $this->db->set('avl_qty',$new_qty);
            $this->db->where('book_id', $id );
            $this->db->update('stock');
    }
    
    function check_stock($id,$qty) {
       $this->db->select("avl_qty");
       $this->db->from('stock');
       $this->db->where("book_id",$id);
       $hasil=$this->db->get();
        $dtt = $hasil->result();  
       
        $new_qty = $dtt[0]->avl_qty - $qty;
        if($new_qty>0){
            return TRUE;
        }else{
            return FALSE;
        }
    }
    
    function getlast_id(){
		$this->db->select('po_id');
		$this->db->from('mes_po');
		$this->db->order_by("po_id", "desc");
		$this->db->limit(1);
		$query = $this->db->get();
		return $query->result();
	}
	
	function getqtnlast_id(){
		$this->db->select('po_id');
		$this->db->from('mes_qto');
		$this->db->order_by("po_id", "desc");
		$this->db->limit(1);
		$query = $this->db->get();
		return $query->result();
	}
    
    function credit_limit(){
        
        $this->db->select("cust_id,cust_creditper");
    	    $this->db->from('mes_customer');
    	    $this->db->where('cust_status',1);
    	    $this->db->where('cust_edate<=',date('Y-m-d', strtotime('-3 days')));
    	    $this->db->or_where('cust_edate', null);
            $detailtax = $this->db->get();
            $cust = $detailtax->result();
            
            for($i=0; $i<count($cust); $i++){
                        $this->db->set('credit_limit', "pass");
                        $this->db->set('cust_creditper', 93);
                        $this->db->where('cust_id', $cust[$i]->cust_id);
                        $result=$this->db->update('mes_customer');
                        
              //  if($cust[$i]->cust_creditper!=0){
                    $date = date_create(date('Y-m-d'));
                    date_sub($date,date_interval_create_from_date_string('93'.' days'));
                    $old_date = date_format($date,"Y-m-d");
                
                    $this->db->from('mes_qto');
                    $this->db->select("*");
                    $this->db->where('po_sup_id',$cust[$i]->cust_id);
                    $this->db->where('po_payment','0');
                    $this->db->where('po_cdate<=',$old_date);
                    //$this->db->where('po_final_tot!=','return_amnt');
                    $detailtax = $this->db->get();
                    $data = $detailtax->result();
                    
                    if(count($data)>0){
                         for($j=0; $j<count($data); $j++){
                        $bal = $this->inv_balance($data[$j]->po_id);
                            if($bal>0){
                                $this->db->set('credit_limit', "fail");
                                $this->db->where('cust_id', $cust[$i]->cust_id);
                                $result=$this->db->update('mes_customer');
                            }
                         }    
                    }else{
                        $this->db->set('credit_limit', "pass");
                        $this->db->where('cust_id', $cust[$i]->cust_id);
                        $result=$this->db->update('mes_customer');
                    }
              //  }
                
            }
            
    }
    
    function cal_credit_limit($customer){
        $this->db->select("cust_id,cust_creditper");
    	    $this->db->from('mes_customer');
    	    $this->db->where('cust_status',1);
    	   $this->db->where('cust_uniqid',$customer);
    	   // $this->db->where('cust_id',$customer);
            $detailtax = $this->db->get();
            $cust = $detailtax->result();
            
            for($i=0; $i<count($cust); $i++){
                        $this->db->set('credit_limit', "pass");
                        $this->db->where('cust_id', $cust[$i]->cust_id);
                        $result=$this->db->update('mes_customer');
                        
                if($cust[$i]->cust_creditper!=0){
                
                    $date = date_create(date('Y-m-d'));
                    date_sub($date,date_interval_create_from_date_string($cust[$i]->cust_creditper.' days'));
                    $old_date = date_format($date,"Y-m-d");
                
                    $this->db->from('mes_qto');
                    $this->db->select("*");
                    $this->db->where('po_sup_id',$cust[$i]->cust_id);
                    $this->db->where('po_cdate<=',$old_date);
                    $this->db->where('po_payment','0');
                   // $this->db->where('po_final_tot!=','return_amnt');
                    $detailtax = $this->db->get();
                    $data = $detailtax->result();
                    
                    if(count($data)>0){
                        
                        for($j=0; $j<count($data); $j++){
                            $bal = $this->inv_balance($data[$j]->po_id);
                           // echo $data[$j]->po_id."-".$bal."<br>";
                            if($bal>0){
                                $this->db->set('credit_limit', "fail");
                                $this->db->where('cust_id', $cust[$i]->cust_id);
                                $result=$this->db->update('mes_customer');
                                //echo "updated";
                            }
                        }
                        
                        
                    }else{
                        $this->db->set('credit_limit', "pass");
                        $this->db->where('cust_id', $cust[$i]->cust_id);
                        $result=$this->db->update('mes_customer');
                    }
                }
                
            }
        return;    
    }
    
    function inv_balance($inv){
        //with unauthorized
        
        $this->db->select("SUM(`po_final_tot`) as tot,SUM(`return_amnt`) as ret");    
            $this->db->from('mes_qto');
            $this->db->where('po_id', $inv);
          //  $this->db->where('po_payment', 0);
            $query = $this->db->get();
            $sdata = $query->result();
            
       // SELECT SUM(`pay_amount`) FROM `mes_payment_main` WHERE `pay_cust_id`='$cust' AND `pay_authrize`=1 
        $this->db->select("SUM(`pay_amount`) as totp");    
            $this->db->from('mes_payment');
            $this->db->where('pay_invid', $inv);
            //$this->db->where('pay_authrize', 1);
            $this->db->where('pay_stetus', 1);
            $query = $this->db->get();
            $pdata = $query->result();
            
            $this->db->select("SUM(`ret_amount`) as unret");    
            $this->db->from('inv_return');
            $this->db->where('ret_invnum', $inv);
            $this->db->where('ret_proceed', 0);
            $this->db->where('ret_stetus', 1);
            $query = $this->db->get();
            $retdata = $query->result();
                        
                  
            $outinv = $sdata[0]->tot - ($sdata[0]->ret + $pdata[0]->totp +$retdata[0]->unret);          
            return $outinv;
                        
    }
    
}