Пример #1
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]);
    }
Пример #2
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]);
    }
Пример #3
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]);
    }
Пример #4
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]);
    }
Пример #5
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));
    }
Пример #6
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]);
    }
Пример #7
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]);
    }
Пример #8
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]);
    }
Пример #9
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]);
    }
Пример #10
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]);
    }
Пример #11
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]);
    }
Пример #12
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]);
    }
Пример #13
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]);
    }
Пример #14
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]);
    }
Пример #15
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));
    }
Пример #16
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]);
    }
Пример #17
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]);
    }
Пример #18
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]);
    }
Пример #19
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]);
    }
Пример #20
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);
        }
    }
Пример #21
0
    /// <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);
        }
    }
Пример #22
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]);
    }
Пример #23
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);
        }
    }
Пример #24
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]);
    }
Пример #25
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);
        }
    }
Пример #26
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]);
    }
Пример #27
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
    }
Пример #28
0
    /// <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
    }