$PBExportHeader$f_order_wkp_barcode_pda.srf global type f_order_wkp_barcode_pda from function_object end type forward prototypes global function integer f_order_wkp_barcode_pda (boolean arg_replace, ref string arg_msg, ref long arg_cnt, ref long arg_failcnt) end prototypes global function integer f_order_wkp_barcode_pda (boolean arg_replace, ref string arg_msg, ref long arg_cnt, ref long arg_failcnt);//==================================================================== // 事件: .f_order_wkp_barcode_pda() //-------------------------------------------------------------------- // 描述: //-------------------------------------------------------------------- // 参数: // value boolean arg_replace 优先完成时间较早的计划 // reference string arg_msg // reference long arg_cnt // reference long arg_failcnt //-------------------------------------------------------------------- // 返回: integer //-------------------------------------------------------------------- // 作者: lwl 日期: 2012年02月24日 //-------------------------------------------------------------------- // LONGJOE //-------------------------------------------------------------------- // 修改历史: // //==================================================================== Int rslt = 1 Long ll_i,ll_j,ll_k,ll_l,ll_m Long ll_wrkgrpid String ls_barcode Long ll_swkpid,ll_owkpid Int li_sflag,li_oflag Long ll_scid,ll_orderid,ll_printid Long ll_printid_tree Int li_kind Long ll_pid DateTime ldt_opdate String ls_opemp Decimal ld_packqty Long ll_row DateTime server_dt,ldt_fdate Decimal ld_addqty String ls_relname,ls_partname Decimal ld_min_orderqty,ld_sum_fqty String ls_bar_relname, ls_bar_partname Long ll_f_mtrlid String ls_f_status ,ls_f_woodcode,ls_f_pcode Long ll_f_swkpid, ll_f_owkpid String ls_f_relname,ls_f_partname Long ll_f_scid,ll_f_orderid DateTime ld_f_requiredate Long cnt Long ll_f_printid,ll_f_pid Long ll_r_scid,ll_r_orderid,ll_r_printid,ll_r_pid String ls_r_barcode Long ll_scid_tmp Long ll_orderid_tmp Long ll_suc,ll_fail Long ll_sqty,ll_oqty Long ll_workgroupid Long ll_scid_ds,ll_orderid_ds,ll_swkpid_ds,ll_workgroupid_ds Decimal ld_minsqty_ds Long ll_workgroupid_up,ll_owrkGrpid_up String ls_wkpname_up Decimal ld_update_qty,ld_orderqty_ds,ld_stopqty_ds,ld_finishqty_ds Long ll_row_wkp String ls_orderstr_arr[] Long ll_rowcnt Long ll_mxbt Long ls_p Long ll_mxbt_wrk Long ll_scid_arr[],ll_orderid_arr[],ll_wrkgrpid_arr[],ll_scid_wkp[],ll_orderid_wkp[] Decimal ld_minsqty ,ld_finishqty Long ll_workgroupid_p Int li_barcode_check Decimal ld_qty ll_mxbt = 0 ll_mxbt_wrk = 0 ll_suc = 0 ll_fail = 0 Int ll_oldfail = 0 datastore ds_pda ds_pda = Create datastore ds_pda.DataObject = 'ds_order_wkp_barcode_pda' ds_pda.SetTransObject(sqlca) datastore ds_update_wrk ds_update_wrk = Create datastore ds_update_wrk.DataObject = 'ds_barcode_update_ordermtrl_workgroup_id' ds_update_wrk.SetTransObject(sqlca) datastore ds_order_wkp ds_order_wkp = Create datastore ds_order_wkp.DataObject = 'ds_barcode_order_wkp_workgroupid' ds_order_wkp.SetTransObject(sqlca) datastore ds_relname ds_relname = Create datastore ds_relname.DataObject = 'ds_cmp_date_barcode_relname' ds_relname.SetTransObject(sqlca) datastore ds_mx ds_mx = Create datastore ds_mx.DataObject = 'ds_cmp_date_barcode_mx' ds_mx.SetTransObject(sqlca) ll_row = ds_pda.Retrieve() IF ll_row = -1 THEN IF f_re_conect(arg_msg) = 0 THEN rslt = 0 GOTO ext END IF ll_row = ds_pda.Retrieve() IF ll_row = -1 THEN arg_msg = '查询未处理条码失败,' + sqlca.SQLErrText rslt = 0 GOTO ext END IF END IF SELECT Top 1 getdate() Into :server_dt From u_user; //取得系统时间,借用操作员表 IF sqlca.SQLCode <> 0 THEN arg_msg = '查询日期失败,'+sqlca.SQLErrText ROLLBACK; rslt = 0 GOTO ext END IF ldt_fdate = DateTime(Date(server_dt),Time(0)) FOR ll_i = 1 To ll_row // 临时处理 重连数据库 IF ll_fail - ll_oldfail >= 5 THEN DISCONNECT; CONNECT; END IF ls_barcode = ds_pda.Object.barcode[ll_i] ll_wrkgrpid = ds_pda.Object.wrkgrpid[ll_i] li_kind = ds_pda.Object.Kind[ll_i] ldt_opdate = ds_pda.Object.opdate[ll_i] ls_opemp = ds_pda.Object.opemp[ll_i] ll_workgroupid = ds_pda.Object.workgroupid[ll_i] SELECT barcode_check INTO :li_barcode_check FROM u_sc_workgroup Where wrkgrpid = :ll_wrkgrpid; IF sqlca.SQLCode <> 0 THEN li_barcode_check = 0 ll_r_orderid = 0 IF li_kind = 0 THEN // 完工模式 //按顺序查找同工的最早条码 SELECT u_Order_ml.mtrlid, u_Order_ml.status_mode, u_Order_ml.woodcode, u_Order_ml.pcode, u_ordermtrl_workgroup_barcode.swkpid, u_ordermtrl_workgroup_barcode.owkpid, u_ordermtrl_workgroup_barcode.relname, u_ordermtrl_workgroup_barcode.partname, u_ordermtrl_workgroup_barcode.scid, u_ordermtrl_workgroup_barcode.orderid, u_OrderMtrl_workgroup.requiredate, u_ordermtrl_workgroup_barcode.printid, u_ordermtrl_workgroup_barcode.pid INTO :ll_f_mtrlid, :ls_f_status, :ls_f_woodcode, :ls_f_pcode, :ll_f_swkpid, :ll_f_owkpid, :ls_f_relname, :ls_f_partname, :ll_f_scid, :ll_f_orderid, :ld_f_requiredate, :ll_f_printid, :ll_f_pid FROM u_ordermtrl_workgroup_barcode INNER JOIN u_Order_ml ON u_ordermtrl_workgroup_barcode.scid = u_Order_ml.scid AND u_ordermtrl_workgroup_barcode.orderid = u_Order_ml.OrderID INNER JOIN u_OrderMtrl_workgroup ON u_ordermtrl_workgroup_barcode.scid = u_OrderMtrl_workgroup.scid AND u_ordermtrl_workgroup_barcode.swkpid = u_OrderMtrl_workgroup.wrkGrpid AND u_ordermtrl_workgroup_barcode.orderid = u_OrderMtrl_workgroup.orderid WHERE u_ordermtrl_workgroup_barcode.barcode = :ls_barcode And u_ordermtrl_workgroup_barcode.swkpid = :ll_wrkgrpid; IF sqlca.SQLCode <> 0 THEN // ROLLBACK; // arg_msg = '查询条码 '+ls_barcode+' 相关信息失败,'+sqlca.SQLErrText // ll_fail++ // CONTINUE GOTO _update_data END IF cnt = 0 SELECT count(*) INTO :cnt FROM u_ordermtrl_workgroup_barcode INNER JOIN u_Order_ml ON u_ordermtrl_workgroup_barcode.scid = u_Order_ml.scid AND u_ordermtrl_workgroup_barcode.orderid = u_Order_ml.OrderID INNER JOIN u_OrderMtrl_workgroup ON u_ordermtrl_workgroup_barcode.scid = u_OrderMtrl_workgroup.scid AND u_ordermtrl_workgroup_barcode.swkpid = u_OrderMtrl_workgroup.wrkGrpid AND u_ordermtrl_workgroup_barcode.orderid = u_OrderMtrl_workgroup.orderid WHERE u_Order_ml.mtrlid = :ll_f_mtrlid AND u_Order_ml.status_mode = :ls_f_status AND u_Order_ml.woodcode = :ls_f_woodcode AND u_Order_ml.pcode = :ls_f_pcode AND u_ordermtrl_workgroup_barcode.swkpid = :ll_f_swkpid AND u_ordermtrl_workgroup_barcode.owkpid = :ll_f_owkpid AND u_ordermtrl_workgroup_barcode.relname = :ls_f_relname AND u_ordermtrl_workgroup_barcode.partname = :ls_f_partname AND u_ordermtrl_workgroup_barcode.orderid <> :ll_f_orderid AND u_OrderMtrl_workgroup.requiredate < :ld_f_requiredate AND u_ordermtrl_workgroup_barcode.sflag = 0 AND u_Order_ml.status = 1 ; IF sqlca.SQLCode <> 0 THEN ROLLBACK; // arg_msg = '查询条码 '+ls_barcode+' 相同部件未完成情况失败,'+sqlca.SQLErrText // ll_fail++ // CONTINUE ll_r_scid = 0 ll_r_orderid = 0 ll_r_printid = 0 ll_r_pid = 0 GOTO _update_data END IF IF cnt > 0 And arg_replace THEN SELECT top 1 u_ordermtrl_workgroup_barcode.scid, u_ordermtrl_workgroup_barcode.orderid, u_ordermtrl_workgroup_barcode.printid, u_ordermtrl_workgroup_barcode.pid, u_ordermtrl_workgroup_barcode.barcode INTO :ll_r_scid, :ll_r_orderid, :ll_r_printid, :ll_r_pid, :ls_r_barcode FROM u_ordermtrl_workgroup_barcode INNER JOIN u_Order_ml ON u_ordermtrl_workgroup_barcode.scid = u_Order_ml.scid AND u_ordermtrl_workgroup_barcode.orderid = u_Order_ml.OrderID INNER JOIN u_OrderMtrl_workgroup ON u_ordermtrl_workgroup_barcode.scid = u_OrderMtrl_workgroup.scid AND u_ordermtrl_workgroup_barcode.swkpid = u_OrderMtrl_workgroup.wrkGrpid AND u_ordermtrl_workgroup_barcode.orderid = u_OrderMtrl_workgroup.orderid WHERE u_Order_ml.mtrlid = :ll_f_mtrlid AND u_Order_ml.status_mode = :ls_f_status AND u_Order_ml.woodcode = :ls_f_woodcode AND u_Order_ml.pcode = :ls_f_pcode AND u_ordermtrl_workgroup_barcode.swkpid = :ll_f_swkpid AND u_ordermtrl_workgroup_barcode.owkpid = :ll_f_owkpid AND u_ordermtrl_workgroup_barcode.relname = :ls_f_relname AND u_ordermtrl_workgroup_barcode.partname = :ls_f_partname AND u_ordermtrl_workgroup_barcode.orderid <> :ll_f_orderid AND u_OrderMtrl_workgroup.requiredate < :ld_f_requiredate AND u_ordermtrl_workgroup_barcode.sflag = 0 AND u_Order_ml.status = 1 Order By u_OrderMtrl_workgroup.requiredate; IF sqlca.SQLCode <> 0 THEN ROLLBACK; // arg_msg = '查询条码 '+ls_barcode+' 相同部件未完成情况失败2,'+sqlca.SQLErrText // ll_fail++ // CONTINUE ll_r_scid = 0 ll_r_orderid = 0 ll_r_printid = 0 ll_r_pid = 0 GOTO _update_data END IF ll_scid_tmp = ll_r_scid - 100000000 ll_orderid_tmp = ll_r_orderid - 100000000 UPDATE u_ordermtrl_workgroup_barcode SET scid = :ll_scid_tmp, orderid = :ll_orderid_tmp WHERE scid = :ll_r_scid AND orderid = :ll_r_orderid AND barcode = :ls_r_barcode And pid = :ll_r_pid; IF sqlca.SQLCode <> 0 THEN ROLLBACK; // arg_msg = '更新条码 '+ls_barcode+' 信息失败 ,'+sqlca.SQLErrText // ll_fail++ // CONTINUE ll_r_scid = 0 ll_r_orderid = 0 ll_r_printid = 0 ll_r_pid = 0 GOTO _update_data END IF // UPDATE u_ordermtrl_workgroup_barcode // SET barcode = :ls_barcode // WHERE scid = :ll_r_scid // AND orderid = :ll_r_orderid // AND barcode = :ls_r_barcode; // IF sqlca.SQLCode <> 0 THEN // ROLLBACK; // arg_msg = '更新条码 '+ls_barcode+' 信息失败 ,'+sqlca.SQLErrText // ll_fail++ // CONTINUE // END IF UPDATE u_ordermtrl_workgroup_barcode SET scid = :ll_r_scid, orderid = :ll_r_orderid, pid = :ll_r_pid WHERE scid = :ll_f_scid AND orderid = :ll_f_orderid AND barcode = :ls_barcode And pid = :ll_f_pid; IF sqlca.SQLCode <> 0 THEN ROLLBACK; // arg_msg = '更新条码 '+ls_barcode + '对应更换条码 '+ls_r_barcode+' 信息失败_1,'+sqlca.SQLErrText // ll_fail++ // CONTINUE ll_r_scid = 0 ll_r_orderid = 0 ll_r_printid = 0 ll_r_pid = 0 GOTO _update_data END IF UPDATE u_ordermtrl_workgroup_barcode SET scid = :ll_f_scid, orderid = :ll_f_orderid, pid = :ll_f_pid WHERE scid = :ll_scid_tmp And orderid = :ll_orderid_tmp; IF sqlca.SQLCode <> 0 THEN ROLLBACK; // arg_msg = '更新条码 '+ls_barcode + '对应更换条码 '+ls_r_barcode+' 信息失败_2,'+sqlca.SQLErrText // ll_fail++ // CONTINUE ll_r_scid = 0 ll_r_orderid = 0 ll_r_printid = 0 ll_r_pid = 0 GOTO _update_data END IF END IF _update_data: SELECT scid, orderid, printid, swkpid, owkpid, sflag, oflag, pid, relname, partname, workgroupid, qty INTO :ll_scid, :ll_orderid, :ll_printid, :ll_swkpid, :ll_owkpid, :li_sflag, :li_oflag, :ll_pid, :ls_bar_relname, :ls_bar_partname, :ll_workgroupid_p, :ld_qty FROM u_ordermtrl_workgroup_barcode WHERE barcode = :ls_barcode And swkpid = :ll_wrkgrpid; IF sqlca.SQLCode <> 0 THEN arg_msg = '1.查询条码 '+ls_barcode+' 相关信息失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF //不严格限制工作中心或条码本身没有指定工作中心,则程序为条码匹配工作中心 IF li_barcode_check = 0 Or ll_workgroupid_p = 0 THEN //20111013 应急处理 UPDATE u_OrderMtrl_workgroup_barcode SET u_ordermtrl_workgroup_barcode.workgroupid = u_OrderMtrl_workgroup.workgroupid FROM u_ordermtrl_workgroup_barcode INNER JOIN u_OrderMtrl_workgroup ON u_ordermtrl_workgroup_barcode.scid = u_OrderMtrl_workgroup.scid AND u_ordermtrl_workgroup_barcode.orderid = u_OrderMtrl_workgroup.orderid AND u_ordermtrl_workgroup_barcode.swkpid = u_OrderMtrl_workgroup.wrkGrpid AND u_ordermtrl_workgroup_barcode.workgroupid <> u_OrderMtrl_workgroup.workgroupid WHERE u_ordermtrl_workgroup_barcode.scid = :ll_scid AND u_ordermtrl_workgroup_barcode.orderid = :ll_orderid And u_ordermtrl_workgroup_barcode.workgroupid <> u_OrderMtrl_workgroup.workgroupid; // SELECT count(*) INTO :cnt FROM u_OrderMtrl_workgroup WHERE scid = :ll_scid AND orderid = :ll_orderid AND wrkgrpid = :ll_swkpid And workgroupid = :ll_workgroupid; IF sqlca.SQLCode <> 0 THEN cnt = 0 END IF IF cnt = 0 THEN SELECT top 1 workgroupid INTO :ll_workgroupid FROM u_OrderMtrl_workgroup WHERE scid = :ll_scid AND orderid = :ll_orderid And wrkgrpid = :ll_swkpid; IF sqlca.SQLCode <> 0 THEN arg_msg = '2.查询条码 '+ls_barcode+' 相关生产计划工组进度信息失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF END IF ELSE IF ll_workgroupid_p <> ll_workgroupid THEN arg_msg = '条码 '+ls_barcode+' 所属工作中心与当前选择的工作中心不符,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF END IF UPDATE u_ordermtrl_workgroup_barcode SET sflag = 1, sdate = :ldt_opdate, semp = :ls_opemp, workgroupid = :ll_workgroupid WHERE scid = :ll_scid AND orderid = :ll_orderid AND printid = :ll_printid And pid = :ll_pid; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新条码完成状态失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF // SELECT sum(sflag),sum(oflag) // INTO :ll_sqty,:ll_oqty // FROM u_ordermtrl_workgroup_barcode // WHERE scid = :ll_scid // AND orderid = :ll_orderid // And printid = :ll_printid; // IF sqlca.SQLCode <> 0 THEN // ROLLBACK; // arg_msg = '查询工组完成进度失败,'+sqlca.SQLErrText // ll_fail++ // CONTINUE // END IF SELECT printid INTO :ll_printid_tree FROM u_OrderMtrl_workgroup_tree WHERE scid = :ll_scid AND orderid = :ll_orderid AND relname = :ls_bar_relname AND partname = :ls_bar_partname AND workgroupid = :ll_workgroupid And orderqty > sqty ; IF sqlca.SQLCode <> 0 THEN SELECT top 1 printid INTO :ll_printid_tree FROM u_OrderMtrl_workgroup_tree WHERE scid = :ll_scid AND orderid = :ll_orderid AND relname = :ls_bar_relname AND partname = :ls_bar_partname And orderqty > sqty ; IF sqlca.SQLCode <> 0 THEN ll_printid_tree = ll_printid END IF END IF UPDATE u_OrderMtrl_workgroup_tree SET u_ordermtrl_workgroup_tree.sqty = case when u_ordermtrl_workgroup_tree.sqty + :ld_qty > u_ordermtrl_workgroup_tree.orderqty then u_ordermtrl_workgroup_tree.orderqty else u_ordermtrl_workgroup_tree.sqty + :ld_qty END, u_ordermtrl_workgroup_tree.sdate = :ldt_opdate WHERE scid = :ll_scid AND orderid = :ll_orderid And printid = :ll_printid_tree; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新工组条码进度已完成数失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF IF ll_r_orderid > 0 THEN UPDATE u_OrderMtrl_workgroup_tree SET u_OrderMtrl_workgroup_tree.sqty = case when isnull(a.sqty,0) > u_ordermtrl_workgroup_tree.orderqty then u_ordermtrl_workgroup_tree.orderqty else isnull(a.sqty,0) END, u_OrderMtrl_workgroup_tree.oqty = case when isnull(a.oqty,0) > u_ordermtrl_workgroup_tree.orderqty then u_ordermtrl_workgroup_tree.orderqty else isnull(a.oqty,0) END FROM u_OrderMtrl_workgroup_tree INNER JOIN (SELECT SUM(sflag) AS sqty, SUM(oflag) AS oqty, scid, orderid, printid FROM u_ordermtrl_workgroup_barcode WHERE scid = :ll_f_scid AND orderid = :ll_f_orderid GROUP BY scid, orderid, printid) a ON u_OrderMtrl_workgroup_tree.scid = a.scid AND u_OrderMtrl_workgroup_tree.orderid = a.orderid AND u_OrderMtrl_workgroup_tree.printid = a.printid WHERE u_OrderMtrl_workgroup_tree.scid = :ll_f_scid And u_OrderMtrl_workgroup_tree.orderid = :ll_f_orderid; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新工组条码进度已完成数失败(原计划),'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF UPDATE u_OrderMtrl_workgroup_tree SET u_OrderMtrl_workgroup_tree.sqty = case when isnull(a.sqty,0) > u_ordermtrl_workgroup_tree.orderqty then u_ordermtrl_workgroup_tree.orderqty else isnull(a.sqty,0) END, u_OrderMtrl_workgroup_tree.oqty = case when isnull(a.oqty,0) > u_ordermtrl_workgroup_tree.orderqty then u_ordermtrl_workgroup_tree.orderqty else isnull(a.oqty,0) END FROM u_OrderMtrl_workgroup_tree INNER JOIN (SELECT SUM(sflag) AS sqty, SUM(oflag) AS oqty, scid, orderid, printid FROM u_ordermtrl_workgroup_barcode WHERE scid = :ll_f_scid AND orderid = :ll_f_orderid GROUP BY scid, orderid, printid) a ON u_OrderMtrl_workgroup_tree.scid = a.scid AND u_OrderMtrl_workgroup_tree.orderid = a.orderid AND u_OrderMtrl_workgroup_tree.printid = a.printid WHERE u_OrderMtrl_workgroup_tree.scid = :ll_r_scid And u_OrderMtrl_workgroup_tree.orderid = :ll_r_orderid; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新工组条码进度已完成数失败(新替换计划),'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF END IF ELSEIF li_kind = 1 THEN // 接收模式 SELECT scid, orderid, printid, swkpid, owkpid, sflag, oflag, pid, relname, partname, qty INTO :ll_scid, :ll_orderid, :ll_printid, :ll_swkpid, :ll_owkpid, :li_sflag, :li_oflag, :ll_pid, :ls_bar_relname, :ls_bar_partname, :ld_qty FROM u_ordermtrl_workgroup_barcode WHERE barcode = :ls_barcode And owkpid = :ll_wrkgrpid; IF sqlca.SQLCode <> 0 THEN arg_msg = '3.查询条码 '+ls_barcode+' 相关信息失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF UPDATE u_ordermtrl_workgroup_barcode SET oflag = 1, odate = :ldt_opdate, oemp = :ls_opemp WHERE scid = :ll_scid AND orderid = :ll_orderid AND printid = :ll_printid And pid = :ll_pid; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新条码接收状态失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF SELECT top 1 printid INTO :ll_printid_tree FROM u_OrderMtrl_workgroup_tree WHERE scid = :ll_scid AND orderid = :ll_orderid AND relname = :ls_bar_relname AND partname = :ls_bar_partname And sqty > oqty ; IF sqlca.SQLCode <> 0 THEN ll_printid_tree = ll_printid END IF UPDATE u_OrderMtrl_workgroup_tree SET u_ordermtrl_workgroup_tree.oqty = case when u_ordermtrl_workgroup_tree.oqty + :ld_qty > u_ordermtrl_workgroup_tree.orderqty then u_ordermtrl_workgroup_tree.orderqty else u_ordermtrl_workgroup_tree.oqty + :ld_qty END , u_ordermtrl_workgroup_tree.odate = :ldt_opdate WHERE scid = :ll_scid AND orderid = :ll_orderid AND printid = :ll_printid_tree And u_OrderMtrl_workgroup_tree.oqty < u_OrderMtrl_workgroup_tree.orderqty; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新工组条码进度已接收数失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF ELSE // 最后工组完工 SELECT u_workgroup.wrkGrpid, u_getbar.orderid, u_getbar.scid, u_mtrlware_mx.packqty INTO :ll_wrkGrpid, :ll_orderid, :ll_scid, :ld_packqty FROM u_workgroup INNER JOIN (SELECT scid, orderid, MAX(workgroupid) AS workgroupid FROM u_OrderMtrl_workgroup WHERE (iflast = 1) GROUP BY scid, orderid) ordermtrl ON u_workgroup.workgroupid = ordermtrl.workgroupid RIGHT OUTER JOIN u_mtrldef INNER JOIN u_Order_ml ON u_mtrldef.mtrlid = u_Order_ml.mtrlid ON ordermtrl.scid = u_Order_ml.scid AND ordermtrl.orderid = u_Order_ml.OrderID RIGHT OUTER JOIN u_mtrlware_mx INNER JOIN u_getbar ON u_mtrlware_mx.getbarid = u_getbar.getbarid ON u_Order_ml.scid = u_getbar.scid AND u_Order_ml.OrderID = u_getbar.orderid Where u_mtrlware_mx.barcode = :ls_barcode; IF sqlca.SQLCode <> 0 THEN arg_msg = '4.查询条码 '+ls_barcode+' 相关信息失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF UPDATE u_OrderMtrl_workgroup_tree SET u_ordermtrl_workgroup_tree.sqty = case when u_ordermtrl_workgroup_tree.sqty + :ld_packqty > u_ordermtrl_workgroup_tree.orderqty then u_ordermtrl_workgroup_tree.orderqty else u_ordermtrl_workgroup_tree.sqty + :ld_packqty END , u_ordermtrl_workgroup_tree.sdate = :ldt_opdate WHERE scid = :ll_scid AND orderid = :ll_orderid AND iflast = 1 And u_OrderMtrl_workgroup_tree.sqty < u_OrderMtrl_workgroup_tree.orderqty; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新最后工组已完成数失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF END IF UPDATE u_order_wkp_barcode_pda SET flag = 1 WHERE barcode = :ls_barcode AND wrkgrpid = :ll_wrkgrpid And Kind = :li_kind; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新条码状态失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF IF li_kind <> 2 THEN // SELECT sum(s_qty.minsqty) // INTO :ld_addqty // FROM u_OrderMtrl_workgroup INNER JOIN // (SELECT scid, orderid, swkpid, MIN(sqty) AS minsqty // FROM (SELECT scid, orderid, swkpid, SUM(sflag) AS sqty, relname, // partname // FROM u_ordermtrl_workgroup_barcode // WHERE sflag = 1 // AND CONVERT(varchar(10), sdate, 120) = CONVERT(varchar(10), :ldt_fdate, 120) // GROUP BY scid, orderid, swkpid, relname, partname) a // GROUP BY scid, orderid, swkpid) s_qty ON // u_OrderMtrl_workgroup.scid = s_qty.scid AND // u_OrderMtrl_workgroup.orderid = s_qty.orderid AND // u_OrderMtrl_workgroup.wrkGrpid = s_qty.swkpid // WHERE (u_OrderMtrl_workgroup.scid = :ll_scid) // AND (u_OrderMtrl_workgroup.orderid = :ll_orderid) // AND (u_OrderMtrl_workgroup.wrkgrpid = :ll_wrkgrpid) // And (u_OrderMtrl_workgroup.iflast = 0); // IF sqlca.SQLCode <> 0 THEN // ld_addqty = 0 // END IF // // IF ld_addqty > 0 THEN // UPDATE u_OrderMtrl_workgroup_date // SET fqty = :ld_addqty // WHERE scid = :ll_scid // AND orderid = :ll_orderid // AND wrkgrpid = :ll_wrkgrpid // And fdate = :ldt_fdate; // IF sqlca.SQLCode = 0 THEN // IF sqlca.SQLNRows = 0 THEN // INSERT INTO u_OrderMtrl_workgroup_date // (scid,orderid,wrkgrpid,fdate,fqty) // VALUES // (:ll_scid,:ll_orderid,:ll_wrkgrpid,:ldt_fdate,:ld_addqty); // IF sqlca.SQLCode <> 0 THEN // ROLLBACK; // arg_msg = '更新工组当天进度失败,'+sqlca.SQLErrText // ll_fail++ // CONTINUE // END IF // END IF // ELSE // ROLLBACK; // arg_msg = '更新工组当天进度失败,'+sqlca.SQLErrText // ll_fail++ // CONTINUE // END IF // END IF IF li_kind = 0 THEN FOR ls_p = 1 To ll_mxbt IF ll_orderid = ll_orderid_arr[ls_p] And ll_scid = ll_scid_arr[ls_p] THEN GOTO _find_wrk END IF NEXT ll_mxbt++ ls_orderstr_arr[ll_mxbt] = String(ll_orderid)+'_'+String(ll_scid) ll_orderid_arr[ll_mxbt] = ll_orderid ll_scid_arr[ll_mxbt] = ll_scid _find_wrk: FOR ls_p = 1 To ll_mxbt_wrk IF ll_orderid = ll_orderid_wkp[ls_p] And ll_scid = ll_scid_wkp[ls_p] And ll_wrkgrpid = ll_wrkgrpid_arr[ls_p] THEN GOTO _find_end END IF NEXT ll_mxbt_wrk++ ll_orderid_wkp[ll_mxbt_wrk] = ll_orderid ll_scid_wkp[ll_mxbt_wrk] = ll_scid ll_wrkgrpid_arr[ll_mxbt_wrk] = ll_wrkgrpid _find_end: END IF // // UPDATE u_OrderMtrl_workgroup // SET u_OrderMtrl_workgroup.finishqty = s_qty.minsqty // FROM u_OrderMtrl_workgroup INNER JOIN // (SELECT scid, orderid, swkpid, workgroupid,MIN(sqty) AS minsqty // FROM (SELECT scid, orderid, swkpid,workgroupid, SUM(sflag) AS sqty, relname, // partname // FROM u_ordermtrl_workgroup_barcode // GROUP BY scid, orderid, swkpid, relname, partname,workgroupid) a // GROUP BY scid, orderid, swkpid,workgroupid) s_qty ON // u_OrderMtrl_workgroup.scid = s_qty.scid AND // u_OrderMtrl_workgroup.orderid = s_qty.orderid AND // u_OrderMtrl_workgroup.wrkGrpid = s_qty.swkpid and // u_OrderMtrl_workgroup.workgroupid = s_qty.workgroupid // WHERE (u_OrderMtrl_workgroup.scid = :ll_scid) // AND (u_OrderMtrl_workgroup.orderid = :ll_orderid) // And (u_OrderMtrl_workgroup.iflast = 0); // IF sqlca.SQLCode <> 0 THEN // ROLLBACK; // arg_msg = '更新工组进度失败,'+sqlca.SQLErrText // ll_fail++ // CONTINUE // END IF ELSE IF ld_packqty > 0 THEN UPDATE u_OrderMtrl_workgroup_date SET fqty = fqty + :ld_packqty WHERE scid = :ll_scid AND orderid = :ll_orderid AND wrkgrpid = :ll_wrkgrpid And fdate = :ldt_fdate; IF sqlca.SQLCode = 0 THEN IF sqlca.SQLNRows = 0 THEN INSERT INTO u_OrderMtrl_workgroup_date (scid,orderid,wrkgrpid,fdate,fqty) VALUES (:ll_scid,:ll_orderid,:ll_wrkgrpid,:ldt_fdate,:ld_packqty); IF sqlca.SQLCode <> 0 THEN arg_msg = '更新工组当天进度失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF END IF ELSE arg_msg = '更新工组当天进度失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF END IF UPDATE u_OrderMtrl_workgroup SET u_OrderMtrl_workgroup.finishqty = case when u_OrderMtrl_workgroup.finishqty + :ld_packqty > u_OrderMtrl_workgroup.orderqty then u_OrderMtrl_workgroup.orderqty else u_OrderMtrl_workgroup.finishqty + :ld_packqty END FROM u_OrderMtrl_workgroup INNER JOIN u_sc_workgroup ON u_OrderMtrl_workgroup.wrkGrpid = u_sc_workgroup.wrkGrpid WHERE (u_OrderMtrl_workgroup.scid = :ll_scid) AND (u_OrderMtrl_workgroup.orderid = :ll_orderid) AND (u_OrderMtrl_workgroup.iflast = 1) AND (u_sc_workgroup.jdtype = 6) And (u_OrderMtrl_workgroup.finishqty < u_OrderMtrl_workgroup.orderqty); IF sqlca.SQLCode <> 0 THEN arg_msg = '更新工组进度失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF END IF UPDATE u_OrderMtrl_workgroup SET finishflag = 1,finishdate = :server_dt WHERE orderid = :ll_orderid AND scid = :ll_scid And orderqty <= finishqty + stopqty; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新工组完成状态失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF COMMIT; ll_suc++ NEXT FOR ll_k = 1 To ll_mxbt_wrk SELECT MIN(sqty) INTO :ld_minsqty FROM (SELECT scid, orderid, swkpid, SUM(sflag) AS sqty, relname, partname FROM u_ordermtrl_workgroup_barcode WHERE scid = :ll_scid_wkp[ll_k] AND orderid = :ll_orderid_wkp[ll_k] AND swkpid = :ll_wrkgrpid_arr[ll_k] Group By scid, orderid, swkpid, relname, partname) a; IF sqlca.SQLCode <> 0 THEN ld_minsqty = 0 END IF IF IsNull(ld_minsqty) THEN ld_minsqty = 0 // SELECT sum(finishqty) // INTO :ld_finishqty // FROM u_OrderMtrl_workgroup // WHERE (u_OrderMtrl_workgroup.scid = :ll_scid_wkp[ll_k]) // AND (u_OrderMtrl_workgroup.orderid = :ll_orderid_wkp[ll_k]) // And (u_OrderMtrl_workgroup.wrkgrpid = :ll_wrkgrpid_arr[ll_k]); // IF sqlca.SQLCode <> 0 THEN // ld_finishqty = 0 // END IF // IF IsNull(ld_finishqty) THEN ld_finishqty = 0 // SELECT MIN(ptcnt) INTO :ld_finishqty FROM (SELECT scid, orderid, swkpid, relname, partname, SUM(ptflag) AS ptcnt FROM u_ordermtrl_workgroup_barcode WHERE (scid = :ll_scid_wkp[ll_k]) AND (orderid = :ll_orderid_wkp[ll_k]) AND (swkpid = :ll_wrkgrpid_arr[ll_k]) Group By scid, orderid, swkpid, relname, partname) b; IF sqlca.SQLCode <> 0 THEN ld_finishqty = 0 END IF ld_addqty = ld_minsqty - ld_finishqty IF ld_addqty > 0 THEN SELECT MIN(orderqty) INTO :ld_min_orderqty FROM u_OrderMtrl_workgroup WHERE (orderid = :ll_orderid_wkp[ll_k]) AND (scid = :ll_scid_wkp[ll_k]) And (wrkgrpid = :ll_wrkgrpid_arr[ll_k]); IF sqlca.SQLCode <> 0 THEN ld_min_orderqty = 0 SELECT sum(fqty) INTO :ld_sum_fqty FROM u_OrderMtrl_workgroup_date WHERE (orderid = :ll_orderid_wkp[ll_k]) AND (scid = :ll_scid_wkp[ll_k]) And (wrkgrpid = :ll_wrkgrpid_arr[ll_k]); IF sqlca.SQLCode <> 0 THEN ld_sum_fqty = 0 IF IsNull(ld_min_orderqty) THEN ld_min_orderqty = 0 IF IsNull(ld_sum_fqty) THEN ld_sum_fqty = 0 UPDATE u_OrderMtrl_workgroup_date SET fqty = case when :ld_sum_fqty + :ld_addqty > :ld_min_orderqty then :ld_min_orderqty else fqty + :ld_addqty END WHERE scid = :ll_scid_wkp[ll_k] AND orderid = :ll_orderid_wkp[ll_k] AND wrkgrpid = :ll_wrkgrpid_arr[ll_k] And fdate = :ldt_fdate; IF sqlca.SQLCode = 0 THEN IF sqlca.SQLNRows = 0 THEN INSERT INTO u_OrderMtrl_workgroup_date (scid,orderid,wrkgrpid,fdate,fqty) VALUES (:ll_scid_wkp[ll_k],:ll_orderid_wkp[ll_k],:ll_wrkgrpid_arr[ll_k],:ldt_fdate,:ld_addqty); IF sqlca.SQLCode <> 0 THEN arg_msg = '插入工组当天进度失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF END IF ELSE arg_msg = '更新工组当天进度失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF ds_relname.Retrieve(ll_scid_wkp[ll_k],ll_orderid_wkp[ll_k],ll_wrkgrpid_arr[ll_k]) FOR ll_m = 1 To ds_relname.RowCount() ls_relname = ds_relname.Object.relname[ll_m] ls_partname = ds_relname.Object.partname[ll_m] ds_mx.Retrieve(ll_scid_wkp[ll_k],ll_orderid_wkp[ll_k],ll_wrkgrpid_arr[ll_k],ls_relname,ls_partname) FOR ll_l = 1 To Int(ld_minsqty) ll_printid = ds_mx.Object.printid[ll_l] ll_pid = ds_mx.Object.pid[ll_l] IF ds_mx.Object.ptflag[ll_l] = 1 THEN CONTINUE UPDATE u_ordermtrl_workgroup_barcode SET ptflag = 1, ptdate = :ldt_fdate WHERE scid = :ll_scid_wkp[ll_k] AND orderid = :ll_orderid_wkp[ll_k] AND printid = :ll_printid And pid = :ll_pid; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新条码匹配标记失败,'+sqlca.SQLErrText ROLLBACK; ll_fail++ CONTINUE END IF NEXT NEXT END IF COMMIT; NEXT FOR ll_k = 1 To ll_mxbt ll_rowcnt = ds_update_wrk.Retrieve(ll_scid_arr[ll_k],ll_orderid_arr[ll_k]) IF ll_rowcnt > 0 THEN UPDATE u_OrderMtrl_workgroup SET u_OrderMtrl_workgroup.finishqty = 0, u_OrderMtrl_workgroup.finishdate = NULL FROM u_OrderMtrl_workgroup INNER JOIN u_sc_workgroup ON u_OrderMtrl_workgroup.wrkGrpid = u_sc_workgroup.wrkGrpid WHERE u_OrderMtrl_workgroup.scid = :ll_scid_arr[ll_k] AND u_OrderMtrl_workgroup.orderid = :ll_orderid_arr[ll_k] AND u_OrderMtrl_workgroup.iflast = 0 And (u_sc_workgroup.jdtype = 6); IF sqlca.SQLCode <> 0 THEN arg_msg = '更新工组进度失败,'+sqlca.SQLErrText ROLLBACK; GOTO _next_order END IF END IF FOR ll_i = 1 To ll_rowcnt ll_scid_ds = ds_update_wrk.Object.scid[ll_i] ll_orderid_ds = ds_update_wrk.Object.orderid[ll_i] ll_swkpid_ds = ds_update_wrk.Object.swkpid[ll_i] ll_workgroupid_ds = ds_update_wrk.Object.workgroupid[ll_i] ld_minsqty_ds = ds_update_wrk.Object.minsqty[ll_i] ll_row_wkp = ds_order_wkp.Retrieve(ll_scid_ds,ll_orderid_ds,ll_swkpid_ds,ll_workgroupid_ds) FOR ll_j = 1 To ll_row_wkp ll_workgroupid_up = ds_order_wkp.Object.workgroupid[ll_j] ll_owrkGrpid_up = ds_order_wkp.Object.owrkgrpid[ll_j] ls_wkpname_up = ds_order_wkp.Object.wkpname[ll_j] ld_orderqty_ds = ds_order_wkp.Object.orderqty[ll_j] ld_stopqty_ds = ds_order_wkp.Object.stopqty[ll_j] ld_finishqty_ds = ds_order_wkp.Object.finishqty[ll_j] IF ll_j = ll_row_wkp THEN ld_update_qty = ld_minsqty_ds ELSE IF ld_minsqty_ds > ld_orderqty_ds - ld_finishqty_ds - ld_stopqty_ds THEN ld_update_qty = ld_orderqty_ds - ld_finishqty_ds - ld_stopqty_ds ld_minsqty_ds = ld_minsqty_ds - (ld_orderqty_ds - ld_finishqty_ds - ld_stopqty_ds) ELSE ld_update_qty = ld_minsqty_ds ld_minsqty_ds = 0 END IF END IF UPDATE u_OrderMtrl_workgroup SET finishqty = case when finishqty + stopqty +:ld_update_qty > orderqty then orderqty else finishqty + :ld_update_qty END WHERE scid = :ll_scid_ds AND orderid = :ll_orderid_ds AND wrkgrpid = :ll_swkpid_ds AND owrkgrpid = :ll_owrkGrpid_up AND workgroupid = :ll_workgroupid_up And wkpname = :ls_wkpname_up; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新工组进度失败,'+sqlca.SQLErrText ROLLBACK; GOTO _next END IF IF ld_minsqty_ds = 0 THEN EXIT NEXT UPDATE u_OrderMtrl_workgroup SET finishflag = 1,finishdate = :server_dt WHERE orderid = :ll_orderid_ds AND scid = :ll_scid_ds And orderqty <= finishqty + stopqty; IF sqlca.SQLCode <> 0 THEN arg_msg = '更新工组完成状态失败,'+sqlca.SQLErrText ROLLBACK; GOTO _next END IF _next: NEXT COMMIT; _next_order: NEXT ext: Destroy ds_order_wkp Destroy ds_update_wrk Destroy ds_relname Destroy ds_mx Destroy ds_pda arg_cnt = ll_suc arg_failcnt = ll_fail RETURN rslt end function