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); } }