public IHttpActionResult GetEmployeeInformationForNotificationByTeam(string adGroupName)
{
var employeeObject = new Employee();
adGroupName = adGroupName.Replace(" and ", " & ");
var employeeList = new List <Employee>();
SurveyTeam teamObject = new SurveyTeam();
teamObject.Team = adGroupName;
SurveyRaterList raterInfo = new SurveyRaterList();
var ADUserName = Generic.GetCurrentLogonUserName();
raterInfo.Type = "Annually";
raterInfo.Period = "2016";
raterInfo.Team_Desc = adGroupName;
// raterInfo.Rater = "Zukiso Diko"; // new Surveys().getSurveyTeamEmployeeList().Where(c => c.AD_User_Name.ToLower() == ADUserName.ToLower()).First().Employee;
var employeeWithSurveys = new Surveys().getSurveysListByTeam(raterInfo);
employeeWithSurveys = employeeWithSurveys.Where(c => c.Team_Desc == adGroupName && c.Verified == true && c.Reviewed == true).ToList();
string data = JsonConvert.SerializeObject(employeeWithSurveys);
return(Ok(data));
}
public IHttpActionResult PopulateUserInformation(string adGroupName)
{
var surveyTeam = new SurveyTeam();
surveyTeam.Team = adGroupName;
return(Ok(new Surveys().getSurveyTeamList(surveyTeam)));
}
public IHttpActionResult GetAllEmployeeStatusSurveyReportPerBusinessUnit(string businessUnit)
{
SurveyPeriod survePeriodObject = new SurveyPeriod();
SurveyTeam teamObject = new SurveyTeam();
teamObject.Team = businessUnit;
survePeriodObject.Type = "Annually";
survePeriodObject.Period = "2016";
var surveyList = new Surveys().getSurveyCompletionStatusBU(survePeriodObject, teamObject);
return(Ok(surveyList));
}
//Return all fg employees per team Team
public List <SurveyTeamList> GetSurveyTeamList(SurveyTeam team)
{
return(new Surveys().getSurveyTeamList(team));
}
//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);
}
