package com.controller.api;

import com.config.BaseController;
import com.config.ControllerInterface;
import com.jfinal.aop.Clear;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.utils.CommonUtils;
import org.apache.commons.lang3.StringUtils;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Clear
@ControllerInterface(path="/api/readerreport")
public class ReaderReportController extends BaseController {
    public SimpleDateFormat format0 = new SimpleDateFormat("yyyy-MM-dd");

    public void findorgname(){
        int orgid = getParaToInt("orgid");
        Record record = Db.findFirst("select orgname from t_org where id = ?",orgid);
        success(record);
    }
    public void duzhepaihang(){
        String startmonth = getPara("startmonth");
        String endmonth = getPara("endmonth");
        if(StringUtils.isBlank(startmonth)){
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.MONTH,-1);
            startmonth = format0.format(calendar.getTime());
        }
        if(StringUtils.isBlank(endmonth)){
            endmonth = format0.format(new Date());
        }
        int orgid = getParaToInt("orgid");
        int pageSize = getParaToInt("pageSize", 10);
        String sql = "select * from (\n" +
                "select r.realname,d.deptname,count(j.id) num from t_jieyue j inner join t_reader r on r.id=j.readerid\n" +
                "inner join t_org_dept d on d.id=r.orgdeptid\n" +
                " where r.orgid=? and j.createtime>=? and j.createtime<=? group by r.realname) table1 order by num desc limit 0,?";
        List<Record> list = Db.find(sql,orgid,startmonth+" 00:00:00",endmonth+" 23:59:59",pageSize);
        System.out.println(sql);
        success(list);
    }
    public void shujipaihang(){
        String startmonth = getPara("startmonth");
        String endmonth = getPara("endmonth");
        if(StringUtils.isBlank(startmonth)){
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.MONTH,-1);
            startmonth = format0.format(calendar.getTime());
        }
        if(StringUtils.isBlank(endmonth)){
            endmonth = format0.format(new Date());
        }
        int pageSize = getParaToInt("pageSize", 10);
        int orgid = getParaToInt("orgid");
        String sql = "select * from (\n" +
                "select b.title,b.author,count(j.id) num from t_jieyue j inner join t_book_detail bd on bd.id=j.bookdetailid\n" +
                "inner join t_book b on b.id=bd.bookid\n" +
                " where j.orgid=? and j.createtime>=? and j.createtime<=? group by b.title) table1 order by num desc limit 0,?";
        List<Record> list = Db.find(sql,orgid,startmonth+" 00:00:00",endmonth+" 23:59:59",pageSize);
        System.out.println(sql);
        success(list);
    }
    public void fenlei(){
        String startmonth = getPara("startmonth");
        String endmonth = getPara("endmonth");
        if(StringUtils.isBlank(startmonth)){
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.MONTH,-1);
            startmonth = format0.format(calendar.getTime());
        }
        if(StringUtils.isBlank(endmonth)){
            endmonth = format0.format(new Date());
        }
        int pageSize = getParaToInt("pageSize", 10);
        int orgid = getParaToInt("orgid");
        String sql = "select * from (" +
                " select t.`name`,count(j.id) num from t_jieyue j inner join t_book_detail d on j.bookdetailid=d.id inner join t_book b on b.id=d.bookid\n" +
                " INNER join t_booktype t on t.id=b.typeid where j.orgid=? and j.createtime>=? and j.createtime<=? group by t.name " +
                ")table1 order by num  limit 0,?";
        List<Record> list = Db.find(sql,orgid,startmonth+" 00:00:00",endmonth+" 23:59:59",pageSize);
        Record totlalrecord = Db.findFirst(" select count(j.id) num from t_jieyue j where j.orgid=? " +
                "and j.createtime>=? and j.createtime<=? ",orgid, startmonth+" 00:00:00", endmonth+" 23:59:59" );
        long total = totlalrecord.getLong("num");
        Map result = new HashMap<>();
        result.put("list", list);
        result.put("total", total);
        String str1,str2,str3;
        if(list.size()>0){
            Record r = list.get(list.size()-1);
            double a = r.getLong("num").longValue()*100d/total;
            str1 = "最受欢迎的图书是<span style=\"color:#fd883a\">"+r.getStr("name")+"</span>类，占阅读总量的<span style=\"color:#fd883a\">"+String.format("%.2f", a)+"%</span>";
        }else{
            str1 = "";
        }
        if(list.size()>1){
            Record r = list.get(list.size()-2);
            double a = r.getLong("num").longValue()*100d/total;
            str2 = "第二热门图书是<span style=\"color:#fd883a\">"+r.getStr("name")+"</span>类，占阅读总量的<span style=\"color:#fd883a\">"+String.format("%.2f", a)+"%</span>";
        }else{
            str2 = "";
        }
        if(list.size()>2){
            Record r = list.get(list.size()-3);
            double a = r.getLong("num").longValue()*100d/total;
            str3 = "第三热门图书是<span style=\"color:#fd883a\">"+r.getStr("name")+"</span>类，占阅读总量的<span style=\"color:#fd883a\">"+String.format("%.2f", a)+"%</span>";
        }else{
            str3 = "";
        }


        result.put("str1", str1);
        result.put("str2", str2);
        result.put("str3", str3);
        success(result);
    }

    public void nianjijieyuefenxi() {
        String startmonth = getPara("startmonth");
        String endmonth = getPara("endmonth");
        if(StringUtils.isBlank(startmonth)){
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.MONTH,-1);
            startmonth = format0.format(calendar.getTime());
        }
        if(StringUtils.isBlank(endmonth)){
            endmonth = format0.format(new Date());
        }
        int orgid = getParaToInt("orgid");
        Record orgRecord = Db.findById("t_org", orgid);
        if (CommonUtils.isNullOrEmpty(orgRecord)) {
            fail(0, "查询不到机构信息");
            return;
        }
        if (CommonUtils.isNullOrEmpty(orgRecord.get("orgtypeid"))) {
            fail(0, "机构类型不明确");
            return;
        }
        if (CommonUtils.isNullOrEmpty(orgRecord.get("schoolcategoryid"))) {
            fail(0, "学校类别不明确，请在学校信息里设置学校类别");
            return;
        }
        if (orgRecord.getInt("orgtypeid") != 5) {
            fail(0, "机构类型不正确");
            return;
        }
        List<Double> sortList = new ArrayList<>();
        Map<Double,String> map = new HashMap<>();
        List reList = new ArrayList();
        String typeid = "";
        int schoolcategoryid = orgRecord.getInt("schoolcategoryid");
        Record record = new Record();
        if (schoolcategoryid == 1 || schoolcategoryid == 3 || schoolcategoryid == 6) {
            record.set("gradename", "1-2年级");
            Record zongjieyueRecord = Db.findFirst("select count(*) ct from t_jieyue j left join t_reader r on j.readerid = r.id left join t_org_dept od on r.orgdeptid = od.id where j.orgid = ? and j.createtime >=? and j.createtime <=? and od.grade in (1,2)", orgid,  startmonth,endmonth);
            int zongjieyue = zongjieyueRecord.getInt("ct");
            record.set("zongjieyue", zongjieyue);
            if (zongjieyue == 0) {
                record.set("pinjunjieyue", "0");
                sortList.add(0d);
                map.put(0d,record.getStr("gradename"));
            } else {
                Record readerRecord = Db.findFirst("select count(*) ct from t_reader r left join t_org_dept od on r.orgdeptid = od.id where r.orgid = ? and od.grade in (1,2)", orgid);
                DecimalFormat df = new DecimalFormat("0.00");
                String pinjunjieyue = df.format((float) zongjieyue / readerRecord.getInt("ct"));
                record.set("pinjunjieyue", pinjunjieyue);
                sortList.add((double) zongjieyue / readerRecord.getInt("ct"));
                map.put((double) zongjieyue / readerRecord.getInt("ct"),record.getStr("gradename"));
            }
            Record zuixihuantushuleibieRecord = Db.findFirst("select typeid from (\n" +
                    "select b.typeid,count(*) ct from t_jieyue j left join t_reader r on j.readerid = r.id left join t_org_dept od on r.orgdeptid = od.id left join t_book_detail d on j.bookdetailid = d.id \n" +
                    "left join t_book b on d.bookid = b.id where j.orgid = ? and j.createtime >=? and j.createtime <=?  and od.grade in (1,2)\n" +
                    "group by b.typeid  \n" +
                    ") t order by ct desc", orgid, startmonth,endmonth);
            if (CommonUtils.isNotNullOrNotEmpty(zuixihuantushuleibieRecord) && CommonUtils.isNotNullOrNotEmpty(zuixihuantushuleibieRecord.get("typeid"))) {
                typeid = zuixihuantushuleibieRecord.getStr("typeid");
                Record typeinfoRecord = Db.findFirst("select * from t_booktype where id = ?", typeid);
                record.set("zuixihuantushuleibie", CommonUtils.isNotNullOrNotEmpty(typeinfoRecord.get("name")) ? typeinfoRecord.getStr("name") : "");

                Record leibiejieyueliangRecord = Db.findFirst("select count(*) ct from t_book_detail d left join t_book b on d.bookid = b.id  where orgid = ? and typeid = ?", orgid, typeid);
                record.set("leibiejieyueliang", leibiejieyueliangRecord.getInt("ct"));
            } else {
                record.set("zuixihuantushuleibie", "");
                record.set("leibiejieyueliang", 0);
            }
            reList.add(record);

            record = new Record();
            record.set("gradename", "3-4年级");
            zongjieyueRecord = Db.findFirst("select count(*) ct from t_jieyue j left join t_reader r on j.readerid = r.id left join t_org_dept od on r.orgdeptid = od.id where j.orgid = ? and j.createtime >=? and j.createtime <=? and od.grade in (3,4)", orgid,  startmonth,endmonth);
            zongjieyue = zongjieyueRecord.getInt("ct");
            record.set("zongjieyue", zongjieyue);
            if (zongjieyue == 0) {
                record.set("pinjunjieyue", "0");
                record.set("pinjunjieyue", "0");
                sortList.add(0d);
            } else {
                Record readerRecord = Db.findFirst("select count(*) ct from t_reader r left join t_org_dept od on r.orgdeptid = od.id where r.orgid = ? and od.grade in (3,4)", orgid);
                DecimalFormat df = new DecimalFormat("0.00");
                String pinjunjieyue = df.format((float) zongjieyue / readerRecord.getInt("ct"));
                record.set("pinjunjieyue", pinjunjieyue);
                sortList.add((double) zongjieyue / readerRecord.getInt("ct"));
                map.put((double) zongjieyue / readerRecord.getInt("ct"),record.getStr("gradename"));
            }
            zuixihuantushuleibieRecord = Db.findFirst("select typeid from (\n" +
                    "select b.typeid,count(*) ct from t_jieyue j left join t_reader r on j.readerid = r.id left join t_org_dept od on r.orgdeptid = od.id left join t_book_detail d on j.bookdetailid = d.id \n" +
                    "left join t_book b on d.bookid = b.id where j.orgid = ? and j.createtime >=? and j.createtime <=? and od.grade in (3,4)\n" +
                    "group by b.typeid  \n" +
                    ") t order by ct desc", orgid, startmonth,endmonth);
            if (CommonUtils.isNotNullOrNotEmpty(zuixihuantushuleibieRecord) && CommonUtils.isNotNullOrNotEmpty(zuixihuantushuleibieRecord.get("typeid"))) {
                typeid = zuixihuantushuleibieRecord.getStr("typeid");
                Record typeinfoRecord = Db.findFirst("select * from t_booktype where id = ?", typeid);
                record.set("zuixihuantushuleibie", CommonUtils.isNotNullOrNotEmpty(typeinfoRecord.get("name")) ? typeinfoRecord.getStr("name") : "");

                Record leibiejieyueliangRecord = Db.findFirst("select count(*) ct from t_book_detail d left join t_book b on d.bookid = b.id  where orgid = ? and typeid = ?", orgid, typeid);
                record.set("leibiejieyueliang", leibiejieyueliangRecord.getInt("ct"));
            } else {
                record.set("zuixihuantushuleibie", "");
                record.set("leibiejieyueliang", 0);
            }
            reList.add(record);

            record = new Record();
            record.set("gradename", "5-6年级");
            zongjieyueRecord = Db.findFirst("select count(*) ct from t_jieyue j left join t_reader r on j.readerid = r.id left join t_org_dept od on r.orgdeptid = od.id where j.orgid = ? and j.createtime >=? and j.createtime <=? and od.grade in (5,6)", orgid,  startmonth,endmonth);
            zongjieyue = zongjieyueRecord.getInt("ct");
            record.set("zongjieyue", zongjieyue);
            if (zongjieyue == 0) {
                record.set("pinjunjieyue", "0");
                record.set("pinjunjieyue", "0");
                sortList.add(0d);
            } else {
                Record readerRecord = Db.findFirst("select count(*) ct from t_reader r left join t_org_dept od on r.orgdeptid = od.id where r.orgid = ? and od.grade in (5,6)", orgid);
                DecimalFormat df = new DecimalFormat("0.00");
                String pinjunjieyue = df.format((float) zongjieyue / readerRecord.getInt("ct"));
                record.set("pinjunjieyue", pinjunjieyue);
                sortList.add((double) zongjieyue / readerRecord.getInt("ct"));
                map.put((double) zongjieyue / readerRecord.getInt("ct"),record.getStr("gradename"));
            }
            zuixihuantushuleibieRecord = Db.findFirst("select typeid from (\n" +
                    "select b.typeid,count(*) ct from t_jieyue j left join t_reader r on j.readerid = r.id left join t_org_dept od on r.orgdeptid = od.id left join t_book_detail d on j.bookdetailid = d.id \n" +
                    "left join t_book b on d.bookid = b.id where j.orgid = ? and j.createtime >=? and j.createtime <=? and od.grade in (5,6)\n" +
                    "group by b.typeid  \n" +
                    ") t order by ct desc", orgid,  startmonth,endmonth);
            if (CommonUtils.isNotNullOrNotEmpty(zuixihuantushuleibieRecord) && CommonUtils.isNotNullOrNotEmpty(zuixihuantushuleibieRecord.get("typeid"))) {
                typeid = zuixihuantushuleibieRecord.getStr("typeid");
                Record typeinfoRecord = Db.findFirst("select * from t_booktype where id = ?", typeid);
                record.set("zuixihuantushuleibie", CommonUtils.isNotNullOrNotEmpty(typeinfoRecord.get("name")) ? typeinfoRecord.getStr("name") : "");

                Record leibiejieyueliangRecord = Db.findFirst("select count(*) ct from t_book_detail d left join t_book b on d.bookid = b.id  where orgid = ? and typeid = ?", orgid, typeid);
                record.set("leibiejieyueliang", leibiejieyueliangRecord.getInt("ct"));
            } else {
                record.set("zuixihuantushuleibie", "");
                record.set("leibiejieyueliang", 0);
            }
            reList.add(record);
        }

        if (schoolcategoryid == 2 || schoolcategoryid == 5 || schoolcategoryid == 6) {
            record = new Record();
            record.set("gradename", "初中");
            Record zongjieyueRecord = Db.findFirst("select count(*) ct from t_jieyue j left join t_reader r on j.readerid = r.id left join t_org_dept od on r.orgdeptid = od.id where j.orgid = ? and j.createtime >=? and j.createtime <=? and od.grade in (7,8,9)", orgid,  startmonth,endmonth);
            int zongjieyue = zongjieyueRecord.getInt("ct");
            record.set("zongjieyue", zongjieyue);
            if (zongjieyue == 0) {
                record.set("pinjunjieyue", "0");
                record.set("pinjunjieyue", "0");
                sortList.add(0d);
            } else {
                Record readerRecord = Db.findFirst("select count(*) ct from t_reader r left join t_org_dept od on r.orgdeptid = od.id where r.orgid = ? and od.grade in (7,8,9)", orgid);
                DecimalFormat df = new DecimalFormat("0.00");
                String pinjunjieyue = df.format((float) zongjieyue / readerRecord.getInt("ct"));
                record.set("pinjunjieyue", pinjunjieyue);
                sortList.add((double) zongjieyue / readerRecord.getInt("ct"));
                map.put((double) zongjieyue / readerRecord.getInt("ct"),record.getStr("gradename"));
            }
            Record zuixihuantushuleibieRecord = Db.findFirst("select typeid from (\n" +
                    "select b.typeid,count(*) ct from t_jieyue j left join t_reader r on j.readerid = r.id left join t_org_dept od on r.orgdeptid = od.id left join t_book_detail d on j.bookdetailid = d.id \n" +
                    "left join t_book b on d.bookid = b.id where j.orgid = ? and j.createtime >=? and j.createtime <=? and od.grade in (7,8,9)\n" +
                    "group by b.typeid  \n" +
                    ") t order by ct desc", orgid,  startmonth,endmonth);
            if (CommonUtils.isNotNullOrNotEmpty(zuixihuantushuleibieRecord) && CommonUtils.isNotNullOrNotEmpty(zuixihuantushuleibieRecord.get("typeid"))) {
                typeid = zuixihuantushuleibieRecord.getStr("typeid");
                Record typeinfoRecord = Db.findFirst("select * from t_booktype where id = ?", typeid);
                record.set("zuixihuantushuleibie", CommonUtils.isNotNullOrNotEmpty(typeinfoRecord.get("name")) ? typeinfoRecord.getStr("name") : "");

                Record leibiejieyueliangRecord = Db.findFirst("select count(*) ct from t_book_detail d left join t_book b on d.bookid = b.id  where orgid = ? and typeid = ?", orgid, typeid);
                record.set("leibiejieyueliang", leibiejieyueliangRecord.getInt("ct"));
            } else {
                record.set("zuixihuantushuleibie", "");
                record.set("leibiejieyueliang", 0);
            }
            reList.add(record);
        }

        if (schoolcategoryid == 4 || schoolcategoryid == 5 || schoolcategoryid == 6) {
            record = new Record();
            record.set("gradename", "高中");
            Record zongjieyueRecord = Db.findFirst("select count(*) ct from t_jieyue j left join t_reader r on j.readerid = r.id left join t_org_dept od on r.orgdeptid = od.id where j.orgid = ? and j.createtime >=? and j.createtime <=? and od.grade in (10,11,12)", orgid,  startmonth,endmonth);
            int zongjieyue = zongjieyueRecord.getInt("ct");
            record.set("zongjieyue", zongjieyue);
            if (zongjieyue == 0) {
                record.set("pinjunjieyue", "0");
                record.set("pinjunjieyue", "0");
                sortList.add(0d);
            } else {
                Record readerRecord = Db.findFirst("select count(*) ct from t_reader r left join t_org_dept od on r.orgdeptid = od.id where r.orgid = ? and od.grade in (10,11,12)", orgid);
                DecimalFormat df = new DecimalFormat("0.00");
                String pinjunjieyue = df.format((float) zongjieyue / readerRecord.getInt("ct"));
                record.set("pinjunjieyue", pinjunjieyue);
                sortList.add((double) zongjieyue / readerRecord.getInt("ct"));
                map.put((double) zongjieyue / readerRecord.getInt("ct"),record.getStr("gradename"));
            }
            Record zuixihuantushuleibieRecord = Db.findFirst("select typeid from (\n" +
                    "select b.typeid,count(*) ct from t_jieyue j left join t_reader r on j.readerid = r.id left join t_org_dept od on r.orgdeptid = od.id left join t_book_detail d on j.bookdetailid = d.id \n" +
                    "left join t_book b on d.bookid = b.id where j.orgid = ? and j.createtime >=? and j.createtime <=? and od.grade in (10,11,12)\n" +
                    "group by b.typeid  \n" +
                    ") t order by ct desc", orgid,  startmonth,endmonth);
            if (CommonUtils.isNotNullOrNotEmpty(zuixihuantushuleibieRecord) && CommonUtils.isNotNullOrNotEmpty(zuixihuantushuleibieRecord.get("typeid"))) {
                typeid = zuixihuantushuleibieRecord.getStr("typeid");
                Record typeinfoRecord = Db.findFirst("select * from t_booktype where id = ?", typeid);
                record.set("zuixihuantushuleibie", CommonUtils.isNotNullOrNotEmpty(typeinfoRecord.get("name")) ? typeinfoRecord.getStr("name") : "");

                Record leibiejieyueliangRecord = Db.findFirst("select count(*) ct from t_book_detail d left join t_book b on d.bookid = b.id  where orgid = ? and typeid = ?", orgid, typeid);
                record.set("leibiejieyueliang", leibiejieyueliangRecord.getInt("ct"));
            } else {
                record.set("zuixihuantushuleibie", "");
                record.set("leibiejieyueliang", 0);
            }
            reList.add(record);
        }
        Map reMap = new HashMap();
        reMap.put("datalist",reList);
        String reinfo = "";
        if(schoolcategoryid != 2 && schoolcategoryid != 4 && sortList.get(sortList.size()-1).compareTo(sortList.get(0)) != 0){
            Collections.sort(sortList);
            reinfo = "* "+map.get(sortList.get(sortList.size()-1))+"同学读书热情最高，"+map.get(sortList.get(0))+"同学读书热情有待提高";
        }
        reMap.put("reinfo",reinfo);

        success(reMap);
    }
}
