USE [LoymarkBaseCart]
GO
/****** Object:  StoredProcedure [Client].[SearchClient]    Script Date: 09/28/2016 15:14:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		GQR
-- Create date: 20160801
-- Description:	Busqueda de clientes.
-- =============================================

ALTER PROCEDURE [Client].[SearchClient] @argument VARCHAR(200),
                                        @status   CHAR(2),
                                        @page     INT          = 1,
                                        @pagesize INT          = 1000000
AS
BEGIN
if (@argument = 'null')
	begin
	   set @argument = null
	end
	if (@status = 'TD')
	begin
	   set @status = null
	end

         SET NOCOUNT ON;


         DECLARE @prowini AS INT;
         DECLARE @prowfin AS INT;
         DECLARE @count AS INT;

         SET @prowini = 0;
         SET @prowfin = 0;
         SET @count = 0;

         SET @prowfin = @page * @pagesize;
         SET @prowini = @prowfin - @pagesize;

         SELECT DISTINCT
                [cli].[IDClient],
                [cli].[Name],
                [cli].[MiddleName],
                [cli].[LastName],
                [cli].[SecondSurname],
                [cli].[Status],
                [cli].[Email],
                [cli].[LoyaltyCard],
                ROW_NUMBER() OVER(ORDER BY [cli].[fullname]) AS [RowID]
         INTO [#TEMPCLIENT]
         FROM Client.Client AS cli WITH (nolock)
              LEFT JOIN client.ClientPhone AS cph ON cli.IDClient = cph.IDClient
         WHERE([cli].[Status] = @status
               OR @status = ''
               OR @status IS NULL)
              AND (([cli].[Email] LIKE '%'+@argument+'%'
                    OR [cli].[Name] LIKE '%'+@argument+'%'
                    OR [cli].[LastName] LIKE '%'+@argument+'%'
                    OR [cli].[LoyaltyCard] LIKE '%'+@argument+'%'
                    OR [cli].[FullName] LIKE '%'+@argument+'%'
                    OR CPH.Phone LIKE '%'+@argument+'%')                    
                   OR (@argument = ''
                       OR @argument IS NULL));
                       
	    Select @count = @@ROWCOUNT

       SELECT [cli].[IDClient],
                [cli].[Name],
                [cli].[MiddleName],
                [cli].[LastName],
                [cli].[SecondSurname],
                [cli].[Status],
                [cli].[Email],
                [cli].[LoyaltyCard],
                @count TotalSize
         FROM #TEMPCLIENT AS CLI
         WHERE [RowID] > @prowini
               AND [RowID] <= @prowfin;

         DROP TABLE #TEMPCLIENT;
         
         
         /*  SELECT  [cli].[IDClient],
                [cli].[Name],
                [cli].[MiddleName],
                [cli].[LastName],
                [cli].[SecondSurname],
                [cli].[Status],
                [cli].[Email],
                [cli].[LoyaltyCard],
                1000 TotalSize
                
                from Client.Client cli*/
     END;