public string setSiteProvisionConfig(string key, string IsSiteProvisionEnabled, string ProvisionType)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var result = sqLiteDatabase.GetDataTable($"SELECT * FROM AdminSettings Where Key='{key}'");
string query = string.Empty;
if (result.Rows.Count > 0)
{
query = $"UPDATE AdminSettings SET IsSiteProvisionEnabled='{IsSiteProvisionEnabled}', ProvisionType='{ProvisionType}' WHERE Key='{key}'";
}
else
{
query = $"INSERT INTO AdminSettings ('Key','IsSiteProvisionEnabled','ProvisionType') VALUES('{key}','{IsSiteProvisionEnabled}','{ProvisionType}')";
}
sqLiteDatabase.ExecuteNonQuery(query);
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
/// <summary>
/// getIRMSettingsTeamsLevel
/// </summary>
/// <param name="siteUrl"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <returns></returns>
/// <summary>
/// setTheme
/// </summary>
/// <param name="emailId"></param>
/// <param name="theme"></param>
/// <returns></returns>
public string setTheme(string emailId, string theme)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var result = sqLiteDatabase.GetDataTable($"SELECT * FROM Theme Where UserId='{emailId}'");
string query = string.Empty;
if (result.Rows.Count > 0)
{
query = $"UPDATE THeme SET Theme='{theme}' WHERE UserId='{emailId}'";
}
else
{
query = $"INSERT INTO Theme ('UserId','Theme') VALUES('{emailId}','{theme}')";
}
sqLiteDatabase.ExecuteNonQuery(query);
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
public string addFavourites(string groupId, string emailId)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var result = sqLiteDatabase.GetDataTable($"SELECT * FROM TeamsMetaData Where UserId='{emailId}' AND groupId ='{groupId}'");
string query = string.Empty;
if (result.Rows.Count > 0)
{
query = $"UPDATE TeamsMetaData SET isFavourite=1 WHERE UserId='{emailId}' AND GroupId='{groupId}'";
}
else
{
query = $"INSERT INTO TeamsMetaData ('UserId','GroupId','isFavourite') VALUES('{emailId}','{groupId}', 1)";
}
sqLiteDatabase.ExecuteNonQuery(query);
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
public string addTags(string tags)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var result = sqLiteDatabase.GetDataTable($"SELECT * FROM Tags Where LOWER(Tags)='{tags.ToLower()}'");
if (result.Rows.Count > 0)
{
JSONString = "ALREADY PRESENT";
}
else
{
sqLiteDatabase.ExecuteNonQuery($"INSERT INTO Tags ('Tags') VALUES('{tags}')");
sqLiteDatabase.CloseConnection();
JSONString = "ADDED";
}
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
public async Task validateInputAsync(IDialogContext context, IAwaitable <IMessageActivity> result)
{
var message = await result;
TeamRequest team = new TeamRequest();
if (message.Value != null)
{
// Got an Action Submit
dynamic formvalue = message.Value;
if (formvalue != null)
{
team.Name = formvalue["Teamname"];
team.Description = formvalue["Description"];
team.Alias = formvalue["TeamMailNickname"];
team.Owners = formvalue["TeamOwners"];
team.SiteType = formvalue["Type"];
team.Classification = formvalue["Classification"];
var error = GetErrorMessage(team); // Validation
IMessageActivity replyMessage = context.MakeMessage();
if (!string.IsNullOrEmpty(error))
{
replyMessage.Text = error;
await context.PostAsync(replyMessage);
}
else
{
//Insert data into database here.
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var result1 = sqLiteDatabase.GetDataTable($"SELECT * FROM SiteProvision");
string query = string.Empty;
if (result1.Rows.Count > 0)
{
query = $"UPDATE SiteProvision SET ";
}
else
{
//query = $"INSERT INTO SiteProvision ('Name','Description', 'Alias', 'SiteType', 'Language','RequestedBy','Owners','Status') VALUES('{name}','{description}','{alias}','{siteType}','{language}','{requestedBy}','{owners}','Requested')";
}
sqLiteDatabase.ExecuteNonQuery(query);
sqLiteDatabase.CloseConnection();
}
// Save Information in service bus
replyMessage.Text = "We have submiited your site request";
await context.PostAsync(replyMessage);
context.Done(true);
}
}
}
}
public string setTeamsHubFeatures(string features)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
JArray array = JArray.Parse(features);
foreach (JObject o in array.Children <JObject>())
{
string feature = "";
string enabled = "";
string subFeature = "";
foreach (JProperty p in o.Properties())
{
if (p.Name == "Feature")
{
feature = (string)p.Value;
}
else if (p.Name == "SubFeature")
{
subFeature = (string)p.Value;
}
else if (p.Name == "Enabled")
{
enabled = (string)p.Value;
}
}
var result = sqLiteDatabase.GetDataTable($"SELECT * FROM TeamsHubFeatures Where Feature='{feature}'");
string query = $"UPDATE TeamsHubFeatures SET Enabled='{enabled}' WHERE Feature='{feature}'";
if (subFeature != "")
{
result = sqLiteDatabase.GetDataTable($"SELECT * FROM TeamsHubFeatures Where Feature='{feature}' AND SubFeature ='{subFeature}'");
query = $"UPDATE TeamsHubFeatures SET Enabled='{enabled}' WHERE Feature='{feature}' AND SubFeature='{subFeature}'";
}
sqLiteDatabase.ExecuteNonQuery(query);
}
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
public static void DropAndCreateTeamsHubFeaturesTable()
{
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
string deleteQuery = $"DROP TABLE TeamsHubFeatures";
string createquery = $"CREATE TABLE TeamsHubFeatures (Feature TEXT, SubFeature TEXT, Description TEXT,Enabled TEXT,DisplayOrder INTEGER,Status TEXT)";
var insertqueryArray = _featureList.Select(t => $"INSERT INTO TeamsHubFeatures (Feature,SubFeature,Description,Enabled,DisplayOrder) VALUES ('{t.Item1}','{t.Item2}','{t.Item3}','{t.Item4}','{t.Item5}' )").ToList();
var queryList = new List <string>();
queryList.Add(deleteQuery);
queryList.Add(createquery);
queryList.AddRange(insertqueryArray);
var query = string.Join(";", queryList);
sqLiteDatabase.ExecuteNonQuery(query);
sqLiteDatabase.CloseConnection();
}
}
private static bool CreateMigrationHistoryTable()
{
try
{
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
string createquery = $"CREATE TABLE {_migrationTableName} (Version TEXT)";
sqLiteDatabase.ExecuteNonQuery(createquery);
sqLiteDatabase.CloseConnection();
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public string deleteTags(string tagId)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
sqLiteDatabase.ExecuteNonQuery($"DELETE FROM Tags Where Id={tagId}");
sqLiteDatabase.CloseConnection();
JSONString = "DELETED";
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
private static bool ApplyMigrations(IEnumerable <KeyValuePair <int, string> > migrations)
{
try
{
using (var sqLiteDatabase = new SqLiteDatabase())
{
var queryList = migrations.Select(t => $"INSERT INTO {_migrationTableName} (Version) VALUES ({t.Key});{t.Value}");
sqLiteDatabase.OpenConnection();
var query = string.Join(";", queryList);
sqLiteDatabase.ExecuteNonQuery(query);
sqLiteDatabase.CloseConnection();
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public string removeFavourites(string groupId, string emailId)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var result = sqLiteDatabase.GetDataTable($"SELECT * FROM TeamsMetaData Where UserId='{emailId}' AND groupId ='{groupId}'");
string query = $"UPDATE TeamsMetaData SET isFavourite=0 WHERE UserId='{emailId}' AND GroupId='{groupId}'";
sqLiteDatabase.ExecuteNonQuery(query);
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
public IHttpActionResult SubmitCloneTeamRequest([FromBody] TeamRequest model)
{
try
{
string JSONString = string.Empty;
model.Name = model.Name.Replace("'", "''");
model.Description = model.Description.Replace("'", "''");
if (!ModelState.IsValid)
{
var errors = ModelState.Values.SelectMany(m => m.Errors)
.Select(e => e.ErrorMessage).ToArray();
return(BadRequest(string.Join("", errors)));
}
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var result = sqLiteDatabase.GetDataTable($"SELECT * FROM SiteProvision Where Name='{model.Name}'");
string query = string.Empty;
if (result.Rows.Count > 0)
{
JSONString = "ALREADY PRESENT";
//query = $"UPDATE SiteProvision SET Description='{model.Description}' WHERE Name='{model.Name}'";
}
else
{
query = $"INSERT INTO SiteProvision ('Name','Description', 'Alias', 'SiteType', 'Language','RequestedBy','Privacy','Classification','GroupId','IsClone','CloneParts','Owners','Members','RequestedDate','Status') VALUES('{model.Name}','{model.Description}','{model.Alias}','{model.SiteType}','{model.Language}','{model.RequestedBy}','{model.Privacy}','{model.Classification}','{model.GroupId}','{model.IsClone}','{model.CloneParts}','{model.Owners}','{model.Members}','{model.RequestedDate}','Requested')";
}
sqLiteDatabase.ExecuteNonQuery(query);
sqLiteDatabase.CloseConnection();
}
return(Ok(new { Message = JSONString }));
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(InternalServerError(ex));
}
}
public string updateTags(string tagId, string tags)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var result = sqLiteDatabase.GetDataTable($"SELECT * FROM Tags Where Id='{tagId}'");
string query = string.Empty;
if (result.Rows.Count > 0)
{
var checkTag = sqLiteDatabase.GetDataTable($"SELECT * FROM Tags Where LOWER(Tags)='{tags.ToLower()}' EXCEPT SELECT * FROM Tags Where Id='{tagId}'");
if (checkTag.Rows.Count > 0)
{
JSONString = "ALREADY PRESENT";
}
else
{
query = $"UPDATE Tags SET Tags='{tags}' WHERE Id='{tagId}'";
}
}
else
{
query = $"INSERT INTO Tags ('Tags') VALUES('{tags}')";
}
sqLiteDatabase.ExecuteNonQuery(query);
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
public IHttpActionResult updateSiteRequest([FromBody] TeamRequest model, [FromUri] string id)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var result = sqLiteDatabase.GetDataTable($"SELECT * FROM SiteProvision Where Id='{id}'");
string query = string.Empty;
if (result.Rows.Count > 0)
{
query = $"UPDATE SiteProvision SET Status='{model.Status}', ApprovedBy='{model.ApprovedBy}', ApprovedDate='{model.ApprovedDate}',Comments='{model.Comments}' WHERE Id='{id}'";
sqLiteDatabase.ExecuteNonQuery(query);
sqLiteDatabase.CloseConnection();
}
}
return(Ok(new { Success = true, Message = "Site request updated sucessfully" }));
}
catch (Exception ex)
{
return(Ok(new { Success = false, Message = ex.Message }));
}
}
