package com.service.report;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import java.util.List;

public class WorkloadService {
    public static final WorkloadService me = new WorkloadService();

    public List<Record> statisticalWorkload(int orgid,String startTime,String endTime) {
        StringBuffer sql = new StringBuffer("select realname,ifnull(bianmunum,0) bianmunum,ifnull(zhuxiaonum,0) zhuxiaonum,ifnull(jieshunum,0) jieshunum,ifnull(huanshunum,0) huanshunum,ifnull(xujienum,0) xujienum from t_user u ");
        sql.append(" left join ");
        sql.append(" (select count(1) bianmunum,b.operatorid from t_book b left join t_book_detail d on b.id = d.bookid  " +
                "where b.operatorid in (select id from t_user where orgid = ?) and b.createtime >? " +
                "and b.createtime <=? group by b.operatorid) book ");
        sql.append(" on u.id=book.operatorid ");
        sql.append(" left join ");
        sql.append(" (");
        sql.append(" select count(1) zhuxiaonum,operatorid from t_book_zhuxiao where operatorid in (select id from t_user where orgid = ?) " +
                "and createtime >? and createtime <=? group by operatorid");
        sql.append(")zhuxiao ");
        sql.append(" on u.id=zhuxiao.operatorid");
        sql.append(" left join ");
        sql.append(" (select count(1) jieshunum,operatorid from t_jieyue " +
                "where operatorid in (select id from t_user where orgid = ?) " +
                "and status in (0,1) and continuenum !=1 and createtime >? " +
                "and createtime <=? group by operatorid) jieshu");
        sql.append(" on u.id=jieshu.operatorid");
        sql.append(" left join ");
        sql.append(" (select count(1) huanshunum,operatorid from t_jieyue where " +
                "operatorid in (select id from t_user where orgid = ?) and status =1 and continuenum !=1 " +
                "and returntime >? and returntime <=? group by operatorid) huanshu");
        sql.append(" on  u.id=huanshu.operatorid");
        sql.append(" left join ");
        sql.append(" (select count(1) xujienum,operatorid from t_jieyue " +
                "where operatorid in (select id from t_user where orgid =?) and continuenum =1 and createtime >? and createtime <=? group by operatorid) xujie");
        sql.append(" on  u.id=xujie.operatorid ");
        sql.append(" where orgid = ?");
        return Db.find(sql.toString(),orgid,startTime,endTime,orgid,startTime,endTime,
                orgid,startTime,endTime,orgid,startTime,endTime,orgid,startTime,endTime,orgid);
    }
}
