62,041
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE [dbo].[Usp_Sign_FS2]
@in_SignId int,
--xiaomintianjia
@in_DepartureTime1 Varchar(30),
@in_DepartureTimeE1 Varchar(30),
@in_DepartureTime Varchar(30),
@in_DepartureTimeE Varchar(30),
@in_ForwardId int,
@ModifierName nvarchar(32),
@RecordTotal int = 0 output,--0,find & findCount 1,find 2,findCount
@Plate varchar(1),
@SignCode Varchar(30),
@Phone Varchar(30)
AS
SET @in_DepartureTimeE1 = DATEADD(dd,1,@in_DepartureTimeE1);
SET @in_DepartureTimeE = DATEADD(dd,1,@in_DepartureTimeE);
select a.SignId,s2.ItemNo,
min(q.SOrderNo) as SOrderNo into #qq from Sign a join SignItem s2 ON s2.SignId = a.SignId
inner join SaOrder q on q.SignId=s2.SignId and q.SignItem=s2.ItemNo
where
a.ForwardId = ISNULL(@in_ForwardId,a.ForwardId)
and(@in_DepartureTime1 is null or s2.RequiredTime>=cast(@in_DepartureTime1 as datetime)) and
(@in_DepartureTimeE1 is null or s2.RequiredTime<cast(@in_DepartureTimeE1 as datetime))
and (@in_DepartureTime is null or a.DepartureTime>=cast(@in_DepartureTime as datetime)) and
(@in_DepartureTimeE is null or a.DepartureTime<cast(@in_DepartureTimeE as datetime))
group by a.SignId,s2.ItemNo
select a.Dischargeid ,min(a.Routesid) as Routesid into #ttt from TDischargeRoutes a join (select Dischargeid ,Routesid from TDischargeRoutes
)b on a.Dischargeid=b.Dischargeid group by a.Dischargeid
SELECT distinct TOP 10000
a.SignCode,a.SignId,
qq.DischargeId
,s2.Remark as Remark1
into #tt from Sign a
JOIN SignItem s2 ON s2.SignId = a.SignId
inner join #qq q on q.SignId=s2.SignId and q.ItemNo=s2.ItemNo
inner join SaOrderDischarge qq on qq.SOrderNo=q.SOrderNo
left join (select [Key],[NickName] from TGroup where groups=1021) b on s2.Unit=b.[Key]
left join (select [Key],[NickName] from TGroup where groups=1005) c on s2.CompanyId=c.[Key]
WHERE
a.ForwardId = ISNULL(@in_ForwardId,a.ForwardId)
and(@in_DepartureTime1 is null or s2.RequiredTime>=cast(@in_DepartureTime1 as datetime)) and
(@in_DepartureTimeE1 is null or s2.RequiredTime<cast(@in_DepartureTimeE1 as datetime))
and (@in_DepartureTime is null or a.DepartureTime>=cast(@in_DepartureTime as datetime)) and
(@in_DepartureTimeE is null or a.DepartureTime<cast(@in_DepartureTimeE as datetime))
and s2.Remark <>'' and s2.Remark is not null
-----创建临时表-----
create table #temp
(rowsnumber int ,SignCode nvarchar(16),SignId int,ForwardId int,LogisticsId int,Canton1 nvarchar(32),
ArrivedDate1 datetime, ArrivedType1 datetime, Departuretime datetime, Deliverytime datetime, DischargeId int, Remark2 nvarchar(64),
Pieces decimal(16,0), Total decimal(16,4),Weight decimal(16,4), DName nvarchar(32) ,DepartmentName nvarchar(16),hourss int,Name nvarchar(100)
,Contact nvarchar(100), Phone nvarchar(100), DepartureTime1 datetime,RequiredTime datetime,CantonID int,Remark nvarchar(32),IsZhiDa bit,
Name1 nvarchar(16),ModiDatecrm datetime,ArrivedDatecrm datetime,CantonName2 nvarchar(32) ,Remark1 nvarchar(100)
)
if(@Plate='0') --全部
begin
insert into #temp
select ROW_NUMBER() over(order by ggg.RequiredTime) as rowsnumber, ggg.*,ccc.Name as CantonName2,cccc.Remark1 as Remark1 from ( SELECT TOP 10000
a.SignCode,a.SignId,a.ForwardId,a.LogisticsId,rr.CantonName1 as Canton1,dd.ArrivedDate1,dd.ArrivedType1,
dd.Departuretime, dd.Deliverytime,qq.DischargeId ,dd.Remark1 as Remark2
,SUM(cast(q.Quantity as decimal(16,0))) as Pieces
,SUM(s2.Total) as Total,SUM(s2.Weight) as Weight,ts2.Name AS DName,ts.NickName as DepartmentName,DATEDIFF(HH,dd.ArrivedDatecrm,s2.RequiredTime) as hourss,
ee.Name ,ee.Contact,ee.Phone ,a.DepartureTime as DepartureTime1,s2.RequiredTime
,case when jj.CantonType=4 then jj.ParentID else jj.CantonID end as CantonID ,dd.Remark ,a.IsZhiDa,vb.NickName as Name1,dd.ModiDatecrm,dd.ArrivedDatecrm
from Sign a
JOIN SignItem s2 ON s2.SignId = a.SignId
LEFT JOIN TForward vb on vb.ForwardId=a.ForwardId
inner join SaOrder q on q.SignId=s2.SignId and q.SignItem=s2.ItemNo
inner join SaOrderDischarge qq on qq.SOrderNo=q.SOrderNo
LEFT JOIN #ttt cc on cc.DischargeId = qq.DischargeId
LEFT JOIN TTransport rr on rr.RoutesId=cc.RoutesId and rr.ForwardId=a.ForwardId
left join TDischarge ee on ee.DischargeId=qq.DischargeID
left join TCanton jj on jj.CantonID=ee.CantonID
LEFT JOIN [Tracking] dd on dd.SignId=a.SignId and dd.DischargeId=qq.DischargeId and dd.[Arrived1]=case when jj.CantonType=4 then (select vv.Name from TCanton vv where jj.ParentID=vv.CantonID) else jj.Name end
left join (select [Key],[NickName] from TGroup where groups=1021) b on s2.Unit=b.[Key]
left join (select [Key],[NickName] from TGroup where groups=1005) c on s2.CompanyId=c.[Key]
left join TDepartment ts on s2.DepartmentId = ts.DepartmentId
left join TDepartment ts2 on ts.ParentID = ts2.DepartmentId
left join TDepartment ts3 on ts2.ParentID = ts3.DepartmentId
where
a.ForwardId = ISNULL(@in_ForwardId,a.ForwardId)
and(@in_DepartureTime1 is null or s2.RequiredTime>=cast(@in_DepartureTime1 as datetime)) and
(@in_DepartureTimeE1 is null or s2.RequiredTime<cast(@in_DepartureTimeE1 as datetime))
and (@in_DepartureTime is null or a.DepartureTime>=cast(@in_DepartureTime as datetime)) and
(@in_DepartureTimeE is null or a.DepartureTime<cast(@in_DepartureTimeE as datetime))
and (@ModifierName is null or ts2.Name like '%'+@ModifierName+'%')
and (@Phone is null or ee.Contact like '%'+@Phone+'%')
and (@SignCode is null or ts.NickName like '%'+@SignCode+'%')
group by a.SignCode,vb.NickName,a.IsZhiDa,dd.ModiDatecrm,ts2.Name ,dd.Remark1, ts.NickName,dd.ArrivedDatecrm,a.SignId ,dd.Remark ,jj.CantonID ,a.DepartureTime,a.ForwardId,a.LogisticsId,rr.CantonName1,dd.ArrivedDate1,dd.ArrivedType1,ee.Name ,ee.Contact,ee.
Phone ,s2.OrderTime,s2.RequiredTime , dd.departuretime, dd.deliverytime,qq.DischargeId,case when jj.CantonType=4 then jj.ParentID else jj.CantonID end
) ggg
left join
TCanton ccc on ccc.CantonID=ggg.CantonID
left join #tt cccc
on cccc.SignId=ggg.SignId and cccc.DischargeID=ggg.DischargeID
end
case SignItem.Sataus
when '0' then '正常'
when '1' then '异常'
wehn '3' then '异常解决'
else if(@Plate='1')
begin
insert into #temp
select ROW_NUMBER() over(order by ggg.RequiredTime) as rowsnumber, ggg.*,ccc.Name as CantonName2,cccc.Remark1 as Remark1 from ( SELECT TOP 10000
a.SignCode,a.SignId,a.ForwardId,a.LogisticsId,rr.CantonName1 as Canton1,dd.ArrivedDate1,dd.ArrivedType1,
dd.Departuretime, dd.Deliverytime,qq.DischargeId ,dd.Remark1 as Remark2
,SUM(cast(q.Quantity as decimal(16,0))) as Pieces
,SUM(s2.Total) as Total,SUM(s2.Weight) as Weight,ts2.Name AS DName,ts.NickName as DepartmentName,DATEDIFF(HH,dd.ArrivedDatecrm,s2.RequiredTime) as hourss,
ee.Name ,ee.Contact,ee.Phone ,a.DepartureTime as DepartureTime1,s2.RequiredTime
,case when jj.CantonType=4 then jj.ParentID else jj.CantonID end as CantonID ,dd.Remark ,a.IsZhiDa,vb.NickName as Name1,dd.ModiDatecrm,dd.ArrivedDatecrm
from Sign a
JOIN SignItem s2 ON s2.SignId = a.SignId
LEFT JOIN TForward vb on vb.ForwardId=a.ForwardId
inner join SaOrder q on q.SignId=s2.SignId and q.SignItem=s2.ItemNo
inner join SaOrderDischarge qq on qq.SOrderNo=q.SOrderNo
LEFT JOIN #ttt cc on cc.DischargeId = qq.DischargeId
LEFT JOIN TTransport rr on rr.RoutesId=cc.RoutesId and rr.ForwardId=a.ForwardId
left join TDischarge ee on ee.DischargeId=qq.DischargeID
left join TCanton jj on jj.CantonID=ee.CantonID
LEFT JOIN [Tracking] dd on dd.SignId=a.SignId and dd.DischargeId=qq.DischargeId and dd.[Arrived1]=case when jj.CantonType=4 then (select vv.Name from TCanton vv where jj.ParentID=vv.CantonID) else jj.Name end
left join (select [Key],[NickName] from TGroup where groups=1021) b on s2.Unit=b.[Key]
left join (select [Key],[NickName] from TGroup where groups=1005) c on s2.CompanyId=c.[Key]
left join TDepartment ts on s2.DepartmentId = ts.DepartmentId
left join TDepartment ts2 on ts.ParentID = ts2.DepartmentId
left join TDepartment ts3 on ts2.ParentID = ts3.DepartmentId
where
a.ForwardId = ISNULL(@in_ForwardId,a.ForwardId)
and(@in_DepartureTime1 is null or s2.RequiredTime>=cast(@in_DepartureTime1 as datetime)) and
(@in_DepartureTimeE1 is null or s2.RequiredTime<cast(@in_DepartureTimeE1 as datetime))
and (@in_DepartureTime is null or a.DepartureTime>=cast(@in_DepartureTime as datetime)) and
(@in_DepartureTimeE is null or a.DepartureTime<cast(@in_DepartureTimeE as datetime))
and (@ModifierName is null or ts2.Name like '%'+@ModifierName+'%')
and (dd.ArrivedDatecrm is null or dd.ArrivedDatecrm='1900-1-1')
and (@Phone is null or ee.Contact like '%'+@Phone+'%')
and (@SignCode is null or ts.NickName like '%'+@SignCode+'%')
group by a.SignCode,vb.NickName,a.IsZhiDa,dd.ModiDatecrm,ts2.Name ,dd.Remark1, ts.NickName,dd.ArrivedDatecrm,a.SignId ,dd.Remark ,jj.CantonID ,a.DepartureTime,a.ForwardId,a.LogisticsId,rr.CantonName1,dd.ArrivedDate1,dd.ArrivedType1,ee.Name ,ee.Contact,ee.
Phone ,s2.OrderTime,s2.RequiredTime , dd.departuretime, dd.deliverytime,qq.DischargeId,case when jj.CantonType=4 then jj.ParentID else jj.CantonID end
) ggg
left join
TCanton ccc on ccc.CantonID=ggg.CantonID
left join #tt cccc
on cccc.SignId=ggg.SignId and cccc.DischargeID=ggg.DischargeID
end
else
begin
insert into #temp
select ROW_NUMBER() over(order by ggg.RequiredTime) as rowsnumber, ggg.*,ccc.Name as CantonName2,cccc.Remark1 as Remark1 from ( SELECT TOP 10000
a.SignCode,a.SignId,a.ForwardId,a.LogisticsId,rr.CantonName1 as Canton1,dd.ArrivedDate1,dd.ArrivedType1,
dd.Departuretime, dd.Deliverytime,qq.DischargeId ,dd.Remark1 as Remark2
,SUM(cast(q.Quantity as decimal(16,0))) as Pieces
,SUM(s2.Total) as Total,SUM(s2.Weight) as Weight,ts2.Name AS DName,ts.NickName as DepartmentName,DATEDIFF(HH,dd.ArrivedDatecrm,s2.RequiredTime) as hourss,
ee.Name ,ee.Contact,ee.Phone ,a.DepartureTime as DepartureTime1,s2.RequiredTime
,case when jj.CantonType=4 then jj.ParentID else jj.CantonID end as CantonID ,dd.Remark ,a.IsZhiDa,vb.NickName as Name1,dd.ModiDatecrm,dd.ArrivedDatecrm
from Sign a
JOIN SignItem s2 ON s2.SignId = a.SignId
LEFT JOIN TForward vb on vb.ForwardId=a.ForwardId
inner join SaOrder q on q.SignId=s2.SignId and q.SignItem=s2.ItemNo
inner join SaOrderDischarge qq on qq.SOrderNo=q.SOrderNo
LEFT JOIN #ttt cc on cc.DischargeId = qq.DischargeId
LEFT JOIN TTransport rr on rr.RoutesId=cc.RoutesId and rr.ForwardId=a.ForwardId
left join TDischarge ee on ee.DischargeId=qq.DischargeID
left join TCanton jj on jj.CantonID=ee.CantonID
LEFT JOIN [Tracking] dd on dd.SignId=a.SignId and dd.DischargeId=qq.DischargeId and dd.[Arrived1]=case when jj.CantonType=4 then (select vv.Name from TCanton vv where jj.ParentID=vv.CantonID) else jj.Name end
left join (select [Key],[NickName] from TGroup where groups=1021) b on s2.Unit=b.[Key]
left join (select [Key],[NickName] from TGroup where groups=1005) c on s2.CompanyId=c.[Key]
left join TDepartment ts on s2.DepartmentId = ts.DepartmentId
left join TDepartment ts2 on ts.ParentID = ts2.DepartmentId
left join TDepartment ts3 on ts2.ParentID = ts3.DepartmentId
where
a.ForwardId = ISNULL(@in_ForwardId,a.ForwardId)
and(@in_DepartureTime1 is null or s2.RequiredTime>=cast(@in_DepartureTime1 as datetime)) and
(@in_DepartureTimeE1 is null or s2.RequiredTime<cast(@in_DepartureTimeE1 as datetime))
and (@in_DepartureTime is null or a.DepartureTime>=cast(@in_DepartureTime as datetime)) and
(@in_DepartureTimeE is null or a.DepartureTime<cast(@in_DepartureTimeE as datetime))
and (@ModifierName is null or ts2.Name like '%'+@ModifierName+'%')
and (@Phone is null or ee.Contact like '%'+@Phone+'%')
and (@SignCode is null or ts.NickName like '%'+@SignCode+'%')
and dd.ArrivedDatecrm>'1901-1-1'
group by a.SignCode,vb.NickName,a.IsZhiDa,dd.ModiDatecrm,ts2.Name ,dd.Remark1, ts.NickName,dd.ArrivedDatecrm,a.SignId ,dd.Remark ,jj.CantonID ,a.DepartureTime,a.ForwardId,a.LogisticsId,rr.CantonName1,dd.ArrivedDate1,dd.ArrivedType1,ee.Name ,ee.Contact,ee.
Phone ,s2.OrderTime,s2.RequiredTime , dd.departuretime, dd.deliverytime,qq.DischargeId,case when jj.CantonType=4 then jj.ParentID else jj.CantonID end
) ggg
left join
TCanton ccc on ccc.CantonID=ggg.CantonID
left join #tt cccc
on cccc.SignId=ggg.SignId and cccc.DischargeID=ggg.DischargeID
end
declare @strsql varchar(7000)
--读算总页数
select @RecordTotal=isnull(count(1),0) from #temp
set @strsql='select * from #temp Order by rowsnumber '
exec (@strsql)
if @@error<>0
begin
raiserror('查询时发生错误!',16,1)
return -1
end
return 0
drop table #tt
drop table #ttt
drop table #qq