USE [DrugSaleSystem]
GO
/****** Object: StoredProcedure [dbo].[ShiftStatistics_Get] Script Date: 11/23/2016 16:38:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------------------
-- Name: ShiftStatistics Store Procedure is to add a operation.
-- Author: 浪剑
-- DateTime: 2016-05-09 14:35:08
-- Description: 分页记录
-----------------------------------------------------------------
/*
@PageIndex 当前页码
@PageSize 每页的记录数
*/
ALTER PROCEDURE [dbo].[ShiftStatistics_Get]
(
@ShiftStatisticsCode varchar(20),
@DepartmentCode int,
@DepartmentName varchar(50)='',
@IsSend int,
@StartShiftDate varchar(20),
@EndShiftDate varchar(20),
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @Page int
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
SET @Page = (@PageIndex - 1)
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@Page + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @Page
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
ShiftStatisticsID int
)
DECLARE @STRSQL varchar(3000)
DECLARE @CONSQL varchar(2000)
SET @STRSQL='
INSERT INTO #PageIndex (ShiftStatisticsID)
SELECT
[ShiftStatisticsID]
FROM
[ShiftStatistics] WHERE 8=8'
SET @CONSQL=''
IF(@ShiftStatisticsCode<>'')
BEGIN
SET @CONSQL=@CONSQL+' AND ShiftStatisticsCode like ''%' + @ShiftStatisticsCode + '%'''
END
IF(@DepartmentCode>0)
BEGIN
SET @CONSQL=@CONSQL+' AND DepartmentCode=' + CAST(@DepartmentCode AS VARCHAR(20))
END
IF(@DepartmentName<>'')
BEGIN
SET @CONSQL=@CONSQL+' AND DepartmentCode in(select DepartmentCode from Department where DepartmentName like ''%' + @DepartmentName + '%'')'
END
IF(@IsSend>-1)
BEGIN
SET @CONSQL=@CONSQL+' AND IsSend=' + CAST(@IsSend AS VARCHAR(20))
END
IF(@StartShiftDate<>'')
BEGIN
SET @CONSQL=@CONSQL+' AND ShiftDate>=''' + @StartShiftDate + ''''
END
IF(@EndShiftDate<>'')
BEGIN
SET @CONSQL=@CONSQL+' AND ShiftDate<=''' + @EndShiftDate + ''''
END
EXEC(@STRSQL+@CONSQL + ' order by ShiftDate desc')
SELECT
c.ShiftStatisticsID,
ShiftStatisticsCode,
DepartmentCode,
POSID,
CashierCode,
ShiftDate,
TotalSalesAmount,
CashAmount,
SocialInsuranceAmount,
UnionpayAmount,
ValueCardAmount,
WeChatAmount,
AlipayAmount,
CouponAmount,
IsSend,
AmountCheckCode,
Operator,
OperateDate,
Remark
FROM [ShiftStatistics] c , #PageIndex PageIndex
WHERE
c.ShiftStatisticsID = PageIndex.ShiftStatisticsID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
order by ShiftDate desc
EXEC('SELECT COUNT(ShiftStatisticsID) AS TotalRecords FROM [ShiftStatistics] where 8=8'+@CONSQL)
END