public List <ExcelSkill> ReadSkillData(string sheetNameInput) { List <ExcelSkill> skillData = new List <ExcelSkill>(); FileInfo file = new FileInfo(filePath); using (ExcelPackage package = new ExcelPackage(file)) { StringBuilder sb = new StringBuilder(); ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetNameInput]; //var totalRows = worksheet.Dimension.Address; //ExcelRange data = worksheet.Cells[totalRows]; //int rowCount = data.Rows; //int colCount = data.Columns; int rowCount = worksheet.Dimension.End.Row; int colCount = worksheet.Dimension.End.Column; //Console.WriteLine("ROWS: " + rowCount.ToString() + "\nCOLUMNS: " + colCount.ToString()); for (int i = 2; i <= rowCount; i++) { string name = String.Empty; string add = String.Empty; string remove = String.Empty; for (int j = 1; j <= colCount; j++) { if (j == 1 && worksheet.Cells[i, j].Value != null) { name = worksheet.Cells[i, j].Value.ToString(); } else if (j == 2 && worksheet.Cells[i, j].Value != null) { add = worksheet.Cells[i, j].Value.ToString(); } else if (j == 3 && worksheet.Cells[i, j].Value != null) { remove = worksheet.Cells[i, j].Value.ToString(); } else if (add == String.Empty || add == null) { continue; } } if (add != String.Empty && add != null && add != "" && add.Length > 2) { //Console.WriteLine("Adding " + name); ExcelSkill skill = new ExcelSkill(name, add, remove); skillData.Add(skill); } } } return(skillData); }
// Gets all skills associated with the input agent's Queue private string BuildSkillMap(ExcelSkill newQueue) { // Template skillMap XML --> Uses Replace in order to build new skillMap string string templateSkill = "<skillCompetency><competencelevel>COMPETENCY_LEVEL</competencelevel><skillNameUriPair name=\"SKILL_NAME\"><refURL>REF_URL</refURL></skillNameUriPair></skillCompetency>"; string skillMap = ""; string skillsReport = ""; bool allSkillsFound = true; foreach (KeyValuePair <string, int> kvp in newQueue.SkillsAdded) { try { // Determine Skill refUrl by querying APIData Skill addSkill = apiData.SkillsData.Skill.Where(p => p.SkillName.ToUpper() == kvp.Key.ToUpper()).First(); //Append skillMap string with new info skillMap += templateSkill.Replace("COMPETENCY_LEVEL", kvp.Value.ToString()).Replace("SKILL_NAME", addSkill.SkillName).Replace("REF_URL", addSkill.Self); skillsReport += $"{kvp.Key}({kvp.Value.ToString()}), "; } catch { // Log and output to console Error cm.LogMessage($"The skill {kvp.Key} was unable to be found within the API Skills. Please check the spelling and formatting of the skill name. The skill name formatting is extremely strict."); allSkillsFound = false; break; } } if (allSkillsFound == true) { // Add XML Outer Node onto new skillMap contents prior to replacing old skillMap XML Node skillMap = $"<skillMap>{skillMap}</skillMap>"; return(skillMap); } else { return("ERROR"); } }
public void ExcelQueueUpdate(ExcelData excelData, ref int eventId, bool wipeData = false) { cm.BeginLog(); cm.LogMessage("Beginning WFM Agent Queue Update Process using the UCCX API."); cm.LogMessage(""); int numFailed = 0; int numAgentsProcessed = 0; foreach (ExcelAgent excelAgent in excelData.excelAgents) { Stopwatch sw = new Stopwatch(); sw.Start(); // Determine Agent URL via apiData.ResourcesData string agentUserId = apiData.ResourcesData.Resource.Where(p => p.FirstName + " " + p.LastName == excelAgent.agentName).First().UserID; string agentUrl = $"{cm.RootURL}/resource/{agentUserId}"; try { // Determine which Resource from APIData.ResourcesData corresponds to the current excelAgent being processed Resource agentInfo = apiData.ResourcesData.Resource.Where(p => p.FirstName + " " + p.LastName == excelAgent.agentName).First(); // Serialize XML related to the current excelAgent being processed using Resource Object XmlDocument xml = SerializeXml(agentInfo); // Isolate Old skillMap Node XmlNode node = xml.SelectSingleNode("/resource/skillMap"); // Determine Agent's desired Queue ExcelSkill newQueue = excelData.excelSkills.Where(p => p.Name == excelAgent.Queue).First(); // Build Skill Map XML to replace current using Agent's desired Queue XmlDocument xmlSkillMap = new XmlDocument(); string skillMapString = BuildSkillMap(newQueue); // Determine Action based on whether or not error occurred in BuildSkillMap() if (skillMapString != "ERROR") { xmlSkillMap.LoadXml(skillMapString); // Create new XmlNode object to replace old skillMap with XmlNode newNode = xmlSkillMap.SelectSingleNode("/skillMap"); // If wipeData == True, remove all skills from Agents if (wipeData == false) { // Replace skillMap Node with new skillMap Node node.InnerXml = newNode.InnerXml; } else { // Remove all skills from Agents node.InnerXml = ""; } // Make PUT Request to update Agent Skill Map try { // Call Method to make PUT Request to API to update Agent skillMap cm.LogMessage($"Attempting to update {excelAgent.agentName} ({agentUserId}) to new Queue: {excelAgent.Queue}\n\tAgent refURL: {agentUrl}"); eventNum = eventId; HttpWebResponse requestResponse = UpdateAgentResource(xml.OuterXml, agentUrl); eventId++; sw.Stop(); if (requestResponse.StatusCode == HttpStatusCode.OK) { eventLog.WriteEntry($"Successfully updated the Agent data with Response Code: {requestResponse.StatusCode}\nDuration: ({sw.ElapsedMilliseconds}ms)"); cm.LogMessage($"Successfully updated the Agent data with Response Code: {requestResponse.StatusCode}\nDuration: ({sw.ElapsedMilliseconds}ms)"); } else { throw new System.ArgumentException($"Error - Status Code Returned: {requestResponse.StatusCode} -- {requestResponse.StatusDescription}"); } } catch (Exception e) { numFailed += 1; eventLog.WriteEntry("Invalid HttpWebResponse.StatusCode while attempting to UPDATE Agent Resource Data (" + agentUserId + ").\n" + e.Message.ToString(), EventLogEntryType.Warning, ++eventId); cm.LogMessage("-->Invalid HttpWebResponse.StatusCode while attempting to UPDATE Agent Resource Data (" + agentUserId + ")"); cm.LogMessage(e.Message.ToString()); } } else { numFailed += 1; } numAgentsProcessed += 1; } catch (Exception e) { eventLog.WriteEntry($"Error Occurred while updating current user {agentUserId}.\nAgent URL Endpoint Used: {agentUrl}\nError Caught: {e.Message.ToString()}\nError Source: {e.Source.ToString()}\nError Stack Trace: {e.StackTrace.ToString()}", EventLogEntryType.Error, eventId++); cm.LogMessage($"Error Occurred -- {e.Message.ToString()}"); cm.LogMessage($"Stack Trace: {e.StackTrace.ToString()}"); cm.LogMessage($"Continuing to the next agent..."); } cm.LogMessage(""); } cm.EndLog(); cm.BeginLog(); EndProcessLog(excelData.excelAgents.Count, numFailed); cm.EndLog(); eventLog.WriteEntry("UCCX API Agent Queue Update has finished.", EventLogEntryType.Information, eventId++); }