lsa_YOS/src/custom/restcontroller/webmanage/lsak3/stockbill/stockbill.java

package restcontroller.webmanage.lsak3.stockbill;

import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.datacontrllog.DataContrlLog;
import beans.parameter.Parameter;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.data.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;

import javax.swing.text.StyledEditorKit;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class stockbill extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public stockbill(JSONObject content) throws YosException {
        super(content);
    }

    /**
     * 更新YOS销售出库单
     * @return
     * @throws YosException
     */
    @API(title = "更新YOS销售出库单", apiversion = R.ID20230717144803.v1.class,accesstoken = false)
    public String updateIcstockbill() throws YosException{
        String msg = "";
        int code = 1;
        ArrayList<String> sqlList =new ArrayList<>();
        // 单据编号
        String fbillnum = content.getString("fbillnum");
        try {
            // 经销商编号
            String fagentnum = content.getString("fagentnum");
            long sys_enterpriseid=0;
            if(dbConnect.runSqlQuery("select sys_enterpriseid from sa_agents where siteid='lsa1986' and  agentnum='"+fagentnum+"'").isNotEmpty()){
                sys_enterpriseid=dbConnect.runSqlQuery("select sys_enterpriseid from sa_agents where siteid='lsa1986' and  agentnum='"+fagentnum+"'").get(0).getLong("sys_enterpriseid");
            }else{
                msg ="yos中不存在【"+fagentnum+"】经销商";
                return  getErrReturnObject().setErrMsg(msg).toString();
            }
            // 单据日期
            String fdate = content.getString("fdate");
            // 会计期间
          //  String fperiod = content.getString("fperiod");
            // 红蓝字
            String frb = content.getString("frb");
            // 表头仓库
           // String fstockno = content.getString("fstockno");
            // 单据类型明细
            //String ftypemx = content.getString("ftypemx");
            // 单据状态
            String fstatus = content.getString("fstatus");
            // 制单人
            String createby = content.getString("createby");
            // 制单日期
            String createdate = content.getString("createdate");
            // 审核人
            String checkby = content.getString("checkby");
            // 审核日期
            String checkdate = content.getString("checkdate");
            // 备注
            String fnotes = content.getString("fnotes");
            // 是否删除
            String isdelete = content.getString("isdelete");

            siteid="lsa1986";


            Rows stockbillRows = dbConnect.runSqlQuery("select * from st_stockbill where siteid='lsa1986' and status='审核' and billno='"+fbillnum+"'");
            if(stockbillRows.isEmpty()  && "0".equals(isdelete) && "审核".equals(fstatus)){
                /**
                 * DRP不存在销售出库单，且中间表状态为审核且不在删除状态时，DRP中需新增该销售出库单
                 */
                SQLFactory sqlFactory =new SQLFactory(this,"销售出库单新增");
                long st_stockbillid=createTableID("st_stockbill");
                sqlFactory.addParameter("siteid","lsa1986");
                sqlFactory.addParameter("st_stockbillid",st_stockbillid);
                sqlFactory.addParameter("sys_enterpriseid",sys_enterpriseid);
                sqlFactory.addParameter("billno",fbillnum);
                sqlFactory.addParameter("type","销售出库");
                sqlFactory.addParameter("rb",frb.equals("蓝")?1:0);
                sqlFactory.addParameter("billdate",fdate);
                sqlFactory.addParameter("createby",createby);
                sqlFactory.addParameter("createdate",getDate(createdate));
                sqlFactory.addParameter("changeby",createby);
                sqlFactory.addParameter("changedate",getDate(createdate));
                sqlFactory.addParameter("checkby",checkby);
                sqlFactory.addParameter("checkdate",getDate(checkdate));
                sqlFactory.addParameter("remarks",fnotes);
                sqlList.add(sqlFactory.getSQL());

                JSONArray detailarray = content.getJSONArray("icstockbilldetails");
                StringBuffer str = new StringBuffer();

                List<Long> sa_dispatch_itemsids=new ArrayList<>();
                sa_dispatch_itemsids.add(0l);
                for (Object object:detailarray) {
                    JSONObject jsonObject = (JSONObject)object;
                    if (str.toString().equals("")) {
                        str.append("'" +jsonObject.getString("fitemno") + "'");
                    } else {
                        str.append(",'" + jsonObject.getString("fitemno") + "'");
                    }
                    sa_dispatch_itemsids.add(jsonObject.getLong("sainvoicedetailid"));
                }
                Rows rows;
                if (!str.toString().equals("")) {
                     rows = dbConnect.runSqlQuery("select * from plm_item  where siteid='lsa1986' and itemno in "+"(" + str.toString() + ")");
                } else {
                     rows = dbConnect.runSqlQuery("select * from plm_item  where siteid='lsa1986' and itemno in('')");
                }
                RowsMap itemRowsMap =rows.toRowsMap("itemno");
                Rows distiptchRows = dbConnect.runSqlQuery("select t2.price,t1.sa_dispatch_itemsid from sa_dispatch_items t1 inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid inner join sa_dispatch t3 on t1.sa_dispatchid=t3.sa_dispatchid and t1.siteid=t3.siteid  where t3.status in('审核','复核','关闭') and  t1.siteid='lsa1986' and  t1.sa_dispatch_itemsid in "+sa_dispatch_itemsids.toString().replace("[", "(").replace("]", ")"));
                RowsMap distiptchRowsMap =distiptchRows.toRowsMap("sa_dispatch_itemsid");
                BigDecimal amountTotal=BigDecimal.ZERO;
                for (Object object:detailarray) {
                    JSONObject jsonObject = (JSONObject)object;
                    String frownum = jsonObject.getString("frownum");
                    String fitemno = jsonObject.getString("fitemno");
                     System.err.println(frownum+":"+fitemno);
                    BigDecimal fqty = new BigDecimal(jsonObject.getString("fqty"));
                    fqty=fqty.compareTo(BigDecimal.ZERO)<0?fqty.negate():fqty;
                    //String fdetailstockno = jsonObject.getString("fstockno");
                    long sainvoicedetailid = jsonObject.getLong("sainvoicedetailid");
                    //String fbatchno = jsonObject.getString("fbatchno");
                    String fdetailnotes = jsonObject.getString("fnotes");
                    SQLFactory sqlFactorydetail =new SQLFactory(this,"销售出库单明细新增");
                    sqlFactorydetail.addParameter("siteid","lsa1986");
                    sqlFactorydetail.addParameter("st_stockbill_itemsid", createTableID("st_stockbill_items"));
                    sqlFactorydetail.addParameter("st_stockbillid", st_stockbillid);
                    sqlFactorydetail.addParameter("rowno", frownum);
                    sqlFactorydetail.addParameter("sa_dispatch_itemsid", sainvoicedetailid);
                    sqlFactorydetail.addParameter("remarks", fnotes);
                    if(itemRowsMap.containsKey(fitemno) && itemRowsMap.get(fitemno).isNotEmpty()){
                        sqlFactorydetail.addParameter("itemid",  itemRowsMap.get(fitemno).get(0).getLong("itemid"));
                    }else{
                        sqlFactorydetail.addParameter("itemid", 0);
                    }
                    sqlFactorydetail.addParameter("remarks", fdetailnotes);
                    sqlFactorydetail.addParameter("qty", fqty);

                    if(distiptchRowsMap.containsKey(String.valueOf(sainvoicedetailid)) && distiptchRowsMap.get(sainvoicedetailid).isNotEmpty()){
                        sqlFactorydetail.addParameter("price", distiptchRowsMap.get(sainvoicedetailid).get(0).getBigDecimal("price"));
                        sqlFactorydetail.addParameter("amount", fqty.multiply(distiptchRowsMap.get(sainvoicedetailid).get(0).getBigDecimal("price")));
                        amountTotal=amountTotal.add(fqty.multiply(distiptchRowsMap.get(sainvoicedetailid).get(0).getBigDecimal("price")));
                        System.out.println(amountTotal);
                    }else{
                        sqlFactorydetail.addParameter("price", 0);
                        sqlFactorydetail.addParameter("amount", 0);
                    }

                    sqlList.add(sqlFactorydetail.getSQL());
                }
               //销售出库单审核逻辑待添加

                BigDecimal billamount =BigDecimal.ZERO;


                //返利比例
                String icstockbillrebateaccount = Parameter.get(siteid,"icstockbillrebateaccount"); //经销商销售出库单返利账户
                String icstockbillrebaterate1 = Parameter.get(siteid,"icstockbillrebaterate"); //销售出库单返利比例
                if(!StringUtils.isBlank(icstockbillrebateaccount) && !StringUtils.isBlank(icstockbillrebaterate1)){
                    BigDecimal icstockbillrebaterate =new BigDecimal(icstockbillrebaterate1);
                    //经销商返利
                    BigDecimal rebateamount = icstockbillrebaterate.multiply(amountTotal);
                    System.out.println(rebateamount);
                    CashbillEntity entity = new CashbillEntity();
                    entity.setAmount(rebateamount);
                    entity.setOwnerid(st_stockbillid);
                    entity.setOwnertable("st_stockbill");
                    entity.setSource("");
                    entity.setRemarks("销售出库单" + fbillnum + "返利");
                    sqlList.addAll(Accountbalance.createCashbillIncome(this,sys_enterpriseid,Long.valueOf(icstockbillrebateaccount).longValue(),entity,true));
                }
                sqlList.add("update st_stockbill set status='审核',checkby='"+checkby+"',checkdate=CURRENT_TIMESTAMP where st_stockbillid = "+st_stockbillid+" and siteid='lsa1986'");


            }else if (!stockbillRows.isEmpty() && "0".equals(isdelete) && "审核".equals(fstatus)) {
                /**
                 * DRP存在销售出库单，且中间表状态为审核且不在删除状态时，DRP中需将当前销售出库单先反审核，修改内容后再进行审核
                 */
                //销售出库单反审核逻辑待添加
                BigDecimal billamount =BigDecimal.ZERO;
                Rows stockbilldetails = dbConnect.runSqlQuery("select * from st_stockbill_items where st_stockbillid ='"
                        + stockbillRows.get(0).getLong("st_stockbillid") + "' and  siteid='" + siteid + "'");
                for (Row row :stockbilldetails) {
                    billamount=billamount.add(row.getBigDecimal("amount").abs().negate());
                }

                //返利比例
                String icstockbillrebateaccount = Parameter.get(siteid,"icstockbillrebateaccount"); //经销商销售出库单返利账户
                String icstockbillrebaterate1 = Parameter.get(siteid,"icstockbillrebaterate"); //销售出库单返利比例
                BigDecimal icstockbillrebaterate =new BigDecimal(icstockbillrebaterate1);
                //经销商返利
                BigDecimal rebateamount = icstockbillrebaterate.multiply(billamount);

                CashbillEntity entity = new CashbillEntity();
                entity.setAmount(rebateamount);
                entity.setOwnerid(stockbillRows.get(0).getLong("st_stockbillid"));
                entity.setOwnertable("st_stockbill");
                entity.setSource("");
                entity.setRemarks("销售出库单" + fbillnum + "反返利");
                sqlList.addAll(Accountbalance.createCashbillIncome(this,sys_enterpriseid,Long.valueOf(icstockbillrebateaccount).longValue(),entity,true));

                sqlList.add("update st_stockbill set status='新建',checkby='',checkdate=null where st_stockbillid = "+stockbillRows.get(0).getLong("st_stockbillid")+" and siteid='lsa1986'");
                msg = "DRP单据状态为审核，现已反审核，下次同步时进行审核操作";
                code = 0;
            }else if (!stockbillRows.isEmpty() && ("1".equals(isdelete) || "新建".equals(fstatus))) {
                /**
                 * DRP存在收入凭证，且中间表状态为新建或在删除状态时，DRP中需将当前收入销售出库单先反审核
                 */
                //销售出库单反审核逻辑待添加
                BigDecimal billamount =BigDecimal.ZERO;
                Rows stockbilldetails = dbConnect.runSqlQuery("select * from st_stockbill_items where st_stockbillid ='"
                        + stockbillRows.get(0).getLong("st_stockbillid") + "' and  siteid='" + siteid + "'");
                for (Row row :stockbilldetails) {
                    billamount=billamount.add(row.getBigDecimal("amount").abs().negate());
                }

                //返利比例
                String icstockbillrebateaccount = Parameter.get(siteid,"icstockbillrebateaccount"); //经销商销售出库单返利账户
                String icstockbillrebaterate1 = Parameter.get(siteid,"icstockbillrebaterate"); //销售出库单返利比例
                BigDecimal icstockbillrebaterate =new BigDecimal(icstockbillrebaterate1);
                //经销商返利
                BigDecimal rebateamount = icstockbillrebaterate.multiply(billamount);

                CashbillEntity entity = new CashbillEntity();
                entity.setAmount(rebateamount);
                entity.setOwnerid(stockbillRows.get(0).getLong("st_stockbillid"));
                entity.setOwnertable("st_stockbill");
                entity.setSource("");
                entity.setRemarks("销售出库单" + fbillnum + "反返利");
                sqlList.addAll(Accountbalance.createCashbillIncome(this,sys_enterpriseid,Long.valueOf(icstockbillrebateaccount).longValue(),entity,true));
                
                sqlList.add("update st_stockbill set status='新建',checkby='',checkdate=null where st_stockbillid = "+stockbillRows.get(0).getLong("st_stockbillid")+" and siteid='lsa1986'");

            }



            dbConnect.runSqlUpdate(sqlList);
            /**
             * 更新发货单数量
             */
            Rows stockbillrows =dbConnect.runSqlQuery("select st_stockbillid,status from st_stockbill where billno='"+fbillnum+"' and siteid='lsa1986'");
            ArrayList<String> sqldispatchitems =new ArrayList<>();
            if(stockbillrows.isNotEmpty()){
                Rows stockbilldetailrows =dbConnect.runSqlQuery("select t1.sa_dispatch_itemsid,sum(t1.qty) qty,t2.sa_dispatchid from st_stockbill_items t1 inner join  sa_dispatch_items t2 on t1.sa_dispatch_itemsid=t2.sa_dispatch_itemsid and t1.siteid=t2.siteid where t1.st_stockbillid = "+stockbillrows.get(0).getLong("st_stockbillid")+" and t1.siteid='lsa1986' group by t1.sa_dispatch_itemsid");
                if(stockbillrows.get(0).getString("status").equals("审核")){
                    if(stockbillrows.get(0).getString("rb").equals("1")){
                        for (Row row :stockbilldetailrows) {
                            sqldispatchitems.add("update sa_dispatch_items set outwarehousedate=CURRENT_TIME,outwarehouseqty=outwarehouseqty+"+row.getBigDecimal("qty")+" where siteid='lsa1986' and sa_dispatch_itemsid="+row.getLong("sa_dispatch_itemsid"));
                        }
                    }else{
                        for (Row row :stockbilldetailrows) {
                            sqldispatchitems.add("update sa_dispatch_items set outwarehousedate=CURRENT_TIME,outwarehouseqty=outwarehouseqty-"+row.getBigDecimal("qty")+" where siteid='lsa1986' and sa_dispatch_itemsid="+row.getLong("sa_dispatch_itemsid"));
                            sqldispatchitems.add("update sa_dispatch set closedate=null,closeby='',status='复核' where siteid='lsa1986' and sa_dispatchid="+row.getLong("sa_dispatchid"));
                        }
                    }

                }else{
                    if(stockbillrows.get(0).getString("rb").equals("1")){
                        for (Row row :stockbilldetailrows) {
                            sqldispatchitems.add("update sa_dispatch_items set outwarehousedate=CURRENT_TIME,outwarehouseqty=outwarehouseqty-"+row.getBigDecimal("qty")+" where siteid='lsa1986' and sa_dispatch_itemsid="+row.getLong("sa_dispatch_itemsid"));
                            sqldispatchitems.add("update sa_dispatch set closedate=null,closeby='',status='复核' where siteid='lsa1986' and sa_dispatchid="+row.getLong("sa_dispatchid"));
                        }
                    }else{
                        for (Row row :stockbilldetailrows) {
                            sqldispatchitems.add("update sa_dispatch_items set outwarehousedate=CURRENT_TIME,outwarehouseqty=outwarehouseqty+"+row.getBigDecimal("qty")+" where siteid='lsa1986' and sa_dispatch_itemsid="+row.getLong("sa_dispatch_itemsid"));
                        }
                    }

                }
            }
            dbConnect.runSqlUpdate(sqldispatchitems);


            //查询可关闭的发货单
            Rows dispatchrows =dbConnect.runSqlQuery( "select t.sa_dispatchid from (select t2.sa_dispatchid,sum(t2.qty) sumqty,sum(outwarehouseqty) sumoutwarehouseqty from st_stockbill_items t1 inner join  sa_dispatch_items t2 on t1.sa_dispatch_itemsid=t2.sa_dispatch_itemsid and t1.siteid=t2.siteid where t1.st_stockbillid = "+stockbillrows.get(0).getLong("st_stockbillid")+" and t1.siteid='lsa1986' group by t2.sa_dispatchid) t where t.sumqty=t.sumoutwarehouseqty");
            Rows undispatchrows =dbConnect.runSqlQuery( "select t.sa_dispatchid from (select t2.sa_dispatchid,sum(t2.qty) sumqty,sum(outwarehouseqty) sumoutwarehouseqty from st_stockbill_items t1 inner join  sa_dispatch_items t2 on t1.sa_dispatch_itemsid=t2.sa_dispatch_itemsid and t1.siteid=t2.siteid where t1.st_stockbillid = "+stockbillrows.get(0).getLong("st_stockbillid")+" and t1.siteid='lsa1986' group by t2.sa_dispatchid) t where t.sumqty!=t.sumoutwarehouseqty");
            ArrayList<String>  sqldispatch =new ArrayList<>();
            for (Row row:dispatchrows) {
                sqldispatch.add("update sa_dispatch set status='关闭',closedate=current_time,closeby='admin' where sa_dispatchid='" + row.getLong("sa_dispatchid") + "' and siteid='lsa1986'");
                SQLFactory sqlFactory = new SQLFactory(this, "数据操作日志新增");
                sqlFactory.addParameter("sys_contrllogid", createTableID("sys_contrllog"));
                sqlFactory.addParameter("ownertable", "sa_dispatch");
                sqlFactory.addParameter("ownerid", row.getLong("sa_dispatchid"));
                sqlFactory.addParameter("action", "自动关闭");
                sqlFactory.addParameter("remarks", "发货单自动关闭成功");
                sqlFactory.addParameter("actionuserid", "1");
                sqlFactory.addParameter("actionby", "admin");
                sqlFactory.addParameter("siteid", "lsa1986");
                sqldispatch.add(sqlFactory.getSQL());
            }
            for (Row row:undispatchrows) {
                sqldispatch.add("update sa_dispatch set status='复核',closedate=null,closeby='' where sa_dispatchid='" + row.getLong("sa_dispatchid") + "' and siteid='lsa1986'");
                sqldispatch.add("delete from sys_contrllog where ownertable='sa_dispatch' and action='自动关闭' and ownerid='" + row.getLong("sa_dispatchid") + "' and siteid='lsa1986'");
            }

            dbConnect.runSqlUpdate(sqldispatch);


            //查询可关闭的订单
            //订单出库总数量
            Rows orderinstockrows =dbConnect.runSqlQuery( "select t1.sa_orderid,t1.siteid,sum(ifnull(t2.outwarehouseqty,0)) sumoutwarehouseqty from sa_orderitems t1 left join sa_dispatch_items t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid where  t1.sa_orderid in(select t3.sa_orderid from st_stockbill_items t1 inner join  sa_dispatch_items t2 on t1.sa_dispatch_itemsid=t2.sa_dispatch_itemsid and t1.siteid=t2.siteid inner join sa_orderitems t3 on t2.sa_orderitemsid=t3.sa_orderitemsid and t2.siteid=t3.siteid  where t1.st_stockbillid ="+stockbillrows.get(0).getLong("st_stockbillid")+" and t1.siteid='lsa1986')   group by t1.sa_orderid,t1.siteid");
            //订单总数量
            Rows ordersumrows =dbConnect.runSqlQuery( "select t1.sa_orderid,t1.siteid,sum(qty) sumqty from sa_orderitems t1  where  t1.sa_orderid in(select t3.sa_orderid from st_stockbill_items t1 inner join  sa_dispatch_items t2 on t1.sa_dispatch_itemsid=t2.sa_dispatch_itemsid and t1.siteid=t2.siteid inner join sa_orderitems t3 on t2.sa_orderitemsid=t3.sa_orderitemsid and t2.siteid=t3.siteid  where t1.st_stockbillid ="+stockbillrows.get(0).getLong("st_stockbillid")+" and t1.siteid='lsa1986')   group by t1.sa_orderid,t1.siteid");
            RowsMap orderinstockRowsMap = orderinstockrows.toRowsMap("sa_orderid");


            ArrayList<String>  sqlorder =new ArrayList<>();
            for (Row row:ordersumrows) {
                if(orderinstockRowsMap.containsKey(row.getString("sa_orderid"))){
                    if(orderinstockRowsMap.get(row.getString("sa_orderid")).get(0).getBigDecimal("sumoutwarehouseqty").compareTo(row.getBigDecimal("sumqty"))==0){
                        sqlorder.add("update sa_order set status='关闭',closedate=current_time,closeby='admin' where sa_orderid='" + row.getLong("sa_orderid") + "' and siteid='lsa1986'");
                        SQLFactory sqlFactory = new SQLFactory(this, "数据操作日志新增");
                        sqlFactory.addParameter("sys_contrllogid", createTableID("sys_contrllog"));
                        sqlFactory.addParameter("ownertable", "sa_order");
                        sqlFactory.addParameter("ownerid", row.getLong("sa_orderid"));
                        sqlFactory.addParameter("action", "自动关闭");
                        sqlFactory.addParameter("remarks", "订单自动关闭成功");
                        sqlFactory.addParameter("actionuserid", "1");
                        sqlFactory.addParameter("actionby", "admin");
                        sqlFactory.addParameter("siteid", "lsa1986");
                        sqlorder.add(sqlFactory.getSQL());
                    }else {
                        sqlorder.add("update sa_order set status='审核',closedate=null,closeby='' where sa_orderid='" + row.getLong("sa_orderid") + "' and siteid='lsa1986'");
                        sqlorder.add("delete from sys_contrllog  where ownertable='sa_order' and action='自动关闭'  and  ownerid='" + row.getLong("sa_orderid") + "' and siteid='lsa1986'");
                    }
                }else {
                    sqlorder.add("update sa_order set status='审核',closedate=null,closeby='' where sa_orderid='" + row.getLong("sa_orderid") + "' and siteid='lsa1986'");
                    sqlorder.add("delete from sys_contrllog  where ownertable='sa_order' and action='自动关闭'  and  ownerid='" + row.getLong("sa_orderid") + "' and siteid='lsa1986'");
                }

            }
            dbConnect.runSqlUpdate(sqlorder);

            /**
             * 如单据为新建状态，则，先把单据强行删除掉
             */
            dbConnect.runSqlUpdate("delete from st_stockbill where status='新建'");

        } catch (Exception e) {
            e.printStackTrace();
            msg = e.getMessage();
            if(msg==null) {
                msg = "";
            }
            code = 0;
            dbConnect.runSqlUpdate("delete from st_stockbill_items where  st_stockbillid in(select st_stockbillid from st_stockbill where siteid='lsa1986' and billno='"+fbillnum+"')");
            dbConnect.runSqlUpdate("delete from st_stockbill where  billno='"+fbillnum+"' and siteid='lsa1986'");

        }
        JSONObject returnObject =new JSONObject();
        returnObject.put("msg", msg);
        returnObject.put("code", code);
        return returnObject.toString();
    }

    public String getDate(String date) {
        try {
            Date a = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(date);
            return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(a);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}