public List <Push_GoldBook_Model> GoldAddList()
{
List <Push_GoldBook_Model> list = new List <Push_GoldBook_Model>();
string sql = @"SELECT DISTINCT UsedId FROM dbo.GoldBook WHERE Status = 1 AND UsedOrder IS NULL AND UsedId > 0 AND ActivateTime BETWEEN DATEADD(HOUR,-@HOUR,GETDATE()) AND DATEADD(HOUR,-1,GETDATE())";
SqlParameter[] para = null;
if (DateTime.Now.Hour == Config.JushAddGoldBookRemindTimeHour1 && Config.IsALLWork())
{
para = new SqlParameter[]
{
new SqlParameter("@HOUR", SqlDbType.Int)
{
Value = 25 - Config.JushAddGoldBookRemindTimeHour2 + Config.JushAddGoldBookRemindTimeHour1
}
};
}
if (DateTime.Now.Hour == Config.JushAddGoldBookRemindTimeHour2 && Config.IsALLWork())
{
para = new SqlParameter[]
{
new SqlParameter("@HOUR", SqlDbType.Int)
{
Value = Config.JushAddGoldBookRemindTimeHour2 - Config.JushAddGoldBookRemindTimeHour1 + 1
}
};
}
if (para != null)
{
using (SqlDataReader dr = SqlDBProvider.ExecuteReader(sql, para))
{
list = dr.ConvertToList <Push_GoldBook_Model>();
}
}
return(list);
}
/// <summary>
/// 定时发送更改数据库
/// </summary>
/// <param name="MessageID"></param>
/// <param name="ErrorCode"></param>
/// <param name="Id"></param>
/// <returns></returns>
public int PushTimingUpdateStatu(long MessageID, int ErrorCode, int Id)
{
string sql = @"UPDATE dbo.SC_APPPush SET MessageID=@MessageID,ErrorCode=@ErrorCode WHERE Id=@Id";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@MessageID", SqlDbType.BigInt)
{
Value = MessageID
},
new SqlParameter("@ErrorCode", SqlDbType.Int)
{
Value = ErrorCode
},
new SqlParameter("@Id", SqlDbType.Int)
{
Value = Id
}
};
if (SqlDBProvider.ExecuteNonQuery(sql, para) > 0)
{
return(1);
}
else
{
return(0);
}
}
public List <Push_Model> PushTiming()
{
string sql = @"SELECT * FROM dbo.SC_APPPush WHERE SendTime BETWEEN GETDATE() AND DATEADD(SECOND,@SECOND,GETDATE()) AND SendType=1 AND Status=0";
List <Push_Model> list = new List <Push_Model>();
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@SECOND", SqlDbType.Int)
{
Value = Config.JushTimingInteval / 1000
}
};
using (SqlDataReader dr = SqlDBProvider.ExecuteReader(sql, para))
{
list = dr.ConvertToList <Push_Model>();
}
return(list);
}
public List <Push_NotLoginUser_Model> NotLoginUserList()
{
string sql = @"SELECT Id FROM dbo.UserBase WHERE DATEDIFF(DAY,LastLoginTime,GETDATE())%@DAY=0 AND Status=0";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@DAY", SqlDbType.Int)
{
Value = Config.JushNotLoginUsersDay
}
};
List <Push_NotLoginUser_Model> list = new List <Push_NotLoginUser_Model>();
using (SqlDataReader dr = SqlDBProvider.ExecuteReader(sql, para))
{
list = dr.ConvertToList <Push_NotLoginUser_Model>();
}
return(list);
}
public List <Push_OrderOut_Model> OrderOutList()
{
List <Push_OrderOut_Model> list = new List <Push_OrderOut_Model>();
string sql = @"SELECT DISTINCT UserId FROM dbo.Orderinfo WHERE dbo.GetOrderStatus(Id)=1 AND Status=0 AND Pay_time IS NULL AND DATEADD(HOUR,24,Order_time) BETWEEN DATEADD(HOUR,1,GETDATE()) AND DATEADD(HOUR,@HOUR,GETDATE())";
SqlParameter[] para = null;
if (DateTime.Now.Hour == Config.JushOrderTimeOutRemindHourHour1 && Config.IsALLWork())
{
para = new SqlParameter[]
{
//第一个推送 12:00 推送 13:00--22:00的订单
new SqlParameter("@HOUR", SqlDbType.Int)
{
Value = Config.JushOrderTimeOutRemindHourHour2 - Config.JushOrderTimeOutRemindHourHour1 + 1
}
};
}
if (DateTime.Now.Hour == Config.JushOrderTimeOutRemindHourHour2 && Config.IsALLWork())
{
//第第二个推送 21:00 推送 21:00--12:00的订单
para = new SqlParameter[]
{
new SqlParameter("@HOUR", SqlDbType.Int)
{
Value = 25 - Config.JushOrderTimeOutRemindHourHour2 + Config.JushOrderTimeOutRemindHourHour1
}
};
}
if (para != null)
{
using (SqlDataReader dr = SqlDBProvider.ExecuteReader(sql, para))
{
list = dr.ConvertToList <Push_OrderOut_Model>();
}
}
return(list);
}
/// <summary>
/// 获取待推送的优惠卷用户信息
/// </summary>
/// <returns></returns>
public DataSet GetRemindData()
{
#region sql
string sqltext = @"DECLARE @tmible_df1 TABLE
(
id INT PRIMARY KEY
IDENTITY(1, 1) ,
mobile CHAR(11)
)
INSERT INTO @tmible_df1
( mobile
)
SELECT DISTINCT
dbo.UserBase.D_MobilePhone
FROM dbo.Orderinfo
INNER JOIN dbo.UserBase ON dbo.Orderinfo.UserId = dbo.UserBase.Id
WHERE Pay_status = 1
AND DATEDIFF(HOUR, Order_time, GETDATE()) >= @_timeouthnum
AND LastPushTime IS NULL
AND DATEADD(mi, dbo.GetTimeOut(2), Order_time) >= GETDATE()
AND dbo.UserBase.Status = 0
AND dbo.UserBase.D_MobilePhone IS NOT NULL
UPDATE dbo.Orderinfo
SET LastPushTime = GETDATE()
WHERE Pay_status = 1
AND DATEDIFF(HOUR, Order_time, GETDATE()) >= @_timeouthnum
AND LastPushTime IS NULL
AND DATEADD(mi, dbo.GetTimeOut(2), Order_time) >= GETDATE()
DECLARE @sendmobiles TABLE ( mobiles NVARCHAR(MAX) );
DECLARE @t_count FLOAT ,
@t_pagesize FLOAT ,
@t_pageindex INT ,
@t_pagecount INT;
SET @t_pageindex = 1;
SET @t_pagesize = @_cpageCount;
SELECT @t_count = COUNT(*)
FROM @tmible_df1
SET @t_pagecount = CEILING(@t_count / @t_pagesize);
WHILE @t_pageindex <= @t_pagecount
BEGIN
DECLARE @t_mobiles NVARCHAR(MAX)
SET @t_mobiles = NULL;
IF @t_pageindex = 1
SELECT @t_mobiles = ISNULL(( @t_mobiles + ',' ), '') + mobile
FROM ( SELECT TOP ( CAST(@t_pagesize AS INT) )
mobile
FROM @tmible_df1
) AS tpagedata
ELSE
BEGIN
SELECT @t_mobiles = ISNULL(( @t_mobiles + ',' ), '') + mobile
FROM ( SELECT TOP ( CAST(@t_pagesize AS INT) )
mobile
FROM @tmible_df1
WHERE id > ( SELECT MAX(Id)
FROM ( SELECT TOP ( ( @t_pageindex
- 1 )
* CAST(@t_pagesize AS INT) )
id
FROM @tmible_df1
ORDER BY id ASC
) AS tpagedatalins
)
) AS tpagedata
END
INSERT INTO @sendmobiles
( mobiles )
VALUES ( @t_mobiles )
SET @t_pageindex = @t_pageindex + 1;
END
SELECT mobiles
FROM @sendmobiles";
#endregion
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@_cpageCount", SqlDbType.Int, 4)
{
Value = Config.MsgSendMaxCount
},
new SqlParameter("@_timeouthnum", SqlDbType.Int, 4)
{
Value = Config.OrderTimeOutRemindHourNum
}
};
DataSet ds = SqlDBProvider.ExecuteSelect(sqltext, para);
return(ds);
}
/// <summary>
/// 获取待推送的优惠卷用户信息
/// </summary>
/// <returns></returns>
public DataSet GetRemindData()
{
#region sql
string sqltext = @"DECLARE @tmible_df1 TABLE
(
id INT PRIMARY KEY
IDENTITY(1, 1) ,
mobile CHAR(11)
)
INSERT INTO @tmible_df1
( mobile
)
SELECT DISTINCT
D_MobilePhone
FROM dbo.UserBase
WHERE Status = 0
AND D_MobilePhone IS NOT NULL
AND EXISTS ( SELECT UsedId
FROM ( SELECT DISTINCT
UsedId
FROM dbo.GoldBook
WHERE Status = 1
AND UsedOrder IS NULL
AND UsedId > 0
AND DATEDIFF(DAY,
GETDATE(),
EndTime) = @_cGoldBookRemindDayNum
) AS gbb
WHERE gbb.UsedId = dbo.UserBase.Id )
DECLARE @sendmobiles TABLE(mobiles NVARCHAR(MAX));
DECLARE @t_count FLOAT ,
@t_pagesize FLOAT ,
@t_pageindex INT ,
@t_pagecount INT;
SET @t_pageindex = 1;
SET @t_pagesize = @_cpageCount;
SELECT @t_count = COUNT(*)
FROM @tmible_df1
SET @t_pagecount = CEILING(@t_count / @t_pagesize);
WHILE @t_pageindex <= @t_pagecount
BEGIN
DECLARE @t_mobiles NVARCHAR(MAX)
SET @t_mobiles = NULL;
IF @t_pageindex = 1
SELECT @t_mobiles = ISNULL(( @t_mobiles + ',' ), '')
+ mobile
FROM ( SELECT TOP ( CAST(@t_pagesize AS INT) )
mobile
FROM @tmible_df1
) AS tpagedata
ELSE
BEGIN
SELECT @t_mobiles = ISNULL(( @t_mobiles + ',' ), '')
+ mobile
FROM ( SELECT TOP ( CAST(@t_pagesize AS INT) )
mobile
FROM @tmible_df1
WHERE id > ( SELECT MAX(Id)
FROM ( SELECT TOP ( ( @t_pageindex
- 1 )
* CAST(@t_pagesize AS INT) )
id
FROM
@tmible_df1
ORDER BY id ASC
) AS tpagedatalins
)
) AS tpagedata
END
INSERT INTO @sendmobiles
( mobiles )
VALUES ( @t_mobiles)
SET @t_pageindex = @t_pageindex + 1;
END
SELECT mobiles FROM @sendmobiles";
#endregion
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@_cpageCount", SqlDbType.Int, 4)
{
Value = Config.MsgSendMaxCount
},
new SqlParameter("@_cGoldBookRemindDayNum", SqlDbType.Int, 4)
{
Value = Config.GoldBookRemindDayNum
}
};
DataSet ds = SqlDBProvider.ExecuteSelect(sqltext, para);
return(ds);
}
