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