<?php
class PO{

    private $table = "T_Po";
    private $itemPerPageAdmin = 10;

    public function update_doc_info($crud, $id, $stat){
        $result = 0;

        $text = "UPDATE $this->table SET po_document = '$stat' WHERE po_id = '$id'";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function update_ponotes($crud, $id, $ponotes){
        $result = 0;

        $text = "UPDATE $this->table SET po_notes = '$ponotes' WHERE po_id = '$id'";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function update_invoice($crud, $id, $invoice){
        $result = 0;

        $text = "UPDATE $this->table SET po_invoice = '$invoice' WHERE po_id = '$id'";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function get_data_export($crud,$status,$region,$start_date,$end_date,$acg_region,$acg_pic){
        $result = 0;
        date_default_timezone_set('Asia/Jakarta');
        $now = date("Y-m-d H:i:s");
        $cond_stat = "";
        $cond_date = "";
        $cond_region = "";

        $cond_region = "";
        $cond_pic = "";
        if($acg_region != ""){
            $cond_region = "AND po_ref_region = '$acg_region'";
        } else {
            if($region != "'all'"){
                $cond_region = "AND po_ref_region IN ($region)";
            }
        }
        if($acg_pic != ""){
            $cond_pic = "AND po_ref_admin_client = '$acg_pic'";
        }

        if($start_date != ""){
            $cond_date = "AND UNIX_TIMESTAMP('$start_date')*1000 <= UNIX_TIMESTAMP(po_date)*1000 AND UNIX_TIMESTAMP('$end_date')*1000 >= UNIX_TIMESTAMP(po_date)*1000";
        }

        if($status != "all"){
            $cond_stat = "AND po_status = '$status'";
        }

        $text = "SELECT po_id, po_order_num, po_subclient, po_invoice, po_status, po_total, po_date, po_exp_date, po_item_total, po_subtotal, po_ppn_amount, po_total, po_document, po_notes, po_ppn, client_name, client_email, client_phone, admin_name, region_title, atpo_id, atpo_desc, atpo_product, atpo_qty, atpo_unit, atpo_price FROM AT_Po LEFT JOIN T_Po ON atpo_ref_po = po_id LEFT JOIN T_Client ON po_ref_client = client_id LEFT JOIN T_Admin ON po_ref_admin = admin_id LEFT JOIN T_Region ON po_ref_region = region_id WHERE po_id != '' $cond_stat $cond_region $cond_pic $cond_date GROUP BY atpo_id ORDER BY atpo_ref_po ASC, po_date ASC";
        $query = $crud->conn()->query($text);
        if($query->num_rows >= 1){
            $result = array();
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }
        return $result;
    }
    public function change_paid($crud, $id, $stat){
        $result = 0;

        $text = "UPDATE $this->table SET po_paid = '$stat' WHERE po_id = '$id'";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function get_data_dashboard($crud,$acg_region,$acg_pic){
        $result = 0;
        date_default_timezone_set('Asia/Jakarta');
        $now = date("Y-m-d H:i:s");
        $cond_region = "";
        $cond_pic = "";
        if($acg_region != ""){
            $cond_region = "AND po_ref_region = '$acg_region'";
        }
        if($acg_pic != ""){
            $cond_pic = "AND po_ref_admin_client = '$acg_pic'";
        }

        $text = "SELECT po_id, po_order_num, po_status, po_total, po_date, po_exp_date, po_total, po_subclient, client_name, admin_name, region_title FROM T_Po LEFT JOIN T_Client ON po_ref_client = client_id LEFT JOIN T_Admin ON po_ref_admin = admin_id LEFT JOIN T_Region ON po_ref_region = region_id WHERE po_status = 'open' $cond_region $cond_pic GROUP BY po_id LIMIT 10";
        $query = $crud->conn()->query($text);
        if($query->num_rows >= 1){
            $result = array();
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }
        return $result;
    }
    public function total_data_dashboard_paid($crud,$acg_region,$acg_pic){
        $result = 0;
        $cond_region = "";
        $cond_pic = "";
        if($acg_region != ""){
            $cond_region = "AND po_ref_region = '$acg_region'";
        }
        if($acg_pic != ""){
            $cond_pic = "AND po_ref_admin_client = '$acg_pic'";
        }

        $text = "SELECT COUNT(po_id) AS total_po FROM T_Po WHERE po_paid = 'yes' $cond_region $cond_pic LIMIT 1";
        $query = $crud->conn()->query($text);
        if($query->num_rows == 1){//HAS TO BE EXACT 1 RESULT
            $row = $query->fetch_assoc();
            $result = $row['total_po'];
        }
        return $result;
    }
    public function total_data_dashboard($crud, $status,$acg_region,$acg_pic){
        $result = 0;
        $cond_region = "";
        $cond_pic = "";
        if($acg_region != ""){
            $cond_region = "AND po_ref_region = '$acg_region'";
        }
        if($acg_pic != ""){
            $cond_pic = "AND po_ref_admin_client = '$acg_pic'";
        }

        $text = "SELECT COUNT(po_id) AS total_po FROM T_Po WHERE po_status = '$status' $cond_region $cond_pic LIMIT 1";
        $query = $crud->conn()->query($text);
        if($query->num_rows == 1){//HAS TO BE EXACT 1 RESULT
            $row = $query->fetch_assoc();
            $result = $row['total_po'];
        }
        return $result;
    }
    public function total_data_dashboard_all($crud,$acg_region,$acg_pic){
        $result = 0;
        $cond_region = "";
        $cond_pic = "";
        if($acg_region != ""){
            $cond_region = "AND po_ref_region = '$acg_region'";
        }
        if($acg_pic != ""){
            $cond_pic = "AND po_ref_admin_client = '$acg_pic'";
        }

        $text = "SELECT COUNT(po_id) AS total_po FROM T_Po WHERE po_id != '' $cond_region $cond_pic LIMIT 1";
        $query = $crud->conn()->query($text);
        if($query->num_rows == 1){//HAS TO BE EXACT 1 RESULT
            $row = $query->fetch_assoc();
            $result = $row['total_po'];
        }
        return $result;
    }
    public function get_total_stat_region($crud, $acg_pic){
        $result = 0;
        $cond_pic = "";

        if($acg_pic != ""){
            $cond_pic = "WHERE pr.po_ref_admin_client = '$acg_pic'";
        }

        $text = "SELECT rg.region_title, COUNT(pr.po_id) as total_po, (SELECT COUNT(pr1.po_id) AS total_open FROM T_Po pr1 WHERE pr1.po_status = 'open' AND pr1.po_ref_region = pr.po_ref_region GROUP BY pr1.po_status) AS total_open, (SELECT COUNT(pr2.po_id) AS total_prepared FROM T_Po pr2 WHERE pr2.po_status = 'prepared' AND pr2.po_ref_region = pr.po_ref_region GROUP BY pr2.po_status) AS total_prepared, (SELECT COUNT(pr3.po_id) AS total_progress FROM T_Po pr3 WHERE pr3.po_status = 'progress' AND pr3.po_ref_region = pr.po_ref_region GROUP BY pr3.po_status) AS total_progress, (SELECT COUNT(pr4.po_id) AS total_complete FROM T_Po pr4 WHERE pr4.po_status = 'complete' AND pr4.po_ref_region = pr.po_ref_region GROUP BY pr4.po_status) AS total_complete, (SELECT COUNT(pr5.po_id) AS total_cancel FROM T_Po pr5 WHERE pr5.po_status = 'cancel' AND pr5.po_ref_region = pr.po_ref_region GROUP BY pr5.po_status) AS total_cancel FROM T_Po pr LEFT JOIN T_Region rg ON pr.po_ref_region = rg.region_id $cond_pic GROUP BY pr.po_ref_region ORDER BY rg.region_title ASC";
        $query = $crud->conn()->query($text);
        if($query->num_rows >= 1){
            $result = array();
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }
        return $result;
    }
    public function get_report_bars($crud, $start_date, $end_date, $status){
        $result = 0;
        $cond_date = "";
        $cond_stat = "AND po_status != 'cancel'";

        if($status != "all"){
            $cond_stat = "AND po_status = '$status'";
        }

        if($start_date != ""){
            $cond_date = "AND UNIX_TIMESTAMP('$start_date')*1000 <= UNIX_TIMESTAMP(po_date)*1000 AND UNIX_TIMESTAMP('$end_date')*1000 >= UNIX_TIMESTAMP(po_date)*1000";
        }

        $text = "SELECT po_ref_region, region_title, SUM(po_total) AS total_amount FROM T_Po LEFT JOIN T_Region ON region_id = po_ref_region WHERE po_id != '' $cond_stat $cond_date GROUP BY po_ref_region ORDER BY region_title ASC";
        $query = $crud->conn()->query($text);
        if($query->num_rows >= 1){
            $result = array();
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }
        return $result;
    }
    public function get_report_chart($crud, $start_date, $end_date, $region, $acg_pic, $status){
        $result = 0;
        $cond_region = "";
        $cond_date = "";
        $cond_pic = "";
        $cond_stat = "AND po_status != 'cancel'";

        if($acg_pic != ""){
            $cond_pic = "AND po_ref_admin_client = '$acg_pic'";
        }

        if($region != "0"){
            $cond_region = "AND po_ref_region = '$region'";
        }

        if($status != "all"){
            $cond_stat = "AND po_status = '$status'";
        }

        if($start_date != ""){
            $cond_date = "AND UNIX_TIMESTAMP('$start_date')*1000 <= UNIX_TIMESTAMP(po_date)*1000 AND UNIX_TIMESTAMP('$end_date')*1000 >= UNIX_TIMESTAMP(po_date)*1000";
        }


        $text = "SELECT DATE_FORMAT(po_date, '%Y-%m-%d') AS po_date, COUNT(po_id) AS total_po FROM T_Po WHERE po_id != '' $cond_stat $cond_region $cond_pic $cond_date GROUP BY DATE_FORMAT(po_date, '%Y-%m-%d') ORDER BY po_date ASC";
        $query = $crud->conn()->query($text);
        if($query->num_rows >= 1){
            $result = array();
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }

        if(is_array($result)){
            $text_high = "SELECT DATE_FORMAT(po_date, '%Y-%m-%d') AS po_date, COUNT(po_id) AS total_po FROM T_Po WHERE po_id != '' $cond_stat $cond_region $cond_pic $cond_date GROUP BY DATE_FORMAT(po_date, '%Y-%m-%d') ORDER BY total_po DESC LIMIT 1";
            $query_high = $crud->conn()->query($text_high);
            if($query_high->num_rows == 1){
                $row_high = $query_high->fetch_assoc();
                $highest_total = $row_high['total_po'];
                $highest_date = $row_high['po_date'];
            }

            $text_low = "SELECT DATE_FORMAT(po_date, '%Y-%m-%d') AS po_date, COUNT(po_id) AS total_po FROM T_Po WHERE po_id != '' $cond_stat $cond_region $cond_pic $cond_date GROUP BY DATE_FORMAT(po_date, '%Y-%m-%d') ORDER BY total_po ASC LIMIT 1";
            $query_low = $crud->conn()->query($text_low);
            if($query_low->num_rows == 1){
                $row_low = $query_low->fetch_assoc();
                $lowest_total = $row_low['total_po'];
                $lowest_date = $row_low['po_date'];
            }

            $result[0]['highest_total'] = $highest_total;
            $result[0]['highest_date'] = $highest_date;
            $result[0]['lowest_total'] = $lowest_total;
            $result[0]['lowest_date'] = $lowest_date;
        }
        return $result;
    }
    public function change_stat($crud, $id, $stat){
        $result = 0;

        $text = "UPDATE $this->table SET po_status = '$stat' WHERE po_id = '$id'";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function update_po($crud,$po_id,$ponumber,$region,$pic,$date_format,$clientid,$cname,$cemail,$cphone,$caddress,$total_item,$csub,$picc){ 
        $result = 0;

        $text = "UPDATE $this->table SET po_order_num = '$ponumber', po_ref_region = '$region', po_ref_admin = '$pic', po_date = '$date_format', po_ref_client = '$clientid', po_client_name = '$cname', po_client_email = '$cemail', po_client_phone = '$cphone', po_client_address = '$caddress', po_item_total = '$total_item', po_subclient = '$csub', po_ref_admin_client = '$picc' WHERE po_id = '$po_id'";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function delete_atpo($crud, $atpoid){
        $result = 0;

        $text = "DELETE FROM AT_Po WHERE atpo_id = '$atpoid'";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function update_price_po($crud,$po_id,$subtotal,$ppn_amount,$total,$total_item){ 
        $result = 0;

        $text = "UPDATE $this->table SET po_subtotal = '$subtotal', po_ppn_amount = '$ppn_amount', po_total = '$total', po_item_total = '$total_item' WHERE po_id = '$po_id'";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function update_atpo($crud,$atpo_id,$edesc,$eunit,$eunittxt,$eprice,$eproduct){ 
        $result = 0;

        $text = "UPDATE AT_Po SET atpo_desc = '$edesc', atpo_ref_unit = '$eunit', atpo_unit = '$eunittxt', atpo_price = '$eprice', atpo_product = '$eproduct' WHERE atpo_id = '$atpo_id'";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function get_atpo_item($crud, $po_id){
        $result = 0;

        $text = "SELECT * FROM AT_Po WHERE atpo_ref_po = '$po_id' ORDER BY atpo_create_date ASC";
        $query = $crud->conn()->query($text);
        if($query->num_rows >= 1){
            $result = array();
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }
        return $result;
    }
    public function get_detail_po($crud, $po_id){
        $result = 0;

        $text = "SELECT po_id, po_order_num, po_invoice, po_ref_client, po_subclient, po_ref_admin, po_ref_admin_client, po_ref_region, po_client_name, po_client_email, po_client_phone, po_paid, po_document, po_client_address, po_subtotal, po_ppn_amount, po_ppn, po_total, po_item_total, po_status, po_date, po_create_date, po_notes, client_name, client_email, client_phone, client_address, client_ref_region, region_title, admin_name, (SELECT adc.admin_name AS admin_client_name FROM T_Admin adc WHERE adc.admin_id = po.po_ref_admin_client LIMIT 1) AS admin_client FROM $this->table po LEFT JOIN T_Admin ON po_ref_admin = admin_id LEFT JOIN T_Region ON po_ref_region = region_id LEFT JOIN T_Client ON po_ref_client = client_id WHERE po_id = '$po_id'";
        $query = $crud->conn()->query($text);
        if($query->num_rows >= 1){
            $result = array();
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }
        return $result;
    }
    public function generate_id($crud){
        $result = 0;

        date_default_timezone_set("Asia/Jakarta");
        $now = date("dny");
        $jam = date("hs");

        $result = "PO".$jam."-".$now."-".strtoupper(substr(sha1(mt_rand()),17,4));

        return $result;
    }
    public function check_po_number($crud, $po_no){
        $result = 0;

        $text = "SELECT po_id FROM $this->table WHERE po_order_num = '$po_no'";
        $query = $crud->conn()->query($text);
        if($query->num_rows >= 1){
            $result = array();
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }
        return $result;
    }
    public function check_po_id($crud, $po_id){
        $result = 0;

        $text = "SELECT po_id FROM $this->table WHERE po_id = '$po_id'";
        $query = $crud->conn()->query($text);
        if($query->num_rows >= 1){
            $result = array();
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }
        return $result;
    }
    public function insert_new_po($crud,$po_id,$ponumber,$region,$pic,$date_format,$clientid,$cname,$cemail,$cphone,$caddress,$posubtotal,$poppn,$poppn_price,$pototal,$total_item,$postatus,$csub,$picc){
        $result = 0;
        date_default_timezone_set("Asia/Jakarta");
        $now = date("Y-m-d H:i:s");

        $text = "INSERT INTO $this->table (po_id, po_order_num, po_ref_client, po_ref_admin, po_ref_region, po_client_name, po_client_email, po_client_phone, po_client_address, po_subtotal, po_ppn_amount, po_ppn, po_total, po_item_total, po_status, po_date, po_create_date, po_subclient, po_ref_admin_client) VALUES('$po_id', '$ponumber', '$clientid', '$pic', '$region', '$cname', '$cemail', '$cphone', '$caddress', '$posubtotal', '$poppn_price', '$poppn', '$pototal', '$total_item', '$postatus', '$date_format', '$now', '$csub', '$picc')";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function insert_at_po($crud, $poid, $hdesc, $hqty, $hunit, $hunittxt, $hprice, $hproduct){
        $result = 0;
        date_default_timezone_set("Asia/Jakarta");
        $now = date("Y-m-d H:i:s");

        $text = "INSERT INTO AT_Po (atpo_ref_po, atpo_desc, atpo_qty, atpo_ref_unit, atpo_unit, atpo_price, atpo_create_date, atpo_product) VALUES('$poid', '$hdesc', '$hqty', '$hunit', '$hunittxt', '$hprice', '$now', '$hproduct')";
        $result = $crud->execute_id($text);
        return $result;
    }
    public function get_data($crud, $page, $itemPerPage, $status, $keyword, $start_date, $end_date, $region, $sortby,$pic){
        $result = 0;
        date_default_timezone_set('Asia/Jakarta');
        $now = date("Y-m-d H:i:s");
        $cond_search = "";
        $cond_region = "";
        $cond_date = "";
        $cond_pic = "";

        if($pic != ""){
            $cond_pic =  "AND po_ref_admin_client = '$pic'";
        }
        if($keyword != ""){
            $cond_search = "AND (po_id LIKE '%$keyword%' OR po_order_num LIKE '%$keyword%' OR client_name LIKE '%$keyword%' OR po_subclient LIKE '%$keyword%' OR atpo_product LIKE '%$keyword%')";
        }
        if($start_date != ""){
            $cond_date = "AND UNIX_TIMESTAMP('$start_date')*1000 <= UNIX_TIMESTAMP(po_date)*1000 AND UNIX_TIMESTAMP('$end_date')*1000 >= UNIX_TIMESTAMP(po_date)*1000";
        }
        if($region != ""){
            $cond_region = "AND po_ref_region = '$region'";
        }

        $cond_sort = "ORDER BY po_date ASC";
        if($sortby != ""){
            if($sortby == "old"){
                $cond_sort = "ORDER BY po_date ASC";
            } else if($sortby == "new") {
                $cond_sort = "ORDER BY po_date DESC";
            }
        }

        //get total data
        $total_data = 0;
        $text_total = "SELECT po_id, po_order_num, po_invoice, po_status, po_total, po_document, po_date, po_exp_date, po_total, po_paid, po_subclient, client_name, admin_name, region_title, atpo_product FROM T_Po LEFT JOIN AT_Po ON po_id = atpo_ref_po LEFT JOIN T_Client ON po_ref_client = client_id LEFT JOIN T_Admin ON po_ref_admin = admin_id LEFT JOIN T_Region ON po_ref_region = region_id WHERE po_status = '$status' $cond_region $cond_pic $cond_date $cond_search GROUP BY po_id";
        $query_total = $crud->conn()->query($text_total);
        $total_data  = $query_total->num_rows;
        // if($query_total->num_rows == 1){//HAS TO BE EXACT 1 RESULT
        //     $row_total = $query_total->fetch_assoc();
        //     $total_data = $row_total['total_po'];
        // }

        //get total page
        $total_page  = ceil($total_data / $itemPerPage);
        $limitBefore = $page <= 1 || $page == null ? 0 : ($page-1) * $itemPerPage;

        $text = "SELECT po_id, po_order_num, po_invoice, po_status, po_total, po_document, po_date, po_exp_date, po_total, po_paid, po_subclient, client_name, admin_name, region_title, atpo_product FROM T_Po LEFT JOIN AT_Po ON po_id = atpo_ref_po LEFT JOIN T_Client ON po_ref_client = client_id LEFT JOIN T_Admin ON po_ref_admin = admin_id LEFT JOIN T_Region ON po_ref_region = region_id WHERE po_status = '$status' $cond_region $cond_pic $cond_date $cond_search GROUP BY po_id $cond_sort LIMIT $limitBefore, $itemPerPage";
        $query = $crud->conn()->query($text);
        if($query->num_rows >= 1){
            $result = array();
            while($row = $query->fetch_assoc()){
                $result[] = $row;
            }
        }

        if(is_array($result)){
            $result[0]['total_page'] = $total_page;
            $result[0]['total_data_all'] = $total_data;
            $result[0]['total_data'] = count($result);
        }
        return $result;
    }

    public function total_data($crud, $status, $keyword, $start_date, $end_date, $region,$pic){
        $result = 0;
        date_default_timezone_set('Asia/Jakarta');
        $now = date("Y-m-d H:i:s");
        $cond_search = "";
        $cond_region = "";
        $cond_date = "";
        $cond_pic = "";

        if($pic != ""){
            $cond_pic =  "AND po_ref_admin_client = '$pic'";
        }
        if($keyword != ""){
            $cond_search = "AND (po_id LIKE '%$keyword%' OR po_order_num LIKE '%$keyword%' OR client_name LIKE '%$keyword%' OR po_subclient LIKE '%$keyword%')";
        }
        if($start_date != ""){
            $cond_date = "AND UNIX_TIMESTAMP('$start_date')*1000 <= UNIX_TIMESTAMP(po_date)*1000 AND UNIX_TIMESTAMP('$end_date')*1000 >= UNIX_TIMESTAMP(po_date)*1000";
        }
        if($region != ""){
            $cond_region = "AND po_ref_region = '$region'";
        }

        $text = "SELECT COUNT(po_id) AS total_po FROM T_Po LEFT JOIN T_Client ON po_ref_client = client_id WHERE po_status = '$status' $cond_region $cond_pic $cond_date $cond_search LIMIT 1";
        $query = $crud->conn()->query($text);
        if($query->num_rows == 1){//HAS TO BE EXACT 1 RESULT
            $row = $query->fetch_assoc();
            $result = $row['total_po'];
        }

        return $result;
    }
    public function delete_atpo_bypo($crud, $poid){
        $result = 0;

        $text = "DELETE FROM AT_Po WHERE atpo_ref_po = '$poid'";
        $result = $crud->execute_query($text);
        return $result;
    }
    public function delete_po($crud, $id){
        $result = 0;
        $this->delete_atpo_bypo($crud, $id); //remove image before

        $text = "DELETE FROM $this->table WHERE po_id = '$id'";
        $result = $crud->execute_query($text);
        return $result;
    }
}
?>