<?php
class Membership{
    public $db;
    public function __construct($db)
    {
        $this->db          = $db;
    }

    public function addMembers(){
        $db             = $this->db;
        $result         = array();
        $newmembers     = self::zeroDebtCustomers() ;
        $debts          = self::debtCustomers();
        $members        = self::members();

        $result = array_diff($newmembers, $debts);
        if(!empty($members)){
            $result = array_diff($result, $members);
        }
        
        $maxsql= $db->prepare("SELECT max(id) as maxd FROM vip");
        $maxsql->execute();
    if($maxsql->rowCount()>0){$row = $maxsql->fetch(PDO::FETCH_ASSOC);
        $maxD = $row['maxd']+1;} else {$maxD = 1 ; }
        $act = 0 ;
        $inser_sql = $db->prepare("INSERT INTO vip(id,custid,totmoney,addition, wanted , dayqty,carskind , model )
                                    VALUES(?,?,?,? ,?,?,?,?)");
        $custmer_contracts = $db->prepare("SELECT contracts.wanted , contracts.days_qty ,contracts.after_vat
                                     , contracts.addtion_cost , cars.model,cars.kind
        FROM `contracts` , cars
        WHERE contracts.carid	= cars.carid AND contracts.cont_status > 1 AND date_in >'2020-01-10'
        AND contracts.cust_id = ?")   ;

        foreach($result as $custid){
            $totmoney = 0 ; 
            $addition = 0 ;
            $wanted = 0;
            $dayqty = 0 ;
            $after_vat = 0 ;
            $carskind = "" ;
            $model = "";

            $custmer_contracts->bindParam(1,$custid);
            $custmer_contracts->execute();
        while ($rows = $custmer_contracts->fetch()) {
                $wanted      = $wanted + $rows ["wanted"];
                $after_vat   = $after_vat + $rows ["after_vat"];
                $addition    = $addition + $rows ["addtion_cost"];
                $dayqty      = $dayqty + $rows ["days_qty"];
                $kind        = $rows['kind'];
            if (str_contains($carskind, $kind)) { 
                }else{
                    $carskind .= $kind . ', ';
                }
                $mod        = $rows['model'];
            if (str_contains($model, $mod)) { 
                }else{
                    $model .= $mod . ', ';
                }
            }
            $totmoney = $after_vat - $wanted   ; 
            $inser_sql->bindParam(1,$maxD);
            $inser_sql->bindParam(2,$custid);
            $inser_sql->bindParam(3,$totmoney);
            $inser_sql->bindParam(4,$addition);
            $inser_sql->bindParam(5,$wanted);
            $inser_sql->bindParam(6,$dayqty);
            $inser_sql->bindParam(7,$carskind);
            $inser_sql->bindParam(8,$model);
            if($inser_sql->execute()){
                $act = $act + 1;
            }
            $maxD = $maxD + 1 ;
        }
        echo ($act); 
    }

    public function changeLevel($custid,$level){
        $db = $this->db ;
        $sql = $db->prepare("UPDATE  vip set cust_level = ? where custid = ? ");
        $sql->bindParam(1,$level);
        $sql->bindParam(2,$custid);
        if($sql->execute()){
            return true;
        }else{
            return false ;
        }
    }
     // Zero debt customers
     public function zeroDebtCustomers(){
        $db          = $this->db;
        $newmembers = array();
        $zero = $db->prepare("SELECT DISTINCT contracts.cust_id as custid FROM `contracts` , customers 
                                WHERE contracts.cust_id = customers.cust_id AND contracts.cont_status = 2
                                 AND contracts.wanted < 9 AND contracts.after_vat > 300
                                 AND date_in >'2020-01-10'
                                 AND customers.dgree > 64
                                  AND customers.idSA < 3000000000");
        $zero->execute();
        if($zero->rowcount() > 0){
            while ($row = $zero->fetch()) {
                array_push($newmembers, $row['custid']);
            }
        }
        return $newmembers ;
    }
// debt customers
 public function debtCustomers(){
    $db          = $this->db;
    $debts = array();
    $debt = $db->prepare("SELECT DISTINCT contracts.cust_id as custid FROM `contracts` , customers 
                        WHERE contracts.cust_id = customers.cust_id AND contracts.cont_status = 3
                        AND contracts.wanted > 500 
                        AND customers.idSA < 3000000000");
    $debt->execute();
    if($debt->rowcount() > 0){
        while ($row = $debt->fetch()) {
            array_push($debts, $row['custid']);
        }
    }
        return $debts ;
    }
     // members
     public function members(){
        $db          = $this->db;
        $members = array();
        $mem = $db->prepare("SELECT DISTINCT custid FROM `vip` ");
        $mem->execute();
        if($mem->rowcount() > 0){
            while ($row = $mem->fetch()) {
                array_push($members, $row['custid']);
            }
        }
            return $members ;
    
        }

        public function active(){
            // select custid ,custid2 from contracts where cont_status = 1
            $db = $this->db ;
            $actives = array();
            $mem = $db->prepare("SELECT DISTINCT cust_id FROM contracts where cont_status = 1
                            UNION
                            SELECT DISTINCT cust_id2 FROM contracts where cont_status = 1");
            $mem->execute();
        if($mem->rowcount() > 0){
            while ($row = $mem->fetch()) {
                array_push($actives, $row['cust_id']);
            }
        }
            return $actives ;
    
        }
        
}
// include "../actions/dbPDO.php";
// $mem = new Membership($db);
// $mem ->addMembers();