/// <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);
}
}
