SHARE
TWEET

Untitled

bisonn Dec 26th, 2019 218 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. set @localxml=
  2. (SELECT
  3.  OrderItems.OrderNo                             "OrderNo"           --"h - OrderNo"             -- 0
  4. ,OrderItems.[LineNo]                            "LineNo"            --"h - LineNo"              -- 1
  5. ,OrderItems.[ItemId]                            "ItemId"            --"h - ItemId"              -- 2
  6. ,OrderItems.[CscuName]                          "CscuName"          --"h - CskuName"            -- 3
  7. ,OrderItems.[VariantCode]                       "VariantCode"       --"h - VarCode"             -- 4
  8. ,OrderItems.[Quantity]                          "Quantity"          --"int - Количество"      -- 5
  9. ,OrderItems.[ItemName]                          "ItemName"          --"txt - Название"          -- 6
  10. ,case when isnull(OrderItems.[VariantName], '')='' then OrderItems.[VariantCode] else OrderItems.[VariantName] end
  11.                                                 "VariantName"       --"txt - Размер"          -- 7
  12. ,OrderItems.[PriceInclVat]                      "PriceInclVat"      --"money - Цена"            -- 8
  13. --,OrderItems.[CouponInclVat]                     "CouponInclVat"     --"money - Купон"            -- 9
  14. ,q3.AmountCP                                    "CouponInclVat"     --"money - Купон"          -- 9
  15. ,OrderItems.[VatPercent]                        "VatPercent"        --"int - Ставка НДС"       --10
  16. ,OrderItems.[VatAmount]                         "VatAmount"         --"money - Сумма НДС"       --11
  17. ,OrderItems.[PriceExclVat]                      "PriceExclVat"      --"h - Цена без НДС"      --12
  18. ,OrderItems.[CouponExclVat]                     "CouponExclVat"     --"h - Купон без НДС"        --13
  19. ,case when OrderItems.[url]='' then '' else
  20.   case when charindex(',', OrderItems.[url], 1)=0 then OrderItems.[url] else SUBSTRING(OrderItems.[url], 1, charindex(',', OrderItems.[url], 1)-1) end
  21. end                                             "url"               --"h - url"                 --14
  22. ,Orders.CampaignName                            "CampaignName"      --"h - Акция"              --15
  23. ,OrderItems.[Quantity]                          "MaxQuantity"       --"h - MaxQuantity"         --16
  24. ,Orders.CampaignCode                            "CampaignCode"      --"h - CampaignCode"        --17
  25. ,OrderItems.[ItemDescription]                   "ItemDescription"   --"h - ItemDescription"     --18
  26. ,OrderItems.Changed                             "Changed"           --"h - Changed"             --19
  27. --,isnull(ROUND((OrderItems.PriceInclVat+OrderItems.CouponInclVat)* convert(float, (Orders.CashlessDiscount))/100, 0), 0)                                               "DiscountCashLess"  --"h - DiscountCashLess"    --20
  28. ,isnull(q2.AmountBN, 0)                                                 "DiscountCashLess"  --"h - DiscountCashLess"    --20
  29. ,isnull(OrderItems.[LPAmount], 0) "LPAmount"
  30. ,isnull(OrderItems.[LPToAdd], 0) "LPToAdd"
  31. ,isnull(q4.[AvaibleQty],0) "InWare"
  32. ,isnull(q5.Reason,0) PCID
  33. ,isnull(q5.ReasonDescr,'') PCD
  34. , OrderItems.PriceInclVat+OrderItems.CouponInclVat+isnull(OrderItems.LPAmount,0) InSite
  35. from
  36. (
  37. select
  38. OrderNo,
  39. Host,
  40. CashlessDiscount,
  41. PaymentMethod,
  42. CampaignName,
  43. CampaignCode
  44. from @Order
  45. )Orders
  46. left join
  47. (
  48. SELECT [OrderNo]
  49.       ,[Host]
  50.       ,[LineNo]
  51.       ,[ItemId]
  52.       ,[CscuName]
  53.       ,[VariantCode]
  54.       ,[Quantity]
  55.       ,[VatPercent]
  56.       ,[VatAmount]
  57.       ,[PriceExclVat]
  58.       ,[PriceInclVat]
  59.       ,[CouponExclVat]
  60.       ,[CouponInclVat]
  61.       ,[ItemName]
  62.       ,[VariantName]
  63.       ,[url]
  64.       ,[ItemDescription]
  65.       ,[Changed]
  66.       ,[LPAmount]
  67.       ,LPToAdd
  68. FROM [dbo].[cc_OrderItems](nolock)
  69. )OrderItems
  70. on OrderItems.OrderNo=@OrderNo and OrderItems.Host=@Host
  71.  outer apply
  72.   (
  73.     select isnull(sum([Amount]),0) AmountBN
  74.     from
  75.     (
  76.         Select distinct CSCU, Variant, DiscountType, Amount
  77.         from
  78.         (
  79.             select distinct *
  80.             from MO.[dbo].[cc_Order_HeadDiscount](nolock) q
  81.             where [OrderNo]=@OrderNo
  82.             and [OrderType]=(select top 1 [ShortName] from MO.[dbo].[cc_Ref_OrderTypes](nolock) where [id]=@OT)
  83.             and Id=(select max(Id) from MO.[dbo].[cc_Order_HeadDiscount](nolock) where [OrderNo]=@OrderNo and [OrderType]=q.OrderType)
  84.         ) t1
  85.         left join MO.[dbo].[cc_Order_HeadDiscountItems](nolock) t2
  86.         on t1.Id=t2.idHead
  87.     ) z1
  88.     where DiscountType='PAYMENT'
  89.     and CSCU=OrderItems.[CscuName]
  90.     and Variant=OrderItems.[VariantCode]
  91.   ) q2
  92.   outer apply
  93.   (
  94.     select isnull(sum([Amount]),0) AmountCP
  95.     from
  96.     (
  97.         Select distinct CSCU, Variant, DiscountType, Amount
  98.         from
  99.         (
  100.             select distinct *
  101.             from MO.[dbo].[cc_Order_HeadDiscount](nolock) q
  102.             where [OrderNo]=@OrderNo
  103.             and [OrderType]=(select top 1 [ShortName] from MO.[dbo].[cc_Ref_OrderTypes](nolock) where [id]=@OT)
  104.             and Id=(select max(Id) from MO.[dbo].[cc_Order_HeadDiscount](nolock) where [OrderNo]=@OrderNo and [OrderType]=q.OrderType)
  105.         ) t1
  106.         left join MO.[dbo].[cc_Order_HeadDiscountItems](nolock) t2
  107.         on t1.Id=t2.idHead
  108.     ) z2
  109.     where DiscountType not in ('PAYMENT','LOYAL')
  110.     and CSCU=OrderItems.[CscuName]
  111.     and Variant=OrderItems.[VariantCode]
  112.   ) q3
  113.   outer apply
  114.   ( select top 1 *
  115.     from [KupiVIP].[dbo].[v_AvaibleQty]
  116.     where [Item No_]=OrderItems.CscuName and [Variant Code]=OrderItems.VariantCode
  117.   ) q4
  118.   outer apply
  119.   (
  120.      select top 1 [Reason],
  121.      (select top 1 [Description] from [dbo].[cc_Ref_PartialCancel](nolock) where [id]=p.Reason) ReasonDescr
  122.      from [dbo].[cc_ItemsChange_Log](nolock) p
  123.      where [OrderNo]=OrderItems.OrderNo
  124.      and [Host]=OrderItems.Host
  125.      and [Cscu]=OrderItems.CscuName
  126.      and [Varcode]=OrderItems.VariantCode
  127.      order by [Date] desc
  128.   ) q5
  129.   where OrderItems.[OrderNo]=@OrderNo and OrderItems.[Host]=@Host
  130.  
  131.  
  132.  
  133. order by OrderItems.OrderNo, OrderItems.[LineNo]
  134. for xml path('Item'), root('Items'))
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top