/// <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]); }