db.sql

短信猫发送短信,使用PDU编码格式

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[log_SimCard_Send]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[log_SimCard_Send]
GO

CREATE TABLE [dbo].[log_SimCard_Send] (
	[id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[simSn] [varchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[tDate] [datetime] NOT NULL ,
	[sendCount] [int] NOT NULL ,
	[SendType] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[log_SimCard_Send] WITH NOCHECK ADD 
	CONSTRAINT [PK_log_SimCard_Send] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO








if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[simCard_GetTodyCanSendCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[simCard_GetTodyCanSendCount]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[simCard_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[simCard_Update]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/*
exec simCard_GetTodyCanSendCount '121212','sms'
*/



CREATE  proc simCard_GetTodyCanSendCount
	 @Sim_SN varchar(300)
	,@SendType varchar(300)
as
set nocount on
declare @MaxSendCount  bigint,@ID bigint,@CanSendCount bigint
select @ID=0, @MaxSendCount=300,@CanSendCount=0


--select * from log_SimCard_Send



select @ID=id,@CanSendCount=@MaxSendCount-sendCount from log_SimCard_Send where datediff(day,tDate,getdate())=0 and simSn=@Sim_SN
if @ID=0
begin
	set @CanSendCount=@MaxSendCount
	insert into log_SimCard_Send (simSn,tDate,sendCount,SendType)
	values(@Sim_SN,getdate(),0,@SendType)
end


select CanSendCount=@CanSendCount
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE proc simCard_Update
	 @Sim_SN varchar(300)
	,@SendType varchar(300)
	,@Count	int
as
set nocount on


update log_SimCard_Send set sendCount=sendCount+@Count
	where datediff(day,tDate,getdate())=0 and simSn=@Sim_SN

 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO