/// <summary>
/// 添加客户服务信息的方法
/// </summary>
/// <param name="CustServiceM">客户服务信息</param>
/// <returns>返回操作记录值</returns>
public static int CustServiceAdd(CustServiceModel CustServiceM)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
LogInfoModel logModel = new LogInfoModel(); //操作日志
int isSucc = 0;//定义返回变量
#region 设置操作日志内容
//设置公司代码
logModel.CompanyCD = userInfo.CompanyCD;
//设置登陆用户ID
logModel.UserID = userInfo.UserID;
//设置模块ID 模块ID在ConstUtil中定义，以便维护
logModel.ModuleID = ConstUtil.MODULE_ID_CUST_SERVICE_ADD;
//操作单据编号 服务单编号
logModel.ObjectID = CustServiceM.ServeNO;
//操作对象 操作的表信息
logModel.ObjectName = ConstUtil.TABLE_NAME_SERVICE;
//涉及关键元素 涉及其他业务、表关系
logModel.Element = string.Empty;
//备注 操作类型
logModel.Remark = ConstUtil.LOG_PROCESS_INSERT;
#endregion
try
{
isSucc = ServiceDBHelper.CustServiceAdd(CustServiceM);
}
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_SERVICE_ADD;
//描述
logSys.Description = ex.ToString();
//输出日志
LogUtil.WriteLog(logSys);
#endregion
}
if (isSucc > 0)//操作成功
{
logModel.Remark += "成功";
}
else//操作失败
{
logModel.Remark += "失败";
}
//记录日志
LogDBHelper.InsertLog(logModel);
return isSucc;
}
/// <summary>
/// 添加客户服务信息的方法
/// </summary>
/// <param name="CustServiceM">客户服务信息</param>
/// <returns>返回操作记录值</returns>
public static int CustServiceAdd(CustServiceModel CustServiceM)
{
try
{
#region 设置参数
SqlParameter[] param = new SqlParameter[23];
param[0] = SqlHelper.GetParameter("@CompanyCD ", CustServiceM.CompanyCD);
param[1] = SqlHelper.GetParameter("@ServeNo ", CustServiceM.ServeNO);
param[2] = SqlHelper.GetParameter("@CustID ", CustServiceM.CustID);
param[3] = SqlHelper.GetParameter("@CustLinkMan ", CustServiceM.CustLinkMan);
param[4] = SqlHelper.GetParameter("@CustLinkTel ", CustServiceM.CustLinkTel);
param[5] = SqlHelper.GetParameter("@Title ", CustServiceM.Title);
param[6] = SqlHelper.GetParameter("@ServeType ", CustServiceM.ServeType);
param[7] = SqlHelper.GetParameter("@Fashion ", CustServiceM.Fashion);
param[8] = SqlHelper.GetParameter("@State ", CustServiceM.State);
param[9] = SqlHelper.GetParameter("@BeginDate ", CustServiceM.BeginDate);
param[10] = SqlHelper.GetParameter("@DateUnit ", CustServiceM.DateUnit);
param[11] = SqlHelper.GetParameter("@SpendTime ", CustServiceM.SpendTime);
param[12] = SqlHelper.GetParameter("@OurLinkMan ", CustServiceM.OurLinkMan);
param[13] = SqlHelper.GetParameter("@Executant ", CustServiceM.Executant);
param[14] = SqlHelper.GetParameter("@Contents ", CustServiceM.Contents);
param[15] = SqlHelper.GetParameter("@Feedback ", CustServiceM.Feedback);
param[16] = SqlHelper.GetParameter("@LinkQA ", CustServiceM.LinkQA);
param[17] = SqlHelper.GetParameter("@Remark ", CustServiceM.Remark);
param[18] = SqlHelper.GetParameter("@ModifiedDate ", CustServiceM.ModifiedDate);
param[19] = SqlHelper.GetParameter("@ModifiedUserID", CustServiceM.ModifiedUserID);
param[20] = SqlHelper.GetParameter("@CanViewUser", CustServiceM.CanViewUser);
param[21] = SqlHelper.GetParameter("@CanViewUserName", CustServiceM.CanViewUserName);
SqlParameter paramid = new SqlParameter("@id", SqlDbType.Int);
paramid.Direction = ParameterDirection.Output;
param[22] = paramid;
#endregion
//创建命令
SqlCommand comm = new SqlCommand();
SqlHelper.ExecuteTransStoredProcedure("officedba.insertCustService", comm, param);
int Serviceid = Convert.ToInt32(comm.Parameters["@id"].Value);
return Serviceid;
}
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) : "BeginDate";//要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_d"))
{
order = "desc";//排序：降序
}
string ord = " ORDER BY " + orderBy + " " + order;
//获取检索条件
string CustID = hiddCustID.Value;//客户ID
CustServiceModel CustServiceM = new CustServiceModel();
CustServiceM.ServeType = Convert.ToInt32(ddlServeType.SelectedItem.Value);//服务类型
CustServiceM.Fashion = Convert.ToInt32(ddlFashion.Value);//服务方式
string ServiceDateBegin = txtServiceDateBegin.Value;
string ServiceDateEnd = txtServiceDateEnd.Value;//服务结束时间
CustServiceM.Title = txtTitle.Value.Trim();//客户服务主题
string Executant = txtExecutant.Value.Trim();//执行人
string CustLinkMan = txtCustLinkMan.Value.Trim();//客户联系人
CustServiceM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
string CanUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID.ToString();
DataTable dt = ServiceBus.ExportServiceInfo(CanUserID,CustID, CustServiceM, ServiceDateBegin, ServiceDateEnd, Executant, CustLinkMan, ord);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "服务单编号", "服务主题", "服务时间", "客户名称", "服务类型", "服务方式", "执行人", "客户联络人" },
new string[] { "ServeNo", "title", "BeginDate", "custnam", "ServeType", "Fashion", "EmployeeName", "LinkManName" },
"客户服务列表");
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "Exp", "<script language=javascript>showPopup('../../../Images/Pic/Close.gif','../../../Images/Pic/note.gif','导出发生异常');</script>");
}
}
public static DataTable GetCustServiceByCustID(CustServiceModel CustServiceM, 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 Title_fw, ");
strSql.Append(" Convert(varchar(20),a.BeginDate,120) BeginDate, ");
strSql.Append(" (case a.Fashion when '1' then '远程支持' ");
strSql.Append(" when '2' then '现场服务' ");
strSql.Append(" when '3' then '综合服务' end) Fashion,b.LinkManName ");
strSql.Append(" from officedba.CustService a ");
strSql.Append(" left join officedba.CustLinkMan b on b.ID = a.CustLinkMan ");
strSql.Append(" where a.CustID = @CustID ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustServiceM.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="CustName"></param>
/// <param name="CustServiceM"></param>
/// <param name="ServiceDateBegin"></param>
/// <param name="ServiceDateEnd"></param>
/// <param name="Executant"></param>
/// <param name="CustLinkMan"></param>
/// <param name="ord"></param>
/// <returns></returns>
public static DataTable ExportServiceInfo(string CanUserID,string CustID, CustServiceModel CustServiceM, string ServiceDateBegin, string ServiceDateEnd, string Executant, string CustLinkMan, string ord)
{
return ServiceDBHelper.ExportServiceInfo(CanUserID,CustID, CustServiceM, ServiceDateBegin, ServiceDateEnd, Executant, CustLinkMan, ord);
}
public static DataTable GetServiceInfoBycondition(string CanUserID, string CustName, CustServiceModel CustServiceM, string ServiceDateBegin, string ServiceDateEnd, string Executant, string CustLinkMan, int pageIndex, int pageCount, string ord, ref int totalCount)
{
try
{
return ServiceDBHelper.GetServiceInfoBycondition(CanUserID,CustName, CustServiceM, ServiceDateBegin, ServiceDateEnd, Executant, CustLinkMan, pageIndex, pageCount, ord, ref totalCount);
}
catch
{
return null;
}
}
public static DataTable GetCustServiceByCustID(CustServiceModel CustServiceM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return CustCallDBHelper.GetCustServiceByCustID(CustServiceM, pageIndex, pageCount, ord, ref TotalCount);
}
/// <summary>
/// 修改客户服务信息的方法
/// </summary>
/// <param name="CustServiceM">服务信息</param>
/// <returns>bool值</returns>
public static bool UpdateService(CustServiceModel CustServiceM)
{
try
{
#region 拼写修改联系人信息SQL语句
StringBuilder sqlcontact = new StringBuilder();
sqlcontact.AppendLine("UPDATE officedba.CustService set ");
sqlcontact.AppendLine("CompanyCD = @CompanyCD, ");
//sqlcontact.AppendLine("ServeNo = @ServeNo, ");
sqlcontact.AppendLine("CustID = @CustID, ");
sqlcontact.AppendLine("CustLinkMan = @CustLinkMan, ");
sqlcontact.AppendLine("CustLinkTel = @CustLinkTel, ");
sqlcontact.AppendLine("Title = @Title, ");
sqlcontact.AppendLine("ServeType = @ServeType, ");
sqlcontact.AppendLine("Fashion = @Fashion, ");
sqlcontact.AppendLine("State = @State, ");
sqlcontact.AppendLine("BeginDate = @BeginDate, ");
sqlcontact.AppendLine("DateUnit = @DateUnit, ");
sqlcontact.AppendLine("SpendTime = @SpendTime, ");
sqlcontact.AppendLine("OurLinkMan = @OurLinkMan, ");
sqlcontact.AppendLine("Executant = @Executant, ");
sqlcontact.AppendLine("Contents = @Contents, ");
sqlcontact.AppendLine("Feedback = @Feedback, ");
sqlcontact.AppendLine("LinkQA = @LinkQA, ");
sqlcontact.AppendLine("Remark = @Remark, ");
sqlcontact.AppendLine("CanViewUser = @CanViewUser, ");
sqlcontact.AppendLine("CanViewUserName = @CanViewUserName, ");
sqlcontact.AppendLine("ModifiedDate = @ModifiedDate, ");
sqlcontact.AppendLine("ModifiedUserID = @ModifiedUserID ");
sqlcontact.AppendLine(" WHERE ");
sqlcontact.AppendLine("ID = @ID ");
#endregion
#region 设置修改联系人信息参数
SqlParameter[] param = new SqlParameter[22];
param[0] = SqlHelper.GetParameter("@ID", CustServiceM.ID);
param[1] = SqlHelper.GetParameter("@CompanyCD", CustServiceM.CompanyCD);
//param[2] = SqlHelper.GetParameter("@ServeNo", CustServiceM.ServeNO);
param[2] = SqlHelper.GetParameter("@CustID", CustServiceM.CustID);
param[3] = SqlHelper.GetParameter("@CustLinkMan", CustServiceM.CustLinkMan);
param[4] = SqlHelper.GetParameter("@CustLinkTel", CustServiceM.CustLinkTel);
param[5] = SqlHelper.GetParameter("@Title", CustServiceM.Title);
param[6] = SqlHelper.GetParameter("@ServeType", CustServiceM.ServeType);
param[7] = SqlHelper.GetParameter("@Fashion", CustServiceM.Fashion);
param[8] = SqlHelper.GetParameter("@State", CustServiceM.State);
param[9] = SqlHelper.GetParameter("@BeginDate", CustServiceM.BeginDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustServiceM.BeginDate.ToString()));
param[10] = SqlHelper.GetParameter("@DateUnit", CustServiceM.DateUnit);
param[11] = SqlHelper.GetParameter("@SpendTime", CustServiceM.SpendTime);
param[12] = SqlHelper.GetParameter("@OurLinkMan", CustServiceM.OurLinkMan);
param[13] = SqlHelper.GetParameter("@Executant", CustServiceM.Executant);
param[14] = SqlHelper.GetParameter("@Contents", CustServiceM.Contents);
param[15] = SqlHelper.GetParameter("@Feedback", CustServiceM.Feedback);
param[16] = SqlHelper.GetParameter("@LinkQA", CustServiceM.LinkQA);
param[17] = SqlHelper.GetParameter("@Remark", CustServiceM.Remark);
param[18] = SqlHelper.GetParameter("@ModifiedDate", CustServiceM.ModifiedDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustServiceM.ModifiedDate.ToString()));
param[19] = SqlHelper.GetParameter("@ModifiedUserID", CustServiceM.ModifiedUserID);
param[20] = SqlHelper.GetParameter("@CanViewUser", CustServiceM.CanViewUser);
param[21] = SqlHelper.GetParameter("@CanViewUserName", CustServiceM.CanViewUserName);
#endregion
SqlHelper.ExecuteTransSql(sqlcontact.ToString(), param);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
catch
{
return false;
}
}
public static DataTable GetServiceInfoBycondition(string CanUserID, string CustName, CustServiceModel CustServiceM, string ServiceDateBegin, string ServiceDateEnd, string Executant, string CustLinkMan, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
string sql = "select " +
" s.id,s.ServeNo,s.title,CONVERT(varchar(100), s.BeginDate, 20) BeginDate,s.custid,c.custname custnam," +
" p.TypeName ServeType,c.CustNo,c.CustBig, c.CanViewUser,c.Manager,c.Creator," +
" (case s.Fashion when '1' then '远程支持' when '2' then '现场服务' when '3' then '综合服务' end) Fashion," +
" e.EmployeeName,l.LinkManName " +
" from " +
" officedba.CustService s" +
" left join officedba.custinfo c on c.id = s.custid " +
" left join officedba.CodePublicType p on p.id = s.ServeType" +
" left join officedba.EmployeeInfo e on e.id = s.Executant" +
" left join officedba.CustLinkMan l on l.id = s.CustLinkMan" +
" where " +
" s.CompanyCD = '" + CustServiceM.CompanyCD + "'" +
" and (s.CanViewUser like '%" + "," + CanUserID + "," + "%' or '" + CanUserID + "' = s.Executant or '" + CanUserID + "' = s.OurLinkMan or s.CanViewUser = '******' or s.CanViewUser is null )";
if (CustName != "")
sql += " and c.id = '" + CustName + "'";
if (CustServiceM.ServeType != 0)
sql += " and p.id = " + CustServiceM.ServeType + "";
if (CustServiceM.Fashion != 0)
sql += " and s.Fashion = " + CustServiceM.Fashion + "";
if (ServiceDateBegin != "")
sql += " and s.BeginDate >= '" + ServiceDateBegin + "'";
if (ServiceDateEnd != "")
sql += " and s.BeginDate <= '" + ServiceDateEnd + "'";
if (CustServiceM.Title != "")
sql += " and s.title like '%" + CustServiceM.Title + "%'";
if (Executant != "")
sql += " and e.EmployeeName like '%" + Executant + "%'";
if (CustLinkMan != "")
sql += " and l.LinkManName like '%" + CustLinkMan + "%'";
#endregion
//return SqlHelper.ExecuteSql(sql);
return SqlHelper.CreateSqlByPageExcuteSql(sql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount);
}
catch
{
return null;
}
}
