1. Pascal / Говнокод #4644

    +112

    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
    70. 70
    71. 71
    sSQLTitle := 'SELECT  CASE WHEN (VTSS.WRKORDNO IS NOT NULL)'
    +' THEN VTSS.WRKORDNO ELSE VTW.WRKORDNO END AS '''+RSNumDok+''','
    +' CASE WHEN (VTSS.GRECNO IS NOT NULL) THEN VTSS.GRECNO ELSE VTW.GRECNO END AS '''+RSSchet+''','
    +' CASE WHEN (VTSS.STYPE IS NOT NULL) THEN VTSS.STYPE ELSE VTW.STYPE END AS '''+RSDocType+''','
    +' CASE WHEN (VTSS.ServD IS NOT NULL) THEN VTSS.ServD ELSE VTW.ServD END AS '''+RSOpen+''','
    +' CASE WHEN (VTSS.BILLD IS NOT NULL) THEN VTSS.BILLD ELSE VTW.BILLD  END AS '''+RSClose+''','
    +' CASE WHEN (VTSS.FULLNAME IS NOT NULL) THEN VTSS.FULLNAME ELSE VTW.FULLNAME  END AS '''+RSClient+''','
    +' CASE WHEN (VTSS.PAYNAME IS NOT NULL) THEN VTSS.PAYNAME ELSE VTW.PAYNAME  END AS '''+RSPlatelwik+''','
    +' CASE WHEN (VTSS.VIN IS NOT NULL) THEN VTSS.VIN ELSE VTW.VIN  END AS '''+RSVIN+''','
    +' CASE WHEN (VTSS.GNUMER IS NOT NULL) THEN VTSS.GNUMER ELSE VTW.GNUMER  END AS '''+RSGosNomer+''','
    +' CASE WHEN (VTSS.HSMANID IS NOT NULL) THEN VTSS.HSMANID ELSE VTW.HSMANID  END AS '''+RSOtvetstv+''','
    +' CASE WHEN (VTSS.LBACK IS NOT NULL) THEN VTSS.LBACK ELSE VTW.LBACK  END AS '''+RSReturn+''','
    +' VTSS.FULLSUMMA AS '''+RSSumItems+''','
    +' VTW.SUMMA AS '''+RSSumWork+'''';
with DataMdl.adsADODataSet do
  begin
    Active := false;
    if rbAllDate.Checked then
    {$REGION 'Поиск по всем датам'}
      case rgZakazType.ItemIndex of
        0: //поиск по открытым заказ-заявкам
        CommandText:=sSQLTitle
          +' FROM'
          +' (SELECT  VTS.WRKORDNO,'
       (...)
          +' SUM(SUMMA) AS FULLSUMMA'
          +' FROM'
          +' (SELECT GS.WRKORDNO,'
          +' GB.GRECNO,'
          + sSQLCase
          +' CONVERT(varchar(10), GS.ServD, 104) AS ServD,'
          +' CONVERT(varchar(10), GB.BILLD,104) AS BILLD,'
          +' CASE'
          +'	WHEN C.FNAME IS NULL THEN C.LNAME'
          +'	WHEN C.LNAME IS NULL THEN C.FNAME'
          +' ELSE C.FNAME+'' ''+C.LNAME'
          +'	END AS FULLNAME,'
//          +' C.LNAME + '' '' + C.FNAME AS FULLNAME,'
          +' CS.PNAME AS PAYNAME,'
          +' V.SERIALNO AS VIN,'
          +' V.LicNo AS GNUMER,'
          +' GB.HSMANID,'
          +' CASE WHEN (GS.CREDIT IS NULL)OR (GS.CREDIT <> 1) THEN NULL ELSE '''+RSReturn+''' END AS LBACK,'
          +' GW.NAME,'
          +' SUM(GW.ORDNUM) AS '''+RSQuantity+''','
          +' SUM(GW.ORDNUM)*GW.UNITPR AS SUMMA'
          +' FROM GSAL'+sPostfix+' GS LEFT OUTER JOIN'
          +' GROW'+sPostfix+' GW ON (GS.GSALID = GW.GSALID)  LEFT OUTER JOIN'
          +' GBIL'+sPostfix+' GB ON (GS.GSALID = GB.GSALID) LEFT OUTER JOIN'
          +' VEHI V ON (GS.VEHIID = V.VEHIID) LEFT OUTER JOIN'
          +' CUST C ON (GS.CUSTNO = C.CUSTNO) RIGHT OUTER JOIN'
          +' (SELECT     CustNo, Lname + '' '' + FNAME AS PNAME'
          +' FROM CUST WITH (NOLOCK))'
          +' CS ON (GB.CUSTNO = CS.CUSTNO)'
          +' WHERE (GS.STATUS = ''A'') AND (GW.RTYPE IN (1,2))'  +sSQLWhere
          +' GROUP BY  GW.ITEM, GW.NAME, GW.UNITPR,'
          +' GS.WRKORDNO, GB.Grecno, GS.ServD, GB.BILLD, C.LNAME, C.FNAME,'
          +' CS.PNAME, V.SERIALNO, V.LicNo, GB.HSMANID, GS.CREDIT, GW.RECMTIME, GS.STYPE)'
          +' VTS'
          +' CASE WHEN (GS.CREDIT IS NULL)OR (GS.CREDIT <> 1) THEN NULL ELSE '''+RSReturn+''' END AS LBACK,'
          +' GW.NAME,'
          +' SUM(GW.ORDNUM) AS '''+RSQuantity+''','
          +' SUM(GW.ORDNUM)*GW.UNITPR AS SUMMA'
          +' FROM GSAL'+sPostfix+' GS LEFT OUTER JOIN'
          +' GROW'+sPostfix+' GW ON (GS.GSALID = GW.GSALID)  LEFT OUTER JOIN'
          +' GBIL'+sPostfix+' GB ON (GS.GSALID = GB.GSALID) LEFT OUTER JOIN'
          +' VEHI V ON (GS.VEHIID = V.VEHIID) LEFT OUTER JOIN'
          +' CUST C ON (GS.CUSTNO = C.CUSTNO) RIGHT OUTER JOIN'
          +' (SELECT     CustNo, Lname + '' '' + FNAME AS PNAME'
       
     ...

    ... и так далее. НАПИСАНО ВРУЧНУЮ! Всего 420 строк, 4 подзапроса, связанных через full outer join. 16 тысяч записей 4-x ядерный mssql server обрабатывал таким образом минуты две. Я уж промолчу, как он по строкам в Delphi разбит...
    Два дня врубался, как оно работает, переписал за два часа. 58 строк, включая каждое поле из select с новой строки :)

    Запостил: artemlight, 16 Ноября 2010

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

    • ava Oleg_quadro 16.11.2010 13:02 # +1

      full outer join да ещё "LEFT OUTER JOIN", "RIGHT OUTER JOIN" в подзапросах.
      Да что это за база?!
      Ответить

      • ava artemlight 16.11.2010 13:05 # 0

        База одного крупного автодилерского центра. Скрипт формирует список заказ-нарядов за определенное время.
        Ответить

    • ava Lure Of Chaos 16.11.2010 17:35 # +1

      китайсы осень трудолюбивы
      Ответить

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