//导出
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
try
{
string orderString = hiddExpOrder.Value.Trim();//排序
string order = "desc";//排序：降序
string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "ModifiedDate";//要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_d"))
{
order = "asc";//排序：降序
}
string ord = " ORDER BY " + orderBy + " " + order;
CustCallModel CustCallM = new CustCallModel();
CustCallM.CustID = hiddCustID.Value == "" ? 0 : Convert.ToInt32(hiddCustID.Value);
CustCallM.Tel = txtTel.Value;
string DateBegin = txtDateBegin.Value.Trim();//开始时间
string DateEnd = txtDateEnd.Value.Trim();//结束时间
CustCallM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
DataTable dt = CustCallBus.GetCustCallByCon(CustCallM, DateBegin, DateEnd, ord);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "客户名称", "来电号码", "来电时间", "来电标题", "来电人", "创建人" },
new string[] { "CustName", "Tel", "CallTime", "Title", "Callor", "EmployeeName" },
"来电记录列表");
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "Exp", "<script language=javascript>showPopup('../../../Images/Pic/Close.gif','../../../Images/Pic/note.gif','导出发生异常');</script>");
}
}
/// <summary>
/// 根据来电号码获取来电记录列表
/// </summary>
/// <param name="CustCallM"></param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="ord"></param>
/// <param name="TotalCount"></param>
/// <returns></returns>
public static DataTable GetCustCallByTel(CustCallModel CustCallM, 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.Tel,a.Callor,Convert(varchar(20), ");
strSql.Append(" a.CallTime,120)CallTime,a.CallContents ,c.EmployeeName ");
strSql.Append(" from officedba.CustCall a ");
strSql.Append(" left join officedba.EmployeeInfo c on c.id = a.Creator ");
strSql.Append(" where a.CompanyCD = @CompanyCD ");
strSql.Append(" and ((a.CustID = (select b.id from officedba.custinfo b where CompanyCD = @CompanyCD ");
strSql.Append(" and( b.Tel=@Tel or b.Mobile=@Tel or b.Fax =@Tel)))");
strSql.Append(" or (a.CustID = (select d.ID from officedba.CustInfo d");
strSql.Append(" left join officedba.CustLinkMan e on e.CustNo = d.CustNo and e.CompanyCD = d.CompanyCD and e.CompanyCD = @CompanyCD");
strSql.Append(" where e.WorkTel=@Tel or e.Fax = @Tel or e.Handset=@Tel");
strSql.Append(" or e.HomeTel=@Tel))");
strSql.Append(" ) ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustCallM.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Tel", CustCallM.Tel));
#endregion
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
//导出
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
try
{
string orderString = hiddExpOrder.Value.Trim(); //排序
string order = "desc"; //排序：降序
string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "ModifiedDate"; //要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_d"))
{
order = "asc";//排序：降序
}
string ord = " ORDER BY " + orderBy + " " + order;
CustCallModel CustCallM = new CustCallModel();
CustCallM.CustID = hiddCustID.Value == "" ? 0 : Convert.ToInt32(hiddCustID.Value);
CustCallM.Tel = txtTel.Value;
string DateBegin = txtDateBegin.Value.Trim(); //开始时间
string DateEnd = txtDateEnd.Value.Trim(); //结束时间
CustCallM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
DataTable dt = CustCallBus.GetCustCallByCon(CustCallM, DateBegin, DateEnd, ord);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "客户名称", "来电号码", "来电时间", "来电标题", "来电人", "创建人" },
new string[] { "CustName", "Tel", "CallTime", "Title", "Callor", "EmployeeName" },
"来电记录列表");
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "Exp", "<script language=javascript>showPopup('../../../Images/Pic/Close.gif','../../../Images/Pic/note.gif','导出发生异常');</script>");
}
}
public static bool AddCustCallByTel(CustCallModel model)
{
//根据来电号码取所属客户ID
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO officedba.CustCall");
sql.AppendLine(" (CustID,CompanyCD,Tel,Creator,ModifiedDate,ModifiedUserID)");
sql.AppendLine(" values (@CustID,@CompanyCD,@Tel,@Creator,@ModifiedDate,@ModifiedUserID)");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Tel", model.Tel));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", model.CustID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", model.ModifiedDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.Creator));
return SqlHelper.ExecuteTransWithCommand(comm);
}
public static DataTable GetCustCallByCon(CustCallModel CustCallM, string DateBegin, string DateEnd, string ord)
{
try
{
#region sql语句
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.ID,a.CompanyCD,a.CustID,a.Tel,a.Title,a.Callor,Convert(varchar(20), ");
strSql.Append(" a.CallTime,120)CallTime,a.CallContents ,c.EmployeeName,b.CustName,a.ModifiedDate ");
strSql.Append(" from officedba.CustCall a ");
strSql.Append(" left join officedba.CustInfo b on b.id = a.CustID ");
strSql.Append(" left join officedba.EmployeeInfo c on c.id = a.Creator ");
strSql.Append(" where a.CompanyCD = @CompanyCD ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustCallM.CompanyCD));
#endregion
if (CustCallM.CustID != 0)
{
strSql.Append(" and a.CustID = @CustID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustCallM.CustID.ToString()));
}
if (!string.IsNullOrEmpty(CustCallM.Tel))
{
strSql.Append(" and a.Tel like @Tel ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Tel", "%" + CustCallM.Tel + "%"));
}
if (!string.IsNullOrEmpty(DateBegin))
{
strSql.AppendLine(" and a.CallTime >= @DateBegin");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DateBegin", DateBegin));
}
if (!string.IsNullOrEmpty(DateEnd))
{
strSql.AppendLine(" and a.CallTime <= @DateEnd");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DateEnd", DateEnd));
}
if (!string.IsNullOrEmpty(ord))
{
strSql.AppendLine(ord);
}
comm.CommandText = strSql.ToString();
return SqlHelper.ExecuteSearch(comm);
//return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
public static bool UpdateCallBuID(CustCallModel model)
{
//根据来电号码取所属客户ID
StringBuilder sql = new StringBuilder();
sql.AppendLine("update officedba.CustCall set ");
sql.AppendLine(" Callor = @Callor,CallContents = @CallContents,ModifiedDate = @ModifiedDate ");
sql.AppendLine(" ,ModifiedUserID = @ModifiedUserID,Title = @Title where ID = @ID ");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CallContents", model.CallContents));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Callor", model.Callor));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", model.ModifiedDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title));
return SqlHelper.ExecuteTransWithCommand(comm);
}
public static DataTable GetCustCallByCon(CustCallModel CustCallM, string DateBegin, string DateEnd, string ord)
{
return(CustCallDBHelper.GetCustCallByCon(CustCallM, DateBegin, DateEnd, ord));
}
public static DataTable GetCustCallByCon(CustCallModel CustCallM, string DateBegin, string DateEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return(CustCallDBHelper.GetCustCallByCon(CustCallM, DateBegin, DateEnd, pageIndex, pageCount, ord, ref TotalCount));
}
public static bool UpdateCallBuID(CustCallModel model)
{
return(CustCallDBHelper.UpdateCallBuID(model));
}
public static bool AddCustCallByTel(CustCallModel model)
{
return(CustCallDBHelper.AddCustCallByTel(model));
}
public static DataTable GetCustCallByTel(CustCallModel CustCallM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return(CustCallDBHelper.GetCustCallByTel(CustCallM, pageIndex, pageCount, ord, ref TotalCount));
}
public static DataTable GetCustCallByCon(CustCallModel CustCallM, string DateBegin, string DateEnd, string ord)
{
return CustCallDBHelper.GetCustCallByCon(CustCallM, DateBegin, DateEnd, ord);
}
public static DataTable GetCustCallByCon(CustCallModel CustCallM, string DateBegin, string DateEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return CustCallDBHelper.GetCustCallByCon(CustCallM, DateBegin, DateEnd, pageIndex, pageCount, ord, ref TotalCount);
}
public static bool UpdateCallBuID(CustCallModel model)
{
return CustCallDBHelper.UpdateCallBuID(model);
}
public static bool AddCustCallByTel(CustCallModel model)
{
return CustCallDBHelper.AddCustCallByTel(model);
}
public static DataTable GetCustCallByTel(CustCallModel CustCallM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return CustCallDBHelper.GetCustCallByTel(CustCallM, pageIndex, pageCount, ord, ref TotalCount);
}
