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

global type uo_ware_pdb_balc from nonvisualobject
end type
global uo_ware_pdb_balc uo_ware_pdb_balc

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_cancel (long arg_balcdateint, long arg_storageid, ref string arg_msg, boolean arg_ifcommit)
public function integer uof_pdb_audit (long arg_storageid, long arg_pdbdate, string arg_opemp, ref string arg_msg, boolean arg_ifcommit)
public function integer uof_pdb_caudit (long arg_storageid, long arg_pdbdate, ref string arg_msg, boolean arg_ifcommit)
public function integer uof_pdb_del (long arg_storageid, long arg_pdbdate, ref string arg_msg, boolean arg_ifcommit)
public function integer uof_pdb_add (long arg_atid, long arg_storageid, datetime arg_pdbdt, integer arg_ifnoallocqty, ref string arg_msg, boolean arg_ifcommit)
public function integer uof_warebalc_amt (long arg_storageid, long arg_balcdateint, ref string arg_msg, boolean arg_ifcommit)
public function integer uof_warebalc_amt_cancel (long arg_storageid, long arg_balcdateint, ref string arg_msg, boolean arg_ifcommit)
public function integer uof_warebalc_check_inoutflag (long arg_storageid, datetime arg_balcdate, ref string arg_msg, integer arg_ifchecksale)
public function integer uof_warebalc (long arg_storageid, datetime arg_balcdate, ref string arg_msg, boolean arg_ifcommit, integer arg_ifchecksale)
public function integer uof_pdb_add_assign (long arg_storageid, datetime arg_pdbdt, integer arg_ifnoallocqty, ref string arg_msg, boolean arg_ifcommit)
public function integer uof_pdb_del_assign (long arg_storageid, long arg_pdbdate, ref string arg_msg, boolean arg_ifcommit)
public function integer uof_pdb_audit_assign (long arg_storageid, long arg_pdbdate, string arg_opemp, ref string arg_msg, boolean arg_ifcommit)
public function integer uof_pdb_caudit_assign (long arg_storageid, long arg_pdbdate, ref string arg_msg, boolean arg_ifcommit)
end prototypes

public function integer uof_warebalc_cancel (long arg_balcdateint, long arg_storageid, ref string arg_msg, boolean arg_ifcommit);int rslt = 1
long cnt
DateTime null_dt,ld_balcdate
string ls_balcdate
int li_StoreBalcType

SetNull(null_dt)

ls_balcdate = left(string(arg_balcdateint),4) + '-' + left(right(string(arg_balcdateint),4),2) +'-'+ right(string(arg_balcdateint),2)
ld_balcdate = datetime(date(ls_balcdate),time('23:59:59'))

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

SELECT StoreBalcType
	INTO :li_StoreBalcType
	FROM u_storage
	Where storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "查询仓库结算数量和金额类型失败"
	GOTO ext
END IF

//检查进仓单
cnt = 0 
SELECT count(*) INTO :cnt FROM u_inware
	WHERE flag  = 1
	AND ( balcdateint > :arg_balcdateint OR balcdateint = 0 )
	AND storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '仓库反结存失败,查询进仓单结存日期后是否有单失败'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库反结存失败,进仓单结存日期后已经有单审核,如果要反结存请将进仓单反审核'
	GOTO ext
END IF

//检查出仓单
cnt = 0
SELECT count(*) INTO :cnt FROM u_outware
	WHERE flag  = 1
	AND ( balcdateint > :arg_balcdateint OR balcdateint = 0 )
	AND storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '仓库反结存失败,查询出仓单结存日期后是否有单失败'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库反结存失败,出仓单结存日期后已经有单审核,如果要反结存请将出仓单反审核'
	GOTO ext
END IF

//检查仓库外协进仓单及返工单
cnt = 0 
SELECT count(*) INTO :cnt 
	FROM ow_wfjg_in INNER JOIN
	ow_wfjgmx_in_aft ON ow_wfjg_in.scid = ow_wfjgmx_in_aft.scid AND
	ow_wfjg_in.inwareid = ow_wfjgmx_in_aft.inwareid
	WHERE ( ow_wfjg_in.flag  = 1 )
	AND ( ow_wfjg_in.indate > :ld_balcdate )
	AND ( ow_wfjgmx_in_aft.storageid = :arg_storageid )
	And ( ow_wfjg_in.billtype = 4);
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '仓库反结存失败,查询仓库外协进仓单或返工单结存日期后是否有单失败'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库反结存失败,仓库外协进仓单或返工单结存日期后已经有单审核,如果要反结存请将仓库外协进仓单或返工单反审核'
	GOTO ext
END IF

//检查仓库外协发出单及退回单
cnt = 0 
SELECT count(*) INTO :cnt 
	FROM ow_wfjg_out INNER JOIN
	ow_wfjgmx_out ON ow_wfjg_out.scid = ow_wfjgmx_out.scid AND
	ow_wfjg_out.outwareid = ow_wfjgmx_out.outwareid
	WHERE ( ow_wfjg_out.flag  = 1 )
	AND ( ow_wfjg_out.outdate > :ld_balcdate)
	AND ( ow_wfjgmx_out.storageid = :arg_storageid )
	And ( ow_wfjg_out.billtype = 4);
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '仓库反结存失败,查询仓库外协出发单或退回单结存日期后是否有单失败'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库反结存失败,仓库外协出发单或退回单结存日期后已经有单审核,如果要反结存请将仓库外协出发单或退回单反审核'
	GOTO ext
END IF

// 写入日表 upartbalc
UPDATE u_inware
	SET balcflag = 0 ,
	u_inware.balcdateint = 0
	WHERE ( u_inware.flag = 1 ) AND
	( u_inware.balcflag = 1 ) AND
	( u_inware.storageid = :arg_storageid ) AND
	( u_inware.balcdateint = :arg_balcdateint );
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "反结存操作失败 ~n原因:"+sqlca.SQLErrText
	GOTO ext
END IF

UPDATE u_outware
	SET balcflag = 0  ,
	u_outware.balcdateint = 0
	WHERE ( u_outware.flag = 1 ) AND
	( u_outware.balcflag = 1 ) AND
	( u_outware.storageid = :arg_storageid ) AND
	( u_outware.balcdateint = :arg_balcdateint );
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "反结存操作失败 ~n原因:"+sqlca.SQLErrText
	GOTO ext
END IF

//-----------------------------------------	
DELETE FROM u_warebalc
	WHERE balcdateint = 0
	AND storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '反结存操作 ~n原因:'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF


UPDATE u_warebalc
	SET balcdateint = 0,
	balcdate = :null_dt,
	QtyAuditFlag = 0,
	QtyAuditDate = :null_dt,
	QtyAuditEmp = '',
	AmtAuditFlag = 0,
	AmtAuditDate = :null_dt,
	AmtAuditEmp = ''
	WHERE balcdateint = :arg_balcdateint
	AND storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "更新结存表结存标记失败. ~n 原因:"+sqlca.SQLErrText
	GOTO ext
END IF


ext:
IF rslt = 0 THEN
	ROLLBACK;
ELSEIF rslt = 1 AND arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt



end function

public function integer uof_pdb_audit (long arg_storageid, long arg_pdbdate, string arg_opemp, ref string arg_msg, boolean arg_ifcommit);Int rslt = 1
Long cnt


SELECT count(*) INTO :cnt
	FROM u_storage
	Where storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询仓库资料失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt <> 1 THEN
	arg_msg = '仓库资料不存在或重复,请检查'
	rslt = 0
	GOTO ext
END IF


cnt = 0
SELECT count(*) INTO :cnt
	FROM u_warepdb
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询盘点表失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt = 0 THEN
	arg_msg = '盘点表没有盘点内容,不能审核'
	rslt = 0
	GOTO ext
END IF

cnt = 0
SELECT count(*) INTO :cnt
	FROM u_warepdb
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate
	AND flag = 1;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询盘点表是否已审核失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt > 0 THEN
	arg_msg = '盘点表已审核,不能审核'
	rslt = 0
	GOTO ext
END IF

UPDATE u_warepdb
	SET flag = 1,
	auditemp = :arg_opemp,
	auditdate = getdate()
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '更新盘点表审核标记失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

//盘点后, 记录库存分配的信息 20120716 lhd
UPDATE u_warepdb_assign_log
	SET edqty = u_mtrlware_assign.assignqty - u_mtrlware_assign.Outqty
	FROM u_warepdb_assign_log INNER join
	u_mtrlware_assign on u_warepdb_assign_log.Assignid = u_mtrlware_assign.Assignid
	WHERE u_warepdb_assign_log.storageid = :arg_storageid
	And u_warepdb_assign_log.pdbdate = :arg_pdbdate;
IF sqlca.SQLCode <> 0 THEN
	arg_msg =  '记录库存分配的信息失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF


ext:
IF rslt = 0 THEN
	ROLLBACK;
ELSEIF rslt = 1 AND arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt
end function

public function integer uof_pdb_caudit (long arg_storageid, long arg_pdbdate, ref string arg_msg, boolean arg_ifcommit);Int rslt = 1
Long cnt
Long ll_pdbdate
DateTime null_dt

SetNull(null_dt)


SELECT count(*) INTO :cnt
	FROM u_storage
	Where storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询仓库资料失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt <> 1 THEN
	arg_msg = '仓库资料不存在或重复,请检查'
	rslt = 0
	GOTO ext
END IF


cnt = 0
SELECT count(*) INTO :cnt
	FROM u_warepdb
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询盘点表失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt = 0 THEN
	arg_msg = '盘点表没有盘点内容,不能撤审'
	rslt = 0
	GOTO ext
END IF

cnt = 0
SELECT count(*) INTO :cnt
	FROM u_warepdb
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate
	AND flag = 0;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询盘点表是否未审核失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt > 0 THEN
	arg_msg = '盘点表未审核,不能撤审'
	rslt = 0
	GOTO ext
END IF

//检查盘点日期后是否有结存
ll_pdbdate = Long(arg_pdbdate)

cnt = 0
SELECT COUNT(*)
	INTO :cnt
	FROM u_warebalc
	WHERE (balcdateint >= :ll_pdbdate)
	And (storageid = :arg_storageid);
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询盘点日期后仓库是否有结存失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt > 0 THEN
	arg_msg = '盘点日期后仓库已结存,不能撤审'
	rslt = 0
	GOTO ext
END IF

UPDATE u_warepdb
	SET flag = 0,
	auditemp = '',
	auditdate = :null_dt
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '更新盘点表审核标记失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

//盘点后, 记录库存分配的信息 20120716 lhd
UPDATE u_warepdb_assign_log
	SET edqty = 0
	WHERE u_warepdb_assign_log.storageid = :arg_storageid
	And u_warepdb_assign_log.pdbdate = :arg_pdbdate;
IF sqlca.SQLCode <> 0 THEN
	arg_msg =  '记录库存分配的信息失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF


ext:
IF rslt = 0 THEN
	ROLLBACK;
ELSEIF rslt = 1 AND arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt

end function

public function integer uof_pdb_del (long arg_storageid, long arg_pdbdate, ref string arg_msg, boolean arg_ifcommit);Int rslt = 1
Long cnt = 0

IF arg_storageid <= 0 THEN
	arg_msg = '请选择要仓库'
	rslt = 0
	GOTO ext
END IF

SELECT count(*)
	INTO :cnt
	FROM u_warepdb
	WHERE u_warepdb.storageid = :arg_storageid
	AND u_warepdb.pdbdate = :arg_pdbdate
	AND flag = 1;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询操作失败,在盘点日:'+String(arg_pdbdate)+'数据资料失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt > 0 THEN
	arg_msg = '盘点日:'+String(arg_pdbdate)+',盘点数据已经审核,不能删除'
	rslt = 0
	GOTO ext
END IF


DELETE FROM u_warepdb
	WHERE pdbdate = :arg_pdbdate
	AND storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '删除当前期的盘点表失败!'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

DELETE FROM u_warepdb_assign_log
	WHERE pdbdate = :arg_pdbdate
	AND storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '删除当前期的盘点表库存分配信息失败!'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF




ext:
IF rslt = 0 THEN
	ROLLBACK;
ELSEIF rslt = 1 AND arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt



end function

public function integer uof_pdb_add (long arg_atid, long arg_storageid, datetime arg_pdbdt, integer arg_ifnoallocqty, ref string arg_msg, boolean arg_ifcommit);//====================================================================
// 事件: uo_ware_pdb_balc.uof_pdb_add()
//--------------------------------------------------------------------
// 描述:
//--------------------------------------------------------------------
// 参数:
// 	value    	long    	arg_atid        	
// 	value    	long    	arg_storageid   	
// 	value    	datetime	arg_pdbdt       	
// 	value    	integer 	arg_ifnoallocqty	
// 	reference	string  	arg_msg         	
// 	value    	boolean 	arg_ifcommit    	
//--------------------------------------------------------------------
// 返回:  integer
//--------------------------------------------------------------------
//--------------------------------------------------------------------
//	Copyright (c) 2002-2018 RICHTA(TM), All rights reserved.
//--------------------------------------------------------------------
// 修改历史:
//
//====================================================================
Int rslt = 1
Long ll_waredate
Long ls_scid
DateTime st_date,ed_date,ls_serverdate
Date ls_enddate
Long cnt
Int li_mtrlprp


ll_waredate = Long(String(arg_pdbdt,'yyyymmdd'))



If arg_storageid <= 0 Then
	arg_msg = "请选择仓库"
	rslt = 0
	Goto ext
End If

Select scid,mtrlprp Into :ls_scid,:li_mtrlprp
	From u_storage
	Where storageid = :arg_storageid;
If sqlca.SQLCode <> 0 Then
	arg_msg = '查询仓库分部失败'+sqlca.SQLErrText
	rslt = 0
	Goto ext
End If

Select count(*)
	Into :cnt
	From u_warepdb
	Where storageid = :arg_storageid
	And pdbdate = :ll_waredate;
If sqlca.SQLCode <> 0 Then
	rslt = 0
	arg_msg = '查询盘点表是否已存在失败,'+sqlca.SQLErrText
	Goto ext
End If

If cnt > 0 Then
	rslt = 0
	arg_msg = '该仓库当前日期的盘点表已经存在,不能再建立!'
	Goto ext
End If


Select count(*)
	Into :cnt
	From u_warepdb_assign
	Where storageid = :arg_storageid
	And pdbdate = :ll_waredate;
If sqlca.SQLCode <> 0 Then
	rslt = 0
	arg_msg = '查询盘点表是否已存在失败,'+sqlca.SQLErrText
	Goto ext
End If

If cnt > 0 Then
	rslt = 0
	arg_msg = '该仓库当前日期的盘点表已经存在,不能再建立!'
	Goto ext
End If

If li_mtrlprp = 3 Then
	cnt = 0
	
	Select count(*)
		Into :cnt
		From u_outware Inner JOIN
		u_outwaremx Inner JOIN
		u_mtrldef ON u_outwaremx.mtrlid = u_mtrldef.mtrlid ON
		u_outware.scid = u_outwaremx.scid And
		u_outware.outwareid = u_outwaremx.outwareid Inner JOIN
		u_storage ON u_outware.StorageID = u_storage.storageid Inner JOIN
		u_sc_workgroup ON u_outware.relint_2 = u_sc_workgroup.wrkGrpid Inner JOIN
		u_workgroup ON u_outware.relid = u_workgroup.workgroupid Inner JOIN
		u_sc_wkp ON u_sc_workgroup.storageid = u_sc_wkp.wrkGrpid RIGHT Outer JOIN
		u_mtrlware ON u_outwaremx.scid = u_mtrlware.scid And
		u_outwaremx.mtrlwareid = u_mtrlware.mtrlwareid LEFT Outer JOIN
		u_outware_scllplan RIGHT Outer JOIN
		u_outware_scllplan_mx_mx ON
		u_outware_scllplan.outwareid = u_outware_scllplan_mx_mx.outwareid ON
		u_outwaremx.relid = u_outware_scllplan_mx_mx.outwareid And
		u_outwaremx.relprintid = u_outware_scllplan_mx_mx.printid
		Where (u_outwaremx.qty > u_outwaremx.planqty) And
		( u_outware.billtype = 3 And  u_outware.relint_1 = 4 )
		And (u_outware.flag = 1)
		And (u_outware.outdate <= :arg_pdbdt)
		And (u_outwaremx.wsauditflag = 0)
		And (u_sc_workgroup.storageid In (Select wrkGrpid From u_sc_wkp Where storageid = :arg_storageid));
	If sqlca.SQLCode <> 0 Then
		arg_msg = '查询未进行车间审核的超发料明细失败'+sqlca.SQLErrText
		rslt = 0
		Goto ext
	End If
	
	If cnt > 0 Then
		rslt = 0
		arg_msg = '该仓库对应车间有未进行车间审核的超发料明细,不能建立盘点表!'
		Goto ext
	End If
	
End If


If arg_atid = 0 Then
	rslt = uof_warebalc_check_inoutflag(arg_storageid,arg_pdbdt,arg_msg,0)
	If rslt = 0 Or rslt = 2 Then
		Goto ext
	End If
End If


Select Top 1 getdate() Into :ls_serverdate From u_user  ;
If sqlca.SQLCode <> 0 Then
	arg_msg = '查询当前日期操作失败'+sqlca.SQLErrText
	rslt = 0
	Goto ext
End If

ls_enddate = Date(String(arg_pdbdt,'yyyy-mm-dd'))

st_date = DateTime(RelativeDate(Date(ls_enddate),1),Time('0:0'))
ed_date = DateTime(RelativeDate(Date(ls_serverdate),1),Time('0:0'))

If st_date > ed_date Then
	arg_msg = '盘点日期不能大过于当前日期'
	rslt = 0
	Goto ext
End If

Delete u_parm_balcdate ;
If sqlca.SQLCode <> 0 Then
	arg_msg = "因网络或其它原因导致操作失败,请重试"
	rslt = 0
	Goto ext
End If

Insert Into u_parm_balcdate (first_date,end_date)
	Values (:st_date,:ed_date) ;
If sqlca.SQLCode <> 0 Then
	arg_msg = "因网络或其它原因导致建立开始日期,结束日期操作失败,请重试,"+sqlca.SQLErrText
	rslt = 0
	Goto ext
End If

Commit ;

Insert Into  u_warepdb (
	atid,
	flag,
	pdbdate,
	scid,
	mtrlwareid,
	mtrlid,
	storageid,
	plancode,
	Status,
	planprice,
	Sptid,
	Dxflag,
	newprice,
	woodcode,
	pcode,
	cost,
	qty,
	factqty,
	uqty,
	factuqty,
	rate,
	wareamt,
	scllflag,
	pdbdt,
	pdbemp,
	mtrlcuscode,
	location)
	Select
	:arg_atid,
	0,
	:ll_waredate ,
	u_mtrlware.scid,
	u_mtrlware.mtrlwareid,
	u_mtrlware.mtrlid,
	u_mtrlware.storageid,
	u_mtrlware.plancode,
	u_mtrlware.Status,
	u_mtrldef.planprice,
	u_mtrlware.Sptid,
	u_mtrlware.Dxflag,
	u_mtrlware.newprice,
	u_mtrlware.woodcode,
	u_mtrlware.pcode,
	u_mtrlware.cost,
	u_mtrlware.noallocqty + ISNULL(uv_pdb_outware.desqty, 0) -  ISNULL(uv_pdb_inware.incqty, 0),
	0,
	case u_mtrldef.ifunit when 0 then u_mtrlware.noallocqty + ISNULL(uv_pdb_outware.desqty, 0) -  ISNULL(uv_pdb_inware.incqty, 0)
	else u_mtrlware.unoallocqty + ISNULL(uv_pdb_outware.desuqty, 0) -  ISNULL(uv_pdb_inware.incuqty, 0) End ,
	0,
	case u_mtrldef.ifunit when 0 then 1 else
	case u_mtrlware.unoallocqty + ISNULL(uv_pdb_outware.desuqty, 0) -  ISNULL(uv_pdb_inware.incuqty, 0) when 0 then u_mtrldef.rate_buy
	else round((u_mtrlware.noallocqty + ISNULL(uv_pdb_outware.desqty, 0) -  ISNULL(uv_pdb_inware.incqty, 0))/(u_mtrlware.unoallocqty + ISNULL(uv_pdb_outware.desuqty, 0) -  ISNULL(uv_pdb_inware.incuqty, 0)),5) End End,
	u_mtrlware.wareamt +	ISNULL(uv_pdb_outware.desamt, 0) -  ISNULL(uv_pdb_inware.incamt,0),
	u_mtrldef.scllflag,
	getdate(),
	:publ_operator,
	u_mtrlware.mtrlcuscode,
	u_mtrlware.location
	From uv_pdb_inware RIGHT Outer JOIN
	u_mtrlware ON uv_pdb_inware.storageid = u_mtrlware.storageid And
	uv_pdb_inware.mtrlid = u_mtrlware.mtrlid And
	uv_pdb_inware.plancode = u_mtrlware.plancode And
	uv_pdb_inware.status = u_mtrlware.Status And
	uv_pdb_inware.cusid = u_mtrlware.Sptid And
	uv_pdb_inware.Dxflag = u_mtrlware.Dxflag And
	uv_pdb_inware.pcode = u_mtrlware.pcode And
	uv_pdb_inware.woodcode = u_mtrlware.woodcode LEFT Outer JOIN
	uv_pdb_outware ON u_mtrlware.storageid = uv_pdb_outware.StorageID And
	u_mtrlware.mtrlid = uv_pdb_outware.mtrlid And
	u_mtrlware.plancode = uv_pdb_outware.plancode And
	u_mtrlware.Status = uv_pdb_outware.status And
	u_mtrlware.Sptid = uv_pdb_outware.Sptid And
	u_mtrlware.Dxflag = uv_pdb_outware.Dxflag And
	u_mtrlware.woodcode = uv_pdb_outware.woodcode And
	u_mtrlware.pcode = uv_pdb_outware.pcode,
	u_mtrldef
	Where (u_mtrldef.mtrlid = u_mtrlware.mtrlid) And
	(u_mtrlware.storageid = :arg_storageid) And
	(u_mtrlware.scid = :ls_scid) And
	(u_mtrlware.noallocqty > 0 And :arg_ifnoallocqty = 1 Or :arg_ifnoallocqty = 0);
	
If sqlca.SQLCode <> 0 Then
	If Pos(sqlca.SQLErrText,'Cannot insert duplicate key in') > 0 Then
		arg_msg = '该仓库当前日期的盘点表已经存在,不能建立!'
	Else
		arg_msg = '生成当前期的盘点表失败!~n原因是:'+sqlca.SQLErrText
	End If
	rslt = 0
	Goto ext
End If


//盘点前, 记录库存分配的信息 20120716 lhd
Insert Into u_warepdb_assign_log
	(storageid, pdbdate, Assignid, assigntype, Scid, Mtrlwareid, cusid, Mtrlcuscode,
	Relbillid, Relbillcode, Relprintid, bgqty)
	Select u_mtrlware.storageid, :ll_waredate as pdbdate, u_mtrlware_assign.Assignid,
	u_mtrlware_assign.assigntype, u_mtrlware_assign.Scid,
	u_mtrlware_assign.Mtrlwareid, u_mtrlware_assign.cusid,
	u_mtrlware_assign.Mtrlcuscode, u_mtrlware_assign.Relbillid,
	u_mtrlware_assign.Relbillcode, u_mtrlware_assign.Relprintid,
	u_mtrlware_assign.assignqty - u_mtrlware_assign.Outqty AS bgqty
	From u_mtrlware_assign Inner JOIN
	u_mtrlware ON u_mtrlware_assign.scid = u_mtrlware.scid
	And u_mtrlware_assign.Mtrlwareid = u_mtrlware.mtrlwareid
	Where (u_mtrlware.storageid = :arg_storageid);
If sqlca.SQLCode <> 0 Then
	arg_msg = ' 记录库存分配的信息失败,'+sqlca.SQLErrText
	rslt = 0
	Goto ext
End If

ext:
If rslt = 0 Or rslt = 2 Then
	Rollback;
ElseIf rslt = 1 And arg_ifcommit Then
	Commit;
End If

Return rslt











end function

public function integer uof_warebalc_amt (long arg_storageid, long arg_balcdateint, ref string arg_msg, boolean arg_ifcommit);Int rslt = 1
Long cnt
Int li_ifautobalccmpl

uo_warebalc_cmpl uo_cmpl
uo_cmpl = Create uo_warebalc_cmpl

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

IF arg_balcdateint = 0 THEN
	arg_msg = '请先执行数量结存'
	rslt = 0
	GOTO ext
END IF

SELECT ifautobalccmpl
	INTO :li_ifautobalccmpl
	FROM u_storage
	Where storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "查询仓库信息失败. ~n 原因:"+sqlca.SQLErrText
	GOTO ext
END IF

//检查进仓单是否有未财审(采购收货单,退货单, 其它进仓单,生产进仓单,盘盈单)
SELECT count(*)
	INTO :cnt
	FROM u_inware
	WHERE secflag = 0
	AND (balcdateint = :arg_balcdateint)
	AND (storageid = :arg_storageid)
	And (billtype In (1,3,8,9));
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "查询结存日期内是否有进仓单未财审失败. ~n 原因:"+sqlca.SQLErrText
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = "结存时间段内有进仓单未财审,不能执行金额结存"
	GOTO ext
END IF

//检查出仓单是否有未财审(销售发货单,其它出仓单,盘亏单)
SELECT count(*)
	INTO :cnt
	FROM u_outware
	WHERE secflag = 0
	AND (balcdateint = :arg_balcdateint)
	AND (storageid = :arg_storageid)
	And (billtype In (1,8,9));
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "查询结存日期内是否有出仓单未财审失败. ~n 原因:"+sqlca.SQLErrText
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = "结存时间段内有出仓单未财审,不能执行金额结存"
	GOTO ext
END IF

//自动重算一次
IF li_ifautobalccmpl = 1 THEN
	IF uo_cmpl.uof_warebalc_cmplamt_all(arg_storageid, arg_balcdateint, arg_msg, False, sqlca) = 0 THEN
		rslt = 0
		GOTO ext
	END IF
END IF

UPDATE u_warebalc
	SET AmtAuditFlag = 1,
	AmtAuditDate = getdate(),
	AmtAuditEmp = :publ_operator
	WHERE balcdateint = :arg_balcdateint
	And storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "更新结存表金额结存标记失败. ~n 原因:"+sqlca.SQLErrText
	GOTO ext
END IF

ext:
Destroy uo_cmpl
IF rslt = 0 THEN
	ROLLBACK;
ELSEIF rslt = 1 And arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt





end function

public function integer uof_warebalc_amt_cancel (long arg_storageid, long arg_balcdateint, ref string arg_msg, boolean arg_ifcommit);Int rslt = 1
Long cnt

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

//检查结存表的金额结存数据,如果有比这个月份后的, 不能撤审
SELECT count(*)
	INTO :cnt
	FROM u_warebalc
	WHERE balcdateint > :arg_balcdateint
	AND storageid = :arg_storageid
	And AmtAuditFlag = 1;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "查询最后一次金额结存失败. ~n 原因:"+sqlca.SQLErrText
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '当前选择不是最后一次金额结存,不能操作'
	GOTO ext
END IF

UPDATE u_warebalc
	SET AmtAuditFlag = 0,
	AmtAuditDate = NULL,
	AmtAuditEmp = ''
	WHERE balcdateint = :arg_balcdateint
	And storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "更新结存表金额结存标记失败. ~n 原因:"+sqlca.SQLErrText
	GOTO ext
END IF

ext:
IF rslt = 0 THEN
	ROLLBACK;
ELSEIF rslt = 1 And arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt



end function

public function integer uof_warebalc_check_inoutflag (long arg_storageid, datetime arg_balcdate, ref string arg_msg, integer arg_ifchecksale);//arg_ifchecksale  0-询问 1-继续 2-不继续

Long rslt = 1
Long cnt = 0
String ls_storagename
Int li_mtrlprp

IF uo_option_warebalc_checksale = -1000 THEN
	arg_msg = '选项:[152]盘点结存限制日期前不能有未审发货单,读取初始默认值失败,操作取消!'
	rslt = 0
	GOTO ext
END IF

IF uo_option_wkpmtrlware = -1000 THEN
	arg_msg = '选项:[057]生产使用车间核算,读取初始默认值失败,操作取消!'
	rslt = 0
	GOTO ext
END IF

IF uo_option_outware_scll_new_cj_mtrlware = -1000 THEN
	arg_msg = '选项:[355]其它领料单增加车间仓库存,读取初始默认值失败,操作取消!'
	rslt = 0
	GOTO ext
END IF


SELECT storagename,mtrlprp
	INTO :ls_storagename,:li_mtrlprp
	FROM u_storage
	Where storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,仓库名称'
	GOTO ext
END IF

IF uo_option_wkpmtrlware = 1 And li_mtrlprp = 3 THEN
	
	cnt = 0
	
	SELECT count(*)
		INTO :cnt
		FROM u_outware INNER JOIN
		u_workgroup ON u_outware.relid = u_workgroup.workgroupid INNER JOIN
		u_sc_workgroup ON u_workgroup.wrkGrpid = u_sc_workgroup.wrkGrpid INNER JOIN
		u_sc_wkp ON u_sc_workgroup.storageid = u_sc_wkp.wrkGrpid
		WHERE (u_outware.billtype = 3)
		AND (u_outware.secflag = 0)
		AND (u_sc_wkp.ifwkpmtrlware = 1)
		AND (u_sc_wkp.storageid = :arg_storageid)
		AND (u_outware.outdate <= :arg_balcdate)
		And (u_outware.relint_1 <> 3 Or :uo_option_outware_scll_new_cj_mtrlware = 1);
	IF sqlca.SQLCode <> 0 THEN
		arg_msg = '查询车间仓是否有相关待二审的领料单失败'+sqlca.SQLErrText
		rslt = 0
		GOTO ext
	END IF
	
	IF cnt > 0 THEN
		rslt = 0
		arg_msg = '车间仓有相关待二审的领料单,请先审核'
		GOTO ext
	END IF
	
	SELECT count(*)
		INTO :cnt
		FROM u_sc_wkp u_sc_wkp_order INNER JOIN
		u_Order_ml ON
		u_sc_wkp_order.wrkGrpid = u_Order_ml.wrkGrpid RIGHT OUTER JOIN
		u_inware INNER JOIN
		u_inwaremx ON u_inware.scid = u_inwaremx.scid AND
		u_inware.inwareid = u_inwaremx.inwareid INNER JOIN
		u_mtrldef ON u_inwaremx.mtrlid = u_mtrldef.mtrlid INNER JOIN
		u_sc_wkp ON u_mtrldef.dftwrkGrpid = u_sc_wkp.wrkGrpid ON
		u_Order_ml.scid = u_inwaremx.scid AND
		u_Order_ml.OrderID = u_inwaremx.relid
		WHERE (u_inware.fkflag = 0)
		AND (u_inware.billtype = 3)
		AND ( u_inware.relint_2 = 0 )
		AND (u_inware.indate <= :arg_balcdate)
		AND ((u_sc_wkp_order.storageid = :arg_storageid AND u_inwaremx.relid > 0 AND u_sc_wkp_order.ifwkpmtrlware = 1)
		Or  (u_sc_wkp.storageid = :arg_storageid And u_inwaremx.relid = 0 And u_sc_wkp.ifwkpmtrlware = 1));
	IF sqlca.SQLCode <> 0 THEN
		arg_msg = '查询车间仓是否有相关未反扣的生产进仓单失败'+sqlca.SQLErrText
		rslt = 0
		GOTO ext
	END IF
	
	IF cnt > 0 THEN
		rslt = 0
		arg_msg = '车间仓有相关相关未反扣的生产进仓单,请先反扣'
		GOTO ext
	END IF
	
END IF


//1.检查日期前
//1.1检查进仓单
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM u_inware
	WHERE flag  = 0
	AND storageid = :arg_storageid
	And indate <= :arg_balcdate ;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期前是否有进仓单未审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有('+String(cnt)+')进仓单未审'
	GOTO ext
END IF


//1.2不检查已开未审发货单,(占库存)
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM u_outware
	WHERE flag  = 0
	AND storageid = :arg_storageid
	AND billtype <> 1
	And outdate <= :arg_balcdate ;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前是否有出仓单未审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有('+String(cnt)+')出仓单未审'
	GOTO ext
END IF

//1.2.1 检查已开未审发货单,(占库存)
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM u_outware
	WHERE flag  = 0
	AND storageid = :arg_storageid
	AND billtype = 1
	And outdate <= :arg_balcdate ;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前是否有发货单未审'
	GOTO ext
END IF

IF uo_option_warebalc_checksale = 1 THEN
	IF cnt > 0 THEN
		rslt = 0
		arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有('+String(cnt)+')发货单未审'
		GOTO ext
	END IF
ELSE
	IF cnt > 0 THEN
		arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有('+String(cnt)+')发货单未审'
		
		IF arg_ifchecksale = 0 THEN
			IF MessageBox('询问',arg_msg+',是否继续?',question!,yesno!) = 2 THEN
				rslt = 2
				GOTO ext
			END IF
		ELSEIF arg_ifchecksale = 2 THEN
			rslt = 2
			GOTO ext
		END IF
	END IF
END IF



//1.3检查调仓单
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM u_outware_move
	WHERE ( flag  = 0 )
	AND ( sstorageid = :arg_storageid )
	And ( outdate <= :arg_balcdate );
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前是否有调仓单调出未审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有('+String(cnt)+')调仓单未审'
	GOTO ext
END IF

cnt = 0
SELECT count(*)
	INTO :cnt
	FROM u_outware_move
	WHERE ( d_auditflag = 0 )
	AND ( dstorageid = :arg_storageid )
	And ( indate <= :arg_balcdate );
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前是否有调仓单调入未审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有('+String(cnt)+')调仓单未审'
	GOTO ext
END IF

//1.4 检查仓库外协进仓单(含返工单)
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM ow_wfjg_in INNER JOIN
	ow_wfjgmx_in_aft ON ow_wfjg_in.scid = ow_wfjgmx_in_aft.scid AND
	ow_wfjg_in.inwareid = ow_wfjgmx_in_aft.inwareid
	WHERE (ow_wfjg_in.flag = 0 )
	AND (ow_wfjg_in.indate <= :arg_balcdate)
	AND (ow_wfjgmx_in_aft.storageid = :arg_storageid)
	And (ow_wfjg_in.billtype = 4);
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前是否有仓库外协进仓单或仓库外协返工单未审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有('+String(cnt)+')仓库外协进仓单或仓库外协返工单未审'
	GOTO ext
END IF

//1.5 检查仓库外协发出单(含退回单)
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM ow_wfjg_out INNER JOIN
	ow_wfjgmx_out ON ow_wfjg_out.scid = ow_wfjgmx_out.scid AND
	ow_wfjg_out.outwareid = ow_wfjgmx_out.outwareid
	WHERE (ow_wfjg_out.flag = 0 )
	AND (ow_wfjg_out.outdate <= :arg_balcdate)
	AND (ow_wfjgmx_out.storageid = :arg_storageid)
	And (ow_wfjg_out.billtype = 4);
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前是否有仓库外协发出单或仓库外协退回单未审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有('+String(cnt)+')仓库外协发出单或仓库外协退回单未审'
	GOTO ext
END IF

//1.5.1 检查外协订单, 开通选项自动生成发出单的情况
IF sys_option_autocreate_outware = 1 THEN
	cnt = 0
	SELECT count(*)
		INTO :cnt
		FROM u_order_wfjg INNER JOIN
		u_order_wfjgMx_out ON u_order_wfjg.scid = u_order_wfjgMx_out.scid AND
		u_order_wfjg.wfjgID = u_order_wfjgMx_out.wfjgID
		WHERE (u_order_wfjg.status = 0 )
		AND (u_order_wfjg.requiredate <= :arg_balcdate)
		And (u_order_wfjgMx_out.storageid = :arg_storageid);
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前是否有外协订单未审'
		GOTO ext
	END IF
	
	IF cnt > 0 THEN
		rslt = 0
		arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有('+String(cnt)+')外协订单未审(自动生成发出)'
		GOTO ext
	END IF
	
END IF

//1.6 检查仓库外协销售单(含退货单)
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM ow_wfjg_out INNER JOIN
	ow_wfjgmx_out ON ow_wfjg_out.scid = ow_wfjgmx_out.scid AND
	ow_wfjg_out.outwareid = ow_wfjgmx_out.outwareid
	WHERE (ow_wfjg_out.flag = 0 )
	AND (ow_wfjg_out.outdate <= :arg_balcdate)
	AND (ow_wfjgmx_out.storageid = :arg_storageid)
	And (ow_wfjg_out.billtype = 7);
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前是否有仓库外协销售单或仓库外协销售退货单未审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有('+String(cnt)+')仓库外协销售单或仓库外协销售退货单未审'
	GOTO ext
END IF

//2.检查日期后
//2.1检查进仓单
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM u_inware
	WHERE flag  = 1
	AND storageid = :arg_storageid
	And indate > :arg_balcdate ;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后是否有进仓单已审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后有('+String(cnt)+')进仓单已审'
	GOTO ext
END IF

//2.2检查出仓单
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM u_outware
	WHERE flag  = 1
	AND storageid = :arg_storageid
	And outdate > :arg_balcdate ;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后是否有出仓单已审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后有('+String(cnt)+')出仓单已审'
	GOTO ext
END IF

//2.3检查调仓单
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM u_outware_move
	WHERE ( flag  = 1 )
	AND ( sstorageid = :arg_storageid )
	And ( outdate > :arg_balcdate );
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后是否有调仓单调出已审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后有('+String(cnt)+')调仓单调出已审'
	GOTO ext
END IF

cnt = 0
SELECT count(*)
	INTO :cnt
	FROM u_outware_move
	WHERE ( d_auditflag = 1 )
	AND ( dstorageid = :arg_storageid )
	And ( outdate > :arg_balcdate );
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后是否有调仓单调入已审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后有('+String(cnt)+')调仓单调入已审'
	GOTO ext
END IF

//2.4 检查仓库外协进仓单(含返工单)
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM ow_wfjg_in INNER JOIN
	ow_wfjgmx_in_aft ON ow_wfjg_in.scid = ow_wfjgmx_in_aft.scid AND
	ow_wfjg_in.inwareid = ow_wfjgmx_in_aft.inwareid
	WHERE (ow_wfjg_in.flag = 1 )
	AND (ow_wfjg_in.indate > :arg_balcdate)
	AND (ow_wfjgmx_in_aft.storageid = :arg_storageid)
	And (ow_wfjg_in.billtype = 4);
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后是否有仓库外协进仓单或仓库外协返工单已审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后有('+String(cnt)+')仓库外协进仓单或仓库外协返工单已审'
	GOTO ext
END IF

//2.5 检查仓库外协发出单(含退回单)
cnt = 0
SELECT count(*)
	INTO :cnt
	FROM ow_wfjg_out INNER JOIN
	ow_wfjgmx_out ON ow_wfjg_out.scid = ow_wfjgmx_out.scid AND
	ow_wfjg_out.outwareid = ow_wfjgmx_out.outwareid
	WHERE (ow_wfjg_out.flag = 1 )
	AND (ow_wfjg_out.outdate > :arg_balcdate)
	AND (ow_wfjgmx_out.storageid = :arg_storageid)
	And (ow_wfjg_out.billtype = 4);
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后是否有仓库外协发出单或仓库外协退回单已审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',后有('+String(cnt)+')仓库外协发出单或仓库外协退回单已审'
	GOTO ext
END IF

//// //

String ls_pdbdate
ls_pdbdate = String(arg_balcdate,'yyyymmdd')

cnt = 0
SELECT count(*) INTO :cnt
	FROM u_warepdb
	WHERE storageid = :arg_storageid
	AND pdbdate <= :ls_pdbdate
	And flag = 0;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询操作失败,日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前是否有盘点表未审'
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '仓库:'+ls_storagename+',日期:'+String(arg_balcdate,'yyyy-mm-dd hh:mm')+',前有盘点表未审'
	GOTO ext
END IF

ext:
RETURN rslt






end function

public function integer uof_warebalc (long arg_storageid, datetime arg_balcdate, ref string arg_msg, boolean arg_ifcommit, integer arg_ifchecksale);Int rslt = 1
Long cnt
DateTime balc_date
Long ls_balcdateint
Int li_StoreBalcType, li_ifautobalccmpl

uo_warebalc_cmpl uo_cmpl
uo_cmpl = Create uo_warebalc_cmpl

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


ls_balcdateint = Year(Date(arg_balcdate)) * 10000 + Month(Date(arg_balcdate)) * 100 + Day(Date(arg_balcdate))
balc_date = DateTime(Date(arg_balcdate),Time('23:59:59'))

SELECT StoreBalcType, ifautobalccmpl
	INTO :li_StoreBalcType, :li_ifautobalccmpl
	FROM u_storage
	Where storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "查询仓库信息失败,"+sqlca.SQLErrText
	GOTO ext
END IF

SELECT count(*) INTO :cnt
	FROM u_warebalc
	WHERE balcdate >= :balc_date
	And storageid = :arg_storageid;
IF cnt <> 0 THEN
	rslt = 0
	arg_msg = "仓库指定日期后已进行过结存操作"
	GOTO ext
END IF

//检查单据
rslt = uof_warebalc_check_inoutflag(arg_storageid,arg_balcdate,arg_msg,arg_ifchecksale)

IF rslt = 0 Or rslt = 2 THEN
	GOTO ext
END IF

//自动重算一次
IF li_StoreBalcType = 0 THEN
	IF li_ifautobalccmpl = 1 THEN
		//未结存,重算的是本期
		IF uo_cmpl.uof_warebalc_cmplamt_all(arg_storageid, 0, arg_msg, False, sqlca) = 0 THEN
			rslt = 0
			GOTO ext
		END IF
	END IF
END IF

//把当前最新的标准成本价更新到结存表的标准成本价列
UPDATE u_warebalc
	SET u_warebalc.stprice = u_mtrldef_planprice.stprice
	FROM u_warebalc INNER JOIN
	u_mtrldef_planprice ON u_warebalc.mtrlid = u_mtrldef_planprice.mtrlid AND
	u_warebalc.status = u_mtrldef_planprice.status AND
	u_warebalc.woodcode = u_mtrldef_planprice.woodcode AND
	u_warebalc.pcode = u_mtrldef_planprice.pcode
	WHERE balcdateint = 0
	And storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "更新结存表标准成本价失败. ~n 原因:"+sqlca.SQLErrText
	GOTO ext
END IF

UPDATE u_warebalc
	SET balcdateint = :ls_balcdateint,
	balcdate = :arg_balcdate,
	QtyAuditFlag = 1,
	QtyAuditDate = getdate(),
	QtyAuditEmp = :publ_operator,
	AmtAuditFlag = case :li_StoreBalcType when 0 then 1 else 0 END,
	AmtAuditDate = case :li_StoreBalcType when 0 then getdate() else NULL END,
	AmtAuditEmp = case :li_StoreBalcType when 0 then :publ_operator else '' END
	WHERE balcdateint = 0
	And storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "更新仓库结存标记失败. ~n 原因:"+sqlca.SQLErrText
	GOTO ext
END IF

INSERT INTO u_warebalc
	(balcdateint,
	storageid,
	mtrlid,
	bgqty,
	ubgqty,
	bgamt,
	incqty,
	uincqty,
	incamt,
	desqty,
	udesqty,
	desamt,
	balcqty,
	ubalcqty,
	balcamt,
	pypk,
	scid,
	status,
	woodcode,
	pcode,
	plancode,
	sptid,
	dxflag,
	mtrlcuscode,
	location,
	mtrlwareid)
	SELECT 0,
	u_warebalc.storageid,
	u_warebalc.mtrlid,
	u_warebalc.balcqty,
	u_warebalc.ubalcqty,
	u_warebalc.balcamt,
	0,
	0,
	0,
	0,
	0,
	0,
	u_warebalc.balcqty,
	u_warebalc.ubalcqty,
	u_warebalc.balcamt,
	0,
	u_warebalc.scid,
	u_warebalc.status,
	u_warebalc.woodcode,
	u_warebalc.pcode,
	u_warebalc.plancode,
	u_warebalc.sptid,
	u_warebalc.dxflag,
	u_warebalc.mtrlcuscode,
	u_warebalc.location,
	u_warebalc.mtrlwareid
	FROM u_warebalc
	WHERE ( (abs(u_warebalc.bgqty)+abs(u_warebalc.desqty)+abs(u_warebalc.incqty)+abs(u_warebalc.balcqty) <> 0)
	OR (abs(u_warebalc.bgamt)+ abs(u_warebalc.incamt)+abs(u_warebalc.desamt)+abs(u_warebalc.balcamt) <> 0) )
	AND ( u_warebalc.storageid = :arg_storageid )
	And ( u_warebalc.balcdateint = :ls_balcdateint );
IF sqlca.SQLCode <> 0 THEN
	arg_msg = "结存操作失败 ~n原因:"+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

// 写入日表 upartbalc
UPDATE u_inware
	SET balcflag = 1 ,
	u_inware.balcdateint = :ls_balcdateint
	WHERE ( u_inware.flag = 1 ) AND
	( u_inware.balcflag = 0 ) AND
	( u_inware.storageid = :arg_storageid ) AND
	( u_inware.balcdateint = 0 );
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "结存操作失败 ~n原因:"+sqlca.SQLErrText
	GOTO ext
END IF

UPDATE u_outware
	SET balcflag = 1  ,
	u_outware.balcdateint = :ls_balcdateint
	WHERE ( u_outware.flag = 1 ) AND
	( u_outware.balcflag = 0 ) AND
	( u_outware.storageid = :arg_storageid ) AND
	( u_outware.balcdateint = 0 );
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = "结存操作失败 ~n原因:"+sqlca.SQLErrText
	GOTO ext
END IF

ext:
Destroy uo_cmpl
IF rslt = 0 Or rslt = 2 THEN
	ROLLBACK;
ELSEIF rslt = 1 And arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt




end function

public function integer uof_pdb_add_assign (long arg_storageid, datetime arg_pdbdt, integer arg_ifnoallocqty, ref string arg_msg, boolean arg_ifcommit);
Int rslt = 1
Long ll_waredate
Long ls_scid
DateTime st_date,ed_date,ls_serverdate
Date ls_enddate
Long cnt
Int li_mtrlprp

ll_waredate = Long(String(arg_pdbdt,'yyyymmdd'))



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

SELECT scid,mtrlprp INTO :ls_scid,:li_mtrlprp
	FROM u_storage
	Where storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询仓库分部失败'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF


SELECT scid,mtrlprp INTO :ls_scid,:li_mtrlprp
	FROM u_storage
	Where storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询仓库分部失败'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

SELECT count(*)
	INTO :cnt
	FROM u_warepdb
	WHERE storageid = :arg_storageid
	And pdbdate = :ll_waredate;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询盘点表是否已存在失败,'+sqlca.SQLErrText
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '该仓库当前日期的盘点表已经存在,不能再建立!'
	GOTO ext
END IF


SELECT count(*)
	INTO :cnt
	FROM u_warepdb_assign
	WHERE storageid = :arg_storageid
	And pdbdate = :ll_waredate;
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询盘点表是否已存在失败,'+sqlca.SQLErrText
	GOTO ext
END IF

IF cnt > 0 THEN
	rslt = 0
	arg_msg = '该仓库当前日期的盘点表已经存在,不能再建立!'
	GOTO ext
END IF


rslt = uof_warebalc_check_inoutflag(arg_storageid,arg_pdbdt,arg_msg,0)
IF rslt = 0 Or rslt = 2 THEN
	GOTO ext
END IF



SELECT Top 1 getdate() Into :ls_serverdate From u_user  ;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询当前日期操作失败'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

ls_enddate = Date(String(arg_pdbdt,'yyyy-mm-dd'))

st_date = DateTime(RelativeDate(Date(ls_enddate),1),Time('0:0'))
ed_date = DateTime(RelativeDate(Date(ls_serverdate),1),Time('0:0'))

IF st_date > ed_date THEN
	arg_msg = '盘点日期不能大过于当前日期'
	rslt = 0
	GOTO ext
END IF


INSERT INTO u_warepdb_assign
	(pdbdate,
	scid,
	mtrlwareid,
	assignid,
	mtrlid,
	storageid,
	status,
	woodcode,
	pcode,
	plancode,
	sptid,
	mtrlcuscode,
	location,
	Dxflag,
	qty,
	factqty,
	cost,
	wareamt,
	relbillid,
	relprintid,
	relbillcode,
	pdbdt,
	pdbemp)
	SELECT :ll_waredate,
	scid, 
	mtrlwareid, 
	0 AS Assignid,
	mtrlid, 
	storageid, 
	Status,
	woodcode, 
	pcode,
	plancode, 
	Sptid, 
	mtrlcuscode,
	location,
	Dxflag,
	noallocqty - allocqty ,
	noallocqty - allocqty ,
	cost,
	wareamt, 
	0 AS Relbillid,
	0 AS Relprintid, 
	'' AS Relbillcode,
	:ls_serverdate,
	:publ_operator
	FROM u_mtrlware
	WHERE (storageid = :arg_storageid) AND
	(scid = :ls_scid) AND
	(noallocqty > 0 AND :arg_ifnoallocqty = 1 OR :arg_ifnoallocqty = 0)
	UNION ALL
	SELECT :ll_waredate,
	u_mtrlware.scid,
	u_mtrlware.mtrlwareid,
	u_mtrlware_assign.Assignid,
	u_mtrlware_assign.mtrlid,
	u_mtrlware.storageid,
	u_mtrlware.Status,
	u_mtrlware.woodcode,
	u_mtrlware.pcode,
	u_mtrlware.plancode,
	u_mtrlware.Sptid,
	u_mtrlware.mtrlcuscode,
	u_mtrlware.location,
	u_mtrlware.Dxflag,
	u_mtrlware_assign.assignqty - u_mtrlware_assign.Outqty ,
	u_mtrlware_assign.assignqty - u_mtrlware_assign.Outqty ,
	u_mtrlware.cost,
	(u_mtrlware_assign.assignqty - u_mtrlware_assign.Outqty)
	* u_mtrlware.cost AS wareamt,
	u_mtrlware_assign.Relbillid,
	u_mtrlware_assign.Relprintid,
	u_mtrlware_assign.Relbillcode,
		:ls_serverdate,
	:publ_operator
	FROM u_mtrlware_assign INNER JOIN
	u_mtrlware ON u_mtrlware_assign.Scid = u_mtrlware.scid AND
	u_mtrlware_assign.Mtrlwareid = u_mtrlware.mtrlwareid
	WHERE (u_mtrlware_assign.assignqty > u_mtrlware_assign.Outqty) AND
	(u_mtrlware_assign.assigntype = 1) AND
	(u_mtrlware.storageid = :arg_storageid) AND
	(u_mtrlware.scid = :ls_scid);
	
IF sqlca.SQLCode <> 0 THEN
	IF Pos(sqlca.SQLErrText,'Cannot insert duplicate key in') > 0 THEN
		arg_msg = '该仓库当前日期的盘点表已经存在,不能建立!'
	ELSE
		arg_msg = '生成当前期的盘点表失败!~n原因是:'+sqlca.SQLErrText
	END IF
	rslt = 0
	GOTO ext
END IF


ext:
IF rslt = 0 Or rslt = 2 THEN
	ROLLBACK;
ELSEIF rslt = 1 And arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt


end function

public function integer uof_pdb_del_assign (long arg_storageid, long arg_pdbdate, ref string arg_msg, boolean arg_ifcommit);Int rslt = 1
Long cnt = 0

IF arg_storageid <= 0 THEN
	arg_msg = '请选择要仓库'
	rslt = 0
	GOTO ext
END IF

SELECT count(*)
	INTO :cnt
	FROM u_warepdb_assign
	WHERE u_warepdb_assign.storageid = :arg_storageid
	AND u_warepdb_assign.pdbdate = :arg_pdbdate
	And flag = 1;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询操作失败,在盘点日:'+String(arg_pdbdate)+'数据资料失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt > 0 THEN
	arg_msg = '盘点日:'+String(arg_pdbdate)+',盘点数据已经审核,不能删除'
	rslt = 0
	GOTO ext
END IF


DELETE FROM u_warepdb_assign
	WHERE pdbdate = :arg_pdbdate
	And storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '删除当前期的盘点表失败!'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF


ext:
IF rslt = 0 THEN
	ROLLBACK;
ELSEIF rslt = 1 And arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt




end function

public function integer uof_pdb_audit_assign (long arg_storageid, long arg_pdbdate, string arg_opemp, ref string arg_msg, boolean arg_ifcommit);Int rslt = 1
Long cnt


SELECT count(*) INTO :cnt
	FROM u_storage
	Where storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询仓库资料失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt <> 1 THEN
	arg_msg = '仓库资料不存在或重复,请检查'
	rslt = 0
	GOTO ext
END IF


cnt = 0
SELECT count(*) INTO :cnt
	FROM u_warepdb_assign
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询盘点表失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt = 0 THEN
	arg_msg = '盘点表没有盘点内容,不能审核'
	rslt = 0
	GOTO ext
END IF

cnt = 0
SELECT count(*) INTO :cnt
	FROM u_warepdb_assign
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate
	AND flag = 1;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询盘点表是否已审核失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt > 0 THEN
	arg_msg = '盘点表已审核,不能审核'
	rslt = 0
	GOTO ext
END IF

UPDATE u_warepdb_assign
	SET flag = 1,
	auditemp = :arg_opemp,
	auditdate = getdate()
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '更新盘点表审核标记失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF


ext:
IF rslt = 0 THEN
	ROLLBACK;
ELSEIF rslt = 1 AND arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt
end function

public function integer uof_pdb_caudit_assign (long arg_storageid, long arg_pdbdate, ref string arg_msg, boolean arg_ifcommit);Int rslt = 1
Long cnt
Long ll_pdbdate
DateTime null_dt

SetNull(null_dt)


SELECT count(*) INTO :cnt
	FROM u_storage
	Where storageid = :arg_storageid;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询仓库资料失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt <> 1 THEN
	arg_msg = '仓库资料不存在或重复,请检查'
	rslt = 0
	GOTO ext
END IF


cnt = 0
SELECT count(*) INTO :cnt
	FROM u_warepdb_assign
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询盘点表失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt = 0 THEN
	arg_msg = '盘点表没有盘点内容,不能撤审'
	rslt = 0
	GOTO ext
END IF

cnt = 0
SELECT count(*) INTO :cnt
	FROM u_warepdb_assign
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate
	AND flag = 0;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询盘点表是否未审核失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt > 0 THEN
	arg_msg = '盘点表未审核,不能撤审'
	rslt = 0
	GOTO ext
END IF

//检查盘点日期后是否有结存
ll_pdbdate = Long(arg_pdbdate)

cnt = 0
SELECT COUNT(*)
	INTO :cnt
	FROM u_warebalc
	WHERE (balcdateint >= :ll_pdbdate)
	And (storageid = :arg_storageid);
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '查询盘点日期后仓库是否有结存失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF

IF cnt > 0 THEN
	arg_msg = '盘点日期后仓库已结存,不能撤审'
	rslt = 0
	GOTO ext
END IF

UPDATE u_warepdb_assign
	SET flag = 0,
	auditemp = '',
	auditdate = :null_dt
	WHERE storageid = :arg_storageid
	AND pdbdate = :arg_pdbdate;
IF sqlca.SQLCode <> 0 THEN
	arg_msg = '更新盘点表审核标记失败,'+sqlca.SQLErrText
	rslt = 0
	GOTO ext
END IF


ext:
IF rslt = 0 THEN
	ROLLBACK;
ELSEIF rslt = 1 AND arg_ifcommit THEN
	COMMIT;
END IF

RETURN rslt

end function

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

on uo_ware_pdb_balc.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