/// <summary> /// 打印客户洽谈信息 /// </summary> /// <param name="CustID"></param> /// <param name="CustTalkM"></param> /// <param name="TalkBegin"></param> /// <param name="TalkEnd"></param> /// <param name="ord"></param> /// <returns></returns> public static DataTable ExportTalkInfo(string CanUserID, string CustID, CustTalkModel CustTalkM, string TalkBegin, string TalkEnd, string ord) { // return TalkDBHelper.ExportTalkInfo(CustID, CustTalkM, TalkBegin, TalkEnd, ord); string LinkerIds = ""; //每条记录多个ID string EmployeeNames = ""; //多个员工姓名 DataTable dt = TalkDBHelper.ExportTalkInfo(CanUserID, CustID, CustTalkM, TalkBegin, TalkEnd, ord); DataColumn LinkerName = new DataColumn(); dt.Columns.Add("LinkerName"); for (int i = 0; i < dt.Rows.Count; i++) { LinkerIds = dt.Rows[i]["Linker"].ToString(); string[] LinkerList = LinkerIds.Split(','); for (int j = 0; j < LinkerList.Length; j++) { //获取参与人ID int inputID = Convert.ToInt32(LinkerList[j]); //调用方法取name EmployeeNames = EmployeeNames + "," + EmployeeDBHelper.GetEmployeeNameByID(inputID, CustTalkM.CompanyCD); } //插入EmployeeNames到一条记录 dt.Rows[i]["LinkerName"] = EmployeeNames.Substring(1); EmployeeNames = ""; } return(dt); }
/// <summary> /// 打印客户洽谈信息 /// </summary> /// <param name="CustID"></param> /// <param name="CustTalkM"></param> /// <param name="TalkBegin"></param> /// <param name="TalkEnd"></param> /// <param name="ord"></param> /// <returns></returns> public static DataTable ExportTalkInfo(string CanUserID, string CustID, CustTalkModel CustTalkM, string TalkBegin, string TalkEnd, string ord) { try { #region sql语句 string sql = "select ct.id,ct.TalkNo,ct.title,ct.custid,ci.custname custnam," + " ct.custlinkman,cl.linkmanname," + " (case ct.Priority when '1' then '暂缓' when '2' then '普通' when '3' then '尽快' when '4' then '立即' end) PriorityName," + " ct.talktype,(case ct.TalkType when '1' then '电话' when '2' then '传真' when '3' then '邮件' when '4' then '远程在线' when '5' then '会晤拜访' when '6' then '综合' end) typename," + " CONVERT(varchar(100), ct.CompleteDate, 20) CompleteDate,ct.Linker,(case ct.Status when '1' then '未开始' when '2' then '进行中' when '3' then '已完成' end) StatusName," + " ct.Creator,ei.EmployeeName," + " CONVERT(varchar(100), ct.CreatedDate, 23) CreatedDate" + " from " + " officedba.custtalk ct " + " left join officedba.custinfo ci on ci.id = ct.custid " + " left join officedba.custlinkman cl on cl.id = ct.custlinkman " + " left join officedba.EmployeeInfo ei on ei.id = ct.creator " + " where " + " ct.CompanyCD = '" + CustTalkM.CompanyCD + "'" + " and (ct.CanViewUser like '%" + "," + CanUserID + "," + "%' or '" + CanUserID + "' = ct.Linker or '" + CanUserID + "' = ct.Creator or ct.CanViewUser = '******' or ct.CanViewUser is null )"; if (CustID != "") { sql += " and ci.id = '" + CustID + "'"; } if (CustTalkM.TalkType != 0) { sql += " and ct.TalkType = " + CustTalkM.TalkType + ""; } if (TalkBegin != "") { sql += " and ct.CompleteDate >= '" + TalkBegin.ToString() + "'"; } if (TalkEnd != "") { sql += " and ct.CompleteDate <= '" + TalkEnd.ToString() + "'"; } if (CustTalkM.Priority != "0") { sql += " and ct.Priority = " + CustTalkM.Priority + ""; } if (CustTalkM.Title != "") { sql += " and ct.title like '%" + CustTalkM.Title + "%'"; } if (CustTalkM.Status != "0") { sql += " and ct.Status = " + CustTalkM.Status + ""; } #endregion return(SqlHelper.ExecuteSql(sql)); } catch (Exception ex) { string smeg = ex.Message; return(null); } }
/// <summary> /// 修改客户洽谈信息 /// </summary> /// <param name="CustTalkM">客户洽谈信息</param> /// <returns>bool值</returns> public static bool UpdateTalk(CustTalkModel CustTalkM) { try { StringBuilder sql = new StringBuilder(); sql.AppendLine("UPDATE officedba.CustTalk set "); sql.AppendLine("CompanyCD =@CompanyCD ,"); sql.AppendLine("CustID =@CustID ,"); sql.AppendLine("CustLinkMan =@CustLinkMan ,"); sql.AppendLine("Title =@Title ,"); sql.AppendLine("Priority =@Priority ,"); sql.AppendLine("TalkType =@TalkType ,"); sql.AppendLine("Linker =@Linker ,"); sql.AppendLine("CompleteDate =@CompleteDate ,"); sql.AppendLine("Status =@Status ,"); sql.AppendLine("Contents =@Contents ,"); sql.AppendLine("Feedback =@Feedback ,"); sql.AppendLine("Result =@Result ,"); sql.AppendLine("remark =@remark ,"); sql.AppendLine("CanViewUser = @CanViewUser, "); sql.AppendLine("CanViewUserName = @CanViewUserName, "); sql.AppendLine("ModifiedDate =@ModifiedDate ,"); sql.AppendLine("ModifiedUserID=@ModifiedUserID"); sql.AppendLine(" WHERE "); sql.AppendLine("ID = @ID "); SqlParameter[] param = new SqlParameter[18]; param[0] = SqlHelper.GetParameter("@ID ", CustTalkM.ID); param[1] = SqlHelper.GetParameter("@CompanyCD ", CustTalkM.CompanyCD); param[2] = SqlHelper.GetParameter("@CustID ", CustTalkM.CustID); param[3] = SqlHelper.GetParameter("@CustLinkMan ", CustTalkM.CustLinkMan); param[4] = SqlHelper.GetParameter("@Title ", CustTalkM.Title); param[5] = SqlHelper.GetParameter("@Priority ", CustTalkM.Priority); param[6] = SqlHelper.GetParameter("@TalkType ", CustTalkM.TalkType); param[7] = SqlHelper.GetParameter("@Linker ", CustTalkM.Linker); param[8] = SqlHelper.GetParameter("@CompleteDate", CustTalkM.CompleteDate == null ? SqlDateTime.Null : SqlDateTime.Parse(CustTalkM.CompleteDate.ToString())); param[9] = SqlHelper.GetParameter("@Status ", CustTalkM.Status); param[10] = SqlHelper.GetParameter("@Contents ", CustTalkM.Contents); param[11] = SqlHelper.GetParameter("@Feedback ", CustTalkM.Feedback); param[12] = SqlHelper.GetParameter("@Result ", CustTalkM.Result); param[13] = SqlHelper.GetParameter("@remark ", CustTalkM.remark); param[14] = SqlHelper.GetParameter("@ModifiedDate ", CustTalkM.ModifiedDate); param[15] = SqlHelper.GetParameter("@ModifiedUserID", CustTalkM.ModifiedUserID); param[16] = SqlHelper.GetParameter("@CanViewUser", CustTalkM.CanViewUser); param[17] = SqlHelper.GetParameter("@CanViewUserName", CustTalkM.CanViewUserName); SqlHelper.ExecuteTransSql(sql.ToString(), param); return(SqlHelper.Result.OprateCount > 0 ? true : false); } catch { return(false); } }
/// <summary> /// 新建洽谈信息的方法 /// </summary> /// <param name="CustTalkM">洽谈信息</param> /// <returns>返回洽谈ID</returns> public static int CustTalkAdd(CustTalkModel CustTalkM) { try { #region 设置参数 SqlParameter[] param = new SqlParameter[21]; param[0] = SqlHelper.GetParameter("@CompanyCD ", CustTalkM.CompanyCD); param[1] = SqlHelper.GetParameter("@CustID ", CustTalkM.CustID); param[2] = SqlHelper.GetParameter("@CustLinkMan ", CustTalkM.CustLinkMan); param[3] = SqlHelper.GetParameter("@TalkNo ", CustTalkM.TalkNo); param[4] = SqlHelper.GetParameter("@Title ", CustTalkM.Title); param[5] = SqlHelper.GetParameter("@Priority ", CustTalkM.Priority); param[6] = SqlHelper.GetParameter("@TalkType ", CustTalkM.TalkType); param[7] = SqlHelper.GetParameter("@Linker ", CustTalkM.Linker); param[8] = SqlHelper.GetParameter("@CompleteDate ", CustTalkM.CompleteDate); param[9] = SqlHelper.GetParameter("@Status ", CustTalkM.Status); param[10] = SqlHelper.GetParameter("@Contents ", CustTalkM.Contents); param[11] = SqlHelper.GetParameter("@Feedback ", CustTalkM.Feedback); param[12] = SqlHelper.GetParameter("@Result ", CustTalkM.Result); param[13] = SqlHelper.GetParameter("@remark ", CustTalkM.remark); param[14] = SqlHelper.GetParameter("@Creator ", CustTalkM.Creator); param[15] = SqlHelper.GetParameter("@CreatedDate ", CustTalkM.CreatedDate); param[16] = SqlHelper.GetParameter("@ModifiedDate ", CustTalkM.ModifiedDate); param[17] = SqlHelper.GetParameter("@ModifiedUserID", CustTalkM.ModifiedUserID); param[18] = SqlHelper.GetParameter("@CanViewUser", CustTalkM.CanViewUser); param[19] = SqlHelper.GetParameter("@CanViewUserName", CustTalkM.CanViewUserName); SqlParameter paramid = new SqlParameter("@id", SqlDbType.Int); paramid.Direction = ParameterDirection.Output; param[20] = paramid; #endregion //创建命令 SqlCommand comm = new SqlCommand(); SqlHelper.ExecuteTransStoredProcedure("officedba.insertCustTalk", comm, param); int Talkid = Convert.ToInt32(comm.Parameters["@id"].Value); return(Talkid); } catch (Exception ex) { string sss = ex.Message; return(0); } }
protected void btnImport_Click(object sender, ImageClickEventArgs e) { try { string orderString = hiddExpOrder.Value.Trim(); //排序 string order = "asc"; //排序:降序 string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "CreatedDate"; //要排序的字段,如果为空,默认为"ID" if (orderString.EndsWith("_d")) { order = "desc";//排序:降序 } string ord = " ORDER BY " + orderBy + " " + order; CustTalkModel CustTalkM = new CustTalkModel(); string CustID = hiddCustID.Value; CustTalkM.TalkType = Convert.ToInt32(ddlTalkType.Value); //类型 CustTalkM.Priority = selePriority.Value; //优先级 string TalkBegin = txtTalkBegin.Value; //== "" ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(context.Request.Form["TalkBegin"].ToString());//开始时间 string TalkEnd = txtTalkEnd.Value; //== "" ? Convert.ToDateTime("9999-12-31") : Convert.ToDateTime(context.Request.Form["TalkEnd"].ToString() + " 23:59:59.000");//结束时间 CustTalkM.Title = txtTitle.Value; //主题 CustTalkM.Status = seleStatus.Value; //状态 CustTalkM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; string CanUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID.ToString(); DataTable dt = TalkBus.ExportTalkInfo(CanUserID, CustID, CustTalkM, TalkBegin, TalkEnd, ord); OutputToExecl.ExportToTableFormat(this, dt, new string[] { "洽谈编号", "洽谈主题", "客户名称", "客户联系人", "优先级", "洽谈方式", "完成期限", "执行人", "状态", "创建人", "创建日期" }, new string[] { "TalkNo", "title", "custnam", "linkmanname", "PriorityName", "typename", "CompleteDate", "LinkerName", "StatusName", "EmployeeName", "CreatedDate" }, "客户洽谈列表信息"); } catch { ClientScript.RegisterStartupScript(this.GetType(), "Exp", "<script language=javascript>showPopup('../../../Images/Pic/Close.gif','../../../Images/Pic/note.gif','导出发生异常');</script>"); } }
/// <summary> /// 修改客户洽谈信息 /// </summary> /// <param name="CustTalkM">客户洽谈信息</param> /// <returns>bool值</returns> public static bool UpdateTalk(CustTalkModel CustTalkM) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; LogInfoModel logModel = new LogInfoModel(); //操作日志 bool isSucc = false; //定义返回变量 #region 设置操作日志内容 //设置公司代码 logModel.CompanyCD = userInfo.CompanyCD; //设置登陆用户ID logModel.UserID = userInfo.UserID; //设置模块ID 模块ID在ConstUtil中定义,以便维护 logModel.ModuleID = ConstUtil.MODULE_ID_CUST_TALK_LIST; //操作单据编号 编号 logModel.ObjectID = CustTalkM.TalkNo; //操作对象 操作的表信息 logModel.ObjectName = ConstUtil.TABLE_NAME_TALK; //涉及关键元素 涉及其他业务、表关系 logModel.Element = string.Empty; //备注 操作类型 logModel.Remark = ConstUtil.LOG_PROCESS_UPDATE; #endregion try { isSucc = TalkDBHelper.UpdateTalk(CustTalkM); } catch (System.Exception ex) { #region 操作失败时记录日志到文件 //定义变量 LogInfo logSys = new LogInfo(); //设置日志类型 需要指定为系统日志 logSys.Type = LogInfo.LogType.SYSTEM; //指定系统日志类型 出错信息 logSys.SystemKind = LogInfo.SystemLogKind.SYSTEM_ERROR; //指定登陆用户信息 logSys.UserInfo = userInfo; //设定模块ID logSys.ModuleID = ConstUtil.MODULE_ID_CUST_TALK_LIST; //描述 logSys.Description = ex.ToString(); //输出日志 LogUtil.WriteLog(logSys); #endregion } if (isSucc)//操作成功 { logModel.Remark += "成功"; } else//操作失败 { logModel.Remark += "失败"; } //记录日志 LogDBHelper.InsertLog(logModel); return(isSucc); }
/// <summary> /// 根据条件查询客户洽谈信息的方法 /// </summary> /// <param name="CustName">客户名称</param> /// <param name="CustTalkM">客户洽谈信息</param> /// <param name="TalkBegin">开始时间</param> /// <param name="TalkEnd">结束时间</param> /// <returns>返回查询结果</returns> public static DataTable GetTalkInfoBycondition(string CanUserID, string CustName, CustTalkModel CustTalkM, string TalkBegin, string TalkEnd, int pageIndex, int pageCount, string ord, ref int TotalCount) { try { #region sql语句 string sql = "select ct.id,ct.TalkNo,ct.title,ct.custid,ci.custname custnam," + " ct.custlinkman,cl.linkmanname,ci.CustNo,ci.CustBig,ci.CanViewUser,ci.Manager,ci.Creator CustCreator, " + " ct.Priority, ct.talktype,(case ct.TalkType when '1' then '电话' when '2' then '传真' when '3' then '邮件' when '4' then '远程在线' when '5' then '会晤拜访' when '6' then '综合' end) typename," + " CONVERT(varchar(100), ct.CompleteDate, 20) CompleteDate,ct.Linker,ct.Status," + " ct.Creator,ei.EmployeeName," + " CONVERT(varchar(100), ct.CreatedDate, 23) CreatedDate" + " from " + " officedba.custtalk ct " + " left join officedba.custinfo ci on ci.id = ct.custid " + " left join officedba.custlinkman cl on cl.id = ct.custlinkman " + //" left join officedba.CodePublicType cp on cp.id = ct.talktype " + " left join officedba.EmployeeInfo ei on ei.id = ct.creator " + " where " + " ct.CompanyCD = '" + CustTalkM.CompanyCD + "'" + " and (ct.CanViewUser like '%" + "," + CanUserID + "," + "%' or '" + CanUserID + "' = ct.Linker or '" + CanUserID + "' = ct.Creator or ct.CanViewUser = '******' or ct.CanViewUser is null )"; if (CustName != "") { sql += " and ci.id = '" + CustName + "'"; } if (CustTalkM.TalkType != 0) { sql += " and ct.TalkType = " + CustTalkM.TalkType + ""; } if (TalkBegin != "") { sql += " and ct.CompleteDate >= '" + TalkBegin.ToString() + "'"; } if (TalkEnd != "") { sql += " and ct.CompleteDate <= '" + TalkEnd.ToString() + "'"; } if (CustTalkM.Priority != "0") { sql += " and ct.Priority = " + CustTalkM.Priority + ""; } if (CustTalkM.Title != "") { sql += " and ct.title like '%" + CustTalkM.Title + "%'"; } if (CustTalkM.Status != "0") { sql += " and ct.Status = " + CustTalkM.Status + ""; } #endregion //return SqlHelper.ExecuteSql(sql); return(SqlHelper.CreateSqlByPageExcuteSql(sql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount)); } catch (Exception ex) { string smeg = ex.Message; return(null); } }