amnesty/dataquality

Functions to facilitate data quality operations over CRM databases

Data Quality

Data quality functions allows you to perform certain validations in your data, such as verify bank account check digits, correct the capitalization of names and surnames, etc.

What kind of validations can I find?

The functions contained here are intended to be used to check the data of a CRM database. So you'll find functions to validate document numbers (Spanish NIFs, NIEs and CIFs), bank account check digits, etc.

How are these functions organised?

I don't want to obtain a result when I execute a validation and JavaScript and a diffent one if I do the same validation in MySQL. You'll find different folders for each language. Be patient, I'll be adding translations soon. Feel free to commit yours.

The idea is to have the same functions in different languages, creating them in the most similar possible way (using the same names for variables, functions, etc).

License

Amnesty / DataQuality functions. Copyright (C) 2013 Amnesty International.

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program (see LICENSE.txt). If not, see http://www.gnu.org/licenses/.

dataquality's Issues

Translate iban.sql to PHP

The bank account validations have been written in MySQL and Transact-SQL (SQL Server) but a translation to PHP is needed.

Solve charset problem with MySQL Tests

More info at: http://stackoverflow.com/questions/19702439/how-to-set-the-character-set-when-running-a-suite-of-tests-with-the-mysql-test-f

Translate IBAN functions to JavaScript

Two new files must be added:

• src/javascript/iban.js: containing the function translations.
• test/javascript/Iban.js: containing the function tests.

One file must be updated:

• run-tests.html: to add the link to the new tests files.

Corresponding wiki pages must be updated to, in order to add the links to the two new files.

Add an Alternative "Soft" IBAN Validation

The actual IBAN validation is way too strict. We should create a second one (isValidIBANMaybe) that verifies the check digits and makes sure the first two digits are characters, but that doesn't verify the length of the string against the list of known lengths and that doesn't need the country to be recognised as a SEPA country to return true.

Translate Spanish Bank Account (es-ban) Functions to JavaScript

Two new JavaScript files must be added:

• src/javascript/es-ban.js: containing the function translations.
• test/javascript/es-ban.js: containing the function tests.

One file must be updated:

• run-tests.html: to add the link to the new tests files.

Do Not Create a Table for "accountLegthPerCountry"

Creating a table shouldn't be necesary. Instead, would be nice to use a function with it's own temporary table (or array, depending on the language).

Translate nif-nie-cif.sql to Transact-SQL

nif-nie-cif.sql has been translated into MySQL and PHP. A translation into Transact-SQL is needed.

Add tests for Transact-SQL functions

Before writing the tests, we must choose a testing framework. We must consider options like TSQLUnit and tSQLt.

The PHP tests we've already written may serve as inspiration.

Add translations for addEvery

We need tests and translations for the addEvery function.

Do Not Create a Table for "getIdType"

Creating a table shouldn't be necesary. Instead, would be nice to use a function with it's own temporary table (or array, depending on the language).

Problem Validate NIE

We encountered some trouble to validate NIE. The problem come from replace Y, X and Z with 1, 0 and 2. With the current code, when the NIE ends by Z we replace it by 2 to call isValidNIF(). Next, this method calls getNIFCheckDigit() which return Z. Unfortunately, the last char at this moment is 2...

I propose this code for method isValidNIE() :

function isValidNIE($docNumber) {
    $isValid = false;
    $fixedDocNumber = "";
    if (!preg_match("/^[A-Z]+$/i", substr($fixedDocNumber, 1, 1))) {
        $fixedDocNumber = strtoupper(substr("000000000" . $docNumber, - 9));
    } else {
        $fixedDocNumber = strtoupper($docNumber);
    }
    if (isValidNIEFormat($fixedDocNumber)) {
        if (substr($fixedDocNumber, 1, 1) == "T") {
            $isValid = true;
        } else {
            $writtenDigit = substr($fixedDocNumber, - 1, 1);

            /* The algorithm for validating the check digits of a NIE number is
            identical to the altorithm for validating NIF numbers. We only have to
            replace Y, X and Z with 1, 0 and 2 respectively; and then, run
            the NIF altorithm */
            $fixedDocNumber = str_replace('Y', '1', $fixedDocNumber);
            $fixedDocNumber = str_replace('X', '0', $fixedDocNumber);
            $fixedDocNumber = str_replace('Z', '2', $fixedDocNumber);

            $writtenDigit2 = substr($fixedDocNumber, - 1, 1);

            $correctDigit = getNIFCheckDigit($fixedDocNumber);
            if ($writtenDigit == $correctDigit || $writtenDigit2 == $correctDigit) {
                $isValid = true;
            }
        }
    }

    return $isValid;
}

Add Validation for Spanish Bank Accounts Check Digit

And translate it to all the available languages.

Add tests for MySQL functions

First, we should find/create a testing framework. Of course, we'll consider using the MySQL Test Framework (have to take a look at it, to see if it fits out needs).

The PHP tests we've already written may be an inspiration.

Document Spanish Bank Account Functions

Create wiki pages for Spanish Bank Account functions.