public Module Select(string ModuleID)
{
String sql = "select * from Module where Module_ID=@ModuleID";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@ModuleID", ModuleID),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
Module index = new Module();
index.ModuleId = dt.Rows[0]["Module_ID"].ToString();
index.ModuleName = dt.Rows[0]["Module_Name"].ToString();
index.ModuleUrl = dt.Rows[0]["Module_URL"].ToString();
index.IconUrl = dt.Rows[0]["Icon_Url"].ToString();
index.ParentId = dt.Rows[0]["Parent_Id"].ToString();
index.OrderId = dt.Rows[0]["Order_Id"].GetDecimal();
index.Notes = dt.Rows[0]["Notes"].ToString();
index.Status = dt.Rows[0]["STATUS"].GetDecimal();
return(index);
}
else
{
return(null);
}
}
public List <Module> Select(int pageSize, int start, Common.HashTableExp hash, out int total, String sqlWhere)
{
List <Module> list = new List <Module>();
sqlWhere = "1=1" + sqlWhere;
#region 查询条件
if (hash["ModuleID"] != null)
{
sqlWhere += string.Format(" and Module_ID='{0}'", hash["ModuleID"]);
}
if (hash["ModuleName"] != null)
{
sqlWhere += string.Format(" and Module_Name='{0}'", hash["ModuleName"]);
}
if (hash["ModuleURL"] != null)
{
sqlWhere += string.Format(" and Module_URL='{0}'", hash["ModuleURL"]);
}
if (hash["IconUrl"] != null)
{
sqlWhere += string.Format(" and Icon_Url='{0}'", hash["IconUrl"]);
}
if (hash["ParentId"] != null)
{
sqlWhere += string.Format(" and Parent_Id='{0}'", hash["ParentId"]);
}
if (hash["OrderId"] != null)
{
sqlWhere += string.Format(" and Order_Id='{0}'", hash["OrderId"]);
}
if (hash["Notes"] != null)
{
sqlWhere += string.Format(" and Notes='{0}'", hash["Notes"]);
}
if (hash["Status"] != null)
{
sqlWhere += string.Format(" and STATUS='{0}'", hash["Status"]);
}
#endregion
DataTable dt = SqlLiteHelper.GetTable("Module",
"Module_ID,Module_Name,Module_URL,Icon_Url,Parent_Id,Order_Id,Notes,STATUS",
pageSize, start, sqlWhere, "Order_Id", "asc", out total);
for (int i = 0; i < dt.Rows.Count; i++)
{
Module index = new Module();
index.ModuleId = dt.Rows[i]["Module_ID"].ToString();
index.ModuleName = dt.Rows[i]["Module_Name"].ToString();
index.ModuleUrl = dt.Rows[i]["Module_URL"].ToString();
index.IconUrl = dt.Rows[i]["Icon_Url"].ToString();
index.ParentId = dt.Rows[i]["Parent_Id"].ToString();
index.OrderId = dt.Rows[i]["Order_Id"].GetInt32();
index.Notes = dt.Rows[i]["Notes"].ToString();
index.Status = dt.Rows[i]["STATUS"].GetDecimal();
list.Add(index);
}
return(list);
}
public List <Module> Select(Common.HashTableExp hash, String sqlWhere)
{
List <Module> list = new List <Module>();
string sql = "select Module_ID,Module_Name,Module_URL,Icon_Url,Parent_Id,Order_Id,Notes,STATUS from Module where 1=1";
#region 查询条件
if (hash["ModuleID"] != null)
{
sql += string.Format(" and Module_ID='{0}'", hash["ModuleID"]);
}
if (hash["ModuleName"] != null)
{
sql += string.Format(" and Module_Name='{0}'", hash["ModuleName"]);
}
if (hash["ModuleURL"] != null)
{
sql += string.Format(" and Module_URL='{0}'", hash["ModuleURL"]);
}
if (hash["IconUrl"] != null)
{
sql += string.Format(" and Icon_Url='{0}'", hash["IconUrl"]);
}
if (hash["ParentId"] != null)
{
sql += string.Format(" and Parent_Id='{0}'", hash["ParentId"]);
}
if (hash["OrderId"] != null)
{
sql += string.Format(" and Order_Id='{0}'", hash["OrderId"]);
}
if (hash["Notes"] != null)
{
sql += string.Format(" and Notes='{0}'", hash["Notes"]);
}
if (hash["Status"] != null)
{
sql += string.Format(" and STATUS='{0}'", hash["Status"]);
}
#endregion
sql += sqlWhere + " order by Order_Id asc";
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
for (int i = 0; i < dt.Rows.Count; i++)
{
Module index = new Module();
index.ModuleId = dt.Rows[i]["Module_ID"].ToString();
index.ModuleName = dt.Rows[i]["Module_Name"].ToString();
index.ModuleUrl = dt.Rows[i]["Module_URL"].ToString();
index.IconUrl = dt.Rows[i]["Icon_Url"].ToString();
index.ParentId = dt.Rows[i]["Parent_Id"].ToString();
index.OrderId = dt.Rows[i]["Order_Id"].GetDecimal();
index.Notes = dt.Rows[i]["Notes"].ToString();
index.Status = dt.Rows[i]["STATUS"].GetDecimal();
list.Add(index);
}
return(list);
}
public DataTable GetCollectionByMonth()
{
String sql = @"select substr(time,1,7) time,
(select ifnull(SUM(price),0) from V_BANK_CARD where substr(a.time,1,7)=substr(time,1,7) and Save_Type='1000200001') moneyInsert,
(select ifnull(SUM(price),0) from V_BANK_CARD where substr(a.time,1,7)=substr(time,1,7) and Save_Type='1000200002') moneyOut
from V_BANK_CARD a
group by substr(time,1,7)";
return(SqlLiteHelper.GetTable(sql, CommandType.Text));
}
/// <summary>
/// 根据月份汇总生活费
/// </summary>
/// <param name="time">时间，如:2013-01</param>
/// <returns></returns>
public DataTable GetCollectionByDay(string time, String sqlWhere)
{
String sql = @"select TIME time,SUM(Price) price from Lifing_Cost ";
if (!String.IsNullOrEmpty(time))
{
sql += " where substr(time,1,7)='" + time + "' " + sqlWhere;
}
sql += " group by time order by time";
return(SqlLiteHelper.GetTable(sql, CommandType.Text));
}
/// <summary>
/// 根据月份汇总生活费类型
/// </summary>
/// <param name="time">时间，如:2013-01</param>
/// <returns></returns>
public DataTable GetCollectionByType(string time, String sqlWhere)
{
String sql = @"select COST_TYPE_NAME costTypeName,SUM(price) price from V_LIFING_COST";
if (!String.IsNullOrEmpty(time))
{
sql += " where substr(time,1,7)='" + time + "' " + sqlWhere;
}
sql += " group by COST_TYPE_NAME";
return(SqlLiteHelper.GetTable(sql, CommandType.Text));
}
/// <summary>
/// 查询所有的自定义分组名称
/// </summary>
/// <returns></returns>
public List <string> GetAllCusGroup()
{
String sql = "select CusGroup from Lifing_Cost where CusGroup is NOT NULL GROUP BY CusGroup";
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
List <String> list = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
list.Add(dt.Rows[i][0].GetString());
}
return(list);
}
/// <summary>
/// 查询所有的消费名称汇总,如:李子、桃子等
/// </summary>
/// <returns></returns>
public List <string> GetAllReasons()
{
List <String> list = new List <String>();
string sql = "select Reason from Lifing_Cost group by Reason";
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
for (int i = 0; i < dt.Rows.Count; i++)
{
list.Add(dt.Rows[i]["Reason"].GetString());
}
return(list);
}
public List <CostMonthCol> GetCollectionByMonth(string beginTime, string endTime, bool?isMark, bool?family, string costTypeIds, String userId)
{
String costSqlWhere = String.Empty;
String incomeSqlWhere = String.Empty;
#region 组装条件
if (!String.IsNullOrEmpty(beginTime))
{
costSqlWhere += String.Format(" and time>='{0}'", beginTime);
incomeSqlWhere += String.Format(" and time>='{0}'", beginTime);
}
if (!String.IsNullOrEmpty(endTime))
{
costSqlWhere += String.Format(" and time<datetime('{0}','+1 hour')", endTime);
incomeSqlWhere += String.Format(" and time<datetime('{0}','+1 hour')", endTime);
}
if (isMark != null)
{
costSqlWhere += String.Format(" and isMark='{0}'", isMark.GetInt32());
incomeSqlWhere += String.Format(" and isMark='{0}'", isMark.GetInt32());
}
if (family != null)
{
costSqlWhere += String.Format(" and FamilyPay='{0}'", family.GetInt32());
incomeSqlWhere += String.Format(" and FamilyIncome='{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);
incomeSqlWhere += String.Format(" and Create_By='{0}'", userId);
}
#endregion
String sql = String.Format(@"select a.PayTime Time,round(a.Pay,2) Pay,ifnull(round(b.Income,2),0) Income,round((ifnull(b.Income,0)-ifnull(a.Pay,0)),2) Balance from (
select strftime('%Y-%m',Time) PayTime,SUM(Price) Pay from Lifing_Cost where 1=1 {0}
group by strftime('%Y-%m',Time)
) a left join
(
select strftime('%Y-%m',Time) InTime,SUM(Price) Income from Income where 1=1 {1}
group by strftime('%Y-%m',Time)
) b on a.PayTime=b.InTime
order by a.PayTime desc", costSqlWhere, incomeSqlWhere);
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
return(Life.Model.Common <CostMonthCol> .ConvertToList(dt));
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <returns></returns>
public List <Income> Select(HashTableExp hash, String sqlWhere)
{
List <Income> list = new List <Income>();
string sql = "select Id,TIME,Price,Note,Create_By,Create_Time,UpDate_By,UpDate_Time,FamilyIncome,IsMark,CusGroup from Income where 1=1 ";
#region 查询条件
if (hash != null)
{
if (hash["Id"] != null)
{
sql += string.Format(" and Id='{0}'", hash["Id"]);
}
if (hash["Time"] != null)
{
sql += string.Format(" and TIME='{0}'", hash["Time"]);
}
if (hash["Price"] != null)
{
sql += string.Format(" and Price='{0}'", hash["Price"]);
}
if (hash["Note"] != null)
{
sql += string.Format(" and Note='{0}'", hash["Note"]);
}
if (hash["CreateBy"] != null)
{
sql += string.Format(" and Create_By='{0}'", hash["CreateBy"]);
}
if (hash["CreateTime"] != null)
{
sql += string.Format(" and Create_Time='{0}'", hash["CreateTime"]);
}
if (hash["UpdateBy"] != null)
{
sql += string.Format(" and UpDate_By='{0}'", hash["UpdateBy"]);
}
if (hash["UpdateTime"] != null)
{
sql += string.Format(" and UpDate_Time='{0}'", hash["UpdateTime"]);
}
}
#endregion
sql += sqlWhere;
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
list = Model.Common <Income> .ConvertToList(dt);
return(list);
}
/// <summary>
/// 分页查询收入记录表
/// </summary>
/// <param name="pageSize">每页多少条数据</param>
/// <param name="start">排除多少条数据</param>
/// <param name="hash">筛选条件</param>
/// <param name="total">总共多少条</param>
/// <returns>数据集合</returns>
public List <Student> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere)
{
List <Student> list = new List <Student>();
#region 查询条件
if (hash != null)
{
if (hash["Id"] != null)
{
sqlWhere += string.Format(" and [id]='{0}'", hash["Id"]);
}
if (hash["Name"] != null)
{
sqlWhere += string.Format(" and [name]='{0}'", hash["Name"]);
}
if (hash["CardNo"] != null)
{
sqlWhere += string.Format(" and [card_no]='{0}'", hash["CardNo"]);
}
if (hash["BirthDay"] != null)
{
sqlWhere += string.Format(" and [birth_day]='{0}'", hash["BirthDay"]);
}
if (hash["Sex"] != null)
{
sqlWhere += string.Format(" and [sex]='{0}'", hash["Sex"]);
}
if (hash["Addr"] != null)
{
sqlWhere += string.Format(" and [addr]='{0}'", hash["Addr"]);
}
if (hash["Remark"] != null)
{
sqlWhere += string.Format(" and [remark]='{0}'", hash["Remark"]);
}
}
sqlWhere = "1=1 " + sqlWhere;
#endregion
DataTable dt = SqlLiteHelper.GetTable("Student",
"[id],[name],[card_no],[birth_day],[sex],[addr],[remark]",
pageSize, start, sqlWhere, "name", "asc", out total);
list = Model.Common <Student> .ConvertToList(dt);
return(list);
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <returns></returns>
public List <Student> Select(HashTableExp hash, String sqlWhere)
{
List <Student> list = new List <Student>();
string sql = "select [id],[name],[card_no],[birth_day],[sex],[addr],[remark] from Student where 1=1 ";
#region 查询条件
if (hash != null)
{
if (hash["Id"] != null)
{
sqlWhere += string.Format(" and [id]='{0}'", hash["Id"]);
}
if (hash["Name"] != null)
{
sqlWhere += string.Format(" and [name]='{0}'", hash["Name"]);
}
if (hash["CardNo"] != null)
{
sqlWhere += string.Format(" and [card_no]='{0}'", hash["CardNo"]);
}
if (hash["BirthDay"] != null)
{
sqlWhere += string.Format(" and [birth_day]='{0}'", hash["BirthDay"]);
}
if (hash["Sex"] != null)
{
sqlWhere += string.Format(" and [sex]='{0}'", hash["Sex"]);
}
if (hash["Addr"] != null)
{
sqlWhere += string.Format(" and [addr]='{0}'", hash["Addr"]);
}
if (hash["Remark"] != null)
{
sqlWhere += string.Format(" and [remark]='{0}'", hash["Remark"]);
}
}
#endregion
sql += sqlWhere;
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
list = Model.Common <Student> .ConvertToList(dt);
return(list);
}
/// <summary>
/// 汇总月份生活费消费信息
/// Sqlite 的ifnull函数和SqlServer的isnull函数功能一样
/// </summary>
/// <returns></returns>
public DataTable GetCollectionByMonth(String sqlWhere, String beginTime, String endTime, String isCalc)
{
if (!String.IsNullOrEmpty(beginTime))
{
sqlWhere += String.Format(" and substr(time,1,7)>='{0}'", beginTime);
}
if (!String.IsNullOrEmpty(endTime))
{
sqlWhere += String.Format(" and substr(time,1,7)<='{0}'", endTime);
}
String sql = String.Format(@"select substr(time,1,7) time,SUM(Price) price,
ifnull((select SUM(price) from Income b where substr(a.time,1,7)=substr(b.time,1,7) {0}),0) interPrice
from Lifing_Cost a where 1=1 {0} {1}
group by substr(time,1,7) order by substr(time,1,7) asc", sqlWhere, isCalc);
return(SqlLiteHelper.GetTable(sql, CommandType.Text));
}
/// <summary>
/// 汇总月份生活费消费信息--图形汇总
/// </summary>
/// <returns></returns>
public DataTable GetCollection(String sqlWhere, String beginTime, String endTime)
{
if (!String.IsNullOrEmpty(beginTime))
{
sqlWhere += String.Format(" and strftime('%Y-%m',Time)>='{0}'", beginTime);
}
if (!String.IsNullOrEmpty(endTime))
{
sqlWhere += String.Format(" and strftime('%Y-%m',Time)<='{0}'", endTime);
}
String sql = String.Format(@"select strftime('%Y-%m',Time) time,SUM(price) price,
ifnull((select SUM(price) from Income b
where strftime('%Y-%m',a.Time)=strftime('%Y-%m',b.Time) {0}
),0) interPrice from Lifing_Cost a where 1=1 {0}
group by strftime('%Y-%m',Time) order by time", sqlWhere);
return(SqlLiteHelper.GetTable(sql, CommandType.Text));
}
public List <Module> SelectByUserId(string userId, string status)
{
String sql = String.Format(@"select distinct d.Module_ID,d.Module_Name,d.Module_URL,d.Icon_Url,d.Parent_Id,d.Order_Id,d.Notes,d.STATUS from User_To_Role a
inner join Roles b on a.Role_Id=b.Role_Id
inner join Role_To_Module c on b.Role_Id=c.Role_Id
inner join Module d on c.Module_Id=d.Module_ID
where a.User_Id='{0}'", userId);
if (!String.IsNullOrEmpty(status))
{
sql += " and d.STATUS=" + status;
}
sql += " order by d.Order_Id";
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
List <Module> list = Life.Model.Common <Module> .ConvertToList(dt);
return(list);
}
/// <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="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="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);
}
/// <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="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>
/// <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 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);
}
/// <summary>
/// 根据编号查询角色表
/// </summary>
/// <param name="RoleId">编号</param>
/// <returns>数据集合</returns>
public Roles Select(string RoleId)
{
String sql = "select * from Roles where Role_Id=@RoleId";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@RoleId", RoleId),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
Roles index = new Roles();
index.RoleId = dt.Rows[0]["Role_Id"].ToString();
index.RoleName = dt.Rows[0]["Role_Name"].ToString();
index.Notes = dt.Rows[0]["Notes"].ToString();
return(index);
}
else
{
return(null);
}
}
/// <summary>
/// 根据编号查询角色模块对应表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public RoleToModule Select(string Id)
{
String sql = "select * from RoleToModule 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)
{
RoleToModule index = new RoleToModule();
index.Id = dt.Rows[0]["Id"].GetString();
index.RoleId = dt.Rows[0]["Role_Id"].GetString();
index.ModuleId = dt.Rows[0]["Module_Id"].GetString();
return(index);
}
else
{
return(null);
}
}
/// <summary>
/// 分页查询角色模块对应表
/// </summary>
/// <param name="pageSize">每页多少条数据</param>
/// <param name="start">排除多少条数据</param>
/// <param name="hash">筛选条件</param>
/// <param name="total">总共多少条</param>
/// <returns>数据集合</returns>
public List <RoleToModule> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere)
{
List <RoleToModule> list = new List <RoleToModule>();
sqlWhere = "1=1 " + sqlWhere;
#region 查询条件
if (hash != null)
{
if (hash["Id"] != null)
{
sqlWhere += string.Format(" and Id='{0}'", hash["Id"]);
}
if (hash["RoleId"] != null)
{
sqlWhere += string.Format(" and Role_Id='{0}'", hash["RoleId"]);
}
if (hash["ModuleId"] != null)
{
sqlWhere += string.Format(" and Module_Id='{0}'", hash["ModuleId"]);
}
}
#endregion
DataTable dt = SqlLiteHelper.GetTable("Role_To_Module",
"Id,Role_Id,Module_Id",
pageSize, start, sqlWhere, "Id", "asc", out total);
for (int i = 0; i < dt.Rows.Count; i++)
{
RoleToModule index = new RoleToModule();
index.Id = dt.Rows[i]["Id"].GetString();
index.RoleId = dt.Rows[i]["Role_Id"].GetString();
index.ModuleId = dt.Rows[i]["Module_Id"].GetString();
list.Add(index);
}
return(list);
}
/// <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);
}
/// <summary>
/// 根据编号查询临时信息存储表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public TempData Select(string Id)
{
String sql = "select * from TempData 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)
{
TempData index = new TempData();
index.Id = dt.Rows[0]["Id"].GetString();
index.Email = dt.Rows[0]["Email"].GetString();
index.Expires = dt.Rows[0]["Expires"].GetDateTime();
index.CreateTime = dt.Rows[0]["Create_Time"].GetDateTime();
return(index);
}
else
{
return(null);
}
}
/// <summary>
/// 分页查询角色表
/// </summary>
/// <param name="pageSize">每页多少条数据</param>
/// <param name="start">排除多少条数据</param>
/// <param name="hash">筛选条件</param>
/// <param name="total">总共多少条</param>
/// <returns>数据集合</returns>
public List <Roles> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere)
{
List <Roles> list = new List <Roles>();
sqlWhere = "1=1 " + sqlWhere;
#region 查询条件
if (hash["RoleId"] != null)
{
sqlWhere += string.Format(" and Role_Id='{0}'", hash["RoleId"]);
}
if (hash["RoleName"] != null)
{
sqlWhere += string.Format(" and Role_Name='{0}'", hash["RoleName"]);
}
if (hash["Notes"] != null)
{
sqlWhere += string.Format(" and Notes='{0}'", hash["Notes"]);
}
#endregion
DataTable dt = SqlLiteHelper.GetTable("Roles",
"Role_Id,Role_Name,Notes",
pageSize, start, sqlWhere, "Role_Id", "asc", out total);
for (int i = 0; i < dt.Rows.Count; i++)
{
Roles index = new Roles();
index.RoleId = dt.Rows[i]["Role_Id"].ToString();
index.RoleName = dt.Rows[i]["Role_Name"].ToString();
index.Notes = dt.Rows[i]["Notes"].ToString();
list.Add(index);
}
return(list);
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <returns></returns>
public List <RoleToModule> Select(HashTableExp hash, String sqlWhere)
{
List <RoleToModule> list = new List <RoleToModule>();
string sql = "select Id,Role_Id,Module_Id from Role_To_Module where 1=1 ";
#region 查询条件
if (hash != null)
{
if (hash["Id"] != null)
{
sql += string.Format(" and Id='{0}'", hash["Id"]);
}
if (hash["RoleId"] != null)
{
sql += string.Format(" and Role_Id='{0}'", hash["RoleId"]);
}
if (hash["ModuleId"] != null)
{
sql += string.Format(" and Module_Id='{0}'", hash["ModuleId"]);
}
}
#endregion
sql += sqlWhere;
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
for (int i = 0; i < dt.Rows.Count; i++)
{
RoleToModule index = new RoleToModule();
index.Id = dt.Rows[i]["Id"].GetString();
index.RoleId = dt.Rows[i]["Role_Id"].GetString();
index.ModuleId = dt.Rows[i]["Module_Id"].GetString();
list.Add(index);
}
return(list);
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <returns></returns>
public List <LifingCost> Select(HashTableExp hash, String sqlWhere)
{
List <LifingCost> list = new List <LifingCost>();
string sql = "select Id,TIME,Reason,Price,Cost_Type_Id,Notes,Img_Url,Create_By,Create_Time,UpDate_By,UpDate_Time,IsMark,FamilyPay,CusGroup from Lifing_Cost where 1=1 ";
#region 查询条件
if (hash != null)
{
if (hash["Id"] != null)
{
sql += string.Format(" and Id='{0}'", hash["Id"]);
}
if (hash["Time"] != null)
{
sql += string.Format(" and TIME='{0}'", hash["Time"]);
}
if (hash["Reason"] != null)
{
sql += string.Format(" and Reason='{0}'", hash["Reason"]);
}
if (hash["Price"] != null)
{
sql += string.Format(" and Price='{0}'", hash["Price"]);
}
if (hash["CostTypeId"] != null)
{
sql += string.Format(" and Cost_Type_Id='{0}'", hash["CostTypeId"]);
}
if (hash["Notes"] != null)
{
sql += string.Format(" and Notes='{0}'", hash["Notes"]);
}
if (hash["ImgUrl"] != null)
{
sql += string.Format(" and Img_Url='{0}'", hash["ImgUrl"]);
}
if (hash["CreateBy"] != null)
{
sql += string.Format(" and Create_By='{0}'", hash["CreateBy"]);
}
if (hash["CreateTime"] != null)
{
sql += string.Format(" and Create_Time='{0}'", hash["CreateTime"]);
}
if (hash["UpdateBy"] != null)
{
sql += string.Format(" and UpDate_By='{0}'", hash["UpdateBy"]);
}
if (hash["UpdateTime"] != null)
{
sql += string.Format(" and UpDate_Time='{0}'", hash["UpdateTime"]);
}
//格式2013-01
if (hash["YearMonth"] != null)
{
sqlWhere += string.Format(" and substr(time,1,7)='{0}'", hash["YearMonth"]);
}
}
#endregion
sql += sqlWhere;
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
list = Model.Common <LifingCost> .ConvertToList(dt);
return(list);
}
