/// <summary>
/// 增加一条数据
/// </summary>
public void Add(LPWeb.Model.UserHomePref model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into UserHomePref(");
strSql.Append("UserId,CompanyCalendar,PipelineChart,SalesBreakdownChart,OrgProductionChart,Org_N_Sales_Charts,RateSummary,GoalsChart,OverDueTaskAlert,Announcements,ExchangeInbox,ExchangeCalendar,AlertFilter,DefaultClientsPipelineViewId,DefaultLoansPipelineViewId,DefaultLeadsPipelineViewId,DashboardLastCompletedStages,QuickLeadForm)");
strSql.Append(" values (");
strSql.Append("@UserId,@CompanyCalendar,@PipelineChart,@SalesBreakdownChart,@OrgProductionChart,@Org_N_Sales_Charts,@RateSummary,@GoalsChart,@OverDueTaskAlert,@Announcements,@ExchangeInbox,@ExchangeCalendar,@AlertFilter,@DefaultClientsPipelineViewId,@DefaultLoansPipelineViewId,@DefaultLeadsPipelineViewId,@DashboardLastCompletedStages,@QuickLeadForm)");
SqlParameter[] parameters =
{
new SqlParameter("@UserId", SqlDbType.Int, 4),
new SqlParameter("@CompanyCalendar", SqlDbType.Bit, 1),
new SqlParameter("@PipelineChart", SqlDbType.Bit, 1),
new SqlParameter("@SalesBreakdownChart", SqlDbType.Bit, 1),
new SqlParameter("@OrgProductionChart", SqlDbType.Bit, 1),
new SqlParameter("@Org_N_Sales_Charts", SqlDbType.Bit, 1),
new SqlParameter("@RateSummary", SqlDbType.Bit, 1),
new SqlParameter("@GoalsChart", SqlDbType.Bit, 1),
new SqlParameter("@OverDueTaskAlert", SqlDbType.Bit, 1),
new SqlParameter("@Announcements", SqlDbType.Bit, 1),
new SqlParameter("@ExchangeInbox", SqlDbType.Bit, 1),
new SqlParameter("@ExchangeCalendar", SqlDbType.Bit, 1),
new SqlParameter("@AlertFilter", SqlDbType.Int),
new SqlParameter("@DefaultClientsPipelineViewId", SqlDbType.Int),
new SqlParameter("@DefaultLoansPipelineViewId", SqlDbType.Int),
new SqlParameter("@DefaultLeadsPipelineViewId", SqlDbType.Int),
new SqlParameter("@DashboardLastCompletedStages", SqlDbType.Int),
new SqlParameter("@QuickLeadForm", SqlDbType.Bit, 1)
};
parameters[0].Value = model.UserId;
parameters[1].Value = model.CompanyCalendar;
parameters[2].Value = model.PipelineChart;
parameters[3].Value = model.SalesBreakdownChart;
parameters[4].Value = model.OrgProductionChart;
parameters[5].Value = model.Org_N_Sales_Charts;
parameters[6].Value = model.RateSummary;
parameters[7].Value = model.GoalsChart;
parameters[8].Value = model.OverDueTaskAlert;
parameters[9].Value = model.Announcements;
parameters[10].Value = model.ExchangeInbox;
parameters[11].Value = model.ExchangeCalendar;
parameters[12].Value = model.AlertFilter;
parameters[13].Value = model.DefaultClientsPipelineViewId;
parameters[14].Value = model.DefaultLoansPipelineViewId;
parameters[15].Value = model.DefaultLeadsPipelineViewId;
parameters[16].Value = model.DashboardLastCompletedStages;
parameters[17].Value = model.QuickLeadForm;
DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(LPWeb.Model.UserHomePref model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update UserHomePref set ");
strSql.Append("UserId=@UserId,");
strSql.Append("CompanyCalendar=@CompanyCalendar,");
strSql.Append("PipelineChart=@PipelineChart,");
strSql.Append("SalesBreakdownChart=@SalesBreakdownChart,");
strSql.Append("OrgProductionChart=@OrgProductionChart,");
strSql.Append("Org_N_Sales_Charts=@Org_N_Sales_Charts,");
strSql.Append("RateSummary=@RateSummary,");
strSql.Append("GoalsChart=@GoalsChart,");
strSql.Append("OverDueTaskAlert=@OverDueTaskAlert,");
strSql.Append("Announcements=@Announcements,");
strSql.Append("ExchangeInbox=@ExchangeInbox,");
strSql.Append("ExchangeCalendar=@ExchangeCalendar,");
strSql.Append("AlertFilter = @AlertFilter,");
strSql.Append("DefaultClientsPipelineViewId = @DefaultClientsPipelineViewId,");
strSql.Append("DefaultLoansPipelineViewId = @DefaultLoansPipelineViewId,");
strSql.Append("DefaultLeadsPipelineViewId = @DefaultLeadsPipelineViewId,");
strSql.Append("DashboardLastCompletedStages = @DashboardLastCompletedStages,");
strSql.Append("QuickLeadForm = @QuickLeadForm");
strSql.Append(" where UserId=@UserId ");
SqlParameter[] parameters =
{
new SqlParameter("@UserId", SqlDbType.Int, 4),
new SqlParameter("@CompanyCalendar", SqlDbType.Bit, 1),
new SqlParameter("@PipelineChart", SqlDbType.Bit, 1),
new SqlParameter("@SalesBreakdownChart", SqlDbType.Bit, 1),
new SqlParameter("@OrgProductionChart", SqlDbType.Bit, 1),
new SqlParameter("@Org_N_Sales_Charts", SqlDbType.Bit, 1),
new SqlParameter("@RateSummary", SqlDbType.Bit, 1),
new SqlParameter("@GoalsChart", SqlDbType.Bit, 1),
new SqlParameter("@OverDueTaskAlert", SqlDbType.Bit, 1),
new SqlParameter("@Announcements", SqlDbType.Bit, 1),
new SqlParameter("@ExchangeInbox", SqlDbType.Bit, 1),
new SqlParameter("@ExchangeCalendar", SqlDbType.Bit, 1),
new SqlParameter("@AlertFilter", SqlDbType.Int),
new SqlParameter("@DefaultClientsPipelineViewId", SqlDbType.Int),
new SqlParameter("@DefaultLoansPipelineViewId", SqlDbType.Int),
new SqlParameter("@DefaultLeadsPipelineViewId", SqlDbType.Int),
new SqlParameter("@DashboardLastCompletedStages", SqlDbType.Int),
new SqlParameter("@QuickLeadForm", SqlDbType.Bit, 1)
};
parameters[0].Value = model.UserId;
parameters[1].Value = model.CompanyCalendar;
parameters[2].Value = model.PipelineChart;
parameters[3].Value = model.SalesBreakdownChart;
parameters[4].Value = model.OrgProductionChart;
parameters[5].Value = model.Org_N_Sales_Charts;
parameters[6].Value = model.RateSummary;
parameters[7].Value = model.GoalsChart;
parameters[8].Value = model.OverDueTaskAlert;
parameters[9].Value = model.Announcements;
parameters[10].Value = model.ExchangeInbox;
parameters[11].Value = model.ExchangeCalendar;
parameters[12].Value = model.AlertFilter;
parameters[13].Value = model.DefaultClientsPipelineViewId;
parameters[14].Value = model.DefaultLoansPipelineViewId;
parameters[15].Value = model.DefaultLeadsPipelineViewId;
parameters[16].Value = model.DashboardLastCompletedStages;
parameters[17].Value = model.QuickLeadForm;
DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public LPWeb.Model.UserHomePref GetModel(int UserId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 UserId,CompanyCalendar,PipelineChart,SalesBreakdownChart,OrgProductionChart,Org_N_Sales_Charts,RateSummary,GoalsChart,OverDueTaskAlert,Announcements,ExchangeInbox,ExchangeCalendar,AlertFilter,DefaultClientsPipelineViewId,DefaultLoansPipelineViewId,DefaultLeadsPipelineViewId,DashboardLastCompletedStages,QuickLeadForm from UserHomePref ");
strSql.Append(" where UserId=@UserId ");
SqlParameter[] parameters =
{
new SqlParameter("@UserId", SqlDbType.Int, 4)
};
parameters[0].Value = UserId;
LPWeb.Model.UserHomePref model = new LPWeb.Model.UserHomePref();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["UserId"].ToString() != "")
{
model.UserId = int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
}
if (ds.Tables[0].Rows[0]["CompanyCalendar"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["CompanyCalendar"].ToString() == "1") || (ds.Tables[0].Rows[0]["CompanyCalendar"].ToString().ToLower() == "true"))
{
model.CompanyCalendar = true;
}
else
{
model.CompanyCalendar = false;
}
}
if (ds.Tables[0].Rows[0]["PipelineChart"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["PipelineChart"].ToString() == "1") || (ds.Tables[0].Rows[0]["PipelineChart"].ToString().ToLower() == "true"))
{
model.PipelineChart = true;
}
else
{
model.PipelineChart = false;
}
}
if (ds.Tables[0].Rows[0]["SalesBreakdownChart"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["SalesBreakdownChart"].ToString() == "1") || (ds.Tables[0].Rows[0]["SalesBreakdownChart"].ToString().ToLower() == "true"))
{
model.SalesBreakdownChart = true;
}
else
{
model.SalesBreakdownChart = false;
}
}
if (ds.Tables[0].Rows[0]["OrgProductionChart"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["OrgProductionChart"].ToString() == "1") || (ds.Tables[0].Rows[0]["OrgProductionChart"].ToString().ToLower() == "true"))
{
model.OrgProductionChart = true;
}
else
{
model.OrgProductionChart = false;
}
}
if (ds.Tables[0].Rows[0]["Org_N_Sales_Charts"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["Org_N_Sales_Charts"].ToString() == "1") || (ds.Tables[0].Rows[0]["Org_N_Sales_Charts"].ToString().ToLower() == "true"))
{
model.Org_N_Sales_Charts = true;
}
else
{
model.Org_N_Sales_Charts = false;
}
}
if (ds.Tables[0].Rows[0]["RateSummary"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["RateSummary"].ToString() == "1") || (ds.Tables[0].Rows[0]["RateSummary"].ToString().ToLower() == "true"))
{
model.RateSummary = true;
}
else
{
model.RateSummary = false;
}
}
if (ds.Tables[0].Rows[0]["GoalsChart"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["GoalsChart"].ToString() == "1") || (ds.Tables[0].Rows[0]["GoalsChart"].ToString().ToLower() == "true"))
{
model.GoalsChart = true;
}
else
{
model.GoalsChart = false;
}
}
if (ds.Tables[0].Rows[0]["OverDueTaskAlert"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["OverDueTaskAlert"].ToString() == "1") || (ds.Tables[0].Rows[0]["OverDueTaskAlert"].ToString().ToLower() == "true"))
{
model.OverDueTaskAlert = true;
}
else
{
model.OverDueTaskAlert = false;
}
}
if (ds.Tables[0].Rows[0]["Announcements"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["Announcements"].ToString() == "1") || (ds.Tables[0].Rows[0]["Announcements"].ToString().ToLower() == "true"))
{
model.Announcements = true;
}
else
{
model.Announcements = false;
}
}
if (ds.Tables[0].Rows[0]["ExchangeInbox"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["ExchangeInbox"].ToString() == "1") || (ds.Tables[0].Rows[0]["ExchangeInbox"].ToString().ToLower() == "true"))
{
model.ExchangeInbox = true;
}
else
{
model.ExchangeInbox = false;
}
}
if (ds.Tables[0].Rows[0]["ExchangeCalendar"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["ExchangeCalendar"].ToString() == "1") || (ds.Tables[0].Rows[0]["ExchangeCalendar"].ToString().ToLower() == "true"))
{
model.ExchangeCalendar = true;
}
else
{
model.ExchangeCalendar = false;
}
}
model.AlertFilter = ds.Tables[0].Rows[0]["AlertFilter"] != DBNull.Value ? Convert.ToInt32(ds.Tables[0].Rows[0]["AlertFilter"]) : 0;
//gdc CR45
model.DashboardLastCompletedStages = ds.Tables[0].Rows[0]["DashboardLastCompletedStages"] != DBNull.Value ? Convert.ToInt32(ds.Tables[0].Rows[0]["DashboardLastCompletedStages"]) : 0;
model.DefaultClientsPipelineViewId = ds.Tables[0].Rows[0]["DefaultClientsPipelineViewId"] != DBNull.Value ? Convert.ToInt32(ds.Tables[0].Rows[0]["DefaultClientsPipelineViewId"]) : 0;
model.DefaultLeadsPipelineViewId = ds.Tables[0].Rows[0]["DefaultLeadsPipelineViewId"] != DBNull.Value ? Convert.ToInt32(ds.Tables[0].Rows[0]["DefaultLeadsPipelineViewId"]) : 0;
model.DefaultLoansPipelineViewId = ds.Tables[0].Rows[0]["DefaultLoansPipelineViewId"] != DBNull.Value ? Convert.ToInt32(ds.Tables[0].Rows[0]["DefaultLoansPipelineViewId"]) : 0;
if (ds.Tables[0].Rows[0]["QuickLeadForm"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["QuickLeadForm"].ToString() == "1") || (ds.Tables[0].Rows[0]["QuickLeadForm"].ToString().ToLower() == "true"))
{
model.QuickLeadForm = true;
}
else
{
model.QuickLeadForm = false;
}
}
return(model);
}
else
{
return(null);
}
}
protected void Page_Load(object sender, EventArgs e)
{
// 获取当前用户信息
this.CurrentUser = new LoginUser();
this.iCurrentUserID = CurrentUser.iUserID;
// 默认条件
string sWhere_DueDate = " and datediff(day, getdate(), a.DueDate) < 0";
#region 获取 UserHomePref 信息
BLL.UserHomePref UserHomePrefManager = new BLL.UserHomePref();
LPWeb.Model.UserHomePref userHomePref = UserHomePrefManager.GetModel(this.iCurrentUserID);
#endregion
if (userHomePref != null && userHomePref.AlertFilter == 7) // Overdue + Today
{
sWhere_DueDate = " and datediff(day, getdate(), a.DueDate) <= 0";
this.ddlDueDateFilter.SelectedIndex = 1;
}
#region DueDate Filter
// Due
if (this.Request.QueryString["DueDate"] != null)
{
string sDue = this.Request.QueryString["DueDate"].ToString();
if (sDue == "In30") // Due in the next month
{
sWhere_DueDate = " and datediff(day, getdate(), a.DueDate) <= 30 and datediff(day, getdate(), a.DueDate) >=0";
}
else if (sDue == "In14") // Due in the next 2 weeks
{
sWhere_DueDate = " and datediff(day, getdate(), a.DueDate) <= 14 and datediff(day, getdate(), a.DueDate) >=0";
}
else if (sDue == "In7") // Due in the next 7 days
{
sWhere_DueDate = " and datediff(day, getdate(), a.DueDate) <= 7 and datediff(day, getdate(), a.DueDate) >=0";
}
else if (sDue == "In1") // Due tomorrow
{
sWhere_DueDate = " and datediff(day, getdate(), a.DueDate) = 1";
}
else if (sDue == "In0") // Due today
{
sWhere_DueDate = " and datediff(day, getdate(), a.DueDate) = 0";
}
else if (sDue == "Overdue")
{
sWhere_DueDate = " and datediff(day, getdate(), a.DueDate) < 0";
}
else if (sDue == "OverToday") // Overdue + Today
{
sWhere_DueDate = " and datediff(day, getdate(), a.DueDate) <= 0";
}
}
#endregion
#region 加载Alerts
LPWeb.BLL.LoanAlerts AlertManager = new LPWeb.BLL.LoanAlerts();
DataTable AlertListData = new DataTable();
AlertListData = AlertManager.Loan_GetSimpleAlertList(this.iCurrentUserID, sWhere_DueDate);
AlertListData.Columns.Add("HRef", typeof(string));
foreach (DataRow dr in AlertListData.Rows)
{
string Url = string.Empty;
string CloseDialogCode = "CloseDialogCodes=sharepoint";
if (dr["AlertType"] != DBNull.Value)
{
string alertType = dr["AlertType"].ToString();
switch (alertType.ToLower())
{
case "rule alert":
Url = string.Format("LoanDetails/RuleAlertPopup.aspx?LoanID={0}&AlertID={1}&{2}", dr["FileId"].ToString(), dr["LoanAlertId"].ToString(), CloseDialogCode);
break;
case "task alert":
Url = string.Format("Pipeline/TaskAlertDetail.aspx?fileID={0}&LoanTaskId={1}&{2}", dr["FileId"].ToString(), dr["LoanTaskId"].ToString(), CloseDialogCode);
break;
}
}
dr["HRef"] = Url;
}
this.rpAlertList.DataSource = AlertListData;
this.rpAlertList.DataBind();
#endregion
}
