app.red

sql-builder: function[] [

    sql-request: copy {SELECT 
                            ID,
                            arch_name,
                            file_name,
                            file_type,
                            forced_processing,
                            processed_date,
                            region,
                            section_name,
                            status,
                            xml_date
                        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 not none? region-name/text [
        if (length? region-name/text) > 0
        [
         append sql-request rejoin ["AND region='" region-name/text "'"]   
        ]
    ]


    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" ]
     ]

    append sql-request " LIMIT 15;"
    replace/all sql-request "  " " "
    print sql-request
    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 ["2015" "2016" "2017" "2018" "2019" "2020" "2021"]
        end-year: drop-down data ["2015" "2016" "2017" "2018" "2019" "2020" "2021"]
    ]

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

    button "Gen SQL" 60x75 [
        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")
        ]
        [
            make-db-query sql-builder
            group-box-order-control/color: none
        ]
        
        ]

]

make-db-query: func[sql-query] [ 
    print [" in f" sql-query]
    ; clear db-result
    db-result: copy []
    ; SQLite/do [
    ;  db-result: exec sql-query
    ; ]

    db-result: sqlite/query sql-query
    ; при извлечении данных не забываем что драйвер возвращает еще индексное поле
    ; поэтому первое значение это количество столбцов плюс один, а второе нужный столбец
    ; пример получения ИД из базы (не того что добавляется драйвером) print extract/index db-result 11 2
    ; стркктура полей:
    {ID,
    arch_name,
    file_name,
    file_type,
    forced_processing,
    processed_date,
    region,
    section_name,
    status,
    xml_date}


    probe db-result

    ; forced_processing выставить высокую приоритетность для повторного процессинга
    ; после репроцессинга флаг должен обнуляться
    db-result-id: extract/index db-result 11 2
    db-result-arch_name: extract/index db-result 11 3
    db-result-file_name: extract/index db-result 11 4
    db-result-file_type: extract/index db-result 11 5
    db-result-forced_processing: extract/index db-result 11 6
    db-result-processed_date: extract/index db-result 11 7
    db-result-region: extract/index db-result 11 8
    db-result-section_name: extract/index db-result 11 9
    db-result-status: extract/index db-result 11 10
    db-result-xml_date: extract/index db-result 11 11

    print db-result-id

    foreach cell ui-table-cells
    [
        if set-word? cell [ 
            fld-name: to-string cell
                if find fld-name  "f-id" [ 
                    face: get cell
                    face/text: take db-result-id
                ]
                if find fld-name  "f-file_name" [ 
                    face: get cell
                    face/text: take db-result-file_name
                ]

                if find fld-name  "f-file_type" [ 
                    face: get cell
                    face/text: take db-result-file_type
                ]

                if find fld-name  "f-processed_date" [ 
                    face: get cell
                    face/text: take db-result-processed_date
                ]

                if find fld-name  "f-status" [ 
                    face: get cell
                    face/text: take db-result-status
                ]

                if find fld-name  "f-xml_date" [ 
                    face: get cell
                    face/text: take db-result-xml_date
                ]

                if find fld-name  "f-forced_processing" [ 
                    face: get cell
                    face/text: take db-result-forced_processing
                ]

                if find fld-name  "section_name" [ 
                    face: get cell
                    face/text: take db-result-section_name
                ]                

                if find fld-name  "region" [ 
                    face: get cell
                    face/text: take db-result-region
                ]        
                                                           

        ]
    ]

]


; view [
; 	panel sql-selection-param-control
;  ]