public void ExecutePagingListTest()
 {
     var script = @"Select * From TBL_DEVICE_INFO WHERE PKID >10 ORDER BY PKID";
     var paging = new Pagination() { CurrentPageIndex = 0, PageSize = 25, Paging = true };
     var result = DbHelper.ExecutePagingList<DeviceInfo>(script, paging);
     Assert.IsTrue(result != null);
 }

        public List<CurrencyExport> GetList(int userId, Pagination paging)
        {
            string sql = null;
            List<DbParameter> parameterList = new List<DbParameter>();

            sql = " select PkId, OrgId, DeviceNumber, OperateStartTime, OperateEndTime, CurrencyNumber, ExportStatus, DataCount, FileName, FileSize, CreateUserId, CreateTime from tbl_currency_export where 1=1 ";

            if (userId >= 0)
            {
                sql += " and CreateUserId=:UserId ";

                parameterList.Add(new OracleParameter(":UserId", userId));
            }

            sql += " order by PkId desc ";

            if (paging != null)
            {
                return DbHelper.ExecutePagingList<CurrencyExport>(sql, paging, parameterList.ToArray());
            }

            else
            {
                return DbHelper.ExecuteList<CurrencyExport>(sql, CommandType.Text, parameterList.ToArray());
            }
        }

        //public ActionResult ProductsPartial(string categoryId = null)
        //{
        //    using (var proxy = new ProductServiceClient())
        //    {
        //        IEnumerable<ProductDto> products = null;
        //        products = string.IsNullOrEmpty((categoryId)) ? proxy.GetProducts() : proxy.GetProductsForCategory(new Guid(categoryId));
        //        if (string.IsNullOrEmpty(categoryId))
        //            ViewBag.CategoryName = "所有商品";
        //        else
        //        {
        //            var category = proxy.GetCategoryById(new Guid(categoryId));
        //            ViewBag.CategoryName = category.Name;
        //        }

        //        ViewBag.CategoryId = categoryId;
        //        return PartialView(products);
        //    }
        //}

        /// <summary>
        /// 商品页面的分页支持
        /// </summary>
        /// <param name="categoryId">类别Id</param>
        /// <param name="fromIndexPage">是否来源首页点击</param>
        /// <param name="pageNumber">页数</param>
        /// <returns></returns>
        public ActionResult ProductsPartial(string categoryId = null, bool? fromIndexPage = null, int pageNumber =1)
        {
            using (var proxy = new ProductServiceClient())
            {
                var numberOfProductsPerPage = int.Parse(ConfigurationManager.AppSettings["productsPerPage"]);
                var pagination = new Pagination { PageSize = numberOfProductsPerPage, PageNumber = pageNumber };
                ProductDtoWithPagination productsDtoWithPagination = null;

                productsDtoWithPagination = string.IsNullOrEmpty((categoryId)) ? 
                    proxy.GetProductsWithPagination(pagination) : 
                    proxy.GetProductsForCategoryWithPagination(new Guid(categoryId), pagination);
                
                if (string.IsNullOrEmpty(categoryId))
                    ViewBag.CategoryName = "所有商品";
                else
                {
                    var category = proxy.GetCategoryById(new Guid(categoryId));
                    ViewBag.CategoryName = category.Name;
                }

                ViewBag.CategoryId = categoryId;
                ViewBag.FromIndexPage = fromIndexPage;
                if (fromIndexPage == null || fromIndexPage.Value)
                    ViewBag.Action = "Index";
                else
                    ViewBag.Action = "Category"; 
                ViewBag.IsFirstPage = productsDtoWithPagination.Pagination.PageNumber == 1;
                ViewBag.IsLastPage = productsDtoWithPagination.Pagination.PageNumber == productsDtoWithPagination.Pagination.TotalPages;
                return PartialView(productsDtoWithPagination);
            }
        }

        public void init()
        {
            currentUrl = Page.Request.Path;
            queryString = Page.Request.ServerVariables["Query_String"];

            Model = new Pagination(Index, count, pageSize, currentUrl, queryString);
        }

        public List<UserRole> GetList(string roleName, Pagination paging)
        {
            string sql = null;
            List<DbParameter> parameterList = new List<DbParameter>();

            sql = " select PkId, RoleName, DataFilter, RoleStatus from tbl_user_role Where 1=1 ";

            if (roleName.IsNotNullOrEmpty())
            {
                sql += " and instr(RoleName, :RoleName) > 0 ";

                parameterList.Add(new OracleParameter(":RoleName", roleName));
            }

            sql += " order by PkId desc ";

            if (paging != null)
            {
                return DbHelper.ExecutePagingList<UserRole>(sql, paging, parameterList.ToArray());
            }

            else
            {
                return DbHelper.ExecuteList<UserRole>(sql, CommandType.Text, parameterList.ToArray());
            }
        }

        public List<CurrencyBlacklist> GetList(string currencyNumber, Pagination paging)
        {
            string sql = null;
            List<DbParameter> parameterList = new List<DbParameter>();

            sql = " select PkId, CurrencyKindCode, FaceAmount, CurrencyVersion, CurrencyNumber from tbl_currency_blacklist where 1=1 ";

            if (currencyNumber.IsNotNullOrEmpty())
            {
                sql += " and CurrencyNumber like concat(\'%\', {0}, \'%\') ".FormatWith("@CurrencyNumber");

                parameterList.Add(new MySqlParameter("@CurrencyNumber", currencyNumber));
            }

            if (paging != null)
            {
                return DbHelper.ExecutePagingList<CurrencyBlacklist>(sql, paging, parameterList.ToArray());
            }

            else
            {
                sql += " order by PkId desc ";

                return DbHelper.ExecuteList<CurrencyBlacklist>(sql, CommandType.Text, parameterList.ToArray());
            }
        }

        public List<UserRole> GetList(string roleName, Pagination paging)
        {
            string sql = null;
            List<DbParameter> parameterList = new List<DbParameter>();

            sql = " select PkId, RoleName, DataFilter, RoleStatus from tbl_user_role Where 1=1 ";

            if (roleName.IsNotNullOrEmpty())
            {
                sql += " and RoleName like concat(\'%\', {0}, \'%\') ".FormatWith("@RoleName");

                parameterList.Add(new MySqlParameter("@RoleName", roleName));
            }

            sql += " order by PkId desc ";

            if (paging != null)
            {
                return DbHelper.ExecutePagingList<UserRole>(sql, paging, parameterList.ToArray());
            }

            else
            {
                return DbHelper.ExecuteList<UserRole>(sql, CommandType.Text, parameterList.ToArray());
            }
        }

        public List<CurrencyBlacklist> GetList(string currencyNumber, Pagination paging)
        {
            string sql = null;
            List<DbParameter> parameterList = new List<DbParameter>();

            sql = " select PkId, CurrencyKindCode, FaceAmount, CurrencyVersion, CurrencyNumber from tbl_currency_blacklist where 1=1 ";

            if (currencyNumber.IsNotNullOrEmpty())
            {
                sql += " and instr(CurrencyNumber, :CurrencyNumber) > 0 ";

                parameterList.Add(new OracleParameter(":CurrencyNumber", currencyNumber));
            }

            sql += " order by PkId desc ";

            if (paging != null)
            {
                return DbHelper.ExecutePagingList<CurrencyBlacklist>(sql, paging, parameterList.ToArray());
            }

            else
            {
                return DbHelper.ExecuteList<CurrencyBlacklist>(sql, CommandType.Text, parameterList.ToArray());
            }
        }

        public List<DeviceInfo> GetList(int orgId, bool isUnknownOrg, string deviceNumber, string registerIp, int deviceKind, int deviceModel, Pagination paging)
        {
            string sql = null;
            List<DbParameter> parameterList = new List<DbParameter>();

            sql = " select PkId, DeviceNumber, SoftwareVersion, RegisterIp, KindCode, ModelCode, OrgId, OnLineTime, DeviceStatus from tbl_device_info Where 1=1 ";

            if (isUnknownOrg || (!isUnknownOrg && orgId > 0))
            {
                sql += " and [email protected] ";

                parameterList.Add(new MySqlParameter("@OrgId", orgId));
            }

            if (!isUnknownOrg && orgId == 0)
            {
                sql += " and OrgId>0 ";
            }

            if (deviceNumber.IsNotNullOrEmpty())
            {
                sql += " and DeviceNumber like concat(\'%\', {0}, \'%\') ".FormatWith("@DeviceNumber");

                parameterList.Add(new MySqlParameter("@DeviceNumber", deviceNumber));
            }

            if (registerIp.IsNotNullOrEmpty())
            {
                sql += " and RegisterIp like concat(\'%\', {0}, \'%\') ".FormatWith("@RegisterIp");

                parameterList.Add(new MySqlParameter("@RegisterIp", registerIp));
            }

            if (deviceKind > 0)
            {
                sql += " and [email protected] ";

                parameterList.Add(new MySqlParameter("@KindCode", deviceKind));
            }

            if (deviceModel > 0)
            {
                sql += " and [email protected] ";

                parameterList.Add(new MySqlParameter("@ModelCode", deviceModel));
            }

            sql += " order by PkId desc ";

            if (paging != null)
            {
                return DbHelper.ExecutePagingList<DeviceInfo>(sql, paging, parameterList.ToArray());
            }

            else
            {
                return DbHelper.ExecuteList<DeviceInfo>(sql, CommandType.Text, parameterList.ToArray());
            }
        }

 public void ExecutePagingListTestWithCustomerConvert()
 {
     var script = @"Select * From TBL_CURRENCY_INFO WHERE PKID >10 ORDER BY PKID";
     var paging = new Pagination() { CurrentPageIndex = 0, PageSize = 2, Paging = true };
     var result = DbHelper.ExecutePagingList<CurrencyInfo>(script, paging, Convert);
     Assert.IsTrue(result.Count > 0);
     Assert.IsTrue(result[0].CurrencyImage.Length > 0);
 }

        public ActionResult Betting(int? userid, int p = 0)
        {
            try
            {
                ViewBag.Title = "投注记录";
                ViewBag.Title2 = "投注记录";
                ViewBag.Page = "投注";

                var currentUser = Session["user"] as DBC.User;
                var pages = 1;
                var sqlCount = "";
                var sqlList = "";
                var sqlCountArgs = new List<object>();
                var sqlListArgs = new List<object>();

                var current = GetCurrentUser();
                sqlCount = string.Format("select count(*) from {0} where {0}.userid=?", DBTables.Betting, DBTables.User);
                sqlCountArgs.Add(current.ID);

                sqlList = string.Format("select id from {0} where {0}.userid=?  order by time desc limit ?,?", DBTables.Betting, DBTables.User);
                sqlListArgs.Add(current.ID);

                //添加分页参数
                sqlListArgs.Add(p * _itemsPerPage);
                sqlListArgs.Add(_itemsPerPage);

                var totalCount = Convert.ToInt32(DB.SExecuteScalar(sqlCount, sqlCountArgs.ToArray()));
                pages = (int)Math.Ceiling(totalCount * 1.0 / _itemsPerPage);
                var res = DB.SExecuteReader(sqlList, sqlListArgs.ToArray());

                var bettingList = new List<DBC.Betting>();
                foreach (var item in res)
                {
                    //数据记录不完整时跳过
                    try
                    {
                        var id = Convert.ToInt32(item[0]);
                        var betting = new DBC.Betting(id);
                        bettingList.Add(betting);
                    }
                    catch { }
                }

                var pagination = new Pagination();
                pagination.Pages = pages;
                pagination.Current = p;
                pagination.BaseUrl = "/home/betting";
                ViewBag.pagination = pagination;

                ViewBag.list = GetBettingOverviewList(bettingList);
            }
            catch
            {
                ViewBag.errorText = "未查询到任何记录";
            }

            return View();
        }

 public IEnumerable<Resource.Service> Get(Pagination pagination)
 {
     try {
         return Client.RequestList<Resource.Service>("GET", @"service/calls", null, pagination);
     }
     catch (System.Web.HttpException e) {
         Exception = e;
     }
     return null;
 }

 public IEnumerable<Resource.Recording> Get(Pagination pagination = null)
 {
     try {
         return Client.RequestList<Resource.Recording>("GET", "recordings", null, pagination);
     }
     catch (HttpException e) {
         Exception = e;
     }
     return null;
 }

 public IEnumerable<Resource.Group> Get(Pagination pagination)
 {
     try {
         return Client.RequestList<Resource.Group>("GET", "groups", null, pagination);
     }
     catch (HttpException e) {
         Exception = e;
     }
     return null;
 }

        public List<DeviceConnection> GetList(string deviceNumber, int orgId, string collectorName, string deviceIp, int connectionStatus, Pagination paging)
        {
            string sql = null;
            List<DbParameter> parameterList = new List<DbParameter>();

            sql = " select PkId, DeviceNumber, DeviceIp, OrgId, CollectorName, CollectorIp, ConnectTime, DisconnectTime, ConnectionStatus, UploadCount from tbl_device_connection Where 1=1 ";

            if (deviceNumber.IsNotNullOrEmpty())
            {
                sql += " and instr(DeviceNumber, :DeviceNumber) > 0 ";

                parameterList.Add(new OracleParameter(":DeviceNumber", deviceNumber));
            }

            if (orgId > 0)
            {
                sql += " and OrgId=:OrgId ";

                parameterList.Add(new OracleParameter(":OrgId", orgId));
            }

            if (collectorName.IsNotNullOrEmpty())
            {
                sql += " and instr(CollectorName, :CollectorName) > 0 ";

                parameterList.Add(new OracleParameter(":CollectorName", collectorName));
            }

            if (deviceIp.IsNotNullOrEmpty())
            {
                sql += " and instr(DeviceIp, :DeviceIp) > 0 ";

                parameterList.Add(new OracleParameter(":DeviceIp", deviceIp));
            }

            if (connectionStatus > 0)
            {
                sql += " and ConnectionStatus=:ConnectionStatus ";

                parameterList.Add(new OracleParameter(":ConnectionStatus", connectionStatus));
            }

            sql += " order by PkId desc ";

            if (paging != null)
            {
                return DbHelper.ExecutePagingList<DeviceConnection>(sql, paging, parameterList.ToArray());
            }

            else
            {
                return DbHelper.ExecuteList<DeviceConnection>(sql, CommandType.Text, parameterList.ToArray());
            }
        }

        public List<DeviceConnection> GetList(string deviceNumber, int orgId, string collectorName, string deviceIp, int connectionStatus, Pagination paging)
        {
            string sql = null;
            List<DbParameter> parameterList = new List<DbParameter>();

            sql = " select PkId, DeviceNumber, DeviceIp, OrgId, CollectorName, CollectorIp, ConnectTime, DisconnectTime, ConnectionStatus, UploadCount from tbl_device_connection Where 1=1 ";

            if (deviceNumber.IsNotNullOrEmpty())
            {
                sql += " and DeviceNumber like concat(\'%\', {0}, \'%\') ".FormatWith("@DeviceNumber");

                parameterList.Add(new MySqlParameter("@DeviceNumber", deviceNumber));
            }

            if (orgId > 0)
            {
                sql += " and [email protected] ";

                parameterList.Add(new MySqlParameter("@OrgId", orgId));
            }

            if (collectorName.IsNotNullOrEmpty())
            {
                sql += " and CollectorName like concat(\'%\', {0}, \'%\') ".FormatWith("@CollectorName");

                parameterList.Add(new MySqlParameter("@CollectorName", collectorName));
            }

            if (deviceIp.IsNotNullOrEmpty())
            {
                sql += " and DeviceIp like concat(\'%\', {0}, \'%\') ".FormatWith("@DeviceIp");

                parameterList.Add(new MySqlParameter("@DeviceIp", deviceIp));
            }

            if (connectionStatus > 0)
            {
                sql += " and [email protected] ";

                parameterList.Add(new MySqlParameter("@ConnectionStatus", connectionStatus));
            }

            sql += " order by PkId desc ";

            if (paging != null)
            {
                return DbHelper.ExecutePagingList<DeviceConnection>(sql, paging, parameterList.ToArray());
            }

            else
            {
                return DbHelper.ExecuteList<DeviceConnection>(sql, CommandType.Text, parameterList.ToArray());
            }
        }

 public IEnumerable<Resource.Contact> Get(string groupId, Pagination pagination = null)
 {
     var endpoint = "contacts";
     if (!string.IsNullOrEmpty(groupId)) {
         endpoint += "/" + groupId;
     }
     try {
         return Client.RequestList<Resource.Contact>("GET", endpoint, null, pagination);
     }
     catch (HttpException e) {
         Exception = e;
     }
     return null;
 }

		/// <summary>
		/// Get all existing MyLibrary folders
		/// </summary>
		/// <param name="sortBy">Specifies how the list of folders is sorted</param>
		/// <param name="limit">Specifies the number of results per page in the output, from 1 - 50, default = 50.</param>
		/// <param name="pag">Pagination object.</param>
		/// <returns>Returns a collection of MyLibraryFolder objects.</returns>
		public ResultSet<MyLibraryFolder> GetLibraryFolders(FoldersSortBy? sortBy, int? limit, Pagination pag)
		{
            string url = (pag == null) ? String.Concat(Settings.Endpoints.Default.BaseUrl, Settings.Endpoints.Default.MyLibraryFolders, GetQueryParameters(new object[] { "sort_by", sortBy, "limit", limit })) : pag.GetNextUrl();
            RawApiResponse response = RestClient.Get(url, UserServiceContext.AccessToken, UserServiceContext.ApiKey);
            try
            {
                var results = response.Get<ResultSet<MyLibraryFolder>>();
                return results;
            }
            catch (Exception ex)
            {
                throw new CtctException(ex.Message, ex);
            } 
		}

 /// <summary>
 /// Get a set of campaigns.
 /// </summary>
 /// <param name="status">Returns list of email campaigns with specified status.</param>
 /// <param name="limit">Specifies the number of results per page in the output, from 1 - 500, default = 500.</param>
 /// <param name="modifiedSince">limit campaigns to campaigns modified since the supplied date</param>
 /// <param name="pag">Pagination object returned by a previous call to GetCampaigns</param>
 /// <returns>Returns a ResultSet of campaigns.</returns>
 public ResultSet<EmailCampaign> GetCampaigns(CampaignStatus? status, int? limit, DateTime? modifiedSince, Pagination pag)
 {
     string url = (pag == null) ? String.Concat(Settings.Endpoints.Default.BaseUrl, Settings.Endpoints.Default.Campaigns, GetQueryParameters(new object[] { "status", status, "limit", limit, "modified_since", Extensions.ToISO8601String(modifiedSince) })) : pag.GetNextUrl();
     RawApiResponse response = RestClient.Get(url, UserServiceContext.AccessToken, UserServiceContext.ApiKey);
     try
     {
         var results = response.Get<ResultSet<EmailCampaign>>();
         return results;
     }
     catch (Exception ex)
     {
         throw new CtctException(ex.Message, ex);
     } 
 }

        /// <summary>
        /// Get a result set of bounces for a given campaign.
        /// </summary>
        /// <param name="campaignId">Campaign id.</param>
        /// <param name="limit">Specifies the number of results per page in the output, from 1 - 500, default = 500.</param>
		/// <param name="createdSince">Filter for bounces created since the supplied date in the collection</param>
        /// <param name="pag">Pagination object.</param>
        /// <returns>ResultSet containing a results array of @link BounceActivity.</returns>
        private ResultSet<BounceActivity> GetBounces(string campaignId, int? limit, DateTime? createdSince, Pagination pag)
        {
            string url = (pag == null) ? ConstructUrl(Settings.Endpoints.Default.CampaignTrackingBounces, new object[] { campaignId }, new object[] { "limit", limit, "created_since", Extensions.ToISO8601String(createdSince) }) : pag.GetNextUrl();
            RawApiResponse response = RestClient.Get(url, UserServiceContext.AccessToken, UserServiceContext.ApiKey);
            try
            {
                var results = response.Get<ResultSet<BounceActivity>>();
                return results;
            }
            catch (Exception ex)
            {
                throw new CtctException(ex.Message, ex);
            }
        }