How to validate data before inserting/updating using CSV import feature

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
snawaz
Posts: 18
Joined: 2019-09-14 17:12

How to validate data before inserting/updating using CSV import feature

Post by snawaz » 2022-12-03 00:31

Dear AppGini Family,

I have built an application that works fine for standard processes but fails to fulfill the business rules when it checks for data validation.

We can somehow control the data inserted or updated through user forms by creating supporting lookup fields to apply filters. If the user inserts the data through CSV import, it destroys the business rules/checks for data validation. It accepts everything until it is not of the same data type and is not already available in the related field.

Based on the situation I have explained, I have the questions below. I need your kind support to get the related answer and a practical guide/tutorial so I can deploy the solution.

Q#1. How to filter the data inside the select2 dropdown in a cascaded dropdown style without adding the extra fields in the same table and dependent tables.

Q#2. The select2 dropdown should only populate the data if the user first selects the parent select2 dropdown. (cascaded style).

Q#3. The most important question is, how do we validate (perform the checks as per the business rules) the data before inserting or updating it using CSV import?

Q#4. Can we filter the data based on our custom SQL query from the advanced button for inserting custom queries?

I am looking forward to getting your kind support situation I have explained above. Thanks


Regards,

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How to validate data before inserting/updating using CSV import feature

Post by jsetzer » 2022-12-03 09:50

  1. Cascading dropdowns in AppGini are depending on Parent-Child-relations, modeled as lookups from child to parent. Use [Advanced...] button in lookup field in AppGini UI and change the SQL query according to your needs. If you need a different behaviour, you should consider implementing SELECT or SELECT2 dropdowns by yourself, for example by just adding a text-input field and converting it to a SELECT2-control in Javascript at runtime. This is very specific, not out of the box, but as soon as you have implemented such once, you will be in control of everything.
  2. Idea: on load, hide 2nd dropdown, if there is no selection in 1st dropdown. Then init an onChange-event-handler for 1st dropdown. On change of 1st dropdown, get selected value and, dependingly, show/hide 2nd dropdown.
  3. This is just my personal opinion:
    It if was my project, and I did many in the past, I would not wait for a new AppGini version nor expect data validation on that given standard CSV-import routine, but I would create my own CSV import with my project-/table-specific validation rules and data-resolving.
  4. I think I did not understand the question. Do you mean you need specific SQL queries for the 2nd dropdown, depending on the selected value of the 1st dropdown, automatically updating onChange of 1st dropdown? I think this is very specific and not possible in standard AG.
    I don't know your use case. For me it sounds unfamiliar that you need different SQL queries in the same field and it may lead to invalid foreign keys.
@Ahmed
It would be helpful if we could customize the data source of a dropdown and options.
  1. In a first step it could be helpful to somehow overwrite $pcConfig-array in parent-children.php
  2. In a future step there could be a PHP-hook, for example a hook function like this, which could get the user's searchTerm from the Select2-Control in the client:

    Code: Select all

    /**
     * TABLENAME_COLUMNNAME_onRequest
     *
     * @param  mixed $selectedID
     * @param  string $searchTerm
     * @param  array $memberInfo
     * @param  array $args
     * @return array|bool
     */
    function TABLENAME_COLUMNNAME_onRequest($selectedID, string $searchTerm, array $memberInfo, array &$args) {
    	return [
    	  	[0, empty_lookup_value],
    		[1, 'My Choice A'],
    		[26, 'My Choice Z']
    	];
    }
    
    • If the function exists and if it returns something different than FALSE, AG (server) could return the array of PK and Text back to the client.
    • We could return static lists or database queries.
    • We coud use $searchTerm for additional filtering.
    • Using getRecord('TABLENAME', $selectedID) we'd be able to fetch last stored (database-) values.
    • If, additionaly, we need current UI values, for example currently selected id of a different lookup, we could read them from $args-array.
    • We could also consider custom permissions or any other setting.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How to validate data before inserting/updating using CSV import feature

Post by jsetzer » 2022-12-03 09:55

@Ahmed (2)
Maybe it would be easier to overwrite the datasource-URL of lookups, which is ajax_combo.php by default, by a custom datasource-URL. So we could implement our own data-fetching algorithm in PHP and return back JSON the way select2 expects.

I think this could be done much faster than implementing the hooks-solution (see my idea above).
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

snawaz
Posts: 18
Joined: 2019-09-14 17:12

Re: How to validate data before inserting/updating using CSV import feature

Post by snawaz » 2022-12-03 11:57

Hi jsetzer,

First, thank you for your prompt support. You have explained each of my questions very beautifully. I will implement your idea.

Regarding Q#3. It is the most critical part of the application. As you have done many projects, I would appreciate it if you could share any working code as an example so that I can implement the same in my project. Moreover, What will be the best and easy option, create our own "CSV import" feature or use the built-in "CSV import" feature with some modification? In both options, please share any guide/tutorial or sample code.

Regarding Q#4. AG provides the "filter by:" option for the lookup field if a supporting field is available in the child table. In most cases, we have lookup fields, but there is no "filter by:" option available, and we don't want to add extra fields to get that "filter by:" option. In that case, Is it possible to get the same "filter by:" result by just inserting the custom query in the "Advanced lookup option." where we can perform the check on the first dropdown field manually?

Regards,

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How to validate data before inserting/updating using CSV import feature

Post by jsetzer » 2022-12-03 16:39

Q3

As a starting point:
There is a table "imports" having id, file, status_id and some more fields. I'm using the built in file upload functionality for letting the users upload csv files. As soon as I have the files on serverside I can use standard php csv import functions for importing row by row into the target table including validation. I'm storing a status per imported row + status per imported file. This allows me to re-validate re-import or even completely remove single lines or whole uploads.

I cannot send complete code because it is owned by customers. But I will see if I can get relevant fragments next week.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How to validate data before inserting/updating using CSV import feature

Post by jsetzer » 2022-12-07 07:42

OK, let's start

There are several abstract PHP classes:

AnyDesk_TNxQSMjAfP.png
AnyDesk_TNxQSMjAfP.png (67.71 KiB) Viewed 992 times

The highlighted class inherits methods from the other classes like loading the CSV-file, reading line-by-line, ...

Main method there is importRow($index, $data). This one gets called for every line of the CSV-file, inserts a record into target-table, does some validation and mapping and sets the status per row.

Code: Select all

protected function importRow($index, $data): bool
    {
        // first index is headline (contains fieldnames)
        $lineNo = $index;


        // first, insert the row with master data
        $set_array = [
            \Model\Tables\DepositItems::DepositID => $this->pk,
            \Model\Tables\DepositItems::DepositTypeID => $this->_deposit["DepositTypeID"],
            \Model\Tables\DepositItems::DepositTypeName => $this->_deposit_type["Name"],
            \Model\Tables\DepositItems::ImportRowIndex => $lineNo,
            \Model\Tables\DepositItems::ImportRowData => json_encode($data),
            \Model\Tables\DepositItems::ImportStatusCode => DEPOSITITEMS::IMPORTSTATUSCODE_NEW,
            \Model\Tables\DepositItems::ImportMessages => null
        ];

        $eo = null;
        if (!insert(\Model\Tables::DepositItems, $set_array, $eo) || $eo)
            throw new \Exception("Error importing line {$lineNo}. Insert failed: {$eo}", 1);

        // get id
        $deposititem_id = db_insert_id(db_link());
        set_record_owner(\Model\TABLES::DepositItems, $deposititem_id, $this->getMemberID());

        // then iterate through all mappings

        $mappings = $this->getMappings();
        $import_errors = [];
        $where_array = [\Model\Tables\DepositItems::DepositItemID => $deposititem_id];

        // update status: PENDING
        $set_array = [
            \Model\Tables\DepositItems::ImportStatusCode => DEPOSITITEMS::IMPORTSTATUSCODE_PENDING,
        ];
        update(\Model\Tables::DepositItems, $set_array, $where_array, $eo);

        foreach ($mappings as $mapping) {

            $source_field = $mapping->getSource();
            $target_field = $mapping->getTarget();

            if (is_callable($target_field)) {
                // call mapping function
                $source_value = $target_field($data[$source_field]);
                $set_array = [$target_field => $source_value == '' ? NULL : $source_value];
            } else {
                $source_value = $data[$source_field];
                $set_array = [$target_field => $source_value == '' ? NULL : $source_value];
            }

            // update column, catch exception
            // if any, append to error list
            try {
                $eo = null;
                update(\Model\Tables::DepositItems, $set_array, $where_array, $eo);
            } catch (\Throwable $th) {
                $import_errors[] = "Error importing CSV-field \"{$source_field}\" into database-column \"{$target_field}\": " . $th->getMessage() . " / " . $eo;
            }
        }

        // update import-status
        $with_errors = sizeof($import_errors);

        // final updates:
        // Amount 10mg ==> 10 mg
        $tn_items = \Model\Tables\DepositItems::NAME;
        $pk_items = \Model\Tables\DepositItems::PK;
        $cn_amount = \Model\Tables\DepositItems::Amount;
        $cn_amount_unit = \Model\Tables\DepositItems::AmountUnit;
        $cn_amount_value = \Model\Tables\DepositItems::AmountValue;

        sql("UPDATE {$tn_items}
            SET 
            {$cn_amount_unit} = RIGHT({$cn_amount}, 2)
            , {$cn_amount_value} = LEFT({$cn_amount}, LENGTH({$cn_amount})-2)
            WHERE RIGHT({$cn_amount},2) IN ('ul', 'mg')
            AND {$pk_items}='{$deposititem_id}'", $eo);

        if ($with_errors) {
            // if there are errors ...
            $set_array = [
                \Model\Tables\DepositItems::ImportMessages => implode("\r\n", $import_errors),
                \Model\Tables\DepositItems::ImportStatusCode => DEPOSITITEMS::IMPORTSTATUSCODE_INVALID
            ];
        } else {
            // no errors
            $set_array = [
                \Model\Tables\DepositItems::ImportMessages => null,
                \Model\Tables\DepositItems::ImportStatusCode => DEPOSITITEMS::IMPORTSTATUSCODE_VALID
            ];
        }
        update(\Model\Tables\DepositItems::NAME, $set_array, $where_array, $eo);

        return !$with_errors;
    }
---

Working with CSV files in PHP

The following code shows the most important PHP commands for reading CSV files:

AnyDesk_DSSinApSly.png
AnyDesk_DSSinApSly.png (154.41 KiB) Viewed 992 times

---

Model.php

If you are wondering about all those \Model\Tables\DepositItems::ImportMessages stuff:
Model.php is an auto-generated file holding constants for all tables, columns etc.

Excerpt:

Code: Select all

<?php
// DO NOT CHANGE THIS FILE MANUALLY
// Generated By AppGiniHelperModelBuilder.php - copyright © 2021 bizzworxx.de
namespace Model {
    class Tables
    {
        public const Requests = 'Requests';
        public const RequestResults = 'RequestResults';
        public const Worklist = 'Worklist';
        public const Orders = 'Orders';
        
        // ...
    }
}
AnyDesk_v4mKlWPrQ7.png
AnyDesk_v4mKlWPrQ7.png (111.64 KiB) Viewed 992 times
Auto-Generating all table names, column names per table etc. allows me to work with variables/constants instead of writing strings. In combination with IDE's intellisense (here: VSCODE) I can avoid spelling mistakes. Especially when renaming columns, I don't have to check and replace hundreds of SQL statements but Intellisense automatically tells me about non existing column names, for example.

See here, how I can access table names and column names instead of typing strings:

AnyDesk_l6TDtEBIM8.gif
AnyDesk_l6TDtEBIM8.gif (33.81 KiB) Viewed 992 times

Validation

For validation there is a programmed rules-engine having dozens of rules, each modeled like this (which is only one ruleset of dozens):

AnyDesk_kizgVUjuj4.png
AnyDesk_kizgVUjuj4.png (123.39 KiB) Viewed 992 times

That drag-and-drop rule designer produces valid SQL.

After successful import, we check every newly imported record against all rulesets. Each imported record has a status like new|validating|validated-ok|validation-failed. Only if all checks for all records pass, that whole import-record gets a "success"-status and will be used in production.

At every time we can delete a whole import (with all records) or single records or, if validation failed, we can upload a modified file and re-validate.

You can see, this is a lot of work with thousands of lines of code. I hope for your understanding that I must not (customer owns code) and cannot post everything here. But it should give you an idea and some code fragments as a starting point.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

Post Reply