/// <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>
/// 查询获取粮食存放在仓库的位置和重量
/// </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>
/// <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>
/// 统计粮库目前的情况，包括托盘,装卸点，叉车情况
/// </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>
/// 根据仓位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>
/// <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>
/// 统计员工工作量
/// </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>
/// 通过计划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>
/// 根据楼层，仓库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>
/// 查询获取正在处理的任务单
/// </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]);
}
