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