protected override CustomListObjectElementCollection GetItemsOverride(CustomListData data)
{
var items = new CustomListObjectElementCollection();
data.Properties.TryGetValue("LightID", StringComparison.OrdinalIgnoreCase, out var macID);
if (string.IsNullOrWhiteSpace(macID))
{
throw new InvalidOperationException("Invalid LightID property. Please check carefully!");
}
data.Properties.TryGetValue("Max_Rows", StringComparison.OrdinalIgnoreCase, out var maxRows);
if (string.IsNullOrWhiteSpace(maxRows))
{
throw new InvalidOperationException("Invalid Max_Rows property. Please check carefully!");
}
string commandText = $"SELECT TOP {maxRows} CONVERT(varchar, datStart,103) +' '+ CONVERT(varchar,datStart,8) AS Timestamp, Channel1, Channel2, Channel3, Channel4 FROM dbo.slaveData WHERE slaveId=(SELECT id FROM dbo.slaveDevice WHERE MacId='{macID}') ORDER BY id DESC";
DataTable sqlresult = GetSQLTable(data, commandText);
foreach (DataRow sqlrow in sqlresult.Rows)
{
CustomListObjectElement newitem = new CustomListObjectElement();
foreach (DataColumn sqlcol in sqlresult.Columns)
{
string cs = sqlrow[sqlcol.ColumnName].ToString();
if (cs == "0")
{
cs = "Off";
}
else if (cs == "1" || cs == "2" || cs == "3" || cs == "16" || cs == "17" || cs == "18" || cs == "19")
{
cs = "On";
}
else if (cs == "20" || cs == "21" || cs == "22" || cs == "23")
{
cs = "Blinking";
}
newitem.Add(sqlcol.ColumnName, cs);
}
items.Add(newitem);
}
this.Log?.Info(string.Format("SQL Server extension fetched {0} rows.", items.Count));
return(items);
}
protected override CustomListObjectElementCollection GetItemsOverride(CustomListData data)
{
string commandText1 = "SELECT id FROM dbo.slaveDevice ORDER BY macId";
DataTable sqlresult1 = GetSQLTable(data, commandText1);
var items = new CustomListObjectElementCollection();
foreach (DataRow sqlrow1 in sqlresult1.Rows)
{
foreach (DataColumn sqlcol1 in sqlresult1.Columns)
{
string deviceSlaveId = sqlrow1[sqlcol1.ColumnName].ToString();
string commandText2 = $"SELECT MacId as ID, Name, Channel1, Channel2, Channel3, Channel4 FROM dbo.slaveData, dbo.slaveDevice WHERE dbo.slaveData.slaveId = dbo.slaveDevice.id AND dbo.slaveData.id = (SELECT MAX(id) FROM dbo.slaveData WHERE slaveId = {deviceSlaveId})";
DataTable sqlresult2 = GetSQLTable(data, commandText2);
foreach (DataRow sqlrow2 in sqlresult2.Rows)
{
CustomListObjectElement newitem = new CustomListObjectElement();
foreach (DataColumn sqlcol2 in sqlresult2.Columns)
{
string cs = sqlrow2[sqlcol2.ColumnName].ToString();
if (cs == "0")
{
cs = "Off";
}
else if (cs == "1" || cs == "2" || cs == "3" || cs == "16" || cs == "17" || cs == "18" || cs == "19")
{
cs = "On";
}
else if (cs == "20" || cs == "21" || cs == "22" || cs == "23")
{
cs = "Blinking";
}
newitem.Add(sqlcol2.ColumnName, cs);
}
items.Add(newitem);
}
}
}
this.Log?.Info(string.Format("SQL Server extension fetched {0} rows.", items.Count));
return(items);
}
protected override CustomListObjectElementCollection GetItemsOverride(CustomListData data)
{
DataTable sqlresult = GetSQLTable(data);
var items = new CustomListObjectElementCollection();
// We simply transfer the Datatable object to a CustomListObjectCollection
foreach (DataRow sqlrow in sqlresult.Rows)
{
CustomListObjectElement newitem = new CustomListObjectElement();
foreach (DataColumn sqlcol in sqlresult.Columns)
{
newitem.Add(sqlcol.ColumnName, DataTypeHelper.GetOrConvertNumericTypeToDouble(sqlcol.DataType, sqlrow[sqlcol.ColumnName]));
}
items.Add(newitem);
}
this.Log?.Info(string.Format("Ingres extension fetched {0} rows.", items.Count));
return(items);
}
protected override CustomListObjectElementCollection GetItemsOverride(CustomListData data)
{
DataTable db2result = GetDB2Table(data);
var items = new CustomListObjectElementCollection();
// Simply transfer the Datatable object to a CustomListObjectCollection
foreach (DataRow db2row in db2result.Rows)
{
CustomListObjectElement newitem = new CustomListObjectElement();
foreach (DataColumn db2col in db2result.Columns)
{
newitem.Add(db2col.ColumnName, db2row[db2col.ColumnName]);
}
items.Add(newitem);
}
Log?.Info(string.Format("DB2 extension fetched {0} rows.", items.Count));
return(items);
}
public static CustomListObjectElementCollection GetDataFromEntity(string link, string username, string password, string maxRows, string table, string displayName, string logicalName)
{
CustomListObjectElementCollection itemsCollection = new CustomListObjectElementCollection();
IOrganizationService service = TryConnection(link, username, password);
if (service == null)
{
throw new InvalidOperationException("Connection has failed!");
}
else
{
QueryExpression qe = new QueryExpression(table.ToLower());
string[] newDisplayName = displayName.Split(',');
string[] newLogicalName = logicalName.Replace(" ", String.Empty).ToLower().Split(',');
qe.ColumnSet = new ColumnSet(newLogicalName);
EntityCollection ec = new EntityCollection();
try
{
ec = service.RetrieveMultiple(qe);
}
catch
{
throw new InvalidOperationException("Connection has failed!");
}
if (int.Parse(maxRows) > ec.Entities.Count)
{
maxRows = ec.Entities.Count.ToString();
}
for (int i = 0; i < int.Parse(maxRows); i++)
{
int j = 0;
CustomListObjectElement item = new CustomListObjectElement();
foreach (string column in newLogicalName)
{
string newString = "";
if (ec.Entities.Count == 0 || !ec.Entities[i].Attributes.Contains(column))
{
newString = "";
}
else
{
if (ec.Entities[i].Attributes[column] is OptionSetValue)
{
OptionSetValue o = new OptionSetValue();
o = (OptionSetValue)ec.Entities[i].Attributes[column];
newString = o.Value.ToString();
}
else if (ec.Entities[i].Attributes[column] is AliasedValue)
{
AliasedValue o = new AliasedValue();
o = (AliasedValue)ec.Entities[i].Attributes[column];
newString = o.Value.ToString();
}
else if (ec.Entities[i].Attributes[column] is EntityReference)
{
EntityReference o = new EntityReference();
o = (EntityReference)ec.Entities[i].Attributes[column];
newString = o.Name.ToString();
}
else if (ec.Entities[i].Attributes[column] is Money)
{
Money o = new Money();
o = (Money)ec.Entities[i].Attributes[column];
newString = o.Value.ToString();
}
else
{
newString = ec.Entities[i].Attributes[column].ToString();
}
}
item.Add(newDisplayName[j], newString);
j++;
}
itemsCollection.Add(item);
}
}
return(itemsCollection);
}
public static CustomListObjectElementCollection GetDataFromView(string link, string username, string password, string maxRows, string logicalNameView)
{
CustomListObjectElementCollection itemsCollection = new CustomListObjectElementCollection();
IOrganizationService service = TryConnection(link, username, password);
if (service == null)
{
throw new InvalidOperationException("Connection has failed!");
}
else
{
var query = new QueryExpression
{
EntityName = "savedquery",
ColumnSet = new ColumnSet("fetchxml"),
Criteria = new FilterExpression
{
FilterOperator = LogicalOperator.And,
Conditions =
{
new ConditionExpression
{
AttributeName = "name",
Operator = ConditionOperator.Equal,
Values = { logicalNameView }//View Name - "Active Accounts"
},
}
}
};
var result = service.RetrieveMultiple(query);
var fetchToQueryExpressionRequest = new FetchXmlToQueryExpressionRequest();
fetchToQueryExpressionRequest.FetchXml = result.Entities[0].Attributes["fetchxml"].ToString();
var fetchToQueryExpressionResponse = (FetchXmlToQueryExpressionResponse)service.Execute(fetchToQueryExpressionRequest);
QueryExpression qe = fetchToQueryExpressionResponse.Query;
EntityCollection ec = new EntityCollection();
try
{
ec = service.RetrieveMultiple(qe);
}
catch
{
throw new InvalidOperationException("Connection has failed!");
}
if (int.Parse(maxRows) > ec.Entities.Count)
{
maxRows = ec.Entities.Count.ToString();
}
for (int i = 0; i < int.Parse(maxRows); i++)
{
CustomListObjectElement item = new CustomListObjectElement();
foreach (string column in qe.ColumnSet.Columns)
{
string newString = "";
if (ec.Entities.Count == 0 || !ec.Entities[i].Attributes.Keys.Contains(column))
{
newString = "";
}
else
{
if (ec.Entities[i].Attributes[column] is OptionSetValue)
{
OptionSetValue o = new OptionSetValue();
o = (OptionSetValue)ec.Entities[i].Attributes[column];
newString = o.Value.ToString();
}
else if (ec.Entities[i].Attributes[column] is AliasedValue)
{
AliasedValue o = new AliasedValue();
o = (AliasedValue)ec.Entities[i].Attributes[column];
newString = o.Value.ToString();
}
else if (ec.Entities[i].Attributes[column] is EntityReference)
{
EntityReference o = new EntityReference();
o = (EntityReference)ec.Entities[i].Attributes[column];
newString = o.Name.ToString();
}
else if (ec.Entities[i].Attributes[column] is Money)
{
Money o = new Money();
o = (Money)ec.Entities[i].Attributes[column];
newString = o.Value.ToString();
}
else
{
newString = ec.Entities[i].Attributes[column].ToString();
}
}
item.Add(column, newString);
}
itemsCollection.Add(item);
}
}
return(itemsCollection);
}
