public bool AddProjectSkillResource(ProjectSkillResourceRequest request) { bool status = false; try { ProjectSkillResource psr = new ProjectSkillResource(); psr.projectId = request.projectId; psr.skillId = request.skillId; psr.competencyLevelId = request.competencyLevelId; psr.expectedResourceCount = request.expectedResourceCount; psr.availableResourceCount = request.availableResourceCount; SqlSvrDAL dal = new SqlSvrDAL(request.ClientInfo); status = dal.AddProjectSkillResource(request.projectId, psr); } catch (Exception ex) { //LogHelper.AddLog("ProjectController,AddProjectSkillResource", ex.Message, ex.StackTrace, "HCL.Academy.Service", request.ClientInfo.emailId); TelemetryClient telemetry = new TelemetryClient(); telemetry.TrackException(ex); } return(status); }
public JsonResult UploadProjectDataFile(HttpPostedFileBase uploadedFile) { IDAL dal = (new DALFactory()).GetInstance(); bool isProjectInserted = false; StringBuilder logText = new StringBuilder(); try { logText.Append("<table border = '1'> <tr><th>Result</th></tr>"); if (uploadedFile != null && uploadedFile.ContentLength > 0) { #region Read file data if ((uploadedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" || uploadedFile.ContentType == "application/octet-stream") && (uploadedFile.FileName.EndsWith(".xls") || uploadedFile.FileName.EndsWith(".xlsx"))) { string url = ConfigurationManager.AppSettings["URL"].ToString(); List <Skill> allSkills = null; List <Competence> allCompetencies = null; List <Project> allProjects = null; try { allSkills = dal.GetAllSkills(); allCompetencies = dal.GetAllCompetenceList(); allProjects = dal.GetAllProjects(); } catch (Exception ex) { UserManager user = (UserManager)Session["CurrentUser"]; LogHelper.AddLog(new LogEntity(AppConstant.PartitionError, user.EmailID.ToString(), AppConstant.ApplicationName, "ImportProject,UploadProjectDataFile", ex.Message, ex.StackTrace)); } List <DataTable> listDataTable = new List <DataTable>(); ProjectData objProjectData = new ProjectData(); using (SpreadsheetDocument doc = SpreadsheetDocument.Open(uploadedFile.InputStream, false)) { Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild <Sheet>(); var listOfSheets = Utilities.GetAllWorksheets(doc); foreach (Sheet sheetItem in listOfSheets) { string sheetName = sheetItem.Name; string sheetId = sheetItem.Id.Value; Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheetId) as WorksheetPart).Worksheet; IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>(); DataTable dt = new DataTable(); int rowcount = rows.Count(); foreach (Row row in rows) { if (row != null) { if (row.RowIndex.Value == 1) { foreach (Cell cell in row.Descendants <Cell>()) { dt.Columns.Add(Utilities.GetSpreadsheetCellValue(doc, cell)); } } else { dt.Rows.Add(); int i = 0; foreach (Cell cell in row.Descendants <Cell>()) { dt.Rows[dt.Rows.Count - 1][i] = Utilities.GetSpreadsheetCellValue(doc, cell); i++; } } } } listDataTable.Add(dt); } } objProjectData.projects = new List <Project>(); Project objProject = null; foreach (DataRow item in listDataTable[0].Rows) { objProject = new Project(); objProject.ProjectName = Convert.ToString(item.ItemArray[0]); List <Project> itemProject = allProjects.Where(project => (project.ProjectName).ToLower() == (objProject.ProjectName).ToLower()).ToList(); if (itemProject != null && itemProject.Count() > 0) { string duplicateProject = itemProject.FirstOrDefault().ProjectName; logText.Append("<tr><td class='error'>The Project <span class='bold'>" + duplicateProject + "</span>already present.<td><tr>"); } else { objProjectData.projects.Add(objProject); } } objProjectData.projectSkills = new List <ProjectSkill>(); ProjectSkill objProjectSkill = null; logText.Append("<tr><td> </td></tr>"); foreach (DataRow item in listDataTable[1].Rows) { objProjectSkill = new ProjectSkill(); objProjectSkill.Project = item.ItemArray[0] != null?Convert.ToString(item.ItemArray[0]) : ""; objProjectSkill.Skill = item.ItemArray[1] != null?Convert.ToString(item.ItemArray[1]) : ""; List <Skill> objSkill = allSkills.Where(i => i.SkillName == objProjectSkill.Skill).ToList(); objProjectSkill.SkillId = objSkill != null && objSkill.Count() > 0 ? Convert.ToInt32(objSkill.FirstOrDefault().SkillId) : -1; if (objProjectSkill.SkillId == -1) { logText.Append("<tr><td class='error'>Project :<span class='bold'>" + objProjectSkill.Project + "</span>Skill : <span class='bold'>" + objProjectSkill.Skill + "</span> is not valid<td><tr>"); } else { objProjectData.projectSkills.Add(objProjectSkill); } } objProjectData.projectSkillResources = new List <ProjectSkillResource>(); ProjectSkillResource objProjectSkillResource = null; logText.Append("<tr><td> </td></tr>"); foreach (DataRow item in listDataTable[2].Rows) { objProjectSkillResource = new ProjectSkillResource(); objProjectSkillResource.ProjectName = Convert.ToString(item.ItemArray[0]); objProjectSkillResource.Skill = Convert.ToString(item.ItemArray[1]); objProjectSkillResource.CompetencyLevel = Convert.ToString(item.ItemArray[2]); objProjectSkillResource.ExpectedResourceCount = Convert.ToString(item.ItemArray[3]); objProjectSkillResource.AvailableResourceCount = Convert.ToString(item.ItemArray[4]); List <Competence> itemSkillResource = allCompetencies.Where(i => i.SkillName == objProjectSkillResource.Skill && i.CompetenceName.ToUpper() == objProjectSkillResource.CompetencyLevel.ToUpper()).ToList(); if (itemSkillResource != null && itemSkillResource.Count() > 0) { objProjectSkillResource.CompetencyLevelId = itemSkillResource != null && itemSkillResource.Count() > 0 ? itemSkillResource.FirstOrDefault().CompetenceId : -1; objProjectSkillResource.SkillId = itemSkillResource != null && itemSkillResource.Count() > 0 ? itemSkillResource.FirstOrDefault().SkillId : -1; objProjectData.projectSkillResources.Add(objProjectSkillResource); } else { logText.Append("<tr><td class='error'>" + "For Project : <span class='bold'>" + objProjectSkillResource.ProjectName + "</span>" + "Skill : <span class='bold'>" + objProjectSkillResource.Skill + "</span>" + ", Competency Level : <span class='bold'>" + objProjectSkillResource.CompetencyLevel + "</span>" + " Combination doesn't exist. So it can't be added to ProjectSkillResource list<td><tr>" ); } } isProjectInserted = InsertProjectFromExcel( objProjectData.projects, objProjectData.projectSkills, objProjectData.projectSkillResources, ref logText); } else { logText.Append("<tr><td>Please upload correct file format<td><tr>"); } #endregion Read file data } else { logText.Append("<tr><td>Please upload correct file format<td><tr>"); } logText.Append("</table>"); } catch (Exception ex) { UserManager user = (UserManager)Session["CurrentUser"]; LogHelper.AddLog(new LogEntity(AppConstant.PartitionError, user.EmailID.ToString(), AppConstant.ApplicationName, "ImportProject,UploadProjectDataFile", ex.Message, ex.StackTrace)); } return(Json(new { statusCode = 200, status = isProjectInserted, message = logText.ToString(), }, JsonRequestBehavior.AllowGet)); }