RawChen. | 大二实习软件二次开发与测试

大二实习软件二次开发与测试转载

2020-07-03 11:30 2804

#ERP业务

#SaaS

SaaS（Software as a Service），意思是“软件即服务”。在业内，SaaS被称为软件运营或简称软营。 SaaS是一种基于互联网提供软件服务的应用模式。在21世纪开始兴起的SaaS概念打破传统软件概念，SaaS模式随着互联网技术的发展和应用软件的成熟不断完善，是软件科技发展的最新趋势。

使用传统的软件工具，除了购买软件的成本，企业还需要支付构建和维护自己独立的IT硬件设备的费用。SaaS模式的出现为企业提供了另外一种解决方案。借助SaaS平台，企业只需通过网络注册使用帐号并在自己设备上进行一些简单的设置，即可以启用SaaS平台上的软件服务以及通过互联网使用共享的基础设备。

#ERP管理系统

ERP（Enterprise Resource Planning）企业资源计划管理系统是现代企业管理的运行模式。它是一个在全公司范围内应用的、高度集成的系统，覆盖了客户、项目、库存和采购、供应、生产等管理工作，通过优化企业资源达到资源效益最大化。

#BOM

BOM（Bill of Material）是指产品所需零部件明细表及其结构。具体而言，物料清单是构成父项装配件的所有子装配件、零件和原材料的清单，也是制造一个装配件所需要每种零部件的数量的清单。

#报工/委外/报关

* 工单报工也称为工单确认，作用是对工单及其进度进行确认，为实际成本核算数据，自动收料等。
* 委外加工，指本单位因为技术原因或者其他原因，不能在本单位加工的产品委托外单位加工，委外加工又分为委外材料加工和工序加工，外单位加工好了，收回产品的过程，而委托方付加工费。
* 报关是指进出口货物收发货人、进出境运输工具负责人、进出境物品所有人或者他们的代理人向海关办理货物、物品或运输工具进出境手续及相关海关事务的过程，包括向海关申报、交验单据证件，并接受海关的监管和检查等。

#平行测试

平行测试(Parallel Testing)即通过输入相同的数据到新、旧两个系统中同时运行。

#我的二开笔记
```Groovy
实体查询：根据ID查询入库单
	查询实体：入库单
	查询类型：实体查询
	参数类型：键值对
	查询条件：ID 等于 #{id}
-------------------------------------------------------------------

包装领料单页面->表格配置->直接新增按钮->操作->新增表单数据
	表单定义：包装领料单
	url参数：
		approvalStatus=wxsp&approvalStatusDisplay=无需审批
	单选框默认配置：
		变量取值逻辑#{approvalStatus}
-------------------------------------------------------------------
流程监听器：回写直接出库审批状态
	回写出库单审批状态
-------------------------------------------------------------------
回写包装领料审批状态操作定义
	键值对 修改实体数据
	目标实体：包装领料单
	操作取值：属性 取值逻辑
		审批意见 #{approvalStatus}
		审批状态显示值 #{approvalStatusDisplay}
		审批意见 #{approvalOpinions}
	ID取取值表达式：
		#{id}
-------------------------------------------------------------------
触发拦截器
	实体数据增加前，删除前(后)，修改前(后)
-------------------------------------------------------------------
内嵌表格质检明细查询
	查询取值查询定义：质检明细查询
	查询取值逻辑：$[质检单:list]
	查询取值条件表达式：
	{ relatedCustomers : $[关联客户:t1591062869758:form] }
	“质检明细查询”返回值处理脚本：return [list:$returnValue]
	返回值属性：属性 list    属性名称 质检单
	内嵌表格回填：
		真实值
		显示值
		显示值来源
-------------------------------------------------------------------
debug(com.alibaba.fastjson.JSON.toJSONString(ruku.details));
-------------------------------------------------------------------
三个参数
	productId
	productUnit
	warehouse
-------------------------------------------------------------------
质检明细单	qualityInspectionDetails  quality_inspection_details
采购到货明细ID		purchaseArrivalDetailId purchase_arrival_detail_id
		采购到货登记明细表	cgdhdjmxb				purchase_arrival_det
		累计已入库数量		accuAmount4Warehousing	accu_amount4_warehousing
-------------------------------------------------------------------

入库明细表 			WarehousingDet			dat_warehousing_det
		|	到货明细ID		purchaseArrivalDetId		purchase_arrival_det_id
		|
		采购到货登记明细表	PurchaseArrivalDet		dat_purchase_arrival_det
			累计已入库数量	accuAmount4Warehousing 	accu_amount4_warehousing

-------------------------------------------------------------------
729335c1-4056-4177-94ef-2813a1896722

update
    dat_purchase_arrival_det
set
    accu_amount4_warehousing = ifnull(invoice_quantity, 0) + #{quantity}
where
    id = #{id}

{id:$[询价单ID:t1586333658961:form]}
-------------------------------------------------------------------

预检结果
	pre_inspection_results
	pre_inspection_results_display

采购到货二开：
预检结果 == 通过  按原先逻辑走
预检结果 == 未通过
则只做保存使用，不回写到货数量到采购订单，不生成自动质检单等其它功能
-------------------------------------------------------------------

拦截 采购到货时
	序号 数字 serial_number
	质检明细单 QualityInspectionDetails

已质检待包装	InspectedBePacked
	序号 serialNumber
-------------------------------------------------------------------
拦截器-采购到货时
查询-质检明细查询2
页面-包装入库单-页面二开
操作-新增入库单
页面-开票通知单待开票箱装信息
-------------------------------------------------------------------
发货单本次装箱明细查询
开票通知单
发货单
-------------------------------------------------------------------
bp.invoice_product_name,
bp.sales_order_id,
bp.serial_number,

account_number
account_bank
currency
currency_display
-------------------------------------------------------------------

$[付款单.收款方账号:t1584502428136.accountNumber:entity]
$[付款单.收款方账号:t1584502428136.accountNumber:entity]

委外送料单outsourcing_Materia-生产数量pro_quantity
	
	委外订单subcontracting_order-已收货数量completed_quantity

subcontracting_order id = outsourcing_Materia subcontracting_order

-------------------------------------------------------------------
UPDATE 
dat_subcontracting_order 
SET 
completed_quantity = #{quantity} 
WHERE 
id = #{id}

subcontractingCode

$[货品明细:list]

{
supplierName : $[供应商名称:t1592912316983:form]
brand : $[品牌:t1592912218474:form]
categoryOfGoods : $[货品类别:t1592912269605:form]
}
------------------------------------------------------------------

select name 
from 
    dat_product 
where 
    brand = 'wx' 
AND 
    `type` = 'wt' 
AND 
    supplierNameDisplay = '代代供应'
-------------------------------------------------------------------

select 
name 
from 
dat_product 
where 
brand = #{brand} 
AND 
type = #{categoryOfGoods}
AND 
supplier = #{supplierName}
-------------------------------------------------------------------

拦截器-新增返利计算单后
	采购到货登记明细表purchase_arrival_det
	这个回写到到货单明细、退货单明细是不是实体叫采购到货登记明细表、供应商返利入库/退货单明细
查询定义-查询是否存在返利

查询该供应商的最新的结算价
-- 通过采购退货单头表的供应商查询退货明细中的货品最新的结算价
-------------------------------------------------------------------
{
supplierId:$[供应商:t1583498694263:form],
startDate:$[超始日期:t1586569152809:form],
endDate:$[截止日期:t1585030924377:form]
}

$[待结算采购明细:details]
-------------------------------------------------------------------
表单-包装明细-装箱标识
实体-主拼箱
	mainLcl
	mainLclDisplay
-------------------------------------------------------------------
return [price : $returnValue[0].currentSettlementAmount]

-------------------------------------------------------------------
//新增入库单时回写赠品入库数量
for(details in $formValue['details']){
    def param = [:]
    param['isGift'] = 
    if(details.giftIdentification == 'true'){
        //回写到返利计算单的赠品明细rebate_cal_gift_details中“已入库数量”stockInQuantity
        param['quantity'] = details.inQty
        param['compItemNumber'] = details.productCode
        executeQuery([],param)
        //根据返利计算单中赠品明细id去入库
        //赠品货品编号	compItemNumber  compItemNumberDisplay
        //入库明细单warehousing_det
        返利计算单ID	rebateCalSheetId
        
        
        //赠品的待入库单入库时，将入库数量回写到返利计算单的赠品明细中“已入库数量”
    |    update
    |    dat_rebate_cal_gift_details 
    |    set
    |    stockInQuantity = ifnull(stockInQuantity, 0) + #{quantity}
    |    where
    |    comp_item_number = #{compItemNumber}
        
    }
}
-------------------------------------------------------------------

cc  c
pur_qu_by_pack

SELECT
    t2.am_acq_field_display,
 t2.subject,
 t2.subject_display,
 t2.subject_name,
 t2.sup_account_display,
 t2.acc_bal_dir,
 t2.acc_bal_dir_display
FROM
 dat_debit_credit_set t1
LEFT JOIN dat_coll_pay_debit_details t2 ON t1.id = t2.loan_type_id
WHERE
 t1.loan_business = '预付款及铺底'

-------------------------------------------------------------------
 select
    t1.name goods_display, 
    t1.search_code, 
    t1.code item_code, 
    t1.packing, 
    t1.type_display category_of_goods, 
    t1.brand_display brand, 
    t1.unit_display company 
from
    dat_product t1 left join dat_supplier_of_goods t2 
on
		t1.supplier = t2.supplier
WHERE
    t1.brand = #{brand} 
AND
    t1.type = #{categoryOfGoods}
AND
	t2.supplier = #{supplierName}
-------------------------------------------------------------------
开票通知单
发货单

报关明细表
开票单价
billingUnitPrice

select 
    s.origin_code product_origin_code,
    s.origin_place product_origin,
    sod.supplier_id,
    sod.main_lcl,
    sod.main_lcl_display,
    sod.net_weight,
    sod.gross_weight,
    sod.invoice_product_name,
    sod.sales_order_id,
    sod.serial_number,
    sod.invoice_id,
    sod.sequence_number,
    sod.packing_no,
    sod.chinese_name,
    sod.english_name,
    sod.product_unit,
    sod.quantity_of_goods,
    sod.number_of_containers,
    sod.billingunitprice billing_unit_price,
    -- sod.invoiced_amount invoice_amount,
    sod.product_id product_id,
    sod.product_id product_name,
    p.unit,
    p.unit_display,
    p.specification,
    p.texure,
    p.english_desc,
    cd.price_contains_tax sales_unit_price,
    sod.gross_weight * sod.number_of_containers gross_weight_of_invoice,
    sod.net_weight * sod.number_of_containers invoice_net_weight,
    cd.price_contains_tax * sod.quantity_of_goods sales_sub_total,
    sod.billingunitprice * sod.quantity_of_goods invoiced_amount  
   
 -- 开票单价
 -- 开票总额
from  
    dat_shipping_order_det sod
left join dat_product p
    on p.id = sod.product_id
left join dat_contract_detail cd
    on cd.id4_contract = sod.sales_order_id
    and cd.product = sod.product_id
left join dat_supplier s 
    on s.id = sod.supplier_id
where sod.invoice_id in
<foreach  item="item" index="index" collection='ids'
 open="(" separator="," close=" )">
 #{item}
</foreach> 
order by sod.packing_no asc 
-----------------------------------------------------------

入出库借贷设置 LoanSetting
入出库借贷设置科目明细 AccountDetailsOfInOut

财务凭证 FinancialVoucher
财务凭证科目明细 FiVouAcc

invoiceDetail['productName'] = currentDetail['productDisplay21111111111111111111111in'];
开票通知单

------------------------------------------------------------
7月30日报告
对于集塑二开需求：根据借贷类型生成相应的财务凭证，根据需求做了下分析，需要根据对象选择器id查询入出库借贷设置，新增财务凭证与财务凭证科目明细，
并对主从表的各字段数据进行查找增加的操作,未完成。
巨杰出现了新的bug，报关没有从上文带出销售订单id，销售小计和销售单价。重新排查发现在包装入库时sql中未带回销售订单id，在发货单二开脚本中对于自动编排箱装号中未写回销售单价。

-------------------------------------------------------------
//回写采购单明细
def entity = $formValue

def details = [[到货明细:details:property]]
def sum1=0
def sum2=0
if (entity.directWarehou == '是'){
if(details != null && details.size() > 0){
    def warehousing= new Warehousing()
    warehousing.name4RelatedReceipt=entity.code
    warehousing.id4RelatedReceipt=entity.id
    warehousing.status='yrk'
    warehousing.statusDisplay='已入库'
    warehousing.typeDisplay='采购入库'
    warehousing.type='cgrk'
    warehousing.timeOfApplication = entity.arrivalDate
    warehousing.applyForPersonnel=[[创建人员:creatorName:property]]
    warehousing.url4RelatedReceipt="/action/bizdata/form/f2adfd25-0c60-41fd-a773-8b40cf2345d9/"+entity.id+"/view/readonly"
    warehousing.inPeople=entity.warehousingPeople
    warehousing.inPeopleDisplay=entity.warehousingPeopleDisplay
    //新增入库单
    def sum = 0
    def detailss=[]
    for(item in details){
        def detail = new WarehousingDet()
        detail.inQty =item.amount4Arrival
        detail.productId =item.productId
        detail.totalCost =item.price*item.amount4Arrival
        detail.productName =item.productName
        detail.productUnit =item.productUnit
        detail.productCode =item.productCode
        detail.shouldInQty=item.amount4Arrival
        detail.productModel=item.productModel
        detail.price4Warehousing=item.price
        sum=sum+item.amount4Arrival
        detailss.add(detail)
        if(item.amount4Arrival > 0){
            //累计到货数量总和
            sum1=item.cumulativeAmount+sum1
            //全部应到货数量
            sum2=item.amount+sum2
            def parameterValue = [id : item.purchaseReqDetId, qty : item.amount4Arrival, ruq : item.remainingUnsettledQuantity]
            executeManipulation([[更新采购明细单累计到货数量:e3b54a6c-c779-4568-ac4e-1cdf50d03a5a:manipulation]], parameterValue)
        }
    }
    warehousing.details=detailss
    warehousing.totalAmount=sum
    executeManipulation([[新增采购入库单（直接入库）:0612d595-585e-480e-b8dc-097051b65661:manipulation]],parameterValue)
    
    def map=[:]
    map["id"]=[[采购单.ID:purchaseReq.id:property]]
    if(sum2<=sum1){
        map["receivingStatusDisplay"]='全部到货'
        map["receivingStatus"]='qbdh'
    }
    if(sum2>sum1){
        map["receivingStatusDisplay"]='部分到货'
        map["receivingStatus"]='bfdh'
    }
    executeManipulation([[回写采购到到货状态:497f9c66-2840-468e-921f-6d6ed2886228:manipulation]],map)

/**
     * 生成入库单时，根据借贷类型，生成相应的财务凭证。
     * 如借贷类型是“采购入库”到“入出库借贷设置”中找“采购入库”的科目配置，生成财务凭证到“财务凭证”下。
     **/
    
    //新增入库单时
    def entity = $formValue
    
    //根据借贷类型生成财务凭证
    def financialVoucher =  new FinancialVoucher();
    
    //财务凭证科目明细
    def financialVoucherAccountDetails = new financialVoucherAccountDetails();
    
    def loanSetting = $formValue.[[入出库借贷设置:loanTypeName:property]]
    def accountDetailsOfInOut = $formValue.[[入出库借贷设置.科目明细:loanTypeName.accountDetails:property]]
    
    debug(com.alibaba.fastjson.JSON.toJSONString(loanSetting));
    debug(com.alibaba.fastjson.JSON.toJSONString(accountDetailsOfInOut));
    
    
    //来源单据编号(与财务凭证同时生成的单据，如到货单、入库单、退货单)
    financialVoucher.sourceDocNo = warehousing.id
    
    //凭证来源(根据当前使用的页面与凭证来源的字典相匹配)
    financialVoucher.sourceOfVoucher = 'cgrk'
    //凭证来源显示值
    financialVoucher.sourceOfVoucherDisplay = '采购入库'
    //记账日期
    financialVoucher.bookkeepingDate = new Date()
    //生成日期
    financialVoucher.generationDate = new Date()
    //制证人 
    financialVoucher.witnessMaking = [[[[创建人员ID:creatorId:property]]
    //制证人显示值
    financialVoucher.witnessMakingDisplay = [[创建人员:creatorName:property]]
    //凭证编号
    // financialVoucher.documentNumber
    
    
            
            
    //凭证ID
    financialVoucherAccountDetails.voucherId = financialVoucher.id
    //凭证凭证编号
    financialVoucherAccountDetails.documentNumber = financialVoucher.documentNumber
    //凭证贷方金额
    financialVoucherAccountDetails.creditAmount = entity.warehousingAmount
    //凭证科目余额方向显示值
    financialVoucherAccountDetails.accBalDireDisplay = accountDetailsOfInOut.accBalDirDisplay
    //凭证序号
    financialVoucherAccountDetails.serialNumber = entity.creditTypeNo
    //凭证辅助核算
    
    
    
    financialVoucherAccountDetails.supplementaryAccounting = entity.[[供应商显示值:supplierDisplay:property]]
    
    
    
    //凭证科目显示值
    financialVoucherAccountDetails.subjectDisplay = accountDetailsOfInOut.subjectDisplay
    //凭证借方金额
    financialVoucherAccountDetails.debitAmount = entity.warehousingAmount
    //凭证科目余额方向
    financialVoucherAccountDetails.accBalDire = accountDetailsOfInOut.accBalDir
    //凭证摘要
    financialVoucherAccountDetails.abstractab = entity.outline
    //凭证科目
    financialVoucherAccountDetails.subject = accountDetailsOfInOut.subject
    //凭证科目名称
    financialVoucherAccountDetails.subjectName = accountDetailsOfInOut.subjectName 
    
    //凭证科目明细(借贷类型配置的字段、科目、借贷)
    financialVoucher.voucherAccDetails = financialVoucherAccountDetails;
        
}

--------------------------------------------------------------------------
//同步
function sync(){
    var tableVM = context.getComponentViewModelByCode('purDetToBeInquired');
    console.log(55)
    if (tableVM) {
        var $table = $('#' + context.formContentViewId + ' #' + tableVM.tableId);
        if ($table.length) {
            var jqgridVM = ko.dataFor($table[0]);
            var selects = jqgridVM.getSelected();
            if (Array.isArray(selects)) {
                var wtDetail = tableVM.realValue();
                var selectdData = [];
                wtDetail.forEach(function(data) {
                    if (data.id && selects.indexOf(data.id) !== -1) {
                        var obj = Object.assign({}, data, {id: Math.ceil(Math.random() * Date.now())});
                        selectdData.push(obj);
                    }
                });
                context.setComponentRealValueByCode('details', []);
                context.setComponentRealValueByCode('details', selectdData);
            }
        }
    }
}

return {
    sync:sync
}
------------------------------------------------------------------------------
	loan_setting

入出库借贷设置科目明细
account_details_of_in_out
loan_type_id
----------------------------------------------------------------------------

```

#Groovy/JavaScript/SQL
##包装入库单二开脚本.js
```Js
function sync(){
     var tableVM = context.getComponentViewModelByCode('detailsBePacked');
    if (tableVM) {
        var $table = $('#' + context.formContentViewId + ' #' + tableVM.tableId);
        if ($table.length) {
            var jqgridVM = ko.dataFor($table[0]);
            var selects = jqgridVM.getSelected();
            if (Array.isArray(selects)) {
                var detailsBePacked = tableVM.realValue();
                var dataGroupByProduct = {};
                var packing4Details=[];
                debugger;
                detailsBePacked.forEach(function(data, i) {
                    if (data.id && selects.indexOf(data.id) !== -1) {
                        var obj = Object.assign({}, data, {id: Math.ceil(Math.random() * Date.now()), sequenceNo: i+1});
                        if(dataGroupByProduct[data.productId]){
                            dataGroupByProduct[data.productId]["quantityOfGoods"]+=Number(data.quantityOfGoods);
                            dataGroupByProduct[data.productId]["sourceIds"]+=","+data.qualityInspectionDetailsId;
                        }else{
                            var itemGroupByProduct={};
                            itemGroupByProduct["productId"]=data.productId;
                            itemGroupByProduct["productIdDisplay"]=data.productIdDisplay;
                            itemGroupByProduct["productNumber"]=data.productNumber;
                            itemGroupByProduct["specificationAndModel"]=data.specificationAndModel;
                            itemGroupByProduct["productUnit"]=data.productUnit;
                            itemGroupByProduct["supplier"]=data.supplier;
                            itemGroupByProduct["supplierDisplay"]=data.supplierDisplay;
                            itemGroupByProduct["quantityOfGoods"]=Number(data.quantityOfGoods);
                            itemGroupByProduct["sourceIds"]=data.qualityInspectionDetailsId;

dataGroupByProduct[data.productId]=itemGroupByProduct;
                            packing4Details.push(itemGroupByProduct);
                        }
                    }
                });
                context.setComponentRealValueByCode('packing4Details', packing4Details);
            }
        }
    }
}

return {
    sync:sync,
    beforeSave:beforeSave
}
```

##包装领料单增加或审批自动生成出库单.groovy
```Groovy
/**
 * 包装领料单，直接新增保存或审批通过后，自动生成一条出库单，该出库单状态为已完成
 * 带入参数：【出库类别为领料出库】【出库明细】
 * 出库明细参数：【产品名称】 【出库数量】【出库仓库】【出库方式】【数量】
 **/

def entity=$formValue;

//如果为待审批或无需审批则不需要自动生成出库单
if(entity.approvalStatus=='dsp'){
    
}else{ 
    def chuku = new ExWarehousing();
    chuku.outState='yck';
    chuku.outStateDisplay='已出库';
    chuku.outModel = 'llck';
    chuku.outModelDisplay = '领料出库';
    chuku.outPeople = entity.receiver;
    chuku.outPeopleDisplay = entity.receiverDisplay;
    chuku.outDate = new Date();
    chuku.id4RelatedReceipt = entity.relatedDocumentNo;
    chuku.name4RelatedReceipt = entity.relatedDocumentNoDisplay;
    
    chuku.applyForPersonnel = [[领用人:receiver:property]];
    chuku.timeOfApplication = new Date();
    
    chuku.approvalStatus = 'wxsp';
    chuku.approvalStatusDisplay = '无需审批';
    
    //备注
    chuku.deliveryNotes = entity.remarks;
    
    def list=$formValue.packingPickingDetails;
    def details= [];
    def total=0;
    if(list!=null){
        list.each{item->
            def exWarehousingDet = new ExWarehousingDet();
            
            //产品/包装物名称
            warehousingDet.productId = item.productId;
            exWarehousingDet.product=item.packageName;
            exWarehousingDet.productDisplay=item.packageNameDisplay;
            
            //产品单元
            exWarehousingDet.productUnit = item.productUnit;
            
            //出库数量
            exWarehousingDet.outQty=item.collectedQuantity;
            
            //出库仓库
            exWarehousingDet.warehouse = item.warehouse;
            exWarehousingDet.warehouseDisplay = item.warehouseDisplay;
            
            //产品编号
            exWarehousingDet.productNumber = item.productNumber;
            
            //规格型号
            exWarehousingDet.productModel = item.productModel;
            
            //库存数量
            total=total+item.collectedQuantity;
            
            exWarehousingDet.method='xjxc';
            exWarehousingDet.methodDisplay='先进先出';
            exWarehousingDet.numberOfPackages = item.inventoryQuantity;//数量
            details.add(exWarehousingDet);
            
        }
    }
    chuku.totalAmount = total;
    chuku.details=details;
    
    def id = executeManipulation([[新增出库单:2b7b93a4-8d3f-4330-b02b-78be96d1a9d4:manipulation]],chuku);
    
    def chuku2 = new ExWarehousing();

chuku2 =  executeQuery([[根据ID查询出库单:062d5167-58c6-493a-8373-026f7939d05d:query]],[id : id])[0];
    
    executeManipulation([[产品出库:72104a60-65f7-4192-91ee-2c3844719bf9:manipulation]],chuku2);
}
```

##包装入库时判断更新入库数量.groovy
```Groovy
def entity=$formValue
def details=[[包装入库明细:packing4Details:property]]
def thisQty=[[本次包装入库数量:thisQty:property]]
if(details!=null){
    details.each{item->
        def amount=item.quantityOfSingleCase*thisQty
        def packingDeduction=""
        def arr=item.sourceIds.split(",")
        for(it4Arr in arr){
            if(amount==0){
                break;
            }
            def quantityOfGoods=executeQuery([[根据质检明细ID查询未包装数量:2b94523e-4fc2-48d4-980f-f964c6926ddb:query]],[id:it4Arr])
            if(amount>=quantityOfGoods){
                packingDeduction+="{id:"+it4Arr+",amount:"+quantityOfGoods+"}"
                amount-=quantityOfGoods
                executeManipulation([[更新质检明细的包装数量:b48edb4c-dc56-4fd5-a861-e2c518c53a1c:manipulation]],[id:it4Arr,currentPackagedAmount:quantityOfGoods])
            }else{
                packingDeduction+="{id:"+it4Arr+",amount:"+amount+"}"
                executeManipulation([[更新质检明细的包装数量:b48edb4c-dc56-4fd5-a861-e2c518c53a1c:manipulation]],[id:it4Arr,currentPackagedAmount:amount])
                amount=0
            }
        }
        item.packingDeduction=packingDeduction
    }
}
```

##出库拦截.groovy
```Groovy
if($formValue.outStateDisplay=='待出库'){
    executeManipulation([[产品出库:72104a60-65f7-4192-91ee-2c3844719bf9:manipulation]], $formValue)
}
$formValue.outState = 'yck'
$formValue.outStateDisplay = '已出库'

def list=$formValue.details
def sum1 =0
if(list != null){
    list.each{item1->
        sum1=sum1+item1.outQty
    }
}

if(sum1<$formValue.totalAmount ){
    def exWarehousing=new ExWarehousing()
    def properties = $formValue.properties
    InvokerHelper.setProperties(exWarehousing, properties)
    def details= []
    if(list != null){
    list.each{item->
        if(item.shouldOutQty>item.outQty){
            def exWarehousingDet = new ExWarehousingDet()
            def pr = item.properties
            InvokerHelper.setProperties(exWarehousingDet, pr)
            exWarehousingDet.outQty=item.shouldOutQty-item.outQty
            exWarehousingDet.shouldOutQty=item.shouldOutQty-item.outQty
            exWarehousingDet.id=null
            details.add(exWarehousingDet)
        }
    }
}
exWarehousing.outStateDisplay='待出库'
exWarehousing.outState='dck'
exWarehousing.totalAmount=$formValue.totalAmount-sum1
exWarehousing.id=null
exWarehousing.details=details
executeManipulation([[新增出库单:2b7b93a4-8d3f-4330-b02b-78be96d1a9d4:manipulation]],exWarehousing)
}

$formValue.totalAmount=sum1
```

##新增包装入库时生成入库单.groovy
```Groovy
/**
 * 包装入库单保存后要自动生成一个入库单
 * 带入参数：【入库类别为包装入库】【入库明细】
 * 入库明细参数:【产品名称】【入库仓库】【入库数量】【包装方式==箱装】
 **/
 
def entity=$formValue;

def ruku = new Warehousing();
ruku.type = 'bzrk';
ruku.typeDisplay = '包装入库';
ruku.timeOfApplication = entity.packingDate;
ruku.applyForPersonnel = [[创建人员:creatorName:property]];
ruku.name4RelatedReceipt = entity.relatedDocumentNo;
ruku.status = 'yrk';
ruku.statusDisplay = '已入库';
ruku.inPeople = [[创建人员ID:creatorId:property]];
ruku.inPeopleDisplay = [[创建人员:creatorName:property]];
ruku.inDate = new Date();
ruku.totalAmount=0;
ruku.details=[];

##包装领料单审批通过后的流程监听器.groovy
```Groovy
def entity=$formValue
def map=[:]
map["approvalOpinions"]=entity.approvalOpinions
map["approvalStatusDisplay"]="审批通过"
map["approvalStatus"]="sptg"
map["id"]=entity.id
executeManipulation([[回写包装领料审批状态:07a6656c-141b-4648-8c91-fae6e989148e:manipulation]],map)

//根据form回写出库单
def chuku = new ExWarehousing();
chuku.outState='yck';
chuku.outStateDisplay='已出库';
chuku.outModel = 'llck';
chuku.outModelDisplay = '领料出库';
chuku.outPeople = entity.receiver;
chuku.outPeopleDisplay = entity.receiverDisplay;
chuku.outDate = new Date();
chuku.id4RelatedReceipt = entity.relatedDocumentNo;
chuku.name4RelatedReceipt = entity.relatedDocumentNoDisplay;

chuku.applyForPersonnel = entity.receiverDisplay;
chuku.timeOfApplication = entity.collectionDate;

chuku.approvalStatus = 'wxsp';
chuku.approvalStatusDisplay = '无需审批';

//备注
chuku.deliveryNotes = entity.remarks;

def list=$formValue.packingPickingDetails;
def details= [];
def total=0;
if(list!=null){
    list.each{item->
        def exWarehousingDet = new ExWarehousingDet();
        
        //产品/包装物名称
        exwarehousingDet.productId = item.productId;
        exWarehousingDet.product=item.packageName;
        exWarehousingDet.productDisplay=item.packageNameDisplay;
        
        //产品单元
        exWarehousingDet.productUnit = item.packagingUnitDisplay;
        
        //出库数量
        exWarehousingDet.outQty=item.collectedQuantity;
        
        //出库仓库
        exWarehousingDet.warehouse = item.warehouse;
        exWarehousingDet.warehouseDisplay = item.warehouseDisplay;
        
        //产品编号
        exWarehousingDet.productNumber = item.productNumber;
        
        //规格型号
        exWarehousingDet.productModel = item.productModel;
        
        
        //库存数量
        total=total+item.collectedQuantity;
        
        exWarehousingDet.method='xjxc';
        exWarehousingDet.methodDisplay='先进先出';
        exWarehousingDet.numberOfPackages = item.inventoryQuantity;//数量
        details.add(exWarehousingDet);
    }
}
chuku.totalAmount = total;
chuku.details=details;

def id = executeManipulation([[新增出库单:2b7b93a4-8d3f-4330-b02b-78be96d1a9d4:manipulation]],chuku);
    
def chuku2 = new ExWarehousing();

chuku2 =  executeQuery([[根据ID查询出库单:062d5167-58c6-493a-8373-026f7939d05d:query]],[id : id])[0];

executeManipulation([[产品出库:72104a60-65f7-4192-91ee-2c3844719bf9:manipulation]],chuku2);
```

##产品出库.groovy
```Groovy

def entity4Exwarehousing=$parameterValue
def details=entity4Exwarehousing.details
def outType = entity4Exwarehousing.outModelDisplay
//出库单明细回写已入库数量
def writebackDetailOutQty = { outOrderDetail -> 
    def updateParam = [:]
    updateParam['qty'] = outOrderDetail.outQty
    
    if(outType == '销售出库' || outType == '换货出库'){
        //回写销售、出货明细表
        updateParam['id'] = outOrderDetail.salesDetailId
        executeManipulation([[回写销售明细出库数量:59a3fac4-72ca-4ddb-bb1d-746ea564199b:manipulation]], updateParam)
        
        updateParam['id'] = outOrderDetail.salesDetailId.shipmentDetailsId
        executeManipulation([[回写出货明细出库数量:3dddc4d5-8c46-4b5f-9c01-4e033a1777a8:manipulation]], updateParam)
    }
    
    if(outType == '采购退货'){

}
    
    if(outType == '领料出库'){
        
    }
    
}

//出库
def outbound = { outOrderDetail, productInventory, inventoryDetails -> 
    if(inventoryDetails == null || inventoryDetails.size() <= 0){
        throw new BaseAppRuntimeException(false, "没有找到对应的库存产品明细")
    }
    
    //校验库存数量
    def outQty = outOrderDetail.outQty
    def totalQty = 0
    for(inventoryDetail in inventoryDetails){
        totalQty += inventoryDetail.total4Inventory
    }
    if(totalQty < outQty){
        throw new BaseAppRuntimeException(false, "库存数量不足")
    }
    
    def data4Reduce = ""
    for(inventoryDetail in inventoryDetails){
        if(inventoryDetail.total4Inventory >= outQty){
            executeManipulation([[减少产品库存数量:5d53f4fd-2761-467c-a413-54e253471ebe:manipulation]],[productInventoryId:productInventory.id,amount4Reduce:outQty])
            executeManipulation([[减少产品库存明细表的库存数量:6e830506-c3bb-4c07-9285-4256fcdc4964:manipulation]],[productInventoryDetId:inventoryDetail.id,amount4Reduce:outQty])
            data4Reduce += productInventory.id + ":" + inventoryDetail.id + ":" + outQty + ","
            if (data4Reduce != '') {
                def param4Change = [:]
                param4Change['id'] = outOrderDetail.id
                param4Change['data4Reduce'] = data4Reduce
                executeManipulation([[修改出库明细表的情况扣减:ce70052c-9031-4800-be50-253c76921c51:manipulation]],param4Change)
            }   
            return;
        }
        
        executeManipulation([[减少产品库存数量:5d53f4fd-2761-467c-a413-54e253471ebe:manipulation]],[productInventoryId:productInventory.id,amount4Reduce:inventoryDetail.total4Inventory])
        executeManipulation([[减少产品库存明细表的库存数量:6e830506-c3bb-4c07-9285-4256fcdc4964:manipulation]], [productInventoryDetId:inventoryDetail.id, amount4Reduce:inventoryDetail.total4Inventory])
        outQty = outQty - inventoryDetail.total4Inventory
        data4Reduce += productInventory.id + ":" + inventoryDetail.id + ":" + inventoryDetail.total4Inventory + ","
        if (data4Reduce != '') {
                debug(987654)
                def param4Change = [:]
                param4Change['id'] = outOrderDetail.id
                param4Change['data4Reduce'] = data4Reduce
                executeQuery([[修改出库明细表情况扣减:70b6cce3-da84-4139-acf1-84f7a85c4823:query]],param4Change)
            }  
    }
    
    writebackDetailOutQty(outOrderDetail)
}

//先进先出
def firstInFirstOut={productInventory, detail4Exwarehousing ->
    def list4ProductInventoryDetails=executeQuery([[根据产品库存ID按入库时间正序查询产品库存明细:df9ef889-af9f-438c-9007-e3005a1b27c2:query]],[productInventoryId:productInventory.id])
    outbound(detail4Exwarehousing, productInventory, list4ProductInventoryDetails)
/*    if (data4Reduce != '') {
       def param4Change = [:]
       param4Change['id'] = detail4Exwarehousing.id
       param4Change['data4Reduce'] = data4Reduce
       executeQuery([[修改出库明细表情况扣减:70b6cce3-da84-4139-acf1-84f7a85c4823:query]],param4Change)
   }*/
   
}

//指定批次号出库
def appointLot = {productInventory, outOrderDetail ->
    def inventoryDetails = executeQuery([[产品库存明细_出库:01ba525c-4416-41b8-84a7-22755d15d431:query]], [ids : outOrderDetail.cprkpclb.split(',')])
    outbound(outOrderDetail, productInventory, inventoryDetails)
}

if(details!=null){
    details.each{detail->
        def entity4ProductInventory=executeQuery([[根据产品ID和仓库ID获取产品库存简要信息:b9d45fe9-68f0-436b-966e-de425ffafedb:query]],[
            productId:detail.product,warehouseId:detail.warehouse,productUnit:detail.productUnit])
        if(entity4ProductInventory==null){
            debug(com.alibaba.fastjson.JSON.toJSONString(detail));
            throw new BaseAppRuntimeException(false,"没有找到对应的库存产品")
        }
        if(entity4ProductInventory.stockQty<detail.outQty){
            throw new BaseAppRuntimeException(false,"库存产品不足")
        }
        def outMethodDisplay=detail.methodDisplay
        
        if(outMethodDisplay=='先进先出'){
            firstInFirstOut(entity4ProductInventory,detail)
        }else if(outMethodDisplay=='指定批次号'){
            appointLot(entity4ProductInventory, detail)
        }
    }
}
```

##质检明细查询.sql
```SQL
select
    t1.product_id,
    t1.product_name product_id_display, -- 产品名称（号）
    t1.product_codes product_number,
    t2.specification specification_and_model, -- 规格型号
    t2.unit product_unit, -- 单位
    t1.quantity_to_be_packed quantity_of_goods, -- 待包装货品数量
    t1.des4_products remarks,
    t1.arrival_note associated_arrival_display,
    t1.quality_inspection_order_id,
    t1.quality_inspection_no,
    t1.purchase_arrival_detail_id arrival_details_id,
    t1.purchase_req_det_id,
    t1.purchase_arrival_id associated_arrival,
    t1.id quality_inspection_details_id,
    (select related_suppliers from dat_inspection_req where id = t1.quality_inspection_order_id) supplier,
    (select related_suppliers_display from dat_inspection_req where id = t1.quality_inspection_order_id) supplier_display
FROM
	dat_quality_inspection_details t1
LEFT OUTER JOIN dat_product t2 ON t2.id = t1.product_id
WHERE
	EXISTS (
		SELECT
			1
		FROM
			dat_inspection_req
		WHERE
			id = t1.quality_inspection_order_id
		AND associated_customers = #{relatedCustomers} 
		AND quality_status = 'yzj'
	)
<![CDATA[  
and
    t1.quantity_to_be_packed > 0 
]]>  
```

##根据产品ID查询产品库存表库存数量.sql
```SQL
-- 根据产品ID查询产品库存表库存数量

SELECT
	stock_qty
FROM
	dat_product_inventory
WHERE
	product_id = #{id}

-- warehousing_packing_list
-- number_of_inner_boxes
-- inner_box_packaging
-- 内盒包装物
-- id4_warehousing
-- 库存数量	inventoryQuantity     getProductWareHouseQuantity
-- $[库存数量:stockQty]
-- {id:$[产品:t1586849793996:form]}
-- 直接新增保存或审批通过
```

##完工入库明细查询.sql
```SQL
SELECT
	t1.*, t2.status_display,
	t2.in_people_display,
	t2.in_date,
	t2.type_display,
	t2.time_of_application,
	t2.apply_for_personnel,
	t2.name4_related_receipt,
	t2.id4_related_receipt,
	t2.url4_related_receipt
FROM
	dat_warehousing_det t1
LEFT OUTER JOIN dat_warehousing t2 ON t1.warehousing_id = t2.id
WHERE
	{{@t2.Warehousing@}}
AND t2.type_display = '完工入库'
AND {{ #Conditions=t1.WarehousingDet;t2.Warehousing#}}
ORDER BY
	t2.create_time DESC
```

##采购到货时新增数据.groovy
```Groovy
//回写采购单明细
def entity = $formValue
def details = [[到货明细:details:property]]
def sum1=0
def sum2=0
if(details != null && details.size() > 0){
    //新增质检申请单
    def inspectionReq= new InspectionReq()
    inspectionReq.associatedArrivalDisplay=entity.code
    inspectionReq.associatedArrival=entity.id
    inspectionReq.remarks = entity.remark
    inspectionReq.qualityStatus='dzj'
    inspectionReq.qualityStatusDisplay='待质检'
    inspectionReq.relatedSuppliersDisplay=entity.supplier.name
    inspectionReq.relatedSuppliers=entity.supplier.id
    inspectionReq.applicationDate=[[到货日期:arrivalDate:property]]
    inspectionReq.associatedPurchaseOrder=entity.purchaseReq.id
    debug(entity.purchaseReq.id)
    //新增入库单
    def sum =0
    def detailss=[]  
    for(item in details){
        
        //判断预检结果是否为通过或者未通过
        if(item.preInspectionResults == 'tg'){
            //通过
            def detail = new QualityInspectionDetails()
            //到货数量
            detail.arrivalQuantity =item.amount4Arrival
            detail.serialNumber = item.serialNumber
            detail.productId =item.productId
            detail.productName =item.productName
            detail.productCodes = item.productCode //货品编码
            detail.productUnit =item.productUnit //产品单位
            detail.productModel=item.productModel //产品规格
            detail.purchaseArrivalDetailId = entity.id
            
            detail.price=item.price
            detailss.add(detail)
            if(item.amount4Arrival > 0){
                //累计到货数量总和
                sum1=item.cumulativeAmount+sum1
                //全部应到货数量
                sum2=item.amount+sum2
                def parameterValue = [id : item.purchaseReqDetId, qty : item.amount4Arrival, ruq : item.remainingUnsettledQuantity]
                executeManipulation([[更新采购明细单累计到货数量:e3b54a6c-c779-4568-ac4e-1cdf50d03a5a:manipulation]], parameterValue)
            }
        }else{
            //未通过
        }
        
        
    }
    debug(com.alibaba.fastjson.JSON.toJSONString(detailss));
    inspectionReq.qualityDetails=detailss
    executeManipulation([[新增货品质检:a22c08b5-bb17-4f70-9523-05520a437a4d:manipulation]],inspectionReq)
}

def map=[:]
map["id"]=[[采购单.ID:purchaseReq.id:property]]
map["receivingStatusDisplay"] = sum2<=sum1 ? '全部到货' : '部分到货'
map["receivingStatus"] = sum2<=sum1 ? 'qbdh' : 'bfdh'
executeManipulation([[回写采购到到货状态:497f9c66-2840-468e-921f-6d6ed2886228:manipulation]],map)
```
##客户询价明细关联采购询价明细.sql
```SQL
SELECT
	t2.id,
	t2.code,
	t3.amount,
	t3.price_answered,
	t3.create_time,
	t2.supplier_display
FROM
	dat_purchase_inquiry_det t3
LEFT JOIN dat_purchase_inquiry t2 -- 采购询价表
ON t2.id = t3.purchase_inquiry_id
LEFT JOIN dat_customer_inquiry t1 -- 客户询价表
ON t1.id = t2.customer_inquiry
WHERE
	t1.id = #{id}
```
##新增委外收料单.groovy
```Groovy
if($formValue.deliveryStatusDisplay=='已出库'){
    executeManipulation([[委外送料出库:6962fff7-0959-4a07-b259-75e5dcbbc8bb:manipulation]],$formValue)
}

/**
 * 回填委外订单明细的送料申请数量
 **/

for(songliaoDetail in $formValue['songliaoDet']){
    def param = [:];
    param['id'] = songliaoDetail['subOrderDetailId'];
    param['qty'] = songliaoDetail['actualFeedQuantity'];
    executeManipulation([[增加委外订单明细送料申请数量:d6661f57-dc2e-4e46-87ae-b35637085bd2:manipulation]], param);
}
if([[委外类型显示值:outsourcingTypeDisplay:property]]=='工序委外'){
    
}
if($formValue.outsourcingType=="gxww"){
    //从委外送料单保存后回写生产数量为已送料数量
    def map = [:];
    map["q"] = $formValue.proQuantity;
    map["id"] = $formValue.subcontractingOrder;
    debug($formValue.id);
    
    executeManipulation([[委外送料单回写生产数量为已送料数量:c6de07d3-efdc-4868-b879-1fa480923a6d:manipulation]],map);
}
```
##新增委外收料单.groovy
```Groovy
if($formValue.deliveryStatusDisplay=='已出库'){
    executeManipulation([[委外送料出库:6962fff7-0959-4a07-b259-75e5dcbbc8bb:manipulation]],$formValue)
}

/**
 * 回填委外订单明细的送料申请数量
 **/

for(songliaoDetail in $formValue['songliaoDet']){
    def param = [:];
    param['id'] = songliaoDetail['subOrderDetailId'];
    param['qty'] = songliaoDetail['actualFeedQuantity'];
    executeManipulation([[增加委外订单明细送料申请数量:d6661f57-dc2e-4e46-87ae-b35637085bd2:manipulation]], param);
}
if([[委外类型显示值:outsourcingTypeDisplay:property]]=='工序委外'){
    
}
if($formValue.outsourcingType=="gxww"){
    //从委外送料单保存后回写生产数量为已送料数量
    def map = [:];
    map["q"] = $formValue.proQuantity;
    map["id"] = $formValue.subcontractingOrder;
    debug($formValue.id);
    
    executeManipulation([[委外送料单回写生产数量为已送料数量:c6de07d3-efdc-4868-b879-1fa480923a6d:manipulation]],map);
}
```
##新增采购退货时拦截.groovy
```Groovy
//新增采购退货时
def entity =$formValue

//退货状态 默认发起退货

////退货状态 确认退货
if(entity.status=='qrth'){
    //退货来源 无订单货品
    if(entity.returnSource=='wddhp'){
        //需要出库
        if(entity.outWare=='xyck'){
            executeManipulation([[采购退货出库:bd76ce6a-115d-4e91-a022-e618f1101bb4:manipulation]],entity)
        }
        //无需出库
        if(entity.outWare=='wxck'){
            //退款方式-换货
            if(entity.method4BackMoney=='hh'){
                executeManipulation([[采购换货入库:9726a612-c1a4-4f01-98f1-9b9fd75f4eae:manipulation]],entity)
            }
            //退款方式-转预付款
            if(entity.method4BackMoney=='zyfk'){
                executeManipulation([[采购退货回写采购预付:5ad46155-691e-45cb-8c4b-313c1f55afad:manipulation]],entity)
            }
            //退款方式-直接退货
            if(entity.method4BackMoney=='zjtk')
            executeManipulation([[采购退货生成收款单:fac58bd7-5e0f-40a4-be63-1ab68ffdf09b:manipulation]],entity)
        }
    }
    
    //退货来源 有订单货品
    if(entity.returnSource=='yddhp'){
        debug("nihao")
        def list=entity.details
        if(list !=null){
            list.each{item->
                executeManipulation([[采购退货回写退货数量到到货:98151c4c-2ab0-4743-ab38-78da199f6295:manipulation]],item)
                def rr = executeQuery([[根据货品id查询货品详情:f9b1ef9e-179c-45fe-90ba-daebcf8053aa:query]],[id:item.productId])
                item.product=rr
                debug(rr)
            }
        }
        //需要出库
        if(entity.outWare=='xyck'){
            executeManipulation([[采购退货出库:bd76ce6a-115d-4e91-a022-e618f1101bb4:manipulation]],entity)
            
        }
        //无需出库
        if(entity.outWare=='wxck'){
            ////退款方式-换货
            if(entity.method4BackMoney=='hh'){
                executeManipulation([[采购换货入库:9726a612-c1a4-4f01-98f1-9b9fd75f4eae:manipulation]],entity)
            }else{
                    executeManipulation([[采购退货非换货时:58e3c0b7-b5eb-43e2-8ca8-a30a2e4693bc:manipulation]],entity)
            }
        }
        
    }
}
```
##新增入库单时回写赠品入库数量.groovy
```Groovy
/**
 * 包装入库单保存后要自动生成一个入库单
 * 带入参数：【入库类别为包装入库】【入库明细】
 * 入库明细参数:【产品名称】【入库仓库】【入库数量】【包装方式==箱装】
 **/
 
def entity=$formValue;

def details=[[包装入库明细:packing4Details:property]]
def thisQty=[[本次包装入库数量:thisQty:property]]
if(details!=null){
    details.each{item->
        def amount=item.quantityOfSingleCase*thisQty
        def packingDeduction=""
        def arr=item.sourceIds.split(",")
        for(it4Arr in arr){
            if(amount==0){
                break;
            }
            def qualityInspectionDetail=executeQuery([[根据质检明细ID加载质检明细:2b94523e-4fc2-48d4-980f-f964c6926ddb:query]],it4Arr)
            
            def warehousingDet = new WarehousingDet();
            warehousingDet.productId = item.productId;
            warehousingDet.productCode = item.productNumber;
            warehousingDet.productName = item.productIdDisplay;
            warehousingDet.packingModel = 'xz';
            warehousingDet.packingModelDisplay = '箱装';
            warehousingDet.productUnit = item.productUnit;
            warehousingDet.productModel = item.specificationAndModel;
            warehousingDet.productBatch = entity.packageWarehousingNo;
            warehousingDet.warehouseId = entity.warehouseId;
            warehousingDet.warehouseIdDisplay = entity.warehouseIdDisplay;
            warehousingDet.purchaseDetailId=qualityInspectionDetail.purchaseReqDetId;
            warehousingDet.purchaseArrivalDetId=qualityInspectionDetail.purchaseArrivalDetailId;
        
            def quantityOfGoods=qualityInspectionDetail.quantityToBePacked
            if(amount>=quantityOfGoods){
                packingDeduction+="{id:"+it4Arr+",amount:"+quantityOfGoods+"}"
                warehousingDet.inQty = quantityOfGoods;
                ruku.totalAmount+=quantityOfGoods;
                amount-=quantityOfGoods
                executeManipulation([[更新质检明细的包装数量:b48edb4c-dc56-4fd5-a861-e2c518c53a1c:manipulation]],[id:it4Arr,currentPackagedAmount:quantityOfGoods])
            }else{
                packingDeduction+="{id:"+it4Arr+",amount:"+amount+"}"
                executeManipulation([[更新质检明细的包装数量:b48edb4c-dc56-4fd5-a861-e2c518c53a1c:manipulation]],[id:it4Arr,currentPackagedAmount:amount])
                warehousingDet.inQty = amount;
                ruku.totalAmount+=amount;
                amount=0
            }
            ruku.details.push(warehousingDet);
        }
        item.packingDeduction=packingDeduction
    }
}
 
def id = executeManipulation([[新增入库单:55050fc4-d861-476c-bed8-39e1fb54a04b:manipulation]],ruku);
def ruku2 = new Warehousing();
ruku2 = executeQuery([[根据ID查询入库单:c8195c60-5a6f-4808-8ebb-e4f436444f08:query]],[id : id])[0];
executeManipulation([[产品入库:c0e673f6-5c5c-4d14-bf8a-3eff8d9cb5ce:manipulation]],ruku2);
```
##报关明细查询返回值处理.groovy
```Groovy
def map = [:]
def details = []
for(custDecList in $returnValue){
    def ds = [:]
    if(custDecList['numberOfContainers'] == 1){
        ds['caseNumberUnit'] = 'CTN'
    }else{
        ds['caseNumberUnit'] = 'CTNS'
    }
    ds['weight'] = 'KGS'
    ds['chinaAndEnglishNames'] = custDecList['englishName']+"（"+custDecList['chineseName']+"）"
    ds['productOriginCode'] = custDecList['productOriginCode']
    ds['productOrigin'] = custDecList['productOrigin']
    ds['supplierId'] = custDecList['supplierId']
    ds['mainLcl'] = custDecList['mainLcl']
    ds['mainLclDisplay'] = custDecList['mainLclDisplay']
    ds['netWeight'] = custDecList['netWeight']
    ds['grossWeight'] = custDecList['grossWeight']
    ds['invoiceProductName'] = custDecList['invoiceProductName']
    ds['salesOrderId'] = custDecList['salesOrderId']
    ds['serialNumber'] = custDecList['serialNumber']
    ds['invoiceId'] = custDecList['invoiceId']
    ds['sequenceNumber'] = custDecList['sequenceNumber']
    ds['packingNo'] = custDecList['packingNo']
    ds['chineseName'] = custDecList['chineseName']
    ds['englishName'] = custDecList['englishName']
    ds['productUnit'] = custDecList['productUnit']
    ds['quantityOfGoods'] = custDecList['quantityOfGoods']
    ds['numberOfContainers'] = custDecList['numberOfContainers']
    ds['billingUnitPrice'] = custDecList['billingUnitPrice']
    ds['productId'] = custDecList['productId']
    ds['productName'] = custDecList['productName']
    ds['unit'] = custDecList['unit']
    ds['unitDisplay'] = custDecList['unitDisplay']
    ds['specification'] = custDecList['specification']
    ds['texure'] = custDecList['texure']
    ds['englishDesc'] = custDecList['englishDesc']
    ds['salesUnitPrice'] = custDecList['salesUnitPrice']
    ds['grossWeightOfInvoice'] = custDecList['grossWeightOfInvoice']
    ds['invoiceNetWeight'] = custDecList['invoiceNetWeight']
    ds['salesSubTotal'] = custDecList['salesSubTotal']
    ds['invoicedAmount'] = custDecList['invoicedAmount']
    details.add(ds)
}
map["list"] = details
return map
```
##新增入库单前.groovy
```Groovy
//回写采购单明细
def entity = $formValue

/**
     * 生成入库单时，根据借贷类型，生成相应的财务凭证。
     * 如借贷类型是“采购入库”到“入出库借贷设置”中找“采购入库”的科目配置，生成财务凭证到“财务凭证”下。
     **/
    
    //新增入库单时
    def entity = $formValue
    
    //根据借贷类型生成财务凭证
    def financialVoucher =  new FinancialVoucher();
    
    //财务凭证科目明细
    def financialVoucherAccountDetails = new financialVoucherAccountDetails();
    
    def loanSetting = $formValue.[[入出库借贷设置:loanTypeName:property]]
    def accountDetailsOfInOut = $formValue.[[入出库借贷设置.科目明细:loanTypeName.accountDetails:property]]
    
    debug(com.alibaba.fastjson.JSON.toJSONString(loanSetting));
    debug(com.alibaba.fastjson.JSON.toJSONString(accountDetailsOfInOut));
    
    
    //来源单据编号(与财务凭证同时生成的单据，如到货单、入库单、退货单)
    financialVoucher.sourceDocNo = warehousing.id
    
    //凭证来源(根据当前使用的页面与凭证来源的字典相匹配)
    financialVoucher.sourceOfVoucher = 'cgrk'
    //凭证来源显示值
    financialVoucher.sourceOfVoucherDisplay = '采购入库'
    //记账日期
    financialVoucher.bookkeepingDate = new Date()
    //生成日期
    financialVoucher.generationDate = new Date()
    //制证人 
    financialVoucher.witnessMaking = [[创建人员ID:creatorId:property]]
    //制证人显示值
    financialVoucher.witnessMakingDisplay = [[创建人员:creatorName:property]]
    //凭证编号
    // financialVoucher.documentNumber
  
    //凭证ID
    financialVoucherAccountDetails.voucherId = financialVoucher.id
    //凭证凭证编号
    financialVoucherAccountDetails.documentNumber = financialVoucher.documentNumber
    //凭证贷方金额
    financialVoucherAccountDetails.creditAmount = entity.warehousingAmount
    //凭证科目余额方向显示值
    financialVoucherAccountDetails.accBalDireDisplay = accountDetailsOfInOut.accBalDirDisplay
    //凭证序号
    financialVoucherAccountDetails.serialNumber = entity.creditTypeNo
    //凭证辅助核算
    
    financialVoucherAccountDetails.supplementaryAccounting = entity.[[供应商显示值:supplierDisplay:property]]
    
    //凭证科目显示值
    financialVoucherAccountDetails.subjectDisplay = accountDetailsOfInOut.subjectDisplay
    //凭证借方金额
    financialVoucherAccountDetails.debitAmount = entity.warehousingAmount
    //凭证科目余额方向
    financialVoucherAccountDetails.accBalDire = accountDetailsOfInOut.accBalDir
    //凭证摘要
    financialVoucherAccountDetails.abstractab = entity.outline
    //凭证科目
    financialVoucherAccountDetails.subject = accountDetailsOfInOut.subject
    //凭证科目名称
    financialVoucherAccountDetails.subjectName = accountDetailsOfInOut.subjectName 
    
    //凭证科目明细(借贷类型配置的字段、科目、借贷)
    financialVoucher.voucherAccDetails = financialVoucherAccountDetails   
}
```
##采购结算单待结算到货明细.sql
```SQL
-- 采购结算单待结算明细
SELECT
	dprd.tax_rate,
    dprd.price_contains_tax price_contains_tax2,
    dprd.total_contains_tax,
	dprd.preferential_amount,
	dprd.total4_item,
	dprd.amount purchase_amount,
	dprd.price purchase_price,
	dprd.quantity_return return_qty,
    dpad.*
FROM
	dat_purchase_arrival_det dpad 
left join dat_purchase_req_det dprd on dprd.id = dpad.purchase_req_det_id
WHERE
	dpad.remaining_unsettled_quantity> 0 
	AND EXISTS (
	SELECT
		1 
	FROM
		dat_purchase_arrival dpa 
	WHERE
		dpa.id = dpad.purchase_arrival_id 
		AND dpa.supplier = #{supplierId} 
		<![CDATA[
		AND dpa.arrival_date >= DATE(#{startDate}) 
	    AND dpa.arrival_date <= DATE(#{endDate}) 
	    ]]>
	)

--------------------------------------------------

-- 采购结算单待结算退货明细
SELECT
	dprd.tax_rate,
  	dprd.price_contains_tax price_contains_tax2,
  	dprd.total_contains_tax,
	dprd.preferential_amount,
	dprd.total4_item,
	dprd.amount purchase_amount,
	dprd.price,
	dprd.quantityReturn,

t1.*
FROM
	dat_purchase_returns_det t1
left join dat_purchase_req_det dprd on dprd.id = t1.purchase_req_det_id
WHERE
	t1.remaining_unsettled_quantity > 0 
	AND EXISTS (
	SELECT
		1 
	FROM
		dat_purchase_returns dpr
	WHERE
		dpr.id = t1.purchase_returns_id
		AND dpr.supplier = '7e94a536-de04-4abd-8ee5-8ff44529424e' 
		AND dpr.return_date >= DATE('2000.01.01') 
	  AND dpr.return_date <= DATE('2021.01.01') 
	)

返回值处理脚本
---------------------------------
def map=[:]
def details = []
for(orderDetail in $returnValue){
    def saleDetail = [:]
    saleDetail['product'] = orderDetail['productId']
    saleDetail['productDisplay'] = orderDetail['productDisplay']
    saleDetail['productCode'] = orderDetail['productCode']
	saleDetail['productUnit'] = orderDetail['productUnit']
    saleDetail['amount4Returns'] = orderDetail['amount4Returns']
    saleDetail['packagingUnit'] = orderDetail['packagingUnit']
    saleDetail['price'] = orderDetail['price']
    saleDetail['purReturnAmount'] = orderDetail['purReturnAmount']
    saleDetail['returnDisRate'] = orderDetail['returnDisRate']
    saleDetail['unitPriceAfterDis'] = orderDetail['unitPriceAfterDis']
    saleDetail['retAmountAfterDis'] = orderDetail['retAmountAfterDis']
    saleDetail['purReturnDisAmount'] = orderDetail['purReturnDisAmount']
    saleDetail['purchaseReturnsId'] = orderDetail['purchaseReturnsId']
    saleDetail['cause4Returns'] = orderDetail['cause4Returns']
    saleDetail['code4PurchaseReturns'] = orderDetail['code4PurchaseReturns']

saleDetail['subtotal'] = orderDetail['total4Item']
    saleDetail['notArrivedQty'] = orderDetail['surplus4Unreached']
    saleDetail['purchaseArrivalDetailId'] = orderDetail['id']
    saleDetail['id4PurchaseReqDet'] = orderDetail['purchaseReqDetId']
    saleDetail['purchaseReqId'] = orderDetail['id4PurchaseReq']
    saleDetail['currentSettlementQty'] = orderDetail['remainingUnsettledQuantity']
    saleDetail['subsidizedFreight'] = orderDetail['subsidizedFreight']
    details.add(saleDetail)
}
map["details"] = details
return map

参数，返回值类型为键值对
details

产品显示值 productDisplay
产品 product
产品编号 productDisplay

产品单位 	productUnit

本次退货数量 amount4Returns
退货数量按包装单位转换 packagingUnit
采购单价 price
采购退货金额 purReturnAmount
退货折扣率 returnDisRate
折扣后单价 unitPriceAfterDis
折扣后退货金额 retAmountAfterDis
采购退货折扣金额 purReturnDisAmount
退货单ID（隐藏）purchaseReturnsld
退货原因（隐藏）cause4Returns
退货单号（隐藏）code4PurchaseReturns

箱装单位caseNumberUnit 如果是箱装数是1显示CTN,复数则显示CTNS,
caseNumberUnit
重量单位weight  :自动填入KGS 
weight
中英品名拼接
chinaAndEnglishNames
```
##根据发货单ID查询报关发票明细.sql
```SQL
select 
    s.origin_code product_origin_code,
    s.origin_place product_origin,
    id.main_lcl,
    id.main_lcl_display,
    id.supplier_id,
	id.invoice_id,
	id.quantity_of_goods,
	id.number_of_containers,
	id.product_id product_name,
	id.packing_no,
	id.sequence_number,
	id.chinese_name,
	id.invoice_product_name,
	id.product_id,
	id.gross_weight,
	id.net_weight,
	id.sales_order_id,
	id.product_id product,
	id.invoiced_amount,
	id.serial_number,
	id.english_name,
	id.package_warehousing_no,
	id.packing_receipt_id packing_warehouse_entry_id,
	id.invoice_product_name billing_name,
	p.unit product_unit,
	id.billingunitprice billing_unit_price,
	p.code product_number,
	p.name product_name_display,
    p.unit,
    p.unit_display,
    p.specification,
    p.texure,
    p.english_desc,
    cd.price_contains_tax sales_unit_price,
    id.gross_weight * id.number_of_containers gross_weight_of_invoice,
    id.net_weight * id.number_of_containers invoice_net_weight,
    cd.price_contains_tax * id.quantity_of_goods sales_sub_total
from  
    dat_invoice_details id
left join dat_product p
    on p.id = id.product_id
left join dat_contract_detail cd
    on cd.id4_contract = id.sales_order_id
    and cd.product = id.product_id
left join dat_supplier s 
    on s.id = id.supplier_id
where id.invoice_id in
<foreach item="outOrderId" index="index" collection='outOrderIds' open="( " separator="," close=" )">
	#{outOrderId}
</foreach> 
order by id.packing_no asc

----------------------------------------
参数处理脚本
$parameterValue.outOrderIds = $parameterValue.outOrderIds.split(',');

----------------------------------------
返回值
return [list : $returnValue];
```
##根据id获取发货明细.sql
```SQL
select 
    s.origin_code product_origin_code,
    s.origin_place product_origin,
    sod.supplier_id,
    sod.main_lcl,
    sod.main_lcl_display,
    sod.net_weight,
    sod.gross_weight,
    sod.invoice_product_name,
    sod.sales_order_id,
    sod.serial_number,
    sod.invoice_id,
    sod.sequence_number,
    sod.packing_no,
    sod.chinese_name,
    sod.english_name,
    sod.product_unit,
    sod.quantity_of_goods,
    sod.number_of_containers,
    sod.billingunitprice ,
    -- sod.invoiced_amount invoice_amount,
    sod.product_id product_id,
    sod.product_id product_name,
    p.unit,
    p.unit_display,
    p.specification,
    p.texure,
    p.english_desc,
    cd.price_contains_tax sales_unit_price,
    sod.gross_weight * sod.number_of_containers gross_weight_of_invoice,
    sod.net_weight * sod.number_of_containers invoice_net_weight,
    cd.price_contains_tax * sod.quantity_of_goods sales_sub_total,
    sod.billingunitprice * sod.quantity_of_goods invoiced_amount  
   
 -- 开票单价
 -- 开票总额
from  
    dat_shipping_order_det sod
left join dat_product p
    on p.id = sod.product_id
left join dat_contract_detail cd
    on cd.id4_contract = sod.sales_order_id
    and cd.product = sod.product_id
left join dat_supplier s 
    on s.id = sod.supplier_id
where sod.invoice_id in
<foreach  item="item" index="index" collection='ids'
 open="(" separator="," close=" )">
 #{item}
</foreach> 
order by sod.packing_no asc

参数处理脚本
-----------------------------------
def ids=$parameterValue.ids.split(",");
$parameterValue.ids=ids;
return $parameterValue;

返回值处理脚本
-----------------------------------
return [list:$returnValue]
```
##根据销售订单代采购明细.sql
```SQL
-- 根据销售订单代采购明细
select 
    default_supplier,
    default_supplier_display,
    quantity_to_be_purchased,
    quantity_purchased,
    invoice_product_name,
    product,
    id4_contract,
    product_quality,
    product_code,
    subtotal,
    product_price,
    total_price_concessions,
    settled_amount,
    product_price as price_contains_tax,
    product_price * amount as total_contains_tax,
    0 as preferential_amount,
    0 as accu_arrival_amount,
    product_price as price,
    product_price * amount as total4_item,
    inventory_of_outer_packing,
    inner_box_inventory,
    middle_box_capacity,
    outer_box_capacity,
    number_of_notches_in_inner_box,
    qty_of_gaps_in_outer_packing,
    inner_box_capacity,
    number_of_boxes_in_stock,
    middle_box_qty,
    medium_box_packaging_display,
    inner_box_packaging_display,
    outer_packing,
    outer_packing_display,
    medium_box_packaging,
    inner_box_packaging,
    inquiry_requirements,
    english_name,
    english_model,
    product_decals,
    product_color,
    product_model,
    date_delivery,
    serial_number,
    code4_contract,
    undelivered_quantity,
    quantity_shipped,
    quantity_returned,
    quantity_not_invoiced,
    quantity_not_delivered,
    invoiced_quantity,
    settled_quantity,
    unsettled_quantity,
    delivered_quantity,
    remark,
    product_unit,
    product_display,
    amount,
    0 as tax_rate,
    id as sales_order_detail_id,
    amount - ifnull(accu_arrival_amount,0) as surplus4_unreached
from 
    dat_contract_detail
where 
    id4_contract = #{id}
order by serial_number

-------------------------------------------

-- return [list:$returnValue];

-- 列表 list 代采购明细
```
##统计报表查询.sql
```SQL
-- 本月采购金额
SELECT
	sum(amount_after_discount)
FROM
	dat_purchase_req
WHERE
	STATUS != 'canceled'
AND DATE_FORMAT(req_date, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')

-- 上月采购金额
SELECT
	sum(amount_after_discount) amount
FROM
	dat_purchase_req
WHERE
	STATUS != 'canceled'
AND
  PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'),DATE_FORMAT(req_date, '%Y%m')) = 1

-- 去年同期采购金额
SELECT
	sum(amount_after_discount) amount
FROM
	dat_purchase_req
WHERE
	status != 'canceled'
AND
	YEAR (req_date) = YEAR (NOW() - INTERVAL 1 YEAR)
AND MONTH (req_date) = MONTH (NOW())

-- 本年度采购金额
SELECT
	sum(amount_after_discount) amount
FROM
	dat_purchase_req
WHERE
	status != 'canceled'
AND
    YEAR(req_date) = YEAR(NOW())

-- 上年度累计采购金额
SELECT
	sum(amount_after_discount) amount
FROM
	dat_purchase_req
WHERE
	status != 'canceled'
AND
  year(req_date) = year((now() - interval 1 year))

-- 本年度截止到当前月的月采购金额
SELECT
	sum(amount_after_discount) amount
FROM
	dat_purchase_req
WHERE
	STATUS != 'canceled'
AND year(req_date) = year(NOW())
AND month(req_date) >= 1 or month(req_date) <= MONTH(NOW())

-- 按月份分组汇总采购订单产品金额合计
SELECT
	month(req_date) mon,sum(amount_after_discount) amount
FROM
	dat_purchase_req
WHERE
	STATUS != 'canceled'
AND year(req_date) = year(NOW())
AND month(req_date) >= 1 or month(req_date) <= MONTH(NOW())
group by month(req_date)

-- 有效供应商询价少于3家的货品，列表供应商报价少于3家的货品名称/货品编号/规格型号/货品单位/已报价的供应商个数；
SELECT
	*
FROM
```

111 2024年11月06日

冲冲冲冲回复