public override void OnCreate()
{
base.OnCreate();
ServicePointManager.ServerCertificateValidationCallback += new RemoteCertificateValidationCallback((sender, certificate, chain, policyErrors) => { return true; });
AndroidEnvironment.UnhandledExceptionRaiser += AndroidEnvironment_UnhandledExceptionRaiser;
string libraryPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
var path = Path.Combine(libraryPath, sqliteFilename);
if(!initTablesInNewDB)
copyInitialDB(path);
SqlLiteHelper sqlConnection = new SqlLiteHelper(path);
SqlLiteInstance sqlInstance = new SqlLiteInstance(sqlConnection);
if(initTablesInNewDB)
sqlInstance.InitTables();
}
/// <summary>
/// 分页查询临时信息存储表
/// </summary>
/// <param name="pageSize">每页多少条数据</param>
/// <param name="start">排除多少条数据</param>
/// <param name="hash">筛选条件</param>
/// <param name="total">总共多少条</param>
/// <returns>数据集合</returns>
public List <TempData> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere)
{
List <TempData> list = new List <TempData>();
sqlWhere = "1=1 " + sqlWhere;
#region 查询条件
if (hash != null)
{
if (hash["Id"] != null)
{
sqlWhere += string.Format(" and Id='{0}'", hash["Id"]);
}
if (hash["Email"] != null)
{
sqlWhere += string.Format(" and Email='{0}'", hash["Email"]);
}
if (hash["Expires"] != null)
{
sqlWhere += string.Format(" and Expires='{0}'", hash["Expires"]);
}
if (hash["CreateTime"] != null)
{
sqlWhere += string.Format(" and Create_Time='{0}'", hash["CreateTime"]);
}
}
#endregion
DataTable dt = SqlLiteHelper.GetTable("Temp_Data",
"Id,Email,Expires,Create_Time",
pageSize, start, sqlWhere, "Id", "asc", out total);
for (int i = 0; i < dt.Rows.Count; i++)
{
TempData index = new TempData();
index.Id = dt.Rows[i]["Id"].GetString();
index.Email = dt.Rows[i]["Email"].GetString();
index.Expires = dt.Rows[i]["Expires"].GetDateTime();
index.CreateTime = dt.Rows[i]["Create_Time"].GetDateTime();
list.Add(index);
}
return(list);
}
/// <summary>
/// 增加收入记录表
/// </summary>
/// <param name="index">收入记录表对象</param>
/// <returns></returns>
public int Add(Income index)
{
string sql = string.Format("insert into Income(Id,TIME,Price,Note,FamilyIncome,IsMark,CusGroup,Create_By,Create_Time,UpDate_By,UpDate_Time) values(@Id,@TIME,@Price,@Note,@FamilyIncome,@IsMark,@CusGroup,@Create_By,@Create_Time,@UpDate_By,@UpDate_Time)");
SQLiteParameter[] parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@TIME", index.Time),
new SQLiteParameter("@Price", index.Price),
new SQLiteParameter("@Note", index.Note),
new SQLiteParameter("@FamilyIncome", index.FamilyIncome),
new SQLiteParameter("@IsMark", index.IsMark),
new SQLiteParameter("@CusGroup", index.CusGroup),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime)
};
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, parm));
}
/// <summary>
/// 修改收入记录表
/// </summary>
/// <param name="index">收入记录表对象</param>
/// <returns></returns>
public int Update(Income index)
{
string sql = "update Income set Id=@Id,TIME=@TIME,Price=@Price,Note=@Note,FamilyIncome=@FamilyIncome,IsMark=@IsMark,Create_By=@Create_By,Create_Time=@Create_Time,UpDate_By=@UpDate_By,UpDate_Time=@UpDate_Time,CusGroup=@CusGroup where Id=@Id";
SQLiteParameter[] parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@TIME", index.Time),
new SQLiteParameter("@Price", index.Price),
new SQLiteParameter("@Note", index.Note),
new SQLiteParameter("@FamilyIncome", index.FamilyIncome),
new SQLiteParameter("@IsMark", index.IsMark),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime),
new SQLiteParameter("@CusGroup", index.CusGroup)
};
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, parm));
}
/// <summary>
/// 根据编号查询银行卡操作记录表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public BankCard Select(string Id)
{
String sql = "select * from Bank_Card where Id=@Id";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@Id", Id),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
List <BankCard> list = Life.Model.Common <BankCard> .ConvertToList(dt);
if (list.Count > 0)
{
return(list[0]);
}
else
{
return(null);
}
}
/// <summary>
/// 批量新增角色模块对应表
/// </summary>
/// <param name="list">角色模块对应表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <RoleToModule> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format("insert into Role_To_Module(Id,Role_Id,Module_Id) values(@Id,@Role_Id,@Module_Id)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@Role_Id", index.RoleId),
new SQLiteParameter("@Module_Id", index.ModuleId)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增角色表
/// </summary>
/// <param name="list">角色表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <Roles> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format("insert into Roles(Role_Id,Role_Name,Notes) values(@Role_Id,@Role_Name,@Notes)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@Role_Id", index.RoleId),
new SQLiteParameter("@Role_Name", index.RoleName),
new SQLiteParameter("@Notes", index.Notes)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 查询生活费的所有消费名称
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public List <LifingCost> GetReasons(string key)
{
List <LifingCost> list = new List <LifingCost>();
string sql = "select Reason from Lifing_Cost group by Reason having Reason like '%@key%'";
SQLiteParameter[] parm = new SQLiteParameter[] {
new SQLiteParameter("@key", key)
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
for (int i = 0; i < dt.Rows.Count; i++)
{
LifingCost index = new LifingCost();
index.Reason = dt.Rows[i]["Reason"].GetString();
list.Add(index);
}
return(list);
}
public static DataTable TrattamentiList(int idConsulto)
{
var sb = new StringBuilder();
sb.Append("SELECT ");
sb.Append("ID,");
sb.Append("data,");
sb.Append("substr(descrizione,1,100) as descrizione");
sb.Append(" FROM ");
sb.Append("trattamento");
sb.Append(" WHERE ");
sb.Append("id_consulto = " + idConsulto);
sb.Append(" ORDER BY ");
sb.Append("data ASC");
var dt = SqlLiteHelper.GetDataTable(sb.ToString());
return(dt);
}
internal void creatTable(SqlLiteHelper sqlLiteHelper)
{
//Id,Name,Describe,Status,Author,DefaultController,DefaultAction,PVersion,MenuShow,Icon
string sql = @"CREATE TABLE `t_plugin` (
`Id` INTEGER NOT NULL,
`Name` TEXT NOT NULL,
`Status` INTEGER NOT NULL DEFAULT 0,
`Describe` TEXT NOT NULL,
`Author` TEXT NOT NULL,
`DefaultController` TEXT NOT NULL,
`DefaultAction` TEXT NOT NULL,
`PVersion` INTEGER,
`MenuShow` INTEGER,
`Icon` TEXT,
PRIMARY KEY(Id)
);";
sqlLiteHelper.RunSQL(sql);
}
/// <summary>
/// 汇总生活费类型
/// </summary>
/// <param name="time">时间,如:'2014-01','2014-12'</param>
/// <param name="beginTime">开始统计的时间</param>
/// <param name="endTime">结束统计的时间</param>
/// <param name="isMark">特殊标识</param>
/// <param name="family">家庭内收支</param>
/// <param name="costTypeId">消费类型编号</param>
/// <param name="userId">用户编号</param>
/// <returns></returns>
public DataTable GetCollectionType(string time, string beginTime, string endTime, bool?isMark, bool?family, string costTypeIds, string userId)
{
String costSqlWhere = String.Empty;
#region 组装条件
if (!String.IsNullOrEmpty(time))
{
costSqlWhere += String.Format(" and strftime('%Y-%m',Time) in ({0})", time);
}
if (!String.IsNullOrEmpty(beginTime))
{
costSqlWhere += String.Format(" and time>='{0}'", beginTime);
}
if (!String.IsNullOrEmpty(endTime))
{
costSqlWhere += String.Format(" and time<datetime('{0}','+1 hour')", endTime);
}
if (isMark != null)
{
costSqlWhere += String.Format(" and isMark='{0}'", isMark.GetInt32());
}
if (family != null)
{
costSqlWhere += String.Format(" and FamilyPay='{0}'", family.GetInt32());
}
if (!String.IsNullOrEmpty(costTypeIds))
{
costSqlWhere += String.Format(" and Cost_Type_Id in({0})", costTypeIds);
}
//不是管理员
if (userId != "-1")
{
costSqlWhere += String.Format(" and Create_By='{0}'", userId);
}
#endregion
String sql = String.Format(@"select COST_TYPE_NAME costTypeName,SUM(price) price
from V_LIFING_COST where 1=1 {0}
group by COST_TYPE_NAME", costSqlWhere);
return(SqlLiteHelper.GetTable(sql, CommandType.Text));
}
/// <summary>
/// 修改银行卡操作记录表
/// </summary>
/// <param name="index">银行卡操作记录表对象</param>
/// <returns></returns>
public int Update(BankCard index)
{
string sql = "update Bank_Card set Id=@Id,TIME=@TIME,Price=@Price,Save_Type=@Save_Type,Balance=@Balance,Bank_Type=@Bank_Type,Note=@Note,Create_By=@Create_By,Create_Time=@Create_Time,UpDate_By=@UpDate_By,UpDate_Time=@UpDate_Time,ImgUrl=@ImgUrl where Id=@Id";
SQLiteParameter[] parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@TIME", index.Time),
new SQLiteParameter("@Price", index.Price),
new SQLiteParameter("@Save_Type", index.SaveType),
new SQLiteParameter("@Balance", index.Balance),
new SQLiteParameter("@Bank_Type", index.BankType),
new SQLiteParameter("@Note", index.Note),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime),
new SQLiteParameter("@ImgUrl", index.ImgUrl)
};
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, parm));
}
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int Update(List <TempData> list)
{
String sql = String.Empty;
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = "update Temp_Data set Id=@Id,Email=@Email,Expires=@Expires,Create_Time=@Create_Time where Id=@Id";
parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@Email", index.Email),
new SQLiteParameter("@Expires", index.Expires),
new SQLiteParameter("@Create_Time", index.CreateTime)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增临时信息存储表
/// </summary>
/// <param name="list">临时信息存储表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <TempData> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format("insert into Temp_Data(Id,Email,Expires,Create_Time) values(@Id,@Email,@Expires,@Create_Time)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@Email", index.Email),
new SQLiteParameter("@Expires", index.Expires),
new SQLiteParameter("@Create_Time", index.CreateTime)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 根据编号查询收入记录表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public Student Select(string Id)
{
String sql = "select * from Student where Id=@Id";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@Id", Id),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
List <Student> list = Life.Model.Common <Student> .ConvertToList(dt);
if (list.Count > 0)
{
return(list[0]);
}
}
return(null);
}
/// <summary>
/// 修改系统配置
/// </summary>
/// <param name="index">系统配置对象</param>
/// <returns></returns>
public int Update(SysConfig index)
{
string sql = "update Sys_Config set Id=@Id,Sys_Key=@Sys_Key,Name=@Name,Sys_Value=@Sys_Value,Remark=@Remark,Group_No=@Group_No,Is_Visible=@Is_Visible,Order_Id=@Order_Id,Create_By=@Create_By,Create_Time=@Create_Time,UpDate_By=@UpDate_By,UpDate_Time=@UpDate_Time where Id=@Id";
SQLiteParameter[] parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@Sys_Key", index.SysKey),
new SQLiteParameter("@Name", index.Name),
new SQLiteParameter("@Sys_Value", index.SysValue),
new SQLiteParameter("@Remark", index.Remark),
new SQLiteParameter("@Group_No", index.GroupNo),
new SQLiteParameter("@Is_Visible", index.IsVisible),
new SQLiteParameter("@Order_Id", index.OrderId),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime)
};
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, parm));
}
/// <summary>
/// 增加系统配置
/// </summary>
/// <param name="index">系统配置对象</param>
/// <returns></returns>
public int Add(SysConfig index)
{
string sql = string.Format("insert into Sys_Config(Id,Sys_Key,Name,Sys_Value,Remark,Group_No,Is_Visible,Order_Id,Create_By,Create_Time,UpDate_By,UpDate_Time) values(@Id,@Sys_Key,@Name,@Sys_Value,@Remark,@Group_No,@Is_Visible,@Order_Id,@Create_By,@Create_Time,@UpDate_By,@UpDate_Time)");
SQLiteParameter[] parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@Sys_Key", index.SysKey),
new SQLiteParameter("@Name", index.Name),
new SQLiteParameter("@Sys_Value", index.SysValue),
new SQLiteParameter("@Remark", index.Remark),
new SQLiteParameter("@Group_No", index.GroupNo),
new SQLiteParameter("@Is_Visible", index.IsVisible),
new SQLiteParameter("@Order_Id", index.OrderId),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime)
};
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, parm));
}
/// <summary>获得插件
/// </summary>
/// <returns></returns>
public bool ExsitPlugin(string Name, int Version)
{
string sql = "select Id from t_plugin where Name = @Name and PVersion=@PVersion";
SQLiteParameter[] para = new SQLiteParameter[]
{
new SQLiteParameter("@PVersion", Version),
new SQLiteParameter("@Name", Name)
};
DataTable dataTable = new SqlLiteHelper().GetDataTable(sql, para);
if (dataTable != null && dataTable.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <returns></returns>
public List <TempData> Select(HashTableExp hash, String sqlWhere)
{
List <TempData> list = new List <TempData>();
string sql = "select Id,Email,Expires,Create_Time from Temp_Data where 1=1 ";
#region 查询条件
if (hash != null)
{
if (hash["Id"] != null)
{
sql += string.Format(" and Id='{0}'", hash["Id"]);
}
if (hash["Email"] != null)
{
sql += string.Format(" and Email='{0}'", hash["Email"]);
}
if (hash["Expires"] != null)
{
sql += string.Format(" and Expires='{0}'", hash["Expires"]);
}
if (hash["CreateTime"] != null)
{
sql += string.Format(" and Create_Time='{0}'", hash["CreateTime"]);
}
}
#endregion
sql += sqlWhere;
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
for (int i = 0; i < dt.Rows.Count; i++)
{
TempData index = new TempData();
index.Id = dt.Rows[i]["Id"].GetString();
index.Email = dt.Rows[i]["Email"].GetString();
index.Expires = dt.Rows[i]["Expires"].GetDateTime();
index.CreateTime = dt.Rows[i]["Create_Time"].GetDateTime();
list.Add(index);
}
return(list);
}
/// <summary>
/// 按条件删除数据
/// </summary>
/// <returns></returns>
public int Delete(HashTableExp hash, String sqlWhere)
{
string sql = "delete from Student where 1=1 ";
#region 查询条件
if (hash != null)
{
if (hash["Id"] != null)
{
sql += string.Format(" and id='{0}'", hash["Id"]);
}
if (hash["Name"] != null)
{
sql += string.Format(" and name='{0}'", hash["Name"]);
}
if (hash["CardNo"] != null)
{
sql += string.Format(" and card_no='{0}'", hash["CardNo"]);
}
if (hash["BirthDay"] != null)
{
sql += string.Format(" and birth_day='{0}'", hash["BirthDay"]);
}
if (hash["Sex"] != null)
{
sql += string.Format(" and sex='{0}'", hash["Sex"]);
}
if (hash["Addr"] != null)
{
sql += string.Format(" and addr='{0}'", hash["Addr"]);
}
if (hash["Remark"] != null)
{
sql += string.Format(" and remark='{0}'", hash["Remark"]);
}
}
#endregion
sql += sqlWhere;
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, null));
}
private void WriteUpdateDbByPrivate()
{
SqlLiteHelper.ExecuteNonQuery("delete from menu_shortcuts ");
foreach (var t in DicClassic)
{
if (string.IsNullOrEmpty(t.Value))
{
continue;
}
string strUpdateDirectroy = "insert into menu_shortcuts(id,shortcut) values";
strUpdateDirectroy += "(" + t.Key + ",'" + t.Value + "')";
try
{
SqlLiteHelper.ExecuteNonQueryInThread(strUpdateDirectroy);
}
catch (Exception ex)
{
ex.ToString();
}
}
}
private void WriteUpdateDbByPrivate(int id)
{
SqlLiteHelper.ExecuteNonQuery("delete from menu_instances where id=" + id);
if (!DicInstances.ContainsKey(id))
{
return;
}
var t = DicInstances[id];
string strUpdateDirectroy = "insert into menu_instances(id,name,key,id_classic) values";
strUpdateDirectroy += "(" + t.Id + ",'" + t.Name + "','" + t.Key + "'," + t.IdClassic +
")";
try
{
SqlLiteHelper.ExecuteNonQueryInThread(strUpdateDirectroy);
}
catch (Exception ex)
{
ex.ToString();
}
}
/// <summary>
/// 增加银行卡操作记录表
/// 并自动计算余额
/// </summary>
/// <param name="index">银行卡操作记录表对象</param>
/// <returns></returns>
public int Add(BankCard index)
{
CalcBalance(index, true);
string sql = string.Format("insert into Bank_Card(Id,TIME,Price,Save_Type,Balance,Bank_Type,Note,Create_By,Create_Time,UpDate_By,UpDate_Time,ImgUrl) values(@Id,@TIME,@Price,@Save_Type,@Balance,@Bank_Type,@Note,@Create_By,@Create_Time,@UpDate_By,@UpDate_Time,@ImgUrl)");
SQLiteParameter[] parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@TIME", index.Time),
new SQLiteParameter("@Price", index.Price),
new SQLiteParameter("@Save_Type", index.SaveType),
new SQLiteParameter("@Balance", index.Balance),
new SQLiteParameter("@Bank_Type", index.BankType),
new SQLiteParameter("@Note", index.Note),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime),
new SQLiteParameter("@ImgUrl", index.ImgUrl)
};
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, parm));
}
public int GetPluginsCount(string searchName)
{
StringBuilder sql = new StringBuilder();
sql.Append("select count(*) as c from t_plugin");
if (!string.IsNullOrEmpty(searchName))
{
sql.Append(" where Name=@Name");
}
SQLiteParameter[] para = new SQLiteParameter[]
{
new SQLiteParameter("@Name", searchName)
};
SqlLiteHelper sqlLiteHelper = new SqlLiteHelper();
DataTable dataTable = sqlLiteHelper.GetDataTable(sql.ToString(), para);
return(int.Parse(dataTable.Rows[0]["c"].ToString()));
}
/// <summary>
/// 查询所有银行卡的最后一条数据 time格式：2015-01-02
/// </summary>
/// <returns></returns>
public List <VBankCard> SelectCalc(String time)
{
String sql = String.Empty;
List <VBankCard> list = new List <VBankCard>();
//查询所有银行卡
List <Diction> dics = new DLDiction().Select(new HashTableExp("ParentId", "1000100000"), null);
foreach (var item in dics)
{
//查询最近一次的记录
String sqlWhere = String.Format("Bank_Type='{0}'", item.Id);
if (!String.IsNullOrEmpty(time))
{
sqlWhere += String.Format(" and time<='{0}'", time);
}
sql = String.Format("select * from V_Bank_Card where {0} order by time desc,create_time desc limit 1 offset 0", sqlWhere);
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
list = Life.Model.Common <VBankCard> .ConvertToList(dt);
}
return(list);
}
private void WriteUpdateDbByPrivate(int id)
{
SqlLiteHelper.ExecuteNonQuery("delete from menu_shortcuts where id=" + id);
if (DicClassic.ContainsKey(id))
{
var t = DicClassic[id];
if (!string.IsNullOrEmpty(t))
{
string strUpdateDirectroy = "insert into menu_shortcuts(id,shortcut) values";
strUpdateDirectroy += "(" + id + ",'" + t + "')";
try
{
SqlLiteHelper.ExecuteNonQueryInThread(strUpdateDirectroy);
}
catch (Exception ex)
{
ex.ToString();
}
}
}
}
/// <summary>
/// 修改生活费操作管理
/// </summary>
/// <param name="index">生活费操作管理对象</param>
/// <returns></returns>
public int Update(LifingCost index)
{
string sql = "update Lifing_Cost set Id=@Id,TIME=@TIME,Reason=@Reason,Price=@Price,Cost_Type_Id=@Cost_Type_Id,Notes=@Notes,Img_Url=@Img_Url,Create_By=@Create_By,Create_Time=@Create_Time,UpDate_By=@UpDate_By,UpDate_Time=@UpDate_Time,IsMark=@IsMark,FamilyPay=@FamilyPay,CusGroup=@CusGroup where Id=@Id";
SQLiteParameter[] parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@TIME", index.Time),
new SQLiteParameter("@Reason", index.Reason),
new SQLiteParameter("@Price", index.Price),
new SQLiteParameter("@Cost_Type_Id", index.CostTypeId),
new SQLiteParameter("@Notes", index.Notes),
new SQLiteParameter("@Img_Url", index.ImgUrl),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime),
new SQLiteParameter("@IsMark", index.IsMark),
new SQLiteParameter("@FamilyPay", index.FamilyPay),
new SQLiteParameter("@CusGroup", index.CusGroup)
};
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, parm));
}
/// <summary>
/// 删除生活费操作管理
/// </summary>
/// <param name="id">编号的集合，如：1,2,3...</param>
/// <returns>影响的行数</returns>
public int Delete(string ids)
{
try
{
String id = "";
String[] temp = ids.Split(',');
foreach (var item in temp)
{
if (!String.IsNullOrEmpty(id))
{
id += ",";
}
id += String.Format("'{0}'", item);
}
String sql = "delete from Lifing_Cost where Id in (" + id + ")";
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, null));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <returns>数据集合</returns>
public List <Users> Select()
{
List <Users> list = new List <Users>();
String sql = "select Id,Login_Id,Login_Pwd,Name,Phone,Mail,Address,Age,Notes from Users";
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
Users index = new Users();
index.Id = dt.Rows[i]["Id"].ToString();
index.LoginId = dt.Rows[i]["Login_Id"].ToString();
index.LoginPwd = dt.Rows[i]["Login_Pwd"].ToString();
index.Name = dt.Rows[i]["Name"].ToString();
index.Phone = dt.Rows[i]["Phone"].ToString();
index.Mail = dt.Rows[i]["Mail"].ToString();
index.Address = dt.Rows[i]["Address"].ToString();
index.Age = dt.Rows[i]["Age"].GetInt32();
index.Notes = dt.Rows[i]["Notes"].ToString();
list.Add(index);
}
return(list);
}
public void Init()
{
db = Path.Combine(Utils.GetUserPath(), "alarm.db");
string sql = "SELECT COUNT(*) FROM sqlite_master where type = 'table' and name = 'record'";
object obj = SqlLiteHelper.ExecuteScalar(db, sql);
if (Convert.ToInt32(obj) == 0)
{
string createSql = "CREATE TABLE record(time varchar(50),field varchar(50),groupname varchar(50),comment varchar(50),state varchar(50))";
int res = SqlLiteHelper.ExecuteNonQuery(db, createSql);
}
foreach (Field field in ConfigData.allFields)
{
field.StateChanged += Field_StateChanged;
}
cdv3 = new CustomDataView();
cdv3.ContextMenuStrip = contextMenuStrip1;
cdv3.Font = new Font("微软雅黑", 15);
cdv3.Parent = this;
cdv3.Dock = DockStyle.Fill;
cdv3.CellFormatting += Cdv_CellFormatting;
cdv = new CustomDataView();
cdv.ContextMenuStrip = contextMenuStrip1;
cdv.Font = new Font("微软雅黑", 15);
cdv.Parent = this;
cdv.Dock = DockStyle.Fill;
dt = SqlLiteHelper.ExecuteReader(db, "SELECT * FROM record");
cdv.DataSource = dt;
cdv.Columns["time"].HeaderText = "时间";
cdv.Columns["field"].HeaderText = "监测项";
cdv.Columns["groupname"].HeaderText = "位置";
cdv.Columns["comment"].HeaderText = "备注";
cdv.Columns["state"].HeaderText = "状态";
cdv.CellFormatting += Cdv_CellFormatting;
cdv.BringToFront();
}
private void GetValueFromDb()
{
try
{
var ssss = Convert.ToInt32(SqlLiteHelper.ExecuteQuery(
"SELECT COUNT(*) as count FROM sqlite_master WHERE type='table' and name= 'login_setting'")
.Tables[0].Rows[0][0].ToString().Trim());
if (ssss < 1)
{
SqlLiteHelper.ExecuteQuery(
"CREATE TABLE 'login_setting' ('key' text, 'value' text)");
}
DataSet ds = SqlLiteHelper.ExecuteQuery("select * from login_setting", null);
if (ds == null)
{
return;
}
int mCount = ds.Tables[0].Rows.Count;
for (int i = 0; i < mCount; i++)
{
try
{
string key = ds.Tables[0].Rows[i]["key"].ToString().Trim();
string value = ds.Tables[0].Rows[i]["value"].ToString().Trim();
UpdateKeyVaule(key, value);
}
catch (Exception ex)
{
WriteLog.WriteLogError(ex.ToString());
}
}
}
catch (Exception ex)
{
WriteLog.WriteLogError("Class login GetValueFromDb has an Error:" + ex.ToString());
}
}
public SqlLiteInstance(SqlLiteHelper sqlInstanceHelper)
{
db = sqlInstanceHelper;
}
