/// <summary>
/// 从服务器端获取分页数据
/// </summary>
/// <param name="context">上下文对象</param>
public void GetCustomerManagerPager(HttpContext context)
{
string username = context.User.Identity.Name;
int DealerId = 0;
SysUserEntity model = new BLL.sysUser().GetSysUserByLoginName(username);
DealerId = model.DealerId;
int totalCount = 0; // 总记录数
int pageIndex = Convert.ToInt32(context.Request["page"]); // 当前页
int pageSize = Convert.ToInt32(context.Request["rows"]); // 页码大小
Common.Entity.Customer search = new Common.Entity.Customer();
if (!string.IsNullOrEmpty(context.Request.Form["CustomersearchContext"]))
{
search = JsonConvert.DeserializeObject <Common.Entity.Customer>(context.Request.Form["CustomersearchContext"]);
}
var list = new BLL.Customer().GetDealerManagerPager(pageIndex, pageSize, DealerId, search, out totalCount);
int totalPages = CommonFunction.CalculateTotalPage(pageSize, totalCount);
var ResultData = new CommonFunction.DataResult <Common.Entity.Customer>()
{
totalCount = totalCount,
totalpages = totalPages,
currPage = pageIndex,
dataList = list,
};
context.Response.Write(JsonConvert.SerializeObject(ResultData));
}
/// <summary>
/// 获取客户信息实体对象
/// </summary>
/// <param name="context">上下文对象</param>
public void GetCustomerByPKID(HttpContext context)
{
int customerId = Convert.ToInt32(context.Request["customerId"]);
if (customerId > 0)
{
Common.Entity.Customer model = new Common.Entity.Customer();
model = new BLL.Customer().GetCustomerByPKID(customerId);
context.Response.Write(JsonConvert.SerializeObject(model));
}
}
/// <summary>
/// 获取客户信息实体对象
/// </summary>
/// <param name="customerId">客户ID</param>
/// <returns>返回实体类</returns>
public Common.Entity.Customer GetCustomerByPKID(int customerId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append($@"select B.Name CustomerNatureName2,D.Name DealerName, A.* from
(select CN.Name as CustomerNatureName,C.* from Customer C left join CustomerNature CN on C.CustomerNatureID=CN.PKID) A
left join (select * from CustomerNature CN) B on A.CustomerNature2ID=B.PKID
left join Dealer D on A.DealerID=D.PKID where A.PKID={customerId}");
Common.Entity.Customer customer = new Common.Entity.Customer();
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
customer = conn.Query <Common.Entity.Customer>(strSql.ToString()).FirstOrDefault();
}
return(customer);
}
/// <summary>
/// 编辑客户
/// </summary>
/// <param name="customer">客户实体</param>
/// <param name="listContact">客户联系人</param>
/// <returns>返回值</returns>
public bool Update(Common.Entity.Customer customer, List <Common.Entity.CustomerContact> listContact)
{
DataTable dt = new BLL.Customer().GetCustomerContactByCustomerId(customer.PKID);
List <Common.Entity.CustomerContact> AddList = new List <Common.Entity.CustomerContact>();
List <Common.Entity.CustomerContact> DeleteList = new List <Common.Entity.CustomerContact>();
List <Common.Entity.CustomerContact> UpdateList = new List <Common.Entity.CustomerContact>();
Common.Entity.CustomerContact AddContact = null;
Common.Entity.CustomerContact DeleteContact = null;
Common.Entity.CustomerContact UpdateContact = null;
if (dt.Rows.Count > 0) //查询已删除添加至List
{
for (int i = 0; i < dt.Rows.Count; i++)
{
List <Common.Entity.CustomerContact> newlist = listContact.Where(x => x.PKID == dt.Rows[i]["PKID"].ToString()).ToList();
if (newlist.Count == 0)
{
DeleteContact = new Common.Entity.CustomerContact();
DeleteContact.PKID = dt.Rows[i]["PKID"].ToString();
DeleteList.Add(DeleteContact);
}
}
}
for (int j = 0; j < listContact.Count; j++)
{
if (string.IsNullOrWhiteSpace(listContact[j].PKID))//原数据不存在新数据（新增）
{
AddContact = new Common.Entity.CustomerContact();
AddContact.CustomerID = customer.PKID;
AddContact.Name = listContact[j].Name;
AddContact.Job = listContact[j].Job;
AddContact.RoleID = listContact[j].RoleID;
AddContact.Phone = listContact[j].Phone;
AddContact.MobileTel = listContact[j].MobileTel;
AddList.Add(AddContact);
}
else//源数据存在新数据（修改）
{
UpdateContact = listContact[j];
UpdateList.Add(UpdateContact);
}
}
return(new DAL.Customer().Update(customer, AddList, DeleteList, UpdateList));
}
/// <summary>
/// 编辑客户
/// </summary>
/// <param name="customer">客户实体</param>
/// <param name="DeleteContactList">客户联系人</param>
/// <returns></returns>
public bool Update(Common.Entity.Customer customer, List <Common.Entity.CustomerContact> list, List <Common.Entity.CustomerContact> Dellist, List <Common.Entity.CustomerContact> UpdateList)
{
try
{
using (TransactionScope tran = new TransactionScope())
{
Hashtable strStringList = new Hashtable();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Update Customer set DealerID=@DealerID,EnterpriseCode=@EnterpriseCode,CreateTime=@CreateTime,Name=@Name,Address=@Address,Zip=@Zip,CustomerNatureID=@CustomerNatureID,
CustomerNature2ID=@CustomerNature2ID,OldCustomerNatureID=@OldCustomerNatureID,CustomerTypeID=@CustomerTypeID,Phone=@Phone,Fax=@Fax,Eamil=@Eamil,WebSite=@WebSite,CompetentDepartment=@CompetentDepartment,
ExecutiveDepartment=@ExecutiveDepartment,UseDepartment=@UseDepartment,MainBusiness=@MainBusiness,IndustryStatus=@IndustryStatus,CustomerProfiles=@CustomerProfiles,
Remark=@Remark,CustomerPrimaryID=@CustomerPrimaryID where PKID=@PKID");
SqlParameter[] param =
{
new SqlParameter("@PKID", customer.PKID),
new SqlParameter("@DealerID", customer.DealerID),
new SqlParameter("@EnterpriseCode", customer.EnterpriseCode),
new SqlParameter("@CreateTime", customer.CreateTime),
new SqlParameter("@Name", customer.Name),
new SqlParameter("@Address", customer.Address),
new SqlParameter("@Zip", customer.Zip),
new SqlParameter("@CustomerNatureID", customer.CustomerNatureID),
new SqlParameter("@CustomerNature2ID", customer.CustomerNature2ID),
new SqlParameter("@OldCustomerNatureID", customer.OldCustomerNatureID),
new SqlParameter("@CustomerTypeID", customer.CustomerTypeID),
new SqlParameter("@Phone", customer.Phone),
new SqlParameter("@Fax", customer.Fax),
new SqlParameter("@Eamil", customer.Eamil),
new SqlParameter("@WebSite", customer.WebSite),
new SqlParameter("@CompetentDepartment", customer.CompetentDepartment),
new SqlParameter("@ExecutiveDepartment", customer.ExecutiveDepartment),
new SqlParameter("@UseDepartment", customer.UseDepartment),
new SqlParameter("@MainBusiness", customer.MainBusiness),
new SqlParameter("@IndustryStatus", customer.IndustryStatus),
new SqlParameter("@CustomerProfiles", customer.CustomerProfiles),
new SqlParameter("@Remark", customer.Remark),
new SqlParameter("@CustomerPrimaryID", customer.CustomerPrimaryID),
};
strStringList.Add(strSql, param);
//删除
if (Dellist.Count > 0)
{
for (int i = 0; i < Dellist.Count; i++)// 删除的用户角色
{
StringBuilder strDeleteSql = new StringBuilder();
strDeleteSql.Append(@"delete from CustomerContact where PKID=@PKID");
SqlParameter[] paramer1 =
{
new SqlParameter("@PKID", SqlDbType.Int, 1000)
};
paramer1[0].Value = Dellist[i].PKID;
strStringList.Add(strDeleteSql, paramer1);
}
}
//新增
if (list.Count > 0)
{
foreach (var item in list)
{
StringBuilder strInsertSql = new StringBuilder();
strInsertSql.Append(@"Insert into CustomerContact(CustomerID,Name,Job,RoleID,Phone,
MobileTel,Fax,Email,OtherContactInfo,Birthday,Hobbies,Remark) Values");
strInsertSql.Append(@"(@CustomerID,@Name,@Job,@RoleID,@Phone,@MobileTel,@Fax,@Email,
@OtherContactInfo,@Birthday,@Hobbies,@Remark)");
strInsertSql.Append(" SELECT @@IDENTITY");// 返回插入用户的主键
SqlParameter[] paramer2 =
{
new SqlParameter("@CustomerID", item.CustomerID),
new SqlParameter("@Name", item.Name),
new SqlParameter("@Job", item.Job),
new SqlParameter("@RoleID", item.RoleID),
new SqlParameter("@Phone", item.Phone),
new SqlParameter("@MobileTel", item.MobileTel),
new SqlParameter("@Fax", item.Fax),
new SqlParameter("@Email", item.Email),
new SqlParameter("@OtherContactInfo", item.OtherContactInfo),
new SqlParameter("@Birthday", item.Birthday),
new SqlParameter("@Hobbies", item.Hobbies),
new SqlParameter("@Remark", item.Remark),
};
strStringList.Add(strInsertSql, paramer2);
}
}
//编辑
if (UpdateList.Count > 0)
{
foreach (var item in UpdateList)
{
StringBuilder strUpdateSql = new StringBuilder();
strUpdateSql.Append(@"update CustomerContact set Name=@Name,Job=@Job,RoleID=@RoleID,Phone=@Phone,
MobileTel=@MobileTel where PKID=@PKID");
SqlParameter[] paramer3 =
{
new SqlParameter("@PKID", item.PKID),
new SqlParameter("@Name", item.Name),
new SqlParameter("@Job", item.Job),
new SqlParameter("@RoleID", item.RoleID),
new SqlParameter("@Phone", item.Phone),
new SqlParameter("@MobileTel", item.MobileTel),
};
strStringList.Add(strUpdateSql, paramer3);
}
}
int result = Convert.ToInt32(SqlHelper.ExecuteNonQuery(SqlHelper.connStr, strStringList));
tran.Complete();
if (result > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch
{
throw;
}
}
/// <summary>
/// 添加客户
/// </summary>
/// <param name="model">客户实体</param>
/// <param name="listContact">客户联系人实体</param>
/// <returns>返回值</returns>
public int AddCustomer(Common.Entity.Customer model, List <Common.Entity.CustomerContact> listContact)
{
Hashtable sqlStringListAdd = new Hashtable();
int result = 0;
if (model != null)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Insert into Customer(DealerID,EnterpriseCode,CreateTime,Name,Address,Zip,CustomerNatureID,CustomerNature2ID,OldCustomerNatureID,CustomerTypeID,
Phone,Fax,Eamil,WebSite,CompetentDepartment,ExecutiveDepartment,UseDepartment,MainBusiness,IndustryStatus,CustomerProfiles,Remark,CustomerPrimaryID) Values");
strSql.Append(@"(@DealerID,@EnterpriseCode,@CreateTime,@Name,@Address,@Zip,@CustomerNatureID,@CustomerNature2ID,@OldCustomerNatureID,@CustomerTypeID,
@Phone,@Fax,@Eamil,@WebSite,@CompetentDepartment,@ExecutiveDepartment,@UseDepartment,@MainBusiness,@IndustryStatus,@CustomerProfiles,@Remark,@CustomerPrimaryID)");
strSql.Append(" SELECT @@IDENTITY");// 返回插入用户的主键
SqlParameter[] param =
{
new SqlParameter("@DealerID", model.DealerID),
new SqlParameter("@EnterpriseCode", model.EnterpriseCode),
new SqlParameter("@CreateTime", model.CreateTime),
new SqlParameter("@Name", model.Name),
new SqlParameter("@Address", model.Address),
new SqlParameter("@Zip", model.Zip),
new SqlParameter("@CustomerNatureID", model.CustomerNatureID),
new SqlParameter("@CustomerNature2ID", model.CustomerNature2ID),
new SqlParameter("@OldCustomerNatureID", model.OldCustomerNatureID),
new SqlParameter("@CustomerTypeID", model.CustomerTypeID),
new SqlParameter("@Phone", model.Phone),
new SqlParameter("@Fax", model.Fax),
new SqlParameter("@Eamil", model.Eamil),
new SqlParameter("@WebSite", model.WebSite),
new SqlParameter("@CompetentDepartment", model.CompetentDepartment),
new SqlParameter("@ExecutiveDepartment", model.ExecutiveDepartment),
new SqlParameter("@UseDepartment", model.UseDepartment),
new SqlParameter("@MainBusiness", model.MainBusiness),
new SqlParameter("@IndustryStatus", model.IndustryStatus),
new SqlParameter("@CustomerProfiles", model.CustomerProfiles),
new SqlParameter("@Remark", model.Remark),
new SqlParameter("@CustomerPrimaryID", model.CustomerPrimaryID),
};
model.PKID = SqlHelper.ExecuteReturnID(SqlHelper.connStr, CommandType.Text, strSql.ToString(), param);
}
if (listContact != null && listContact.Count > 0)
{
foreach (Common.Entity.CustomerContact contatcEntity in listContact)
{
contatcEntity.CustomerID = model.PKID;
StringBuilder strSqlContact = new StringBuilder();
strSqlContact.Append(@"Insert into CustomerContact(CustomerID,Name,Job,RoleID,Phone,
MobileTel,Fax,Email,OtherContactInfo,Birthday,Hobbies,Remark) Values");
strSqlContact.Append(@"(@CustomerID,@Name,@Job,@RoleID,@Phone,@MobileTel,@Fax,@Email,
@OtherContactInfo,@Birthday,@Hobbies,@Remark)");
strSqlContact.Append(" SELECT @@IDENTITY");// 返回插入用户的主键
SqlParameter[] paramContact =
{
new SqlParameter("@CustomerID", contatcEntity.CustomerID),
new SqlParameter("@Name", contatcEntity.Name),
new SqlParameter("@Job", contatcEntity.Job),
new SqlParameter("@RoleID", contatcEntity.RoleID),
new SqlParameter("@Phone", contatcEntity.Phone),
new SqlParameter("@MobileTel", contatcEntity.MobileTel),
new SqlParameter("@Fax", contatcEntity.Fax),
new SqlParameter("@Email", contatcEntity.Email),
new SqlParameter("@OtherContactInfo", contatcEntity.OtherContactInfo),
new SqlParameter("@Birthday", contatcEntity.Birthday),
new SqlParameter("@Hobbies", contatcEntity.Hobbies),
new SqlParameter("@Remark", contatcEntity.Remark),
};
sqlStringListAdd.Add(strSqlContact, paramContact);
}
}
try
{
result = Convert.ToInt32(SqlHelper.ExecuteNonQuery(SqlHelper.connStr, sqlStringListAdd));
if (result > 0)
{
return(result);
}
else
{
return(0);
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 从服务器端获取分页列表
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">页码大小</param>
/// <param name="totalCount">总记录数</param>
/// <returns>返回DataTable</returns>
public List <Common.Entity.Customer> GetDealerManagerPager(int pageIndex, int pageSize, int dealerId, Common.Entity.Customer search, out int totalCount)
{
StringBuilder sqlCount = new StringBuilder();
sqlCount.Append($@"select count(1) from (select distinct b.PKID 父级ID,b.Name 父级名称,c.* from CustomerNature a INNER JOIN
(select * from CustomerNature where ParentID='0') b
ON a.ParentID=b.PKID
INNER JOIN Customer c on b.PKID=c.CustomerNatureID and c.DealerID={dealerId}
INNER JOIN Dealer d on c.DealerID=d.PKID where c.PKID>0");
if (!string.IsNullOrEmpty(search.EnterpriseCode))
{
sqlCount.Append(" and c.EnterpriseCode like '%" + search.EnterpriseCode + "%'");
}
if (!string.IsNullOrEmpty(search.Name))
{
sqlCount.Append(" and c.Name like '%" + search.Name + "%'");
}
if (search.CustomerNatureID > 0)
{
sqlCount.Append(" and c.CustomerNatureID=" + search.CustomerNatureID);
}
if (search.CustomerNature2ID > 0)
{
sqlCount.Append(" and c.CustomerNature2ID=" + search.CustomerNature2ID);
}
sqlCount.Append(" ) A");
StringBuilder strSql = new StringBuilder();
strSql.Append($@"select top ({pageSize}) * from (
select row_number() over (order by S.Name) as rowNumber,S.* from
(select distinct b.PKID 父级ID,d.Name as DealerName,b.Name CustomerNatureName,
c.* from CustomerNature a INNER JOIN
(select * from CustomerNature where ParentID='0') b
ON a.ParentID=b.PKID INNER JOIN Customer c
on b.PKID=c.CustomerNatureID and c.DealerID={dealerId} INNER JOIN Dealer d
on c.DealerID=d.PKID where c.PKID>0");
if (!string.IsNullOrEmpty(search.EnterpriseCode))
{
strSql.Append(" and c.EnterpriseCode like '%" + search.EnterpriseCode + "%'");
}
if (!string.IsNullOrEmpty(search.Name))
{
strSql.Append(" and c.Name like '%" + search.Name + "%'");
}
if (search.CustomerNatureID > 0)
{
strSql.Append(" and c.CustomerNatureID=" + search.CustomerNatureID);
}
if (search.CustomerNature2ID > 0)
{
strSql.Append(" and c.CustomerNature2ID=" + search.CustomerNature2ID);
}
strSql.Append($" ) as S) as T where T.rowNumber>({pageIndex}-1)*{pageSize}");
List <Common.Entity.Customer> list = new List <Common.Entity.Customer>();
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
totalCount = conn.Query <int>(sqlCount.ToString()).FirstOrDefault();
list = conn.Query <Common.Entity.Customer>(strSql.ToString()).ToList();
}
return(list);
}
/// <summary>
/// 添加客户
/// </summary>
/// <param name="model">客户实体</param>
/// <param name="listContact">客户联系人实体</param>
/// <returns>返回值</returns>
public int AddCustomer(Common.Entity.Customer model, List <Common.Entity.CustomerContact> listContact)
{
return(new DAL.Customer().AddCustomer(model, listContact));
}
/// <summary>
/// 保存客户信息
/// </summary>
/// <param name="context">上下文对象</param>
public void SaveCustomer(HttpContext context)
{
try
{
string opType = context.Request.Form["opType"];
int DealerId = Convert.ToInt32(context.Request.Form["DealerId"]);
int ContactName = Convert.ToInt32(context.Request.Form["ContactName"]);
int ContactName2 = Convert.ToInt32(context.Request.Form["ContactName2"]);
string EnterpriseCode = context.Request.Form["EnterpriseCode"];
string Name = context.Request.Form["Name"];
string Zip = context.Request.Form["Zip"];
string Address = context.Request.Form["Address"];
string Eamil = context.Request.Form["Eamil"];
string Phone = context.Request.Form["Phone"];
DateTime CreateTime = Convert.ToDateTime(context.Request.Form["CreateTime"]);
string CompetentDepartment = context.Request.Form["CompetentDepartment"];
string ExecutiveDepartment = context.Request.Form["ExecutiveDepartment"];
string MainBusiness = context.Request.Form["MainBusiness"];
string Remark = context.Request.Form["Remark"];
string contactData = context.Request["contactData"];
BLL.Customer bll = new BLL.Customer();
List <Common.Entity.CustomerContact> Contactlist = JsonConvert.DeserializeObject <List <Common.Entity.CustomerContact> >(contactData); // 获取客户联系人信息
if ("update".Equals(opType))
{
// 编辑
int cusId = Convert.ToInt32(context.Request["cusId"]); // 客户ID
if (cusId > 0)
{
Common.Entity.Customer entity = new Common.Entity.Customer();
entity.PKID = cusId;
entity.DealerID = DealerId;
entity.CustomerNatureID = ContactName;
entity.CustomerNature2ID = ContactName2;
entity.EnterpriseCode = EnterpriseCode;
entity.Name = Name;
entity.Zip = Zip;
entity.Address = Address;
entity.Eamil = Eamil;
entity.Phone = Phone;
entity.CreateTime = CreateTime;
entity.CompetentDepartment = CompetentDepartment;
entity.ExecutiveDepartment = ExecutiveDepartment;
entity.MainBusiness = MainBusiness;
entity.Remark = Remark;
bool result = bll.Update(entity, Contactlist);
if (result)
{
context.Response.Write("{\"msg\":\"保存成功。\",\"success\":true}");
}
else
{
context.Response.Write("{\"msg\":\"保存失败。\",\"success\":false}");
}
}
}
else
{
// 添加
Common.Entity.Customer model = new Common.Entity.Customer();
model.DealerID = DealerId;
model.CustomerNatureID = ContactName;
model.CustomerNature2ID = ContactName2;
model.EnterpriseCode = EnterpriseCode;
model.Name = Name;
model.Zip = Zip;
model.Address = Address;
model.Eamil = Eamil;
model.Phone = Phone;
model.CreateTime = CreateTime;
model.CompetentDepartment = CompetentDepartment;
model.ExecutiveDepartment = ExecutiveDepartment;
model.MainBusiness = MainBusiness;
model.Remark = Remark;
if (bll.Exists(model.EnterpriseCode))
{
context.Response.Write("{\"msg\":\"客户信息已存在，请修改。\",\"state\":\"T\"}");
}
else if (bll.AddCustomer(model, Contactlist) > 0)
{
context.Response.Write("{\"msg\":\"保存成功。\",\"success\":true}");
}
else
{
context.Response.Write("{\"msg\":\"保存失败。\",\"success\":false}");
}
}
}
catch (Exception ex)
{
throw ex;
}
}