FXApp/ws_objects/app_base.pbl.src/f_warebalc_cmpl_one.srf

$PBExportHeader$f_warebalc_cmpl_one.srf
global type f_warebalc_cmpl_one from function_object
end type

forward prototypes
global function integer f_warebalc_cmpl_one (long arg_storageid, long arg_balcdateint, long arg_mtrlwareid, ref string arg_msg, boolean arg_ifcommit, transaction commit_transaction)
end prototypes

global function integer f_warebalc_cmpl_one (long arg_storageid, long arg_balcdateint, long arg_mtrlwareid, ref string arg_msg, boolean arg_ifcommit, transaction commit_transaction);
Long rslt = 1
Int li_storagetype
Long ll_scid_storage
Long ll_balcdateint_cmpl_arr[], ll_cmpl_cnt
Long ll_balcdateint

Long cnt
Long ll_balcdateint_cmpl = 0 //要计算的月份结存日期
Long ll_balcdateint_last //= ? 前一个月份的结存日期

Long i
Decimal ld_newcost // 最新成本价
//Decimal ld_balcqty_last,ld_balcamt_last,ld_inamt,ld_inqty,ld_outamt,ld_outqty
//Decimal ld_ubalcqty_last,ld_uinqty,ld_uoutqty

Decimal ld_bgqty,ld_bgamt,ld_incqty,ld_incamt,ld_desqty,ld_desamt
Decimal ld_ubgqty,ld_uincqty,ld_udesqty
Long ll_mtrlid
Int li_ifunit
String ls_status,ls_pcode,ls_woodcode
String ls_mtrlcode,ls_msg

Decimal ld_balcqty_update,ld_balcamt_update
Decimal ld_desamt_update,ld_outcost_update

//Long m
//Long ll_mtrlid_last,ll_scid_last
//String ls_status_last,ls_woodcode_last,ls_pcode_last
//String ls_plancode_last,ls_mtrlcuscode_last,ls_location_last
//Int li_dxflag_last
//Long ll_sptid_last
//Long arg_mtrlwareid_last
//Decimal ld_qty_last,ld_amt_last

Decimal lde_add_desamt, lde_costamt_add, lde_addamt
Long ll_scid_add, ll_outwareid_add, ll_printid_add

Decimal ld_vincqty,ld_vincamt,ld_vdesqty,ld_vdesamt,ld_uvincqty,ld_uvdesqty

Decimal ld_vinqty,ld_vuinqty,ld_vinamt
Decimal ld_voutqty,ld_vuoutqty,ld_voutamt

IF arg_storageid = -1 THEN
	arg_msg = '请先选择仓库'
	GOTO ext
END IF

SELECT scid,storagetype
	INTO :ll_scid_storage,:li_storagetype
	FROM u_storage
	Where storageid = :arg_storageid
	using commit_transaction;
IF commit_transaction.SQLCode <> 0 THEN
	arg_msg = '查询仓库资料失败,操作取消'
	GOTO ext
END IF

SELECT count(*)
	INTO :cnt
	FROM u_inwaremx INNER JOIN
	u_inware ON u_inwaremx.scid = u_inware.scid AND
	u_inwaremx.inwareid = u_inware.inwareid
	WHERE (u_inware.flag = 1)
	AND  u_inware.balcdateint = :ll_balcdateint_cmpl
	AND  u_inwaremx.mtrlwareid = 0
	And u_inware.storageid = :arg_storageid
	using commit_transaction;
IF commit_transaction.SQLCode <> 0 THEN
	arg_msg = '查询进仓明细库存ID失败,'+commit_transaction.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt > 0 THEN
	arg_msg = '进仓明细存在非法库存ID,请先更新进仓明细库存ID'
	GOTO ext
END IF

SELECT  u_mtrldef.mtrlcode, u_warebalc.status, u_warebalc.woodcode,
	u_warebalc.pcode, u_mtrldef.ifunit
	INTO :ls_mtrlcode, :ls_status, :ls_woodcode, :ls_pcode, :li_ifunit
	FROM u_warebalc INNER JOIN
	u_mtrldef ON u_warebalc.mtrlid = u_mtrldef.mtrlid
	WHERE u_warebalc.storageid = :arg_storageid
	AND u_warebalc.balcdateint = :arg_balcdateint
	And u_warebalc.mtrlwareid = :arg_mtrlwareid
	using commit_transaction;
IF commit_transaction.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询结存信息失败,'+commit_transaction.SQLErrText
	GOTO ext
END IF

//确定要计算的范围
IF arg_balcdateint = 0 THEN
	ll_cmpl_cnt++
	ll_balcdateint_cmpl_arr[ll_cmpl_cnt] = arg_balcdateint
ELSE
	DECLARE waredate_cur CURSOR FOR
		SELECT distinct u_warebalc.balcdateint
		FROM u_warebalc
		WHERE ( storageid = :arg_storageid )
		AND balcdateint >= :arg_balcdateint
		Order By balcdateint 
		using commit_transaction;
		
	OPEN waredate_cur;
	FETCH waredate_cur Into :ll_balcdateint;
	DO WHILE commit_transaction.SQLCode = 0 //循环读取明细数据
		ll_cmpl_cnt++
		ll_balcdateint_cmpl_arr[ll_cmpl_cnt] = ll_balcdateint
		
		FETCH waredate_cur Into :ll_balcdateint;
	LOOP
	CLOSE waredate_cur;
	
	ll_cmpl_cnt++
	ll_balcdateint_cmpl_arr[ll_cmpl_cnt] = 0
END IF

ll_balcdateint_cmpl = ll_balcdateint_cmpl_arr[1] //按选择, 可以重算历史
SELECT max(balcdateint) INTO :ll_balcdateint_last
	FROM u_warebalc
	WHERE storageid = :arg_storageid
	AND ((:ll_balcdateint_cmpl = 0 AND balcdateint > 0)
	Or (:ll_balcdateint_cmpl > 0  And balcdateint > 0 And balcdateint < :ll_balcdateint_cmpl))
	using commit_transaction;
IF commit_transaction.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询上次结存失败,操作取消'
	GOTO ext
END IF

IF IsNull(ll_balcdateint_last) THEN ll_balcdateint_last = -1

FOR i = 1 To ll_cmpl_cnt
	ll_balcdateint_cmpl = ll_balcdateint_cmpl_arr[i]
	
	ls_msg = '期间:'+String(ll_balcdateint_cmpl)+',库存id:'+String(arg_mtrlwareid)+',物料:'+ls_mtrlcode+','+sys_option_change_status+':'+ls_status+','+sys_option_change_woodcode+':'+ls_woodcode+','+sys_option_change_pcode+':'+ls_pcode
	
	
	//查期初
	SELECT balcqty,balcamt,ubalcqty
		INTO :ld_bgqty,:ld_bgamt,:ld_ubgqty
		FROM u_warebalc
		WHERE storageid = :arg_storageid
		AND mtrlwareid = :arg_mtrlwareid
		And balcdateint = :ll_balcdateint_last
		using commit_transaction;
	IF commit_transaction.SQLCode = -1  THEN
		rslt = 0
		arg_msg = '查询上月结存失败,'+ls_msg+','+commit_transaction.SQLErrText
		GOTO ext
	ELSEIF commit_transaction.SQLCode = 100 THEN
		ld_bgqty = 0
		ld_bgamt = 0
		ld_ubgqty = 0
	END IF
	
	IF IsNull(ld_bgqty) THEN ld_bgqty = 0
	IF IsNull(ld_bgamt) THEN ld_bgamt = 0
	IF IsNull(ld_ubgqty) THEN ld_ubgqty = 0
	
	//查进仓
	SELECT isnull(sum(u_inwaremx.qty),0),
	isnull(sum(u_inwaremx.uqty),0),
	isnull(sum(round(u_inwaremx.cost * u_inwaremx.qty,2)),0),
		isnull(sum(case u_inwaremx.ifvqty when 0 then 0 else u_inwaremx.qty END ),0),
		isnull(sum(case u_inwaremx.ifvqty when 0 then 0 else u_inwaremx.uqty END ),0),
		isnull(sum(round(u_inwaremx.cost * case u_inwaremx.ifvqty when 0 then 0 else u_inwaremx.qty END,2)),0)
		INTO :ld_incqty,
		:ld_uincqty,
		:ld_incamt,
		:ld_vincqty,
		:ld_uvincqty,
		:ld_vincamt
		FROM u_inware,u_inwaremx
		WHERE u_inware.scid = u_inwaremx.scid
		AND u_inware.inwareid = u_inwaremx.inwareid
		AND u_inwaremx.mtrlwareid = :arg_mtrlwareid
		AND u_inware.storageid = :arg_storageid
		AND u_inware.balcdateint = :ll_balcdateint_cmpl
		And u_inware.flag = 1
		using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '查询本月进仓失败,'+ls_msg+','+commit_transaction.SQLErrText
		GOTO ext
	END IF
	IF IsNull(ld_incqty) THEN ld_incqty = 0
	IF IsNull(ld_incamt) THEN ld_incamt = 0
	IF IsNull(ld_uincqty) THEN ld_uincqty = 0
	IF IsNull(ld_vincqty) THEN ld_vincqty = 0
	IF IsNull(ld_vincamt) THEN ld_vincamt = 0
	IF IsNull(ld_uvincqty) THEN ld_uvincqty = 0
	
	//查出仓
	SELECT isnull(sum(u_outwaremx.qty),0),
	isnull(sum(u_outwaremx.uqty),0),
	isnull(sum(round(u_outwaremx.costamt,2)),0),
			isnull(sum(case u_outwaremx.ifvqty when 0 then 0 else u_outwaremx.qty END),0),
		isnull(sum(case u_outwaremx.ifvqty when 0 then 0 else u_outwaremx.uqty END),0),
		isnull(sum(round(case u_outwaremx.ifvqty when 0 then 0 else u_outwaremx.costamt END,2)),0)
		INTO :ld_desqty,
		:ld_udesqty,
		:ld_desamt,
		:ld_vdesqty,
		:ld_uvdesqty,
		:ld_vdesamt
		FROM u_outware,u_outwaremx
		WHERE u_outware.scid = u_outwaremx.scid
		AND u_outware.outwareid = u_outwaremx.outwareid
		AND u_outwaremx.mtrlwareid = :arg_mtrlwareid
		AND u_outware.storageid = :arg_storageid
		AND u_outware.balcdateint = :ll_balcdateint_cmpl
		And u_outware.flag = 1
		using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '查询本月出仓失败,'+ls_msg+','+commit_transaction.SQLErrText
		GOTO ext
	END IF
	IF IsNull(ld_desqty) THEN ld_desqty = 0
	IF IsNull(ld_desamt) THEN ld_desamt = 0
	IF IsNull(ld_udesqty) THEN ld_udesqty = 0
	IF IsNull(ld_vdesqty) THEN ld_vdesqty = 0
	IF IsNull(ld_vdesamt) THEN ld_vdesamt = 0
	IF IsNull(ld_uvdesqty) THEN ld_uvdesqty = 0
	
	
	IF ld_bgqty + ld_incqty <> 0 THEN
		ld_outcost_update = Round((ld_bgamt + ld_incamt) / (ld_bgqty + ld_incqty),sys_option_cost_dec)
	ELSEIF ld_desqty <> 0 And ld_desamt <> 0 THEN //有可能是先退仓, 再出仓
		ld_outcost_update = Round(ld_desamt / ld_desqty,sys_option_cost_dec)
	ELSE
		ld_outcost_update = 0
	END IF
	
	IF ld_outcost_update < 0 THEN ld_outcost_update = 0
	
	
	UPDATE u_outwaremx
		SET u_outwaremx.cost = :ld_outcost_update,
		u_outwaremx.costamt = round(u_outwaremx.qty * :ld_outcost_update,2)
		FROM u_outwaremx INNER JOIN
		u_outware ON u_outwaremx.scid = u_outware.scid AND
		u_outwaremx.outwareid = u_outware.outwareid
		WHERE u_outwaremx.mtrlwareid = :arg_mtrlwareid
		AND u_outware.storageid = :arg_storageid
		AND u_outware.scid = :ll_scid_storage
		And u_outware.balcdateint  = :ll_balcdateint_cmpl
		using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '更新未结存的出仓单金额失败,'+ls_msg+','+commit_transaction.SQLErrText
		GOTO ext
	END IF
	
	//再读一次数，防止小数点的误差
	SELECT isnull(sum(round(u_outwaremx.costamt,2)),0)
		INTO :ld_desamt_update
		FROM u_outware,u_outwaremx
		WHERE u_outware.scid = u_outwaremx.scid
		AND u_outware.outwareid = u_outwaremx.outwareid
		AND u_outwaremx.mtrlwareid = :arg_mtrlwareid
		AND u_outware.storageid = :arg_storageid
		AND u_outware.balcdateint = :ll_balcdateint_cmpl
		And u_outware.flag = 1
		using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '重新查询本月出仓成本金额失败,'+ls_msg+','+commit_transaction.SQLErrText
		GOTO ext
	END IF
	
	//修正结余为负数; 结余数=0,结余金额<>0;的情况
	If (ld_bgqty + ld_incqty - ld_desqty = 0 And ld_bgamt + ld_incamt - ld_desamt_update <> 0) Or ld_bgamt + ld_incamt - ld_desamt_update < 0  THEN
		lde_add_desamt = ld_bgamt + ld_incamt - ld_desamt_update
		
		DECLARE outware CURSOR FOR
			SELECT  u_outwaremx.scid, u_outwaremx.outwareid, u_outwaremx.printid, u_outwaremx.costamt
			FROM u_outware,u_outwaremx
			WHERE u_outware.scid = u_outwaremx.scid
			AND u_outware.outwareid = u_outwaremx.outwareid
			AND u_outwaremx.mtrlwareid = :arg_mtrlwareid
			AND u_outware.storageid = :arg_storageid
			AND u_outware.balcdateint = :ll_balcdateint_cmpl
			And u_outware.flag = 1
			using commit_transaction;
		OPEN outware;
		FETCH outware  Into :ll_scid_add, :ll_outwareid_add, :ll_printid_add, :lde_costamt_add;
		DO WHILE commit_transaction.SQLCode = 0
			IF lde_add_desamt > 0 THEN
				
				lde_addamt = lde_costamt_add
				lde_costamt_add = 0
				
			ELSE
				IF lde_costamt_add > Abs(lde_add_desamt) THEN
					lde_addamt = lde_add_desamt
					lde_add_desamt = 0
				ELSE
					lde_addamt = 0 - lde_costamt_add
					lde_add_desamt = lde_add_desamt + lde_costamt_add
				END IF
				
			END IF
			
			UPDATE u_outwaremx
				SET costamt = costamt + :lde_addamt
				WHERE scid = :ll_scid_add
				AND outwareid = :ll_outwareid_add
				And printid = :ll_printid_add
				using commit_transaction;
			IF commit_transaction.SQLCode <> 0 THEN
				rslt = 0
				arg_msg = "修正出仓明细金额失败,"+commit_transaction.SQLErrText
				GOTO ext
			END IF
			
			IF lde_add_desamt = 0 THEN EXIT //完成修正
			
			FETCH outware  Into :ll_scid_add, :ll_outwareid_add, :ll_printid_add, :lde_costamt_add;
		LOOP
		
		CLOSE outware;
		
		ld_desamt_update = ld_bgamt + ld_incamt
	END IF
	
	//yyx2012-10-27
	ld_balcqty_update = ld_bgqty + ld_incqty - ld_desqty
	ld_balcamt_update = ld_bgamt + ld_incamt - ld_desamt_update
	
	IF ld_balcqty_update <> 0 THEN
		ld_newcost = Round(ld_balcamt_update/ld_balcqty_update,sys_option_cost_dec)
	ELSE
		ld_newcost = ld_outcost_update
	END IF
	
	//yyx2012-10-27_end
	
	IF li_ifunit = 0 THEN
		ld_ubgqty = 0
		ld_uincqty = 0
		ld_udesqty = 0
	END IF
	
	//更新期间出金额,结存金额
	UPDATE u_warebalc
		SET bgqty = :ld_bgqty,
		bgamt = :ld_bgamt,
		incqty = :ld_incqty,
		incamt = :ld_incamt,
		desqty = :ld_desqty,
		desamt = :ld_desamt_update,
		balcqty = :ld_balcqty_update,
		balcamt = :ld_balcamt_update,
		ubgqty = :ld_ubgqty,
		uincqty = :ld_uincqty,
		udesqty = :ld_udesqty,
		ubalcqty = :ld_ubgqty + :ld_uincqty - :ld_udesqty,
				vincqty = :ld_vincqty,
		vdesqty = :ld_vdesqty,
		vincamt = :ld_vincamt,
		vdesamt = :ld_vdesamt,
		uvincqty = :ld_uvincqty,
		uvdesqty = :ld_uvdesqty
		WHERE mtrlwareid = :arg_mtrlwareid
		AND balcdateint = :ll_balcdateint_cmpl
		And scid = :ll_scid_storage;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '更新结存失败,'+ls_msg+','+commit_transaction.SQLErrText
		GOTO ext
	END IF
	
	IF ll_balcdateint_cmpl = 0 THEN
		UPDATE u_mtrlware
			SET cost = :ld_newcost,
			noallocqty = :ld_balcqty_update,
			unoallocqty = :ld_ubgqty + :ld_uincqty - :ld_udesqty,
			wareamt = :ld_balcamt_update
			WHERE mtrlwareid = :arg_mtrlwareid
			AND Storageid = :arg_storageid
			And scid = :ll_scid_storage
			using commit_transaction;
		IF commit_transaction.SQLCode <> 0 THEN
			rslt = 0
			arg_msg = '更新库存金额失败,'+ls_msg+','+commit_transaction.SQLErrText
			GOTO ext
		END IF
	END IF
	
	
	//更新分类结存
	DECLARE sp_warebalc_cmpl PROCEDURE FOR sp_warebalc_cmpl
		@scid = :ll_scid_storage,
		@storageid = :arg_storageid,
		@balcdateint = :ll_balcdateint_cmpl,
		@balcdateintlast = :ll_balcdateint_last,
		@mtrlwareid = :arg_mtrlwareid ;
		
	EXECUTE   sp_warebalc_cmpl ;
	CLOSE sp_warebalc_cmpl;
	
	
	ll_balcdateint_last = ll_balcdateint_cmpl //为下个循环准备
NEXT


ext:
IF rslt = 0 THEN
	ROLLBACK using commit_transaction;
ELSEIF arg_ifcommit THEN
	COMMIT using commit_transaction;
END IF

RETURN rslt





























end function