FXApp/ws_objects/l1_uo.pbl.src/uo_warebalc_cmpl.sru

$PBExportHeader$uo_warebalc_cmpl.sru
forward
global type uo_warebalc_cmpl from nonvisualobject
end type
end forward

global type uo_warebalc_cmpl from nonvisualobject
end type
global uo_warebalc_cmpl uo_warebalc_cmpl

type variables
Int uo_option_warebalc_checksale
int uo_option_wkpmtrlware
int uo_option_outware_scll_new_cj_mtrlware
end variables

forward prototypes
public function integer uof_warebalc_cmplamt (long arg_storageid, long arg_balcdateint, long arg_mtrlwareid, ref string arg_msg, boolean arg_ifcommit, transaction commit_transaction)
public function integer uof_get_planprice (long arg_mtrlid, string arg_status, string arg_woodcode, string arg_pcode, ref decimal arg_planprice, ref string arg_msg)
public function integer uof_cmpl_warebalc_amt (long cur_storageid, long ll_balcdateint_cmpl, ref string arg_msg)
public function integer uof_warebalc_cmplamt_all (long arg_storageid, long arg_balcdateint, ref string arg_msg, boolean arg_ifcommit, transaction commit_transaction)
end prototypes

public function integer uof_warebalc_cmplamt (long arg_storageid, long arg_balcdateint, long arg_mtrlwareid, ref string arg_msg, boolean arg_ifcommit, transaction commit_transaction);//0.采购收货单、成本价调整单的成本价不调整；采购退货单考虑【311】选项判断
//1.查找最后成本调整时间,无则用上月结存时间;查上月结存时间(无则询问确定?怕是截数了的);查下月结存时间
//2.依次更新外协收货单成本价、更新其他进仓单成本价（进仓正数）、更新其他进仓单成本价（进仓负数）
//3.更新出仓单成本价
//4.更新结存金额,下月期初金额;如果是最后结存,则更新库存金额

Long rslt = 1
Int li_storagetype
Long ll_scid_storage
Long ll_balcdateint_cmpl_arr[], ll_cmpl_cnt
Long ll_balcdateint

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

Long i, j, cnt
Decimal ld_newcost // 最新成本价

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, li_BalcCmplNotUpdate
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

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, ld_vdesamt_update


DateTime ldt_Aduitingdate, ldt_chkdate
String ls_pfcode
Decimal lde_planprice, lde_cost_in, lde_cost, lde_mtrlprice, lde_planprice_son
Long ll_sonmtrlid[]
Decimal ld_qty[]

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

//如果是算历史,但后期有成本调整的,同样不算
SELECT MAX(u_inware.auditingdate)
	INTO :ldt_Aduitingdate
	FROM u_inware INNER JOIN
	u_inwaremx ON u_inware.scid = u_inwaremx.scid AND
	u_inware.inwareid = u_inwaremx.inwareid
	WHERE (u_inware.storageid = :arg_storageid)
	AND (u_inwaremx.mtrlwareid = :arg_mtrlwareid)
	AND  (u_inware.sptname = '调整成本价(负单)' OR
	u_inware.sptname = '更新成本价(正单)')
	Using commit_transaction;
IF commit_transaction.SQLCode <> 0 THEN
	rslt = 0
	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
	rslt = 0
	arg_msg = '查询仓库资料失败,操作取消'
	GOTO ext
END IF

SELECT  u_mtrldef.mtrlid, u_mtrldef.mtrlcode, u_warebalc.status, u_warebalc.woodcode,
	u_warebalc.pcode, u_mtrldef.ifunit, u_mtrldef.BalcCmplNotUpdate
	INTO :ll_mtrlid, :ls_mtrlcode, :ls_status, :ls_woodcode, :ls_pcode, :li_ifunit, :li_BalcCmplNotUpdate
	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 uof_get_planprice(ll_mtrlid, ls_status,ls_woodcode, ls_pcode, lde_planprice, arg_msg) = 0 THEN
	rslt = 0
	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
		AND mtrlwareid = :arg_mtrlwareid
		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 mtrlwareid = :arg_mtrlwareid
	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]
	
	IF IsNull(ldt_Aduitingdate) THEN
		IF ll_balcdateint_last = -1 THEN
			ldt_chkdate = DateTime(2000-01-01,23:59:59)
		ELSE
			ldt_chkdate = DateTime(Date(Left(String(ll_balcdateint_last),4)+'-'+Mid(String(ll_balcdateint_last), 5, 2)+'-'+Right(String(ll_balcdateint_last),2)),23:59:59)
		END IF
	ELSE
		ldt_chkdate = ldt_Aduitingdate
	END IF
	
	IF ll_balcdateint_cmpl = 0 THEN
		ls_msg = '期间:本期'
	ELSE
		ls_msg = '期间:'+String(ll_balcdateint_cmpl)
	END IF
	ls_msg += ',物料:'+ls_mtrlcode+ ',库存id:'+String(arg_mtrlwareid)
	IF ls_status <> "" THEN
		ls_msg += ','+sys_option_change_status+':'+ls_status
	END IF
	IF ls_woodcode <> "" THEN
		ls_msg += ','+sys_option_change_woodcode+':'+ls_woodcode
	END IF
	IF ls_pcode <> "" THEN
		ls_msg += ','+sys_option_change_pcode+':'+ls_pcode
	END IF
	
	//查期初
	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+'~r~n'+'查询上月结存失败,'+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
	IF ld_bgqty = 0 THEN ld_bgamt = 0
	
	//0.更新进仓costamt
	UPDATE u_inwaremx
		SET u_inwaremx.costamt = round(u_inwaremx.price * u_inwaremx.qty, 2)
		FROM u_inware INNER JOIN
		u_inwaremx ON u_inware.scid = u_inwaremx.scid AND
		u_inware.inwareid = u_inwaremx.inwareid
		WHERE (u_inwaremx.mtrlwareid = :arg_mtrlwareid)
		AND (u_inware.balcdateint = :ll_balcdateint_cmpl)
		Using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = ls_msg+'~r~n'+'更新进仓单成本金额失败,'+commit_transaction.SQLErrText
		GOTO ext
	END IF
	
	//1.更新外协收货单单价(billtype = 4)
	//有外协单的才查,提搞速度
	SELECT count(*) INTO :cnt
		FROM ow_wfjgmx_in_aft
		WHERE EXISTS
		(SELECT 1
		FROM (SELECT u_inware.scid, u_inware.relid, u_inwaremx.printid
		FROM u_inware INNER JOIN
		u_inwaremx ON u_inware.scid = u_inwaremx.scid AND
		u_inware.inwareid = u_inwaremx.inwareid
		WHERE (u_inware.billtype = 4)
		AND (u_inwaremx.mtrlwareid = :arg_mtrlwareid)
		AND (u_inware.auditingdate > :ldt_chkdate)
		AND (u_inware.balcdateint = :ll_balcdateint_cmpl)) a
		WHERE a.scid = ow_wfjgmx_in_aft.scid AND
		a.relid = ow_wfjgmx_in_aft.inwareid AND
		a.printid = ow_wfjgmx_in_aft.printid)
		Using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = ls_msg+'~r~n'+'更新外协收货单材料成本价失败,'+commit_transaction.SQLErrText
		GOTO ext
	END IF
	
	IF cnt > 0 THEN
		ls_pfcode = f_get_dft_pf(ll_mtrlid)
		//	
		j = 1
		DECLARE cur_inmx CURSOR FOR
			SELECT u_PrdPF.SonMtrlid,
			u_prdpf.Sonscale / (1 - u_PrdPF.sonloss) + u_PrdPF.SonDECLoss
			FROM u_PrdPF
			WHERE ( u_PrdPF.mtrlid = :ll_mtrlid )
			And ( u_PrdPF.pfcode = :ls_pfcode );
			
		OPEN cur_inmx;
		FETCH cur_inmx Into :ll_sonmtrlid[j],:ld_qty[j];
		DO WHILE sqlca.SQLCode = 0
			j++
			FETCH cur_inmx Into :ll_sonmtrlid[j],:ld_qty[j];
		LOOP
		CLOSE cur_inmx;
		
		cnt = j - 1
		
		lde_mtrlprice = 0
		FOR j = 1 To cnt
			IF uof_get_planprice(ll_sonmtrlid[j], '','', '', lde_planprice_son, arg_msg) = 0 THEN
				rslt = 0
				GOTO ext
			END IF
			
			lde_mtrlprice += lde_planprice_son * ld_qty[j]
		NEXT
		
		//更新外协收货单材料成本价
		UPDATE ow_wfjgmx_in_aft
			SET fprice = :lde_mtrlprice,
			price = :lde_mtrlprice + case when qty = 0 then 0 else round(jgprice * uqty / qty, 5) * rebate END
			WHERE EXISTS
			(SELECT 1
			FROM (SELECT u_inware.scid, u_inware.relid, u_inwaremx.printid
			FROM u_inware INNER JOIN
			u_inwaremx ON u_inware.scid = u_inwaremx.scid AND
			u_inware.inwareid = u_inwaremx.inwareid
			WHERE (u_inware.billtype = 4)
			AND (u_inwaremx.mtrlwareid = :arg_mtrlwareid)
			AND (u_inware.auditingdate > :ldt_chkdate)
			AND (u_inware.balcdateint = :ll_balcdateint_cmpl)) a
			WHERE a.scid = ow_wfjgmx_in_aft.scid AND
			a.relid = ow_wfjgmx_in_aft.inwareid AND
			a.printid = ow_wfjgmx_in_aft.printid)
			Using commit_transaction;
		IF commit_transaction.SQLCode <> 0 THEN
			rslt = 0
			arg_msg = ls_msg+'~r~n'+'更新外协收货单材料成本价失败,'+commit_transaction.SQLErrText
			GOTO ext
		END IF
		
		//更新外协收货单对应进仓单成本价
		UPDATE u_inwaremx
			SET u_inwaremx.fprice = ow_wfjgmx_in_aft.fprice * ow_wfjgmx_in_aft.rate + ow_wfjgmx_in_aft.jgprice * ow_wfjgmx_in_aft.rebate,
			u_inwaremx.planprice = ow_wfjgmx_in_aft.fprice * ow_wfjgmx_in_aft.rate + ow_wfjgmx_in_aft.jgprice * ow_wfjgmx_in_aft.rebate,
			u_inwaremx.price = ow_wfjgmx_in_aft.fprice * ow_wfjgmx_in_aft.rate + ow_wfjgmx_in_aft.jgprice * ow_wfjgmx_in_aft.rebate,
			u_inwaremx.cost = ow_wfjgmx_in_aft.fprice * ow_wfjgmx_in_aft.rate + ow_wfjgmx_in_aft.jgprice * ow_wfjgmx_in_aft.rebate,
			u_inwaremx.uprice = ( ow_wfjgmx_in_aft.fprice * ow_wfjgmx_in_aft.rate + ow_wfjgmx_in_aft.jgprice * ow_wfjgmx_in_aft.rebate) /u_inwaremx.rate,
			u_inwaremx.costamt = round(( ow_wfjgmx_in_aft.fprice * ow_wfjgmx_in_aft.rate + ow_wfjgmx_in_aft.jgprice * ow_wfjgmx_in_aft.rebate) * u_inwaremx.qty, 2)
			FROM u_inware INNER JOIN
			u_inwaremx ON u_inware.scid = u_inwaremx.scid AND
			u_inware.inwareid = u_inwaremx.inwareid INNER JOIN
			ow_wfjgmx_in_aft ON u_inware.scid = ow_wfjgmx_in_aft.scid AND
			u_inware.relid = ow_wfjgmx_in_aft.inwareid AND
			u_inwaremx.printid = ow_wfjgmx_in_aft.printid
			WHERE (u_inware.billtype = 4)
			AND (u_inwaremx.mtrlwareid = :arg_mtrlwareid)
			AND (u_inware.auditingdate > :ldt_chkdate)
			AND (u_inware.balcdateint = :ll_balcdateint_cmpl)
			Using commit_transaction;
		IF commit_transaction.SQLCode <> 0 THEN
			rslt = 0
			arg_msg = ls_msg+'~r~n'+'更新外协收货单对应进仓单成本价失败,'+commit_transaction.SQLErrText
			GOTO ext
		END IF
	END IF
	//,23,25,
	//2.更新其他正数进仓单价(qty > 0 billtype <> 1 billtype <> 4)
	IF li_BalcCmplNotUpdate = 0 THEN
		UPDATE u_inwaremx
			SET u_inwaremx.fprice = :lde_planprice,
			u_inwaremx.planprice = :lde_planprice,
			u_inwaremx.price = :lde_planprice,
			u_inwaremx.cost = :lde_planprice,
			u_inwaremx.uprice = :lde_planprice /u_inwaremx.rate,
			u_inwaremx.costamt = round(:lde_planprice * u_inwaremx.qty, 2)
			FROM u_inware INNER JOIN
			u_inwaremx ON u_inware.scid = u_inwaremx.scid AND
			u_inware.inwareid = u_inwaremx.inwareid
			WHERE (u_inwaremx.qty > 0) AND (u_inware.billtype <> 1 AND u_inware.billtype <> 4)
			AND (u_inwaremx.mtrlwareid = :arg_mtrlwareid)
			AND (u_inware.auditingdate > :ldt_chkdate)
			AND (u_inware.balcdateint = :ll_balcdateint_cmpl)
			Using commit_transaction;
		IF commit_transaction.SQLCode <> 0 THEN
			rslt = 0
			arg_msg = ls_msg+'~r~n'+'更新非采购外协的正数进仓单成本价失败,'+commit_transaction.SQLErrText
			GOTO ext
		END IF
	END IF
	
	//处理一次带V的数据
		//查正数进仓
		SELECT isnull(sum(u_inwaremx.qty),0),
			isnull(sum(u_inwaremx.uqty),0),
			isnull(sum(u_inwaremx.costamt),0)
			INTO 	: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
			AND u_inwaremx.qty > 0
			AND u_inwaremx.ifvqty = 0
			Using commit_transaction;
		IF commit_transaction.SQLCode <> 0 THEN
			rslt = 0
			arg_msg = ls_msg+'~r~n'+'查询正数进仓信息失败,'+ls_msg+','+commit_transaction.SQLErrText
			GOTO ext
		END IF
		
		IF IsNull(ld_vincqty) THEN ld_vincqty = 0
		IF IsNull(ld_vincamt) THEN ld_vincamt = 0
		IF IsNull(ld_uvincqty) THEN ld_uvincqty = 0
		
		IF ld_bgqty + ld_vincqty <> 0 And ld_bgamt + ld_vincamt <> 0 THEN
			lde_cost_in = Round((ld_bgamt + ld_vincamt) / (ld_bgqty + ld_vincqty),sys_option_cost_dec)
		ELSE
			lde_cost_in = lde_planprice
		END IF
		
		UPDATE u_inwaremx
			SET u_inwaremx.fprice = :lde_cost_in,
			u_inwaremx.planprice = :lde_cost_in,
			u_inwaremx.price = :lde_cost_in,
			u_inwaremx.cost = :lde_cost_in,
			u_inwaremx.uprice = :lde_cost_in /u_inwaremx.rate,
			u_inwaremx.costamt = round(:lde_cost_in * u_inwaremx.qty, 2)
			FROM u_inware INNER JOIN
			u_inwaremx ON u_inware.scid = u_inwaremx.scid AND
			u_inware.inwareid = u_inwaremx.inwareid
			WHERE (u_inwaremx.qty > 0) AND (u_inware.billtype <> 1 AND u_inware.billtype <> 4)
			AND (u_inwaremx.mtrlwareid = :arg_mtrlwareid)
			AND (u_inware.auditingdate > :ldt_chkdate)
			AND (u_inware.balcdateint = :ll_balcdateint_cmpl)
			AND (u_inwaremx.ifvqty = 1)
			Using commit_transaction;
		IF commit_transaction.SQLCode <> 0 THEN
			rslt = 0
			arg_msg = ls_msg+'~r~n'+'更新非采购外协的正数进仓单成本价失败,'+commit_transaction.SQLErrText
			GOTO ext
		END IF
		
	//END IF
	
	//3.更新其他负数进仓单价(qty < 0 billtype <> 1 billtype <> 4)
	
	//查正数进仓
	SELECT isnull(sum(u_inwaremx.qty),0),
		isnull(sum(u_inwaremx.uqty),0),
		isnull(sum(u_inwaremx.costamt),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(case u_inwaremx.ifvqty when 0 then 0 else u_inwaremx.costamt 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
		AND u_inwaremx.qty > 0
		Using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = ls_msg+'~r~n'+'查询正数进仓信息失败,'+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
	
	
	
	IF ld_bgqty + ld_incqty <> 0 And ld_bgamt + ld_incamt <> 0 THEN
		lde_cost_in = Round((ld_bgamt + ld_incamt) / (ld_bgqty + ld_incqty),sys_option_cost_dec)
	ELSE
		lde_cost_in = lde_planprice
	END IF
	IF lde_cost_in < 0 THEN lde_cost_in = lde_planprice
	
	UPDATE u_inwaremx
		SET u_inwaremx.fprice = :lde_cost_in,
		u_inwaremx.planprice = :lde_cost_in,
		u_inwaremx.price = :lde_cost_in,
		u_inwaremx.cost = :lde_cost_in,
		u_inwaremx.uprice = :lde_cost_in /u_inwaremx.rate,
		u_inwaremx.costamt = round(:lde_cost_in * u_inwaremx.qty, 2)
		FROM u_inware INNER JOIN
		u_inwaremx ON u_inware.scid = u_inwaremx.scid AND
		u_inware.inwareid = u_inwaremx.inwareid
		WHERE (u_inwaremx.qty < 0) AND (u_inware.billtype <> 1) AND (u_inware.billtype <> 4)
		AND (u_inwaremx.mtrlwareid = :arg_mtrlwareid)
		AND (u_inware.auditingdate > :ldt_chkdate)
		AND (u_inware.balcdateint = :ll_balcdateint_cmpl)
		Using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = ls_msg+'~r~n'+'更新非采购外协的负数进仓单成本价失败,'+commit_transaction.SQLErrText
		GOTO ext
	END IF
	
	IF sys_option_buyth_use_price_update_wareamt = 0 THEN
		UPDATE u_inwaremx
			SET u_inwaremx.planprice = :lde_cost_in,
			u_inwaremx.cost = :lde_cost_in,
			u_inwaremx.costamt = round(:lde_cost_in * u_inwaremx.qty, 2)
			FROM u_inware INNER JOIN
			u_inwaremx ON u_inware.scid = u_inwaremx.scid AND
			u_inware.inwareid = u_inwaremx.inwareid
			WHERE (u_inwaremx.qty < 0) AND (u_inware.billtype = 1)
			AND (u_inwaremx.mtrlwareid = :arg_mtrlwareid)
			AND (u_inware.auditingdate > :ldt_chkdate)
			AND (u_inware.balcdateint = :ll_balcdateint_cmpl)
			Using commit_transaction;
		IF commit_transaction.SQLCode <> 0 THEN
			rslt = 0
			arg_msg = ls_msg+'~r~n'+'更新采购退货单成本价失败,'+commit_transaction.SQLErrText
			GOTO ext
		END IF
	END IF
	
	//4.更新出仓单价
	
	
	//查进仓
	SELECT isnull(sum(u_inwaremx.qty),0),
		isnull(sum(u_inwaremx.uqty),0),
		isnull(sum(u_inwaremx.costamt),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(case u_inwaremx.ifvqty when 0 then 0 else u_inwaremx.costamt END),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+'~r~n'+'查询进仓信息失败,'+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(case u_outwaremx.ifvqty when 0 then 0 else u_outwaremx.costamt END),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+'~r~n'+'查询出仓信息失败,'+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
	
	
	//	SELECT count(*)
	//		INTO :cnt
	//		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
	//		AND (u_outware.auditingdate > :ldt_chkdate)
	//		Using commit_transaction;
	//	IF commit_transaction.SQLCode <> 0 THEN
	//		rslt = 0
	//		arg_msg = ls_msg+'~r~n'+'更新出仓单成本价失败,'+commit_transaction.SQLErrText
	//		GOTO ext
	//	END IF
	//	
	//	IF cnt > 0 THEN
	IF ld_bgqty + ld_incqty <> 0 And ld_bgamt + ld_incamt <> 0 THEN
		lde_cost = Round((ld_bgamt + ld_incamt) / (ld_bgqty + ld_incqty),sys_option_cost_dec)
	ELSE
		lde_cost = lde_planprice
	END IF
	
	IF lde_cost < 0 THEN lde_cost = lde_planprice
	
	UPDATE u_outwaremx
		SET u_outwaremx.cost = :lde_cost,
		u_outwaremx.costamt = round(u_outwaremx.qty * :lde_cost,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
		AND (u_outware.auditingdate > :ldt_chkdate)
		Using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = ls_msg+'~r~n'+'更新出仓单成本价失败,'+commit_transaction.SQLErrText
		GOTO ext
	END IF
	//	END IF
	//5.更新结存(库存)金额
	
	//再读一次数，防止小数点的误差
	SELECT isnull(sum(round(u_outwaremx.costamt,2)),0),
		isnull(sum(case u_outwaremx.ifvqty when 0 then 0 else u_outwaremx.costamt END),0)
		INTO :ld_desamt_update, :ld_vdesamt_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+'~r~n'+'重新查询本月出仓成本金额失败,'+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_add_desamt
				lde_add_desamt = 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 = ls_msg+'~r~n'+"修正出仓明细金额失败,"+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
	
	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_balcamt_update = 0
	
	IF ld_balcqty_update <> 0 THEN
		ld_newcost = Round(ld_balcamt_update/ld_balcqty_update,sys_option_cost_dec)
	ELSE
		ld_newcost = lde_planprice
	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 bgamt = :ld_bgamt,
		incamt = :ld_incamt,
		desamt = :ld_desamt_update,
		balcamt = :ld_balcamt_update,
		vincamt = :ld_vincamt,
		vdesamt = :ld_vdesamt_update
		WHERE mtrlwareid = :arg_mtrlwareid
		AND balcdateint = :ll_balcdateint_cmpl
		AND scid = :ll_scid_storage
		Using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = ls_msg+'~r~n'+'更新结存失败,'+commit_transaction.SQLErrText
		GOTO ext
	END IF
	
	IF ll_balcdateint_cmpl = 0 THEN
		UPDATE u_mtrlware
			SET cost = :ld_newcost,
			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+'~r~n'+'更新库存金额失败,'+commit_transaction.SQLErrText
			GOTO ext
		END IF
	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
	//		Using commit_transaction;
	//	IF commit_transaction.SQLCode <> 0 THEN
	//		rslt = 0
	//		arg_msg = ls_msg+'~r~n'+'更新结存失败,'+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+'~r~n'+'更新库存金额失败,'+commit_transaction.SQLErrText
	//			GOTO ext
	//		END IF
	//	END IF
	
	
	
	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

public function integer uof_get_planprice (long arg_mtrlid, string arg_status, string arg_woodcode, string arg_pcode, ref decimal arg_planprice, ref string arg_msg);Int rslt = 1
Decimal lde_planprice,ld_price_sh,ld_price_bj
Int li_mtrlorigin

SELECT mtrlorigin INTO :li_mtrlorigin
	FROM u_mtrldef
	WHERE u_mtrldef.mtrlid = :arg_mtrlid
	Using sqlca;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询物料计划价失败,'+sqlca.SQLErrText
	GOTO ext
END IF

//采购类,如果最新价为0,取最新报价,
IF li_mtrlorigin = 2 THEN
	SELECT top 1 v_maxprice_sptprice.price,v_maxprice_sptprice.price_bj
		INTO :ld_price_sh,:ld_price_bj
		FROM  v_maxprice_sptprice
		WHERE v_maxprice_sptprice.mtrlid = :arg_mtrlid
		Using sqlca;
	IF sqlca.SQLCode = -1 THEN
		rslt = 0
		arg_msg = '查询采购物料最新价失败,'+sqlca.SQLErrText
		GOTO ext
	ELSEIF sqlca.SQLCode = 100 THEN
		lde_planprice = 0
	ELSEIF sqlca.SQLCode = 0 THEN
		IF ld_price_sh > 0 THEN
			lde_planprice = ld_price_sh
		ELSE
			IF ld_price_bj > 0 THEN
				lde_planprice = ld_price_bj
			ELSE
				lde_planprice = 0
			END IF
		END IF
	END IF
END IF

IF lde_planprice = 0 THEN
	SELECT top 1 dbo.f_get_planprice_mtrl(:arg_mtrlid,:arg_status,:arg_woodcode, :arg_pcode)
		INTO :lde_planprice
		FROM u_user
		WHERE empid = 0
		Using sqlca;
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '查询物料计划价失败,'+sqlca.SQLErrText
		GOTO ext
	END IF
END IF

arg_planprice = lde_planprice
ext:
RETURN rslt





end function

public function integer uof_cmpl_warebalc_amt (long cur_storageid, long ll_balcdateint_cmpl, ref string arg_msg);Long rslt = 1

IF cur_storageid <= 0 THEN
	rslt = 0
	arg_msg = '请先选择仓库'
	RETURN rslt
END IF

Long cnt

Long ll_i = 0
Long ll_mtrlwareid, arr_mtrlwareid[]

SELECT count(0)
	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 = :cur_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询进仓明细库存ID失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

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

DECLARE cur1 CURSOR FOR 
	SELECT u_warebalc.mtrlwareid
	FROM u_mtrldef INNER JOIN
	u_warebalc ON u_mtrldef.mtrlid = u_warebalc.mtrlid
	WHERE (u_warebalc.Storageid = :cur_storageid)
	And (u_warebalc.balcdateint = :ll_balcdateint_cmpl);
	
OPEN cur1;
FETCH cur1 Into :ll_mtrlwareid;
DO WHILE sqlca.SQLCode = 0
	ll_i++
	arr_mtrlwareid[ll_i] = ll_mtrlwareid
	FETCH cur1 Into :ll_mtrlwareid;
LOOP
CLOSE cur1;

FOR ll_i = 1 To UpperBound(arr_mtrlwareid)
	ll_mtrlwareid = arr_mtrlwareid[ll_i]
	IF uof_warebalc_cmplamt(cur_storageid, ll_balcdateint_cmpl, ll_mtrlwareid, arg_msg, True, sqlca) = 0 THEN
		rslt = 0
		GOTO ext
	END IF
NEXT


ext:
RETURN rslt

end function

public function integer uof_warebalc_cmplamt_all (long arg_storageid, long arg_balcdateint, ref string arg_msg, boolean arg_ifcommit, transaction commit_transaction);
Long rslt = 1
datastore ds_cmpl

IF arg_storageid = -1 THEN
	rslt = 0
	arg_msg = "请选择仓库!"
	GOTO ext
END IF

String ls_storagename
ls_storagename = f_find_storageid(String(arg_storageid))

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

ll_balcdateint_cmpl = arg_balcdateint //按选择, 可以重算历史

Long ll_i
Long ll_mtrlid,ll_mtrlwareid
String ls_mtrlcode,ls_msg

//ll_i = dw_pageretr.GetRow()

ds_cmpl = Create datastore
ds_cmpl.DataObject = 'ds_warebalc_cmpl_warebalc_amt'
ds_cmpl.SetTransObject(sqlca)

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;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询进仓明细库存ID失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

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



//清空筛选内容
cnt = ds_cmpl.Retrieve(arg_storageid,ll_balcdateint_cmpl,sys_option_cost_dec)
//


String ls_status, ls_woodcode, ls_pcode

FOR ll_i = 1 To ds_cmpl.RowCount()
	ll_mtrlid = ds_cmpl.Object.u_mtrldef_mtrlid[ll_i]
	ll_mtrlwareid = ds_cmpl.Object.u_warebalc_mtrlwareid[ll_i]
	ls_mtrlcode = ds_cmpl.Object.u_mtrldef_mtrlcode[ll_i]
	ls_status = ds_cmpl.Object.u_warebalc_status[ll_i]
	ls_woodcode = ds_cmpl.Object.u_warebalc_woodcode[ll_i]
	ls_pcode = ds_cmpl.Object.u_warebalc_pcode[ll_i]
	
	ls_msg = '物料:'+ls_mtrlcode+',库存id:'+String(ll_mtrlwareid)
	
	IF ls_status <> "" THEN
		ls_msg += ','+sys_option_change_status+':'+ls_status
	END IF
	IF ls_woodcode <> "" THEN
		ls_msg += ','+sys_option_change_woodcode+':'+ls_woodcode
	END IF
	IF ls_pcode <> "" THEN
		ls_msg += ','+sys_option_change_pcode+':'+ls_pcode
	END IF
	
	IF uof_warebalc_cmplamt(arg_storageid, ll_balcdateint_cmpl, ll_mtrlwareid, arg_msg, false, commit_transaction) = 0 THEN
		rslt = 0
		GOTO ext
	END IF
NEXT



ext:
Destroy ds_cmpl
IF rslt = 0 THEN
	ROLLBACK Using commit_transaction;
ELSEIF arg_ifcommit THEN
	COMMIT Using commit_transaction;
END IF
RETURN rslt





























end function

on uo_warebalc_cmpl.create
call super::create
TriggerEvent( this, "constructor" )
end on

on uo_warebalc_cmpl.destroy
TriggerEvent( this, "destructor" )
call super::destroy
end on

event constructor;String arg_msg
String str_optionvalue

str_optionvalue = ''
f_get_sys_option_value('152',str_optionvalue,arg_msg)
uo_option_warebalc_checksale = Long(str_optionvalue)

str_optionvalue = ''
f_get_sys_option_value('057',str_optionvalue,arg_msg)
uo_option_wkpmtrlware = Long(str_optionvalue)

end event