/// <summary>
/// 添加客户投诉信息的方法
/// </summary>
/// <param name="CustComplainM">客户投诉信息</param>
/// <returns>投诉信息ID</returns>
public static int CustComplainAdd(CustComplainModel CustComplainM)
{
try
{
#region 设置参数
SqlParameter[] param = new SqlParameter[23];
param[0] = SqlHelper.GetParameter("@CompanyCD ", CustComplainM.CompanyCD);
param[1] = SqlHelper.GetParameter("@CustID ", CustComplainM.CustID);
param[2] = SqlHelper.GetParameter("@CustLinkMan ", CustComplainM.CustLinkMan);
param[3] = SqlHelper.GetParameter("@CustLinkTel ", CustComplainM.CustLinkTel);
param[4] = SqlHelper.GetParameter("@ComplainNo ", CustComplainM.ComplainNo);
param[5] = SqlHelper.GetParameter("@Title ", CustComplainM.Title);
param[6] = SqlHelper.GetParameter("@ComplainType ", CustComplainM.ComplainType);
param[7] = SqlHelper.GetParameter("@DestClerk ", CustComplainM.DestClerk);
param[8] = SqlHelper.GetParameter("@ComplainDate ", CustComplainM.ComplainDate);
param[9] = SqlHelper.GetParameter("@Critical ", CustComplainM.Critical);
param[10] = SqlHelper.GetParameter("@State ", CustComplainM.State);
param[11] = SqlHelper.GetParameter("@DateUnit ", CustComplainM.DateUnit);
param[12] = SqlHelper.GetParameter("@SpendTime ", CustComplainM.SpendTime);
param[13] = SqlHelper.GetParameter("@Contents ", CustComplainM.Contents);
param[14] = SqlHelper.GetParameter("@DisposalProcess", CustComplainM.DisposalProcess);
param[15] = SqlHelper.GetParameter("@Feedback ", CustComplainM.Feedback);
param[16] = SqlHelper.GetParameter("@Remark ", CustComplainM.Remark);
param[17] = SqlHelper.GetParameter("@ModifiedDate ", CustComplainM.ModifiedDate);
param[18] = SqlHelper.GetParameter("@ModifiedUserID ", CustComplainM.ModifiedUserID);
param[19] = SqlHelper.GetParameter("@ComplainedMan ", CustComplainM.ComplainedMan);
param[20] = SqlHelper.GetParameter("@CanViewUser ", CustComplainM.CanViewUser);
param[21] = SqlHelper.GetParameter("@CanViewUserName", CustComplainM.CanViewUserName);
SqlParameter paramid = new SqlParameter("@id", SqlDbType.Int);
paramid.Direction = ParameterDirection.Output;
param[22] = paramid;
#endregion
//创建命令
SqlCommand comm = new SqlCommand();
SqlHelper.ExecuteTransStoredProcedure("officedba.insertCustComplain", comm, param);
int Complainid = Convert.ToInt32(comm.Parameters["@id"].Value);
return Complainid;
}
catch
{
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) : "LinkDate";//要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_d"))
{
order = "desc";//排序：降序
}
string ord = " ORDER BY " + orderBy + " " + order;
CustComplainModel CustComplainM = new CustComplainModel();
string CustID = hiddCustID.Value;//客户ID
CustComplainM.ComplainType = Convert.ToInt32(ddlComplainType.SelectedItem.Value);//投诉类型
CustComplainM.Critical = seleCritical.Value;//紧急程度
string ComplainBegin = txtComplainBegin.Value.Trim();//投诉开始时间
string ComplainEnd = txtComplainEnd.Value.Trim();//结束时间
CustComplainM.Title = txtTitle.Value.Trim();//客户投诉主题
string CustLinkMan = txtCustLinkMan.Value.Trim();//客户联系人
string DestClerk = txtEmplNameL.Value.Trim();//接待人
CustComplainM.State = seleState.Value;//状态
CustComplainM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
string CanUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID.ToString();
DataTable dt = ComplainBus.ExportComplainInfo(CanUserID,CustID, CustComplainM, ComplainBegin, ComplainEnd, CustLinkMan, DestClerk, ord);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "投诉单编号", "投诉主题", "客户名称", "投诉时间", "投诉分类", "紧急程度", "接待人", "处理状态" },
new string[] { "ComplainNo", "title", "custNam", "ComplainDate", "typename", "Critical", "EmployeeName", "state" },
"客户投诉列表");
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "Exp", "<script language=javascript>showPopup('../../../Images/Pic/Close.gif','../../../Images/Pic/note.gif','导出发生异常');</script>");
}
}
/// <summary>
/// 修改客户投诉信息的方法
/// </summary>
/// <param name="CustComplainM">客户投诉信息</param>
/// <returns>操作结果</returns>
public static bool UpdateComplain(CustComplainModel CustComplainM)
{
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_COMPLAIN_LIST;
//操作单据编号 投诉单编号
logModel.ObjectID = CustComplainM.ComplainNo;
//操作对象 操作的表信息
logModel.ObjectName = ConstUtil.TABLE_NAME_COMPLAIN;
//涉及关键元素 涉及其他业务、表关系
logModel.Element = string.Empty;
//备注 操作类型
logModel.Remark = ConstUtil.LOG_PROCESS_UPDATE;
#endregion
try
{
isSucc = ComplainDBHelper.UpdateComplain(CustComplainM);
}
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_COMPLAIN_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="CustID"></param>
/// <param name="CustComplainM"></param>
/// <param name="ComplainBegin"></param>
/// <param name="ComplainEnd"></param>
/// <param name="CustLinkMan"></param>
/// <param name="DestClerk"></param>
/// <param name="ord"></param>
/// <returns></returns>
public static DataTable ExportComplainInfo(string CanUserID,string CustID, CustComplainModel CustComplainM, string ComplainBegin, string ComplainEnd, string CustLinkMan, string DestClerk, string ord)
{
return ComplainDBHelper.ExportComplainInfo(CanUserID,CustID, CustComplainM, ComplainBegin, ComplainEnd, CustLinkMan, DestClerk, ord);
}
public static DataTable GetComplainInfoByComplainPerson(string ComplainPerson,string CustName, CustComplainModel CustComplainM, string ComplainBegin, string ComplainEnd, string CustLinkMan, string DestClerk, int pageIndex, int pageCount, string ord, ref int totalCount)
{
return ComplainDBHelper.GetComplainInfoByComplainPerson(ComplainPerson, CustName, CustComplainM, ComplainBegin, ComplainEnd, CustLinkMan, DestClerk, pageIndex, pageCount, ord, ref totalCount);
}
public static DataTable GetCustComplainByCustID(CustComplainModel CustComplainM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.ID,a.CompanyCD,a.CustID,a.Title, ");
strSql.Append(" Convert(varchar(20),a.ComplainDate,120) ComplainDate, ");
strSql.Append(" (case a.State when '1' then '处理中' ");
strSql.Append(" when '2' then '未处理' ");
strSql.Append(" when '3' then '已处理' end) State ");
strSql.Append(" from officedba.CustComplain a ");
strSql.Append(" where a.CustID = @CustID ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustComplainM.CustID.ToString()));
#endregion
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
/// <summary>
/// 修改客户投诉信息的方法
/// </summary>
/// <param name="CustComplainM">客户投诉信息</param>
/// <returns>操作结果</returns>
public static bool UpdateComplain(CustComplainModel CustComplainM)
{
try
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE officedba.CustComplain set ");
sql.AppendLine("CompanyCD = @CompanyCD, ");
sql.AppendLine("CustID = @CustID ,");
sql.AppendLine("CustLinkMan = @CustLinkMan ,");
sql.AppendLine("CustLinkTel = @CustLinkTel ,");
//sql.AppendLine("ComplainNo = @ComplainNo ,");
sql.AppendLine("Title = @Title ,");
sql.AppendLine("ComplainType = @ComplainType ,");
sql.AppendLine("DestClerk = @DestClerk ,");
sql.AppendLine("ComplainDate = @ComplainDate ,");
sql.AppendLine("Critical = @Critical ,");
sql.AppendLine("State = @State ,");
sql.AppendLine("DateUnit = @DateUnit ,");
sql.AppendLine("SpendTime = @SpendTime ,");
sql.AppendLine("Contents = @Contents ,");
sql.AppendLine("ComplainedMan = @ComplainedMan ,");
sql.AppendLine("DisposalProcess = @DisposalProcess,");
sql.AppendLine("Feedback = @Feedback ,");
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[22];
param[0] = SqlHelper.GetParameter("@ID ", CustComplainM.ID);
param[1] = SqlHelper.GetParameter("@CompanyCD ", CustComplainM.CompanyCD);
param[2] = SqlHelper.GetParameter("@CustID ", CustComplainM.CustID);
param[3] = SqlHelper.GetParameter("@CustLinkMan ", CustComplainM.CustLinkMan);
param[4] = SqlHelper.GetParameter("@CustLinkTel ", CustComplainM.CustLinkTel);
//param[] = SqlHelper.GetParameter("@ComplainNo ",CustComplainM.ComplainNo );
param[5] = SqlHelper.GetParameter("@Title ", CustComplainM.Title);
param[6] = SqlHelper.GetParameter("@ComplainType ", CustComplainM.ComplainType);
param[7] = SqlHelper.GetParameter("@DestClerk ", CustComplainM.DestClerk);
param[8] = SqlHelper.GetParameter("@ComplainDate", CustComplainM.ComplainDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustComplainM.ComplainDate.ToString()));
param[9] = SqlHelper.GetParameter("@Critical ", CustComplainM.Critical);
param[10] = SqlHelper.GetParameter("@State ", CustComplainM.State);
param[11] = SqlHelper.GetParameter("@DateUnit ", CustComplainM.DateUnit);
param[12] = SqlHelper.GetParameter("@SpendTime ", CustComplainM.SpendTime);
param[13] = SqlHelper.GetParameter("@Contents ", CustComplainM.Contents);
param[14] = SqlHelper.GetParameter("@DisposalProcess", CustComplainM.DisposalProcess);
param[15] = SqlHelper.GetParameter("@Feedback ", CustComplainM.Feedback);
param[16] = SqlHelper.GetParameter("@Remark ", CustComplainM.Remark);
param[17] = SqlHelper.GetParameter("@ModifiedDate", CustComplainM.ModifiedDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustComplainM.ModifiedDate.ToString()));
param[18] = SqlHelper.GetParameter("@ModifiedUserID ", CustComplainM.ModifiedUserID);
param[19] = SqlHelper.GetParameter("@ComplainedMan ", CustComplainM.ComplainedMan);
param[20] = SqlHelper.GetParameter("@CanViewUser", CustComplainM.CanViewUser);
param[21] = SqlHelper.GetParameter("@CanViewUserName", CustComplainM.CanViewUserName);
SqlHelper.ExecuteTransSql(sql.ToString(), param);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
catch
{
return false;
}
}
public static DataTable GetComplainInfoByComplainPerson(string ComplainPerson,string CustName, CustComplainModel CustComplainM, string ComplainBegin, string ComplainEnd, string CustLinkMan, string DestClerk, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
string sql = "select " +
" cc.id,cc.ComplainNo,CONVERT(varchar(100), cc.ComplainDate, 20) ComplainDate,cc.custid,ci.custName custNam ,cc.title,cp.typename,ei.EmployeeName,cc.Critical,cc.state " +
" from " +
" officedba.CustComplain cc," +
" officedba.CustInfo ci," +
" officedba.CodePublicType cp," +
" officedba.EmployeeInfo ei " +
" where " +
" cc.custid = ci.id " +
" and cc.ComplainType = cp.id " +
" and cc.DestClerk = ei.id " +
" and cc.CompanyCD = '" + CustComplainM.CompanyCD + "'";
if (ComplainPerson != "")
sql += " and cc.ComplainedMan = " + ComplainPerson + " ";
if (CustName != "")
sql += " and ci.id = '" + CustName + "'";
if (CustComplainM.ComplainType != 0)
sql += " and cc.ComplainType = " + CustComplainM.ComplainType + "";
if (CustComplainM.Critical != "0")//紧急程度
sql += " and cc.Critical = " + CustComplainM.Critical + "";
if (ComplainBegin != "")
sql += " and cc.ComplainDate >= '" + ComplainBegin + "'";
if (ComplainEnd != "")
sql += " and cc.ComplainDate <= '" + ComplainEnd + "'";
if (CustComplainM.Title != "")
sql += " and cc.title like '%" + CustComplainM.Title + "%'";
if (DestClerk != "")
sql += " and ei.EmployeeName like '%" + DestClerk + "%'";
if (CustComplainM.State != "0")
sql += " and cc.state = '" + CustComplainM.State + "'";
#endregion
//return SqlHelper.ExecuteSql(sql);
return SqlHelper.CreateSqlByPageExcuteSql(sql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount);
}
catch
{
return null;
}
}
/// <summary>
/// 导出客户投诉信息
/// </summary>
/// <param name="CustID"></param>
/// <param name="CustComplainM"></param>
/// <param name="ComplainBegin"></param>
/// <param name="ComplainEnd"></param>
/// <param name="CustLinkMan"></param>
/// <param name="DestClerk"></param>
/// <param name="ord"></param>
/// <returns></returns>
public static DataTable ExportComplainInfo(string CanUserID,string CustID, CustComplainModel CustComplainM, string ComplainBegin, string ComplainEnd, string CustLinkMan, string DestClerk, string ord)
{
try
{
#region sql语句
string sql = "select " +
" cc.id,cc.ComplainNo,CONVERT(varchar(100), cc.ComplainDate, 20) ComplainDate,cc.custid," +
"ci.custName custNam ,cc.title,cp.typename,ei.EmployeeName,(case cc.Critical when '1' then '宽松' when '2' then '一般' when '3' then '较急' when '4' then '紧急' when '5' then '特急' end)Critical," +
"(case cc.state when '1' then '处理中' when '2' then '未处理' when '3' then '已处理' end)state " +
" from " +
" officedba.CustComplain cc," +
" officedba.CustInfo ci," +
" officedba.CodePublicType cp," +
" officedba.EmployeeInfo ei, " +
" officedba.CustLinkMan cl " +
" where " +
" cc.custid = ci.id " +
" and cc.ComplainType = cp.id " +
" and cc.DestClerk = ei.id " +
" and cc.CustLinkMan = cl.id " +
" and cc.CompanyCD = '" + CustComplainM.CompanyCD + "'" +
" and (cc.CanViewUser like '%" + "," + CanUserID + "," + "%' or '" + CanUserID + "' = cc.DestClerk or '" + CanUserID + "' = cc.ComplainedMan or cc.CanViewUser = '******' or cc.CanViewUser is null )";
if (CustID != "")
sql += " and ci.id = '" + CustID + "'";
if (CustComplainM.ComplainType != 0)
sql += " and cc.ComplainType = " + CustComplainM.ComplainType + "";
if (CustComplainM.Critical != "0")//紧急程度
sql += " and cc.Critical = " + CustComplainM.Critical + "";
if (ComplainBegin.ToString() != "")
sql += " and cc.ComplainDate >= '" + ComplainBegin.ToString() + "'";
if (ComplainEnd.ToString() != "")
sql += " and cc.ComplainDate <= '" + ComplainEnd.ToString() + "'";
if (CustComplainM.Title != "")
sql += " and cc.title like '%" + CustComplainM.Title + "%'";
if (CustLinkMan != "")
sql += " and cl.LinkManName like '%" + CustLinkMan + "%'";
if (DestClerk != "")
sql += " and ei.EmployeeName like '%" + DestClerk + "%'";
if (CustComplainM.State != "0")
sql += " and cc.state = '" + CustComplainM.State + "'";
#endregion
return SqlHelper.ExecuteSql(sql);
}
catch
{
return null;
}
}
public static DataTable GetCustComplainByCustID(CustComplainModel CustComplainM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return CustCallDBHelper.GetCustComplainByCustID(CustComplainM, pageIndex, pageCount, ord, ref TotalCount);
}
