/* To invoke thin axl from .Net client
* ExecuteSQLQuery
* Simple RemovePhone request with mandatory tags
* ##Values in the method are hardcoded please modify accordingly to make it work###
*/
private static void callExecuteQuery(AXLAPIService axlApiService)
{
ExecuteSQLQueryReq exec = new ExecuteSQLQueryReq();
exec.sql = "select * from typestatus";
ExecuteSQLQueryRes resp = axlApiService.executeSQLQuery(exec);
Console.WriteLine("end of the callExecuteQuery" + resp.@return);
int i = 0;
Console.WriteLine("Number Of Rows Of Data retrived" + [email protected]);
while (i < [email protected])
{
System.Xml.XmlNode [] xmlNode = (System.Xml.XmlNode[])resp.@return[i];
int noChildNodes = xmlNode.Length;
int j = 0;
Console.WriteLine("<row>");
while (j < noChildNodes)
{
String name = xmlNode[j].Name;
String value = xmlNode[j].FirstChild.Value;
Console.WriteLine("<" + name + ">" + value + "</" + name + ">");
j++;
}
Console.WriteLine("</row>");
i++;
}
}
public override SpeedDial[] GetSpeedDials(string extension)
{
log.Debug("GetSpeedDial from " + extension);
List<SpeedDial> speeddials = new List<SpeedDial>();
string sql = "select label, speeddialindex, speeddialnumber from speeddial where fkdevice in (select pkid from device d where pkid in (select fkdevice from devicenumplanmap where fknumplan in (select pkid from numplan where dnorpattern = '" + extension + "'";
if (defaultContext != "")
{
sql += " and fkroutepartition in (select pkid from routepartition where name = '"+defaultContext+"')";
}
sql += ")))";
ExecuteSQLQueryReq esqr = new ExecuteSQLQueryReq();
esqr.sql = sql;
ExecuteSQLQueryRes esqres = _aas.executeSQLQuery(esqr);
if (esqres.@return != null)
{
if ([email protected] > 0)
{
foreach (XmlNode[] nodes in esqres.@return)
{
SpeedDial sd = new SpeedDial();
sd.displayName = nodes[0].InnerText;
sd.directoryNumber = nodes[2].InnerText;
sd.position = Convert.ToInt32(nodes[1].InnerText);
speeddials.Add(sd);
}
}
else
{
log.Debug("No speeddial available for " + extension);
}
}
return speeddials.ToArray();
}
private string GetPhoneFromLine(string line)
{
string phone = "";
ExecuteSQLQueryReq sql = new ExecuteSQLQueryReq();
sql.sql = "select name from device d inner join devicenumplanmap dnpm on dnpm.fkdevice = d.pkid inner join numplan np on np.pkid = dnpm.fknumplan where dnorpattern = '" + line + "' and fkroutepartition in (select pkid from routepartition where name = '" + _sharedLinePartition + "')";
if (_axl != null)
{
ExecuteSQLQueryRes res = _axl.executeSQLQuery(sql);
if (res != null)
{
if ([email protected] > 0)
{
if ([email protected] > 1)
{
string[] device = new string[[email protected]];
int compteur = 0;
foreach (object o in res.@return)
{
XmlNode[] element = (XmlNode[])o;
device[compteur] = element[0].InnerText;
compteur++;
}
throw new SharedLineException(line);
}
else
{
XmlNode[] element = (XmlNode[])res.@return[0];
phone = element[0].InnerText;
}
return(phone);
}
else
{
log.Error("Line " + line + " doesn't exist.");
throw new Exception("No phone device founded from this line");
}
}
else
{
throw new Exception("No result retreived.");
}
}
else
{
throw new System.Exception("Impossible d'éxécuter l'ordre sans client AXL");
}
}
public static DataSet Search(DirectoryType dir)
{
DataSet results = new DataSet();
DataTable dt = results.Tables.Add();
CiscoDatasourceType ldt = (CiscoDatasourceType)dir.Item;
try
{
Init(ldt.server, ldt.axluser, ldt.axluserpwd);
ExecuteSQLQueryReq query = new ExecuteSQLQueryReq();
query.sql = "select firstname, lastname, telephonenumber, department from enduser";
ExecuteSQLQueryRes response = _axl.executeSQLQuery(query);
dt.Columns.Add("firstname");
dt.Columns.Add("lastname");
dt.Columns.Add("telephonenumber");
dt.Columns.Add("department");
if (response != null && response.@return != null && [email protected] > 0)
{
foreach (object o in response.@return)
{
List <object> values = new List <object>();
values.Add(((XmlNode[])o)[0].InnerText);
values.Add(((XmlNode[])o)[1].InnerText);
values.Add(((XmlNode[])o)[2].InnerText);
values.Add(((XmlNode[])o)[3].InnerText);
dt.Rows.Add(values.ToArray());
}
}
else
{
log.Error("No result retreive from AXL Server");
}
return(results);
}
catch (Exception e)
{
log.Error(e.Message);
return(results);
}
}
public override bool IsPrivate(string dn)
{
bool isprivate = false;
if (System.Web.HttpContext.Current.Cache.Get(dn + "_privacy") == null)
{
ExecuteSQLQueryReq query = new ExecuteSQLQueryReq();
query.sql = "select name from dirgroup dg inner join enduserdirgroupmap eudgm";
query.sql += " on dg.pkid = eudgm.fkdirgroup inner join enduser eu on eu.pkid = ";
query.sql += "eudgm.fkenduser where eu.pkid in (select distinct eu.pkid from enduser eu";
query.sql += " inner join endusernumplanmap eunpm on eu.pkid = eunpm.fkenduser ";
query.sql += "inner join numplan np on np.pkid = eunpm.fknumplan where dnorpattern in ('";
query.sql += dn + "'))";
ExecuteSQLQueryRes response = _aas.executeSQLQuery(query);
if (response != null && response.@return != null)
{
foreach (XmlNode[] nodes in response.@return)
{
if (nodes.Length == 1)
{
if (System.Web.Configuration.WebConfigurationManager.AppSettings["CiscoPrivacyGroup"] == nodes[0].InnerText)
{
isprivate = true;
break;
}
}
}
}
else
{
log.Error("Unable to retreive group membership from " + dn);
}
System.Web.HttpContext.Current.Cache.Add(dn + "_privacy", isprivate, null, DateTime.Now.AddHours(10), System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.Normal, OnCacheSuppress);
}
else
{
isprivate = (bool)System.Web.HttpContext.Current.Cache.Get(dn + "_privacy");
}
return(isprivate);
}
public static DataSet Search(DirectoryType dir)
{
DataSet results = new DataSet();
DataTable dt = results.Tables.Add();
CiscoDatasourceType ldt = (CiscoDatasourceType)dir.Item;
try
{
Init(ldt.server, ldt.axluser, ldt.axluserpwd);
ExecuteSQLQueryReq query = new ExecuteSQLQueryReq();
query.sql = "select firstname, lastname, telephonenumber, department from enduser";
ExecuteSQLQueryRes response = _axl.executeSQLQuery(query);
dt.Columns.Add("firstname");
dt.Columns.Add("lastname");
dt.Columns.Add("telephonenumber");
dt.Columns.Add("department");
if (response != null && response.@return != null && [email protected] > 0)
{
foreach (object o in response.@return)
{
List<object> values = new List<object>();
values.Add(((XmlNode[])o)[0].InnerText);
values.Add(((XmlNode[])o)[1].InnerText);
values.Add(((XmlNode[])o)[2].InnerText);
values.Add(((XmlNode[])o)[3].InnerText);
dt.Rows.Add(values.ToArray());
}
}
else
{
log.Error("No result retreive from AXL Server");
}
return results;
}
catch (Exception e)
{
log.Error(e.Message);
return results;
}
}
public override bool IsPrivate(string dn)
{
bool isprivate = false;
if (System.Web.HttpContext.Current.Cache.Get(dn + "_privacy") == null)
{
ExecuteSQLQueryReq query = new ExecuteSQLQueryReq();
query.sql = "select name from dirgroup dg inner join enduserdirgroupmap eudgm";
query.sql += " on dg.pkid = eudgm.fkdirgroup inner join enduser eu on eu.pkid = ";
query.sql += "eudgm.fkenduser where eu.pkid in (select distinct eu.pkid from enduser eu";
query.sql += " inner join endusernumplanmap eunpm on eu.pkid = eunpm.fkenduser ";
query.sql += "inner join numplan np on np.pkid = eunpm.fknumplan where dnorpattern in ('";
query.sql += dn + "'))";
ExecuteSQLQueryRes response = _aas.executeSQLQuery(query);
if (response != null && response.@return != null)
{
foreach (XmlNode[] nodes in response.@return)
{
if (nodes.Length == 1)
{
if (System.Web.Configuration.WebConfigurationManager.AppSettings["CiscoPrivacyGroup"] == nodes[0].InnerText)
{
isprivate = true;
break;
}
}
}
}
else
{
log.Error("Unable to retreive group membership from " + dn);
}
System.Web.HttpContext.Current.Cache.Add(dn + "_privacy", isprivate, null, DateTime.Now.AddHours(10), System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.Normal, OnCacheSuppress);
}
else
{
isprivate = (bool)System.Web.HttpContext.Current.Cache.Get(dn + "_privacy");
}
return isprivate;
}
public bool RunQuery(string sqlQuery)
{
ErrorMsg = null;
ReturnDataSet = new List <Dictionary <string, string> >();
ExecuteSQLQueryReq sqlReq = new ExecuteSQLQueryReq();
sqlReq.sql = sqlQuery;
try
{
ExecuteSQLQueryRes sqlRes = AXLAPIClient.executeSQLQuery(sqlReq);
for (int i = 0; i < [email protected]; i++)
{
System.Xml.XmlNode[] columnNodes = (System.Xml.XmlNode[])sqlRes.@return[i];
Dictionary <string, string> fieldValues = new Dictionary <string, string>();
for (int j = 0; j < columnNodes.Length; j++)
{
if (columnNodes[j].FirstChild != null)
{
fieldValues[columnNodes[j].Name] = columnNodes[j].FirstChild.Value;
}
else
{
fieldValues[columnNodes[j].Name] = null;
}
}
ReturnDataSet.Add(fieldValues);
//string tmpVal = rootNode.Attributes["devicecount"].Value;
}
return(false);
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
//Console.WriteLine(ex.Message);
return(true);
}
}
public override SpeedDial[] GetSpeedDials(string extension)
{
log.Debug("GetSpeedDial from " + extension);
List <SpeedDial> speeddials = new List <SpeedDial>();
string sql = "select label, speeddialindex, speeddialnumber from speeddial where fkdevice in (select pkid from device d where pkid in (select fkdevice from devicenumplanmap where fknumplan in (select pkid from numplan where dnorpattern = '" + extension + "'";
if (defaultContext != "")
{
sql += " and fkroutepartition in (select pkid from routepartition where name = '" + defaultContext + "')";
}
sql += ")))";
ExecuteSQLQueryReq esqr = new ExecuteSQLQueryReq();
esqr.sql = sql;
ExecuteSQLQueryRes esqres = _aas.executeSQLQuery(esqr);
if (esqres.@return != null)
{
if ([email protected] > 0)
{
foreach (XmlNode[] nodes in esqres.@return)
{
SpeedDial sd = new SpeedDial();
sd.displayName = nodes[0].InnerText;
sd.directoryNumber = nodes[2].InnerText;
sd.position = Convert.ToInt32(nodes[1].InnerText);
speeddials.Add(sd);
}
}
else
{
log.Debug("No speeddial available for " + extension);
}
}
return(speeddials.ToArray());
}
static async Task Main(string[] args)
{
Console.WriteLine("\nStarting up...\n");
// Load environment variables from .env
DotNetEnv.Env.Load("../../.env");
// Change to true to enable output of request/response headers and XML
var DEBUG = System.Environment.GetEnvironmentVariable("DEBUG");
// Create a custom binding so we can allow the client to use cookies with AXL
BasicHttpsBinding binding = new BasicHttpsBinding();
binding.AllowCookies = true;
// Specify the CUCM AXL API location for the SOAP client
EndpointAddress address = new EndpointAddress($"https://{ System.Environment.GetEnvironmentVariable( "CUCM_ADDRESS" ) }:8443/axl/");
//Class generated from AXL WSDL
AXLPortClient client = new AXLPortClient(binding, address);
if (DEBUG == "True")
{
client.Endpoint.EndpointBehaviors.Add(new DebugEndpointBehaviour());
}
// To disable HTTPS certificate checking, uncomment the below lines
// NOT for production use! See README.md for AXL certificate install steps
// client.ChannelFactory.Credentials.ServiceCertificate.SslCertificateAuthentication = new X509ServiceCertificateAuthentication
// {
// CertificateValidationMode = X509CertificateValidationMode.None,
// RevocationMode = X509RevocationMode.NoCheck
// };
// client.ChannelFactory.Credentials.ServiceCertificate.Authentication.CertificateValidationMode = X509CertificateValidationMode.None;
// client.ClientCredentials.ServiceCertificate.Authentication.CertificateValidationMode = X509CertificateValidationMode.None;
// Incantation to force alternate serializer reflection behaviour due to complexities in the AXL schema
// See https://github.com/dotnet/wcf/issues/2219
MethodInfo method = typeof(XmlSerializer).GetMethod("set_Mode", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static);
method.Invoke(null, new object[] { 1 });
// Base64 encode AXL username/password for Basic Auth
var encodedUserPass = Convert.ToBase64String(Encoding.ASCII.GetBytes(
System.Environment.GetEnvironmentVariable("CUCM_USERNAME") + ":" +
System.Environment.GetEnvironmentVariable("CUCM_PASSWORD")
));
// Incantation to create and populate a Basic Auth HTTP header
// This must be done to force SoapCore to include the Authorization header on the first attempt
HttpRequestMessageProperty requestProperty = new HttpRequestMessageProperty();
requestProperty.Headers["Authorization"] = "Basic " + encodedUserPass;
// Creating a context scope allows attaching custom HTTP headers to the request
var scope = new OperationContextScope(client.InnerChannel);
OperationContext.Current.OutgoingMessageProperties[HttpRequestMessageProperty.Name] = requestProperty;
// Create the request object
ExecuteSQLQueryReq request = new ExecuteSQLQueryReq();
// Specify SQL statement we want to execute
request.sql = "select name, pkid from applicationuser";
object[] rows;
executeSQLQueryResponse response = new executeSQLQueryResponse();
//Try the getPhone request
try
{
executeSQLQueryResponse response = await client.executeSQLQueryAsync(request);
}
catch (Exception ex)
{
Console.WriteLine($"\nError: getPhone: { ex.Message }");
Environment.Exit(-1);
}
Console.WriteLine($"\nexecuteSqlQuery: SUCCESS\n");
//Parse/print the phone's model name to the console
// Get the rows object array from the response
rows = response.executeSQLQueryResponse1.@return;
// Loop through each row, which consists of a XmlNode array
foreach (XmlNode[] row in rows)
{
// From the first/second XmlNode in each row, parse the Value field from the FirstChild
Console.WriteLine(
"Name: " + row[0].FirstChild.Value.PadRight(20) +
"PKID: " + row[1].FirstChild.Value
);
}
}
private string GetPhoneFromLine(string line)
{
string phone = "";
ExecuteSQLQueryReq sql = new ExecuteSQLQueryReq();
sql.sql = "select name from device d inner join devicenumplanmap dnpm on dnpm.fkdevice = d.pkid inner join numplan np on np.pkid = dnpm.fknumplan where dnorpattern = '"+line+"' and fkroutepartition in (select pkid from routepartition where name = '"+_sharedLinePartition+"')";
if (_axl != null)
{
ExecuteSQLQueryRes res = _axl.executeSQLQuery(sql);
if (res != null)
{
if ([email protected] > 0)
{
if ([email protected] > 1)
{
string[] device = new string[[email protected]];
int compteur = 0;
foreach (object o in res.@return)
{
XmlNode[] element = (XmlNode[])o;
device[compteur] = element[0].InnerText;
compteur++;
}
throw new SharedLineException(line);
}
else
{
XmlNode[] element = (XmlNode[])res.@return[0];
phone = element[0].InnerText;
}
return phone;
}
else
{
log.Error("Line " + line + " doesn't exist.");
throw new Exception("No phone device founded from this line");
}
}
else
{
throw new Exception("No result retreived.");
}
}
else
{
throw new System.Exception("Impossible d'éxécuter l'ordre sans client AXL");
}
}
