public static List <IdName> GetSales(Context db, int projectid)
{
List <IdName> list = new List <IdName>();
var query =
db.Database.SqlQuery <IdName>(
@"select u.id,u.name from systemusers u join roleusers ru on ru.userid=u.id
join rolefunctions rf on rf.roleid=ru.roleid join functions f on f.id=rf.functionid where f.ParentName='客户管理' and f.name='销售员'")
.ToList();
var projectUsers = DepartmentBLL.GetDepartmentUsersIdNames(projectid);
foreach (var idName in query)
{
foreach (var projectUser in projectUsers)
{
if (projectUser.Id == idName.Id)
{
list.Add(idName);
}
}
}
return(list);
}
public SearchListResult <JMSLXR> SearchList(JMSLXRReq req)
{
var query = from source in db.JMSLXR select source;
if (req.JmsID != null)
{
query = query.Where(d => d.JmsID == req.JmsID);
}
if (!string.IsNullOrEmpty(req.JmsName))
{
query = query.Where(d => d.JmsName.Contains(req.JmsName));
}
if (!string.IsNullOrEmpty(req.LxrName))
{
query = query.Where(d => d.LxrName.Contains(req.LxrName));
}
if (!string.IsNullOrEmpty(req.LxrSex))
{
query = query.Where(d => d.LxrSex.Contains(req.LxrSex));
}
if (!string.IsNullOrEmpty(req.LxrPhone))
{
query = query.Where(d => d.LxrPhone.Contains(req.LxrPhone));
}
if (!string.IsNullOrEmpty(req.LxrWeiXin))
{
query = query.Where(d => d.LxrWeiXin.Contains(req.LxrWeiXin));
}
if (!string.IsNullOrEmpty(req.LxrQQ))
{
query = query.Where(d => d.LxrQQ.Contains(req.LxrQQ));
}
if (!string.IsNullOrEmpty(req.LxrMail))
{
query = query.Where(d => d.LxrMail.Contains(req.LxrMail));
}
if (!string.IsNullOrEmpty(req.LxrZhiWu))
{
query = query.Where(d => d.LxrZhiWu.Contains(req.LxrZhiWu));
}
if (req.LxrStateID != null)
{
query = query.Where(d => d.LxrStateID == req.LxrStateID);
}
if (!string.IsNullOrEmpty(req.optName))
{
query = query.Where(d => d.optName.Contains(req.optName));
}
if (req.optDateTimeStart != DateTime.MinValue && req.optDateTimeStart != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.optDateTime >= req.optDateTimeStart);
}
if (req.optDateTimeEnd != DateTime.MinValue && req.optDateTimeEnd != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.optDateTime >= req.optDateTimeEnd);
}
#region 数据权限
if (req.optId != null && req.optId.Value != 0)
{
//组长能看到所有，
List <int> subUserIds = DepartmentBLL.GetSubSystemUsers(req.optId.Value);
query = query.Where(d => d.optId == null || subUserIds.Contains(d.optId.Value));
}
#endregion
if (string.IsNullOrEmpty(req.sidx))
{
req.sidx = "id";
}
SearchListResult <JMSLXR> retListResult = query.ToSearchList(req);
return(retListResult);
}
public SearchListResult <JMSGengZong> SearchList(JMSGengZongReq req, int?UserId = 0)
{
var query = from source in db.JMSGengZong select source;
//join jms in db.JiaMengShangInfo on source.JmsID equals jms.id
// into tempgz from jms in tempgz.DefaultIfEmpty()
if (req.JmsID != null)
{
query = query.Where(d => d.JmsID == req.JmsID);
}
if (!string.IsNullOrEmpty(req.JmsName))
{
query = query.Where(d => d.JmsName.Contains(req.JmsName));
}
if (req.LxrID != null)
{
query = query.Where(d => d.LxrID == req.LxrID);
}
if (!string.IsNullOrEmpty(req.LxrName))
{
query = query.Where(d => d.LxrName.Contains(req.LxrName));
}
if (!string.IsNullOrEmpty(req.GenzongInfo))
{
query = query.Where(d => d.GenzongInfo.Contains(req.GenzongInfo));
}
if (req.GengzongDateTimeStart != DateTime.MinValue &&
req.GengzongDateTimeStart != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.GengzongDateTime >= req.GengzongDateTimeStart);
}
if (req.GengzongDateTimeEnd != DateTime.MinValue && req.GengzongDateTimeEnd != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.GengzongDateTime >= req.GengzongDateTimeEnd);
}
if (req.GenzongStateID != null)
{
query = query.Where(d => d.GenzongStateID == req.GenzongStateID);
}
if (!string.IsNullOrEmpty(req.optName))
{
query = query.Where(d => d.optName.Contains(req.optName));
}
if (req.optDateTimeStart != DateTime.MinValue && req.optDateTimeStart != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.optDateTime >= req.optDateTimeStart);
}
if (req.optDateTimeEnd != DateTime.MinValue && req.optDateTimeEnd != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.optDateTime >= req.optDateTimeEnd);
}
//数据权限
if (UserId != null && UserId.Value != 0)
{
//组长能看到所有，
List <int> subUserIds = DepartmentBLL.GetSubSystemUsers(UserId.Value);
query = query.Where(d => d.optId == null || subUserIds.Contains(d.optId.Value));
}
if (string.IsNullOrEmpty(req.sidx))
{
req.sord = "desc";
req.sidx = "id";
}
SearchListResult <JMSGengZong> retListResult = query.ToSearchList(req);
return(retListResult);
}
/// <summary>
/// 查询JMSShouKuan
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public SearchListResult <ShouKuanInfo> SearchList(JMSShouKuanReq req)
{
var query = from source in db.JMSShouKuan
join rule in db.JMSShouKuanRule on source.JmsShouKuanRuleId equals rule.id
select new ShouKuanInfo {
JMSShouKuan = source, JMSShouKuanRule = rule
};
if (!string.IsNullOrEmpty(req.JmsIds))
{
List <int> intJids = req.JmsIds.Split(","[0]).Select(d => Convert.ToInt32(d)).ToList();
query = from source in db.JMSShouKuan join rule in db.JMSShouKuanRule on source.JmsShouKuanRuleId equals rule.id where intJids.Contains(rule.JmsId.Value) select new ShouKuanInfo {
JMSShouKuan = source, JMSShouKuanRule = rule
};
}
if (req.JmsShouKuanRuleId != null)
{
query = query.Where(d => d.JMSShouKuan.JmsShouKuanRuleId == req.JmsShouKuanRuleId);
}
if (!string.IsNullOrEmpty(req.ShouKuanType))
{
List <string> shoukuanTypes = req.ShouKuanType.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList();
query = query.Where(d => shoukuanTypes.Contains(d.JMSShouKuan.ShouKuanType));
}
if (req.ShouKuanMoney != null)
{
query = query.Where(d => d.JMSShouKuan.ShouKuanMoney == req.ShouKuanMoney);
}
if (req.ShouKuanTimeStart != DateTime.MinValue && req.ShouKuanTimeStart != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.JMSShouKuan.ShouKuanTime >= req.ShouKuanTimeStart);
}
if (req.ShouKuanTimeEnd != DateTime.MinValue && req.ShouKuanTimeEnd != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.JMSShouKuan.ShouKuanTime >= req.ShouKuanTimeEnd);
}
if (!string.IsNullOrEmpty(req.ShouKuanState))
{
query = query.Where(d => d.JMSShouKuan.ShouKuanState.Contains(req.ShouKuanState));
}
if (req.ShouKuanConfirmerId != null)
{
query = query.Where(d => d.JMSShouKuan.ShouKuanConfirmerId == req.ShouKuanConfirmerId);
}
if (!string.IsNullOrEmpty(req.ShouKuanConfirmerName))
{
query = query.Where(d => d.JMSShouKuan.ShouKuanConfirmerName.Contains(req.ShouKuanConfirmerName));
}
if (req.ShouKuanConfirmDateStart != DateTime.MinValue &&
req.ShouKuanConfirmDateStart != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.JMSShouKuan.ShouKuanConfirmDate >= req.ShouKuanConfirmDateStart);
}
if (req.ShouKuanConfirmDateEnd != DateTime.MinValue &&
req.ShouKuanConfirmDateEnd != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.JMSShouKuan.ShouKuanConfirmDate >= req.ShouKuanConfirmDateEnd);
}
if (!string.IsNullOrEmpty(req.OptName))
{
query = query.Where(d => d.JMSShouKuan.OptName.Contains(req.OptName));
}
if (req.CreatedateStart != DateTime.MinValue && req.CreatedateStart != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.JMSShouKuan.Createdate >= req.CreatedateStart);
}
if (req.CreatedateEnd != DateTime.MinValue && req.CreatedateEnd != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.JMSShouKuan.Createdate >= req.CreatedateEnd);
}
#region 数据权限
if (req.OptId != null && req.OptId.Value != 0)
{
//组长能看到所有，
List <int> subUserIds = DepartmentBLL.GetSubSystemUsers(req.OptId.Value);
query = query.Where(d => d.JMSShouKuan.OptId == null || subUserIds.Contains(d.JMSShouKuan.OptId.Value));
}
#endregion
if (string.IsNullOrEmpty(req.sidx))
{
req.sidx = "JMSShouKuan";
req.sord = "desc";
}
SearchListResult <ShouKuanInfo> retListResult = query.ToSearchShouKuanInfoList(req);
return(retListResult);
}
/// <summary>
///
/// </summary>
/// <param name="req"></param>
/// <param name="isSign">是否签约加盟商</param>
/// <returns></returns>
public SearchListResult <JiaMengShangInfo> SearchList(JiaMengShangInfoReq req, int?UserId = 0, bool?isSign = null)
{
#if DEBUG
db.Database.Log = (log) => { System.Diagnostics.Debug.WriteLine(log); };
#endif
var query = from source in db.JiaMengShangInfo select source;
if (req.id != 0)
{
query = query.Where(d => d.id.Equals(req.id));
}
if (req.JieDianTbStateID != null)
{
query = query.Where(d => d.JieDianTbStateID == req.JieDianTbStateID.Value);
}
if (!string.IsNullOrEmpty(req.JmsName))
{
query = query.Where(d => d.JmsName.Contains(req.JmsName));
}
if (!string.IsNullOrEmpty(req.JmsPhone))
{
query = query.Where(d => d.JmsPhone.Contains(req.JmsPhone));
}
if (req.JmsStateID != null)
{
query = query.Where(d => d.JmsStateID == req.JmsStateID);
}
else
{
query = query.Where(d => d.JmsStateID != 3);
}
if (!string.IsNullOrEmpty(req.JmsQuDaoLaiYuan))
{
query = query.Where(d => d.JmsQuDaoLaiYuan.Contains(req.JmsQuDaoLaiYuan));
}
if (!string.IsNullOrEmpty(req.JmsWeiXinHao))
{
query = query.Where(d => d.JmsWeiXinHao.Contains(req.JmsWeiXinHao));
}
if (!string.IsNullOrEmpty(req.JmsMail))
{
query = query.Where(d => d.JmsMail.Contains(req.JmsMail));
}
if (!string.IsNullOrEmpty(req.JmsCity))
{
query = query.Where(d => d.JmsCity.Contains(req.JmsCity));
}
if (!string.IsNullOrEmpty(req.JmsAddress))
{
query = query.Where(d => d.JmsAddress.Contains(req.JmsAddress));
}
if (!string.IsNullOrEmpty(req.JmsConShiHangYe))
{
query = query.Where(d => d.JmsConShiHangYe.Contains(req.JmsConShiHangYe));
}
if (!string.IsNullOrEmpty(req.JmsGuDongGouCheng))
{
query = query.Where(d => d.JmsGuDongGouCheng.Contains(req.JmsGuDongGouCheng));
}
if (!string.IsNullOrEmpty(req.JmsYiXiang))
{
query = query.Where(d => d.JmsYiXiang.Contains(req.JmsYiXiang));
}
if (!string.IsNullOrEmpty(req.JmsHasWuYe))
{
query = query.Where(d => d.JmsHasWuYe.Contains(req.JmsHasWuYe));
}
if (!string.IsNullOrEmpty(req.JmsWuYeClass))
{
query = query.Where(d => d.JmsWuYeClass.Contains(req.JmsWuYeClass));
}
if (!string.IsNullOrEmpty(req.JmsWuYeQuYu))
{
query = query.Where(d => d.JmsWuYeQuYu.Contains(req.JmsWuYeQuYu));
}
if (!string.IsNullOrEmpty(req.JmsZiJinYuSuan))
{
query = query.Where(d => d.JmsZiJinYuSuan.Contains(req.JmsZiJinYuSuan));
}
if (!string.IsNullOrEmpty(req.JmsHeZuoModel))
{
query = query.Where(d => d.JmsHeZuoModel.Contains(req.JmsHeZuoModel));
}
if (!string.IsNullOrEmpty(req.JmsXiaoFeiLi))
{
query = query.Where(d => d.JmsXiaoFeiLi.Contains(req.JmsXiaoFeiLi));
}
if (!string.IsNullOrEmpty(req.JmsYZHSShuLiang))
{
query = query.Where(d => d.JmsYZHSShuLiang.Contains(req.JmsYZHSShuLiang));
}
if (!string.IsNullOrEmpty(req.JmsYZHSJunJia))
{
query = query.Where(d => d.JmsYZHSJunJia.Contains(req.JmsYZHSJunJia));
}
if (!string.IsNullOrEmpty(req.JmsYongYouZiYuan))
{
query = query.Where(d => d.JmsYongYouZiYuan.Contains(req.JmsYongYouZiYuan));
}
if (!string.IsNullOrEmpty(req.JmsVisitedXiXi))
{
query = query.Where(d => d.JmsVisitedXiXi.Contains(req.JmsVisitedXiXi));
}
if (!string.IsNullOrEmpty(req.optName))
{
query = query.Where(d => d.optName.Contains(req.optName));
}
if (req.optDateTimeStart != null && req.optDateTimeStart != DateTime.MinValue && req.optDateTimeStart != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.optDateTime >= req.optDateTimeStart);
}
if (req.optDateTimeEnd != null && req.optDateTimeEnd != DateTime.MinValue && req.optDateTimeEnd != SqlDateTime.MinValue.Value)
{
query = query.Where(d => d.optDateTime <= req.optDateTimeEnd);
}
//if (!string.IsNullOrEmpty(req.JmsCity))
// query = query.Where(d => d.JmsVisitedXiXi.Contains(req.JmsCity));
//if (!string.IsNullOrEmpty(req.JmsProvince))
// query = query.Where(d => d.JmsVisitedXiXi.Contains(req.JmsProvince));
if (!string.IsNullOrEmpty(req.JmsClassName))
{
query = query.Where(d => d.JmsClassName.Contains(req.JmsClassName));
}
if (!string.IsNullOrEmpty(req.JmsHasVisitOther))
{
query = query.Where(d => d.JmsHasVisitOther.Contains(req.JmsHasVisitOther));
}
//数据权限
if (UserId != null && UserId.Value != 0)
{
//组长能看到所有，
List <int> subUserIds = DepartmentBLL.GetSubSystemUsers(UserId.Value);
query = query.Where(d => d.SaleId == null || subUserIds.Contains(d.SaleId.Value));
}
if (!string.IsNullOrEmpty(req.SaleName))
{
query = query.Where(d => d.SaleName.Contains(req.SaleName));
}
if (req.SaleId != null)
{
query = query.Where(d => d.SaleId == req.SaleId.Value);
}
if (!string.IsNullOrEmpty(req.JmsTelPhone))
{
query = query.Where(d => d.JmsTelPhone.Contains(req.JmsTelPhone));
}
if (isSign == null)
{
}
else
{
if (isSign.Value)
{
query = query.Where(d => d.JmsClassName != "" && d.JmsClassName != null);
}
else
{
query = query.Where(d => d.JmsClassName == "" || d.JmsClassName == null);
}
}
if (string.IsNullOrEmpty(req.sidx))
{
req.sidx = "id";
req.sord = "desc";
}
SearchListResult <JiaMengShangInfo> retListResult = query.ToSearchList(req);
return(retListResult);
}