public void CountPlus(string cdn) { using (var db = SugarContext.GetInstance()) { var id = db.Queryable <MUSICS>().Where(c => c.CDN == cdn).ToList().FirstOrDefault()?.ID; if (string.IsNullOrEmpty(id)) { throw new Exception("歌名不存在"); } var music = db.Queryable <PLAY_COUNT>().Where(c => c.MUSIC_ID == id).ToList().FirstOrDefault(); if (music is null) { music = new PLAY_COUNT { ID = Guid.NewGuid().ToString("N").ToUpper(), USER_CREATED = "SYS", DATETIME_CREATED = DateTime.Now, STATE = "A", MUSIC_ID = id, QTY = 1 }; db.Insertable(music).ExecuteCommand(); } else { music.DATETIME_MODIFIED = DateTime.Now; music.USER_MODIFIED = "SYS"; music.QTY++; db.Updateable(music).ExecuteCommand(); } } }
public bool PwdIsRight(string user, string password) { using (var db = SugarContext.GetInstance()) { return(DotNetCoreServer.Domains.User.Current.PwdIsRight(user, password, db)); } }
public void Delete(string ID) { using (var db = SugarContext.GetInstance()) { db.Deleteable <SYS_SHOP_INFO>().Where(c => c.ID == ID).ExecuteCommand(); } }
public object GetArticle(string user, string category) { using (var db = SugarContext.GetInstance()) { var query = db.Queryable <ARTICLE, ARTICLE, ARTICLE>((a, a2, a3) => new ( JoinType.Left, a.LAST_ESSAY == a2.ID, JoinType.Left, a.NEXT_ESSAY == a3.ID )).OrderBy((a, a2, a3) => a.DATETIME_CREATED, OrderByType.Desc) .Where((a, a2, a3) => a.USER_CREATED == user && a.STATE == "A") .WhereIF(!string.IsNullOrEmpty(category) && category != "全部", (a, a2, a3) => a.ARTICLE_CATEGORY.Contains(category)) .Select((a, a2, a3) => new { a.IMG_CODE, a.ID, DATETIME_CREATED = Convert.ToDateTime(a.DATETIME_CREATED.ToString("yyyy-MM-dd HH:mm:ss")), CONTENT = a.CONTENT.Substring(0, 200), a.ARTICLE_NAME, a.ARTICLE_CODE, a.ARTICLE_CATEGORY, a.LAST_ESSAY, LAST_ESSAY_NAME = a2.ARTICLE_NAME, a.NEXT_ESSAY, NEXT_ESSAY_NAME = a3.ARTICLE_NAME }); var result = query.ToList(); return(result); } }
public void UpdateList(string groupId, string groupName, string users) { using (var db = SugarContext.GetInstance()) { var g = db.Queryable <GROUPS>().Where(x => x.GROUP_ID == groupId).ToList().FirstOrDefault(); if (g == null) { g = new GROUPS() { ID = Guid.NewGuid().ToString("N").ToUpper(), DATETIME_CREATED = DateTime.Now, USER_CREATED = "SYS", STATE = "A", GROUP_ID = groupId, GROUP_NAME = groupName, USERS = users }; db.Insertable(g).ExecuteCommand(); } else { var us = g.USERS.Split(';').Distinct().ToList(); if (!us.Contains(users)) { us.Add(users); } g.GROUP_NAME = groupName; g.USERS = string.Join(';', us); db.Updateable(g).ExecuteCommand(); } } }
public object GetAllArticle(string user) { using (var db = SugarContext.GetInstance()) { var result = db.Queryable <ARTICLE, ARTICLE, ARTICLE>((a, a2, a3) => new ( JoinType.Left, a.LAST_ESSAY == a2.ID, JoinType.Left, a.NEXT_ESSAY == a3.ID )).OrderBy((a, a2, a3) => a.DATETIME_CREATED, OrderByType.Desc) .Where((a, a2, a3) => a.USER_CREATED == user && a.STATE == "A") .Select((a, a2, a3) => new { a.IMG_CODE, a.ID, CONTENT = a.CONTENT, a.ARTICLE_NAME, a.ARTICLE_CODE, a.ARTICLE_CATEGORY, a.LAST_ESSAY, LAST_ESSAY_NAME = a2.ARTICLE_NAME, a.NEXT_ESSAY, NEXT_ESSAY_NAME = a3.ARTICLE_NAME }).ToList(); return(result); } }
public void AddEmoji(string groupId, string url) { using (var db = SugarContext.GetInstance()) { var result = db.Queryable <EMOJI>().Where(x => x.GROUP_ID == groupId).ToList().FirstOrDefault(); var list = new List <string>(); if (result == null) { list.Add(url); result = new EMOJI { ID = Guid.NewGuid().ToString("N").ToUpper(), DATETIME_CREATED = DateTime.Now, USER_CREATED = "SYS", STATE = "A", GROUP_ID = groupId, URLS = JsonConvert.SerializeObject(list) }; db.Insertable(result).ExecuteCommand(); } else { var rs = JsonConvert.DeserializeObject <List <string> >(result.URLS); if (!rs.Contains(url)) { rs.Add(url); } result.URLS = JsonConvert.SerializeObject(rs); db.Updateable(result).ExecuteCommand(); } } }
public void UploadMusic(JToken jt) { var list = JsonConvert.DeserializeObject <List <MUSICS> >(jt.ToString()); using (var db = SugarContext.GetInstance()) { foreach (var ds in list) { ds.ID = Guid.NewGuid().ToString("N").ToUpper(); ds.DATETIME_CREATED = DateTime.Now; ds.USER_CREATED = "SYS"; ds.STATE = "A"; ds.MUSIC_NAME = ds.MUSIC_NAME.Trim(); ds.ARTISTS = ds.ARTISTS.Trim(); var msg = db.Queryable <MUSICS>().Where(c => c.MUSIC_NAME == ds.MUSIC_NAME).ToList().FirstOrDefault(); if (msg == null) { db.Insertable(ds).ExecuteCommand(); } else { ds.DATETIME_MODIFIED = DateTime.Now; ds.USER_MODIFIED = "SYS"; db.Updateable(ds).ExecuteCommand(); } } } }
public object GetArticleConent(string id) { using (var db = SugarContext.GetInstance()) { var result = db.Queryable <ARTICLE, ARTICLE, ARTICLE>((a, a2, a3) => new ( JoinType.Left, a.LAST_ESSAY == a2.ID && a.STATE == a2.STATE, JoinType.Left, a.NEXT_ESSAY == a3.ID && a.STATE == a3.STATE )).Where((a, a2, a3) => a.ID == id && a.STATE == "A").Select((a, a2, a3) => new { a.IMG_CODE, a.ID, DATETIME_CREATED = Convert.ToDateTime(a.DATETIME_CREATED.ToString("yyyy-MM-dd HH:mm:ss")), a.CONTENT, a.ARTICLE_NAME, a.ARTICLE_CODE, a.ARTICLE_CATEGORY, LAST_ESSAY = a2.ID, LAST_ESSAY_NAME = a2.ARTICLE_NAME, NEXT_ESSAY = a3.ID, NEXT_ESSAY_NAME = a3.ARTICLE_NAME }).First(); return(result); } }
public void DeleteSongFromList(string user, string listId, string songId) { using (var db = SugarContext.GetInstance()) { db.Deleteable <SONG_LIST_DETAIL>().Where(c => c.LIST_ID == listId && c.USER_CREATED == user && c.MUSIC_ID == songId).ExecuteCommand(); } }
public void AddSongToList(string user, string listId, string songId) { using (var db = SugarContext.GetInstance()) { var detail = db.Queryable <SONG_LIST_DETAIL>().Where(c => c.LIST_ID == listId && c.MUSIC_ID == songId).ToList().FirstOrDefault(); if (detail is null) { detail = new SONG_LIST_DETAIL { ID = Guid.NewGuid().ToString("N").ToUpper(), DATETIME_CREATED = DateTime.Now, USER_CREATED = user, STATE = "A", LIST_ID = listId, MUSIC_ID = songId }; db.Insertable(detail).ExecuteCommand(); } else { detail.USER_MODIFIED = user; detail.DATETIME_MODIFIED = DateTime.Now; detail.STATE = "A"; db.Updateable(detail).ExecuteCommand(); } } }
public void CreateSongList(string user, string name, string img) { if (string.IsNullOrEmpty(user) || string.IsNullOrEmpty(name)) { throw new Exception("用户名和歌单名不能为空!"); } using (var db = SugarContext.GetInstance()) { var list = db.Queryable <SONG_LIST>().Where(c => c.USER_CREATED == user && c.LIST_NAME == name).ToList().FirstOrDefault(); if (list is null) { list = new SONG_LIST { ID = Guid.NewGuid().ToString("N").ToUpper(), DATETIME_CREATED = DateTime.Now, USER_CREATED = user, STATE = "A", LIST_NAME = name, IMG = img }; db.Insertable(list).ExecuteCommand(); } else { list.LIST_NAME = name; list.IMG = img; list.USER_MODIFIED = user; list.DATETIME_MODIFIED = DateTime.Now; db.Updateable(list).ExecuteCommand(); } } }
public object GetSongList(string user, string listId, string listName) { using (var db = SugarContext.GetInstance()) { var result = db.Queryable <SONG_LIST, SONG_LIST_DETAIL, MUSICS>((l, d, m) => new ( JoinType.Left, l.ID == d.LIST_ID, JoinType.Left, d.MUSIC_ID == m.ID )).WhereIF(!string.IsNullOrEmpty(listId), (l, d, m) => l.ID == listId) .WhereIF(!string.IsNullOrEmpty(listName), (l, d, m) => l.LIST_NAME == listName) .Where((l, d, m) => l.USER_CREATED == user) .Select((l, d, m) => new { l.LIST_NAME, LIST_ID = l.ID, l.IMG, m.ID, m.MUSIC_NAME, m.CDN, m.ARTISTS, m.QUALITY }).ToList().GroupBy(c => new { c.LIST_NAME, c.LIST_ID, c.IMG }) .Select(c => new { c.Key.LIST_ID, c.Key.LIST_NAME, c.Key.IMG, DETAILS = c.Select(x => new { x.ID, x.MUSIC_NAME, x.ARTISTS, x.CDN, x.QUALITY }).Where(x => !string.IsNullOrEmpty(x.MUSIC_NAME)).ToList() }).ToList(); return(result); } }
public void DeleteSongList(string listId) { using (var db = SugarContext.GetInstance()) { db.Deleteable <SONG_LIST_DETAIL>().Where(c => c.LIST_ID == listId).ExecuteCommand(); db.Deleteable <SONG_LIST>().Where(c => c.ID == listId).ExecuteCommand(); } }
public void Delete(string id) { using (var db = SugarContext.GetInstance()) { var article = db.Queryable <ARTICLE>().Where(x => x.ID == id).ToList().FirstOrDefault(); article.STATE = "D"; article.DATETIME_MODIFIED = DateTime.Now; db.Updateable(article).ExecuteCommand(); } }
public void Submit(JToken jt) { var model = JsonConvert.DeserializeObject <NicheCommentsSubmit>(jt.ToString()); using (var db = SugarContext.GetInstance()) { var shop = db.Queryable <SYS_SHOP_INFO>().Where(c => c.SHOP_NAME == model.SHOP_NAME).ToList().FirstOrDefault(); if (shop == null) { shop = new SYS_SHOP_INFO { ID = Guid.NewGuid().ToString("N").ToUpper(), DATETIME_CREATED = DateTime.Now, USER_CREATED = "SYS", SHOP_NAME = model.SHOP_NAME, ENVIRONMENT_SCORE = model.ENVIRONMENT_SCORE, OTHER_SCORE = model.OTHER_SCORE }; db.Insertable(shop).ExecuteCommand(); } foreach (var item in model.DETAILS) { var detail = db.Queryable <SYS_SHOP_DETAIL>().Where(c => c.SHOP_ID == shop.ID && c.FOOD_NAME == item.project).ToList().FirstOrDefault(); if (detail is null) { detail = new SYS_SHOP_DETAIL { ID = Guid.NewGuid().ToString("N").ToUpper(), DATETIME_CREATED = DateTime.Now, USER_CREATED = "SYS", SHOP_ID = shop.ID, FOOD_NAME = item.project, SCORE = item.stars }; db.Insertable(detail).ExecuteCommand(); } if (item.atts != null) { foreach (var url in item.atts) { var att = new FOOD_IMAGES { ID = Guid.NewGuid().ToString("N").ToUpper(), DATETIME_CREATED = DateTime.Now, USER_CREATED = "SYS", SHOP_DETAIL_ID = detail.ID, URL = url.CDN }; db.Insertable(att).ExecuteCommand(); } } } } }
public object GetChatList(string username) { try { using (var db = SugarContext.GetInstance()) { var rs = db.Queryable <GROUPS>().Where(x => x.STATE == "A").ToList(); var list = new List <GROUPS>(); var group = rs.Where(c => c.GROUP_NAME != "friend").ToList(); foreach (var ds in group) { var user = ds.USERS.Split(';'); if (user.Contains(username)) { var m = new GROUPS { USERS = username, GROUP_ID = ds.GROUP_ID, GROUP_NAME = ds.GROUP_NAME }; list.Add(m); } } var friend = rs.Where(x => x.GROUP_NAME == "friend").ToList(); foreach (var ds in friend) { var user = ds.USERS.Split(';'); if (user.Contains(username)) { var friends = ds.GROUP_ID.Split(';').ToList(); var friend_id = friends.Where(c => c != username).FirstOrDefault(); var friend_info = db.Queryable <SYS_USER>().Where(e => e.USER_NAME == friend_id).ToList().FirstOrDefault(); if (friend_info != null) { var m = new GROUPS { USERS = username, GROUP_ID = string.Join(';', friends.OrderBy(c => c.Length).OrderBy(c => c[0])), GROUP_NAME = friend_info?.DISPLAY_NAME, ID = friend_info?.IMG }; list.Add(m); } } } return(list); } } catch (Exception ex) { throw ex; } }
public static object GetImage(string user) { using (var db = SugarContext.GetInstance()) { var result = db.Queryable <IMAGE>() .Where(x => x.USER_CREATED == user) .OrderBy(x => x.DATETIME_CREATED, OrderByType.Desc) .ToList(); return(result); } }
public object SearchArtist(string artist) { try { using (var db = SugarContext.GetInstance()) { var music = db.Queryable <MUSICS>().Where(c => c.ARTISTS.Contains(artist)).ToList(); var album = db.Queryable <MUSIC_INFO>().Where(c => c.SINGER_NAME == artist && !string.IsNullOrEmpty(c.ALBUM_NAME) && c.ALBUM_NAME != "空").ToList().Select(c => new MUSIC_INFO { SINGER_NAME = c.SINGER_NAME.Trim(), ALBUM_NAME = c.ALBUM_NAME.Trim() }).ToList(); var musics = music.GroupBy(c => c.ARTISTS.Trim()).Select(c => new MUSICS { ARTISTS = c.Key.Trim(), QTY = c.Count() }).ToList(); var albums = album.GroupBy(c => c.SINGER_NAME).Select(c => new MUSIC_INFO { SINGER_NAME = c.Key, ALBUM_COUNT = c.Count(), ALBUMS = c.GroupBy(x => x.ALBUM_NAME).Select(x => new MUSIC_INFO { NAME = x.Key, COUNT = x.Count() }).ToList() }).ToList(); var list = new List <dynamic>(); foreach (var m in musics) { var a = albums.FirstOrDefault(c => c.SINGER_NAME == m.ARTISTS); dynamic _ = new { ARTISTS = m.ARTISTS, COUNT = m.QTY, ALBUM_COUNT = a == null ? 0 : a.ALBUM_COUNT, ALBUM = a }; list.Add(_); } return(list.OrderByDescending(c => c.COUNT).Take(1).ToList()); } } catch (Exception ex) { throw ex; } }
public object GetArticleCategory() { using (var db = SugarContext.GetInstance()) { var res = db.Queryable <ARTICLE>().Where(x => !string.IsNullOrEmpty(x.ARTICLE_CATEGORY)).Select(c => c.ARTICLE_CATEGORY).ToList(); var ls = new List <string>(); res.ForEach(c => { c.Split(';').ToList().ForEach(x => ls.Add(x)); }); return(ls.Distinct().ToList()); } }
public string GetToken(string name, string pwd) { using (var db = SugarContext.GetInstance()) { var isLogin = false; if (Domains.User.Current.PwdIsRight(name, pwd, db)) { isLogin = true; } //返回Token return(Domians.AuthDomain.Current.BuildToken(isLogin)); } }
public MUSIC_INFO GetMusicInfo(string song, string singer) { using (var db = SugarContext.GetInstance()) { var singers = singer.Split(new string[] { "&", "_", ".", "," }, StringSplitOptions.RemoveEmptyEntries); var singersTrim = singers.Select(c => c.Trim()).ToList(); singer = string.Join("&", singersTrim); var result = db.Queryable <MUSIC_INFO>().Where(c => c.SONG_NAME == song && c.SINGER_NAME == singer).ToList().OrderByDescending(c => c.LYRIC).FirstOrDefault(); if (result != null) { result.LYRIC = result.LYRIC.Replace("\\n", "\n"); } return(result); } }
public List <string> GetEmoji(string groupId) { using (var db = SugarContext.GetInstance()) { var result = db.Queryable <EMOJI>().Where(x => x.GROUP_ID == groupId).ToList().FirstOrDefault(); if (result == null) { return(new List <string>()); } else { return(JsonConvert.DeserializeObject <List <string> >(result.URLS)); } } }
public object GetChatRecord(string groupId) { try { using (var db = SugarContext.GetInstance()) { var rs = db.Queryable <CHAT_RECORD>().Where(x => x.GROUP_ID == groupId && x.STATE == "A").ToList().FirstOrDefault(); return(rs?.CHAR_RECORD); } } catch (Exception ex) { throw ex; } }
public void RegistUser(object value) { using (var db = SugarContext.GetInstance()) { try { var res = JsonConvert.DeserializeObject <dynamic>(value.ToString()); string username = res.username; string oldpassword = res.oldpassword; string isedit = res.isedit; var info = db.Queryable <SYS_USER>().Where(x => x.USER_NAME == username && x.PASSWORD == oldpassword).ToList().FirstOrDefault(); if (isedit == "Y") { if (info == null) { throw new Exception("用户不存在或密码错误"); } else { info.DISPLAY_NAME = res.displayname; info.PASSWORD = res.password; info.IMG = res.img; db.Updateable(info).ExecuteCommand(); } } else { info = new SYS_USER { ID = Guid.NewGuid().ToString().ToUpper(), DATETIME_CREATED = DateTime.Now, USER_CREATED = "SYS", STATE = "A", USER_NAME = res.username, DISPLAY_NAME = res.displayname, PASSWORD = res.password, IMG = res.img }; db.Insertable(info).ExecuteCommand(); } } catch (Exception ex) { throw ex; } } }
public object EditArticle(JToken jt) { var id = jt["ID"]?.ToString(); var name = jt["NAME"]?.ToString(); var content = jt["CONTENT"]?.ToString(); var category = jt["CATEGORY"]?.ToString(); var last = jt["last"]?.ToString(); var next = jt["next"]?.ToString(); using (var db = SugarContext.GetInstance()) { db.Ado.BeginTran(); try { var article = db.Queryable <ARTICLE>().Where(x => x.ID == id).ToList().FirstOrDefault(); article.CONTENT = content; article.ARTICLE_NAME = name; article.ARTICLE_CATEGORY = category; article.DATETIME_MODIFIED = DateTime.Now; article.LAST_ESSAY = last; article.NEXT_ESSAY = next; db.Updateable(article).Where(x => x.ID == article.ID).ExecuteCommand(); var lastEssay = db.Queryable <ARTICLE>().Where(x => x.ID == last).ToList().FirstOrDefault(); if (lastEssay != null) { lastEssay.NEXT_ESSAY = article.ID; db.Updateable(lastEssay).ExecuteCommand(); } var nextEssay = db.Queryable <ARTICLE>().Where(x => x.ID == next).ToList().FirstOrDefault(); if (nextEssay != null) { nextEssay.LAST_ESSAY = article.ID; db.Updateable(nextEssay).ExecuteCommand(); } db.Ado.CommitTran(); } catch (Exception ex) { db.Ado.RollbackTran(); throw ex; } } return(true); }
public object NewArticle(string title, string content, string user, string category, string last, string next) { using (var db = SugarContext.GetInstance()) { db.Ado.BeginTran(); try { ARTICLE atc = new ARTICLE(); atc.ID = string.Join("", Guid.NewGuid().ToString().ToUpper().Where(c => char.IsDigit(c))); atc.USER_CREATED = user; atc.DATETIME_CREATED = DateTime.Now; atc.ARTICLE_CODE = atc.ID; atc.ARTICLE_NAME = title; atc.CONTENT = content; atc.ARTICLE_CATEGORY = category; atc.LAST_ESSAY = last; atc.NEXT_ESSAY = next; db.Insertable(atc).ExecuteCommand(); var lastEssay = db.Queryable <ARTICLE>().Where(x => x.ID == last).ToList().FirstOrDefault(); if (lastEssay != null) { lastEssay.NEXT_ESSAY = atc.ID; db.Updateable(lastEssay).ExecuteCommand(); } var nextEssay = db.Queryable <ARTICLE>().Where(x => x.ID == next).ToList().FirstOrDefault(); if (nextEssay != null) { nextEssay.LAST_ESSAY = atc.ID; db.Updateable(nextEssay).ExecuteCommand(); } db.Ado.CommitTran(); return(true); } catch (Exception ex) { db.Ado.RollbackTran(); throw ex; } } }
public void DeleteEmoji(string groupId, string url) { using (var db = SugarContext.GetInstance()) { var result = db.Queryable <EMOJI>().Where(x => x.GROUP_ID == groupId).ToList().FirstOrDefault(); var list = new List <string>(); if (result == null) { return; } else { var rs = JsonConvert.DeserializeObject <List <string> >(result.URLS); rs.Remove(url); result.URLS = JsonConvert.SerializeObject(rs); db.Updateable(result).ExecuteCommand(); } } }
public object GetRank(int start, int length, string user, string singerName) { using (var db = SugarContext.GetInstance()) { int total = 0; var musics = db.Queryable <MUSICS, PLAY_COUNT>((m, p) => new ( JoinType.Left, m.ID == p.MUSIC_ID )).WhereIF(!string.IsNullOrEmpty(singerName), (m, p) => m.ARTISTS == singerName) .OrderBy((m, p) => p.QTY, OrderByType.Desc).Select((m, p) => new MUSICS { QTY = p.QTY, MUSIC_NAME = m.MUSIC_NAME, CDN = m.CDN, ARTISTS = m.ARTISTS, QUALITY = m.QUALITY }).ToPageList(start, length, ref total); var ilike = db.Queryable <I_LIKE>().WhereIF(!string.IsNullOrEmpty(user), c => c.USER_CODE == user).ToList(); var map = new Dictionary <string, string>(); ilike.ForEach(x => map[x.MUSIC_NAME] = x.MUSIC_NAME); foreach (var item in musics) { item.MUSIC_NAME = item.MUSIC_NAME.Trim(); item.COLOR = map.ContainsKey(item.MUSIC_NAME) ? "red" : "black"; } if (length == 0) { return(musics); } else { return new { data = musics, total } }; } }
public object Login(string user, string pwd) { using (var db = SugarContext.GetInstance()) { try { if (!UserIsExist(user, db)) { throw new Exception("用户不存在"); } if (!PwdIsRight(user, pwd, db)) { throw new Exception("密码错误"); } var res = db.Queryable <SYS_USER>() .Where(e => e.USER_NAME == user && e.PASSWORD == pwd).Select(c => new { c.IMG, c.ID, c.DISPLAY_NAME }).First(); if (res != null) { var token = AuthDomain.Current.BuildToken(true); return(new { Message = "登录成功", data = res, token }); } else { throw new Exception("账号或密码不正确"); } } catch (Exception ex) { throw ex; } } }