public IHttpActionResult GetEmployeeSurveyQuestions(string id)
{
//First get a list of all the questions
var Survey = new Surveys();
var surveyObject = new SurveyRaterSurvey();
surveyObject.Type = "Annually";
surveyObject.Period = "2016";
surveyObject.Rater = new Surveys().getSurveyTeamEmployeeList().Where(c => c.AD_User_Name.ToLower() == Generic.GetCurrentLogonUserName().ToLower()).First().Employee;
surveyObject.Ratee = id;
try
{
var surveyList = new Surveys().getSurveyFor(surveyObject);
//for (int i = 0; i < surveyList.Count(); i++)
//{
// surveyList[counter].Q_No = counter;
// counter++;
//}
return(Ok(surveyList.Distinct().ToList()));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public IHttpActionResult GetEmployeeSurveyQuestionsForReview(string id)
{
//First get a list of all the questions
var Survey = new Surveys();
var surveyObject = new SurveyForList();
surveyObject.Type = "Annually";
surveyObject.Period = "2016";
surveyObject.Survey_For = id;
//surveyObject.Rater = new Surveys().getSurveyTeamEmployeeList().Where(c => c.AD_User_Name.ToLower() == Generic.GetCurrentLogonUserName().ToLower()).First().Employee;
//surveyObject.Ratee = id;
try
{
var surveyList = new Surveys().getSurveyForListRaters(surveyObject).Distinct().ToList();
return(Ok(surveyList.Distinct().ToList()));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public IHttpActionResult GetEmployeeQuestionsByRater(string rater)
{
var empSurveyObject = GetSurveyObject();
var Survey = new Surveys();
var surveyObject = new SurveyRaterSurvey();
surveyObject.Type = "Annually";
surveyObject.Period = "2016";
surveyObject.Rater = new Surveys().getSurveyTeamEmployeeList().Where(c => c.AD_User_Name.ToLower() == Generic.GetCurrentLogonUserName().ToLower()).First().Employee;
surveyObject.Ratee = rater;
var raterListString = string.Empty;
var surveyForList = new Surveys().getSurveyForVerifyAllRaters(empSurveyObject);
foreach (var surveyObj in surveyForList)
{
raterListString += surveyObj.Rater + ";";
}
var list = new Surveys().getSurveyFor(surveyObject);
list[0].Rater = raterListString;
return(Ok(list.Distinct().ToList()));
}
public IHttpActionResult GetSurveyForVerifybyRater(string id)
{
//First get a list of all the questions
var Survey = new Surveys();
var surveyObject = new SurveyRaterSurvey();
surveyObject.Type = "Annually";
surveyObject.Period = "2016";
surveyObject.Manager = new Surveys().getSurveyTeamEmployeeList().Where(c => c.AD_User_Name.ToLower() == Generic.GetCurrentLogonUserName().ToLower()).First().Employee;
surveyObject.Ratee = id;
surveyObject.Rater = surveyObject.Manager;
try
{
var surveyList = new Surveys().getSurveyForVerifybyRater(surveyObject);
return(Ok(surveyList.Distinct().ToList()));
}
catch (Exception ex)
{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("fgnotes.futuregrowth.co.za");
mail.From = new MailAddress("*****@*****.**");
mail.To.Add("*****@*****.**");
mail.Subject = "HR Performance System - Verify Survey - Manager";
mail.Body = ex.Message + "User: " + Generic.GetCurrentLogonUserName();
SmtpServer.Port = 25;
SmtpServer.Send(mail);
throw new Exception(ex.Message);
}
}
public IHttpActionResult GetEmployeeSurveyQuestionsForraterUpdate(string id)
{
var surveyObject = new SurveyForList();
surveyObject.Type = "Annually";
surveyObject.Period = "2016";
surveyObject.Survey_For = id;
var raterListString = string.Empty;
var surveyForList = new Surveys().getSurveyForListRaters(surveyObject);
//foreach (var surveyObj in surveyForList.Distinct().ToList())
//{
// raterListString += surveyObj + ",";
//}
//for (int i = 0; i < list.Count; i++)
//{
// list[i].Rater = raterListString;
//}
try
{
//var surveyList = new Surveys().getSurveyFor(surveyObject);
return(Ok(surveyForList.Distinct().ToList()));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//The excel file is read from the provided file path, processes it and returns a list of all the valid questions that are in the spreadsheet
public static List <QuestionnaireObject> ProcessFileUpload(string fileName, string fileExtension, string questionnairePeriodType, string questionnairePeriodValue)
{
//Store all the questions that are in the spreadsheet
var questionList = new List <QuestionnaireObject>();
var ratersInformation = new List <Rater>();
var conString = GetExcelConnectionString(fileExtension, fileName);
//This ensures that only questions that have more than 3 raters are added proccessed and later saved into the database
var minimumNumberOfRaters = 0;
var excelSheetList = new List <string>();
//Get employee information from AD, this is used to validate a sheet against empoloyee Names
var ADEmpList = new Surveys().getSurveyTeamEmployeeList();
var teamList = new Surveys().getSurveyTeams();
using (OleDbConnection con = new OleDbConnection(conString))
{
try
{
con.Open();
//Reading a spreadsheet sheets
DataTable sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//Creating a datatable to store all the sheet names from survey excel spreadsheet
var exceldt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (exceldt == null)
{
return(null);
}
// Add the sheet name to the string array.
foreach (DataRow row in exceldt.Rows)
{
//Reading sheet names
var tempSheet = row["TABLE_NAME"].ToString();
if (tempSheet.ToString().Contains("$"))
{
tempSheet = tempSheet.ToString().Replace("'", "").Replace("$", "");
if (ADEmpList.Where(c => c.Employee.Trim().ToLower().Replace(" ", "") == tempSheet.Trim().ToLower().Replace(" ", "")).Count() > 0)
{
excelSheetList.Add(tempSheet);
}
}
}
//A sheet name has to be the name of the person selected from upload survey view / screen
var sheetName = excelSheetList.Count() > 0 ? excelSheetList.First().ToString() : string.Empty;
using (OleDbCommand command = new OleDbCommand("SELECT * FROM [" + sheetName + "$]", con))
{
using (OleDbDataReader reader = command.ExecuteReader())
{
//Get column names from a spreadsheet
var columnNames = GetColumnNames(reader);
//Compares uploaded file header against predefined survey spreadsheet (which is stored in the web config file) header columns
//if the columns do not match then the operation will stop and an empty list will be returned
if (!VerifyUploadedTemplate(columnNames))
{
return(new List <QuestionnaireObject>());
}
while (reader.Read())
{
DataTable dt = new DataTable();
//Get a question from the first col/row
var question = !string.IsNullOrWhiteSpace(reader[0].ToString()) ? reader[0].ToString() : string.Empty;
var questionRaterList = new List <Rater>();
for (int i = 1; i < reader.FieldCount; i++)
{
//Ensure that rater's name is not empty and add the name to the list of raters
if (!string.IsNullOrWhiteSpace(reader[i].ToString()))
{
questionRaterList.Add(new Rater()
{
Name = reader[i].ToString()
});
//if a rater is a team, get team size by team name and increment minimum number of raters by team size
if (teamList.Where(c => c.Team.ToLower() == reader[i].ToString().ToLower()).Count() > 0)
{
var surveyTeamObject = new SurveyTeam();
surveyTeamObject.Team = reader[i].ToString();
var teamInfo = new Surveys().getSurveyTeamList(surveyTeamObject);
minimumNumberOfRaters += teamInfo.Distinct().Count();
}
else
{
minimumNumberOfRaters++;
}
}
}
//Ensure that a question has at least 3 raters
if (minimumNumberOfRaters >= 3 && !string.IsNullOrWhiteSpace(question))
{
questionList.Add(new QuestionnaireObject()
{
Question = question, Raters = new List <Rater>(questionRaterList),
QuestionnairePeriodType = questionnairePeriodType,
QuestionnairePeriodValue = questionnairePeriodValue,
RateeName = sheetName
});
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
//var xml = XMLHelper.Serialize(questionList);
return(questionList);
}
