Пример #1
0
        public void TestXxMethod()
        {
            var helper = new MySqlDbHelper("mysqldb");

            var query = new AAQueryModel();
            query.PageIndex = 1;
            query.PageSize = 5;
            query.Group = "";

            const string spName = "sp_common_pager";
            const string tableName = @"car_info";
            const string fields = " * ";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? "createdtime desc" : query.Order;
            //查询条件
            var sqlWhere = "1=1";
            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere, query.PageSize, query.PageIndex);
            var list2 = helper.ExecutePaging<dynamic>(model, query.Echo);

            Assert.IsNotNull(list2);
        }
Пример #2
0
        /// <summary>
        /// 获取正在拍卖的车辆列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<AuctionCarInfoViewModel> GetAuctioningList(AuctionCarInfoQueryModel query)
        {
            const string spName = "sp_common_pager";
            string tableName = @"auction_carinfo as a
                                    left join car_info as b on b.innerid=a.carid
                                    left join base_carbrand as c1 on b.brand_id=c1.innerid
                                    left join base_carseries as c2 on b.series_id=c2.innerid
                                    left join base_carmodel as c3 on b.model_id=c3.innerid
                                    left join base_city as ct on b.cityid=ct.innerid
                                    left join base_province as pr on b.provid=pr.innerid
                                    left join (select count(1) as count,auctionid from auction_participant group by auctionid) d on d.auctionid=a.innerid ";

            string fields = @"a.innerid,a.mobile,a.lowestprice,a.status as auditstatus,b.pic_url,b.status,b.price,b.mileage,b.register_date,a.validtime,b.createdtime
                ,c1.brandname as brand_name,a.no,c2.seriesname as series_name,c3.modelname as model_name,c3.modelprice,ct.cityname,pr.provname,d.count";

            if (!string.IsNullOrWhiteSpace(query.userid))
            {
                tableName += " left join (select count(1) as follow, auctionid from auction_follow where isdelete=0 and userid = '" + query.userid + "' group by auctionid ) e on e.auctionid = a.innerid";
                fields += " ,ifnull(e.follow,0) as follow ";
            }

            var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime asc " : query.Order;

            var sqlWhere = new StringBuilder(" a.status=6 ");

            //省份
            if (query.provid != null)
            {
                sqlWhere.Append($" and b.provid={query.provid}");
            }

            //城市
            if (query.cityid != null)
            {
                sqlWhere.Append($" and b.cityid={query.cityid}");
            }

            //品牌
            if (query.brand_id != null && query.brand_id != 0)
            {
                sqlWhere.Append($" and b.brand_id={query.brand_id}");
            }

            //车系
            if (query.series_id != null && query.series_id != 0)
            {
                sqlWhere.Append($" and b.series_id={query.series_id}");
            }

            //车型
            if (query.model_id != null && query.model_id != 0)
            {
                sqlWhere.Append($" and b.model_id={query.model_id}");
            }

            //开始时间
            if (query.publishedtime.HasValue)
            {
                sqlWhere.Append($" and a.publishedtime='{query.publishedtime}'");
            }

            //结束时间
            if (query.validtime.HasValue)
            {
                sqlWhere.Append($" and a.validtime='{query.validtime}'");
            }
            else
            {
                sqlWhere.Append($" and a.validtime>='{DateTime.Now}'");
            }

            //里程数
            if (query.minmileage.HasValue)
            {
                sqlWhere.Append($" and b.mileage>={query.minmileage}");
            }
            //里程数
            if (query.maxmileage.HasValue)
            {
                sqlWhere.Append($" and b.mileage<{query.maxmileage}");
            }
            //上牌时间
            if (query.register_date.HasValue)
            {
                sqlWhere.Append($" and YEAR(b.register_date)=YEAR('{query.register_date}')");
            }

            var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<AuctionCarInfoViewModel>(model, query.Echo);
            return list;
        }
Пример #3
0
        /// <summary>
        /// 评论列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<CommentListModel> GetCommentPageList(CommentQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @" settled_comment as a left join settled_info as b on b.innerid =a.companyid";
            const string fields = @"a.innerid, a.companyid, ifnull(a.mobile,'') as mobile, a.headportrait, a.score, a.ip, ifnull(a.commentdesc,'') as commentdesc, a.createdtime,b.companyname,a.pictures";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? " createdtime desc" : query.Order;
            //查詢條件
            var sqlWhere = new StringBuilder(" 1=1 and (isdelete <>1 or isdelete is null) ");
            if (!string.IsNullOrWhiteSpace(query.Companyid))
            {
                sqlWhere.Append($" and companyid='{query.Companyid}'");
            }
            if (!string.IsNullOrWhiteSpace(query.CompanyName))
            {
                sqlWhere.Append($" and companyname like '%{query.CompanyName}%'");
            }

            if (query.OnlyLow)
            {
                sqlWhere.Append(" and score<=2");
            }

            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CommentListModel>(model, query.Echo);
            return list;
        }
Пример #4
0
        /// <summary>
        /// 修改申请列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<CompanyUpdateApplyListModel> GetUpdateApplyPageList(CompanyUpdateApplyQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @" settled_info_applyupdate as a left join settled_info as b on b.innerid=a.settid left join sys_user as c on c.innerid=a.modifierid ";
            const string fields = @"a.innerid, b.companyname, a.address, b.opername,  b.originalregistcapi, b.companystatus, a.officephone, a.picurl, a.companytitle, a.customdesc, a.boutiqueurl,a.status,ifnull(a.contactsphone,'') as contactsphone, a.createrid, a.createdtime, ifnull(c.username,'') as modifierid, a.modifiedtime,
            (select group_concat(codename) from base_code where typekey='car_ancestry' and FIND_IN_SET(codevalue,a.ancestryids)) as ancestryname,
            (select group_concat(codename) from base_code where typekey='car_category' and FIND_IN_SET(codevalue,a.categoryids)) as categoryname";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime desc" : query.Order;
            //查詢條件
            var sqlWhere = new StringBuilder(" 1=1 ");

            if (!string.IsNullOrWhiteSpace(query.Settid))
            {
                sqlWhere.Append($" and settid='{query.Settid}'");
            }

            if (!string.IsNullOrWhiteSpace(query.CompanyName))
            {
                sqlWhere.Append($" and companyname like '%{query.CompanyName}%'");
            }

            if (!string.IsNullOrWhiteSpace(query.Address))
            {
                sqlWhere.Append($" and address like '%{query.Address}%'");
            }

            if (!string.IsNullOrWhiteSpace(query.Address))
            {
                sqlWhere.Append($" and officephone like '%{query.OfficePhone}%'");
            }

            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CompanyUpdateApplyListModel>(model, query.Echo);
            return list;
        }
Пример #5
0
 /// <summary>
 /// 获取cust_wechat信息列表
 /// </summary>
 /// <param name="query"></param>
 /// <returns></returns>
 public BasePageList<CustWeChatViewModel> GetCustWeChatList(CustWeChatQueryModel query)
 {
     const string spName = "sp_common_pager";
     const string tableName = @" wechat_friend ";
     const string fields = @"ifnull(accountid,'') accountid,ifnull(nickname,'') nickname,ifnull(openid,'') openid,ifnull(remarkname,'') remarkname,sex,ifnull(createdtime,current_date()) createdtime
                             ,innerid, nickname, photo, area, isdel, subscribe_time, subscribe, country, province, city";
     var orderField = string.IsNullOrWhiteSpace(query.Order) ? " createdtime desc" : query.Order;
     //查詢條件
     var sqlWhere = new StringBuilder(" 1=1 ");
     if (!string.IsNullOrWhiteSpace(query.nickname))
     {
         sqlWhere.Append($" and nickname like '%{query.nickname}%'");
     }
     var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
     var list = Helper.ExecutePaging<CustWeChatViewModel>(model, query.Echo);
     return list;
 }
Пример #6
0
        /// <summary>
        /// 获取投票活动的参赛人员列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<VotePerListModel> GetVotePerList(VotePerQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"activity_vote_per as a ";
            const string fields = "innerid, activityid, fullname, num, picture, mobile, ip,isaudit, openid, createrid, createdtime, modifierid, modifiedtime," +
                                  "(select count(1) from activity_vote_log where perid=a.innerid and invalid=0) as votenum";
            var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime asc " : query.Order;

            var sqlWhere = new StringBuilder($" a.activityid='{query.Activityid}' ");

            if (!string.IsNullOrWhiteSpace(query.Mobile))
            {
                sqlWhere.Append($" and mobile like '%{query.Mobile}%'");
            }

            if (query.Num != null)
            {
                sqlWhere.Append($" and num={query.Num}");
            }

            if (!string.IsNullOrWhiteSpace(query.Fullname))
            {
                sqlWhere.Append($" and fullname like '%{query.Fullname}%'");
            }

            if (!string.IsNullOrWhiteSpace(query.Openid))
            {
                sqlWhere.Append($" and openid like '%{query.Openid}%'");
            }

            //前端调用,必须是1审核通过的
            if (query.Isfront == 1)
            {
                query.Status = 1;
            }

            if (query.Status != null)
            {
                sqlWhere.Append($" and isaudit={query.Status}");
            }

            var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<VotePerListModel>(model, query.Echo);
            return list;
        }
Пример #7
0
        /// <summary>
        /// 获取投票活动列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<VoteListModel> GetVoteList(VoteQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"activity_vote_info as a ";
            const string fields = "innerid, title, enrollstarttime, enrollendtime, votestarttime, voteendtime,votemode,voteflow,prizedeac,awardstime,createdtime," +
                                  "(select count(1) from activity_vote_per where activityid=a.innerid) as numper," +
                                  "(select count(1) from activity_vote_log where activityid=a.innerid and invalid=0) as numvote";
            var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime asc " : query.Order;

            var sqlWhere = new StringBuilder(" 1=1 ");
            var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<VoteListModel>(model, query.Echo);
            return list;
        }
Пример #8
0
        /// <summary>
        /// 获取投票活动的参赛人员列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<CrowdGradeModel> GetGradePageList(QueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"activity_vote_per as a ";
            const string fields = "innerid, activityid, fullname, num, picture, mobile, ip, openid, createrid, createdtime, modifierid, modifiedtime,(select count(1) from  where perid=a.innerid) as votenum";
            var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime asc " : query.Order;

            var sqlWhere = new StringBuilder($" a.activity='' ");

            var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CrowdGradeModel>(model, query.Echo);
            return list;
        }
Пример #9
0
 /// <summary>
 /// 获取基础数据代码值列表
 /// </summary>
 /// <param name="query"></param>
 /// <returns></returns>
 public BasePageList<BaseCodeSelectModel> GetCodeList(BaseCodeQueryModel query)
 {
     const string spName = "sp_common_pager";
     const string tableName = @"base_code as a left join base_code_type as b on a.typekey=b.typekey ";
     const string fields = "a.innerid,codevalue,codename,sort,a.typekey,a.isenabled,ifnull(remark,'') remark,b.typename";
     var oldField = string.IsNullOrWhiteSpace(query.Order) ? " sort asc,b.typename asc " : query.Order;
     var sqlWhere = new StringBuilder("1=1");
     if (!string.IsNullOrWhiteSpace(query.Typekey))
     {
         sqlWhere.Append($" and a.typekey = '{query.Typekey}'");
     }
     if (!string.IsNullOrWhiteSpace(query.CodeName))
     {
         sqlWhere.Append($" and codename like '%{query.CodeName}%'");
     }
     var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
     var list = Helper.ExecutePaging<BaseCodeSelectModel>(model, query.Echo);
     return list;
 }
Пример #10
0
 /// <summary>
 /// 获取车系列表
 /// </summary>
 /// <param name="query">查询条件</param>
 /// <returns></returns>
 public BasePageList<BaseCarSeriesListViewModel> GetCarSeriesList(BaseCarSeriesQueryModel query)
 {
     const string spName = "sp_common_pager";
     const string tableName = @"base_carseries as a left join base_carbrand as b on a.brandid=b.innerid ";
     const string fields = "a.innerid,seriesname,seriesgroupname,brandid,a.isenabled,ifnull(a.remark,'') remark,b.brandname ";
     var orderField = string.IsNullOrWhiteSpace(query.Order) ? " brandid asc" : query.Order;
     //查詢條件
     var sqlWhere = new StringBuilder("1=1");
     if (!string.IsNullOrWhiteSpace(query.SeriesName))
     {
         sqlWhere.Append($" and seriesname like '%{query.SeriesName}%'");
     }
     if (!string.IsNullOrWhiteSpace(query.BrandId))
     {
         sqlWhere.Append($" and b.innerid = '{query.BrandId}'");
     }
     var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
     var list = Helper.ExecutePaging<BaseCarSeriesListViewModel>(model, query.Echo);
     return list;
 }
Пример #11
0
        /// <summary>
        /// 获取车辆列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public BasePageList<CarInfoListViewModel> GetCarPageList(CarQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"car_info as a
                                    left join base_carbrand as c1 on a.brand_id=c1.innerid
                                    left join base_carseries as c2 on a.series_id=c2.innerid
                                    left join base_carmodel as c3 on a.model_id=c3.innerid
                                    left join base_city as ct on a.cityid=ct.innerid ";
            const string fields = "a.innerid,a.pic_url,a.price,a.buyprice,a.dealprice,a.buytime,a.status,a.mileage,a.register_date,c1.brandname as brand_name,c2.seriesname as series_name,c3.modelname as model_name,ct.cityname";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? "a.createdtime desc" : query.Order;

            #region 查询条件
            var sqlWhere = new StringBuilder("1=1");

            sqlWhere.Append(query.status != null
                ? $" and a.status={query.status}"
                : " and a.status<>0");

            if (!string.IsNullOrWhiteSpace(query.custid))
            {
                sqlWhere.Append($" and a.custid='{query.custid}'");
            }

            if (!string.IsNullOrWhiteSpace(query.title))
            {
                sqlWhere.Append($" and a.title like '%{query.title}%'");
            }

            //省份
            if (query.provid != null)
            {
                sqlWhere.Append($" and a.provid={query.provid}");
            }

            //城市
            if (query.cityid != null)
            {
                sqlWhere.Append($" and a.cityid={query.cityid}");
            }

            //品牌
            if (query.brand_id != null)
            {
                sqlWhere.Append($" and a.brand_id={query.brand_id}");
            }

            //车系
            if (query.series_id != null)
            {
                sqlWhere.Append($" and a.series_id={query.series_id}");
            }

            //车型
            if (query.model_id != null)
            {
                sqlWhere.Append($" and a.model_id={query.model_id}");
            }

            //收购价大于..
            if (query.minbuyprice.HasValue)
            {
                sqlWhere.Append($" and a.buyprice>={query.minbuyprice}");
            }

            //收购价小于..
            if (query.maxbuyprice.HasValue)
            {
                sqlWhere.Append($" and a.buyprice<={query.maxbuyprice}");
            }

            if (!string.IsNullOrWhiteSpace(query.SearchField) && query.model_id == null)
            {
                //sqlWhere.Append($" and (c1.brandname like '%{query.SearchField}%' or c2.seriesname like '%{query.SearchField}%')");
                //车辆添加时会将【品牌/车系】放到该字段
                sqlWhere.Append($" and title like '%{query.SearchField}%'");
            }

            #endregion

            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CarInfoListViewModel>(model, query.Echo);
            return list;
        }
Пример #12
0
        /// <summary>
        /// 获取会员积分记录列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public BasePageList<CustPointViewModel> GetCustPointLogPageList(CustPointQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"point_record as a left join base_code as b on a.sourceid=b.codevalue and b.typekey='point_source'";
            const string fields = "a.innerid, a.custid, a.type, a.point, a.remark, a.validtime, a.createdtime,b.codename as source";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? "a.createdtime desc" : query.Order;
            //查询条件
            var sqlWhere = new StringBuilder($"a.custid='{query.Custid}'");

            if (query.Type > 0)
            {
                sqlWhere.Append($" and a.type={query.Type}");
            }

            if (query.Sourceid > 0)
            {
                sqlWhere.Append($" and a.sourceid={query.Sourceid}");
            }

            if (query.MinPoint > 0)
            {
                sqlWhere.Append($" and a.point>={query.MinPoint}");
            }

            if (query.MaxPoint > 0)
            {
                sqlWhere.Append($" and a.point<={query.MaxPoint}");
            }

            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CustPointViewModel>(model, query.Echo);
            return list;
        }
Пример #13
0
        /// <summary>
        /// 获取获取礼券列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public BasePageList<CouponInfoModel> GetCouponPageList(CouponQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"coupon_card";
            const string fields = "innerid, title, titlesub, amount, logourl, vtype, vstart, vend, value1, value2, maxcount, count, codetype, createdtime, modifiedtime, isenabled";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? "createdtime desc" : query.Order;
            //查询条件
            var sqlWhere = new StringBuilder("1=1");

            if (query.IsEnabled.HasValue)
            {
                sqlWhere.Append($" and isenabled={query.IsEnabled}");
            }

            if (!string.IsNullOrWhiteSpace(query.Title))
            {
                sqlWhere.Append($" and title like '%{query.Title}%'");
            }

            if (!string.IsNullOrWhiteSpace(query.Titlesub))
            {
                sqlWhere.Append($" and titlesub like '%{query.Titlesub}%'");
            }

            if (query.MinAmount > 0)
            {
                sqlWhere.Append($" and amount>={query.MinAmount}");
            }

            if (query.MaxAmount > 0)
            {
                sqlWhere.Append($" and amount<={query.MaxAmount}");
            }

            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CouponInfoModel>(model, query.Echo);
            return list;
        }
Пример #14
0
        /// <summary>
        /// 获取竞拍人列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<AuctionCarParticipantViewModel> GetAuctionParticipantList(AuctionCarParticipantQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"auction_participant as a
                                       left join auction_carinfo as b on b.innerid=a.auctionid
                                       left join car_info as c on c.innerid=b.carid
                                       left join base_carmodel as c1 on c.model_id=c1.innerid
                                       left join sys_user as su on su.innerid=b.operatedid
                                       left join base_city as ct on c.cityid=ct.innerid
                                       left join base_province as pr on c.provid=pr.innerid
                                       left join (select count(1) as pricecount,auctionid from auction_participant group by auctionid) as e on e.auctionid=a.auctionid";
            const string fields = @"a.innerid,a.auctionid,a.mobile,a.amount,a.status,a.createrid,a.createdtime,a.username,
                                    a.orderno,b.no as auctionno,b.lowestprice,b.validtime,b.status as auditstatus, now() as currenttime,c.register_date,c.mileage,c.pic_url,
                                    c1.modelprice as price,c1.modelname as model_name,
                                    ct.cityname,pr.provname,e.pricecount";
            var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime asc " : query.Order;

            var sqlWhere = new StringBuilder("1=1");

            //拍卖ID
            if (!string.IsNullOrWhiteSpace(query.Auctionid))
            {
                sqlWhere.Append($" and a.auctionid='{query.Auctionid}'");
            }
            //手机号
            if (!string.IsNullOrWhiteSpace(query.Mobile))
            {
                sqlWhere.Append($" and a.mobile='{query.Mobile}'");
            }
            //用户ID
            if (!string.IsNullOrWhiteSpace(query.userid))
            {
                sqlWhere.Append($" and a.userid='{query.userid}'");
            }
            //员工编号
            if (!string.IsNullOrWhiteSpace(query.userno))
            {
                sqlWhere.Append($" and su.no='{query.userno}'");
            }
            //拍卖编号
            if (!string.IsNullOrWhiteSpace(query.auctionno))
            {
                sqlWhere.Append($" and b.no='{query.auctionno}'");
            }
            //状态
            if (query.status.HasValue)
            {
                sqlWhere.Append($" and a.status={query.status}");
            }
            //业务员
            if (!string.IsNullOrWhiteSpace(query.operatedid))
            {
                sqlWhere.Append($" and b.operatedid='{query.operatedid}'");
            }
            //里程数
            if (query.minmileage.HasValue)
            {
                sqlWhere.Append($" and c.mileage>={query.minmileage}");
            }
            //里程数
            if (query.maxmileage.HasValue)
            {
                sqlWhere.Append($" and c.mileage<{query.maxmileage}");
            }
            //上牌时间
            if (query.register_date.HasValue)
            {
                sqlWhere.Append($" and YEAR(c.register_date)=YEAR('{query.register_date}')");
            }
            //城市
            if (query.cityid != null)
            {
                sqlWhere.Append($" and c.cityid={query.cityid}");
            }

            var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<AuctionCarParticipantViewModel>(model, query.Echo);
            return list;
        }
Пример #15
0
        /// <summary>
        /// 获取拍卖车辆列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<AuctionCarInfoViewModel> GetAuctionList(AuctionCarInfoQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"auction_carinfo as a
                                    left join car_info as b on b.innerid=a.carid
                                    left join base_carbrand as c1 on b.brand_id=c1.innerid
                                    left join base_carseries as c2 on b.series_id=c2.innerid
                                    left join base_carmodel as c3 on b.model_id=c3.innerid
                                    left join base_city as ct on b.cityid=ct.innerid
                                    left join base_province as pr on b.provid=pr.innerid
                                    left join sys_user as su on su.innerid=a.operatedid";
            const string fields = "a.innerid,a.no,a.carid,a.mobile,a.lowestprice,a.status as auditstatus,b.pic_url,a.status,b.price,b.mileage,b.register_date,a.publishedtime,a.validtime,b.createdtime,c1.brandname as brand_name,c2.seriesname as series_name,c3.modelname as model_name,c3.modelprice,ct.cityname,pr.provname,su.username";
            var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime asc " : query.Order;

            var sqlWhere = new StringBuilder("1=1");

            //车商手机号
            if (!string.IsNullOrWhiteSpace(query.mobile))
            {
                sqlWhere.Append($" and a.mobile like '%{query.mobile}%'");
            }

            //省份
            if (query.provid != null)
            {
                sqlWhere.Append($" and b.provid={query.provid}");
            }

            //城市(车源地)
            if (query.cityid != null)
            {
                sqlWhere.Append($" and b.cityid={query.cityid}");
            }

            //品牌
            if (query.brand_id != null && query.brand_id != 0)
            {
                sqlWhere.Append($" and b.brand_id={query.brand_id}");
            }

            //车系
            if (query.series_id != null && query.series_id != 0)
            {
                sqlWhere.Append($" and b.series_id={query.series_id}");
            }

            //车型
            if (query.model_id != null && query.model_id != 0)
            {
                sqlWhere.Append($" and b.model_id={query.model_id}");
            }
            //里程数
            if (query.minmileage.HasValue)
            {
                sqlWhere.Append($" and b.mileage>={query.minmileage}");
            }
            //里程数
            if (query.maxmileage.HasValue)
            {
                sqlWhere.Append($" and b.mileage<{query.maxmileage}");
            }
            //上牌时间
            if (query.register_date.HasValue)
            {
                sqlWhere.Append($" and b.register_date='{query.register_date}'");
            }
            //状态
            if (query.status.HasValue)
            {
                sqlWhere.Append($" and a.status='{query.status}'");
            }
            //员工编号
            if (query.userno != null)
            {
                sqlWhere.Append($" and su.no='{query.userno}'");
            }
            //拍品编号
            if (query.auctionno != null)
            {
                sqlWhere.Append($" and a.no='{query.auctionno}'");
            }
            //业务员Id operatedid
            if (query.operatedid != null)
            {
                sqlWhere.Append($" and a.operatedid='{query.operatedid}'");
            }

            var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<AuctionCarInfoViewModel>(model, query.Echo);
            return list;
        }
Пример #16
0
 /// <summary>
 /// 获取车型列表
 /// </summary>
 /// <param name="query">查询条件</param>
 /// <returns></returns>
 public BasePageList<BaseCarModelListViewModel> GetCarModelList(BaseCarModelQueryModel query)
 {
     const string spName = "sp_common_pager";
     const string tableName = @"base_carmodel as a left join base_carseries as b on a.seriesid=b.innerid left join base_carbrand c on b.brandid=c.innerid";
     const string fields = " a.innerid,modelname,modelprice,modelyear,minregyear,maxregyear,liter,geartype,dischargestandard,seriesid,a.isenabled,ifnull(a.remark,'') remark,b.seriesname,c.brandname";
     var orderField = string.IsNullOrWhiteSpace(query.Order) ? " innerid asc" : query.Order;
     //查詢條件
     var sqlWhere = new StringBuilder(" 1=1 ");
     if (!string.IsNullOrWhiteSpace(query.Modelname))
     {
         sqlWhere.Append($" and modelname like '%{query.Modelname}%'");
     }
     if (!string.IsNullOrWhiteSpace(query.BrandId))
     {
         sqlWhere.Append($" and c.innerid = '{query.BrandId}'");
     }
     if (!string.IsNullOrWhiteSpace(query.SeriesId))
     {
         sqlWhere.Append($" and b.innerid = '{query.SeriesId}'");
     }
     var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
     var list = Helper.ExecutePaging<BaseCarModelListViewModel>(model, query.Echo);
     return list;
 }
Пример #17
0
        /// <summary>
        /// 获取活动列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<CrowdInfoListModel> GetCrowdActivityPageList(CrowdInfoQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"activity_crow_info as a ";
            const string fields = @"innerid,flagcode, title, enrollstarttime, enrollendtime, secrettime, status, type, qrcode, createrid, createdtime,
                (select count(1) from activity_crow_player where flagcode=a.flagcode and isenabled=1) as playernum,
                (select sum(totalfee) from activity_crow_payrecord where flagcode=a.flagcode and ispay=1) as upperedtotal";
            var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime asc " : query.Order;

            var sqlWhere = new StringBuilder("");

            var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CrowdInfoListModel>(model, query.Echo);
            return list;
        }
Пример #18
0
 /// <summary>
 /// 获取基础数据代码类型列表
 /// </summary>
 /// <param name="query"></param>
 /// <returns></returns>
 public BasePageList<BaseCodeTypeListModel> GetCodeTypeList(BaseCodeTypeQueryModel query)
 {
     const string spName = "sp_common_pager";
     const string tableName = @"base_code_type ";
     const string fields = "innerid,typekey,typename,isenabled";
     var oldField = string.IsNullOrWhiteSpace(query.Order) ? " innerid asc " : query.Order;
     var sqlWhere = new StringBuilder("1=1");
     if (!string.IsNullOrWhiteSpace(query.Typename))
     {
         sqlWhere.Append($" and typename like '%{query.Typename}%'");
     }
     if (!string.IsNullOrWhiteSpace(query.Typekey))
     {
         sqlWhere.Append($" and typekey = '{query.Typekey}'");
     }
     var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
     var list = Helper.ExecutePaging<BaseCodeTypeListModel>(model, query.Echo);
     return list;
 }
Пример #19
0
        /// <summary>
        /// 获取投票活动的参赛人员列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<CrowdPlayerListModel> GetPlayerPageList(CrowdPlayerQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"activity_crow_player as a ";
            const string fields = @"innerid, wechatnick, wechatheadportrait, mobile, openid, flagcode, isenabled, iswinning,
                                    (select      count(1) from activity_crow_payrecord where flagcode=a.flagcode and openid=a.openid and ispay=1) as paynum,
                                    (select sum(totalfee) from activity_crow_payrecord where flagcode=a.flagcode and openid=a.openid and ispay=1) as totalfee";
            var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime desc " : query.Order;

            var sqlWhere = new StringBuilder($" a.flagcode='{query.Flagcode}' ");

            if (!string.IsNullOrWhiteSpace(query.Wechatnick))
            {
                sqlWhere.Append($" and a.wechatnick like '%{query.Wechatnick}%'");
            }
            if (query.Iswinning == 1)
            {
                sqlWhere.Append(" and a.iswinning=1");
            }
            var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CrowdPlayerListModel>(model, query.Echo);
            return list;
        }
Пример #20
0
        /// <summary>
        /// 获取部门列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<BaseDepartmentViewModel> GetManageCityList(BaseDepartmentQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"sys_department ";
            const string fields = "`id`, `name`, `areaid`, `tel`, `email`, `sort`, `remark`, `code`";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? " sort asc " : query.Order;
            var sqlWhere = new StringBuilder("1=1");
            //菜单名称
            if (!string.IsNullOrWhiteSpace(query.name))
            {
                sqlWhere.AppendFormat(" and name like '%{0}%' ", query.name);
            }

            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<BaseDepartmentViewModel>(model, query.Echo);
            return list;
        }
Пример #21
0
        /// <summary>
        /// 获取参赛人的投票列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<VoteLogListModel> GetVoteLogList(VoteLogQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"activity_vote_log as a inner join wechat_friend as b on a.openid=b.openid ";
            const string fields = "a.openid,b.nickname,b.photo, a.createdtime, a.modifiedtime,a.invalid";
            var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime asc " : query.Order;

            var sqlWhere = new StringBuilder($" a.perid='{query.Perid}' ");

            if (!string.IsNullOrWhiteSpace(query.Activityid))
            {
                sqlWhere.Append($" and a.activityid='{query.Activityid}'");
            }

            if (!string.IsNullOrWhiteSpace(query.Openid))
            {
                sqlWhere.Append($" and a.openid='{query.Openid}'");
            }

            var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<VoteLogListModel>(model, query.Echo);
            return list;
        }
Пример #22
0
 /// <summary>
 /// 获取菜单列表
 /// </summary>
 /// <param name="query"></param>
 /// <returns></returns>
 public BasePageList<MenuViewMode> GetMenuList(MenuQueryModel query)
 {
     const string spName = "sp_common_pager";
     const string tableName = @"sys_menu ";
     const string fields = "innerid, ifnull(code,'') as code,ifnull(name,'') as name, ifnull(url,'') as url, sort, parentid, level, openmode, isenabled, ifnull(remark,'') as remark, createdtime, modifiedtime, icon, submenu";
     var orderField = string.IsNullOrWhiteSpace(query.Order) ? " level asc " : query.Order;
     //  var groupField = string.IsNullOrWhiteSpace(query.Group) ? " parentid " : query.Group; ;
     var sqlWhere = new StringBuilder("1=1");
     //菜单名称
     if (!string.IsNullOrWhiteSpace(query.name))
     {
         sqlWhere.AppendFormat(" and name like '%{0}%' ", query.name);
     }
     //层级
     if (query.level != null)
     {
         sqlWhere.AppendFormat(" and level = {0} ", query.level);
     }
     var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
     var list = Helper.ExecutePaging<MenuViewMode>(model, query.Echo);
     return list;
 }
Пример #23
0
        /// <summary>
        /// 获取会员列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public BasePageList<CustModel> GetCustPageList(CustQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"cust_info";
            const string fields = "innerid, custname, password, mobile, telephone, email, headportrait, status, authstatus, provid, cityid, area, sex, brithday, qq, totalpoints, level, qrcode, type, createdtime, modifiedtime,(select count(1) from car_info where custid=cust_info.innerid and status<>0) as carnum";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? "createdtime desc" : query.Order;
            //查询条件
            var sqlWhere = new StringBuilder("1=1");

            sqlWhere.Append(query.Status != null
                ? $" and status={query.Status}"
                : "");
            //会员ID
            //if (!string.IsNullOrWhiteSpace(query.innerid))
            //{
            //    sqlWhere.Append($" and cityid in (select cityid from sys_user_city where userid='{query.innerid}')");
            //}
            //手机号
            if (!string.IsNullOrWhiteSpace(query.Mobile))
            {
                sqlWhere.Append($" and mobile like '%{query.Mobile}%'");
            }
            //昵称
            if (!string.IsNullOrWhiteSpace(query.Custname))
            {
                sqlWhere.Append($" and custname like '%{query.Custname}%'");
            }

            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CustModel>(model, query.Echo);
            return list;
        }
Пример #24
0
 /// <summary>
 /// 获取角色列表
 /// </summary>
 /// <param name="query"></param>
 /// <returns></returns>
 public BasePageList<BaseRoleViewModel> GetRoleList(BaseRoleQueryModel query)
 {
     const string spName = "sp_common_pager";
     const string tableName = @"sys_role ";
     const string fields = "innerid, name, remark, isenabled";
     //var oldField = string.IsNullOrWhiteSpace(query.Order) ? " modifiedtime desc " : query.Order;
     var oldField = "";
     var sqlWhere = new StringBuilder("1=1");
     if (query.isenabled != null)
     {
         sqlWhere.Append($" and isenabled = %{query.isenabled}%");
     }
     var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
     var list = Helper.ExecutePaging<BaseRoleViewModel>(model, query.Echo);
     return list;
 }
Пример #25
0
        /// <summary>
        /// 获取标签列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public BasePageList<CustTagModel> GetTagPageList(CustTagQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"cust_tag";
            const string fields = "innerid, tagname, hotcount, isenabled, createdtime, modifiedtime";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? "createdtime desc" : query.Order;
            //查询条件
            var sqlWhere = new StringBuilder("1=1");

            sqlWhere.Append(query.Isenabled != null
                ? $" and status={query.Isenabled}"
                : "");

            if (!string.IsNullOrWhiteSpace(query.Tagname))
            {
                sqlWhere.Append($" and tagname like '%{query.Tagname}%'");
            }

            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CustTagModel>(model, query.Echo);
            return list;
        }
Пример #26
0
 /// <summary>
 /// 获取用户列表
 /// </summary>
 /// <param name="query"></param>
 /// <returns></returns>
 public BasePageList<BaseUserModel> GetUserList(BaseUserQueryModel query)
 {
     const string spName = "sp_common_pager";
     const string tableName = @"sys_user ";
     const string fields = "innerid, username, loginname, password, mobile, telephone, email, status, createdtime, modifiedtime";
     var oldField = string.IsNullOrWhiteSpace(query.Order) ? " modifiedtime desc " : query.Order;
     var sqlWhere = new StringBuilder("1=1");
     if (query.status != null)
     {
         sqlWhere.Append($" and status = %{query.status}%");
     }
     var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
     var list = Helper.ExecutePaging<BaseUserModel>(model, query.Echo);
     return list;
 }
Пример #27
0
        /// <summary>
        /// C用户 获取会员列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public BasePageList<UserListModel> GetUserPageList(UserQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"user_info";
            const string fields = "innerid, nickname, mobile, email, headportrait, status, sex, brithday, qq, createdtime";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? "createdtime desc" : query.Order;
            //查询条件
            var sqlWhere = new StringBuilder("1=1");

            sqlWhere.Append(query.Status != null
                ? $" and status={query.Status}"
                : "");

            //手机号
            if (!string.IsNullOrWhiteSpace(query.Mobile))
            {
                sqlWhere.Append($" and mobile like '%{query.Mobile}%'");
            }
            //昵称
            if (!string.IsNullOrWhiteSpace(query.Nickname))
            {
                sqlWhere.Append($" and nickname like '%{query.Nickname}%'");
            }

            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<UserListModel>(model, query.Echo);
            return list;
        }
Пример #28
0
        /// <summary>
        /// 获取广告列表--分页
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<BaseBannerPageListModel> GetBannerPageList(BaseBannerQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @"base_banner ";
            const string fields = "innerid, title, picurl, linkurl, autoenabletime, autodisabletime, sort, isenabled, createrid, createdtime, modifierid, modifiedtime";
            var oldField = string.IsNullOrWhiteSpace(query.Order) ? " sort asc " : query.Order;
            var sqlWhere = new StringBuilder("1=1");
            if (!string.IsNullOrWhiteSpace(query.Title))
            {
                sqlWhere.Append($" and title like '%{query.Title}%'");
            }

            if (query.Isenabled != null)
            {
                sqlWhere.Append($" and isenabled = '{query.Isenabled}'");
            }
            var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<BaseBannerPageListModel>(model, query.Echo);
            return list;
        }
Пример #29
0
        /// <summary>
        /// 公司列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public BasePageList<CompanyListModel> GetCompanyPageList(CompanyQueryModel query)
        {
            const string spName = "sp_common_pager";
            const string tableName = @" settled_info as a  left join (select settid,count(1) as setttotal,createdtime from settled_info_applyupdate where status=2 group by settid) as b on b.settid=a.innerid";
            const string fields = @"innerid, companyname, address, opername, originalregistcapi, companystatus, ifnull(officephone,'') as officephone, picurl, companytitle, ancestryids, categoryids, customdesc, boutiqueurl, spare1, spare2, createrid, a.createdtime, modifierid, modifiedtime,b.setttotal,
            (select count(innerid) from settled_praiselog where companyid=a.innerid) as PraiseNum,
            (select count(innerid) from settled_comment where companyid=a.innerid) as CommentNum,
            (select avg(score) from settled_comment where companyid=a.innerid) as ScoreNum,
            (select group_concat(codename) from base_code where typekey='car_ancestry' and FIND_IN_SET(codevalue,a.ancestryids)) as ancestryname,
            (select group_concat(codename) from base_code where typekey='car_category' and FIND_IN_SET(codevalue,a.categoryids)) as categoryname";
            var orderField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime,b.setttotal desc,b.createdtime asc " : query.Order;
            //查詢條件
            var sqlWhere = new StringBuilder(" 1=1 ");
            if (!string.IsNullOrWhiteSpace(query.CompanyName))
            {
                sqlWhere.Append($" and companyname like '%{query.CompanyName}%'");
            }
            if (!string.IsNullOrWhiteSpace(query.Address))
            {
                sqlWhere.Append($" and address like '%{query.Address}%'");
            }

            if (!string.IsNullOrWhiteSpace(query.City))
            {
                sqlWhere.Append($" and (address like '%{query.City}%' or companyname like '%{query.City}%')");
            }

            if (!string.IsNullOrWhiteSpace(query.County))
            {
                sqlWhere.Append($" and address like '%{query.County}%'");
            }

            if (!string.IsNullOrWhiteSpace(query.Ancestryids))
            {
                string str = "";
                foreach (var item in query.Ancestryids.Split(','))
                {
                    str += " locate('" + item + "',ancestryids)>0 or";
                }
                str = str.Substring(0, str.Length - 2);
                sqlWhere.Append(" and (" + str + ")");
            }

            if (!string.IsNullOrWhiteSpace(query.Categoryids))
            {
                string str = "";
                foreach (var item in query.Categoryids.Split(','))
                {
                    str += " locate('" + item + "',categoryids)>0 or";
                }
                str = str.Substring(0, str.Length - 2);
                sqlWhere.Append(" and (" + str + ")");
            }

            var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
            var list = Helper.ExecutePaging<CompanyListModel>(model, query.Echo);
            return list;
        }
Пример #30
0
 /// <summary>
 /// 获取品牌列表
 /// </summary>
 /// <param name="query">查询条件</param>
 /// <returns></returns>
 public BasePageList<BaseCarBrandListViewModel> GetCarBrandList(BaseCarBrandQueryModel query)
 {
     const string spName = "sp_common_pager";
     const string tableName = @"base_carbrand ";
     const string fields = " innerid,brandname,initial,isenabled,ifnull(remark,'') remark,ifnull(logurl,'') logurl,hot ";
     var oldField = string.IsNullOrWhiteSpace(query.Order) ? " initial asc " : query.Order;
     var sqlWhere = new StringBuilder("1=1");
     if (!string.IsNullOrWhiteSpace(query.BrandName))
     {
         sqlWhere.Append($" and brandname like '%{query.BrandName}%'");
     }
     var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
     var list = Helper.ExecutePaging<BaseCarBrandListViewModel>(model, query.Echo);
     return list;
 }