漏刻有时多功能php-API接口开发实例

在漏刻有时数据可视化的开发过程中，对于php-api的后端开发，牵涉到数据统计、数据清洗、数据过滤等多种方式，其中环境部署、需求变化、技术迭代等问题，将在本文做集中处理。

1.跨表查询

采用连表查询，即. $db->table('maintain') . " AS a," . $db->table('poi') . " AS b方式，进行不同数据表的关系关联。

  /*06.疑难点位管理*/
    function getDifficult()
    {
        global $db, $res;
        dbc();
        @$poi_address = get_param("poi_address");
        @$poi_sn = get_param("poi_sn");
        @$poi_ins5 = $_GET["poi_ins5"];
        @$p = $_GET['page'] == "" ? 1 : $_GET['page'];//获取用户选择的页码
        @$pagesize = $_GET['limit'] == "" ? 15 : $_GET['limit'];//获取用户选择的每页显示多少条数据
        @$limit = ($p - 1) * $pagesize;//偏移量

        $sql = "select a.main_id,a.poi_id,a.poi_sn,a.poi_name,a.poi_address,a.poi_difficult,a.poi_md,from_unixtime(a.poi_brdown) as poi_brdown,b.poi_id,b.poi_ins1,b.poi_ins2,b.poi_ins3,b.poi_ins4,b.poi_bdlng AS poi_lon,b.poi_bdlat AS poi_lat from " . $db->table('maintain') . " AS a," . $db->table('poi') . " AS b WHERE a.poi_id = b.poi_id";
        $sql .= " AND a.poi_difficult = 1";
        //编号和名称搜索;
        IF ($poi_sn != "") {
            $sql .= " AND (a.poi_name like '%" . $poi_sn . "%' OR a.poi_sn like '%" . $poi_sn . "%')";
        }

        IF ($poi_ins5 != "") {
            $sql .= " AND a.poi_ins5 ='" . $poi_ins5 . "'";
        }
        $sql .= " ORDER BY a.main_id DESC LIMIT " . $limit . "," . $pagesize;
        $row = $db->queryall($sql);

        $sql_c = "select a.main_id,a.poi_id,a.poi_sn,a.poi_name,a.poi_address,a.poi_difficult,from_unixtime(a.poi_brdown) as poi_brdown,b.poi_id,b.poi_ins1,b.poi_ins2,b.poi_ins3,b.poi_ins4 from " . $db->table('maintain') . " AS a," . $db->table('poi') . " AS b WHERE a.poi_id = b.poi_id";
        $sql_c .= " AND a.poi_difficult = 1";
        //编号和名称搜索;
        IF ($poi_sn != "") {
            $sql_c .= " AND (a.poi_name like '%" . $poi_sn . "%' OR a.poi_sn like '%" . $poi_sn . "%')";
        }

        IF ($poi_ins5 != "") {
            $sql_c .= " AND a.poi_ins5 ='" . $poi_ins5 . "'";
        }
        $sql_c .= " ORDER BY a.main_id DESC LIMIT " . $limit . "," . $pagesize;
        $row_c = $db->queryall($sql_c);

        $res['code'] = 0;
        $res['count'] = count($row_c);
        $res["data"] = $row;
        die(json_encode_lockdata($res));
    }

2.CASE … WHEN …用法统计

将数据表同字段分类统计，并输出数据。

 function getPoints()
    {
        global $db, $res;
        dbc();
        $sql = "select COUNT(CASE poi_type WHEN '参保登记' THEN 1 END) AS poi_canbao,COUNT(CASE poi_type WHEN '费用报销' THEN 1 END) AS poi_baoxiao from " . $db->table('poi') . " WHERE 1";
        $row = $db->queryall($sql);
        $res["data"] = $row;
        die(json_encode_lockdata($res));
    }

3.GROUP的用法

进行数据数量统计、求和统计时使用。

  public function getEcharts()
    {
        global $db, $res;
        dbc();
        /*图表数据*/
        $sql = "select poi_area,COUNT(poi_area) AS poi_total from " . $db->table('poi') . " WHERE 1";
        $sql .= " GROUP BY poi_area ORDER BY poi_total DESC";
        $row = $db->queryall($sql);
        $res['code'] = 0;
        $res["data"] = $row;
        die(json_encode_lockdata($res));
    }

4.权限分配

通过读取用去登录权限$_COOKIE['admin_roles']，来设置对应的筛选查询规则。使用$_COOKIE时，一定要做好加密，防止前端窃取进行XSS漏洞注入。根据不同的用户权限，输入不同的数据内容。

public function getZiping()
    {
        global $db, $res;
        dbc();
        @$user_city = get_param("user_city");
        @$user_area = get_param("user_area");
        @$project_name = get_param("project_name");
        @$user_name = get_param("user_name");
        @$zp_status = get_param("zp_status");
        @$p = get_param('page') == "" ? 1 : get_param('page');
        @$pagesize = get_param('limit') == "" ? 15 : get_param('limit');
        @$limit = ($p - 1) * $pagesize;

        //设置筛选条件;
        if ($_COOKIE['admin_roles'] == 0) {//超级管理员
            $filter_condition = " 1";
        } elseif ($_COOKIE['admin_roles'] == 1) {//地市管理员
            $filter_condition = " b.user_city = '" . $_COOKIE['admin_city'] . "'";
        } else {//单位管理
            $filter_condition = " user_depart = " . $_COOKIE['admin_depart'];
        }

        //获取数据;
        $sql = "select a.zp_id,a.user_name,a.project_name,a.zp_status,a.zp_support,b.user_city,b.user_area FROM " . $db->table('ziping') . " AS a," . $db->table('user') . " AS b WHERE " . $filter_condition;
        $sql .= " AND a.user_phone = b.user_phone";
        if ($user_city != "") {
            $sql .= " and b.user_city like '%" . $user_city . "%'";
        }
        if ($user_area != "") {
            $sql .= " and b.user_area like '%" . $user_area . "%'";
        }
        if ($project_name != "") {
            $sql .= " and a.project_name like '%" . $project_name . "%'";
        }
        if ($user_name != "") {
            $sql .= " and a.user_name like '%" . $user_name . "%'";
        }
        if ($zp_status != "") {
            $sql .= " and a.zp_status = " . $zp_status;
        }
        $sql .= " ORDER BY a.zp_id DESC LIMIT " . $limit . "," . $pagesize;
        $row = $db->queryall($sql);

        //获取总记录;
        $sql_c = "select a.zp_id FROM " . $db->table('ziping') . " AS a," . $db->table('user') . " AS b WHERE " . $filter_condition;
        $sql_c .= " AND a.user_phone = b.user_phone";
        if ($user_city != "") {
            $sql_c .= " and b.user_city like '%" . $user_city . "%'";
        }
        if ($user_area != "") {
            $sql_c .= " and b.user_area like '%" . $user_area . "%'";
        }
        if ($project_name != "") {
            $sql_c .= " and a.project_name like '%" . $project_name . "%'";
        }
        if ($user_name != "") {
            $sql_c .= " and a.user_name like '%" . $user_name . "%'";
        }
        if ($zp_status != "") {
            $sql_c .= " and a.zp_status = " . $zp_status;
        }
        $sql_c .= " ORDER BY a.zp_id DESC";
        $row_c = $db->queryall($sql_c);

        /*信息输出*/
        $res['code'] = 0;
        $res['msg'] = 0;
        $res['count'] = count($row_c);
        $res["data"] = $row;
        die(json_encode_lockdata($res));
    }

5.日期格式化

漏刻有数mysql数据的时间格式统一采用时间戳的格式，即int(11)格式。

• 在前端输出时，需要转化为2023-04-24 12:45:23的时间格式，默认采用MySQL内置函数FROM_UNIXTIME(feed_time) as feed_time进行处理。
• date_format(from_unixtime(safe_time),'%Y-%m-%d')，使用date_format对时间进行格式化；

 public function getFeedback()
    {
        global $db, $res;
        dbc();
        @$feed_name = get_param("feed_name");
        @$p = get_safe('page') == "" ? 1 : get_safe('page');
        @$pagesize = get_safe('limit') == "" ? 15 : get_safe('limit');
        @$limit = ($p - 1) * $pagesize;
        $sql = "select feed_id,feed_name,feed_content,from_orign,FROM_UNIXTIME(feed_time) as feed_time FROM " . $db->table('feedback') . " WHERE 1";
        if ($feed_name != "") {
            $sql .= " and feed_name like '%" . $feed_name . "%'";
        }
        $sql .= " ORDER BY feed_id DESC LIMIT " . $limit . "," . $pagesize;
        $row = $db->queryall($sql);

        //获取总记录;
        $sql_c = "select feed_id FROM " . $db->table('feedback') . "  WHERE 1";
        if ($feed_name != "") {
            $sql_c .= " and feed_name like '%" . $feed_name . "%'";
        }
        $sql_c .= " ORDER BY feed_id DESC";
        $row_c = $db->queryall($sql_c);

        /*信息输出*/
        $res['code'] = 0;
        $res['msg'] = 0;
        $res['count'] = count($row_c);
        $res["data"] = $row;
        die(json_encode_lockdata($res));
    }

6.内置函数instr的用法

instr函数为字符查找函数，其功能是查找一个字符串在另一个字符串中首次出现的位置。例如：前端传递单位名称的数组或字符串，‘张三、李四、王萌、赵七’，需要和数据表的的字段depart_name匹配是否有’张三’时使用。传过来的字符串多，数据表中的字符少。和like的用法类似，但是like传过来的字符串少，数据表中的字符多。

    public function safeFinder()
    {
        global $db, $res;
        dbc();
        $safeDepart = $_POST['safeDepart'];
        $selectFinder = $_POST['selectFinder'];
        $selectStatus = $_POST['selectStatus'];
        $start_date = $_POST['start_date'];
        $end_date = $_POST['end_date'];
        //查询语句；
        $sql = "select safe_id,depart_name,safe_finder,COUNT(safe_finder) AS total from " . $db->table('safe') . " where 1";
        //单位查询；
        if ($safeDepart != "") {
            $sql .= " AND instr (\" . $safeDepart . \",depart_name) > 0";
        }
        //发现人查询；
        if ($selectFinder != "") {
            $sql .= " AND instr (\" . $selectFinder . \",safe_finder) > 0";
        }
        //整改情况查询；
        if ($selectStatus != "") {
            $sql .= " AND instr (\" . $selectStatus . \",safe_status) > 0";
        }
        //起始时间；
        if ($start_date != "") {
            $sql .= "  AND date_format(from_unixtime(safe_time),'%Y-%m-%d') >= '$start_date'";
        }
        //结束时间；
        if ($end_date != "") {
            $sql .= "  AND date_format(from_unixtime(safe_time),'%Y-%m-%d')