public List <bx_userinfo> GetMyBjdList(bool hasOutOrder, string strWhere, int pageSize, int curPage, out int totalCount)
{
totalCount = 0;
var listuserinfo = new List <bx_userinfo>();
try
{
MySqlParameter[] parameters =
{
new MySqlParameter("@pagebegin", MySqlDbType.Int32),
new MySqlParameter("@pageend", MySqlDbType.Int32)
};
parameters[0].Value = (curPage - 1) * pageSize;
parameters[1].Value = pageSize;
//列表
var sbSqlList = new StringBuilder();
sbSqlList.Append(" SELECT ui.* FROM bx_userinfo AS ui");
sbSqlList.Append(" LEFT JOIN (SELECT buid,order_status,MAX(create_time) AS createdate FROM bx_car_order");
if (hasOutOrder)
{//是否已收单
sbSqlList.Append(" WHERE order_status =-3");
}
else
{
sbSqlList.Append(" GROUP BY buid,order_status,create_time limit 1) co ON co.buid=ui.id");
}
sbSqlList.Append(" WHERE QuoteStatus > -1 ");
if (!hasOutOrder)
{//是否已收单
sbSqlList.Append(" AND (co.order_status!=-3 OR co.order_status IS NULL)");
}
else
{
sbSqlList.Append(" AND co.order_status IS NOT NULL");
}
sbSqlList.Append(strWhere)
.Append(" ORDER BY ui.UpdateTime DESC limit @pagebegin,@pageend ");
//数量
var sbSqlCount = new StringBuilder();
sbSqlCount.Append(" SELECT COUNT(1) FROM bx_userinfo AS ui")
.Append(" LEFT JOIN (SELECT buid,order_status,MAX(create_time) AS createdate FROM bx_car_order GROUP BY buid,order_status,create_time limit 1) co ON co.buid=ui.id")
.Append(" WHERE QuoteStatus > -1 ")
.Append(" AND (co.order_status!=-3 OR co.order_status IS NULL)")
.Append(strWhere);
//查询总条数
totalCount = DataContextFactory.GetDataContext().Database.SqlQuery <int>(sbSqlCount.ToString(), Enumerable.ToArray(parameters)).FirstOrDefault();
//查询列表
listuserinfo = DataContextFactory.GetDataContext().Database.SqlQuery <bx_userinfo>(sbSqlList.ToString(), Enumerable.ToArray(parameters)).ToList();
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(listuserinfo);
}
/// <summary>
/// 根据buid获取活动信息
/// </summary>
/// <param name="buid"></param>
/// <returns></returns>
public List <bx_preferential_activity> GetActivityByBuid(long buid)
{
var result = from pa in DataContextFactory.GetDataContext().bx_preferential_activity
where pa.activity_status == 1 &&
(from ba in DataContextFactory.GetDataContext().bx_buid_activity
where ba.B_Uid == buid
select ba.activity_id).Contains(pa.id)
select pa;
return(result.ToList());
}
public int Update(long agentId)
{
//bx_agent agent=new bx_agent();
//agent = GetAgent(agentId);
//agent.ParentShareCode = (agentId + 1000).ToString();
//DataContextFactory.GetDataContext().bx_agent.AddOrUpdate(agent);
long ParentShareCode = agentId + 1000;
int intUpdate = DataContextFactory.GetDataContext().Database.ExecuteSqlCommand("UPDATE bx_agent set ShareCode={0} WHERE Id={1}", ParentShareCode, agentId);
//DataContextFactory.GetDataContext().SaveChanges();
return(intUpdate);
}
/// <summary>
/// 根据AgentId获取顶级代理
/// </summary>
/// <param name="currentAgent"></param>
/// <returns></returns>
public string GetTopAgentId(int currentAgent)
{
MySqlParameter[] parms = new MySqlParameter[1];
var parentAgentid = new MySqlParameter("parentAgentid", currentAgent);
parentAgentid.MySqlDbType = MySqlDbType.Int32;
parentAgentid.Size = 128;
parms[0] = parentAgentid;
var ts = DataContextFactory.GetDataContext().Database.SqlQuery <string>("select `getAgentTopParent`(@parentAgentid)", parms).FirstOrDefault();
return(ts);
}
//类型,1=验车照片,2=在京使用证明,-1=已删除
public List <bx_images> FindByBuid(long buid)
{
try
{
return(DataContextFactory.GetDataContext().bx_images.Where(x => x.buid == buid && x.type >= 0).ToList());
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(null);
}
public List <bx_agent_rate> GetAgentRates(List <int> agents, int source)
{
if (agents.Count == 0)
{
return(null);
}
var result =
DataContextFactory.GetDataContext().bx_agent_rate.Where(x => agents.Contains(x.agent_id) && x.company_id == source)
.ToList();
return(result);
}
public IList <bx_hebaodianwei> FindList(long buid, long[] sources)
{
List <int> v = new List <int>()
{
1
};
var all = DataContextFactory.GetDataContext().bx_hebaodianwei.Where(x => x.buid == buid).ToList();
var list = all.Where(x => x.source == null ? false : sources.Contains((long)x.source)).ToList();
return(list);
//return DataContextFactory.GetDataContext().bx_hebaodianwei.Where(x => x.buid == buid && x.source == null ? false : sources.Contains((long)x.source)).ToList();
}
public async Task <List <bx_preferential_activity> > GetActivityByIdsAsync(string stringId)
{
string[] strArray = stringId.Split(',');
if (strArray.Length > 0)
{
int[] intArray = Array.ConvertAll <string, int>(strArray, int.Parse);
var result = DataContextFactory.GetDataContext().bx_preferential_activity.Where(
i => intArray.Contains(i.id) && i.activity_status == 1)
.ToList().OrderByDescending(o => o.create_time);
return(result.ToList());
}
return(new List <bx_preferential_activity>());
}
/// <summary>
/// 根据Buid修改批量续保子表状态
/// </summary>
/// <param name="buId">userInfoId</param>
/// <param name="itemStatus">状态</param>
/// <returns>修改影响行数</returns>
public int UpdateItemStatus(long buId, int itemStatus)
{
try
{
var sql = string.Format("UPDATE bx_batchrenewal_item SET ItemStatus={1},HistoryItemStatus={1},UpdateTime=NOW() WHERE BUId ={0} AND IsDelete=0 ", buId, itemStatus);;
return(DataContextFactory.GetDataContext().Database.ExecuteSqlCommand(sql));
}
catch (Exception ex)
{
logError.Error("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(0);
}
public bx_config Find(string configKey, int configType)
{
try
{
bx_config model = DataContextFactory.GetDataContext().bx_config.FirstOrDefault(i => i.config_key.Equals(configKey) && i.config_type == configType && i.is_delete == 0);
return(model);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(null);
}
public List <bx_config> FindList(int configType)
{
try
{
List <bx_config> list = DataContextFactory.GetDataContext().bx_config.Where(i => i.config_type == configType && i.is_delete == 0).ToList();
return(list);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(null);
}
public string GetAgentName(int agentId)
{
string agentName = string.Empty;
try
{
agentName = DataContextFactory.GetDataContext().bx_agent.Where(l => l.Id == agentId).Select(a => a.AgentName).FirstOrDefault();
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(agentName);
}
public bx_carinfo FindVinCarInfo(string carVin, int renewaltype = 0)
{
bx_carinfo carinfo = new bx_carinfo();
try
{
carinfo = DataContextFactory.GetDataContext().bx_carinfo.FirstOrDefault(x => x.vin_no == carVin && x.RenewalCarType == renewaltype);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(carinfo);
}
public List <bx_agent_distributed> FindByParentAgent(int parentAgentId)
{
var item = new List <bx_agent_distributed>();
try
{
item = DataContextFactory.GetDataContext().bx_agent_distributed.Where(x => x.ParentAgentId == parentAgentId && x.AgentType == 1 && x.Deteled == false).OrderByDescending(o => o.Id).ToList();
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(item);
}
/// <summary>
/// 根据上一级代理人Id获取代理人
/// </summary>
/// <param name="openid"></param>
/// <param name="parentAgent"></param>
/// <returns></returns>
public bx_agent GetAgentByParentAgent(string openid, int parentAgent)
{
var item = new bx_agent();
try
{
item = DataContextFactory.GetDataContext().bx_agent.FirstOrDefault(x => x.OpenId == openid && x.ParentAgent == parentAgent);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(item);
}
/// <summary>
/// 根据mobile获取所有的代理
/// </summary>
/// <param name="openId"></param>
/// <returns></returns>
public List <bx_agent> GetAllAgentByPhone(string mobile)
{
var item = new List <bx_agent>();
try
{
item = DataContextFactory.GetDataContext().bx_agent.Where(x => x.Mobile == mobile).ToList();
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(item);
}
public List <bx_area> FindByPid(int pid)
{
List <bx_area> bxAreas = new List <bx_area>();
try
{
bxAreas = DataContextFactory.GetDataContext().bx_area.Where(x => x.Pid == pid).ToList();
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(bxAreas);
}
public async Task <List <bx_claim_detail> > FindListAsync(long buid)
{
List <bx_claim_detail> list = new List <bx_claim_detail>();
try
{
list = DataContextFactory.GetDataContext().bx_claim_detail.Where(x => x.b_uid == buid).ToList();
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(list);
}
public long Add(bx_order_quoteresult quoteresult)
{
bx_order_quoteresult item = new bx_order_quoteresult();
try
{
item = DataContextFactory.GetDataContext().bx_order_quoteresult.Add(quoteresult);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(item.Id);
}
/// <summary>
/// 续保计费系统的续保列表
/// 只查续保
/// </summary>
/// <param name="strWhere">查询条件</param>
/// <param name="isAgent"></param>
/// <param name="sonself"></param>
/// <param name="strPass"></param>
/// <param name="licenseNo"></param>
/// <param name="renewalStatus"></param>
/// <param name="pageSize"></param>
/// <param name="curPage"></param>
/// <param name="totalCount"></param>
/// <param name="lastYearSource"></param>
/// <returns></returns>
public List <bx_userinfo> FindReList(string strWhere, int pageSize, int curPage, out int totalCount)
{
totalCount = 0;
var listuserinfo = new List <bx_userinfo>();
try
{
MySqlParameter[] parameters =
{
new MySqlParameter("@pagebegin", MySqlDbType.Int32),
new MySqlParameter("@pageend", MySqlDbType.Int32)
};
parameters[0].Value = (curPage - 1) * pageSize;
parameters[1].Value = pageSize;
//列表
string strSqlList = string.Format("SELECT * FROM bx_userinfo WHERE {0} ORDER BY UpdateTime DESC limit @pagebegin,@pageend", strWhere);
//数量
string strSqlCount = string.Format("SELECT COUNT(1) FROM bx_userinfo WHERE {0}", strWhere);
//查询总条数
totalCount = DataContextFactory.GetDataContext().Database.SqlQuery <int>(strSqlCount).FirstOrDefault();
//查询列表
listuserinfo = DataContextFactory.GetDataContext().Database.SqlQuery <bx_userinfo>(strSqlList, Enumerable.ToArray(parameters)).ToList();
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(listuserinfo);
//try
//{
// //查询我的报价单列表，搜索bx_car_order中未出单的记录
// var list = from ui in context.bx_userinfo
// where (isAgent ? listStr.Contains(ui.Agent) : ui.OpenId.Equals(strPass))
// && (!lastYearSource.HasValue || ui.LastYearSource == lastYearSource.Value)
// && (!renewalStatus.HasValue || (renewalStatus.Value == 1 ?
// ((ui.NeedEngineNo == 0 && ui.RenewalStatus != 1) || (ui.NeedEngineNo == 0 && ui.LastYearSource > -1) || ui.RenewalStatus == 1) : ui.NeedEngineNo == 1))
// && ui.QuoteStatus == -1
// && ui.OpenId.Length > 9
// && (string.IsNullOrEmpty(licenseNo) || (ui.LicenseNo.Contains(licenseNo.ToUpper()) || ui.LicenseOwner.Contains(licenseNo.ToUpper())))
// select ui;
// listuserinfo = list.ToList();
// totalCount = listuserinfo.Count;
//}
//catch (Exception ex)
//{
// logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
//}
//return listuserinfo.OrderByDescending(i => (i.UpdateTime.HasValue ? i.UpdateTime : i.CreateTime)).ThenByDescending(i => i.CreateTime).Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
}
public bx_userinfo FindByBuid(long buid)
{
bx_userinfo userinfo = new bx_userinfo();
try
{
userinfo = DataContextFactory.GetDataContext().bx_userinfo.AsNoTracking().FirstOrDefault(x => x.Id == buid);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(userinfo);
}
public bx_userinfo Find(int userId)
{
bx_userinfo userinfo = new bx_userinfo();
try
{
userinfo = DataContextFactory.GetDataContext().bx_userinfo.First(x => x.UserId == userId);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(userinfo);
}
/// <summary>
/// app端用查询userinfo，由于无openid限制，故3个条件不用openid查
/// 顶级代理下的车辆有可能重复，以最新的一条记录来处理，即使查看老数据，也展示最新的一条数据
/// </summary>
/// <param name="licenseno"></param>
/// <param name="agent"></param>
/// <returns></returns>
public bx_userinfo FindByAgentLicense(string licenseno, string agent)
{
bx_userinfo tt = new bx_userinfo();
try
{
tt = DataContextFactory.GetDataContext().bx_userinfo.OrderByDescending(o => o.UpdateTime.HasValue ? o.UpdateTime : o.CreateTime).ThenByDescending(o => o.CreateTime).FirstOrDefault(x => x.LicenseNo == licenseno && x.Agent == agent);// && x.OpenId.Length > 9// && (!x.IsSingleSubmit.HasValue)
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(tt);
}
public List <bx_para_bhtype> GetListByTypeId(int typeId, int isAll)
{
var list = new List <bx_para_bhtype>();
try
{
list = DataContextFactory.GetDataContext().bx_para_bhtype.Where(x => x.bh_type == typeId && (isAll == 1 ? true : x.is_support == 1)).ToList();
}
catch (Exception ex)
{
_logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(list);
}
public List <bx_agent> GetAgentExpireList(int status)
{
try
{
string strSql = string.Format("SELECT * FROM bx_agent WHERE parentagent=0 AND isused={0} AND enddate BETWEEN '{1}' AND '{2}'", status, DateTime.Now.ToString("yyyy-MM-dd"), DateTime.Now.AddDays(7).ToString("yyyy-MM-dd"));
List <bx_agent> listAgents = DataContextFactory.GetDataContext().Database.SqlQuery <bx_agent>(strSql.ToString()).ToList();
return(listAgents);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(new List <bx_agent>());
}
public bx_order_quoteresult GetQuoteResultByBuid(long buid, int source)
{
bx_order_quoteresult quoteresult = new bx_order_quoteresult();
try
{
quoteresult = DataContextFactory.GetDataContext().bx_order_quoteresult.FirstOrDefault(x => x.B_Uid == buid && x.Source == source);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(quoteresult);
}
//对外公开
public bx_agent GetAgent(int agentId)
{
var item = new bx_agent();
try
{
item = DataContextFactory.GetDataContext().bx_agent.FirstOrDefault(x => x.Id == agentId);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(item);
}
public List <int> GetAgentIds(string openId)
{
var item = new List <int>();
try
{
item = DataContextFactory.GetDataContext().bx_agent.Where(x => x.OpenId == openId).Select(s => s.Id).ToList();
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(item);
}
public bx_quotehistory_related GetModel(long buid, long groupspan)
{
var item = new bx_quotehistory_related();
try
{
item = DataContextFactory.GetDataContext().bx_quotehistory_related.FirstOrDefault(x => x.b_uid == buid && x.groupspan == groupspan);
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(item);
}
public List <bx_gsc_renewal> FindListByBuid(long buid)
{
var bxGscRenewal = new List <bx_gsc_renewal>();
try
{
bxGscRenewal = DataContextFactory.GetDataContext().bx_gsc_renewal.Where(x => x.B_uid == buid).ToList();
}
catch (Exception ex)
{
logError.Info("发生异常:" + ex.Source + "\n" + ex.StackTrace + "\n" + ex.Message + "\n" + ex.InnerException);
}
return(bxGscRenewal);
}