aa.rar_广告

关于SmartAD5.0数据库升级说明: 如果您是使用智向广告管理（SmartAD）V5.0版本中的数据库备份（SQL2005和SQL2000），我们建议您执行下面的步骤.修改了广告条统计中“今日点击数”的Bug。 操作步骤: (1).打开SQL Server Management Stutio(2005)或SQL企业管理器(2000)，展开“智向管理系统（SmartAD）”所使用的数据库。 (2).新建一个SQL语句查询,把下面的SQL复制到查询语句中执行即可. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Lenson> -- Create date: <2006-9-17> -- Description: <往统计表"Sta"插入记录，修改[SAD_Cofig],[SAD_Adv]中相应的记录> -- ============================================= ALTER PROCEDURE [dbo].[SP_Insert2Sta] -- Add the parameters for the stored procedure here @IP varchar(20),@Hit2Page varchar(max),@Vp varchar(max), @Provcity varchar(50),@Browser varchar(50),@OS varchar(50), @AdvID int,@AdpID int, @AdID int,@iType char(1) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @retcode int declare @iDate varchar(10) declare @ChargeType char(1) select @retcode = 1 select @iDate = Convert(varchar(10),getdate(),120) Begin Tran -- Insert statements for procedure here Select Top 1 TodayDate From [SAD_AD] where ID=@AdID AND TodayDate=Convert(varchar(10),getdate(),120) if @@rowcount = 1 begin if @iType='0' begin Update [SAD_AD] Set ViewToday=ViewToday+1 where ID=@AdID if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end end else if @iType='1' begin Update [SAD_AD] Set ClickToday=ClickToday+1 where ID=@AdID if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end end end else begin if @iType='0' begin Update [SAD_AD] Set ViewToday=1,ClickToday=0,TodayDate=Convert(varchar(10),getdate(),120) where ID=@AdID if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end end else if @iType='1' begin Update [SAD_AD] Set ClickToday=1,TodayDate=Convert(varchar(10),getdate(),120) where ID=@AdID if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end end end if @iType='0' begin Update [SAD_ADP] Set Total_View=Total_View+1 where ID=@AdpID if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end Update [SAD_AD] Set Total_View=Total_View+1 where ID=@AdID if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end end else if @iType='1' begin Update [SAD_ADP] Set Total_Click=Total_Click+1 where ID=@AdpID if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end Update [SAD_AD] Set Total_Click=Total_Click+1 where ID=@AdID if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end end else if @iType='2' begin Update [SAD_ADP] Set Total_Action=Total_Action+1 where ID=@AdpID if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end Update [SAD_AD] Set Total_Action=Total_Action+1 where ID=@AdID if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end end if((Select count(1) sDate From [SAD_UseNum] Where sDate =@iDate) =1) begin Update [SAD_UseNum] Set UseNum=UseNum+1 Where sDate =@iDate if @@error<>0 --有错误进行回滚 begin select @retcode=0 goto L_end end end else begin Insert into [SAD_UseNum] (sDate,UseNum) Values (@iDate,1) if @@error<>0 --有错误进行回滚 begin select @retcode=0 goto L_end end end declare @Cost int DECLARE csAccount CURSOR FOR --　０显示；１点击;2 效果 SELECT Top 1 Cost,ChargeType FROM [SAD_ADP] Where ID=@AdpID --ORDER BY au_lname OPEN csAccount -- Perform the first fetch. FETCH NEXT FROM csAccount INTO @Cost,@ChargeType -- Check @@FETCH_STATUS to see if there are any more rows to fetch. if @@FETCH_STATUS = 0 BEGIN -- 当广告位设置的扣费类型与本次记录的类型一致时 -- 才进行广告条的扣点行为和加上已经使用的点数 if @ChargeType = @iType BEGIN if((select count(1) From [SAD_AD] Where ID=@AdID)=1) begin Update [SAD_AD] Set ValuePoint=ValuePoint-@Cost,AllCostPoint=AllCostPoint+@Cost Where ID=@AdID if @@error<>0 --有错误进行回滚 begin select @retcode=0 goto L_end end end else begin select @retcode=0 goto L_end end END if @ChargeType != @iType BEGIN set @Cost = 0 END Insert Into [SAD_Sta] (IP,iDate,Hit2Page,Vp,Provcity,Browser,OS,AdvID,AdpID,AdID,iType,Cost) Values (@IP,getdate(),@Hit2Page,@Vp,@Provcity,@Browser,@OS,@AdvID ,@AdpID, @AdID,@iType,@Cost) if @@error<>0 --有错误进行回滚 begin select @retcode = 0 goto L_end end End CLOSE csAccount DEALLOCATE csAccount L_end: if @retcode = 1 commit tran else rollback tran END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO