package com.service.schoolhome;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.model.BookRecommend;
import com.utils.CommonUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;

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

    public Record readerInfo(Integer readerid){
        Record r = Db.findFirst("SELECT a.realname,a.cardnumber,a.headimgurl,b.jieyueshuliang,COUNT(c.id) as yjs " +
                "FROM t_reader a " +
                "LEFT JOIN t_reader_auth b ON a.readerauthid = b.id " +
                "LEFT JOIN t_jieyue c ON a.id = c.readerid " +
                "WHERE c.`status` = 0 AND a.id = ?",readerid);
        return r;
    }
    public Page<Record> myBorrowing(int pageNumber, int pagesize, Integer readerid,Integer jilv,String title,
                                    String author,Integer isoverdue,String kssj){
        String select = "SELECT b.barcode,c.title,c.author,c.publisher," +
                "DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime,DATE_FORMAT(a.duetime,'%Y-%m-%d') AS 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 WHERE a.readerid = ?");
        List list = new ArrayList();
        list.add(readerid);
        if(jilv != null){
            if(jilv == 0){
                where.append(" AND a.status = 0");
            }
        }
        if(StringUtils.isNotBlank(title)){
            where.append(" AND c.title = ?");
            list.add(title);
        }
        if(StringUtils.isNotBlank(author)){
            where.append(" AND c.author = ?");
            list.add(author);
        }
        if(isoverdue != null){
            if(isoverdue == 1){
                where.append(" AND a.isoverdue = 1");
            }else if(isoverdue == 2){
                where.append(" AND (a.isoverdue = 2 or a.isoverdue is null)");
            }
        }
        if(StringUtils.isNotBlank(kssj)){
            where.append(" AND DATE_FORMAT(a.createtime,'%Y-%m-%d') = ?");
            list.add(kssj);
        }
        where.append(" ORDER BY a.createtime DESC");
        return Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
    }

    public Page<Record> myYuYue(int pageNumber, int pagesize, Integer readerid){
        String select = "SELECT a.id as yuyueid,b.isbn,b.title,b.author,b.publisher,b.bookno,a.createtime,a.endtime,a.`status` ";
        StringBuilder where = new StringBuilder(" FROM t_yuyue a " +
                "LEFT JOIN t_book b ON a.bookid = b.id WHERE a.deletestatus = 0 AND a.readerid = ?");
        List list = new ArrayList();
        list.add(readerid);
        where.append(" ORDER BY a.createtime DESC");
        return Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
    }

    public Page<Record> myYuJie(int pageNumber, int pagesize, Integer readerid){
        String select = "SELECT a.id as yuyueid,b.isbn,b.title,b.author,b.publisher,b.bookno,a.createtime,a.endtime,a.`status` ";
        StringBuilder where = new StringBuilder(" FROM t_yujie a " +
                "LEFT JOIN t_book b ON a.bookid = b.id WHERE a.deletestatus = 0 AND a.readerid = ?");
        List list = new ArrayList();
        list.add(readerid);
        where.append(" ORDER BY a.createtime DESC");
        return Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
    }

    public Page<Record> myCollect(int pageNumber, int pagesize, Integer readerid){
        String select = "SELECT a.id,b.isbn,b.title,b.author,b.publisher,b.bookno,b.id as bookid,SUM(CASE WHEN c.status=0 THEN 1 ELSE 0 END) AS zgs ";
        StringBuilder where = new StringBuilder(" FROM t_collection a LEFT JOIN t_book b ON a.bookid = b.id " +
                "LEFT JOIN t_book_detail c ON b.id = c.bookid WHERE a.readerid = ? GROUP BY a.id");
        List list = new ArrayList();
        list.add(readerid);
        return Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
    }

    public void cancelCollect(int collectid){
        Db.delete("DELETE FROM t_collection WHERE id = ?",collectid);
    }

    public Page<Record> myBookRecommend(int pageNumber, int pagesize, Integer readerid){
        String select = "SELECT isbn,bookname,author ";
        StringBuilder where = new StringBuilder(" FROM t_book_recommend WHERE readerid = ? ");
        List list = new ArrayList();
        list.add(readerid);
        return Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
    }

    public void saveBookRecommend(String title,String isbn,String author,Integer readerid){
        BookRecommend bean = new BookRecommend();
        bean.setBookname(title).setReaderid(readerid).setIsbn(isbn).setAuthor(author).save();
    }

    public Page<Record> myPayment(int pageNumber, int pagesize, Integer readerid){
        String select = "SELECT CASE WHEN type = '0' THEN '丢书' WHEN type = '1' THEN '损书'" +
                "WHEN type = '2' THEN '超期' ELSE '其它' END as type,penaltyamount,DATE_FORMAT(createtime,'%Y-%m-%d') AS createtime ";
        StringBuilder where = new StringBuilder(" FROM t_book_penalty WHERE readerid = ? ");
        List list = new ArrayList();
        list.add(readerid);
        return Db.paginate(pageNumber, pagesize,select,where.toString(),CommonUtils.listToArray(list));
    }

    public void cancelYuyueBook(int yuyuetype,int yuyueid,int flag,int yuyuestatus){
        String yuyuetablename = "t_yuyue";
        if(2==yuyuetype) {
            yuyuetablename = "t_yujie";
        }
        String tablename="t_book_detail";
        if (flag == 1) {
            tablename = "t_periodical_detail ";
        }
        Db.update("update "+yuyuetablename +" set status=? where deletestatus=0 and id = ?",yuyuestatus,yuyueid);
    }

    public int deleteYuyueInfo(String yuyueidstr,int yuyuetype) {
        String yuyuetablename = "t_yuyue";
        if(2==yuyuetype) {
            yuyuetablename = "t_yujie";
        }
        return Db.update("update "+yuyuetablename+" set deletestatus = 1 where id in ("+yuyueidstr+") ");
    }
    public Record userInfo(Integer readerid){
        Record r = Db.findFirst("SELECT CASE WHEN a.`status` = 1 THEN '正常' WHEN a.`status` = 2 THEN '挂失' " +
                "WHEN a.`status` = 3 THEN '停借' WHEN a.`status` = 4 THEN '注销' " +
                "WHEN a.`status` = 5 THEN '超借' END AS status,a.cardnumber,a.realname,a.sex," +
                "DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime,DATE_FORMAT(a.losedate,'%Y-%m-%d') AS losedate,b.deptname,c.* " +
                "FROM t_reader a LEFT JOIN t_org_dept b ON a.orgdeptid = b.id LEFT JOIN t_reader_auth c ON a.readerauthid = c.id " +
                "WHERE a.id = ?",readerid);
        return r;
    }

    public int editPw(String originalpw,String pw,Integer readerid){
        Record r = Db.findFirst("SELECT COUNT(*) as num FROM t_reader WHERE `password` = ? AND id = ?",originalpw,readerid);
        Integer num = r.getInt("num");
        if(num>0){
            Db.update("UPDATE t_reader SET `password` = ? WHERE id = ? AND `password` = ?",pw,readerid,originalpw);
            return 0;
        }else {
            return 1;
        }
    }

    public int readerCardLoss(String pw,Integer readerid){
        Record r = Db.findFirst("SELECT COUNT(*) as num FROM t_reader WHERE `password` = ? AND id = ?",pw,readerid);
        Integer num = r.getInt("num");
        if(num>0){
            Db.update("UPDATE t_reader SET `status` = 2 WHERE id = ? AND `password` = ?",readerid,pw);
            return 0;
        }else {
            return 1;
        }
    }
}
