CRM客户关系管理系统 ——客户联系人添加(十五)
需求描述:
1.业务员自己可以查看属于自己的客户信息
2.客服部经理可以查看查看所有客户信息
3.其他人员不得查看客户信息
效果截图:
前台代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="LinkManAdd.aspx.cs" Inherits="BioErpWeb.CRMSystem.LinkManAdd" %> <%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="cc1" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <link href="../Styles/ERPBaseStyle.css" rel="stylesheet" type="text/css" /> <link href="../Styles/CalenderStyle.css" rel="stylesheet" type="text/css" /> <script src="../JS/CustomerName.js" type="text/javascript"></script> <script src="../Scripts/jquery-1.4.1.min.js" type="text/javascript"></script> <style type="text/css"> .style1 { height: 22px; } .style3 { width: 252px; } .style4 { width: 94px; } .style5 { width: 71px; } </style> <script type="text/javascript"> $(document).ready(function () { $("#btnSubmit").click(function () { var customer = $("#txtCustomerName"); if (customer.val() == '') { alert('必须填写客户名称'); return false; } var JuridicalPerson = $("#txtJuridicalPerson"); if (JuridicalPerson.val() == '') { alert('必须填写法人代表'); return false; } var txtRegisterTime = $("#txtRegisterTime"); if (txtRegisterTime.val() == '') { alert('公司注册时间必须填写'); txtRegisterTime.focus(); return false; } var txtNextTime = $("#txtNextTime"); if (txtNextTime.val() == '') { alert('下次联系时间必须填写'); txtNextTime.focus(); return false; } var txtUserName = $("#txtUserName"); if (txtUserName.val() == '') { alert('所属销售代表'); txtRegisterTime.focus(); return false; } var ddlProvince = $("#ddlProvince"); var selectCaption = ddlProvince.select(); if (selectCaption.val() == 0) { alert("请选择所属省份"); ddlProvince.focus(); return false; } var ddlCity = $("#ddlCity"); var selectCityCaption = ddlCity.select(); if (selectCityCaption.val() =='') { alert("请选择所属城市"); ddlCity.focus(); return false; } var txtIntegral = $("#txtIntegral"); if (txtIntegral.val() == '') { alert('请填写用户积分'); txtIntegral.focus(); return false; } return true; }); }); </script> </head> <body> <form id="form1" runat="server"> <asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager> <div> <table class="maintable"> <tr> <td colspan="4" class="titlebar">客户<span>联系人信息添加</span></td> </tr> <tr> <td>联系人姓名</td><td><asp:TextBox ID="txtLinkmanName" runat="server"></asp:TextBox></td> <td>联系人昵称</td><td><asp:TextBox ID="txtNickname" runat="server"></asp:TextBox></td> </tr> <tr> <td>性别</td><td> <asp:DropDownList ID="ddlSex" runat="server"> <asp:ListItem Value="0">男</asp:ListItem> <asp:ListItem Value="1">女</asp:ListItem> </asp:DropDownList> </td> <td>生日</td><td><asp:TextBox ID="txtBirthday" runat="server"></asp:TextBox></td> </tr> <tr> <td>部门</td><td><asp:TextBox ID="txtDepartment" runat="server"></asp:TextBox></td> <td>职务</td><td><asp:TextBox ID="txtHeadship" runat="server"></asp:TextBox></td> </tr> <tr> <td>是否是主联系人</td><td> <asp:DropDownList ID="ddlMainMan" runat="server"> <asp:ListItem Value="1">是</asp:ListItem> <asp:ListItem Value="0">否</asp:ListItem> </asp:DropDownList> </td> <td>工作电话</td><td><asp:TextBox ID="txtWorkPhone" runat="server"></asp:TextBox></td> </tr> <tr> <td> 个人手机 </td> <td> <asp:TextBox ID="txtMobile" runat="server"></asp:TextBox> </td> <td> Email </td> <td> <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox> </td> </tr> <tr> <td> 住址 </td> <td> <asp:TextBox ID="txtAddress" runat="server"></asp:TextBox> </td> <td> 邮编 </td> <td> <asp:TextBox ID="txtPostcode" runat="server"></asp:TextBox> </td> </tr> <tr> <td> QQ号码 </td> <td> <asp:TextBox ID="txtQQ" runat="server"></asp:TextBox> </td> <td> MSN </td> <td> <asp:TextBox ID="txtMsN" runat="server"></asp:TextBox> </td> </tr> <tr> <td> 所属客户 </td> <td colspan="3"> <asp:TextBox ID="txtCustomerID" runat="server"></asp:TextBox><input type="button" value="选择客户" style=" width:100px;" onclick="showCustomerDialog()" /> </td> </tr> <tr> <td colspan="4" class="bottomtd"> <asp:Button ID="btnSubmit" runat="server" Text="客户联系人添加" onclick="btnSubmit_Click" /> <asp:Button ID="btnReturn" runat="server" Text="返回列表" onclick="btnReturn_Click" /> </td> </tr> </table> <br /> </div> </form> </body> </html>
跳转到查询页面:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CustomerListSelect.aspx.cs" Inherits="BioErpWeb.PersonList.CustomerListSelect" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <select multiple="multiple" id="sltUserList" style=" width:80px; height:270px; margin:0px;"> <%=this.GetUserList%> </select> </div> </form> </body> </html>
组装查询的代码:
public string GetUserList { get { string strHtml = ""; if (Request.QueryString["Customer"] == null) { DataSet ds = SqlComm.GetDataByCondition("BioCrmCustomer", "top 10 CustomerID,CustomerName", " 1=1 order by CustomerID"); foreach (DataRow dr in ds.Tables[0].Rows) { strHtml += "<option value='" + dr["CustomerID"] + "'>" + dr["CustomerName"] + "</option>"; } } else { DataSet ds = SqlComm.GetDataByCondition("BioCrmCustomer", "top 10 CustomerID,CustomerName", " CustomerName like '" + Request.QueryString["Customer"].ToString() + "%' order by CustomerID"); foreach (DataRow dr in ds.Tables[0].Rows) { strHtml += "<option value='" + dr["CustomerID"] + "'>" + dr["CustomerName"] + "</option>"; } } return strHtml; } }
员工的查询页面:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CustomerList.aspx.cs" Inherits="BioErpWeb.PersonList.CustomerList" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <script type="text/javascript"> function search() { if (document.getElementById("txtSearchName").value == "") { alert("请输入员工姓名"); return; } document.getElementById("PersonIframeList").src = "CustomerListSelect.aspx?Customer=" + encodeURIComponent(document.getElementById("txtSearchName").value); } function choose() { window.returnValue = PersonIframeList.document.getElementById("sltUserList").value; window.close(); } </script> <style type="text/css"> .style1 { width: 157px; } </style> </head> <body> <form id="form1" runat="server"> <div> <table style=" width:310px; "> <tr> <td class="style1">员工姓名 </td> <td> <input id="txtSearchName" name="txtSearchName" type="text" size="10" maxlength="10"/> <input type="button" value="查询" onclick="search()" /> </td> </tr> <tr> <td class="style1"> <iframe width="120px" id="PersonIframeList" frameborder=0 src="CustomerListSelect.aspx" height="310px" > </iframe> </td> <td style=" vertical-align:bottom; padding-bottom:20px;"> <input type="button" value="选择" onclick="choose()"/> </td> </tr> </table> </div> </form> </body> </html>
JS的代码:
//人员选择对话框 function showCustomerDialog() { var re = showModalDialog("../PersonList/CustomerList.aspx", "", "dialogWidth=320px;dialogHeight=350px"); if (re == "" || re == null) { if (document.getElementById("txtCustomerID").value == "" || document.getElementById("txtCustomerID").value == null) { document.getElementById("txtCustomerID").value = "请选择"; } } else { document.getElementById("txtCustomerID").value = re; } }
添加联系人的BLL层代码:
/// <summary> /// 添加一条联系人记录 /// </summary> /// <param name="LinkMan">BioCrmLinkmanInfo对象</param> /// <returns>int</returns> public int LinkmanInfoADD( BioCrmLinkmanInfo LinkMan) { SqlParameter[] pars = new SqlParameter[]{ new SqlParameter("@LinkmanName",LinkMan.LinkmanName), new SqlParameter("@Nickname",LinkMan.Nickname), new SqlParameter("@Sex",LinkMan.Sex), new SqlParameter("@Birthday",LinkMan.Birthday), new SqlParameter("@LinkmanNumber",LinkMan.LinkmanNumber), new SqlParameter("@Department",LinkMan.Department), new SqlParameter("@Headship",LinkMan.Headship), new SqlParameter("@IsMain",LinkMan.IsMain), new SqlParameter("@WorkPhone",LinkMan.WorkPhone), new SqlParameter("@HomePhone",LinkMan.HomePhone), new SqlParameter("@Mobile",LinkMan.Mobile), new SqlParameter("@Email",LinkMan.Email), new SqlParameter("@Address",LinkMan.Address), new SqlParameter("@Postcode",LinkMan.Postcode), new SqlParameter("@QQ",LinkMan.QQ), new SqlParameter("@MSN",LinkMan.MSN), new SqlParameter("@CustomerID",LinkMan.CustomerID) }; return DataBaseHelper.ExcuteSqlReturnInt("BioCrmLinkmanInfo_ADD", CommandType.StoredProcedure, pars); } /// <summary> /// 修改联系人信息 /// </summary> /// <param name="LinkMan"></param> /// <returns>int</returns> public int LinkmanInfoUpdate(BioCrmLinkmanInfo LinkMan) { SqlParameter[] pars = new SqlParameter[]{ new SqlParameter("@LinkmanID",LinkMan.LinkmanID), new SqlParameter("@LinkmanName",LinkMan.LinkmanName), new SqlParameter("@Nickname",LinkMan.Nickname), new SqlParameter("@Sex",LinkMan.Sex), new SqlParameter("@Birthday",LinkMan.Birthday), new SqlParameter("@LinkmanNumber",LinkMan.LinkmanNumber), new SqlParameter("@Department",LinkMan.Department), new SqlParameter("@Headship",LinkMan.Headship), new SqlParameter("@IsMain",LinkMan.IsMain), new SqlParameter("@WorkPhone",LinkMan.WorkPhone), new SqlParameter("@HomePhone",LinkMan.HomePhone), new SqlParameter("@Mobile",LinkMan.Mobile), new SqlParameter("@Email",LinkMan.Email), new SqlParameter("@Address",LinkMan.Address), new SqlParameter("@Postcode",LinkMan.Postcode), new SqlParameter("@QQ",LinkMan.QQ), new SqlParameter("@MSN",LinkMan.MSN), new SqlParameter("@CustomerID",LinkMan.CustomerID) }; return DataBaseHelper.ExcuteSqlReturnInt("BioCrmLinkmanInfo_Update", CommandType.StoredProcedure, pars); } /// <summary> /// 根据客户ID号返回客户详细信息 /// </summary> /// <param name="id">id</param> /// <returns>BioCrmLinkmanInfo</returns> public BioCrmLinkmanInfo getLinkManByID(string id) { BioCrmLinkmanInfo linkman=null; //BioCrmLinkmanInfo linkman = new BioCrmLinkmanInfo(); SqlDataReader reader= SqlComm.GetDataReaderByCondition("BioCrmLinkmanInfo", "*", " LinkmanID= " + id); if (reader.Read()) { linkman = new BioCrmLinkmanInfo() { LinkmanID = int.Parse(reader["LinkmanID"].ToString()), LinkmanName = reader["LinkmanName"].ToString(), Nickname = reader["Nickname"].ToString(), Address = reader["Address"].ToString(), Birthday = Convert.ToDateTime(reader["Birthday"].ToString()), WorkPhone = reader["WorkPhone"].ToString(), Sex = Convert.ToBoolean(reader["Sex"].ToString()), QQ = reader["QQ"].ToString(), Postcode = reader["Postcode"].ToString(), MSN = reader["MSN"].ToString(), CustomerID = int.Parse(reader["CustomerID"].ToString()), Mobile = reader["Mobile"].ToString(), LinkmanNumber = reader["LinkmanNumber"].ToString(), Department = reader["Department"].ToString(), HomePhone = reader["HomePhone"].ToString(), IsMain = Convert.ToBoolean(reader["IsMain"].ToString()), Headship = reader["Headship"].ToString(), DeleteState = Convert.ToBoolean(reader["DeleteState"].ToString()), Email = reader["Email"].ToString() }; } reader.Close(); return linkman; }
进行查询页面的跳转:
protected void btnReturn_Click(object sender, EventArgs e) { Server.Transfer("CustomerListShow.aspx"); }
流程图:
给客户的编号number建立自动生成列的触发器:
CREATE TRIGGER [dbo].[CreateLinkmanNumberByLinkmanID] --表名 ON [dbo].[BioCrmLinkmanInfo] AFTER INSERT AS BEGIN --定义变量 DECLARE @str nvarchar(10) DECLARE @LinkmanNumber nvarchar(50) DECLARE @LinkmanID nvarchar(50) --给字符串赋值 SET @str='0000000' --在查询的过程中赋值 SELECT @LinkmanID=MAX(LinkmanID) FROM dbo.BioCrmLinkmanInfo --重新组装字符串函数 SET @LinkmanNumber=RIGHT(@str+@LinkmanID,7) UPDATE dbo.BioCrmLinkmanInfo SET LinkmanNumber=@LinkmanNumber WHERE LinkmanID=@LinkmanID end
给联系人建立查询的视图:
右键添加视图
选中要生成视图的表:
生成Sql语句执行:
查询的时候直接查询视图:
在登录界面定义全局的变量:
public static UserManager user = new UserManager();
添加的后台代码:
public partial class LinkManAdd : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { } } protected void btnSubmit_Click(object sender, EventArgs e) { BioCrmLinkmanInfo linkman = new BioCrmLinkmanInfo() { LinkmanName = this.txtLinkmanName.Text, Address = this.txtAddress.Text, Birthday = Convert.ToDateTime(this.txtBirthday.Text), WorkPhone = this.txtWorkPhone.Text, Sex = this.ddlSex.SelectedValue == "0" ? false : true, Department = this.txtDepartment.Text, Email = this.txtEmail.Text, QQ = this.txtQQ.Text, MSN = this.txtMsN.Text, Headship = this.txtHeadship.Text, HomePhone = this.txtHeadship.Text, Mobile = this.txtMobile.Text, Postcode = this.txtPostcode.Text, Nickname = this.txtNickname.Text, IsMain = this.ddlMainMan.SelectedValue == "1" ? true : false, CustomerID = int.Parse(this.txtCustomerID.Text) }; LinkManInfoBLL linkbll = new LinkManInfoBLL(); if (linkbll.LinkmanInfoADD(linkman) != 0) { Server.Transfer("LinkManListShow.aspx"); } } protected void btnReturn_Click(object sender, EventArgs e) { Server.Transfer("CustomerListShow.aspx"); } }
视图中case ... then ...的用法: