package com.service;


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.model.Book;
import com.model.BookDetail;
import com.tools.ISBNTools;
import com.utils.CommonUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 图书详细信息
 * 2018-10-23
 * wush
 */
public class BookDetailService {

	public static final BookDetailService me = new BookDetailService();
	private BookDetail dao = new BookDetail().dao();

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



	public Page<BookDetail> paginate(int pageNumber,int pagesize,String timesortd,String timesorta,Book bean, String bookstatus,String status,Integer library,String barcode,String startDate,String endDate, String field ){
		String select = "SELECT   a.id as bookid, a.bookno, a.orgid, typeid, typecode, typecommonid, shucihao, booknum, isbn, title, publisher, keyword, edition, author, authorworkmodeid," +
				" authorguobieid, publishdate,  publishaddress, price,   newsource,   c.id as bookdetailid,  libraryid, piciid, barcode, status, bookstatus, jieyuenums, yuyuenums, yujienums," +
				"   destroytime, destroyreason, putindate  ,   b.id as bookplusid,   b.bltitle, subtitle, otherauthor, totalpages, workmodeid, guobieid, sizeid, filesurl, languageid, bindingid," +
				" congshuming, congshuauthor, juanci,c.bookshelfinfoStr," +
				"    ceci, b.imgurl, b.resourceurl, b.resourceopentimes, moneysource, arrivedate, fuzhu ,d.library ";
		StringBuilder where = new StringBuilder(" from t_book a left JOIN t_book_plus b ON a.id = b.bookid " +
				" inner JOIN t_book_detail c on a.id=c.bookid" +
				" inner JOIN t_library d  ON c.libraryid = d.id " +
				" where a.orgid =? ");


		List<Object> paramList =new ArrayList<Object>();
		paramList.add(bean.getOrgid());
		if (bean.getIsbn() !=null && !bean.getIsbn().equals("")){
			where.append(" and isbn =? ");
			paramList.add(ISBNTools.fmtisbn(bean.getIsbn()));
		}

		if (bean.getTitle() !=null && !bean.getTitle().equals("")){
			where.append(" and title like ? ");
			paramList.add("%"+bean.getTitle()+"%");
		}

		if (barcode!=null && !barcode.equals("")){
			String[] barcodes =barcode.split(",");
			where.append(" and ( ");
			for (int i=0;i<barcodes.length;i++){
				String code = barcodes[i];
				if (code.indexOf("-")==-1){
					where.append(" barcode =? ");
					paramList.add(code);
				}else{
					where.append("    ( barcode >=? and barcode<=? ) ");
					paramList.add(code.split("-")[0]);
					paramList.add(code.split("-")[1]);
				}

				if (i <barcodes.length-1){
					where.append(" OR ");
				}
			}
			where.append(" )");
		}

		if (bean.getAuthor() !=null && !bean.getAuthor().equals("")){
			where.append(" and author like ? ");
			paramList.add("%"+bean.getAuthor()+"%");
		}
		if (bean.getPublisher() !=null && !bean.getPublisher().equals("")){
			where.append(" and publisher like ? ");
			paramList.add("%"+bean.getPublisher()+"%");
		}
		if (bean.getBookno() !=null && !bean.getBookno().equals("")){
			where.append(" and bookno =? ");
			paramList.add(bean.getBookno());
		}
		if (bean.getTypeid() !=null){
			where.append(" and typeid =? ");
			paramList.add(bean.getTypeid());
		}
		if(StringUtils.isNotBlank(bookstatus)){
			where.append(" and c.bookstatus =? ");
			paramList.add(bookstatus);
		}
		if(StringUtils.isNotBlank(status)){
			if(status.equals("0,1,2,4")){
				where.append(" and c.status != 3 ");
			}else {
				where.append(" and c.status in (? ) ");
				paramList.add(status);
			}

		}
		if(CommonUtils.isNotNullOrNotEmpty(bean.getTypecode())) {
			where.append(" and a.typecode =? ");
			paramList.add(bean.getTypecode());
		}

		if (library !=null){
			where.append(" and c.libraryid =? ");
			paramList.add(library);
		}

		if(CommonUtils.isNotNullOrNotEmpty(startDate)) {
			where.append(" and DATE_FORMAT(c.createtime,'%Y-%m-%d') >= ? ");
			paramList.add(startDate);
		}
		if(CommonUtils.isNotNullOrNotEmpty(endDate)) {
			where.append(" and DATE_FORMAT(c.createtime,'%Y-%m-%d') <= ? ");
			paramList.add(endDate);
		}

		if(StringUtils.isNotBlank(field)){
			if(field.indexOf("order by barcode desc") > -1) {
				where.append(" order by c.barcode desc");
			} else if(field.indexOf("order by barcode asc") > -1){
				where.append(" order by c.barcode asc");
			}
		}else {
			where.append(" order by a.createtime desc");
		}


		/*if(StringUtils.isNotBlank(timesortd)){
			where.append(" order by a.createtime desc ");
		}
		if(StringUtils.isNotBlank(timesorta)){
			where.append(" order by a.createtime asc ");
		}*/

		return dao.paginate(pageNumber, pagesize,select,where.toString(),paramList.toArray(new Object[0]));
	}

	public BookDetail booktotal(Book bean, String bookstatus,String status,Integer library,String barcode ){
		String select = "SELECT   COUNT(c.id) as tszcs,count(distinct a.shucihao,a.typecode) tszs,sum(a.price) as price ";
		StringBuilder where = new StringBuilder(" from t_book a left JOIN t_book_plus b ON a.id = b.bookid " +
				" inner JOIN t_book_detail c on a.id=c.bookid" +
				" inner JOIN t_library d  ON c.libraryid = d.id " +
				" where a.orgid =?");


		List<Object> paramList =new ArrayList<Object>();
		paramList.add(bean.getOrgid());
		if (bean.getIsbn() !=null && !bean.getIsbn().equals("")){
			where.append(" and isbn =? ");
			paramList.add(ISBNTools.fmtisbn(bean.getIsbn()));
		}

		if (bean.getTitle() !=null && !bean.getTitle().equals("")){
			where.append(" and title like ? ");
			paramList.add("%"+bean.getTitle()+"%");
		}

		if (barcode!=null && !barcode.equals("")){
			String[] barcodes =barcode.split(",");
			where.append(" and ( ");
			for (int i=0;i<barcodes.length;i++){
				String code = barcodes[i];
				if (code.indexOf("-")==-1){
					where.append(" barcode =? ");
					paramList.add(code);
				}else{
					where.append("    ( barcode >=? and barcode<=? ) ");
					paramList.add(code.split("-")[0]);
					paramList.add(code.split("-")[1]);
				}

				if (i <barcodes.length-1){
					where.append(" OR ");
				}
			}
			where.append(" )");
		}

		if (bean.getAuthor() !=null && !bean.getAuthor().equals("")){
			where.append(" and author like ? ");
			paramList.add("%"+bean.getAuthor()+"%");
		}
		if (bean.getPublisher() !=null && !bean.getPublisher().equals("")){
			where.append(" and publisher like ? ");
			paramList.add("%"+bean.getPublisher()+"%");
		}
		if (bean.getBookno() !=null && !bean.getBookno().equals("")){
			where.append(" and bookno =? ");
			paramList.add(bean.getBookno());
		}
		if (bean.getTypeid() !=null){
			where.append(" and typeid =? ");
			paramList.add(bean.getTypeid());
		}
		if(StringUtils.isNotBlank(bookstatus)){
			where.append(" and c.bookstatus =? ");
			paramList.add(bookstatus);
		}
		if(StringUtils.isNotBlank(status)){
			if(status.equals("0,1,2,4")){
				where.append(" and c.status != 3 ");
			}else {
				where.append(" and c.status = (? ) ");
				paramList.add(status);
			}

		}
		if (library !=null){
			where.append(" and c.libraryid =? ");
			paramList.add(library);
		}


		return dao.findFirst(select+where.toString(),paramList.toArray(new Object[0]));
	}

    /**
     * 通过书本ID查询
     * @param bookId
     */
    public List<Record> findByBookId(int bookId) {
		List<Record> bookList = Db.find("select * from t_book_detail where bookid=?", bookId);
		return bookList;
	}

	/**
	 * 批量保存信息
	 * @param list
	 */
	public void batchSaveInfo(List<BookDetail> list,int operatorid){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				for (BookDetail bookDetail : list) {
					bookDetail.setCreatetime(new Date());
					bookDetail.setOperatorid(operatorid);
					bookDetail.save();
				}
				return true;
			}
		});
	}

	public List<Record> findDetailListById(String id,int orgid){
		return Db.find("select d.* from t_book_detail d left join t_book b on d.bookid = b.id where (d.status  in (0,1,2,4) or d.status is null) and b.id = ? and b.orgid=?",id,orgid);
	}

	/**
	 * 馆藏图书功能用
	 * @param id
	 * @param orgid
	 * @return
	 */
	public List<Record> findDetailList(String id,int orgid){
		return Db.find("select d.* from t_book_detail d left join t_book b on d.bookid = b.id where (d.status  in (0,1,2,4) or d.status is null) and bookstatus=1 and b.id = ? and b.orgid=?",id,orgid);
	}

	public void deleteById(int id,int orgid){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Record record = Db.findFirst("select d.id,d.bookid from t_book_detail d left join t_book b on d.bookid = b.id where d.id = ? and d.bookstatus = 0 and b.orgid=?",id,orgid);
				if(CommonUtils.isNotNullOrNotEmpty(record)){
					Db.delete("delete from t_book_detail where id = ?",record.getInt("id"));
				}
//				Db.update("update t_book set booknum = booknum-1 where id = ?",record.getInt("bookid"));
				return true;
			}
		});
	}


	public List<Record>  findNotCancelBook(String bookdetailidlist){
		return Db.find("select title,t_book_detail.id from t_book inner join t_book_detail on t_book.id=t_book_detail.bookid where status not in (0) and  t_book_detail.id in ("+bookdetailidlist+") ");

	}

	public void updateBookDetailStatus(String bookdetailidlist,Integer status,Integer orgid,String reason,Integer operatorid){
		Db.update("update t_book_detail set status =?,destroytime =?,destroyreason =? where  id in ("+bookdetailidlist+")",status,new Date(),reason);
		Db.update("INSERT INTO t_book_zhuxiao (orgid, bookdetailid, bookid, flag, reason, status, createtime, operatorid) " +
				" select "+ orgid+",id,bookid,0,'"+reason+"',0, now(), "+operatorid+" from  t_book_detail where id in (" + bookdetailidlist +")");



	}

	public void updateBookNum(List<String> bookIdList){
		for (String bookid : bookIdList){
			int booknum =Db.queryInt("select count(*) from t_book_detail where bookid=? and status in (0,1,2,4)",bookid);
			Db.update("update t_book set booknum=? where id=?",booknum,bookid);
		}
	}

	public List<BookDetail> findBookDetail(String[] idList){
		StringBuffer idbuf=new StringBuffer();
		for (String id: idList){
			idbuf.append(id).append(",");
		}
		idbuf.append("-1");
		String sql ="select * from t_book_detail where id in (" +idbuf.toString()+")";
		return dao.find(sql);
	}

	public Record bookBarcodeExists(int orgid,String barcode) {
		return Db.findFirst("select d.id from t_book_detail d left join t_book b on d.bookid=b.id where barcode = ? and orgid = ?  ",barcode,orgid);
	}

	public Record findBookByBarcode(int orgid,String barcode) {
		return Db.findFirst("select d.id as bookdetailid , barcode,b.title,b.bookno,title,author,price,publisher,libraryid from t_book_detail d left join t_book b on d.bookid=b.id where barcode = ? and orgid = ? and d.status in (0) and d.bookstatus in (1) ",barcode,orgid);
	}

	public void updateBarCode(String barcode,Integer id){
		Db.update("update t_book_detail set barcode=? where id=? ",barcode,id);
	}

	public void updateRFIFByBarCode(String barcode,String rfid,Integer orgid) throws Exception {

		Record record =Db.findFirst(" SELECT bd.id FROM t_book_detail bd " +
				"INNER JOIN t_book b ON bd.bookid = b.id " +
				"WHERE bd.barcode=? AND b.orgid = ? ",barcode,orgid);
		if (record ==null){
			throw new Exception("没有找到图书");
		}
		Db.update("UPDATE t_book_detail SET rfid =? WHERE id=? ",rfid,record.getInt("id"));
	}
}
