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