예제 #1
0
void CGantnerTime::reloadAllData()
{
    //Reload all user
    QSqlQuery userQuery("SELECT id, CONCAT(name, ' ', firstname) AS fullname, language FROM hr_user");
    while(userQuery.next())
    {
        QSqlQuery groupAttributionQuery;
        groupAttributionQuery.prepare("SELECT pg.id, pg.name, pg.comment FROM hr_user_group AS pg LEFT JOIN hr_user_group_attribution AS ga ON ga.id_group=pg.id LEFT JOIN hr_user AS pe ON pe.id=ga.id_user WHERE pe.id=:id");
        groupAttributionQuery.bindValue(":id", userQuery.value(0));
        groupAttributionQuery.exec();

        while( groupAttributionQuery.next())
        {
            QString groupId = groupAttributionQuery.value(0).toString();

            QSqlQuery deviceQuery;
            deviceQuery.prepare("SELECT uga.id_device FROM hr_user_group_access AS uga LEFT JOIN hr_device AS d ON d.id = uga.id_device WHERE id_group=:id AND d.type='gantner_TimeTerminal'");
            deviceQuery.bindValue(":id", groupId);
            deviceQuery.exec();

            while( deviceQuery.next())
            {
                QString deviceId = deviceQuery.value(0).toString();
                QSqlQuery insertQuery;
                insertQuery.prepare("INSERT INTO hr_gantner_standalone_action (`type`, `func`, `userId`,`keyId`, `deviceId`, `param`, `param2`, `param3`) VALUES (:type,:func,:userId,:keyId,:deviceId, :param, :param2, :param3)");
                insertQuery.bindValue(":func", "add");
                insertQuery.bindValue(":type", "user");
                insertQuery.bindValue(":userId", userQuery.value(0) );
                insertQuery.bindValue(":keyId", 0 );
                insertQuery.bindValue(":deviceId", deviceId );
                insertQuery.bindValue(":param", userQuery.value(1)  );
                insertQuery.bindValue(":param2",userQuery.value(2) );


                //check the last booking to know if the user is present or absent
                QSqlQuery checkLastBooking;
                checkLastBooking.prepare("SELECT * FROM hr_tracking AS t LEFT JOIN hr_timux_booking AS tb ON t.id=tb.tracking_id WHERE t.id_user=:userId ORDER BY t.date DESC, t.time DESC, tb.action DESC LIMIT 0,1");
                checkLastBooking.bindValue(":userId", userQuery.value(0) );
                checkLastBooking.exec();
                if(checkLastBooking.next()) {
                    if(checkLastBooking.value(11).toInt() == 255 || checkLastBooking.value(11).toInt() == 155 || checkLastBooking.value(12).toString().right(3) == "_IN" ) {
                        insertQuery.bindValue(":param3",1 );
                    } else {
                        insertQuery.bindValue(":param3",2 );
                    }
                } else {
                    //no booking, the user is absent
                    insertQuery.bindValue(":param3",2 );
                }



                insertQuery.exec();

                QSqlQuery keyQuery;
                keyQuery.prepare("SELECT k.id, serialNumber FROM hr_keys_attribution AS ka LEFT JOIN hr_keys AS k ON k.id=ka.id_key WHERE id_user=:id AND k.isBlocked=0");
                keyQuery.bindValue(":id", userQuery.value(0) );
                keyQuery.exec();

                while(keyQuery.next())
                {
                    QSqlQuery insertQuery;
                    insertQuery.prepare("INSERT INTO hr_gantner_standalone_action (`type`, `func`, `userId`,`keyId`, `deviceId`, `param`, `param2`) VALUES (:type,:func,:userId,:keyId,:deviceId, :param, :param2)");
                    insertQuery.bindValue(":func", "add");
                    insertQuery.bindValue(":type", "key");
                    insertQuery.bindValue(":userId", userQuery.value(0) );
                    insertQuery.bindValue(":keyId", keyQuery.value(0) );
                    insertQuery.bindValue(":deviceId", deviceId );
                    insertQuery.bindValue(":param", keyQuery.value(1)  );
                    insertQuery.bindValue(":param2", "" );
                    insertQuery.exec();
                }
            }
        }
    }

    // Reload all absent reason
    QSqlQuery deviceQuery("SELECT id_device FROM hr_gantner_TimeTerminal");

    while( deviceQuery.next())
    {
        QSqlQuery timeCodeInQuery("SELECT * FROM hr_timux_timecode WHERE signtype='both' OR signtype='in'");

        while( timeCodeInQuery.next())
        {
            QSqlQuery insertQuery;
            insertQuery.prepare("INSERT INTO hr_gantner_standalone_action (`type`, `func`, `userId`,`keyId`, `deviceId`, `param`, `param2`,`reasonId`) VALUES (:type,:func,:userId,:keyId,:deviceId, :param, :param2,:reasonId)");
            insertQuery.bindValue(":func", "add");
            insertQuery.bindValue(":type", "reason");
            insertQuery.bindValue(":userId", 0 );
            insertQuery.bindValue(":keyId", 0 );
            insertQuery.bindValue(":deviceId", deviceQuery.value(0) );
            insertQuery.bindValue(":param", timeCodeInQuery.value(13).toString()  );
            insertQuery.bindValue(":param2", "1" );
            insertQuery.bindValue(":reasonId", timeCodeInQuery.value(0).toString() + "_IN" );
            insertQuery.exec();
        }

        QSqlQuery timeCodeOutQuery("SELECT * FROM hr_timux_timecode WHERE signtype='both' OR signtype='out'");

        while( timeCodeOutQuery.next())
        {
            QSqlQuery insertQuery;
            insertQuery.prepare("INSERT INTO hr_gantner_standalone_action (`type`, `func`, `userId`,`keyId`, `deviceId`, `param`, `param2`,`reasonId`) VALUES (:type,:func,:userId,:keyId,:deviceId, :param, :param2,:reasonId)");
            insertQuery.bindValue(":func", "add");
            insertQuery.bindValue(":type", "reason");
            insertQuery.bindValue(":userId", 0 );
            insertQuery.bindValue(":keyId", 0 );
            insertQuery.bindValue(":deviceId", deviceQuery.value(0) );

            insertQuery.bindValue(":param", timeCodeOutQuery.value(13).toString()  );

            insertQuery.bindValue(":param2", "2" );
            insertQuery.bindValue(":reasonId", timeCodeOutQuery.value(0).toString() + "_OUT" );
            insertQuery.exec();
        }

        //Reload balances text
        QSqlQuery balancesTextQuery("SELECT * FROM hr_timux_timecode WHERE (type='overtime' OR type='leave') AND (defaultHoliday=1 OR defaultOvertime=1) ORDER BY type");
        int fieldNo = 0;
        while( balancesTextQuery.next())
        {
            QSqlQuery insertQuery;
            insertQuery.prepare("INSERT INTO hr_gantner_standalone_action (`type`, `func`, `userId`,`keyId`, `deviceId`, `param`, `param2`) VALUES (:type,:func,:userId,:keyId,:deviceId, :param, :param2)");
            insertQuery.bindValue(":func", "add");
            insertQuery.bindValue(":type", "balancesText");
            insertQuery.bindValue(":userId", 0 );
            insertQuery.bindValue(":keyId", 0 );
            insertQuery.bindValue(":deviceId", deviceQuery.value(0) );
            insertQuery.bindValue(":param",fieldNo  );
            insertQuery.bindValue(":param2",balancesTextQuery.value(13).toString()  );
            insertQuery.exec();
            fieldNo++;
        }

        for(int i=fieldNo; i<10; i++)
        {
            QSqlQuery insertQuery;
            insertQuery.prepare("INSERT INTO hr_gantner_standalone_action (`type`, `func`, `userId`,`keyId`, `deviceId`, `param`, `param2`) VALUES (:type,:func,:userId,:keyId,:deviceId, :param, :param2)");
            insertQuery.bindValue(":func", "add");
            insertQuery.bindValue(":type", "balancesText");
            insertQuery.bindValue(":userId", 0 );
            insertQuery.bindValue(":keyId", 0 );
            insertQuery.bindValue(":deviceId", deviceQuery.value(0) );
            insertQuery.bindValue(":param",i  );
            insertQuery.bindValue(":param2","");
            insertQuery.exec();
        }

        //Reload user balances
        checkBalances();

        // Remove all the load time code
        for(int i=1; i<=20; i++ ) {
            QSqlQuery insertQuery;
            insertQuery.prepare("INSERT INTO hr_gantner_standalone_action (`type`, `func`, `userId`,`keyId`, `deviceId`, `param`) VALUES (:type,:func,:userId,:keyId,:deviceId, :param)");
            insertQuery.bindValue(":func", "sub");
            insertQuery.bindValue(":type", "load");
            insertQuery.bindValue(":userId", 0 );
            insertQuery.bindValue(":keyId", 0 );
            insertQuery.bindValue(":deviceId", deviceQuery.value(0) );
            insertQuery.bindValue(":param",i  );
            insertQuery.exec();

        }

        //Reload the load time code
        QSqlQuery loadQuery("SELECT * FROM hr_timux_timecode WHERE type='load' ORDER BY name");
        while( loadQuery.next()) {
            QSqlQuery insertQuery;
            insertQuery.prepare("INSERT INTO hr_gantner_standalone_action (`type`, `func`, `userId`,`keyId`, `deviceId`, `param`, `param2`, `param3`) VALUES (:type,:func,:userId,:keyId,:deviceId, :param, :param2, :param3)");
            insertQuery.bindValue(":type", "load");
            insertQuery.bindValue(":func", "add");
            insertQuery.bindValue(":userId", 0 );
            insertQuery.bindValue(":keyId", 0 );
            insertQuery.bindValue(":deviceId", deviceQuery.value(0) );
            insertQuery.bindValue(":param",loadQuery.value(15).toString()  );
            insertQuery.bindValue(":param2",loadQuery.value(2).toString());
            insertQuery.bindValue(":param3",loadQuery.value(13).toString());
            insertQuery.exec();

        }
    }

}
CUDADeviceManager::CUDADeviceManager() 
{
    deviceQuery();
}