Using .Net to let users create Scheduled Tasks to send themselves issue tracking reports from MySql

by MikeHogg 11. March 2009 19:39

I had written a reporting page in asp.Net that queried the MySql database of our issue tracking system.  Users in different teams could use the page to create reports on the issues created, resolved, and worked on by their team as well as filtering by any number of other attributes. 

The page in itself was neat, and necessary, as the issue tracking system had grown at a pace of 50 new issues weekly for a couple of years.  But it was neater to build a way for different users to save their more complicated report filters, and even neater to allow them to send them an automatic email containing their report on any daily or weekly schedule.  Although it sounded like a lot of work at first, it turned out to be easy to set up.

The first part was to only show each user their own saved reports, and allow them to edit and save again, without affecting or viewing anybody else’s.  Because we were in an intranet environment, this was easy enough, using Windows as our authentication scheme meant I always already had the current user, and I could then save the reports in a table with their Id, and only show links to reports that they owned. 

The report page had over twenty filters and group by’s and sorts so saving all of the settings individually was not the way to go.  Instead I would take the output of that page, the actual query, generated dynamically, and save that.  Then all I had to do was get the query, saved in the database, and run it.  The code that created the query can be a great exercise for refactoring, and it is incredibly long, but you can imagine it was quick to write initially, and I only ever had to add one or two features to it …

        using (MySqlDataAdapter myAdp = new MySqlDataAdapter())
        {
            myCmdQuery.Append("select distinct iss_id  Issue_ID ");
            if (project) { myCmdQuery.Append(",prj_title Project "); };
            if (category) { myCmdQuery.Append(",prc_title Category "); };
            if (summary) { myCmdQuery.Append(",iss_summary  Summary "); };
            if (assignee)
            {
                myCmdQuery.Append(", (select group_concat(usr_full_name order by usr_full_name separator ', ') " +
                    " from eventum_user join eventum_issue_user " +
                    " on isu_usr_id = usr_id where isu_iss_id = iss_id) Assignees ");
            };
            if (status) { myCmdQuery.Append(",sta_title Status "); };
            if (priority) { myCmdQuery.Append(",pri_title Priority "); };
            if (createDate) { myCmdQuery.Append(",iss_created_date Created_Date "); };
            if (createAge) { myCmdQuery.Append(",datediff(curdate(), iss_created_date) Days_Old "); };
            if (updateDate) { myCmdQuery.Append(",iss_updated_date Updated_Date "); };
            if (updateAge) { myCmdQuery.Append(", datediff(curdate(),iss_updated_date) Days_Since_Update "); };
            if (updateUser)
            {
                myCmdQuery.Append(",(select usr_full_name from eventum_user join eventum_issue_history " +
                      "on usr_id = his_usr_id where his_id = (select max(his_id) from " +
                      "eventum_issue_history where his_iss_id = iss_id)) Update_User ");};
            if (updateSummary) { myCmdQuery.Append(",(select his_summary from eventum_issue_history where " +
                "his_id = (select max(his_id) from eventum_issue_history where his_iss_id = iss_id)) Update_Summary "); }; 
            if (abbrev)
            {
                if (notes) { myCmdQuery.Append(", left(n.not_note,200) Last_Internal_Note "); };
                if (closeComments) { myCmdQuery.Append(", left(nc.not_note,200) Closed_Comment "); };
            }
            else
            {
                if (notes) { myCmdQuery.Append(", n.not_note Last_Internal_Note "); };
                if (closeComments) { myCmdQuery.Append(", nc.not_note Closed_Comment "); };
            }
            if (custom) { myCmdQuery.Append(", cfo_value Custom "); };
            if (expResDate) { myCmdQuery.Append(", iss_expected_resolution_date Exp_Res_Date "); };
            if (dupe) {myCmdQuery.Append(", iss_duplicated_iss_id dupe "); };
            myCmdQuery.Append("FROM eventum_issue e ");
            myCmdQuery.Append("join eventum_status s on e.iss_sta_id = s.sta_id ");
            myCmdQuery.Append("join eventum_project p on e.iss_prj_id = p.prj_id ");
            myCmdQuery.Append("join eventum_project_category pc on e.iss_prc_id = pc.prc_id ");
            if (priority) { myCmdQuery.Append("join eventum_project_priority pp on e.iss_pri_id = pp.pri_id "); };
            if (notes)
            {
                myCmdQuery.Append("left outer join eventum_note n on iss_id = not_iss_id and not_id = " +
                    "  (select max(not_id) from eventum_note nn " +
                    "   where n.not_iss_id = nn.not_iss_id and nn.not_title <> 'Issue closed comments') ");
            }
            if (closeComments)
            {
                myCmdQuery.Append("left outer join eventum_note nc on iss_id = nc.not_iss_id and nc.not_id = " +
                    "  (select max(ncc.not_id) from eventum_note ncc " +
                    "   where nc.not_iss_id = ncc.not_iss_id and ncc.not_title = 'Issue closed comments') ");
            }
            if (custom)
            {
                myCmdQuery.Append("left outer join eventum_issue_custom_field on icf_iss_id = iss_id " +
                    "left outer join eventum_custom_field_option on cfo_id = icf_value ");
            } 
            if (this.ddlUserAssignment.SelectedIndex > 0)
            {
                myCmdQuery.Append("join eventum_issue_user on iss_id = isu_iss_id ");
            }
            myCmdQuery.Append("WHERE prj_status = 'active' ");
            if (this.ddlProject.SelectedIndex > 0)
            {
                myCmdQuery.Append("and prj_id = " + this.ddlProject.SelectedValue.ToString() + " ");
            }
            if (this.ddlUserAssignment.SelectedIndex > 0)
            {
                myCmdQuery.Append(" and isu_usr_id = " + this.ddlUserAssignment.SelectedValue.ToString() + " ");
            }
            if (this.rblDate.SelectedIndex == 3)  //NOT touched
            {
                myCmdQuery.Append("and not exists(select 3 from eventum_issue_history where his_iss_id = iss_id " );
                
                if (this.ddlGroupAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id in (select usr_id from eventum_user where usr_grp_id = " +
                        ddlGroupAction.SelectedValue + ") ");
                }
                if (this.ddlUserAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id = " + ddlUserAction.SelectedValue + " ");
                }
                if (numberOfDays != null)
                {
                    myCmdQuery.Append(" and datediff(curdate(), his_created_date) <= " + numberOfDays + " ");
                }
                else
                {
                    if (start != null)
                    {
                        myCmdQuery.Append(" and his_created_date >= '" + start + "' ");
                    }
                    if (end != null)
                    {
                        myCmdQuery.Append(" and his_created_date <= '" + end + "' ");
                    }
                }
                myCmdQuery.Append(") ");
            }
            if (this.rblDate.SelectedIndex == 2)  //touched
            {
                myCmdQuery.Append("and exists(select 2 from eventum_issue_history where his_iss_id = iss_id ");
                
                if (this.ddlGroupAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id in (select usr_id from eventum_user where usr_grp_id = " +
                        ddlGroupAction.SelectedValue + ") ");
                }
                if (this.ddlUserAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id = " + ddlUserAction.SelectedValue + " ");
                }
                 
                if (numberOfDays != null)
                {
                    myCmdQuery.Append(" and datediff(curdate(), his_created_date) <= " + numberOfDays + " ");
                }
                else
                {
                    if (start != null) { myCmdQuery.Append(" and his_created_date >= '" + start + "' "); };
                    if (end != null) { myCmdQuery.Append(" and his_created_date <= '" + end + "' "); }
                }
                myCmdQuery.Append(") ");
            }
            else if (this.rblDate.SelectedIndex == 1)  //closed
            {
                myCmdQuery.Append("and exists(select 1 from eventum_issue_history where his_iss_id = iss_id " + 
                    "and his_htt_id = 23 ");
                
                if (this.ddlGroupAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id in (select usr_id from eventum_user where usr_grp_id = " +
                        ddlGroupAction.SelectedValue + ") ");
                }
                if (this.ddlUserAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id = " + ddlUserAction.SelectedValue + " ");
                }
                if (numberOfDays != null)
                {
                    myCmdQuery.Append(" and datediff(curdate(), iss_closed_date) <= " + numberOfDays + " ");
                }
                else
                {
                    if (start != null) { myCmdQuery.Append(" and iss_closed_date >= '" + start + "' "); };
                    if (end != null) { myCmdQuery.Append(" and iss_closed_date <= '" + end + "' "); };
                }
                myCmdQuery.Append(") ");
            }
            else if (this.rblDate.SelectedIndex == 0)    //created
            {
                if (this.ddlGroupAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and iss_usr_id in (select usr_id from eventum_user where usr_grp_id = " + 
                        ddlGroupAction.SelectedValue + ") ");
                }
                if (this.ddlUserAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and iss_usr_id = " + ddlUserAction.SelectedValue + " ");
                }
                
                if (numberOfDays != null)
                {
                    myCmdQuery.Append(" and datediff(curdate(), iss_created_date) <= " + numberOfDays + " ");
                }
                else
                {
                    if (start != null) { myCmdQuery.Append(" and iss_created_date >= '" + start + "' "); };
                    if (end != null) { myCmdQuery.Append(" and iss_created_date <= '" + end + "' "); };
                }
                
            }
            if (closed && !open) { myCmdQuery.Append(" and iss_closed_date is not null "); };
            if (open && !closed) { myCmdQuery.Append(" and iss_closed_date is null "); };
            if (!open && !closed) { Response.Write("umm.  Not Closed and Not Open?  Kindly try again."); return; };
            if (this.ddlGroupAssignment.SelectedIndex > 0)
            {
                myCmdQuery.Append(" and iss_id in (select isu_iss_id from eventum_issue_user " +
                    "join eventum_user on isu_usr_id = usr_id " +
                    "where usr_grp_id = " + this.ddlGroupAssignment.SelectedValue + ") ");
            }
            if (this.txtKeyword.Text.Length > 0)
            { 
                myCmdQuery.Append(" and iss_id in ( select * from ((SELECT DISTINCT(iss_id)  FROM evdb.eventum_issue " +
                    "WHERE  MATCH(iss_summary, iss_description) AGAINST ('" + this.txtKeyword.Text + "' IN BOOLEAN MODE) " +
                    ") UNION (  SELECT DISTINCT(not_iss_id)  FROM  evdb.eventum_note " +
                    "WHERE  MATCH(not_note) AGAINST ('" + this.txtKeyword.Text + "' IN BOOLEAN MODE) " +
                    ") UNION ( SELECT DISTINCT(sup_iss_id) FROM evdb.eventum_support_email, " +
                    "evdb.eventum_support_email_body WHERE sup_id = seb_sup_id AND " +
                    "MATCH(seb_body) AGAINST ('" + this.txtKeyword.Text + "' IN BOOLEAN MODE) ) ) a )   ");
            }
            if (this.ddlCategory.SelectedIndex > 0) { myCmdQuery.Append(" and iss_prc_id = " + this.ddlCategory.SelectedValue.ToString()); };
            if (this.ddlPriority.SelectedIndex > 0) { myCmdQuery.Append(" and iss_pri_id = " + this.ddlPriority.SelectedValue.ToString()); };
            myAdp.SelectCommand = new MySqlCommand(myCmdQuery.ToString());
            myAdp.SelectCommand.Connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["evdbConn"].ToString());
            Session["_query"] = myCmdQuery.ToString();
            DataTable myTable = new DataTable();
            try
            {
                myAdp.Fill(myTable);
                DataView myDV = new DataView(myTable);
                // insert sorts in reverse order here  FIRST USER SORTS, THEN GROUPINGS
                if (this.ddlSort3.SelectedIndex > 0)
                {
                    addSort(ddlSort3.SelectedItem.Text.Replace(" ", "_"), ref myDV);
                }
                if (this.ddlSort2.SelectedIndex > 0)
                {
                    addSort(ddlSort2.SelectedItem.Text.Replace(" ", "_"), ref myDV);
                }
                if (this.ddlSort1.SelectedIndex > 0)
                {
                    addSort(ddlSort1.SelectedItem.Text.Replace(" ", "_"), ref myDV);
                }
                
                if (this.chkGroupAssignees.Checked)
                {
                    addSort("Assignees", ref myDV);
                }
                if (this.chkGroupCategory.Checked)
                {
                    addSort("Category", ref myDV);
                }
                if (this.chkGroupProject.Checked)
                {
                    addSort("Project", ref myDV);
                }

The result grid also had grouping on any of three separate attributes and was also written in the long declarative style, which I am not proud of, but it resulted in an organized and detailed report grid.

 

The second part required the use of the webserver’s Scheduled tasks to run daily and check the database for any reports that were scheduled to go at that time.  The task ran a simple console script.  I saved the task using impersonation and  .Net interfaced with Scheduled Tasks easily.

        ScheduledTasks st = new ScheduledTasks();
        Trigger newTrigger;
        Task newTask;
        string username = User.Identity.Name.Replace("SOMECORP\\","").ToLower();
        string reportName = "QBR" + username + this.txtScheduledReportName.Text;
        if (this.txtScheduledReportName.Text.Length > 0 && username.Length > 0)
        {
            try
            {
                int adjustedHour = (this.ddlAM.SelectedIndex == 0) ? Convert.ToInt16(this.ddlHour.SelectedValue) :
                    (Convert.ToInt16(this.ddlHour.SelectedValue)) + 12;
                st.DeleteTask(reportName); // in case we are saving new version
                DeleteReport(reportName);
                newTask = st.CreateTask(reportName);
                if (newTask != null)
                {
                    newTask.ApplicationName = "c:\\bin\\QbertEmailer.exe";
                    newTask.Parameters = "\"" + reportName + "\"";
                    newTask.SetAccountInformation("someserviceaccount", "password");
                    if (this.ddlFrequency.SelectedIndex == 0)
                    {
                        newTrigger = new DailyTrigger(
                            (short)adjustedHour,
                            (short)Convert.ToInt16(this.ddlMinute.SelectedValue.Replace(":", "")));
                    }
                    else
                    {
                        newTrigger = new WeeklyTrigger(
                            (short)adjustedHour,
                            (short)Convert.ToInt16(this.ddlMinute.SelectedValue.Replace(":", "")),
                            (DaysOfTheWeek)Convert.ToInt16(this.ddlWeekday.SelectedValue));
                    }
                    newTask.Triggers.Add(newTrigger);
                    // Call LogonUser to get a token for the user
                    Utils.ImpersonateUser iu = new Utils.ImpersonateUser();
                    iu.Impersonate("corp", "someserviceaccount", "password");
                    newTask.Save();
                    newTask.Close();
                    iu.Undo();
                    this.bindGrid();
                    this.SaveReport(reportName, true);
                    resetScheduleForm();
                }
                else
                {
                    Response.Write("Problem creating report name.  Please try again");
                }
            }
            catch (Exception ex)
            {
                logThis(ex.ToString(), EventLogEntryType.Error);
                Response.Write("Could not save report.  Click back and try again or contact your favorite QBERT support representative");
                //continue
            }
            finally
            {
                st.Dispose();
            }
        }
        else
        {
            Response.Write("Bad User or Report Name, cannot save.");
        }
    }

 

AD lookups provided email addresses.  I didn’t put the email addresses in our database, because that would just add another place users had to manage their email addresses, and employees already managed their emails through the company’s AD system. 

                using (DirectorySearcher ds = new DirectorySearcher("(samaccountname=" + user + ")"))
                {
                    
                    ds.PropertiesToLoad.Add("mail");
                    SearchResult sr = ds.FindOne();
                    if (sr == null)
                    {
                        return;
                    }
                    if (sr.Path.Length == 0)
                    {
                        return;
                    }
                    user = sr.Properties["mail"][0].ToString();
                }

The email was simple html, just formatting the results of the saved report query, run at that moment.

htmlReport.Append("<html><table cellspacing='0' border='1'style='border-collapse:collapse;font-size:9pt'>" + 
                        "<tr style='background-color:Silver;font-size:X-Small;'>");
                        foreach (DataColumn dc in dt.Columns)
                        {
                            htmlReport.Append("<td>" + dc.ColumnName + "</td>");
                        }
                        htmlReport.Append("</tr><tr>");
                        int x = 1;
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (x == 1)
                            {
                                htmlReport.Append("<tr>");
                                x = x * -1;
                            }
                            else
                            {
                                htmlReport.Append("<tr style='background-color:AliceBlue;'>");
                                x = x * -1;
                            }
                            foreach (object fieldVal in dr.ItemArray)
                            {
                                if (dr.ItemArray[0].ToString().Equals(fieldVal.ToString()))
                                {
                                    htmlReport.Append("<td><a href='http://boitqueue/view.php?id=" +
                                        fieldVal.ToString() + "'>" + fieldVal.ToString() + "</td>");
                                }
                                else
                                {
                                    htmlReport.Append("<td>" + fieldVal.ToString() + "</td>");
                                }
                            }
                            htmlReport.Append("</tr>");
                        }
                        htmlReport.Append("</table></html>");

About Mike Hogg

Mike Hogg is a c# developer in Brooklyn.

More Here

Favorite Books

This book had the most influence on my coding style. It drastically changed the way I write code and turned me on to test driven development even if I don't always use it. It made me write clearer, functional-style code using more principles such as DRY, encapsulation, single responsibility, and more. amazon.com

This book opened my eyes to a methodical and systematic approach to upgrading legacy codebases step by step. Incrementally transforming code blocks into testable code before making improvements. amazon.com

More Here