/// <summary>
/// 查询获取粮食存放在仓库的位置和重量
/// </summary>
/// <param name="TEId">出入厂单据ID</param>
/// <returns></returns>
public static DataTable GetGrainStorage(string TEId)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select te.*,t.PRMBNumber,m.MaterialNumber,m.MaterialName,wh.WHNumber,wh.WHName,wp.WPNumber,wp.WPName,
wpOut.WPNumber as OutWPNumber,wpOut.WPName as OutWPName
from TruckEir te
left join Material m on te.MaterialID=m.MaterialID
left join WareHouse wh on te.WHID=wh.WHID
left join WarePlace wp on te.WPID=wp.WPID
left join WarePlace wpOut on te.OutWPID=wpOut.WPID
left join (
select prid,prnumber as prmbnumber from postrequisition
union
select mbid as prid,mbnumber as prmbnumber from movelocationbill) t on te.prmbid=t.prid
where te.TEID=@TEId";
cmd.ClearParameters();
cmd.setCommandText(sql);
cmd.AddParameter("@TEId", TEId);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 根据仓库，楼层，仓位查询目前存粮情况
/// </summary>
/// <param name="whId">仓库ID</param>
/// <param name="floor">楼层数</param>
/// <param name="wpId">仓位ID</param>
/// <returns></returns>
public static DataTable GetStorageGrainByStorageId(string whId, string floor, string wpId)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select sum(WPGBaseQty) as weight
from View_WarePlaceGrain where WHID=@WHID";
cmd.ClearParameters();
if (!string.IsNullOrEmpty(floor))
{
sql += " and Floor=@Floor";
cmd.AddParameter("@Floor", floor);
}
if (!string.IsNullOrEmpty(wpId))
{
sql += " and WPID=@WPID";
cmd.AddParameter("@WPID", wpId);
}
cmd.setCommandText(sql);
cmd.AddParameter("@WHID", whId);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <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>
public static bool UpdateBatchPlanReturnState(string batchPlanId)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
cmd.ClearParameters();
cmd.setCommandText("update batchplan set upload=0 where batchplanid=@batchPlanId");
cmd.AddParameter("@batchPlanId", batchPlanId);
return(dm.ExecNonQueryCommand(cmd));
}
/// <summary>
/// 统计粮库目前的情况，包括托盘,装卸点，叉车情况
/// </summary>
/// <returns></returns>
public static DataTable GetGroupCurrentInfo()
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.StoredProcedure);
cmd.ClearParameters();
cmd.setCommandText("PRO_GROUPCURRENTINFO");
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 获取叉车司机用户
/// </summary>
/// <returns></returns>
public static DataTable GetWorkItemUser()
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
cmd.ClearParameters();
cmd.setCommandText("select * from [user] u,usergroup up where up.hasloadcarrole=1 and up.usergroupid=u.usergroupid");
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 查询获取已经存在粮库的物料名称
/// </summary>
/// <returns></returns>
public static DataTable GetMaterialInStorage()
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
cmd.ClearParameters();
cmd.setCommandText("select distinct MaterialName from View_WarePlaceGrainList");
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 查询获取粮食物料信息
/// </summary>
/// <returns></returns>
public static DataTable GetMaterial()
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
cmd.ClearParameters();
cmd.setCommandText("select * from Material");
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 根据仓位ID查询获取堆位信息
/// </summary>
/// <param name="wpId">库ID</param>
/// <returns></returns>
public static DataTable GetStorageDetailById(string wpId)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
cmd.ClearParameters();
cmd.setCommandText("select * from WarePlace where wpId=@wpId");
cmd.AddParameter("@wpId", wpId);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 按粮库统计粮库目前存量的情况
/// </summary>
public static DataTable GetGroupCurrentGrainByStorage()
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select WHName, sum(isnull(WPGBaseQty,0))/1000 as weight from View_WarePlaceGrain group by WHName";
cmd.setCommandText(sql);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 查询统计托盘使用情况
/// </summary>
/// <param name="beginTime">开始日期</param>
/// <param name="beginTime">结束日期</param>
/// <returns></returns>
public static DataTable GetGroupSalver(DateTime beginTime, DateTime endTime)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.StoredProcedure);
cmd.ClearParameters();
cmd.setCommandText("PRO_GROUPSALVER");
cmd.AddParameter("@beginDate", beginTime);
cmd.AddParameter("@endDate", endTime);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 根据仓库ID查询获取楼层信息
/// </summary>
/// <param name="storageId">仓库ID</param>
/// <returns></returns>
public static DataTable GetBuildingFloor(string whId)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = "select distinct floor as floor from WarePlace where WHID=@WHID";
cmd.ClearParameters();
cmd.setCommandText(sql);
cmd.AddParameter("@WHID", whId);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 根据仓位ID查询获取堆位目前存粮情况
/// </summary>
/// <param name="wpId">仓位ID</param>
/// <returns></returns>
public static DataTable GetStorageGrainByStorageDetailId(string wpId)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select MaterialNumber,MaterialName,sum(WPGBaseQty) as WPGBaseQty
from View_WarePlaceGrain where WPID=@wpId group by MaterialNumber,MaterialName ";
cmd.ClearParameters();
cmd.setCommandText(sql);
cmd.AddParameter("@wpId", wpId);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 执行某条语句
/// </summary>
/// <returns></returns>
public static bool ExecNonQuerySql(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.ExecNonQueryCommand(cmd));
}
/// <summary>
/// 统计员工工作量
/// </summary>
/// <param name="beginTime">开始日期</param>
/// <param name="beginTime">结束日期</param>
/// <returns></returns>
public static DataTable GetGroupWorkItem(DateTime beginTime, DateTime endTime)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select JobNo,RealName,sum(opernum)as OperNum
from view_workitemhis where optime>=@begintime and optime<=@endtime
group by JobNo,RealName";
cmd.ClearParameters();
cmd.setCommandText(sql);
cmd.AddParameter("@begintime", beginTime);
cmd.AddParameter("@endtime", endTime);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <summary>
/// 按月查询统计时间段内出入库粮食信息
/// </summary>
/// <param name="year">年份</param>
/// <param name="type">查询类型，0为全部，1为按品名，2为按仓库，3为楼层，4为按仓位，5为品名仓库，6为品名楼层，7为品名仓位</param>
/// <param name="materialName">物料名</param>
/// <param name="whId">仓库ID</param>
/// <param name="floorDigit">楼层数</param>
/// <param name="wpId">仓位ID</param>
/// <returns></returns>
public static DataTable GetGroupIOStorageTotalByMonth(int year, int type, string materialName, string whId, int floorDigit, string wpId)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.StoredProcedure);
cmd.ClearParameters();
cmd.setCommandText("Pro_GroupIOStorageTotalByMouth");
cmd.AddParameter("@year", year);
cmd.AddParameter("@type", type);
cmd.AddParameter("@materialName", materialName);
cmd.AddParameter("@whId", whId);
cmd.AddParameter("@floorDigit", floorDigit);
cmd.AddParameter("@wpId", wpId);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.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>
/// 通过计划ID查询计划详细信息
/// </summary>
/// <returns></returns>
public static DataTable GetBatchPlanById(string batchPlanId)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select b.*,s.storagecode,s.storagename,sd.storagedetailid,sd.storagecode as storagedetailcode,t.totalweight
from (select * from batchplan where batchplanid=@batchPlanId) b,storage s,batchplanstorage bs,storagedetail sd
,(select abs(sum(inweight)-sum(outweight)) as totalweight from truckeir where batchplanid=@batchPlanId) t
where b.storageid=s.storageid and b.batchplanid=bs.batchplanid
and bs.storagedetailid=sd.storagedetailid";
cmd.ClearParameters();
cmd.setCommandText(sql);
cmd.AddParameter("@batchPlanId", batchPlanId);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <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>
/// 根据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>
/// 根据楼层，仓库ID查询获取仓位信息
/// </summary>
/// <param name="storageId">仓库ID</param>
/// <param name="楼层">floor</param>
/// <param name="区域">area</param>
/// <returns></returns>
public static DataTable GetStorageDetail(string whId, string floor, string area)
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select * from WarePlace wp left join (select wpid,sum(wpgbaseqty)totalWeight
from wareplacegrain group by wpid) t on wp.wpid=t.wpid
where wp.WHID=@WHID and wp.[Floor]=@Floor";
if (!string.IsNullOrEmpty(area))
{
sql += " and wp.Area='" + area + "'";
}
sql += " order by wp.WPName";
cmd.ClearParameters();
cmd.setCommandText(sql);
cmd.AddParameter("@WHID", whId);
cmd.AddParameter("@Floor", floor);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
/// <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>
/// <returns></returns>
public static DataTable GetTask()
{
DataManager dm = new Invengo.RiceManangeServices.DBCommon.DataManager();
DBCommand cmd = dm.CreateDBCommand(CommandType.Text);
string sql = @"select r.PRMBNumber,t.PRMBLot,t.TETaskType,m.MaterialNumber,m.MaterialName,wh.WHName,wp.Floor,wp.WPName,
t.TESalverQty,t.TECurSalverQty
from truckeir t
left join Material m on t.MaterialId=m.MaterialId
left join WareHouse wh on t.WHID=wh.WHID
left join WarePlace wp on t.WPID=wp.WPID
left join (
select prid,prnumber as prmbnumber from postrequisition
union
select mbid as prid,mbnumber as prmbnumber from movelocationbill) r on t.prmbid=r.prid
where t.TEStatus='4'";
cmd.ClearParameters();
cmd.setCommandText(sql);
DataSet ds = dm.ExecProcCommand(cmd);
return(ds.Tables[0]);
}
///<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
}
/// <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
}
