ÿþ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,--u˜pe @pageSize int,--Ïku˜>f:yLˆpe @pageCount int Output,--“úQ;`u˜pe @recordCount bigint Output,--Ó~œgLˆpe @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*/ /*;Nhˆagö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°ežX 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+') ' /*;NhˆagöNüb¥cend*/ /*ibU\hˆagö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\hˆagö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 --gbLˆRu˜X[¨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