/// <summary>
///获得数据列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable QueryCheckDiary(int pageIndex, int pageCount, string Begintime, string EndTimeStr, string UsersId)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" and 1=1");
if (Begintime != "" && Begintime != null)
{
strSqlWhere.Append(" and convert(varchar, addtime, 23)>='" + Begintime + "'");
}
if (EndTimeStr != "" && EndTimeStr != null)
{
strSqlWhere.Append(" and convert(varchar, addtime, 23)<='" + EndTimeStr + "'");
}
if (UsersId != "" && UsersId != null)
{
strSqlWhere.Append(" and UserId='" + UsersId + "'");
}
string strSql = @"select top " + pageCount.ToString() + " * from VIEW_Diary where Id not in (select top " + ((pageIndex - 1) * pageCount).ToString() + @" Id from Tab_Diary where 1=1" + strSqlWhere.ToString() + " order by addtime desc)" + strSqlWhere.ToString() + " order by addtime desc";
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
///查询出所有数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable QueryCheckRoles(int pageIndex, int pageCount, string RolesName)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" and 1=1");
if (!string.IsNullOrEmpty(RolesName))
{
strSqlWhere.Append(" and RolesName like '%" + RolesName + "%'");
}
string strSql = @"select top " + pageCount.ToString() + " * from tab_Roles where Id not in (select top " + ((pageIndex - 1) * pageCount).ToString() + @" Id from tab_Roles where 1=1" + strSqlWhere.ToString() + " order by id desc)" + strSqlWhere.ToString() + " order by id desc";
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
/// 获取导航内容
/// </summary>
public DataTable GetListForNav(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select *");
strSql.Append(" FROM Tab_Nav ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by StrSort asc");
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql.ToString()).Tables[0]);
}
/// <summary>
/// 登录
/// </summary>
/// <param name="name"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public DataTable login(string userNumber, string Password)
{
string sql = " where";
sql += " userNumber=@userNumber and passWord=@passWord";
string strSql = "select * from Tab_User " + sql;
SqlParameter[] sqlParams =
{
new SqlParameter("userNumber", userNumber),
new SqlParameter("passWord", Password)
};
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql, sqlParams).Tables[0]);
}
/// <summary>
///查询出所有数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable getAllNameByuserNumber(string Number)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" 1=1");
if (!string.IsNullOrEmpty(Number))
{
strSqlWhere.Append(" and userNumber = '" + Number + "'");
}
string strSql = "select top 1 * from Tab_User where " + strSqlWhere.ToString();
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
///查询出所有数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable getAllName(string name)
{
StringBuilder strSqlWhere = new StringBuilder();
strSqlWhere.Append(" 1=1");
if (!string.IsNullOrEmpty(name))
{
strSqlWhere.Append(" and userName like '%" + name + "%'");
}
string strSql = "select top 10 * from Tab_User where " + strSqlWhere.ToString();
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public TF.RunSafty.Entry.Roles GetModel(int Id)
{
string strSql = "select * from tab_Roles where Id='" + Id + "'";
DataSet ds = SqlHelps.ReturnDataSet(SqlHelps.SQLConnString, CommandType.Text, strSql);
TF.RunSafty.Entry.Roles rl = new TF.RunSafty.Entry.Roles();
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public TF.RunSafty.Entry.Diary GetModel(int Id)
{
string strSql = "select * from Tab_Diary where Id='" + Id + "'";
DataSet ds = SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql);
TF.RunSafty.Entry.Diary model = new TF.RunSafty.Entry.Diary();
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public TF.RunSafty.Entry.nav GetModel()
{
//该表无主键信息，请自定义主键/条件字段
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 TypeID,TypeParentID,typeName from Tab_Nav ");
strSql.Append(" where ");
SqlParameter[] parameters =
{
};
TF.RunSafty.Entry.nav model = new TF.RunSafty.Entry.nav();
DataSet ds = SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
///查询出所有数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable getAllRolesName()
{
string strSql = @"select id,RolesName from tab_Roles order by id desc";
return(SqlHelps.ReturnDataSet(SqlHelps.SQLConnString, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
/// 获取那个最大的typeid
/// </summary>
/// <param name="typeID"></param>
/// <returns></returns>
public DataSet GetMaxId(string typeID)
{
string strSql = "select max(typeID) as maxId from Tab_Nav where TypeParentID='" + typeID + "'";
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql.ToString()));
}
/// <summary>
///通过时间查询出在这段时间内每一天的数量
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable GetallCount(string beginTime, string endTime)
{
string strSql = "select convert(varchar(100), AddTime, 23) as strDate,count(*) as countNum from Tab_Diary where AddTime>='" + beginTime + "' and AddTime<='" + endTime + "' group by convert(varchar(100), AddTime, 23) order by strDate desc";
return(SqlHelps.ReturnDataSet(ConnectionString, CommandType.Text, strSql).Tables[0]);
}