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 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 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 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 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 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 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 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 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 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 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 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 static TabItemClose Init(TreeList SelectedNode, MainWindow plugin)
{
using (var db = SugarContext.GetContext(SelectedNode.ParentNode.ParentNode.CONN_STRING, SelectedNode.ParentNode.ParentNode.Type.Value))
{
var NodeName = SelectedNode.NODE_NAME;
var ls = NodeName.Split('.');
if (ls.Length == 2)
{
NodeName = ls[1];
}
var Idb = db.GetIDb();
var result = Idb.GetColumns(db, NodeName);
var index = Idb.GetIndex(db, NodeName);
var p_tab = new TabControl();
var data1 = new DataGrid
{
ItemsSource = result.DefaultView,
GridLinesVisibility = DataGridGridLinesVisibility.All,
CanUserAddRows = false
};
var item1 = new TabItem
{
Content = data1,
Height = 30,
MinWidth = 100,
Header = "列",
Style = (Style)plugin.FindResource("TabItemNormal")
};
var data_index = new DataGrid
{
ItemsSource = index.DefaultView,
GridLinesVisibility = DataGridGridLinesVisibility.All,
CanUserAddRows = false
};
var item_index = new TabItem
{
Content = data_index,
Height = 30,
MinWidth = 100,
Header = "索引",
Style = (Style)plugin.FindResource("TabItemNormal")
};
p_tab.Items.Add(item1);
p_tab.Items.Add(item_index);
var p_item = new TabItemClose();
p_item.Content = p_tab;
return(p_item);
}
}
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 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 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 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 static List <TabItem> InitTabItem(string NodeName, dbcfg cfg, MainWindow plugin)
{
using (var db = SugarContext.GetContext(cfg.connection_string, cfg.dbtype))
{
var ls = NodeName.Split('.');
if (ls.Length == 2)
{
NodeName = ls[1];
}
var Idb = db.GetIDb();
var result = Idb.GetColumns(db, NodeName);
var index = Idb.GetIndex(db, NodeName);
var p_item = new List <TabItem>();
var data1 = new DataGrid
{
ItemsSource = result.DefaultView,
GridLinesVisibility = DataGridGridLinesVisibility.All,
CanUserAddRows = false
};
var item1 = new TabItem
{
Content = data1,
Height = 30,
MinWidth = 100,
Header = "列",
Style = (Style)plugin.FindResource("TabItemNormal")
};
var data_index = new DataGrid
{
ItemsSource = index.DefaultView,
GridLinesVisibility = DataGridGridLinesVisibility.All,
CanUserAddRows = false
};
var item_index = new TabItem
{
Content = data_index,
Height = 30,
MinWidth = 100,
Header = "索引",
Style = (Style)plugin.FindResource("TabItemNormal")
};
p_item.Add(item1);
p_item.Add(item_index);
return(p_item);
}
}
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));
}
}
private static bool AsyncSql(string sql)
{
try
{
using (var db = SugarContext.GetInstance2())
{
var result = db.Ado.ExecuteCommand(sql);
}
return(true);
}
catch
{
return(false);
}
}
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;
}
}
}
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Set our view from the "main" layout resource
SetContentView(Resource.Layout.Main);
SugarContext.Init(this);
// System.Console.WriteLine(TAG+ "google app id: " + GetString(Resource.String.common_google_play_services_notification_channel_name));
Firebase.FirebaseApp.InitializeApp(this);
// Get our button from the layout resource,
// and attach an event to it
cc = CometChat.GetInstance(this.ApplicationContext);
setUpFields();
}
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);
}