//private void GetInstagramMessages(string userId)
//{
// string query = "select Insta_inboxUserId,PlateformType,Message,ImageSource from TblJobFb where Fbcomment_InboxUserId='" + userId + "'";
// var dt = sql.GetDataTable(query);
// foreach (DataRow item in dt.Rows)
// {
// string InstainboxUserId = Convert.ToString(item["Insta_inboxUserId"]);
// string PlateformType = Convert.ToString(item["PlateformType"]);
// string Message = Convert.ToString(item["Message"]);
// string ImgSource = Convert.ToString(item["ImageSource"]);
// messagingInstapageListInfo.Add(new FbUserMessageInfo() { Message = Message });
// }
//}
public ObservableCollection <SocialUser> GetInstaUserList(string userId)
{
SqLiteHelper sql = new SqLiteHelper();
ObservableCollection <SocialUser> InstaListmembers = new ObservableCollection <SocialUser>();
string query = "select Insta_inboxUserId,Insta_inboxUserName,Insta_inboxUserImage,InstaInboxNavigationUrl from Tbl_Instagram where Parent_User_Id='" + userId + "'";
var dt = sql.GetDataTable(query);
foreach (DataRow item in dt.Rows)
{
string InstainboxUserId = Convert.ToString(item["Insta_inboxUserId"]);
string Insta_inboxUserName = Convert.ToString(item["Insta_inboxUserName"]);
string Insta_inboxUserImage = Convert.ToString(item["Insta_inboxUserImage"]);
string InstaInboxNavigationUrl = Convert.ToString(item["InstaInboxNavigationUrl"]);
if (!InstaListmembers.Any(m => m.InboxUserName.Equals(Insta_inboxUserName)))
{
InstaListmembers.Add(new SocialUser()
{
InboxUserId = InstainboxUserId,
InboxUserName = Insta_inboxUserName,
InboxUserImage = Insta_inboxUserImage,
InboxNavigationUrl = InstaInboxNavigationUrl,
MessageUserType = TabType.Instagram.ToString()
});
}
}
return(InstaListmembers);
}
public ObservableCollection <AcquisitionBaseData> SelectBy(string _colname, string _value, bool _isdesc)
{
string connstr = "Data Source=ZNCPlatform.db;Version=3;";
var col = "ID";//默认为ID
var sort = _isdesc ? "desc" : "asc";
if (_colname != "")
{
col = _colname;
}
var collection = new ObservableCollection <AcquisitionBaseData>();
var sql = "SELECT * FROM [AcquisitionBaseData] WHERE " + col + " = " + _value + " order by ID " + _isdesc;
var conn = new SQLiteConnection(connstr);
var ds = SqLiteHelper.ExecuteDataSet(conn, sql, null);
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
var resource = new AcquisitionBaseData();
resource.ID = int.Parse(dr["FloorID"].ToString());
resource.Code = int.Parse(dr["FloorName"].ToString());
resource.Confidence = int.Parse(dr["BuildingID"].ToString());
resource.Name = dr["Invalid"].ToString();
resource.Remark = dr["Remark"].ToString();
collection.Add(resource);
}
}
return(collection);
}
private void btnToDo_Click(object sender, EventArgs e)
{
try {
string[] str = new string[dataGridView1.Rows.Count];
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Selected == true)
{
TryGameToDo tryGameToDoObject = new TryGameToDo();
tryGameToDoObject.Id = Convert.ToInt64(dataGridView1.Rows[i].Cells[0].Value);
tryGameToDoObject.Url = Convert.ToString(dataGridView1.Rows[i].Cells[1].Value);
tryGameToDoObject.UserName = Convert.ToString(dataGridView1.Rows[i].Cells[2].Value);
tryGameToDoObject.PassWord = Convert.ToString(dataGridView1.Rows[i].Cells[3].Value);
tryGameToDoObject.ReMark = Convert.ToString(dataGridView1.Rows[i].Cells[4].Value);
tryGameToDoObject.DeadLine = Convert.ToDateTime(dataGridView1.Rows[i].Cells[6].Value);
tryGameToDoObject.IsDeleted = false;
//sql = new SqLiteHelper("data source=mydb.db");
sql = new SqLiteHelper();
//创建名为TryGameToDo的数据表
sql.CreateTable("TryGameToDo", new string[] { "Id", "Url", "UserName", "PassWord", "ReMark", "DeadLine", "IsDeleted" }, new string[] { "INTEGER", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT" });
//插入数据
sql.InsertValues("TryGameToDo", new string[] { tryGameToDoObject.Id.ToString(), tryGameToDoObject.Url, tryGameToDoObject.UserName, tryGameToDoObject.PassWord, tryGameToDoObject.ReMark, tryGameToDoObject.DeadLine.ToString(), tryGameToDoObject.IsDeleted.ToString() });
}
}
}
catch (Exception ex)
{
MessageBox.Show("系统发生异常，请联系管理员！", "错误");
LogHelper.WriteLog("窗体异常", ex);
}
}
/// <inheritdoc />
/// <summary>
/// 获取表的所有字段名及字段类型
/// </summary>
public List <Dictionary <string, string> > GetAllColumns(string tableName)
{
var sqliteHelper = new SqLiteHelper();
var dt = sqliteHelper.Query("PRAGMA table_info('" + tableName + "')");
var result = new List <Dictionary <string, string> >();
foreach (DataRow dr in dt.Rows)
{
var dic = new Dictionary <string, string>
{
{ "columns_name", dr["name"].ToString() },
{ "notnull", dr["notnull"].ToString() == "NO" ? "1" : "0" },
{ "comments", "" }
};
var dataType = dr["type"].ToString();
var pos = dataType.IndexOf("(", StringComparison.Ordinal);
if (pos != -1)
{
dataType = dataType.Substring(0, pos);
}
dic.Add("data_type", dataType);
dic.Add("data_scale", "");
dic.Add("data_precision", "");
dic.Add("constraint_type", dr["pk"].ToString() != "0" ? "P" : "");
result.Add(dic);
}
return(result);
}
/// <summary>
/// Gets the item identifier.
/// </summary>
/// <param name="itemTitle">The item title.</param>
/// <param name="itemType">Type of the item.</param>
/// <returns></returns>
public static int GetItemId(string itemTitle, string itemType)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Query table for item name
DataTable mediaData =
sh.Select(
"SELECT item.id FROM item " + "JOIN type ON type.id = item.typeId " +
"WHERE item.title = @title AND type.type = @type;",
new[]
{
new SQLiteParameter("@title", itemTitle),
new SQLiteParameter("@type", itemType)
});
// Return 0 if there are no rows from query
if ( mediaData.Rows.Count == 0 ) return 0;
// Otherwise return the Items ID
return Convert.ToInt32(mediaData.Rows[0].ItemArray.GetValue(0));
}
}
}
public async Task <bool> LoadTheDatabase(string sqLiteDbFilename)
{
try
{
string dbPath = await DependencyService.Get <IEqFileHelper>().GetDBPathAndCreateIfNotExists(sqLiteDbFilename);
SQLiteConnection = new SQLite.SQLiteConnection(dbPath);
IList <string> tbls = SqLiteHelper.GetTableNames(SQLiteConnection); // Useful for debugging
_infos = ReadInfoTable(SQLiteConnection);
DbFormatNumber = _infos[STR_DbFormatNumber];
Name = _infos[STR_LibraryName];
Description = _infos[STR_LibraryDescription];
PackageName = _infos[STR_PackageName];
//await Task.Delay(5000); // Simulate a delay for Debugging
return(true);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// Gets the theme from the users settings table
/// </summary>
public static void GetTheme()
{
// Get the users preferred theme from the database
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Setup database connection
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Get users settings from database
DataTable dt = sh.Select("SELECT * FROM settings WHERE id = @userid;",
new[] { new SQLiteParameter("@userid", CurrentUserId) });
if ( dt.Rows.Count != 0 )
{
// Get users theme
string themeAccent = dt.Rows[0]["themeAccent"].ToString();
string themeBg = dt.Rows[0]["themeBG"].ToString();
// Apply theme
SetTheme(themeAccent, themeBg);
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public ResponseSet <UploadFile> GetList(string sql)
{
Response <DataTable> result = SqLiteHelper.ExecuteDataTable(sql);
if (!result.Success)
{
return(new ResponseSet <UploadFile> {
Errors = result.Errors
});
}
if (result.Data == null || result.Data.Rows.Count <= 0)
{
return(new ResponseSet <UploadFile> {
Errors = "No Data"
});
}
ObservableCollection <UploadFile> datas = new ObservableCollection <UploadFile>();
result.Data.Rows.Cast <DataRow>().ToList().ForEach(x => datas.Add(x));
return(new ResponseSet <UploadFile> {
Datas = datas
});
}
private void course_Load(object sender, EventArgs e)
{
sql = new SqLiteHelper("data source=" + Application.StartupPath + "\\mydb.db");
//---------------------------------------------------------------------------
SQLiteDataReader read = sql.ExecuteQuery("select cname from course");
//bool bRead = false;
while (read.Read())
{
cbName.Items.Add(read.GetString(read.GetOrdinal("cname")));
//bRead = true;
}
//if (bRead) cbName.SelectedIndex = 0;
//----------------------------------------------------------------------------
if (TITLE == "编辑")
{
txtNO.Enabled = false;
SQLiteDataReader reader = sql.ExecuteQuery("select * from course where cno='" + GUID + "'");
while (reader.Read())
{
txtNO.Text = reader.GetString(reader.GetOrdinal("cno"));
txtName.Text = reader.GetString(reader.GetOrdinal("cname"));
cbName.Text = reader.GetString(reader.GetOrdinal("cpno"));
txtCredit.Text = reader.GetString(reader.GetOrdinal("ccredit"));
}
}
}
public static List <IssueModel> ReaderDaysIssue(int readDataDays)
{
List <IssueModel> result = new List <IssueModel>();
try
{
string beginIssue = DateTime.Now.AddDays(0 - readDataDays).ToString("yyyyMMdd") + "001";
string sql = "select * from cqssc where issue>=" + beginIssue + " order by issue desc";
DataTable dt = SqLiteHelper.getBLLInstance().ExecuteTable(sql, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
IssueModel dm = new IssueModel();
dm.StrIssue = row["issue"].ToString();
dm.Wan = int.Parse(row["wan"].ToString());
dm.Qian = int.Parse(row["qian"].ToString());
dm.Bai = int.Parse(row["bai"].ToString());
dm.Shi = int.Parse(row["shi"].ToString());
dm.Ge = int.Parse(row["ge"].ToString());
dm.OpenCode = row["opencode"].ToString();
dm.OpenTime = row["opentime"].ToString();
dm.sync = int.Parse(row["sync"].ToString());
result.Add(dm);
}
}
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
public ObservableCollection <SocialUser> GetFacebookListData(string userId)
{
SqLiteHelper sql = GetSqliteHelper();
ObservableCollection <SocialUser> FbPageListmembers = new ObservableCollection <SocialUser>();
string query = "select Fbcomment_InboxUserId, Fbcomment_InboxUserName,Fbcomment_InboxUserImage,FBInboxNavigationUrl from TblFbComment where Parent_User_Id='" + userId + "'";
var dt = sql.GetDataTable(query);
foreach (DataRow item in dt.Rows)
{
string Fbcomment_InboxUserId = Convert.ToString(item["Fbcomment_InboxUserId"]);
string Fbcomment_InboxUserName = Convert.ToString(item["Fbcomment_InboxUserName"]);
string Fbcomment_InboxUserImage = Convert.ToString(item["Fbcomment_InboxUserImage"]);
string FBInboxNavigationUrl = Convert.ToString(item["FBInboxNavigationUrl"]);
if (!FbPageListmembers.Any(m => m.InboxUserName.Equals(Fbcomment_InboxUserName)))
{
FbPageListmembers.Add(new SocialUser()
{
InboxUserId = Fbcomment_InboxUserId,
InboxUserName = Fbcomment_InboxUserName,
InboxUserImage = Fbcomment_InboxUserImage,
InboxNavigationUrl = FBInboxNavigationUrl,
MessageUserType = TabType.Facebook.ToString()
});
}
}
return(FbPageListmembers);
}
public ObservableCollection <SocialUser> GetLeftMessengerListData(string userId, TabType tabType, string pageid)
{
SqLiteHelper sql = new SqLiteHelper();
ObservableCollection <SocialUser> userListInfo = new ObservableCollection <SocialUser>();
//string query = string.Format("select FacebookId,DisplayName,ImageUrl,NavigationUrl from FacebookUsers where Parent_User_Id='{0}' and JobType={1} ", userId, (int)tabType);
string query = "select FacebookId,DisplayName,ImageUrl,NavigationUrl,PageId from FacebookUsers where Parent_User_Id='" + userId + "' and JobType='" + (int)tabType + "' and PageId='" + pageid + "'";
var dt = sql.GetDataTable(query);
foreach (DataRow item in dt.Rows)
{
string inboxUserId = Convert.ToString(item["FacebookId"]);
string inboxUserName = Convert.ToString(item["DisplayName"]);
string inboxUserImage = Convert.ToString(item["ImageUrl"]);
string inboxNavigationUrl = Convert.ToString(item["NavigationUrl"]);
string pageId = Convert.ToString(item["PageId"]);
//if (!UserListInfo.Any(m => m.InboxUserName.Equals(M_inboxUserName)))
if (!userListInfo.Any(m => m.InboxUserId.Equals(inboxUserId)))
{
userListInfo.Add(new SocialUser()
{
InboxUserId = inboxUserId, InboxUserName = inboxUserName,
InboxUserImage = inboxUserImage,
InboxNavigationUrl = inboxNavigationUrl,
MessageUserType = tabType.ToString(),
PageId = pageId
});
}
}
return(userListInfo);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="model"></param>
/// <returns>成功返回true</returns>
public static bool DeleteCascading(Cascading model)
{
if (model != null)
{
var con = SqLiteHelper.Open(_dbPath);
try
{
string mysqlString = "delete from ipvt_cascadingtable where CascadingID=?id";
var param = new MySqlParameter("?id", model.Id);
CustomMySqlHelper.ExecuteNonQuery(mysqlString, param);
string sqliteSql = string.Format("delete from db_data where RowID={0}", model.RowId);
SqLiteHelper.ExecuteNonquery(con, sqliteSql);
return(true);
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (con != null)
{
con.Close();
}
}
}
return(false);
}
/// <summary>
/// Gets the font from the users settings table
/// </summary>
public static void GetFont()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Setup database connection
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Get users settings from database
DataTable dt = sh.Select("SELECT * FROM settings WHERE id = @userid;",
new[] { new SQLiteParameter("@userid", CurrentUserId) });
if ( dt.Rows.Count == 0 ) return;
// Get users theme
int fontSize = Convert.ToInt32(dt.Rows[0]["fontSize"]);
string fontType = dt.Rows[0]["fontType"].ToString();
UseFont(fontSize, fontType);
}
}
}
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Create your application here
// Set our view from the "home" layout resource
SetContentView(Resource.Layout.Home);
InitComponents();
ActionBar.Hide();
// Cargamos el avatar
database = new SqLiteHelper();
dbpath = System.IO.Path.Combine(
System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "ormdemo.db3");
ImageView imageMicrophone = FindViewById <ImageView>(Resource.Id.imageViewMicrophone);
imageMicrophone.Click += delegate
{
Hablar();
};
Button BtnModo = FindViewById <Button>(Resource.Id.BtnModo);
BtnModo.Click += delegate
{
Intent intent = new Intent(this, typeof(MasterActivity));
StartActivity(intent);
};
// Empezamos a saludar al usuario
Hablar();
}
private void btnSearch_Click(object sender, EventArgs e)
{
try
{
sql = new SqLiteHelper();
//读取整张表
SQLiteDataAdapter da = sql.ReadFullTabledataAdapterNotDelete("TryGameToDo");
if (da != null)
{
ClearData("TryGameToDo");
da.Fill(ds, "TryGameToDo");
this.dataGridView1.DataSource = ds.Tables[0];
foreach (DataTable item in ds.Tables)
{
if (this.cbTable.FindString(item.TableName) == -1)
{
cbTable.Items.Add(item.TableName);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("系统发生异常，请联系管理员！", "错误");
LogHelper.WriteLog("异常", ex);
}
}
/// <summary>
/// Gets the reviews and stores them in the Reviews property
/// </summary>
/// <param name="itemId">The item identifier.</param>
private void GetReviews(int itemId)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the UserList datatable
Reviews =
sh.Select(
"SELECT review.id, user.username, progress.rating, review.date, review.review " +
"FROM review " +
"JOIN user ON user.ID = review.userId " +
"JOIN progress ON progress.UserId = review.userid AND progress.ItemId = review.itemid " +
"WHERE review.review <> '' AND review.itemId = @itemid ;",
new[] { new SQLiteParameter("@itemid", itemId) });
// Set property to true if item has reviews
int noOfReviews = Reviews.Rows.Count;
HasReviews = noOfReviews > 0;
}
}
}
private void GetAllItems()
{
string query = "select * from TblMessage";
SqLiteHelper sql = new SqLiteHelper();
DtCampaign = sql.GetDataTable(query);
// dataGrid.ItemsSource = DtCampaign;
}
public MainWindow()
{
InitializeComponent();
SqLiteHelper.InitializeDB();
ShowWelcomeCake();
}
public TangleMessenger(Seed seed, int minWeightMagnitude = 14)
{
this.seed = seed;
this.MinWeight = minWeightMagnitude;
this.repository = DependencyResolver.Resolve <IRepositoryFactory>().Create();
this.ShortStorageAddressList = new List <string>();
this.sqLite = new SqLiteHelper();
}
private void teacher_Load(object sender, EventArgs e)
{
sql = new SqLiteHelper("data source=" + Application.StartupPath + "\\mydb.db");
cbSex.Items.Add("男");
cbSex.Items.Add("女");
cbSex.SelectedIndex = 0;
for (int i = 60; i >= 24; i--)
{
cbAge.Items.Add(i.ToString());
}
cbAge.SelectedIndex = 0;
cbEb.Items.Add("博士");
cbEb.Items.Add("硕士");
cbEb.Items.Add("学士");
cbEb.SelectedIndex = 0;
cbPt.Items.Add("教授");
cbPt.Items.Add("副教授");
cbPt.Items.Add("讲师");
cbPt.Items.Add("助教");
cbPt.SelectedIndex = 0;
//---------------------------------------------------------------------------
SQLiteDataReader read = sql.ExecuteQuery("select cname from course");
//bool bRead = false;
while (read.Read())
{
string strCno = read.GetString(read.GetOrdinal("cname"));
cbCno1.Items.Add(strCno);
cbCno2.Items.Add(strCno);
cbCno3.Items.Add(strCno);
//bRead = true;
}
//if (bRead) cbName.SelectedIndex = 0;
//----------------------------------------------------------------------------
if (TITLE == "编辑")
{
txtNO.Enabled = false;
SQLiteDataReader reader = sql.ExecuteQuery("select * from teacher where tno='" + GUID + "'");
while (reader.Read())
{
txtNO.Text = reader.GetString(reader.GetOrdinal("tno"));
txtName.Text = reader.GetString(reader.GetOrdinal("tname"));
cbSex.Text = reader.GetString(reader.GetOrdinal("tsex"));
cbAge.Text = reader.GetString(reader.GetOrdinal("tage"));
cbEb.Text = reader.GetString(reader.GetOrdinal("teb"));
cbPt.Text = reader.GetString(reader.GetOrdinal("tpt"));
cbCno1.Text = reader.GetString(reader.GetOrdinal("cno1"));
cbCno2.Text = reader.GetString(reader.GetOrdinal("cno2"));
cbCno3.Text = reader.GetString(reader.GetOrdinal("cno3"));
}
}
}
/// <inheritdoc />
/// <summary>
/// 获取数据库名
/// </summary>
public List <Dictionary <string, string> > GetAllTables()
{
var sqliteHelper = new SqLiteHelper();
var dt = sqliteHelper.Query("select tbl_name from sqlite_master where type='table'");
return((from DataRow dr in dt.Rows select new Dictionary <string, string> {
{ "table_name", dr["tbl_name"].ToString() }, { "comments", "" }
}).ToList());
}
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
//让用户选择是否删除
MessageBoxButtons btn = MessageBoxButtons.YesNoCancel;
if (MessageBox.Show("确定要删除数据吗？", "删除数据", btn) == DialogResult.Yes)
{
//取出选中行里面绑定的对象
//TryGameToDo tryGameToDoObject = dataGridView1.SelectedRows[0].DataBoundItem as TryGameToDo;
TryGameToDo tryGameToDoObject = new TryGameToDo();
DataRowView rowView = this.dataGridView1.CurrentRow.DataBoundItem as DataRowView;
if (rowView != null)
{
DataRow currentRow = rowView.Row;
tryGameToDoObject.Id = long.Parse(currentRow["Id"].ToString());
tryGameToDoObject.Url = currentRow["Url"].ToString();
tryGameToDoObject.UserName = currentRow["UserName"].ToString();
tryGameToDoObject.PassWord = currentRow["PassWord"].ToString();
tryGameToDoObject.ReMark = currentRow["ReMark"].ToString();
tryGameToDoObject.DeadLine = DateTime.Parse(currentRow["DeadLine"].ToString());
tryGameToDoObject.IsDeleted = true;
}
sql = new SqLiteHelper();
//更新数据
sql.UpdateValues("TryGameToDo", new string[] { "IsDeleted", }, new string[] { tryGameToDoObject.IsDeleted.ToString() }, "Id", tryGameToDoObject.Id.ToString());
//刷新数据
//读取整张表
SQLiteDataAdapter da = sql.ReadFullTabledataAdapterNotDelete("TryGameToDo");
if (da != null)
{
ClearData("TryGameToDo");
da.Fill(ds, "TryGameToDo");
this.dataGridView1.DataSource = ds.Tables[0];
foreach (DataTable item in ds.Tables)
{
if (this.cbTable.FindString(item.TableName) == -1)
{
cbTable.Items.Add(item.TableName);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("系统发生异常，请联系管理员！", "错误");
LogHelper.WriteLog("异常", ex);
}
}
public void Test01()
{
var sSql = "Select * from Dicts where 1=1 and IsDeleted = '0'";
var aHelper = new SqLiteHelper();
var dsr = aHelper.ExecuteDataSet(_connString, sSql, CommandType.Text);
object x = "";
Console.WriteLine(dsr.Tables.Count);
}
/// <summary>
/// 删除过期日志
/// </summary>
/// <param name="time">过去时间</param>
public static int DeleteLogs(DateTime time)
{
if (HaveDb())
{
string sql = "delete from log where logtime<@time";
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, new SQLiteParameter("@time", time)));
}
return(0);
}
private void InitComponents()
{
apiService = new ApiService();
accountListView = FindViewById <ListView>(Resource.Id.listViewAccounts);
avatarWebView = FindViewById <WebView>(Resource.Id.webViewAvatar);
title = FindViewById <TextView>(Resource.Id.textViewTitle);
microphoneImageView = FindViewById <ImageView>(Resource.Id.imageViewMicrophone);
database = new SqLiteHelper();
dbpath = System.IO.Path.Combine(
System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "ormdemo.db3");
}
private void NewUserCommandHandler(object obj)
{
MessageBox.Show("UserId= " + TxtUserId + Environment.NewLine + "Password= "******":" + TxtPassword;
SqLiteHelper sql1 = new SqLiteHelper();
string query = "INSERT INTO TblLogin(FbUserName,FbPassword) values('" + TxtUserId + "','" + TxtPassword + "')";
int yy = sql.ExecuteNonQuery(query);
BindListView();
}
private void CleanButton_Click(object sender, RoutedEventArgs e)
{
////Maintenance: Drop creates tables
SqLiteHelper localDB = new SqLiteHelper();
localDB.Clean();
//Initialize();
//CheckKegStatus();
}
/// <summary>
/// 删除设备
/// </summary>
/// <param name="device"></param>
public static int DeleteDevice(Device device)
{
if (device != null)
{
HaveDb();
string sql = "delete from device where did=@did";
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, new SQLiteParameter("@did", device.Id)));
}
return(-1);
}
private async void UpdateUserConsumption()
{
this.imageLoaded = false;
await Dispatcher.RunAsync(CoreDispatcherPriority.Normal, () =>
{
if (null != this.loggedInUser && deliverOunces)
{
if (App._flow != null)
{
Measurement desp = App._flow.GetFlow();
if (desp != null)
{
dispensed.Add(desp.Amount);
}
}
//Disable FlowControl
if (App._flowControl != null)
{
App._flowControl.IsActive = false;
Reset(false);
}
//Resetting dispensed collection to 0
if (App._flow != null)
{
App._flow.ResetFlow();
}
float totalDispensed = dispensed != null ? dispensed.Sum() : 0.0f;
if (totalDispensed > 0.0f)
{
SqLiteHelper localDB = new SqLiteHelper();
//TODO: Dummy code of random value
//localDB.AddPersonConsumption(this.loggedInUser.HashCode, new Random().Next(1, 20));
localDB.AddPersonConsumption(this.loggedInUser.HashCode, totalDispensed);
KegLogger.KegLogEvent("Beer Delivered!", "Delivered", new Dictionary <string, string>()
{
{ "UserID", this.loggedInUser.HashCode },
{ "Quantity", totalDispensed.ToString() }
});
}
}
this.loggedInUser = null;
this.Frame.Navigate(typeof(MainPage), $"FromPage2:");
});
}
private void Init()
{
userList = new ObservableCollection <User>();
AddUserCommand = new DelegateCommand(runAddUser);
ClosedCommand = new DelegateCommand(runClosedCommand);
DataGridSelectedCommand = new DelegateCommand <DataGrid>(runDataGridSelectedCommand);
Del = new DelegateCommand <DataGrid>(runDel);
sql = SqLiteHelper.getInstance;
sql.ConnectionString = "data source=mydb.db";
AddPowerList = new ObservableCollection <string>();
showUsers();
}
public static bool InsertOrUpdateIssueModel2Db(IssueModel model)
{
string sql = "select count(*) from cqssc where issue=" + model.Issue;
if (SqLiteHelper.getBLLInstance().Exists(sql))
{
return(UpdateIssueModel2Db(model));
}
else
{
return(InsertIssueModel2Db(model));
}
}
public static void LoadDB(Model type)
{
if (type == Model.Voc && Vocabularies == null)
{
SqLiteHelper.GetVocabulariesDB();
}
else if (type == Model.Pron && Pronunciations == null)
{
SqLiteHelper.GetPronunciationsDB();
}
else if (type == Model.Spell && Spellings == null)
{
SqLiteHelper.GetSpellingsDB();
}
}
/// <inheritdoc />
/// <summary>
/// 获取表的所有字段名及字段类型
/// </summary>
public List <Dictionary <string, string> > GetAllColumns(string tableName)
{
var sqliteHelper = new SqLiteHelper();
var dt = sqliteHelper.Query("PRAGMA table_info('" + tableName + "')");
return((from DataRow dr in dt.Rows
select new Dictionary <string, string>
{
{ "columns_name", dr["name"].ToString() },
{ "notnull", dr["notnull"].ToString() == "1" ? "1" : "0" },
{ "comments", "" },
{ "data_type", "string" },
{ "data_scale", "" },
{ "data_precision", "" },
{ "constraint_type", dr["pk"].ToString() == "1" ? "P" : "" }
}).ToList());
}
public static void Init()
{
SqLiteHelper.CreateCommand(
"CREATE TABLE IF NOT EXISTS BookItem ( Id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(100) UNIQUE, Image VARCHAR(100), Description VARCHAR(255), Author VARCHAR(45), Source VARCHAR(20) DEFAULT '本地', Kind VARCHAR(45) DEFAULT '其他', Url VARCHAR(255), `Index` INT DEFAULT 0, Count INT, Time DATETIME);"
).ExecuteNonQuery();
SqLiteHelper.CreateCommand(
"CREATE TABLE IF NOT EXISTS ChapterItem (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(100), Content TEXT NULL, BookId INT, Url VARCHAR(255));"
).ExecuteNonQuery();
SqLiteHelper.CreateCommand(
"CREATE TABLE IF NOT EXISTS WebsiteItem (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(100) UNIQUE, Url VARCHAR(255) UNIQUE);"
).ExecuteNonQuery();
SqLiteHelper.CreateCommand(
"CREATE TABLE IF NOT EXISTS WebRuleItem (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(100) UNIQUE, Url VARCHAR(255) UNIQUE, CatalogBegin VARCHAR(100), CatalogEnd VARCHAR(100), ChapterBegin VARCHAR(100), ChapterEnd VARCHAR(100), Replace TEXT NULL, AuthorBegin VARCHAR(100), AuthorEnd VARCHAR(100), DescriptionBegin VARCHAR(100), DescriptionEnd VARCHAR(100), CoverBegin VARCHAR(100), CoverEnd VARCHAR(100));"
).ExecuteNonQuery();
SqLiteHelper.CreateCommand(
"CREATE TABLE IF NOT EXISTS OptionItem (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(100) UNIQUE, Value Text);"
).ExecuteNonQuery();
}
/// <summary>
/// Try to login to the users account
/// </summary>
private void TryLogin()
{
string inputUsername = TxtUsername.Text;
string inputPassword = TxtPassword.Password;
bool loginSuccess = false;
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Query database for user details
DataTable dt =
sh.Select(
"SELECT * FROM user " + "WHERE UPPER(username) = UPPER(@user) " +
"AND password = @pass;",
new[]
{
new SQLiteParameter("@user", inputUsername),
new SQLiteParameter("@pass", inputPassword)
});
// flag login as success and store user ID
if ( dt.Rows.Count != 0 )
{
loginSuccess = true;
User.CurrentUserId = Convert.ToInt32(dt.Rows[0]["id"]);
}
}
}
// If login successful start the MainWindow and close this
if ( loginSuccess )
{
MainWindow main = new MainWindow();
main.Show();
Close();
}
else Utilities.ShowMessage("Error", "Username or password is incorrect");
}
/// <summary>
/// Adds to database.
/// </summary>
/// <returns></returns>
public static int AddToDatabase()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Look for item in database and return its ID
DataTable dt =
sh.Select(
"SELECT item.id, item.title, type.type " + "FROM item " +
"JOIN Type ON item.typeId = Type.id " +
"WHERE UPPER(title) = UPPER(@title) AND " + "type = @type ;",
new[]
{
new SQLiteParameter("@title", SearchItem.Media.Title),
new SQLiteParameter("@type", SearchItem.Media.MediaType)
});
if ( dt.Rows.Count == 0 )
{
// Item doesn't exist in DB - Add it and get ID
Dictionary <string, object> newItemData = GetItemDetails();
sh.Insert("item", newItemData);
return Convert.ToInt32(sh.LastInsertRowId()); // ID of last insert
}
// Item exists - Get ID
int itemId = Convert.ToInt32(dt.Rows[0]["id"]);
Dictionary <string, object> newData = GetItemDetails();
sh.Update("item", newData, "id", itemId);
return itemId;
}
}
}
/// <summary>
/// Gets the top 10 ranked items from the database
/// </summary>
private void GetPopularItems()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the UserList datatable
PopularItemsDataTable =
sh.Select("SELECT item.id, item.title, item.year, item.genre, type.type " +
"FROM item " + "JOIN type ON type.id = item.typeId " +
"ORDER BY item.imdbRating DESC " + "LIMIT 10");
// Set item source so that DataGrid updates
DataGridPopularItems.ItemsSource = PopularItemsDataTable.DefaultView;
}
}
}
/// <summary>
/// Mark user as having particular item in list
/// </summary>
/// <param name="newItemId">The new item identifier.</param>
private void AddToUsersList(int newItemId)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Add item to users list
Dictionary <string, object> mediaItem = new Dictionary <string, object>();
mediaItem["itemid"] = newItemId;
mediaItem["userid"] = User.CurrentUserId;
sh.Insert("progress", mediaItem);
// Set rating and status
ManipulateItem.SetRating(Convert.ToDouble(RatingPersonal.Value), newItemId);
ManipulateItem.SetStatus(ComboStatus.Text, newItemId);
// Create empty review slot in table
Dictionary <string, object> newReviewData = new Dictionary <string, object>();
newReviewData["userid"] = User.CurrentUserId;
newReviewData["itemid"] = newItemId;
sh.Insert("review", newReviewData);
}
}
}
/// <summary>
/// Saves media item details to database
/// </summary>
private void SaveMedia()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Save item to database
ItemId = ManipulateItem.AddToDatabase();
#region Add item to list
// Check if user already has item
DataTable dt2 =
sh.Select(
"SELECT * FROM progress " +
"WHERE itemid = @itemId AND userid = @userId;",
new[]
{
new SQLiteParameter("@itemid", ItemId),
new SQLiteParameter("@userId", User.CurrentUserId)
});
if ( dt2.Rows.Count == 0 )
{
// User doesn't have item - Add it
AddToUsersList(ItemId);
// Refresh reviews
ShowReviews();
// Refresh Tables
( (MainWindow) Application.Current.MainWindow ).RefreshContent();
Utilities.ShowMessage("Complete", "Finished adding item to list");
}
else Utilities.ShowMessage("Woops", "You already have this item");
#endregion Add item to list
}
}
}
/// <summary>
/// Method called when displaying a specified item
/// </summary>
public void GetLocalItemData()
{
// Get specified data from database
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the DtMovie DataTable
DataTable itemData =
sh.Select(
"SELECT item.id, item.title, item.year, item.imdbrating, item.length, item.synopsis, " +
"item.posterUrl, type.type, " +
"item.trailerurl, item.agerating, item.genre, item.author " +
"FROM item " + "JOIN type ON item.typeId = type.id " +
"WHERE item.id = @item", new[] { new SQLiteParameter("@item", ItemId) });
SearchItem.Media = new Item(itemData);
// Show content and try to get trailer and reviews
ShowItem();
ShowTrailer();
ShowReviews();
BtnReview.Visibility = Visibility.Hidden;
if ( ManipulateItem.HasItem(ItemId) ) BtnReview.Visibility = Visibility.Visible;
}
}
}
/// <summary>
/// Try to register a users account
/// </summary>
private void TryRegister()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
string inputUsername = TxtRegisterUsername.Text;
string inputPassword = TxtRegisterPassword1.Password;
string inputPasswordCheck = TxtRegisterPassword2.Password;
// Check that both passwords match and input isn't empty
if ( inputPassword != inputPasswordCheck || inputPassword == "" ||
inputPasswordCheck == "" || inputUsername == "" )
{
// if no match, or empty, stop
Utilities.ShowMessage("Error", "Passwords do not match!");
return;
}
// Check if username already exists
DataTable dataUserConfirm =
sh.Select("SELECT * FROM User WHERE UPPER(username) = UPPER(@user)",
new[] { new SQLiteParameter("@user", inputUsername) });
if ( dataUserConfirm.Rows.Count > 0 )
// A row getting returned would indicate the user exists
{
Utilities.ShowMessage("Error", "Username has been taken.");
return;
}
// Insert new user into table
Dictionary <string, object> userDict = new Dictionary <string, object>();
userDict["username"] = inputUsername;
userDict["password"] = inputPassword;
sh.Insert("user", userDict);
sh.Execute("INSERT INTO settings DEFAULT VALUES;");
Utilities.ShowMessage("Success", "Account created!");
}
}
}
/// <summary>
/// Stores the font in the users settings table
/// </summary>
/// <param name="fontSize">Size of the font.</param>
/// <param name="fontType">Type of the font.</param>
public static void SetFont(int fontSize, string fontType)
{
// Save the users theme to database
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Setup database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Upload data to database
Dictionary <string, object> settings = new Dictionary <string, object>();
settings["fontSize"] = fontSize;
settings["fontType"] = fontType;
sh.Update("settings", settings, "id", CurrentUserId);
// Apply font
UseFont(fontSize, fontType);
}
}
}
/// <summary>
/// Determines whether the specified item identifier has item.
/// </summary>
/// <param name="itemId">The item identifier.</param>
/// <returns></returns>
public static bool HasItem(int itemId)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Query table for item name
DataTable itemData =
sh.Select(
"SELECT item.id, item.title " + "FROM item " +
"JOIN progress ON item.id = progress.itemId " +
"WHERE item.id = @item AND progress.userId = @user;",
new[]
{
new SQLiteParameter("@item", itemId),
new SQLiteParameter("@user", User.CurrentUserId)
});
// Return false if no items queried, else it's true
if ( itemData.Rows.Count == 0 ) return false;
return true;
}
}
}
/// <summary>
/// Refreshes or set the table.
/// </summary>
public void RefreshTable()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the DtMovie DataTable
MediaDataTable =
sh.Select(
"select item.id, item.title, item.year, progress.rating, progress.progress 'itemprogress', status.id as 'statusid' " +
"from progress " + "join user on progress.userId = user.id " +
"JOIN item on progress.itemId = item.id " +
"JOIN status on progress.statusId = status.id " +
"JOIN type on item.typeId = type.id " +
"WHERE user.id = @user AND type.type = @type AND status.status = @status " +
"ORDER BY item.title ASC;",
new[]
{
new SQLiteParameter("@user", UserId),
new SQLiteParameter("@type", Type),
new SQLiteParameter("@status", Status)
});
DataGridItem.ItemsSource = MediaDataTable.DefaultView;
}
}
}
/// <summary>
/// Get the users data and display it
/// </summary>
/// <param name="userid">The userid.</param>
private void ShowUser(int userid)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Setup database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Query database for user details
DataTable dt = sh.Select("SELECT username, registerdate FROM user WHERE id = @user",
new[] { new SQLiteParameter("@user", userid) });
// If data is returned, display it
if ( dt.Rows.Count == 0 ) return;
LblUsername.Content = dt.Rows[0].ItemArray[0].ToString();
LblDateRegistered.Content = dt.Rows[0].ItemArray[1].ToString();
}
}
}
/// <summary>
/// A generic method used by the other methods to update rows in the specified table
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="columnValue">The column value.</param>
/// <param name="condition">The condition.</param>
/// <param name="conditionValue">The condition value.</param>
private static void UpdateData(string tableName, string columnName, string columnValue, string condition,
int conditionValue)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Update the database
Dictionary <string, object> dicData = new Dictionary <string, object>();
dicData[columnName] = columnValue;
Dictionary <string, object> dicCondition = new Dictionary <string, object>();
dicCondition["userId"] = User.CurrentUserId;
dicCondition[condition] = conditionValue;
sh.Update(tableName, dicData, dicCondition);
}
}
}
/// <summary>
/// Removes the item.
/// </summary>
/// <param name="itemId">The item identifier.</param>
/// <param name="tableName">Name of the table.</param>
public static void RemoveItem(int itemId, string tableName = "progress")
{
// Remove an item from the users list
// Can be used to remove recommendations if using the optional parameter
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
string sql = "DELETE FROM " + sh.Escape(tableName) +
" WHERE itemid = @itemId AND userid = @userId;";
// Remove item
sh.Execute(sql,
new[]
{
new SQLiteParameter("@tableName", tableName),
new SQLiteParameter("@itemId", itemId),
new SQLiteParameter("@userId", User.CurrentUserId)
});
}
}
}
/// <summary>
/// Recommends the item.
/// </summary>
/// <param name="itemId">The item identifier.</param>
/// <param name="userId">The user identifier.</param>
public static void RecommendItem(int itemId, int userId)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Save item details
Dictionary <string, object> recommendationData = new Dictionary <string, object>();
recommendationData["userId"] = userId;
recommendationData["fromId"] = User.CurrentUserId;
recommendationData["itemId"] = itemId;
sh.Insert("recommendation", recommendationData);
Utilities.ShowMessage("Success", "Sending recommendation complete.");
}
}
}
/// <summary>
/// Gets the recommendations.
/// </summary>
private void GetRecommendations()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the UserList datatable
RecommendationsDataTable =
sh.Select(
"SELECT recommendation.itemId, item.title, item.year, type.type, recommendation.date, user.username 'Sent by' " +
"FROM recommendation " + "JOIN user ON recommendation.fromId = user.id " +
"JOIN item ON recommendation.itemid = item.id " +
"JOIN type ON item.typeId = type.id " + "WHERE userid = @userid",
new[] { new SQLiteParameter("@userid", User.CurrentUserId) });
DataGridRecs.ItemsSource = RecommendationsDataTable.DefaultView;
// Set property to true if item has reviews
int noOfRecommendations = RecommendationsDataTable.Rows.Count;
HasRecommendations = noOfRecommendations > 0;
// If no reviews, hide table and show message
if ( !HasRecommendations )
{
DataGridRecs.Visibility = Visibility.Collapsed;
LblNoRecs.Visibility = Visibility.Visible;
}
}
}
}
/// <summary>
/// Saves the theme to the users settins table
/// </summary>
/// <param name="themeAccent">The theme accent.</param>
/// <param name="themeBg">The theme bg.</param>
public static void SetTheme(string themeAccent, string themeBg)
{
// Save the users theme to database
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Setup database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Upload data to database
Dictionary <string, object> settings = new Dictionary <string, object>();
settings["themeAccent"] = themeAccent;
settings["themeBG"] = themeBg;
sh.Update("settings", settings, "id", CurrentUserId);
// Apply theme
UseTheme(themeAccent, themeBg);
}
}
}
/// <summary>
/// Queries the database for list of users and stores it in the UserList property
/// </summary>
public static void GetUsers()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the UserList datatable
UserList =
sh.Select(
"SELECT id, username, registerdate " + "FROM user " +
"WHERE id != @currentuser;",
new[] { new SQLiteParameter("@currentuser", CurrentUserId) });
conn.Close();
}
}
}
/// <summary>
/// Show the users media stats
/// </summary>
/// <param name="userid">The userid.</param>
private void ShowStats(int userid)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Setup database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Query database for user details
DataTable dt =
sh.Select(
"SELECT type.type, ROUND(SUM(item.length), 2) 'Total Length', SUM(progress.Progress) 'Number Watched', " +
" ROUND(SUM(item.length * progress.Progress)/60.0/24.0, 2) 'Days Total' " +
"FROM progress " + "JOIN item ON progress.itemId = item.id " +
"JOIN type ON item.typeId = type.id " +
"WHERE progress.userId = @user AND progress.statusId IN (0, 1, 2) " +
"GROUP BY item.typeId", new[] { new SQLiteParameter("@user", userid) });
// If data is returned, display it
if ( dt.Rows.Count == 0 ) return;
float movieTime = 0, movieNumber = 0;
float seriesTime = 0, seriesNumber = 0;
float bookTime = 0, bookNumber = 0;
for ( int i = 0; i < dt.Rows.Count; i++ )
{
string type = dt.Rows[i].ItemArray[0].ToString();
// To parse the data without it being rounded
NumberFormatInfo decimalpoint = CultureInfo.InvariantCulture.NumberFormat;
// Parse the contents to appropriate variables
switch ( type )
{
case "Movie":
movieTime = float.Parse(dt.Rows[i].ItemArray[3].ToString(),
decimalpoint);
movieNumber = float.Parse(
dt.Rows[i].ItemArray[2].ToString(), decimalpoint);
break;
case "Series":
seriesTime = float.Parse(
dt.Rows[i].ItemArray[3].ToString(), decimalpoint);
seriesNumber =
float.Parse(dt.Rows[i].ItemArray[2].ToString(),
decimalpoint);
break;
case "Book":
bookTime = float.Parse(dt.Rows[i].ItemArray[3].ToString(),
decimalpoint);
bookNumber = float.Parse(
dt.Rows[i].ItemArray[2].ToString(), decimalpoint);
break;
}
}
// Display the contents
LblStatMovie.Content =
String.Format("{0} Movies Added, spending {1} days in total.", movieNumber,
movieTime);
LblStatSeries.Content =
String.Format("{0} Episodes Added, spending {1} days in total.",
seriesNumber, seriesTime);
LblStatBook.Content = String.Format("{0} Books Added, at {1} pages in total.",
bookNumber, bookTime);
}
}
}
