SELECT [o].[OrderNum] AS [MainOrderNum], [x].[OrderNum] AS [OrderNum], (select COUNT(Id) from Package p1 where p1.OrderId = x.Id ) AS GoodsNum, [x].[AdminRemarks] AS [AdminRemarks], [x].[Remarks] AS [Remarks], [x].[ASurchargeName] AS [ASurc
SELECT
[o].[OrderNum] AS [MainOrderNum], [x].[OrderNum] AS [OrderNum],
(select COUNT(Id) from Package p1 where p1.OrderId = x.Id ) AS GoodsNum,
[x].[AdminRemarks] AS [AdminRemarks],
[x].[Remarks] AS [Remarks],
[x].[ASurchargeName] AS [ASurchargeName],
d2.Name as DictionariesName,
(
select
top 1 [Content]
from
Track t
where
t.OrderId = x.Id
order by
TrackTime desc
) Zxgj,
[x].UserGroupId,
[x].AuditInformationState,
[x].[FJCountryId] AS [FJCountryId],
[cfj].[Name] AS [FJCountry],
[x].[FJProvinceId] AS [FJProvinceId],
[pfj].[Name] AS [FJProvince],
[x].[FJCityId] AS [FJCityId],
[cLfj].[Name] AS [FJCity],
[x].VillawoodChu,
[x].VillawoodChuTime,
[x].FenCangScan,
[x].FenCangScanTime,
[x].VillawoodScan,
[x].VillawoodScanTime,
[x].[FJCountryStateId] AS [FJCountryStateId],
[x].[FJSuburbId] AS [FJSuburbId],
css.Name as FJCountryStateIdName,
suu.Name as FJSuburbIdName,
x.FJAddress,
x.FJContacts,
x.FJPhone,
x.FJPostcode,
[x].[CountryStateId] AS [CountryStateId],
[x].[SuburbId] AS [SuburbId],
cs.Name as CountryStateIdName,
su.Name as SuburbIdName,
x.NuclearWidth,
x.NuclearHeight,
x.NuclearLength,
x.NuclearVolumes,
x.ImgUrl ImgUrl,
IIF(
x.[ProblemState] = 1,
'无问题',
IIF(
x.[ProblemState] = 2, '有问题',
'已解决'
)
) AS ProblemStateName,
[x].[ProblemRemarks] AS [ProblemRemarks],
[x].[ProblemState] AS [ProblemState],
[x].[Id] AS [Id],
[x].[Address] AS [Address],
[x].[AdminId] AS [AdminId],
[a].[Name] AS [AdminIdName],
[x].[WareHouseId] AS [WareHouseId],
[w].[Name] AS [WareHouseIdName],
[w].[EnName] AS [EnWareHouseIdName],
[c].[Name] AS [Country],
[c].[EnName] AS [EnCountry],
[x].[CountryId] AS [CountryId],
[p].[Name] AS [Province],
[p].[EnName] AS [EnProvince],
[x].[ProvinceId] AS [ProvinceId],
[cl].[Name] AS [City],
[cl].[EnName] AS [EnCity],
[x].[CityId] AS [CityId],
[x].[AreaId] AS [AreaId],
[a1].[Name] AS [AreaIdName],
[a1].[EnName] AS [EnAreaIdName],
[x].[ChannelId] AS [ChannelId],
[c2].[Name] AS [ChannelIdName],
[c2].[EnName] AS [EnChannelIdName],
[x].[CustomsId] AS [CustomsId],
[c3].[Name] AS [CustomsIdName],
[x].[ASurcharge] AS [ASurcharge],
[x].[BaggingNum] AS [BaggingNum],
[x].[BaggingState] AS [BaggingState],
(
CASE WHEN ([x].[BaggingState] = 1) THEN '未装袋' ELSE '已装袋' END
) AS [BaggingStateName],
[x].[BaggingTime] AS [BaggingTime],
[x].[CollectionMoney] AS [CollectionMoney],
[x].[Contacts] AS [Contacts],
[x].[DeclarePrice] AS [DeclarePrice],
[x].[DeclareProductName] AS [DeclareProductName],
[x].[DeliveryType] AS [DeliveryType],
(
CASE WHEN ([x].[DeliveryType] = 1) THEN '自提' ELSE '派送' END
) AS [DeliveryTypeName],
(
CASE WHEN ([x].[DeliveryType] = 1) THEN 'Self Extraction' ELSE 'Delivery' END
) AS [EnDeliveryTypeName],
[x].[Freight] AS [Freight],
[x].[FreightCost] AS [FreightCost],
[x].[Height] AS [Height],
[x].[IDNumber] AS [IDNumber],
[x].[NuclearImg] AS [NuclearImg],
[x].[IsNuclear] AS [IsNuclear],
(
CASE WHEN ([x].[IsNuclear] = 1) THEN '未复磅' ELSE '已复磅' END
) AS [IsNuclearName],
[x].[NuclearTime] AS [NuclearTime],
[x].[NuclearWeight] AS [NuclearWeight],
[x].[Length] AS [Length],
[x].[Lnsurance] AS [Lnsurance],
[x].[MainOrderId] AS [MainOrderId],
[x].[MeterWeight] AS [MeterWeight],
[x].[NatureId] AS [NatureId],
[n].[Name] AS [NatureIdName],
[n].[EnName] AS [EnNatureIdName],
[x].[PayBean] AS [PayBean],
[x].[PayMoney] AS [PayMoney],
[x].[PayState] AS [PayState],
[d].[Name] AS [PayStateName],
[x].[PayTime] AS [PayTime],
[x].[PayType] AS [PayType],
(
CASE WHEN ([x].[PayType] = 100) THEN '余额付款' ELSE [p1].Name END
) AS [PayTypeName],
[x].[PayVolume] AS [PayVolume],
[x].[PayVolumeId] AS [PayVolumeId],
[x].[Phone] AS [Phone],
[x].[PickingState] AS [PickingState],
IIF(
x.[PickingState] = 1,
'未拣货',
IIF(
x.[PickingState] = 2, '正在拣货',
'已拣货'
)
) AS [PickingStateName],
[x].[PickingTime] AS [PickingTime],
[x].[Postcode] AS [Postcode],
[x].[SelfRaisingId] AS [SelfRaisingId],
s.Name AS [SelfRaisingIdName],
[x].[ShipmentCom] AS [ShipmentCom],
c4.Name AS [ShipmentComName],
c4.EnName AS [EnShipmentComName],
[x].[ShipmentNum] AS [ShipmentNum],
[x].[ShipmentState] AS [ShipmentState],
(
CASE WHEN ([x].[ShipmentState] = 1) THEN '未出货' ELSE '已出货' END
) AS [ShipmentStateName],
[x].[ShipmentTime] AS [ShipmentTime],
[x].[PaymentType] AS [PaymentType],
(
CASE WHEN ([x].[PaymentType] = 2) THEN '寄付' WHEN ([x].[PaymentType] = 1) THEN '到付' ELSE '未选择' END
) AS [PaymentTypeName],
[x].[State] AS [State],
[d1].Name AS [StateName],
[d1].EnName AS [EnStateName],
[x].[SuperLong] AS [SuperLong],
[x].[ToExamineState] AS [ToExamineState],
(
CASE WHEN ([x].ToExamineState = 1) THEN '未审核' ELSE '已审核' END
) AS [ToExamineStateName],
[x].[ToExamineTime] AS [ToExamineTime],
[x].[TotalCost] AS [TotalCost],
[x].[UserAddressId] AS [UserAddressId],
[x].[UserId] AS [UserId],
u.UserName AS [UserName],
[x].[Volume] AS [Volume],
[x].[Weight] AS [Weight],
[x].[Width] AS [Width],
[x].[WoodenFrame] AS [WoodenFrame],
[x].[Del] AS [Del],
x.CreationTime as CreationTime,
dWeb.Name AS WebIdName,
u.WebId AS WebId,
x.Container,
x.DeliveryTypeTime as DeliveryTypeTime,
x.IsDeliveryTypeTime,
x.OverWeight,
x.RentFreeMoney,
x.FumigationFee,
x.WoodenCaseFee,
x.InspectionFee,
x.ChinaAustraliaFee,
x.DocumentOperationFee,
x.SpecialOperationFee,
x.PackingFee,
x.CityAsurcharge,
x.DeliveryOrderNo,
x.NuclearTiZhi,
x.NuclearMerter
FROM
[Order] x
Left JOIN [Admin] a ON ([x].[AdminId] = [a].[Id])
Left JOIN [Warehouse] w ON ([x].[WareHouseId] = [w].[Id])
Left JOIN [CountryList] c ON ([x].[CountryId] = [c].[Id])
Left JOIN [ProvinceList] p ON ([x].[ProvinceId] = [p].[Id])
Left JOIN [CityList] cl ON ([x].[CityId] = [cl].[Id])
Left JOIN [AreaList] a1 ON ([x].[AreaId] = [a1].[Id])
Left JOIN [Channel] c2 ON ([x].[ChannelId] = [c2].[Id])
Left JOIN [Customs] c3 ON ([x].[CustomsId] = [c3].[Id])
Left JOIN [Order] o ON ([x].[MainOrderId] = [o].[Id])
Left JOIN [Nature] n ON ([x].[NatureId] = [n].[Id])
Left JOIN [Dictionaries] d ON (([x].[PayState] = [d].[Value]) AND ([d].[TypeId] = 12) )
Left JOIN PayApi p1 on x.PayType = p1.Id
Left JOIN [Dictionaries] d1 ON (([x].[State] = [d1].[Value]) AND ([d1].[TypeId] = 11) )
left join SelfRaising s on s.Id = x.SelfRaisingId
left join Carrier c4 on x.ShipmentCom = c4.Id
left join [User] u on x.UserId = u.Id
left join Dictionaries dWeb on u.WebId = dWeb.Value
and [dWeb].[TypeId] = 1
Left JOIN CountryState cs ON ([x].[CountryStateId] = [cs].[Id])
Left JOIN Suburb su ON ([x].[SuburbId] = [su].[Id])
Left JOIN CountryState css ON ( [x].[FJCountryStateId] = [css].[Id] )
Left JOIN Suburb suu ON ([x].[FJSuburbId] = [suu].[Id])
Left JOIN [CountryList] cfj ON ([x].[FJCountryId] = [cfj].[Id])
Left JOIN [ProvinceList] pfj ON ([x].[FJProvinceId] = [pfj].[Id])
Left JOIN [CityList] cLfj ON ([x].[FJCityId] = [cLfj].[Id])
Left JOIN [Dictionaries] d2 ON (([c2].[ChannelTypeId] = [d2].[Value] ) and ([d2].[TypeId] = 18))
left JOIN [Order] o2 ON ([x].[MainOrderId] = [o].[Id])