package com.ekexiu.console.system.service;

import com.ekexiu.console.system.dao.UserCountDao;
import com.ekexiu.console.system.po.UserCount;
import org.jfw.apt.annotation.Autowrie;
import org.jfw.apt.web.annotation.Path;
import org.jfw.apt.web.annotation.operate.Get;
import org.jfw.apt.web.annotation.param.JdbcConn;
import org.jfw.util.jdbc.JdbcUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by TT on 2017/4/10.
 */
@Path("/operation/statist")
public class StatistService {

    @Autowrie
    private UserCountDao userCountDao;

    public UserCountDao getUserCountDao() {
        return userCountDao;
    }

    public void setUserCountDao(UserCountDao userCountDao) {
        this.userCountDao = userCountDao;
    }

    @Get
    @Path("/user")
    public Map<String, Object> user(@JdbcConn Connection con) throws SQLException {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("all",JdbcUtil.queryInt(con,"SELECT COUNT(1) FROM LUSER",0));
        map.put("actived",JdbcUtil.queryInt(con,"SELECT COUNT(1) FROM LUSER WHERE PASSWD<>'11111111111111111111111111111111'",0));
        map.put("byRole", JdbcUtil.queryMaps(con, "select authentication code,count(1) num from professor group by code"));
        map.put("byType", JdbcUtil.queryMaps(con, "select auth_type code,count(1) num from professor group by code"));
        return map;
    }

    @Get
    @Path("/userCount")
    public List<UserCount> query(@JdbcConn Connection con) throws SQLException {
        return this.userCountDao.query(con);
    }

    @Get
    @Path("/total")
    public List<Map<String, Object>> total()throws SQLException {
        Connection con = DriverManager.getConnection("jdbc:postgresql://121.42.53.174:5432/ekexiu_log", "postgres", "postgres");
        return JdbcUtil.queryMaps(con, "SELECT day,sum(pv)as pv,source FROM article_day GROUP BY day,source ORDER BY day DESC");
    }

}