I am building a Project Management module for SugarCRM. It consists of a "Project" which has information on the project like Name, description, start and completion dates, creator name, status (open, in progress, cancelled, completed), priority (high, low, etc...), type (php, javascript, magento, sugarCRM, SEO, etc...).

A Project also has "Project Tasks" which a project can have as many Tasks as you want. A Project Task has many of the same DB fields as a Project does with some other features like sort order.

Lastly a Project has "Project Documents" which allow you to upload images, text files, any sort of file really related to the project.

On the Project database, I have a field called "subscribers" which is a text field and I store a comma separated list of User ID's for user's who are subscribed to updates/emails related to this project. So the subscriber field is on a Per Project basis. One user might be subscribed to Project 1 but not Project 2.

The reasoning behind this Subscriber database field on the Projects, is I would like to send out a daily email to all subscribed users showing Projects they are subscribed to that have been edited the day before. So if I am subscribed to "Project 1" and it's database fields are modified, it has new documents added, or new tasks added, completed, or modified, then I would get an email the following morning showing all that data in a Daily Recap email.

My idea for this comes from using the popular Project Management app/service Basecamp which sends out a Daily Recap every morning of all projects and activity from the previous day that you are subscribed to.

I am looking for feedback on how to best implement this system. I have everything coded and working up to the subscription part where the user ID's are saved to the Project database. So I basically will have a cron or similar that runs daily to send the emails to everyone that is subscribed.

I guess I am looking for the best approach to determine what projects and such should be in these emails. On my Project Tasks database, I have a modified datetime field so I can determine when the items were modified, same on the Projects table...

So I am thinking I can just query my 3 database tables and build a list based on items modified in the past day.

What Basecamp does, is instead of sending a separate email to the same user for each project they are subscribed to, they get 1 email. To do this, I would have to iterate over my user list instead of my project list. On each User, I would then have to query all the projects to find which projects that user is subscribed to, then run my queries on the appropriate projects. Keep in mind, I can't simply store a list of projects each user is subscribed to on the user table as this is a module.

I am just hoping to get some feedback, ideas, suggestions on improvements to this idea or a better way or if what I have outlined sounds pretty good?

  • Can you UNION the results of the 3 database table queries to build a single list for each user? – JeffO Sep 23 '14 at 12:19

You can query which projects or task have changed? -->Yes Then I would iterate over all this items, extract the users and build an inmemory-table, where for every user the changed items are registered. Then I iterate over the users, get the items I want to add into the mail, build the mail and send it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.