lsa_YOS/src/custom/beans/accountbalance/Accountbalance.java

package beans.accountbalance;

import beans.remind.Remind;
import com.sun.xml.internal.rngom.parse.host.Base;
import common.BaseClass;
import common.Controller;
import common.YosException;
import common.data.Rows;
import common.data.SQLFactory;
import org.apache.commons.lang.StringUtils;

import java.math.BigDecimal;
import java.util.ArrayList;

public class Accountbalance extends BaseClass {

    /**
     * @param controller
     * @param sys_enterpriseid  //企业id
     * @param sa_accountclassid //营销账户id
     * @return AccountbalanceEntity //营销账户实体（可用余额（getCanuseamount）,账户余额(getBalance),信用额度(getCreditquota)）
     * @throws YosException
     */
    public static AccountbalanceEntity getAccountbalance(Controller controller, long sys_enterpriseid, long sa_accountclassid) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(new Accountbalance(), "账户余额查询");
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
        sqlFactory.addParameter("siteid", controller.siteid);
        Rows rows = controller.dbConnect.runSqlQuery(sqlFactory.getSQL());
        AccountbalanceEntity accountbalanceEntity = new AccountbalanceEntity();
        if (!rows.isEmpty()) {
            accountbalanceEntity.setBalance(rows.get(0).getBigDecimal("balance"));
            accountbalanceEntity.setCreditquota(rows.get(0).getBigDecimal("creditquota"));
            accountbalanceEntity.setCanuseamount(rows.get(0).getBigDecimal("balance").add(rows.get(0).getBigDecimal("creditquota")));
        }
        return accountbalanceEntity;
    }

    /**
     * 判断当前账户余额是否足够
     *
     * @param controller
     * @param sys_enterpriseid
     * @param sa_accountclassid
     * @param amount
     * @return
     * @throws YosException
     */
    public static boolean judgeBalance(Controller controller, long sys_enterpriseid, long sa_accountclassid, BigDecimal amount) throws YosException {
        AccountbalanceEntity accountbalanceEntity = getAccountbalance(controller, sys_enterpriseid, sa_accountclassid);
        BigDecimal canuseamount = accountbalanceEntity.getCanuseamount();
        if (canuseamount != null) {
            if (canuseamount.compareTo(amount) == 1 || canuseamount.compareTo(amount) == 0) {
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }

    }

    /**
     * 返回不足金额
     *
     * @param controller
     * @param sys_enterpriseid
     * @param sa_accountclassid
     * @param amount
     * @return
     * @throws YosException
     */
    public static BigDecimal InsufficientBalance(Controller controller, long sys_enterpriseid, long sa_accountclassid, BigDecimal amount) throws YosException {
        AccountbalanceEntity accountbalanceEntity = getAccountbalance(controller, sys_enterpriseid, sa_accountclassid);
        BigDecimal canuseamount = accountbalanceEntity.getCanuseamount();
        if (canuseamount != null) {
            if (canuseamount.compareTo(amount) == 1 || canuseamount.compareTo(amount) == 0) {
                return BigDecimal.ZERO;
            } else {
                return amount.subtract(canuseamount);
            }
        } else {
            return amount;
        }

    }

    /**
     * 创建收入凭证(返回SQL)
     *
     * @param controller
     * @param sys_enterpriseid  企业id
     * @param sa_accountclassid 账户id
     * @param cashbillEntity    收支凭证实体
     * @param ischeck           是否审核
     * @return
     * @throws YosException
     */
    public static ArrayList<String> createCashbillIncome(Controller controller, long sys_enterpriseid, long sa_accountclassid, CashbillEntity cashbillEntity, boolean ischeck) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        long sa_cashbillid = controller.createTableID("sa_cashbill");
        String billcode = controller.createBillCode("cashbill");
        SQLFactory sqlFactory = new SQLFactory(new Accountbalance(), "收支凭证新增");
        sqlFactory.addParameter("billno", billcode);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
        sqlFactory.addParameter("type", 1);
        sqlFactory.addParameter("siteid", controller.siteid);
        sqlFactory.addParameter("remarks", cashbillEntity.getRemarks());
        sqlFactory.addParameter("amount", cashbillEntity.getAmount());
        sqlFactory.addParameter("sa_cashbillid", sa_cashbillid);
        sqlFactory.addParameter("userid", controller.userid);
        sqlFactory.addParameter("username", controller.username);
        sqlFactory.addParameter("ownertable", cashbillEntity.getOwnertable());
        sqlFactory.addParameter("ownerid", cashbillEntity.getOwnerid());
        sqlFactory.addParameter("source", cashbillEntity.getSource());
        sqlFactory.addParameter("sourcenote", cashbillEntity.getSourcenote());
        sqlFactory.addParameter("sa_cashbillid_rebate", "null");
        sqlFactory.addParameter("class", cashbillEntity.getType());
        sqlFactory.addParameter("subclass", cashbillEntity.getTypemx());
        if (ischeck) {
            BigDecimal balance = BigDecimal.ZERO;  //当前账户余额
            sqlFactory.addParameter("status", "审核");
            sqlFactory.addParameter("checkby", StringUtils.isBlank(controller.username)?"autocheck":controller.username);
            sqlFactory.addParameter_SQL("checkdate", "CURRENT_TIME");

            // 审核逻辑判断
            Rows rowsaccountbalance = controller.dbConnect.runSqlQuery(
                    "select sa_accountbalanceid,balance,creditquota from sa_accountbalance where sys_enterpriseid ='"
                            + sys_enterpriseid + "' and  sa_accountclassid='" + sa_accountclassid + "' and siteid='"
                            + controller.siteid + "'");

            if (rowsaccountbalance.isEmpty()) {
                SQLFactory accountbalanceaddSqlFactory = new SQLFactory(new Accountbalance(), "营销账户余额新增");
                accountbalanceaddSqlFactory.addParameter("sa_accountbalanceid",
                        controller.createTableID("sa_accountbalance"));
                accountbalanceaddSqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                accountbalanceaddSqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
                accountbalanceaddSqlFactory.addParameter("siteid", controller.siteid);
                accountbalanceaddSqlFactory.addParameter("balance", cashbillEntity.getAmount());
                accountbalanceaddSqlFactory.addParameter("userid", controller.userid);
                accountbalanceaddSqlFactory.addParameter("username", controller.username);
                balance = cashbillEntity.getAmount();
                sqlList.add(accountbalanceaddSqlFactory.getSQL());
            } else {
                BigDecimal newbalance = cashbillEntity.getAmount().add(rowsaccountbalance.get(0).getBigDecimal("balance"));
                sqlList.add("update sa_accountbalance set balance='" + newbalance
                        + "',changedate=CURRENT_TIME,changeby ='" + controller.username + "',changeuserid='"
                        + controller.userid + "' where sys_enterpriseid ='" + sys_enterpriseid
                        + "' and  sa_accountclassid='" + sa_accountclassid + "' and siteid='" + controller.siteid
                        + "'");
                balance = newbalance;
            }
            sqlFactory.addParameter("balance", balance);
            Rows accountclassrows =controller.dbConnect.runSqlQuery("select * from sa_accountclass where siteid='"+controller.siteid+"' and  sa_accountclassid="+sa_accountclassid);
            if(!accountclassrows.isEmpty()){
                String content = "您的"+accountclassrows.get(0).getString("accountname")+"已收入【"+cashbillEntity.getAmount()+"】元";
                new Accountbalance().sendMsg(controller, content, cashbillEntity.getOwnerid(), sys_enterpriseid);
            }

        } else {
            sqlFactory.addParameter("status", "新建");
            sqlFactory.addParameter("checkby", "null");
            sqlFactory.addParameter("checkdate", "null");
            sqlFactory.addParameter("balance", 0);
        }
        sqlList.add(sqlFactory.getSQL());

        return sqlList;
    }

    /**
     * 创建支出凭证(返回SQL)
     *
     * @param controller
     * @param sys_enterpriseid  企业id
     * @param sa_accountclassid 账户id
     * @param cashbillEntity    收支凭证实体
     * @param ischeck           是否审核
     * @return
     * @throws YosException
     */
    public static ArrayList<String> createCashbillPay(Controller controller, long sys_enterpriseid, long sa_accountclassid, CashbillEntity cashbillEntity, boolean ischeck) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        long sa_cashbillid = controller.createTableID("sa_cashbill");
        String billcode = controller.createBillCode("cashbill");
        SQLFactory sqlFactory = new SQLFactory(new Accountbalance(), "收支凭证新增");
        sqlFactory.addParameter("billno", billcode);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
        sqlFactory.addParameter("type", 0);
        sqlFactory.addParameter("siteid", controller.siteid);
        sqlFactory.addParameter("remarks", cashbillEntity.getRemarks());
        sqlFactory.addParameter("amount", cashbillEntity.getAmount());
        sqlFactory.addParameter("sa_cashbillid", sa_cashbillid);
        sqlFactory.addParameter("userid", controller.userid);
        sqlFactory.addParameter("username", controller.username);
        sqlFactory.addParameter("ownertable", cashbillEntity.getOwnertable());
        sqlFactory.addParameter("ownerid", cashbillEntity.getOwnerid());
        sqlFactory.addParameter("source", cashbillEntity.getSource());
        sqlFactory.addParameter("sourcenote", cashbillEntity.getSourcenote());
        sqlFactory.addParameter("class", cashbillEntity.getType());
        sqlFactory.addParameter("subclass", cashbillEntity.getTypemx());
        sqlFactory.addParameter("sa_cashbillid_rebate", "null");
        if (ischeck) {
            BigDecimal balance = BigDecimal.ZERO;  //当前账户余额
            sqlFactory.addParameter("status", "审核");
            sqlFactory.addParameter("checkby", controller.username);
            sqlFactory.addParameter_SQL("checkdate", "CURRENT_TIME");

            // 审核逻辑判断
            Rows rowsaccountbalance = controller.dbConnect.runSqlQuery(
                    "select sa_accountbalanceid,balance,creditquota from sa_accountbalance where sys_enterpriseid ='"
                            + sys_enterpriseid + "' and  sa_accountclassid='" + sa_accountclassid + "' and siteid='"
                            + controller.siteid + "'");
            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());
                throw new YosException(false,"该营销账户不存在，支出凭证无法审核");
            } else {
                BigDecimal canuseamount = rowsaccountbalance.get(0).getBigDecimal("creditquota").add(rowsaccountbalance.get(0).getBigDecimal("balance"));
                if (cashbillEntity.getAmount().compareTo(canuseamount) == 1) {
                    throw new YosException(false,"该营销账户可用余额不足，支出凭证无法审核");
                }
                BigDecimal newbalance = rowsaccountbalance.get(0).getBigDecimal("balance").subtract(cashbillEntity.getAmount());
                sqlList.add("update sa_accountbalance set balance='" + newbalance + "',changedate=CURRENT_TIME,changeby ='" + controller.username + "',changeuserid='" + controller.userid + "' where sys_enterpriseid ='" + sys_enterpriseid + "' and  sa_accountclassid='" + sa_accountclassid + "' and siteid='" + controller.siteid + "'");
                balance = newbalance;
            }
            sqlFactory.addParameter("balance", balance);
            Rows accountclassrows =controller.dbConnect.runSqlQuery("select * from sa_accountclass where siteid='"+controller.siteid+"' and  sa_accountclassid="+sa_accountclassid);
            if(!accountclassrows.isEmpty()){
                String content = "您的"+accountclassrows.get(0).getString("accountname")+"已支出【"+cashbillEntity.getAmount()+"】元";
                new Accountbalance().sendMsg(controller, content, cashbillEntity.getOwnerid(), sys_enterpriseid);
            }
        } else {
            sqlFactory.addParameter("status", "新建");
            sqlFactory.addParameter("checkby", "");
            sqlFactory.addParameter("checkdate", "");
            sqlFactory.addParameter("balance", 0);
        }
        sqlList.add(sqlFactory.getSQL());
        return sqlList;
    }


    public static ArrayList<String> createCashbillPay(Controller controller, long sys_enterpriseid, long sa_accountclassid, CashbillEntity cashbillEntity, boolean ischeck, Long sa_cashbillid_rebate) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        long sa_cashbillid = controller.createTableID("sa_cashbill");
        String billcode = controller.createBillCode("cashbill");
        SQLFactory sqlFactory = new SQLFactory(new Accountbalance(), "收支凭证新增");
        sqlFactory.addParameter("billno", billcode);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
        sqlFactory.addParameter("type", 0);
        sqlFactory.addParameter("siteid", controller.siteid);
        sqlFactory.addParameter("remarks", cashbillEntity.getRemarks());
        sqlFactory.addParameter("amount", cashbillEntity.getAmount());
        sqlFactory.addParameter("sa_cashbillid", sa_cashbillid);
        sqlFactory.addParameter("userid", controller.userid);
        sqlFactory.addParameter("username", controller.username);
        sqlFactory.addParameter("ownertable", cashbillEntity.getOwnertable());
        sqlFactory.addParameter("ownerid", cashbillEntity.getOwnerid());
        sqlFactory.addParameter("source", cashbillEntity.getSource());
        sqlFactory.addParameter("sourcenote", cashbillEntity.getSourcenote());
        sqlFactory.addParameter("sa_cashbillid_rebate", sa_cashbillid_rebate);
        sqlFactory.addParameter("class", cashbillEntity.getType());
        sqlFactory.addParameter("subclass", cashbillEntity.getTypemx());
        if (ischeck) {
            BigDecimal balance = BigDecimal.ZERO;  //当前账户余额
            sqlFactory.addParameter("status", "审核");
            sqlFactory.addParameter("checkby", controller.username);
            sqlFactory.addParameter_SQL("checkdate", "CURRENT_TIME");

            // 审核逻辑判断
            Rows rowsaccountbalance = controller.dbConnect.runSqlQuery(
                    "select sa_accountbalanceid,balance,creditquota from sa_accountbalance where sys_enterpriseid ='"
                            + sys_enterpriseid + "' and  sa_accountclassid='" + sa_accountclassid + "' and siteid='"
                            + controller.siteid + "'");
            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());
                throw new YosException(false,"该营销账户不存在，支出凭证无法审核");
            } else {
                BigDecimal canuseamount = rowsaccountbalance.get(0).getBigDecimal("creditquota").add(rowsaccountbalance.get(0).getBigDecimal("balance"));
                if (cashbillEntity.getAmount().compareTo(canuseamount) == 1) {
                    throw new YosException(false,"该营销账户可用余额不足，支出凭证无法审核");
                }
                BigDecimal newbalance = rowsaccountbalance.get(0).getBigDecimal("balance").subtract(cashbillEntity.getAmount());
                sqlList.add("update sa_accountbalance set balance='" + newbalance + "',changedate=CURRENT_TIME,changeby ='" + controller.username + "',changeuserid='" + controller.userid + "' where sys_enterpriseid ='" + sys_enterpriseid + "' and  sa_accountclassid='" + sa_accountclassid + "' and siteid='" + controller.siteid + "'");
                balance = newbalance;
            }
            sqlFactory.addParameter("balance", balance);
            Rows accountclassrows =controller.dbConnect.runSqlQuery("select * from sa_accountclass where siteid='"+controller.siteid+"' and  sa_accountclassid="+sa_accountclassid);
            if(!accountclassrows.isEmpty()){
                String content = "您的"+accountclassrows.get(0).getString("accountname")+"已支出【"+cashbillEntity.getAmount()+"】元";
                new Accountbalance().sendMsg(controller, content, cashbillEntity.getOwnerid(), sys_enterpriseid);
            }
        } else {
            sqlFactory.addParameter("status", "新建");
            sqlFactory.addParameter("checkby", "");
            sqlFactory.addParameter("checkdate", "");
            sqlFactory.addParameter("balance", 0);
        }
        sqlList.add(sqlFactory.getSQL());
        return sqlList;
    }

    /**
     * 创建收入凭证(直接执行SQL语句)
     *
     * @param controller
     * @param sys_enterpriseid  企业id
     * @param sa_accountclassid 账户id
     * @param cashbillEntity    凭证实体
     * @param ischeck           是否审核
     * @throws YosException
     */
    public static void createCashbillIncomeWithoutSQL(Controller controller, long sys_enterpriseid, long sa_accountclassid,
                                                      CashbillEntity cashbillEntity,
                                                      boolean ischeck) throws YosException {
        ArrayList<String> sqlList = createCashbillIncome(controller, sys_enterpriseid, sa_accountclassid, cashbillEntity, ischeck);
        controller.dbConnect.runSqlUpdate(sqlList);

    }

    /**
     * 创建支出凭证(直接执行SQL语句)
     *
     * @param controller
     * @param sys_enterpriseid  企业id
     * @param sa_accountclassid 账户id
     * @param cashbillEntity    凭证实体
     * @param ischeck           是否审核
     * @throws YosException
     */
    public static void createCashbillPayWithoutSQL(Controller controller, long sys_enterpriseid, long sa_accountclassid,
                                                   CashbillEntity cashbillEntity,
                                                   boolean ischeck) throws YosException {
        ArrayList<String> sqlList = createCashbillPay(controller, sys_enterpriseid, sa_accountclassid, cashbillEntity, ischeck);
        controller.dbConnect.runSqlUpdate(sqlList);

    }

    /**
     * 查询企业审核通过的有效期内的返利金余额
     *
     * @param controller
     * @param sys_enterpriseid
     * @return
     * @throws YosException
     */
    public static BigDecimal getRebateBalance(Controller controller, Long sys_enterpriseid) throws YosException {
        Rows rows = controller.dbConnect.runSqlQuery("SELECT sum(rebate_balance) rebate_balance from sa_cashbill WHERE class='返利金' and STATUS ='审核' and rebate_enddate>=CURRENT_TIME and sys_enterpriseid= " + sys_enterpriseid + " and siteid ='" + controller.siteid + "'");
        return rows.get(0).getBigDecimal("rebate_balance");
    }

    /**
     * 更新经销商装修返利表金额
     * @param controller
     * @param sys_enterpriseid
     * @param isrebateupdate
     * @param amount
     * @return
     * @throws YosException
     */
    public static  ArrayList<String>  rebateupdate(Controller controller, Long sys_enterpriseid,boolean isrebateupdate,BigDecimal amount) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rows = controller.dbConnect.runSqlQuery("SELECT * from sa_agentdecorationtrebate where  sys_enterpriseid= " + sys_enterpriseid + " and siteid ='" + controller.siteid + "'");
        if(!rows.isEmpty()){
            if(isrebateupdate) {
                sqlList.add("update  sa_agentdecorationtrebate set balance="+rows.get(0).getBigDecimal("balance").subtract(amount)+" where sa_agentdecorationtrebateid="+rows.get(0).getLong("sa_agentdecorationtrebateid")+ " and siteid ='" + controller.siteid + "'");
            }else {
                sqlList.add("update  sa_agentdecorationtrebate set balance="+rows.get(0).getBigDecimal("balance").add(amount)+" where sa_agentdecorationtrebateid="+rows.get(0).getLong("sa_agentdecorationtrebateid")+ " and siteid ='" + controller.siteid + "'");
            }
        }
        return sqlList;
    }



    public void sendMsg(Controller controller, String content, Long sa_orderid, Long sys_enterpriseid) throws YosException {

        ArrayList<Long> userList = getEnterpriseHrs(sys_enterpriseid, controller.siteid).toArrayList("userid", new ArrayList<>());
        Remind remind = new Remind(controller);
        remind.setTitle("凭证消息");
        remind.setContent(content);
        remind.setType("应用");
        remind.setObjectid(sa_orderid);
        remind.setObjectname("sa_order");
        remind.setTouserid(userList);
        remind.createSys_message();
    }
}