Agregar un usuario en las tareas de otros usuarios y con los mismos roles

Cuando damos de alta un nuevo usuario en el Redmine, lo usual es que nos pidan que le agreguemos a los mismo proyectos y con los mismos roles que algún usuario específico.

Hasta ahora lo haciamos mirando en la propia herramienta los proyectos a los que estaba asociado ese usuario en concreto, y uno a uno lo agregábamos con los mismos roles.

A partir de ahora se puede hacer en la propia BBDDs:

En este ejemplo nos piden que agreguemos al usuario jmrelo a los mismos proyectos y con los mismos roles que dmaza y rgarrote:

Obtenemos los ids de esos usuarios:

mysql> select * from users where login="dmaza";
+----+-------+------------------------------------------+-----------+----------+-----------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| id | login | hashed_password                          | firstname | lastname | mail            | mail_notification | admin | status | last_login_on       | language | auth_source_id | created_on          | updated_on          | type |
+----+-------+------------------------------------------+-----------+----------+-----------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| 13 | dmaza | 9b53b2097a54c0324b98cea1136f7a40fd47e35b | Daniel    | Maza     | dmaza@luismido.com |                 0 |     0 |      1 | 2011-06-28 12:40:18 | es       |              1 | 2009-03-10 14:35:57 | 2011-06-28 12:40:18 | NULL |
+----+-------+------------------------------------------+-----------+----------+-----------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
1 row in set (0.00 sec)

mysql> select * from users where login="rgarrote";
+----+----------+------------------------------------------+-----------+----------+--------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| id | login    | hashed_password                          | firstname | lastname | mail               | mail_notification | admin | status | last_login_on       | language | auth_source_id | created_on          | updated_on          | type |
+----+----------+------------------------------------------+-----------+----------+--------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| 25 | rgarrote | dc0bbc7553741a04bb7bd07d70b45b77ce4a3f03 | Rafael    | Garrote  | rgarrote@luismido.com |                 0 |     0 |      1 | 2011-09-13 17:09:37 | es       |              1 | 2009-03-11 00:07:41 | 2011-09-13 17:09:37 | NULL |
+----+----------+------------------------------------------+-----------+----------+--------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
1 row in set (0.00 sec)

mysql> select * from users where login="jmerelo";
+-----+---------+------------------------------------------+-----------+----------+-------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| id  | login   | hashed_password                          | firstname | lastname | mail              | mail_notification | admin | status | last_login_on       | language | auth_source_id | created_on          | updated_on          | type |
+-----+---------+------------------------------------------+-----------+----------+-------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| 109 | jmerelo | 6213fed2eba4c79b1ac5b08adfe2c187445d79d4 | Julio     | Merelo   | jmerelo@luismido.com |                 0 |     0 |      1 | 2011-09-13 10:11:55 | es       |              1 | 2011-09-13 10:03:31 | 2011-09-13 10:11:55 | NULL |
+-----+---------+------------------------------------------+-----------+----------+-------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+

Obtenemos los proyectos en los que ambos usuarios concretos participan, con los datos que nos interesan:

mysql> SELECT project_id, role_id FROM members WHERE user_id=13 OR user_id=25 GROUP BY project_id;
+------------+---------+
| project_id | role_id |
+------------+---------+
|          3 |       4 |
|          4 |       5 |
|          5 |       4 |
|          6 |       3 |
|         56 |       4 |
|         57 |       4 |
|         59 |       3 |
|         62 |       4 |
|         69 |       5 |
|         73 |       5 |
|         75 |       4 |
|         76 |       3 |
|         77 |       3 |
|         78 |       4 |
|         79 |       3 |
|         81 |       3 |
|         82 |       3 |
|         83 |       4 |
|         86 |       3 |
|         88 |       4 |
|        100 |       3 |
|        102 |       6 |
|        103 |       4 |
|        104 |       4 |
|        105 |       4 |
|        107 |       4 |
|        108 |       6 |
|        110 |       6 |
|        112 |       4 |
|        113 |       4 |
|        118 |       4 |
|        119 |       3 |
|        120 |       4 |
|        121 |       3 |
|        122 |       3 |
|        124 |       4 |
|        125 |       3 |
|        127 |       4 |
|        128 |       3 |
|        130 |       4 |
|        131 |       3 |
|        132 |       3 |
|        133 |       4 |
|        137 |       3 |
|        139 |       3 |
|        140 |       3 |
|        141 |       4 |
|        142 |       3 |
|        143 |       3 |
|        146 |       3 |
|        147 |       3 |
|        152 |       3 |
|        153 |       3 |
|        154 |       4 |
|        156 |       3 |
|        159 |       3 |
|        160 |       3 |
|        163 |       3 |
|        164 |       3 |
|        167 |       3 |
|        170 |       3 |
|        172 |       3 |
|        173 |       3 |
|        174 |       3 |
|        175 |       4 |
|        176 |       3 |
|        177 |       3 |
|        178 |       3 |
|        180 |       3 |
|        181 |       3 |
|        183 |       3 |
|        184 |       3 |
|        185 |       3 |
|        186 |       3 |
|        190 |       3 |
|        191 |       3 |
|        192 |       3 |
|        193 |       3 |
|        195 |       3 |
|        196 |       3 |
|        197 |       3 |
|        198 |       3 |
|        199 |       3 |
|        200 |       3 |
|        201 |       3 |
|        203 |       3 |
+------------+---------+
86 rows in set (0.00 sec)

De esta forma nos saca todos los proyectos en los que participan bien solo uno de ellos o los dos, y con el role que tenga dmaza, porque está ordenado de forma ascendente. Nos vale ya que el usuario jmerelo lo agregaremos a los mismos proyectos con un solo role.

Creamos un script en php para realizar las inserciones:

<?php
# Datos de conexión a la BBDD
$conexion = mysql_connect("localhost", "xxxxxxx", "xxxxxxxx");
mysql_select_db("redmine", $conexion);

# Querys
$queEmp1 = "SELECT project_id, role_id FROM members WHERE user_id=13 OR user_id=25 GROUP BY project_id;";
$resEmp1 = mysql_query($queEmp1, $conexion) or die(mysql_error());
$totEmp1 = mysql_num_rows($resEmp1);

if ($totEmp1> 0) {
        while ($rowEmp1 = mysql_fetch_assoc($resEmp1)) {
                $queEmp2 = "INSERT INTO members(user_id, project_id, role_id, created_on) VALUES(109, ".$rowEmp1['project_id'].", ".$rowEmp1['role_id'].", '2011-09-14 14:58:53');";
                $resEmp2 = mysql_query($queEmp2, $conexion) or die(mysql_error());
         }
      }
?>

Lo ejecutamos accediendo a la dirección en la que lo hemos guardado y comprobamos que se han realizado bien tanto por web como en la BBDD:

mysql> select * from members where user_id=109;
+------+---------+------------+---------+---------------------+-------------------+
| id   | user_id | project_id | role_id | created_on          | mail_notification |
+------+---------+------------+---------+---------------------+-------------------+
| 3262 |     109 |          3 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3263 |     109 |          4 |       5 | 2011-09-14 14:58:53 |                 0 |
| 3264 |     109 |          5 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3265 |     109 |          6 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3266 |     109 |         56 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3267 |     109 |         57 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3268 |     109 |         59 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3269 |     109 |         62 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3270 |     109 |         69 |       5 | 2011-09-14 14:58:53 |                 0 |
| 3271 |     109 |         73 |       5 | 2011-09-14 14:58:53 |                 0 |
| 3272 |     109 |         75 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3273 |     109 |         76 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3274 |     109 |         77 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3275 |     109 |         78 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3276 |     109 |         79 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3277 |     109 |         81 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3278 |     109 |         82 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3279 |     109 |         83 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3280 |     109 |         86 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3281 |     109 |         88 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3282 |     109 |        100 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3283 |     109 |        102 |       6 | 2011-09-14 14:58:53 |                 0 |
| 3284 |     109 |        103 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3285 |     109 |        104 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3286 |     109 |        105 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3287 |     109 |        107 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3288 |     109 |        108 |       6 | 2011-09-14 14:58:53 |                 0 |
| 3289 |     109 |        110 |       6 | 2011-09-14 14:58:53 |                 0 |
| 3290 |     109 |        112 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3291 |     109 |        113 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3292 |     109 |        118 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3293 |     109 |        119 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3294 |     109 |        120 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3295 |     109 |        121 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3296 |     109 |        122 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3297 |     109 |        124 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3298 |     109 |        125 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3299 |     109 |        127 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3300 |     109 |        128 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3301 |     109 |        130 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3302 |     109 |        131 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3303 |     109 |        132 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3304 |     109 |        133 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3305 |     109 |        137 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3306 |     109 |        139 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3307 |     109 |        140 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3308 |     109 |        141 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3309 |     109 |        142 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3310 |     109 |        143 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3311 |     109 |        146 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3312 |     109 |        147 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3313 |     109 |        152 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3314 |     109 |        153 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3315 |     109 |        154 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3316 |     109 |        156 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3317 |     109 |        159 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3318 |     109 |        160 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3319 |     109 |        163 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3320 |     109 |        164 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3321 |     109 |        167 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3322 |     109 |        170 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3323 |     109 |        172 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3324 |     109 |        173 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3325 |     109 |        174 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3326 |     109 |        175 |       4 | 2011-09-14 14:58:53 |                 0 |
| 3327 |     109 |        176 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3328 |     109 |        177 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3329 |     109 |        178 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3330 |     109 |        180 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3331 |     109 |        181 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3332 |     109 |        183 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3333 |     109 |        184 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3334 |     109 |        185 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3335 |     109 |        186 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3336 |     109 |        190 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3337 |     109 |        191 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3338 |     109 |        192 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3339 |     109 |        193 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3340 |     109 |        195 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3341 |     109 |        196 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3342 |     109 |        197 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3343 |     109 |        198 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3344 |     109 |        199 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3345 |     109 |        200 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3346 |     109 |        201 |       3 | 2011-09-14 14:58:53 |                 0 |
| 3347 |     109 |        203 |       3 | 2011-09-14 14:58:53 |                 0 |
+------+---------+------------+---------+---------------------+-------------------+
86 rows in set (0.00 sec)

Vemos que hay el mismo nº de registros. (86)

« Linux

Si no se indica lo contrario, el contenido de esta página se ofrece bajo Creative Commons Attribution-ShareAlike 3.0 License