/// <summary>
/// 获取所有港股交易商品
/// </summary>
/// <param name="HKCommodityCode">港股商品代码</param>
/// <param name="HKCommodityName">港股商品名称</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllHKCommodity(string HKCommodityCode, string HKCommodityName, int pageNo, int pageSize,
out int rowCount)
{
//条件查询
if ((HKCommodityCode != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(HKCommodityCode)) ||
(HKCommodityName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(HKCommodityName)))
{
SQL_SELECT_HKCOMMODITY += " AND (HKCommodityCode LIKE '%' + @HKCommodityCode + '%' ";
SQL_SELECT_HKCOMMODITY += " OR HKCommodityName LIKE '%' + @HKCommodityName + '%') ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_HKCOMMODITY);
if (HKCommodityCode != AppGlobalVariable.INIT_STRING && HKCommodityCode != string.Empty)
{
database.AddInParameter(dbCommand, "HKCommodityCode", DbType.String, HKCommodityCode);
}
if (HKCommodityName != AppGlobalVariable.INIT_STRING && HKCommodityName != string.Empty)
{
database.AddInParameter(dbCommand, "HKCommodityName", DbType.String, HKCommodityName);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_HKCOMMODITY, pageNo, pageSize,
out rowCount, "HK_Commodity"));
}
/// <summary>
/// 清算柜台分页查询
/// </summary>
/// <param name="counterQueryEntity">清算柜台实体</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">当前要显示的记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetPagingCounter(CT_Counter counterQueryEntity, int pageNo, int pageSize,
out int rowCount)
{
string SQL_SELECT_Counter =
@" select CouterID,name,IP,XiaDanServicePort,MaxValues,State,XiaDanServiceName,QueryServiceName,AccountServiceName,AccountServicePort,QueryServicePort,SendServiceName,SendPort FROM CT_Counter where 1=1 ";
if (counterQueryEntity.name != null && !string.IsNullOrEmpty(counterQueryEntity.name))
{
SQL_SELECT_Counter += "AND name LIKE '%' + @name + '%' ";
}
if (counterQueryEntity.IP != null && !string.IsNullOrEmpty(counterQueryEntity.IP))
{
SQL_SELECT_Counter += "AND IP=@IP ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_Counter);
if (counterQueryEntity.name != null && !string.IsNullOrEmpty(counterQueryEntity.name))
{
database.AddInParameter(dbCommand, "name", DbType.String, counterQueryEntity.name);
}
if (counterQueryEntity.IP != null && !string.IsNullOrEmpty(counterQueryEntity.IP))
{
database.AddInParameter(dbCommand, "IP", DbType.String, counterQueryEntity.IP);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_Counter, pageNo, pageSize, out rowCount,
"UM_Counter"));
}
/// <summary>
/// 获取所有现货交易费用
/// </summary>
/// <param name="BreedClassID">品种ID</param>
/// <param name="BreedClassName">品种名称</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllSpotCosts(int BreedClassID, string BreedClassName, int pageNo, int pageSize,
out int rowCount)
{
//条件查询(暂不需要根据品种ID查询)
if (BreedClassID != AppGlobalVariable.INIT_INT)
{
SQL_SELECT_XHSPOTCOSTS += "AND (BreedClassID=@BreedClassID) ";
}
if (BreedClassName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(BreedClassName))
{
SQL_SELECT_XHSPOTCOSTS += "AND (BreedClassName LIKE '%' + @BreedClassName + '%') ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_XHSPOTCOSTS);
if (BreedClassID != AppGlobalVariable.INIT_INT)
{
database.AddInParameter(dbCommand, "BreedClassID", DbType.Int32, BreedClassID);
}
if (BreedClassName != AppGlobalVariable.INIT_STRING && BreedClassName != string.Empty)
{
database.AddInParameter(dbCommand, "BreedClassName", DbType.String, BreedClassName);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_XHSPOTCOSTS, pageNo, pageSize,
out rowCount, "XH_SpotConsts"));
}
/// <summary>
/// 获取所有交易商品品种
/// </summary>
/// <param name="BreedClassTypeID">品种类型ID</param>
/// <param name="BourseTypeID">交易所类型ID</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllCMBreedClass(int BreedClassTypeID, int BourseTypeID, int pageNo, int pageSize,
out int rowCount)
{
//条件查询
if (BreedClassTypeID != AppGlobalVariable.INIT_INT)
{
SQL_SELECTALL_CMBREEDCLASS += "AND (BreedClassTypeID=@BreedClassTypeID) ";
}
if (BourseTypeID != AppGlobalVariable.INIT_INT)
{
SQL_SELECTALL_CMBREEDCLASS += "AND (BourseTypeID=@BourseTypeID) ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECTALL_CMBREEDCLASS);
if (BreedClassTypeID != AppGlobalVariable.INIT_INT)
{
database.AddInParameter(dbCommand, "BreedClassTypeID", DbType.Int32, BreedClassTypeID);
}
if (BourseTypeID != AppGlobalVariable.INIT_INT)
{
database.AddInParameter(dbCommand, "BourseTypeID", DbType.Int32, BourseTypeID);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECTALL_CMBREEDCLASS, pageNo, pageSize,
out rowCount, "CM_BreedClass"));
}
/// <summary>
/// 分页查询权限组
/// </summary>
/// <param name="strwhere">条件</param>
/// <param name="pageNo">第几页</param>
/// <param name="pageSize">每页大小</param>
/// <param name="rowCount">总记录数</param>
/// <returns>返回DataSet</returns>
public DataSet GetPagingManagerGroup(string strwhere, int pageNo, int pageSize, out int rowCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ManagerGroupID,ManagerGroupName ");
strSql.Append(" FROM UM_ManagerGroup ");
if (strwhere.Trim() != "")
{
strSql.Append(" where " + strwhere);
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(strSql.ToString());
return(CommPager.QueryPager(database, dbCommand, strSql.ToString(), pageNo, pageSize, out rowCount, "UM_ManagerGroup"));
}
/// <summary>
/// 获取所有交易规则_交易方向_交易单位_交易量(最小交易单位)
/// </summary>
/// <param name="BreedClassName">品种名称</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllXHMinVolumeOfBusiness(string BreedClassName, int pageNo, int pageSize, out int rowCount)
{
//条件查询
if (BreedClassName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(BreedClassName))
{
SQL_SELECT_XHMINVOLUMEOFBUSINESS += "AND (BreedClassName LIKE '%' + @BreedClassName + '%') ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_XHMINVOLUMEOFBUSINESS);
if (BreedClassName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(BreedClassName))
{
database.AddInParameter(dbCommand, "BreedClassName", DbType.String, BreedClassName);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_XHMINVOLUMEOFBUSINESS, pageNo, pageSize,
out rowCount, "XH_MinVolumeOfBusiness"));
}
/// <summary>
/// 获取所有现货交易规则
/// </summary>
/// <param name="BreedClassName">品种名称</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllSpotTradeRules(string BreedClassName, int pageNo, int pageSize,
out int rowCount)
{
//条件查询
if (BreedClassName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(BreedClassName))
{
SQL_SELECT_XHSPOTTRADERULES += "AND (BreedClassName LIKE '%' + @BreedClassName + '%') ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_XHSPOTTRADERULES);
if (BreedClassName != AppGlobalVariable.INIT_STRING && BreedClassName != string.Empty)
{
database.AddInParameter(dbCommand, "BreedClassName", DbType.String, BreedClassName);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_XHSPOTTRADERULES, pageNo, pageSize,
out rowCount, "XH_SpotTradeRules"));
}
/// <summary>
/// 获取所有股指期货持仓限制和品种_股指期货_保证金数据
/// </summary>
/// <param name="BreedClassName">品种名称</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllQHSIFPositionAndQHSIFBail(string BreedClassName, int pageNo, int pageSize,
out int rowCount)
{
//条件查询
if (BreedClassName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(BreedClassName))
{
SQL_SELECT_QHSIFPOSITIONANDQHSIFBAIL += "AND (BreedClassName LIKE '%' + @BreedClassName + '%') ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_QHSIFPOSITIONANDQHSIFBAIL);
if (BreedClassName != AppGlobalVariable.INIT_STRING && BreedClassName != string.Empty)
{
database.AddInParameter(dbCommand, "BreedClassName", DbType.String, BreedClassName);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_QHSIFPOSITIONANDQHSIFBAIL, pageNo, pageSize,
out rowCount, "QHSIFPositionAndQHSIFBail"));
}
/// <summary>
/// 获取所有可交易商品_熔断
/// </summary>
/// <param name="CommodityCode">商品代码</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllCMCommodityFuse(string CommodityCode, int pageNo, int pageSize,
out int rowCount)
{
//条件查询
if (CommodityCode != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(CommodityCode))
{
SQL_SELECTBREEDCLASSNAME_CMCOMMODITYFUSES += "AND (A.CommodityCode LIKE '%' + @CommodityCode + '%') ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECTBREEDCLASSNAME_CMCOMMODITYFUSES);
if (CommodityCode != AppGlobalVariable.INIT_STRING && CommodityCode != string.Empty)
{
database.AddInParameter(dbCommand, "CommodityCode", DbType.String, CommodityCode);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECTBREEDCLASSNAME_CMCOMMODITYFUSES, pageNo, pageSize,
out rowCount, "CM_CommodityFuse"));
}
/// <summary>
/// 获取所有交易所类型_交易时间
/// </summary>
/// <param name="BourseTypeName">交易所类型名称</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllCMTradeTime(string BourseTypeName, int pageNo, int pageSize,
out int rowCount)
{
//条件查询
if (BourseTypeName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(BourseTypeName))
{
SQL_SELECT_CMTRADETIME += "AND (BourseTypeName LIKE '%' + @BourseTypeName + '%') ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_CMTRADETIME);
if (BourseTypeName != AppGlobalVariable.INIT_STRING && BourseTypeName != string.Empty)
{
database.AddInParameter(dbCommand, "BourseTypeName", DbType.String, BourseTypeName);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_CMTRADETIME, pageNo, pageSize,
out rowCount, "CM_TradeTime"));
}
/// <summary>
/// 追加资金历史分页查询
/// </summary>
/// <param name="fundAddQueryEntity">追加资金实体</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetPagingFund(ManagementCenter.Model.UserManage.FundAddQueryEntity fundAddQueryEntity, int pageNo, int pageSize,
out int rowCount)
{
string SQL_SELECT_Fund =
@" select AddFundID,ManagerID,UM_FundAddInfo.UserID,USNumber,RMBNumber,UM_FundAddInfo.AddTime,
HKNumber,UM_FundAddInfo.Remark,LoginName
FROM UM_FundAddInfo,UM_UserInfo
WHERE UM_FundAddInfo.ManagerID=UM_UserInfo.UserID ";
if (fundAddQueryEntity.UserID != int.MaxValue)
{
SQL_SELECT_Fund += "AND UM_FundAddInfo.UserID=@UserID ";
}
//if (fundAddQueryEntity.ManagerID != int.MaxValue)
//{
// SQL_SELECT_Fund += "AND ManagerID=@ManagerID ";
//}
if (fundAddQueryEntity.loginName != null && !string.IsNullOrEmpty(fundAddQueryEntity.loginName))
{
SQL_SELECT_Fund += "AND LoginName LIKE '%' + @LoginName + '%' ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_Fund);
if (fundAddQueryEntity.UserID != int.MaxValue)
{
database.AddInParameter(dbCommand, "UserID", DbType.Int32, fundAddQueryEntity.UserID);
}
//if (fundAddQueryEntity.ManagerID != int.MaxValue)
//{
// database.AddInParameter(dbCommand, "ManagerID", DbType.Int32, fundAddQueryEntity.ManagerID);
//}
//登陆名称(根据要求把按管理员编号查询改成按管理员登陆名称查询)
if (fundAddQueryEntity.loginName != null && !string.IsNullOrEmpty(fundAddQueryEntity.loginName))
{
database.AddInParameter(dbCommand, "LoginName", DbType.String, fundAddQueryEntity.loginName);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_Fund, pageNo, pageSize, out rowCount,
"UM_FundAddInfo"));
}
/// <summary>
/// 获取所有现货_品种_交易单位换算
/// </summary>
/// <param name="BreedClassName">品种名称</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllCMUnitConversion(string BreedClassName, int pageNo, int pageSize,
out int rowCount)
{
//条件查询
if (BreedClassName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(BreedClassName))
{
SQL_SELECT_CMUNITCONVERSION += "AND (BreedClassName LIKE '%' + @BreedClassName + '%') ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_CMUNITCONVERSION);
if (BreedClassName != AppGlobalVariable.INIT_STRING && BreedClassName != string.Empty)
{
database.AddInParameter(dbCommand, "BreedClassName", DbType.String, BreedClassName);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_CMUNITCONVERSION, pageNo, pageSize,
out rowCount, "CM_UnitConversion"));
}
/// <summary>
///获取所有(商品)期货_持仓限制
/// </summary>
/// <param name="BreedClassName">品种名称</param>
/// <param name="DeliveryMonthType">交割月份类型标识</param>
/// <param name="PositionBailTypeID">持仓和保证金控制类型标识</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllQHPositionLimitValue(string BreedClassName, int DeliveryMonthType, int PositionBailTypeID,
int pageNo, int pageSize,
out int rowCount)
{
//条件查询
if (BreedClassName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(BreedClassName))
{
SQL_SELECT_QHPOSITIONLIMITVALUE += "AND (BreedClassName LIKE '%' + @BreedClassName + '%') ";
}
if (DeliveryMonthType != AppGlobalVariable.INIT_INT)
{
SQL_SELECT_QHPOSITIONLIMITVALUE += "AND (DeliveryMonthType=@DeliveryMonthType) ";
}
if (PositionBailTypeID != AppGlobalVariable.INIT_INT)
{
SQL_SELECT_QHPOSITIONLIMITVALUE += "AND (PositionBailTypeID=@PositionBailTypeID) ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_QHPOSITIONLIMITVALUE);
if (BreedClassName != AppGlobalVariable.INIT_STRING && BreedClassName != string.Empty)
{
database.AddInParameter(dbCommand, "BreedClassName", DbType.String, BreedClassName);
}
if (DeliveryMonthType != AppGlobalVariable.INIT_INT)
{
database.AddInParameter(dbCommand, "DeliveryMonthType", DbType.Int32, DeliveryMonthType);
}
if (PositionBailTypeID != AppGlobalVariable.INIT_INT)
{
database.AddInParameter(dbCommand, "PositionBailTypeID", DbType.Int32, PositionBailTypeID);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_QHPOSITIONLIMITVALUE, pageNo, pageSize,
out rowCount, "QH_PositionLimitValue"));
}
/// <summary>
/// 撮合机分页查询
/// </summary>
/// <param name="machineQueryEntity">撮合机实休</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetPagingMachine(ManagementCenter.Model.RC_MatchMachine machineQueryEntity, int pageNo,
int pageSize,
out int rowCount)
{
string SQL_SELECT_Machine =
@" select MatchMachineID,MatchMachineName,BourseTypeID,MatchCenterID FROM RC_MatchMachine where 1=1 ";
if (machineQueryEntity.BourseTypeID != int.MaxValue)
{
SQL_SELECT_Machine += "AND BourseTypeID=@BourseTypeID ";
}
if (machineQueryEntity.MatchCenterID != int.MaxValue)
{
SQL_SELECT_Machine += "AND MatchCenterID=@MatchCenterID ";
}
if (machineQueryEntity.MatchMachineID != int.MaxValue)
{
SQL_SELECT_Machine += "AND MatchMachineID=@MatchMachineID ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_Machine);
if (machineQueryEntity.BourseTypeID != int.MaxValue)
{
database.AddInParameter(dbCommand, "BourseTypeID", DbType.Int32, machineQueryEntity.BourseTypeID);
}
if (machineQueryEntity.MatchCenterID != int.MaxValue)
{
database.AddInParameter(dbCommand, "MatchCenterID", DbType.Int32, machineQueryEntity.MatchCenterID);
}
if (machineQueryEntity.MatchMachineID != int.MaxValue)
{
database.AddInParameter(dbCommand, "MatchMachineID", DbType.Int32, machineQueryEntity.MatchMachineID);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_Machine, pageNo, pageSize, out rowCount,
"UM_Machine"));
}
/// <summary>
/// 获取所有交易商品
/// </summary>
/// <param name="CommodityCode">商品代码</param>
/// <param name="CommodityName">商品名称</param>
/// <param name="BreedClassID">品种ID</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">显示记录数</param>
/// <param name="rowCount">总行数</param>
/// <returns></returns>
public DataSet GetAllCMCommodity(string CommodityCode, string CommodityName, int BreedClassID, int pageNo, int pageSize,
out int rowCount)
{
//条件查询
if ((CommodityCode != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(CommodityCode)) ||
(CommodityName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(CommodityName)))
{
SQL_SELECT_CMCOMMODITY += " AND (CommodityCode LIKE '%' + @CommodityCode + '%' ";
//}
//if (CommodityName != AppGlobalVariable.INIT_STRING && !string.IsNullOrEmpty(CommodityName))
//{
SQL_SELECT_CMCOMMODITY += " OR CommodityName LIKE '%' + @CommodityName + '%') ";
}
if (BreedClassID != AppGlobalVariable.INIT_INT)
{
SQL_SELECT_CMCOMMODITY += "AND (BreedClassID=@BreedClassID) ";
}
SQL_SELECT_CMCOMMODITY += " AND ISEXPIRED<>1 ";
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_CMCOMMODITY);
if (CommodityCode != AppGlobalVariable.INIT_STRING && CommodityCode != string.Empty)
{
database.AddInParameter(dbCommand, "CommodityCode", DbType.String, CommodityCode);
}
if (CommodityName != AppGlobalVariable.INIT_STRING && CommodityName != string.Empty)
{
database.AddInParameter(dbCommand, "CommodityName", DbType.String, CommodityName);
}
if (BreedClassID != AppGlobalVariable.INIT_INT)
{
database.AddInParameter(dbCommand, "BreedClassID", DbType.Int32, BreedClassID);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_CMCOMMODITY, pageNo, pageSize,
out rowCount, "CM_Commodity"));
}
//=================================================管理员==========================================
#region 管理员分页查询
/// <summary>
/// 分页查询用户
/// </summary>
/// <param name="managerQueryEntity">查询条件 海外用户实体</param>
/// <param name="pageNo">页码</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="rowCount">总页数</param>
/// <returns></returns>
public DataSet GetPagingManager(ManagementCenter.Model.UserManage.ManagerQueryEntity managerQueryEntity, int pageNo, int pageSize, out int rowCount)
{
string SQL_SELECT_Manager = @"select UM_UserInfo.*,UM_ManagerBeloneToGroup.ManagerGroupID,UM_ManagerGroup.ManagerGroupName
From UM_UserInfo,UM_ManagerBeloneToGroup,UM_ManagerGroup
Where UM_UserInfo.UserID=UM_ManagerBeloneToGroup.UserID
AND UM_ManagerBeloneToGroup.ManagerGroupID=UM_ManagerGroup.ManagerGroupID ";
if (managerQueryEntity.LoginName != null && !string.IsNullOrEmpty(managerQueryEntity.LoginName))
{
SQL_SELECT_Manager += "AND LoginName LIKE '%' + @LoginName + '%' ";
}
if (managerQueryEntity.UserName != null && !string.IsNullOrEmpty(managerQueryEntity.UserName))
{
SQL_SELECT_Manager += "AND UserName LIKE '%' + @UserName + '%' ";
}
if (managerQueryEntity.UserID != int.MaxValue)
{
SQL_SELECT_Manager += "AND UM_UserInfo.UserID=@UserID ";
}
if (managerQueryEntity.RoleID != int.MaxValue)
{
SQL_SELECT_Manager += "AND RoleID=@RoleID ";
}
if (managerQueryEntity.ManagerGroupID != int.MaxValue)
{
SQL_SELECT_Manager += "AND ManagerGroupID=@ManagerGroupID ";
}
if (managerQueryEntity.ManagerGroupName != null && !string.IsNullOrEmpty(managerQueryEntity.ManagerGroupName))
{
SQL_SELECT_Manager += "AND ManagerGroupName LIKE '%' + @ManagerGroupName + '%' ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_Manager);
if (managerQueryEntity.LoginName != null && !string.IsNullOrEmpty(managerQueryEntity.LoginName))
{
database.AddInParameter(dbCommand, "LoginName", DbType.String, managerQueryEntity.LoginName);
}
if (managerQueryEntity.UserName != null && !string.IsNullOrEmpty(managerQueryEntity.UserName))
{
database.AddInParameter(dbCommand, "UserName", DbType.String, managerQueryEntity.UserName);
}
if (managerQueryEntity.UserID != int.MaxValue)
{
database.AddInParameter(dbCommand, "UserID", DbType.Int32, managerQueryEntity.UserID);
}
if (managerQueryEntity.RoleID != int.MaxValue)
{
database.AddInParameter(dbCommand, "RoleID", DbType.Int32, managerQueryEntity.RoleID);
}
if (managerQueryEntity.ManagerGroupID != int.MaxValue)
{
database.AddInParameter(dbCommand, "ManagerGroupID", DbType.Int32, managerQueryEntity.ManagerGroupID);
}
if (managerQueryEntity.ManagerGroupName != null && !string.IsNullOrEmpty(managerQueryEntity.ManagerGroupName))
{
database.AddInParameter(dbCommand, "ManagerGroupName", DbType.String, managerQueryEntity.ManagerGroupName);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_Manager, pageNo, pageSize, out rowCount, "UM_UserInfo"));
}
//=================================================交易员==========================================
#region 交易员分页查询
/// <summary>
/// 分页查询用户
/// </summary>
/// <param name="userInfo">查询条件 用户实体</param>
/// <param name="pageNo">页码</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="rowCount">总页数</param>
/// <returns></returns>
public DataSet GetPagingUser(Model.UM_UserInfo userInfo, int pageNo, int pageSize, out int rowCount)
{
// string SQL_SELECT_CUSTOMER =
//@"select UserName,LoginName,Password,UserID,CertificateStyle,Postalcode,RoleID,CertificateNo,Telephone,Address,Email,QuestionID,Answer,CouterID,Remark,AddType,AddTime FROM UM_UserInfo where 1=1 ";
string SQL_SELECT_CUSTOMER =
@"select a.*,b.name from UM_UserInfo a,CT_Counter b where b.CouterID=a.CouterID ";
if (userInfo.LoginName != null && !string.IsNullOrEmpty(userInfo.LoginName))
{
SQL_SELECT_CUSTOMER += "AND LoginName LIKE '%' + @LoginName + '%' ";
}
if (userInfo.UserName != null && !string.IsNullOrEmpty(userInfo.UserName))
{
SQL_SELECT_CUSTOMER += "AND UserName LIKE '%' + @UserName + '%' ";
}
//if (userInfo.CouterID != int.MaxValue)
//{
// SQL_SELECT_CUSTOMER += "AND CouterID=@CouterID ";
//}
if (userInfo.UserID != int.MaxValue)
{
SQL_SELECT_CUSTOMER += "AND UserID=@UserID ";
}
if (userInfo.RoleID != int.MaxValue)
{
SQL_SELECT_CUSTOMER += "AND RoleID=@RoleID ";
}
if (userInfo.Name != null && !string.IsNullOrEmpty(userInfo.Name)) //柜台名称
{
SQL_SELECT_CUSTOMER += "AND name LIKE '%' + @name + '%' ";
}
Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetSqlStringCommand(SQL_SELECT_CUSTOMER);
if (userInfo.LoginName != null && !string.IsNullOrEmpty(userInfo.LoginName))
{
database.AddInParameter(dbCommand, "LoginName", DbType.String, userInfo.LoginName);
}
if (userInfo.UserName != null && !string.IsNullOrEmpty(userInfo.UserName))
{
database.AddInParameter(dbCommand, "UserName", DbType.String, userInfo.UserName);
}
//if (userInfo.CouterID != int.MaxValue)
//{
// database.AddInParameter(dbCommand, "CouterID", DbType.Int32, userInfo.CouterID);
//}
if (userInfo.UserID != int.MaxValue)
{
database.AddInParameter(dbCommand, "UserID", DbType.Int32, userInfo.UserID);
}
if (userInfo.RoleID != int.MaxValue)
{
database.AddInParameter(dbCommand, "RoleID", DbType.Int32, userInfo.RoleID);
}
if (userInfo.Name != null && !string.IsNullOrEmpty(userInfo.Name)) //柜台名称
{
database.AddInParameter(dbCommand, "name", DbType.String, userInfo.Name);
}
return(CommPager.QueryPager(database, dbCommand, SQL_SELECT_CUSTOMER, pageNo, pageSize, out rowCount, "TSM_CUSTOMER"));
}
