1 CREATE PROCEDURE  [dbo].[GetStepDataListByCollectDate]
  2     @hospId            uniqueidentifier = null,
  3     @userId            uniqueidentifier = null,
  4     @dataId            uniqueidentifier = null,
  5     @loginName        nvarchar(50) = null,
  6     @userName        nvarchar(50) = null,
  7     @phone            varchar(20) = null,
  8     @fromDate        varchar(19) = null,
  9     @toDate            varchar(19) = null,
 10     @pageIndex        int = 1,
 11     @pageSize        bigint = 100
 12 AS
 13 SET NOCOUNT ON;
 14 
 15 IF ISDATE(@fromDate) = 0    SET @fromDate = '1753-01-01 00:00:00';
 16 IF ISDATE(@toDate) = 0        SET @toDate = '9999-12-31 23:59:59';
 17 IF @pageIndex < 1            SET @pageIndex = 1
 18 IF @pageSize < 1            SET @pageSize = 10
 19 
 20 DECLARE @lowerBound    bigint
 21 DECLARE    @upperRound    bigint
 22 DECLARE    @top        bigint
 23 
 24 SET @lowerBound = convert(numeric(20,0),@pageSize) * @pageIndex - convert(numeric(20,0),@pageSize);
 25 SET @upperRound = @lowerBound + convert(numeric(20,0),@pageSize) + 1;
 26 SET @top = @lowerBound +convert(numeric(20,0),@pageSize);
 27 
 28 SET @loginName = ISNULL(@loginName, '');
 29 SET @userName = ISNULL(@userName, '');
 30 SET @phone = ISNULL(@phone, '');
 31 
 32 WITH PageIndex AS
 33 (
 34     SELECT TOP (@top) ROW_NUMBER() OVER (ORDER BY COLLECTDATE DESC) IndexId,
 35           u.HOSPITALID, u.USERID, d.DATAID, d.ADDDATE, d.COLLECTDATE, d.GATEWAYCODE, d.SENSORCODE, 
 36           u.LOGINNAME, u.USERNAME, g.GENDERNAME, ISNULL(u.AGE, 0) AS AGE,
 37           u.ROLEID, u.USERCODE, u.MOBILE, u.PHONE, u.EMAIL, 
 38           ISNULL(d.IMPORTANT, 0) AS IMPORTANT, d.ISREMARK, d.DIAGNOSIS,
 39           ISNULL(SMS.SMSCOUNT, 0) AS SMSCOUNT,
 40           d.DATATYPE, d.STRIDE, d.CALORIE, 
 41           d.STEPS, d.FASTSTEPS, 
 42           d.SLOWSTEPS, d.MINUTES, 
 43           d.EXERCISE, d.BEGINTIME, 
 44           d.ENDTIME, d.FAST00TO01, 
 45           d.SLOW00TO01, d.FAST01TO02, 
 46           d.SLOW01TO02, d.FAST02TO03, 
 47           d.SLOW02TO03, d.FAST03TO04, 
 48           d.SLOW03TO04, d.FAST04TO05, 
 49           d.SLOW23TO00, d.FAST23TO00, 
 50           d.SLOW22TO23, d.FAST22TO23, 
 51           d.SLOW21TO22, d.FAST21TO22, 
 52           d.SLOW20TO21, d.FAST20TO21, 
 53           d.SLOW19TO20, d.FAST19TO20, 
 54           d.SLOW18TO19, d.FAST18TO19, 
 55           d.SLOW17TO18, d.FAST17TO18, 
 56           d.SLOW16TO17, d.FAST16TO17, 
 57           d.SLOW15TO16, d.FAST15TO16, 
 58           d.SLOW14TO15, d.FAST14TO15, 
 59           d.SLOW13TO14, d.FAST13TO14, 
 60           d.SLOW12TO13, d.FAST12TO13, 
 61           d.SLOW11TO12, d.FAST11TO12, 
 62           d.SLOW10TO11, d.FAST10TO11, 
 63           d.SLOW09TO10, d.FAST09TO10, 
 64           d.SLOW08TO09, d.FAST08TO09, 
 65           d.SLOW07TO08, d.FAST07TO08, 
 66           d.SLOW06TO07, d.FAST06TO07, 
 67           d.SLOW05TO06, d.FAST05TO06, 
 68           d.SLOW04TO05, ISNULL(d.DESTSTEPS, 0) 
 69           AS DESTSTEPS, ISNULL(d.DESTDISTANCE, 0) AS DESTDISTANCE, 
 70           ISNULL(d.DESTEXERCISE, 0) AS DESTEXERCISE, 
 71           ISNULL(d.DESTCALORIE, 0) AS DESTCALORIE, 
 72           ISNULL(d.ISSMSNOTIFY, 0) AS ISSMSNOTIFY
 73     FROM dbo.T_STEPDATA d WITH (NOLOCK) INNER JOIN
 74           dbo.T_USER u WITH (NOLOCK) ON d.USERID = u.USERID LEFT OUTER JOIN
 75           dbo.T_GENDER g WITH (NOLOCK) ON 
 76           u.GENDERID = g.GENDERID LEFT OUTER JOIN
 77               (SELECT DATAID, COUNT(*) AS SMSCOUNT
 78              FROM dbo.T_SMS s WITH (NOLOCK)
 79              WHERE ISNULL(ISDELETED, 0) = 0
 80              GROUP BY DATAID) SMS ON SMS.DATAID = d.DATAID
 81     WHERE (ISNULL(d.ISDELETED, 0) = 0) AND 
 82           (ISNULL(u.ISDELETED, 0) = 0) AND
 83           (u.HOSPITALID = @hospId OR @hospId IS NULL) AND
 84           (u.USERID = @userId OR @userId IS NULL) AND
 85           (d.DATAID = @dataId OR @dataId IS NULL) AND
 86           (d.COLLECTDATE BETWEEN @fromDate AND @toDate) AND
 87           (u.LOGINNAME = @loginName OR @loginName = '') AND
 88           (u.USERNAME LIKE '%' + @userName + '%' OR @userName = '') AND
 89           ((u.MOBILE LIKE '%' + @phone + '%' OR @phone = '') OR
 90           (u.PHONE LIKE '%' + @phone + '%' OR @phone = ''))
 91 )
 92 SELECT *
 93 FROM PageIndex
 94 WHERE PageIndex.Indexid > @lowerBound AND PageIndex.IndexId < @upperRound;
 95 
 96 DECLARE    @totalRecords    int
 97 
 98 SELECT @totalRecords = COUNT(*)
 99 FROM dbo.T_STEPDATA d WITH (NOLOCK) INNER JOIN
100       dbo.T_USER u WITH (NOLOCK) ON d.USERID = u.USERID
101 WHERE (ISNULL(d.ISDELETED, 0) = 0) AND 
102       (ISNULL(u.ISDELETED, 0) = 0) AND
103       (u.HOSPITALID = @hospId OR @hospId IS NULL) AND
104       (u.USERID = @userId OR @userId IS NULL) AND
105       (d.DATAID = @dataId OR @dataId IS NULL) AND
106       (d.COLLECTDATE BETWEEN @fromDate AND @toDate) AND    
107       (u.LOGINNAME = @loginName OR @loginName = '') AND
108       (u.USERNAME LIKE '%' + @userName + '%' OR @userName = '') AND
109       ((u.MOBILE LIKE '%' + @phone + '%' OR @phone = '') OR
110       (u.PHONE LIKE '%' + @phone + '%' OR @phone = ''));
111 
112 SET NOCOUNT OFF;
113 
114 RETURN @totalRecords;