lsa_YOS/src/custom/restcontroller/sale/invoice/invoiceapply.java

package restcontroller.sale.invoice;

import beans.datacontrllog.DataContrlLog;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
import restcontroller.R;
import restcontroller.webmanage.executorService.Executor;

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

public class invoiceapply extends Controller {

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


    @API(title = "查询开票申请单列表", apiversion = R.ID20221219133703.v1.class)
    @CACHEING
    public String queryinvoiceapplyList() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t1.createdate >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.createdate <='").append(whereObject.getString("enddate")).append("' ");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "开票申请单列表查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        ArrayList<Long> ids = rows.toArrayList("sa_invoiceapplyid", new ArrayList<>());

        SQLFactory sqlFactorySum = new SQLFactory(this, "开票申请单合计价格");
        sqlFactorySum.addParameter("siteid", siteid);
        sqlFactorySum.addParameter_in("sa_invoiceapplyid", ids);
        Rows sumrows = dbConnect.runSqlQuery(sqlFactorySum);
        RowsMap rowsMap = sumrows.toRowsMap("sa_invoiceapplyid");
        for (Row row : rows) {
            Long id = row.getLong("sa_invoiceapplyid");
            if (rowsMap.get(id.toString()).isNotEmpty()) {
                row.put("suminvoiceamount",
                        rowsMap.get(id.toString()).get(0).getBigDecimal("suminvoiceamount").toPlainString());
            } else {
                row.put("suminvoiceamount", 0);
            }
        }
        return getSucReturnObject().setData(rows).toString();
    }


    @API(title = "提交", apiversion = R.ID20221219133803.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221216143003.v1.class, R.ID20221216143103.v1.class, R.ID20221219133703.v1.class})
    public String check() throws YosException {
        Long sa_invoiceapplyid = content.getLong("sa_invoiceapplyid");

        int invoicingpoint = 99; //开票节点 1为订单审核，2为订单发货


        Rows rows = dbConnect.runSqlQuery(
                "select sa_invoiceapplyid,status,billno,sys_enterpriseid from sa_invoiceapply where sa_invoiceapplyid ='"
                        + sa_invoiceapplyid + "' and  siteid='" + siteid + "'");

        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该开票申请单不存在").toString();
        } else {
            if (!rows.get(0).getString("status").equals("新建")) {
                return getErrReturnObject().setErrMsg("单号为：【" + rows.get(0).getString("billno") + "】的开票申请单为非新建状态，无法提交")
                        .toString();
            } else {
                Rows sys_enterpriseRows = dbConnect
                        .runSqlQuery("select invoicingpoint from sys_enterprise where sys_enterpriseid=" + rows.get(0).getLong("sys_enterpriseid"));
                if (!sys_enterpriseRows.isEmpty()) {
                    invoicingpoint = sys_enterpriseRows.get(0).getInteger("invoicingpoint");
                }
            }
        }

        if (dbConnect.runSqlQuery(
                "select sa_invoiceapplyid from sa_invoiceapply_order where sa_invoiceapplyid ='"
                        + sa_invoiceapplyid + "' and  siteid='" + siteid + "'").isEmpty()) {
            return getErrReturnObject().setErrMsg("订单信息为空不可提交").toString();
        }
//        if (dbConnect.runSqlQuery(
//				"select sa_invoiceapplyid from sa_invoicebill where sa_invoiceapplyid ='"
//						+ sa_invoiceapplyid + "' and  siteid='" + siteid + "'").isEmpty()) {
//        	return getErrReturnObject().setErrMsg("开票信息为空不可提交").toString();
//        }
        if ((dbConnect.runSqlQuery(
                "select sa_invoiceapplyid,invoiceamount from sa_invoiceapply_order where sa_invoiceapplyid ='"
                        + sa_invoiceapplyid + "' and  siteid='" + siteid + "' order by invoiceamount asc").get(0).getBigDecimal("invoiceamount")).compareTo(BigDecimal.ZERO) <= 0) {
            return getErrReturnObject().setErrMsg("订单行开票金额必须大于0").toString();
        }

        Rows rowsDetail = dbConnect.runSqlQuery(
                "select * from sa_invoiceapply_order where sa_invoiceapplyid ='" + sa_invoiceapplyid + "' and  siteid='" + siteid + "'");  //获取开票申请单表体

        Rows rowsOrderDetail = dbConnect.runSqlQuery("select t1.sa_orderitemsid,t4.sonum,t3.itemname from sa_invoiceapply_order t1 inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid inner join plm_item t3  on t2.itemid=t3.itemid and t2.siteid=t3.siteid inner join sa_order t4 on t4.sa_orderid=t2.sa_orderid and  t4.siteid=t2.siteid  where t1.sa_invoiceapplyid ='" + sa_invoiceapplyid + "' and  t1.siteid='" + siteid + "'");  //获取开票申请单表体对应的订单信息
        RowsMap OrderDetailRowsMap = rowsOrderDetail.toRowsMap("sa_orderitemsid"); //获取开票申请单表体对应的订单信息，订单号，商品名称的rowsmap

        Rows sa_orderitemsRows = dbConnect
                .runSqlQuery("select t1.sa_orderitemsid,t1.price,t1.invoiceamount,t1.amount from sa_orderitems t1 inner join sa_order t2 on t1.sa_orderid=t2.sa_orderid and t1.siteid=t2.siteid  where t2.sys_enterpriseid=" + rows.get(0).getLong("sys_enterpriseid"));
        RowsMap saorderitemsRowsMap = sa_orderitemsRows.toRowsMap("sa_orderitemsid"); //获取订单已开票金额，单价，订单金额的rowsmap

        Rows saorderRecviceRows = dbConnect.runSqlQuery("select t1.sa_orderid,t2.sa_orderitemsid,SUM(t2.qty) as receivedqty,\r\n" +
                "  case when SUM(t2.qty)!=t3.qty then sum(ifnull(t2.qty,0)*t3.price-ifnull(t3.returnqty,0)*t3.price) else t3.amount end as receivedamount \r\n" +
                "from sa_dispatch t1\r\n" +
                "inner join sa_dispatch_items t2 on t1.siteid=t2.siteid and t1.sa_dispatchid=t2.sa_dispatchid\r\n" +
                "inner join sa_orderitems t3 on t2.siteid=t3.siteid and t2.sa_orderitemsid=t3.sa_orderitemsid\r\n" +
                "where t1.siteid='" + siteid + "' and t1.status='关闭' \r\n" +
                "group by t1.siteid,t1.sa_orderid,t2.sa_orderitemsid,t3.returnqty,t3.qty,t3.price,t3.amount");
        RowsMap saorderRecviceRowsMap = saorderRecviceRows.toRowsMap("sa_orderitemsid"); //获取订单已收货数量、金额的rowsmap


//        Rows tinvoiceapply_totalview =dbConnect.runSqlQuery("select t1.* from (\r\n" + 
//        		"select sa_invoiceapplyid,sum(orderamount) as orderamount,sum(billamount) as billamount,sum(redbillamount) as redbillamount from \r\n" + 
//        		"						(select sa_invoiceapplyid,ifnull(SUM(invoiceamount),0) as orderamount,0 as billamount,0 as redbillamount  from sa_invoiceapply_order group by sa_invoiceapplyid\r\n" + 
//        		"						union all\r\n" + 
//        		"					  select t1.sa_invoiceapplyid,0 as orderamount,ifnull(SUM(case when t2.rb='1' then taxincludedamount else 0 end),0) as billamount,ifnull(SUM(case when t2.rb='0' then taxincludedamount else 0 end),0) as redbillamount from sa_invoicebill_item t1\r\n" + 
//        		"						inner join sa_invoicebill t2 on t1.siteid=t2.siteid and t1.sa_invoicebillid=t2.sa_invoicebillid group by t1.sa_invoiceapplyid ) t  group by sa_invoiceapplyid) t1 where t1.sa_invoiceapplyid="+sa_invoiceapplyid);
//        
//        if (tinvoiceapply_totalview.get(0).getBigDecimal("orderamount").compareTo(tinvoiceapply_totalview.get(0).getBigDecimal("billamount"))!=0) {
//            return getErrReturnObject().setErrMsg("订单金额和开票金额不一致").toString();
//        }


        for (Row row : rowsDetail) {
            BigDecimal invoiceamount_order = saorderitemsRowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("invoiceamount");//订单已开票金额
            BigDecimal invoiceamount = row.getBigDecimal("invoiceamount");//本次开票金额
            BigDecimal price = saorderitemsRowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("price");//单价
            BigDecimal receivedamount = new BigDecimal(0);
            if (invoicingpoint == 1) {
                receivedamount = saorderitemsRowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("amount");//订单金额
            } else if (invoicingpoint == 2) {
                if (!saorderRecviceRowsMap.get(row.getLong("sa_orderitemsid")).isEmpty()) {
                    receivedamount = saorderRecviceRowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("receivedamount");//订单已收货金额
                }
            }
            String fsonum = OrderDetailRowsMap.get(row.getLong("sa_orderitemsid")).get(0).getString("sonum");//订单号
            String fitemname = OrderDetailRowsMap.get(row.getLong("sa_orderitemsid")).get(0).getString("itemname");//商品名称
            if (invoiceamount.compareTo(receivedamount.subtract(invoiceamount_order)) > 0) {
                return getErrReturnObject().setErrMsg("订单" + fsonum + "商品" + fitemname + "本次开票金额" + invoiceamount + "超过订单可开票金额" + receivedamount.subtract(invoiceamount_order)).toString();
            }
        }
        ArrayList<String> sqlList = new ArrayList<>();
        SQLFactory sqlFactoryupdate = new SQLFactory(this, "开票申请单提交");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_invoiceapplyid", sa_invoiceapplyid);
        sqlFactoryupdate.addParameter("submitby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        sqlList.add(DataContrlLog.createLog(this, "sa_invoiceapply", sa_invoiceapplyid, "提交", "开票申请单提交成功").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        Executor.sendEml(this, "invoiceapplysub", sa_invoiceapplyid,new ArrayList<>());
        return getSucReturnObject().toString();

    }

    @API(title = "反提交", apiversion = R.ID20221219133903.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221216143003.v1.class, R.ID20221216143103.v1.class, R.ID20221219133703.v1.class})
    public String uncheck() throws YosException {
        Long sa_invoiceapplyid = content.getLong("sa_invoiceapplyid");
        Long sa_orderid = 0L;
        Rows rows = dbConnect.runSqlQuery(
                "select sa_invoiceapplyid,status,billno from sa_invoiceapply where sa_invoiceapplyid ='"
                        + sa_invoiceapplyid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该开票申请单不存在").toString();
        } else {
            if (!rows.get(0).getString("status").equals("提交")) {
                return getErrReturnObject().setErrMsg("单号为：【" + rows.get(0).getString("billno") + "】的开票申请单为非提交状态，无法反提交")
                        .toString();
            }
        }

        ArrayList<String> sqlList = new ArrayList<>();

        SQLFactory sqlFactoryupdate = new SQLFactory(this, "开票申请单反提交");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_invoiceapplyid", sa_invoiceapplyid);
        sqlFactoryupdate.addParameter("submitby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        sqlList.add(DataContrlLog.createLog(this, "sa_invoiceapply", sa_invoiceapplyid, "反提交", "开票申请单反提交成功").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }


}