/// <summary>
/// 导出功能
/// </summary>
/// <param name="model"></param>
/// <param name="OrderBy"></param>
/// <param name="BeginTime"></param>
/// <param name="EndTime"></param>
/// <returns></returns>
public static DataTable GetCustAdvice(string CanUserID, CustAdviceModel model, string OrderBy, string BeginTime, string EndTime)
{
StringBuilder sql = new StringBuilder();
SqlCommand comm = new SqlCommand();
sql.AppendLine(" select a.ID,isnull(a.AdviceNo,'') as AdviceNo,isnull(a.Title,'') as Title,isnull(b.CustName,'') as CustName,isnull(c.LinkManName,'') as LinkManName,isnull(d.EmployeeName,'') as EmployeeName, ");
sql.AppendLine(" case a.Accept when '1' then '暂不考虑' when '2' then '一般' when '3' then '争取改进' when '4' then '一定做到' else '' end as Accept ");
sql.AppendLine(" ,isnull(substring(CONVERT(varchar,a.[AdviceDate],120),0,11),'')as AdviceDate,case a.AdviceType when '1' then '不满意' when '2' then '希望做到' when '3' then '其他' else '' end as AdviceType ");
sql.AppendLine(" ,case a.State when '1' then '未处理' when '2' then '处理中' when '3' then '已处理' else '' end as State ");
sql.AppendLine(" from officedba.CustAdvice as a left join officedba.CustInfo as b on a.CustID=b.ID ");
sql.AppendLine(" left join officedba.CustLinkMan as c on a.CustLinkMan=c.ID ");
sql.AppendLine(" left join officedba.EmployeeInfo as d on a.DestClerk=d.ID ");
sql.AppendLine(" where a.CompanyCD=@CompanyCD ");
sql.AppendLine(" and (a.CanViewUser like '%" + "," + CanUserID + "," + "%' or '" + CanUserID + "' = a.DestClerk or '" + CanUserID + "' = a.Creator or a.CanViewUser = '******' or a.CanViewUser is null )");
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
if (!string.IsNullOrEmpty(model.AdviceNo))
{
sql.AppendLine(" and a.AdviceNo like @AdviceNo");
comm.Parameters.Add(SqlHelper.GetParameter("@AdviceNo", "%" + model.AdviceNo + "%"));
}
if (!string.IsNullOrEmpty(model.Title))
{
sql.AppendLine(" and a.Title like @Title");
comm.Parameters.Add(SqlHelper.GetParameter("@Title", "%" + model.Title + "%"));
}
if (!string.IsNullOrEmpty(model.CustID) && model.CustID != "0")
{
sql.AppendLine(" and a.CustID=@CustID");
comm.Parameters.Add(SqlHelper.GetParameter("@CustID", model.CustID));
}
if (!string.IsNullOrEmpty(model.DestClerk) && model.DestClerk != "0")
{
sql.AppendLine(" and a.DestClerk=@DestClerk");
comm.Parameters.Add(SqlHelper.GetParameter("@DestClerk", model.DestClerk));
}
if (model.AdviceType != "0")
{
sql.AppendLine(" and a.AdviceType=@AdviceType");
comm.Parameters.Add(SqlHelper.GetParameter("@AdviceType", model.AdviceType));
}
if (model.State != "0")
{
sql.AppendLine(" and a.State=@State");
comm.Parameters.Add(SqlHelper.GetParameter("@State", model.State));
}
if (!string.IsNullOrEmpty(BeginTime))
{
sql.AppendLine(" and a.AdviceDate>=@AdviceDate");
comm.Parameters.Add(SqlHelper.GetParameter("@AdviceDate", BeginTime));
}
if (!string.IsNullOrEmpty(EndTime))
{
sql.AppendLine(" and a.AdviceDate<@AdviceDate1");
comm.Parameters.Add(SqlHelper.GetParameter("@AdviceDate1", Convert.ToDateTime(EndTime).AddDays(1)));
}
sql.AppendLine(" order by " + OrderBy);
comm.CommandText = sql.ToString();
return(SqlHelper.ExecuteSearch(comm));
}
/// <summary>
/// 修改建议
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool UpCustAdvice(CustAdviceModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
try
{
bool succ = false;
LogInfoModel logModel = InitLogInfo(model.AdviceNo);
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
succ = CustAdvice.UpCustAdvice(model);
if (!succ)
{
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
}
else
{
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
model.ID = IDIdentityUtil.GetIDIdentity("officedba.CustAdvice");
LogDBHelper.InsertLog(logModel);
return(succ);
}
catch (Exception ex)
{
WriteSystemLog(userInfo, ex);
return(false);
}
}
/// <summary>
/// 单据打印需要
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetOneCustAdviceInfo(CustAdviceModel model)
{
StringBuilder sql = new StringBuilder();
SqlCommand comm = new SqlCommand();
sql.AppendLine("SELECT a.[ID] ");
sql.AppendLine(" ,isnull(a.[AdviceNo],'') as AdviceNo ");
sql.AppendLine(" ,isnull(a.[Title],'') as Title ");
sql.AppendLine(" ,isnull(a.[Advicer],'') as Advicer ");
sql.AppendLine(" ,case a.[AdviceType] when '1' then '不满意' when '2' then '希望做到' when '3' then '其他' else '' end as AdviceType ");
sql.AppendLine(" ,a.[AdviceDate] ");
sql.AppendLine(" ,case a.[Accept] when '1' then '暂不考虑' when '2' then '一般' when '3' then '争取改进' when '4' then '一定做到' else '' end as Accept ");
sql.AppendLine(" ,case a.[State] when '1' then '未处理' when '2' then '处理中' when '3' then '已处理' else '' end as State ");
sql.AppendLine(" ,isnull(a.[Contents],'') as Contents ");
sql.AppendLine(" ,isnull(a.[DoSomething],'') as DoSomething ");
sql.AppendLine(" ,isnull(a.[LeadSay],'') as LeadSay ");
sql.AppendLine(" ,isnull(a.[Remark],'') as Remark ");
sql.AppendLine(" ,a.[CreatedDate] as CreatedDate ");
sql.AppendLine(" ,isnull(substring(CONVERT(varchar,a.[ModifiedDate],120),0,11),'') as ModifiedDate ");
sql.AppendLine(" ,a.[ModifiedUserID] ");
sql.AppendLine(" ,isnull(b.CustName,'') as CustID ");
sql.AppendLine(" ,isnull(c.LinkManName,'') as CustLinkMan ");
sql.AppendLine(" ,isnull(d.EmployeeName,'') as DestClerk ");
sql.AppendLine(" ,isnull(e.EmployeeName,'') as Creator ");
sql.AppendLine(" ,isnull(a.CanViewUserName,'') as CanViewUserName ");
sql.AppendLine(" FROM [officedba].[CustAdvice] as a left join officedba.CustInfo as b on a.CustID=b.ID ");
sql.AppendLine(" left join officedba.CustLinkMan as c on a.CustLinkMan=c.ID ");
sql.AppendLine(" left join officedba.EmployeeInfo as d on a.DestClerk=d.ID ");
sql.AppendLine(" left join officedba.EmployeeInfo as e on a.Creator=e.ID ");
sql.AppendLine(" where a.ID=@ID");
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.CommandText = sql.ToString();
return(SqlHelper.ExecuteSearch(comm));
}
protected void BindInfo()
{
CustAdviceModel model = new CustAdviceModel();
UserInfoUtil UserInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
/*接受参数*/
model.ID = Convert.ToInt32(Request.QueryString["ID"].ToString());
string CompanyCD = UserInfo.CompanyCD;
/*读取数据*/
DataTable dtDetail = new DataTable();
DataTable dt = XBase.Business.Office.CustManager.CustAdviceBus.GetOneCustAdviceInfo(model);
/*绑定RPT*/
if (dt != null)
{
/*加载主报表*/
this.CrystalReportSource1.ReportDocument.Load(Server.MapPath(@"~/PrinttingModel/CustManager/CustAdvice.rpt"));
CrystalReportViewer1.LogOnInfo.Add(ReportUtil.GetTableLogOnInfo("officedba.CustAdvice"));
//绑定数据
CrystalReportSource1.ReportDocument.SetDataSource(dt);
CrystalReportSource1.DataBind();
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
CrystalReportSource1.ReportDocument.SetParameterValue("CreatorName", userInfo.UserName);
CrystalReportSource1.ReportDocument.SetParameterValue("CreatorName", userInfo.UserName);
CrystalReportSource1.ReportDocument.SetParameterValue("CreatorName", userInfo.UserName);
this.CrystalReportViewer1.ReportSource = CrystalReportSource1;
}
}
/// <summary>
/// 修改建议
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool UpCustAdvice(CustAdviceModel model)
{
StringBuilder sql = new StringBuilder();
SqlCommand comm = new SqlCommand();
sql.AppendLine(" UPDATE [officedba].[CustAdvice] ");
sql.AppendLine(" SET [Title] =@Title ");
sql.AppendLine(" ,[CustID] =@CustID ");
sql.AppendLine(" ,[Advicer] =@Advicer ");
sql.AppendLine(" ,[CustLinkMan] =@CustLinkMan ");
sql.AppendLine(" ,[DestClerk] =@DestClerk ");
sql.AppendLine(" ,[AdviceType] =@AdviceType ");
sql.AppendLine(" ,[AdviceDate] =@AdviceDate ");
sql.AppendLine(" ,[Accept] =@Accept ");
sql.AppendLine(" ,[State] =@State ");
sql.AppendLine(" ,[Contents] =@Contents ");
sql.AppendLine(" ,[DoSomething] =@DoSomething ");
sql.AppendLine(" ,[LeadSay] =@LeadSay ");
sql.AppendLine(" ,[Remark] =@Remark ");
sql.AppendLine(" ,[CanViewUser] = @CanViewUser ");
sql.AppendLine(" ,[CanViewUserName] = @CanViewUserName ");
sql.AppendLine(" ,[ModifiedDate] =@ModifiedDate ");
sql.AppendLine(" ,[ModifiedUserID] =@ModifiedUserID ");
sql.AppendLine(" where ID=@ID ");
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.Parameters.Add(SqlHelper.GetParameter("@Title", model.Title));
comm.Parameters.Add(SqlHelper.GetParameter("@CustID", model.CustID));
comm.Parameters.Add(SqlHelper.GetParameter("@Advicer", model.Advicer));
comm.Parameters.Add(SqlHelper.GetParameter("@CustLinkMan", model.CustLinkMan));
comm.Parameters.Add(SqlHelper.GetParameter("@DestClerk", model.DestClerk));
comm.Parameters.Add(SqlHelper.GetParameter("@AdviceType", model.AdviceType));
comm.Parameters.Add(SqlHelper.GetParameter("@AdviceDate", model.AdviceDate));
comm.Parameters.Add(SqlHelper.GetParameter("@Accept", model.Accept));
comm.Parameters.Add(SqlHelper.GetParameter("@State", model.State));
comm.Parameters.Add(SqlHelper.GetParameter("@Contents", model.Contents));
comm.Parameters.Add(SqlHelper.GetParameter("@DoSomething", model.DoSomething));
comm.Parameters.Add(SqlHelper.GetParameter("@LeadSay", model.LeadSay));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", model.ModifiedDate));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@CanViewUser", model.CanViewUser));
comm.Parameters.Add(SqlHelper.GetParameter("@CanViewUserName", model.CanViewUserName));
comm.CommandText = sql.ToString();
return(SqlHelper.ExecuteTransWithCommand(comm));
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
try
{
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
CustAdviceModel model = new CustAdviceModel();
model.CompanyCD = companyCD;
model.Title = this.txtTitle.Text.Trim();
model.AdviceNo = this.txtAdvicetNo.Text.Trim();
model.CustID = this.hiddenCustIDL.Value;
model.DestClerk = this.hiddenExecutor.Value;
model.AdviceType = this.txtAdviceType.Value;
model.State = this.txtState.Value;
string theBeginTime = this.BeginTime.Value;
string theEndTime = this.EndTime.Value;
string myOrderBy = " AdviceDate Desc";
if (this.hiddenOrder.Value != "0")
{
string[] myOrder = this.hiddenOrder.Value.Split('_');
if (myOrder[1] == "a")
{
myOrderBy = myOrder[0] + " asc ";
}
else
{
myOrderBy = myOrder[0] + " desc ";
}
}
string CanUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID.ToString();
DataTable dt = CustAdviceBus.GetAllCustAdvice(CanUserID, model, myOrderBy, theBeginTime, theEndTime);
//导出标题
string headerTitle = "单据编号|单据主题|提出建议客户|客户联系人|接待人|采纳程度|建议时间|建议类型|状态";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "AdviceNo|Title|CustName|LinkManName|EmployeeName|Accept|AdviceDate|AdviceType|State";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "客户建议列表");
}
catch
{ }
}
/// <summary>
/// 获取所有
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="OrderBy"></param>
/// <param name="BeginTime"></param>
/// <param name="EndTime"></param>
/// <param name="CompanyCD"></param>
/// <param name="TotalCount"></param>
/// <returns></returns>
public static DataTable GetAllCustAdvice(string CanUserID, CustAdviceModel model, int pageIndex, int pageSize, string OrderBy, string BeginTime, string EndTime, string CompanyCD, ref int TotalCount)
{
return(CustAdvice.GetCustAdvice(CanUserID, model, pageIndex, pageSize, OrderBy, BeginTime, EndTime, CompanyCD, ref TotalCount));
}
/// <summary>
/// 导出
/// </summary>
/// <param name="model"></param>
/// <param name="OrderBy"></param>
/// <param name="BeginTime"></param>
/// <param name="EndTime"></param>
/// <returns></returns>
public static DataTable GetAllCustAdvice(string CanUserID, CustAdviceModel model, string OrderBy, string BeginTime, string EndTime)
{
return(CustAdvice.GetCustAdvice(CanUserID, model, OrderBy, BeginTime, EndTime));
}
/// <summary>
/// 单据打印需要
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetOneCustAdviceInfo(CustAdviceModel model)
{
return(CustAdvice.GetOneCustAdviceInfo(model));
}
/// <summary>
/// 添加客户建议
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool AddCustAdvice(CustAdviceModel model)
{
StringBuilder sql = new StringBuilder();
SqlCommand comm = new SqlCommand();
sql.AppendLine("INSERT INTO [officedba].[CustAdvice] ");
sql.AppendLine(" ([CompanyCD] ");
sql.AppendLine(" ,[AdviceNo] ");
sql.AppendLine(" ,[Title] ");
sql.AppendLine(" ,[CustID] ");
sql.AppendLine(" ,[Advicer] ");
sql.AppendLine(" ,[CustLinkMan] ");
sql.AppendLine(" ,[DestClerk] ");
sql.AppendLine(" ,[AdviceType] ");
sql.AppendLine(" ,[AdviceDate] ");
sql.AppendLine(" ,[Accept] ");
sql.AppendLine(" ,[State] ");
sql.AppendLine(" ,[Contents] ");
if (!string.IsNullOrEmpty(model.DoSomething))
{
sql.AppendLine(" ,[DoSomething] ");
}
if (!string.IsNullOrEmpty(model.LeadSay))
{
sql.AppendLine(" ,[LeadSay] ");
}
if (!string.IsNullOrEmpty(model.Remark))
{
sql.AppendLine(" ,[Remark] ");
}
sql.AppendLine(" ,[ModifiedDate] ");
sql.AppendLine(" ,[ModifiedUserID] ");
sql.AppendLine(" ,[Creator] ");
sql.AppendLine(" ,[CreatedDate] ");
sql.AppendLine(" ,[CanViewUser] ");
sql.AppendLine(" ,[CanViewUserName]) ");
sql.AppendLine(" VALUES ");
sql.AppendLine(" (@CompanyCD ");
sql.AppendLine(" ,@AdviceNo ");
sql.AppendLine(" ,@Title ");
sql.AppendLine(" ,@CustID ");
sql.AppendLine(" ,@Advicer ");
sql.AppendLine(" ,@CustLinkMan ");
sql.AppendLine(" ,@DestClerk ");
sql.AppendLine(" ,@AdviceType ");
sql.AppendLine(" ,@AdviceDate ");
sql.AppendLine(" ,@Accept ");
sql.AppendLine(" ,@State ");
sql.AppendLine(" ,@Contents ");
if (!string.IsNullOrEmpty(model.DoSomething))
{
sql.AppendLine(" ,@DoSomething ");
}
if (!string.IsNullOrEmpty(model.LeadSay))
{
sql.AppendLine(" ,@LeadSay ");
}
if (!string.IsNullOrEmpty(model.Remark))
{
sql.AppendLine(" ,@Remark ");
}
sql.AppendLine(" ,@ModifiedDate ");
sql.AppendLine(" ,@ModifiedUserID ");
sql.AppendLine(" ,@Creator ");
sql.AppendLine(" ,@CreatedDate ");
sql.AppendLine(" ,@CanViewUser ");
sql.AppendLine(" ,@CanViewUserName ) ");
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@AdviceNo", model.AdviceNo));
comm.Parameters.Add(SqlHelper.GetParameter("@Title", model.Title));
comm.Parameters.Add(SqlHelper.GetParameter("@CustID", model.CustID));
comm.Parameters.Add(SqlHelper.GetParameter("@Advicer", model.Advicer));
comm.Parameters.Add(SqlHelper.GetParameter("@CustLinkMan", model.CustLinkMan));
comm.Parameters.Add(SqlHelper.GetParameter("@DestClerk", model.DestClerk));
comm.Parameters.Add(SqlHelper.GetParameter("@AdviceType", model.AdviceType));
comm.Parameters.Add(SqlHelper.GetParameter("@AdviceDate", model.AdviceDate));
comm.Parameters.Add(SqlHelper.GetParameter("@Accept", model.Accept));
comm.Parameters.Add(SqlHelper.GetParameter("@State", model.State));
comm.Parameters.Add(SqlHelper.GetParameter("@Contents", model.Contents));
if (!string.IsNullOrEmpty(model.DoSomething))
{
comm.Parameters.Add(SqlHelper.GetParameter("@DoSomething", model.DoSomething));
}
if (!string.IsNullOrEmpty(model.LeadSay))
{
comm.Parameters.Add(SqlHelper.GetParameter("@LeadSay", model.LeadSay));
}
if (!string.IsNullOrEmpty(model.Remark))
{
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
}
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", model.ModifiedDate));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameter("@CreatedDate", model.CreatedDate));
comm.Parameters.Add(SqlHelper.GetParameter("@CanViewUser", model.CanViewUser));
comm.Parameters.Add(SqlHelper.GetParameter("@CanViewUserName", model.CanViewUserName));
comm.CommandText = sql.ToString();
return(SqlHelper.ExecuteTransWithCommand(comm));
}