CRM客户关系管理系统（十三）

CRM客户关系管理系统（十三）

---客户资料添加

1.事件流程：

-----增加的存储过程ALTER PROCEDURE [dbo].[BioCrmCustomer_ADD]@CustomerName nvarchar(100),@JuridicalPerson nvarchar(100),@Address nvarchar(100),@Tel1 nvarchar(20),@Tel2 nvarchar(20),@Fax nvarchar(20),@Email nvarchar(100),@WebSite nvarchar(100),@RegisterTime datetime,@UserID int,@CustomerLevel nvarchar(50),@NextTime datetime,@Source nvarchar(100),@Type nvarchar(100),@Vocation nvarchar(100),@State nvarchar(50),@Property nvarchar(100),@Area nvarchar(100),@Province nvarchar(20),@City nvarchar(100),@Bank nvarchar(50),@Accounts nvarchar(100),@TaxNumber nvarchar(100),@Credit nvarchar(100),@Integral int,@Remark nvarchar(200) AS INSERT INTO [BioCrmCustomer]( [CustomerName],[JuridicalPerson],[Address],[Tel1],[Tel2],[Fax],[Email],[WebSite],[RegisterTime],[UserID],[CustomerLevel],[NextTime],[Source],[Type],[Vocation],[State],[Property],[Area],[Province],[City],[Bank],[Accounts],[TaxNumber],[Credit],[Integral],[Remark] )VALUES( @CustomerName,@JuridicalPerson,@Address,@Tel1,@Tel2,@Fax,@Email,@WebSite,@RegisterTime,@UserID,@CustomerLevel,@NextTime,@Source,@Type,@Vocation,@State,@Property,@Area,@Province,@City,@Bank,@Accounts,@TaxNumber,@Credit,@Integral,@Remark )

修改的存储过程：

ALTER PROCEDURE [dbo].[BioCrmCustomer_Update]@CustomerID int,@CustomerNumber nvarchar(80),@CustomerName nvarchar(100),@JuridicalPerson nvarchar(100),@Address nvarchar(100),@Tel1 nvarchar(20),@Tel2 nvarchar(20),@Fax nvarchar(20),@Email nvarchar(100),@WebSite nvarchar(100),@RegisterTime datetime,@UserID int,@CustomerLevel nvarchar(50),@NextTime datetime,@Source nvarchar(100),@Type nvarchar(100),@Vocation nvarchar(100),@State nvarchar(50),@Property nvarchar(100),@Area nvarchar(100),@Province nvarchar(20),@City nvarchar(100),@Bank nvarchar(50),@Accounts nvarchar(100),@TaxNumber nvarchar(100),@Credit nvarchar(100),@Integral int,@Remark nvarchar(200),@DeleteState bit AS ---- UPDATE [BioCrmCustomer] SET [CustomerNumber] = @CustomerNumber,[CustomerName] = @CustomerName,[JuridicalPerson] = @JuridicalPerson,[Address] = @Address,[Tel1] = @Tel1,[Tel2] = @Tel2,[Fax] = @Fax,[Email] = @Email,[WebSite] = @WebSite,[RegisterTime] = @RegisterTime,[UserID] = @UserID,[CustomerLevel] = @CustomerLevel,[NextTime] = @NextTime,[Source] = @Source,[Type] = @Type,[Vocation] = @Vocation,[State] = @State,[Property] = @Property,[Area] = @Area,[Province] = @Province,[City] = @City,[Bank] = @Bank,[Accounts] = @Accounts,[TaxNumber] = @TaxNumber,[Credit] = @Credit,[Integral] = @Integral,[Remark] = @Remark,[DeleteState] = @DeleteState WHERE CustomerID=@CustomerID

BLL层的代码：

public class BioCrmCustomerBLL { ///

/// 添加一个客户资料 ///

/// BioCrmCustomer /// int public int CustomerAdd(BioCrmCustomer customer) { SqlParameter[] parameters = { new SqlParameter("@CustomerName", SqlDbType.NVarChar,100), new SqlParameter("@JuridicalPerson", SqlDbType.NVarChar,100), new SqlParameter("@Address", SqlDbType.NVarChar,100), new SqlParameter("@Tel1", SqlDbType.NVarChar,20), new SqlParameter("@Tel2", SqlDbType.NVarChar,20), new SqlParameter("@Fax", SqlDbType.NVarChar,20), new SqlParameter("@Email", SqlDbType.NVarChar,100), new SqlParameter("@WebSite", SqlDbType.NVarChar,100), new SqlParameter("@RegisterTime", SqlDbType.DateTime), new SqlParameter("@UserID", SqlDbType.Int,4), new SqlParameter("@CustomerLevel", SqlDbType.NVarChar,50), new SqlParameter("@NextTime", SqlDbType.DateTime), new SqlParameter("@Source", SqlDbType.NVarChar,100), new SqlParameter("@Type", SqlDbType.NVarChar,100), new SqlParameter("@Vocation", SqlDbType.NVarChar,100), new SqlParameter("@State", SqlDbType.NVarChar,50), new SqlParameter("@Property", SqlDbType.NVarChar,100), new SqlParameter("@Area", SqlDbType.NVarChar,100), new SqlParameter("@Province", SqlDbType.NVarChar,20), new SqlParameter("@City", SqlDbType.NVarChar,100), new SqlParameter("@Bank", SqlDbType.NVarChar,50), new SqlParameter("@Accounts", SqlDbType.NVarChar,100), new SqlParameter("@TaxNumber", SqlDbType.NVarChar,100), new SqlParameter("@Credit", SqlDbType.NVarChar,100), new SqlParameter("@Integral", SqlDbType.Int,4), new SqlParameter("@Remark", SqlDbType.NVarChar,200), }; parameters[0].Value = customer.CustomerName; parameters[1].Value = customer.JuridicalPerson; parameters[2].Value = customer.Address; parameters[3].Value = customer.Tel1; parameters[4].Value = customer.Tel2; parameters[5].Value = customer.Fax; parameters[6].Value = customer.Email; parameters[7].Value = customer.WebSite; parameters[8].Value = customer.RegisterTime; parameters[9].Value = customer.UserID; parameters[10].Value = customer.CustomerLevel; parameters[11].Value = customer.NextTime; parameters[12].Value = customer.Source; parameters[13].Value = customer.Type; parameters[14].Value = customer.Vocation; parameters[15].Value = customer.State; parameters[16].Value = customer.Property; parameters[17].Value = customer.Area; parameters[18].Value = customer.Province; parameters[19].Value = customer.City; parameters[20].Value = customer.Bank; parameters[21].Value = customer.Accounts; parameters[22].Value = customer.TaxNumber; parameters[23].Value = customer.Credit; parameters[24].Value = customer.Integral; parameters[25].Value = customer.Remark; return DataBaseHelper.ExcuteSqlReturnInt("BioCrmCustomer_ADD", CommandType.StoredProcedure, parameters); } ///

/// 修改客户资料 ///

/// /// public int CustomerUpdate(BioCrmCustomer model) { SqlParameter[] parameters = { new SqlParameter("@CustomerID", SqlDbType.Int,4), new SqlParameter("@CustomerNumber", SqlDbType.NVarChar,80), new SqlParameter("@CustomerName", SqlDbType.NVarChar,100), new SqlParameter("@JuridicalPerson", SqlDbType.NVarChar,100), new SqlParameter("@Address", SqlDbType.NVarChar,100), new SqlParameter("@Tel1", SqlDbType.NVarChar,20), new SqlParameter("@Tel2", SqlDbType.NVarChar,20), new SqlParameter("@Fax", SqlDbType.NVarChar,20), new SqlParameter("@Email", SqlDbType.NVarChar,100), new SqlParameter("@WebSite", SqlDbType.NVarChar,100), new SqlParameter("@RegisterTime", SqlDbType.DateTime), new SqlParameter("@UserID", SqlDbType.Int,4), new SqlParameter("@CustomerLevel", SqlDbType.NVarChar,50), new SqlParameter("@NextTime", SqlDbType.DateTime), new SqlParameter("@Source", SqlDbType.NVarChar,100), new SqlParameter("@Type", SqlDbType.NVarChar,100), new SqlParameter("@Vocation", SqlDbType.NVarChar,100), new SqlParameter("@State", SqlDbType.NVarChar,50), new SqlParameter("@Property", SqlDbType.NVarChar,100), new SqlParameter("@Area", SqlDbType.NVarChar,100), new SqlParameter("@Province", SqlDbType.NVarChar,20), new SqlParameter("@City", SqlDbType.NVarChar,100), new SqlParameter("@Bank", SqlDbType.NVarChar,50), new SqlParameter("@Accounts", SqlDbType.NVarChar,100), new SqlParameter("@TaxNumber", SqlDbType.NVarChar,100), new SqlParameter("@Credit", SqlDbType.NVarChar,100), new SqlParameter("@Integral", SqlDbType.Int,4), new SqlParameter("@Remark", SqlDbType.NVarChar,200), new SqlParameter("@DeleteState", SqlDbType.Bit,1)}; parameters[0].Value = model.CustomerID; parameters[1].Value = model.CustomerNumber; parameters[2].Value = model.CustomerName; parameters[3].Value = model.JuridicalPerson; parameters[4].Value = model.Address; parameters[5].Value = model.Tel1; parameters[6].Value = model.Tel2; parameters[7].Value = model.Fax; parameters[8].Value = model.Email; parameters[9].Value = model.WebSite; parameters[10].Value = model.RegisterTime; parameters[11].Value = model.UserID; parameters[12].Value = model.CustomerLevel; parameters[13].Value = model.NextTime; parameters[14].Value = model.Source; parameters[15].Value = model.Type; parameters[16].Value = model.Vocation; parameters[17].Value = model.State; parameters[18].Value = model.Property; parameters[19].Value = model.Area; parameters[20].Value = model.Province; parameters[21].Value = model.City; parameters[22].Value = model.Bank; parameters[23].Value = model.Accounts; parameters[24].Value = model.TaxNumber; parameters[25].Value = model.Credit; parameters[26].Value = model.Integral; parameters[27].Value = model.Remark; parameters[28].Value = model.DeleteState; return DataBaseHelper.ExcuteSqlReturnInt("BioCrmCustomer_Update", CommandType.StoredProcedure, parameters); } ///

/// 根据客户id获取客户信息 ///

/// id /// BioCrmCustomer public BioCrmCustomer GetCustomerByID(string id) { SqlDataReader reader= SqlComm.GetDataReaderByCondition("BioCrmCustomer", "*", " CustomerID=" + id); BioCrmCustomer customer = new BioCrmCustomer(); while (reader.Read()) { customer.CustomerID = int.Parse(reader["CustomerID"].ToString()); customer.CustomerNumber = reader["CustomerNumber"].ToString(); customer.CustomerName= reader["CustomerNumber"].ToString(); customer.JuridicalPerson = reader["JuridicalPerson"].ToString(); customer.Address = reader["Address"].ToString(); customer.Tel1 = reader["Tel1"].ToString(); customer.Tel2 = reader["Tel2"].ToString(); customer.Fax = reader["Fax"].ToString(); customer.Email = reader["Email"].ToString(); customer.WebSite = reader["WebSite"].ToString(); customer.RegisterTime =Convert.ToDateTime(reader["RegisterTime"].ToString()); customer.UserID = int.Parse(reader["UserID"].ToString()); customer.CustomerLevel = reader["CustomerLevel"].ToString(); customer.NextTime =Convert.ToDateTime(reader["NextTime"].ToString()); customer.Source = reader["Source"].ToString(); customer.Type = reader["Type"].ToString(); customer.Vocation = reader["Vocation"].ToString(); customer.State = reader["State"].ToString(); customer.Property = reader["Property"].ToString(); customer.Area = reader["Area"].ToString(); customer.Province = reader["Province"].ToString(); customer.City = reader["City"].ToString(); customer.Bank = reader["Bank"].ToString(); customer.Accounts = reader["Accounts"].ToString(); customer.TaxNumber = reader["TaxNumber"].ToString(); customer.Credit = reader["Credit"].ToString(); customer.Integral =int.Parse( reader["Integral"].ToString()); customer.Remark = reader["Remark"].ToString(); customer.DeleteState =Convert.ToBoolean(reader["DeleteState"].ToString()); } reader.Close(); return customer; } }

获取省份的代码：

///

/// 获取省份列表 ///

/// public static DataTable getProvinceInfoList() { SqlParameter[] pars = new SqlParameter[]{ new SqlParameter("@tableName","tbProvinceInfo"), new SqlParameter("@columns","*") }; return DataBaseHelper.SelectSQLReturnDataSet("getDataByTableNameValue", CommandType.StoredProcedure, pars).Tables[0]; } ///

/// 根据省份ID获取对应的城市 ///

/// /// public static DataTable getCityListByProvinceID(string ProvinceID) { SqlParameter[] pars = new SqlParameter[]{ new SqlParameter("@tableName","tbCityInfo"), new SqlParameter("@columns"," * "), new SqlParameter("@condition"," ProvinceInfoID ="+ProvinceID) }; return DataBaseHelper.SelectSQLReturnDataSet("GetDataByCondition", CommandType.StoredProcedure, pars).Tables[0]; }

存储过程：

ALTER PROCEDURE [dbo].[getDataByTableNameValue] @tableName nvarchar(500), @columns nvarchar(2000)ASBEGIN SET NOCOUNT ON; DECLARE @Sql nvarchar(4000) SET @Sql='select '+@columns+' from '+@tableName EXEC(@Sql)END----根据指定列，指定条件，指定表查询数据ALTER PROCEDURE [dbo].[GetDataByCondition] @tableName nvarchar(200), @columns nvarchar(500), @condition nvarchar(500)=' 1=1' ASBEGIN SET NOCOUNT ON; DECLARE @sqlStr nvarchar(2000) SET @sqlStr='select '+@columns+' from '+@tableName+' where '+@condition EXEC(@sqlStr) END

触发器的定义：

触发器是一种DBMS响应特殊表或列的INSERT，UPDATE,DELETE操作的特殊类型的存储过程

自动生成客户编号的触发器：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[CreateCustomerNoByCustomerID] ON [dbo].[BioCrmCustomer] AFTER INSERTAS BEGIN DECLARE @str nvarchar(10) DECLARE @CustomerNo nvarchar(50) DECLARE @CustomerID nvarchar(50) SET @str='0000000' SELECT @CustomerID=MAX(CustomerID) FROM dbo.BioCrmCustomer SET @CustomerNo=RIGHT(@str+@CustomerID,7) UPDATE dbo.BioCrmCustomer SET CustomerNumber=@CustomerNo WHERE CustomerID=@CustomerIDend

添加的前台页面：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CustomerAdd.aspx.cs" Inherits="BioErpWeb.CRMSystem.CustomerAdd" %><%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="cc1" %>

客户信息添加
客户名		法人代表
企业地址		传真
电话号码1		电话号码2
Email邮件		公司网址
注册时间		所属销售代表
税号		下次联系时间
客户状态	良好 一般	客户来源	国内 国外
客户类型	国有 民营 外资 合资 个体	所属行业	制造 服务 教育 医疗
客户性质	高端客户 中端客户 低端客户	客户等级	一级 二级 三级 四级
省份 城市
开户银行		银行账号
积分		信用级别
所属区域	东 西 南 北
备注：

后台代码：

public partial class CustomerAdd : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { ddlProvinceBind(); } } public void ddlProvinceBind() { this.ddlProvince.DataSource = SqlComm.getProvinceInfoList(); this.ddlProvince.DataTextField = "ProvinceInfoName"; this.ddlProvince.DataValueField = "ProvinceInfoID"; this.ddlProvince.DataBind(); this.ddlProvince.Items.Add(new ListItem("--请选择省份--","0")); this.ddlProvince.SelectedValue="0"; } protected void btnSubmit_Click(object sender, EventArgs e) { BioCrmCustomer customer = new BioCrmCustomer(); customer.CustomerName = this.txtCustomerName.Text; customer.JuridicalPerson = this.txtJuridicalPerson.Text; customer.Address = this.txtAddress.Text; customer.Fax = this.txtFax.Text; customer.Tel1 = this.txtTel1.Text; customer.Tel2 = this.txtTel2.Text; customer.Email = this.txtEmail.Text; customer.WebSite = this.txtWebSite.Text; customer.RegisterTime = Convert.ToDateTime(this.txtRegisterTime.Text); customer.UserID = int.Parse(this.txtUserName.Text); customer.TaxNumber = this.txtTaxNumber.Text; customer.NextTime = Convert.ToDateTime(this.txtNextTime.Text); customer.State= this.ddlState.SelectedItem.Text; customer.Source = this.ddlState.SelectedItem.Text; customer.CustomerLevel = this.ddlCustomerLevel.SelectedItem.Text; customer.Property = this.ddlProperty.SelectedItem.Text; customer.Type = this.ddlType.SelectedItem.Text; customer.Vocation = this.ddlVocation.SelectedItem.Text; customer.Province = this.ddlProvince.SelectedItem.Text; customer.City = this.ddlCity.SelectedItem.Text; customer.Bank = this.txtBank.Text; customer.Accounts = this.txtAccounts.Text; customer.Integral =int.Parse(this.txtIntegral.Text.Trim()); customer.Credit = this.txtCredit.Text; customer.Remark = this.txtRemark.Text; customer.Area = this.ddlArea.SelectedValue; BioCrmCustomerBLL custormerbll = new BioCrmCustomerBLL(); if (custormerbll.CustomerAdd(customer) != 0) { ScriptManager.RegisterStartupScript(this, this.GetType(), "test", "alert('添加成功')", true); Server.Transfer("CustomerListShow.aspx"); } else { ScriptManager.RegisterStartupScript(this, this.GetType(), "test", "alert('添加失败')", true); Server.Transfer("CustomerListShow.aspx"); } } protected void ddlProvince_SelectedIndexChanged(object sender, EventArgs e) { if (this.ddlProvince.SelectedValue != "0") { string ProvinceId = this.ddlProvince.SelectedValue.ToString(); ddlCity.DataSource = SqlComm.getCityListByProvinceID(ProvinceId); ddlCity.DataTextField = "CityInfoName"; ddlCity.DataValueField = "CityInfoID"; ddlCity.DataBind(); } } }

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。