app.red

sql-builder: function[] [
    unset 'sql-request
    sql-request: copy "SELECT * FROM xml_files WHERE 1+1 "
    if only-failed-button/enabled? [
        if not find sql-request "AND status='failed'" [append sql-request "AND status='failed' "  replace/all sql-request " AND status='success'" ""]
        ]
    if only-succesed-button/enabled? [
         if not find sql-request "AND status='success'" [append sql-request "AND status='success' " replace/all sql-request " AND status='failed'" ""]
        ]

    if all [only-succesed-button/enabled? only-failed-button/enabled?] [ replace/all sql-request " AND status='success'" "" replace/all sql-request " AND status='failed'" "" ]


     if all [not none? start-year/text any [none? end-year/text (length? end-year/text) = 0]] [
         append sql-request rejoin ["AND xml_date >='" start-year/text "'"]
     ]

     if all [not none? end-year/text any [none? start-year/text (length? start-year/text) = 0]] [
         append sql-request rejoin ["AND xml_date <='" end-year/text "'"]
     ]

     if all [not none? start-year/text not none? end-year/text ]  [
         replace sql-request "AND xml_date >='' AND xml_date <=''" "" ; this maybe a hack, but at last it work
         if all [(length? start-year/text) > 0  (length? end-year/text) > 0]
         [
             append sql-request rejoin [" AND xml_date>='" start-year/text "' AND xml_date <='" end-year/text "'" ]
         ]
         
     ]

    ; Order By should be alwayes at the end
    if extract-top-added/enabled? [ 
        if any [only-failed-button/enabled? only-succesed-button/enabled?] 
        [
            replace/all sql-request "ORDER by ID DESC"
            if not find sql-request "ORDER by ID ASC" [append sql-request " ORDER by ID ASC" ]
        ]

     ]

    if extract-new-added/enabled? [ 
        if any [only-failed-button/enabled? only-succesed-button/enabled?] 
        [
            replace/all sql-request "ORDER by ID ASC"
            if not find sql-request "ORDER by ID DESC" [append sql-request " ORDER by ID DESC" ]
        ]

     ]

     if extract-last-processed/enabled? [
         replace/all sql-request "ORDER by ID DESC"
         replace/all sql-request "ORDER by ID ASC"
         if not find sql-request "ORDER by processed_date DESC" [append sql-request " ORDER by processed_date DESC" ]
     ]

     if extract-forced-processed/enabled? [
         replace/all sql-request "ORDER by ID DESC"
         replace/all sql-request "ORDER by ID ASC"
         if not find sql-request "AND forced_processing = true" [append sql-request " AND forced_processing = true" ]
     ]


    print sql-request
]

sql-selection-param-control: [
	group-box "Status"
	[
		only-succesed-button: button "Only Succesed" [only-failed-button/enabled?: not only-failed-button/enabled? ]
		only-failed-button: button "Only Failed" [only-succesed-button/enabled?: not only-succesed-button/enabled? ]
	]
    group-box-order-control: group-box "Order"
	[
		extract-top-added: button "Top Added" disabled [
            extract-new-added/enabled?: not extract-new-added/enabled?
            extract-last-processed/enabled?: not extract-last-processed/enabled?
            extract-forced-processed/enabled?: not extract-forced-processed/enabled?
            ]

		extract-new-added: button "New Added" [
            extract-top-added/enabled?: not extract-top-added/enabled?
            extract-last-processed/enabled?: not extract-last-processed/enabled?
            extract-forced-processed/enabled?: not extract-forced-processed/enabled?
            ]

        extract-last-processed: button "Last Processed" disabled [
            extract-top-added/enabled?: not extract-top-added/enabled?
            extract-new-added/enabled?: not extract-new-added/enabled?
            extract-forced-processed/enabled?: not extract-forced-processed/enabled?
            ]
        extract-forced-processed: button "Forced Reprocessing" disabled [
            extract-top-added/enabled?: not extract-top-added/enabled?
            extract-new-added/enabled?: not extract-new-added/enabled?
            extract-last-processed/enabled?: not extract-last-processed/enabled?
            ]
    ]

    group-box "Year"
	[
        start-year: drop-down data [none "2015" "2016" "2017" "2018" "2019" "2020" "2021"]
        end-year: drop-down data [none "2015" "2016" "2017" "2018" "2019" "2020" "2021"]
    ]

    group-box "Region"
	[
        region-name: drop-down data ["Amsterdam"]
    ]


]

view [
	panel sql-selection-param-control
    button "Gen SQL" [
        either all [
            extract-top-added/enabled? 
            extract-new-added/enabled?
            extract-last-processed/enabled?
            extract-forced-processed/enabled?
        ]
        [
            group-box-order-control/color: silver
            print("Please select Order")
        ]
        [
            sql-builder
            group-box-order-control/color: none
        ]
        
        ]
]