/// <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
}
