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