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; } }
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); }
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; } }
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 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; } }