/// <summary> /// 判断是否能删网站角色 /// </summary> /// <returns></returns> public static bool IsCanDelRole(string roleID) { DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); string sql = @"select count(*) from [user] where roleid=@roleID"; cmd.ClearParameters(); cmd.AddParameter("@roleID", roleID); cmd.setCommandText(sql); if (Convert.ToInt32(dm.ExecScalarCommand(cmd)) > 0) { return(false); } else { return(true); } }
/// <summary> /// 判断角色是否有菜单访问权限 /// </summary> /// <returns></returns> public static bool IsRoleAuthenticated(string roleId, string path) { DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); string sql = @"select count(*) from menu m, usermenurole umr where m.path=@path and umr.roleid=@roleId and m.menuid=umr.menuid"; cmd.ClearParameters(); cmd.setCommandText(sql); cmd.AddParameter("@path", path); cmd.AddParameter("@roleId", roleId); if (Convert.ToInt32(dm.ExecScalarCommand(cmd)) > 0) { return(true); } else { return(false); } }
/// <summary> /// 查询对象列表 /// </summary> /// <returns></returns> public static DataTable GetObjectList(string sqlCount, string sqlQuery, List <SqlParameter> listSqlPara, out int recordNum) { DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); if (listSqlPara != null && listSqlPara.Count > 0) { cmd.ClearParameters(); for (int i = 0; i < listSqlPara.Count; i++) { cmd.AddParameter(listSqlPara[i]); } } if (!string.IsNullOrEmpty(sqlCount)) { cmd.setCommandText(sqlCount); object o = dm.ExecScalarCommand(cmd); if (o == null) { recordNum = 0; } else { recordNum = int.Parse(o.ToString()); } } else { recordNum = 0; } if (!string.IsNullOrEmpty(sqlQuery)) { cmd.setCommandText(sqlQuery); return(dm.ExecDataSetCommand(cmd).Tables[0]); } else { return(null); } }
/// <summary> /// 判断某表中记录是否存在除了id对应的值 /// </summary> /// <returns></returns> public static bool IsExsit(string tableName, List <TablePrimary> paramList, int id) { DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); string sql = @"select count(*) from [" + tableName + "] where 1=1 and " + tableName + "id <>" + id; cmd.ClearParameters(); foreach (TablePrimary param in paramList) { sql += " and " + param.PrimaryName + "=@" + param.PrimaryName; cmd.AddParameter("@" + param.PrimaryName, param.PrimaryValue); } cmd.setCommandText(sql); if (Convert.ToInt32(dm.ExecScalarCommand(cmd)) > 0) { return(true); } else { return(false); } }
/// <summary> /// 查询仓位粮食情况 /// </summary> /// <param name="QueryCondition">查询条件</param> /// <param name="pageSize">网页列表一次最多显示多少行记录</param> /// <param name="currentPage">用户查询的第几页信息</param> /// <param name="recordNume">根据查询条件查询的总记录数</param> /// <returns></returns> public static DataTable QueryGrainListByStorage(Dictionary <string, string> QueryCondition, int pageSize, int currentPage, out int recordNum) { recordNum = 0; #region 处理查询语句和排序语句 string sqlSelect = @"select *,Row_Number() Over(Order By WPNumber) AS serialNum"; string sqlFrom = @"from View_WarePlaceGrain where 1=1"; #endregion #region 处理参数 List <System.Data.SqlClient.SqlParameter> listSqlPara = new List <System.Data.SqlClient.SqlParameter>(); if (QueryCondition != null) { System.Data.SqlClient.SqlParameter sqlPara = null; if (!string.IsNullOrEmpty(QueryCondition["WPNumber"])) { sqlFrom += " and WPNumber like '%'+@WPNumber+'%'"; sqlPara = new System.Data.SqlClient.SqlParameter(); sqlPara.SqlDbType = SqlDbType.NVarChar; sqlPara.ParameterName = "WPNumber"; sqlPara.Value = QueryCondition["WPNumber"]; listSqlPara.Add(sqlPara); } if (!string.IsNullOrEmpty(QueryCondition["MaterailNumber"])) { sqlFrom += " and MaterialNumber like '%'+@MaterailNumber+'%'"; sqlPara = new System.Data.SqlClient.SqlParameter(); sqlPara.SqlDbType = SqlDbType.NVarChar; sqlPara.ParameterName = "MaterailNumber"; sqlPara.Value = QueryCondition["MaterailNumber"]; listSqlPara.Add(sqlPara); } if (!string.IsNullOrEmpty(QueryCondition["WPGLot"])) { sqlFrom += " and WPGLot like '%'+@WPGLot+'%'"; sqlPara = new System.Data.SqlClient.SqlParameter(); sqlPara.SqlDbType = SqlDbType.NVarChar; sqlPara.ParameterName = "WPGLot"; sqlPara.Value = QueryCondition["WPGLot"]; listSqlPara.Add(sqlPara); } if (!string.IsNullOrEmpty(QueryCondition["WHID"])) { sqlFrom += " and WHID=@WHID"; sqlPara = new System.Data.SqlClient.SqlParameter(); sqlPara.SqlDbType = SqlDbType.NVarChar; sqlPara.ParameterName = "WHID"; sqlPara.Value = QueryCondition["WHID"]; listSqlPara.Add(sqlPara); } } #endregion #region 调用Ado.net查询数据库 DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); string sqlCount = @"select count(1) " + sqlFrom; cmd.setCommandText(sqlCount); if (listSqlPara != null && listSqlPara.Count > 0) { for (int i = 0; i < listSqlPara.Count; i++) { cmd.AddParameter(listSqlPara[i]); } } object o = dm.ExecScalarCommand(cmd); if (o == null) { recordNum = 0; } else { recordNum = int.Parse(o.ToString()); } string sqlQuery = @"select * from (" + sqlSelect + " " + sqlFrom + ") t " + @"where t.serialNum>@pageSize*(@pageCount-1) and t.serialNum<=@pageSize*@pageCount"; // cmd.ClearParameters(); cmd.setCommandText(sqlQuery); cmd.AddParameter("pageSize", pageSize); cmd.AddParameter("pageCount", currentPage); return(dm.ExecDataSetCommand(cmd).Tables[0]); #endregion }
///<summary> /// 网站查询出入库通知单列表 ///</summary> public static DataTable QueryBatchPlanList(Dictionary <string, string> queryCondition, int pageSize, int currentPage, out int recordNum) { recordNum = 0; #region 处理查询语句和排序语句 string sqlSelect = @"select pr.Id,pr.PRNumber,pr.PRType,pr.PRBizDate,pr.PRFinishDate,pr.PRConsignmentUnitName, pr.PRConsigneeUnitName,pr.PRBaseStatus,pr.PRLot,pr.PRCards,pr.PRSealNum,pr.PRBaseQty,m.MaterialNumber,m.MaterialName, pr.PRRelation,pr.PRID,pr.PRIsFinished,Row_Number() Over(Order By pr.Id desc) AS serialNum "; string sqlFrom = @"from PostRequisition pr,Material m where pr.MaterialId=m.MaterialId "; #endregion #region 处理参数 List <System.Data.SqlClient.SqlParameter> listSqlPara = new List <System.Data.SqlClient.SqlParameter>(); if (queryCondition != null) { System.Data.SqlClient.SqlParameter sqlPara = null; if (!string.IsNullOrEmpty(queryCondition["PRType"])) { sqlFrom += " and pr.PRType=@PRType"; sqlPara = new System.Data.SqlClient.SqlParameter(); sqlPara.SqlDbType = SqlDbType.Int; sqlPara.ParameterName = "PRType"; sqlPara.Value = Int32.Parse(queryCondition["PRType"]); listSqlPara.Add(sqlPara); } if (!string.IsNullOrEmpty(queryCondition["PRNumber"])) { sqlFrom += " and pr.PRNumber like '%'+@PRNumber+'%'"; sqlPara = new System.Data.SqlClient.SqlParameter(); sqlPara.SqlDbType = SqlDbType.NVarChar; sqlPara.ParameterName = "PRNumber"; sqlPara.Value = queryCondition["PRNumber"]; listSqlPara.Add(sqlPara); } if (!string.IsNullOrEmpty(queryCondition["PRLot"])) { sqlFrom += " and pr.PRLot like '%'+@PRLot+'%'"; sqlPara = new System.Data.SqlClient.SqlParameter(); sqlPara.SqlDbType = SqlDbType.NVarChar; sqlPara.ParameterName = "PRLot"; sqlPara.Value = queryCondition["PRLot"]; listSqlPara.Add(sqlPara); } if (!string.IsNullOrEmpty(queryCondition["StartTime"])) { sqlFrom += " and pr.PRBizDate >= @StartTime"; sqlPara = new System.Data.SqlClient.SqlParameter(); sqlPara.SqlDbType = SqlDbType.DateTime; sqlPara.ParameterName = "StartTime"; sqlPara.Value = Convert.ToDateTime(queryCondition["StartTime"]); listSqlPara.Add(sqlPara); } if (!string.IsNullOrEmpty(queryCondition["EndTime"])) { sqlFrom += " and pr.PRBizDate <= @EndTime"; sqlPara = new System.Data.SqlClient.SqlParameter(); sqlPara.SqlDbType = SqlDbType.DateTime; sqlPara.ParameterName = "EndTime"; sqlPara.Value = Convert.ToDateTime(queryCondition["EndTime"]); listSqlPara.Add(sqlPara); } if (!string.IsNullOrEmpty(queryCondition["PRID"])) { sqlFrom += " and pr.PRID = @PRID and pr.PRRelation=3"; sqlPara = new System.Data.SqlClient.SqlParameter(); sqlPara.SqlDbType = SqlDbType.NVarChar; sqlPara.ParameterName = "PRID"; sqlPara.Value = queryCondition["PRID"]; listSqlPara.Add(sqlPara); } else { sqlFrom += " and pr.PRRelation<>3"; } } #endregion #region 调用Ado.net查询数据库 DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); string sqlCount = @"select count(1) " + sqlFrom; cmd.setCommandText(sqlCount); if (listSqlPara != null && listSqlPara.Count > 0) { for (int i = 0; i < listSqlPara.Count; i++) { cmd.AddParameter(listSqlPara[i]); } } object o = dm.ExecScalarCommand(cmd); if (o == null) { recordNum = 0; } else { recordNum = int.Parse(o.ToString()); } string sqlQuery = @"select * from (" + sqlSelect + " " + sqlFrom + ") t " + @"where t.serialNum>@pageSize*(@pageCount-1) and t.serialNum<=@pageSize*@pageCount"; // cmd.ClearParameters(); cmd.setCommandText(sqlQuery); cmd.AddParameter("pageSize", pageSize); cmd.AddParameter("pageCount", currentPage); return(dm.ExecDataSetCommand(cmd).Tables[0]); #endregion }