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