需求描述： 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" "/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="/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;" οnclick="showCustomerDialog()" /></td> </tr><tr><td colspan="4" class="bottomtd"><asp:Button ID="btnSubmit" runat="server" Text="客户联系人添加" οnclick="btnSubmit_Click" /><asp:Button ID="btnReturn" runat="server" Text="返回列表" οnclick="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" "/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="/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" "/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="/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="查询" οnclick="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="选择" οnclick="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 INSERTAS 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=@LinkmanIDend
给联系人建立查询的视图：
右键添加视图
选中要生成视图的表：
生成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 ...的用法：
