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 = common::deCode($_REQUEST['serial_no'], 'D');
            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,field_level,field_type,field_db,field_display_name,
                        field_display_name_user,data_type,custom_value_type,
                        custom_fixed_value,is_filter_enabled,is_sort_enabled 
                    from kln_report_field_config  where template_serial_no = '$serial_no' order by order_index");
                foreach($reportField as  $_reportField){
                    $_reportField['is_filter_enabled'] = $_reportField['is_filter_enabled'] == 't' ? true : false;
                    $_reportField['is_sort_enabled'] = $_reportField['is_sort_enabled'] == '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 = common::deCode($_POST['serial_no'], 'D');
            $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'];

            //字段配置信息
            $field_ids = $_POST['field_id'];
            $field_level = $_POST['field_level'];
            $field_type = $_POST['field_type'];
            $field_db = $_POST['field_db'];
            $field_code = $_POST['field_code'];
            $display_name = $_POST['display_name'];
            $data_type = $_POST['data_type'];
            $value_type = $_POST['value_type'];
            $fixed_value = $_POST['fixed_value'];
            $is_filter_enabled = $_POST['is_filter_enabled'];
            $is_sort_enabled = $_POST['is_sort_enabled'];

            $report_sql = common::excuteOneSql("select sql from public.kln_report_sql_manage where level = '$level'");
            $sql = "";
            if (!empty($serial_no)){
                $updateSqlSet = " report_sql = '$report_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)) {
                    $pgsql_array = '"' . implode('","', array_map(fn($v) => str_replace("'", "''", $v), $party_ids)) . '"';
                    $updateSqlSet.= ", party_ids = '{".$pgsql_array."}'::text[]";
                }
                if (!empty($group_names)) {
                    $pgsql_array = '"' . implode('","', array_map(fn($v) => str_replace("'", "''", $v), $group_names)) . '"';
                    $updateSqlSet.= ", group_names = '{".$pgsql_array."}'::text[]";
                }
                if (!empty($field_ids)) {
                    $pgsql_array = '"' . implode('","', array_map(fn($v) => str_replace("'", "''", $v), $field_ids)) . '"';
                    $updateSqlSet.= ", field_ids = '{".$pgsql_array."}'::INTEGER[]";
                }
                //代表update 
                $sql .= "update public.kln_report_template set ".$updateSqlSet."
                    where serial_no = '$serial_no';";
            } else {
                $party_ids_filed = null;
                if (!empty($party_ids)) {
                    $pgsql_array = '"' . implode('","', array_map(fn($v) => str_replace("'", "''", $v), $party_ids)) . '"';
                    $party_ids_filed= "'{".$pgsql_array."}'::text[]";
                }

                $group_names_filed = null;
                if (!empty($group_names)) {
                    $pgsql_array = '"' . implode('","', array_map(fn($v) => str_replace("'", "''", $v), $group_names)) . '"';
                    $group_names_filed = "'{".$pgsql_array."}'::text[]";
                }

                $ids_filed = null;
                if (!empty($field_ids)) {
                    $pgsql_array = '"' . implode('","', array_map(fn($v) => str_replace("'", "''", $v), $field_ids)) . '"';
                    $ids_filed = "'{".$pgsql_array."}'::INTEGER[]";
                }

                $serial_no = common::uuid();
                $sql .= "INSERT INTO public.kln_report_template(
                                serial_no,name, description, level, field_ids, access_type, report_sql,
                                party_ids, group_names, create_by, created_time, modify_by, update_time )
                        VALUES ('$serial_no','$name', '$description', '$level', $ids_filed, '$access_type', '$report_sql'
                                $party_ids_filed, $group_names_filed , '"._getLoginName()."', now(), '"._getLoginName()."', now());";
            }

            $errmsg = "";
            //柜子是先删除，后添加
            $sql .= "delete from  public.kln_report_field_config where template_serial_no = '$serial_no';";
            foreach($field_ids as $key =>$_field_id){
                $_field_level = $field_level[$key];
                $_ield_type = $field_type[$key];
                $_field_db = $field_db[$key];
                $_field_code = $field_code[$key];
                $_display_name = $display_name[$key];
                $_data_type = $data_type[$key];
                $_value_type = $value_type[$key];
                $_fixed_value = $fixed_value[$key];
                $_is_filter_enabled = $is_filter_enabled[$key];
                $_is_sort_enabled = $is_sort_enabled[$key];
                $sql .=  "INSERT INTO public.kln_report_field_config(
                            template_serial_no, field_id, field_level, field_type, field_db, 
                            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', '$_ield_type', '$_field_db', 
                            '$_field_code', '$_display_name', '$_data_type', '$_value_type', 
                            '$_fixed_value', '$_is_filter_enabled', '$_is_sort_enabled',now());";

            }
            if (empty($errmsg) && !empty($sql)){
                common::excuteUpdateSql($sql);
                $data = array("msg" =>"success");
            } else {
                $data = array("msg" =>$errmsg);
            }  
            common::echo_json_encode(200,$data);                
            exit(); 
        }

        if ($operate == "delete"){
            $serial_no = common::deCode($_POST['a'], 'D');
            $sql = "delete from public.kln_report_template where serial_no = '$serial_no';";
            $sql .= "delete from public.kln_report_field_config where template_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']); 
            
            common::excuteListSql("select * from kln_report_field_config where template_serial_no = '".$serial_no."' and field_type = 'System' order by id ");
           

            //第一次进来,把查询放到一起了
            $cp = 10;
            $ps = 1;
            $rc = $_POST ['rc'];
            if ($rc == - 1) {
                $sql = "select count(*) from public.kln_report_template where " ;
                $rc = common::excuteOneSql($sql);
            }
            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                $sql = "select name,description from public.kln_report_template where " ;
                $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();
            }
        }
    }
}

?>