components_node/functions-db.js
/**
     * Function do return results from any table.
     * @static
     * @async
     * @param {string} strTable categories
     * @param {array} arrSearchParameters ["fieldNameSearch1;fieldValueSearch1;fieldTypeSearch1", "fieldNameSearch2;fieldValueSearch2;fieldTypeSearch2", "fieldNameSearch3;fieldValueSearch3;fieldTypeSearch3"]
     * @param {string} configSortOrder 
     * @param {string} strNRecords 
     * @param {string} strReturnFields field names, separated by commas. Ex: id, id_parent
     * @param {integer} searchType 1 - all results | 2 - first result | 3 - count records
     * @param {object} objSpecialParameters 
     * @returns {object}
     */
Function do return results from any table.
Function do return results from any table .
static async genericTableGet02(strTable, 
    arrSearchParameters, 
    configSortOrder = "", 
    strNRecords = "", 
    strReturnFields = "", 
    searchType = 1, 
    objSpecialParameters = {returnType: 3})
    {
        let strReturn = "";
        let strSQLGenericTableSelect = "";
        let strSQLGenericTableSelectParams = [];
        let resultsSQLGenericTable = "";
        let pageNumber = (objSpecialParameters.hasOwnProperty("_pageNumber")) ? objSpecialParameters._pageNumber : "";
        let pagingNRecords = (objSpecialParameters.hasOwnProperty("_pagingNRecords")) ? objSpecialParameters._pagingNRecords : "";
        let strOperator = "";
        strSQLGenericTableSelect += "SELECT ";
        strSQLGenericTableSelect += FunctionsGeneric.contentMaskWrite(strReturnFields, "db_sanitize") + " ";
        strSQLGenericTableSelect += "FROM " + process.env.CONFIG_SYSTEM_DB_TABLE_PREFIX + FunctionsGeneric.contentMaskWrite(strTable, "db_sanitize");
        for(let countArray = 0; countArray < arrSearchParameters.length; countArray++)
        {
            let arrSearchParametersInfo = arrSearchParameters[countArray].split(";");
            let searchParametersFieldName = arrSearchParametersInfo[0];
            let searchParametersFieldValue = arrSearchParametersInfo[1];
            let searchParametersFieldType = arrSearchParametersInfo[2];
            if(countArray == 0)
            {
                strOperator = "WHERE"
            }else{
                strOperator = "AND"
            }
            if(searchParametersFieldType == "i")
            {
                strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric.contentMaskWrite(searchParametersFieldName, "db_sanitize") + " = ?";
                strSQLGenericTableSelectParams.push(searchParametersFieldValue);
            }
            if(searchParametersFieldType == "!i")
            {
                strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric.contentMaskWrite(searchParametersFieldName, "db_sanitize") + " <> ?";
                strSQLGenericTableSelectParams.push(searchParametersFieldValue);
            }
            if(searchParametersFieldType == "oi")
            {
                if(strOperator != "WHERE")
                {
                    strOperator = "OR"
                }
                strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric.contentMaskWrite(searchParametersFieldName, "db_sanitize") + " <> ?";
                strSQLGenericTableSelectParams.push(searchParametersFieldValue);
            }
            if(searchParametersFieldType == "s")
            {
                strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric.contentMaskWrite(searchParametersFieldName, "db_sanitize") + " = ?";
                strSQLGenericTableSelectParams.push(searchParametersFieldValue);
            }
            if(searchParametersFieldType == "!s")
            {
                strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric.contentMaskWrite(searchParametersFieldName, "db_sanitize") + " <> ?";
                strSQLGenericTableSelectParams.push(searchParametersFieldValue);
            }
            if(searchParametersFieldType == "os")
            {
                if(strOperator != "WHERE")
                {
                    strOperator = "OR"
                }
                strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric.contentMaskWrite(searchParametersFieldName, "db_sanitize") + " = ?";
                strSQLGenericTableSelectParams.push(searchParametersFieldValue);
            }
            if(searchParametersFieldType == "ids")
            {
                let arrIds = searchParametersFieldValue.split(",");
                strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric.contentMaskWrite(searchParametersFieldName, "db_sanitize") + " IN (" + "?".repeat(arrIds.length).split("").join(",") + ")";
                for(let countArrayParameters = 0; countArrayParameters < arrIds.length; countArrayParameters++)
                {
                    strSQLGenericTableSelectParams.push(arrIds[countArrayParameters]);
                }
            }
        }
        if(configSortOrder)
        {
            strSQLGenericTableSelect += " ORDER BY " + configSortOrder;
        }
        if(strNRecords)
        {
            strSQLGenericTableSelect += " LIMIT " + strNRecords;
        }
        if(pageNumber != "" && pagingNRecords != "")
        {
            let limitStart = (pageNumber * pagingNRecords) - pagingNRecords;
            let limitEnd = pagingNRecords;
            strSQLGenericTableSelect += " LIMIT " + limitStart + ", " + limitEnd;
        }
        if(gSystemConfig.configDebug === true)
        {
            console.log("strSQLGenericTableSelect=", strSQLGenericTableSelect);
        }
        return new Promise((resolve, reject) => {
            dbSystemConPool.getConnection(function(dbSystemPoolError, dbSystemConPoolGetConnection) {
                if(dbSystemPoolError)
                {
                    if(gSystemConfig.configDebug === true)
                    {
                        console.log(FunctionsGeneric.appLabelsGet(gSystemConfig.configLanguageBackend.appLabels, "statusMessageError50"));
                    }
                    throw dbSystemPoolError;
                }else{
                    dbSystemConPoolGetConnection.query(strSQLGenericTableSelect, strSQLGenericTableSelectParams, (dbSystemError, resultsSQLGenericTableRows) => {
                        dbSystemConPoolGetConnection.release();
                        if(dbSystemError)
                        {
                            if(gSystemConfig.configDebug === true)
                            {
                                console.log(FunctionsGeneric.appLabelsGet(gSystemConfig.configLanguageBackend.appLabels, "statusMessageError50"));
                            }
                            throw dbSystemError;
                        }else{
                            if(searchType == 1)
                            {
                                resultsSQLGenericTable = resultsSQLGenericTableRows;
                            }
                            if(searchType == 3)
                            {
                                resultsSQLGenericTable = resultsSQLGenericTableRows;
                            }
                            if(resultsSQLGenericTable === undefined)
                            {
                                if(gSystemConfig.configDebug === true)
                                {
                                    console.log(FunctionsGeneric.appLabelsGet(gSystemConfig.configLanguageBackend.appLabels, "statusMessage9"));
                                }
                                reject(new Error(FunctionsGeneric.appLabelsGet(gSystemConfig.configLanguageBackend.appLabels, "statusMessage9")));
                            }else{
                                if(searchType == 1)
                                {
                                    if(objSpecialParameters.returnType == 3)
                                    {
                                        resolve(resultsSQLGenericTable);
                                    }
                                    if(objSpecialParameters.returnType == 5)
                                    {
                                        resolve(Object.keys(resultsSQLGenericTable).map(key => resultsSQLGenericTable[key][strReturnFields]));
                                    }
                                }
                                if(searchType == 3)
                                {
                                    resolve(resultsSQLGenericTable.length);
                                }
                            }
                        }
                    });
                }
            });  
        });
    }
static async genericTableGet02 ( strTable , arrSearchParameters , configSortOrder = "" , strNRecords = "" , strReturnFields = "" , searchType = 1 , objSpecialParameters = { returnType : 3 } ) { let strReturn = "" ; let strSQLGenericTableSelect = "" ; let strSQLGenericTableSelectParams = [ ] ; let resultsSQLGenericTable = "" ; let pageNumber = ( objSpecialParameters . hasOwnProperty ( "_pageNumber" ) ) ? objSpecialParameters . _pageNumber : "" ; let pagingNRecords = ( objSpecialParameters . hasOwnProperty ( "_pagingNRecords" ) ) ? objSpecialParameters . _pagingNRecords : "" ; let strOperator = "" ; strSQLGenericTableSelect += "SELECT " ; strSQLGenericTableSelect += FunctionsGeneric . contentMaskWrite ( strReturnFields , "db_sanitize" ) + " " ; strSQLGenericTableSelect += "FROM " + process . env . CONFIG_SYSTEM_DB_TABLE_PREFIX + FunctionsGeneric . contentMaskWrite ( strTable , "db_sanitize" ) ; for ( let countArray = 0 ; countArray < arrSearchParameters . length ; countArray ++ ) { let arrSearchParametersInfo = arrSearchParameters [ countArray ] . split ( ";" ) ; let searchParametersFieldName = arrSearchParametersInfo [ 0 ] ; let searchParametersFieldValue = arrSearchParametersInfo [ 1 ] ; let searchParametersFieldType = arrSearchParametersInfo [ 2 ] ; if ( countArray == 0 ) { strOperator = "WHERE" } else { strOperator = "AND" } if ( searchParametersFieldType == "i" ) { strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric . contentMaskWrite ( searchParametersFieldName , "db_sanitize" ) + " = ?" ; strSQLGenericTableSelectParams . push ( searchParametersFieldValue ) ; } if ( searchParametersFieldType == "!i" ) { strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric . contentMaskWrite ( searchParametersFieldName , "db_sanitize" ) + " <> ?" ; strSQLGenericTableSelectParams . push ( searchParametersFieldValue ) ; } if ( searchParametersFieldType == "oi" ) { if ( strOperator != "WHERE" ) { strOperator = "OR" } strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric . contentMaskWrite ( searchParametersFieldName , "db_sanitize" ) + " <> ?" ; strSQLGenericTableSelectParams . push ( searchParametersFieldValue ) ; } if ( searchParametersFieldType == "s" ) { strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric . contentMaskWrite ( searchParametersFieldName , "db_sanitize" ) + " = ?" ; strSQLGenericTableSelectParams . push ( searchParametersFieldValue ) ; } if ( searchParametersFieldType == "!s" ) { strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric . contentMaskWrite ( searchParametersFieldName , "db_sanitize" ) + " <> ?" ; strSQLGenericTableSelectParams . push ( searchParametersFieldValue ) ; } if ( searchParametersFieldType == "os" ) { if ( strOperator != "WHERE" ) { strOperator = "OR" } strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric . contentMaskWrite ( searchParametersFieldName , "db_sanitize" ) + " = ?" ; strSQLGenericTableSelectParams . push ( searchParametersFieldValue ) ; } if ( searchParametersFieldType == "ids" ) { let arrIds = searchParametersFieldValue . split ( "," ) ; strSQLGenericTableSelect += " " + strOperator + " " + FunctionsGeneric . contentMaskWrite ( searchParametersFieldName , "db_sanitize" ) + " IN (" + "?" . repeat ( arrIds . length ) . split ( "" ) . join ( "," ) + ")" ; for ( let countArrayParameters = 0 ; countArrayParameters < arrIds . length ; countArrayParameters ++ ) { strSQLGenericTableSelectParams . push ( arrIds [ countArrayParameters ] ) ; } } } if ( configSortOrder ) { strSQLGenericTableSelect += " ORDER BY " + configSortOrder ; } if ( strNRecords ) { strSQLGenericTableSelect += " LIMIT " + strNRecords ; } if ( pageNumber != "" && pagingNRecords != "" ) { let limitStart = ( pageNumber * pagingNRecords ) - pagingNRecords ; let limitEnd = pagingNRecords ; strSQLGenericTableSelect += " LIMIT " + limitStart + ", " + limitEnd ; } if ( gSystemConfig . configDebug === true ) { console . log ( "strSQLGenericTableSelect=" , strSQLGenericTableSelect ) ; } return new Promise ( ( resolve , reject ) => { dbSystemConPool . getConnection ( function ( dbSystemPoolError , dbSystemConPoolGetConnection ) { if ( dbSystemPoolError ) { if ( gSystemConfig . configDebug === true ) { console . log ( FunctionsGeneric . appLabelsGet ( gSystemConfig . configLanguageBackend . appLabels , "statusMessageError50" ) ) ; } throw dbSystemPoolError ; } else { dbSystemConPoolGetConnection . query ( strSQLGenericTableSelect , strSQLGenericTableSelectParams , ( dbSystemError , resultsSQLGenericTableRows ) => { dbSystemConPoolGetConnection . release ( ) ; if ( dbSystemError ) { if ( gSystemConfig . configDebug === true ) { console . log ( FunctionsGeneric . appLabelsGet ( gSystemConfig . configLanguageBackend . appLabels , "statusMessageError50" ) ) ; } throw dbSystemError ; } else { if ( searchType == 1 ) { resultsSQLGenericTable = resultsSQLGenericTableRows ; } if ( searchType == 3 ) { resultsSQLGenericTable = resultsSQLGenericTableRows ; } if ( resultsSQLGenericTable === undefined ) { if ( gSystemConfig . configDebug === true ) { console . log ( FunctionsGeneric . appLabelsGet ( gSystemConfig . configLanguageBackend . appLabels , "statusMessage9" ) ) ; } reject ( new Error ( FunctionsGeneric . appLabelsGet ( gSystemConfig . configLanguageBackend . appLabels , "statusMessage9" ) ) ) ; } else { if ( searchType == 1 ) { if ( objSpecialParameters . returnType == 3 ) { resolve ( resultsSQLGenericTable ) ; } if ( objSpecialParameters . returnType == 5 ) { resolve ( Object . keys ( resultsSQLGenericTable ) . map ( key => resultsSQLGenericTable [ key ] [ strReturnFields ] ) ) ; } } if ( searchType == 3 ) { resolve ( resultsSQLGenericTable . length ) ; } } } } ) ; } } ) ; } ) ; }
Function do return results from any table.
Function do return results from any table .
//arrSearchParameters: ["fieldNameSearch1;fieldValueSearch1;fieldTypeSearch1", "fieldNameSearch2;fieldValueSearch2;fieldTypeSearch2", "fieldNameSearch3;fieldValueSearch3;fieldTypeSearch3"] //typeFieldSearch1: s (string) | !s (string - not equal) | i (integer) | !i (integer - not equal) | oi (integer - or) | d (date) | dif (initial date and final date) | ids (id IN) //strReturnFields: field names, separated by commas. Ex: id, id_parent //searchType: 1 - all results | 2 - first result | 3 - count records //objSpecialParameters {returnType: 3, pageNumber: 2, pagingNRecords: 20} //returnType: 1 - string | 3 - JSon Object | 5 - Array (single column of data - strReturnFields must contain only one db column) //strSeparator: (any string) - in case of using returnType: 1 (string) //Variables. //---------------------- //---------------------- //Query. //---------------------- //strSQLGenericTableSelect += "* "; //debug. //strSQLGenericTableSelect += "id, "; //strSQLGenericTableSelect += "counter_global, "; //strSQLGenericTableSelect += "description "; //strSQLGenericTableSelect += "FROM " + process.env.CONFIG_SYSTEM_DB_TABLE_PREFIX + FunctionsGeneric.contentMaskWrite(strTable, "db_sanitize") + " "; //strSQLGenericTableSelect += "WHERE id <> 0"; //strSQLGenericTableSelect += "AND id = :id "; //strSQLGenericTableSelect += "AND id = ? "; //---------------------- //Parameters. //---------------------- //if(idTbCounter != null && idTbCounter != "" && (typeof idTbCounter !== 'undefined')) //{ //strSQLCounterSelect += " AND " + mysql.escape("id") + " = ?";//not workig, because it wraps in single quotes. //strSQLGenericTableSelect += " AND id_parent = ?"; //strSQLGenericTableSelectParams.push("0"); //} //Variables. //Operator selection. //Integer. //Integer - not equal. //Integer - or. //String. //String - not equal. //String - or. //ids. //Debug. //print("arrSearchParameters=" + arrSearchParameters[countArray]); //print("searchParametersFieldName=" + searchParametersFieldName); //print("searchParametersFieldValue=" + searchParametersFieldValue); //print("searchParametersFieldType=" + searchParametersFieldType); //Paging. //Logic - calculating limit. //let limitEnd = pageNumber * pagingNRecords; //wrong //Debug. /*
            console.log("genericTableGet02.pageNumber = ", pageNumber);
            console.log("genericTableGet02.pagingNRecords = ", pagingNRecords);
            console.log("genericTableGet02.limitStart = ", limitStart);
            console.log("genericTableGet02.limitEnd = ", limitEnd);
            */ //Debug. //---------------------- //Execute query. //---------------------- //dbSystemCon.query(strSQLCounterSelect, strSQLCounterSelectParams, (dbSystemError, resultsSQLCounterRows) => { //Error. //return reject(dbSystemError); //Select the counter number. //All results. //Count. //Error. //reject(new Error("nCounter is undefined.")); //All results. //JSon Object. //Success. //Array. //Success. //Convert single column of object into array. //Count records. //Success. //resolve(resultsSQLGenericTable); //Debug. //resolve(resultsSQLCounterRows); //resolve(nCounter); //resolve(json(resultsSQLCounterRows));//working: returns [ RowDataPacket { id: 1, counter_global: 123, description: 'Node database test' } ] //---------------------- //return strReturn; //Usage. //---------------------- /*
        let genericTableGet02ResultDebug = await SyncSystemNS.FunctionsDB.genericTableGet02("categories", 
                                                                                            ["id_parent;0;i", "activation;1;i"], 
                                                                                            gSystemConfig.configCategoriesSort, 
                                                                                            "5", 
                                                                                            "id, id_parent, sort_order, category_type, date_creation, title, activation", 
                                                                                            1, 
                                                                                            {returnType: 3});

        //In case of returning one record.
        valueCurrent = genericTableGet02ResultDebug[0]["fieldName"];

        //Loop through records.
        ${oclRecords.genericTableGet02ResultDebug.map((recordsRow)=>{
            return `
                ${ recordsRow.id }
             `;
         }).join("")}
        */ //----------------------