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

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