package com.controller;

import com.config.BaseController;
import com.config.ControllerInterface;
import com.jfinal.aop.Clear;
import com.jfinal.log.Log;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.upload.UploadFile;
import com.model.Book;
import com.model.BookZhuxiao;
import com.service.BookService;
import com.utils.CommonUtils;
import com.utils.ExcelExportUtil;
import com.utils.ExcelUtil;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 图书盘查
 * 2018-10-23
 * zj
 */
@ControllerInterface(path="/checkBook")
public class CheckBookController extends BaseController {
    public Log logger = Log.getLog(CheckBookController.class);
    BookService service = BookService.me;

    /**
     * 用户上传txt文件，读取文件后获取里面所有条码，并且查出所有上传条码以外的图书列表
     */
    public void findListByUploadFiles(){
        List<UploadFile> list = getFiles();
        Integer orgid = getParaToInt("orgid",0);
        if(orgid == 0){
            fail(0,"orgid不能为空");
            return;
        }
        for(UploadFile uploadFile : list) {
            List<Record> barcodeList = new ArrayList();
            String fileext = uploadFile.getOriginalFileName().substring(uploadFile.getOriginalFileName().lastIndexOf(".")+1);
            FileReader reader = null;//定义一个fileReader对象，用来初始化BufferedReader
            try {
                if(fileext.equals("txt")){
                    reader = new FileReader(uploadFile.getFile());
                    BufferedReader bReader = new BufferedReader(reader);//new一个BufferedReader对象，将文件内容读取到缓存
                    String s = "";
                    while ((s =bReader.readLine()) != null) {//逐行读取文件内容，不读取换行符和末尾的空格
                        if(s.length() > 20) {
                            fail(0,"条码异常！");
                            return;
                        }
                        Record record = new Record();
                        record.set("barcode",s);
                        record.set("orgid",orgid);
                        barcodeList.add(record);
                    }
                    bReader.close();
                } else if (fileext.equals("xls")){
                    List<String> lit = readExcel(new FileInputStream(uploadFile.getFile()));
                    for(String str : lit){
                        Record record = new Record();
                        record.set("barcode",str);
                        record.set("orgid",orgid);
                        barcodeList.add(record);
                    }
                } else if (fileext.equals("xlsx")){
                    List<String> lit = readExcel(new FileInputStream(uploadFile.getFile()));
                    for(String str : lit){
                        Record record = new Record();
                        record.set("barcode",str);
                        record.set("orgid",orgid);
                        barcodeList.add(record);
                    }
                }
                Db.batchSave("t_temp_bookinfo",barcodeList,1000);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        success();
    }

    public void findList(){
        Integer orgid = getParaToInt("orgid",0);
        if(orgid == 0){
            fail(0,"orgid不能为空");
            return;
        }
        String select = "SELECT isbn,title,author,publisher,c.barcode,a.bookno";
        StringBuilder where = new StringBuilder(" FROM t_book a INNER JOIN t_book_detail c ON a.id = c.bookid LEFT JOIN ( SELECT * FROM t_temp_bookinfo WHERE orgid =? ) t ON c.barcode = t.barcode WHERE a.orgid =? AND t.barcode IS NULL ");
        List<Object> paramList =new ArrayList<Object>();
        paramList.add(orgid);
        paramList.add(orgid);
        String orderby = getPara("orderby");
        if(CommonUtils.isNotNullOrNotEmpty(orderby)){
            where.append(orderby);
        }
        Page page = Db.paginate(getParaToInt("pageNumber",1),getParaToInt("pageSize",10),select,where.toString(),CommonUtils.listToArray(paramList));
        success(page);
    }

    public void findZhuxiaoCount(){
        Integer orgid = getParaToInt("orgid",0);
        if(orgid == 0){
            fail(0,"orgid不能为空");
            return;
        }
        success(Db.findFirst("SELECT count(*) ct FROM t_book a INNER JOIN t_book_detail c ON a.id = c.bookid WHERE a.orgid =? and barcode not in (select barcode from t_temp_bookinfo where orgid = ?)",orgid,orgid));
    }
    /**
     * 用户上传txt文件，读取文件后获取里面所有条码，并且查出所有上传条码以外的图书列表
     * 注销上述列表的所有图书
     */
    public void batchZhuxiao(){
        Integer orgid = getParaToInt("orgid",0);
        if(orgid == 0){
            fail(0,"orgid不能为空");
            return;
        }
        Calendar calendar = Calendar.getInstance();
        List<BookZhuxiao> list = new ArrayList<>();
        List<Record> list1 = Db.find("SELECT c.id,a.id bookid FROM t_book a INNER JOIN t_book_detail c ON a.id = c.bookid WHERE a.orgid =? and barcode not in (select barcode from t_temp_bookinfo where orgid = ?)",orgid,orgid);
        for(Record record : list1) {
            BookZhuxiao bookZhuxiao = new BookZhuxiao();
            bookZhuxiao.setOrgid(orgid);
            bookZhuxiao.setBookdetailid(record.getInt("id"));
            bookZhuxiao.setBookid(record.getInt("bookid"));
            bookZhuxiao.setFlag(0);
            bookZhuxiao.setReason("盘点注销");
            bookZhuxiao.setStatus(0);
            bookZhuxiao.setCreatetime(calendar.getTime());
            list.add(bookZhuxiao);
        }
        Db.batchSave(list,1000);
        int count = Db.update("update t_book_detail set `status` = 3,destroytime=? where id in (select * from (SELECT c.id FROM t_book a INNER JOIN t_book_detail c ON a.id = c.bookid WHERE a.orgid =? and barcode not in (select barcode from t_temp_bookinfo where orgid = ?)) t)",new Date(),orgid,orgid);
        Db.delete("delete from t_temp_bookinfo where orgid = ?",orgid);
        success(count);
    }


    /**
     * 用户上传txt文件，读取文件后获取里面所有条码，并且根据条码查出图书列表
     */
    public void findListByUploadFilesEx(){
        List<UploadFile> list = getFiles();
        Integer orgid = getParaToInt("orgid",0);
        if(orgid == 0){
            fail(0,"orgid不能为空");
            return;
        }
        for(UploadFile uploadFile : list) {
            List<Record> barcodeList = new ArrayList();
            try {
                String fileext = uploadFile.getOriginalFileName().substring(uploadFile.getOriginalFileName().lastIndexOf(".")+1);
                if(fileext.equals("txt")){
                    FileReader reader = null;//定义一个fileReader对象，用来初始化BufferedReader
                    reader = new FileReader(uploadFile.getFile());
                    BufferedReader bReader = new BufferedReader(reader);//new一个BufferedReader对象，将文件内容读取到缓存
                    String s = "";
                    while ((s =bReader.readLine()) != null) {//逐行读取文件内容，不读取换行符和末尾的空格
                        if(s.length() > 20) {
                            fail(0,"条码异常！");
                            return;
                        }
                        Record record = new Record();
                        record.set("barcode",s);
                        record.set("orgid",orgid);
                        barcodeList.add(record);
                    }
                    bReader.close();
                } else if (fileext.equals("xls")){
                    List<String> lit = readExcel(new FileInputStream(uploadFile.getFile()));
                    for(String str : lit){
                        Record record = new Record();
                        record.set("barcode",str);
                        record.set("orgid",orgid);
                        barcodeList.add(record);
                    }
                } else if (fileext.equals("xlsx")){
                    List<String> lit = readExcel(new FileInputStream(uploadFile.getFile()));
                    for(String str : lit){
                        Record record = new Record();
                        record.set("barcode",str);
                        record.set("orgid",orgid);
                        barcodeList.add(record);
                    }
                }
                Db.batchSave("t_temp_bookinfo",barcodeList,1000);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        success();
    }

    public void findListEx(){
        Integer orgid = getParaToInt("orgid",0);
        if(orgid == 0){
            fail(0,"orgid不能为空");
            return;
        }
        String select = "select * ";
        StringBuilder where = new StringBuilder(" from ( SELECT isbn,title,author,publisher,d.barcode,b.bookno from t_temp_bookinfo t inner join t_book_detail d on t.barcode = d.barcode left join t_book b on d.bookid = b.id where t.orgid= ? and b.orgid = ? group by d.barcode ) t ");
        List<Object> paramList =new ArrayList<Object>();
        paramList.add(orgid);
        paramList.add(orgid);
        String orderby = getPara("orderby");
        if(CommonUtils.isNotNullOrNotEmpty(orderby)){
            where.append(orderby);
        }
        Page page = Db.paginate(getParaToInt("pageNumber",1),getParaToInt("pageSize",10),select,where.toString(),CommonUtils.listToArray(paramList));
        success(page);
    }

    public void findZhuxiaoCountEx(){
        Integer orgid = getParaToInt("orgid",0);
        if(orgid == 0){
            fail(0,"orgid不能为空");
            return;
        }
        success(Db.findFirst("SELECT count(*) ct FROM t_book a INNER JOIN t_book_detail c ON a.id = c.bookid WHERE a.orgid =? and barcode in (select barcode from t_temp_bookinfo where orgid = ?)",orgid,orgid));
    }

    /**
     * 用户上传txt文件，读取文件后获取里面所有条码 并注销
     */
    public void batchZhuxiaoEx(){
        Integer orgid = getParaToInt("orgid",0);
        if(orgid == 0){
            fail(0,"orgid不能为空");
            return;
        }
        Calendar calendar = Calendar.getInstance();
        List<BookZhuxiao> list = new ArrayList<>();
        List<Record> list1 = Db.find("SELECT c.id,a.id bookid FROM t_book a INNER JOIN t_book_detail c ON a.id = c.bookid WHERE a.orgid =? and barcode in (select barcode from t_temp_bookinfo where orgid = ?)",orgid,orgid);
        for(Record record : list1) {
            BookZhuxiao bookZhuxiao = new BookZhuxiao();
            bookZhuxiao.setOrgid(orgid);
            bookZhuxiao.setBookdetailid(record.getInt("id"));
            bookZhuxiao.setBookid(record.getInt("bookid"));
            bookZhuxiao.setFlag(0);
            bookZhuxiao.setReason("盘点注销");
            bookZhuxiao.setStatus(0);
            bookZhuxiao.setCreatetime(calendar.getTime());
            list.add(bookZhuxiao);
        }
        Db.batchSave(list,1000);
        int count = Db.update("update t_book_detail set `status` = 3,destroytime=? where id in (select * from (SELECT c.id FROM t_book a INNER JOIN t_book_detail c ON a.id = c.bookid WHERE a.orgid =? and barcode in (select barcode from t_temp_bookinfo where orgid = ?)) t)",new Date(),orgid,orgid);
        Db.delete("delete from t_temp_bookinfo where orgid = ?",orgid);
        success(count);
    }

    public List<String> readExcel(InputStream in){
        List<String> dataList = new ArrayList();
        try{
            // 获得工作簿
            Workbook workbook = WorkbookFactory.create(in);
            // 获得工作表个数
            int sheetCount = workbook.getNumberOfSheets();
            // 遍历工作表
            for (int i = 0; i < sheetCount; i++)
            {
                Sheet sheet = workbook.getSheetAt(i);
                // 获得行数
                int rows = sheet.getLastRowNum() + 1;
                // 读取数据
                for (int rownum = 0; rownum < rows; rownum++)
                {
                    String info = "";;
                    Row r = sheet.getRow(rownum);
                    if(r ==null) continue;
                    boolean flag = false; //行数据全部为空，不继续读
                    for (int col = 0; col < sheet.getRow(0).getLastCellNum(); col++)
                    {
                        Cell cell = r.getCell(col);
                        if(cell !=null){
                            String value = getCellValue(cell);
                            if (!"".equals(value.trim())){
                                flag=true;
                            }
                            info = value;
                        }
                    }
                    if (flag){
                        dataList.add(info);
                    }
                }
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return dataList;
    }

    public String getCellValue(Cell cell){
        if(cell.getCellType() == CellType.NUMERIC){
            short format = cell.getCellStyle().getDataFormat();
            SimpleDateFormat sdf = null;
            if(format == 14 || format == 31 || format == 57 || format == 58){
                //日期
                sdf = new SimpleDateFormat("yyyy-MM-dd");
            }else if (format == 20 || format == 32) {
                //时间
                sdf = new SimpleDateFormat("HH:mm");
            }else { // 不是日期格式
                NumberFormat nf = NumberFormat.getInstance();
                String s = nf.format(cell.getNumericCellValue());
                if (s.indexOf(",") >= 0) {
                    s = s.replace(",", "");
                }
                return s;
            }
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
            return sdf.format(date);
        } else if (cell.getCellType() == CellType.BOOLEAN){
            return cell.getBooleanCellValue()+"";
        }else {
            return cell.getStringCellValue();
        }

    }
}
