/// <summary>
/// 按条件删除数据
/// </summary>
/// <returns></returns>
public int Delete(HashTableExp hash, String sqlWhere)
{
string sql = "delete 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;
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, null));
}
/// <summary>
/// 按条件删除数据
/// </summary>
/// <returns></returns>
public int Delete(HashTableExp hash, String sqlWhere)
{
string sql = "delete 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;
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, null));
}
/// <summary>
/// 分页查询
/// </summary>
/// <returns></returns>
public String SelectByPage(int limit, int start, String key, String startTime)
{
#region 封装查询方法
String sqlWhere = string.Format(" and (Bank_Type_Name like '%{0}%' or Create_Name like '%{0}%' or Save_Name like '%{0}%' or Note like '%{0}%')", key);
if (!String.IsNullOrEmpty(startTime))
{
sqlWhere += String.Format(" and time >='{0}'", startTime.GetDateTime().ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!String.IsNullOrEmpty(Request["endTime"]))
{
sqlWhere += String.Format(" and time <= '{0}'", Request["endTime"].GetDateTime().ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!String.IsNullOrEmpty(Request["BankType"]))
{
sqlWhere += String.Format(" and Bank_Type_Name='{0}'", Request["BankType"]);
}
if (!String.IsNullOrEmpty(Request["SaveType"]))
{
sqlWhere += String.Format(" and Save_Name='{0}'", Request["SaveType"]);
}
String yearMonth = Request["yearMonth"];//年月,格式2013-1
HashTableExp hash = new HashTableExp();
if (!String.IsNullOrEmpty(yearMonth))
{
hash.Add("YearMonth", yearMonth);
}
#endregion
int total;
List <VBankCard> bankCards = new BLBankCard().Select(limit, start, hash, out total, sqlWhere);
return(JsonConvert.JavaScriptSerializer(new ExtGridRecord(bankCards, total)));
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
String subKey = Request["subKey"];
String email = Request["email"];
HashTableExp hash = new HashTableExp("Id", subKey);
hash.Add("Email", email);
TempData data = new BLTempData().Select(hash, String.Format(" and Expires>'{0}'", DateTime.Now.AddSeconds(1).ToString("yyyy-MM-dd HH:mm:ss"))).SingleOrDefault();
//删除过期数据
new BLTempData().Delete(null, String.Format(" and Expires<'{0}'", DateTime.Now.AddSeconds(1).ToString("yyyy-MM-dd HH:mm:ss")));
if (data == null)
{
Response.Redirect("error.htm");
}
else
{
new BLTempData().Delete(subKey);
Users user = new BLUsers().Select(new HashTableExp("Mail", email)).SingleOrDefault();
if (user == null)
{
ClientScript.RegisterClientScriptBlock(this.GetType(), DateTime.Now.ToString(), "alert('邮件地址不存在');location.href='error.htm'", true);
}
else
{
this.LitUserName.Text = user.LoginId;
Session["TempUser"] = user;
}
}
}
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <returns></returns>
public List <Roles> Select(HashTableExp hash, String sqlWhere)
{
List <Roles> list = new List <Roles>();
string sql = "select Role_Id,Role_Name,Notes from Roles where 1=1 ";
#region 查询条件
if (hash["RoleId"] != null)
{
sql += string.Format(" and Role_Id='{0}'", hash["RoleId"]);
}
if (hash["RoleName"] != null)
{
sql += string.Format(" and Role_Name='{0}'", hash["RoleName"]);
}
if (hash["Notes"] != null)
{
sql += string.Format(" and Notes='{0}'", hash["Notes"]);
}
#endregion
sql += sqlWhere;
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text);
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 int Delete(HashTableExp hash, String sqlWhere)
{
string sql = "delete from Bank_Card 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["SaveType"] != null)
{
sql += string.Format(" and Save_Type='{0}'", hash["SaveType"]);
}
if (hash["Balance"] != null)
{
sql += string.Format(" and Balance='{0}'", hash["Balance"]);
}
if (hash["BankType"] != null)
{
sql += string.Format(" and Bank_Type='{0}'", hash["BankType"]);
}
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;
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, null));
}
/// <summary>
/// 按条件删除数据
/// </summary>
/// <returns></returns>
public int Delete(HashTableExp hash, String sqlWhere)
{
string sql = "delete 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"]);
}
}
#endregion
sql += sqlWhere;
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, null));
}
/// <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>
/// 按条件删除数据
/// </summary>
/// <returns></returns>
public int Delete(HashTableExp hash, String sqlWhere)
{
string sql = "delete from Article where 1=1 ";
#region 查询条件
if (hash != null)
{
if (hash["Id"] != null)
{
sql += string.Format(" and Id='{0}'", hash["Id"]);
}
if (hash["Title"] != null)
{
sql += string.Format(" and Title='{0}'", hash["Title"]);
}
if (hash["Content"] != null)
{
sql += string.Format(" and Content='{0}'", hash["Content"]);
}
if (hash["CateId"] != null)
{
sql += string.Format(" and Cate_Id='{0}'", hash["CateId"]);
}
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;
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, null));
}
/// <summary>
/// 按条件删除数据
/// </summary>
/// <returns></returns>
public int Delete(HashTableExp hash, String sqlWhere)
{
string sql = "delete from Art_Category where 1=1 ";
#region 查询条件
if (hash != null)
{
if (hash["CatId"] != null)
{
sql += string.Format(" and Cat_Id='{0}'", hash["CatId"]);
}
if (hash["CatName"] != null)
{
sql += string.Format(" and Cat_Name='{0}'", hash["CatName"]);
}
if (hash["CatRemark"] != null)
{
sql += string.Format(" and Cat_Remark='{0}'", hash["CatRemark"]);
}
if (hash["CatOrder"] != null)
{
sql += string.Format(" and Cat_Order='{0}'", hash["CatOrder"]);
}
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;
return(SqlLiteHelper.ExecuteNonQuery(sql, CommandType.Text, null));
}
/// <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>
/// <returns></returns>
public Tuple <HashTableExp, String> GetParam()
{
#region 封装查询方法
String sqlWhere = "";
HashTableExp hash = new HashTableExp();
if (!String.IsNullOrEmpty(Request["name"]))
{
sqlWhere += string.Format(" and name like '%{0}%'", Request["name"]);
}
if (!String.IsNullOrEmpty(Request["cardno"]))
{
sqlWhere += string.Format(" and card_no like '%{0}%'", Request["cardno"]);
}
#endregion
Tuple <HashTableExp, String> result = new Tuple <HashTableExp, string>(hash, sqlWhere);
return(result);
}
/// <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));
}
/// <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>
/// 查询所有数据
/// </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>
/// <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 Tuple <HashTableExp, String> GetParam(Boolean?isMark, Boolean?family)
{
#region 封装查询方法
String sqlWhere = "";
HashTableExp hash = new HashTableExp();
if (!String.IsNullOrEmpty(Request["key"]))
{
String.Format(" and (Note like '%{0}%')", Request["key"]);
}
String yearMonth = Request["yearMonth"];//年月,格式2013-01
if (!String.IsNullOrEmpty(yearMonth))
{
String[] times = yearMonth.Split('-');
hash.Add("YearMonth", yearMonth);
}
if (!String.IsNullOrEmpty(Request["startTime"]))
{
sqlWhere += String.Format(" and time >='{0}'", Request["startTime"].GetDateTime().ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!String.IsNullOrEmpty(Request["endTime"]))
{
sqlWhere += String.Format(" and time <= '{0}'", Request["endTime"].GetDateTime().ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!String.IsNullOrEmpty(Request["minPrice"]))
{
sqlWhere += String.Format(" and Price>='{0}'", Request["minPrice"]);
}
if (!String.IsNullOrEmpty(Request["maxPrice"]))
{
sqlWhere += String.Format(" and Price<='{0}'", Request["maxPrice"]);
}
if (!String.IsNullOrEmpty(Request["CusGroup"]))
{
hash.Add("CusGroupMore", Request["CusGroup"]);
}
if (!String.IsNullOrEmpty(Request["CusGroupNo"]))
{
hash.Add("CusGroupNo", Request["CusGroupNo"]);
}
if (CurrentUser.Id != "-1")
{
hash.Add("CreateBy", CurrentUser.Id);
}
if (isMark == false)
{
sqlWhere += String.Format(" and IsMark ='{0}'", 0);
}
if (family == false)
{
sqlWhere += String.Format(" and FamilyIncome ='{0}'", 0);
}
if (!String.IsNullOrEmpty(Request["searchMark"]))
{
hash.Add("IsMark", Request["searchMark"]);
}
if (!String.IsNullOrEmpty(Request["searchFamily"]))
{
hash.Add("FamilyIncome", Request["searchFamily"]);
}
#endregion
Tuple <HashTableExp, String> result = new Tuple <HashTableExp, string>(hash, sqlWhere);
return(result);
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="pageSize">每页多少条数据</param>
/// <param name="start">排除多少条数据</param>
/// <param name="filter">筛选条件</param>
/// <param name="total">总共多少条</param>
/// <returns>数据集合</returns>
public List <SalesVol> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere = "")
{
return(dLSalesVol.Select(pageSize, start, hash, out total, sqlWhere));
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="pageSize">每页多少条数据</param>
/// <param name="start">排除多少条数据</param>
/// <param name="filter">筛选条件</param>
/// <param name="total">总共多少条</param>
/// <returns>数据集合</returns>
public List <Teacher> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere)
{
return(dLTeacher.Select(pageSize, start, hash, out total, sqlWhere));
}
/// <summary>
/// 按条件查询所有符合条件的数据(不分页)
/// </summary>
/// <param name="filter">筛选条件</param>
/// <returns>信息集合</returns>
public List <Teacher> Select(HashTableExp hash, String sqlWhere = "")
{
return(dLTeacher.Select(hash, sqlWhere));
}
/// <summary>
/// 按条件查询所有符合条件的数据(不分页)
/// </summary>
/// <param name="filter">筛选条件</param>
/// <returns>信息集合</returns>
public List <BankCard> Select(HashTableExp hash, String sqlWhere = "")
{
return(dLBankCard.Select(hash, sqlWhere));
}
/// <summary>
/// 按条件删除数据
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public int Delete(HashTableExp hash, String sqlWhere = "")
{
return(dLTeacher.Delete(hash, sqlWhere));
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="pageSize">每页多少条数据</param>
/// <param name="start">排除多少条数据</param>
/// <param name="filter">筛选条件</param>
/// <param name="total">总共多少条</param>
/// <returns>数据集合</returns>
public List <VBankCard> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere = "")
{
return(dLBankCard.Select(pageSize, start, hash, out total, sqlWhere));
}
/// <summary>
/// 将dataTable保存到数据库
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public String SaveData(DataTable dt, String userId)
{
List <BankCard> list = new List <BankCard>();
String msg = "";
foreach (DataRow row in dt.Rows)
{
BankCard index = new BankCard();
String type = row["操作类型"].ToString();
//判断操作类型是否存在
HashTableExp hash = new HashTableExp("Name", type);
hash.Add("ParentId", "1000200000");
List <Diction> dictions = new BLDiction().Select(hash);
if (dictions.Count <= 0)
{
msg += "<br/>" + type + "不存在";
continue;
}
else
{
index.SaveType = dictions[0].Id;
}
//判断银行卡是否正确
type = row["银行卡名称"].ToString();
hash.Clear();
hash.Add("Name", type);
hash.Add("ParentId", "1000100000");
dictions = new BLDiction().Select(hash);
if (dictions.Count <= 0)
{
msg += "<br/>" + type + "不存在";
continue;
}
else
{
index.BankType = dictions[0].Id;
}
DateTime time;
//判断时间是否正确
if (!DateTime.TryParse(row["时间"].ToString(), out time))
{
msg += "<br/>" + row["时间"] + "不是时间类型";
continue;
}
else
{
index.Time = time;
}
double price;
//判断金额是否正确
if (!double.TryParse(row["金额"].ToString(), out price))
{
msg += "<br/>" + row["金额"] + "不是数字类型";
continue;
}
else
{
index.Price = price;
}
index.Id = Guid.NewGuid().ToString();
index.Note = row["备注"].ToString();
index.CreateBy = index.UpdateBy = userId;
index.CreateTime = index.UpdateTime = DateTime.Now;
list.Add(index);
}
int result = dLBankCard.Add(list);
if (result > 0)
{
return("成功保存" + result + "条数据." + msg);
}
else
{
return(msg);
}
}
/// <summary>
/// 按条件查询所有符合条件的数据(不分页)
/// </summary>
/// <param name="filter">筛选条件</param>
/// <returns>信息集合</returns>
public List <SalesVol> Select(HashTableExp hash, String sqlWhere = "")
{
return(dLSalesVol.Select(hash, sqlWhere));
}
/// <summary>
/// 按条件查询所有符合条件的数据(不分页)
/// </summary>
/// <param name="filter">筛选条件</param>
/// <returns>信息集合</returns>
public List <Article> Select(HashTableExp hash, String sqlWhere = "")
{
return(dLArticle.Select(hash, sqlWhere));
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <returns></returns>
public List <Users> Select(HashTableExp hash)
{
return(dLUsers.Select(hash, ""));
}
