delphi 5.0 +SQL server

use OPETMIS go EXECUTE sp_configure 'allow updates', 1 declare @res int select @res = charindex('SQL Server/11', @@version) if @res = 0 begin print ' RECONFIGURE' RECONFIGURE WITH OVERRIDE end go print 'procgetmaxid' /* ** dbo.procgetmaxid **********/ SETUSER 'dbo' go create procedure procgetmaxid @tablename varchar(60), @maxid int output as begin /*取任意表的最大单据号，@tablebname为表名，@maxid为返回的最大ID号*/ begin tran select @maxid=fmaxid from tbes_billno where ftablename=@tablename if @maxid=null begin select @maxid=fmaxid from tbes_billno where ftablename='tbes_billno' if @maxid=null begin if @tablename<>'tbes_billno' begin insert into tbes_billno(fid,ftablename,fmaxid) values(1 ,'tbes_billno',2) select @maxid=1 end end else begin update tbes_billno set fmaxid=fmaxid+1 where ftablename='tbes_billno' insert into tbes_billno(fid,ftablename,fmaxid) values(@maxid ,@tablename,2) end select @maxid=1 end else update tbes_billno set fmaxid=fmaxid+1 where ftablename=@tablename if @@error <>0 begin rollback tran commit tran raiserror 900001 '取最大ID号失败' return -1 end else begin commit tran end end go SETUSER go UPDATE sysobjects SET userstat = 0 where id = object_id('dbo.procgetmaxid') go print 'procgetmaxid1' /* ** dbo.procgetmaxid1 **********/ SETUSER 'dbo' go create procedure procgetmaxid1 @tablename char(60), @maxid int output as begin select @maxid=1 end go SETUSER go UPDATE sysobjects SET userstat = 0 where id = object_id('dbo.procgetmaxid1') go print 'sp_addstreamtrend' /* ** dbo.sp_addstreamtrend **********/ SETUSER 'dbo' go create procedure sp_addstreamtrend (@pitemid int, @ptime datetime, @pcheckerid int, @pcheckposid int, @pchecknum int ) as /*@pitemid int, 服务项目ID @ptime datetime, 时间 @pcheckerid int, 检票人ID @pcheckposid int, 检票机 ID @pchecktype int 检票类型 1入检 -1出检*/ declare @tchecktype int declare @ttrendid int declare @tstreamid int declare @tdate datetime declare @tinsum int declare @toutsum int begin tran select @tdate=convert(datetime, convert(char(10),@ptime,112),2) select @tstreamid=fstreamid,@tinsum=finsum,@toutsum=foutsum from tber_gueststream where fitemid=@pitemid and fcheckerid=@pcheckerid and fcheckposid=@pcheckposid and fdate=@tdate if @@rowcount=0 begin exec procgetmaxid 'tber_gueststream',@tstreamid output if @pchecknum<0 begin select @toutsum=-@pchecknum,@tinsum=0 select @tchecktype=-1 end else begin select @tinsum=@pchecknum,@toutsum=0 select @tchecktype=1 end insert into tber_gueststream(fstreamid,fitemid,fcheckerid,fcheckposid,fdate,finsum,foutsum) values(@tstreamid,@pitemid,@pcheckerid,@pcheckposid,@tdate,@tinsum,@toutsum) end else begin if @pchecknum<0 select @toutsum=@toutsum-@pchecknum else select @tinsum=@tinsum+@pchecknum update tber_gueststream set finsum=@tinsum,foutsum=@toutsum where fstreamid=@tstreamid end exec procgetmaxid 'tber_streamtrend',@ttrendid output insert into tber_streamtrend(ftrendid,fitemid,fcheckerid,fcheckposid,ftime,finsum,foutsum,fonline,fchecktype) values(@ttrendid,@pitemid,@pcheckerid,@pcheckposid,@ptime,@tinsum,@toutsum,@tinsum-@toutsum,@tchecktype) if @@error<>0 begin rollback return(9) end else commit go SETUSER go UPDATE sysobjects SET userstat = 0 where id = object_id('dbo.sp_addstreamtrend') go print 'sp_addtable' /* ** dbo.sp_addtable **********/ SETUSER 'dbo' go create procedure sp_addtable @poldtablename char(20), @pnewtablename char(20), @pnewtableno char(20) as begin /*declare @poldtablename char(20) declare @pnewtablename char(20) declare @pnewtableno char(20) select @poldtablename='tber_ticketsell' select @pnewtablename='tbeh_ticketsell' select @pnewtableno='1202'*/ select * into #temp1 from sysz_table_fieldnote where ftablename=@poldtablename update #temp1 set ftablename=@pnewtablename,ftable=@pnewtableno insert into sysz_table_fieldnote(fcode,fname,fnote,fmemo,ftable,ftype,flinktable,flength,flinkfield,fnull,flocalfield,fedit, fvisible,fexit,fsave,ftag,fviewlength,ftermfield,ftermvalue,ftablename,flinkkeyfield,fdefault,fprintlength) select fcode,fname,fnote,fmemo,ftable,ftype,flinktable,flength,flinkfield,fnull,flocalfield,fedit, fvisible,fexit,fsave,ftag,fviewlength,ftermfield,ftermvalue,ftablename,flinkkeyfield,fdefault,fprintlength from #temp1 drop table #temp1 end go SETUSER go UPDATE sysobjects SET userstat = 0 where id = object_id('dbo.sp_addtable') go print 'sp_addticketsell' /* ** dbo.sp_addticketsell **********/ SETUSER 'dbo' go create procedure sp_addticketsell @pticketid int as /*重处理全部销售*/ declare @ttickettypeid int /*票种*/ declare @tdate datetime /*日期*/ declare @tsaleposid int /*POS*/ declare @tsalerid int /*售票员*/ declare @tamount int /*数量*/ declare @tsumagio money /*优惠*/ declare @tmoney money /*金额*/ declare @tcommid int /*群体*/ declare @tclientid int /*客户*/ declare @tpaywayid int /*支付方式*/ select @ttickettypeid=b.ftickettypeid,@tdate=isnull(a.fdate,getdate()),@tsaleposid=a.fsaleposid,@tsalerid=a.foperator ,@tamount=b.famount,@tsumagio=fprice*famount-b.fsale,@tmoney=b.fsale,@tcommid=b.fcommtypeid,@tclientid=a.fclientid,@tpaywayid=a.fpaywayid from tbeu_ticketbill a,tbeu_ticket b where b.fticketbillid=a.fticketbillid and b.fticketid=@pticketid declare @tticketsaleid int select @tdate=convert(datetime, convert(char(10),@tdate,112),2) begin tran select @tticketsaleid=fticketsaleid from tber_ticketsell where ftickettypeid=@ttickettypeid and fdate=@tdate and fsaleposid=@tsaleposid and fsalerid=@tsalerid and fcommtypeid=@tcommid and fclientid=@tclientid /* and fpaywayid=@tpaywayid */ if @@rowcount=0 begin exec procgetmaxid 'tber_ticketsell',@tticketsaleid output insert into tber_ticketsell(fticketsaleid,ftickettypeid,fdate,fsaleposid,fsalerid,famount,fsumagio,fmoney,fcommtypeid,fclientid,fpaywayid) values(@tticketsaleid,@ttickettypeid,@tdate,@tsaleposid,@tsalerid,@tamount,@tsumagio,@tmoney,@tcommid,@tclientid,@tpaywayid) end else begin update tber_ticketsell set famount=famount+@tamount,fsumagio=fsumagio+@tsumagio,fmoney=fmoney+@tmoney where fticketsaleid=@tticketsaleid end if @tpaywayid=1 begin update tber_ticketsell set fpayway1=isnull(fpayway1,0)+@tmoney where fticketsaleid=@ttick