USE [DATA_Volkov]
GO
/****** Object:  StoredProcedure [dbo].[pr_get_order_list_nak_list_ADMIN_02]    Script Date: 02.03.2019 19:55:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pr_get_order_list_nak_list_ADMIN_02]  @start int, @end int, 
															  @ShowHidden int = 1, 
															  @FilterBy as varchar = '0',
															  @idNak as varchar(10) = '0', 
															  @Client nvarchar(100) = '--empty--',
															  @Comment nvarchar(100) = '--empty--',
															  @OrderType nvarchar(10) = '--empty--'
AS
BEGIN 
	SET NOCOUNT ON;
	
	 if @FilterBy = '0' 
	 begin
	   select @idNak = '0', @Client = '--empty--', @Comment = '--empty--', @OrderType = '--empty--'
	 end

	 if @FilterBy = 'ByClient' 
	 begin
	   select @idNak = '0', @Comment = '--empty--'
	 end

	 if @FilterBy = 'ByNak' 
	 begin
	   select @Client = '--empty--', @Comment = '--empty--'
	 end

	 if @FilterBy = 'ByComment' 
	 begin
	   select @Client = '--empty--', @idNak = '0'
	 end

	if @idNak <> '0'
	begin
	 set @idNak = @idNak + '%'
	end

	set @end = @start + @end;

	With NakL as 
   ( 
    --сначала берем порцию из наклист+клиенты, фильтруем и сортируем
	--потом вяжем к этой порции все суммы и проперти  
     select * from 
     (
     SELECT  ROW_NUMBER() OVER (ORDER BY SERV.id DESC, NAKL.id DESC) as Row,
	 NAKL.*,ClientName,ClientTelephon  from [dbo].[ShopSaleNakList] NAKL 
	 left join [dbo].[ShopClients] CL on CL.idClient = NAKL.idClient 
     left join (select id from [dbo].[ShopSaleNakList] where AdmOrd = 1 and Done = 0) SERV on NAKL.id = SERV.id 		
		                   where 
								cast([hidden] as varchar(1)) like
								case   when @ShowHidden = 0 then '%'
									   when  @ShowHidden = 1 then  '0'
								end
							and
								--поиск по номеру накладной - если есть 
								cast(isnull(NAKL.[id],0) as varchar) like
								case   when @idNak = '0' then '%'
									   else @idNak 
								end
						    and

								--поиск по клиенту - имя + телефон - бля нахера я это делаю....
								--конечно через динамику строить нада но если к нему идет обращение
								--каждую минуту то лучше скомпилированный код
						        isnull([ClientName] + [ClientTelephon],'') like
								case   when @Client = '--empty--' then '%'
									   else '%' + @Client + '%'
								end
                            and

                               isnull([Comment],'') like
								case   when @Comment = '--empty--' then '%'
						 		   else '%' + @Comment + '%'
								end

      ) GENERAL
      where Row between @start  and  @end
	  ) --конец with  
	
	
	select

        NAKL.id idNak, NAKL.DateCreated NakDate, isnull(NAKL.Collected,0) Collected , NAKP.NAKSUM, isnull(NAKL.idClient,0) idClient, NAKL.ClientName, NAKL.ClientTelephon,
	    L.id, NAKL.comment as comment, L.comment as ClientComent,
       
      case when isnull(L.id,0) > 0
	    then 
	       case when [Admitted] = 0 
		      then 'Не подтвержден' 
		      else 'Подтвержден' 
		   end 
		else
		    ''
      end 
		as stat, 
		
		cast( L.DateAdmitted as varchar(10)) as DateAdmitted,

		T.S as OrdSum, T.c as OrdCount, cast(L.[DateCreated] as varchar(10)) as DateCreated,
		 
	  case when isnull(total_t,0) = 0  then 'на заказ'
	       when isnull(total_id,0) = 0 then 'из наличия'
	       when isnull(total_t,0) > 0 and isnull(total_id,0) > 0 then 'смешанный'	
		   else 

		    case when isnull(L.id,0) > 0  then
			case isnull([Type],0) when 0 
			   then 'из наличия' else 'на заказ' 
			end
	        else
	         ''		 
	        end

	  end
	  as TypeOfOrd, OP.PropertyValue as OrderColor, 
	  
	  Payment.P as Payment, NAKL.[Hidden],
      

	    'files/' + DOC.[DocName] as P, DOC.[DocName] as F, DOCTransp.[DocName] as DocTransComp,
		 'В:' + ltrim(str([W],10,2)) + ' Г:' + ltrim(str([X])) + 'x' + ltrim(str([Y])) + 'x' + ltrim(str([Z])) as WG, isnull(NAKL.Done,0) Done, total,
		DelSum as serv 
		from  NAKL left join 
	    
		[dbo].[ShopOrdersList] L on NAKL.idOrd = L.id 
		--сумма заказа
		OUTER APPLY (select idOrd, count(id) as c, round(sum(Q*f8b),0) as S from [dbo].[ShopOrdersDetailes] OD where OD.idOrd = L.id group by OD.idOrd) T
		--доки первого типа
		left join (select * from  [dbo].[ShopDocsForClients] where DocType = 0) DOC on DOC.idNak = NAKL.id 
		--доки втрого типа
		left join (select * from  [dbo].[ShopDocsForClients] where DocType = 1) DOCTransp on DOCTransp.idNak = NAKL.id 
		
		--суммы накладных а также тоталы для понимать тип заказа - из наличия или на заказ
		OUTER APPLY (select round(sum(Q*f8bb),0) NAKSUM, sum(case when t = 0 then 1 end) as total_t, sum(case when t > 0 then 1 end) as total_id from [dbo].[ShopSaleNakPos] NAKP where NAKP.idNak = NAKL.id group by NAKP.idNak) NAKP 
		--суммы оплат - на накладную может упасть несколко сумм - поэтому группировка
		OUTER APPLY (select idNak, round(sum(f7b + f7a*curs_f7b),2) as P from [dbo].[ShopSaleNakPayment] Payment where Payment.idNak = NAKL.id group by Payment.idNak) Payment    
        --доставка
		OUTER APPLY (select idNak, sum(case when mode=0 then -f8b when mode = 1 then f8b end) DelSum from [dbo].[ShopOrdersDelivery] DEL where DEL.idNak = NAKL.id group by DEL.idNak) Delivery
 		--вес и габариты накладной если есть
	    
		left join [dbo].[ShopSaleNakList_WXYZ]  WXYZ on WXYZ.idNak = NAKL.id 
		left join (select * from  [dbo].[ShopOrdersProperties] where [PropertyName] = 'OrderNomerColor') OP on OP.[idOrd] =  NAKL.id
		--замена на OUTER APPLY
		--left join (select idNak, round(sum(f7b + f7a*curs_f7b),2) as P from [dbo].[ShopSaleNakPayment] where idNak > 0 group by idNak) Payment on Payment.idNak = NAKL.id 
		-- закрыта или открыта
		
		-- вещь: сколко проведено и не проведено - нужно для смешанных накладных-заказов
		left join (

		select [idNak],'Всего:' +  ltrim(str(max(total))) + ' ' + case when max(total)=sum(Проведено) then 'Проведено' else ' Проблема: ' + ltrim(str(isnull(Sum(Висит),0))) end total 
           from 
					(
					select [idNak], max(total) total,
  
					 case when closed = 1 then count(id) end as Проведено,  
					 case when closed = 0 then count(id) end as Висит

					from (select id, idNak, COUNT(id) OVER(PARTITION BY idNak) AS total, closed  from [dbo].[ShopSaleNakPos] where Q<>0) QQ group by [idNak],closed 
					) Q

					group by [idNak]

		) Проводки on NAKL.id = Проводки.idNak 

		  
		                        where 
								isnull(total_id,0)  > 
								case   when @OrderType = 'на заказ' then 0
									   else   -1
								end
	 
end