/// <summary> /// 通过通知单ID查询移库通知单信息 /// </summary> /// <param name="id">移库通知单ID</param> public static DataTable QueryMovePlanById(int id) { DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); string sql = @"select mlb.*,m.MaterialNumber,m.MaterialName,m.MaterialModel, suOut.SUNumber as outSUNumber,suOut.SUName as outSUName, whOut.WHNumber as outWHNumber,whOut.WHName as outWHName, wpOut.WPNumber as outWPNumber,wpOut.WPName as outWPName, suIn.SUNumber as inSUNumber,suIn.SUName as inSUName, whIn.WHNumber as inWHNumber,whIn.WHName as inWHName, wpIn.WPNumber as inWPNumber,wpIn.WPName as inWPName from MoveLocationBill mlb left join Material m on mlb.MaterialId=m.MaterialId left join StorageUnit suOut on mlb.outSUID=suOut.SUID left join WareHouse whOut on mlb.OutWHID=whOut.WHID left join WarePlace wpOut on mlb.OutWPID=wpOut.WPID left join StorageUnit suIn on mlb.inSUID=suIn.SUID left join WareHouse whIn on mlb.InWHID=whIn.whId left join WarePlace wpIn on mlb.InWPID=wpIn.wpId where mlb.Id=@Id"; cmd.ClearParameters(); cmd.AddParameter("@Id", id); cmd.setCommandText(sql); return(dm.ExecDataSetCommand(cmd).Tables[0]); }
/// <summary> /// 查询对象列表 /// </summary> /// <returns></returns> public static DataTable GetObjectList(string tableName, int id) { DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); string sqlQuery = "select * from [" + tableName + "] where " + tableName + "Id=" + id; cmd.setCommandText(sqlQuery); return(dm.ExecDataSetCommand(cmd).Tables[0]); }
/// <summary> /// 查询对象列表 /// </summary> /// <returns></returns> public static DataTable GetObjectList(string sql, List <SqlParameter> listSqlPara) { DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); cmd.setCommandText(sql); if (listSqlPara != null && listSqlPara.Count > 0) { for (int i = 0; i < listSqlPara.Count; i++) { cmd.AddParameter(listSqlPara[i]); } } return(dm.ExecDataSetCommand(cmd).Tables[0]); }
/// <summary> /// 通过出入库通知单ID查询出入库通知单信息 /// </summary> /// <param name="id">出入库通知单ID</param> public static DataTable QueryBatchPlanById(int id) { DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); string sql = @"select pr.*,m.MaterialNumber,m.MaterialName,m.MaterialModel,wh.WHNumber,wh.WHName,wp.WPNumber,wp.WPName from PostRequisition pr left join Material m on pr.MaterialId=m.MaterialId left join WareHouse wh on pr.whId=wh.whId left join WarePlace wp on pr.wpId=wp.wpId where pr.Id=@Id"; cmd.ClearParameters(); cmd.AddParameter("@Id", id); cmd.setCommandText(sql); return(dm.ExecDataSetCommand(cmd).Tables[0]); }
/// <summary> /// 根据RoleID(网站角色)返回能返回的网站菜单 /// </summary> /// <param name="roleID"></param> /// <param name="retValue"></param> /// <returns></returns> public static DataTable QueryUserRoleMenu(int roleID) { DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager(); DBCommand cmd = dm.CreateDBCommand(CommandType.Text); string sql = null; sql = @"select M.* from [Role] AS R Inner Join UserMenuRole MR On MR.RoleID=R.RoleID Inner Join Menu M On M.MenuID=MR.MenuID Where R.RoleID=@RoleID"; cmd.ClearParameters(); cmd.setCommandText(sql); cmd.AddParameter("RoleID", roleID); DataSet ds = dm.ExecDataSetCommand(cmd); return(ds.Tables[0]); }
/// <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> /// 查询仓位粮食情况 /// </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 }