/// <summary>
/// 修改一条记录
/// </summary>
/// <param name="model">实体类</param>
/// <returns></returns>
public bool Update(Common.Entity.Dealer model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Update Dealer set Code=@Code,Name=@Name,OldName=@OldName,BearUserId=@BearUserId,RegionManagerUserId=@RegionManagerUserId,CityId=@CityId,Address=@Address,
ZipCode=@ZipCode,Phone=@Phone,fax=@fax,Email=@Email,SalesTel=@SalesTel,ServerTel=@ServerTel,SystemEmail=@SystemEmail,SalesDepartment=@SalesDepartment,
SalesDepartmentTel=@SalesDepartmentTel,MaxCommissioner=@MaxCommissioner,MaxCommissionerTel=@MaxCommissionerTel where PKID=@PKID");
SqlParameter[] param =
{
new SqlParameter("@Code", model.Code),
new SqlParameter("@Name", model.Name),
new SqlParameter("@OldName", model.OldName),
new SqlParameter("@BearUserId", model.BearUserId),
new SqlParameter("@RegionManagerUserId", model.RegionManagerUserId),
new SqlParameter("@CityId", model.CityId),
new SqlParameter("@Address", model.Address),
new SqlParameter("@ZipCode", model.ZipCode),
new SqlParameter("@Phone", model.Phone),
new SqlParameter("@fax", model.fax),
new SqlParameter("@Email", model.Email),
new SqlParameter("@SalesTel", model.SalesTel),
new SqlParameter("@ServerTel", model.ServerTel),
new SqlParameter("@SystemEmail", model.SystemEmail),
new SqlParameter("@SalesDepartment", model.SalesDepartment),
new SqlParameter("@SalesDepartmentTel", model.SalesDepartmentTel),
new SqlParameter("@MaxCommissioner", model.MaxCommissioner),
new SqlParameter("@MaxCommissionerTel", model.MaxCommissionerTel),
new SqlParameter("@PKID", model.PKID),
};
object obj = SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, strSql.ToString(), param);
try
{
if (Convert.ToInt32(obj) > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 根据经销店编号查询经销店信息
/// </summary>
/// <param name="code">经销店编号</param>
/// <returns>返回值</returns>
public Common.Entity.Dealer GetDealerByCode(string code)
{
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
string sql = string.Format(@"select* from Dealer DL
left join (select * from(select c.大区ID, d.ParentID as 省份ID, d.PKID as 城市ID, c.大区 as 大区, c.省份 as 省份, d.City as 城市 from
(select b.PKID as 省份ID, b.ParentID as 大区ID, a.City as 大区, b.City as 省份 from
(select PKID, City from CityDictionary WHERE ParentID = '0') a,
CityDictionary b where a.PKID = b.ParentID) c,
CityDictionary d where c.省份ID = d.ParentID) e) CT on DL.CityId = CT.城市ID where DL.Code = @Code");
Common.Entity.Dealer model = new Common.Entity.Dealer();
DynamicParameters param = new DynamicParameters();
param.Add("Code", code);
model = conn.Query <Common.Entity.Dealer>(sql, param).FirstOrDefault();
return(model);
}
}
/// <summary>
/// 获取经销店实体对象
/// </summary>
/// <param name="dealerId">经销店ID</param>
/// <returns>返回DataTable</returns>
public Common.Entity.Dealer GetDealerByPKID(int dealerId)
{
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
string sql = string.Format(@"select * from Dealer DL
left join (select * from (select c.CountryID,d.ParentID,d.PKID as CityId, c.大区 as Country,c.省份 as Province,d.City as City from
(select b.PKID as ProvinceID,b.ParentID as CountryID,a.City as 大区,b.City as 省份 from
(select PKID,City from CityDictionary WHERE ParentID='0') a,
CityDictionary b where a.PKID=b.ParentID) c,
CityDictionary d where c.ProvinceID=d.ParentID) e) CT on DL.CityId=CT.CityId where DL.PKID=@PKID" );
Common.Entity.Dealer model = new Common.Entity.Dealer();
DynamicParameters param = new DynamicParameters();
param.Add("PKID", dealerId);
model = conn.Query <Common.Entity.Dealer>(sql, param).FirstOrDefault();
return(model);
}
}
/// <summary>
/// 获取经销商数据分页
/// </summary>
/// <param name="context">上下文对象</param>
public void GetDealerManagePager(HttpContext context)
{
int pageIndex = Convert.ToInt32(context.Request["page"]); // 当前页码
int pageSize = Convert.ToInt32(context.Request["rows"]); // 页码大小
int totalCount = 0;
var orderName = context.Request["sort"]; // 排序字段
var orderBy = context.Request["sortOrder"]; // 排序规则
var sort = sortInfo.GetSortOrder(orderName, orderBy);
List <Common.Entity.Dealer> list = new List <Common.Entity.Dealer>();
string code = context.Request.Form["Code"];
string name = context.Request.Form["Name"];
int countryId = Convert.ToInt32(context.Request["cboBigArea"]);
int proviceId = Convert.ToInt32(context.Request["cboProvince"]);
int cityId = Convert.ToInt32(context.Request["cboCity"]);
Common.Entity.Dealer filter = new Common.Entity.Dealer
{
Code = code,
Name = name,
CountryID = countryId,
ProvinceID = proviceId,
CityId = cityId
};
if (sort.SortName != null && !string.IsNullOrEmpty(sort.IsDesc))
{
list = new BLL.Dealer().GetDealerManagePager(pageIndex, pageSize, sort.SortName, sort.IsDesc, filter, out totalCount);
}
else
{
list = new BLL.Dealer().GetDealerManagePager(pageIndex, pageSize, filter, out totalCount);
}
var models = new BLL.BoostrapTableInfo <Common.Entity.Dealer>
{
total = totalCount,
rows = list
};
string resultJson = JsonConvert.SerializeObject(models);
context.Response.Write(resultJson);
}
/// <summary>
/// 新增一条数据
/// </summary>
/// <param name="model">实体对象</param>
/// <returns></returns>
public int Add(Common.Entity.Dealer model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Insert into Dealer(Code,Name,OldName,BearUserId,RegionManagerUserId,CityId,Address,ZipCode,Phone,fax,Email,SalesTel,ServerTel,SystemEmail,SalesDepartment,SalesDepartmentTel,MaxCommissioner,MaxCommissionerTel) Values");
strSql.Append("(@Code,@Name,@OldName,@BearUserId,@RegionManagerUserId,@CityId,@Address,@ZipCode,@Phone,@fax,@Email,@SalesTel,@ServerTel,@SystemEmail,@SalesDepartment,@SalesDepartmentTel,@MaxCommissioner,@MaxCommissionerTel)");
strSql.Append(";SELECT @@IDENTITY");// 返回插入用户的主键
SqlParameter[] param =
{
new SqlParameter("@Code", model.Code),
new SqlParameter("@Name", model.Name),
new SqlParameter("@OldName", model.OldName),
new SqlParameter("@BearUserId", model.BearUserId),
new SqlParameter("@RegionManagerUserId", model.RegionManagerUserId),
new SqlParameter("@CityId", model.CityId),
new SqlParameter("@Address", model.Address),
new SqlParameter("@ZipCode", model.ZipCode),
new SqlParameter("@Phone", model.Phone),
new SqlParameter("@fax", model.fax),
new SqlParameter("@Email", model.Email),
new SqlParameter("@SalesTel", model.SalesTel),
new SqlParameter("@ServerTel", model.ServerTel),
new SqlParameter("@SystemEmail", model.SystemEmail),
new SqlParameter("@SalesDepartment", model.SalesDepartment),
new SqlParameter("@SalesDepartmentTel", model.SalesDepartmentTel),
new SqlParameter("@MaxCommissioner", model.MaxCommissioner),
new SqlParameter("@MaxCommissionerTel", model.MaxCommissionerTel),
};
try
{
return(SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, strSql.ToString(), param));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 保存经销店信息
/// </summary>
/// <param name="context">上下文对象</param>
public void SaveDealer(HttpContext context)
{
int HidId = Convert.ToInt32(context.Request.Form["HidId"]);
BLL.Dealer bll = new BLL.Dealer();
string code = context.Request.Form["fr_Code"];
string name = context.Request.Form["fr_Name"];
string oldName = context.Request.Form["OldName"];
int BearUserId = Convert.ToInt32(context.Request.Form["BearUserId"]);
int RegionManagerUserId = Convert.ToInt32(context.Request.Form["RegionManagerUserId"]);
int cityId = Convert.ToInt32(context.Request.Form["cboCity"]);
string Address = context.Request.Form["Address"];
string ZipCode = context.Request.Form["ZipCode"];
string Phone = context.Request.Form["Phone"];
string fax = context.Request.Form["fax"];
string Email = context.Request.Form["Email"];
string SalesTel = context.Request.Form["SalesTel"];
string ServerTel = context.Request.Form["ServerTel"];
string SystemEmail = context.Request.Form["SystemEmail"];
string SalesDepartment = context.Request.Form["SalesDepartment"];
string SalesDepartmentTel = context.Request.Form["SalesDepartmentTel"];
string MaxCommissioner = context.Request.Form["MaxCommissioner"];
string MaxCommissionerTel = context.Request.Form["MaxCommissionerTel"];
Common.Entity.Dealer model = new Common.Entity.Dealer();
if (HidId > 0)
{
// 修改操作
model.PKID = HidId;
model.Code = code;
model.Name = name;
model.OldName = oldName;
model.BearUserId = BearUserId;
model.RegionManagerUserId = RegionManagerUserId;
model.CityId = cityId;
model.Address = Address;
model.ZipCode = ZipCode;
model.Phone = Phone;
model.fax = fax;
model.Email = Email;
model.SalesTel = SalesTel;
model.ServerTel = ServerTel;
model.SystemEmail = SystemEmail;
model.SalesDepartment = SalesDepartment;
model.SalesDepartmentTel = SalesDepartmentTel;
model.MaxCommissioner = MaxCommissioner;
model.MaxCommissionerTel = MaxCommissionerTel;
bool result = bll.Update(model);
if (result)
{
context.Response.Write("{\"msg\":\"保存成功。\",\"success\":true}");
}
else
{
context.Response.Write("{\"msg\":\"保存失败。\",\"success\":false}");
}
}
else
{
// 新增操作
bool isExistDealerCodeAndName = bll.IsExistsDealerCodeAndName(code, name);
model.Code = code;
model.Name = name;
model.OldName = oldName;
model.BearUserId = BearUserId;
model.RegionManagerUserId = RegionManagerUserId;
model.CityId = cityId;
model.Address = Address;
model.ZipCode = ZipCode;
model.Phone = Phone;
model.fax = fax;
model.Email = Email;
model.SalesTel = SalesTel;
model.ServerTel = ServerTel;
model.SystemEmail = SystemEmail;
model.SalesDepartment = SalesDepartment;
model.SalesDepartmentTel = SalesDepartmentTel;
model.MaxCommissioner = MaxCommissioner;
model.MaxCommissionerTel = MaxCommissionerTel;
if (isExistDealerCodeAndName)
{
context.Response.Write("{\"msg\":\"经销店编号或经销店名称已存在，请修改。\",\"state\":\"T\"}");
}
else if (bll.Add(model) > 0)
{
context.Response.Write("{\"msg\":\"保存成功。\",\"success\":true}");
}
else
{
context.Response.Write("{\"msg\":\"保存失败。\",\"success\":false}");
}
}
}
/// <summary>
/// 修改一条记录
/// </summary>
/// <param name="model">实体类</param>
/// <returns></returns>
public bool Update(Common.Entity.Dealer model)
{
return(new DAL.Dealer().Update(model));
}
/// <summary>
/// 新增一条数据
/// </summary>
/// <param name="model">实体对象</param>
/// <returns></returns>
public int Add(Common.Entity.Dealer model)
{
return(new DAL.Dealer().Add(model));
}
/// <summary>
/// 获取经销商分页列表(当按排序查询时)
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">页码大小</param>
/// <param name="totalCount">总记录数</param>
/// <returns></returns>
public List <Common.Entity.Dealer> GetDealerManagePager(int pageIndex, int pageSize, string sortName, string IsDesc, Common.Entity.Dealer filter, out int totalCount)
{
using (IDbConnection conn = new SqlConnection(SqlHelper.connStr))
{
StringBuilder sqlCount = new StringBuilder();
sqlCount.Append(@"select count(@pageSize) from Dealer DL left join (select * from (select c.大区ID,d.ParentID as 省份ID,d.PKID as 城市ID, c.大区 as 大区,c.省份 as 省份,d.City as 城市 from
(select b.PKID as 省份ID,b.ParentID as 大区ID,a.City as 大区,b.City as 省份 from (select PKID,City from CityDictionary WHERE ParentID='0') a,
CityDictionary b where a.PKID=b.ParentID) c,
CityDictionary d where c.省份ID=d.ParentID) e) CT on DL.CityId=CT.城市ID where DL.PKID>0 ");
if (!string.IsNullOrEmpty(filter.Code))
{
sqlCount.Append(" and DL.Code like'%" + filter.Code + "%'");
}
if (!string.IsNullOrEmpty(filter.Name))
{
sqlCount.Append(" and DL.Name like'%" + filter.Name + "%'");
}
if (filter.CountryID > 0)
{
sqlCount.Append(" and CT.大区ID=@大区ID");
}
if (filter.ProvinceID > 0)
{
sqlCount.Append(" and CT.省份ID=@省份ID");
}
if (filter.CityId > 0)
{
sqlCount.Append(" and DL.CityId=@CityId");
}
DynamicParameters param1 = new DynamicParameters();
param1.Add("pageSize", pageSize);
param1.Add("大区ID", filter.CountryID);
param1.Add("省份ID", filter.ProvinceID);
param1.Add("CityId", filter.CityId);
totalCount = conn.Query <int>(sqlCount.ToString(), param1).FirstOrDefault(); // 输出总记录数
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select top (@pageSize) * from (select row_number() over (order by Name) as rowNumber,CT.城市ID,CT.City,CT.Province,CT.省份ID,CT.Country,CT.大区ID,DL.* from Dealer DL
left join (select * from (select c.大区ID,d.ParentID as 省份ID,d.PKID as 城市ID, c.大区 as Country,c.省份 as Province,d.City as City from
(select b.PKID as 省份ID,b.ParentID as 大区ID,a.City as 大区,b.City as 省份 from
(select PKID,City from CityDictionary WHERE ParentID='0') a,
CityDictionary b where a.PKID=b.ParentID) c,
CityDictionary d where c.省份ID=d.ParentID) e) CT
on DL.CityId=CT.城市ID where DL.PKID>0" );
if (!string.IsNullOrEmpty(filter.Code))
{
strSql.Append(" and DL.Code like'%" + filter.Code + "%'");
}
if (!string.IsNullOrEmpty(filter.Name))
{
strSql.Append(" and DL.Name like'%" + filter.Name + "%'");
}
if (filter.CountryID > 0)
{
strSql.Append(" and CT.大区ID=@大区ID");
}
if (filter.ProvinceID > 0)
{
strSql.Append(" and CT.省份ID=@省份ID");
}
if (filter.CityId > 0)
{
strSql.Append(" and CT.城市ID=@城市ID");
}
strSql.Append($" ) as t where t.rowNumber > (@pageIndex - 1) * @pageSize order by t.{sortName} {IsDesc}");
DynamicParameters param2 = new DynamicParameters();
param2.Add("pageSize", pageSize);
param2.Add("pageIndex", pageIndex);
param2.Add("大区ID", filter.CountryID);
param2.Add("省份ID", filter.ProvinceID);
param2.Add("城市ID", filter.CityId);
List <Common.Entity.Dealer> list = conn.Query <Common.Entity.Dealer>(strSql.ToString(), param2).ToList();
return(list);
};
}
