public DataTable GetFileDTByPage(out int recordCount, int pageIndex = 1, int pageSize = 20, string whereConditional = "", string order = "")
{
this.SelectField = CiFileTable.FieldId
+ " ," + CiFileTable.FieldFolderId
+ " ," + CiFileTable.FieldFileName
+ " ," + CiFileTable.FieldFilePath
+ " ," + CiFileTable.FieldFileSize
+ " ," + CiFileTable.FieldReadCount
+ " ," + CiFileTable.FieldCategory
+ " ," + CiFileTable.FieldDescription
+ " ," + CiFileTable.FieldEnabled
+ " ," + CiFileTable.FieldSortCode
+ " ," + CiFileTable.FieldCreateUserId
+ " ," + CiFileTable.FieldCreateBy
+ " ," + CiFileTable.FieldCreateOn
+ " ," + CiFileTable.FieldModifiedUserId
+ " ," + CiFileTable.FieldModifiedBy
+ " ," + CiFileTable.FieldModifiedOn
+ " , (SELECT " + CiFolderTable.FieldFolderName
+ " FROM " + CiFolderTable.TableName
+ " WHERE " + CiFolderTable.FieldId + " = '" + CiFileTable.FieldFolderId +
"') AS FolderFullName ";
recordCount = DbCommonLibary.GetCount(DBProvider, this.CurrentTableName, whereConditional);
return(DbCommonLibary.GetDTByPage(DBProvider, this.CurrentTableName, this.SelectField, pageIndex, pageSize, whereConditional, order));
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="searchValue">查询字段</param>
/// <param name="departmentId">部门主键</param>
/// <param name="roleId">角色主键</param>
/// <param name="recordCount">记录数</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示</param>
/// <param name="order">排序</param>
/// <returns>数据表</returns>
public DataTable GetDTByPage(string searchValue, string departmentId, string roleId, out int recordCount, int pageIndex = 0, int pageSize = 20, string order = null)
{
string whereConditional = PiUserTable.TableName + "." + PiUserTable.FieldDeleteMark + " = 0 "
+ " AND " + PiUserTable.TableName + "." + PiUserTable.FieldEnabled + " = 1 "
+ " AND " + PiUserTable.TableName + "." + PiUserTable.FieldIsVisible + " = 1 ";
if (!String.IsNullOrEmpty(departmentId))
{
/*
* 用非递归调用的建议方法
* sqlQuery += " AND " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId
+ " IN ( SELECT " + PiOrganizeTable.FieldId
+ " FROM " + BaseOrganizeEntity.TableName
+ " WHERE " + PiOrganizeTable.FieldId + " = " + departmentId + " OR " + PiOrganizeTable.FieldParentId + " = " + departmentId + ")";
*/
PiOrganizeManager organizeManager = new PiOrganizeManager(this.DBProvider, this.UserInfo);
string[] organizeIds = organizeManager.GetChildrensId(PiOrganizeTable.FieldId, departmentId, PiOrganizeTable.FieldParentId);
if (organizeIds != null && organizeIds.Length > 0)
{
whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldCompanyId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")"
+ " OR " + PiUserTable.TableName + "." + PiUserTable.FieldSubCompanyId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")"
+ " OR " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")"
+ " OR " + PiUserTable.TableName + "." + PiUserTable.FieldSubDepartmentId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")"
+ " OR " + PiUserTable.TableName + "." + PiUserTable.FieldWorkgroupId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + "))";
}
}
if (!string.IsNullOrEmpty(roleId))
{
string tableNameUserRole = PiUserRoleTable.TableName;
whereConditional += " AND ( " + PiUserTable.TableName + "." + PiUserTable.FieldId + " IN "
+ " (SELECT + " + PiUserRoleTable.FieldUserId
+ " FROM " + tableNameUserRole
+ " WHERE " + PiUserRoleTable.FieldRoleId + " = '" + roleId + "'"
+ " AND " + PiUserRoleTable.FieldEnabled + " = 1"
+ " AND " + PiUserRoleTable.FieldDeleteMark + " = 0)) ";
}
if (!string.IsNullOrEmpty(searchValue))
{
searchValue = "'" + StringHelper.GetSearchString(searchValue) + "'";
whereConditional += " AND (" + PiUserTable.FieldRealName + " LIKE " + searchValue;
whereConditional += " OR " + PiUserTable.FieldUserName + " LIKE " + searchValue + ")";
//whereConditional += " AND (" + searchValue + ")"; ;
}
recordCount = DbCommonLibary.GetCount(DBProvider, this.CurrentTableName, whereConditional);
this.CurrentTableName = "PIUSER LEFT OUTER JOIN PIUSERLOGON ON PIUSER.ID = PIUSERLOGON.ID ";
switch (DBProvider.CurrentDbType)
{
case CurrentDbType.SqlServer:
case CurrentDbType.Access:
case CurrentDbType.Oracle:
case CurrentDbType.MySql:
this.SelectField = PiUserTable.TableName + ".* "
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldFirstVisit
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldPreviousVisit
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldLastVisit
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldIPAddress
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldMACAddress
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldLogOnCount
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldUserOnLine;
break;
case CurrentDbType.DB2:
break;
}
return(DbCommonLibary.GetDTByPage(DBProvider, this.CurrentTableName, this.SelectField, pageIndex, pageSize, whereConditional, order));
}
/// <summary>
/// 获取分页DataTable
/// </summary>
/// <param name="recordCount">记录总数</param>
/// <param name="currentPage">当前页数</param>
/// <param name="perPage">每页显示多少条</param>
/// <param name="whereConditional">条件</param>
/// <param name="order">排序字段</param>
/// <returns>数据表</returns>
public virtual DataTable GetDT(out int recordCount, int currentPage, int perPage, string whereConditional, string order)
{
recordCount = DbCommonLibary.GetCount(DBProvider, this.CurrentTableName, whereConditional);
return(DbCommonLibary.GetDT(DBProvider, this.CurrentTableName, currentPage, perPage, whereConditional, order));
}
/// <summary>
/// 获取分页DataTable
/// </summary>
/// <param name="recordCount">记录总数</param>
/// <param name="pageIndex">当前页数</param>
/// <param name="pageSize">每页显示多少条</param>
/// <param name="whereConditional">条件</param>
/// <param name="order">排序字段</param>
/// <returns>数据表</returns>
public virtual DataTable GetDTByPage(out int recordCount, int pageIndex, int pageSize, string whereConditional, string order)
{
recordCount = DbCommonLibary.GetCount(DBProvider, this.CurrentTableName, whereConditional);
return(DbCommonLibary.GetDTByPage(DBProvider, this.CurrentTableName, this.SelectField, pageIndex, pageSize, whereConditional, order));
}
public virtual IDataReader GetDataReaderByPage(out int recordCount, int pageIndex, int pageSize, string whereClause, IDbDataParameter[] dbParameters, string order)
{
recordCount = DbCommonLibary.GetCount(this.DBProvider, this.CurrentTableName, whereClause, dbParameters);
return(DbCommonLibary.GetDataReaderByPage(this.DBProvider, this.CurrentTableName, this.SelectField, pageIndex, pageSize, whereClause, dbParameters, order));
}
