单据列显示优化

王佳豪

2021-06-08 24654f1681b8f00a75a867730449858affc3f59e

 src/main/java/com/zy/common/service/erp/ErpService.java

@@ -1,7 +1,10 @@
package com.zy.common.service.erp;

import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.core.common.Cools;
import com.core.common.DateUtils;
import com.zy.asrs.service.OutStockMainService;
import com.zy.asrs.service.OutStockService;
import com.zy.common.service.erp.dto.InStockDto;
import com.zy.common.service.erp.dto.PInStockDto;
import com.zy.common.service.erp.entity.*;
@@ -9,6 +12,7 @@
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
@@ -150,11 +154,11 @@
            CPICMO cpicmo = select.get(0);
            boolean complete = false;
            if (cpicmo.getFQty() > 0) {
                if (cpicmo.getFAuxCommitQty() >= cpicmo.getFQty()) {
                if (Double.doubleToLongBits(cpicmo.getFAuxCommitQty()) == Double.doubleToLongBits(cpicmo.getFQty())) {
                    complete = true;
                }
            } else {
                if (cpicmo.getFAuxCommitQty() >= cpicmo.getFAuxQty()) {
                if (Double.doubleToLongBits(cpicmo.getFAuxCommitQty()) == Double.doubleToLongBits(cpicmo.getFAuxQty())) {
                    complete = true;
                }
            }
@@ -281,160 +285,51 @@
        return erpSqlServer.update(sql) > 0;
    }

    public boolean syncOutStock() {
        return erpSqlServer.update("MERGE INTO xtyasrs.dbo.OutStockBill AS a \n" +
                "USING (SELECT * FROM  xtyasrs_dual.dbo.OutStockBill c)\n" +
                "AS b\n" +
                " ON a.FInterID = b.FInterID\n" +
                "WHEN MATCHED THEN\n" +
                "UPDATE SET \n" +
                "FBrNo = b.FBrNo,\n" +
                "FInterID = b.FInterID,\n" +
                "FBillNo = b.FBillNo,\n" +
                "FTranType = b.FTranType,\n" +
                "FSalType = b.FSalType,\n" +
                "FCustID = b.FCustID,\n" +
                "FDate = b.FDate,\n" +
                "FStockID = b.FStockID,\n" +
                "FAdd = b.FAdd,\n" +
                "FNote = b.FNote,\n" +
                "FEmpID = b.FEmpID,\n" +
                "FCheckerID = b.FCheckerID,\n" +
                "FBillerID = b.FBillerID,\n" +
                "FManagerID = b.FManagerID,\n" +
                "FClosed = b.FClosed,\n" +
                "FInvoiceClosed = b.FInvoiceClosed,\n" +
                "FBClosed = b.FBClosed,\n" +
                "FDeptID = b.FDeptID,\n" +
                "FSettleID = b.FSettleID,\n" +
                "FTranStatus = b.FTranStatus,\n" +
                "FExchangeRate = b.FExchangeRate,\n" +
                "FCurrencyID = b.FCurrencyID,\n" +
                "FStatus = b.FStatus,\n" +
                "FCancellation = b.FCancellation,\n" +
                "FMultiCheckLevel1 = b.FMultiCheckLevel1,\n" +
                "FMultiCheckLevel2 = b.FMultiCheckLevel2,\n" +
                "FMultiCheckLevel3 = b.FMultiCheckLevel3,\n" +
                "FMultiCheckLevel4 = b.FMultiCheckLevel4,\n" +
                "FMultiCheckLevel5 = b.FMultiCheckLevel5,\n" +
                "FMultiCheckLevel6 = b.FMultiCheckLevel6,\n" +
                "FMultiCheckDate1 = b.FMultiCheckDate1,\n" +
                "FMultiCheckDate2 = b.FMultiCheckDate2,\n" +
                "FMultiCheckDate3 = b.FMultiCheckDate3,\n" +
                "FMultiCheckDate4 = b.FMultiCheckDate4,\n" +
                "FMultiCheckDate5 = b.FMultiCheckDate5,\n" +
                "FMultiCheckDate6 = b.FMultiCheckDate6,\n" +
                "FCurCheckLevel = b.FCurCheckLevel,\n" +
                "FRelateBrID = b.FRelateBrID,\n" +
                "FCheckDate = b.FCheckDate,\n" +
                "FExplanation = b.FExplanation,\n" +
                "FFetchAdd = b.FFetchAdd,\n" +
                "FSelTranType = b.FSelTranType,\n" +
                "FChildren = b.FChildren,\n" +
                "FBrID = b.FBrID,\n" +
                "FAreaPS = b.FAreaPS,\n" +
                "FPOOrdBillNo = b.FPOOrdBillNo,\n" +
                "FManageType = b.FManageType,\n" +
                "FPrintCount = b.FPrintCount,\n" +
                "Fflag_rw = b.Fflag_rw,\n" +
                "Fflag_finish = b.Fflag_finish,\n" +
                "FWeiOrder = b.FWeiOrder,\n" +
                "FWeiOpenID = b.FWeiOpenID,\n" +
                "FOrderBillNo = b.FOrderBillNo,\n" +
                "FWLNumber = b.FWLNumber,\n" +
                "FWLCompany = b.FWLCompany,\n" +
                "FReturnFundType = b.FReturnFundType\n" +
                "WHEN NOT MATCHED  THEN\n" +
                "\tINSERT (FBrNo, FInterID, FBillNo, FTranType, FSalType, FCustID, FDate, FStockID, FAdd, FNote, FEmpID, FCheckerID, FBillerID, FManagerID, FClosed, FInvoiceClosed, FBClosed, FDeptID, FSettleID, FTranStatus, FExchangeRate, FCurrencyID, FStatus, FCancellation, FMultiCheckLevel1, FMultiCheckLevel2, FMultiCheckLevel3, FMultiCheckLevel4, FMultiCheckLevel5, FMultiCheckLevel6, FMultiCheckDate1, FMultiCheckDate2, FMultiCheckDate3, FMultiCheckDate4, FMultiCheckDate5, FMultiCheckDate6, FCurCheckLevel, FRelateBrID, FCheckDate, FExplanation, FFetchAdd, FSelTranType, FChildren, FBrID, FAreaPS, FPOOrdBillNo, FManageType, FPrintCount, Fflag_rw, Fflag_finish, FWeiOrder, FWeiOpenID, FOrderBillNo, FWLNumber, FWLCompany, FReturnFundType) \n" +
                "\tVALUES (b.FBrNo, b.FInterID, b.FBillNo, FTranType, FSalType, FCustID, FDate, FStockID, FAdd, FNote, FEmpID, FCheckerID, FBillerID, FManagerID, FClosed, FInvoiceClosed, FBClosed, FDeptID, FSettleID, FTranStatus, FExchangeRate, FCurrencyID, FStatus, FCancellation, FMultiCheckLevel1, FMultiCheckLevel2, FMultiCheckLevel3, FMultiCheckLevel4, FMultiCheckLevel5, FMultiCheckLevel6, FMultiCheckDate1, FMultiCheckDate2, FMultiCheckDate3, FMultiCheckDate4, FMultiCheckDate5, FMultiCheckDate6, FCurCheckLevel, FRelateBrID, FCheckDate, FExplanation, FFetchAdd, FSelTranType, FChildren, FBrID, FAreaPS, FPOOrdBillNo, FManageType, FPrintCount, Fflag_rw, Fflag_finish, FWeiOrder, FWeiOpenID, FOrderBillNo, FWLNumber, FWLCompany, FReturnFundType);")> 0;
    /* 查询erp中年表OutStockBill数据 */
    public List<OutStockBill> syncOutStock() {
        return erpSqlServer.select("SELECT * FROM  xtyasrs_dual.dbo.OutStockBill", OutStockBill.class);
    }

    public boolean syncOutStockDetail() {
        return erpSqlServer.update("MERGE INTO xtyasrs.dbo.OutStockBillEntry AS a \n" +
                "USING (SELECT * FROM  xtyasrs_dual.dbo.OutStockBillEntry c)\n" +
                "AS b\n" +
                " ON (a.FInterID = b.FInterID and a.FEntryID = b.FEntryID)\n" +
                "WHEN MATCHED THEN\n" +
                "UPDATE SET\n" +
                "FBrNo = b.FBrNo,\n" +
                "FInterID = b.FInterID,\n" +
                "FEntryID = b.FEntryID,\n" +
                "FDetailID = b.FDetailID,\n" +
                "FItemID = b.FItemID,\n" +
                "FQty = b.FQty,\n" +
                "FCommitQty = b.FCommitQty,\n" +
                "FPrice = b.FPrice,\n" +
                "FAmount = b.FAmount,\n" +
                "FOrderInterID = b.FOrderInterID,\n" +
                "FDate = b.FDate,\n" +
                "FNote = b.FNote,\n" +
                "FInvoiceQty = b.FInvoiceQty,\n" +
                "FBCommitQty = b.FBCommitQty,\n" +
                "FUnitID = b.FUnitID,\n" +
                "FAuxBCommitQty = b.FAuxBCommitQty,\n" +
                "FAuxCommitQty = b.FAuxCommitQty,\n" +
                "FAuxInvoiceQty = b.FAuxInvoiceQty,\n" +
                "FAuxPrice = b.FAuxPrice,\n" +
                "FAuxQty = b.FAuxQty,\n" +
                "FSourceEntryID = b.FSourceEntryID,\n" +
                "FMapNumber = b.FMapNumber,\n" +
                "FMapName = b.FMapName,\n" +
                "FAuxPropID = b.FAuxPropID,\n" +
                "FBatchNo = b.FBatchNo,\n" +
                "FCheckDate = b.FCheckDate,\n" +
                "FExplanation = b.FExplanation,\n" +
                "FFetchAdd = b.FFetchAdd,\n" +
                "FFetchDate = b.FFetchDate,\n" +
                "FMultiCheckDate1 = b.FMultiCheckDate1,\n" +
                "FMultiCheckDate2 = b.FMultiCheckDate2,\n" +
                "FMultiCheckDate3 = b.FMultiCheckDate3,\n" +
                "FMultiCheckDate4 = b.FMultiCheckDate4,\n" +
                "FMultiCheckDate5 = b.FMultiCheckDate5,\n" +
                "FMultiCheckDate6 = b.FMultiCheckDate6,\n" +
                "FSecCoefficient = b.FSecCoefficient,\n" +
                "FSecQty = b.FSecQty,\n" +
                "FSecCommitQty = b.FSecCommitQty,\n" +
                "FSourceTranType = b.FSourceTranType,\n" +
                "FSourceInterId = b.FSourceInterId,\n" +
                "FSourceBillNo = b.FSourceBillNo,\n" +
                "FContractInterID = b.FContractInterID,\n" +
                "FContractEntryID = b.FContractEntryID,\n" +
                "FContractBillNo = b.FContractBillNo,\n" +
                "FOrderEntryID = b.FOrderEntryID,\n" +
                "FOrderBillNo = b.FOrderBillNo,\n" +
                "FStockID = b.FStockID,\n" +
                "FBackQty = b.FBackQty,\n" +
                "FAuxBackQty = b.FAuxBackQty,\n" +
                "FSecBackQty = b.FSecBackQty,\n" +
                "FStdAmount = b.FStdAmount,\n" +
                "FPlanMode = b.FPlanMode,\n" +
                "FMTONo = b.FMTONo,\n" +
                "FStockQtyOnlyForShow = b.FStockQtyOnlyForShow,\n" +
                "FComplexQty = b.FComplexQty,\n" +
                "Fmodel = b.Fmodel,\n" +
                "Fname = b.Fname,\n" +
                "Fnumber = b.Fnumber,\n" +
                "FBarCode = b.FBarCode,\n" +
                "FBTPLCommitQty = b.FBTPLCommitQty,\n" +
                "FTPLCommitQty = b.FTPLCommitQty,\n" +
                "fsecinvoiceqty = b.fsecinvoiceqty\n" +
                "WHEN NOT MATCHED  THEN\n" +
                "\tINSERT (FBrNo, FInterID, FEntryID, FDetailID, FItemID, FQty, FCommitQty, FPrice, FAmount, FOrderInterID, FDate, FNote, FInvoiceQty, FBCommitQty, FUnitID, FAuxBCommitQty, FAuxCommitQty, FAuxInvoiceQty, FAuxPrice, FAuxQty, FSourceEntryID, FMapNumber, FMapName, FAuxPropID, FBatchNo, FCheckDate, FExplanation, FFetchAdd, FFetchDate, FMultiCheckDate1, FMultiCheckDate2, FMultiCheckDate3, FMultiCheckDate4, FMultiCheckDate5, FMultiCheckDate6, FSecCoefficient, FSecQty, FSecCommitQty, FSourceTranType, FSourceInterId, FSourceBillNo, FContractInterID, FContractEntryID, FContractBillNo, FOrderEntryID, FOrderBillNo, FStockID, FBackQty, FAuxBackQty, FSecBackQty, FStdAmount, FPlanMode, FMTONo, FStockQtyOnlyForShow, FComplexQty, Fmodel, Fname, Fnumber, FBarCode, FBTPLCommitQty, FTPLCommitQty, fsecinvoiceqty) \n" +
                "\tVALUES (b.FBrNo, b.FInterID, b.FEntryID, b.FDetailID, b.FItemID, b.FQty, b.FCommitQty, b.FPrice, b.FAmount, b.FOrderInterID, b.FDate, b.FNote, b.FInvoiceQty, b.FBCommitQty, b.FUnitID, b.FAuxBCommitQty, b.FAuxCommitQty, b.FAuxInvoiceQty, b.FAuxPrice, b.FAuxQty, b.FSourceEntryID, b.FMapNumber, b.FMapName, b.FAuxPropID, b.FBatchNo, b.FCheckDate, b.FExplanation, b.FFetchAdd, b.FFetchDate, b.FMultiCheckDate1, b.FMultiCheckDate2, b.FMultiCheckDate3, b.FMultiCheckDate4, b.FMultiCheckDate5, b.FMultiCheckDate6, b.FSecCoefficient, b.FSecQty, b.FSecCommitQty, b.FSourceTranType, b.FSourceInterId, b.FSourceBillNo, b.FContractInterID, b.FContractEntryID, b.FContractBillNo, b.FOrderEntryID, b.FOrderBillNo, b.FStockID, b.FBackQty, b.FAuxBackQty, b.FSecBackQty, b.FStdAmount, b.FPlanMode, b.FMTONo, b.FStockQtyOnlyForShow, b.FComplexQty, b.Fmodel, b.Fname, b.Fnumber, b.FBarCode, b.FBTPLCommitQty, b.FTPLCommitQty, b.fsecinvoiceqty);")> 0;
    public List<OutStockBillEntry> syncOutStockDetail() {
        return erpSqlServer.select("SELECT * FROM  xtyasrs_dual.dbo.OutStockBillEntry", OutStockBillEntry.class);
    }

    /* 成品增量出库 */
    public boolean incrementCPakOut(Integer FInterID, String Fnumber, Double increment, String FBillNo) {
        String sql = "update OutStockbillEntry set FAuxCommitQty = (FAuxCommitQty + {0,number,#}) where 1=1 and Fnumber = ''{1}'' and FInterID = {2,number,#}";
        sql = MessageFormat.format(sql, increment, Fnumber, FInterID);
        if (erpSqlServer.update(sql) > 0) {
            sql = "select * from OutStockbillEntry where 1=1 and Fnumber = ''{0}'' and FInterID = {1,number,#}";
            sql = MessageFormat.format(sql, Fnumber, FInterID);
            List<OutStockBillEntry> select = erpSqlServer.select(sql, OutStockBillEntry.class);
            OutStockBillEntry outStockBillEntry = select.get(0);
            boolean complete = false;
            if (outStockBillEntry.getFQty().compareTo(BigDecimal.ZERO) == 1) {
                if (outStockBillEntry.getFAuxCommitQty().compareTo(outStockBillEntry.getFQty()) > -1) {
                    complete = true;
                }
            } else {
                if (outStockBillEntry.getFAuxCommitQty().compareTo(outStockBillEntry.getFAuxQty()) > -1) {
                    complete = true;
                }
            }
            if (complete) {
                if (!completeCPakOut(FBillNo)) {
                    log.error("{}出库单标记完成失败", FBillNo);
                }
            }
            return true;
        } else {
            return false;
        }
    }












    /* 成品出库单标记完成 */
    private boolean completeCPakOut(String FBillNo){
        String sql = "update OutStockbill set Fflag_finish = 1 where FBillNo = ''{0}''";
        sql = MessageFormat.format(sql, FBillNo);
        return erpSqlServer.update(sql) > 0;
    }


    public boolean updateFnumber(Integer itemId, String FNumber){
@@ -449,4 +344,18 @@
        return erpSqlServer.update(sql) > 0;
    }

    public List<CPICMO> queryErpCPICMO(String fbillNo, String fsourceBillNo) {
        String sql = "select * from CPICMO";
        if (!Cools.isEmpty(fbillNo) && Cools.isEmpty(fsourceBillNo)) {
            sql = "select * from CPICMO where FBillNo = ''{0}''";
            sql = MessageFormat.format(sql, fbillNo);
        } else if (Cools.isEmpty(fbillNo) && !Cools.isEmpty(fsourceBillNo)) {
            sql = "select * from CPICMO where FSourceBillNo = ''{0}''";
            sql = MessageFormat.format(sql, fsourceBillNo);
        } else if (!Cools.isEmpty(fbillNo) && !Cools.isEmpty(fsourceBillNo)) {
            sql = "select * from CPICMO where FBillNo = ''{0}'' and FSourceBillNo = ''{1}''";
            sql = MessageFormat.format(sql, fbillNo, fsourceBillNo);
        }
        return erpSqlServer.select(sql, CPICMO.class);
    }
}