public List <Client> GetClientList()
        {
            List <Client> list = new List <Client>();

            using (actitimeContext context = HttpContext.RequestServices.GetService(typeof(statusReport.Models.actitimeContext)) as actitimeContext)
            {
                using (MySqlConnection conn = context.GetConnection())
                {
                    conn.Open();
                    MySqlCommand cmd = new MySqlCommand("select * from customer order by Name", conn);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(new Client()
                            {
                                Id   = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString()
                            });
                        }
                    }
                }
            }
            return(list);
        }
Exemplo n.º 2
0
        public int getActiTimeHrs(int id)
        {
            try
            {
                List <string> userCommnets = new List <string>();

                using (actitimeContext actitimeContext = HttpContext.RequestServices.GetService(typeof(statusReport.Models.actitimeContext)) as actitimeContext)
                {
                    using (MySqlConnection conn = actitimeContext.GetConnection())
                    {
                        conn.Open();

                        MySqlCommand cmd = new MySqlCommand("select * from actitime.tt_record where user_id in (select user_id from actitime.user_project where project_id =" + Convert.ToInt32(id) + ") and task_id in (select id from actitime.task where project_id = " + Convert.ToInt32(id) + ")", conn);

                        MySqlDataReader reader = cmd.ExecuteReader();
                        DataTable       data   = reader.GetSchemaTable();

                        while (reader.Read())
                        {
                            // userCommnets.Add("test");
                        }
                    }

                    return(0);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Exemplo n.º 3
0
        public ActionResult getLastMonthHrs(int id, int reportDate)
        {
            try
            {
                double lastMonthHrs = 0.0;
                double currentYearHrsExcludingCurrMonth = 0.0;
                double totalHrsPrevYears = 0.0;


                //&date=20091202   (yyyymmdd)
                int year  = reportDate / 10000;
                int month = ((reportDate - (10000 * year)) / 100);
                int day   = DateTime.DaysInMonth(year, month); //(reportDate - (10000 * year) - (100 * month));

                string date = year.ToString() + '-' + month.ToString() + '-' + day.ToString();

                using (actitimeContext actitimeContext = HttpContext.RequestServices.GetService(typeof(statusReport.Models.actitimeContext)) as actitimeContext)
                {
                    using (MySqlConnection conn = actitimeContext.GetConnection())
                    {
                        conn.Open();

                        MySqlCommand cmd = new MySqlCommand(" select sum(actuals)/60 as Hrs from actitime.tt_record where task_id in (select id from actitime.task where project_id = " + Convert.ToInt32(id) + " and name not like '%Non Productive%' and name not like '%Non Billable%' and name not like '%Non-Productive%') and Year(record_date) < Year('" + date + "')", conn);

                        MySqlDataReader reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            if (reader["Hrs"] != DBNull.Value)
                            {
                                totalHrsPrevYears = Convert.ToDouble(reader["Hrs"]);
                            }
                        }
                    }
                    using (MySqlConnection conn = actitimeContext.GetConnection())
                    {
                        conn.Open();
                        MySqlCommand cmdPreviousYear = new MySqlCommand(" select sum(actuals)/60 as Hrs from actitime.tt_record where task_id in (select id from actitime.task where project_id = " + Convert.ToInt32(id) + " and name not like '%Non Productive%' and name not like '%Non Billable%' and name not like '%Non-Productive%') and month(record_date) < month('" + date + "') and Year(record_date) = year('" + date + "')", conn);

                        MySqlDataReader readerPreviousYear = cmdPreviousYear.ExecuteReader();

                        while (readerPreviousYear.Read())
                        {
                            if (readerPreviousYear["Hrs"] != DBNull.Value)
                            {
                                currentYearHrsExcludingCurrMonth = Convert.ToDouble(readerPreviousYear["Hrs"]);
                            }
                        }
                    }
                }

                return(Ok(new { lastMnthHrs = totalHrsPrevYears + currentYearHrsExcludingCurrMonth }));
            }
            catch (Exception)
            {
                throw;
            }
        }
Exemplo n.º 4
0
        public ActionResult GetWeekComments(string id, int reportDate, string clientID)
        {
            try
            {
                //List<userCommends> userCommnets = new List<userCommends>();
                string userCommnets = String.Empty;

                //&date=20091202   (yyyymmdd)
                int year  = reportDate / 10000;
                int month = ((reportDate - (10000 * year)) / 100);
                int day   = (reportDate - (10000 * year) - (100 * month));

                string date = year.ToString() + '-' + month.ToString() + '-' + day.ToString();

                using (actitimeContext actitimeContext = HttpContext.RequestServices.GetService(typeof(statusReport.Models.actitimeContext)) as actitimeContext)
                {
                    using (MySqlConnection conn = actitimeContext.GetConnection())
                    {
                        conn.Open();

                        MySqlCommand cmd = new MySqlCommand("select distinct comments from actitime.user_task_comment where task_id in (select id from actitime.task where project_id = " + Convert.ToInt32(id) + " and name not like '%Non Productive%' and name not like '%Non Billable%' and name not like '%Non-Productive%' and customer_id = " + Convert.ToInt32(clientID) + ") and week(comment_date,1) = week('" + date + "',1) and Year(comment_date) = year('" + date + "')", conn);

                        MySqlDataReader reader = cmd.ExecuteReader();
                        DataTable       data   = reader.GetSchemaTable();

                        while (reader.Read())
                        {
                            userCommnets += reader["comments"].ToString();
                            userCommnets += " , ";
                        }
                    }
                }

                return(Ok(new { weekComments = userCommnets }));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
 public ReportController(actitimeContext context, IEmailSender _emailSender)
 {
     dbContext = context;
 }
Exemplo n.º 6
0
        public ActionResult getLastWeekHrs(int id, int reportDate, string type)
        {
            try
            {
                double lastweekHrs       = 0.0;
                double currentMonthHours = 0.0;
                double currentweekHrs    = 0.0;
                double currentYearHrsExcludingCurrMonth = 0.0;
                double totalHrsPrevYears = 0.0;

                //&date=20091202   (yyyymmdd)
                int year  = reportDate / 10000;
                int month = ((reportDate - (10000 * year)) / 100);
                int day   = (reportDate - (10000 * year) - (100 * month));

                string date = year.ToString() + '-' + month.ToString() + '-' + day.ToString();

                int week = GetWeekNumberOfMonth(Convert.ToDateTime(date));

                using (actitimeContext actitimeContext = HttpContext.RequestServices.GetService(typeof(statusReport.Models.actitimeContext)) as actitimeContext)
                {
                    if (type?.Trim() == "Staff Augmented")
                    {
                        using (MySqlConnection conn = actitimeContext.GetConnection())
                        {
                            conn.Open();

                            MySqlCommand cmd = new MySqlCommand(" select sum(actuals)/60 as Hrs from actitime.tt_record where task_id in (select id from actitime.task where project_id = " + Convert.ToInt32(id) + " and name not like '%Non Productive%' and name not like '%Non Billable%' and name not like '%Non-Productive%') and month(record_date) = month('" + date + "') and Year(record_date) = year('" + date + "')", conn);

                            MySqlDataReader reader = cmd.ExecuteReader();

                            while (reader.Read())
                            {
                                if (reader["Hrs"] != DBNull.Value)
                                {
                                    currentMonthHours = Convert.ToDouble(reader["Hrs"]);
                                }
                            }
                        }
                        using (MySqlConnection conn = actitimeContext.GetConnection())
                        {
                            conn.Open();
                            MySqlCommand cmdCurrentWeek = new MySqlCommand(" select sum(actuals)/60 as Hrs from actitime.tt_record where task_id in (select id from actitime.task where project_id = " + Convert.ToInt32(id) + " and name not like '%Non Productive%' and name not like '%Non Billable%' and name not like '%Non-Productive%') and week(record_date) >= week('" + date + "') and month(record_date) = month('" + date + "') and Year(record_date) = year('" + date + "')", conn);

                            MySqlDataReader readerCurrentWeek = cmdCurrentWeek.ExecuteReader();

                            while (readerCurrentWeek.Read())
                            {
                                if (readerCurrentWeek["Hrs"] != DBNull.Value)
                                {
                                    currentweekHrs = Convert.ToDouble(readerCurrentWeek["Hrs"]);
                                }
                            }
                            if (week == 1)
                            {
                                lastweekHrs = 0;
                            }
                            else
                            {
                                lastweekHrs = currentMonthHours - currentweekHrs;
                            }
                        }
                    }
                    else
                    {
                        using (MySqlConnection conn = actitimeContext.GetConnection())
                        {
                            conn.Open();

                            MySqlCommand cmd = new MySqlCommand(" select sum(actuals)/60 as Hrs from actitime.tt_record where task_id in (select id from actitime.task where project_id = " + Convert.ToInt32(id) + " and name not like '%Non Productive%' and name not like '%Non Billable%' and name not like '%Non-Productive%') and Year(record_date) < Year('" + date + "')", conn);

                            MySqlDataReader reader = cmd.ExecuteReader();

                            while (reader.Read())
                            {
                                if (reader["Hrs"] != DBNull.Value)
                                {
                                    totalHrsPrevYears = Convert.ToDouble(reader["Hrs"]);
                                }
                            }
                        }
                        using (MySqlConnection conn = actitimeContext.GetConnection())
                        {
                            conn.Open();
                            MySqlCommand cmdPreviousYear = new MySqlCommand(" select sum(actuals)/60 as Hrs from actitime.tt_record where task_id in (select id from actitime.task where project_id = " + Convert.ToInt32(id) + " and name not like '%Non Productive%' and name not like '%Non Billable%' and name not like '%Non-Productive%') and Week(record_date) < Week('" + date + "') and Year(record_date) = year('" + date + "')", conn);

                            MySqlDataReader readerPreviousYear = cmdPreviousYear.ExecuteReader();

                            while (readerPreviousYear.Read())
                            {
                                if (readerPreviousYear["Hrs"] != DBNull.Value)
                                {
                                    currentYearHrsExcludingCurrMonth = Convert.ToDouble(readerPreviousYear["Hrs"]);
                                }
                            }
                        }

                        lastweekHrs = totalHrsPrevYears + currentYearHrsExcludingCurrMonth;
                    }
                }
                return(Ok(new { lastWKHrs = lastweekHrs }));
            }
            catch (Exception ex)
            {
                throw;
            }
        }