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