sql

提取最高价格
CREATE procedure key_price
@keyword varchar(50)
as
declare @priceDb money
begin
select @priceDb = max(price) from tbl_keyword_info  where keyword=@keyword
if  @priceDb is null
begin

  set @priceDb=0.30
end
else
set @priceDb=@priceDb + 0.01
select @priceDB
end
GO
-------
CREATE procedure font_key
@keyword varchar(50),
@temp char(10),
@title varchar(50),
@url varchar(200),
@explain varchar(50),
@auto char(10),
@highPrice money,
@username varchar(50)

as

 
declare @priceDb money
declare @useridDb numeric
declare @company varchar(50)
begin

select @priceDb = max(price) from tbl_keyword_info  where keyword=@keyword
if  @priceDb is null
begin

  set @priceDb=0.30
end
else
set @priceDb=@priceDb + 0.01
select @priceDB

select @useridDb= id from tbl_customer_info where username=@username

select @company= company from tbl_customer_info where username=@username

insert into tbl_keyword_info  (customer_id,keyword,price,company,url,jy_way,topprice,commend,title,explian)
values (@useridDb,@keyword,@priceDb,@company,@url,@auto,@highPrice,@temp,@title,@explain)
end
GO

----用户登陆
reate procedure login
@username varchar(50),
@password varchar(50)
as
declare @sign int
declare @customer numeric
declare @pwd varchar(50)
declare @name varchar(20)
declare @author varchar(40)
set @sign=0
if exists(select username from tbl_customer_info where username=@username)
  begin
 select @customer=id,@pwd=password,@name=company,@author=lxr from tbl_customer_info where username=@username
set @sign=-1
end

if @pwd=@password
select @customer as customer,@name as name , @author as author
else
select @sign

GO
----------点击几数
CREATE PROCEDURE pay_list 
@username varchar(50),
@pay_time  datetime,
@account_num varchar(50) ,
@accept_pay  varchar(50),
@class varchar(20),
@method_pay varchar(20),
@outlay money

AS
begin
declare @customer_id numeric
declare @sign int
set @sign=0

 if exists( select username from tbl_customer_info where username=@username)
set @sign=1
if @sign=1
begin
select @customer_id=[id] from  tbl_customer_info where username=@username
       insert into tbl_pay_list (customer_id,outlay,pay_time,account_num,username,accept_pay,class,method_pay) values (@customer_id,@outlay,@pay_time,@account_num,@username,@accept_pay,@class,@method_pay)
end
else
begin
select @sign as sign1
end
end
GO
-----用户注册
CREATE procedure register
@username varchar(50),
@password varchar(50),
@company  varchar(200),
@address  varchar(100),
@post_code char(8),
@url varchar(200),
@lxr varchar(20),
@phone  varchar(15),
@fax varchar(15),
@Email varchar(50),
@other_contact_way varchar(50),
@bz varchar(8000),

@reg_time datetime
as
declare @sign int
set @sign=0
if exists(select username from tbl_customer_info where username=@username)
set @sign=-1 --该用户已经存在
if @sign=-1
begin
select @sign
 return
end
else
  begin
  insert into tbl_customer_info(username,[password],company,address,post_code,url,lxr,phone,fax,Email,other_contact_way,bz,reg_time)values(@username,@password,@company,@address,@post_code,@url,@lxr,@phone,@fax,@Email,@other_contact_way,@bz,@reg_time)
select @@identity
end
GO

---------
CREATE procedure save_client_info
@customer_id numeric,
@keyword varchar(100),
@ipaddress varchar(20)
as
 --if exists(select id from tbl_customer_info where id=@customer_id)
declare @sign int --表示是否需要发送邮件 100为需要
declare @sign1 int  --表示是否发过邮件的用户 1为发送过 0为未发过
declare @total money
declare @price money
declare @group varchar (50)
declare  @Email varchar(50)
declare  @url varchar(200)
select @group=[group] from tbl_keyword_info where customer_id=@customer_id and keyword=@keyword  --取关键字的分组
select @price=price from tbl_keyword_info where customer_id=@customer_id and keyword=@keyword       --取关键自的当前价格
update tbl_customer_info set total=total-@price where id=@customer_id   ----扣除用户余额
insert into tbl_vis_info (customer_id,keyword,[group],ipaddress,price) values (@customer_id,@keyword,@group,@ipaddress,@price) --插人费用扣除标
insert into history_vis_info (customer_id,keyword,[group],ipaddress,price) values (@customer_id,@keyword,@group,@ipaddress,@price) --插人历史费用扣除表
select @total=total from tbl_customer_info where id=@customer_id         ---取出余额
select  @sign1=sendemail from tbl_customer_info where id=@customer_id  --取出是否发送过邮件
select  @Email=Email from tbl_customer_info where id=@customer_id
select  @url=url from  tbl_keyword_info where customer_id=@customer_id and keyword=@keyword
set @sign=100
if @total<=@sign and @sign1=0
begin
select 1 , @Email,@url
end
else
select 0 , @Email,@url
GO
---添加关键字
CREATE PROCEDURE tj_keyword
@keyword varchar(50),
@url varchar(100),
@explian varchar (200),
@id numeric
AS
begin
declare @priceDb money
declare @company varchar(200)
declare  @title  varchar(50)
select @priceDb = max(price) from tbl_keyword_info  where keyword=@keyword
if  @priceDb is null
begin
 set @priceDb=0.30
end
else
set @priceDb=@priceDb + 0.01
select @priceDb
select  @company =company   from  tbl_customer_info where id=@id
select @title=title from   tbl_keyword_info where customer_id=@id
   insert into tbl_keyword_info  (customer_id,keyword,[group],price,company,url,status,jy_way,commend,title,explian) values (@id,@keyword,'客服推荐',@priceDb,@company,@url,'5','手动','需要', @title,@explian)
end
GO

----修改关键字
CREATE procedure update_keyword
@customer_id1 int,
@key varchar(50),
@jy_way1 char(10),
@topprice1 money

as

 
declare @priceDb money

begin

select @priceDb = max(price) from tbl_keyword_info  where keyword=@key
if  @priceDb is null
begin

  set @priceDb=0.30
end
else
set @priceDb=@priceDb + 0.01
select @priceDb

update tbl_keyword_info set jy_way=@jy_way1,price=@priceDb,topprice=@topprice1 where
keyword=@key and customer_id=@customer_id1
end
GO

------删除
create procedure DelBigClass
@bigID bigint
as
declare @Hit int
begin
--删除大类
--删除revertable表
delete from revertable
where infotableid in
(select id as infotableid from infotable where bigclassid=@BigID)
--删除infotable表
delete from infotable where bigclassid=@BigID
--删除smallClassTable表
delete from smallClassTable where BigClassID=@BigID
--删除bigClassTable表
delete from BigClassTable where BigClassID=@BigID

end
GO
-----------插入信息 并返会ID
CREATE   procedure fb_InfoTable
@Uname varchar(50),
@Title varchar(100),
@Content Ntext,
@UserIP  varchar(50),
@ImgAddres varchar(50),
@SmallClassID bigint,
@BigClassID   bigint

as

declare @Uimg varchar(50)
declare @UserStatus varchar(50)
select @Uimg=HeadImg,@UserStatus=author from UserTable where UName=@Uname
if @UserStatus='1'
 begin
 set @UserStatus='管理员' 
 end
if @UserStatus='2'
 begin
 set @UserStatus='版主' 
 end
if @UserStatus='3'
 begin
 set @UserStatus='高级用户' 
 end
if @UserStatus='4'
 begin
 set @UserStatus='中级用户' 
 end
if @UserStatus='5'
 set @UserStatus='初级用户' 
insert into  InfoTable(Uname,Title,Content,Uimg,UserIP,ImgAddres,UserStatus,SmallClassID,BigClassID) values(@Uname,@Title,@Content,@Uimg,@UserIP,@ImgAddres,@UserStatus,@SmallClassID,@BigClassID) select @@identity as ' identity'
GO
-----统计点击
create procedure hit
@infoId bigint

as

declare @Hit int

begin

select @Hit = hit from infoTable  where [id]=@infoId
if  @Hit=0
begin
  set @Hit=1
end
else
 set @Hit=@Hit+1
update InfoTable set Hit=@Hit where [id]=@infoId

end

GO
------换算用户级别
create procedure upgrade

@uname varchar(50)

as

declare @Integral varchar(50)
declare @author varchar(50)

begin

select @Integral = Integral from UserTable  where UName=@uname
if  @Integral>1999 and @Integral!=1 and @Integral!=2
begin
  set @author=3
end

if  @Integral>199 and @Integral!=1 and @Integral!=2 and @Integral<2000
begin
  set @author=4
end
else
 set @author=5
update usertable set author=@author  where UName=@uname

end

GO
------------自动价格
CREATE PROCEDURE   Auto_prcie
@keyword varchar(200)
as
declare @id bigint
declare @price money
declare @jy_way char(10)
declare @sign  bigint

declare @id1 bigint
declare @price1 money
declare @jy_way1 char(10)

declare @id2 bigint
declare @price2 money
declare @jy_way2 char(10)
declare @time int
set @time=0
set @id2=0
set @sign=0

--游标开始
declare wsql cursor for select id,price,jy_way from (select id,price as price,jy_way from tbl_keyword_info where keyword=@keyword and jy_way='手动'
union
select id,topprice as price,jy_way from tbl_keyword_info where keyword=@keyword and jy_way='自动') as tempt order by price desc

open wsql
while @@fetch_status=0--游标成功
begin
if @time>=0
begin

set @time=@time + 1

if @time=1
  begin 
    set @id1=@id
    set @price1=@price
    set @jy_way1=@jy_way
  end

if @time>=2
begin

      set @id2=@id
      set @price2=@price
      set @jy_way2=@jy_way

   if @price1>@price2
     begin
       if @sign<>@id1
        begin
        update tbl_keyword_info set price=@price2+0.01 where id=@id1 and jy_way='自动'
-- set @sign=@id1        
       end
     end
    if @price1=@price2
       begin
         update tbl_keyword_info set price=@price2 where id=@id1 and jy_way='自动'
         update tbl_keyword_info set price=@price2 where id=@id2 and jy_way='自动' 
         set @sign=@id2
       end
      set @id1=@id2
      set @price1=@price2
      set @jy_way1=@jy_way2

    
select @id1,@price1,@jy_way1
end
end
--比较
--end
--比较
--end

fetch next from wsql
into @id,@price,@jy_way

end

close wsql
deallocate wsql
--游标结束
GO
-----统计数据
CREATE PROCEDURE  tongj_data
as

begin

insert into tbl_report_day(customer_id,vis_time ,vis_num,pay_total,vis_num_ip,keyword_num,ave_price)
select customer_id,convert(char(10),vis_time ,20) as vis_time,count(ipaddress) as vis_num,pay_total=count(ipaddress)*avg(price) ,count(distinct ipaddress) as vis_num_ip,keyword_num=count(distinct keyword),ave_price=avg(price) from tbl_vis_info
where convert(char(10),vis_time,20)=convert(char(10),getdate()-1,20)
group by customer_id,convert(char(10),vis_time ,20)

insert into tbl_group_report(customer_id,vis_time ,[group],price,ipaddress_num,keyword,pay_total)
select customer_id,convert(char(10),vis_time ,20) as vis_time,[group],price ,count(distinct ipaddress) as ipaddress_num,keyword,pay_total=count(ipaddress)*avg(price)from tbl_vis_info
where convert(char(10),vis_time,20)=convert(char(10),getdate()-1,20)
group by customer_id,convert(char(10),vis_time ,20),[group],price,keyword

insert into tbl_keyword_report(customer_id,vis_time ,vis_num,pay_total,ipaddress_num,keyword,price)
select customer_id,convert(char(10),vis_time ,20) as vis_time,count(ipaddress) as vis_num,pay_total=count(ipaddress)*avg(price) ,count(distinct ipaddress) as ipaddress_num,keyword,price from tbl_vis_info
where convert(char(10),vis_time,20)=convert(char(10),getdate()-1,20)
group by customer_id,convert(char(10),vis_time ,20),keyword,price
end
--delete from tbl_vis_info where convert(char(10),vis_time,20)=convert(char(10),getdate()-1,20)
GO