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 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 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 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);
}
}
}
}
private static bool CheckMigrationHistoryTable()
{
bool tableExists = false;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
string query = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{_migrationTableName}';";
string value = sqLiteDatabase.ExecuteScalar(query);
tableExists = !string.IsNullOrWhiteSpace(value);
sqLiteDatabase.CloseConnection();
}
return(tableExists);
}
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 getTeamsHubFeatures()
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var dt = sqLiteDatabase.GetDataTable("SELECT * FROM TeamsHubFeatures ORDER BY DisplayOrder");
JSONString = JsonConvert.SerializeObject(dt);
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
public string getSiteRequestById(string id)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var dt = sqLiteDatabase.GetDataTable($"SELECT * FROM SiteProvision WHERE Id='{id}'");
JSONString = JsonConvert.SerializeObject(dt);
sqLiteDatabase.CloseConnection();
}
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 getSiteProvisionConfig()
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var dt = sqLiteDatabase.GetDataTable("SELECT Key,IsSiteProvisionEnabled,ProvisionType FROM AdminSettings");
JSONString = JsonConvert.SerializeObject(dt);
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
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);
}
}
public string getTheme(string emailId)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var dt = sqLiteDatabase.GetDataTable($"SELECT Theme,UserId FROM Theme WHERE UserId='{emailId}'");
JSONString = JsonConvert.SerializeObject(dt);
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
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 getTeamsInfo(string emailId)
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
DataTable dt1 = new DataTable();
var p1 = dt1.Columns.Add("GroupId", typeof(string));
dt1.Columns.Add("Tags", typeof(string));
dt1.Columns.Add("isFavourite", typeof(bool));
dt1.Columns.Add("", typeof(bool));
dt1.PrimaryKey = new DataColumn[] { p1 };
dt1 = sqLiteDatabase.GetDataTable($"SELECT GroupId,Tags,isFavourite FROM TeamsMetaData Where UserId='{emailId}'");
DataTable dt2 = new DataTable();
var p2 = dt2.Columns.Add("GroupId", typeof(string));
dt2.Columns.Add("IsArchived", typeof(bool));
dt2.Columns.Add("IsDeleted", typeof(bool));
dt2.PrimaryKey = new DataColumn[] { p2 };
dt2 = sqLiteDatabase.GetDataTable($"SELECT Id as GroupId,IsArchived,isDeleted FROM TeamsActivity Where IsArchived='True' OR isDeleted='True' ");
var dt3 = dt1.Copy();
dt3.Merge(dt2);
JSONString = JsonConvert.SerializeObject(dt3);
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
private static IEnumerable <KeyValuePair <int, string> > GetPendingMigrationsToApply()
{
int maxVersion = -1;
try
{
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
string createquery = $"SELECT MAX(Version) FROM {_migrationTableName}; ";
string maxVersionInDb = sqLiteDatabase.ExecuteScalar(createquery);
maxVersion = string.IsNullOrWhiteSpace(maxVersionInDb) ? -1 : int.Parse(maxVersionInDb);
sqLiteDatabase.CloseConnection();
}
return(Migrations.migrationData.Where(x => x.Key > maxVersion).OrderBy(x => x.Key));
}
catch (Exception)
{
return(null);
}
}
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 }));
}
}
public string getAllTeamsActivity()
{
try
{
string JSONString = string.Empty;
using (var sqLiteDatabase = new SqLiteDatabase())
{
sqLiteDatabase.OpenConnection();
var result = sqLiteDatabase.GetDataTable($"SELECT * FROM TeamsActivity");
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(string));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Description", typeof(string));
dt.Columns.Add("Type", typeof(string));
dt.Columns.Add("Mail", typeof(string));
dt.Columns.Add("CreatedDate", typeof(string));
dt.Columns.Add("Classification", typeof(string));
dt.Columns.Add("MailEnabled", typeof(string));
dt.Columns.Add("SecurityEnabled", typeof(string));
dt.Columns.Add("Channelcount", typeof(int));
dt.Columns.Add("ChannelName", typeof(string));
dt.Columns.Add("Owners", typeof(string));
dt.Columns.Add("Members", typeof(string));
dt.Columns.Add("OwnersCount", typeof(int));
dt.Columns.Add("MembersCount", typeof(int));
dt.Columns.Add("Tags", typeof(string));
dt.Columns.Add("isArchived", typeof(string));
dt.Columns.Add("LastActivityDate", typeof(string));
dt.Columns.Add("GroupOwner", typeof(string));
dt.Columns.Add("ExternalMemberCount", typeof(int));
dt.Columns.Add("IsDeleted", typeof(string));
dt.Columns.Add("MailBoxEmailCount", typeof(long));
dt.Columns.Add("TotalFileCount", typeof(int));
dt.Columns.Add("TotalStorage", typeof(long));
dt.Columns.Add("NumberChats", typeof(long));
dt.Columns.Add("NumberConversations", typeof(long));
dt.Columns.Add("IsObselete", typeof(string));
dt.Columns.Add("Popular", typeof(long));
dt.Columns.Add("Status", typeof(string));
for (int rowIndex = 0; rowIndex < result.Rows.Count; rowIndex++)
{
var reader = result.Rows[rowIndex];
DateTime createdDate = DateTime.Parse(reader["CreatedDate"].ToString());
string status = "Active";
if (reader["isArchived"].ToString().ToLower() == "true")
{
status = "Archived";
}
else if (reader["IsDeleted"].ToString().ToLower() == "true")
{
status = "Deleted";
}
dt.Rows.Add(reader["Id"].ToString(),
reader["TeamsName"].ToString(),
reader["Description"].ToString(),
reader["TeamType"].ToString(),
reader["Mail"].ToString(),
createdDate.ToString("yyyy'-'MM'-'dd"),
reader["Classification"].ToString(),
reader["MailEnabled"].ToString(),
reader["SecurityEnabled"].ToString(),
Convert.ToInt32(reader["Channelcount"]),
reader["ChannelName"].ToString(),
reader["Owners"].ToString(),
reader["Members"].ToString(),
Convert.ToInt32(reader["OwnersCount"]),
Convert.ToInt32(reader["MembersCount"]),
reader["Tags"].ToString(),
reader["isArchived"].ToString(),
reader["LastActivityDate"].ToString(),
reader["GroupOwner"].ToString(),
Convert.ToInt32(reader["ExternalMemberCount"]),
reader["IsDeleted"].ToString(),
Convert.ToInt64(reader["MailBoxEmailCount"]),
Convert.ToInt32(reader["TotalFileCount"]),
Convert.ToInt64(reader["TotalStorage"]),
Convert.ToInt64(reader["NumberChats"]),
Convert.ToInt64(reader["NumberConversations"]),
reader["IsObselete"].ToString(),
Convert.ToInt64(reader["NumberConversations"]) + Convert.ToInt64(reader["NumberChats"]),
status);
}
JSONString = JsonConvert.SerializeObject(dt);
sqLiteDatabase.CloseConnection();
}
return(JSONString);
}
catch (Exception ex)
{
Console.WriteLine("Exception in Datacontroller : " + ex.Message);
return(null);
}
}
