k_online_backend/service/column.class.php

<?php

if (!defined('IN_ONLINE')) {
    exit('Access Denied');
}

class column {

    private static $_column;

    public static function getInstance() {
        if (!self::$_column) {
            $c = __CLASS__;
            self::$_column = new $c;
        }
        return self::$_column;
    }

    public function settingDisplayForVIPReport() {
        $data = array();
        //判断是否重置
        $level = common::check_input($_REQUEST['level']);
        $serial_no = common::check_input($_REQUEST['serial_no']);
        $defualt = common::check_input($_REQUEST['default']);

        if(strtolower($level) == strtolower("Shipment Level")){
            //这个是kln_report_field level :"Shipment level" 不变
            $levelSQlWhere = " is_enabled = true and level = 'Shipment level'";
            $rightSqlwhere = " and is_enabled = true and shipment_level_default = true order by shipment_level_default_order";
            $leftSqlwhere = " and is_enabled = true and coalesce(shipment_level_default,false) = false";
        }elseif(strtolower($level) == strtolower("Container Level")){
            $levelSQlWhere = "is_enabled = true and level in ('Shipment level','Container Level')";
            $rightSqlwhere = " and is_enabled = true and ctnr_level_default = true order by ctnr_level_default_order";
            $leftSqlwhere = " and coalesce(ctnr_level_default,false) = false";
        }else {
            $levelSQlWhere = " is_enabled = true and 1=1 ";
            $rightSqlwhere = " and is_enabled = true and item_level_default = true order by item_level_default_order";
            $leftSqlwhere = " and is_enabled = true and coalesce(item_level_default,false) = false";
        }
 
        $kln_report_field_str = " id as ids,
            code as \"fieldBb\",
            display_name as field, 
            display_name as label,
            level as \"fieldLevel\",
            'System' as \"fieldType\",
            data_type as \"dataType\",
            is_field_data_mapping as \"isFieldDataMapping\",
            group_name as \"groupName\" ";
        $kln_report_field_config = " field_id as ids,
            field_db as \"fieldBb\",
            field_display_name as field,
            field_display_name as label,
            field_level as \"fieldLevel\",
            field_type as \"fieldType\",
            data_type as \"dataType\",
            (select is_field_data_mapping from public.kln_report_field where display_name = field_display_name limit 1) as \"isFieldDataMapping\",
            field_group_name as \"groupName\" ";

        $allData = common::excuteListSql("select ".$kln_report_field_str." from public.kln_report_field where ".$levelSQlWhere." order by id");
        if ($defualt == 'true'){
                //代表第一次添加或者用户恢复默认顺序
                $groupSetting = common::excuteListSql("select  ".$kln_report_field_str." from public.kln_report_field where  ".$levelSQlWhere."  ".$rightSqlwhere);
                $dataLeft = common::excuteListSql("select   ".$kln_report_field_str." from public.kln_report_field where  ".$levelSQlWhere."  ".$leftSqlwhere." order by id");
        }else{
                // $groupSetting = common::excuteListSql("select ".$kln_report_field_config." from public.kln_report_field_config 
                //     where template_serial_no = '$serial_no' and field_type = 'System' order by id");
                // $dataLeft = common::excuteListSql("select ".$kln_report_field_str."from public.kln_report_field 
                //     where lower(level) = '".strtolower($level)."' and id != ALL(select field_ids from public.kln_report_template where serial_no = '$serial_no' and field_type = 'System') 
                //         order by id");
        }

        $children['All'] = array();
        $children['Reference No.'] = array();
        $children['General'] = array();
        $children['CO2 Emission'] = array();
        $children['Parties'] = array();
        $children['Packing'] = array();
        $children['Time'] = array();
        $children['Places'] = array();
        $children['Transportation'] = array();
        $children['Milestone'] = array();
        $children['Container info'] = array();
        $children['Container Status'] = array();
        $children['Item info'] = array();
        $children['Others'] = array();

        $groupColumns_all = $this->allGroupVipReprotColumns($allData,$children);
        //总分组的检索要去掉右边的已经选中的部分
        $groupColumns_left = $this->allGroupVipReprotColumns($dataLeft,$children);
        
        $data['GroupColumnsAll'] = $groupColumns_all;
        $data['GroupColumnsLeft'] = $groupColumns_left;
        $data['GroupColumnsRight'] = $groupSetting;

        //false特殊逻辑
        if ($defualt == 'false'){
            $data['GroupColumnsAll'] = $groupColumns_all;
            $data['GroupColumnsLeft'] = $groupColumns_all;
            $data['GroupColumnsRight'] = array();
        }

        common::echo_json_encode(200,$data);
        exit;
    }

    public function settingDisplay($model_name, $url, $type = null) {
        $data = array();
        //判断是否重置
        if(!empty($_REQUEST['reset']) && $_REQUEST['reset'] == "yes"){
            // common::excuteUpdateSql("update ra_online_search_display_config_cso set ids = '' 
            //      where lower(login_name)='" . strtolower(_getLoginName()) . "' and lower(model_name) = '" . strtolower($model_name) . "'");
        }
        if (empty($type)) {
            if($model_name <> 'destination_delivery_shipment_search'){
                //预先初始化，空的也要保留
                $children['All'] = array();
                $children['Reference No.'] = array();
                $children['General'] = array();
                $children['Parties'] = array();
                $children['Packing'] = array();
                $children['Time'] = array();
                $children['Places'] = array();
                $children['Transportation'] = array();
                $children['Others'] = array();
            }else{
                //预先初始化，空的也要保留.只需要ALL
                $children['All'] = array();
            }

            $page = common::getPageMapping($model_name);
            $allData = $this->getDisplayColumnAllReomveDefault($model_name);
            $groupColumns_all = $this->allGroupColumns($allData,$children,$page);

            //总分组的检索要去掉右边的已经选中的部分
            $dataLeft = $this->getDisplayColumn($model_name,false);
            $groupColumns_left = $this->allGroupColumns($dataLeft,$children,$page);

            $setting = $this->getDisplayColumn($model_name);
            $groupSetting = $this->groupColumnsSetting($setting,$page);
            
            $data['GroupColumnsAll'] = $groupColumns_all;
            $data['GroupColumnsLeft'] = $groupColumns_left;
            $data['GroupColumnsRight'] = $groupSetting;
        } 
        //search目前页面没有
        //$data['search_setting'] = search::getInstance()->getDisplayColumn($model_name);
        //$data['search_diff'] = search::getInstance()->getDisplayColumn($model_name, FALSE);
        $data['model_name'] = $model_name;
        $data['url'] = $url;
        common::echo_json_encode(200,$data);
        exit;
    }

    //改为从新的配置表和配置信息，与旧的分开
    public function getDisplayColumn($model_name, $isin = true) {
        //测试服务器，改为UAT字段
        if(utils::startWith($_SERVER["HTTP_HOST"],"online-beta.kln.com")){
            $model_name = "UAT_".$model_name;
        }

        $rs = null;
        $sql = "select ids from public.ra_online_search_display_config_cso where lower(login_name)='" . strtolower(_getLoginName()) . "' and lower(model_name) = '" . strtolower($model_name) . "'";
        $rs = common::excuteOneSql($sql);
        if(!empty($_REQUEST['reset']) && $_REQUEST['reset'] == "yes"){
            $rs = array();
        }
        if (empty($rs)) {
            $sql = "select array_to_string(array(select id from public.ra_online_search_display_cso where active = true and default_display = true and lower(model_name) = '" . strtolower($model_name) . "'";
            if (_isCustomerLogin())
                $sql .= " and customer_display = true";
            $sql .= " order by default_order asc), ',')";
            $rs = common::excuteOneSql($sql);
        }
        if ($isin) {
            $sql = $this->_getSql($rs);
            $rss = common::excuteListSql($sql);
        } else {
            $sql = "SELECT id, display_name as name, database_column_name, column_width,group_name,
                        TRIM('_' FROM REGEXP_REPLACE(LOWER(display_name), '[^a-z0-9]+', '_', 'g')) AS cleaned_field_name
                    FROM public.ra_online_search_display_cso WHERE active = true and lower(model_name) = '" . strtolower($model_name) . "' and id not in ($rs)";
            if (_isCustomerLogin())
                $sql .= ' and customer_display=true';
            $rss = common::excuteListSql($sql);
        }
        //旧版本去除新加__字段
        $action = strtolower($_REQUEST['action']);
        if (Soure == 'TopOcean' &&  empty(common::isNewVersion())
                && ($action =="ocean_order" || $action == "ocean_booking" || $action == "containers")){
            $rss = common::removeTopOceanOldVersionSpecialField($rss);
        }
        
        return $rss;
    }

    public function _getSql($ids) {
        $ids_arr = explode(',', $ids);
        $sql = "";
        foreach ($ids_arr as $value) {
            if (!empty($value)) {
                if (empty($sql)) {
                    $sql .= "SELECT id, display_name as name,  database_column_name,column_width,group_name,
                                TRIM('_' FROM REGEXP_REPLACE(LOWER(display_name), '[^a-z0-9]+', '_', 'g')) AS cleaned_field_name 
                            FROM public.ra_online_search_display_cso where active=true and id = " . $value;
                    if (_isCustomerLogin())
                        $sql .= ' and CUSTOMER_DISPLAY = true';
                } else {
                    $sql .= " union all SELECT id, display_name as name, database_column_name,column_width,group_name,
                                TRIM('_' FROM REGEXP_REPLACE(LOWER(display_name), '[^a-z0-9]+', '_', 'g')) AS cleaned_field_name  
                            FROM public.ra_online_search_display_cso where active=true and id = " . $value;
                    if (_isCustomerLogin())
                        $sql .= ' and CUSTOMER_DISPLAY = true';
                }
            }
        }
        return $sql;
    }

    public function saveSettingDisplay($newid, $search_id, $model_name) {
        //测试服务器，改为UAT字段
        if(utils::startWith($_SERVER["HTTP_HOST"],"online-beta.kln.com")){
            $model_name = "UAT_".$model_name;
        }
		
        global $db;
        $login_name = _getLoginName();
        $db->StartTrans();
        if ($this->checkUserDisplaySetting($model_name) === TRUE) {
            $sql = "UPDATE public.ra_online_search_display_config_cso SET ids = '" . common::check_input($newid) . "' WHERE lower(model_name) = '" . common::check_input(strtolower($model_name)) . "' AND lower(login_name) = '" . common::check_input(strtolower($login_name)) . "'";
        } else {
            $sql = "INSERT INTO public.ra_online_search_display_config_cso (LOGIN_NAME, MODEL_NAME, ids) VALUES ('" . common::check_input($login_name) . "', '" . common::check_input($model_name) . "', '" . common::check_input($newid) . "')";
        }
        //search 部分的数据不做查询
        //search::getInstance()->saveSettingDisplay($search_id, $model_name);
        common::excuteUpdateSql($sql);
        $db->CompleteTrans();
    }

    /*
     * check user had setting display
    */
    public function checkUserDisplaySetting($model_name) {
        $login_name = _getLoginName();
        $sql = "select ids from public.ra_online_search_display_config_cso where lower(login_name)='" . strtolower($login_name) . "' and lower(model_name) = '" . strtolower($model_name) . "'"; //check login_name is setting
        $rs = common::excuteObjectSql($sql);
        if (!empty($rs)) {
            return TRUE;
        }
        return FALSE;
    }

    public function getSearchSql($model_name) {
        //测试服务器，改为UAT字段
        if(utils::startWith($_SERVER["HTTP_HOST"],"online-beta.kln.com")){
            $model_name = "UAT_".$model_name;
        }

        $sql = "";
        $ss = "SELECT database_column_name, display_name FROM public.ra_online_search_display_cso WHERE lower(model_name) = '" . strtolower($model_name) . "'";
        if (_isCustomerLogin())
            $ss .= ' and customer_display=true';
        $rss = common::excuteListSql($ss);
        
        //旧版本去除新加__字段
        $action = strtolower($_REQUEST['action']);
        $rss = common::removeTopOceanOldVersionSpecialField($rss);
        
        //不和以前重命名有冲突，改为database_column_name，display 显示由固定配置设置
        foreach ($rss as $v) {
            if ($sql == '')
                $sql .=  $v['database_column_name'];
            else
                $sql .= ',' . $v['database_column_name'];
        }
        return ' ' . $sql . ' ';
    }

    //search 查询
    public function getSearchSqlForDisplay($model_name) {
        //测试服务器，改为UAT字段
        if(utils::startWith($_SERVER["HTTP_HOST"],"online-beta.kln.com")){
            $model_name = "UAT_".$model_name;
        }

        $sql = "";
        $ss = "SELECT database_column_name, display_name FROM public.ra_online_search_display_cso WHERE lower(model_name) = '" . strtolower($model_name) . "'";
        if (_isCustomerLogin())
            $ss .= ' and customer_display=true';
        $rss = common::excuteListSql($ss);
        
        //旧版本去除新加__字段
        $action = strtolower($_REQUEST['action']);
        $rss = common::removeTopOceanOldVersionSpecialField($rss);
        
        foreach ($rss as $v) {
            if($v['database_column_name'] == "other_refenrence_no" && $model_name = "Ocean_Search"){
                continue;
            }
            if (empty($v['database_column_name'])) {
                if ($sql == '')
                    $sql .= '\'' . $v['display_name'] . '\'';
                else
                    $sql .= ', \'' . $v['display_name'] . '\'';
            }else {
                if ($sql == '')
                    $sql .= $v['database_column_name'] . ' as "' . $v['display_name'] . '"';
                else
                    $sql .= ', ' . $v['database_column_name'] . ' as "' . $v['display_name'] . '"';
            }
        }
        return ' ' . $sql . ' ';
    }

    public function getDisplayColumnAll($model_name) {
        $sql = "SELECT id, display_name as name, database_column_name as db,database_column_name, column_width,group_name, 
                TRIM('_' FROM REGEXP_REPLACE(LOWER(display_name), '[^a-z0-9]+', '_', 'g')) AS cleaned_field_name
            FROM public.ra_online_search_display_cso 
                WHERE default_display = true and active=true and lower(model_name) = '" . strtolower($model_name) . "'";
        if (_isCustomerLogin())
            $sql .= ' and customer_display=true';
        $sql .= " order by order_by,display_name";
        $rss = common::excuteListSql($sql);
        return $rss;
    }

    public function getDisplayColumnAllReomveDefault($model_name) {
        //测试服务器，改为UAT字段
        if(utils::startWith($_SERVER["HTTP_HOST"],"online-beta.kln.com")){
            $model_name = "UAT_".$model_name;
        }

        $sql = "SELECT id, display_name as name, database_column_name as db,database_column_name, column_width,group_name,
                TRIM('_' FROM REGEXP_REPLACE(LOWER(display_name), '[^a-z0-9]+', '_', 'g')) AS cleaned_field_name 
            FROM public.ra_online_search_display_cso 
                WHERE  active=true and lower(model_name) = '" . strtolower($model_name) . "'";
        if (_isCustomerLogin())
            $sql .= ' and customer_display=true';
        $sql .= " order by order_by";
        $rss = common::excuteListSql($sql);
        return $rss;
    }
   
    public function tableColumns($type,$column){
        $tableColumns = array();
        foreach ($column as $key => $rs) {
            $temp = array();
            $temp['field'] = $rs['database_column_name'];
            $temp['title'] = $rs['name'];
            $spconfig = $this->specialTableConfig($type);
            if (!empty($spconfig[$rs['database_column_name']]['type'])){
                $temp['type'] = $spconfig[$rs['database_column_name']]['type'];

            }else{
                $temp['type'] = "normal";
            }
            //formatter
            if (!empty($spconfig[$rs['database_column_name']]['formatter'])){
                $temp['formatter'] = $spconfig[$rs['database_column_name']]['formatter'];
                if($temp['formatter'] == "number"){
                    $temp['digits'] = $spconfig[$rs['database_column_name']]['digits'];
                }
            }else{
                $temp['formatter'] = "";
            }

            //这两个的field 不用as display  
            if(!($type == "Destination_Delivery_Search" || $type == "destination_delivery_shipment_search")){
                $temp['field'] = $rs['name'];
            }
           
            //$temp['title'] = $rs['cleaned_field_name'];
            $page = common::getPageMapping($type);
            $temp['cleaned_field_name'] = $page .'.'.$rs['cleaned_field_name'];
            $tableColumns[] = $temp;
        }
        return $tableColumns;
    }

    public function groupColumnsSetting($column,$page){
        $groupSetting = array();
        foreach ($column as $key => $rs) {
            $temp = array();
            $temp['ids'] = $rs['id'];
            $temp['field'] = $rs['database_column_name'];
            $temp['label'] = $rs['name'];
            $temp['cleaned_field_name'] = $page .'.'.$rs['cleaned_field_name'];
            $groupSetting[] = $temp;
        }
        return $groupSetting;
    }

    public function specialTableConfig($type){
        if ($type == "Booking_Search"){
            return array("booking_no" =>array("type" =>"link"),
            "h_bol" =>array("type" =>"link"),
            "mode" =>array("type" =>"mode"),
            "transport_mode" =>array("type" =>"mode"),
            "status" =>array("type" =>"status"),
            "created_time" =>array("formatter" =>"dateTime"),
            "confirmation_time" =>array("formatter" =>"dateTime"),
            "f_etd" =>array("formatter" =>"date"),
            "m_eta" =>array("formatter" =>"date"),
            "qty" =>array("formatter" =>"number","digits"=>0),
            "piece_count" =>array("formatter" =>"number","digits"=>3),
            "weight" =>array("formatter" =>"number","digits"=>3));
        }
        if ($type == "Ocean_Search"){
            return array("booking_no" =>array("type" =>"link"),
            "h_bol" =>array("type" =>"link"),
            "mode" =>array("type" =>"mode"),
            "transport_mode" =>array("type" =>"mode"),
            "status" =>array("type" =>"status"),
            "created_time" =>array("formatter" =>"dateTime"),
            "etd" =>array("formatter" =>"date"),
            "eta" =>array("formatter" =>"date"),
            "qty" =>array("formatter" =>"number","digits"=>0),
            "piece_count" =>array("formatter" =>"number","digits"=>3),
            "weight" =>array("formatter" =>"number","digits"=>3),
            "cbm" =>array("formatter" =>"number","digits"=>3));
        }
        if ($type == "Operation_Search"){
            return array("operation_time" =>array("formatter" =>"dateTime"));
        }
        if ($type == "Robot_Chat_Search"){
            return array("request_time" =>array("formatter" =>"dateTime"),
                         "response_time" =>array("formatter" =>"dateTime"));
        }
        if ($type == "Robot_API_Search"){
            return array("request_time" =>array("formatter" =>"dateTime"),
                         "response_time" =>array("formatter" =>"dateTime"),
                        "request_id" =>array("type" =>"link"));
        }
        if ($type == "Destination_Delivery_Search"){
            return array("delivery_date" =>array("formatter" =>"date"),
                        "created_time" =>array("formatter" =>"date"),
                        "status" =>array("type" =>"status"),
                        "h_bol" =>array("type" =>"multiple_link"),
                        "date_range" =>array("formatter" =>"range"));
        }
        if ($type == "destination_delivery_shipment_search"){
            return array("eta" =>array("formatter" =>"date"),
                        "ata" =>array("formatter" =>"date"),
                        "date_range" =>array("type" =>"recommend"),
                        "packing_list" =>array("type" =>"download"),
                        "commercial_invoice" =>array("type" =>"download"));
        }
    }

    /**
     * 通用返回setting 分组数据
     */
    public function allGroupColumns($allData,$children,$page){
        foreach ($allData as $key => $data) {
            //固定ALl的分组
            if (array_key_exists("All", $children)) {
                $temp = $children['All'];
                $temp[] = array("ids"=>$data['id'],"label"=>$data['name'],"field"=>$data['database_column_name'],"cleaned_field_name"=>$page.'.'.$data['cleaned_field_name']);
                $children['All'] = $temp;
            } 
            if (array_key_exists($data['group_name'], $children)) {
                $temp = $children[$data['group_name']];
                $temp[] = array("ids"=>$data['id'],"label"=>$data['name'],"field"=>$data['database_column_name'],"cleaned_field_name"=>$page.'.'.$data['cleaned_field_name']);
                $children[$data['group_name']] = $temp;
            } 
        }

        $groupColumns  = array();
        foreach ($children as $k => $ch) {
            $field_display_name = strtolower($k);
            $field_display_name = preg_replace('/[^a-z0-9]+/', '_', $field_display_name); // 非字母数字 → _
            $field_display_name = trim($field_display_name, '_');
            $groupColumns[] = array("name"=>$k,"cleaned_field_name"=>$page.'.'.$field_display_name,"children" =>$ch);
        }
        return $groupColumns;
    }

    /**
     * 通用返回setting vip_reprot 分组数据
     */
    public function allGroupVipReprotColumns($allData,$children){
        foreach ($allData as $key => $data) {
            //固定ALl的分组
            if (array_key_exists("All", $children)) {
                $temp = $children['All'];
                $temp[] = $data;
                $children['All'] = $temp;
            } 
            if (array_key_exists($data['groupName'], $children)) {
                $temp = $children[$data['groupName']];
                $temp[] = $data;
                $children[$data['groupName']] = $temp;
            } 
        }

        $groupColumns  = array();
        foreach ($children as $k => $ch) {
            $groupColumns[] = array("name"=>$k,"children" =>$ch);
        }
        return $groupColumns;
    }
}

?>