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.Library;
import com.model.LibraryClock;
import com.model.User;
import com.utils.CommonUtils;
import org.apache.commons.lang3.StringUtils;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.*;

/**
 * 藏书室信息表
 * 2018-10-19
 * zj
 */
public class LibraryService {


	public static final LibraryService me = new LibraryService();
	private Library dao = new Library().dao();
	private User userDao = new User().dao();

	public Library findById(int id){
		return dao.findById(id);
	}
	public Page<Library> paginate(int pageNumber,int pagesize, Library bean){
		String select = "select a.*,b.orgname ";
		StringBuilder where = new StringBuilder(" from t_library a LEFT JOIN t_org b on a.orgid = b.id where 1=1");
		List values = new ArrayList();
		String library = bean.getLibrary();
		if(StringUtils.isNotBlank(library)){
			where.append(" and a.library like ? ");
			values.add("%"+library+"%");
		}

		if (bean.getOrgid() !=null){
			where.append(" and a.orgid=?");
			values.add(bean.getOrgid());
		}

		return dao.paginate(pageNumber, pagesize,select,where.toString(), CommonUtils.listToArray(values));
	}

	public List<Map<String,String>> reportdatabylibrary(Integer orgid){
		List<Map<String,String>> resultList = new ArrayList<>();

		String sql ="select id,library from t_library  where orgid=?";
		List<Record> recordList = Db.find(sql,orgid);
		for (Record record :recordList) {
			Map<String,String> map = new HashMap<String, String>();
			map.put("library",record.get("library"));

			sql = "select count(distinct c.id) as tszcs,count(distinct b.shucihao,b.typecode) as tszs,ifnull(sum(b.price),0) as tszje  from  t_book b inner join t_book_detail c on b.id =c.bookid where orgid =?   and c.status !=3  AND c.bookstatus = 1 and libraryid=?";
			Record record2=Db.findFirst(sql,orgid,record.getInt("id"));
			map.put("tszcs",record2.get("tszcs"));
			map.put("tszs",record2.get("tszs"));
			map.put("tszje",record2.get("tszje"));

			sql = " select count(distinct d.kanming,d.bookno) as qkzs,ifnull(sum(e.price),0) as qkzje,count(e.id) as qkzcs from t_periodical_zhengding  d  inner join t_periodical_detail e on e.periodicalid = d.id and e.status not in (3) and d.orgid=? and d.libraryid=? ";
			record2=Db.findFirst(sql,orgid,record.getInt("id"));
			map.put("qkzs",record2.get("qkzs"));
			map.put("qkzje",record2.get("qkzje"));
			map.put("qkzcs",record2.get("qkzcs"));

			resultList.add(map);
		}
		return resultList;
	}

	public void setDefault(Integer id,Integer orgid){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Db.update("UPDATE t_library SET defaultlibrary = 1 WHERE id = ?",id);
				Db.update("UPDATE t_library SET defaultlibrary = 0 WHERE orgid = ? AND id != ?",orgid,id);
				return true;
			}
		});
	}

	public void deleteById(int id) throws Exception{
		int n=Db.queryInt("SELECT COUNT(*) AS num FROM t_book_detail WHERE libraryid=?",id );
		if (n>0){
			throw new Exception("藏地存有图书信息，请先进行调拨转移，否则不可删除！");
		}
		n=Db.queryInt("SELECT COUNT(*) AS num FROM t_periodical_detail WHERE libraryid=?" ,id);
		if (n>0){
			throw new Exception("藏地存有期刊信息，请先进行调拨转移，否则不可删除！");
		}

		dao.deleteById(id);
	}
	public void addOrUpdate(Library bean, int operatorid){
		Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				if(bean.getId()== null){
					User userorgid = userDao.findFirst("SELECT orgid from t_user WHERE id = ? ",operatorid);
					int orgid = userorgid.getInt("orgid");
					bean.setOrgid(orgid).setCreatetime(new Date()).setOperatorid(operatorid).save();
				}else{
					Library old = dao.findById(bean.getId());
					bean.setCreatetime(old.getCreatetime()).setOperatorid(old.getOperatorid()).setUpdatetime(new Date()).setUpdateuserid(operatorid).update();
				}
				return true;
			}
		});
	}



	public  List<Library> librarylistforselect(int orgid){
		List list = Db.find("SELECT id,library as text from t_library where orgid=?",orgid);
		return list;
	}

	public Map<String,Integer> libraryNameToIDMap(int orgid){
		List<Library> libraryList=dao.find("select library,id from t_library where orgid = ?",orgid);
		Map<String,Integer> map = new HashMap<String,Integer>();
		for (Library library : libraryList) {
			map.put(library.getLibrary(),library.getId());
		}
		return map;
	}

	public  List<Library> floatinglibrary(int orgid){
		List list = Db.find("SELECT relationid,equipmentname FROM t_equipment WHERE equipmenttype=? and orgid=?",9,orgid);
		return list;
	}
}
