漏刻有时API接口实战开发系列(12)：php-mysql语句开发

项目拓展性的重要标准就是API接口，根据项目的需要，实时开发对应API接口来延展项目的需求。本案例是多个项目开发经验的总结，便于后期复盘，汲取经验。

1.跨表查询API

下面API接口是一个综合性极强的API接口，涉及

• 跨表查询功能；
• 关键词筛选功能；
• layui后台动态翻页功能；

public function getExpertMyProject()
    {
        global $db, $res;
        dbc();
        @$user_city = get_param("user_city");
        @$user_area = get_param("user_area");
        @$pro_category = get_param("pro_category");
        @$pro_name = get_param("pro_name");
        @$pro_serial = get_param("pro_serial");
        @$pro_declarant = get_param("pro_declarant");
        @$pro_condition = $_GET["pro_condition"];
        @$pro_types = $_GET["pro_types"];
        @$p = $_GET['page'] == "" ? 1 : $_GET['page'];
        @$pagesize = $_GET['limit'] == "" ? 15 : $_GET['limit'];
        @$limit = ($p - 1) * $pagesize;

        $sql = "select a.pro_id,a.user_id,MAX(a.ex_times) as ex_times,b.pro_id,b.pro_types,b.pro_audit,b.pro_name,b.pro_serial,b.pro_category,b.pro_condition,b.pro_declarant FROM " . $db->table('expert') . " AS a, " . $db->table('project') . " AS b WHERE a.user_id =" . $_COOKIE['user_id'] . " AND a.pro_id = b.pro_id ";
        if ($user_city != "") {
            $sql .= " AND a.user_city = '" . $user_city . "'";
        }

        if ($user_area != "") {
            $sql .= " AND a.user_city = b.user_city AND a.user_area = '" . $user_area . "'";
        }

        if ($pro_types != "") {
            $sql .= " AND b.pro_types = " . $pro_types;
        }

        if ($pro_condition != "") {
            $sql .= " AND b.pro_condition = " . $pro_condition;
        }

        if ($pro_category != "") {
            $sql .= " AND b.pro_category like '%" . $pro_category . "%'";
        }

        if ($pro_name != "") {
            $sql .= " AND b.pro_name like '%" . $pro_name . "%'";
        }

        if ($pro_serial != "") {
            $sql .= " AND b.pro_serial like '%" . $pro_serial . "%'";
        }

        if ($pro_declarant != "") {
            $sql .= " AND b.pro_declarant like '%" . $pro_declarant . "%'";
        }

        $sql .= " GROUP BY a.pro_id ORDER BY a.ex_id DESC LIMIT " . $limit . "," . $pagesize;
        $row = $db->queryall($sql);

        //获取总记录;
        $sql_c = "select a.pro_id,MAX(a.ex_times) AS ex_times,b.pro_id FROM " . $db->table('expert') . " AS a," . $db->table('project') . " AS b WHERE a.pro_id = b.pro_id";//  AND b.pro_status = 1
        //设置筛选条件;
        if ($_COOKIE['dbRoles'] == 2) {//县管理员
            $sql_c .= " AND a.user_city = b.user_city AND a.user_area = b.user_area";
        }

        if ($_COOKIE['dbRoles'] == 3) {//市管理员
            $sql_c .= " AND a.user_city = b.user_city";
        }

        if ($_COOKIE['dbRoles'] == 4) {//省管理员
            $sql_c .= " AND a.user_province = b.user_province";
        }
        if ($pro_types != "") {
            $sql_c .= " AND b.pro_types = " . $pro_types;
        }

        if ($pro_condition != "") {
            $sql_c .= " AND b.pro_condition = " . $pro_condition;
        }

        if ($user_city != "") {
            $sql_c .= " AND a.user_city = '" . $user_city . "'";
        }

        if ($user_area != "") {
            $sql_c .= " AND a.user_area = '" . $user_area . "'";
        }

        if ($pro_category != "") {
            $sql_c .= " AND b.pro_category like '%" . $pro_category . "%'";
        }

        if ($pro_name != "") {
            $sql_c .= " AND b.pro_name like '%" . $pro_name . "%'";
        }

        if ($pro_serial != "") {
            $sql_c .= " AND b.pro_serial like '%" . $pro_serial . "%'";
        }

        if ($pro_declarant != "") {
            $sql_c .= " AND b.pro_declarant like '%" . $pro_declarant . "%'";
        }
        $sql_c .= " GROUP BY a.pro_id ORDER BY a.ex_id DESC";
        $row_c = $db->queryall($sql_c);

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

2.地图下钻API接口

传递不同的地区参数，获取对应的区域数据。例如：省级–地市级，二级下钻。默认读取省级数据，当传递地级市名称的时候，获取区县级数据。

    public function getProjectMap()
    {
        global $db, $res;
        dbc();
        @$user_city = get_param("user_city");
        if ($user_city != "") {
            $sql = "select user_city,user_area,COUNT(CASE pro_status WHEN '2' THEN 2 END) AS total FROM " . $db->table('project') . " WHERE 1";
            $sql .= " AND user_city ='" . $user_city . "'";
            $sql .= " GROUP BY user_area";
        } else {
            $sql = "select user_city,COUNT(CASE pro_status WHEN '2' THEN 2 END) AS total FROM " . $db->table('project') . " WHERE 1";
            $sql .= " GROUP BY user_city";
        }
        $row = $db->queryall($sql);
        $res['msg'] = "OK";
        $res["data"] = $row;
        die(json_encode_lockdata($res));
    }

3.循环读取API

  //总站-城市站点排名信息表;
    public function getCityInfo()
    {
        global $db, $res;
        dbc();
        @$p = $_GET['page'] == "" ? 1 : $_GET['page'];//获取用户选择的页码
        @$pagesize = $_GET['limit'] == "" ? 10 : $_GET['limit'];//获取用户选择的每页显示多少条数据
        @$keys = $_GET['keys'];//站点Id
        @$fromTime = $_GET['fromTime'];//开始时间
        @$toTime = $_GET['toTime'];//结束时间
        @$limit = ($p - 1) * $pagesize;//偏移量

        //筛选全部记录并随前端自动分页展示;
        $sql = "select a.uniacid,a.name,a.dpopen,a.short_name from " . $db->table('set_basic') . " AS a where a.dpopen = 1";
        //按照站点筛选;
        IF ($keys != "") {
            $sql .= " AND a.uniacid = " . $keys;
        }
        $sql .= " ORDER BY a.uniacid DESC";
        $sql .= " LIMIT " . $limit . "," . $pagesize;
        $row = $db->queryall($sql);

        //获取总记录数量;
        $sql_c = "select a.uniacid,a.name,a.dpopen,a.short_name from " . $db->table('set_basic') . " AS a where a.dpopen = 1";
        //按照站点筛选;
        IF ($keys != "") {
            $sql_c .= " AND a.uniacid = " . $keys;
        }
        $sql_c .= " ORDER BY a.uniacid DESC";
        $row_c = $db->queryall($sql_c);
        $count = count($row_c);


        //获取对应站点的销量及对应的item;
        foreach ($row as $k => $v) {
            $sql_p = "select COUNT(a.id) AS transTotal,SUM(a.should_paid) AS totalPays,COUNT(DISTINCT a.user_id) AS totalUser,a.paid_at,a.uniacid,SUM(a.all_num) AS totalAmount from " . $db->table('orders') . " AS a";
            $sql_p .= " WHERE a.status  '-128' AND a.uniacid =" . $v['uniacid'];//AND a.status  '-126'
            //按照时间筛选;
            IF ($fromTime != "") {
                $sql_p .= " AND date_format(a.paid_at,'%Y-%m-%d') >= '$fromTime'";
            }
            IF ($toTime != "") {
                $sql_p .= " AND date_format(a.paid_at,'%Y-%m-%d')