using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using CY.IDAL;
|
using System.Data.SqlClient;
|
using System.Data;
|
using CY.Infrastructure.DESEncrypt;
|
using CY.Infrastructure.Common;
|
using CY.Infrastructure.Query;
|
using CY.Model;
|
|
namespace CY.SQLDAL
|
{
|
public class OA_IntentionCustomerDAL : IOA_IntentionCustomerDAL
|
{
|
|
private Database _dataBase = null;
|
private Pagination _fixedPagination;//固定分页参数
|
|
/// <summary>
|
/// 实例化page对象的属性
|
/// </summary>
|
public OA_IntentionCustomerDAL()
|
{
|
_dataBase = new Database();
|
|
_fixedPagination = new Pagination();
|
_fixedPagination.PageSize = int.MaxValue;
|
_fixedPagination.PageIndex = 1;
|
}
|
|
/// <summary>
|
/// 实例化page对象的属性
|
/// </summary>
|
public OA_IntentionCustomerDAL(Database dDatabase)
|
{
|
_dataBase = dDatabase;
|
}
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.OA_IntentionCustomer trueModel = model as Model.OA_IntentionCustomer;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@FirmId",trueModel.FirmId),
|
new SqlParameter("@CustomerId",trueModel.CustomerId),
|
new SqlParameter("@CompanyName",trueModel.CompanyName),
|
new SqlParameter("@CustomerIndustriesId",trueModel.CustomerIndustriesId),
|
new SqlParameter("@CustomerTypeId",trueModel.CustomerTypeId),
|
new SqlParameter("@SourcesInfoId",trueModel.SourcesInfoId),
|
new SqlParameter("@DegreeImportanId",trueModel.DegreeImportanId),
|
new SqlParameter("@TurnoverIntentionId",trueModel.TurnoverIntentionId),
|
new SqlParameter("@Province",trueModel.Province),
|
new SqlParameter("@City",trueModel.City),
|
new SqlParameter("@County",trueModel.County),
|
new SqlParameter("@DetailedAddress",trueModel.DetailedAddress),
|
new SqlParameter("@Postcode",trueModel.Postcode),
|
new SqlParameter("@PhoneNum",trueModel.PhoneNum),
|
new SqlParameter("@MobileNum",trueModel.MobileNum),
|
new SqlParameter("@Email",trueModel.Email),
|
new SqlParameter("@QQ",trueModel.QQ),
|
new SqlParameter("@CompanyOfficers",trueModel.CompanyOfficers),
|
new SqlParameter("@OfficersMobile",trueModel.OfficersMobile),
|
new SqlParameter("@BusinessManagers",trueModel.BusinessManagers),
|
new SqlParameter("@CuPost",trueModel.CuPost),
|
new SqlParameter("@AccountManagerId",trueModel.AccountManagerId),
|
new SqlParameter("@BusinessManagerId",trueModel.BusinessManagerId),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@CustomerStatusId",trueModel.CustomerStatusId),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@Remark",trueModel.Remark) ,
|
new SqlParameter("@BusinessMobile",trueModel.BusinessMobile),
|
new SqlParameter("@Fax",trueModel.Fax)
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_IntentionCustomer_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.OA_IntentionCustomer trueModel = model as Model.OA_IntentionCustomer;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@FirmId",trueModel.FirmId),
|
new SqlParameter("@CustomerId",trueModel.CustomerId),
|
new SqlParameter("@CompanyName",trueModel.CompanyName),
|
new SqlParameter("@CustomerIndustriesId",trueModel.CustomerIndustriesId),
|
new SqlParameter("@CustomerTypeId",trueModel.CustomerTypeId),
|
new SqlParameter("@SourcesInfoId",trueModel.SourcesInfoId),
|
new SqlParameter("@DegreeImportanId",trueModel.DegreeImportanId),
|
new SqlParameter("@TurnoverIntentionId",trueModel.TurnoverIntentionId),
|
new SqlParameter("@Province",trueModel.Province),
|
new SqlParameter("@City",trueModel.City),
|
new SqlParameter("@County",trueModel.County),
|
new SqlParameter("@DetailedAddress",trueModel.DetailedAddress),
|
new SqlParameter("@Postcode",trueModel.Postcode),
|
new SqlParameter("@PhoneNum",trueModel.PhoneNum),
|
new SqlParameter("@MobileNum",trueModel.MobileNum),
|
new SqlParameter("@Email",trueModel.Email),
|
new SqlParameter("@QQ",trueModel.QQ),
|
new SqlParameter("@CompanyOfficers",trueModel.CompanyOfficers),
|
new SqlParameter("@OfficersMobile",trueModel.OfficersMobile),
|
new SqlParameter("@BusinessManagers",trueModel.BusinessManagers),
|
new SqlParameter("@CuPost",trueModel.CuPost),
|
new SqlParameter("@AccountManagerId",trueModel.AccountManagerId),
|
new SqlParameter("@BusinessManagerId",trueModel.BusinessManagerId),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@CustomerStatusId",trueModel.CustomerStatusId),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@Remark",trueModel.Remark) ,
|
new SqlParameter("@BusinessMobile",trueModel.BusinessMobile),
|
new SqlParameter("@Fax",trueModel.Fax)
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_IntentionCustomer_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.OA_IntentionCustomer trueModel = model as Model.OA_IntentionCustomer;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid)
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_IntentionCustomer_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 分页查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <param name="pagination"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_IntentionCustomer> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 单个查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_IntentionCustomer> SelectAllModel(Infrastructure.Query.Query query)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 获取最大的CustomerId
|
/// </summary>
|
/// <param name="FirmId"></param>
|
/// <returns></returns>
|
public OA_IntentionCustomer getMaxCustomerIdModel(Guid FirmId)
|
{
|
try
|
{
|
string selectTarget = "TOP 1 * ";
|
string fromSouce = "OA_IntentionCustomer WHERE FirmId='" + FirmId + "' ORDER BY createtime DESC";
|
IList<OA_IntentionCustomer> result = _dataBase.SelectModel<OA_IntentionCustomer>(selectTarget, fromSouce);
|
if (result != null && result.Count > 0)
|
return result[0];
|
else
|
return null;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 获取全部ItentionCustomer
|
/// </summary>
|
/// <param name="FirmId"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_IntentionCustomer> getAllIntentionCustomer(Guid FirmId)
|
{
|
string selectTarget = @" a.* ,b.Name as DegreeImportanName " +
|
" ,c.Name as SourcesInfoName " +
|
" ,d.Name as TurnoverIntentionName " +
|
" ,e.Name as CustomerTypeName " +
|
" ,g.Name as CustomerIndustriesName " +
|
" ,h.Name as CustomerStatusName ";
|
string fromSouce = "OA_IntentionCustomer as a " +
|
"left join Sys_Dictionary as b on a.DegreeImportanId=b.Keyid " +
|
"left join Sys_Dictionary as c on a.SourcesInfoId=c.Keyid " +
|
"left join Sys_Dictionary as d on a.TurnoverIntentionId=d.Keyid " +
|
"left join Sys_Dictionary as e on a.CustomerTypeId=e.Keyid " +
|
"left join Sys_Dictionary as g on a.CustomerIndustriesId=g.Keyid " +
|
"left join Sys_Dictionary as h on a.CustomerStatusId=h.Keyid " +
|
"and a.FirmId='" + FirmId + "'";
|
|
|
|
return _dataBase.SelectModelPage<OA_IntentionCustomer>(_fixedPagination, selectTarget, fromSouce, "a.CreateTime DESC");
|
}
|
|
public IEnumerable<Model.OA_IntentionCustomer> getAllIntentionCustomer(Pagination pa, Guid FirmId, string CompanyName, DateTime? beginTime, DateTime? endTime, string SourcesInfoId, string CustomerIndustriesId, string AccountManagerId, string BusinessManagerId, string CustomerTypeId, string TurnoverIntentionId, string CustomerStatusId, string DegreeImportanId, string Province, string City, string County)
|
{
|
string Condition = " where FirmId='" + FirmId + "'";
|
|
if (!string.IsNullOrEmpty(CompanyName))
|
{
|
Condition += " and CompanyName like '%" + CompanyName + "%" + "'";
|
}
|
|
if (beginTime.HasValue)
|
{
|
Condition += string.Format(" and CAST(CreateTime AS DATE) >='{0}'", beginTime);
|
}
|
|
if (endTime.HasValue)
|
{
|
Condition += string.Format(" and CAST(CreateTime AS DATE) <='{0}'", endTime);
|
}
|
|
if (!string.IsNullOrEmpty(SourcesInfoId))
|
{
|
Condition += string.Format(" and SourcesInfoId='{0}'", SourcesInfoId);
|
}
|
if (!string.IsNullOrEmpty(CustomerIndustriesId))
|
{
|
Condition += string.Format(" and CustomerIndustriesId='{0}'", CustomerIndustriesId);
|
}
|
if (!string.IsNullOrEmpty(AccountManagerId))
|
{
|
Condition += string.Format(" and AccountManagerId='{0}'", AccountManagerId);
|
}
|
if (!string.IsNullOrEmpty(BusinessManagerId))
|
{
|
Condition += string.Format(" and BusinessManagerId='{0}'", BusinessManagerId);
|
}
|
if (!string.IsNullOrEmpty(CustomerTypeId))
|
{
|
Condition += string.Format(" and CustomerTypeId='{0}'", CustomerTypeId);
|
}
|
if (!string.IsNullOrEmpty(TurnoverIntentionId))
|
{
|
Condition += string.Format(" and TurnoverIntentionId='{0}'", TurnoverIntentionId);
|
}
|
if (!string.IsNullOrEmpty(CustomerStatusId))
|
{
|
Condition += string.Format(" and CustomerStatusId='{0}'", CustomerStatusId);
|
}
|
if (!string.IsNullOrEmpty(DegreeImportanId))
|
{
|
Condition += string.Format(" and DegreeImportanId='{0}'", DegreeImportanId);
|
}
|
if (!string.IsNullOrEmpty(Province))
|
{
|
Condition += string.Format(" and Province='{0}'", Province);
|
}
|
|
if (!string.IsNullOrEmpty(City))
|
{
|
Condition += string.Format(" and City='{0}'", City);
|
}
|
if (!string.IsNullOrEmpty(County))
|
{
|
Condition += string.Format(" and County='{0}'", County);
|
}
|
|
|
string selectTarget = @" a.* ,b.Name as DegreeImportanName " +
|
" ,c.Name as SourcesInfoName " +
|
" ,d.Name as TurnoverIntentionName " +
|
" ,e.Name as CustomerTypeName " +
|
" ,g.Name as CustomerIndustriesName " +
|
" ,h.Name as CustomerStatusName ,d_OA_Staff.Name AS BusinessmanagerName,d_OA_Staff1.Name AS AccountmanagerName";
|
string fromSouce = "(SELECT * FROM dbo.OA_IntentionCustomer " + Condition + ") as a " +
|
"left join Sys_Dictionary as b on a.DegreeImportanId=b.Keyid " +
|
"left join Sys_Dictionary as c on a.SourcesInfoId=c.Keyid " +
|
"left join Sys_Dictionary as d on a.TurnoverIntentionId=d.Keyid " +
|
"left join Sys_Dictionary as e on a.CustomerTypeId=e.Keyid " +
|
"left join Sys_Dictionary as g on a.CustomerIndustriesId=g.Keyid " +
|
"left join Sys_Dictionary as h on a.CustomerStatusId=h.Keyid " +
|
"LEFT JOIN dbo.OA_Staff AS d_OA_Staff ON (a.BusinessManagerId=d_OA_Staff.Keyid) " +
|
"LEFT JOIN dbo.OA_Staff AS d_OA_Staff1 ON (a.AccountManagerId=d_OA_Staff1.Keyid) ";
|
|
|
|
return _dataBase.SelectModelPage<OA_IntentionCustomer>(pa, selectTarget, fromSouce, " a.CreateTime DESC ");
|
|
}
|
|
public IEnumerable<Model.OA_IntentionCustomer> getAllIntentionCustomerList(Pagination pa, Guid FirmId, string CompanyName, DateTime? beginTime, DateTime? endTime, string SourcesInfoId, string CustomerIndustriesId, string AccountManagerId, string BusinessManagerId, string CustomerTypeId, string TurnoverIntentionId, string CustomerStatusId, string DegreeImportanId, string Province, string City, string County, string selNupdoor, string Nupdoor, string selNphone, string Nphone, string selNqq, string Nqq, string selNmessage, string Nmessage, string selNemial, string Nemial, string selNDirectMail, string NDirectMail, string selNall, string Nall)
|
{
|
string Condition = " where FirmId='" + FirmId + "'";
|
string Condition2 = " where 1=1 ";
|
if (!string.IsNullOrEmpty(CompanyName))
|
{
|
Condition += " and CompanyName like '%" + CompanyName + "%" + "'";
|
}
|
|
if (beginTime.HasValue)
|
{
|
Condition += string.Format(" and CAST(CreateTime AS DATE) >='{0}'", beginTime);
|
}
|
|
if (endTime.HasValue)
|
{
|
Condition += string.Format(" and CAST(CreateTime AS DATE) <='{0}'", endTime);
|
}
|
|
if (!string.IsNullOrEmpty(SourcesInfoId))
|
{
|
Condition += string.Format(" and SourcesInfoId='{0}'", SourcesInfoId);
|
}
|
if (!string.IsNullOrEmpty(CustomerIndustriesId))
|
{
|
Condition += string.Format(" and CustomerIndustriesId='{0}'", CustomerIndustriesId);
|
}
|
if (!string.IsNullOrEmpty(AccountManagerId))
|
{
|
Condition += string.Format(" and AccountManagerId='{0}'", AccountManagerId);
|
}
|
if (!string.IsNullOrEmpty(BusinessManagerId))
|
{
|
Condition += string.Format(" and BusinessManagerId='{0}'", BusinessManagerId);
|
}
|
if (!string.IsNullOrEmpty(CustomerTypeId))
|
{
|
Condition += string.Format(" and CustomerTypeId='{0}'", CustomerTypeId);
|
}
|
if (!string.IsNullOrEmpty(TurnoverIntentionId))
|
{
|
Condition += string.Format(" and TurnoverIntentionId='{0}'", TurnoverIntentionId);
|
}
|
if (!string.IsNullOrEmpty(CustomerStatusId))
|
{
|
Condition += string.Format(" and CustomerStatusId='{0}'", CustomerStatusId);
|
}
|
if (!string.IsNullOrEmpty(DegreeImportanId))
|
{
|
Condition += string.Format(" and DegreeImportanId='{0}'", DegreeImportanId);
|
}
|
if (!string.IsNullOrEmpty(Province))
|
{
|
Condition += string.Format(" and Province='{0}'", Province);
|
}
|
|
if (!string.IsNullOrEmpty(City))
|
{
|
Condition += string.Format(" and City='{0}'", City);
|
}
|
if (!string.IsNullOrEmpty(County))
|
{
|
Condition += string.Format(" and County='{0}'", County);
|
}
|
|
if (!string.IsNullOrEmpty(selNupdoor) && !string.IsNullOrEmpty(Nupdoor))
|
{
|
if (selNupdoor == ">=")
|
{
|
if (Nupdoor != "0")
|
Condition2 += string.Format(" and Nupdoor{0}'{1}'", selNupdoor, Nupdoor);
|
}
|
else
|
Condition2 += string.Format(" and ( Nupdoor{0}'{1}' or Nupdoor is null ) ", selNupdoor, Nupdoor);
|
}
|
if (!string.IsNullOrEmpty(selNphone) && !string.IsNullOrEmpty(Nphone))
|
{
|
if (selNphone == ">=")
|
{
|
if (Nphone != "0")
|
Condition2 += string.Format(" and Nphone{0}'{1}'", selNphone, Nphone);
|
}
|
else
|
Condition2 += string.Format(" and ( Nphone{0}'{1}' or Nphone is null ) ", selNphone, Nphone);
|
}
|
if (!string.IsNullOrEmpty(selNqq) && !string.IsNullOrEmpty(Nqq))
|
{
|
if (selNqq == ">=")
|
{
|
if (Nqq != "0")
|
Condition2 += string.Format(" and Nqq{0}'{1}'", selNqq, Nqq);
|
}
|
else
|
Condition2 += string.Format(" and ( Nqq{0}'{1}' or Nqq is null ) ", selNqq, Nqq);
|
}
|
if (!string.IsNullOrEmpty(selNmessage) && !string.IsNullOrEmpty(Nmessage))
|
{
|
if (selNmessage == ">=")
|
{
|
if (Nmessage != "0")
|
Condition2 += string.Format(" and Nmessage{0}'{1}'", selNmessage, Nmessage);
|
}
|
else
|
Condition2 += string.Format(" and ( Nmessage{0}'{1}' or Nmessage is null ) ", selNmessage, Nmessage);
|
}
|
if (!string.IsNullOrEmpty(selNemial) && !string.IsNullOrEmpty(Nemial))
|
{
|
if (selNemial == ">=")
|
{
|
if (Nemial != "0")
|
Condition2 += string.Format(" and Nemial{0}'{1}'", selNemial, Nemial);
|
}
|
else
|
Condition2 += string.Format(" and ( Nemial{0}'{1}' or Nemial is null ) ", selNemial, Nemial);
|
}
|
if (!string.IsNullOrEmpty(selNDirectMail) && !string.IsNullOrEmpty(NDirectMail))
|
{
|
if (selNDirectMail == ">=")
|
{
|
if (NDirectMail != "0")
|
Condition2 += string.Format(" and NDirectMail{0}'{1}'", selNDirectMail, NDirectMail);
|
}
|
else
|
Condition2 += string.Format(" and ( NDirectMail{0}'{1}' or NDirectMail is null ) ", selNDirectMail, NDirectMail);
|
}
|
|
if (!string.IsNullOrEmpty(selNall))
|
{
|
if (selNall == "true")
|
{
|
Condition2 += string.Format(" and (Nupdoor >0 or Nphone >0 or Nqq >0 or Nmessage >0 or Nemial >0 or NDirectMail >0)");
|
}
|
else
|
{
|
Condition2 += string.Format(" and (Nupdoor is null and Nphone is null and Nqq is null and Nmessage is null and Nemial is null and NDirectMail is null)");
|
}
|
}
|
|
string selectTarget = @" a.*,b.Name AS SourcesInfoName,c.Name AS DegreeImportanName,d.Name AS CustomerStatusName, f.*,d_OA_Staff.Name AS BusinessmanagerName,d_OA_Staff1.Name AS AccountmanagerName ";
|
string fromSouce = "(SELECT * FROM dbo.OA_IntentionCustomer " + Condition + ") AS a " +
|
" LEFT JOIN dbo.Sys_Dictionary AS b ON a.SourcesInfoId=b.Keyid " +
|
" LEFT JOIN dbo.Sys_Dictionary AS c ON a.DegreeImportanId=c.Keyid " +
|
"LEFT JOIN dbo.OA_Staff AS d_OA_Staff ON (a.BusinessManagerId=d_OA_Staff.Keyid) " +
|
"LEFT JOIN dbo.OA_Staff AS d_OA_Staff1 ON (a.AccountManagerId=d_OA_Staff1.Keyid) " +
|
" LEFT JOIN dbo.Sys_Dictionary AS d ON a.CustomerStatusId=d.Keyid " +
|
" LEFT JOIN( SELECT CustomerId AS Fid, SUM(CASE when AccessTypeId='1' THEN 1 ELSE 0 END) AS Nupdoor , " +
|
" SUM(CASE when AccessTypeId='2' THEN 1 ELSE 0 END) AS Nphone, " +
|
" SUM(CASE when AccessTypeId='3' THEN 1 ELSE 0 END) AS Nqq, " +
|
" SUM(CASE when AccessTypeId='4' THEN 1 ELSE 0 END) AS Nmessage, " +
|
" SUM(CASE when AccessTypeId='5' THEN 1 ELSE 0 END) AS NDirectMail, " +
|
" SUM(CASE when AccessTypeId='6' THEN 1 ELSE 0 END) AS Nemial, " +
|
" COUNT(*) AS Nall FROM dbo.OA_CustomerAccessRecord GROUP BY CustomerId) AS f ON a.Keyid=f.Fid " + Condition2;
|
|
|
return _dataBase.SelectModelPage<OA_IntentionCustomer>(pa, selectTarget, fromSouce, " a.CreateTime DESC ");
|
|
}
|
|
/// <summary>
|
/// 得到单个意向客户
|
/// </summary>
|
/// <param name="Keyid"></param>
|
/// <returns></returns>
|
public OA_IntentionCustomer getSingleIntentionCustomer(string Keyid)
|
{
|
try
|
{
|
string selectTarget = @" a.* ,b.Name as DegreeImportanName " +
|
" ,c.Name as SourcesInfoName " +
|
" ,d.Name as TurnoverIntentionName " +
|
" ,e.Name as CustomerTypeName " +
|
" ,g.Name as CustomerIndustriesName " +
|
" ,h.Name as CustomerStatusName ";
|
string fromSouce = "( select * from OA_IntentionCustomer where Keyid='" + Keyid + "' ) as a " +
|
"left join Sys_Dictionary as b on a.DegreeImportanId=b.Keyid " +
|
"left join Sys_Dictionary as c on a.SourcesInfoId=c.Keyid " +
|
"left join Sys_Dictionary as d on a.TurnoverIntentionId=d.Keyid " +
|
"left join Sys_Dictionary as e on a.CustomerTypeId=e.Keyid " +
|
"left join Sys_Dictionary as g on a.CustomerIndustriesId=g.Keyid " +
|
"left join Sys_Dictionary as h on a.CustomerStatusId=h.Keyid ";
|
|
IList<OA_IntentionCustomer> m_OA_IntentionCustomerList = _dataBase.SelectModel<OA_IntentionCustomer>(selectTarget, fromSouce);
|
if (m_OA_IntentionCustomerList != null && m_OA_IntentionCustomerList.Count() > 0)
|
return m_OA_IntentionCustomerList[0];
|
else
|
return null;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 返回 特定CompanyName的对象
|
/// </summary>
|
/// <param name="CompanyName"></param>
|
/// <returns></returns>
|
public bool isExistCompanyName(String CompanyName, Guid FirmId, int ID)
|
{
|
try
|
{
|
String selectTarget = " * ";
|
string fromSouce = "OA_IntentionCustomer where CompanyName='" + CompanyName + "' and FirmId='" + FirmId + "'";
|
List<OA_IntentionCustomer> m_OA_IntentionCustomerList = _dataBase.SelectModel<OA_IntentionCustomer>(selectTarget, fromSouce) as List<OA_IntentionCustomer>;
|
if (m_OA_IntentionCustomerList != null && m_OA_IntentionCustomerList.Count() > 0)
|
{
|
if (m_OA_IntentionCustomerList[0].Keyid == ID)
|
return false;
|
else
|
return true;
|
}
|
else
|
return false;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 模糊查询公司名称
|
/// </summary>
|
/// <param name="CompanyName"></param>
|
/// <param name="FirmId"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_IntentionCustomer> SelectListByName(String CompanyName, Guid FirmId)
|
{
|
if (CompanyName == null)
|
return null;//错误数据返会空
|
try
|
{
|
IList<OA_IntentionCustomer> result = _dataBase.SelectModel<OA_IntentionCustomer>(" top 3 * ", " OA_IntentionCustomer ", string.Format(" CompanyName like '%{0}%' and FirmId = '{1}' ", CompanyName, FirmId)) as IList<OA_IntentionCustomer>;//执行查询
|
return result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 根据客户编号获取客户
|
/// </summary>
|
/// <param name="Keyid">客户编号</param>
|
/// <returns></returns>
|
public IEnumerable<OA_IntentionCustomer> SelectListListByCustomId(string Keyid, Guid MemberId)
|
{
|
if (string.IsNullOrEmpty(Keyid))
|
return null;//错误数据返会空
|
|
try
|
{
|
IList<OA_IntentionCustomer> result = _dataBase.SelectModel<OA_IntentionCustomer>(" a.* ,f.*,b.Name as DegreeImportanName ,c.Name as SourcesInfoName ,d.Name as TurnoverIntentionName ,e.Name as CustomerTypeName ,g.Name as CustomerIndustriesName ,h.Name as CustomerStatusName ,d_OA_Staff.Name AS BusinessmanagerName,d_OA_Staff1.Name AS AccountmanagerName ", " OA_IntentionCustomer as a left join Sys_Dictionary as b on a.DegreeImportanId=b.Keyid left join Sys_Dictionary as c on a.SourcesInfoId=c.Keyid left join Sys_Dictionary as d on a.TurnoverIntentionId=d.Keyid left join Sys_Dictionary as e on a.CustomerTypeId=e.Keyid left join Sys_Dictionary as g on a.CustomerIndustriesId=g.Keyid left join Sys_Dictionary as h on a.CustomerStatusId=h.Keyid LEFT JOIN dbo.OA_Staff AS d_OA_Staff ON (a.BusinessManagerId=d_OA_Staff.Keyid) LEFT JOIN dbo.OA_Staff AS d_OA_Staff1 ON (a.AccountManagerId=d_OA_Staff1.Keyid) " +
|
" LEFT JOIN( SELECT CustomerId AS Fid, SUM(CASE when AccessTypeId='1' THEN 1 ELSE 0 END) AS Nupdoor , " +
|
" SUM(CASE when AccessTypeId='2' THEN 1 ELSE 0 END) AS Nphone, " +
|
" SUM(CASE when AccessTypeId='3' THEN 1 ELSE 0 END) AS Nqq, " +
|
" SUM(CASE when AccessTypeId='4' THEN 1 ELSE 0 END) AS Nmessage, " +
|
" SUM(CASE when AccessTypeId='5' THEN 1 ELSE 0 END) AS NDirectMail, " +
|
" SUM(CASE when AccessTypeId='6' THEN 1 ELSE 0 END) AS Nemial, " +
|
" COUNT(*) AS Nall FROM dbo.OA_CustomerAccessRecord GROUP BY CustomerId) AS f ON a.Keyid=f.Fid ", string.Format(" a.Keyid in ({0}) and a.FirmId ='{1}' order by a.CreateTime DESC", Keyid, MemberId)) as IList<OA_IntentionCustomer>;//执行查询
|
return result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
}
|
}
|
