/// <summary>
/// 增加一条数据
/// <param name="model"></param>
/// </summary>
public void Add(Customer model)
{
using (DBHelper db = DBHelper.Create())
{
db.Insert<Customer>(model);
}
}
public JsonResult CustomerModify(Ajax.Model.Customer customer, List <CustomerChargeItem> ccItems)
{
AjaxResult result = new AjaxResult();
try
{
customer.UpdateTime = DateTime.Now;
customer.PY = Pinyin.GetPinyin(customer.Name);
//对应缴费项
foreach (CustomerChargeItem ccItem in ccItems)
{
ccItem.ID = Guid.NewGuid().ToString("N");
ccItem.CustomerID = customer.ID;
}
new CustomerRule().Modify(customer, ccItems);
result.Success = true;
result.Message = "客户信息修改成功。";
}
catch (Exception ex)
{
result.Success = false;
result.Message = ex.Message;
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
public ActionResult AddCustomer(Ajax.Model.Customer customer, List <CustomerChargeItem> ccItems)
{
AjaxResult result = new AjaxResult();
customer.ID = Guid.NewGuid().ToString("N");
customer.CreateTime = DateTime.Now;
customer.UpdateTime = DateTime.Now;
customer.PY = Pinyin.GetPinyin(customer.Name);
customer.Status = 0;//默认无效，需要审核
customer.OperatorID = MyTicket.CurrentTicket.EmployeeID;
customer.Code = 0;
//对应缴费项
foreach (CustomerChargeItem ccItem in ccItems)
{
ccItem.ID = WebHelper.GetNewGuidUpper();
ccItem.CustomerID = customer.ID;
}
try
{
new CustomerRule().Add(customer, ccItems);
result.Success = true;
result.Message = "客户添加成功";
}
catch (Exception ex)
{
result.Success = false;
result.Message = ex.Message;
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 新增客户重载
/// <param name="model"></param>
/// <param name="ccItemLists"></param>
/// </summary>
public void Add(Customer model, List<CustomerChargeItem> ccItemLists)
{
using (DBHelper db = DBHelper.Create())
{
db.BeginTransaction();
db.Insert<Customer>(model);
foreach (CustomerChargeItem item in ccItemLists)
{
db.Insert<CustomerChargeItem>(item);
}
db.Commit();
}
}
public ActionResult Search(EasyUIGridParamModel param, Ajax.Model.Customer customer)
{
int itemCount = 0;
CustomerRule cr = new CustomerRule();
List <dynamic> list = cr.Search(param, customer, out itemCount);
var showList = from customers in list
select new
{
ID = customers.ID,
Name = customers.NAME,
TypeName = customers.CUSTOMERTYPE,
Status = customers.STATUS.Replace("1", "正常").Replace("0", "未审核").Replace("2", "禁用").Replace("3", "已删除"),
CreateTime = Ajax.Common.TimeParser.FormatDateTime(customers.CREATETIME),
Contactor = customers.CONTACTOR,
FeeType = string.IsNullOrEmpty(customers.AGREEID) ? "非协议缴费" : "协议缴费",
Address = customers.ADDRESS
};
return(Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 获取收费协议json
/// </summary>
/// <param name="param"></param>
/// <param name="agree"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List <dynamic> SearchAgree(EasyUIGridParamModel param, Agreements agree, Ajax.Model.Customer c, out int itemCount)
{
return(new Ajax.DAL.AgreementsDAL().SearchContrast(param, agree, c, out itemCount));
}
/// <summary>
/// 更新客户信息重载
/// </summary>
/// <param name="model">客户基本信息</param>
/// <param name="ccItemLists">客户对应缴费项</param>
/// <returns></returns>
public bool Update(Customer model, List<CustomerChargeItem> ccItemLists)
{
using (DBHelper db = DBHelper.Create())
{
db.BeginTransaction();
db.Update<Customer>(model);
string sql = "delete from T_CustomerChargeItem where customerID=@ID";
Dictionary<string, object> paramList = new Dictionary<string, object>();
paramList.Add("ID", model.ID);
db.ExecuteNonQuery(sql, CommandType.Text, paramList);
foreach (CustomerChargeItem item in ccItemLists)
{
db.Insert<CustomerChargeItem>(item);
}
db.Commit();
return true;
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Customer model)
{
using (DBHelper db = DBHelper.Create())
{
db.Update<Customer>(model);
return true;
}
}
/// <summary>
/// 客户查询
/// </summary>
/// <param name="pageModel">分页参数</param>
/// <param name="customer">查询实体</param>
/// <param name="itemCount">查询结果总数</param>
/// <returns></returns>
public List<dynamic> Search(EasyUIGridParamModel pageModel, Customer customer, out int itemCount)
{
Dictionary<string, object> param = new Dictionary<string, object>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select tc.ID,tc.Name,tct.Name as customerType,tc.createTime,tc.Status,
Contactor,Address,a.ID as agreeID
FROM T_Customer tc
left join T_CustomerType tct on tc.TypeID=tct.ID
left join T_Agreements a on a.CustomerID=tc.ID and a.status=1
where 1=1 ");
//前台三个查询条件
if (!string.IsNullOrEmpty(customer.Name))
{
string str = string.Format("%{0}%", customer.Name.ToUpper());
strSql.Append(" and (tc.Name like @name or tc.PY like @PY)");
param.Add("name", str);
param.Add("PY", str);
}
if (customer.Status != -1)
{
strSql.Append(" and tc.status=@status ");
param.Add("status", customer.Status);
}
if (!string.IsNullOrEmpty(customer.AreaID))
{
strSql.Append(" and tc.areaID=@areaID ");
param.Add("status", customer.AreaID);
}
//分页信息
int pageIndex = Convert.ToInt32(pageModel.page) - 1;
int pageSize = Convert.ToInt32(pageModel.rows);
using (DBHelper db = DBHelper.Create())
{
string sql = strSql.ToString();
itemCount = db.GetCount(string.Format(DBHelper.StrGetCountSql, sql), param);
return db.GetDynaminObjectList(sql, pageIndex, pageSize, "ID", param);
}
}
/// <summary>
/// 新增客户重载
/// </summary>
/// <param name="model">客户基本信息</param>
/// <param name="ccItems">客户对应缴费项</param>
public void Add(Ajax.Model.Customer model, List <CustomerChargeItem> ccItems)
{
dal.Add(model, ccItems);
}
/// <summary>
/// 批量删除数据
/// </summary>
public bool DeleteList(string IDlist)
{
using (DBHelper db = DBHelper.Create())
{
db.BeginTransaction();
Customer customer = new Customer();
foreach (string item in IDlist.TrimEnd(',').Split(','))
{
customer = db.GetById<Customer>(item);
customer.Status = 2;
db.Update<Customer>(customer);
}
db.Commit();
return true;
}
}
/// <summary>
/// 客户信息的分页查询
/// </summary>
/// <param name="pageModel">分页信息</param>
/// <param name="c">保存查询信息的实体类</param>
/// <param name="itemCount">查询结果总数</param>
/// <returns></returns>
public List<dynamic> Search(EasyUIGridParamModel pageModel, Customer c, out int itemCount)
{
return dal.Search(pageModel, c, out itemCount);
}
/// <summary>
/// 客户信息修改
/// </summary>
/// <param name="customer">客户基本信息</param>
/// <param name="ccItems">客户对应缴费项</param>
/// <returns></returns>
public bool Modify(Customer customer, List<CustomerChargeItem> ccItems)
{
return dal.Update(customer, ccItems);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Ajax.Model.Customer model)
{
return(dal.Update(model));
}
/// <summary>
/// 获取收费协议json
/// </summary>
/// <param name="param"></param>
/// <param name="agree"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> SearchContrast(EasyUIGridParamModel param, Agreements agree, Customer c, out int itemCount)
{
List<SqlParameter> paramList = new List<SqlParameter>();
StringBuilder StrSql = new StringBuilder();
StrSql.Append(@"select a.ID,c.ID as customerID,c.Code,c.Name,a.Money,a.Status,
a.begindate,a.enddate,a.createtime,a.checktime,a.Code as agreementCode
from T_Agreements a
left join T_Customer c on c.ID=a.CustomerID
where 1=1 ");
Dictionary<string, object> paramDic = new Dictionary<string, object>();
if (!string.IsNullOrEmpty(agree.ID))
{
StrSql.Append("and a.ID=@ID ");
paramDic.Add("ID", agree.ID);
}
if (agree.BeginDate.ToString() != "0001/1/1 0:00:00" && agree.EndDate.ToString() != "0001/1/1 0:00:00")
{
StrSql.Append("and a.begindate>=@beginDate and a.enddate<=@endDate ");
paramDic.Add("beginDate", agree.BeginDate);
paramDic.Add("endDate", agree.EndDate);
}
if (agree.Status != -1)
{
StrSql.Append("and a.status=@status ");
paramDic.Add("status", agree.Status);
}
if (!string.IsNullOrEmpty(agree.CustomerID))
{
StrSql.Append("and c.ID=@customerID ");
paramDic.Add("customerID", agree.CustomerID);
}
if (!string.IsNullOrEmpty(c.Name))
{
StrSql.Append("and c.Name like @name ");
paramDic.Add("name", string.Format("%{0}%", c.Name));
}
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
using (DBHelper db = DBHelper.Create())
{
itemCount = db.GetCount(string.Format(DBHelper.StrGetCountSql, StrSql), paramDic);
return db.GetDynaminObjectList(StrSql.ToString(), pageIndex, pageSize, null, paramDic);
}
}
/// <summary>
/// 模糊查询
/// </summary>
/// <param name="filter"></param>
/// <param name="queryType">
/// 客户查询类型
/// 1：只查询子客户，2只查询父客户，为空时查询全部
/// </param>
/// <returns></returns>
public List<Ajax.Model.Customer> GetCustomerList(string filter, string queryType)
{
List<Customer> cList = new List<Customer>();
string namePY = Pinyin.GetPinyin(filter);
string isParent = string.Empty;
if (string.IsNullOrEmpty(queryType))
{
}
else if (queryType.Equals("2"))
{
isParent = "and Agent = 1";
}
else if (queryType.Equals("1"))
{
isParent = "and Agent = 0";
}
string strSql = "select ID,Name from T_Customer where (Name like @Name or py like @PY ) and status = 1 {0}";
strSql = string.Format(strSql, isParent);
Dictionary<string, object> param = new Dictionary<string, object>();
param.Add("Name", "%" + filter + "%");
param.Add("PY", "%" + namePY + "%");
using (DBHelper db = DBHelper.Create())
{
using (DbDataReader sdr = db.ExecuteReader(strSql, param))
{
while (sdr != null && sdr.Read())
{
Customer customer = new Customer();
customer.ID = sdr["ID"].ToString();
customer.Name = sdr["Name"].ToString();
cList.Add(customer);
}
}
}
return cList;
}
public ActionResult SearchAgree(EasyUIGridParamModel param, Agreements agree, Ajax.Model.Customer c)
{
int itemCount = 0;
List <dynamic> agreeList = new AgreementsRule().SearchAgree(param, agree, c, out itemCount);
var showList = from a in agreeList
select new
{
ID = a.ID,
CONTRASTID = a.ID,
CUSTOMERID = a.CUSTOMERID,
CODE = a.CODE,
NAME = a.NAME,
MONEY = a.MONEY,
BEGINDATE = a.BEGINDATE,
AGREEMENTCODE = a.AGREEMENTCODE,
ENDDATE = TimeParser.FormatDateTime(a.ENDDATE),
STATUS = Convert.ToString(a.STATUS).Replace("0", "未审核").Replace("1", "已审核").Replace("2", "已删除")
};
return(Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 新增客户
/// </summary>
public void Add(Ajax.Model.Customer model)
{
dal.Add(model);
}