Want To Add All Users to Projects / Tasks

[Jason S]

A little lengthy explanation... Please stick with it to understand my logic.

I had some foresight when I first started running this to add about 25 "extra" users for future growth and personnel changes. We have people jump around on projects as needed for support and many times older projects pop up and need to have time applied to them. We decided to have all employees added to every project as we added new projects. This would allow anyone to be able to go back and put time on any project they may have to work on and avoided continuous maintenance of project participants. Over the few years of using the program we have unfortunately had some personnel churn and I went through all of the spare user accounts I had created. Now with each new user it seems we would have to go project by project to get them assigned or just have each new person contact the administrator as projects come up that they don't have access to in order to be added. Seems like a lot of headache and maintenance. There is a script that adds all tasks for projects assigned to a user globally. It would be handy to have a similar function for assigning all projects to all users. Maybe there would be a two step approach where all projects are added first and then all tasks related to those projects are added.

For those familiar with the structure of this database, is there any reason why the timesheet_assignments table couldn't be deleted and recreated. I don't believe there are any dependencies on it. I believe you can just delete the entire table and rebuild it with something like (not real programming language here... more like logical process):

FOR EACH proj_id

FOR EACH username

    ADD RECORD in timesheet_assignments with the following fields:

        proj_id  username  rate_id

We don't use the rate functions so I am not sure what that would do... Our rate_id for each would be 1 based on our existing database.

If anyone out there knows how to do scripting to generate this table, I would appreciate it. Also, if someone "in the know" can confirm if this table is somewhat expendable in terms of deleting it and recreating it.

Much appreciated... I need a quick fix if possible.

Thanks,

Jason

[tommo]

I think your analysis is correct, concerning the task_assignment table. You can delete it and replace it without too much problem. The time_entries contain the user (name or id) with a task id.

If you don't use the project rates, leave it as 1 as you said.

You probably don't need to change the project assignments table, just the task_assignments.

[Jason S]

I have created a new php file based on the assign all tasks to all users page to try and do a similar function to do what I outlined I wanted it to do. Again, I am not a php or mysql wiz kid and it appears that my code produces the correct data but it fails to enter it into the table. I ended up abandoning my efforts and simply had the programming create a CSV file which I manually imported into the table using Sequel Pro. It all seems to be working, but I would like to figure this out if anyone has any pointers to help others or for future use.

I have not upgraded to the most recent version since I have made other significant mods to the code to suit my needs.

Any help is appreciated! The code section here is what I created to attempt this function (note that the file is included as an attachment):

<?php } else {

//  $task_array=array();
$proj_array=array();
$user_array=array();

$sql = "SELECT proj_id FROM $PROJECT_TABLE";
$result = do_query($sql);

if($result) {
    while($data = mysql_fetch_array($result)) {
        $proj_id=$data["proj_id"];
        $proj_array[$proj_id]=0;
    }
}

$sql = "SELECT username FROM $USER_TABLE";
$result = do_query($sql);
if($result) {
    while($data = mysql_fetch_array($result)) {
        $username=$data["username"];
        $user_array[$username]=0;
    }
}

$sql = "DELETE from $ASSIGNMENTS_TABLE";
$rslt = do_query($sql);
print "All task assignments removed<br>\n";

//  $lastuser="";

//  $userprojcnt=0;
//  $usertaskcnt=0;

//  $sql = "SELECT username, proj_id FROM $ASSIGNMENTS_TABLE order by username";
//  $result = do_query($sql);
//  if($result) {
//      while($data = mysql_fetch_array($result)) {
//          $proj_id=$data["proj_id"];
//          $username=$data["username"];

//          if($lastuser=='') $lastuser=$username;
//          if($lastuser != $username) {
//              print "$lastuser assigned to $usertaskcnt tasks in $userprojcnt projects<br>\n";
//              $lastuser=$username;
//              $userprojcnt=0;
//              $usertaskcnt=0;
//          }
//          $userprojcnt++;

//          foreach($task_array[$proj_id] as $task_id => $value) {  
//              $sql = "INSERT into $TASK_ASSIGNMENTS_TABLE VALUES ($task_id,'$username',$proj_id)";
//              $rslt = do_query($sql);
//              $usertaskcnt++;
//          }

            foreach($proj_array as $proj => $value1) {

                foreach($user_array as $username => $value2) {

                    $sql = "INSERT into $ASSIGNMENTS_TABLE VALUES ($proj,$username,1)";
                    $rslt = do_query($sql);

//                  I used the following lines to create a CSV file and ended up just importing into the table manually.
//                  $tester = "$proj,$username,1\n";
//                  print "$tester";
//                  file_put_contents('/mnt/raid/ftp/new_assignments_table.csv', $tester, FILE_APPEND | LOCK_EX);

                }
            }

//      }
//      print "$lastuser assigned to $usertaskcnt tasks in $userprojcnt projects<br>\n";
//  }

}

?>