1. SQL / Говнокод #22100

    −50

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    64. 64
    65. 65
    66. 66
    67. 67
    68. 68
    69. 69
    select
0 as IS_WRK,
case when DD.DELIVERY_DET_ID is null then 1 else 0 end NW,
0 as NH,
round(WO.AMOUNT * AD.PRICE_BASE*100.0/(100.0+AD.NDS), 2)*AMM.CURS*CDS.CURRENCY_KOEF/CDV.CURRENCY_KOEF as SUMM_NO_NDS,
round(WO.AMOUNT * AD.PRICE_BASE, 2)*AMM.CURS*CDS.CURRENCY_KOEF/CDV.CURRENCY_KOEF as SUMM_VV,
case 
  when DD.DELIVERY_DET_ID is null
    then 
     ( isnull(round(WO.AMOUNT * A.PRICE_IN *
           (select CURRENCY_KOEF from CURRENCY_DAILY (nolock) where CURRENCY_GROUP_ID =
              (select CURRENCY_GROUP_ID from CURRENCY_GROUP (nolock) where CURRENCY_DATE =
                 (select max(CURRENCY_DATE) from CURRENCY_GROUP (nolock) where CURRENCY_DATE <= AD.DATE_ACTION)) and CURRENCY_ID = @Currency)
          /
           (select CURRENCY_KOEF from CURRENCY_DAILY (nolock) where CURRENCY_GROUP_ID =
              (select CURRENCY_GROUP_ID from CURRENCY_GROUP (nolock) where CURRENCY_DATE =
                 (select max(CURRENCY_DATE) from CURRENCY_GROUP (nolock) where CURRENCY_DATE <= AD.DATE_ACTION)) and CURRENCY_ID = A.CURRENCY_ID), 2),0)
      )
   
   else 
     (     /* 08.04.2015 - 4700 BEGIN */
         ISNULL (  round(WO.AMOUNT *case @Currency
           when DM.CURRENCY_ID then  DD.PRICE_NO_NDS
           when @CurrencyBase then DD.PRICE_BASE*(100.0/(100.0+DD.NDS))
           else DD.PRICE_BASE*(100.0/(100.0+DD.NDS))*CDS.CURRENCY_KOEF
        end, 2),  0)  
         /* 08.04.2015 - 4700 END */
      )
   end
  as IN_NO_NDS,
case 
  when DD.DELIVERY_DET_ID is null
    then 
     ( isnull(round(WO.AMOUNT * A.PRICE_IN *
           (select CURRENCY_KOEF from CURRENCY_DAILY (nolock) where CURRENCY_GROUP_ID =
              (select CURRENCY_GROUP_ID from CURRENCY_GROUP (nolock) where CURRENCY_DATE =
                 (select max(CURRENCY_DATE) from CURRENCY_GROUP (nolock) where CURRENCY_DATE <= AD.DATE_ACTION)) and CURRENCY_ID = @Currency)
          /
           (select CURRENCY_KOEF from CURRENCY_DAILY (nolock) where CURRENCY_GROUP_ID =
              (select CURRENCY_GROUP_ID from CURRENCY_GROUP (nolock) where CURRENCY_DATE =
                 (select max(CURRENCY_DATE) from CURRENCY_GROUP (nolock) where CURRENCY_DATE <= AD.DATE_ACTION)) and CURRENCY_ID = A.CURRENCY_ID), 2),0)
      )
   
   else 
     (     /* 08.04.2015 - 4700 BEGIN */
         ISNULL(round(WO.AMOUNT *case @Currency
           when DM.CURRENCY_ID then  DD.PRICE
           when @CurrencyBase then DD.PRICE_BASE
           else DD.PRICE_BASE*(100.0/(100.0+DD.NDS))*CDS.CURRENCY_KOEF
        end, 2), 0) 
          /* 08.04.2015 - 4700 END */
      )
   end
   as IN_VV,
AD.USER_WRITER as WRITER,
AD.ACCOUNT_ID
from ACCOUNT_DETAIL as AD (nolock)
inner join @AM as AM on AM.ACCOUNT_ID = AD.ACCOUNT_ID
inner join ARTICLE as A (nolock) on A.ARTICLE_ID = AD.ARTICLE_ID
left join #_WRITEOUT_TEMP_REST_ as WO on WO.ACCOUNT_DET_ID = AD.ACCOUNT_DET_ID
left join DELIVERY_DETAIL as DD (nolock) on WO.ROOT_ID = DD.DELIVERY_DET_ID
left join DELIVERY_MASTER as DM (nolock) on DM.DELIVERY_MST_ID = DD.DELIVERY_MST_ID
inner join ACCOUNT_MASTER AMM (nolock) on AMM.ACCOUNT_ID=AM.ACCOUNT_ID
inner join CURRENCY_DAILY CDV (nolock) on ((CDV.CURRENCY_GROUP_ID = AMM.CURRENCY_GROUP_ID)and(CDV.CURRENCY_ID = AMM.CURRENCY_ID))
inner join CURRENCY_DAILY CDS (nolock) on ((CDS.CURRENCY_GROUP_ID = AMM.CURRENCY_GROUP_ID)and(CDS.CURRENCY_ID = @Currency)) 
where
AD.IS_WRITEOUT = 0 and AD.OUT = 1
and (@Articles = '' and @ArticleGroups = '' or charindex(',' + convert(varchar, A.ARTICLE_ID) + ',', @Articles) > 0
or charindex(',' + convert(varchar, A.GROUP_ID) + ',', @ArticleGroups) > 0)

    Xzibit.jpg

    Запостил: tucvbif, 01 Февраля 2017

    Комментарии (1) RSS

    Добавить комментарий