cucu_YOS/src/custom/restcontroller/sale/cashbill/cashbill.java

package restcontroller.sale.cashbill;

import beans.clear.ClearData;
import beans.data.BatchDeleteErr;
import beans.datacontrllog.DataContrlLog;
import beans.datateam.DataTeam;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sun.xml.internal.xsom.impl.Ref;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.annotation.cm;
import common.data.*;
import common.data.Row;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;
import restcontroller.system.attachment.Attachment;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

import static restcontroller.webmanage.saletool.orderclue.orderclue.isBigDecimal;

public class cashbill extends Controller {

    public cashbill(JSONObject arg0) throws YosException {
        super(arg0);
        // TODO Auto-generated constructor stub
    }


    /**
     * 下载凭证导入单模板
     *
     * @return
     */
    @API(title = "下载凭证导入单模板", apiversion = R.ID20230506162603.v1.class)
    public String downloadCashbillExcel() throws YosException {
        ExcelFactory excelFactory = new ExcelFactory("ImportTemplateForCashbill");

        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
        CellStyle style = xssfFWorkbook.createCellStyle();
        // 设置为文本格式，防止身份证号变成科学计数法
        DataFormat format = xssfFWorkbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        // 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
        sheet.setDefaultColumnStyle(0, style);
        sheet.setDefaultColumnStyle(1, style);
        sheet.setDefaultColumnStyle(2, style);
        if(content.getString("type").equals("收入")){
            IncomeExportExcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
            XSSFCellStyle titleCellStyle1 = IncomeExportExcel.createTitleCellStyle1(xssfFWorkbook);
            XSSFCellStyle titleCellStyle2 = IncomeExportExcel.createTitleCellStyle2(xssfFWorkbook);
            XSSFCellStyle titleCellStyle3 = IncomeExportExcel.createBodyCellStyle(xssfFWorkbook);
            XSSFCellStyle titleCellStyle4 = IncomeExportExcel.createTitleCellStyle3(xssfFWorkbook);
            IncomeExportExcel.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, titleCellStyle4,
                    xssfFWorkbook);// 写入标题

        }else if(content.getString("type").equals("支出")){
            PayExportExcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
            XSSFCellStyle titleCellStyle1 = PayExportExcel.createTitleCellStyle1(xssfFWorkbook);
            XSSFCellStyle titleCellStyle2 = PayExportExcel.createTitleCellStyle2(xssfFWorkbook);
            XSSFCellStyle titleCellStyle3 = PayExportExcel.createBodyCellStyle(xssfFWorkbook);
            XSSFCellStyle titleCellStyle4 = PayExportExcel.createTitleCellStyle3(xssfFWorkbook);
            PayExportExcel.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, titleCellStyle4,
                    xssfFWorkbook);// 写入标题
        }else{
            return getErrReturnObject().setErrMsg("凭证类型不匹配，无法下载导入文件").toString();
        }

        Rows aa = uploadExcelToObs(excelFactory);
        String url = "";
        if (!aa.isEmpty()) {
            url = aa.get(0).getString("url");
        }
        return getSucReturnObject().setData(url).toString();

    }

    /**
     * 导入凭证
     *
     * @return
     */
    @API(title = "导入凭证", apiversion = R.ID20230506162703.v1.class)
    @CACHEING_CLEAN(apiClass = cashbill.class)
    public String uploadCashbillByExcel() throws YosException {

        ExcelFactory e;
        try {

            // 华为云
            //e = getPostExcelFactory(content.getLong("attachmentid"));
            // 本地
            e = getPostExcelFactory();

            // Rows keyRow = dbConnect.runSqlQuery("select fagentnum,faddress from tagents
            // where 1=2");
            // Rows row1 = e.getSheetRows(1, keyRow, 1);
            ArrayList<String> keys = new ArrayList<>();
            ArrayList<String> sqllist = new ArrayList<>();
            keys.add("agentnum");
            keys.add("accountname");
            keys.add("amount");
            keys.add("type");
            keys.add("typemx");
            keys.add("remarks");
            Rows accountclassRows = dbConnect.runSqlQuery("select sa_accountclassid,accountname from sa_accountclass where siteid='"+siteid+"' and isused=1");
            RowsMap accountclassRowsMap = accountclassRows.toRowsMap("accountname");

            Rows agentRows = dbConnect.runSqlQuery("select agentnum,sys_enterpriseid from sa_agents where siteid='"+siteid+"'");
            RowsMap agentRowsMap = agentRows.toRowsMap("agentnum");
            if (!(content.getString("type") + "凭证导入")
                    .equals(((String) getCellFormatValue(e.getSheet(0).getRow(0).getCell(0))).replace(" ", ""))) {
                return getErrReturnObject().setErrMsg("导入失败，不符合" + content.getString("type") + "凭证导入要求").toString();
            }
            Rows rows = e.getSheetRows(0, keys, 3);
            int a = 1;
            int i = 0;
            // String msg = "";
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            for (Row row : rows) {
                String msg = "";
                if (StringUtils.isEmpty(row.getString("agentnum"))) {
                    iserr = true;
                    msg =msg + "【经销商编码为空】";
                }
                if (StringUtils.isEmpty(row.getString("accountname"))) {
                    iserr = true;
                    msg = msg +"【账户名称为空】";

                }
                if (!isBigDecimal(row.getString("amount"))) {
                    iserr = true;
                    msg = msg + "【金额错误：" + row.getString("amount") + "】";

                }
                if (StringUtils.isEmpty(row.getString("type"))) {
                    iserr = true;
                    msg = msg +"【分类为空】";

                }
                if(!agentRowsMap.containsKey(row.getString("agentnum"))){
                    msg = msg +"【该经销商编码找不到匹配的经销商】";
                }else{
                    if(agentRowsMap.get(row.getString("agentnum")).isEmpty()){
                        msg = msg +"【该经销商编码找不到匹配的经销商】";
                    }
                }

                if(!accountclassRowsMap.containsKey(row.getString("accountname"))){
                    msg = msg +"【该账户名称找不到匹配的账户】";
                }else{
                    if(accountclassRowsMap.get(row.getString("accountname")).isEmpty()){
                        msg = msg +"【该账户名称找不到匹配的账户】";
                    }
                }

                if (iserr) {
                    row.put("msg", "错误信息:" + msg);
                    rowserr.add(row);
                } else {
                    rowssuc.add(row);
                }

//				if (!msg.equals("111")) {
//					XSSFWorkbook xssfFWorkbook = e.getXssfWorkbook();
//					XSSFCellStyle cellStyle = xssfFWorkbook.createCellStyle();
//
//					XSSFFont font = xssfFWorkbook.createFont();
//
//					font.setColor((short) 0xa);
//					font.setFontHeightInPoints((short) 8);
//					font.setBold(true);
//					cellStyle.setFont(font);
//					datarow.createCell(8).setCellValue("错误信息:" + msg);
//					datarow.getCell(8).setCellStyle(cellStyle);
//
//				}

            }
            long[] sa_cashbillid = createTableID("sa_cashbill",
                    rowssuc.size());
            if (!rowssuc.isEmpty()) {
                for (Row row : rowssuc) {
                    SQLFactory sqlFactoryupload = new SQLFactory(this, "收支凭证新增");
                    sqlFactoryupload.addParameter("sa_cashbillid", sa_cashbillid[i]);
                    sqlFactoryupload.addParameter("billno", createBillCode("cashbill"));
                    if(agentRowsMap.containsKey(row.getString("agentnum"))){
                        sqlFactoryupload.addParameter("sys_enterpriseid", agentRowsMap.get(row.getString("agentnum")).get(0).getLong("sys_enterpriseid"));
                    }else {
                        continue;
                    }
                    if(accountclassRowsMap.containsKey(row.getString("accountname"))){
                        sqlFactoryupload.addParameter("sa_accountclassid", accountclassRowsMap.get(row.getString("accountname")).get(0).getLong("sa_accountclassid"));
                    }else {
                        continue;
                    }
                    if(content.getString("type").equals("收入")){
                        sqlFactoryupload.addParameter("type", 1);
                    }else{
                        sqlFactoryupload.addParameter("type", 0);
                    }
                    sqlFactoryupload.addParameter("siteid", siteid);
                    sqlFactoryupload.addParameter("remarks", row.getString("remarks"));
                    sqlFactoryupload.addParameter("amount",  row.getString("amount"));
                    sqlFactoryupload.addParameter("subclass", row.getString("typemx"));
                    sqlFactoryupload.addParameter("class", row.getString("type"));
                    sqlFactoryupload.addParameter("userid", userid);
                    sqlFactoryupload.addParameter("username", username);
                    sqlFactoryupload.addParameter("ownertable", "");
                    sqlFactoryupload.addParameter("ownerid", 0);
                    sqlFactoryupload.addParameter("source", "");
                    sqlFactoryupload.addParameter("sourcenote", "");
                    sqllist.add(sqlFactoryupload.getSQL());
                    i++;
                }
            }
            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("sa_cashbillerr");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("agentnum", "经销商编码");
                map.put("accountname", "账户名称");
                map.put("amount", "金额");
                map.put("type", "分类");
                map.put("typemx", "分类明细");
                map.put("remarks", "备注");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("agentnum");
                colNameList.add("accountname");
                colNameList.add("amount");
                colNameList.add("type");
                colNameList.add("typemx");
                colNameList.add("remarks");
                colNameList.add("msg");
                keytypemap.put("agentnum", String.class);
                keytypemap.put("accountname", String.class);
                keytypemap.put("amount", String.class);
                keytypemap.put("type", String.class);
                keytypemap.put("typemx", String.class);
                keytypemap.put("remarks", String.class);
                keytypemap.put("msg", String.class);
                rowserr.setFieldList(colNameList);
                rowserr.setFieldTypeMap(keytypemap);
                if(content.getString("type").equals("收入")){
                    addSheetIncome(excelFactory, "Sheet1", rowserr, map);
                }else {
                    addSheetPay(excelFactory, "Sheet1", rowserr, map);
                }


                Rows aa = uploadExcelToObs(excelFactory);
                String url = "";
                if (!aa.isEmpty()) {
                    url = aa.get(0).getString("url");
                }
                return getSucReturnObject().setData(url).toString();

            }
        } catch (Exception e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();

    }



    @API(title = "收支凭证新建更新", apiversion = R.ID20221009102803.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221009102903.v1.class, R.ID20221010102903.v1.class, R.ID20221009103003.v1.class, R.ID20230111103403.v1.class})
    public String insertormodify_creditbill() throws YosException {
        Long sa_cashbillid = content.getLong("sa_cashbillid");
        Long type = content.getLong("type"); //收支类型（1:收；0:支）
        Long sys_enterpriseid = content.getLong("sys_enterpriseid");
        Long sa_accountclassid = content.getLong("sa_accountclassid");
        String billcode = createBillCode("cashbill");
        String subclass = content.getString("subclass");
        String class1 = content.getString("class");
        //String billcode = "123456789";
        BigDecimal amount = content.getBigDecimal("amount");
        String remarks = content.getString("remarks");
//        String source = content.getString("source");
//        String sourcenote = content.getString("sourcenote");
        ArrayList<String> sqlList = new ArrayList<>();
        if (sa_cashbillid <= 0 || dbConnect
                .runSqlQuery("select sa_cashbillid from sa_cashbill where sa_cashbillid="
                        + sa_cashbillid)
                .isEmpty()) {
            sa_cashbillid = createTableID("sa_cashbill");
            SQLFactory sqlFactory = new SQLFactory(this, "收支凭证新增");
            sqlFactory.addParameter("billno", billcode);
            sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
            sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
            sqlFactory.addParameter("type", type);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("remarks", remarks);
            sqlFactory.addParameter("amount", amount);
            sqlFactory.addParameter("subclass", subclass);
            sqlFactory.addParameter("class", class1);
            sqlFactory.addParameter("sa_cashbillid", sa_cashbillid);
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter("ownertable", "");
            sqlFactory.addParameter("ownerid", 0);
            sqlFactory.addParameter("source", "");
            sqlFactory.addParameter("sourcenote", "");
            content.put("sa_cashbillid", sa_cashbillid);
            sqlList.add(sqlFactory.getSQL());
        } else {
            Rows rowscount = dbConnect.runSqlQuery(
                    "select status from sa_cashbill where sa_cashbillid=" + sa_cashbillid);
            if (rowscount.isEmpty()) {
                return getErrReturnObject().setErrMsg("此收支凭证不存在").toString();
            } else {
                if (!rowscount.get(0).getString("status").equals("新建")) {
                    return getErrReturnObject().setErrMsg("非新建状态的收支凭证无法更新").toString();
                }
            }
            SQLFactory sqlFactory = new SQLFactory(this, "收支凭证更新");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("remarks", remarks);
            sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
            sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
            sqlFactory.addParameter("amount", amount);
            sqlFactory.addParameter("subclass", subclass);
            sqlFactory.addParameter("class", class1);
            sqlFactory.addParameter("sa_cashbillid", sa_cashbillid);
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter("source", "");
            sqlFactory.addParameter("sourcenote", "");
            sqlList.add(sqlFactory.getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);
        return queryCashbillMain();
    }

//	@API(title = "合作企业档案列表",apiversion = R.ID20221008164103.v1.class)
//	@CACHEING
//	public String queryEnterpriseList() throws YosException {
//
//		/*
//		 * 过滤条件设置
//		 */
//		String where = " 1=1 ";
//		if (content.containsKey("where")) {
//			JSONObject whereObject = content.getJSONObject("where");
//			if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
//				where = where + " and(t1.enterprisename like'%" + whereObject.getString("condition")
//						+ "%' or t1.phonenumber like '%" + whereObject.getString("condition") + "%')";
//			}	
//		}
//		SQLFactory sqlFactory = new SQLFactory(this, "合作企业档案列表查询", pageSize, pageNumber, pageSorting);
//		sqlFactory.addParameter_SQL("where", where);
//		sqlFactory.addParameter("siteid", siteid);
//		Rows rows = dbConnect.runSqlQuery(sqlFactory);
//		return getSucReturnObject().setData(rows).toString();
//	}
//	
//	@API(title = "营销账户类别列表",apiversion = R.ID20221008164203.v1.class)
//	@CACHEING
//	public String queryAccountclassList() throws YosException {
//		long sys_enterpriseid=content.getLong("sys_enterpriseid");
//		
//		SQLFactory sqlFactory = new SQLFactory(this, "营销账户类别列表查询");
//		sqlFactory.addParameter("siteid", siteid);
//		sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
//		Rows rows = dbConnect.runSqlQuery(sqlFactory);
//		return getSucReturnObject().setData(rows).toString();
//	}

    @API(title = "收入凭证列表", apiversion = R.ID20221009102903.v1.class)
    @CACHEING
    public String queryIncomeCashbillList() throws YosException {

        /*
         * 过滤条件设置
         */
        String where = " 1=1 and t1.type=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and(t2.enterprisename like'%" + whereObject.getString("condition")
                        + "%' or t3.accountname like '%" + whereObject.getString("condition") + "%'"
                        + "  or t2.abbreviation like '%" + whereObject.getString("condition") + "%'"
                        + "  or t1.billno like '%" + whereObject.getString("condition") + "%'"
                        + "  or t1.remarks like '%" + whereObject.getString("condition") + "%'"
                        + ")";
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where = where + " and t1.status ='" + whereObject.getString("status") + "' ";
            }
            if (whereObject.containsKey("accountname") && !"".equals(whereObject.getString("accountname"))) {
                where = where + " and t3.accountname ='" + whereObject.getString("accountname") + "' ";
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where = where + " and t1.createdate >='" + whereObject.getString("begindate") + "'";
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where = where + " and t1.createdate <='" + whereObject.getString("enddate") + "'";
            }
            if (whereObject.containsKey("billno") && !"".equals(whereObject.getString("billno"))) {
                where = where + " and t1.billno ='" + whereObject.getString("billno") + "'";
            }
            if (whereObject.containsKey("isrebate") && !"".equals(whereObject.getString("isrebate"))) {
                where = where + " and ifnull(t3.isrebate,0) ='" + whereObject.getLongValue("isrebate") + "'";
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "收支凭证列表查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        ArrayList sa_cashbillids = rows.toArrayList("sa_cashbillid", new ArrayList<>());
        sa_cashbillids.add(0L);

        String sql = "select t1.sa_cashbillid,t2.billno from sa_cashbill t1 inner join sa_cashbill t2 on t1.siteid=t2.siteid and t1.ownerid=t2.sa_cashbillid and t1.ownertable='sa_cashbill' where t1.siteid='" + siteid + "' and  t1.ownerid in " + sa_cashbillids;
        sql = sql.replace("[", "(").replace("]", ")");
        RowsMap offsettingRowsMap = dbConnect.runSqlQuery(sql).toRowsMap("sa_cashbillid");
        for (Row row : rows) {
            if (row.getString("ownertable").equals("sa_cashbill") && offsettingRowsMap.containsKey(row.getString("sa_cashbillid"))) {
                row.put("offsettingbillno", offsettingRowsMap.get(row.getString("sa_cashbillid")).get(0).getString("billno"));
            }
            row.put("amount", row.getBigDecimal("amount"));
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "支出收入凭证列表", apiversion = R.ID20221010102903.v1.class)
    @CACHEING
    public String queryPayCashbillList() throws YosException {

        /*
         * 过滤条件设置
         */
        String where = " 1=1 and t1.type=0 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and(t2.enterprisename like'%" + whereObject.getString("condition")
                        + "%' or t3.accountname like '%" + whereObject.getString("condition") + "%'"
                        + "  or t2.abbreviation like '%" + whereObject.getString("condition") + "%'"
                        + "  or t1.billno like '%" + whereObject.getString("condition") + "%'"
                        + ")";
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where = where + " and t1.status ='" + whereObject.getString("status") + "' ";
            }
            if (whereObject.containsKey("accountname") && !"".equals(whereObject.getString("accountname"))) {
                where = where + " and t3.accountname ='" + whereObject.getString("accountname") + "' ";
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where = where + " and t1.createdate >='" + whereObject.getString("begindate") + "'";
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where = where + " and t1.createdate <='" + whereObject.getString("enddate") + "'";
            }
            if (whereObject.containsKey("billno") && !"".equals(whereObject.getString("billno"))) {
                where = where + " and t1.billno ='" + whereObject.getString("billno") + "'";
            }
            if (whereObject.containsKey("isrebate") && !"".equals(whereObject.getString("isrebate"))) {
                where = where + " and ifnull(t3.isrebate,0) ='" + whereObject.getLongValue("isrebate") + "'";
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "收支凭证列表查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        for (Row row : rows) {
            row.put("amount", row.getBigDecimal("amount"));
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "收支凭证详情", apiversion = R.ID20221009103003.v1.class)
    public String queryCashbillMain() throws YosException {
        Long sa_cashbillid = content.getLong("sa_cashbillid");
        SQLFactory sqlFactory = new SQLFactory(this, "收支凭证详情查询");
        sqlFactory.addParameter("sa_cashbillid", sa_cashbillid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        if (rows.size() > 0) {
            Rows offsetting = dbConnect.runSqlQuery("select billno from sa_cashbill where siteid='" + siteid + "' and '" + rows.get(0).getString("ownertable") + "'='sa_cashbill' and sa_cashbillid=" + rows.get(0).getLong("ownerid"));
            if (offsetting.isNotEmpty()) {
                rows.get(0).put("offsettingbillno", offsetting.get(0).getString("billno"));
            }
            rows.get(0).put("amount", rows.get(0).getBigDecimal("amount"));
        }
        return getSucReturnObject().setData(rows.size() > 0 ? rows.get(0) : new Row()).toString();
    }

    /**
     * 收支凭证审核
     *
     * @return
     */
    @API(title = "收支凭证审核", apiversion = R.ID20221009103103.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221009102903.v1.class, R.ID20221010102903.v1.class, R.ID20221009103003.v1.class, R.ID20221008145903.v1.class, R.ID20221009160003.v1.class, R.ID20230111103403.v1.class})
    public String release() throws YosException {
        long sa_cashbillid = content.getLong("sa_cashbillid");
        String[] s = {"sa_cashbillid"};
        for (String s1 : s) {
            if (!content.containsKey(s1))
                return getErrReturnObject().setErrMsg("缺少" + s1 + "参数").toString();
        }
        //List<String> list = sa_creditbillids.toJavaList(String.class);
        //String[] stringArray = list.toArray(new String[0]);
        //SQLFactory sqlFactoryquery = new SQLFactory(this, "信用额度调整单状态查询");
        //sqlFactoryquery.addParameter("siteid", siteid);
        //sqlFactoryquery.addParameter_in("sa_creditbillids", stringArray);
        Rows rows = dbConnect.runSqlQuery("select sa_cashbillid,sys_enterpriseid,sa_accountclassid,status,billno,amount,type,ownerid from sa_cashbill where sa_cashbillid ='" + sa_cashbillid + "' and  siteid='" + siteid + "'");
        for (Row row : rows) {
            if (row.getString("status").equals("审核")) {
                return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的收支凭证已审核，无法再次审核")
                        .toString();
            }
            if (!row.getString("status").equals("新建")) {
                return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的收支凭证为非新建状态，无法审核")
                        .toString();
            }
        }

        ArrayList<String> sqlList = new ArrayList<>();
        BigDecimal balance = BigDecimal.ZERO;  //当前账户余额
        if (!rows.isEmpty()) {
            long sys_enterpriseid = rows.get(0).getLong("sys_enterpriseid");
            long sa_accountclassid = rows.get(0).getLong("sa_accountclassid");
            long type = rows.get(0).getLong("type");
            Rows rowsaccountbalance = dbConnect.runSqlQuery("select sa_accountbalanceid,balance,creditquota from sa_accountbalance where sys_enterpriseid ='" + sys_enterpriseid + "' and  sa_accountclassid='" + sa_accountclassid + "' and siteid='" + siteid + "'");
            if (type == 1) {
                Rows offsettingRows = dbConnect.runSqlQuery("select amount from sa_cashbill where siteid='" + siteid + "' and ownertable='sa_cashbill' and ownerid=" + rows.get(0).getLong("ownerid"));
                if (offsettingRows.isNotEmpty()) {
                    sqlList.add("update sa_cashbill set writeoffamount=writeoffamount+" + offsettingRows.get(0).getBigDecimal("amount").abs() + " where siteid='" + siteid + "' and sa_cashbillid=" + rows.get(0).getLong("ownerid"));
                }
                if (rowsaccountbalance.isEmpty()) {
                    balance = rows.get(0).getBigDecimal("amount");
                    SQLFactory sqlFactory = new SQLFactory(this, "营销账户余额新增");
                    sqlFactory.addParameter("sa_accountbalanceid", createTableID("sa_accountbalance"));
                    sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                    sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("balance", rows.get(0).getBigDecimal("amount"));
                    sqlFactory.addParameter("userid", userid);
                    sqlFactory.addParameter("username", username);
                    sqlList.add(sqlFactory.getSQL());
                } else {
                    balance = rows.get(0).getBigDecimal("amount").add(rowsaccountbalance.get(0).getBigDecimal("balance"));
                    sqlList.add("update sa_accountbalance set balance='" + rows.get(0).getBigDecimal("amount").add(rowsaccountbalance.get(0).getBigDecimal("balance")) + "',changedate=CURRENT_TIME,changeby ='" + username + "',changeuserid='" + userid + "' where sys_enterpriseid ='" + sys_enterpriseid + "' and  sa_accountclassid='" + sa_accountclassid + "' and siteid='" + siteid + "'");
                }

                sqlList.add(DataContrlLog.createLog(this, "sa_cashbill", sa_cashbillid, "审核", "收入凭证审核成功").getSQL());
            } else {
                if (rowsaccountbalance.isEmpty()) {
//					SQLFactory sqlFactory = new SQLFactory(this, "营销账户余额新增");
//					sqlFactory.addParameter("sa_accountbalanceid",  createTableID("sa_accountbalance"));
//					sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
//					sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
//					sqlFactory.addParameter("siteid", siteid);
//					sqlFactory.addParameter("balance",  -rows.get(0).getLong("amount"));
//					sqlFactory.addParameter("userid", userid);
//					sqlFactory.addParameter("username", username);
//					sqlList.add(sqlFactory.getSQL());
                    return getErrReturnObject().setErrMsg("该营销账户不存在，支出凭证无法审核")
                            .toString();
                } else {
                    BigDecimal canuseamount = rowsaccountbalance.get(0).getBigDecimal("creditquota").add(rowsaccountbalance.get(0).getBigDecimal("balance"));
                    if (rows.get(0).getBigDecimal("amount").compareTo(canuseamount) == 1) {
                        return getErrReturnObject().setErrMsg("该营销账户可用余额不足，支出凭证无法审核")
                                .toString();
                    }
                    BigDecimal newbalance = rowsaccountbalance.get(0).getBigDecimal("balance").subtract(rows.get(0).getBigDecimal("amount"));
                    balance = newbalance;
                    sqlList.add("update sa_accountbalance set balance='" + newbalance + "',changedate=CURRENT_TIME,changeby ='" + username + "',changeuserid='" + userid + "' where sys_enterpriseid ='" + sys_enterpriseid + "' and  sa_accountclassid='" + sa_accountclassid + "' and siteid='" + siteid + "'");
                }

                sqlList.add(DataContrlLog.createLog(this, "sa_cashbill", sa_cashbillid, "审核", "支出凭证审核成功").getSQL());
            }


        }

        SQLFactory sqlFactoryupdate = new SQLFactory(this, "收支凭证审核");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_cashbillid", sa_cashbillid);
        sqlFactoryupdate.addParameter("balance", balance);
        sqlFactoryupdate.addParameter("checkby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }


    @API(title = "删除", apiversion = R.ID20221009103203.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221009102903.v1.class, R.ID20221010102903.v1.class, R.ID20221009103003.v1.class, R.ID20230111103403.v1.class})
    public String delete() throws YosException {

        JSONArray sa_cashbillids = content.getJSONArray("sa_cashbillids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_cashbillids.size());
        for (Object o : sa_cashbillids) {
            long sa_cashbillid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect.runSqlQuery("select sa_cashbillid,status from sa_cashbill where siteid='"
                    + siteid + "' and sa_cashbillid='" + sa_cashbillid + "'");

            if (RowsStatus.isNotEmpty()) {
                if (!RowsStatus.get(0).getString("status").equals("新建")) {
                    batchDeleteErr.addErr(sa_cashbillid, "非新建状态的凭证无法删除");
                    continue;
                }
            }
            ArrayList<String> list = new ArrayList<>();
            SQLFactory deletesql = new SQLFactory("sql:delete from sa_cashbill where siteid='" + siteid
                    + "' and sa_cashbillid=" + sa_cashbillid);
            list.add(deletesql.getSQL());

            dbConnect.runSqlUpdate(list);
        }

        ClearData.clearCashbill(this);
        return batchDeleteErr.getReturnObject().toString();

    }

    @API(title = "审核状态的收支凭证详情列表", apiversion = R.ID20221022161304.v1.class)
    @CACHEING
    public String query_cashnill_audit() throws YosException {
		/*
        过滤条件
         */
        StringBuffer where = new StringBuffer(" 1=1 ");
        Long sa_accountclassid = content.getLong("sa_accountclassid");
        Long sys_enterpriseid = content.getLong("sys_enterpriseid");

        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and (t1.billno like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t3.enterprisename like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t1.remarks like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t1.source like '%").append(whereObject.getString("condition")).append("%')");
            }

            if (whereObject.containsKey("minAmount") && whereObject.getBigDecimal("minAmount").compareTo(new BigDecimal("0")) == 1) {
                BigDecimal minAmount = whereObject.getBigDecimal("minAmount");
                if (whereObject.containsKey("maxAmount") && whereObject.getBigDecimal("maxAmount").compareTo(new BigDecimal("0")) == 1) {
                    BigDecimal maxAmount = whereObject.getBigDecimal("maxAmount");
                    where.append(" and t1.amount between ").append(minAmount).append(" and ").append(maxAmount);
                } else {
                    where.append(" and t1.amount > ").append(minAmount);
                }
            } else if (whereObject.containsKey("maxAmount") && whereObject.getBigDecimal("maxAmount").compareTo(new BigDecimal("0")) == 1) {
                BigDecimal maxAmount = whereObject.getBigDecimal("maxAmount");
                where.append(" and t1.amount < ").append(maxAmount);
            }

            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and t1.type = ").append(whereObject.getLong("type"));
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "审核状态的收支凭证详情列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "资金流水账列表查询", apiversion = R.ID20230111103403.v1.class)
    @CACHEING
    public String query_cashBill() throws YosException {
		/*
        过滤条件
         */
        Long sa_accountclassid = content.getLong("sa_accountclassid");
        if (content.containsKey("sys_enterpriseid")) {
            sys_enterpriseid = content.getLong("sys_enterpriseid");
        }

        Map<String, Rows> map = new HashMap<String, Rows>();
        /**
         * 过滤条件设置
         */
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and (t1.billno like '%" + whereObject.getString("condition") + "%') ";

            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where = where + " and DATE_FORMAT(t1.checkdate,'%Y-%m-%d') >=DATE_FORMAT('"+whereObject.getString("begindate")+"','%Y-%m-%d')";

            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where = where + " and DATE_FORMAT(t1.checkdate,'%Y-%m-%d') <=DATE_FORMAT('"+whereObject.getString("enddate")+"','%Y-%m-%d')";

            }

        }

        /**
         * SQL资金流水账查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "资金流水账查询", pageSize, pageNumber, pageSorting);
        factory.addParameter("sys_enterpriseid", sys_enterpriseid);
        factory.addParameter("sa_accountclassid", sa_accountclassid);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL());


        /**
         * SQL资金流水账汇总信息查询参数设置并查询
         */
        SQLFactory factoryhz = new SQLFactory(this, "资金流水账查询汇总");
        factoryhz.addParameter("sys_enterpriseid", sys_enterpriseid);
        factoryhz.addParameter("sa_accountclassid", sa_accountclassid);
        factoryhz.addParameter("siteid", siteid);
        factoryhz.addParameter_SQL("where", where);
        Rows rowszh = dbConnect.runSqlQuery(factoryhz.getSQL());
        if(!rows.isEmpty()){
            rows.get(0).put("total",rowszh);
        }

        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "核销单关联查询", apiversion = R.ID20230224185904.v1.class)
    @CACHEING
    public String querywriteoffamount() throws YosException {
        Long sa_cashbillid = content.getLong("sa_cashbillid");
        SQLFactory sqlFactory = new SQLFactory(this, "核销单关联查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("sa_cashbillid", sa_cashbillid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "收入凭证对冲生成", apiversion = R.ID20230306102904.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221009102903.v1.class, R.ID20221010102903.v1.class, R.ID20221009103003.v1.class, R.ID20230111103403.v1.class})
    public String cashbilloffsetting() throws YosException {
        Long sa_cashbillid = content.getLong("sa_cashbillid");
        SQLFactory sqlFactory = new SQLFactory(this, "收支凭证详情查询");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_cashbillid", sa_cashbillid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("无效收入凭证").toString();
        }
        if (rows.get(0).getLong("type") == 0) {
            return getErrReturnObject().setErrMsg("支出凭证不能生成对冲").toString();
        }
        if (!rows.get(0).getString("status").equals("审核")) {
            return getErrReturnObject().setErrMsg("非审核状态不能生成对冲").toString();
        }
        if (rows.get(0).getBigDecimal("unwriteoffamount").compareTo(new BigDecimal(0)) < 1) {
            return getErrReturnObject().setErrMsg("未核销金额必须大于0").toString();
        }
        if (dbConnect.runSqlQuery("select 1 from sa_cashbill where siteid='" + siteid + "' and ownertable='sa_cashbill' and sa_cashbillid=" + sa_cashbillid).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("该凭证已生成对冲凭证").toString();
        }
        if (dbConnect.runSqlQuery("select 1 from sa_cashbill where siteid='" + siteid + "' and ownertable='sa_cashbill' and ownerid=" + sa_cashbillid).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("该凭证已生成对冲凭证").toString();
        }
        Long offsettingCashbillid = createTableID("sa_cashbill");
        sqlFactory = new SQLFactory(this, "收支凭证新增");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_cashbillid", offsettingCashbillid);
        sqlFactory.addParameter("billno", createBillCode("cashbill"));
        sqlFactory.addParameter("sys_enterpriseid", rows.get(0).getLong("sys_enterpriseid"));
        sqlFactory.addParameter("sa_accountclassid", rows.get(0).getLong("sa_accountclassid"));
        sqlFactory.addParameter("type", rows.get(0).getLong("type"));
        sqlFactory.addParameter("remarks", "凭证对冲");
        sqlFactory.addParameter("amount", rows.get(0).getBigDecimal("unwriteoffamount").negate());
        sqlFactory.addParameter("source", rows.get(0).getString("amount"));
        sqlFactory.addParameter("sourcenote", rows.get(0).getString("sourcenote"));
        sqlFactory.addParameter("subclass", rows.get(0).getString("subclass"));
        sqlFactory.addParameter("class", rows.get(0).getString("class"));
        sqlFactory.addParameter("ownertable", "sa_cashbill");
        sqlFactory.addParameter("ownerid", sa_cashbillid);
        dbConnect.runSqlUpdate(sqlFactory.getSQL());
        content.put("sa_cashbillid", offsettingCashbillid);
        return queryCashbillMain();
    }

    public Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 判断cell类型
            CellType cellType = cell.getCellType();
            switch (cellType) {
                case NUMERIC: {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        short num = cell.getCellStyle().getDataFormat();
                        String format = "YYYY-mm-dd";
                        SimpleDateFormat df = new SimpleDateFormat(format);
                        cellValue = df.format(cell.getDateCellValue());
                    } else {
                        cell.setCellType(CellType.STRING); // 将数值型cell设置为string型
                        cellValue = cell.getStringCellValue();
                    }
                    break;
                }
                case FORMULA: {
                    // 判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        // 数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    public XSSFSheet addSheetIncome(ExcelFactory excelFactory, String sheetname, Rows datarows,
                              HashMap<String, String> titlemap) {
        ArrayList<String> keylist = datarows.getFieldList();
        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet(sheetname);
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

        XSSFCellStyle xssfCellStyle1 = xssfFWorkbook.createCellStyle();
        XSSFFont font = xssfFWorkbook.createFont();

        font.setColor((short) 0xa);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        xssfCellStyle1.setFont(font);

        IncomeExportExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = IncomeExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = IncomeExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = IncomeExportExcel.createTitleCellStyle3(xssfFWorkbook);
        IncomeExportExcel.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook);// 写入标题

        for (int n = 0; n < datarows.size(); n++) {
            Row row = datarows.get(n);
            XSSFRow datarow = sheet.createRow(n + 3);
            for (int i1 = 0; i1 < keylist.size(); i1++) {
                Class fieldclazztype = datarows.getFieldMeta(keylist.get(i1)).getFieldtype();
                if (fieldclazztype == Integer.class) {
                    datarow.createCell(i1).setCellValue(row.getInteger((String) keylist.get(i1)).intValue());
                } else if (fieldclazztype == Long.class) {
                    datarow.createCell(i1).setCellValue(row.getLong((String) keylist.get(i1)));
                } else if (fieldclazztype == Float.class) {
                    datarow.createCell(i1).setCellValue(row.getFloat((String) keylist.get(i1)));
                } else if (fieldclazztype == Double.class) {
                    datarow.createCell(i1).setCellValue(row.getDouble((String) keylist.get(i1)));
                } else {
                    datarow.createCell(i1).setCellValue(row.getString((String) keylist.get(i1)));
                }
                if (i1 == 6) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }

    public XSSFSheet addSheetPay(ExcelFactory excelFactory, String sheetname, Rows datarows,
                                    HashMap<String, String> titlemap) {
        ArrayList<String> keylist = datarows.getFieldList();
        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet(sheetname);
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

        XSSFCellStyle xssfCellStyle1 = xssfFWorkbook.createCellStyle();
        XSSFFont font = xssfFWorkbook.createFont();

        font.setColor((short) 0xa);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        xssfCellStyle1.setFont(font);

        PayExportExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = PayExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = PayExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = PayExportExcel.createTitleCellStyle3(xssfFWorkbook);
        PayExportExcel.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook);// 写入标题

        for (int n = 0; n < datarows.size(); n++) {
            Row row = datarows.get(n);
            XSSFRow datarow = sheet.createRow(n + 3);
            for (int i1 = 0; i1 < keylist.size(); i1++) {
                Class fieldclazztype = datarows.getFieldMeta(keylist.get(i1)).getFieldtype();
                if (fieldclazztype == Integer.class) {
                    datarow.createCell(i1).setCellValue(row.getInteger((String) keylist.get(i1)).intValue());
                } else if (fieldclazztype == Long.class) {
                    datarow.createCell(i1).setCellValue(row.getLong((String) keylist.get(i1)));
                } else if (fieldclazztype == Float.class) {
                    datarow.createCell(i1).setCellValue(row.getFloat((String) keylist.get(i1)));
                } else if (fieldclazztype == Double.class) {
                    datarow.createCell(i1).setCellValue(row.getDouble((String) keylist.get(i1)));
                } else {
                    datarow.createCell(i1).setCellValue(row.getString((String) keylist.get(i1)));
                }
                if (i1 == 6) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }
}