k_online_backend/service/report.class.php

<?php

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

/**
 * Description of operation_log
 *
 * @author Administrator
 */
class report {

    private static $_report;

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

    /**
     * report 配置
    */
    public function report_config(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        /**
         * report的配置查询，
        */
        if ($operate == "search"){
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 100;
            if (empty($cp))
                $cp = 1;

            $sqlWhere = "1=1";
            $text_search = $_POST['text_search'];
            if (!empty($text_search)){
                $sqlWhere .= " and (lower(name) like '%".strtolower($text_search)."%')";
            }
            if(!empty($_POST['application_scope'])){
                $pplication_scope =  $_POST['application_scope'] =='all' ? "All Users" : "Specific Users";
                $sqlWhere .= " and access_type ='". common::check_input($pplication_scope)."'";
            }
            if(!empty($_POST['is_active'])){
                if($_POST['is_active'] == 't'){
                    $sqlWhere .= " and is_active = true";
                }else{
                    $sqlWhere .= " and is_active = false";
                }
            }

            $rc = $_POST ['rc'];
            if ($rc == -1) {
                $sql = "select count(*) from public.kln_report_template where " .$sqlWhere;
                $rc = common::excuteOneSql($sql);
            }
            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                $sql = "select * from public.kln_report_template where " .$sqlWhere;
                $sql .= " order by id desc limit " . $ps . " offset " . ($cp - 1) * $ps;
                $rs = common::excuteListSql($sql);
                $arrTmp = array('searchData' => $rs, 
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
                common::echo_json_encode(200,$arrTmp);
                exit();
            }else{
                $arrTmp = array('searchData' => array());
                common::echo_json_encode(200, $arrTmp);
                exit();
            }
        }

        if ($operate == "add"){
            $serial_no = $_REQUEST['serial_no'];
            if(!empty($serial_no)){
                //代表编辑
                $reportMain = common::excuteObjectSql("select *,
                        array_to_json(party_ids) as party_ids_json,
                        array_to_json(group_names) as group_names_json
                    from kln_report_template where serial_no = '$serial_no'");
                
                $reportFields = array();    
                $reportField = common::excuteListSql("select 
                        field_id as ids,
                        field_level as \"fieldLevel\",
                        field_type as \"fieldType\",
                        field_group_name as \"groupName\",
                        field_db as field,
                        field_display_name as title,
                        field_display_name_user as \"displayName\",
                        data_type as \"dataType\",
                        custom_fixed_value as value,
                        is_filter_enabled as \"isFilter\",
                        is_sort_enabled as  \"isSort\"
                    from kln_report_field_config  where template_serial_no = '$serial_no' order by id");
                foreach($reportField as  $_reportField){
                    $_reportField['isFilter'] = $_reportField['isFilter'] == 't' ? true : false;
                    $_reportField['isSort'] = $_reportField['isSort'] == 't' ? true : false;
                    $reportFields[] = $_reportField;
                }

                $reportAccess = array("type"=>$reportMain['access_type'],
                    "partyId"=>json_decode($reportMain['party_ids_json'],true),
                    "groupName"=>json_decode($reportMain['group_names_json'],true));
                $data = array("reportName"=>$reportMain['name'],"reportLevel"=>$reportMain['level'],"reportDescription"=>$reportMain['description'],
                    "reportFields"=>$reportFields,"reportAccess"=>$reportAccess);
            }else{
                $data = array();
            }
            common::echo_json_encode(200,$data);                
            exit(); 
        }
       
        if ($operate == "report_field_load"){
            column::getInstance()->settingDisplayForVIPReport();
        }

        if ($operate == "save"){
            $serial_no = $_POST['serial_no'];
            $name = common::check_input($_POST['report_name']);
            $description = common::check_input($_POST['report_description']);
            $level = common::check_input($_POST['report_level']);
            $access_type = common::check_input($_POST['access_type']);
            $party_ids = $_POST['party_ids'];
            $group_names = $_POST['group_names'];

            //单独处理字段id
            $fieldsList = $_POST['fieldsList'];
            $field_ids = array();
            foreach($fieldsList as $_fieldsList){
                if (!empty($_fieldsList['ids'])){
                    $field_ids[] = $_fieldsList['ids'];
                }
            }

            $report_sql_data = common::excuteObjectSql("select sql,count_sql from public.kln_report_sql_manage where level = '$level'");
            $sql = "";
            if (!empty($serial_no)){
                $updateSqlSet = " report_sql = '".common::check_input($report_sql_data['sql'])."', 
                                  count_sql = '".common::check_input($report_sql_data['count_sql'])."', 
                                  modify_by = '"._getLoginName()."',update_time = now()";
                if (!empty($name)) {
                    $updateSqlSet.= ", name = '$name' ";
                }
                if (!empty($description)) {
                    $updateSqlSet.= ", description = '$description' ";
                }
                if (!empty($level)) {
                    $updateSqlSet.= ", level = '$level' ";
                }
                if (!empty($access_type)) {
                    $updateSqlSet.= ", access_type = '$access_type' ";
                }
                if (!empty($party_ids)) {
                    $party_ids_filed= common::toPgTextArrayLiteral($party_ids);
                    $updateSqlSet.= ", party_ids = $party_ids_filed";
                }
                if (!empty($group_names)) {
                    $group_names_filed = common::toPgTextArrayLiteral($group_names);
                    $updateSqlSet.= ", group_names = $group_names_filed";
                }
                if (!empty($field_ids)) {
                    $ids_filed =  common::toPgTextArrayLiteral($field_ids);
                    $updateSqlSet.= ", field_ids = $ids_filed";
                }
                //代表update 
                $sql .= "update public.kln_report_template set ".$updateSqlSet."
                    where serial_no = '$serial_no';";
            } else {
                $party_ids_filed = "NULL";
                if (!empty($party_ids)) {
                    $party_ids_filed= common::toPgTextArrayLiteral($party_ids);
                }

                $group_names_filed = "NULL";
                if (!empty($group_names)) {
                    $group_names_filed = common::toPgTextArrayLiteral($group_names);
                }

                $ids_filed = "NULL";
                if (!empty($field_ids)) {
                    $ids_filed =  common::toPgTextArrayLiteral($field_ids);
                }

                $serial_no = common::uuid();
                $sql .= "INSERT INTO public.kln_report_template(
                                serial_no,name, description, level, field_ids, access_type, report_sql,count_sql,
                                party_ids, group_names, create_by, created_time, modify_by, update_time )
                        VALUES ('$serial_no','$name', '$description', '$level', $ids_filed, '$access_type',
                                '".common::check_input($report_sql_data['sql'])."','".common::check_input($report_sql_data['count_sql'])."',
                                $party_ids_filed, $group_names_filed , '"._getLoginName()."', now(), '"._getLoginName()."', now());";              
            }
            //先删除，后添加
            $sql .= "delete from  public.kln_report_field_config where template_serial_no = '$serial_no';";
            foreach($fieldsList as $key =>$_tempFieldsList){
                $_field_id = empty($_tempFieldsList['ids'])? "NULL": $_tempFieldsList['ids'];
                $_field_level = common::check_input($_tempFieldsList['fieldLevel']);
                $_field_type = common::check_input($_tempFieldsList['fieldType']);
                $_field_group_name = common::check_input($_tempFieldsList['groupName']);
                $_field_db = common::check_input($_tempFieldsList['field']);
                $_field_code = common::check_input($_tempFieldsList['title']);
                $_display_name = common::check_input($_tempFieldsList['displayName']);
                $_data_type = common::check_input($_tempFieldsList['dataType']);
                $_value_type =  $_field_type == "System" ? "" : (empty($_tempFieldsList['value']) ? "Blank" :"Fixed Value");
                $_fixed_value = common::check_input($_tempFieldsList['value']);
                $_is_filter_enabled = $_tempFieldsList['isFilter'];
                $_is_sort_enabled = $_tempFieldsList['isSort'];
                $sql .=  "INSERT INTO public.kln_report_field_config(
                            template_serial_no, field_id, field_level, field_type, field_db, field_group_name,
                            field_display_name, field_display_name_user, data_type, custom_value_type, 
                            custom_fixed_value, is_filter_enabled, is_sort_enabled, created_time)
                    VALUES ('$serial_no', $_field_id, '$_field_level', '$_field_type', '$_field_db', '$_field_group_name', 
                            '$_field_code', '$_display_name', '$_data_type', '$_value_type', 
                            '$_fixed_value', '$_is_filter_enabled', '$_is_sort_enabled',now());";
            }
            if (!empty($sql)){
                common::excuteUpdateSql($sql);
                $data = array("msg" =>"success");
            }
            common::echo_json_encode(200,$data);                
            exit(); 
        }

        if ($operate == "active"){
            $serial_no =$_POST['serial_no'];
            $is_active = $_POST['is_active'];
            $sql = "update public.kln_report_template set is_active = '$is_active'  where serial_no = '$serial_no';";
            common::excuteUpdateSql($sql);
            $data = array("msg" =>"success");
            common::echo_json_encode(200,$data);                
            exit(); 
        }

    }

    /**
     *  shipment_status_report
    */
    public function shipment_status_report(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if ($operate == "report_search") {
           $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 100;
            if (empty($cp))
                $cp = 1;

            $sqlWhere = "1=1 and is_active = true";
            $text_search = $_POST['text_search'];
            if (!empty($text_search)){
                $sqlWhere .= " and (lower(name) like '%".strtolower($text_search)."%')";
            }
           
            $rc = $_POST ['rc'];
            if ($rc == - 1) {
                $sql = "select count(*) from public.kln_report_template where " .$sqlWhere;
                $rc = common::excuteOneSql($sql);
            }
            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                $sql = "select name,description from public.kln_report_template where " .$sqlWhere;
                $sql .= " order by id desc limit " . $ps . " offset " . ($cp - 1) * $ps;
                $rs = common::excuteListSql($sql);
                $arrTmp = array('searchData' => $rs, 
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
                common::echo_json_encode(200,$arrTmp);
                exit();
            }else{
                $arrTmp = array('searchData' => array());
                common::echo_json_encode(200, $arrTmp);
                exit();
            }
        }

        if ($operate == "report_detail") {
            $serial_no = common::check_input($_POST ['serial_no']); 
            $dataReturn = array();
            $tableColumns = array();
            $filtersList = array();
            $sortByOptions = array();
            $reportFiled = common::excuteListSql("select * from public.kln_report_field_config where template_serial_no = '".$serial_no."' 
                and field_type = 'System' and is_enabled = true order by id ");
            foreach($reportFiled as $filed){
                if($filed['is_filter_enabled'] == 't'){
                    $type = $filed['data_type'] == "string" ? "input" : ($filed['data_type'] == "date" ? "date" : "input");
                    $filtersList[] = array(
                        "label"=>$filed['field_display_name_user'],
                        "field"=>$filed['field_display_name'],
                        "type"=>$type,
                        "data_type"=>$filed['data_type'],
                        "value"=>[],"options"=>[]);
                }
                if($filed['is_sort_enabled'] == 't'){
                    $sortByOptions[] = $filed['field_db'];
                }

                $temp = array();
                $temp['field'] = $filed['field_display_name'];
                $temp['title'] = $filed['field_display_name_user'];
                $temp['type'] = $filed['field_db'] == "Status" ? "status" : "normal";
                $temp['formatter'] = "";
                $tableColumns[] = $temp;
            }
            $dataReturn['tableColumns'] =  $tableColumns;
            $dataReturn['filtersList'] =  $filtersList;
            if(!empty($sortByOptions)){
                $dataReturn['sortBy'] =  array("field"=>$sortByOptions[0],"options"=>$sortByOptions,"order"=>"asc");
            }else{
                $dataReturn['sortBy'] =  array("field"=>"","options"=>[],"order"=>"asc");
            }
            
            $reportFiled = common::excuteObjectSql("select report_sql,count_sql from public.kln_report_template where serial_no = '".$serial_no."'");
            $report_sql = $reportFiled["report_sql"];
            $count_sql = $reportFiled["count_sql"];

            $filterSQLArr = $this->returnFilterSql($filtersList);
            //return array("vvSearchKLN"=>$vvSearchKLN,"klnOceanSearchKLN"=>$klnOceanSearchKLN,"ocItemSearchKLN"=>$ocItemSearchKLN);
            $report_sql = str_replace('<{vvSearchKLN}>', $filterSQLArr['vvSearchKLN'], $report_sql);
            $report_sql = str_replace('<{klnOceanSearchKLN}>', $filterSQLArr['klnOceanSearchKLN'], $report_sql);
            $report_sql = str_replace('<{ocItemSearchKLN}>', $filterSQLArr['ocItemSearchKLN'], $report_sql);
            

            //查询data
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 10;
            if (empty($cp))
                $cp = 1;
           
            if (true) {
                $count_sql = str_replace('<{orderby}>', "", $count_sql);
                error_log($count_sql);
                $rc = common::excuteOneSql($count_sql);
            }
            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                $report_sql = str_replace('<{orderby}>', $this->returnSortBySql(), $report_sql);
                $tmp_search_without_limit = $report_sql;
                $report_sql .= " limit " . $ps . " offset " . ($cp - 1) * $ps;
                error_log($report_sql);

                $rs = common::excuteListSql($report_sql);
                $arrTmp = array('searchData' => $rs, 
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
            }else{
                $arrTmp = array('searchData' => array());
            }
            $dataReturn['tableData'] =  $arrTmp;
            $dataReturn['tmp_search'] =  common::deCode($tmp_search_without_limit, 'E');
            common::echo_json_encode(200, $dataReturn);
            exit();
        }

        /*
         * export excel
        */
        if ($operate == "excel") {
            $sql = common::deCode($_POST['tmp_search'], 'D');
            if(!empty($sql)){
                $rs = common::excuteListSql($sql);
            }
            //去除null
            foreach($rs as $index => $val) {
                foreach($val as $index_2 => $_val) {
                    if(empty($rs[$index][$index_2]) || $rs[$index][$index_2] == null){
                        $rs[$index][$index_2] = "";
                    }
                }
            }
            common::echo_json_encode(200,array("msg"=>"success","Data" => $rs));
            exit;
        }

        if ($operate == "manage_fileds") {
            $serial_no = common::check_input($_POST ['serial_no']); 
            $reportFiled = common::excuteListSql("select * from public.kln_report_field_config 
                where template_serial_no = '".$serial_no."' 
                    and field_type = 'System' order by id ");
            $showData = array();
            $hideData = array();    
            foreach($reportFiled as $_reportFiled){
                $_reportFiled['is_filter_enabled'] = $_reportFiled['is_filter_enabled'] == 't' ? true : false;
                $_reportFiled['is_sort_enabled'] = $_reportFiled['is_sort_enabled'] == 't' ? true : false;
                if($_reportFiled['is_enabled'] == 't'){
                    $showData[] = $_reportFiled;
                }else{
                    $hideData[] = $_reportFiled;
                }
            }    
            common::echo_json_encode(200,array("msg"=>"success","showData" => $showData,"hideData" => $hideData));
            exit;
        }
        if ($operate == "manage_fileds_save") {
            $serial_no = common::check_input($_POST['serial_no']); 
            $fieldsList = $_POST['fieldsList'];
            //先删除，后添加  因为不涉及系统配置字段，全部用kln_report_field_config 字段name
            $sql = "delete from  public.kln_report_field_config where template_serial_no = '$serial_no';";
            foreach($fieldsList as $key =>$_tempFieldsList){
                $_field_id = empty($_tempFieldsList['field_id'])? "NULL": $_tempFieldsList['field_id'];
                $_field_level = common::check_input($_tempFieldsList['field_level']);
                $_field_type = common::check_input($_tempFieldsList['field_type']);
                $_field_group_name = common::check_input($_tempFieldsList['field_group_name']);
                $_field_db = common::check_input($_tempFieldsList['field_db']);
                $_field_code = common::check_input($_tempFieldsList['field_display_name']);
                $_display_name = common::check_input($_tempFieldsList['field_display_name_user']);
                $_data_type = common::check_input($_tempFieldsList['data_type']);
                $_value_type = common::check_input($_tempFieldsList['custom_value_type']);
                $_fixed_value = common::check_input($_tempFieldsList['custom_fixed_value']);
                $_is_filter_enabled = $_tempFieldsList['is_filter_enabled'];
                $_is_sort_enabled = $_tempFieldsList['is_sort_enabled'];
                $sql .=  "INSERT INTO public.kln_report_field_config(
                            template_serial_no, field_id, field_level, field_type, field_db, field_group_name,
                            field_display_name, field_display_name_user, data_type, custom_value_type, 
                            custom_fixed_value, is_filter_enabled, is_sort_enabled, created_time)
                    VALUES ('$serial_no', $_field_id, '$_field_level', '$_field_type', '$_field_db', '$_field_group_name', 
                            '$_field_code', '$_display_name', '$_data_type', '$_value_type', 
                            '$_fixed_value', '$_is_filter_enabled', '$_is_sort_enabled',now());";
            }
            if (!empty($sql)){
                common::excuteUpdateSql($sql);
                $data = array("msg" =>"success");
            }

            common::echo_json_encode(200,array("msg"=>"success","Data" => ''));
            exit;
        }
    }

    /**
     * 根据提交的参数动态的拼接filter sql
     */
    public function returnFilterSql($filtersList){
        $klnOceanDb = common::getReportRealDBFiled("klnOceanDb");
        $ocItemDb = common::getReportRealDBFiled("ocItemDb");

        $vvSearchKLN = " where 1=1 ";
        $klnOceanSearchKLN = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $ocItemSearchKLN = " where 1=1 ";

        foreach($filtersList as $fiter){
            if(!empty($_POST[$fiter['field']])){
                $key = array_search($fiter['field'], $klnOceanDb);
                $ockey = array_search($fiter['field'], $ocItemDb);
                if($key !== false){
                    //找到给key
                    if ($fiter['data_type'] == "string"){
                        $klnOceanSearchKLN .= " and ".$key." = '". common::check_input($_POST[$fiter['field']])."'"; 
                    } elseif ($fiter['data_type'] == "number"){
                        $klnOceanSearchKLN .= " and ".$key." >= '". common::check_input($_POST[$fiter['field']."_from"])."'"; 
                        $klnOceanSearchKLN .= " and ".$key." <= '". common::check_input($_POST[$fiter['field']."_to"])."'"; 
                    } elseif ($fiter['data_type'] == "date"){
                        $date_from = common::check_input(common::usDate2sqlDate($_POST [$fiter['field']."_from"]) . ' 00:00:00');
                        $date_to = common::check_input(common::usDate2sqlDate($_POST [$fiter['field']."_to"]) . ' 23:59:59');
                        $klnOceanSearchKLN .= " and ".$key." >= '". $date_from."'"; 
                        $klnOceanSearchKLN .= " and ".$key." <= '". $date_to."'"; 
                    }
                } elseif ($ockey !== false){
                    //找到给key
                    if ($fiter['data_type'] == "string"){
                        $ocItemSearchKLN .= " and ".$ockey." = '". common::check_input($_POST[$fiter['field']])."'"; 
                    } elseif ($fiter['data_type'] == "number"){
                        $ocItemSearchKLN .= " and ".$ockey." >= '". common::check_input($_POST[$fiter['field']."_from"])."'"; 
                        $ocItemSearchKLN .= " and ".$ockey." <= '". common::check_input($_POST[$fiter['field']."_to"])."'"; 
                    } elseif ($fiter['data_type'] == "date"){
                        $date_from = common::check_input(common::usDate2sqlDate($_POST[$fiter['field']."_from"]) . ' 00:00:00');
                        $date_to = common::check_input(common::usDate2sqlDate($_POST[$fiter['field']."_to"]) . ' 23:59:59');
                        $ocItemSearchKLN .= " and ".$ockey." >= '". $date_from."'"; 
                        $ocItemSearchKLN .= " and ".$ockey." <= '". $date_to."'"; 
                    }
                } else {
                    if ($fiter['data_type'] == "string"){
                        $vvSearchKLN .= " and \"".$fiter['field']."\" ilike '%". common::check_input($_POST [$fiter['field']])."%'"; 
                    } elseif ($fiter['data_type'] == "number"){
                        $vvSearchKLN .= " and \"".$fiter['field']."\" >= '". common::check_input($_POST [$fiter['field']."_from"])."'"; 
                        $vvSearchKLN .= " and \"".$fiter['field']."\" <= '". common::check_input($_POST [$fiter['field']."_to"])."'"; 
                    } elseif ($fiter['data_type'] == "date"){
                        $date_from = common::check_input(common::usDate2sqlDate($_POST [$fiter['field']."_from"]) . ' 00:00:00');
                        $date_to = common::check_input(common::usDate2sqlDate($_POST [$fiter['field']."_to"]) . ' 23:59:59');
                        //先判断日期字符串是否为空,这里则有做 是因为sql 整合了柜子315时间，和 milestone的时间, 只能text转date
                        $vvSearchKLN .= " and COALESCE(\"".$fiter['field']."\",''::text)<> ''::text ";  
                        $vvSearchKLN .= " and to_timestamp(\"".$fiter['field']."\", 'MM/DD/YYYY HH24:MI:SS') >= '". $date_from."'"; 
                        $vvSearchKLN .= " and to_timestamp(\"".$fiter['field']."\", 'MM/DD/YYYY HH24:MI:SS') <= '". $date_to."'"; 
                    }
                }
            }
        }
        
        return array("vvSearchKLN"=>$vvSearchKLN,"klnOceanSearchKLN"=>$klnOceanSearchKLN,"ocItemSearchKLN"=>$ocItemSearchKLN);
    }

    /**
     * 根据提交的参数动态的拼接sort by sql
     */
    public function returnSortBySql(){
        $sortByField = $_POST['sortByField'];
        $sortByOrder = $_POST['sortByOrder'];

        $sort_sql_temp = " ";
        if(!empty($sortByField) && !empty($sortByOrder)){
            $sort_sql_temp = " order by \"".$sortByField."\" ". $sortByOrder;
        }
        return  $sort_sql_temp;
    }
}

?>