package com.service.screendisplay;

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

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 大屏幕首页
 * 2019-05-05
 * chenw
 */
public class ScreenHomeService {
	public static final ScreenHomeService me = new ScreenHomeService();
	private OrgService orgService = OrgService.me;
	/**
	 * 学校书刊数量展示
	 */
	public Object schoolHomeCount(Integer orgid) {
		StringBuilder sql = new StringBuilder("SELECT xss as readerNum,tszcs as bookNum,ifnull(qkzs,0) as periodicalNum,tszcs/xss as avgNum " +
				"from t_dayreport WHERE DATEDIFF(createtime,NOW())=0 and orgid = "+orgid);
//		sql.append("SELECT d.*,ROUND(d.bookNum/d.readerNum) AS avgNum FROM (");
//		sql.append(" SELECT (SELECT COUNT(DISTINCT d.barcode) FROM t_book b LEFT JOIN t_book_detail d ON b.id = d.bookid WHERE  b.orgid = "+ orgid +" AND d.status !=3 AND d.bookstatus = 1 ) AS bookNum,");
//		sql.append(" (SELECT COUNT(1) FROM t_periodical_zhengding p WHERE p.orgid = "+ orgid +" ) AS periodicalNum,");
//        sql.append(" (SELECT COUNT(1) FROM t_reader r WHERE r.orgid = "+ orgid +" ) AS readerNum )d");
		return Db.find(sql.toString());
	}


	/**
	 * 学校今日简报
	 * @param orgid
	 * @return
	 */
	public Object schoolTodayReport(Integer orgid) {
		StringBuilder sql = new StringBuilder("SELECT ");
		sql.append("(SELECT COUNT(1) FROM t_jieyue t WHERE t.orgid = " + orgid + " and TO_DAYS(t.createtime) = TO_DAYS(NOW())) as borNum,");
		sql.append("(SELECT COUNT(1) FROM t_jieyue t WHERE t.orgid = " + orgid + " and TO_DAYS(t.returntime) = TO_DAYS(NOW())) as retNum,");
		sql.append("(SELECT COUNT(1) FROM t_jieyue j WHERE j.orgid = "+orgid+" AND TO_DAYS(NOW()) - TO_DAYS(j.createtime) = 1) yesBorNum,");
		sql.append("(SELECT COUNT(1) FROM t_jieyue j WHERE j.orgid = "+orgid+" AND TO_DAYS(NOW()) - TO_DAYS(j.returntime) = 1) yesRetNum,");
		sql.append("(SELECT COUNT(DISTINCT d.barcode) FROM t_jieyue j LEFT JOIN t_book_detail d ON j.bookdetailid=d.id WHERE j.orgid="+orgid+" AND (TO_DAYS(j.createtime) = TO_DAYS(NOW()) OR TO_DAYS(j.returntime) = TO_DAYS(NOW()))) total");
		return Db.findFirst(sql.toString());
	}

	/**
	 * 学校新书、好书推荐
	 * @param orgid 机构id
	 * @param flag 0新书推荐 1好书推荐
	 * @return
	 */
	public Object schoolBookRecommend(Integer orgid, Integer flag) {
		String sql = "SELECT s.bookid,b.title,s.contentsynopsis,s.recommendreason FROM t_schoolbookrecommend s ,t_book b " +
				"WHERE s.bookid = b.id  AND s.deletestatus = 0 AND s.orgid = ? AND s.flag = ? order by s.createtime desc";
		return Db.find(sql,orgid,flag);
	}

	/**
	 * 超期催还列表
	 * @param orgid
	 * @return
	 */
	public Object overdueList(Integer orgid) {
		StringBuilder where = new StringBuilder("SELECT r.realname,od.deptname,r.cardnumber,DATE_FORMAT(j.duetime,'%Y-%m-%d') duetime,d.barcode,b.title  FROM t_jieyue j,t_reader r,t_book_detail d,t_book b,t_org_dept od ");
		where.append(" WHERE j.readerid = r.id AND j.bookdetailid = d.id AND d.bookid = b.id AND od.id=r.orgdeptid AND j.isoverdue = 1 and j.flag=0 and j.status=0 and j.orgid = "+orgid);
	/*	where.append(" UNION ALL SELECT r.realname,od.deptname,r.cardnumber,DATE_FORMAT(j.duetime,'%Y-%m-%d') duetime,d.barcode,p.kanming AS title FROM t_jieyue j,t_reader r,t_periodical_detail d,t_periodical_zhengding p,t_org_dept od  ");
		where.append(" WHERE j.readerid = r.id AND j.bookdetailid = d.id AND d.periodicalid = p.id AND od.id=r.orgdeptid AND j.isoverdue = 1 and j.flag=0 and j.status=0 and j.orgid = "+orgid);*/
		where.append(" ORDER BY duetime limit 6");
		return Db.find(where.toString());
	}

	/**
	 * 图书借阅排行
	 * @param orgid
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public Object borrowRank(Integer orgid, Integer pageNumber, Integer pageSize) {
		String select = "SELECT b.title,COUNT(1) num,b.author,b.publisher ";
		StringBuilder where = new StringBuilder(" FROM t_jieyue j,t_book_detail d,t_book b ");
		where.append(" WHERE j.bookdetailid = d.id AND d.bookid = b.id AND j.orgid = "+orgid+" GROUP BY b.title,b.author,b.publisher");
		where.append(" UNION ALL SELECT p.kanming AS title,COUNT(1) num,'' as author,p.bianjibu publisher FROM t_jieyue j,t_periodical_detail d,t_periodical_zhengding p ");
		where.append(" WHERE j.bookdetailid = d.id AND d.periodicalid = p.id  and j.orgid = "+orgid+" GROUP BY p.kanming,p.bianjibu");
		where.append(" ORDER BY num+0 DESC");
		return Db.paginate(pageNumber, pageSize,select,where.toString());
	}

	/**
	 * 读者借阅排行
	 * @param orgid
	 * @return
	 */
	public Object readerBorrowRank(Integer orgid) {
		StringBuilder where = new StringBuilder("SELECT detail.realname,detail.cardnumber,detail.num,ifnull(d.deptname,'')deptname ");
		where.append(" FROM (SELECT r.realname,r.cardnumber,r.orgdeptid,COUNT(DISTINCT d.barcode) num FROM t_jieyue j,t_reader r,t_book_detail d ");
		where.append(" WHERE j.readerid = r.id AND j.orgid = "+orgid+" AND j.bookdetailid=d.id GROUP BY j.readerid,r.realname,r.cardnumber,r.orgdeptid) detail ");
		where.append(" LEFT JOIN t_org_dept d ON detail.orgdeptid = d.id ");
		where.append(" ORDER BY detail.num DESC LIMIT 6 ");
		return Db.find(where.toString());
	}

    /**
     * 教育局首页数量
     * @param orgid
     * @return
     */
    public Object orgHomeCount(Integer orgid) {

        StringBuilder sql = new StringBuilder("SELECT COUNT(orgid) as schoolNum,SUM(xss) as readerNum,SUM(tszcs) as bookNum,SUM(tszcs)/SUM(xss) as avgNum " +
				"        from t_dayreport as a left join t_org as b on a.orgid = b.id WHERE DATEDIFF(a.createtime,NOW())=0  and b.isclose =0 and  orgid IN"+ findSchoolids(orgid));
        return Db.find(sql.toString());
    }

    /**
     * 教育局今日简报
     * @param orgid
     * @return
     */
    public Object orgTodayReport(Integer orgid) {
    	String val = me.findSchoolids(orgid);
        StringBuilder sql = new StringBuilder("SELECT ");
        sql.append("(SELECT COUNT(1) FROM t_book b LEFT JOIN t_book_detail d ON b.id = d.bookid LEFT JOIN t_org o ON b.orgid = o.id WHERE o.id IN "+ val +" AND  TO_DAYS(d.shangjiatime) = TO_DAYS(NOW()))  addNum,");
        sql.append("(SELECT COUNT(1) FROM t_book b LEFT JOIN t_book_detail d ON b.id = d.bookid LEFT JOIN t_org o ON b.orgid = o.id WHERE o.id IN "+ val +" AND  TO_DAYS(d.destroytime) = TO_DAYS(NOW())) destNum,");
        sql.append("(SELECT COUNT(1) FROM t_jieyue j LEFT JOIN t_org o ON j.orgid= o.id WHERE o.id IN "+ val +" AND TO_DAYS(j.createtime) = TO_DAYS(NOW())) borNum,");
        sql.append("(SELECT COUNT(1) FROM t_jieyue j LEFT JOIN t_org o ON j.orgid= o.id WHERE o.id IN "+ val +" AND TO_DAYS(j.returntime) = TO_DAYS(NOW())) retNum,");
		sql.append("(SELECT COUNT(1) FROM t_jieyue j LEFT JOIN t_org o ON j.orgid= o.id WHERE o.id IN "+ val +" AND TO_DAYS(NOW()) - TO_DAYS(j.createtime) = 1) yesBorNum,");
		sql.append("(SELECT COUNT(1) FROM t_jieyue j LEFT JOIN t_org o ON j.orgid= o.id WHERE o.id IN "+ val +" AND TO_DAYS(NOW()) - TO_DAYS(j.returntime) = 1) yesRetNum,");
		sql.append("(SELECT COUNT(DISTINCT d.barcode) FROM t_jieyue j LEFT JOIN t_org o ON j.orgid= o.id LEFT JOIN t_book_detail d ON j.bookdetailid=d.id WHERE o.id IN "+ val +" AND (TO_DAYS(j.createtime) = TO_DAYS(NOW()) OR TO_DAYS(j.returntime) = TO_DAYS(NOW()))) total");
        return Db.findFirst(sql.toString());
    }

    /**
     * 优秀读者
     * @param orgid
     * @return
     */
    public Object excellentReaders(Integer orgid) {
    	String val = me.findSchoolids(orgid);
        StringBuilder where = new StringBuilder("SELECT r.realname,ifnull(t.deptname,'') deptname,COUNT(d.id) AS nums,o.orgname  FROM t_jieyue j LEFT JOIN t_org o ON j.orgid= o.id  ");
        where.append(" LEFT JOIN t_reader r ON r.id = j.readerid LEFT JOIN t_org_dept t ON r.orgdeptid = t.id LEFT JOIN t_book_detail d ON j.bookdetailid=d.id ");
        where.append(" WHERE o.id IN "+val);
        where.append(" GROUP BY j.readerid,r.realname,t.deptname,o.orgname ORDER BY nums DESC limit 6");
        return Db.find(where.toString());
    }

    /**
     * 教育局图书借阅排行
     * @param orgid
     * @param pageNumber
     * @param pageSize
     * @return
     */
    public Object orgBorrowRank(Integer orgid, Integer pageNumber, Integer pageSize) {
        String select = "SELECT b.title,b.author,b.publisher,COUNT(1) num ";
        StringBuilder where = new StringBuilder(" FROM t_jieyue j ");
        where.append(" LEFT JOIN t_book_detail d ON d.id = j.bookdetailid LEFT JOIN t_book b ON d.bookid = b.id LEFT JOIN t_org o ON j.orgid = o.id ");
        where.append(" WHERE j.flag = 0 AND o.parentid = "+ orgid +" GROUP BY b.title,b.author,b.publisher ");
        where.append(" UNION ALL SELECT p.kanming AS title,COUNT(1) num,'' AS author,p.bianjibu publisher ");
        where.append(" FROM t_jieyue j LEFT JOIN t_periodical_detail d ON d.id = j.bookdetailid LEFT JOIN t_periodical_zhengding p ON d.periodicalid = p.id LEFT JOIN t_org o ON j.orgid = o.id ");
        where.append(" WHERE j.flag = 1 AND o.parentid = "+ orgid +" GROUP BY p.kanming,p.bianjibu ");
        where.append(" ORDER BY num+0 DESC limit 10");
        return Db.paginate(pageNumber, pageSize,select,where.toString());
    }

	public Object circulateRank(Integer orgid) {
//		StringBuilder where = new StringBuilder("SELECT o.orgname,COUNT(DISTINCT d.barcode) borNum,d.totalNum,ROUND(COUNT(DISTINCT d.barcode)/d.totalNum*100,0) rate ");
//		where.append(" FROM t_book_detail d LEFT JOIN t_book b ON d.bookid=b.id LEFT JOIN t_org o ON o.id=b.orgid");
//		where.append(" LEFT JOIN (SELECT COUNT(DISTINCT bd.barcode)totalNum,tb.orgid FROM t_book tb LEFT JOIN t_book_detail bd ON tb.id=bd.bookid WHERE bd.status IN(0,1,2) GROUP BY tb.orgid) d ON d.orgid =o.id ");
//		where.append(" WHERE d.status=1 AND o.parentid="+orgid);
//		where.append(" GROUP BY b.orgid ORDER BY borNum DESC LIMIT 5");
		String val = me.findSchoolids(orgid);
		StringBuffer where = new StringBuffer("SELECT a.orgname,COUNT(DISTINCT d.bookdetailid) as borNum,COUNT(distinct c.id) as totalNum,ifnull(ROUND(COUNT(DISTINCT d.bookdetailid)/COUNT( distinct c.id)*100,0),0) rate " +
				" FROM t_org a " +
				" LEFT JOIN t_book  b on b.orgid = a.id " +
				" LEFT JOIN t_book_detail c ON c.bookid = b.id  and c.bookstatus=1 and c.status not in (3) " +
				" left join t_jieyue d on d.bookdetailid=c.id and d.flag = 0  and  d.createtime > date_sub(curdate(),INTERVAL 1 YEAR)" +
				"WHERE  a.id IN "+val+" GROUP BY a.id  order by borNum desc limit 6");
		return Db.find(where.toString());
	}

	public Object orgWisdomRank(Integer orgid) {
    	String val = me.findSchoolids(orgid);
		Map<String,Object> result = new HashMap<>();
		//查询漂流柜集合
		StringBuilder plg = new StringBuilder("SELECT l.id,o.orgname,IFNULL(d.totalNum,0)totalNum,IFNULL(today.num,0) num,IFNULL(yesterday.num,0) yesNum FROM t_library l ");
		plg.append(" LEFT JOIN t_org o ON l.orgid=o.id ");
		plg.append(" LEFT JOIN (SELECT COUNT(DISTINCT bd.barcode)totalNum,bd.libraryid FROM t_book tb LEFT JOIN t_book_detail bd ON tb.id=bd.bookid WHERE bd.status IN(0,1,2) GROUP BY bd.libraryid) d ON d.libraryid =l.id ");
		plg.append(" LEFT JOIN (SELECT COUNT(DISTINCT d.barcode)num,d.libraryid FROM t_jieyue j LEFT JOIN t_org o ON j.orgid= o.id LEFT JOIN t_book_detail d ON j.bookdetailid=d.id ");
		plg.append(" WHERE (TO_DAYS(j.createtime) = TO_DAYS(NOW()) OR TO_DAYS(j.returntime) = TO_DAYS(NOW())) GROUP BY d.libraryid) today ON today.libraryid=l.id");
		plg.append(" LEFT JOIN (SELECT COUNT(DISTINCT d.barcode)num,d.libraryid FROM t_jieyue j LEFT JOIN t_org o ON j.orgid= o.id LEFT JOIN t_book_detail d ON j.bookdetailid=d.id ");
		plg.append(" WHERE (TO_DAYS(NOW()) - TO_DAYS(j.createtime) = 1 OR TO_DAYS(NOW()) - TO_DAYS(j.returntime) = 1) GROUP BY d.libraryid) yesterday ON yesterday.libraryid=l.id ");
		plg.append(" WHERE o.id IN "+val+" AND l.libraryType=1 ");
		plg.append(" ORDER BY num DESC LIMIT 2");
		List<Record> plgList = Db.find(plg.toString());
		result.put("plgList",plgList);
		//自助借还机
		StringBuilder autoMachine = new StringBuilder("");
		//朗读亭
		StringBuilder readInfo = new StringBuilder("SELECT o.orgname,IFNULL(today.num,0) num,IFNULL(yesterday.num,0) yesNum FROM t_reading_info i ");
		readInfo.append(" LEFT JOIN t_org o ON i.orgid=o.id ");
		readInfo.append(" LEFT JOIN (SELECT COUNT(1)num,w.readinginfoid FROM t_reading_works w WHERE TO_DAYS(w.createDate) = TO_DAYS(NOW()) GROUP BY w.readinginfoid) today ON today.readinginfoid=i.id");
		readInfo.append(" LEFT JOIN (SELECT COUNT(1)num,w.readinginfoid FROM t_reading_works w WHERE TO_DAYS(NOW()) - TO_DAYS(w.createDate) = 1 GROUP BY w.readinginfoid) yesterday ON yesterday.readinginfoid=i.id");
		readInfo.append(" WHERE o.id IN "+val+" ORDER BY num DESC");
		List<Record> readList = Db.find(readInfo.toString());
		result.put("readList",readList);
		return result;
	}

	public Object orgTypeNumber(Integer orgid) {
    	String val = me.findSchoolids(orgid);
		List topTen = Db.find("SELECT a.name,COUNT(c.id) AS value FROM t_booktype a " +
				"LEFT JOIN t_book b ON a.id = b.typeid " +
				"LEFT JOIN t_book_detail c ON b.id = c.bookid " +
				"LEFT JOIN t_jieyue d on d.bookdetailid = c.id " +
				"LEFT JOIN t_org o ON b.orgid = o.id " +
				" WHERE c.status !=3 AND c.bookstatus = 1 AND o.id IN "+val+"  and d.flag=0 and d.createtime > date_sub(curdate(),INTERVAL 1 YEAR) " +
				"  GROUP BY a.id ORDER BY value DESC LIMIT 8");
		return topTen;
    }

	/**
	 * 学校优秀集体
	 * @param orgid
	 * @return
	 */
	public Object excellentDept(Integer orgid) {
		StringBuilder where = new StringBuilder("SELECT COUNT(d.id) num,od.deptname FROM t_jieyue j ");
		where.append(" LEFT JOIN t_reader r ON j.readerid=r.id ");
		where.append(" LEFT JOIN t_book_detail d ON j.bookdetailid=d.id LEFT JOIN t_org_dept od ON od.id=r.orgdeptid  ");
		where.append(" WHERE j.orgid="+orgid+" AND od.deptname IS NOT NULL GROUP BY r.orgdeptid ");
		where.append(" ORDER BY num DESC LIMIT 5");
		return Db.find(where.toString());
	}

	public Object schoolWisdomMonitor(Integer orgid) {
		Map<String,Object> result = new HashMap<>();
		//查询漂流柜集合
		StringBuilder plg = new StringBuilder("SELECT l.id,IFNULL(d.totalNum,0)totalNum,IFNULL(today.num,0) num,IFNULL(yesterday.num,0) yesNum FROM t_library l ");
		plg.append(" LEFT JOIN (SELECT COUNT(DISTINCT bd.barcode)totalNum,bd.libraryid FROM t_book tb LEFT JOIN t_book_detail bd ON tb.id=bd.bookid WHERE bd.status IN(0,1,2) GROUP BY bd.libraryid) d ON d.libraryid =l.id  ");
		plg.append(" LEFT JOIN (SELECT COUNT(DISTINCT d.barcode)num,d.libraryid FROM t_jieyue j LEFT JOIN t_org o ON j.orgid= o.id LEFT JOIN t_book_detail d ON j.bookdetailid=d.id  ");
		plg.append(" WHERE (TO_DAYS(j.createtime) = TO_DAYS(NOW()) OR TO_DAYS(j.returntime) = TO_DAYS(NOW())) GROUP BY d.libraryid) today ON today.libraryid=l.id");
		plg.append(" LEFT JOIN (SELECT COUNT(DISTINCT d.barcode)num,d.libraryid FROM t_jieyue j LEFT JOIN t_org o ON j.orgid= o.id LEFT JOIN t_book_detail d ON j.bookdetailid=d.id ");
		plg.append(" WHERE (TO_DAYS(NOW()) - TO_DAYS(j.createtime) = 1 OR TO_DAYS(NOW()) - TO_DAYS(j.returntime) = 1) GROUP BY d.libraryid) yesterday ON yesterday.libraryid=l.id ");
		plg.append(" WHERE l.orgid="+orgid+" AND l.libraryType=1 ");
		plg.append(" ORDER BY num DESC LIMIT 2");
		List<Record> plgList = Db.find(plg.toString());
		result.put("plgList",plgList);
		//自助借还机
		StringBuilder autoMachine = new StringBuilder("");
		//朗读亭
		StringBuilder readInfo = new StringBuilder("SELECT IFNULL(total.num,0) total,IFNULL(today.num,0) num,IFNULL(yesterday.num,0) yesNum FROM t_reading_info i ");
		readInfo.append(" LEFT JOIN (SELECT COUNT(1)num,w.readinginfoid FROM t_reading_works w WHERE TO_DAYS(w.createDate) = TO_DAYS(NOW()) GROUP BY w.readinginfoid) today ON today.readinginfoid=i.id");
		readInfo.append(" LEFT JOIN (SELECT COUNT(1)num,w.readinginfoid FROM t_reading_works w WHERE TO_DAYS(NOW()) - TO_DAYS(w.createDate) = 1 GROUP BY w.readinginfoid) yesterday ON yesterday.readinginfoid=i.id");
		readInfo.append("  LEFT JOIN (SELECT COUNT(1)num,w.readinginfoid FROM t_reading_works w  GROUP BY w.readinginfoid) total ON total.readinginfoid=i.id");
		readInfo.append(" WHERE i.orgid="+orgid+" ORDER BY num DESC");
		List<Record> readList = Db.find(readInfo.toString());
		result.put("readList",readList);
		return result;
	}

	public String findSchoolids(int orgid){
		List<Record> r_orgidList = Db.find("select id from t_org where  orgtypeid=5 and  parentcodes like ?","%"+orgid+"%");
		StringBuffer values = new StringBuffer();
		for(Record r : r_orgidList){
			int id = r.getInt("id");
			String value = String.valueOf(id);
			values.append(value+",");
		}
		String val = "-1";
		if (values.length() > 0){
			values.deleteCharAt(values.length() - 1);
			//获取该机构下有哪些下级机构
			val = "("+values+")";
		}
		return val;
	}

}
