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

forward prototypes
global function long f_sys_scidentity (long arg_sc_id, string arg_tablename, string arg_columnname, ref string arg_msg, boolean arg_ifcommit, transaction arg_transaction)
end prototypes

global function long f_sys_scidentity (long arg_sc_id, string arg_tablename, string arg_columnname, ref string arg_msg, boolean arg_ifcommit, transaction arg_transaction);//f_sys_scidentity
//long arg_sc_id       :目标学校id,>=0;(如果是为取得学校id本身,可以赋0)
//string arg_tablename :目标表名称
//string arg_columnname:目标表字段名称
//return 0  //操作失败
//return >0 //操作成功返回新ID


long new_selfid=0,ls_idtop
long rslt_id=1
string str_dwSQl,str_SyntaxFromSQL
datastore ds_maxid
long ins_maxid
long add_n=1
string ls_msg=""



if arg_sc_id<0 then
	arg_msg='错误唯一编码'
	rslt_id=0
	goto ext
end if

arg_tablename=lower(arg_tablename)
arg_columnname=lower(arg_columnname)


//-------------------------------------------------------1.ID加1
update Sys_scIdentity 
	set ScIdentityno =ScIdentityno + 1
 WHERE Sys_scIdentity.Scid = :arg_sc_id and
 		 lower(Sys_scIdentity.tablename)=:arg_tablename and
		 lower(Sys_scIdentity.columnname)=:arg_columnname
 using arg_transaction;
if arg_transaction.sqlcode <>0 then
	arg_msg='更新相关唯一码记录操作失败'+arg_transaction.sqlerrtext
	rslt_id=0
	goto ext
end if

//-------------------------------------------------------2.如果无则加入新ID
if arg_transaction.sqlnrows=0 then
	new_selfid=0
	insert into Sys_scIdentity (id,Scid,ScIdentityno,tablename,columnname)
	values (:new_selfid,:arg_sc_id,11,:arg_tablename,:arg_columnname)
	using arg_transaction;
	//预留1~10的id作系统约定的预设数据id
	if arg_transaction.sqlcode<>0 then  
		arg_msg='新建相关唯一码记录操作失败'+arg_transaction.sqlerrtext
		rslt_id=0
		goto ext
	end if
end if

//-------------------------------------------------------3.查询最新ID
SELECT top 1 Sys_scIdentity.ScIdentityno  
INTO :rslt_id  
FROM Sys_scIdentity  
WHERE Sys_scIdentity.Scid = :arg_sc_id and
		lower(Sys_scIdentity.tablename)=:arg_tablename and
		lower(Sys_scIdentity.columnname)=:arg_columnname
using arg_transaction;

if arg_transaction.sqlcode<>0 then //---------查询ID失败
	arg_msg='查询相关唯一码记录操作失败'+arg_transaction.sqlerrtext
	rslt_id=0
	goto ext
end if

//-------------------------------------------------------4.建立查找最大ID_dw
ds_maxid= create datastore
str_dwSQl='select max('+arg_columnname+') as maxid from '+arg_tablename	
str_SyntaxFromSQL =sqlca.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg)//本地检查
if ls_msg>'' then
	arg_msg="dw语法生成失败"	
	rslt_id=0
	goto ext
end if
		
ds_maxid.Create(str_SyntaxFromSQL,ls_msg)
if ls_msg>'' then
	arg_msg="建立相关DW失败"
	rslt_id=0
	goto ext
end if
ds_maxid.settransobject(arg_transaction)
ds_maxid.retrieve()		
ins_maxid=ds_maxid.GetItemNumber(1,1)
if isnull(ins_maxid) then
	ins_maxid=10
end if		
destroy ds_maxid

//--------------------------------------------------------5.比较ID
if ins_maxid < rslt_id then
	goto ext
else
	add_n=ins_maxid - rslt_id + 1
			
	update Sys_scIdentity 
	set ScIdentityno =ScIdentityno + :add_n 
	WHERE Sys_scIdentity.Scid = :arg_sc_id and
			lower(Sys_scIdentity.tablename)=:arg_tablename and
			 lower(Sys_scIdentity.columnname)=:arg_columnname 
	using arg_transaction;
	if arg_transaction.sqlcode <>0 then
		arg_msg='更新相关唯一码记录操作失败'+arg_transaction.sqlerrtext
		rslt_id=0
		goto ext
	end if
	
	rslt_id=rslt_id + add_n
	goto ext
end if

//-----------------------------------------------------------
ext:
if isnull(rslt_id) then rslt_id = 0 
if arg_ifcommit and  rslt_id>0 then
	commit using arg_transaction;
end if
if rslt_id=0 then
	rollback using arg_transaction;		
end if

return rslt_id


end function

