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