/// <summary>
/// 获取指定会员信息
/// 作者：姚东
/// 时间：20100919
/// </summary>
/// <returns></returns>
public DataTable GetInfoHuiYuan(string huiYuanID)
{
string sql = "SELECT ID, LoginAcc, LoginPWD, Name, Email, Tel, CreateTime, TotalPoint, RemainPoint, Status,StatusName,UserID " +
" FROM UV_QS_HuiYuan_Point where ID='" + huiYuanID + "'";
return(NDDBAccess.Fill(sql));
}
/// <summary>
/// 根据代码组键值获得代码信息
/// 当CodeValue值为数字时可调用此函数
/// </summary>
/// <param name="CodeGroupKey"></param>
/// <returns></returns>
public DataTable GetAllCode2(string CodeGroupKey)
{
string sql = "SELECT * FROM UT_SYS_Code WHERE IsDeleted = 0 AND CodeGroupID in (SELECT CodeGroupID FROM UT_SYS_CodeGroup WHERE CodeGroupKey = '" + CodeGroupKey + "') ORDER BY CAST(CodeValue AS INT) ";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 获取指定会员信息
/// 作者：姚东
/// 时间：20100925
/// </summary>
/// <returns></returns>
public DataTable GetInfoHuiYuan(string account, string pwd)
{
string sql = "SELECT ID, LoginAcc, LoginPWD, Name, Email, Tel, CreateTime, TotalPoint, RemainPoint, Status,StatusName,UserID " +
" FROM UV_QS_HuiYuan_Point where LoginAcc='" + account + "' and LoginPWD='" + pwd + "'";
return(NDDBAccess.Fill(sql));
}
/// <summary>
/// 根据代码组ID获得代码信息
/// </summary>
/// <param name="CodeGroupID"></param>
/// <returns></returns>
public DataTable GetAllCode(long CodeGroupID)
{
string sql = "SELECT * FROM UT_SYS_Code WHERE IsDeleted = 0 AND CodeGroupID = " + CodeGroupID + " ORDER BY CodeValue";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 邦定下拉框
/// </summary>
/// <returns></returns>
public DataTable GetMenuFunction(long MenuID)
{
string sql = "SELECT * FROM UT_SYS_MenuFunction WHERE IsDeleted = 0 AND IsShow = 1 AND MenuID = " + MenuID + " ORDER BY SortIndex";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 根据代码ID获得子代码信息
/// </summary>
/// <param name="CodeID"></param>
/// <returns></returns>
public DataTable GetCodeDetail(long CodeID)
{
string sql = "SELECT * FROM UT_SYS_CodeDetail WHERE IsDeleted = 0 AND CodeID = " + CodeID;
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
public DataTable GetCode()
{
string sql = "SELECT CodeID,CodeName,CodeValue FROM UT_SYS_Code WHERE IsDeleted = 0 ORDER BY SortOrder";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 获得部门最大排序号
/// </summary>
/// <returns></returns>
public static int GetMaxSortIndex()
{
string sql = "SELECT MAX(sortIndex) + 1 FROM UT_SYS_OU";
DataTable dt = NDDBAccess.Fill(sql);
return(NDConvert.ToInt32(dt.Rows[0][0].ToString()));
}
/// <summary>
/// 根据父级部门ID获得下级部门信息
/// </summary>
/// <param name="OUParentID"></param>
/// <returns></returns>
public DataTable GetOU1(long OUID)
{
string sql = "SELECT OUID,OUName FROM UT_SYS_OU WHERE IsDeleted = 0 AND OUID=" + OUID;
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
public DataTable GetParentOU(long ParentID)
{
string sql = string.Format("SELECT OUID,OUName FROM UT_SYS_OU WHERE IsDeleted = 0 AND OUParentID={0} ORDER BY SortIndex", ParentID);
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 根据父级部门ID获得下级部门信息
/// </summary>
/// <param name="OUParentID"></param>
/// <returns></returns>
public DataTable GetOU(long OUParentID)
{
string sql = "SELECT OUID,OUName FROM UT_SYS_OU WHERE IsDeleted = 0 AND OUParentID=" + OUParentID + " ORDER BY SortIndex";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 删除群组菜单权限中的功能
/// </summary>
/// <param name="EmployeID"></param>
/// <returns></returns>
public bool DeleteMenuFunctionGroupRight(long GroupID)
{
string sql = "DELETE UT_SYS_MenuFunctionGroupRight WHERE GroupID = " + GroupID;
NDDBAccess.ExecuteNonQuery(sql);
return(true);
}
public static int GetMaxSortIndex(long OUParentID)
{
string sql = string.Format("SELECT MAX(sortIndex) + 1 FROM UT_SYS_OU where OUParentID={0}", OUParentID);
DataTable dt = NDDBAccess.Fill(sql);
return(NDConvert.ToInt32(dt.Rows[0][0].ToString()));
}
/// <summary>
/// 删除用户菜单权限中的功能
/// </summary>
/// <param name="EmployeID"></param>
/// <returns></returns>
public bool DeleteUserMenuFunctionRight(long UserID)
{
string sql = "DELETE UT_SYS_MenuFunctionRight WHERE UserID = " + UserID;
NDDBAccess.ExecuteNonQuery(sql);
return(true);
}
/// <summary>
/// 根据父级部门ID获得下级部门信息
/// </summary>
/// <param name="OUParentID"></param>
/// <returns></returns>
public DataTable GetAllCodeGroup()
{
string sql = "SELECT * FROM UT_SYS_CodeGroup WHERE IsDeleted = 0 ORDER BY SortOrder DESC";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 获取最大排序号
/// </summary>
/// <returns></returns>
public static int MaxSortIndex(long MenuID)
{
string sql = "SELECT MAX(SortIndex) FROM UT_SYS_MenuFunction WHERE IsDeleted = 0 AND MenuID = " + MenuID;
DataTable dt = NDDBAccess.Fill(sql);
return(NDConvert.ToInt32(dt.Rows[0][0].ToString()) + 1);
}
/// <summary>
/// 获得个人用户菜单权限
/// </summary>
/// <param name="PersonID"></param>
/// <param name="MenuID"></param>
/// <returns></returns>
//public static bool GetUserRightQuery(long UserID, long MenuID)
//{
// string sql = "SELECT Count(MenuID) FROM UT_SYS_Right WHERE UserID = " + UserID + " AND MenuID = " + MenuID;
// DataTable dt = NDDBAccess.Fill(sql);
// if (NDConvert.ToInt32(dt.Rows[0][0].ToString()) > 0)
// {
// return true;
// }
// return false;
//}
/// <summary>
/// 根据用户ＩＤ获得用户权限
/// </summary>
/// <param name="PersonID"></param>
/// <returns></returns>
public DataTable GetUserRight(long UserID)
{
string sql = "SELECT MenuID FROM UT_SYS_Right WHERE UserID = " + UserID;
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 根据菜单类型获得用户操作菜单
/// </summary>
/// <param name="Account"></param>
/// <returns></returns>
public DataTable GetMenuAll2(long parentMenuID)
{
string sql = "SELECT * FROM UT_SYS_Menu WHERE IsDeleted = 0 AND ParentMenuID = " + parentMenuID + " ORDER BY SortOrder";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 根据菜单类型获得用户操作菜单
/// </summary>
/// <param name="Account"></param>
/// <returns></returns>
public DataTable GetMenuAll(long parentMenuID, CommonEnum.MenuType menuType)
{
string sql = "SELECT * FROM UT_SYS_Menu WHERE IsDeleted = 0 AND IsDisplay = 1 AND Type= " + (int)menuType + " AND ParentMenuID = " + parentMenuID + " ORDER BY SortOrder";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 获得部门列表，只能邦定两级部门
/// </summary>
/// <returns></returns>
public DataTable GetOU()
{
string sql = "SELECT OUID,OUName FROM UT_SYS_OU WHERE IsDeleted = 0 ORDER BY SortIndex";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 根据部门ＩＤ获得部门人员
/// </summary>
/// <param name="OUID"></param>
/// <returns></returns>
public DataTable GetRightOUUser(long OUID)
{
string sql = "SELECT UserID, UserName FROM UT_SYS_User WHERE IsDeleted = 0 AND UserID <> 1 AND OUID = " + OUID + " ORDER BY SortIndex ";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 部门排序下移
/// </summary>
/// <param name="curOUID"></param>
/// <param name="perOUID"></param>
/// <returns></returns>
public static void OUSortMovedown(long curOUID, long NextOUID)
{
List <System.Data.SqlClient.SqlParameter> paramList = new List <System.Data.SqlClient.SqlParameter>();
paramList.Add(new System.Data.SqlClient.SqlParameter("@CurOUID", curOUID));
paramList.Add(new System.Data.SqlClient.SqlParameter("@NextOUID", NextOUID));
NDDBAccess.ExecuteNonQuery("UP_ORG_Oth_OUSortMovedown", paramList, CommandType.StoredProcedure);
}
/// <summary>
/// 获取调查问卷的数量
/// 作者：姚东
/// 时间：20100925
/// </summary>
/// <returns></returns>
public int GetSurveyAmount()
{
string sql = @"SELECT count(1) FROM UV_QS_SurveyTable WHERE State=1 and Active=1 and
datediff(day,getdate(),isnull(EndDate,'2199-12-31'))>=0
and (MaxAnswerAmount=0 or MaxAnswerAmount<>0 and AnswerAmount<MaxAnswerAmount) and ApprovalStaus=1";
return(NDConvert.ToInt32(NDDBAccess.Fill(sql).Rows[0][0]));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public DataTable GetOnLinePartner()
{
string sql = "SELECT ID, Name, URL,Status, sort, Image, " +
" CASE Status WHEN 1 THEN '上线' WHEN '0' THEN '下线' END AS StatusName " +
" FROM UT_QS_Partner where status=1 order by sort";
return(NDDBAccess.Fill(sql));
}
/// <summary>
/// 小调查数据源
/// 作者：姚东
/// 时间：20100925
/// </summary>
/// <returns></returns>
public DataTable GetDiaoChaInfo()
{
string sql = "select top 1 * from ut_diaocha where ishome=1 order by edittime asc";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 人员排序上移
/// </summary>
/// <param name="curUserID"></param>
/// <param name="perUserID"></param>
/// <returns></returns>
public static void UserSortMoveup(long curUserID, long perUserID)
{
List <System.Data.SqlClient.SqlParameter> paramList = new List <System.Data.SqlClient.SqlParameter>();
paramList.Add(new System.Data.SqlClient.SqlParameter("@CurUserID", curUserID));
paramList.Add(new System.Data.SqlClient.SqlParameter("@PerUserID", perUserID));
NDDBAccess.ExecuteNonQuery("UP_ORG_Oth_UserSortMoveup", paramList, CommandType.StoredProcedure);
}
/// <summary>
/// 获取所有上架的礼品信息
/// 作者：姚东
/// 时间：20101014
/// </summary>
/// <returns></returns>
public DataTable GetAllGifts()
{
string sql = @"SELECT ID,
GiftName ,
NeedPoint,RemainAmount, Description, Picture, Status, " +
" CreateTime,UpdateTime FROM UT_QS_Gifts where status=1";
return(NDDBAccess.Fill(sql));
}
/// <summary>
/// 根据帐号获得人员信息
/// </summary>
/// <param name="Account"></param>
/// <returns></returns>
public DataTable GetUserByAccount(string Account)
{
List <System.Data.SqlClient.SqlParameter> paramList = new List <System.Data.SqlClient.SqlParameter>();
paramList.Add(new System.Data.SqlClient.SqlParameter("@Account", Account));
string sql = "SELECT * FROM UT_SYS_User WHERE IsDeleted=0 AND Account = @Account ";
DataTable dt = NDDBAccess.Fill(sql, paramList);
return(dt);
}
/// <summary>
/// 获得村官用户信息
/// </summary>
/// <returns></returns>
public DataTable GetCGUser()
{
string sql = "SELECT UserID, UserName FROM UT_SYS_User " +
"WHERE IsDeleted = 0 " +
"AND OUID IN (SELECT OUID FROM UT_SYS_OU WHERE IsDeleted = 0 AND OUType = " + (int)CommonEnum.OUType.Outer + ") " +
"ORDER BY SortIndex ";
DataTable dt = NDDBAccess.Fill(sql);
return(dt);
}
/// <summary>
/// 根据键值获取配置参数值
/// </summary>
/// <param name="Key"></param>
/// <returns></returns>
public static string GetSystemParam(string Key)
{
string sql = "SELECT ParamValue FROM UT_SYS_SystemParam WHERE EnglishName = '" + Key + "'";
DataTable dt = NDDBAccess.Fill(sql);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0].ToString());
}
return("");
}
