Пример #1
0
        //获取用户列表
        public DataTable GetPageList(ref JqGridParam jqgridparam)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"select UserId,Account,Mobile, Email,CreateTime,Enabled from BaseUser where IsDelete=0 ");
            return(Repository().FindTablePageBySql(strSql.ToString(), ref jqgridparam));
        }
Пример #2
0
        /// <summary>
        /// 加班申请列表
        /// </summary>
        public DataTable GetPageList(string keywords, ref JqGridParam jqgridparam)
        {
            StringBuilder      strSql    = new StringBuilder();
            List <DbParameter> parameter = new List <DbParameter>();

            strSql.Append(@"SELECT  *
                            FROM    ( SELECT a.OvertimeId,
                                         (select FullName from Base_DataDictionaryDetail where Code=a.Type and DataDictionaryId=
                                            (select DataDictionaryId from Base_DataDictionary where Code='Overtime')) AS Type,
                                         a.Date,a.StartTime,a.EndTime,
                                         (select FullName from Base_DataDictionaryDetail where Code=a.Time and DataDictionaryId=
                                            (select DataDictionaryId from Base_DataDictionary where Code='OvertimeLong')) AS Time,
                                         b.FullName as DepartmentName,c.RealName as UserName,
                                         d.FullName as CreatDepartmentName,e.RealName as CreatUserName,a.CreateTime
                                         from OvertimeRequest a 
                                         inner join Base_Department b on a.DepartmentId=b.DepartmentId
                                         inner join Base_User c on a.UserId=c.UserId
                                         left join Base_Department d on a.CreateDepartmentId=d.DepartmentId
                                         left join Base_User e on a.CreateUserId=e.UserId
                                    ) T WHERE 1=1");
            if (!string.IsNullOrEmpty(keywords))
            {
                strSql.Append(@" AND (UserName LIKE @keyword)");
                parameter.Add(DbFactory.CreateDbParameter("@keyword", '%' + keywords + '%'));
            }
            //if (!ManageProvider.Provider.Current().IsSystem)
            //{
            //    strSql.Append(" AND ( UserId IN ( SELECT ResourceId FROM Base_DataScopePermission WHERE");
            //    strSql.Append(" ObjectId IN ('" + ManageProvider.Provider.Current().ObjectId.Replace(",", "','") + "') ");
            //    strSql.Append(" ) )");
            //}
            return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam));
        }
Пример #3
0
        // <summary>
        /// 获取列表
        /// </summary>
        /// <param name="jqgridparam">分页条件</param>
        /// <returns></returns>
        public IList <Ho_SetSubscribe> GetPageList(ref JqGridParam jqgridparam, string KeyValue, string Keyword)
        {
            StringBuilder      strSql    = new StringBuilder();
            List <DbParameter> parameter = new List <DbParameter>();

            strSql.Append(@"SELECT  *
                            FROM  Ho_SetSubscribe where 1 = 1");
            //订单编号
            if (!string.IsNullOrEmpty(KeyValue))
            {
                strSql.Append(" AND MS_Number = @MS_Number");
                parameter.Add(DbFactory.CreateDbParameter("@MS_Number", KeyValue));
            }
            //关键字
            if (!string.IsNullOrEmpty(Keyword))
            {
                strSql.Append(@" AND (s_CarOrBus LIKE @keyword
                                    OR s_Address LIKE @keyword
                                    OR s_Reception LIKE @keyword
                                    OR s_CarType LIKE @keyword
                                    OR s_CarNumer LIKE @keyword
                                    OR s_CarColor LIKE @keyword)");
                parameter.Add(DbFactory.CreateDbParameter("@keyword", '%' + Keyword + '%'));
            }
            return(Repository().FindListPageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam));
        }
        public DataTable GetPageList(ref JqGridParam jqgridparam)
        {
            StringBuilder      strSql    = new StringBuilder();
            List <DbParameter> parameter = new List <DbParameter>();

            strSql.Append(@" select * from ( 
                                           select 
                                               m.meetingid as meetingid ,
                                                   m.Meeting_id as Meeting_id   ,     --主键                                                   
                                                    m.unit_id as unit_id,	          --组会单位ID
                                                    c.unit as unit_name,			  --组会单位名称
                                                    m.adduser_id as adduser_id,		  --组会人员ID
                                                    u.RealName as adduser_name,       --组会人员名称
                                                    m.adddate as adddate,             --组会日期
                                                    m.name as name ,                  --会议名称
                                                    m.type as type,                   --会议类型
                                                    m.Room_id as room_id  ,           --房间ID
                                                    m.startdate as startdate,         --开始时间
                                                    m.enddate as enddate,             --结束时间
                                                    m.userid as userid,               --会场参会人员的Id
                                                    m.username as username,           --会场参会人员
                                                    m.QJid as QJid,                   --会场全景ID
                                                    m.LDid as LDid,                   --领导ID
                                                    m.ZhCid as ZhCid,                 --主持人ID
                                                    m.screenmodel as screenmodel  ,   --窗口模式
                                                    m.state                           --状态
                                          FROM      Base_Meeting m
                                                    LEFT JOIN Base_Unit c ON c.Base_Unit_id = m.Unit_id
                                                    LEFT JOIN Base_User u ON u.UserId = m.adduser_id
                                        ) a  where 1=1 and state=0 and type=2    ");
            strSql.Append(" AND adduser_id = @user_id");
            parameter.Add(DbFactory.CreateDbParameter("@user_id", ManageProvider.Provider.Current().UserId));
            return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam));
        }
Пример #5
0
 /// <summary>
 /// 【系统日志】返回系统日志列表JSON
 /// </summary>
 /// <param name="ModuleId">模块ID</param>
 /// <param name="ParameterJson">搜索条件</param>
 /// <param name="jqgridparam">表格参数</param>
 /// <returns></returns>
 public ActionResult GridPageListJson(string ModuleId, string ParameterJson, JqGridParam jqgridparam)
 {
     try
     {
         Stopwatch watch = CommonHelper.TimerStart();
         //if (!string.IsNullOrEmpty(ParameterJson) && ParameterJson.Length > 2)
         //{
         //    List<DbParameter> parameter = new List<DbParameter>();
         //    IList conditions = ParameterJson.JonsToList<Condition>();
         //    string WhereSql = ConditionBuilder.GetWhereSql(conditions, out parameter);
         //}
         List <Base_SysLog> ListData = base_syslogbll.GetPageList(ModuleId, ParameterJson, ref jqgridparam);
         var JsonData = new
         {
             total    = jqgridparam.total,
             page     = jqgridparam.page,
             records  = jqgridparam.records,
             costtime = CommonHelper.TimerEnd(watch),
             rows     = ListData,
         };
         return(Content(JsonData.ToJson()));
     }
     catch (Exception ex)
     {
         Base_SysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message);
         return(null);
     }
 }
Пример #6
0
        public ActionResult GridPageListJson(string keywords, JqGridParam jqgridparam, string ParameterJson)
        {
            try
            {
                Stopwatch          watch     = CommonHelper.TimerStart();
                StringBuilder      strSql    = new StringBuilder();
                List <DbParameter> parameter = new List <DbParameter>();
                strSql.Append(@" select * from product where 1=1   ");
                if (!CommonHelper.IsEmpty(keywords))
                {
                    strSql.AppendFormat(@" and (partname like '%{0}%' or productno like '%{0}%') ", keywords);
                }
                if (ManageProvider.Provider.Current().IsSystem == false)
                {
                    strSql.AppendFormat(@" and CompanyID='{0}' ", ManageProvider.Provider.Current().CompanyId);
                }

                DataTable ListData = productre.FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam);

                var JsonData = new
                {
                    total    = jqgridparam.total,
                    page     = jqgridparam.page,
                    records  = jqgridparam.records,
                    costtime = CommonHelper.TimerEnd(watch),
                    rows     = ListData,
                };
                return(Content(JsonData.ToJson()));
            }
            catch (Exception ex)
            {
                //Base_SysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message);
                return(null);
            }
        }
Пример #7
0
 public virtual JsonResult GridPageJson(string parameterJson, JqGridParam jqgridparam)
 {
     try
     {
         Stopwatch      watch    = CommonHelper.TimerStart();
         List <TEntity> ListData = new List <TEntity>();
         if (!string.IsNullOrEmpty(parameterJson))
         {
             List <DbParameter> parameter = new List <DbParameter>();
             IList  conditions            = parameterJson.JonsToList <Condition>();
             string WhereSql = ConditionBuilder.GetWhereSql(conditions, out parameter);
             ListData = Repositoryfactory.Repository().FindListPage(WhereSql, parameter.ToArray(), ref jqgridparam);
         }
         else
         {
             ListData = Repositoryfactory.Repository().FindListPage(ref jqgridparam);
         }
         var JsonData = new
         {
             total    = jqgridparam.total,
             page     = jqgridparam.page,
             records  = jqgridparam.records,
             costtime = CommonHelper.TimerEnd(watch),
             rows     = ListData,
         };
         return(Json(JsonData, JsonRequestBehavior.AllowGet));
     }
     catch (Exception ex)
     {
         BaseSysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message + "\r\n条件:" + parameterJson);
         return(null);
     }
 }
Пример #8
0
 public ActionResult GridPageListJson(string keywords, string CompanyId, string DepartmentId,
                                      JqGridParam jqgridparam, string ParameterJson)
 {
     try
     {
         Stopwatch          watch     = CommonHelper.TimerStart();
         StringBuilder      strSql    = new StringBuilder();
         List <DbParameter> parameter = new List <DbParameter>();
         strSql.Append(@" select * from WanWuYunDevice where 1=1  ");
         DataTable ListData = re.FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam);
         var       JsonData = new
         {
             total    = jqgridparam.total,
             page     = jqgridparam.page,
             records  = jqgridparam.records,
             costtime = CommonHelper.TimerEnd(watch),
             rows     = ListData,
         };
         return(Content(JsonConvert.SerializeObject(JsonData)));
     }
     catch (Exception ex)
     {
         return(null);
     }
 }
Пример #9
0
        /// <summary>
        /// 获取系统日志列表
        /// </summary>
        /// <param name="ModuleId">模块ID</param>
        /// <param name="ParameterJson">搜索条件</param>
        /// <param name="jqgridparam">分页条件</param>
        /// <returns></returns>
        public List <Base_SysLog> GetPageList(string ModuleId, string ParameterJson, ref JqGridParam jqgridparam)
        {
            StringBuilder      strSql    = new StringBuilder();
            List <DbParameter> parameter = new List <DbParameter>();

            strSql.Append(@"SELECT  *
                            FROM    ( SELECT    l.SysLogId ,
                                                l.ObjectId ,
                                                l.LogType ,
                                                l.IPAddress ,
                                                l.IPAddressName ,
                                                c.FullName AS CompanyId ,
                                                D.FullName AS DepartmentId ,
                                                l.CreateDate ,
                                                l.CreateUserId ,
                                                l.CreateUserName ,
                                                m.FullName AS ModuleId ,
                                                l.Remark ,
                                                l.Status
                                      FROM      Base_SysLog l
                                                LEFT JOIN Base_Department d ON d.DepartmentId = l.DepartmentId
                                                LEFT JOIN Base_Company c ON c.CompanyId = l.CompanyId
                                                LEFT JOIN Base_Module m ON m.ModuleId = l.ModuleId
                                    ) A WHERE 1 = 1");
            //strSql.Append(WhereSql);
            if (!string.IsNullOrEmpty(ModuleId))
            {
                strSql.Append(" AND ModuleId = @ModuleId");
                parameter.Add(DbFactory.CreateDbParameter("@ModuleId", ModuleId));
            }
            return(Repository().FindListPageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam));
        }
Пример #10
0
        /// <summary>
        /// 列表加载
        /// </summary>
        /// <param name="ParameterJson"></param>
        /// <param name="unit_id"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public string GetQuery(string ParameterJson, JqGridParam jqgridparam)
        {
            string user_id = ManageProvider.Provider.Current().UserId;

            try
            {
                int       pageIndex = jqgridparam.page;
                int       pageSize  = jqgridparam.rows;
                Stopwatch watch     = CommonHelper.TimerStart();
                string    sql       = "select orm.OperationRoomMain_id,orm.room_id,orm.adduser,CONVERT(varchar(20), orm.adddate, 120)  adddate,";
                sql = sql + " case orm.state when 0 then '待销售' when 1 then '待发放' when 2 then '已发放' end state,r.Name as roomName,u.RealName from Operation_RoomMain orm";
                sql = sql + " left join base_room r on orm.room_id=r.room_id";
                sql = sql + " left join Base_User u on  u.UserId=orm.adduser";
                sql = sql + " and orm.room_id in (select room_id from base_room where user_id='" + user_id + "')";
                sql = sql + " order by adddate";


                DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql);

                var JsonData = new
                {
                    total    = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数
                    page     = jqgridparam.page,                                                      //当前页码
                    records  = dt.Rows.Count,                                                         //总记录数
                    costtime = CommonHelper.TimerEnd(watch),                                          //查询消耗的毫秒数
                    rows     = dt
                };
                return(JsonData.ToJson());
            }
            catch (Exception)
            {
                return(null);
            }
        }
        public Dictionary <string, object> CauseList(JqGridParam jqgridparam)
        {
            var                  type     = HttpContext.Current.Request.Form["type"].ToString();
            var                  key      = HttpContext.Current.Request.Form["key"].ToString();
            IDatabase            database = DataFactory.Database();
            IRepository <Module> re       = new Repository <Module>();
            DataTable            data     = new DataTable();
            string               strwhere = " 1=1 ";

            if (!string.IsNullOrEmpty(type))
            {
                strwhere += " and ParentId=" + type;
            }
            if (!string.IsNullOrEmpty(key))
            {
                strwhere += " and (CauseContent like '%" + key + "%' or SuggestionContent like '%" + key + "%')";
            }
            data = re.FindTablePageBySql("select CauseId,ParentId,Code,[CauseContent],SuggestionContent,CreateTime,[CreateUserId],CreateUserName ,Sort,RelatedContent from Sys_CauseSuggestion ", ref jqgridparam);
            return(new Dictionary <string, object>
            {
                { "code", 1 },
                { "total", jqgridparam.total },
                { "page", jqgridparam.page },
                { "records", jqgridparam.records },
                { "rows", data },
            });
        }
        public Dictionary <string, object> YearList(JqGridParam jqgridparam)
        {
            var                  datetime = HttpContext.Current.Request.Form["datetime"].ToString();
            var                  factory  = HttpContext.Current.Request.Form["factory"].ToString();
            var                  key      = HttpContext.Current.Request.Form["key"].ToString();
            IDatabase            database = DataFactory.Database();
            IRepository <Module> re       = new Repository <Module>();
            DataTable            data     = new DataTable();
            string               strwhere = " 1=1 ";

            if (!string.IsNullOrEmpty(factory))
            {
                strwhere += " and FactorySation='" + factory + "'";
            }
            if (!string.IsNullOrEmpty(datetime))
            {
                strwhere += " and datediff(Year, CreateTime,'" + datetime + "-01-01" + "') = 0";
            }
            if (!string.IsNullOrEmpty(key))
            {
                strwhere += " and Signal like'%" + key + "%'";
            }
            data = re.FindTablePageBySql("SELECT sum(happentimes) as total,[FactorySation],year(CreateTime) as CreateTime,Signal FROM[ElectricPower].[dbo].[M_HitchInfo] where " + strwhere + "   group by  year(CreateTime),[FactorySation], Signal", ref jqgridparam);

            return(new Dictionary <string, object>
            {
                { "code", 1 },
                { "total", jqgridparam.total },
                { "page", jqgridparam.page },
                { "records", jqgridparam.records },
                { "rows", data },
            });
        }
        /// <summary>
        /// 列表加载
        /// </summary>
        /// <param name="ParameterJson"></param>
        /// <param name="unit_id"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public string GetSale(string ParameterJson, JqGridParam jqgridparam)
        {
            string unit_id = ManageProvider.Provider.Current().CompanyId;
            string user_id = ManageProvider.Provider.Current().UserId;

            try
            {
                int       pageIndex = jqgridparam.page;
                int       pageSize  = jqgridparam.rows;
                Stopwatch watch     = CommonHelper.TimerStart();
                string    sql       = "select om.operationmain_id,r.name as roomName,p.designation,p.name as peopleName,CONVERT(varchar(20), om.adddate, 120)  adddate,om.saletype,om.cash,u.RealName,u.RealName  from Operation_Main om";
                sql = sql + " left join base_room r on om.room_id=r.room_id";
                sql = sql + " left join people p on om.People_id=p.People_id";
                sql = sql + " left join Base_User u on  u.UserId=om.actuser";
                sql = sql + " where om.room_id in (select room_id from base_room where user_id='" + user_id + "')";
                sql = sql + " and om.room_id <> p.room_id and  om.state=0 and p.state = 1 ";//换监室 and 商品未发放 and 在押
                sql = sql + " order by operationmain_id";

                DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql);

                var JsonData = new
                {
                    total    = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数
                    page     = jqgridparam.page,                                                      //当前页码
                    records  = dt.Rows.Count,                                                         //总记录数
                    costtime = CommonHelper.TimerEnd(watch),                                          //查询消耗的毫秒数
                    rows     = dt
                };
                return(JsonData.ToJson());
            }
            catch (Exception)
            {
                return(null);
            }
        }
        /// <summary>
        /// 列表加载明细
        /// </summary>
        /// <param name="ParameterJson"></param>
        /// <param name="unit_id"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public string GetSaleDetail(string ParameterJson, JqGridParam jqgridparam, string KeyValue)
        {
            string unit_id = ManageProvider.Provider.Current().CompanyId;
            string user_id = ManageProvider.Provider.Current().UserId;

            try
            {
                int       pageIndex = jqgridparam.page;
                int       pageSize  = jqgridparam.rows;
                Stopwatch watch     = CommonHelper.TimerStart();
                string    sql       = "select g.shortcode ,g.name,g.name ,g.price,case ISNULL(od.goodsnum,'') when '' then od.goodschoosenum else od.goodsnum end as goodsnum from Operation_Detail od";
                sql = sql + " left join base_goods g on od.goods_id=g.goods_id";
                sql = sql + " where od.operationmain_id='" + KeyValue + "'";


                DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql);

                var JsonData = new
                {
                    total    = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数
                    page     = jqgridparam.page,                                                      //当前页码
                    records  = dt.Rows.Count,                                                         //总记录数
                    costtime = CommonHelper.TimerEnd(watch),                                          //查询消耗的毫秒数
                    rows     = dt
                };
                return(JsonData.ToJson());
            }
            catch (Exception)
            {
                return(null);
            }
        }
Пример #15
0
        /// <summary>
        /// 获取用户信息列表
        /// </summary>
        /// <param name="parameterJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public JsonResult GetUserListByQuery(string parameterJson, JqGridParam jqgridparam)
        {
            var model = new QueryUserModel();

            if (!string.IsNullOrWhiteSpace(parameterJson))
            {
                model = JsonConvert.DeserializeObject <QueryUserModel>(parameterJson);
            }
            model.PageIndex = jqgridparam.page;
            model.PageSize  = jqgridparam.rows;
            model.UserType  = (int)EnumUserType.User;
            var send   = JsonConvert.SerializeObject(model);
            var result = ServiceHelper.CallService <PagedListData <List <SysUser> > >(
                ServiceConst.SysUserApi.GetUserListByQuery,
                send, this.CurrentUser.Token);
            var json = new
            {
                page    = result.Data.PagingInfo.PageIndex,
                total   = result.Data.PagingInfo.PageCount,
                records = result.Data.PagingInfo.TotalCount,
                rows    = result.Data.PagingData.ToArray()
            };

            return(Json(json, JsonRequestBehavior.AllowGet));
        }
Пример #16
0
        /// <summary>
        /// 获取公告内容
        /// </summary>
        /// <param name="keyvalue">关键字条件</param>
        /// <param name="jqgridparam">JqGrid参数</param>
        /// <returns>数据集</returns>
        public DataTable GetAnnouncementPageList(string keyvalue, ref JqGridParam jqgridparam)
        {
            StringBuilder      strSql    = new StringBuilder();
            List <DbParameter> parameter = new List <DbParameter>();

            strSql.Append(@"SELECT 
                                d.ID,                        --Id,主键
                                d.Title,                     --标题
                                d.Content,                   --内容
                                d.ContentState,              --状态
                                d.CreateTime,                --创建时间
                                d.UpdateTime,                --更新时间
                                d.Remark
                                FROM ");
            string sqlTableName = "DStudent_Announcement  d";
            string sqlConditon  = " WHERE 1=1";

            strSql.Append(sqlTableName);
            strSql.Append(sqlConditon);
            if (!string.IsNullOrEmpty(keyvalue))
            {
                strSql.Append(@" AND (d.Title LIKE @keyvalue or d.Content LIKE @keyvalue)");
                parameter.Add(DbFactory.CreateDbParameter("@keyvalue", '%' + keyvalue + '%'));
            }
            return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam));
        }
Пример #17
0
        /// <summary>
        /// 根据公司id获取角色 列表
        /// </summary>
        /// <param name="companyId">公司ID</param>
        /// <param name="jqgridparam">分页条件</param>
        /// <returns></returns>
        public DataTable GetPageList(string companyId, ref JqGridParam jqgridparam)
        {
            StringBuilder      strSql    = new StringBuilder();
            List <DbParameter> parameter = new List <DbParameter>();

            strSql.Append(@"SELECT  *
                            FROM    ( SELECT    r.RoleId, r.CompanyId, c.FullName AS CompanyName, r.Code,
                                                r.FullName, ISNULL(U.Qty, 0) AS MemberCount, r.Category,
                                                r.Enabled, r.SortCode, r.Remark
                                      FROM      Base_Roles r
                                                LEFT JOIN Base_Company c ON c.CompanyId = r.CompanyId
                                                LEFT JOIN ( SELECT  COUNT(1) AS Qty, ObjectId
                                                            FROM    Base_ObjectUserRelation
                                                            WHERE   Category = '2'
                                                            GROUP BY ObjectId
                                                          ) U ON U.ObjectId = r.RoleId
                                    ) T
                            WHERE   1 = 1 ");
            if (!string.IsNullOrEmpty(companyId))
            {
                strSql.Append(" AND CompanyId = @CompanyId");
                parameter.Add(DbFactory.CreateDbParameter("@CompanyId", companyId));
            }
            if (!ManageProvider.Provider.Current().IsSystem)
            {
                strSql.Append(" AND ( RoleId IN ( SELECT ResourceId FROM Base_DataScopePermission WHERE");
                strSql.Append(" ObjectId IN ('" + ManageProvider.Provider.Current().ObjectId.Replace(",", "','") + "') ");
                strSql.Append(" ) )");
            }
            return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam));
        }
Пример #18
0
        /// <summary>
        /// 列表加载
        /// </summary>
        /// <param name="ParameterJson"></param>
        /// <param name="unit_id"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public string GetPeople(string ParameterJson, JqGridParam jqgridparam)
        {
            string unit_id = ManageProvider.Provider.Current().CompanyId;
            string user_id = ManageProvider.Provider.Current().UserId;

            try
            {
                int       pageIndex = jqgridparam.page;
                int       pageSize  = jqgridparam.rows;
                Stopwatch watch     = CommonHelper.TimerStart();
                string    sql       = "select p.People_id,p.designation,r.name as room_id,p.name,p.sex,p.selftype,p.selfmoney,";
                sql = sql + " CONVERT(varchar(20), p.startdate, 120) startdate,CONVERT(varchar(20), p.enddate, 120) enddate,p.limit,p.punish,p.note,p.adduser,p.adddate  from people p";
                sql = sql + " left join base_room r on r.room_id=p.room_id";
                sql = sql + " where p.room_id in (select room_id from base_room where user_id='" + user_id + "')";
                sql = sql + " order by p.People_id";

                DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql);

                var JsonData = new
                {
                    total    = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数
                    page     = jqgridparam.page,                                                      //当前页码
                    records  = dt.Rows.Count,                                                         //总记录数
                    costtime = CommonHelper.TimerEnd(watch),                                          //查询消耗的毫秒数
                    rows     = dt
                };
                return(JsonData.ToJson());
            }
            catch (Exception)
            {
                return(null);
            }
        }
Пример #19
0
        /// <summary>
        /// 返回产品栏目列表JSON
        /// </summary>
        /// <param name="jqgridparam">表格参数</param>
        /// <returns></returns>
        public ActionResult GridPageListJson(JqGridParam jqgridparam, string StartTime, string EndTime,
                                             string Keyword, string Stuts, [DefaultValue(0)] int IsAll)
        {
            try
            {
                Stopwatch watch = CommonHelper.TimerStart();
                //DataTable ListData = bll.GetPageList(ref jqgridparam);
                var ListData = bll.GetPageList1(ref jqgridparam, StartTime, EndTime, Keyword, Stuts, IsAll);
                var newlist  = new List <Fx_Orders>();
                foreach (var item in ListData)
                {
                    item.Arddress = item.Province + item.City + item.County + item.Arddress;

                    newlist.Add(item);
                }
                var JsonData = new
                {
                    total    = jqgridparam.total,
                    page     = jqgridparam.page,
                    records  = jqgridparam.records,
                    costtime = CommonHelper.TimerEnd(watch),
                    rows     = newlist
                };
                return(Content(JsonData.ToJson()));
            }
            catch (Exception ex)
            {
                Base_SysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message);
                return(null);
            }
        }
        public Dictionary <string, object> YujingList(JqGridParam jqgridparam)
        {
            var                  timetype = HttpContext.Current.Request.Form["timetype"].ToString();
            var                  datetime = HttpContext.Current.Request.Form["datetime"].ToString();
            var                  times    = HttpContext.Current.Request.Form["times"].ToString();
            var                  factory  = HttpContext.Current.Request.Form["factory"].ToString();
            IDatabase            database = DataFactory.Database();
            IRepository <Module> re       = new Repository <Module>();
            DataTable            data     = new DataTable();
            string               strwhere = "";

            if (!string.IsNullOrEmpty(factory))
            {
                strwhere = " and FactorySation='" + factory + "'";
            }
            //按照月份删选

            if (timetype == "0")
            {
                data = re.FindTablePageBySql("select [FactorySation],[Signal],sum([HappenTimes]) as total from (SELECT [FactorySation],[Signal] ,[HappenTimes] ,[SignalType], [CreateTime]   FROM[ElectricPower].[dbo].[M_HitchInfo] where datediff(month, CreateTime, '" + datetime + "') = 0 " + strwhere + ") a group by[FactorySation],[Signal] having sum([HappenTimes])>" + times + "", ref jqgridparam);
            }
            else
            {
                data = re.FindTablePageBySql("select [FactorySation],[Signal],sum([HappenTimes]) as total from (SELECT [FactorySation],[Signal] ,[HappenTimes] ,[SignalType], [CreateTime]   FROM[ElectricPower].[dbo].[M_HitchInfo] where datediff(day, CreateTime, '" + datetime + "') = 0 " + strwhere + ") a group by[FactorySation],[Signal] having sum([HappenTimes])>" + times + "", ref jqgridparam);
            }
            return(new Dictionary <string, object>
            {
                { "code", 1 },
                { "total", jqgridparam.total },
                { "page", jqgridparam.page },
                { "records", jqgridparam.records },
                { "rows", data },
            });
        }
Пример #21
0
 /// <summary>
 /// 返回产品栏目列表JSON
 /// </summary>
 /// <param name="jqgridparam">表格参数</param>
 /// <returns></returns>
 public ActionResult GridPageListJson(JqGridParam jqgridparam, string Keyword)
 {
     try
     {
         Stopwatch watch = CommonHelper.TimerStart();
         //DataTable ListData = bll.GetPageList(ref jqgridparam);
         var ListData = bll.GetPageList1(ref jqgridparam, Keyword);
         var newlist  = new List <Fx_ProductClass>();
         foreach (var item in ListData)
         {
             if (item.IsShow == 1 && ManageProvider.Provider.Current().InnerUser > 2)
             {
                 continue; //非管理员看不到此项,2为管理员
             }
             item.ClassUrl = "http://" + item.ClassPath + "/" + item.ClassUrl + "?p=" + item.Number + "&u=" + ManageProvider.Provider.Current().UserId;
             newlist.Add(item);
         }
         var JsonData = new
         {
             total    = jqgridparam.total,
             page     = jqgridparam.page,
             records  = jqgridparam.records,
             costtime = CommonHelper.TimerEnd(watch),
             rows     = newlist
         };
         return(Content(JsonData.ToJson()));
     }
     catch (Exception ex)
     {
         Base_SysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message);
         return(null);
     }
 }
Пример #22
0
        /// <summary>
        /// 获取列表-导出
        /// </summary>
        /// <param name="jqgridparam">分页条件</param>
        /// <returns></returns>
        public IList <Am_MoneyDetail> GetPageList(ref JqGridParam jqgridparam, string keywords,
                                                  string BeginTime, string EndTime)
        {
            StringBuilder      strSql    = new StringBuilder();
            List <DbParameter> parameter = new List <DbParameter>();

            strSql.Append(@"SELECT  *
                            FROM  Am_MoneyDetail where 1=1 ");
            //关键字
            if (!string.IsNullOrEmpty(keywords))
            {
                strSql.Append(@" AND (UserName LIKE @keyword 
                                    OR U_Name LIKE @keyword 
                                    OR OperateTypeStr LIKE @keyword 
                                    OR CreateUserName LIKE @keyword)");
                parameter.Add(DbFactory.CreateDbParameter("@keyword", '%' + keywords + '%'));
            }
            //开始时间
            if (!string.IsNullOrEmpty(BeginTime))
            {
                strSql.Append(" AND CreateTime > @StartTime");
                parameter.Add(DbFactory.CreateDbParameter("@StartTime", Convert.ToDateTime(BeginTime).ToString("yyyy-MM-dd") + " 00:00:00"));
            }
            //结束时间
            if (!string.IsNullOrEmpty(EndTime))
            {
                strSql.Append(" AND CreateTime < @EndTime");
                parameter.Add(DbFactory.CreateDbParameter("@EndTime", Convert.ToDateTime(EndTime).AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00"));
            }
            return(Repository().FindListPageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam));
        }
Пример #23
0
 public ActionResult GridProductCalListJson(string keywords, JqGridParam jqgridparam, string ParameterJson)
 {
     try
     {
         Stopwatch          watch     = CommonHelper.TimerStart();
         StringBuilder      strSql    = new StringBuilder();
         List <DbParameter> parameter = new List <DbParameter>();
         strSql.AppendFormat(@" select a.PartName,a.ProductNO,a.Feature,a.Customer,a.PictureType,b.* from product a left join CalMethod b on a.ProductID=b.ProductID where 1=1    ");
         if (!CommonHelper.IsEmpty(keywords))
         {
             strSql.AppendFormat(@" and (partname like '%{0}%' or productno like '%{0}%') ", keywords);
         }
         strSql.AppendFormat(@" and a.CompanyID='{0}' ", ManageProvider.Provider.Current().CompanyId);
         DataTable ListData = Productre.FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam);
         var       JsonData = new
         {
             total    = jqgridparam.total,
             page     = jqgridparam.page,
             records  = jqgridparam.records,
             costtime = CommonHelper.TimerEnd(watch),
             rows     = ListData,
         };
         return(Content(JsonData.ToJson()));
     }
     catch (Exception ex)
     {
         return(null);
     }
 }
Пример #24
0
        /// <summary>
        /// 获取商品列表列表
        /// </summary>
        /// <param name="keyword">查询关键词</param>
        /// <param name="categoryId">商品分类ID</param>
        /// <param name="jqgridparam">分页条件</param>
        /// <returns></returns>
        public DataTable GetPageList(string keyword, string categoryId, ref JqGridParam jqgridparam)
        {
            StringBuilder      strSql    = new StringBuilder();
            List <DbParameter> parameter = new List <DbParameter>();

            strSql.Append(@"SELECT p.ProductId, p.CategoryId, p.Code, p.ProductName, p.BriefName,
                                    p.Weight, p.Volume, p.Specification, p.Brand, p.BarCode, p.Price,
                                    p.BaseUnit, p.IsLotControl, p.IsForcedScan, p.Remark, p.Enabled,
                                    p.SortCode, p.CreateDate, p.ModifyDate, c.CategoryName
                             FROM   Product p
                                    LEFT JOIN dbo.Product_Category c ON c.CategoryId = p.CategoryId
                             WHERE  1 = 1");
            if (!string.IsNullOrEmpty(keyword))
            {
                strSql.AppendFormat(@" AND (p.Code LIKE '{0}'
                                    OR p.ProductName LIKE '{0}'
                                    OR p.BriefName LIKE '{0}'
                                    OR p.BarCode LIKE '{0}')", '%' + keyword + '%');
            }
            if (!string.IsNullOrEmpty(categoryId) && categoryId != "0")
            {
                strSql.Append(" AND p.CategoryId = @CategoryId");
                parameter.Add(DbFactory.CreateDbParameter("@CategoryId", categoryId));
            }
            //if (!ManageProvider.Provider.Current().IsSystem)
            //{
            //    strSql.Append(" AND ( MerchantId IN ( SELECT ResourceId FROM Base_DataScopePermission WHERE");
            //    strSql.Append(" ObjectId IN ('" + ManageProvider.Provider.Current().ObjectId.Replace(",", "','") + "') ");
            //    strSql.Append(" ) )");
            //}
            return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam));
        }
Пример #25
0
        /// <summary>
        /// 新增监室销售初始(jqgrid方式)
        /// </summary>
        /// <param name="ParameterJson"></param>
        /// <param name="unit_id"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public string AddRoomSale(string ParameterJson, JqGridParam jqgridparam)
        {
            string user_id = ManageProvider.Provider.Current().UserId;

            try
            {
                int       pageIndex = jqgridparam.page;
                int       pageSize  = jqgridparam.rows;
                Stopwatch watch     = CommonHelper.TimerStart();
                string    sql       = "select p.People_id,p.code,p.name,p.room_id,r.Name as roomName,account,";
                sql = sql + " p.limit-(select isnull(sum(cash),0) from Operation_Main where People_id=p.People_id and CONVERT(varchar(7), adddate, 120)=CONVERT(varchar(7), getdate(), 120) group by People_id  ) ";//,p.People_id,p.code,p.room_id,
                sql = sql + " as canuse from People p";
                sql = sql + " left join base_room r on p.room_id=r.room_id";
                sql = sql + " and p.room_id in (select room_id from base_room where user_id='" + user_id + "')";
                sql = sql + " where 1=2 ";
                sql = sql + " order by People_id";

                DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql);

                var JsonData = new
                {
                    total    = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数
                    page     = jqgridparam.page,                                                      //当前页码
                    records  = dt.Rows.Count,                                                         //总记录数
                    costtime = CommonHelper.TimerEnd(watch),                                          //查询消耗的毫秒数
                    rows     = dt
                };
                return(JsonData.ToJson());
            }
            catch (Exception)
            {
                return(null);
            }
        }
Пример #26
0
        public Dictionary <string, object> PredicSettingList(JqGridParam jqgridparam)
        {
            var                  factory  = HttpContext.Current.Request.Form["factory"].ToString();
            var                  key      = HttpContext.Current.Request.Form["key"].ToString();
            IDatabase            database = DataFactory.Database();
            IRepository <Module> re       = new Repository <Module>();
            DataTable            data     = new DataTable();
            string               strwhere = " 1=1 ";

            if (!string.IsNullOrEmpty(factory))
            {
                strwhere += " and FactorySation='" + factory + "'";
            }
            if (!string.IsNullOrEmpty(key))
            {
                strwhere += " and Signal like'%" + key + "%'";
            }
            data = re.FindTablePageBySql("select * from PredicSetting where " + strwhere, ref jqgridparam);
            return(new Dictionary <string, object>
            {
                { "code", 1 },
                { "total", jqgridparam.total },
                { "page", jqgridparam.page },
                { "records", jqgridparam.records },
                { "rows", data },
            });
        }
Пример #27
0
 /// <summary>
 /// 绑定表格
 /// </summary>
 /// <param name="ParameterJson">查询条件</param>
 /// <param name="Gridpage">分页条件</param>
 /// <returns></returns>
 public virtual JsonResult GridPageJson(string ParameterJson, JqGridParam jqgridparam)
 {
     try
     {
         Stopwatch      watch    = CommonHelper.TimerStart();
         List <TEntity> ListData = new List <TEntity>();
         if (!string.IsNullOrEmpty(ParameterJson))
         {
             List <DbParameter> parameter = new List <DbParameter>();
             IList  conditions            = ParameterJson.JonsToList <Condition>();
             string WhereSql = ConditionBuilder.GetWhereSql(conditions, out parameter);
             ListData = repositoryfactory.Repository().FindListPage(WhereSql, parameter.ToArray(), ref jqgridparam);
         }
         else
         {
             ListData = repositoryfactory.Repository().FindListPage(ref jqgridparam);
         }
         var JsonData = new
         {
             total    = jqgridparam.total,
             page     = jqgridparam.page,
             records  = jqgridparam.records,
             costtime = CommonHelper.TimerEnd(watch),
             rows     = ListData,
         };
         return(Json(JsonData, JsonRequestBehavior.AllowGet));
     }
     catch (Exception ex)
     {
         return(null);
     }
 }
Пример #28
0
        /// <summary>
        /// 列表加载
        /// </summary>
        /// <param name="ParameterJson"></param>
        /// <param name="unit_id"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public string GridPageJsonMyQuery(string ParameterJson, JqGridParam jqgridparam)
        {
            //string unit_id = ManageProvider.Provider.Current().CompanyId;

            try
            {
                int       pageIndex = jqgridparam.page;
                int       pageSize  = jqgridparam.rows;
                Stopwatch watch     = CommonHelper.TimerStart();
                string    sql       = "SELECT Base_Goods.goods_id ,Base_Goods.code,Base_Goods.shortcode,Base_Goods.name,Base_Goods.shortname, " +
                                      "Base_Goods.standand,Base_Goods.unit,Base_GoodsType.name As goodstypename,Base_Goods.price,Base_Goods.islimit,Base_Goods.imgurl,Base_Goods.filetype,Base_Goods.limitnum,Base_Goods.state" +
                                      " FROM Base_Goods" +
                                      " LEFT JOIN Base_GoodsType" +
                                      " ON Base_Goods.goodstype_id=Base_GoodsType.goodstype_id";


                DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql);

                var JsonData = new
                {
                    total    = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数
                    page     = jqgridparam.page,                                                      //当前页码
                    records  = dt.Rows.Count,                                                         //总记录数
                    costtime = CommonHelper.TimerEnd(watch),                                          //查询消耗的毫秒数
                    rows     = dt
                };
                return(JsonData.ToJson());
            }
            catch (Exception)
            {
                return(null);
            }
        }
Пример #29
0
        /// <summary>
        /// 订单列表
        /// </summary>
        /// <param name="pickNo">拣货单号</param>
        /// <param name="startTime">订单开始时间</param>
        /// <param name="endTime">订单结束时间</param>
        /// <param name="jqgridparam">分页参数</param>
        /// <param name="warehouseId"></param>
        /// <returns></returns>
        public List <PickMasterViewModel> GetPickMasterList(string warehouseId, string pickNo, string startTime,
                                                            string endTime, JqGridParam jqgridparam)
        {
            var strSql    = new StringBuilder();
            var parameter = new List <DbParameter>();

            strSql.Append(@"SELECT PM.*, W.WarehouseName
                            FROM dbo.Pick_Master PM
                            LEFT JOIN dbo.Warehouse W ON PM.WarehouseId = W.WarehouseId
                            WHERE 1 = 1");
            if (!string.IsNullOrEmpty(warehouseId))
            {
                strSql.Append(" AND PM.WarehouseId = @WarehouseId ");
                parameter.Add(DbFactory.CreateDbParameter("@WarehouseId", warehouseId));
            }

            if (!string.IsNullOrEmpty(pickNo))
            {
                strSql.Append(" AND PM.PickNo = @PickNo ");
                parameter.Add(DbFactory.CreateDbParameter("@PickNo", pickNo));
            }

            if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
            {
                strSql.Append(" AND PM.CreateDate Between @StartTime AND @EndTime ");
                parameter.Add(DbFactory.CreateDbParameter("@StartTime", CommonHelper.GetDateTime(startTime + " 00:00")));
                parameter.Add(DbFactory.CreateDbParameter("@EndTime", CommonHelper.GetDateTime(endTime + " 23:59")));
            }
            return(new Repository <PickMasterViewModel>().FindListPageBySql(strSql.ToString(), parameter.ToArray(),
                                                                            ref jqgridparam));
        }
        /// <summary>
        /// 根据公司id获取部门 列表
        /// </summary>
        /// <param name="CompanyId">公司ID</param>
        /// <returns></returns>
        public DataTable GetList(ref JqGridParam jqgridparam)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"SELECT  *
                            FROM    ( SELECT    d.DepartmentId ,			--主键
                                                c.FullName AS CompanyName ,	--所属公司
                                                d.CompanyId ,				--所属公司Id
                                                d.Code ,					--编码
                                                d.FullName ,				--部门名称
                                                d.ShortName ,				--部门简称
                                                d.Nature ,					--部门性质
                                                d.Manager ,					--负责人
                                                d.Phone ,					--电话
                                                d.Fax ,						--传真
                                                d.Enabled ,					--有效
                                                d.SortCode,                 --排序码
                                                d.Remark					--说明
                                      FROM      Base_Department d
                                                LEFT JOIN Base_Company c ON c.CompanyId = d.CompanyId
                                    ) T WHERE 1=1 ");
            List <DbParameter> parameter = new List <DbParameter>();

            return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam));
            //return Repository().FindTableBySql(strSql.ToString(), parameter.ToArray());
        }
Пример #31
0
 /// <summary>
 /// 查询数据列表、返回 DataTable
 /// </summary>
 /// <param name="strSql">Sql语句</param>
 /// <param name="jqgridparam">分页参数</param>
 /// <returns></returns>
 public DataTable FindTablePageBySql(string strSql, ref JqGridParam jqgridparam)
 {
     string orderField = jqgridparam.sidx;
     string orderType = jqgridparam.sord;
     int pageIndex = jqgridparam.page;
     int pageSize = jqgridparam.rows;
     int totalRow = jqgridparam.records;
     DataTable dt = DataFactory.Database().FindTablePageBySql(strSql, orderField, orderType, pageIndex, pageSize, ref totalRow);
     jqgridparam.records = totalRow;
     return dt;
 }
Пример #32
0
 public ActionResult TestTableMegaListJson(JqGridParam jqgridparam)
 {
     Stopwatch watch = CommonHelper.TimerStart();
     string UserId = ManageProvider.Provider.Current().UserId;
     DataTable ListData = this.FindTablePageBySql("SELECT TestId, Code, FullName, CreateDate, CreateUserName, Remark FROM TestTable", ref jqgridparam);
     var JsonData = new
     {
         total = jqgridparam.total,
         page = jqgridparam.page,
         records = jqgridparam.records,
         costtime = CommonHelper.TimerEnd(watch),
         rows = ListData,
     };
     return Content(JsonData.ToJson());
 }
Пример #33
0
 /// <summary>
 /// 获取数据库表数据
 /// </summary>
 /// <param name="tableName">表名</param>
 /// <param name="ParameterJson">查询条件</param>
 /// <param name="jqgridparam">分页参数</param>
 /// <returns></returns>
 public DataTable GetDataTableList(string tableName, string ParameterJson, ref JqGridParam jqgridparam)
 {
     StringBuilder strSql = new StringBuilder();
     List<DbParameter> parameter = new List<DbParameter>();
     strSql.Append("SELECT * FROM " + tableName + " WHERE 1=1");
     if (!string.IsNullOrEmpty(ParameterJson) && ParameterJson.Length > 2)
     {
         strSql.Append(ConditionBuilder.GetWhereSql(ParameterJson.JonsToList<Condition>(), out parameter));
     }
     int totalRow = jqgridparam.records;
     DataTable dt = DataFactory.Database().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), jqgridparam.sidx, jqgridparam.sord, jqgridparam.page, jqgridparam.rows, ref totalRow);
     jqgridparam.records = totalRow;
     return dt;
 }
Пример #34
0
 /// <summary>
 /// 查询数据库表数据
 /// </summary>
 /// <param name="tableName">表名</param>
 /// <param name="ParameterJson">查询条件</param>
 /// <param name="jqgridparam">分页参数</param>
 /// <returns></returns>
 public ActionResult GridDataTableListJson(string tableName, string ParameterJson, JqGridParam jqgridparam)
 {
     try
     {
         Stopwatch watch = CommonHelper.TimerStart();
         DataTable ListData = base_databasebll.GetDataTableList(tableName, ParameterJson, ref jqgridparam);
         var JsonData = new
         {
             total = jqgridparam.total,
             page = jqgridparam.page,
             records = jqgridparam.records,
             costtime = CommonHelper.TimerEnd(watch),
             rows = ListData,
         };
         string str = JsonData.ToJson();
         return Content(JsonData.ToJson());
     }
     catch (Exception ex)
     {
         Base_SysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message);
         return null;
     }
 }