/// <summary>
/// 批量新增生活费操作管理
/// </summary>
/// <param name="list">生活费操作管理对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <LifingCost> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format(@"insert into Lifing_Cost(Id,TIME,Reason,Price,Cost_Type_Id,Notes,Img_Url,Create_By,Create_Time,UpDate_By,UpDate_Time,IsMark,FamilyPay,CusGroup)
values(@Id,@TIME,@Reason,@Price,@Cost_Type_Id,@Notes,@Img_Url,@Create_By,@Create_Time,@UpDate_By,@UpDate_Time,@IsMark,@FamilyPay,@CusGroup)");
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)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增字典表
/// </summary>
/// <param name="list">字典表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <Diction> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format("insert into Diction(Id,Name,Note,Parent_Id,Order_Id,Create_By,Create_Time,Update_By,Update_Time) values(@Id,@Name,@Note,@Parent_Id,@Order_Id,@Create_By,@Create_Time,@Update_By,@Update_Time)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@Name", index.Name),
new SQLiteParameter("@Note", index.Note),
new SQLiteParameter("@Parent_Id", index.ParentId),
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)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增银行卡操作记录表
/// </summary>
/// <param name="list">银行卡操作记录表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <BankCard> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
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)");
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)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int Update(List <Income> list)
{
String sql = String.Empty;
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
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";
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)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int Update(List <SysConfig> list)
{
String sql = String.Empty;
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
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";
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)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增系统配置
/// </summary>
/// <param name="list">系统配置对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <SysConfig> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
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)");
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)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量保存纯收入信息，存在就修改，否则就新增
/// </summary>
/// <param name="incomes"></param>
/// <returns></returns>
public bool Save(List <Income> incomes)
{
try
{
List <SqlHashTable> list = new List <SqlHashTable>();
foreach (var index in incomes)
{
var b = Select(index.Id);
if (b == null)
{
#region 新增
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)
};
list.Add(new SqlHashTable(sql, parm));
#endregion
}
else
{
#region 修改
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)
};
list.Add(new SqlHashTable(sql, parm));
#endregion
}
}
SqlLiteHelper.ExecuteSql(list);
return(true);
}
catch (Exception ex)
{
LogHelper.Log.Error("批量保存纯收入信息", ex);
return(false);
}
throw new NotImplementedException();
}
/// <summary>
/// 批量新增收入记录表
/// </summary>
/// <param name="list">收入记录表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <Income> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
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)");
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)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增收入记录表
/// </summary>
/// <param name="list">收入记录表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <Teacher> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format("insert into Teacher(id,name,Sex,native_place,phone,qq,email,graduate_school,professional,good_subjects,self_assessment,create_by,create_time,update_by,update_time) values(@id,@name,@Sex,@native_place,@phone,@qq,@email,@graduate_school,@professional,@good_subjects,@self_assessment,@create_by,@create_time,@update_by,@update_time)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@id", index.Id),
new SQLiteParameter("@name", index.Name),
new SQLiteParameter("@Sex", index.Sex),
new SQLiteParameter("@native_place", index.NativePlace),
new SQLiteParameter("@phone", index.Phone),
new SQLiteParameter("@qq", index.Qq),
new SQLiteParameter("@email", index.Email),
new SQLiteParameter("@graduate_school", index.GraduateSchool),
new SQLiteParameter("@professional", index.Professional),
new SQLiteParameter("@good_subjects", index.GoodSubjects),
new SQLiteParameter("@self_assessment", index.SelfAssessment),
new SQLiteParameter("@create_by", index.CreateBy),
new SQLiteParameter("@create_time", index.CreateTime),
new SQLiteParameter("@update_by", index.UpdateBy),
new SQLiteParameter("@update_time", index.UpdateTime)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int Update(List <Teacher> list)
{
String sql = String.Empty;
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = "update Teacher set id=@id,name=@name,Sex=@Sex,native_place=@native_place,phone=@phone,qq=@qq,email=@email,graduate_school=@graduate_school,professional=@professional,good_subjects=@good_subjects,self_assessment=@self_assessment,create_by=@create_by,create_time=@create_time,update_by=@update_by,update_time=@update_time where id=@id";
parm = new SQLiteParameter[] {
new SQLiteParameter("@id", index.Id),
new SQLiteParameter("@name", index.Name),
new SQLiteParameter("@Sex", index.Sex),
new SQLiteParameter("@native_place", index.NativePlace),
new SQLiteParameter("@phone", index.Phone),
new SQLiteParameter("@qq", index.Qq),
new SQLiteParameter("@email", index.Email),
new SQLiteParameter("@graduate_school", index.GraduateSchool),
new SQLiteParameter("@professional", index.Professional),
new SQLiteParameter("@good_subjects", index.GoodSubjects),
new SQLiteParameter("@self_assessment", index.SelfAssessment),
new SQLiteParameter("@create_by", index.CreateBy),
new SQLiteParameter("@create_time", index.CreateTime),
new SQLiteParameter("@update_by", index.UpdateBy),
new SQLiteParameter("@update_time", index.UpdateTime)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增
/// </summary>
/// <param name="list"></param>
/// <returns>影响的行数</returns>
public int Add(List <Users> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format("insert into Users(Id,Login_Id,Login_Pwd,Name,Phone,Mail,Address,Age,Notes) values(@Id,@Login_Id,@Login_Pwd,@Name,@Phone,@Mail,@Address,@Age,@Notes)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@Login_Id", index.LoginId),
new SQLiteParameter("@Login_Pwd", index.LoginPwd),
new SQLiteParameter("@Name", index.Name),
new SQLiteParameter("@Phone", index.Phone),
new SQLiteParameter("@Mail", index.Mail),
new SQLiteParameter("@Address", index.Address),
new SQLiteParameter("@Age", index.Age),
new SQLiteParameter("@Notes", index.Notes)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int Update(List <BankCard> list)
{
String sql = String.Empty;
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
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";
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)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int Update(List <LifingCost> list)
{
String sql = String.Empty;
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
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";
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)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 计算所有信息的余额
/// </summary>
/// <returns></returns>
public bool CalcAllBalance()
{
Double cal = 0;
SQLiteParameter[] parm;
String sql = String.Empty;
List <Diction> dics = new DLDiction().Select(new HashTableExp("ParentId", "1000100000"), "");
foreach (var item in dics)
{
item.Note = "0";
}
List <BankCard> banks = new DLBankCard().Select(null, "").OrderBy(f => f.Time).ThenBy(f => f.CreateTime).ToList();
foreach (var item in banks)
{
//表示存入
if (item.SaveType == 1000200001)
{
cal = dics.Where(f => f.Id == item.BankType).SingleOrDefault().Note.GetDouble();
cal += item.Price;
dics.Where(f => f.Id == item.BankType).SingleOrDefault().Note = cal.GetString();
}
//表示取出
else
{
cal = dics.Where(f => f.Id == item.BankType).SingleOrDefault().Note.GetDouble();
cal -= item.Price;
dics.Where(f => f.Id == item.BankType).SingleOrDefault().Note = cal.GetString();
}
item.Balance = cal;
}
List <SqlHashTable> hashList = new List <SqlHashTable>();
foreach (var item in dics)
{
sql = "update Diction set Note=@Note where Id=@Id";
parm = new SQLiteParameter[] {
new SQLiteParameter("@Note", item.Note),
new SQLiteParameter("@Id", item.Id)
};
hashList.Add(new SqlHashTable(sql, parm));
}
foreach (var item in banks)
{
sql = "update Bank_Card set Balance=@Balance where Id=@Id";
parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", item.Id),
new SQLiteParameter("@Balance", item.Balance)
};
hashList.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashList) > 0);
}
/// <summary>
/// 批量修改自定义分组信息
/// </summary>
/// <param name="ids"></param>
/// <param name="value"></param>
/// <returns></returns>
public int ModifyCusGroup(string ids, string value)
{
try
{
List <SqlHashTable> sqls = new List <SqlHashTable>();
String[] temp = ids.Split(',');
foreach (var item in temp)
{
String sql = String.Format("update Lifing_Cost set CusGroup='{0}' where Id='{1}'", value, item);
sqls.Add(new SqlHashTable(sql, null));
}
return(SqlLiteHelper.ExecuteSql(sqls));
}
catch (Exception ex)
{
throw ex;
}
}
/// <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="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 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));
}
public int Save(string userId, string[] roleIds)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
//删除用户的所有角色
hashTables.Add(new SqlHashTable("delete from User_To_Role where User_Id='" + userId + "'", null));
//循环新增角色
foreach (var roleId in roleIds)
{
sql = string.Format("insert into User_To_Role(Id,User_Id,Role_Id) values(@Id,@User_Id,@Role_Id)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", Guid.NewGuid()),
new SQLiteParameter("@User_Id", userId),
new SQLiteParameter("@Role_Id", roleId)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
public int Save(string roleId, string[] moduleIds)
{
String sql = "delete from Role_To_Module where Role_Id='" + roleId + "'";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
//删除角色所对应的模块信息
hashTables.Add(new SqlHashTable(sql, null));
//循环增加
foreach (var moduleId in moduleIds)
{
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", Guid.NewGuid()),
new SQLiteParameter("@Role_Id", roleId),
new SQLiteParameter("@Module_Id", moduleId)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int Update(List <Student> list)
{
String sql = String.Empty;
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = "update Student set id=@id,name=@name,card_no=@card_no,birth_day=@birth_day,sex=@sex,addr=@addr,remark=@remark where id=@id";
parm = new SQLiteParameter[] {
new SQLiteParameter("@id", index.Id),
new SQLiteParameter("@name", index.Name),
new SQLiteParameter("@card_no", index.CardNo),
new SQLiteParameter("@birth_day", index.BirthDay),
new SQLiteParameter("@sex", index.Sex),
new SQLiteParameter("@addr", index.Addr),
new SQLiteParameter("@remark", index.Remark)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增收入记录表
/// </summary>
/// <param name="list">收入记录表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <Student> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format("insert into Student(id,name,card_no,birth_day,sex,addr,remark) values(@id,@name,@card_no,@birth_day,@sex,@addr,@remark)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@id", index.Id),
new SQLiteParameter("@name", index.Name),
new SQLiteParameter("@card_no", index.CardNo),
new SQLiteParameter("@birth_day", index.BirthDay),
new SQLiteParameter("@sex", index.Sex),
new SQLiteParameter("@addr", index.Addr),
new SQLiteParameter("@remark", index.Remark)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增文章表
/// </summary>
/// <param name="list">文章表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <Article> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format("insert into Article(Id,Title,Content,Cate_Id,Create_By,Create_Time,UpDate_By,UpDate_Time) values(@Id,@Title,@Content,@Cate_Id,@Create_By,@Create_Time,@UpDate_By,@UpDate_Time)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@Title", index.Title),
new SQLiteParameter("@Content", index.Content),
new SQLiteParameter("@Cate_Id", index.CateId),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int Update(List <ArtCategory> list)
{
String sql = String.Empty;
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = "update Art_Category set Cat_Id=@Cat_Id,Cat_Name=@Cat_Name,Cat_Remark=@Cat_Remark,Cat_Order=@Cat_Order,Create_By=@Create_By,Create_Time=@Create_Time,UpDate_By=@UpDate_By,UpDate_Time=@UpDate_Time where Cat_Id=@Cat_Id";
parm = new SQLiteParameter[] {
new SQLiteParameter("@Cat_Id", index.CatId),
new SQLiteParameter("@Cat_Name", index.CatName),
new SQLiteParameter("@Cat_Remark", index.CatRemark),
new SQLiteParameter("@Cat_Order", index.CatOrder),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增文章类型表
/// </summary>
/// <param name="list">文章类型表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <ArtCategory> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format("insert into Art_Category(Cat_Id,Cat_Name,Cat_Remark,Cat_Order,Create_By,Create_Time,UpDate_By,UpDate_Time) values(@Cat_Id,@Cat_Name,@Cat_Remark,@Cat_Order,@Create_By,@Create_Time,@UpDate_By,@UpDate_Time)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@Cat_Id", index.CatId),
new SQLiteParameter("@Cat_Name", index.CatName),
new SQLiteParameter("@Cat_Remark", index.CatRemark),
new SQLiteParameter("@Cat_Order", index.CatOrder),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int Update(List <Article> list)
{
String sql = String.Empty;
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = "update Article set Id=@Id,Title=@Title,Content=@Content,Cate_Id=@Cate_Id,Create_By=@Create_By,Create_Time=@Create_Time,UpDate_By=@UpDate_By,UpDate_Time=@UpDate_Time where Id=@Id";
parm = new SQLiteParameter[] {
new SQLiteParameter("@Id", index.Id),
new SQLiteParameter("@Title", index.Title),
new SQLiteParameter("@Content", index.Content),
new SQLiteParameter("@Cate_Id", index.CateId),
new SQLiteParameter("@Create_By", index.CreateBy),
new SQLiteParameter("@Create_Time", index.CreateTime),
new SQLiteParameter("@UpDate_By", index.UpdateBy),
new SQLiteParameter("@UpDate_Time", index.UpdateTime)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量新增模块表
/// </summary>
/// <param name="list">模块表对象集合</param>
/// <returns>影响的行数</returns>
public int Add(List <Module> list)
{
String sql = "";
SQLiteParameter[] parm = null;
List <SqlHashTable> hashTables = new List <SqlHashTable>();
foreach (var index in list)
{
sql = string.Format("insert into Module(Module_ID,Module_Name,Module_URL,Icon_Url,Parent_Id,Order_Id,Notes,STATUS) values(@Module_ID,@Module_Name,@Module_URL,@Icon_Url,@Parent_Id,@Order_Id,@Notes,@STATUS)");
parm = new SQLiteParameter[] {
new SQLiteParameter("@Module_ID", index.ModuleId),
new SQLiteParameter("@Module_Name", index.ModuleName),
new SQLiteParameter("@Module_URL", index.ModuleUrl),
new SQLiteParameter("@Icon_Url", index.IconUrl),
new SQLiteParameter("@Parent_Id", index.ParentId),
new SQLiteParameter("@Order_Id", index.OrderId),
new SQLiteParameter("@Notes", index.Notes),
new SQLiteParameter("@STATUS", index.Status)
};
hashTables.Add(new SqlHashTable(sql, parm));
}
return(SqlLiteHelper.ExecuteSql(hashTables));
}
/// <summary>
/// 批量保存生活费信息,存在就修改,否则就新增
/// </summary>
/// <param name="lifes"></param>
/// <returns></returns>
public bool Save(List <LifingCost> lifes)
{
try
{
List <SqlHashTable> list = new List <SqlHashTable>();
foreach (var index in lifes)
{
var b = Select(index.Id);
if (b == null)
{
#region 新增
string sql = string.Format(@"insert into Lifing_Cost(Id,TIME,Reason,Price,Cost_Type_Id,Notes,Img_Url,Create_By,Create_Time,UpDate_By,UpDate_Time,IsMark,FamilyPay,CusGroup)
values(@Id,@TIME,@Reason,@Price,@Cost_Type_Id,@Notes,@Img_Url,@Create_By,@Create_Time,@UpDate_By,@UpDate_Time,@IsMark,@FamilyPay,@CusGroup)");
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)
};
list.Add(new SqlHashTable(sql, parm));
#endregion
}
else
{
#region 修改
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)
};
list.Add(new SqlHashTable(sql, parm));
#endregion
}
}
SqlLiteHelper.ExecuteSql(list);
return(true);
}
catch (Exception ex)
{
LogHelper.Log.Error("批量保存生活费信息", ex);
return(false);
}
}
/// <summary>
/// 保存银行卡信息,存在就修改,否则就新增
/// </summary>
/// <param name="banks"></param>
/// <returns></returns>
public bool Save(List <BankCard> banks)
{
try
{
List <SqlHashTable> list = new List <SqlHashTable>();
foreach (var index in banks)
{
var b = Select(index.Id);
if (b == null)
{
#region 新增
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)
};
list.Add(new SqlHashTable(sql, parm));
#endregion
}
else
{
#region 修改
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)
};
list.Add(new SqlHashTable(sql, parm));
#endregion
}
}
SqlLiteHelper.ExecuteSql(list);
return(true);
}
catch (Exception ex)
{
LogHelper.Log.Error("批量保存银行卡信息出错", ex);
return(false);
}
}
