package com.service;


import com.config.BaseController;
import com.config.Constants;
import com.jfinal.aop.Before;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.IAtom;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.model.BookPenalty;
import com.model.Jieyue;
import com.model.Reader;
import com.model.User;
import com.utils.CommonUtils;
import com.utils.DateUtils;
import jdk.nashorn.internal.objects.annotations.Where;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 借阅
 * 2018-10-19
 * zj
 */
public class JieyueService {


	public static final JieyueService me = new JieyueService();
	private Jieyue dao = new Jieyue().dao();
	private BookPenalty bookPenaltyDao = new BookPenalty().dao();

	public Jieyue findById(int id){
		return dao.findById(id);
	}

	public Page<Jieyue> paginate(int pageNumber,int pagesize, Jieyue bean,String realname){
		String select = "SELECT a.*,b.realname ";
		StringBuilder where = new StringBuilder(" from t_jieyue a LEFT JOIN t_reader b on a.readerid = b.id where 1=1");
		List values = new ArrayList();
		if(StringUtils.isNotBlank(realname)){
			where.append(" and b.realname like ? ");
			values.add("%"+realname+"%");
		}
		return dao.paginate(pageNumber, pagesize,select,where.toString(), CommonUtils.listToArray(values));
	}

	public Page<Record> jieyuerecord(int pageNumber,int pagesize, Jieyue bean,Integer readerid,String beginDate,String endDate,String type){
		String select ="select t.* ";
		StringBuilder sql = new StringBuilder(" from (  ");
		if (type==null || type.equals("0") ||  type.equals("")){
			sql.append(" select '图书' as type, b.barcode,c.title,c.bookno, a.createtime,a.isoverdue,a.duetime,a.returntime  from t_jieyue a " +
					"inner join t_book_detail b on a.bookdetailid=b.id " +
					"inner join t_book c on b.bookid =c.id " +
					"where flag=0");

			if (readerid !=null) {
				sql.append(" and  a.readerid=").append(readerid);
			}
			if (StringUtils.isNotBlank(beginDate)){
				sql.append(" and a.createtime>='").append(beginDate).append("' ");
			}
			if (StringUtils.isNotBlank(beginDate)){
				sql.append(" and a.createtime<='").append(endDate).append("' ");
			}
		}

		if ( type==null || type.equals("") ) {
			sql.append(" union all ");
		}

		if (type==null || type.equals("1")||  type.equals("")) {
			sql.append(" select '期刊' as type, b.barcode,c.kanming,c.bookno, a.createtime,a.isoverdue,a.duetime,a.returntime ");
			sql.append(" from t_jieyue a inner join t_periodical_detail b on a.bookdetailid=b.id inner join t_periodical_zhengding c on b.periodicalid =c.id where flag=1 ");

			if (readerid !=null) {
				sql.append(" and  a.readerid=").append(readerid);
			}
			if (StringUtils.isNotBlank(beginDate)){
				sql.append(" and a.createtime>='").append(beginDate).append("' ");
			}
			if (StringUtils.isNotBlank(beginDate)){
				sql.append(" and a.createtime<='").append(endDate).append("' ");
			}
		}

		sql.append(" ) as t ");

		return Db.paginate(pageNumber, pagesize,select, sql.toString());
	}

	public void deleteById(int id) {
		dao.deleteById(id);
	}

	public void add(Jieyue bean, int operatorid){
		if(bean.getId()== null){
			Db.tx(new IAtom() {
				@Override
				public boolean run() throws SQLException {
					bean.setStatus(0).setContinuenum(0).setCreatetime(new Date()).setOperatorid(operatorid).save();
					//更改馆藏明细书本状态
					Db.update("UPDATE t_book_detail SET `status`=1,jieyuenums=jieyuenums+1,updatetime=?,updateuserid=? WHERE id =? ",new Date(),operatorid,bean.getBookdetailid());
					return true;
				}
			});
		}
	}

	public void still(int id,int operatorid,int bookdetailid){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Db.update("UPDATE t_jieyue set `status` = 1,returntime = ?,returnuserid = ? WHERE id = ? ",new Date(),operatorid,id);
				//更改馆藏明细书本状态
				Db.update("UPDATE t_book_detail SET `status`=0,updatetime=?,updateuserid=? WHERE id =? ",new Date(),operatorid,bookdetailid);
				return true;
			}
		});
	}

	/**
	 * 通过条形码和机构id 查询书本借阅信息
	 * @param barCode 条形码
	 * @param type 类型 0 书本 1 期刊
	 */
	public Record findByBarCode(String barCode,int type,int orgid) {
		if (type == 0) {
			Record record = Db.findFirst("SELECT " +
					"'0' AS booktype," +
					"d.id detailid," +
					"b.id bookid," +
					"d. STATUS bookstatus," +
					"b.title," +
					"b.bookno," +
					"b.author," +
					"b.price," +
					"b.publisher," +
					"l.library," +
					"ifnull(l.isopen, 1) isopen " +
					"FROM " +
					"t_book b " +
					"INNER JOIN t_book_detail d ON b.id = d.bookid " +
					"INNER JOIN t_library l ON d.libraryid = l.id " +
					"WHERE " +
					"d.barcode = ? " +
					"AND b.orgid = ? " +
					"AND d.bookstatus = 1 ",barCode,orgid);
			if(record == null){
				return null;
			}
			Record r = Db.findFirst("select * from t_jieyue j where bookdetailid=? and orgid=? and j.flag=0 order by status, createtime desc",record.get("detailid"),orgid);
			if(r == null){
				r = new Record();
			}
			r.set("booktype", record.get("booktype"));
			r.set("detailid", record.get("detailid"));
			r.set("bookid", record.get("bookid"));
			r.set("bookstatus", record.get("bookstatus"));
			r.set("title", record.get("title"));
			r.set("bookno", record.get("bookno"));
			r.set("author", record.get("author"));
			r.set("price", record.get("price"));
			r.set("publisher", record.get("publisher"));
			r.set("library", record.get("library"));
			r.set("isopen", record.get("isopen"));
			return r;
		} else if (type == 1) {
			return Db.findFirst("select '1' as booktype,d.id detailid,p.id bookid,d.status bookstatus,p.kanming title,p.bookno,d.niandu author,d.price,d.qizhiqi publisher,l.library,ifnull(l.isopen,1) isopen,j.* from t_periodical_zhengding p inner join t_periodical_detail d on p.id = d.periodicalid left join t_jieyue j on j.bookdetailid = d.id and j.flag = 1 inner join t_library l on d.libraryid = l.id where d.barcode = ? and p.orgid=? and d.bookstatus=1 ORDER BY j.status asc,j.createtime desc",barCode,orgid);
		} else {
			return null;
		}
	}

	public Record findByBarCode(String barCode,int type,int orgid,int status) {
		if (type == 0) {
			return Db.findFirst("select '0' as booktype,b.id bookid,b.title,b.bookno,b.author,b.price,b.publisher,l.library,ifnull(l.isopen,1) isopen,j.* from t_book b inner JOIN t_book_detail d on b.id = d.bookid left join t_jieyue j on j.bookdetailid = d.id and j.flag = 0 inner join t_library l on d.libraryid = l.id where d.barcode = ? and b.orgid=? and d.bookstatus=1 and j.status=? ORDER BY j.status asc,j.createtime desc",barCode,orgid,status);
		} else if (type == 1) {
			return Db.findFirst("select '1' as booktype,p.id bookid,p.kanming title,p.bookno,d.niandu author,d.price,d.qizhiqi publisher,l.library,ifnull(l.isopen,1) isopen,j.* from t_periodical_zhengding p inner join t_periodical_detail d on p.id = d.periodicalid left join t_jieyue j on j.bookdetailid = d.id and j.flag = 1 inner join t_library l on d.libraryid = l.id where d.barcode = ? and p.orgid=? and d.bookstatus=1 and j.status=? and j.flag = 1 ORDER BY j.status asc,j.createtime desc",barCode,orgid,status);
		} else {
			return null;
		}
	}

	/**
	 * 查询读者信息以及借阅记录
	 * @param cardnumber
	 * @return
	 */
	public Map findReaderInfoAndJieyueListByCardnumber(String cardnumber,int orgid,boolean userInfoFlag){
		Date date = new Date();
		Map remap = new HashMap();
		List<Record> list = Db.find("select * from (" +
				"select '0' as booktype,b.price,b.id bookid,d.id detailid,j.id jieyueid,d.barcode,b.title,b.bookno,j.createtime,j.flag,j.duetime huandate,j.isoverdue,r.realname,r.cardnumber,a.deptname from t_jieyue j left join t_book_detail d on j.bookdetailid = d.id left join t_book b on d.bookid = b.id left join t_reader r on j.readerid = r.id LEFT JOIN t_org_dept a ON r.orgdeptid = a.id where j.flag=0 and j.status=0 and r.cardnumber = '"+cardnumber+"' and b.orgid='"+orgid+"'" +
				"union " +
				"select '1' as booktype,d.price,p.id bookid,d.id detailid,j.id jieyueid,d.barcode,p.kanming title,p.bookno,j.createtime,j.flag,j.duetime huandate,j.isoverdue,r.realname,r.cardnumber,a.deptname from t_jieyue j left join t_periodical_detail d on j.bookdetailid = d.id left join t_periodical_zhengding p on d.periodicalid = p.id left join t_reader r on j.readerid = r.id LEFT JOIN t_org_dept a ON r.orgdeptid = a.id where j.flag=1 and j.status=0 and r.cardnumber = '"+cardnumber+"' and p.orgid='"+orgid+"' " +
				") t ORDER BY createtime desc");
		remap.put("list",list);
		if(userInfoFlag) {
			Record record = findReaderInfoByCardnum(cardnumber,orgid);
			if(CommonUtils.isNullOrEmpty(record)) {
				remap.put("userInfo", null);
			} else {
				//record.set("jieyueshuliang", record.getInt("jieyueshuliang") - list.size());
				remap.put("userInfo", record);
			}
		}
		return remap;
	}

	public Page<Record> findJieyueListByCardnumber(int pageNumber,int pagesize,String cardnumber,int orgid){
		List list = new ArrayList();
		String select = "select * ";
		StringBuffer where = new StringBuffer();
		where.append("from (select * from (");
		where.append("(select '0' as booktype,b.price,b.id bookid,d.id detailid,j.id jieyueid,d.barcode,b.title,b.bookno,j.createtime,j.flag,j.duetime huandate,j.isoverdue,j.status,r.realname,r.cardnumber,a.deptname from t_jieyue j left join t_book_detail d on j.bookdetailid = d.id left join t_book b on d.bookid = b.id left join t_reader r on j.readerid = r.id LEFT JOIN t_org_dept a ON r.orgdeptid = a.id where j.flag=0 ");
		if(CommonUtils.isNotNullOrNotEmpty(cardnumber)) {
			where.append(" and r.cardnumber = ? and j.status = 0 ");
			list.add(cardnumber);
		}
		if(CommonUtils.isNotNullOrNotEmpty(orgid)) {
			where.append(" and b.orgid=? ");
			list.add(orgid);
		}
		if(CommonUtils.isNullOrEmpty(cardnumber)) {
			where.append(" order by j.id desc limit 0,100)");
		} else {
			where.append(" order by j.id desc)");
		}
		where.append(" union ");
		where.append(" (select '1' as booktype,d.price,p.id bookid,d.id detailid,j.id jieyueid,d.barcode,p.kanming title,p.bookno,j.createtime,j.flag,j.duetime huandate,j.isoverdue,j.status,r.realname,r.cardnumber,a.deptname from t_jieyue j left join t_periodical_detail d on j.bookdetailid = d.id left join t_periodical_zhengding p on d.periodicalid = p.id left join t_reader r on j.readerid = r.id LEFT JOIN t_org_dept a ON r.orgdeptid = a.id where j.flag=1 ");
		if(CommonUtils.isNotNullOrNotEmpty(cardnumber)) {
			where.append(" and r.cardnumber = ?  and j.status = 0 ");
			list.add(cardnumber);
		}
		if(CommonUtils.isNotNullOrNotEmpty(orgid)) {
			where.append(" and p.orgid=? ");
			list.add(orgid);
		}
		if(CommonUtils.isNullOrEmpty(cardnumber)) {
			where.append(" order by j.id desc limit 0,100)");
		} else {
			where.append(" order by j.id desc)");
		}
		where.append(" )  t ORDER BY createtime desc");
		if(CommonUtils.isNullOrEmpty(cardnumber)) {
			where.append(" limit 0,100");
		}
		where.append(" )  tt");
		return Db.paginate(pageNumber,pagesize,select,where.toString(),CommonUtils.listToArray(list));
	}

	public List findJieyueListByCardnumber(String cardnumber,int orgid){
		Date date = new Date();
		List list = new ArrayList();
		String select = "select * ";
		StringBuffer where = new StringBuffer();
		where.append(" from (");
		where.append("select '0' as booktype,b.price,b.id bookid,d.id detailid,j.id jieyueid,d.barcode,b.title,b.bookno,j.createtime,j.flag,j.duetime huandate,j.isoverdue from t_jieyue j left join t_book_detail d on j.bookdetailid = d.id left join t_book b on d.bookid = b.id left join t_reader r on j.readerid = r.id where j.flag=0 and j.status=0 ");

		if(CommonUtils.isNotNullOrNotEmpty(cardnumber)) {
			where.append(" and r.cardnumber = ? ");
			list.add(cardnumber);
		}
		if(CommonUtils.isNotNullOrNotEmpty(orgid)) {
			where.append(" and b.orgid=? ");
			list.add(orgid);
		}
		where.append(" union ");
		where.append(" select '1' as booktype,d.price,p.id bookid,d.id detailid,j.id jieyueid,d.barcode,p.kanming title,p.bookno,j.createtime,j.flag,j.duetime huandate,j.isoverdue from t_jieyue j left join t_periodical_detail d on j.bookdetailid = d.id left join t_periodical_zhengding p on d.periodicalid = p.id left join t_reader r on j.readerid = r.id where j.flag=1 and j.status=0 ");
		if(CommonUtils.isNotNullOrNotEmpty(cardnumber)) {
			where.append(" and r.cardnumber = ? ");
			list.add(cardnumber);
		}
		if(CommonUtils.isNotNullOrNotEmpty(orgid)) {
			where.append(" and p.orgid=? ");
			list.add(orgid);
		}
		where.append(" )  t ORDER BY createtime desc");
		return Db.find(select+where.toString(),CommonUtils.listToArray(list));
	}

	public Page<Record> findJieyueListInfoByOrgid(int pageNumber,int pagesize,int orgid){
		String select = "select r.cardnumber,r.realname,deptname,d.barcode,b.title,j.`status`";
		StringBuffer where = new StringBuffer("from t_jieyue j inner join t_reader r on j.readerid = r.id inner join t_org_dept od on r.orgdeptid = od.id inner join t_book_detail d on j.bookdetailid = d.id left join t_book b on d.bookid = b.id where j.orgid = ? and DATE_FORMAT(j.createtime,'%Y-%m-%d') >= DATE_FORMAT(NOW(),'%Y-%m-%d') order by j.id desc");
		return Db.paginate(pageNumber, pagesize,select,where.toString(),orgid);
	}

	public Record findReaderInfoByCardnum(String cardnumber,int orgid){
		Record r = Db.findFirst("select r.id,r.headimgurl,r.cardnumber,r.realname,d.deptname,ifnull(a.jieyueshuliang,0) jieyueshuliang,(select count(1) from t_jieyue where status=0 and readerid=r.id) yijieshuliang,r.status,r.losedate from t_reader r left join t_org_dept d on r.orgdeptid = d.id left join t_reader_auth a on r.readerauthid = a.id where r.orgid = ? and (r.cardnumber = ? or r.readcardcode=?)",orgid,cardnumber,cardnumber);
		if(r != null){
			int jieyueshuliang = r.getInt("jieyueshuliang");
			int yijieshuliang = r.getInt("yijieshuliang");
			r.set("jieyueshuliang", jieyueshuliang-yijieshuliang);
		}

		return r;

	}

	public Record findReaderInfoByCardnum(int readerid,int orgid){
		Record r = Db.findFirst("select r.id,r.headimgurl,r.cardnumber,r.sex,r.realname,d.deptname,a.jieyueshuliang,(select count(1) from t_jieyue where status=0 and readerid=r.id) yijieshuliang,r.status,r.losedate from t_reader r left join t_org_dept d on r.orgdeptid = d.id left join t_reader_auth a on r.readerauthid = a.id where r.orgid = ? and r.id = ?",orgid,readerid);
		if(r != null){
			int jieyueshuliang = r.getInt("jieyueshuliang");
			int yijieshuliang = r.getInt("yijieshuliang");
			r.set("jieyueshuliang", jieyueshuliang-yijieshuliang);
		}
		return r;
	}

	/**
	 * 还书 如果超期 则保存用户超期记录 并将借阅记录和书本状态修改
	 * @param yujieRecordId
	 * @param readerid
	 * @param detailid
	 * @param operatorid
	 */
	public void returnBook(int yujieRecordId, int readerid, int detailid, int operatorid, int type){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Date date = new Date();
//				if(overdueStatus) { // 超期
//					Db.update("update t_reader set overduetime = ?,overdue=1 where id=?",date,readerid);
//				}
				Db.update("update t_jieyue set status = 1,isoverdue=2,returntime=?,returnuserid=? where id = ?",date,operatorid,yujieRecordId);
				Db.update("update t_jieyue set continuenum=0 where readerid = ? and bookdetailid=?",readerid,detailid);
				String updatesql = "update t_book_detail ";
				if(type == 1) {
					updatesql = "update t_periodical_detail ";
				}
				Db.update(updatesql+" set status = 0 where status=1 and id = ?",detailid);
				return true;
			}
		});

		Db.update("update t_jieyue set isoverdue = 1 where id = ? and duetime < returntime",yujieRecordId);
	}

	public void findOverdueInfo(int readerid){
		Record record = Db.findFirst("select count(1) count from t_jieyue where isoverdue = 1 and status = 0 and readerid = ?",readerid);
		if(record.getInt("count") == 0) {
			Db.update("update t_reader set overdue = null,overduetime =null where id = ?",readerid);
		}
	}

	/**
	 * 借书 保存借阅信息 修改书本状态
	 */
	public void borrowBooks(Jieyue jieyue,int type,int borrowType,Record yuyueRecord,int readerid,int detailid){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				jieyue.save();
				if(1 == borrowType && null != yuyueRecord) {
					int yuyueReaderid = yuyueRecord.getInt("readerid");
					if(readerid == yuyueReaderid) {
						String yuyuetablename = "t_yuyue";
						if("yujie".equals(yuyueRecord.getStr("yuyuetype"))){
							yuyuetablename = "t_yujie";
						}
						Db.update("update "+yuyuetablename+" set `status` = 2 where deletestatus=0 and id = ?",yuyueRecord.getInt("id"));
					}
				}

				String tablename="t_book_detail";
				if (type == 1) {
					tablename = "t_periodical_detail ";
				}

				if(1 != borrowType && 0 != borrowType) {
					String yuyuetablename = "t_yuyue";
					if("yujie".equals(yuyueRecord.getStr("yuyuetype"))){
						yuyuetablename = "t_yujie";
					}
					int yuyueid = yuyueRecord.getInt("id");
					Db.update("update "+tablename +" set status=2 where id = ?"+borrowType); //找出的在馆副本的状态修改为2 已预订 且更新到预约表
					Db.update("update "+yuyuetablename+" set bookdetailid = ? where deletestatus=0 and id = ?",borrowType,yuyueid); //替换预约书籍id
					//Db.update("update "+tablename +" set status=1 where id = ?"+detailid); //detailid 状态修改为1(原本是2 已预约 现在换成了另一个副本被预约)
				}
				Db.update("update " +tablename + " set status = 1,jieyuenums = ifnull(jieyuenums,0)+1 where id = ?",detailid);
				return true;
			}
		});
	}

	public Record findDetailListByBookIdAndExist(int bookid){
		return Db.findFirst("select d.id from t_book_detail d left join t_book b on d.bookid = b.id where b.id=? and d.`status`=0 " +
				" UNION " +
				" select d.id from t_periodical_detail d left join t_periodical_zhengding p on d.periodicalid=p.id where p.id=? and d.`status`=0",bookid,bookid);
	}

	/**
	 * 统计用户的借阅数量
	 * @param readerid
	 * @return
	 */
	public int countJieyueBooks(int readerid){
		Record record = Db.findFirst("select count(1) jieyuenum from t_jieyue where readerid=? and status=0",readerid);
		int jieyuenum = record.getInt("jieyuenum");
		return jieyuenum;
	}
	/**
	 * 图书流通记录
	 *
	 */
	public Page<Record> jieyueRecord(int pageNumber,int pagesize,int orgid,Integer record,String kssj,String jssj,Integer dept,
									 Integer overdue,String cardnumber,String title){

		String select = "SELECT b.barcode,c.isbn,c.title,c.bookno,d.cardnumber,d.realname,e.deptname,a.createtime,a.returntime,a.duetime,a.isoverdue ";
		StringBuilder where = new StringBuilder(" FROM t_jieyue a LEFT JOIN t_book_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_book c ON b.bookid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id WHERE a.flag = 0 AND a.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(StringUtils.isBlank(kssj) && StringUtils.isBlank(jssj)){
			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime >= ?");
			list.add(kssj+" 00:00:00");
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime <= ?");
			list.add(jssj+" 23:59:59");
		}
		//record 查询记录 1为全部记录 2为在借记录 3为借阅历史
		if(record != null){
			if(record == 1){
			}else if(record == 2){
				where.append(" AND a.status = 0 ");
			}else if(record == 3){
				where.append(" AND (a.status = 1 or a.status = 2) ");
			}
		}
		//flag 0为图书，1为期刊
//		if(flag != null){
//			if(flag == 0){
//				where.append(" AND a.flag = 0");
//			}else if(flag == 1){
//				where.append(" AND a.flag = 1");
//			}
//		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber like ? OR d.realname like ?)");
			list.add("%"+cardnumber+"%");
			list.add("%"+cardnumber+"%");
		}

		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}

		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		if(overdue != null){
			if(overdue == 1){
				where.append(" AND a.isoverdue = 1");
			}else if(overdue == 2){
				//where.append(" AND a.isoverdue = 2");
				where.append(" AND (a.isoverdue <> 1 OR a.isoverdue is NULL) ");
			}
		}
		where.append(" ORDER BY a.createtime DESC");
		return  Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
	}

	public Page<Record> jieyueRecord(int pageNumber,int pagesize,int orgid,Integer record,String kssj,String jssj,Integer dept,
									 Integer overdue,String cardnumber,String title,String order,String txm,String bm,Integer library){

		String select = "SELECT b.barcode,c.price,c.isbn,c.title,c.bookno,d.cardnumber,d.realname,e.deptname,a.createtime,a.returntime,a.duetime,a.isoverdue ";
		StringBuilder where = new StringBuilder(" FROM t_jieyue a LEFT JOIN t_book_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_book c ON b.bookid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id WHERE a.flag = 0 AND a.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(StringUtils.isBlank(kssj) && StringUtils.isBlank(jssj) && record != 2){
			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj) && record != 2){
			where.append(" AND a.createtime >= ?");
			list.add(kssj+" 00:00:00");
		}
		if(StringUtils.isNotBlank(jssj) && record != 2){
			where.append(" AND a.createtime <= ?");
			list.add(jssj+" 23:59:59");
		}

		if(StringUtils.isNotBlank(txm)){
			where.append(" AND b.barcode like ?");
			list.add("%"+txm+"%");
		}
		if(CommonUtils.isNotNullOrNotEmpty(bm)){
			where.append(" AND e.deptname like ?");
			list.add("%"+bm+"%");
		}
		if(0 != library){
			where.append(" AND b.libraryid = ?");
			list.add(library);
		}
		//record 查询记录 1为全部记录 2为在借记录 3为借阅历史
		if(record != null){
			if(record == 1){
			}else if(record == 2){
				where.append(" AND a.status = 0 ");
			}else if(record == 3){
				where.append(" AND (a.status = 1 or a.status = 2) ");
			}
		}
		//flag 0为图书，1为期刊
//		if(flag != null){
//			if(flag == 0){
//				where.append(" AND a.flag = 0");
//			}else if(flag == 1){
//				where.append(" AND a.flag = 1");
//			}
//		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber like ? OR d.realname like ?)");
			list.add("%"+cardnumber+"%");
			list.add("%"+cardnumber+"%");
		}

		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}

		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		if(overdue != null){
			if(overdue == 1){
				where.append(" AND a.isoverdue = 1");
			}else if(overdue == 2){
				//where.append(" AND a.isoverdue = 2");
				where.append(" AND (a.isoverdue <> 1 OR a.isoverdue is NULL) ");
			}
		}
		if(CommonUtils.isNotNullOrNotEmpty(order)) {
			where.append(" ORDER BY a.createtime ").append(order);
		} else {
			where.append(" ORDER BY a.createtime DESC");
		}
		return  Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
	}

	/**
	 * 图书流通记录 --导出
	 *
	 */
	public List<Record> exportJieyueRecord(int orgid,Integer record,String kssj,String jssj,Integer dept,
										   Integer overdue,String cardnumber,String title,String order,String txm,String bm,Integer library){

		String select = "SELECT b.barcode,c.isbn,c.title,c.price,c.bookno,d.cardnumber,d.realname,e.deptname,a.createtime,a.returntime,a.duetime,a.isoverdue ";
		StringBuilder where = new StringBuilder(" FROM t_jieyue a LEFT JOIN t_book_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_book c ON b.bookid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id WHERE a.flag = 0 AND a.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(StringUtils.isBlank(kssj) && StringUtils.isBlank(jssj) && record != 2){
			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj) && record != 2){
			where.append(" AND a.createtime >= ?");
			list.add(kssj+" 00:00:00");
		}
		if(StringUtils.isNotBlank(jssj) && record != 2){
			where.append(" AND a.createtime <= ?");
			list.add(jssj+" 23:59:59");
		}

		if(StringUtils.isNotBlank(txm)){
			where.append(" AND b.barcode like ?");
			list.add("%"+txm+"%");
		}
		if(CommonUtils.isNotNullOrNotEmpty(bm)){
			where.append(" AND e.deptname like ?");
			list.add("%"+bm+"%");
		}
		if(0 != library){
			where.append(" AND b.libraryid = ?");
			list.add(library);
		}
		//record 查询记录 1为全部记录 2为在借记录 3为借阅历史
		if(record != null){
			if(record == 1){
			}else if(record == 2){
				where.append(" AND a.status = 0 ");
			}else if(record == 3){
				where.append(" AND (a.status = 1 or a.status = 2) ");
			}
		}
		//flag 0为图书，1为期刊
//		if(flag != null){
//			if(flag == 0){
//				where.append(" AND a.flag = 0");
//			}else if(flag == 1){
//				where.append(" AND a.flag = 1");
//			}
//		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber like ? OR d.realname like ?)");
			list.add("%"+cardnumber+"%");
			list.add("%"+cardnumber+"%");
		}

		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}

		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		if(overdue != null){
			if(overdue == 1){
				where.append(" AND a.isoverdue = 1");
			}else if(overdue == 2){
				//where.append(" AND a.isoverdue = 2");
				where.append(" AND (a.isoverdue <> 1 OR a.isoverdue is NULL) ");
			}
		}

		if(CommonUtils.isNotNullOrNotEmpty(order)) {
			where.append(" ORDER BY a.createtime ").append(order);
		} else {
			where.append(" ORDER BY a.createtime DESC");
		}		String sql = select+where.toString();
		return Db.find(sql,CommonUtils.listToArray(list));
	}


	/**
	 * 期刊流通记录
	 *
	 */
	public Page<Record> jieyueRecordQ(int pageNumber,int pagesize,int orgid,Integer record,String kssj,String jssj,Integer dept,
									  Integer overdue,String cardnumber,String title,String txm){

		String select = "SELECT b.barcode,c.issn,c.kanming,c.bookno,d.cardnumber,d.realname,e.deptname,a.createtime,a.returntime,a.duetime,a.isoverdue,d.orgdeptid,b.niandu,b.qizhiqi ";
		StringBuilder where = new StringBuilder(" FROM t_jieyue a LEFT JOIN t_periodical_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_periodical_zhengding c ON b.periodicalid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id WHERE a.flag = 1 AND a.orgid = ? and c.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);list.add(orgid);
		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime >= ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime <= ?");
			list.add(jssj+" 23:59:59");
		}

		if(StringUtils.isNotBlank(txm)){
			where.append(" AND b.barcode like ?");
			list.add("%"+txm+"%");
		}
		//record 查询记录 1为全部记录 2为在借记录 3为借阅历史
		if(record != null){
			if(record == 1){
			}else if(record == 2){
				where.append(" AND a.status = 0");
			}else if(record == 3){
				where.append(" AND (a.status = 1 or a.status = 2)");
			}
		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber like ? OR d.realname like ?)");
			list.add("%"+cardnumber+"%");
			list.add("%"+cardnumber+"%");
		}

		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}

		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.kanming like ?");
			list.add("%"+title+"%");
		}
		if(overdue != null){
			if(overdue == 1){
				where.append(" AND a.isoverdue = 1");
			}else if(overdue == 2){
				//where.append(" AND a.isoverdue = 2");
				where.append(" AND (a.isoverdue != 1 OR a.isoverdue IS NULL)");
			}
		}
		where.append(" ORDER BY a.createtime DESC");
		return  Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
	}

	/**
	 * 期刊流通记录 --export
	 *
	 */
	public List<Record> exportJieyueRecordQ(int orgid,Integer record,String kssj,String jssj,Integer dept,
											Integer overdue,String cardnumber,String title,String txm){

		String select = "SELECT b.barcode,c.issn,c.kanming,c.bookno,d.cardnumber,d.realname,e.deptname,a.createtime,a.returntime,a.duetime,a.isoverdue,d.orgdeptid ";
		StringBuilder where = new StringBuilder(" FROM t_jieyue a LEFT JOIN t_periodical_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_periodical_zhengding c ON b.periodicalid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id WHERE a.flag = 1 AND c.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime > ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime <?");
			list.add(jssj+" 23:59:59");
		}
		if(StringUtils.isNotBlank(txm)){
			where.append(" AND b.barcode like ?");
			list.add("%"+txm+"%");
		}
		//record 查询记录 1为全部记录 2为在借记录 3为借阅历史
		if(record != null){
			if(record == 1){
				where.append(" AND a.status != 2");
			}else if(record == 2){
				where.append(" AND a.status = 0");
			}else if(record == 3){
				where.append(" AND a.status = 1");
			}
		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber = ? OR d.realname = ?)");
			list.add(cardnumber);
			list.add(cardnumber);
		}

		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}

		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.kanming = ?");
			list.add(title);
		}
		if(overdue != null){
			if(overdue == 1){
				where.append(" AND a.isoverdue = 1");
			}else if(overdue == 2){
				//where.append(" AND a.isoverdue = 2");
				where.append(" AND (a.isoverdue != 1 OR a.isoverdue IS NULL)");
			}
		}
		where.append(" ORDER BY a.createtime DESC");
		String sql = select+where.toString();
		return Db.find(sql,CommonUtils.listToArray(list));
	}

	/**
	 * 图书丢书记录
	 *
	 */
	public Page<Record> loseBook(int pageNumber,int pagesize,int orgid,String kssj,String jssj,Integer dept,
								 Integer cancel,String cardnumber,String title){

		String select = "SELECT b.id as detailid,b.status,b.barcode,c.isbn,c.title,c.bookno,d.cardnumber,d.realname,e.deptname,a.createtime,f.duetime,a.remarks,f.createtime as fcreatetime ";
		StringBuilder where = new StringBuilder(" FROM t_book_penalty a " +
				"LEFT JOIN t_book_detail b ON a.bookdetailid = b.id LEFT JOIN t_book c ON b.bookid = c.id " +
				"LEFT JOIN t_jieyue f ON a.jieyueid = f.id LEFT JOIN t_reader d ON f.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id " +
				"WHERE a.type = 0 AND a.status = 0 AND b.status != 0 AND a.flag = 0 AND c.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
//		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
//			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
//		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime > ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime < ?");
			list.add(jssj+" 23:59:59");
		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber like ? OR d.realname like ?)");
			list.add("%"+cardnumber+"%");
			list.add("%"+cardnumber+"%");
		}
		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}
		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		if(cancel != null){
			if(cancel == 1){
				where.append(" AND b.status = 3");
			}else if(cancel == 2){
				where.append(" AND b.status != 3");
			}
		}
		where.append(" ORDER BY a.createtime DESC");
		return  Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
	}

	/**
	 * 图书丢书记录 --导出
	 *
	 */
	public List<Record> exportLoseBook(int orgid,String kssj,String jssj,Integer dept,
									   Integer cancel,String cardnumber,String title){

		String select = "SELECT b.id as detailid,b.status,b.barcode,c.isbn,c.title,c.bookno,d.cardnumber,d.realname,e.deptname,DATE_FORMAT(a.createtime,'%Y-%m-%d') createtime,DATE_FORMAT(f.duetime,'%Y-%m-%d') duetime,a.remarks,DATE_FORMAT(f.createtime,'%Y-%m-%d') fcreatetime ";
		StringBuilder where = new StringBuilder(" FROM t_book_penalty a LEFT JOIN t_book_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_book c ON b.bookid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id LEFT JOIN t_jieyue f ON a.jieyueid = f.id " +
				"WHERE a.type = 0 AND a.status = 0 AND a.flag = 0 AND c.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
//		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
//			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
//		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime >= ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime <=?");
			list.add(jssj+" 23:59:59");
		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber = ? OR d.realname = ?)");
			list.add(cardnumber);
			list.add(cardnumber);
		}
		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}
		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		if(cancel != null){
			if(cancel == 1){
				where.append(" AND b.status = 3");
			}else if(cancel == 2){
				where.append(" AND b.status != 3");
			}
		}
		where.append(" ORDER BY a.createtime DESC");
		String sql = select+where.toString();
		return Db.find(sql,CommonUtils.listToArray(list));
	}

	/**
	 * 期刊丢书记录
	 *
	 */
	public Page<Record> loseBookQ(int pageNumber,int pagesize,int orgid,String kssj,String jssj,Integer dept,
								  Integer cancel,String cardnumber,String title){

		String select = "SELECT b.id as detailid,b.status,b.barcode,c.issn,c.kanming,c.bookno,d.cardnumber,d.realname,e.deptname,a.createtime,f.duetime,a.remarks,f.createtime as fcreatetime ";
		StringBuilder where = new StringBuilder(" FROM t_book_penalty a LEFT JOIN t_periodical_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_periodical_zhengding c ON b.periodicalid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id LEFT JOIN t_jieyue f ON a.jieyueid = f.id " +
				"WHERE a.type = 0 AND a.status = 0 AND a.flag = 1 AND c.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
//		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
//			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
//		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime > ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime < ?");
			list.add(jssj+" 23:59:59");
		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber like ? OR d.realname like ?)");
			list.add("%"+cardnumber+"%");
			list.add("%"+cardnumber+"%");
		}
		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}
		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.kanming like ?");
			list.add("%"+title+"%");
		}
		if(cancel != null){
			if(cancel == 1){
				where.append(" AND b.status = 3");
			}else if(cancel == 2){
				where.append(" AND b.status != 3");
			}
		}
		where.append(" ORDER BY a.createtime DESC");
		return  Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
	}

	/**
	 * 期刊丢书记录 -- export
	 *
	 */
	public List<Record> exportLoseBookQ(int orgid,String kssj,String jssj,Integer dept,
										Integer cancel,String cardnumber,String title){

		String select = "SELECT b.id as detailid,b.status,b.barcode,c.issn,c.kanming,c.bookno,d.cardnumber,d.realname,e.deptname,DATE_FORMAT(a.createtime,'%Y-%m-%d') createtime,DATE_FORMAT(f.duetime,'%Y-%m-%d') duetime,a.remarks,DATE_FORMAT(f.createtime,'%Y-%m-%d') fcreatetime ";
		StringBuilder where = new StringBuilder(" FROM t_book_penalty a LEFT JOIN t_periodical_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_periodical_zhengding c ON b.periodicalid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id LEFT JOIN t_jieyue f ON a.jieyueid = f.id " +
				"WHERE a.type = 0 AND a.status = 0 AND a.flag = 1 AND c.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
//		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
//			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
//		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime > '"+kssj+"'");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime < ?");
			list.add(jssj+" 23:59:59");
		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber = ? OR d.realname = ?)");
			list.add(cardnumber);
			list.add(cardnumber);
		}
		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}
		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.kanming like ?");
			list.add("%"+title+"%");
		}
		if(cancel != null){
			if(cancel == 1){
				where.append(" AND b.status = 3");
			}else if(cancel == 2){
				where.append(" AND b.status != 3");
			}
		}
		where.append(" ORDER BY a.createtime DESC");
		String sql = select+where.toString();
		return Db.find(sql,CommonUtils.listToArray(list));
	}

	/**
	 * 图书损书记录
	 *
	 */
	public Page<Record> damagedBook(int pageNumber,int pagesize,int orgid,String kssj,String jssj,Integer dept,String cardnumber,String title){

		String select = "SELECT b.barcode,c.isbn,c.title,c.bookno,d.cardnumber,d.realname,e.deptname,a.createtime,a.remarks,f.createtime as fcreatetime,f.returntime ";
		StringBuilder where = new StringBuilder(" FROM t_book_penalty a LEFT JOIN t_book_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_book c ON b.bookid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id LEFT JOIN t_jieyue f ON a.jieyueid = f.id " +
				"WHERE a.type = 1 AND a.flag = 0 AND c.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
//		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
//			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
//		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime > ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime < ?");
			list.add(jssj+" 23:59:59");
		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber like ? OR d.realname like ?)");
			list.add("%"+cardnumber+"%");
			list.add("%"+cardnumber+"%");
		}
		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}
		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		where.append(" ORDER BY a.createtime DESC");
		return  Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
	}

	/**
	 * 图书损书记录 --export
	 *
	 */
	public List<Record> exportDamagedBook(int orgid,String kssj,String jssj,Integer dept,String cardnumber,String title){

		String select = "SELECT b.barcode,c.isbn,c.title,c.bookno,d.cardnumber,d.realname,e.deptname,DATE_FORMAT(a.createtime,'%Y-%m-%d') createtime,a.remarks,DATE_FORMAT(f.createtime,'%Y-%m-%d') fcreatetime,DATE_FORMAT(f.returntime,'%Y-%m-%d') returntime ";
		StringBuilder where = new StringBuilder(" FROM t_book_penalty a LEFT JOIN t_book_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_book c ON b.bookid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id LEFT JOIN t_jieyue f ON a.jieyueid = f.id " +
				"WHERE a.type = 1 AND a.flag = 0 AND c.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
//		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
//			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
//		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime >= ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime <= ?");
			list.add(jssj+" 23:59:59");
		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber = ? OR d.realname = ?)");
			list.add(cardnumber);
			list.add(cardnumber);
		}
		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}
		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		where.append(" ORDER BY a.createtime DESC");
		String sql = select+where.toString();
		return Db.find(sql,CommonUtils.listToArray(list));
	}


	/**
	 * 期刊损书记录
	 *
	 */
	public Page<Record> damagedBookQ(int pageNumber,int pagesize,int orgid,String kssj,String jssj,Integer dept,String cardnumber,String title){

		String select = "SELECT b.barcode,c.issn,c.kanming,c.bookno,d.cardnumber,d.realname,e.deptname,a.createtime,a.remarks,f.createtime as fcreatetime,f.returntime,b.niandu,b.qizhiqi ";
		StringBuilder where = new StringBuilder(" FROM t_book_penalty a LEFT JOIN t_periodical_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_periodical_zhengding c ON b.periodicalid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id LEFT JOIN t_jieyue f ON a.jieyueid = f.id " +
				"WHERE a.type = 1 AND a.flag = 1 AND c.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
//        if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
//            where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
//        }
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime > ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime < ?");
			list.add(jssj+" 23:59:59");
		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber like ? OR d.realname like ?)");
			list.add("%"+cardnumber+"%");
			list.add("%"+cardnumber+"%");
		}
		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}
		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.kanming like ?");
			list.add("%"+title+"%");
		}
		where.append(" ORDER BY a.createtime DESC");
		return  Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
	}


	/**
	 * 期刊损书记录 --export
	 *
	 */
	public List<Record> exportDamagedBookQ(int orgid,String kssj,String jssj,Integer dept,String cardnumber,String title){

		String select = "SELECT b.barcode,c.issn,c.kanming,c.bookno,d.cardnumber,d.realname,e.deptname,DATE_FORMAT(a.createtime,'%Y-%m-%d') createtime,a.remarks,DATE_FORMAT(f.createtime,'%Y-%m-%d') fcreatetime,DATE_FORMAT(f.returntime,'%Y-%m-%d') returntime ";
		StringBuilder where = new StringBuilder(" FROM t_book_penalty a LEFT JOIN t_periodical_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_periodical_zhengding c ON b.periodicalid = c.id LEFT JOIN t_reader d ON a.readerid = d.id " +
				"LEFT JOIN t_org_dept e ON d.orgdeptid = e.id LEFT JOIN t_jieyue f ON a.jieyueid = f.id " +
				"WHERE a.type = 1 AND a.flag = 1 AND c.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
//        if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
//            where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
//        }
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime >=?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime <= ?");
			list.add(jssj+" 23:59:59");
		}
		//读者证号或姓名
		if(StringUtils.isNotBlank(cardnumber)){
			where.append(" AND (d.cardnumber = ? OR d.realname = ?)");
			list.add(cardnumber);
			list.add(cardnumber);
		}
		//部门id
		if(dept != null){
			where.append(" AND e.id = ?");
			list.add(dept);
		}
		//题名
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.kanming like ?");
			list.add("%"+title+"%");
		}
		where.append(" ORDER BY a.createtime DESC");
		String sql = select+where.toString();
		return Db.find(sql,CommonUtils.listToArray(list));
	}

	/**
	 * 图书丢书注销
	 * 更改book表复本数量，更改bookdetail表status状态
	 * 存入t_book_zhuxiao表记录，注销原因为丢书
	 *
	 */
	public void removeLoseBook(Long [] detailids,int operatorid){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				for(int i=0;i<detailids.length;i++){
					long detailid = detailids[i];
					Record r = Db.findFirst("SELECT a.id as bookid,a.orgid FROM t_book a LEFT JOIN t_book_detail b ON a.id = b.bookid " +
							" WHERE b.id = ?",detailid);
					if(r != null){
						Integer bookid = r.getInt("bookid");
						Integer orgid = r.getInt("orgid");
						//Integer penaltyid = r.getInt("penaltyid");
						//Db.update("UPDATE t_book_penalty SET status = 1 WHERE id = ?",penaltyid);
						Db.update("UPDATE t_book_detail SET `status` = 3,destroytime = ?,destroyreason = ? WHERE id = ?",new Date(),"丢失",detailid);
						Db.update("UPDATE t_book SET booknum = booknum-1 WHERE id = ?",bookid);
						Db.update("INSERT INTO t_book_zhuxiao (orgid,bookdetailid,bookid,flag,reason,status,createtime,operatorid) VALUES " +
								"(?,?,?,?,?,?,?,?)",orgid,detailid,bookid,0,"丢失",0,new Date(),operatorid);
					}
				}
				return true;
			}
		});
	}

	/**
	 * 期刊丢书注销
	 * @param detailids
	 */
	public void removeLoseBookQ(Long [] detailids,int operatorid){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				for(int i=0;i<detailids.length;i++){
					long detailid = detailids[i];
					Record r = Db.findFirst("SELECT a.id as bookid,a.orgid FROM t_periodical_zhengding a LEFT JOIN t_periodical_detail b ON a.id = b.periodicalid " +
							" WHERE b.id = ?",detailid);
					if(r != null){
						Integer bookid = r.getInt("bookid");
						Integer orgid = r.getInt("orgid");
						Db.update("UPDATE t_periodical_detail SET `status` = 3 WHERE id = ?",detailid);
						//Db.update("UPDATE t_periodical_zhengding SET booknum = booknum-1 WHERE id = ?",bookid);
						Db.update("INSERT INTO t_book_zhuxiao (orgid,bookdetailid,bookid,flag,reason,createtime,operatorid) VALUES " +
								"(?,?,?,?,?,?,?)",orgid,detailid,bookid,1,"丢失",new Date(),operatorid);
					}
				}
				return true;
			}
		});

	}
	/**
	 * 图书注销清单
	 */
	public Page<Record> removeBook(int pageNumber,int pagesize,int orgid,String kssj,String jssj,String booktype,Integer library,String reason,String barcode,String title){
		String select = "SELECT a.id as zxid,b.barcode,c.title,c.author,c.publisher,c.bookno,c.price,d.library,a.reason,DATE_FORMAT(b.destroytime,'%Y-%m-%d') as createtime ";
		StringBuilder where = new StringBuilder(" FROM t_book_zhuxiao a LEFT JOIN t_book_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_book c ON b.bookid = c.id LEFT JOIN t_library d ON b.libraryid = d.id " +
				"WHERE a.flag = 0 AND a.status = 0 AND a.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
			where.append(" AND b.destroytime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND b.destroytime > ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND b.destroytime < ?");
			list.add(jssj+" 23:59:59");
		}
		if(StringUtils.isNotBlank(booktype)){
			where.append(" AND c.typeid = ?");
			list.add(booktype);
		}
		if(library != null){
			where.append(" AND d.id = ?");
			list.add(library);
		}
		if(StringUtils.isNotBlank(reason)){
			if(!reason.equals("其它")){
				where.append(" AND a.reason = ?");
				list.add(reason);
			}else {
				//where.append(" AND a.reason != '丢失' AND a.reason != '剔旧' AND a.reason != '损坏'");
			}
		}
		if(StringUtils.isNotBlank(barcode)){
			where.append(" AND b.barcode = ?");
			list.add(barcode);
		}
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		where.append(" ORDER BY a.createtime DESC");
		return  Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
	}

	public List<Record> removeBookPrint(int orgid,String kssj,String jssj,String booktype,Integer library,String reason,String barcode,String title){
		String select = "SELECT a.id as zxid,b.barcode,c.title,c.author,c.publisher,c.bookno,c.price,d.library,a.reason,DATE_FORMAT(b.destroytime,'%Y-%m-%d') as createtime ";
		StringBuilder where = new StringBuilder(" FROM t_book_zhuxiao a LEFT JOIN t_book_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_book c ON b.bookid = c.id LEFT JOIN t_library d ON b.libraryid = d.id " +
				"WHERE a.flag = 0 AND a.status = 0 AND a.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
			where.append(" AND b.destroytime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND b.destroytime > ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND b.destroytime < ?");
			list.add(jssj+" 23:59:59");
		}
		if(StringUtils.isNotBlank(booktype)){
			where.append(" AND c.typeid = ?");
			list.add(booktype);
		}
		if(library != null){
			where.append(" AND d.id = ?");
			list.add(library);
		}
		if(StringUtils.isNotBlank(reason)){
			if(!reason.equals("其它")){
				where.append(" AND a.reason = ?");
				list.add(reason);
			}else {
				where.append(" AND a.reason != '丢失' AND a.reason != '剔旧' AND a.reason != '损坏'");
			}
		}
		if(StringUtils.isNotBlank(barcode)){
			where.append(" AND b.barcode = ?");
			list.add(barcode);
		}
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		where.append(" ORDER BY a.createtime DESC");
		return  Db.find(select +where.toString(),CommonUtils.listToArray(list));
	}

	public String removeBookTotalPrice(int orgid,String kssj,String jssj,String booktype,Integer library,String reason,String barcode,String title){
		String select = "SELECT SUM(c.price) as totalprice ";
		StringBuilder where = new StringBuilder(" FROM t_book_zhuxiao a LEFT JOIN t_book_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_book c ON b.bookid = c.id LEFT JOIN t_library d ON b.libraryid = d.id " +
				"WHERE a.flag = 0 AND a.status = 0 AND a.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
			where.append(" AND b.destroytime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND b.destroytime > ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND b.destroytime < ?");
			list.add(jssj+" 23:59:59");
		}
		if(StringUtils.isNotBlank(booktype)){
			where.append(" AND c.typeid = ?");
			list.add(booktype);
		}
		if(library != null){
			where.append(" AND d.id = ?");
			list.add(library);
		}
		if(StringUtils.isNotBlank(reason)){
			if(!reason.equals("其它")){
				where.append(" AND a.reason = ?");
				list.add(reason);
			}else {
				where.append(" AND a.reason != '丢失' AND a.reason != '剔旧' AND a.reason != '损坏'");
			}
		}
		if(StringUtils.isNotBlank(barcode)){
			where.append(" AND b.barcode = ?");
			list.add(barcode);
		}
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		Record r = Db.findFirst(select+where.toString(),CommonUtils.listToArray(list));
		String totalprice = r.getStr("totalprice");
		if(StringUtils.isBlank(totalprice)){
			totalprice = "0";
		}
		return  totalprice;
	}

	/**
	 * 图书注销清单 --导出
	 */
	public List<Record> exportRemoveBook(int orgid,String kssj,String jssj,String booktype,Integer library,String reason,String barcode,String title){
		String select = "SELECT a.id as zxid,b.barcode,c.title,c.author,c.publisher,c.bookno,c.price,d.library,a.reason,DATE_FORMAT(b.destroytime,'%Y-%m-%d') as createtime ";
		StringBuilder where = new StringBuilder(" FROM t_book_zhuxiao a LEFT JOIN t_book_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_book c ON b.bookid = c.id LEFT JOIN t_library d ON b.libraryid = d.id " +
				"WHERE a.flag = 0 AND a.status = 0 AND a.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime >=?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime <= ?");
			list.add(jssj+" 23:59:59");
		}
		if(StringUtils.isNotBlank(booktype)){
			where.append(" AND c.typeid = ?");
			list.add(booktype);
		}
		if(library != null){
			where.append(" AND d.id = ?");
			list.add(library);
		}
		if(StringUtils.isNotBlank(reason)){
			if(!reason.equals("其它")){
				where.append(" AND a.reason = ?");
				list.add(reason);
			}else {
				where.append(" AND a.reason != '丢失' AND a.reason != '剔旧' AND a.reason != '损坏'");
			}
		}
		if(StringUtils.isNotBlank(barcode)){
			where.append(" AND b.barcode = ?");
			list.add(barcode);
		}
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		where.append(" ORDER BY a.createtime DESC");
		String sql = select+where.toString();
		return Db.find(sql,CommonUtils.listToArray(list));
	}

	/**
	 * 期刊注销清单
	 */
	public Page<Record> removeBookQ(int pageNumber,int pagesize,int orgid,String kssj,String jssj,String booktype,Integer library,String reason,String barcode,String title){
		String select = "SELECT a.id as zxid,c.issn,b.barcode,c.kanming,c.bianjibu,c.bookno,b.price,d.library,a.reason,a.createtime ";
		StringBuilder where = new StringBuilder(" FROM t_book_zhuxiao a LEFT JOIN t_periodical_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_periodical_zhengding c ON b.periodicalid = c.id LEFT JOIN t_library d ON b.libraryid = d.id " +
				"WHERE a.flag = 1 AND a.status = 0 AND a.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime >= ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime < ?");
			list.add(jssj+" 23:59:59");
		}
		if(StringUtils.isNotBlank(booktype)){
			where.append(" AND c.typeid = ?");
			list.add(booktype);
		}
		if(library != null){
			where.append(" AND d.id = ?");
			list.add(library);
		}
		if(StringUtils.isNotBlank(reason)){
			if(!reason.equals("其它")){
				where.append(" AND a.reason = ?");
				list.add(reason);
			}else {
				where.append(" AND a.reason != '丢失' AND a.reason != '剔旧' AND a.reason != '损坏'");
			}
		}
		if(StringUtils.isNotBlank(barcode)){
			where.append(" AND b.barcode = ?");
			list.add(barcode);
		}
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.kanming like ?");
			list.add("%"+title+"%");
		}
		where.append(" ORDER BY a.createtime DESC");
		return  Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));

	}
	/**
	 * 期刊注销总价
	 */
	public String removeBookQTotalPrice(int orgid,String kssj,String jssj,String booktype,Integer library,String reason,String barcode,String title){
		String select = "SELECT SUM(b.price) as totalprice ";
		StringBuilder where = new StringBuilder(" FROM t_book_zhuxiao a LEFT JOIN t_periodical_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_periodical_zhengding c ON b.periodicalid = c.id LEFT JOIN t_library d ON b.libraryid = d.id " +
				"WHERE a.flag = 1 AND a.status = 0 AND a.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime >=?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime <= ?");
			list.add(jssj+" 23:59:59");
		}
		if(StringUtils.isNotBlank(booktype)){
			where.append(" AND c.typeid = ?");
			list.add(booktype);
		}
		if(library != null){
			where.append(" AND d.id = ?");
			list.add(library);
		}
		if(StringUtils.isNotBlank(reason)){
			if(!reason.equals("其它")){
				where.append(" AND a.reason = ?");
				list.add(reason);
			}else {
				where.append(" AND a.reason != '丢失' AND a.reason != '剔旧' AND a.reason != '损坏'");
			}
		}
		if(StringUtils.isNotBlank(barcode)){
			where.append(" AND b.barcode = ?");
			list.add(barcode);
		}
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		Record r = Db.findFirst(select+where.toString(),CommonUtils.listToArray(list));
		String totalprice = r.getStr("totalprice");
		if(StringUtils.isBlank(totalprice)){
			totalprice = "0";
		}
		return totalprice;
	}

	/**
	 * 期刊注销清单 --导出
	 */
	public List<Record> exportRemoveBookQ(int orgid,String kssj,String jssj,String booktype,Integer library,String reason,String barcode,String title){
		String select = "SELECT a.id as zxid,b.barcode,c.kanming,c.bianjibu,c.bookno,b.price,d.library,a.reason,a.createtime ";
		StringBuilder where = new StringBuilder(" FROM t_book_zhuxiao a LEFT JOIN t_periodical_detail b ON a.bookdetailid = b.id " +
				"LEFT JOIN t_periodical_zhengding c ON b.periodicalid = c.id LEFT JOIN t_library d ON b.libraryid = d.id " +
				"WHERE a.flag = 1 AND a.status = 0 AND a.orgid = ?");
		List list = new ArrayList();
		list.add(orgid);
		if(!StringUtils.isNotBlank(kssj) && !StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ");
		}
		if(StringUtils.isNotBlank(kssj)){
			where.append(" AND a.createtime > ?");
			list.add(kssj);
		}
		if(StringUtils.isNotBlank(jssj)){
			where.append(" AND a.createtime < ?");
			list.add(jssj+" 23:59:59");
		}
		if(StringUtils.isNotBlank(booktype)){
			where.append(" AND c.typeid = ?");
			list.add(booktype);
		}
		if(library != null){
			where.append(" AND d.id = ?");
			list.add(library);
		}
		if(StringUtils.isNotBlank(reason)){
			if(!reason.equals("其它")){
				where.append(" AND a.reason = ?");
				list.add(reason);
			}else {
				where.append(" AND a.reason != '丢失' AND a.reason != '剔旧' AND a.reason != '损坏'");
			}
		}
		if(StringUtils.isNotBlank(barcode)){
			where.append(" AND b.barcode = ?");
			list.add(barcode);
		}
		if(StringUtils.isNotBlank(title)){
			where.append(" AND c.title like ?");
			list.add("%"+title+"%");
		}
		where.append(" ORDER BY a.createtime DESC");
		String sql = select+where.toString();
		return Db.find(sql,CommonUtils.listToArray(list));

	}





	/**
	 * 图书注销恢复
	 * 更改book表复本数量，更改bookdetail表status状态
	 * 修改t_book_zhuxiao表状态
	 *
	 */
	public void recoveryRemoveBook(Long [] zxids){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				for (int i=0;i<zxids.length;i++){
					Long zxid = zxids[i];
					Record r = Db.findFirst("SELECT bookid,bookdetailid FROM t_book_zhuxiao WHERE id = ?",zxid);
					if(r != null){
						Integer bookid = r.getInt("bookid");
						Integer bookdetailid = r.getInt("bookdetailid");
						Db.update("UPDATE t_book_zhuxiao SET `status` = 1 WHERE id = ?",zxid);
						Db.update("UPDATE t_book_detail SET `status` = 0 WHERE id = ?",bookdetailid);
						Db.update("UPDATE t_book SET booknum = booknum+1 WHERE id = ?",bookid);
//						Record record = Db.findFirst("select * from t_jieyue where bookdetailid = ? and flag = 0 and status in (0,2) order by id desc limit 1",bookdetailid);
//						if(CommonUtils.isNotNullOrNotEmpty(record)) {
//							record.set("status",1);
//							Db.update("t_jieyue",record);
//						}
					}
				}
				return true;
			}
		});
	}
	/**
	 * 期刊注销恢复
	 * 更改book表复本数量，更改bookdetail表status状态
	 * 修改t_book_zhuxiao表状态
	 *
	 */
	public void recoveryRemoveBookQ(Long [] zxids){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				for (int i=0;i<zxids.length;i++){
					Long zxid = zxids[i];
					Record r = Db.findFirst("SELECT bookid,bookdetailid FROM t_book_zhuxiao WHERE id = ?",zxid);
					if(r != null){
						Integer bookid = r.getInt("bookid");
						Integer bookdetailid = r.getInt("bookdetailid");
						Db.update("UPDATE t_book_zhuxiao SET `status` = 1 WHERE id = ?",zxid);
						Db.update("UPDATE t_periodical_detail SET `status` = 0 WHERE id = ?",bookdetailid);
						Db.update("UPDATE t_periodical_zhengding SET hedingcishu = hedingcishu+1 WHERE id = ?",bookid);
						Record record = Db.findFirst("select * from t_jieyue where bookdetailid = ? and flag = 1 and status in (0,2) order by id desc limit 1",bookdetailid);
						if(CommonUtils.isNotNullOrNotEmpty(record)) {
							record.set("status",1);
							Db.save("t_jieyue",record);
						}
					}
				}
				return true;
			}
		});
	}


	public void peishu(Jieyue jieyue,String barcode,int operatorid,BookPenalty bookPenalty){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				if(CommonUtils.isNotNullOrNotEmpty(bookPenalty)) {
					bookPenalty.save();
				}
				String tablename = "t_book_detail";
				if(1 == jieyue.getFlag()) {
					tablename = "t_periodical_detail";
				}
				if(CommonUtils.isNotNullOrNotEmpty(barcode)) {
					Db.update("update " + tablename + " set barcode = ? where id = ?", barcode, jieyue.getBookdetailid());
				}
				Db.update("update " + tablename + " set status=0 where id = ? ", jieyue.getBookdetailid());

				Db.update("update t_jieyue set status = 1,returnuserid=?,returntime=now() where id = ?",operatorid,jieyue.getId());
				return true;
			}
		});
	}

	public int findJieyueNumber(int readerid,int detailid){
		Record record = Db.findFirst("select count(1) jieyuenumber from t_jieyue where continuenum =1 and readerid = ? and bookdetailid = ? ",readerid,detailid);
		return record.getInt("jieyuenumber");
	}

	public void xujie(int operatorid,int jieyueid,Jieyue jieyue){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				jieyue.save();
				if(jieyue.getFlag() == 0) {
					Db.update("update t_book_detail set jieyuenums = jieyuenums + 1 where id = ?", jieyue.getBookdetailid());
				} else {
					Db.update("update t_periodical_detail set jieyuenums = jieyuenums + 1 where id = ?", jieyue.getBookdetailid());
				}
				Db.update("update t_jieyue set continuenum = 1,status=1,isoverdue=2,returntime=?,returnuserid=? where id = ? and status=0 ",new Date(),operatorid,jieyueid);
				return true;
			}
		});
	}

	/**
	 * 查询预约预借列表
	 * @param type 1预约 2 预借
	 * @param readerinfo
	 * @param bookinfo
	 */
	public Page<Record> yuyueList(int pageNumber,int pagesize, int type,String readerinfo,String bookinfo,int orgid,String status) {
		String tableName = "t_yuyue";
		if (2 == type) {
			tableName = "t_yujie";
		}
		List values = new ArrayList();
		StringBuffer querySql1 = new StringBuffer(" from (select y.id yuyueid,'0' as booktype,y.bookid,y.readerid,r.realname,od.deptname,r.cardnumber,b.title title,b.publisher publisher,b.bookno,y.createtime,y.endtime,y.`status` from ");

		querySql1.append(tableName).append(" y inner join t_reader r on y.readerid=r.id left join t_org_dept od on r.orgdeptid=od.id inner join t_book b on y.bookid = b.id where y.deletestatus=0 and y.flag=0 and b.orgid=?");
		values.add(orgid);

		if(null != bookinfo && !"".equals(bookinfo)) {
			querySql1.append( " and (b.title like ?");
			values.add("%"+bookinfo+"%");
			querySql1.append(" OR b.bookno LIKE ?)");
			values.add("%"+bookinfo+"%");
		}

		if(null != readerinfo && !"".equals(readerinfo)) {
			querySql1.append(" and (r.cardnumber =? or r.realname like ?) and r.orgid =? ");
			values.add(readerinfo);
			values.add("%"+readerinfo+"%");
			values.add(orgid);
		}
		if(null != status && !"".equals(status)) {
			querySql1.append(" and y.status = ?");
			values.add(status);
		}

		StringBuffer querySql2 = new StringBuffer("select distinct y.id yuyueid,'1' as booktype,y.bookid,y.readerid,r.realname,od.deptname,r.cardnumber,p.kanming title,p.bianjibu publisher,p.bookno,y.createtime,y.endtime,y.`status` from ");
		querySql2.append(tableName).append(" y inner join t_reader r on y.readerid=r.id left join t_org_dept od on r.orgdeptid=od.id inner join t_periodical_zhengding p on y.bookid = p.id left join t_periodical_detail d on p.id=d.periodicalid where y.deletestatus=0 and y.flag=1 and p.orgid=?");
		values.add(orgid);

		if(null != bookinfo && !"".equals(bookinfo)) {
			querySql2.append(" and (p.kanming like ?");
			values.add("%"+bookinfo+"%");
			querySql2.append(" OR p.bookno LIKE ?)");
			values.add("%"+bookinfo+"%");
		}
		if(null != readerinfo && !"".equals(readerinfo)) {
			querySql2.append(" and (r.cardnumber = ? or r.realname like ?) and r.orgid = ?");
			values.add(readerinfo);
			values.add("%"+readerinfo+"%");
			values.add(orgid);
		}
		if(null != status && !"".equals(status)) {
			querySql2.append(" and y.status = ?");
			values.add(status);
		}
		String sql = querySql1.append(" union all ").append(querySql2.toString()).append(" ) t").toString();
		String select = "select * ";


		return Db.paginate(pageNumber, pagesize,select, sql,CommonUtils.listToArray(values));
	}


	public List jieyueReport(Integer orgid,String begindate,String enddate){
		String parentcodes = CommonUtils.codeAddOne(orgid,6);
		String sql ="SELECT  DATE_FORMAT(createtime,'%Y-%m') AS m,flag,COUNT(*) as num FROM t_jieyue WHERE createtime > DATE_SUB(CURDATE(),INTERVAL 12 MONTH) and orgid=? GROUP BY m,flag ORDER BY flag,m";
		List<Record> recordList=null;
		String orgidStr = String.valueOf(orgid);
		String databasename = "";
		if(orgidStr.length() > 6) {
			Record databaseRecord = Db.findFirst("select * from t_database where status = 1 and orgid like ?",orgidStr.substring(0,4)+"%");
			if(CommonUtils.isNotNullOrNotEmpty(databaseRecord)){
				databasename = databaseRecord.getStr("databasename");
			}
		}
		int orgtypeid;
		if("".equals(databasename)){
			orgtypeid = Db.queryLong("SELECT orgtypeid FROM t_org  where id =?",orgid).intValue();
		} else {
			orgtypeid = Db.use(databasename).queryLong("SELECT orgtypeid FROM t_org  where id =?",orgid).intValue();
		}
		if (5==orgtypeid) {
			if("".equals(databasename)) {
				recordList = Db.find(sql, orgid);
			} else {
				recordList = Db.use(databasename).find(sql, orgid);
			}
		}else{
			sql ="SELECT  DATE_FORMAT(a.createtime,'%Y-%m') AS m,flag,COUNT(*) AS num FROM t_jieyue  a" +
					" INNER JOIN t_org b  ON a.orgid=b.id " +
					" WHERE a.createtime > DATE_SUB(CURDATE(),INTERVAL 12 MONTH) AND b.orgtypeid=5" +
					" AND  parentcodes LIKE ? GROUP BY m,flag ORDER BY flag,m ";

			if("".equals(databasename)) {
				recordList=Db.find(sql,"%"+parentcodes+"%");
			} else {
				recordList=Db.use(databasename).find(sql,"%"+parentcodes+"%");
			}
		}

		Map<String,Long> bookmap=new HashMap<String, Long>();
		Map<String,Long> periodicalmap=new HashMap<String, Long>();
		for (Record record : recordList) {
			if (record.getInt("flag")==0){
				bookmap.put(record.get("m"),record.get("num"));
			}else{
				periodicalmap.put(record.get("m"),record.get("num"));
			}
		}

		List<Record> databaseList = Db.find("select * from t_database where status = 1 and parentorgid = ?",orgid);
		for(Record record : databaseList) {
			sql ="SELECT  DATE_FORMAT(a.createtime,'%Y-%m') AS m,flag,COUNT(*) AS num FROM t_jieyue  a" +
					" INNER JOIN t_org b  ON a.orgid=b.id " +
					" WHERE a.createtime > DATE_SUB(CURDATE(),INTERVAL 12 MONTH) AND b.orgtypeid=5" +
					" AND  parentcodes LIKE ? GROUP BY m,flag ORDER BY flag,m ";
			recordList=Db.use(record.getStr("databasename")).find(sql,"%"+record.getStr("orgid")+"%");

			for (Record reportRecord : recordList) {
				if (reportRecord.getInt("flag")==0){
					if(CommonUtils.isNotNullOrNotEmpty(bookmap.get(reportRecord.get("m")))){
						long num = bookmap.get(reportRecord.get("m"));
						bookmap.put(reportRecord.get("m"),reportRecord.getLong("num")+num);
					} else {
						bookmap.put(reportRecord.get("m"),reportRecord.getLong("num"));
					}
				}else{
					if(CommonUtils.isNotNullOrNotEmpty(bookmap.get(reportRecord.get("m")))){
						long num = bookmap.get(reportRecord.get("m"));
						periodicalmap.put(reportRecord.get("m"),reportRecord.getLong("num")+num);
					} else {
						periodicalmap.put(reportRecord.get("m"),reportRecord.getLong("num"));
					}
				}
			}
		}

		List<Long> bookList = new ArrayList<Long>();
		List<Long> periodicalList = new ArrayList<Long>();
		List<String> monthList = new ArrayList<String>();

		Calendar calendar=Calendar.getInstance();
		Date nowDate =calendar.getTime();
		calendar.add(Calendar.MONTH,-11);
		calendar.set(Calendar.HOUR,0);
		calendar.set(Calendar.MINUTE,0);


		while (calendar.getTime().before(nowDate)){
			monthList.add(calendar.get(Calendar.MONTH)+1 +"月份");
			bookList.add(bookmap.getOrDefault(DateUtils.getSimpleDateFormat("yyyy-MM").format(calendar.getTime()),0l));
			periodicalList.add(periodicalmap.getOrDefault(DateUtils.getSimpleDateFormat("yyyy-MM").format(calendar.getTime()),0l));
			calendar.add(Calendar.MONTH,1);
		}

		List<List> resultList = new ArrayList<List>();
		resultList.add(monthList);
		resultList.add(bookList);
		resultList.add(periodicalList);

		if (5==orgtypeid){
			if("".equals(databasename)) {
				recordList = Db.find("SELECT ifnull(typeid,'未知') as typeid ,COUNT(*) AS NUM FROM t_jieyue a INNER JOIN t_book_detail b ON a.bookdetailid=b.id " +
						" INNER JOIN t_book c ON b.bookid =c.id " +
						" WHERE b.status !=? AND  b.bookstatus = ?   and a.orgid=?    and  a.createtime>=? and a.createtime <=? " +
						" GROUP BY typeid ORDER BY NUM DESC", 3, 1, orgid, begindate, enddate);
			} else {
				recordList = Db.use(databasename).find("SELECT ifnull(typeid,'未知') as typeid ,COUNT(*) AS NUM FROM t_jieyue a INNER JOIN t_book_detail b ON a.bookdetailid=b.id " +
						" INNER JOIN t_book c ON b.bookid =c.id " +
						" WHERE b.status !=? AND  b.bookstatus = ?   and a.orgid=?    and  a.createtime>=? and a.createtime <=? " +
						" GROUP BY typeid ORDER BY NUM DESC", 3, 1, orgid, begindate, enddate);
			}
		}else {
			if ("".equals(databasename)) {
				recordList = Db.find("SELECT ifnull(typeid,'未知') as typeid ,COUNT(*) AS NUM FROM t_jieyue a INNER JOIN t_book_detail b ON a.bookdetailid=b.id " +
						" INNER JOIN t_book c ON b.bookid =c.id " +
						" INNER JOIN t_org d  ON a.orgid=d.id " +
						" WHERE b.status !=? AND  b.bookstatus = ?  AND d.orgtypeid=? AND  parentcodes LIKE ?    and  a.createtime>=? and a.createtime <=? " +
						" GROUP BY typeid ORDER BY NUM DESC", 3, 1, 5, "%" + parentcodes + "%", begindate, enddate);
			} else {
				recordList = Db.use(databasename).find("SELECT ifnull(typeid,'未知') as typeid ,COUNT(*) AS NUM FROM t_jieyue a INNER JOIN t_book_detail b ON a.bookdetailid=b.id " +
						" INNER JOIN t_book c ON b.bookid =c.id " +
						" INNER JOIN t_org d  ON a.orgid=d.id " +
						" WHERE b.status !=? AND  b.bookstatus = ?  AND d.orgtypeid=? AND  parentcodes LIKE ?    and  a.createtime>=? and a.createtime <=? " +
						" GROUP BY typeid ORDER BY NUM DESC", 3, 1, 5, "%" + parentcodes + "%", begindate, enddate);
			}
		}
		List<String> typelist=new ArrayList<String>();
		List<String> typenumlist=new ArrayList<String>();
		for (Record record : recordList){
			typelist.add(record.get("typeid"));
			typenumlist.add(record.get("NUM"));
		}
		resultList.add(typelist);
		resultList.add(typenumlist);

		return resultList;
	}

	public List floatingBookreprot(String library,String begindate, String enddate, String orgid){
		try {
			List resultList = new ArrayList();
			long t1 = System.currentTimeMillis();
			String sql = "SELECT CASE WHEN SUBSTRING(typecode,1,1) IN ('A') THEN '第一大类' " +
					"                                WHEN SUBSTRING(typecode,1,1) IN ('B') THEN '第二大类' " +
					"                                WHEN SUBSTRING(typecode,1,1) IN ('C','D','E','F','G','H','I','J','K') THEN '第三大类' " +
					"                        WHEN SUBSTRING(typecode,1,1) IN ('N','O','P','Q','R','S','T','U','V','X') THEN '第四大类'" +
					"                         ELSE '第五大类' END AS name,COUNT(*) AS value FROM t_book  b INNER JOIN t_book_detail bd ON b.id=bd.bookid " +
					"INNER JOIN t_library l ON l.id=bd.libraryid left join t_jieyue j on bd.id = j.bookdetailid " +
					"WHERE l.orgid=? and l.libraryType=1  and j.createtime>=? and j.createtime<=?  " + ("".equals(library) ? "" : " and l.id=" + library) +
					" GROUP BY name order by value desc limit 10";
			List<Record> type5List = Db.find(sql, orgid,begindate+" 00:00:00", enddate+" 23:59:59");

			List<String> legendList = new ArrayList<String>();
			for (Record record : type5List) {
				legendList.add(record.getStr("name"));
			}

			List<Record> type22List = Db.find("SELECT name,COUNT(*) AS value FROM t_book  b INNER JOIN t_book_detail bd ON b.id=bd.bookid " +
					"INNER JOIN t_library l ON l.id=bd.libraryid " +
					"INNER JOIN t_booktype bt ON bt.id=b.typeid left join t_jieyue j on bd.id = j.bookdetailid " +
					"WHERE l.orgid=? and l.libraryType=1  and j.createtime>=? and j.createtime<=? " + ("".equals(library) ? "" : " and l.id=" + library) +
					"  GROUP BY name order by value desc limit 10",orgid, begindate, enddate);

			for (Record record : type22List) {
				legendList.add(record.getStr("name"));
			}

			resultList.add(legendList);
			resultList.add(type5List);
			resultList.add(type22List);

			List<Record> recordList = Db.find(" SELECT DATE_FORMAT(createtime,'%Y-%m-%d') createDate, COUNT(DISTINCT readerid) readernum, COUNT(*) AS jieyuenum FROM t_jieyue " +
					" WHERE orgid=? and jieyuetype=1  and createtime>=? and createtime<=? " + ("".equals(library) ? "" : " and libraryid=" + library) +
					"  GROUP BY createDate  " +
					" ORDER BY createDate ",orgid, begindate, enddate);
			Calendar start = Calendar.getInstance();
			Calendar end = Calendar.getInstance();
			List<String> datelist = new ArrayList();
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			start.setTime(format.parse(begindate));
			end.setTime(format.parse(enddate));
			if(start.getTimeInMillis() == end.getTimeInMillis()){
				datelist.add(format.format(start.getTime()));
			}else {
				while (start.before(end)) {
					datelist.add(format.format(start.getTime()));
					start.add(Calendar.DATE, 1);
				}
				datelist.add(format.format(end.getTime()));
			}

			List dayReaderlist = new ArrayList();
			List dayJieyuelist = new ArrayList();

			for (String time : datelist) {
				boolean a = false;
				for(Record record : recordList){
					if(time.equals(record.getStr("createDate"))){
						dayReaderlist.add(record.get("readernum"));
						dayJieyuelist.add(record.get("jieyuenum"));
						a = true;
						break;
					}
				}
				if(a == false){
					dayReaderlist.add(0);
					dayJieyuelist.add(0);
				}

			}


			resultList.add(datelist);
			resultList.add(dayReaderlist);
			resultList.add(dayJieyuelist);
			long t2 = System.currentTimeMillis();
			System.out.println("costtime=" + (t2 - t1));
			return resultList;
		}catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}


	public Page<Record> overdueList(int pageNumber,int pagesize,String queryStr,String orgdeptid,String title,String startDate,String endDate,int orgid,int libraryid){
		String select = "select *  ";
		List list = new ArrayList();
		StringBuffer where = new StringBuffer(" from (select b.title,d.barcode,DATE_FORMAT(j.duetime,'%Y-%m-%d') duetime,DATE_FORMAT(j.createtime,'%Y-%m-%d') createtime,od.deptname,r.realname,r.cardnumber,(to_days(now()) - to_days(duetime)) days,b.price from t_jieyue j ");
		where.append(" inner join t_book_detail d on j.bookdetailid = d.id ");
		where.append(" inner join t_book b on d.bookid = b.id ");
		where.append(" inner join t_org o on j.orgid = o.id ");
		where.append(" inner join t_reader r on j.readerid = r.id ");
		where.append(" inner join t_org_dept od on r.orgdeptid = od.id");
		where.append(" where j.isoverdue = 1 and j.flag = 0 and j.status = 0 and j.orgid = ? ");
		list.add(orgid);
		if(CommonUtils.isNotNullOrNotEmpty(queryStr)) {
			where.append(" and (r.realname =? or r.cardnumber = ?) ");
			list.add(queryStr);
			list.add(queryStr);
		}
		if(CommonUtils.isNotNullOrNotEmpty(orgdeptid)) {
			where.append(" and od.id = ? ");
			list.add(orgdeptid);
		}
		if(CommonUtils.isNotNullOrNotEmpty(title)) {
			where.append(" and b.title like ? ");
			list.add("%"+title+"%");
		}
		if(CommonUtils.isNotNullOrNotEmpty(startDate)) {
			where.append(" and j.duetime >= ? ");
			list.add(startDate + " 00:00:00");
		}
		if(CommonUtils.isNotNullOrNotEmpty(endDate)) {
			where.append(" and j.duetime <= ? ");
			list.add(endDate + " 23:59:59");
		}
		if(libraryid != 0){

			where.append(" and d.libraryid = ? ");
			list.add(libraryid);
		}

		where.append(" ) t order by days desc");
		return Db.paginate(pageNumber,pagesize,select,where.toString(),list.toArray());
	}

	public Page<Record> overdueQList(int pageNumber,int pagesize,String queryStr,String orgdeptid,String title,String startDate,String endDate,int orgid){
		String select = "select *  ";
		List list = new ArrayList();
		StringBuffer where = new StringBuffer(" from (");

		where.append(" select p.kanming title,d.barcode,DATE_FORMAT(j.duetime,'%Y-%m-%d') duetime,DATE_FORMAT(j.createtime,'%Y-%m-%d') createtime,od.deptname,r.realname,d.niandu,d.qizhiqi,r.cardnumber,(to_days(now()) - to_days(duetime)) days,d.price from t_jieyue j  ");
		where.append(" inner join t_periodical_detail d on j.bookdetailid = d.id ");
		where.append(" inner join t_periodical_zhengding p on d.periodicalid = p.id ");
		where.append(" inner join t_org o on j.orgid = o.id ");
		where.append(" inner join t_reader r on j.readerid = r.id ");
		where.append(" inner join t_org_dept od on r.orgdeptid = od.id");
		where.append(" where j.isoverdue = 1 and j.flag = 1 and j.status = 0 and j.orgid = ? ");
		list.add(orgid);
		if(CommonUtils.isNotNullOrNotEmpty(queryStr)) {
			where.append(" and (r.realname =? or r.cardnumber = ?) ");
			list.add(queryStr);
			list.add(queryStr);
		}
		if(CommonUtils.isNotNullOrNotEmpty(orgdeptid)) {
			where.append(" and od.id = ? ");
			list.add(orgdeptid);
		}
		if(CommonUtils.isNotNullOrNotEmpty(title)) {
			where.append(" and p.kanming like ? ");
			list.add("%"+title+"%");
		}
		if(CommonUtils.isNotNullOrNotEmpty(startDate)) {
			where.append(" and j.duetime >= ? ");
			list.add(startDate + " 00:00:00");
		}
		if(CommonUtils.isNotNullOrNotEmpty(endDate)) {
			where.append(" and j.duetime <= ? ");
			list.add(endDate + " 23:59:59");
		}
		where.append(" ) t order by days desc");
		return Db.paginate(pageNumber,pagesize,select,where.toString(),list.toArray());
	}

	public List exportOverdueList(String queryStr,String orgdeptid,String title,String startDate,String endDate,int orgid){
		String select = "select *  ";
		List list = new ArrayList();
		StringBuffer where = new StringBuffer(" from (select b.title,d.barcode,DATE_FORMAT(j.duetime,'%Y-%m-%d') duetime,DATE_FORMAT(j.createtime,'%Y-%m-%d') createtime,od.deptname,r.realname,r.cardnumber,(to_days(now()) - to_days(duetime)) days,b.price from t_jieyue j ");
		where.append(" inner join t_book_detail d on j.bookdetailid = d.id ");
		where.append(" inner join t_book b on d.bookid = b.id ");
		where.append(" inner join t_org o on j.orgid = o.id ");
		where.append(" inner join t_reader r on j.readerid = r.id ");
		where.append(" inner join t_org_dept od on r.orgdeptid = od.id");
		where.append(" where j.isoverdue = 1 and j.flag = 0 and j.status = 0 and j.orgid = ? ");
		list.add(orgid);
		if(CommonUtils.isNotNullOrNotEmpty(queryStr)) {
			where.append(" and (r.realname =? or r.cardnumber = ?) ");
			list.add(queryStr);
			list.add(queryStr);
		}
		if(CommonUtils.isNotNullOrNotEmpty(orgdeptid)) {
			where.append(" and od.id = ? ");
			list.add(orgdeptid);
		}
		if(CommonUtils.isNotNullOrNotEmpty(title)) {
			where.append(" and b.title like ? ");
			list.add("%"+title+"%");
		}
		if(CommonUtils.isNotNullOrNotEmpty(startDate)) {
			where.append(" and j.duetime >= ? ");
			list.add(startDate + " 00:00:00");
		}
		if(CommonUtils.isNotNullOrNotEmpty(endDate)) {
			where.append(" and j.duetime <= ? ");
			list.add(endDate + " 23:59:59");
		}

		where.append(" ) t order by days desc");
		return Db.find(select+where.toString(),list.toArray());
	}

	public List exportOverdueQList(String queryStr,String orgdeptid,String title,String startDate,String endDate,int orgid){
		String select = "select *  ";
		List list = new ArrayList();
		StringBuffer where = new StringBuffer(" from (");

		where.append(" select p.kanming title,d.barcode,DATE_FORMAT(j.duetime,'%Y-%m-%d') duetime,DATE_FORMAT(j.createtime,'%Y-%m-%d') createtime,od.deptname,r.realname,d.niandu,d.qizhiqi,r.cardnumber,(to_days(now()) - to_days(duetime)) days,d.price from t_jieyue j  ");
		where.append(" inner join t_periodical_detail d on j.bookdetailid = d.id ");
		where.append(" inner join t_periodical_zhengding p on d.periodicalid = p.id ");
		where.append(" inner join t_org o on j.orgid = o.id ");
		where.append(" inner join t_reader r on j.readerid = r.id ");
		where.append(" inner join t_org_dept od on r.orgdeptid = od.id");
		where.append(" where j.isoverdue = 1 and j.flag = 1 and j.status = 0 and j.orgid = ? ");
		list.add(orgid);
		if(CommonUtils.isNotNullOrNotEmpty(queryStr)) {
			where.append(" and (r.realname =? or r.cardnumber = ?) ");
			list.add(queryStr);
			list.add(queryStr);
		}
		if(CommonUtils.isNotNullOrNotEmpty(orgdeptid)) {
			where.append(" and od.id = ? ");
			list.add(orgdeptid);
		}
		if(CommonUtils.isNotNullOrNotEmpty(title)) {
			where.append(" and p.kanming like ? ");
			list.add("%"+title+"%");
		}
		if(CommonUtils.isNotNullOrNotEmpty(startDate)) {
			where.append(" and j.duetime >= ? ");
			list.add(startDate + " 00:00:00");
		}
		if(CommonUtils.isNotNullOrNotEmpty(endDate)) {
			where.append(" and j.duetime <= ? ");
			list.add(endDate + " 23:59:59");
		}
		where.append(" ) t order by days desc");
		return Db.find(select+where.toString(),list.toArray());
	}


	public List<Record> findJieyueList(String cardnumber,int orgid){
		List list = new ArrayList();

		if(StringUtils.isNotBlank(cardnumber)){
			return Db.find("select d.id detailid,j.id jieyueid,b.bookno,d.barcode,b.title,j.createtime,j.duetime,j.isoverdue from t_jieyue j left join t_reader r on j.readerid = r.id left join t_book_detail d on j.bookdetailid = d.id left join t_book b on d.bookid = b.id where r.orgid = ? and (r.cardnumber = ? or r.readcardcode = ?) and j.status = 0",orgid,cardnumber,cardnumber);
		}

		String select = "SELECT * ";
		StringBuffer where = new StringBuffer(" FROM (select * from (" +
				" select * from (" +
				" select j.id,j.orgid,j.readerid,j.createtime operatetime,0 status,r.cardnumber,r.realname,od.deptname,d.barcode,b.title,b.bookno,j.createtime jieyuetime,j.duetime 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 left join t_book b on d.bookid = b.id where j.flag=0 and j.orgid = ? and DATE_FORMAT(j.createtime,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') " +
				" union" +
				" select j.id,j.orgid,j.readerid,j.createtime operatetime,0 status,r.cardnumber,r.realname,od.deptname,d.barcode,p.kanming title,p.bookno,j.createtime jieyuetime,j.duetime from t_jieyue j left join t_reader r on j.readerid = r.id inner join t_org_dept od on r.orgdeptid = od.id left join t_periodical_detail d on j.bookdetailid = d.id left join t_periodical_zhengding p on d.periodicalid = p.id where j.flag=1 and j.orgid = ? and DATE_FORMAT(j.createtime,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') " +
				" ) t_jie" +
				" union" +
				" select * from (" +
				" select j.id,j.orgid,j.readerid,j.returntime operatetime,j.status,r.cardnumber,r.realname,od.deptname,d.barcode,b.title,b.bookno,j.createtime jieyuetime,j.duetime 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 left join t_book b on d.bookid = b.id where j.flag=0 and j.returntime is not null and j.orgid = ? and DATE_FORMAT(j.returntime,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') " +
				" union" +
				" select j.id,j.orgid,j.readerid,j.returntime operatetime,j.status,r.cardnumber,r.realname,od.deptname,d.barcode,p.kanming title,p.bookno,j.createtime jieyuetime,j.duetime from t_jieyue j left join t_reader r on j.readerid = r.id inner join t_org_dept od on r.orgdeptid = od.id left join t_periodical_detail d on j.bookdetailid = d.id left join t_periodical_zhengding p on d.periodicalid = p.id where j.flag=1 and j.returntime is not null and j.orgid = ? and DATE_FORMAT(j.returntime,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') " +
				" ) t_huan" +
				" union" +
				" select * from (" +
				" select j.id,j.orgid,j.readerid,bp.createtime operatetime,CONCAT(bp.penaltystatus,bp.type) status,r.cardnumber,r.realname,od.deptname,d.barcode,b.title,b.bookno,j.createtime jieyuetime,j.duetime from t_book_penalty bp left join  t_jieyue j on bp.jieyueid = j.id 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 left join t_book b on d.bookid = b.id where j.flag=0 and bp.retrievetime is null and bp.orgid = ? and DATE_FORMAT(bp.createtime,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') " +
				" union" +
				" select j.id,j.orgid,j.readerid,bp.createtime operatetime,CONCAT(bp.penaltystatus,bp.type) status,r.cardnumber,r.realname,od.deptname,d.barcode,p.kanming title,p.bookno,j.createtime jieyuetime,j.duetime from  t_book_penalty bp left join  t_jieyue j on bp.jieyueid = j.id left join t_reader r on j.readerid = r.id inner join t_org_dept od on r.orgdeptid = od.id left join t_periodical_detail d on j.bookdetailid = d.id left join t_periodical_zhengding p on d.periodicalid = p.id where j.flag=1 and bp.retrievetime is null and bp.orgid = ? and DATE_FORMAT(bp.createtime,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') " +
				" ) t_fakuan" +
				" union" +
				" select * from (" +
				" select j.id,j.orgid,j.readerid,bp.retrievetime operatetime,CONCAT(bp.penaltystatus,bp.type) status,r.cardnumber,r.realname,od.deptname,d.barcode,b.title,b.bookno,j.createtime jieyuetime,j.duetime from t_book_penalty bp left join  t_jieyue j on bp.jieyueid = j.id 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 left join t_book b on d.bookid = b.id where j.flag=0 and bp.retrievetime is not null and bp.orgid = ? and DATE_FORMAT(bp.retrievetime,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') " +
				" union" +
				" select j.id,j.orgid,j.readerid,bp.retrievetime operatetime,CONCAT(bp.penaltystatus,bp.type) status,r.cardnumber,r.realname,od.deptname,d.barcode,p.kanming title,p.bookno,j.createtime jieyuetime,j.duetime from  t_book_penalty bp left join  t_jieyue j on bp.jieyueid = j.id left join t_reader r on j.readerid = r.id inner join t_org_dept od on r.orgdeptid = od.id left join t_periodical_detail d on j.bookdetailid = d.id left join t_periodical_zhengding p on d.periodicalid = p.id where j.flag=1 and bp.retrievetime is not null and bp.orgid = ? and DATE_FORMAT(bp.retrievetime,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') " +
				" ) t_fakuan_tui" +
				") t ");
		list.add(orgid);list.add(orgid);list.add(orgid);list.add(orgid);list.add(orgid);list.add(orgid);list.add(orgid);list.add(orgid);
		where.append(" order by operatetime desc,id desc LIMIT 100) tt ");
		return Db.find(select+where.toString(),CommonUtils.listToArray(list));
	}

	public List<Record> jieyuereport(Integer orgid,String starttime,String endtime){
		String sql = " select library," +
				"(select count(*)   from t_jieyue b inner join t_book_detail c on b.bookdetailid=c.id   where a.orgid =b.orgid   and b.flag=0 and  c.libraryid = a.id and b.createtime between ? and ? )  as tszcs," +
				"(select count(*)   from t_jieyue b inner join t_periodical_detail c on b.bookdetailid=c.id  where a.orgid =b.orgid  and b.flag=1 and  a.id = c.libraryid and b.createtime between ? and ? )  as qkzcs " +
				" from t_library a  where orgid=?  " ;

		return Db.find(sql,starttime,endtime+" 23:59:59",starttime,endtime+" 23:59:59" ,orgid);
	}

	public Record jieyuereport(Integer orgid){
		String sql = " select sum(tszcs) as tszcs,sum(qkzcs) as qkzcs from ( " +
				"select  " +
				"(select count(*)   from t_jieyue b inner join t_book_detail c on b.bookdetailid=c.id   where a.orgid =b.orgid   and b.flag=0 and  c.libraryid = a.id and b.status=? )  as tszcs," +
				"(select count(*)   from t_jieyue b inner join t_periodical_detail c on b.bookdetailid=c.id  where a.orgid =b.orgid  and b.flag=1 and  a.id = c.libraryid and b.status=?)  as qkzcs " +
				" from t_library a  where orgid=?  ) as t " ;

		return Db.findFirst(sql,0,0,orgid);
	}

	public List<Record> findCardnumber(int orgid,String realname){
		List<Record> r = Db.find("SELECT a.cardnumber,a.realname,b.deptname FROM t_reader a LEFT JOIN t_org_dept b ON a.orgdeptid = b.id WHERE a.orgid = ? AND a.realname = ? and a.status = 1 ",orgid,realname);
		return r;
	}
}
