public static AccountInfo FindBackChecked(string account, string key)
{
string sql = string.Format("select * from UsersTable where Account ='{0}' and Key='{1}'", account,
Encryption.EncryptBase64(key));
using (SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql))
{
if (reader == null)
{
return(null);
}
int result = 0;
while (reader.Read() && result < 1)
{
result++;
_account.ID = string.IsNullOrEmpty(reader["ID"].ToString())
? 0
: int.Parse(reader["ID"].ToString());
_account.Person = reader["Person"].ToString();
_account.Account = reader["Account"].ToString();
_account.Password = reader["Password"].ToString();
_account.Key = reader["Key"].ToString();
_account.Power = string.IsNullOrEmpty(reader["Power"].ToString())
? 0
: int.Parse(reader["Power"].ToString());
}
if (result != 1)
{
return(null);
}
}
return(_account);
}
private void delete_toolStripMenuItem_Click(object sender, EventArgs e)
{
if (XtraMessageBox.Show("确认删除选中的缴费记录吗？删除后数据不可恢复，请谨慎操作。", "消息", MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == DialogResult.No)
{
return;
}
VerificationForm form = new VerificationForm();
form.ShowDialog();
if (!form.Result)
{
return;
}
int[] selectRows = gridView1.GetSelectedRows();
int[] rowsID = new int[selectRows.Length];
for (int i = 0; i < selectRows.Length; i++)
{
rowsID[i] = (gridView1.GetRow(selectRows[i]) as PayRecordInfo).ID;
}
int result = SQLiteControl.RealyDelete("PayRecordTable", "ID", rowsID);
if (result <= 0)
{
XtraMessageBox.Show("删除选中的缴费记录失败", "消息", MessageBoxButtons.YesNo, MessageBoxIcon.Error);
return;
}
Query();
}
/// <summary>
/// 账号密码完全验证
/// </summary>
/// <param name="Account"></param>
/// <returns></returns>
public static bool AccountFullChecked(string Account, string Password)
{
string sql = string.Format("select * from UsersTable where Account='{0}' and Password='******'", Account, Password);
using (SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql))
{
if (reader == null)
{
return(false);
}
int result = 0;
while (reader.Read())
{
++result;
_account.ID = string.IsNullOrEmpty(reader["ID"].ToString())
? 0
: int.Parse(reader["ID"].ToString());
_account.Person = reader["Person"].ToString();
_account.Account = reader["Account"].ToString();
_account.Password = reader["Password"].ToString();
_account.Key = reader["Key"].ToString();
_account.Power = string.IsNullOrEmpty(reader["Power"].ToString())
? 0
: int.Parse(reader["Power"].ToString());
}
if (result == 1)
{
return(true);
}
}
return(false);
}
private static async Task CreateTable(SQLiteControl command)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"CREATE TABLE IF NOT EXISTS t_nico_temporary (");
sql.AppendLine($" video_id TEXT NOT NULL,");
sql.AppendLine($" tick INTEGER NOT NULL,");
sql.AppendLine($"PRIMARY KEY (video_id)");
sql.AppendLine($")");
await command.ExecuteNonQueryAsync(sql.ToString());
sql.Clear();
sql.AppendLine($"CREATE TABLE IF NOT EXISTS t_nico_history (");
sql.AppendLine($" video_id TEXT NOT NULL,");
sql.AppendLine($" tick INTEGER NOT NULL,");
sql.AppendLine($"PRIMARY KEY (video_id, tick)");
sql.AppendLine($")");
await command.ExecuteNonQueryAsync(sql.ToString());
sql.Clear();
sql.AppendLine($"CREATE TABLE IF NOT EXISTS t_nico_favorite (");
sql.AppendLine($" mylist_id TEXT NOT NULL,");
sql.AppendLine($" tick INTEGER NOT NULL,");
sql.AppendLine($"PRIMARY KEY (mylist_id)");
sql.AppendLine($")");
await command.ExecuteNonQueryAsync(sql.ToString());
}
public static async Task Initialize(SQLiteControl command)
{
Temporaries = new StatefulModel.SortedObservableCollection <TNicoTemporary, DateTime>(
await command.SelectNicoTemporary(), x => x.Date, true
);
Histories = new StatefulModel.SortedObservableCollection <VNicoHistory, DateTime>(
await command.SelectNicoHistory(), x => x.Date, true
);
Favorites = new StatefulModel.SortedObservableCollection <TNicoFavorite, DateTime>(
await command.SelectNicoFavorite(), x => x.Date, true
);
var combo = XDocument.Load(CoreUtil.RelativePathToAbsolutePath(NicoComboPath)).Root;
Combos = combo.Descendants("combo")
.SelectMany(xml =>
{
return(xml.Descendants("item")
.Select(tag => new ComboboxItemModel(
(string)xml.Attribute("group"),
(string)tag.Attribute("value"),
(string)tag.Attribute("display")
)));
})
.ToArray();
}
public static int AddRecord(StudentInfo stu)
{
string sql = string.Format("insert into CheckRecordTable values(NULL, {0},'{1}', '{2}', {3}, '{4}')",
AccountInfo.AccountSession.ID, AccountInfo.AccountSession.Person, DateTime.Now.ToString("yyyy-MM-dd"), stu.ID, stu.Name);
return(SQLiteControl.ExecuteNonQuery(sql));
}
public LoginForm()
{
InitializeComponent();
//连接Sqlite数据库
if (!SQLiteControl.ConnectToDatabase(@"./DataBase/database.sqlite"))
{
XtraMessageBoxArgs args =
ControlHelper.XtraMessageBoxArgs("消息", "连接数据库失败", new DialogResult[] { DialogResult.Yes });
XtraMessageBox.Show(args);
Application.Exit();
}
account_textEdit.Focus();
//每次登录前Copy一份DB
FileInfo file = new FileInfo(@"./DataBase/database.sqlite");
if (!File.Exists(@"./DataBase/" + DateTime.Today.ToString("yyyy-MM-dd") + ".sqlite"))
{
file = file.CopyTo(@"./DataBase/" + DateTime.Today.ToString("yyyy-MM-dd") + ".sqlite");
}
//解密文件
//else
//{
// file = new FileInfo(@"./DataBase/" + DateTime.Today.ToString("yyyy-MM-dd") + ".sqlite");
// file.Decrypt();
//}
PreLoad();
login_simpleButton.Focus();
}
/// <summary>
/// 创建学生
/// </summary>
/// <param name="stu"></param>
/// <returns></returns>
public static int CreateStudentInfo(StudentInfo stu)
{
string sql = string.Format("insert into StudentsTable values(NULL,'{0}','{1}','{2}','{3}','{4}','{5}',{6},{7},{8},{9},'{10}',{11}, {12})", stu.SerialNum,
stu.Name, stu.Sex, stu.Parents, stu.Contacts, stu.Address, stu.Tuition, stu.Remaining, stu.ClassHours,
stu.Pay ? 1 : 0, stu.LastPayDate.ToString("yyyy-MM-dd"), stu.NotPay, 0);
return(SQLiteControl.ExecuteNonQuery(sql));
}
public static int Updata(StudentInfo stu)
{
string sql = string.Format("update StudentsTable set Name='{0}', Sex='{1}',Parents='{2}',Contacts='{3}',Address='{4}',Tuition={5},Remaining={6},ClassHours={7},Pay={8},LastPayDate='{9}',NotPay={10} where ID={11}",
stu.Name, stu.Sex, stu.Parents, stu.Contacts, stu.Address, stu.Tuition, stu.Remaining, stu.ClassHours,
stu.Pay ? 1 : 0, stu.LastPayDate.ToString("yyyy-MM-dd"), stu.NotPay, stu.ID);
return(SQLiteControl.ExecuteNonQuery(sql));
}
public static int CreateRecord(PayRecordInfo payRecord)
{
string sql = string.Format("insert into PayRecordTable values(NULL,{0},'{1}','{2}',{3},'{4}','{5}',{6},{7},{8},{9},{10},'{11}')", payRecord.StudentID,
payRecord.StudentName, payRecord.LastDateTime.ToString("yyyy-MM-dd"), payRecord.OperationID,
payRecord.OperationPerson, payRecord.Remark, payRecord.Tuition, payRecord.Remaining,
payRecord.ClassHours, payRecord.Pay ? 1 : 0, payRecord.NotPay, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
return(SQLiteControl.ExecuteNonQuery(sql));
}
/// <summary>
/// 修改班级信息
/// </summary>
/// <param name="arg"></param>
/// <returns></returns>
public static int ModifyGroup(GroupClassInfo arg)
{
string sql =
string.Format(
"update GroupClassTable set GroupName = '{0}', ClassTeacher = '{1}' where ID ={2}",
arg.GroupName, arg.ClassTeacher, arg.ID);
return(SQLiteControl.ExecuteNonQuery(sql));
}
/// <summary>
/// 创建班级
/// </summary>
/// <param name="arg"></param>
/// <returns></returns>
public static int CreateGroup(GroupClassInfo arg)
{
string sql =
string.Format("insert into GroupClassTable values(NULL, '{0}', '{1}','{2}','{3}','{4}', 0)",
arg.GroupName, arg.CreatePerson, arg.CreateDate.ToString("yyyy-MM-dd"), arg.PersonID,
arg.ClassTeacher);
return(SQLiteControl.ExecuteNonQuery(sql));
}
public static Task <SQLiteControl> GetControl()
{
var path = @"lib\database.sqlite3";
var work = System.AppDomain.CurrentDomain.BaseDirectory.TrimEnd('\\');
var full = Path.Combine(work, path);
Directory.CreateDirectory(Path.GetDirectoryName(full));
return(SQLiteControl.CreateAsync(path));
}
public static List <AccountInfo> GetAll()
{
string sql = "select * from UsersTable where Power >= 0";
using (SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql))
{
List <AccountInfo> accounts = Looper(reader);
return(accounts);
}
}
public static int Modify(AccountInfo account)
{
return(SQLiteControl.SQLiteUpDate("UsersTable", "ID", new int[] { account.ID },
new string[] { "Person", "Account", "Password", "Key" },
new string[]
{
account.Person, account.Account, Encryption.EncryptBase64(account.Password),
Encryption.EncryptBase64(account.Key)
}));
}
/// <summary>
/// 真正的删除
/// </summary>
/// <param name="IDs"></param>
/// <returns></returns>
public static int RealyDelete(int[] IDs)
{
//删除GroupClassTable的内容
int result = SQLiteControl.RealyDelete("GroupClassTable", "ID", IDs);
//删除关联的StudentsCheckinTable内容
if (result > 0)
{
result = SQLiteControl.RealyDelete("StudentsCheckinTable", "GroupID", IDs);
}
return(result);
}
public static async Task <IEnumerable <TNicoFavorite> > SelectNicoFavorite(this SQLiteControl command)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"SELECT mylist_id, tick FROM t_nico_favorite ORDER BY tick DESC");
using (var reader = await command.ExecuteReaderAsync(sql.ToString()))
{
return(await reader.GetRows(r => new TNicoFavorite(r)));
}
}
public static async Task <IEnumerable <VNicoHistory> > SelectNicoHistory(this SQLiteControl command)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"SELECT video_id, tick, cnt FROM v_nico_history ORDER BY tick DESC");
using (var reader = await command.ExecuteReaderAsync(sql.ToString()))
{
return(await reader.GetRows(r => new VNicoHistory(r)));
}
}
public static async Task DeleteNicoHistory(this SQLiteControl command, string videoid)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"DELETE FROM t_nico_history WHERE video_id = ?");
var parameters = new[]
{
Sqlite3Util.CreateParameter(DbType.String, videoid),
};
await command.ExecuteNonQueryAsync(sql.ToString(), parameters);
}
public static List <PayRecordInfo> SimpleQuery(int pageIndex, int pageSize, string key, out int total)
{
string sql = string.Format("select * from PayRecordTable");
SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql);
total = Looper(reader).Count;
sql = string.Format("select * from PayRecordTable where (StudentName like '%{0}%' " +
"or OperationPerson like '%{0}%') " +
"limit {1} offset {2}", key, pageSize, (pageIndex - 1) * pageSize);
reader = SQLiteControl.ExecuteReader(sql);
var result = Looper(reader);
reader.Close();
return(result);
}
public static bool RegisterAccount(AccountInfo account)
{
string sql =
string.Format(
"insert into UsersTable values(NULL, '{0}', '{1}', '{2}', '{3}', {4})",
account.Person, account.Account, account.Password, account.Key, account.Power);
int result = SQLiteControl.ExecuteNonQuery(sql);
if (result != 1)
{
return(false);
}
return(true);
}
private static async Task CreateView(SQLiteControl command)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"CREATE VIEW IF NOT EXISTS v_nico_history AS");
sql.AppendLine($"SELECT");
sql.AppendLine($" video_id,");
sql.AppendLine($" MAX(tick) tick,");
sql.AppendLine($" COUNT(video_id) cnt");
sql.AppendLine($"FROM");
sql.AppendLine($" t_nico_history");
sql.AppendLine($"GROUP BY");
sql.AppendLine($" video_id");
await command.ExecuteNonQueryAsync(sql.ToString());
}
/// <summary>
/// 为小组添加成员
/// </summary>
/// <returns></returns>
public static int AddMembers(int GroupID, int[] StudentsID)
{
if (StudentsID.Length <= 0)
{
return(-1);
}
string sql = string.Format("insert into StudentsCheckinTable values(NULL, {0}, {1}", GroupID, StudentsID[0]);
for (int i = 1; i < StudentsID.Length; i++)
{
sql += string.Format("),(NULL, {0}, {1}", GroupID, StudentsID[i]);
}
sql += ")";
return(SQLiteControl.ExecuteNonQuery(sql));
}
public static bool SimpleDelete(int[] id)
{
string sql = string.Format("delete from UsersTable where ID in ({0}", id[0]);
for (int i = 1; i < id.Length; i++)
{
sql += string.Format(", {0}", id[i]);
}
sql += ")";
int result = SQLiteControl.ExecuteNonQuery(sql);
if (result > 0)
{
return(true);
}
return(false);
}
/// <summary>
/// 简单的查询
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public static List <AccountInfo> SimpleQuery(int pageIndex, int pageSize, string key, out int total)
{
string sql = "select * from UsersTable where Power >= 0";
SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql);
List <AccountInfo> accounts = Looper(reader);
total = accounts == null ? 0 : accounts.Count;
sql =
string.Format(
"select * from UsersTable where (Person like '%{0}%' or Account like '%{0}%') and Power >= 0 limit {1} offset {2}",
key, pageSize, (pageIndex - 1) * pageSize);
reader = SQLiteControl.ExecuteReader(sql);
accounts.Clear();
accounts = Looper(reader);
reader.Dispose();
return(accounts);
}
/// <summary>
/// 查询被删除的学生信息
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="key"></param>
/// <param name="total"></param>
/// <returns></returns>
public static List <StudentInfo> RealyQuerry(int pageIndex, int pageSize, string key, out int total)
{
string sql = string.Format("select * from StudentsTable where IsDelete = 1");
SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql);
total = Looper(reader).Count;
reader.Close();
sql = string.Format(
"select * from StudentsTable where (SerialNum like '%{0}%' " +
"or Name like '%{0}%' or Parents like '%{0}%' " +
"or Address like '%{0}%') " +
"and IsDelete = 1 " +
"limit {1} offset {2}",
key, pageSize, (pageIndex - 1) * pageSize);
reader = SQLiteControl.ExecuteReader(sql);
return(Looper(reader));
}
/// <summary>
/// 查询
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="key"></param>
/// <param name="total"></param>
/// <returns></returns>
public static List <GroupClassInfo> SimpleQuery(int pageIndex, int pageSize, string key, out int total)
{
string sql = string.Format("select * from GroupClassTable where IsDelete is NULL or IsDelete = 0");
SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql);
total = Looper(reader).Count;
sql = string.Format(
"select * from GroupClassTable where (GroupName like '%{0}%' " +
"or ClassTeacher like '%{0}%') " +
"and (IsDelete is NULL or IsDelete = 0) " +
"limit {1} offset {2}",
key, pageSize, (pageIndex - 1) * pageSize);
reader = SQLiteControl.ExecuteReader(sql);
var result = Looper(reader);
reader.Close();
return(result);
}
public static async Task MergeNicoHistory(this SQLiteControl command, string videoid)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"INSERT OR REPLACE INTO t_nico_history (");
sql.AppendLine($" video_id, tick");
sql.AppendLine($")");
sql.AppendLine($"VALUES (");
sql.AppendLine($" ?, ?");
sql.AppendLine($")");
var parameters = new[]
{
Sqlite3Util.CreateParameter(DbType.String, videoid),
Sqlite3Util.CreateParameter(DbType.Int64, DateTime.Now.Ticks),
};
await command.ExecuteNonQueryAsync(sql.ToString(), parameters);
}
public static async Task MergeNicoFavorite(this SQLiteControl command, string mylistid, DateTime date)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"INSERT OR REPLACE INTO t_nico_favorite (");
sql.AppendLine($" mylist_id, tick");
sql.AppendLine($")");
sql.AppendLine($"VALUES (");
sql.AppendLine($" ?, ?");
sql.AppendLine($")");
var parameters = new[]
{
Sqlite3Util.CreateParameter(DbType.String, mylistid),
Sqlite3Util.CreateParameter(DbType.Int64, date.Ticks),
};
await command.ExecuteNonQueryAsync(sql.ToString(), parameters);
}
/// <summary>
/// 账号检查
/// </summary>
/// <param name="Account"></param>
/// <returns></returns>
public static bool AccountChecked(string Account)
{
string sql = string.Format("select * from UsersTable where Account='{0}'", Account);
using (SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql))
{
if (reader == null)
{
return(false);
}
int result = 0;
while (reader.Read())
{
++result;
}
if (result == 1)
{
return(true);
}
}
return(false);
}
