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(){
        // $config = [
        //     'delivery_frequency' => 'monthly',
        //     'timezone'           => 'UTC+05',
        //     'monthly_day'        => [2,3,4],
        //     'monthly_time'       => '09:00:00',
        // ];
        // $config = [
        //     'delivery_frequency' => 'daily',
        //     'timezone'           => 'UTC+05',
        //     'daily_time'       => '09:00:00',
        // ];
        // $config = [
        //     'delivery_frequency' => 'weekly',
        //     'timezone'           => 'UTC+05',
        //     'weekly_day'        => [1],
        //     'weekly_time'       => '09:00:00',
        // ];
        // $config = [
        //     'delivery_frequency' => 'quarterly',
        //     'timezone'           => 'UTC+05',
        //     'quarterly_month'        =>'1',
        //     'quarterly_day'        =>'1',
        //     'quarterly_time'       => '09:00:00',
        // ];
        // $config = [
        //     'delivery_frequency' => 'yearly',
        //     'timezone'           => 'UTC+05',
        //     'yearly_month'        =>[1,2],
        //     'yearly_day'        =>'1',
        //     'yearly_time'       => '09:00:00',
        // ];

        // $next = common::calculateNextRunTime($config);
        //echo $next->format('Y-m-d H:i:s');

        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if ($operate == "parity_id"){
            //search  parity id
            if(_isCustomerLogin()){
                //$ocean_contact_id = _getContactIDHandNew($_SESSION["ONLINE_USER"], 'public');
                $ocean_contact_id = _getCompanyContactHandNew($_SESSION["ONLINE_USER"]);
                $air_contact_id = _getAirContactID('public');

                $all_id = "ALL;".$ocean_contact_id;
                if (utils::endWith($ocean_contact_id,";")){
                    $all_id .=$air_contact_id;
                } else {
                    $all_id .=";".$air_contact_id;
                }
                $arr = explode(';', $all_id);
                $unique_arr = array_unique($arr);
            }else{
                $unique_arr= ['ALL'];
            }
            
            $option = array();
            foreach($unique_arr as $temp){
                $option[] =array("label"=>$temp,"value"=>$temp);
            }
            common::echo_json_encode(200, $option);
            exit();
        }

        /**
         * 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";
                }
            }

            if(!empty($_POST['party_id'])){
                if($_POST['party_id'] != 'ALL'){
                    $sqlWhere .= " and '".common::check_input($_POST['party_id'])."' = any(party_ids)";
                }
            }

            if(_isCustomerLogin()){
                $ocean_contact_id = _getCompanyContactHandNew($_SESSION["ONLINE_USER"]);
                $air_contact_id = _getAirContactID('public');

                $all_id = $ocean_contact_id;
                if (utils::endWith($ocean_contact_id,";")){
                    $all_id .=$air_contact_id;
                } else {
                    $all_id .=";".$air_contact_id;
                }
                $all_id = strtolower($all_id);
                $unique_arr = explode(';', $all_id);
                //构建安全的数组字面量用于 PostgreSQL 的 && 交集判断
                $sqlWhere_party_id = '';
                if (!empty($unique_arr)) {
                    // 转义每个 ID 防注入
                    $escaped_ids = array();
                    foreach($unique_arr as $id){
                        $id = trim($id);
                        if (empty($id))
                            continue;
                        $escaped_ids[] =  "'" .common::check_input($id) . "'";
                    }
                    if(!empty($escaped_ids)){
                        $array_str = 'ARRAY[' . implode(',', $escaped_ids) . ']';
                        $sqlWhere_party_id = " or party_ids && $array_str ";
                    }
                }
                //Group Name 判断
                $company = _getCompanyHandNew($_SESSION["ONLINE_USER"],'public');
                $company = strtolower($company);
                $aa = explode(";", $company);

                $sqlWhere_group_name = '';
                if(!empty($aa)){
                    $escaped_company = array();
                    foreach ($aa as $value) {
                        $value = trim($value);
                        if (empty($value))
                            continue;
                        $escaped_company[] = "'" . common::check_input($value) . "'";
                    }
                    if(!empty($escaped_company)){
                        $array_str = 'ARRAY[' . implode(',', $escaped_company) . ']';
                        $sqlWhere_group_name = "  or group_names && $array_str";
                    }
                }

                $sqlWhere .= " and ( access_type = 'All Users' ".$sqlWhere_party_id . " ".$sqlWhere_group_name .")";
            } 
            $sqlWhere .= " and (system_account IS NULL  OR array_length(system_account, 1) = 0  OR '".common::check_input(_getLoginName())."' = any(system_account))";


            $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']);
                //这两个不能配置查询和过滤
                if($_field_group_name == 'Container info' || $_field_group_name == 'Milestone'
                        ||$_field_code == 'Other reference No.'  || $_field_code == 'Shipper Zip Code' || $_field_code == 'Consignee Zip Code' ){
                    $_is_filter_enabled = "false";
                } else {
                    $_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 serial_no,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();

            $CustomFiled = "";
            $reportFiled = common::excuteListSql("select * from public.kln_report_field_config where template_serial_no = '".$serial_no."' 
                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");
                    $field_display_name = $filed['field_display_name'];
                    $field_display_name = strtolower($field_display_name);
                    $field_display_name = preg_replace('/[^a-z0-9]+/', '_', $field_display_name); // 非字母数字 → _
                    $field_display_name = trim($field_display_name, '_');
                    $filtersList[] = array(
                        "label"=>$filed['field_display_name_user'],
                        "field"=>$field_display_name,
                        "field_real"=>$filed['field_display_name'],
                        "type"=>$type,
                        "data_type"=>$filed['data_type'],
                        "value"=>[],"options"=>[]);
                }
                if($filed['is_sort_enabled'] == 't'){
                    $sortByOptions[] = $filed['field_db'];
                }

                //用户自定义字段
                if($filed['field_type'] == 'Custom'){
                    $CustomFiled = " , '".$filed['custom_fixed_value']."' AS \"".$filed['field_display_name_user']."\"";
                }

                $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,level from public.kln_report_template where serial_no = '".$serial_no."'");
            $sqlManage = common::excuteObjectSql("select sql,count_sql,level from public.kln_report_sql_manage where level = '".$reportFiled['level']."'");
            $report_sql = $sqlManage["sql"];
            $count_sql = $sqlManage["count_sql"];

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

            $report_sql = str_replace('<{klnOceanSearchKLN}>', $filterSQLArr['klnOceanSearchKLN'], $report_sql);
            $report_sql = str_replace('<{ocItemSearchKLN}>', $filterSQLArr['ocItemSearchKLN'], $report_sql);
            $report_sql = str_replace('<{CustomFiled}>', $CustomFiled, $report_sql);
            $report_sql = str_replace('<{vvSearchKLN}>', $filterSQLArr['vvSearchKLN'], $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;
            
            $rc = $_POST ['rc'];
            if ($rc == -1) {
                $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."' order by id ");
            $data = 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;
                $_reportFiled['is_enabled'] = $_reportFiled['is_enabled'] == 't' ? true : false;
                $data[] = $_reportFiled;
            }    
            common::echo_json_encode(200,array("msg"=>"success","data" => $data));
            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'];
                $_is_enabled = $_tempFieldsList['is_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,is_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','$_is_enabled',now());";
            }
            if (!empty($sql)){
                common::excuteUpdateSql($sql);
                $data = array("msg" =>"success");
            }

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

        if ($operate == "report_schedule"){
            $serial_no = common::check_input($_POST ['serial_no']); 
            $schedule = common::excuteObjectSql("select schedule_order_field,validity_type,valid_from,valid_to,
                    data_reference_field,data_range_type,dynamic_start_offset,dynamic_end_offset,fixed_start_date,fixed_end_date,
                    delivery_frequency,TO_CHAR(daily_time, 'HH24:MI') as daily_time,
                    array_to_json(weekly_day) as weekly_days_json,TO_CHAR(weekly_time, 'HH24:MI') as weekly_time,
                    array_to_json(monthly_day) as monthly_day_json,TO_CHAR(monthly_time, 'HH24:MI') as monthly_time,
                    quarterly_month,quarterly_day,TO_CHAR(quarterly_time, 'HH24:MI') as quarterly_time,
                    array_to_json(yearly_month) as yearly_month_json,yearly_day, TO_CHAR(yearly_time, 'HH24:MI') as yearly_time,
                    timezone,email_recipients
                from public.kln_report_template where  serial_no = '$serial_no'");

            $schedule["weekly_days"] = json_decode($schedule["weekly_days"],true);
            $schedule["monthly_day"] = json_decode($schedule["monthly_day"],true);
            $schedule["yearly_month"] = json_decode($schedule["yearly_month"],true);

            //处理成VUE 识别的格式
            $data = array();
            $data["validityPeriod"] = array("type"=>$schedule["validity_type"],"startDate"=>$schedule["valid_from"],"endDate"=>$schedule["valid_to"]);
            if($schedule["data_range_type"] == "dynamic_rolling"){
                $data["timeRange"] = array("fieldType"=>$schedule["data_reference_field"],"type"=>$schedule["data_range_type"],
                    "startDate"=>$schedule["dynamic_start_offset"],"endDate"=>$schedule["dynamic_end_offset"]);
            }else{
                $data["timeRange"] = array("fieldType"=>$schedule["data_reference_field"],"type"=>$schedule["data_range_type"],
                    "startDate"=>$schedule["fixed_start_date"],"endDate"=>$schedule["fixed_end_date"]);
            }
            $time = "";
            $week = array();
            $month = array();
            $quarterMonth = "";
            $day = array();
            $delivery_frequency = $schedule["delivery_frequency"];
            if($delivery_frequency ==  'daily'){
                $time =  $schedule["daily_time"];
            }elseif($delivery_frequency ==  'weekly'){
                $week= json_decode($schedule["weekly_days_json"],true);
                $time =  $schedule["weekly_time"];
            }elseif($delivery_frequency ==  'monthly'){
                $monthlyDay= json_decode($schedule["monthly_day_json"],true);
                $time =  $schedule["monthly_time"];
            }elseif($delivery_frequency ==  'quarterly'){
                $quarterMonth =  $schedule["quarterly_month"];
                $day =  $schedule["quarterly_day"];
                $time =  $schedule["quarterly_time"];
            }elseif($delivery_frequency ==  'yearly'){
                $month= json_decode($schedule["yearly_month_json"],true);
                $day = $schedule["yearly_day"];
                $time =  $schedule["yearly_time"];
            }
            $data["deliveryFrequency"] = array("emailRecipients"=>$schedule["email_recipients"],"orderBy"=>$schedule["schedule_order_field"],
                "timezone"=>$schedule["timezone"],"deliveryFrequency"=>$schedule["delivery_frequency"],
                "scheduleDetails" =>array("time"=>$time,"week"=>$week,"month"=>$month,"monthlyDay" =>$monthlyDay,"quarterMonth"=>$quarterMonth,"day"=>$day));
            common::echo_json_encode(200,array("msg"=>"success","showData" => $data));
            exit;
        }

        if ($operate == "report_schedule_search"){
            $dataReturn = array();
            $serial_no = common::check_input($_POST ['serial_no']);
            $data_reference_field =  strtolower(common::check_input($_POST ['fieldType']));
            $data_range_type =  common::check_input($_POST ['type']);
            if($data_range_type == "dynamic_rolling"){
                $dynamic_start_offset =  common::check_input($_POST ['startDate']);
                $dynamic_end_offset =  common::check_input($_POST ['endDate']);
                $fixed_start_date =  "null";
                $fixed_end_date =  "null";
            }else{
                $dynamic_start_offset =  "null";
                $dynamic_end_offset =  "null";
                $fixed_start_date =  common::check_input($_POST ['startDate']);
                $fixed_end_date =  common::check_input($_POST ['endDate']);
            }
            $schedule_order_field =  common::check_input($_POST ['orderBy']);


            //查询列名
            $tableColumns = array();
            $CustomFiled = "";
            $reportFiled = common::excuteListSql("select * from public.kln_report_field_config where template_serial_no = '".$serial_no."' 
                and is_enabled = true order by id ");
            foreach($reportFiled as $filed){
                $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;

                //用户自定义字段
                if($filed['field_type'] == 'Custom'){
                    $CustomFiled = " , '".$filed['custom_fixed_value']."' AS \"".$filed['field_display_name_user']."\"";
                }
            }
            $dataReturn['tableColumns'] =  $tableColumns;
            
            
            //形成sql
            $schedule_search = common::excuteObjectSql("select schedule_order_field,data_reference_field,data_range_type,
                    dynamic_start_offset,dynamic_end_offset,
                    fixed_start_date,fixed_end_date,
                    report_sql,count_sql
                from public.kln_report_template where  serial_no = '$serial_no'");

            $report_sql = $schedule_search["report_sql"];
            $count_sql = $schedule_search["count_sql"];

            //$klnOceanSearchKLN = " where " . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            $klnOceanSearchKLN = " where  created_time > CURRENT_DATE - INTERVAL '1 years' and  " . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            $orderby = "";
            if(!empty($data_range_type)){
                //代表有用户设置的查询参数
                if($data_range_type == "dynamic_rolling"){
                    $klnOceanSearchKLN .= " and ".$data_reference_field." >= CURRENT_DATE - ".$dynamic_start_offset." and  ".$data_reference_field." <= CURRENT_DATE + ".$dynamic_end_offset."";
                } else {
                    if(!empty($fixed_start_date)){
                        $klnOceanSearchKLN .= " and ".$data_reference_field." >= '".common::usDate2sqlDate($fixed_start_date)."'";
                    }
                    if(!empty($fixed_end_date)){
                        $klnOceanSearchKLN .= " and  ".$data_reference_field." <=  '".common::usDate2sqlDate($fixed_end_date)."'";
                    }
                }
                if(!empty($schedule_order_field)){
                    $orderby = " order by \"".strtoupper($schedule_order_field)."\"";
                }
            } else {
                if($schedule_search["data_range_type"] == "dynamic_rolling"){
                    $field_db = strtolower($schedule_search["data_reference_field"]);
                    $dynamic_start_offset_db = empty($schedule_search["dynamic_start_offset"]) ? 0 : $schedule_search["dynamic_start_offset"];
                    $dynamic_end_offset_db = empty($schedule_search["dynamic_end_offset"]) ? 0 : $schedule_search["dynamic_end_offset"];
                    $klnOceanSearchKLN .= " and ".$field_db." >= CURRENT_DATE - ".$dynamic_start_offset_db." and  ".$field_db." <= CURRENT_DATE + ".$dynamic_end_offset_db."";
                } else {
                    $field_db = strtolower($schedule_search["data_reference_field"]);
                    $fixed_start_date_db = $schedule_search["fixed_start_date"];
                    $fixed_end_date_db = $schedule_search["fixed_end_date"];

                    if(!empty($fixed_start_date_db)){
                        $klnOceanSearchKLN .= " and ".$field_db." >= '".$fixed_start_date_db."'";
                    }
                    if(!empty($fixed_end_date_db)){
                        $klnOceanSearchKLN .= " and  ".$field_db." <=  '".$fixed_end_date_db."'";
                    }
                }

                if(!empty($schedule_search["schedule_order_field"])){
                    $orderby = " order by \"".strtoupper($schedule_search["schedule_order_field"])."\"";
                }
            }

            $count_sql = str_replace('<{klnOceanSearchKLN}>', $klnOceanSearchKLN, $count_sql);
            $count_sql = str_replace('<{ocItemSearchKLN}>', " ", $count_sql);
            $count_sql = str_replace('<{vvSearchKLN}>', " ", $count_sql);

            $report_sql = str_replace('<{klnOceanSearchKLN}>', $klnOceanSearchKLN, $report_sql);
            $report_sql = str_replace('<{ocItemSearchKLN}>', " ", $report_sql);
            $report_sql = str_replace('<{CustomFiled}>', $CustomFiled, $report_sql);
            $report_sql = str_replace('<{vvSearchKLN}>', " ", $report_sql);

            //查询sql
            $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;
           
            $rc = $_POST ['rc'];
            if ($rc == -1) {
                $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}>', $orderby, $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(),
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
            }
            $dataReturn['tableData'] =  $arrTmp;
            common::echo_json_encode(200, $dataReturn);
            exit();
            exit;
        }

        if ($operate == "report_schedule_save"){
            $serial_no = common::check_input($_POST ['serial_no']);

            $validity_type =  common::check_input($_POST ['validityPeriodType']);
            $valid_from = "null";
            $valid_to = "null";
            if($validity_type == "custom"){
                $valid_from =  "'".common::check_input($_POST ['validityPeriodStartDate'])."'";
                $valid_to =  "'".common::check_input($_POST ['validityPeriodEndDate'])."'";
            }

            $data_reference_field =  strtolower(common::check_input($_POST ['fieldType']));
            $data_range_type =  common::check_input($_POST ['type']);
            if($data_range_type == "dynamic_rolling"){
                $dynamic_start_offset =  "'".common::check_input($_POST ['startDate'])."'";
                $dynamic_end_offset =  "'".common::check_input($_POST ['endDate'])."'";
                $fixed_start_date =  "null";
                $fixed_end_date =  "null";
            }else{
                $dynamic_start_offset =  "null";
                $dynamic_end_offset =  "null";
                $fixed_start_date =  "'".common::check_input($_POST ['startDate'])."'";
                $fixed_end_date =  "'".common::check_input($_POST ['endDate'])."'";
            }
            $schedule_order_field =  common::check_input($_POST ['orderBy']);
            if(empty($schedule_order_field)){
                $schedule_order_field = "ETD";
            }

            $email_recipients =  common::check_input($_POST ['emailRecipients']);

            $timezone =  common::check_input($_POST ['timezone']);
            $daily_time = "null";
            $weekly_day = "null";
            $weekly_time = "null";
            $monthly_day = "null";
            $monthly_time = "null";
            $quarterly_month= "null";
            $quarterly_day= "null";
            $quarterly_time = "null";
            $yearly_month = "null";
            $yearly_day = "null";
            $yearly_time = "null";
            $delivery_frequency =  common::check_input($_POST ['deliveryFrequency']);
            if($delivery_frequency ==  'daily'){
                $daily_time =  "'".common::check_input($_POST ['time'])."'";
            }elseif($delivery_frequency ==  'weekly'){
                $weekly_day= common::toPgTextArrayLiteral($_POST ['week']);
                $weekly_time = "'".common::check_input($_POST ['time'])."'";
            }elseif($delivery_frequency ==  'monthly'){
                $monthly_day= common::toPgTextArrayLiteral($_POST ['monthlyDay']);
                $monthly_time = "'".common::check_input($_POST ['time'])."'";
            }elseif($delivery_frequency ==  'quarterly'){
                //检查数组是否有值
                $quarterly_month =  "'".common::check_input($_POST ['quarterMonth'])."'";
                $quarterly_day =  "'".common::check_input($_POST ['day'])."'";
                $quarterly_time = "'".common::check_input($_POST ['time'])."'";
            }elseif($delivery_frequency ==  'yearly'){
                $yearly_month= common::toPgTextArrayLiteral($_POST ['yearlyMonth']);
                $yearly_day = "'".common::check_input($_POST ['day'])."'";
                $yearly_time = "'".common::check_input($_POST ['time'])."'";
            }
            $sql = "";
            $klnOceanSearchKLN = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            if (!empty($serial_no)){
                $updateSqlSet = " schedule_order_field = '".$schedule_order_field."', 
                                  validity_type = '".$validity_type."', 
                                  valid_from = ".$valid_from.", 
                                  valid_to = ".$valid_to.", 

                                  data_reference_field = '".$data_reference_field."', 
                                  data_range_type = '".$data_range_type."', 
                                  dynamic_start_offset = ".$dynamic_start_offset.", 
                                  dynamic_end_offset = ".$dynamic_end_offset.", 
                                  fixed_start_date = ".$fixed_start_date.", 
                                  fixed_end_date = ".$fixed_end_date.", 

                                  delivery_frequency = '".$delivery_frequency."', 
                                  daily_time = ".$daily_time.", 
                                  weekly_day = ".$weekly_day.", 
                                  weekly_time = ".$weekly_time.", 
                                  monthly_day = ".$monthly_day.", 
                                  monthly_time = ".$monthly_time.", 
                                  quarterly_month = ".$quarterly_month.", 
                                  quarterly_day = ".$quarterly_day.", 
                                  quarterly_time = ".$quarterly_time.", 
                                  yearly_month = ".$yearly_month.", 
                                  yearly_day = ".$yearly_day.", 
                                  yearly_time = ".$yearly_time.",
                                  timezone = '".$timezone."',
                                  email_recipients = '".$email_recipients."',
                                  next_run_time = null,
                                  search_extend_hand = '".common::check_input($klnOceanSearchKLN)."',
                                  modify_by = '"._getLoginName()."',
                                  update_time = now()";
                //代表update 
                $sql .= "update public.kln_report_template set ".$updateSqlSet."
                    where serial_no = '$serial_no';";
            } 
            if (!empty($sql)){
                common::excuteUpdateSql($sql);
                //执行成功后，处理next_run_time 
                $config = common::excuteObjectSql("select delivery_frequency,daily_time,
                        array_to_json(weekly_day) as weekly_day_json,weekly_time,
                        array_to_json(monthly_day) as monthly_day_json,monthly_time,
                        quarterly_month,quarterly_day,quarterly_time,
                        array_to_json(yearly_month) as yearly_month_json,yearly_day,yearly_time,timezone 
                    from public.kln_report_template where  serial_no = '$serial_no';");

                $config["weekly_day"] = json_decode($config["weekly_day_json"],true);
                $config["monthly_day"] = json_decode($config["monthly_day_json"],true);
                $config["yearly_month"] = json_decode($config["yearly_month_json"],true); 
                $next = common::calculateNextRunTime($config);
                $next_run_time = $next->format('Y-m-d H:i:s');   
                common::excuteUpdateSql("update public.kln_report_template set next_run_time = '$next_run_time' where  serial_no = '$serial_no';");
                
                $data = array("msg" =>"success");
            }
            common::echo_json_encode(200,$data);                
            exit();
        }
    }

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

        $vvSearchKLN = " where 1=1 ";
        $klnOceanSearchKLN = " where  created_time > CURRENT_DATE - INTERVAL '1 years' and  " . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        //$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'], $klnVipDb);
                if($key !== false){
                    //找到给key
                    $temp_sql_where = "";
                    if ($fiter['data_type'] == "string"){
                        $temp_sql_where .= " and ".$key." = '". common::check_input($_POST[$fiter['field']])."'"; 
                    } elseif ($fiter['data_type'] == "number"){
                        $temp_arr = $_POST [$fiter['field']];
                        if(!empty($temp_arr[0])){
                            $temp_sql_where .= " and ".$key."::integer >= '". common::check_input($temp_arr[0])."'"; 
                        }
                        if(!empty($temp_arr[1])){
                            $temp_sql_where .= " and ".$key."::integer <= '". common::check_input($temp_arr[1])."'"; 
                        } 
                    } elseif ($fiter['data_type'] == "date"){
                        $temp_arr = $_POST [$fiter['field']];
                        if(!empty($temp_arr[0])){
                            $date_from = common::check_input(common::usDate2sqlDate($temp_arr[0]) . ' 00:00:00');
                            $temp_sql_where .= " and ".$key." >= '". $date_from."'"; 
                        }
                        if(!empty($temp_arr[1])){
                            $date_to = common::check_input(common::usDate2sqlDate($temp_arr[1]) . ' 23:59:59');
                            $temp_sql_where .= " and ".$key." <= '". $date_to."'"; 
                        }
                    }

                    if(utils::startWith($key,"oc") || utils::startWith($key,"oi")){
                        $ocItemSearchKLN .= $temp_sql_where;
                    } else {
                        $klnOceanSearchKLN .= $temp_sql_where;
                    }
                } else {
                    //获取POST name  用户去别名得字段，放在vvSearchKLN上
                    $_post_field = $fiter['field'];
                    $_post_field = strtolower($_post_field);
                    $_post_field = preg_replace('/[^a-z0-9]+/', '_', $_post_field); // 非字母数字 → _
                    $_post_field = trim($_post_field, '_');

                    if ($fiter['data_type'] == "string"){
                        $vvSearchKLN .= " and \"".$fiter['field_real']."\" ilike '%". common::check_input($_POST [$_post_field])."%'"; 
                    } elseif ($fiter['data_type'] == "number"){
                        $temp_arr = $_POST [$_post_field];
                        if(!empty($temp_arr[0])){
                            $vvSearchKLN .= " and \"".$fiter['field_real']."\"::integer >= '". common::check_input($temp_arr[0])."'"; 
                        }
                        if(!empty($temp_arr[1])){
                            $vvSearchKLN .= " and \"".$fiter['field_real']."\"::integer <= '". common::check_input($temp_arr[1])."'"; 
                        }  
                    } elseif ($fiter['data_type'] == "date"){
                        $temp_arr = $_POST [$_post_field];
                        //先判断日期字符串是否为空,这里则有做 是因为sql 整合了柜子315时间，和 milestone的时间, 只能text转date
                        if(!empty($temp_arr[0])){
                            $date_from = common::check_input(common::usDate2sqlDate($temp_arr[0]) . ' 00:00:00');
                            $vvSearchKLN .= " and COALESCE(\"".$fiter['field_real']."\",''::text)<> ''::text ";  
                            $vvSearchKLN .= " and to_timestamp(\"".$fiter['field_real']."\", 'MM/DD/YYYY HH24:MI:SS') >= '". $date_from."'"; 
                        }
                        if(!empty($temp_arr[1])){
                            $date_to = common::check_input(common::usDate2sqlDate($temp_arr[1]) . ' 23:59:59');
                            $vvSearchKLN .= " and COALESCE(\"".$fiter['field_real']."\",''::text)<> ''::text ";  
                            $vvSearchKLN .= " and to_timestamp(\"".$fiter['field_real']."\", '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;
    }
}

?>