<?php
class Itembranch extends MY_Controller
{
    function __construct()
    {
        parent::__construct();
        $this->db_inventory = $this->load->database("inventory", true);
        $this->db_inventory_log = $this->load->database('inventory_log', true);
        $this->db_onex = 'one_aditya';
    }
    
    function search()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;

            $search = "%%";
            if (isset($prm['search'])) {
                $search = trim($prm["search"]);
                $search = '%' . $prm['search'] . '%';
            }
            $order_by = "SupplierCode";
            if (isset($prm['order_by'])) {
                $order_by = trim($prm["order_by"]);
            }
            $order_type = "asc";
            if (isset($prm['order_type'])) {
                $order_type = trim($prm["order_type"]);
            }
            $order = $order_by.' '.$order_type;
            $perpage = 10;
	        $offset = ($prm['current_page'] - 1) * $perpage ;

            $sql = "SELECT COUNT(*) as total FROM 
                    (
                        SELECT *
                        FROM `item`  
                        LEFT JOIN `itembranch` ON ItemBranchItemID = ItemID AND ItemBranchM_CompanyID = ?
                        WHERE
                        ItemIsActive = 'Y' AND 
                        ( ItemName  like ? OR ItemSKU like ?)
                    ) x";
            $qry = $this->db_inventory->query($sql,array($prm['company']['id'],$search, $search));
            $tot_count = 0;
            $tot_page = 0;
            if ($qry) {
                $tot_count = $qry->row()->total;
                $tot_page = ceil($tot_count/$perpage);
            } else {
                $this->sys_error_db("supplier count error", $this->db_inventory->last_query());
                exit;
            }  

            $rows = array();
            $sql = "SELECT ItemID as item_id,
                    ItemName as item_name,
                    ItemSKU as item_sku,
                    IF(ItemBranchID IS NULL,'N','Y') as selected
                    FROM `item`  
                    LEFT JOIN `itembranch` ON ItemBranchItemID = ItemID AND ItemBranchM_CompanyID = ? AND 
                    ItemBranchIsActive = 'Y'
                    WHERE
                    ItemIsActive = 'Y' AND 
                    ( ItemName  like ? OR ItemSKU like ?)
                    ORDER BY ?
                    LIMIT ? OFFSET ?";
            $qry = $this->db_inventory->query($sql, array($prm['company']['id'],$search, $search, $order, $perpage, $offset));
            if($qry){
                $rows = $qry->result_array();
              

            }else {
                $this->sys_error_db("supplier data error", $this->db_inventory->last_query());
                exit;
            }  

          
            $result = array("total_page" => $tot_page, "records" => $rows);
            $this->sys_ok($result);
            exit;

        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function save(){
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $userid = $this->sys_user['M_UserID'];
            $prm = $this->sys_input;

            $name = "";
            if (isset($prm['name'])) {
                $name = trim($prm["name"]);
            }
            $phone = "";
            if (isset($prm['phone'])) {
                $phone = trim($prm["phone"]);
            }
            $email = "";
            if (isset($prm['email'])) {
                $email = trim($prm["email"]);
            }
            $website = "";
            if (isset($prm['website'])) {
                $website = trim($prm["website"]);
            }

            $this->db_inventory->trans_start();
            $this->db_inventory->trans_strict(FALSE); 

            $numbering = '';
            $sql = "SELECT `fn_numbering`('S') as numbering";
            $qry = $this->db_inventory->query($sql);
            if($qry){
                $numbering = $qry->row()->numbering;
            }
            else{
                $this->sys_error_db("get numbering error", $this->db_inventory->last_query());
                exit;
            }
            
            $last_id = 0;
            $sql = "INSERT INTO supplier(
                SupplierCode,
                SupplierName,
                SupplierPhone,
                SupplierEmail,
                SupplierWebsite,
                SupplierUserID,
                SupplierCreated,
                SupplierLastUpdated
            )
            VALUES(
                ?,?,?,?,?,?,NOW(),NOW()
            )";
            $qry = $this->db_inventory->query($sql, array($numbering,$name,$phone,$email,$website,$userid));
            if($qry){
                $last_id = $this->db_inventory->insert_id();
            }
            else{
                $this->sys_error_db("save supplier error", $this->db_inventory->last_query());
                exit;
            }

            if(count($prm['address'])>0){
                foreach ($prm['address'] as $key => $value) {
                    $type = trim($value['type']);
                    $postcode = trim($value['postcode']);
                    $description = trim($value['description']);
                    $note = trim($value['note']);
                    $sql = "INSERT INTO supplieraddress(
                        SupplierAddressSupplierID,
                        SupplierAddressType,
                        SupplierAddressPostCode,
                        SupplierAddressDescription,
                        SupplierAddressNote,
                        SupplierAddressUserID,
                        SupplierAddressCreated,
                        SupplierAddressLastUpdated
                    )
                    VALUES(
                        ?,?,?,?,?,?,NOW(),NOW()
                    )";
                    $qry = $this->db_inventory->query($sql, array($last_id, $type, $postcode, $description, $note, $userid));
                    if(!$qry){
                        $this->sys_error_db("save supplier address error", $this->db_inventory->last_query());
                        exit;
                    }
                }
            }

            if(count($prm['contact'])>0){
                foreach ($prm['contact'] as $key => $value) {
                    $name = trim($value['name']);
                    $email = trim($value['email']);
                    $mobile = trim($value['mobile']);

                    $sql = "INSERT INTO suppliercontactperson(
                        SupplierContactPersonSupplierID,
                        SupplierContactPersonName,
                        SupplierContactPersonEmail,
                        SupplierContactPersonMobile,
                        SupplierContactPersonUserID,
                        SupplierContactPersonLastUpdated,
                        SupplierContactPersonCreated
                    )
                    VALUES(
                        ?,?,?,?,?,NOW(),NOW()
                    )";
                    $qry = $this->db_inventory->query($sql, array($last_id, $name, $email, $mobile, $userid));
                    if(!$qry){
                        $this->sys_error_db("save supplier contact error", $this->db_inventory->last_query());
                        exit;
                    }
                }
            }

            $this->db_inventory->trans_complete();

            $id = $last_id;
            $sql = "SELECT supplier.*, '' as address, '' as contact 
                    FROM supplier 
                    WHERE SupplierID = ? ";
            $qry = $this->db_inventory->query($sql, array($id));
            //echo $this->db_inventory->last_query();
            if(!$qry){
                $this->sys_error_db("select supplier error", $this->db_inventory->last_query());
                exit;
            }
            $row_after = $qry->row_array();
            $sql = "SELECT *
                    FROM `supplieraddress`
                    WHERE
                    SupplierAddressSupplierID = ? AND 
                    SupplierAddressIsActive = 'Y'";
            $qry = $this->db_inventory->query($sql, array($id));
            if($qry){
                $row_after['address'] = $qry->result_array();
            }else{
                $this->sys_error_db("select supplier address error", $this->db_inventory->last_query());
                exit;
            }

            $sql = "SELECT *
                    FROM `suppliercontactperson`
                    WHERE
                    SupplierContactPersonSupplierID = ? AND 
                    SupplierContactPersonIsActive = 'Y'";
            $qry = $this->db_inventory->query($sql, array($id));
            if($qry){
                $row_after['contact'] = $qry->result_array();
            }
            else{
                $this->sys_error_db("select supplier contact error", $this->db_inventory->last_query());
                exit;
            }

            $data_log_after = $row_after;

            $sql = "INSERT INTO supplier_log (
                SupplierLogSupplierID,
                SupplierLogStatus,
                SupplierLogJSONBefore,
                SupplierLogJSONAfter,
                SupplierLogUserID,
                SupplierLogCreated
            )
            VALUES(
                ?,?,?,?,?,NOW()
            )";
            $qry = $this->db_inventory_log->query($sql, array($id,'ADD',NULL,json_encode($data_log_after),$userid));
            if(!$qry){
                $this->sys_error_db("insert log error", $this->db_inventory_log->last_query());
                exit;
            }

            $result = array(
                "message" => ''
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function edit(){
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $userid = $this->sys_user['M_UserID'];
            $prm = $this->sys_input;

            $name = "";
            if (isset($prm['name'])) {
                $name = trim($prm["name"]);
            }
            $phone = "";
            if (isset($prm['phone'])) {
                $phone = trim($prm["phone"]);
            }
            $email = "";
            if (isset($prm['email'])) {
                $email = trim($prm["email"]);
            }
            $website = "";
            if (isset($prm['website'])) {
                $website = trim($prm["website"]);
            }

            $id = $prm['id'];

            $sql = "SELECT supplier.*, '' as address, '' as contact 
                    FROM supplier 
                    WHERE SupplierID = ? ";
            $qry = $this->db_inventory->query($sql, array($id));
            //echo $this->db_inventory->last_query();
            if(!$qry){
                $this->sys_error_db("select supplier error", $this->db_inventory->last_query());
                exit;
            }
            $row = $qry->row_array();
            $sql = "SELECT *
                    FROM `supplieraddress`
                    WHERE
                    SupplierAddressSupplierID = ? AND 
                    SupplierAddressIsActive = 'Y'";
            $qry = $this->db_inventory->query($sql, array($id));
            if($qry){
                $row['address'] = $qry->result_array();
            }else{
                $this->sys_error_db("select supplier address error", $this->db_inventory->last_query());
                exit;
            }

            $sql = "SELECT *
                    FROM `suppliercontactperson`
                    WHERE
                    SupplierContactPersonSupplierID = ? AND 
                    SupplierContactPersonIsActive = 'Y'";
            $qry = $this->db_inventory->query($sql, array($id));
            if($qry){
                $row['contact'] = $qry->result_array();
            }
            else{
                $this->sys_error_db("select supplier contact error", $this->db_inventory->last_query());
                exit;
            }

            $data_log_before = $row;

            $this->db_inventory->trans_start();
            $this->db_inventory->trans_strict(FALSE); 
            
            $last_id = 0;
            $sql = "UPDATE supplier SET 
                    SupplierName = ?,
                    SupplierPhone = ?,
                    SupplierEmail = ?,
                    SupplierWebsite = ?,
                    SupplierLastUpdated = NOW(),
                    SupplierUserID = ?
                    WHERE
                    SupplierID = ?";
            $qry = $this->db_inventory->query($sql, array($name,$phone,$email,$website,$userid,$prm['id']));
            if(!$qry){
                //echo $this->db_inventory->last_query();
                $this->sys_error_db("edit supplier error", $this->db_inventory->last_query());
                exit;
            }

            $sql = "UPDATE supplieraddress SET SupplierAddressIsActive = 'N' WHERE SupplierAddressSupplierID = ?";
            $qry = $this->db_inventory->query($sql, array($id));
            if(!$qry){
                //echo $this->db_inventory->last_query();
                $this->sys_error_db("update N address error", $this->db_inventory->last_query());
                exit;
            }
            if(count($prm['address'])>0){
                foreach ($prm['address'] as $key => $value) {
                    $type = trim($value['type']);
                    $postcode = trim($value['postcode']);
                    $description = trim($value['description']);
                    $note = trim($value['note']);
                    if(intval($value['id']) > 0){
                        $sql = "UPDATE supplieraddress SET
                                SupplierAddressType = ?,
                                SupplierAddressPostCode = ?,
                                SupplierAddressDescription = ?,
                                SupplierAddressNote = ?,
                                SupplierAddressUserID = ?,
                                SupplierAddressIsActive = 'Y',
                                SupplierAddressLastUpdated = NOW()
                                WHERE
                                SupplierAddressID = ?";
                        $qry = $this->db_inventory->query($sql, array( $type, $postcode, $description, $note, $userid, $value['id']));
                        if(!$qry){
                            echo $this->db_inventory->last_query();
                            $this->sys_error_db("edit supplier address error", $this->db_inventory->last_query());
                            exit;
                        }
                    }else{
                        $sql = "INSERT INTO supplieraddress(
                            SupplierAddressSupplierID,
                            SupplierAddressType,
                            SupplierAddressPostCode,
                            SupplierAddressDescription,
                            SupplierAddressNote,
                            SupplierAddressUserID,
                            SupplierAddressCreated,
                            SupplierAddressLastUpdated
                        )
                        VALUES(
                            ?,?,?,?,?,?,NOW(),NOW()
                        )";
                        $qry = $this->db_inventory->query($sql, array($prm['id'], $type, $postcode, $description, $note, $userid));
                        if(!$qry){
                            $this->sys_error_db("save supplier address error", $this->db_inventory->last_query());
                            exit;
                        }
                    }
                    
                }
            }


            $sql = "UPDATE suppliercontactperson SET SupplierContactPersonIsActive = 'N' WHERE SupplierContactPersonSupplierID = ?";
            $qry = $this->db_inventory->query($sql, array($id));
            if(!$qry){
                //echo $this->db_inventory->last_query();
                $this->sys_error_db("update N contact error", $this->db_inventory->last_query());
                exit;
            }
            if(count($prm['contact'])>0){
                foreach ($prm['contact'] as $key => $value) {
                    $name = trim($value['name']);
                    $email = trim($value['email']);
                    $mobile = trim($value['mobile']);

                    if(intval($value['id']) > 0){
                        $sql = "UPDATE suppliercontactperson SET
                                SupplierContactPersonName = ?,
                                SupplierContactPersonEmail = ?,
                                SupplierContactPersonMobile = ?,
                                SupplierContactPersonLastUpdated = NOW(),
                                SupplierContactPersonIsActive = 'Y',
                                SupplierContactPersonUserID = ?
                                WHERE
                                SupplierContactPersonID = ?";
                        $qry = $this->db_inventory->query($sql, array($name, $email, $mobile, $userid, $value['id']));
                        if(!$qry){
                            $this->sys_error_db("save supplier contact error", $this->db_inventory->last_query());
                            exit;
                        }
                    }else{
                        $sql = "INSERT INTO suppliercontactperson(
                                    SupplierContactPersonSupplierID,
                                    SupplierContactPersonName,
                                    SupplierContactPersonEmail,
                                    SupplierContactPersonMobile,
                                    SupplierContactPersonUserID,
                                    SupplierContactPersonLastUpdated,
                                    SupplierContactPersonCreated
                                )
                                VALUES(
                                    ?,?,?,?,?,NOW(),NOW()
                                )";
                        $qry = $this->db_inventory->query($sql, array($prm['id'], $name, $email, $mobile, $userid));
                        if(!$qry){
                            echo  $this->db_inventory->last_query();
                            $this->sys_error_db("save supplier contact error", $this->db_inventory->last_query());
                            exit;
                        }
                    }
                    
                }
            }

            $this->db_inventory->trans_complete();

            $sql = "SELECT supplier.*, '' as address, '' as contact 
                    FROM supplier 
                    WHERE SupplierID = ? ";
            $qry = $this->db_inventory->query($sql, array($id));
            //echo $this->db_inventory->last_query();
            if(!$qry){
                $this->sys_error_db("select supplier error", $this->db_inventory->last_query());
                exit;
            }
            $row_after = $qry->row_array();
            $sql = "SELECT *
                    FROM `supplieraddress`
                    WHERE
                    SupplierAddressSupplierID = ? AND 
                    SupplierAddressIsActive = 'Y'";
            $qry = $this->db_inventory->query($sql, array($id));
            if($qry){
                $row_after['address'] = $qry->result_array();
            }else{
                $this->sys_error_db("select supplier address error", $this->db_inventory->last_query());
                exit;
            }

            $sql = "SELECT *
                    FROM `suppliercontactperson`
                    WHERE
                    SupplierContactPersonSupplierID = ? AND 
                    SupplierContactPersonIsActive = 'Y'";
            $qry = $this->db_inventory->query($sql, array($id));
            if($qry){
                $row_after['contact'] = $qry->result_array();
            }
            else{
                $this->sys_error_db("select supplier contact error", $this->db_inventory->last_query());
                exit;
            }

            $data_log_after = $row_after;

            
            $sql = "INSERT INTO supplier_log (
                        SupplierLogSupplierID,
                        SupplierLogStatus,
                        SupplierLogJSONBefore,
                        SupplierLogJSONAfter,
                        SupplierLogUserID,
                        SupplierLogCreated
                    )
                    VALUES(
                        ?,?,?,?,?,NOW()
                    )";
            $qry = $this->db_inventory_log->query($sql, array($prm['id'],'EDIT',json_encode($data_log_before),json_encode($data_log_after),$userid));
            if(!$qry){
                $this->sys_error_db("insert log error", $this->db_inventory_log->last_query());
                exit;
            }

            $result = array(
                "message" => ''
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function delete()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $userid = $this->sys_user['M_UserID'];
            $param = $this->sys_input;
            $id = "";
            if (isset($param['id'])) {
                $id = intval($param["id"]);
            }
            if ($id == "" || !$id) {
                $error = array(
                    "message" => "id is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }

            $this->db_inventory->trans_start();
            $this->db_inventory->trans_strict(FALSE); 

            $sql = "UPDATE supplier
                    SET SupplierIsActive = 'N',
                        SupplierLastUpdated = now(),
                        SupplierUserID = ?
                    WHERE SupplierID = ?
                    ";
            $qry = $this->db_inventory->query($sql, [$userid,$id]);
            if(!$qry){
               
                $this->sys_error_db("delete supplier error", $this->db_inventory->last_query());
                exit;
            }else{
                $sql = "UPDATE supplieraddress
                        SET SupplierAddressIsActive = 'N',
                            SupplierAddressLastUpdated = now(),
                            SupplierAddressUserID = ?
                        WHERE
                            SupplierAddressSupplierID = ?
                            ";
                $qry = $this->db_inventory->query($sql, [$userid,$id]);
                if(!$qry){
                    $this->sys_error_db("delete supplier address error", $this->db_inventory->last_query());
                    exit;
                }

                $sql = "UPDATE suppliercontactperson
                        SET SupplierContactPersonIsActive = 'N',
                            SupplierContactPersonLastUpdated = now(),
                            SupplierContactPersonUserID = ?
                        WHERE
                            SupplierContactPersonSupplierID = ?
                            ";
                $qry = $this->db_inventory->query($sql, [$userid,$id]);
                if(!$qry){
                    $this->sys_error_db("delete supplier contact error", $this->db_inventory->last_query());
                    exit;
                }
            }

            $this->db_inventory->trans_complete();

            $sql = "SELECT supplier.*, '' as address, '' as contact 
                    FROM supplier 
                    WHERE SupplierID = ? ";
            $qry = $this->db_inventory->query($sql, array($id));
            //echo $this->db_inventory->last_query();
            if(!$qry){
                $this->sys_error_db("select supplier error", $this->db_inventory->last_query());
                exit;
            }
            $row_after = $qry->row_array();
            $sql = "SELECT *
                    FROM `supplieraddress`
                    WHERE
                        SupplierAddressSupplierID = ? AND 
                        SupplierAddressIsActive = 'Y'";
            $qry = $this->db_inventory->query($sql, array($id));
            if($qry){
                $row_after['address'] = $qry->result_array();
            }else{
                $this->sys_error_db("select supplier address error", $this->db_inventory->last_query());
                exit;
            }

            $sql = "SELECT *
                    FROM `suppliercontactperson`
                    WHERE
                        SupplierContactPersonSupplierID = ? AND 
                        SupplierContactPersonIsActive = 'Y'";
            $qry = $this->db_inventory->query($sql, array($id));
            if($qry){
                $row_after['contact'] = $qry->result_array();
            }
            else{
                $this->sys_error_db("select supplier contact error", $this->db_inventory->last_query());
                exit;
            }

            $data_log_after = $row_after;

            
            $sql = "INSERT INTO supplier_log (
                        SupplierLogSupplierID,
                        SupplierLogStatus,
                        SupplierLogJSONBefore,
                        SupplierLogJSONAfter,
                        SupplierLogUserID,
                        SupplierLogCreated
                    )
                    VALUES(
                        ?,?,?,?,?,NOW()
                    )";
            $qry = $this->db_inventory_log->query($sql, array($id,'DELETE',NULL,json_encode($data_log_after),$userid));
            if(!$qry){
                $this->sys_error_db("insert log error", $this->db_inventory_log->last_query());
                exit;
            }
            $result = array(
                "message" => ''
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function get_companies(){
        try{
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $sql = "SELECT CompanyID as id,
                    CompanyName as name,
                    M_BranchName as description
                    FROM company
                    JOIN $this->db_onex.m_branch ON CompanyM_BranchID = M_BranchID 
                    WHERE
                    CompanyIsActive = 'Y'";
            $query = $this->db_inventory->query($sql);
                
            if ($query) { 
                $row = $query->result_array();

                /*$sql = "SELECT CompanyAddressID as id,
                        CompanyAddressLabel as name,
                        CompanyAddressDescription as description
                        FROM companyaddress
                        WHERE
                        CompanyAddressCompanyID = ? AND CompanyAddressIsActive = 'Y'";
                $query = $this->db_inventory->query($sql,array($row['id']));
                $data_address = $query->result_array();
                $row['address'] = $data_address;*/
                    
                

                $result = array("records" => $row);
                $this->sys_ok($result);
            } 
            else {
                $this->sys_error_db("expedition rows",$this->db_inventory);
                exit;
            }
        
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function select_item()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $userid = $this->sys_user['M_UserID'];
            $prm = $this->sys_input;
            $sql = "UPDATE itembranch SET ItemBranchIsActive = 'N', ItemBranchUserID = ?,ItemBranchLastUpdated = NOW()  
                    WHERE 
                    ItemBranchItemID = ? AND ItemBranchM_CompanyID = ? AND ItemBranchIsActive = 'Y'";
            $qry =  $this->db_inventory->query($sql,array($userid,$prm['item_id'],$prm['company']['id']));
            //echo $this->db_inventory->last_query();
            if(!$qry){
                $this->sys_error_db("update is active exist error", $this->db_inventory_log->last_query());
                exit;
            }
            $data = array(
                'ItemBranchM_CompanyID' => $prm['company']['id'] ,
                'ItemBranchItemID' =>$prm['item_id'],
                'ItemBranchCreated' =>date("Y-m-d H:i:s"),
                'ItemBranchLastUpdated' =>date("Y-m-d H:i:s"),
                'ItemBranchUserID' => $userid 
            );
             
            $save = $this->db_inventory->insert('itembranch', $data);
            if(!$save){
                $this->sys_error_db("save failed", $this->db_inventory_log->last_query());
                exit;
            }
            $result = array(
                "message" => 'save success'
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function unselected_item()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $userid = $this->sys_user['M_UserID'];
            $prm = $this->sys_input;
            $sql = "UPDATE itembranch SET ItemBranchIsActive = 'N', ItemBranchUserID = ?,ItemBranchLastUpdated = NOW()  
                    WHERE 
                    ItemBranchItemID = ? AND ItemBranchM_CompanyID = ? AND ItemBranchIsActive = 'Y'";
            $qry =  $this->db_inventory->query($sql,array($userid,$prm['item_id'],$prm['company']['id']));
            //echo $this->db_inventory->last_query();
            if(!$qry){
                $this->sys_error_db("update is active exist error", $this->db_inventory_log->last_query());
                exit;
            }
            
            $result = array(
                "message" => 'save success'
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
   
}