public ActionResult CreateCustomListData(CustomListData customListData)
{
var acResponse = new ActivityResponse();
try
{
if (string.IsNullOrEmpty(customListData.ListDataName))
{
acResponse.Code = -1;
acResponse.Message = "Please provide custom List Data Name";
return(Json(acResponse, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrEmpty(customListData.CustomListDataId))
{
acResponse.Code = -1;
acResponse.Message = "Please select custom List";
return(Json(acResponse, JsonRequestBehavior.AllowGet));
}
customListData.CustomListDataId = Guid.NewGuid().ToString();
_customListDataService.Insert(customListData);
_unitOfWork.SaveChanges();
acResponse.Code = 5;
acResponse.Message = "Custom List Data was successfully Created";
return(Json(acResponse, JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
acResponse.Code = -1;
acResponse.Message = "An unknown error was encountered. Please try again.";
return(Json(acResponse, JsonRequestBehavior.AllowGet));
}
}
protected override CustomListObjectElementCollection GetItemsOverride(CustomListData data)
{
if (data.Parameter.Split(';').Length != 2)
{
throw new InvalidOperationException("Invalid data");
}
var bridgeIP = data.Parameter.Split(';')[0];
var userName = data.Parameter.Split(';')[1];
List <HueLight> mylights = GetLights(bridgeIP, userName);
var items = new CustomListObjectElementCollection();
foreach (var light in mylights)
{
items.Add(new CustomListObjectElement {
{ "Id", light.Id }, { "Name", light.Name }, { "Type", light.Type },
{ "ProductName", light.ProductName }, { "SwitchedOn", light.SwitchedOn }, { "Brightness", light.Brightness },
});
}
//this.Log?.Info(string.Format("Hue extension fetched {0} rows.", items.Count));
return(items);
}
protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
CustomListColumnCollection cols = new CustomListColumnCollection();
DataTable sqlresult = GetSQLTable(data);
foreach (DataColumn sqlcol in sqlresult.Columns)
{
CustomListColumn newcol = new CustomListColumn(sqlcol.ColumnName);
// We convert the SQdL type to one of the three Peakboard types (string, number or boolean)
if (sqlcol.DataType.ToString().Equals("System.Int32"))
{
newcol.Type = CustomListColumnTypes.Number;
}
else if (sqlcol.DataType.ToString().Equals("System.Boolean"))
{
newcol.Type = CustomListColumnTypes.Boolean;
}
else
{
newcol.Type = CustomListColumnTypes.String;
}
cols.Add(newcol);
}
return(cols);
}
protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
var columnCollection = new CustomListColumnCollection();
var chooseEntityOrView = data.Parameter.Split(';')[7];
if (chooseEntityOrView == "View")
{
var link = data.Parameter.Split(';')[0];
var username = data.Parameter.Split(';')[1];
var password = data.Parameter.Split(';')[2];
var logicalNameView = data.Parameter.Split(';')[4];
columnCollection = CrmHelper.GetViewColumns(link, username, password, logicalNameView);
}
else if (chooseEntityOrView == "Entity")
{
var displayNameColumn = data.Parameter.Split(';')[5];
string[] newDisplayNameColumn = displayNameColumn.Replace(" ", String.Empty).Split(',');
foreach (var c in newDisplayNameColumn)
{
columnCollection.Add(new CustomListColumn(c, CustomListColumnTypes.String));
}
}
return(columnCollection);
}
protected override CustomListObjectElementCollection GetItemsOverride(CustomListData data)
{
var items = new CustomListObjectElementCollection();
var fh = new FritzHelper();
data.Properties.TryGetValue("Hostname", StringComparison.OrdinalIgnoreCase, out var Hostname);
data.Properties.TryGetValue("User", StringComparison.OrdinalIgnoreCase, out var User);
data.Properties.TryGetValue("Password", StringComparison.OrdinalIgnoreCase, out var Password);
var thermostats = fh.GetThermostats(Hostname, User, Password);
foreach (var thermostat in thermostats)
{
items.Add(new CustomListObjectElement {
{ "Id", thermostat.Id },
{ "Name", thermostat.Name },
{ "Present", thermostat.Present },
{ "Battery", thermostat.Battery },
{ "TempCurrent", thermostat.TempCurrent },
{ "TempTarget", thermostat.TempTarget }
});
}
return(items);
}
protected override CustomListObjectElementCollection GetItemsOverride(CustomListData data)
{
CustomListObjectElementCollection itemsCollection = new CustomListObjectElementCollection();
var chooseEntityOrView = data.Parameter.Split(';')[7];
var link = data.Parameter.Split(';')[0];
var username = data.Parameter.Split(';')[1];
var password = data.Parameter.Split(';')[2];
var maxRows = data.Parameter.Split(';')[3];
var logicalNameViewOrTable = data.Parameter.Split(';')[4];
if (chooseEntityOrView == "View")
{
itemsCollection = CrmHelper.GetDataFromView(link, username, password, maxRows, logicalNameViewOrTable);
}
else if (chooseEntityOrView == "Entity")
{
var displayNameColumn = data.Parameter.Split(';')[5];
var logicalNameColumn = data.Parameter.Split(';')[6];
itemsCollection = CrmHelper.GetDataFromEntity(link, username, password, maxRows, logicalNameViewOrTable, displayNameColumn, logicalNameColumn);
}
return(itemsCollection);
}
protected override CustomListObjectElementCollection GetItemsOverride(CustomListData data)
{
CheckProperties(data, out string Subdomain, out string Email, out string Password);
List <YaroomsMeeting> mymeetings = YaroomsHelper.GetAllMeetings(Subdomain, Email, Password);
var items = new CustomListObjectElementCollection();
foreach (var meeting in mymeetings)
{
items.Add(new CustomListObjectElement {
{ "Id", meeting.id },
{ "Date", meeting.date },
{ "StartTime", meeting.start },
{ "EndTime", meeting.end },
{ "Location", meeting.location },
{ "Room", meeting.room },
{ "Name", meeting.name },
{ "Description", meeting.description },
});
}
this.Log?.Info(string.Format("Ingres extension fetched {0} rows.", items.Count));
return(items);
}
private Int32 CheckAndGetMaxLengthProperty(CustomListData data)
{
if (!data.Properties.TryGetValue("MaxLength", StringComparison.OrdinalIgnoreCase, out var MaxLength))
{
throw new InvalidOperationException("The property MaxLength is not defined");
}
int length;
try
{
length = Convert.ToInt32(MaxLength);
}
catch (Exception e)
{
throw new InvalidOperationException("Max Length data type mismatch", e);
}
if (length < 20)
{
throw new InvalidOperationException("Max Length property is not long enough");
}
return(length);
}
protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
return(new CustomListColumnCollection
{
new CustomListColumn("Fact", CustomListColumnTypes.String),
new CustomListColumn("Length", CustomListColumnTypes.Number),
});
}
protected override void CheckDataOverride(CustomListData data)
{
data.Properties.TryGetValue("Password", StringComparison.OrdinalIgnoreCase, out var Password);
if (string.IsNullOrWhiteSpace(Password))
{
throw new InvalidOperationException("Please provide a password");
}
}
private void CheckProperties(CustomListData data, out string Subdomain, out string Email, out string Password)
{
data.Properties.TryGetValue("Subdomain", StringComparison.OrdinalIgnoreCase, out Subdomain);
data.Properties.TryGetValue("Email", StringComparison.OrdinalIgnoreCase, out Email);
data.Properties.TryGetValue("Password", StringComparison.OrdinalIgnoreCase, out Password);
if (string.IsNullOrWhiteSpace(Subdomain) || string.IsNullOrWhiteSpace(Email) || string.IsNullOrWhiteSpace(Password))
{
throw new InvalidOperationException("Invalid properties. Please check carefully!");
}
}
protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
return(new CustomListColumnCollection
{
new CustomListColumn("Timestamp", CustomListColumnTypes.String),
new CustomListColumn("Channel1", CustomListColumnTypes.String),
new CustomListColumn("Channel2", CustomListColumnTypes.String),
new CustomListColumn("Channel3", CustomListColumnTypes.String),
new CustomListColumn("Channel4", CustomListColumnTypes.String),
});
}
private DataTable GetSQLTable(CustomListData data, string commandText)
{
SqlConnection con = GetConnection(data);
SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(commandText, con));
DataTable sqlresult = new DataTable();
da.Fill(sqlresult);
con.Close();
da.Dispose();
return(sqlresult);
}
private void CheckProperties(CustomListData data)
{
data.Properties.TryGetValue("Host", StringComparison.OrdinalIgnoreCase, out var DBServer);
data.Properties.TryGetValue("Database", StringComparison.OrdinalIgnoreCase, out var DBName);
data.Properties.TryGetValue("Username", StringComparison.OrdinalIgnoreCase, out var Username);
data.Properties.TryGetValue("Password", StringComparison.OrdinalIgnoreCase, out var Password);
if (string.IsNullOrWhiteSpace(DBServer) || string.IsNullOrWhiteSpace(DBName) || string.IsNullOrWhiteSpace(Username) || string.IsNullOrWhiteSpace(Password))
{
throw new InvalidOperationException("Invalid properties. Please check carefully!");
}
}
private SqlConnection GetConnection(CustomListData data)
{
data.Properties.TryGetValue("DBServer", StringComparison.OrdinalIgnoreCase, out var DBServer);
data.Properties.TryGetValue("DBName", StringComparison.OrdinalIgnoreCase, out var DBName);
data.Properties.TryGetValue("Username", StringComparison.OrdinalIgnoreCase, out var Username);
data.Properties.TryGetValue("Password", StringComparison.OrdinalIgnoreCase, out var Password);
SqlConnection con = new SqlConnection(string.Format("Server={0};Database={1};User Id={2};Password={3};", DBServer, DBName, Username, Password));
con.Open();
return(con);
}
private IngresConnection GetConnection(CustomListData data)
{
data.Properties.TryGetValue("Host", StringComparison.OrdinalIgnoreCase, out var Host);
data.Properties.TryGetValue("Database", StringComparison.OrdinalIgnoreCase, out var Database);
data.Properties.TryGetValue("Username", StringComparison.OrdinalIgnoreCase, out var Username);
data.Properties.TryGetValue("Password", StringComparison.OrdinalIgnoreCase, out var Password);
IngresConnection con = new IngresConnection(string.Format("Host={0};Database={1};Uid={2};Pwd={3}", Host, Database, Username, Password));
con.Open();
return(con);
}
protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
// create a static collection of columns
return(new CustomListColumnCollection
{
new CustomListColumn("Id", CustomListColumnTypes.String),
new CustomListColumn("Name", CustomListColumnTypes.String),
new CustomListColumn("Present", CustomListColumnTypes.Boolean),
new CustomListColumn("Battery", CustomListColumnTypes.Number),
new CustomListColumn("TempCurrent", CustomListColumnTypes.Number),
new CustomListColumn("TempTarget", CustomListColumnTypes.Number),
});
}
protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
// create a static collection of columns
return(new CustomListColumnCollection
{
new CustomListColumn("Id", CustomListColumnTypes.String),
new CustomListColumn("Name", CustomListColumnTypes.String),
new CustomListColumn("Type", CustomListColumnTypes.String),
new CustomListColumn("ProductName", CustomListColumnTypes.String),
new CustomListColumn("SwitchedOn", CustomListColumnTypes.Boolean),
new CustomListColumn("Brightness", CustomListColumnTypes.Number),
});
}
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 CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
// create a static collection of columns
return(new CustomListColumnCollection
{
new CustomListColumn("AirportCode", CustomListColumnTypes.String),
new CustomListColumn("ObservationTime", CustomListColumnTypes.String),
new CustomListColumn("Temperature", CustomListColumnTypes.Number),
new CustomListColumn("WindDirection", CustomListColumnTypes.Number),
new CustomListColumn("WindSpeed", CustomListColumnTypes.Number),
new CustomListColumn("AirPressure", CustomListColumnTypes.Number),
});
}
private DataTable GetSQLTable(CustomListData data)
{
IngresConnection con = GetConnection(data);
data.Properties.TryGetValue("SQLStatement", StringComparison.OrdinalIgnoreCase, out var SQLStatement);
IngresDataAdapter da = new IngresDataAdapter(new IngresCommand(SQLStatement, con));
DataTable sqlresult = new DataTable();
da.Fill(sqlresult);
con.Close();
da.Dispose();
return(sqlresult);
}
private DB2Connection GetConnection(CustomListData data)
{
data.Properties.TryGetValue("Host", StringComparison.OrdinalIgnoreCase, out var Host);
data.Properties.TryGetValue("Database", StringComparison.OrdinalIgnoreCase, out var Database);
data.Properties.TryGetValue("Username", StringComparison.OrdinalIgnoreCase, out var Username);
data.Properties.TryGetValue("Password", StringComparison.OrdinalIgnoreCase, out var Password);
string Db2ConnectionString = string.Format("server={0};database={1};uid={2};pwd={3};", Host, Database, Username, Password);
DB2Connection Db2Connection = new DB2Connection(Db2ConnectionString);
Db2Connection.Open();
return(Db2Connection);
}
protected override CustomListObjectElementCollection GetItemsOverride(CustomListData data)
{
Int32 maxLength = CheckAndGetMaxLengthProperty(data);
CatFact myfact = GetRandomCatfact(maxLength).Result;
var items = new CustomListObjectElementCollection();
items.Add(new CustomListObjectElement {
{ "Fact", myfact.fact }, { "Length", myfact.length },
});
return(items);
}
protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
return(new CustomListColumnCollection
{
new CustomListColumn("Id", CustomListColumnTypes.String),
new CustomListColumn("Date", CustomListColumnTypes.String),
new CustomListColumn("StartTime", CustomListColumnTypes.String),
new CustomListColumn("EndTime", CustomListColumnTypes.String),
new CustomListColumn("Location", CustomListColumnTypes.String),
new CustomListColumn("Room", CustomListColumnTypes.String),
new CustomListColumn("Name", CustomListColumnTypes.String),
new CustomListColumn("Description", CustomListColumnTypes.String),
});
}
// GET: CustomListData/EditCustomListData/5
public ActionResult GetCustomListData(int?id)
{
if (id == null)
{
return(Json(new CustomListData(), JsonRequestBehavior.AllowGet));
}
CustomListData customListData = _customListDataService.Find(id);
if (customListData == null)
{
return(Json(new CustomListData(), JsonRequestBehavior.AllowGet));
}
return(Json(customListData, JsonRequestBehavior.AllowGet));
}
static void Test2()
{
var extension = new MySqlExtension(null);
var customList = extension.GetCustomLists()?.Value[0];
var data = new CustomListData();
data.Properties.Add("Host", Host);
data.Properties.Add("Database", Database);
data.Properties.Add("Username", UserID);
data.Properties.Add("Password", Password);
data.Properties.Add("SQLStatement", "select * from `sys`.`material`");
var columns = customList.GetColumns(data)?.Value;
var items = customList.GetItems(data)?.Value;
}
protected override void CheckDataOverride(CustomListData data)
{
// doing several checks with the Parameter that contains all out properties as JSon string
if (string.IsNullOrWhiteSpace(data.Parameter))
{
throw new InvalidOperationException("Please use the map editor to select an airport");
}
Airport myAirport = JsonConvert.DeserializeObject <Airport>(data.Parameter);
if (string.IsNullOrWhiteSpace(myAirport.AirportCode) || myAirport.AirportCode.Length != 4)
{
throw new InvalidOperationException("There's something with wrong with the airport code");
}
}
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 CustomListExecuteReturnContext ExecuteFunctionOverride(CustomListData data, CustomListExecuteParameterContext context)
{
var bridgeIP = data.Parameter.Split(';')[0];
var userName = data.Parameter.Split(';')[1];
this.Log?.Info(string.Format("The function {0} has been called with {1} parameters", context.FunctionName, context.Values.Count));
var returnContext = default(CustomListExecuteReturnContext);
if (context.FunctionName.Equals("switchlighton", StringComparison.InvariantCultureIgnoreCase))
{
string lightname = context.Values[0].StringValue;
this.Log?.Info(string.Format("Lightname: {0} -> switchlighton", lightname));
HueHelper.SwitchLight(bridgeIP, userName, lightname, true);
}
else if (context.FunctionName.Equals("switchlightoff", StringComparison.InvariantCultureIgnoreCase))
{
string lightname = context.Values[0].StringValue;
this.Log?.Info(string.Format("Lightname: {0} -> switchlightoff", lightname));
HueHelper.SwitchLight(bridgeIP, userName, lightname, false);
}
else if (context.FunctionName.Equals("setlightbrightness", StringComparison.InvariantCultureIgnoreCase))
{
string lightname = context.Values[0].StringValue;
int brightness = Convert.ToInt32(context.Values[1].GetValue());
this.Log?.Info(string.Format("Lightname: {0} -> setlightbrightness -> {1}", lightname, brightness));
HueHelper.SetLightBrightness(bridgeIP, userName, lightname, brightness);
}
else if (context.FunctionName.Equals("setlightcolor", StringComparison.InvariantCultureIgnoreCase))
{
string lightname = context.Values[0].StringValue;
int color = Convert.ToInt32(context.Values[1].GetValue());
this.Log?.Info(string.Format("Lightname: {0} -> setlightcolor -> {1}", lightname, color));
HueHelper.SetLightColor(bridgeIP, userName, lightname, color);
}
else if (context.FunctionName.Equals("alert", StringComparison.InvariantCultureIgnoreCase))
{
string lightname = context.Values[0].StringValue;
this.Log?.Info(string.Format("Lightname: {0} -> alert", lightname));
HueHelper.Alert(bridgeIP, userName, lightname);
}
else
{
throw new DataErrorException("Function is not supported in this version.");
}
return(returnContext);
}
protected override void CheckDataOverride(CustomListData data)
{
var link = data.Parameter.Split(';')[0];
var username = data.Parameter.Split(';')[1];
var password = data.Parameter.Split(';')[2];
var maxRows = data.Parameter.Split(';')[3];
var logicalNameViewOrTable = data.Parameter.Split(';')[4];
var displayNameColumn = data.Parameter.Split(';')[5];
var logicalNameColumn = data.Parameter.Split(';')[6];
var chooseEntityOrView = data.Parameter.Split(';')[7];
if (string.IsNullOrWhiteSpace(link))
{
throw new InvalidOperationException("Please provide a link");
}
if (string.IsNullOrWhiteSpace(username))
{
throw new InvalidOperationException("Please provide a username");
}
if (string.IsNullOrWhiteSpace(password))
{
throw new InvalidOperationException("Please provide a password");
}
if (string.IsNullOrWhiteSpace(maxRows))
{
throw new InvalidOperationException("Please provide a number of rows");
}
if (string.IsNullOrWhiteSpace(logicalNameViewOrTable))
{
throw new InvalidOperationException("Please provide a View or an Entity");
}
if (chooseEntityOrView == "Entity")
{
if (string.IsNullOrWhiteSpace(logicalNameColumn))
{
throw new InvalidOperationException("Please select some columns");
}
}
if (!int.TryParse(maxRows, out int i))
{
throw new InvalidOperationException("Invalid max rows property. Please check carefully!");
}
}
