ÿþUSE [ECTEST]

GO

/****** Object:  StoredProcedure [dbo].[sp_EC_OrderBasic_Search_Seller]    Script Date: 2021-09-15 15:41:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

      

ALTER Procedure [dbo].[sp_EC_OrderBasic_Search_Seller]    

(    

@pageIndex int,--upe    

@pageSize int,--Ïku>f:yLpe    

@pageCount int Output,--úQ;`upe    

@recordCount bigint Output,--Ó~gLpe    

@buyerId uniqueidentifier,--pN¶[÷S  

@buyerId1 uniqueidentifier,--pN¶[÷S  

@sellerId uniqueidentifier,--VS¶[    

@orderId varchar(25),--¢US÷S(VS¶[¢US÷S)    

@buyerName varchar(50),--pN¶[ÓY
T    

@SellerName varchar(50),--VS¶[ÓY
T    

@createTimeBegin date,--NUS_ËYåeg    

@createTimeEnd date,--NUS*bbkåeg    

@printType int,--pS7R{|W    

@orderType int,--¢US{|W    

@orderState int,--¢US¶r`    

@orderPayState int,--¢US/eØN¶r`    

@creater varchar(20),--6RUSºN    

/*¢[7bagöN*/    

@customerLevel int,--¢[7b§~+R    

@customerManager int,--¢[7bÏ~t    

@businessManager INT,--N¡RÏ~t    

@payType VARCHAR(20),--ØN>k¹e_    

@returnvisit VARCHAR(20),--ÞV¿Å`µQ    

@appraise VARCHAR(20), --Ä÷NÅ`µQ    

@IsStaff INT, --/f&TXTå]/1/f,0
N/f    

@StaffMemberId UNIQUEIDENTIFIER, --XTå]÷S    

@StaffId INT, --XTå]÷S    

@StaffName VARCHAR(20), --XTå]ÓY
T 

@KaipiaoshenqingTimeStart date,--_hy3u÷_ËYåeg    

@KaipiaoshenqingTimeEnd date,--_hy3u÷*bbkåeg 

@Kaipiaoshenqing int,--_hy3u÷   

@Kehuqianshou int, --¢[7b~{6e  

@KaipiaoshenqingId uniqueidentifier, -- _hy3u÷id

@DocumentName VARCHAR(50)  ---pSöN
Tðy

)    

As    

Declare @resultSql varchar(max)--;`agöN    

Declare @orderCondition varchar(200)--¢USågâagöN    

Declare @extendCondition varchar(200)--ibU\hågâagöN    

    

SELECT @extendCondition='',@resultSql=' EC_OrderBasic as ob Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId '    

    

/*üb¥cagöN*/    

/*;NhagöNüb¥cbegin*/    

Select @resultSql=@resultSql + Case When @buyerId Is Null  Then  ''  Else  ' And '''+CONVERT(varchar(36),@buyerId)+'''=ob.BuyerId '  End;    

Select @resultSql=@resultSql + Case When @sellerId Is Null  Then  ''  Else  ' And '''+CONVERT(varchar(36),@sellerId)+'''=ob.SellerId  '  End;    

Select @resultSql=@resultSql + Case When @orderId Is Null  Then  ''  Else  ' And ob.SellerOrderId Like(''%'+@orderId+'%'') '  End;    

Select @resultSql=@resultSql + Case When @buyerName       Is Null  Then  ''  Else  ' And ob.BuyerName like (''%'+@buyerName+'%'')  '  End;    

Select @resultSql=@resultSql + Case When @SellerName      Is Null  Then  ''  Else  ' And ob.SellerName like (''%'+@SellerName+'%'')  '  End;    

--2021.09.14°eX

Select @resultSql=@resultSql + Case When @DocumentName      Is Null  Then  ''  Else  ' And ob.DocumentName like (''%'+@DocumentName+'%'')  '  End;  

 

Select @resultSql=@resultSql + Case When @createTimeBegin Is Null  Then  ''  Else  ' And CAST(ob.CreateTime AS DATE) >='''+Convert(varchar(10),@createTimeBegin)+''' '   End;    

Select @resultSql=@resultSql + Case When @createTimeEnd   Is Null  Then  ''  Else  ' And CAST(ob.CreateTime AS DATE) <='''+Convert(varchar(10),@createTimeEnd )+''' '  End;   







Select @resultSql=@resultSql + Case When @printType       Is Null  Then  ''  Else  ' And '+CONVERT(varchar(10),@printType)+'=ob.PrintTypeId '  End;    

Select @resultSql=@resultSql + Case When @orderType       Is Null  Then  ''  Else  ' And '+CONVERT(varchar(10),@orderType)+'= ob.OrderTypeId '  End;    

--Select @resultSql=@resultSql + Case When @orderState      Is Null  Then  '' WHEN @orderState=2 THEN ' And '+CONVERT(varchar(10),@orderState)+'<=ob.OrderState '  Else  ' And '+CONVERT(varchar(10),@orderState)+' =ob.OrderState '  End;     

     

Select @resultSql=@resultSql + Case When @orderPayState   Is Null  Then  ''  ELSE CASE WHEN @orderPayState = '0' THEN  ' And ( 1=ob.PayState or 0=ob.PayState) '  ELSE   ' And '+CONVERT(varchar(10),@orderPayState)+'=ob.PayState ' END End;          

      

Select @resultSql=@resultSql + Case When @creater    Is Null  Then  ''  Else  ' And ob.Creater like ('''+@creater+'%'')  '  End;     

SELECT @resultSql=@resultSql + CASE WHEN @payType IS NULL OR @payType='' THEN '' WHEN @payType='0' THEN ' And (ts.UsedCredit is not null and ts.UsedCredit>0) ' WHEN @payType='1' THEN ' and (ts.OnlineMoney is not null and  ts.OnlineMoney>0)' WHEN @payType=

  

    

'2' THEN ' and (ts.LineMoney is not null and  ts.LineMoney>0) ' END;      

IF @IsStaff =1    

BEGIN    

Select @resultSql=@resultSql+ Case When @StaffId IS NOT Null  Then  ' And ( ob.BuyerId in ((select axz.MemberId from  [OA_CorporateClients] axz join [OA_CustomerCommunications] bxz on axz.Keyid=bxz.Keyid LEFT JOIN dbo.OA_Staff AS  d_OA_Staff ON axz.BusinessManagerId=d_OA_Staff.Keyid LEFT JOIN dbo.OA_Staff AS  d_OA_Staff1  ON axz.AccountManagerId=d_OA_Staff1.Keyid where axz.FirmId ='''+CONVERT(varchar(36),@sellerId)+''' and ( bxz.Province IN (SELECT Name FROM dbo.Sys_CitySite WHERE Keyid IN (SELECT value FROM dbo.SplitString(CAST((SELECT ManageProvince FROM dbo.OA_Staff WHERE MemberId='''+CONVERT(varchar(36),@StaffMemberId)+''') AS VARCHAR(MAX)),'','',1))) and bxz.City IN (SELECT Name FROM dbo.Sys_CitySite WHERE Keyid IN (SELECT value FROM dbo.SplitString(

CAST((SELECT ManageCity FROM dbo.OA_Staff WHERE MemberId='''+CONVERT(varchar(36),@StaffMemberId)+''') AS VARCHAR(MAX)),'','',1))) AND ((SELECT TOP(1) Keyid FROM dbo.Sys_CitySite WHERE Name=bxz.County AND CityId = (SELECT TOP(1) Keyid FROM dbo.Sys_CitySite WHERE Name=bxz.City )) IN  (SELECT value FROM dbo.SplitString(CAST((SELECT ISNULL(ManageCountry,'''') FROM dbo.OA_Staff WHERE MemberId='''+CONVERT(varchar(36),@StaffMemberId)+''') AS VARCHAR(MAX)),'','',1))OR(SELECT TOP(1) Keyid FROM dbo.Sys_CitySite WHERE Name=bxz.County AND CityId = (SELECT TOP(1) Keyid FROM dbo.Sys_CitySite WHERE Name=bxz.City ))IN  (SELECT value FROM dbo.SplitString(CAST((SELECT ISNULL(

ManageCountry1,'''') FROM dbo.OA_Staff WHERE MemberId='''+CONVERT(varchar(36),@StaffMemberId)+''') AS VARCHAR(MAX)),'','',1)))) or axz.AccountManagerId='+CONVERT(varchar(10),@StaffId)+' or axz.BusinessManagerId='+CONVERT(varchar(10),@StaffId)+')) or ob.Creater ='''+CONVERT(varchar(10),@StaffName)+''' ) '  Else ''   End;    

  

--PRINT @resultSql;  

END    

Select @resultSql=@resultSql+') '     

/*;NhagöNüb¥cend*/        

    

/*ibU\hagöNüb¥cbegin*/    

Select @extendCondition=@extendCondition+ Case When @customerLevel IS NOT Null  Then  ' And '+CONVERT(varchar(10),@customerLevel) +'=oe.CustomerLevelId '   Else ' '  End;    

Select @extendCondition=@extendCondition+ Case When @businessManager IS NOT Null  Then  ' And '+CONVERT(varchar(10),@businessManager)+'= oe.BusinessManagerId '   Else ''  End;    

Select @extendCondition=@extendCondition+ Case When @customerManager IS NOT Null  Then  ' And '+CONVERT(varchar(10),@customerManager)+'= oe.CustomerManagerId '  Else ''   End;   

Select @extendCondition=@extendCondition + Case When @KaipiaoshenqingTimeStart Is NOT Null  Then  ' And CAST(oe.KaipiaoshenqingTime AS DATE) >='''+Convert(varchar(10),@KaipiaoshenqingTimeStart)+''' '   Else  ''  End;    

Select @extendCondition=@extendCondition + Case When @KaipiaoshenqingTimeEnd Is NOT Null  Then ' And CAST(oe.KaipiaoshenqingTime AS DATE) <='''+Convert(varchar(10),@KaipiaoshenqingTimeEnd)+''' '   Else  ''   End;    

Select @extendCondition=@extendCondition + Case When @Kaipiaoshenqing       Is  NOT Null  Then   ' And '+CONVERT(varchar(10),@Kaipiaoshenqing)+'=oe.Kaipiaoshenqing '  Else ''  End; 

Select @extendCondition=@extendCondition + Case When @Kehuqianshou       Is  NOT Null  Then  ' And '+CONVERT(varchar(10),@Kehuqianshou)+'=oe.Kehuqianshou '   Else ''  End; 

Select @extendCondition=@extendCondition + Case When @KaipiaoshenqingId Is Null  Then  ''  Else  ' And '''+CONVERT(varchar(36),@KaipiaoshenqingId)+'''=oe.KaipiaoshenqingId  '  End;    



IF @IsStaff =1    

BEGIN    

Select @extendCondition=@extendCondition+ Case When @customerManager IS NOT Null  Then  ' And (oe.BusinessManagerId='+CONVERT(varchar(10),@StaffId)+' OR oe.CustomerManagerId='+CONVERT(varchar(10),@StaffId)+') '  Else ''   End;    

END    

/*ibU\hagöNüb¥cend*/       

           

/*teTsql*/    

Select @resultSql=@resultSql +' Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid '+ Case When LEN(@extendCondition)=0 Then '' Else @extendCondition+'' End+' )';    

Select @resultSql=@resultSql +' Left Join SysInquiry_PrintingType as pt On(ob.PrintTypeId=pt.Keyid)'    

Select @resultSql=@resultSql +' Left Join Sys_Dictionary as otp On (otp.DicType=''EC_¢US{|W'' And  otp.MeanValue = ob.OrderTypeId )'    

Select @resultSql=@resultSql +' Left Join Sys_Dictionary as ost On (ost.DicType=''EC_¢US¶r`'' And  ost.MeanValue = ob.OrderState )'    

Select @resultSql=@resultSql +' Left Join Sys_Dictionary as opt On (opt.DicType=''EC_¢US/eØN¶r`'' And  opt.MeanValue = ob.PayState )'  

Select @resultSql=@resultSql +' Left Join OA_Kaipiaoshenqing as ok On (  ok.Keyid = oe.KaipiaoshenqingId )'  

Select @resultSql=@resultSql +' Left Join Sys_Dictionary as opk On (opk.DicType=''EC__hy3u÷'' And  opk.MeanValue = ok.Kaipiaoshenqing )'   

Select @resultSql=@resultSql +' Left Join Sys_Dictionary as opf On (opf.DicType=''EC_ÑShy~{6e'' And  opf.MeanValue = oe.Kehuqianshou )'  

--2021.9.16îO9e

Select @resultSql=@resultSql +' Left Join Sys_Dictionary as opy On (opy.DicType=''pS7RUSMO'' And  opy.MeanValue = oe.printunit )'  



Select @resultSql=@resultSql + ' left join OA_VisitNote as vi on ob.keyid=vi.Keyid '  

Select @resultSql=@resultSql + ' left join OA_Staff as os on oe.BusinessManagerId=os.Keyid '    

Select @resultSql=@resultSql + ' left join Integrity_EvaluationRecords as ev on ob.keyid=ev.OrderId '         

Select @resultSql=@resultSql + ' left join LF_OrderFiles as lf on ob.keyid=lf.OrderId '     

Select @resultSql=@resultSql+ ' where 1=1 '  



 -- PRINT @resultSql;  

IF @returnvisit='ò]ÞV¿'    

BEGIN    

 Select @resultSql=@resultSql + ' and vi.Keyid is not null '    

END    

IF @returnvisit='*gÞV¿'    

BEGIN    

 Select @resultSql=@resultSql + ' and vi.Keyid is null '    

END    

    

IF @appraise='*gÄ÷N'    

BEGIN    

   Select @resultSql=@resultSql + ' and ev.Keyid is null '    

END    

ELSE IF(@appraise IS NOT NULL OR @appraise<>'')    

BEGIN    

 Select @resultSql=@resultSql + ' and ev.BuyerEvaluation='''+@appraise+''''    

END    

      

IF @orderState IS NOT NULL    

BEGIN    

     

 IF @orderState=2    

 SELECT @resultSql=@resultSql+' and EXISTS    

 (    

  SELECT 1    

  FROM EC_OrderOperate WHERE OrderId=ob.KeyId AND OperateType='''+CONVERT(varchar(10),@orderState)+'''    

 )'    

 else IF @orderState=4 OR @orderState=5     

 Select @resultSql=@resultSql+' and (ob.OrderState>='''+CONVERT(varchar(10),@orderState)+''' or ob.OrderState=-1)'    

 ELSE     

 SELECT @resultSql=@resultSql+' and ob.OrderState='''+CONVERT(varchar(10),@orderState)+''''    

END    

    

Declare @returnState int         

      

      

    

--gbLRuX[¨PÇz     

EXECUTE   @returnState=[dbo].[SP_CurrencyPage]     

   ' ob.*,oe.shifouDelivery,oe.DeliveryOrderId,ok.Kaipiaoshenqing,oe.KaipiaoshenqingCreater,oe.KaipiaoshenqingTime,oe.KaipiaoquerenCreater,oe.KaipiaoquerenTime,oe.Kehuqianshou,oe.KehuqianshouCreater,oe.KehuqianshouTime ,opf.Name as KehuqianshouName   ,oe.Quchudingdan,oe.QuchudingdanCreater,oe.QuchudingdanTime,oe.PrintPackDeliveryRequir,oe.PrintNum,oe.ExigencyCaseId,ts.OnlineMoney,ts.LineMoney,ts.UsedCredit,ts.PayedMoney,pt.PrintName as PrintTypeName,otp.Name as  OrderTypeName ,ost.Name as OrderStateName ,opt.Name as PayStateName,opk.Name as KaipiaoshenqingName ,os.Name as BusinessManager,opy.Name as yssldw

 ,case when ts.UsedCredit>0 then ''QÙzÅbÝO'' when ts.OnlineMoney>0 then ''	N¹e/eØN'' when ts.LineMoney>0 then ''¿~N/eØN'' else '''' end as PayType          

    ,case when vi.Keyid is not null then 1 else 0 end as IsVisit          

    ,case when ev.Keyid is not null then 1 else 0 end as IsEvaluation        

    ,case when lf.OrderId is not null then 1 else 0 end as IsUpload        

    ,case when lf.OrderId is null then -1 else lf.FileState end as IsDownload    

    '    

  ,@resultSql    

  ,''    

  ,' ob.SellerOrderId desc '    

  ,' TempT.CreateTime desc,TempT.SellerOrderId desc '    

  ,@pageIndex    

  ,@pageSize    

  ,@pageCount OUTPUT    

  ,@recordCount OUTPUT    

      

return @returnState 





