SELECT distinct
u.Name as UserName,
a.OrderId,
(
select d.SignCity from T_OperaterInfo o left join T_DepartmentInfo d on o.DepId=d.DepId where o.OptId=a.CreateUserId
) jinjianchengshi,
o.Name as lurukefu,
p.ProductName,
VerifyName = v.VerifyName ,
DepName=
(
select D6.DepName from T_OperaterInfo O5
left join T_DepartmentInfo D5 on D5.DepId = O5.DepId
left join T_DepartmentInfo D6 on D6.DepId = D5.ParentDepId
where a.FirstVerifyOptId = O5.OptId
) ,
a.NetAmount,
a.Amount,
a.Term,
a.RepayPerMonth,
(select count(1) from T_VerifySituation v1 where v1.VerifyStatus=1005 and v1.OrderId=a.OrderId) tuihuicishu,
vst3.RefuseWayName as jujueyuanyin,
vst10.VerifyDate as lixiangshijian,
'jinjianshijian' =
case
when ( CONVERT(varchar(100), vs2.VerifyDate, 20)) is not null then ( CONVERT(varchar(100), vs2.VerifyDate, 20))
else
CONVERT(varchar(100), vst4.VerifyDate , 20)
end,
vst5.VerifyDate as zongpingshijian,
CONVERT(varchar(100), a.ContractStartDate, 20) jihuaqianyueshijian ,
CONVERT(varchar(100), a.ContractEndDate, 20) hetongjieshushijian,
(select O1.Name from T_OperaterInfo O1 where O1.OptId= a.FirstVerifyOptId ) chushen,
(select O2.Name from T_OperaterInfo O2 where O2.OptId= a.LastVerifyOptId ) zhongshen,
(select O3.Name from T_OperaterInfo O3 where O3.OptId= a.TotalReviewOptId ) zongping,
(select O4.Name from T_OperaterInfo O4 where O4.OptId= a.ReconsiderOptId ) fuyi,
''''+( select cast(u.IDCard as varchar(25))) as IDCard,
vst6.AuditOpinion as chushenyijian,
vst7.AuditOpinion as zhongshenyijian,
vst8.AuditOpinion as zongpingyijian,
vst9.AuditOpinion as fuyiyijian,
shifougenggaichanpin = case when a.IsAccordanceProduct=1 then '是' else '否' end ,
'ChangeProductName'=
case
when (a.ChangeProductName is not null and a.ChangeProductName != '') then substring(a.ChangeProductName,0,charindex(',', ChangeProductName))
end,
shifoulianmingjiekuan =case when a.IsCoborrowLoan = 1 then '是' else '否' end,
'CoborrowName'=
case
when (a.CoborrowName is not null and a.CoborrowName != '') then substring(a.CoborrowName,0,charindex(',', CoborrowName))
end
FROM T_LoanProductOrder as a
inner join T_UserInfo as u on a.UserId=u.UserId
inner join T_VerfyStatus as v on a.VerifyStatus=v.VerifyId
inner join T_Product as p on a.ProductTypeId=p.ProductId
left join T_OperaterInfo as o on o.OptId=a.CreateUserId
left join T_DepartmentInfo as d on o.DepId=d.DepId
left join T_UserWorkInfo as w on u.UserId=w.UserId
left join (select OrderId, VerifyDate = max (VerifyDate) ,flag=count(*) from T_VerifySituation where VerifyStatus=3023 group by OrderId ) vs2 on a.OrderId=vs2.OrderId
left join (select OrderId, RefuseWayName ,VerifyDate = max (VerifyDate) ,flag=count(*) from T_VerifySituation where VerifyStatus in (1015,1016) group by OrderId ,RefuseWayName) vst3 on a.OrderId=vst3.OrderId
left join (select OrderId ,VerifyDate = max (VerifyDate) ,flag=count(*) from T_VerifySituation where VerifyStatus in (1002) group by OrderId ) vst4 on a.OrderId=vst4.OrderId
left join (select OrderId ,VerifyDate = max (VerifyDate) ,flag=count(*) from T_VerifySituation where VerifyStatus in (1008 ,1015 ) group by OrderId ) vst5 on a.OrderId=vst5.OrderId
left join (select OrderId ,VerifyDate,AuditOpinion from (
select ROW_NUMBER() over(partition by OrderId order by VerifyDate desc) ROWNO,OrderId ,VerifyDate = max (VerifyDate),AuditOpinion
from T_VerifySituation where VerifyStatus in (1004,1005,3013,3016,1028 ) group by OrderId,VerifyDate,AuditOpinion
) as t where ROWNO=1 ) vst6 on a.OrderId=vst6.OrderId
left join (
select OrderId ,VerifyDate,AuditOpinion from (
select ROW_NUMBER() over(partition by OrderId order by VerifyDate desc) ROWNO,OrderId ,VerifyDate = max (VerifyDate),AuditOpinion
from T_VerifySituation where VerifyStatus in (1006,1007,3018,3025,1029 ) group by OrderId,VerifyDate,AuditOpinion
) as t where ROWNO=1 ) vst7 on a.OrderId=vst7.OrderId
left join (
select OrderId ,VerifyDate,AuditOpinion from (
select ROW_NUMBER() over(partition by OrderId order by VerifyDate desc) ROWNO,OrderId ,VerifyDate = max (VerifyDate),AuditOpinion
from T_VerifySituation where VerifyStatus in (1008, 1009 ,1015) group by OrderId,VerifyDate,AuditOpinion
) as t where ROWNO=1
) vst8 on a.OrderId=vst8.OrderId
left join (
select OrderId ,VerifyDate,AuditOpinion from (
select ROW_NUMBER() over(partition by OrderId order by VerifyDate desc) ROWNO,OrderId ,VerifyDate = max (VerifyDate),AuditOpinion
from T_VerifySituation where VerifyStatus in (1010,1011) group by OrderId,VerifyDate,AuditOpinion
) as t where ROWNO=1 ) vst9 on a.OrderId=vst9.OrderId
left join (select OrderId ,VerifyDate = min (VerifyDate),AuditOpinion ,flag=count(*) from T_VerifySituation where VerifyStatus in (1002) group by OrderId ,AuditOpinion ) vst10 on a.OrderId=vst10.OrderId
where 1=1
and a.ProductTypeId !=30013
and a.OrderId=11376