protected void Page_Load(object sender, EventArgs e)
{
if (CurrUser.userRole.ContactBranch.ToString().IndexOf('1') == -1)
{
Response.Redirect("../Unauthorize.aspx"); // have not View Power
return;
}
sHasCreate = CurrUser.userRole.ContactBranch.ToString().IndexOf('2') > -1 ? "1" : "0";
sHasModify = CurrUser.userRole.ContactBranch.ToString().IndexOf('3') > -1 ? "1" : "0";
sHasDelete = CurrUser.userRole.ContactBranch.ToString().IndexOf('4') > -1 ? "1" : "0";
sHasDisable = CurrUser.userRole.ContactBranch.ToString().IndexOf('5') > -1 ? "1" : "0";
sHasAddBranch = CurrUser.userRole.ContactBranch.ToString().IndexOf('6') > -1 ? "1" : "0";
sHasRemoveBranch = CurrUser.userRole.ContactBranch.ToString().IndexOf('7') > -1 ? "1" : "0";
sHasModifyCompany = CurrUser.userRole.ContactCompany.ToString().IndexOf('3') > -1 ? "1" : "0"; //Company Modify
sHasModifyServiceType = CurrUser.userRole.ServiceType.ToString().IndexOf('1') > -1 ? "1" : "0"; //Service Type View
if (sHasDelete == "0")
{
btnDelete.Enabled = false;
}
if (sHasDisable == "0")
{
btnDisable.Enabled = false;
}
#region 加载Service Type Filter
ServiceTypes ServiceTypeManager = new ServiceTypes();
DataTable ServiceTypeList = ServiceTypeManager.GetServiceTypeList(" and (Enabled=1)");
DataRow NewServiceTypeRow = ServiceTypeList.NewRow();
NewServiceTypeRow["ServiceTypeId"] = "0";
NewServiceTypeRow["Name"] = "All Service Types";
ServiceTypeList.Rows.InsertAt(NewServiceTypeRow, 0);
ServiceTypeList.AcceptChanges();
this.ddlServiceType.DataSource = ServiceTypeList;
this.ddlServiceType.DataBind();
#endregion
#region 加载Company Filter
ContactCompanies ContactCompaniesManager = new ContactCompanies();
DataSet ContactCompaniesList = ContactCompaniesManager.GetList("(Enabled=1) order by Name");
//if (ContactCompaniesList != null && ContactCompaniesList.Tables[0].Rows.Count > 0)
//{
DataRow NewContactCompaniesRow = ContactCompaniesList.Tables[0].NewRow();
NewContactCompaniesRow["ContactCompanyId"] = "0";
NewContactCompaniesRow["Name"] = "All Companies";
ContactCompaniesList.Tables[0].Rows.InsertAt(NewContactCompaniesRow, 0);
ContactCompaniesList.Tables[0].AcceptChanges();
this.ddlCompany.DataSource = ContactCompaniesList.Tables[0];
this.ddlCompany.DataBind();
//}
#endregion
#region 加载States Filter
LPWeb.Layouts.LPWeb.Common.USStates.Init(this.ddlStates);
#endregion
#region 加载Company列表
this.BranchSqlDataSource.ConnectionString = LPWeb.DAL.DbHelperSQL.connectionString;
string sWhere = string.Empty;
bool bSetCondition = false;
// Alphabet
if (this.Request.QueryString["Alphabet"] != null)
{
string sAlphabet = this.Request.QueryString["Alphabet"].ToString();
sWhere += " and (Branch like '" + sAlphabet + "%')";
bSetCondition = true;
}
// Service Type
if (this.Request.QueryString["ServiceType"] != null)
{
string sServiceType = this.Request.QueryString["ServiceType"].ToString();
if (PageCommon.IsID(sServiceType) == true)
{
sWhere += " and (ServiceTypeId =" + SqlTextBuilder.ConvertQueryValue(sServiceType) + ")";
}
else
{
sWhere += " and (ServiceTypeId =0)";
}
bSetCondition = true;
}
// Company
if (this.Request.QueryString["Company"] != null)
{
string sCompany = this.Request.QueryString["Company"].ToString();
if (PageCommon.IsID(sCompany) == true)
{
sWhere += " and (ContactCompanyId =" + SqlTextBuilder.ConvertQueryValue(sCompany) + ")";
}
else
{
sWhere += " and (ContactCompanyId =0)";
}
bSetCondition = true;
}
// State
if (this.Request.QueryString["State"] != null)
{
string sState = this.Request.QueryString["State"].ToString();
if (sState != "")
{
sWhere += " and ([State] ='" + SqlTextBuilder.ConvertQueryValue(sState) + "')";
bSetCondition = true;
}
}
int iRowCount = LPWeb.DAL.DbHelperSQL.Count(this.BranchSqlDataSource.SelectParameters["DbTable"].DefaultValue, sWhere);
// empty data text
if (iRowCount == 0 && bSetCondition == true)
{
this.gridBranchList.EmptyDataText = "There is no partner branch by search criteria，please search again.";
}
else
{
this.gridBranchList.EmptyDataText = "There is no partner branch.";
}
this.AspNetPager1.RecordCount = iRowCount;
this.BranchSqlDataSource.SelectParameters["Where"].DefaultValue = sWhere;
this.gridBranchList.DataBind();
#endregion
}
protected void Page_Load(object sender, EventArgs e)
{
// 获取当前用户信息
this.CurrentUser = new LoginUser();
this.iCurrentUserID = CurrentUser.iUserID;
#region chart title
string sChartTitle = string.Empty;
if (this.Request.QueryString["DateRange"] != null)
{
this.sDateRange = this.Request.QueryString["DateRange"].ToString();
if (this.sDateRange != "ThisMonth" && this.sDateRange != "NextMonth" &&
this.sDateRange != "ThisQuarter" && this.sDateRange != "NextQuarter" &&
this.sDateRange != "ThisYear" && this.sDateRange != "NextYear")
{
this.sDateRange = "ThisMonth";
}
}
if (this.sDateRange.Contains("Month") == true)
{
sChartTitle = "Monthly Production Goals";
}
else if (this.sDateRange.Contains("Quarter") == true)
{
sChartTitle = "Quarterly Production Goals";
}
else if (this.sDateRange.Contains("Year") == true)
{
sChartTitle = "Yearly Production Goals";
}
#endregion
#region low/medium/high range
int iLowRangeEnd = 0;
int iMediumRangeEnd = 0;
int iHighRangeEnd = 0;
DataTable UserGoalsInfo;
// get month
if (this.sDateRange.Contains("Month") == true)
{
int iMonth = 0;
if (this.sDateRange == "ThisMonth")
{
iMonth = DateTime.Now.Month;
}
else // NextMonth
{
iMonth = DateTime.Now.AddMonths(1).Month;
}
// get user goals info
UserGoalsInfo = UserGoalsManager.GetUserGoals(this.iCurrentUserID, iMonth);
}
else if (this.sDateRange.Contains("Quarter") == true)
{
int iThisMonth = DateTime.Now.Month;
string sMonths = string.Empty;
// 计算This Quarter
int iThisQuarter = this.GetQuarter(iThisMonth);
if (this.sDateRange == "ThisQuarter")
{
#region ThisQuarter
if (iThisQuarter == 1)
{
sMonths = "1,2,3";
}
else if (iThisQuarter == 2)
{
sMonths = "4,5,6";
}
if (iThisQuarter == 3)
{
sMonths = "7,8,9";
}
if (iThisQuarter == 4)
{
sMonths = "10,11,12";
}
#endregion
}
else if (this.sDateRange == "NextQuarter")
{
#region NextQuarter
int iNextQuarter = iThisQuarter + 1;
if (iNextQuarter > 4)
{
iNextQuarter = 1;
}
if (iNextQuarter == 1)
{
sMonths = "1,2,3";
}
else if (iNextQuarter == 2)
{
sMonths = "4,5,6";
}
if (iNextQuarter == 3)
{
sMonths = "7,8,9";
}
if (iNextQuarter == 4)
{
sMonths = "10,11,12";
}
#endregion
}
// get user goals info
UserGoalsInfo = UserGoalsManager.GetUserGoals(this.iCurrentUserID, sMonths);
}
else
{
// get user goals info
UserGoalsInfo = UserGoalsManager.GetUserGoals(this.iCurrentUserID);
}
// if no user goals setup
if (UserGoalsInfo.Rows.Count == 0)
{
this.Response.End();
}
if (UserGoalsInfo.Rows[0]["LowRange"] == DBNull.Value ||
UserGoalsInfo.Rows[0]["MediumRange"] == DBNull.Value ||
UserGoalsInfo.Rows[0]["HighRange"] == DBNull.Value)
{
this.Response.End();
}
iLowRangeEnd = Convert.ToInt32(UserGoalsInfo.Rows[0]["LowRange"]);
iMediumRangeEnd = Convert.ToInt32(UserGoalsInfo.Rows[0]["MediumRange"]);
iHighRangeEnd = Convert.ToInt32(UserGoalsInfo.Rows[0]["HighRange"]);
#endregion
#region scale interval
int iScaleInterval = 6;
decimal dScaleRange = iHighRangeEnd / iScaleInterval;
iScaleInterval = Convert.ToInt32(dScaleRange / 1000);
#endregion
#region calculate real amount
int iRealAmount = 0;
string sWhere = string.Empty;
string sSqlx1 = string.Empty;
object oLoanAmount = null;
DataTable UserLoanList = null;
string sFiledName = "b.LastStageComplDate";
string sStageAlias = "Closed";
if (this.sDateRange.Contains("Month") == true)
{
if (this.sDateRange == "ThisMonth")
{
#region ThisMonth
// 获取本月天数
int iDaysInMonth = DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
// 本月开始日期和结束日期
DateTime StartDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
DateTime EndDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, iDaysInMonth);
//// 获取Loan Amount
//decimal dLoanAmount = this.UserGoalsManager.GetUserLoanAmount_This(this.iCurrentUserID, StartDate, EndDate);
//// 转换成int
//iRealAmount = Convert.ToInt32(dLoanAmount);
sWhere += SqlTextBuilder.BuildDateSearchCondition(sFiledName, StartDate, EndDate);
sSqlx1 = "select b.Amount as Amount, b.Status as Status from lpfn_GetUserLoans(" + this.iCurrentUserID + ") as a inner join V_ProcessingPipelineInfo as b on a.LoanID = b.FileId "
+ "where (1=1) and (b.Stage='Closed') " + sWhere;
UserLoanList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSqlx1);
oLoanAmount = UserLoanList.Compute("Sum(Amount)", "Status='Closed'");
if ((oLoanAmount != null) && (oLoanAmount != DBNull.Value))
{
iRealAmount = Convert.ToInt32(oLoanAmount, null);
}
#endregion
}
else if (this.sDateRange == "NextMonth")
{
#region NextMonth
// 获取下个月天数
int iDaysInNextMonth = DateTime.DaysInMonth(DateTime.Now.AddMonths(1).Year, DateTime.Now.AddMonths(1).Month);
// 本月开始日期和结束日期
DateTime StartDate = new DateTime(DateTime.Now.AddMonths(1).Year, DateTime.Now.AddMonths(1).Month, 1);
DateTime EndDate = new DateTime(DateTime.Now.AddMonths(1).Year, DateTime.Now.AddMonths(1).Month, iDaysInNextMonth);
//// 获取Loan Amount
//decimal dLoanAmount = this.UserGoalsManager.GetUserLoanAmount_Next(this.iCurrentUserID, StartDate, EndDate);
//// 转换成int
//iRealAmount = Convert.ToInt32(dLoanAmount);
sWhere += SqlTextBuilder.BuildDateSearchCondition(sFiledName, StartDate, EndDate);
sSqlx1 = "select b.Amount as Amount, b.Status as Status from lpfn_GetUserLoans(" + this.iCurrentUserID + ") as a inner join V_ProcessingPipelineInfo as b on a.LoanID = b.FileId "
+ "where (1=1) and (b.Stage='Closed') " + sWhere;
UserLoanList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSqlx1);
oLoanAmount = UserLoanList.Compute("Sum(Amount)", "Stage='" + sStageAlias + "'");
if ((oLoanAmount != null) && (oLoanAmount != DBNull.Value))
{
iRealAmount = Convert.ToInt32(oLoanAmount, null);
}
#endregion
}
}
else if (this.sDateRange.Contains("Quarter") == true)
{
// 计算This Quarter
int iThisQuarter = this.GetQuarter(DateTime.Now.Month);
if (this.sDateRange == "ThisQuarter")
{
#region ThisQuarter
DateTime StartDate = DateTime.Now;
DateTime EndDate = DateTime.Now;
if (iThisQuarter == 1) // 一季度=1,2,3月
{
// 1月开始日期
StartDate = new DateTime(DateTime.Now.Year, 1, 1);
// 获取3月天数
int iDaysIn3 = DateTime.DaysInMonth(DateTime.Now.Year, 3);
// 结束日期
EndDate = new DateTime(DateTime.Now.Year, 3, iDaysIn3);
}
else if (iThisQuarter == 2) // 二季度=4,5,6月
{
// 4月开始日期
StartDate = new DateTime(DateTime.Now.Year, 4, 1);
// 获取6月天数
int iDaysIn6 = DateTime.DaysInMonth(DateTime.Now.Year, 6);
// 结束日期
EndDate = new DateTime(DateTime.Now.Year, 6, iDaysIn6);
}
else if (iThisQuarter == 3) // 三季度=7,8,9月
{
// 7月开始日期
StartDate = new DateTime(DateTime.Now.Year, 7, 1);
// 获取9月天数
int iDaysIn9 = DateTime.DaysInMonth(DateTime.Now.Year, 9);
// 结束日期
EndDate = new DateTime(DateTime.Now.Year, 9, iDaysIn9);
}
else if (iThisQuarter == 4) // 四季度=10,11,12月
{
// 7月开始日期
StartDate = new DateTime(DateTime.Now.Year, 10, 1);
// 获取12月天数
int iDaysIn12 = DateTime.DaysInMonth(DateTime.Now.Year, 12);
// 结束日期
EndDate = new DateTime(DateTime.Now.Year, 12, iDaysIn12);
}
//// 获取Loan Amount
//decimal dLoanAmount = this.UserGoalsManager.GetUserLoanAmount_This(this.iCurrentUserID, StartDate, EndDate);
//// 转换成int
//iRealAmount = Convert.ToInt32(dLoanAmount);
sWhere += SqlTextBuilder.BuildDateSearchCondition(sFiledName, StartDate, EndDate);
sSqlx1 = "select b.Amount as Amount, b.Status as Status from lpfn_GetUserLoans(" + this.iCurrentUserID + ") as a inner join V_ProcessingPipelineInfo as b on a.LoanID = b.FileId "
+ "where (1=1) and (b.Stage='Closed') " + sWhere;
UserLoanList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSqlx1);
oLoanAmount = UserLoanList.Compute("Sum(Amount)", "Stage='" + sStageAlias + "'");
if ((oLoanAmount != null) && (oLoanAmount != DBNull.Value))
{
iRealAmount = Convert.ToInt32(oLoanAmount, null);
}
#endregion
}
else if (this.sDateRange == "NextQuarter")
{
#region NextQuarter
int iNextQuarter = iThisQuarter + 1;
int iNextYear = DateTime.Now.Year;
if (iNextQuarter > 4)
{
iNextQuarter = 1;
iNextYear = DateTime.Now.AddYears(1).Year;
}
DateTime StartDate = DateTime.Now;
DateTime EndDate = DateTime.Now;
if (iNextQuarter == 1) // 一季度=1,2,3月
{
// 1月开始日期
StartDate = new DateTime(iNextYear, 1, 1);
// 获取3月天数
int iDaysIn3 = DateTime.DaysInMonth(iNextYear, 3);
// 结束日期
EndDate = new DateTime(iNextYear, 3, iDaysIn3);
}
else if (iNextQuarter == 2) // 二季度=4,5,6月
{
// 4月开始日期
StartDate = new DateTime(iNextYear, 4, 1);
// 获取6月天数
int iDaysIn6 = DateTime.DaysInMonth(iNextYear, 6);
// 结束日期
EndDate = new DateTime(iNextYear, 6, iDaysIn6);
}
else if (iNextQuarter == 3) // 三季度=7,8,9月
{
// 7月开始日期
StartDate = new DateTime(iNextYear, 7, 1);
// 获取9月天数
int iDaysIn9 = DateTime.DaysInMonth(iNextYear, 9);
// 结束日期
EndDate = new DateTime(iNextYear, 9, iDaysIn9);
}
else if (iNextQuarter == 4) // 四季度=10,11,12月
{
// 7月开始日期
StartDate = new DateTime(iNextYear, 10, 1);
// 获取12月天数
int iDaysIn12 = DateTime.DaysInMonth(iNextYear, 12);
// 结束日期
EndDate = new DateTime(iNextYear, 12, iDaysIn12);
}
//// 获取Loan Amount
//decimal dLoanAmount = this.UserGoalsManager.GetUserLoanAmount_Next(this.iCurrentUserID, StartDate, EndDate);
//// 转换成int
//iRealAmount = Convert.ToInt32(dLoanAmount);
sWhere += SqlTextBuilder.BuildDateSearchCondition(sFiledName, StartDate, EndDate);
sSqlx1 = "select b.Amount as Amount, b.Status as Status from lpfn_GetUserLoans(" + this.iCurrentUserID + ") as a inner join V_ProcessingPipelineInfo as b on a.LoanID = b.FileId "
+ "where (1=1) and (b.Stage='Closed') " + sWhere;
UserLoanList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSqlx1);
oLoanAmount = UserLoanList.Compute("Sum(Amount)", "Stage='" + sStageAlias + "'");
if ((oLoanAmount != null) && (oLoanAmount != DBNull.Value))
{
iRealAmount = Convert.ToInt32(oLoanAmount, null);
}
#endregion
}
}
else if (this.sDateRange.Contains("Year") == true)
{
if (this.sDateRange == "ThisYear")
{
#region ThisYear
// 开始日期：1月1日
DateTime StartDate = new DateTime(DateTime.Now.Year, 1, 1);
// 获取12月天数
int iDaysIn12 = DateTime.DaysInMonth(DateTime.Now.Year, 12);
// 结束日期
DateTime EndDate = new DateTime(DateTime.Now.Year, 12, iDaysIn12);
//// 获取Loan Amount
//decimal dLoanAmount = this.UserGoalsManager.GetUserLoanAmount_This(this.iCurrentUserID, StartDate, EndDate);
//// 转换成int
//iRealAmount = Convert.ToInt32(dLoanAmount);
sWhere += SqlTextBuilder.BuildDateSearchCondition(sFiledName, StartDate, EndDate);
sSqlx1 = "select b.Amount as Amount, b.Status as Status from lpfn_GetUserLoans(" + this.iCurrentUserID + ") as a inner join V_ProcessingPipelineInfo as b on a.LoanID = b.FileId "
+ "where (1=1) and (b.Stage='Closed') " + sWhere;
UserLoanList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSqlx1);
oLoanAmount = UserLoanList.Compute("Sum(Amount)", "Stage='" + sStageAlias + "'");
if ((oLoanAmount != null) && (oLoanAmount != DBNull.Value))
{
iRealAmount = Convert.ToInt32(oLoanAmount, null);
}
#endregion
}
else if (this.sDateRange == "NextYear")
{
#region NextYear
// 开始日期：1月1日
DateTime StartDate = new DateTime(DateTime.Now.AddYears(1).Year, 1, 1);
// 获取12月天数
int iDaysIn12 = DateTime.DaysInMonth(DateTime.Now.AddYears(1).Year, 12);
// 结束日期
DateTime EndDate = new DateTime(DateTime.Now.AddYears(1).Year, 12, iDaysIn12);
//// 获取Loan Amount
//decimal dLoanAmount = this.UserGoalsManager.GetUserLoanAmount_Next(this.iCurrentUserID, StartDate, EndDate);
//// 转换成int
//iRealAmount = Convert.ToInt32(dLoanAmount);
sWhere += SqlTextBuilder.BuildDateSearchCondition(sFiledName, StartDate, EndDate);
sSqlx1 = "select b.Amount as Amount, b.Status as Status from lpfn_GetUserLoans(" + this.iCurrentUserID + ") as a inner join V_ProcessingPipelineInfo as b on a.LoanID = b.FileId "
+ "where (1=1) and (b.Stage='Closed') " + sWhere;
UserLoanList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSqlx1);
oLoanAmount = UserLoanList.Compute("Sum(Amount)", "Stage='" + sStageAlias + "'");
if ((oLoanAmount != null) && (oLoanAmount != DBNull.Value))
{
iRealAmount = Convert.ToInt32(oLoanAmount, null);
}
#endregion
}
}
#endregion
sLowRangeEnd = (iLowRangeEnd / 1000).ToString();
sMediumRangeEnd = (iMediumRangeEnd / 1000).ToString();
sHighRangeEnd = (iHighRangeEnd / 1000).ToString();
sScaleInterval = iScaleInterval.ToString();
sRealAmount = (iRealAmount / 1000).ToString();
}
/// <summary>
/// 根据用户界面选择生成过滤条件
/// </summary>
/// <returns></returns>
private string GenerateQueryCondition()
{
string sWhere = " 0=0 ";
// Alphabet
if (!string.IsNullOrEmpty(this.Request.QueryString["Alphabet"]))
{
string sAlphabet = this.Request.QueryString["Alphabet"].ToString();
sWhere += " and (ContactName like '" + sAlphabet + "%')";
}
// Alphabet
if (!string.IsNullOrEmpty(this.Request.QueryString["PageIndex"]))
{
string sPageIndex = this.Request.QueryString["PageIndex"].ToString();
if (PageCommon.IsID(sPageIndex) == true && sPageIndex != "1")
{
try
{
PageIndex = int.Parse(sPageIndex);
}
catch
{
PageIndex = 1;
}
}
else
{
PageIndex = 1;
}
}
else
{
PageIndex = 1;
}
// Service Type
if (!string.IsNullOrEmpty(this.Request.QueryString["ServiceType"]))
{
string sServiceType = this.Request.QueryString["ServiceType"].ToString();
if (PageCommon.IsID(sServiceType) == true && sServiceType != "0")
{
sWhere += " and (ServiceTypeId =" + SqlTextBuilder.ConvertQueryValue(sServiceType) + ")";
}
}
// CompanyID
if (!string.IsNullOrEmpty(this.Request.QueryString["CompanyID"]))
{
string sCompanyID = this.Request.QueryString["CompanyID"].ToString();
if (PageCommon.IsID(sCompanyID) == true && sCompanyID != "0")
{
sWhere += string.Format(" AND ContactCompanyId={0}", sCompanyID);
}
}
if (!string.IsNullOrEmpty(this.Request.QueryString["BranchID"]))
{
string sBranchID = this.Request.QueryString["BranchID"].ToString();
if (PageCommon.IsID(sBranchID) == true && sBranchID != "0")
{
sWhere += string.Format(" AND ContactBranchId={0}", sBranchID);
}
}
if (this.Request.QueryString["Referral"] != null)
{
sWhere += " and ContactId in (select top(10) ContactId from "
+ "(select dbo.lpfn_GetTotalReferral(ContactId, " + this.CurrUser.iUserID + ") as TotalReferral, ContactId from lpvw_PartnerContacts) as t "
+ "order by TotalReferral desc)";
}
if (!string.IsNullOrEmpty(this.Request.QueryString["ContactIds"]))
{
string sSearchConditions = this.Request.QueryString["ContactIds"].ToString();
sWhere += LPWeb.Common.Encrypter.Base64Decode(sSearchConditions);
}
if (sHasAccessAllContacts == "0")
{
//sWhere += " AND ContactId in (select ContactId from ContactUsers where UserID="+ CurrUser.iUserID +") ";
//数据权限 gdc Bug #1670
sWhere += string.Format(@"AND t.ContactId IN(SELECT dbo.Contacts.ContactId
FROM dbo.LoanContacts INNER JOIN
dbo.Contacts ON dbo.LoanContacts.ContactId = dbo.Contacts.ContactId INNER JOIN
dbo.ContactRoles ON dbo.LoanContacts.ContactRoleId = dbo.ContactRoles.ContactRoleId AND dbo.ContactRoles.Name <> 'Borrower' AND
dbo.ContactRoles.Name <> 'CoBorrower'
AND dbo.LoanContacts.FileId in (SELECT LoanID FROM dbo.[lpfn_GetUserLoans2] ('{0}', '{1}')))", CurrUser.iUserID, CurrUser.bAccessOtherLoans);
}
return(sWhere);
}
protected void Page_Load(object sender, EventArgs e)
{
// 获取当前用户信息
this.CurrentUser = new LoginUser();
this.iCurrentUserID = CurrentUser.iUserID;
#region 页面重定向
if (this.CurrentUser.sRoleName == "Executive" || this.CurrentUser.sRoleName == "Branch Manager")
{
try
{
this.Response.Redirect("DashBoardHome2.aspx");
}
catch
{
}
}
#endregion
#region Get User Home Profile
LPWeb.BLL.UserHomePref UserHomePref1 = new LPWeb.BLL.UserHomePref();
LPWeb.Model.UserHomePref UserHomePref2 = UserHomePref1.GetModel(this.iCurrentUserID);
#endregion
#region 角色权限-显示控制
#region 设置是否显示Alert
if (this.CurrentUser.userRole.OverdueTaskAlerts == true)
{
this.AlertWebPart1.Visible = true;
}
else
{
this.AlertWebPart1.Visible = false;
}
// User Home Profile
if (UserHomePref2 != null)
{
if (UserHomePref2.OverDueTaskAlert == true)
{
this.AlertWebPart1.Visible = true;
}
else
{
this.AlertWebPart1.Visible = false;
}
}
#endregion
#region 设置是否显示Email Inbox
if (this.CurrentUser.userRole.ExchangeInbox == true)
{
this.divEmailInbox.Visible = true;
}
else
{
this.divEmailInbox.Visible = false;
}
// User Home Profile
if (UserHomePref2 != null)
{
if (UserHomePref2.ExchangeInbox == true)
{
this.divEmailInbox.Visible = true;
}
else
{
this.divEmailInbox.Visible = false;
}
}
#endregion
#region 设置是否显示Company Announcement
//if (this.CurrentUser.userRole.Announcements == true)
//{
// this.divCompanyAnn.Visible = true;
//}
//else
//{
// this.divCompanyAnn.Visible = false;
//}
//// User Home Profile
//if (UserHomePref2 != null)
//{
// if (UserHomePref2.Announcements == true)
// {
// this.divCompanyAnn.Visible = true;
// }
// else
// {
// this.divCompanyAnn.Visible = false;
// }
//}
#endregion
#region 设置是否显示Calendar
if (this.CurrentUser.userRole.ExchangeCalendar == true)
{
this.divCalendar.Visible = true;
}
else
{
this.divCalendar.Visible = false;
}
// User Home Profile
if (UserHomePref2 != null)
{
if (UserHomePref2.ExchangeCalendar == true)
{
this.divCalendar.Visible = true;
}
else
{
this.divCalendar.Visible = false;
}
}
#endregion
#region 设置是否显示User Goals
if (this.CurrentUser.userRole.GoalsChart == true)
{
this.divUserGoals.Visible = true;
}
else
{
this.divUserGoals.Visible = false;
}
// User Home Profile
if (UserHomePref2 != null)
{
if (UserHomePref2.GoalsChart == true)
{
this.divUserGoals.Visible = true;
}
else
{
this.divUserGoals.Visible = false;
}
}
#endregion
#region 设置是否显示Pipeline Summary
if (this.CurrentUser.userRole.PipelineChart == true)
{
this.divPipelineAnalysis.Visible = true;
}
else
{
this.divPipelineAnalysis.Visible = false;
}
// User Home Profile
if (UserHomePref2 != null)
{
if (UserHomePref2.PipelineChart == true)
{
this.divPipelineAnalysis.Visible = true;
}
else
{
this.divPipelineAnalysis.Visible = false;
}
}
#endregion
#region 设置是否显示filter
if (this.divPipelineAnalysis.Visible == false &&
this.divUserGoals.Visible == false)
{
this.divFilters.Visible = false;
}
#endregion
#region UserHomePref.DashboardLastCompletedStages
// StageFilter
string sStageFilter = string.Empty;
this.ddlStageFilter.Value = "CurrentStages";
sStageFilter = "CurrentStages";
if (UserHomePref2 != null && UserHomePref2.DashboardLastCompletedStages != null && UserHomePref2.DashboardLastCompletedStages == 1)
{
this.ddlStageFilter.Value = "LastCompletedStages";
sStageFilter = "LastCompletedStages";
}
#endregion
#region Quick Lead Form
if (this.CurrentUser.QuickLeadForm == true)
{
this.divQuickLead.Visible = true;
}
else
{
this.divQuickLead.Visible = false;
}
#endregion
#endregion
string sErrorMsg = "Invalid query string, be ignored.";
#region 获取页面参数
int iRegionID = 0;
int iDivisionID = 0;
string sRegionID = string.Empty;
string sDivisionID = string.Empty;
string sBranchID = string.Empty;
string sDateType = string.Empty;
string sFromDate = string.Empty;
string sToDate = string.Empty;
// Template_Stage.WorkflowType = Loan.Status
string sWorkflowType = string.Empty;
#region RegionID
if (this.Request.QueryString["Region"] != null)
{
#region get region id
string sRegionID_Encode = this.Request.QueryString["Region"].ToString();
string sRegionID_Decode = Encrypter.Base64Decode(sRegionID_Encode);
if (sRegionID_Decode == sRegionID_Encode)
{
PageCommon.WriteJsEnd(this, sErrorMsg, "window.location.href = window.location.pathname");
}
bool IsValid = Regex.IsMatch(sRegionID_Decode, @"^(-)?\d+$");
if (IsValid == false)
{
PageCommon.WriteJsEnd(this, sErrorMsg, "window.location.href = window.location.pathname");
}
sRegionID = sRegionID_Decode;
iRegionID = Convert.ToInt32(sRegionID_Decode);
#endregion
}
#endregion
#region DivisionID
if (this.Request.QueryString["Division"] != null)
{
#region get division id
string sDivisionID_Encode = this.Request.QueryString["Division"].ToString();
string sDivisionID_Decode = Encrypter.Base64Decode(sDivisionID_Encode);
if (sDivisionID_Decode == sDivisionID_Encode)
{
PageCommon.WriteJsEnd(this, sErrorMsg, "window.location.href = window.location.pathname");
}
bool IsValid = Regex.IsMatch(sDivisionID_Decode, @"^(-)?\d+$");
if (IsValid == false)
{
PageCommon.WriteJsEnd(this, sErrorMsg, "window.location.href = window.location.pathname");
}
sDivisionID = sDivisionID_Decode;
iDivisionID = Convert.ToInt32(sDivisionID_Decode);
#endregion
}
#endregion
#region BranchID
if (this.Request.QueryString["Branch"] != null)
{
string sBranch_Encode = this.Request.QueryString["Branch"].ToString();
sBranchID = Encrypter.Base64Decode(sBranch_Encode);
if (sBranch_Encode == sBranchID)
{
PageCommon.WriteJsEnd(this, sErrorMsg, "window.location.href = window.location.pathname");
}
bool IsValid = Regex.IsMatch(sBranchID, @"^(-)?\d+$");
if (IsValid == false)
{
PageCommon.WriteJsEnd(this, sErrorMsg, "window.location.href = window.location.pathname");
}
}
#endregion
#region Date Type
if (this.Request.QueryString["DateType"] != null)
{
string sDateType_Encode = this.Request.QueryString["DateType"].ToString();
sDateType = Encrypter.Base64Decode(sDateType_Encode);
if (sDateType != "CloseDate" && sDateType != "OpenDate")
{
PageCommon.WriteJsEnd(this, sErrorMsg, "window.location.href = window.location.pathname");
}
}
else
{
sDateType = "CloseDate";
}
#endregion
#region FromDate
if (this.Request.QueryString["FromDate"] != null)
{
string sFromDate_Encode = this.Request.QueryString["FromDate"].ToString();
sFromDate = Encrypter.Base64Decode(sFromDate_Encode);
if (sFromDate_Encode == sFromDate)
{
PageCommon.WriteJsEnd(this, sErrorMsg, "window.location.href = window.location.pathname");
}
}
#endregion
#region ToDate
if (this.Request.QueryString["ToDate"] != null)
{
string sToDate_Encode = this.Request.QueryString["ToDate"].ToString();
sToDate = Encrypter.Base64Decode(sToDate_Encode);
if (sToDate_Encode == sToDate)
{
PageCommon.WriteJsEnd(this, sErrorMsg, "window.location.href = window.location.pathname");
}
}
#endregion
#region Workflow Type
if (this.Request.QueryString["WorkflowType"] != null)
{
string sWorkflowType_Encode = this.Request.QueryString["WorkflowType"].ToString();
sWorkflowType = Encrypter.Base64Decode(sWorkflowType_Encode);
if (sWorkflowType != "Processing" && sWorkflowType != "Prospect" && sWorkflowType != "Archived")
{
PageCommon.WriteJsEnd(this, sErrorMsg, "window.location.href = window.location.pathname");
}
}
else
{
sWorkflowType = "Processing";
}
#endregion
#region StageFilter
if (this.Request.QueryString["StageFilter"] != null)
{
sStageFilter = this.Request.QueryString["StageFilter"].ToString();
if (sStageFilter != "CurrentStages")
{
sStageFilter = "LastCompletedStages";
}
}
#endregion
#endregion
#region load filters
DataSet OrganFilters = PageCommon.GetOrganFilter(iRegionID, iDivisionID);
// region filter
DataTable RegionListData = OrganFilters.Tables["Regions"];
this.ddlRegions.DataSource = RegionListData;
this.ddlRegions.DataBind();
// division filter
DataTable DivisionListData = OrganFilters.Tables["Divisions"];
this.ddlDivisions.DataSource = DivisionListData;
this.ddlDivisions.DataBind();
// branch filter
DataTable BranchListData = OrganFilters.Tables["Branches"];
this.ddlBranches.DataSource = BranchListData;
this.ddlBranches.DataBind();
#endregion
#region Build Search Conditions
string sWhere = string.Empty;
// Loans.Status
if (sWorkflowType == "Archived")
{
sWhere += " and (b.Status!='Processing') and (b.Status!='Prospect')";
//CR063 : Remove the “Suspended” status in the list of the “Archived Loans” status
sWhere += " AND (b.Status != 'Suspended')";
}
else
{
sWhere += " and (b.Status='" + sWorkflowType + "')";
}
if (sWorkflowType == "Prospect")
{
sWhere += " and (b.ProspectLoanStatus='Active')";
}
if (sRegionID != string.Empty)
{
sWhere += " and (a.RegionID = " + sRegionID + ")";
}
if (sDivisionID != string.Empty)
{
sWhere += " and (a.DivisionID = " + sDivisionID + ")";
}
if (sBranchID != string.Empty)
{
sWhere += " and (a.BranchID = " + sBranchID + ")";
}
if (sDateType != string.Empty)
{
#region FromDate and ToDate
DateTime?FromDate = null;
DateTime?ToDate = null;
if (sFromDate != string.Empty)
{
DateTime FromDate1;
bool IsDate1 = DateTime.TryParse(sFromDate, out FromDate1);
if (IsDate1 == true)
{
FromDate = FromDate1;
}
}
if (sToDate != string.Empty)
{
DateTime ToDate1;
bool IsDate2 = DateTime.TryParse(sToDate, out ToDate1);
if (IsDate2 == true)
{
ToDate = ToDate1;
}
}
string sFiledName = string.Empty;
if (sDateType == "CloseDate")
{
sFiledName = "b.LastStageComplDate";
}
else
{
sFiledName = "b.LastStageComplDate";
}
sWhere += SqlTextBuilder.BuildDateSearchCondition(sFiledName, FromDate, ToDate);
#endregion
}
#endregion
#region Pipeline Summary
#region get stage template list
// loan manager
LPWeb.BLL.Loans LoanManager = new LPWeb.BLL.Loans();
if (sWorkflowType == "Archived")
{
#region init LoanAnalysisData
this.LoanAnalysisData = new DataTable();
this.LoanAnalysisData.Columns.Add("StageName", typeof(string));
this.LoanAnalysisData.Columns.Add("StageAlias", typeof(string));
this.LoanAnalysisData.Columns.Add("Href", typeof(string));
this.LoanAnalysisData.Columns.Add("Amount", typeof(decimal));
this.LoanAnalysisData.Columns.Add("LoanCounts", typeof(int)); //gdc CR40
DataRow ClosedRow = this.LoanAnalysisData.NewRow();
ClosedRow["StageName"] = "Closed";
ClosedRow["StageAlias"] = "Closed";
ClosedRow["Href"] = string.Empty;
ClosedRow["Amount"] = decimal.Zero;
ClosedRow["LoanCounts"] = decimal.Zero;//gdc CR40
this.LoanAnalysisData.Rows.Add(ClosedRow);
DataRow CanceledRow = this.LoanAnalysisData.NewRow();
CanceledRow["StageName"] = "Canceled";
CanceledRow["StageAlias"] = "Canceled";
CanceledRow["Href"] = string.Empty;
CanceledRow["Amount"] = decimal.Zero;
CanceledRow["LoanCounts"] = decimal.Zero;//gdc CR40
this.LoanAnalysisData.Rows.Add(CanceledRow);
DataRow DeniedRow = this.LoanAnalysisData.NewRow();
DeniedRow["StageName"] = "Denied";
DeniedRow["StageAlias"] = "Denied";
DeniedRow["Href"] = string.Empty;
DeniedRow["Amount"] = decimal.Zero;
DeniedRow["LoanCounts"] = decimal.Zero;//gdc CR40
this.LoanAnalysisData.Rows.Add(DeniedRow);
//CR063 : Remove the “Suspended” status in the list of the “Archived Loans” status
//DataRow SuspendedRow = this.LoanAnalysisData.NewRow();
//SuspendedRow["StageName"] = "Suspended";
//SuspendedRow["StageAlias"] = "Suspended";
//SuspendedRow["Href"] = string.Empty;
//SuspendedRow["Amount"] = decimal.Zero;
//SuspendedRow["LoanCounts"] = decimal.Zero;//gdc CR40
//this.LoanAnalysisData.Rows.Add(SuspendedRow);
#endregion
#region 添加Uncategorized分类
DataRow UncategorizedRow = this.LoanAnalysisData.NewRow();
UncategorizedRow["StageName"] = "Uncategorized";
UncategorizedRow["StageAlias"] = "Uncategorized";
UncategorizedRow["Href"] = string.Empty;
UncategorizedRow["Amount"] = decimal.Zero;
UncategorizedRow["LoanCounts"] = decimal.Zero;//gdc CR40
this.LoanAnalysisData.Rows.Add(UncategorizedRow);
#endregion
}
else
{
string sSql2 = "select Name as StageName, Alias as StageAlias, '' as Href, 0.00 as Amount,0 as LoanCounts from Template_Stages "
+ "where WorkflowType=@WorkflowType and [Enabled]=1 order by SequenceNumber";
SqlCommand SqlCmd2 = new SqlCommand(sSql2);
DbHelperSQL.AddSqlParameter(SqlCmd2, "@WorkflowType", sWorkflowType);
this.LoanAnalysisData = DbHelperSQL.ExecuteDataTable(SqlCmd2);
#region 如果Lead，添加Uncategorized分类
if (sWorkflowType == "Prospect")
{
DataRow NewStageTempRow = this.LoanAnalysisData.NewRow();
NewStageTempRow["StageName"] = "Uncategorized";
NewStageTempRow["StageAlias"] = "Uncategorized";
NewStageTempRow["Href"] = string.Empty;
NewStageTempRow["Amount"] = decimal.Zero;
NewStageTempRow["LoanCounts"] = decimal.Zero; //gdc CR40
this.LoanAnalysisData.Rows.Add(NewStageTempRow);
}
#endregion
}
#endregion
#region get user loan list
string sStageField = string.Empty;
if (sStageFilter == "CurrentStages")
{
sStageField = "b.Stage";
}
else
{
sStageField = "b.LastCompletedStage";
}
//string sSqlx1 = string.Format("select {0} as Stage, b.Amount as Amount from lpfn_GetUserLoans2({1}, {2}) as a inner join V_ProcessingPipelineInfo as b on a.LoanID = b.FileId ", sStageField, iCurrentUserID, (this.CurrentUser.bAccessOtherLoans)?1:0)
// + "where (1=1) " + sWhere;
string sSqlx1 = "select " + sStageField + " as Stage, b.Amount as Amount from lpfn_GetUserLoans(" + this.iCurrentUserID + ") as a inner join V_ProcessingPipelineInfo as b on a.LoanID = b.FileId "
+ "where (1=1) " + sWhere;
DataTable UserLoanList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSqlx1);
#endregion
#region set Amount and Href
foreach (DataRow LoanAnaylysisRow in this.LoanAnalysisData.Rows)
{
string sStageName = LoanAnaylysisRow["StageName"].ToString();
string sStageAlias = LoanAnaylysisRow["StageAlias"] == DBNull.Value ? LoanAnaylysisRow["StageName"].ToString() : LoanAnaylysisRow["StageAlias"].ToString();
if (sStageName == "Uncategorized")
{
#region Amount
string sSqlx10 = string.Empty;
if (sWorkflowType == "Prospect")
{
sSqlx10 = "select isnull(SUM(b.Amount),0.00) as TotalAmount,count(1) as Total from lpfn_GetUserLoans(" + this.iCurrentUserID + ") as a inner join V_ProcessingPipelineInfo as b on a.LoanID=b.FileId "
+ "where (select COUNT(1) from LoanStages where FileId=a.LoanID)=0 " + sWhere;
}
else if (sWorkflowType == "Archived")
{
//string sWhere10 = sWhere.Replace(" and (b.Status!='Processing') and (b.Status!='Prospect')", " and b.Status='Uncategorized Archive'");
//CR063 : Remove the “Suspended” status in the list of the “Archived Loans” status
string sWhere10 = sWhere.Replace(" and (b.Status!='Processing') and (b.Status!='Prospect') AND (b.Status != 'Suspended')", " and b.Status='Uncategorized Archive'");
sSqlx10 = "select isnull(SUM(b.Amount),0.00) as TotalAmount,count(1) as Total from lpfn_GetUserLoans(" + this.iCurrentUserID + ") as a inner join V_ProcessingPipelineInfo as b on a.LoanID=b.FileId "
+ "where 1=1 " + sWhere10;
}
//object oSum = LPWeb.DAL.DbHelperSQL.ExecuteScalar(sSqlx10);
//decimal dSum = oSum == DBNull.Value ? decimal.Zero : Convert.ToDecimal(oSum);
//LoanAnaylysisRow["Amount"] = dSum;
decimal dSum = 0;
int Total = 0;
var dt = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSqlx10);
if (dt.Rows.Count > 0)
{
dSum = dt.Rows[0]["TotalAmount"] == DBNull.Value ? decimal.Zero : Convert.ToDecimal(dt.Rows[0]["TotalAmount"]);
Total = dt.Rows[0]["Total"] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[0]["Total"]);
}
LoanAnaylysisRow["Amount"] = dSum;
LoanAnaylysisRow["LoanCounts"] = Total;
#endregion
#region Href
string sPipelineUrl = string.Empty;
if (sWorkflowType == "Prospect")
{
string sAliasString = this.BuildQueryString_PipelineSummary(sRegionID, sDivisionID, sBranchID, sDateType, sFromDate, sToDate, sWorkflowType, sStageAlias, sStageFilter);
sPipelineUrl = "Pipeline/ProspectPipelineSummaryLoan.aspx?q=" + Encrypter.Base64Encode(sAliasString);
}
else if (sWorkflowType == "Archived")
{
string sAliasString = this.BuildQueryString_PipelineSummary(sRegionID, sDivisionID, sBranchID, sDateType, sFromDate, sToDate, sStageAlias, string.Empty, sStageFilter);
sPipelineUrl = "Pipeline/ProcessingPipelineSummary.aspx?q=" + Encrypter.Base64Encode(sAliasString);
}
LoanAnaylysisRow["Href"] = sPipelineUrl;
#endregion
}
else
{
#region Amount
object oLoanAmount = null;
//oLoanAmount = UserLoanList.Compute("Sum(Amount)", "Stage='" + sStageName + "'");
oLoanAmount = UserLoanList.Compute("Sum(Amount)", "Stage='" + sStageAlias + "'");
//gdc CR40
object oLoanTotal = null;
oLoanTotal = UserLoanList.Compute("Count(Amount)", "Stage='" + sStageAlias + "'");
decimal dLoanAmount = decimal.Zero;
int iLoanTotal = 0;
if ((oLoanAmount != null) && (oLoanAmount != DBNull.Value))
{
dLoanAmount = Convert.ToDecimal(oLoanAmount, null);
}
//gdc CR40
if ((oLoanTotal != null) && (oLoanTotal != DBNull.Value))
{
iLoanTotal = Convert.ToInt32(oLoanTotal, null);
}
LoanAnaylysisRow["Amount"] = dLoanAmount;
LoanAnaylysisRow["LoanCounts"] = iLoanTotal; //gdc CR40
#endregion
#region Href
string sPipelineUrl = string.Empty;
if (sWorkflowType == "Processing")
{
string sAliasString = this.BuildQueryString_PipelineSummary(sRegionID, sDivisionID, sBranchID, sDateType, sFromDate, sToDate, sWorkflowType, sStageAlias, sStageFilter);
sPipelineUrl = "Pipeline/ProcessingPipelineSummary.aspx?q=" + Encrypter.Base64Encode(sAliasString);
}
else if (sWorkflowType == "Prospect")
{
string sAliasString = this.BuildQueryString_PipelineSummary(sRegionID, sDivisionID, sBranchID, sDateType, sFromDate, sToDate, sWorkflowType, sStageAlias, sStageFilter);
sPipelineUrl = "Pipeline/ProspectPipelineSummaryLoan.aspx?q=" + Encrypter.Base64Encode(sAliasString);
}
else if (sWorkflowType == "Archived")
{
string sAliasString = this.BuildQueryString_PipelineSummary(sRegionID, sDivisionID, sBranchID, sDateType, sFromDate, sToDate, sStageAlias, string.Empty, sStageFilter);
sPipelineUrl = "Pipeline/ProcessingPipelineSummary.aspx?q=" + Encrypter.Base64Encode(sAliasString);
}
string sHref = sPipelineUrl;
LoanAnaylysisRow["Href"] = sHref;
#endregion
}
}
#endregion
this.rptLoanAnalysis.DataSource = this.LoanAnalysisData;
this.rptLoanAnalysis.DataBind();
#endregion
#region peter's codes
string strRootUrl = "";
if (null != ConfigurationManager.AppSettings["WPOWARootUrl"])
{
strRootUrl = ConfigurationManager.AppSettings["WPOWARootUrl"];
}
try
{
OWAInboxPart myInbox = new OWAInboxPart();
myInbox.OWAServerAddressRoot = strRootUrl;
myInbox.ViewName = ConfigurationManager.AppSettings["WPOWAInboxViewName"];
this.phInbox.Controls.Add(myInbox);
}
catch (Exception ex)
{
Label lbl = new Label();
lbl.Text = "Failed to load Webpart OWAInbox error: " + ex.Message;
this.phInbox.Controls.Add(lbl);
LPLog.LogMessage(LogType.Logerror, "Failed to load Webpart OWAInbox error: " + ex.Message);
}
try
{
OWACalendarPart myCalendar = new OWACalendarPart();
myCalendar.OWAServerAddressRoot = strRootUrl;
myCalendar.ViewName = ConfigurationManager.AppSettings["WPOWACalendarViewName"];
this.phMyCalendar.Controls.Add(myCalendar);
}
catch (Exception ex)
{
Label lbl = new Label();
lbl.Text = "Failed to load Webpart OWACalendar error: " + ex.Message;
this.phMyCalendar.Controls.Add(lbl);
LPLog.LogMessage(LogType.Logerror, "Failed to load Webpart OWACalendar error: " + ex.Message);
}
SPWeb web = this.Web;
//SPList spList = web.Lists[System.Configuration.ConfigurationManager.AppSettings["WPComAnnName"]];
//SPView spView = spList.Views[System.Configuration.ConfigurationManager.AppSettings["WPComAnnViewName"]];
//this.xlvwpComAnn.ListId = spList.ID;
//this.xlvwpComAnn.ListName = string.Format("{{{0}}}", spList.ID.ToString());
//this.xlvwpComAnn.ViewGuid = spView.ID.ToString();
#region =====show company announcement webpart=====
if (this.CurrentUser.userRole.Announcements == true)
{
this.divCompanyAnn.Visible = true;
}
else
{
this.divCompanyAnn.Visible = false;
}
// User Home Profile
if (UserHomePref2 != null)
{
if (UserHomePref2.Announcements == true)
{
this.divCompanyAnn.Visible = true;
}
else
{
this.divCompanyAnn.Visible = false;
}
}
if (this.divCompanyAnn.Visible == true)
{
SPList spListComAnn = null;
SPView spViewComAnn = null;
try
{
spListComAnn = web.Lists[System.Configuration.ConfigurationManager.AppSettings["WPComAnnName"]];
}
catch
{
spListComAnn = null;
}
if (null != spListComAnn)
{
try
{
spViewComAnn = spListComAnn.Views[System.Configuration.ConfigurationManager.AppSettings["WPComAnnViewName"]];
}
catch
{
spViewComAnn = null;
}
if (null != spViewComAnn)
{
strComAnnListUrl = spListComAnn.DefaultViewUrl;
XsltListViewWebPart xlvwpComAnn = new XsltListViewWebPart();
xlvwpComAnn.ListId = spListComAnn.ID;
xlvwpComAnn.ListName = string.Format("{{{0}}}", spListComAnn.ID.ToString());
xlvwpComAnn.ViewGuid = spViewComAnn.ID.ToString();
//xlvwpComAnn.Toolbar = "None";
xlvwpComAnn.XmlDefinition = @"
<View Name=""{EC6E2014-F0A2-4273-B6BC-1A9F00110341}"" MobileView=""TRUE"" Type=""HTML"" Hidden=""TRUE"" DisplayName="""" Url=""/SitePages/Home.aspx"" Level=""1"" BaseViewID=""1"" ContentTypeID=""0x"" ImageUrl=""/_layouts/images/announce.png"">
<Query>
<OrderBy>
<FieldRef Name=""Modified"" Ascending=""FALSE""/>
</OrderBy>
</Query>
<ViewFields>
<FieldRef Name=""LinkTitle""/>
</ViewFields>
<RowLimit Paged=""TRUE"">5</RowLimit>
<Toolbar Type=""None""/>
</View>";
xlvwpComAnn.Xsl = @"
<xsl:stylesheet xmlns:x=""http://www.w3.org/2001/XMLSchema"" xmlns:d=""http://schemas.microsoft.com/sharepoint/dsp"" version=""1.0"" exclude-result-prefixes=""xsl msxsl ddwrt"" xmlns:ddwrt=""http://schemas.microsoft.com/WebParts/v2/DataView/runtime"" xmlns:asp=""http://schemas.microsoft.com/ASPNET/20"" xmlns:__designer=""http://schemas.microsoft.com/WebParts/v2/DataView/designer"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"" xmlns:msxsl=""urn:schemas-microsoft-com:xslt"" xmlns:SharePoint=""Microsoft.SharePoint.WebControls"" xmlns:ddwrt2=""urn:frontpage:internal"" xmlns:o=""urn:schemas-microsoft-com:office:office"">
<xsl:include href=""/_layouts/xsl/main.xsl""/>
<xsl:include href=""/_layouts/xsl/internal.xsl""/>
<xsl:param name=""AllRows"" select=""/dsQueryResponse/Rows/Row[$EntityName = '' or (position() >= $FirstRow and position() <= $LastRow)]""/>
<xsl:param name=""dvt_apos"">'</xsl:param>
<xsl:template name=""FieldRef_ValueOf.Modified"" ddwrt:dvt_mode=""body"" ddwrt:ghost="""" xmlns:ddwrt2=""urn:frontpage:internal"">
<xsl:param name=""thisNode"" select="".""/>
<span style=""color: #FF00FF"">
<xsl:value-of select=""$thisNode/@*[name()=current()/@Name]"" />
</span>
</xsl:template>
</xsl:stylesheet>";
this.phComAnn.Controls.Add(xlvwpComAnn);
}
else
{
Label lbl = new Label();
lbl.Text = string.Format("Can not find the specified webpart view with name \"{0}\".",
System.Configuration.ConfigurationManager.AppSettings["WPComAnnViewName"]);
this.phComAnn.Controls.Add(lbl);
}
}
else
{
Label lbl = new Label();
lbl.Text = string.Format("Can not find the specified webpart with name \"{0}\".",
System.Configuration.ConfigurationManager.AppSettings["WPComAnnName"]);
this.phComAnn.Controls.Add(lbl);
}
}
#endregion
#region =====show company calendar webpart=====
if (this.CurrentUser.userRole.CompanyCalendar == true)
{
this.divComCal.Visible = true;
}
else
{
this.divComCal.Visible = false;
}
// User Home Profile
if (UserHomePref2 != null)
{
if (UserHomePref2.CompanyCalendar == true)
{
this.divComCal.Visible = true;
}
else
{
this.divComCal.Visible = false;
}
}
if (this.divComCal.Visible == true)
{
SPList spListComCal = null;
SPView spViewComCal = null;
try
{
spListComCal = web.Lists[System.Configuration.ConfigurationManager.AppSettings["WPComCalName"]];
}
catch
{
spListComCal = null;
}
if (null != spListComCal)
{
try
{
spViewComCal = spListComCal.Views[System.Configuration.ConfigurationManager.AppSettings["WPComCalViewName"]];
}
catch
{
spViewComCal = null;
}
if (null != spViewComCal)
{
strComCalListUrl = spListComCal.DefaultViewUrl;
XsltListViewWebPart xlvwpComCal = new XsltListViewWebPart();
xlvwpComCal.ListId = spListComCal.ID;
xlvwpComCal.ListName = string.Format("{{{0}}}", spListComCal.ID.ToString());
xlvwpComCal.ViewGuid = spViewComCal.ID.ToString();
//xlvwpComCal.Toolbar = "None";
xlvwpComCal.XmlDefinition = @"<View Name=""{3A64E1C5-8CDF-418C-A94F-5E66F61EB5CF}"" MobileView=""TRUE"" Type=""HTML"" Hidden=""TRUE"" DisplayName="""" Url=""/SitePages/Test webparts.aspx"" Level=""1"" BaseViewID=""2"" ContentTypeID=""0x"" MobileUrl=""_layouts/mobile/viewdaily.aspx"" ImageUrl=""/_layouts/images/events.png"">
<Query>
<Where>
<DateRangesOverlap>
<FieldRef Name=""EventDate""/>
<FieldRef Name=""EndDate""/>
<FieldRef Name=""RecurrenceID""/>
<Value Type=""DateTime"">
<Month/>
</Value>
</DateRangesOverlap>
</Where>
</Query>
<ViewFields>
<FieldRef Name=""Title""/>
<FieldRef Name=""EventDate""/>
</ViewFields>
<Toolbar Type=""None""/>
</View>" ;
xlvwpComCal.Xsl = @"<xsl:stylesheet xmlns:x=""http://www.w3.org/2001/XMLSchema"" xmlns:d=""http://schemas.microsoft.com/sharepoint/dsp"" version=""1.0"" exclude-result-prefixes=""xsl msxsl ddwrt"" xmlns:ddwrt=""http://schemas.microsoft.com/WebParts/v2/DataView/runtime"" xmlns:asp=""http://schemas.microsoft.com/ASPNET/20"" xmlns:__designer=""http://schemas.microsoft.com/WebParts/v2/DataView/designer"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"" xmlns:msxsl=""urn:schemas-microsoft-com:xslt"" xmlns:SharePoint=""Microsoft.SharePoint.WebControls"" xmlns:ddwrt2=""urn:frontpage:internal"" xmlns:o=""urn:schemas-microsoft-com:office:office"">
<xsl:include href=""/_layouts/xsl/main.xsl""/>
<xsl:include href=""/_layouts/xsl/internal.xsl""/>
<xsl:param name=""AllRows"" select=""/dsQueryResponse/Rows/Row[$EntityName = '' or (position() >= $FirstRow and position() <= $LastRow)]""/>
<xsl:param name=""dvt_apos"">'</xsl:param>
<xsl:template name=""FieldRef_Recurrence_body.fRecurrence"" ddwrt:dvt_mode=""body"" match=""FieldRef[@Name='fRecurrence']"" mode=""Recurrence_body"" ddwrt:ghost="""" xmlns:ddwrt2=""urn:frontpage:internal"">
<xsl:param name=""thisNode"" select="".""/>
<xsl:variable name=""fRecurrence"" select=""$thisNode/@*[name()=current()/@Name]""/>
<xsl:variable name=""src"">/_layouts/images/
<xsl:choose>
<xsl:when test=""$fRecurrence='1'"">
<xsl:choose>
<xsl:when test=""$thisNode/@EventType='3'"">recurEx.gif</xsl:when>
<xsl:when test=""$thisNode/@EventType='4'"">recurEx.gif</xsl:when>
<xsl:otherwise>recur.gif</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:otherwise>blank.gif</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:variable name=""alt"">
<xsl:if test=""$fRecurrence='1'"">
<xsl:choose>
<xsl:when test=""@EventType='3'"">
<xsl:value-of select=""'Exception to Recurring Event'""/>
</xsl:when>
<xsl:when test=""@EventType='4'"">
<xsl:value-of select=""'Exception to Recurring Event'""/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select=""'Recurring Event'""/>
</xsl:otherwise>
</xsl:choose>
</xsl:if>
</xsl:variable>
<img border=""0"" width=""16"" height=""16"" src=""/_layouts/images/
blank.gif"" alt=""{$alt}"" title=""{$alt}""/>
</xsl:template></xsl:stylesheet>";
this.phComCal.Controls.Add(xlvwpComCal);
}
else
{
Label lbl = new Label();
lbl.Text = string.Format("Can not find the specified webpart view with name \"{0}\".",
System.Configuration.ConfigurationManager.AppSettings["WPComCalViewName"]);
this.phComCal.Controls.Add(lbl);
}
}
else
{
Label lbl = new Label();
lbl.Text = string.Format("Can not find the specified webpart with name \"{0}\".",
System.Configuration.ConfigurationManager.AppSettings["WPComCalName"]);
this.phComCal.Controls.Add(lbl);
}
}
#endregion
#region =====show rates sheet webpart=====
if (this.CurrentUser.userRole.RateSummary == true)
{
this.divRates.Visible = true;
}
else
{
this.divRates.Visible = false;
}
// User Home Profile
if (UserHomePref2 != null)
{
if (UserHomePref2.RateSummary == true)
{
this.divRates.Visible = true;
}
else
{
this.divRates.Visible = false;
}
}
if (this.divRates.Visible == true)
{
SPList spListRates = null;
SPView spViewRates = null;
try
{
spListRates = web.Lists[System.Configuration.ConfigurationManager.AppSettings["WPRatesName"]];
}
catch
{
spListRates = null;
}
if (null != spListRates)
{
try
{
spViewRates = spListRates.Views[System.Configuration.ConfigurationManager.AppSettings["WPRatesViewName"]];
}
catch
{
spViewRates = null;
}
if (null != spViewRates)
{
strRatesListUrl = spListRates.DefaultViewUrl;
XsltListViewWebPart xlvwpRates = new XsltListViewWebPart();
xlvwpRates.ListId = spListRates.ID;
xlvwpRates.ListName = string.Format("{{{0}}}", spListRates.ID.ToString());
xlvwpRates.ViewGuid = spViewRates.ID.ToString();
//xlvwpRates.Toolbar = "None";
xlvwpRates.XmlDefinition = @"<View Name=""{FE66F61E-EBF7-49E8-BD37-F9955793DDCE}"" MobileView=""TRUE"" Type=""HTML"" Hidden=""TRUE"" DisplayName="""" Url=""/SitePages/Test webparts.aspx"" Level=""1"" BaseViewID=""1"" ContentTypeID=""0x"" ImageUrl=""/_layouts/images/dlicon.png"">
<Query>
<OrderBy>
<FieldRef Name=""FileLeafRef""/>
</OrderBy>
</Query>
<ViewFields>
<FieldRef Name=""LinkFilename""/>
</ViewFields>
<RowLimit Paged=""TRUE"">30</RowLimit>
<Toolbar Type=""None""/>
</View>" ;
xlvwpRates.Xsl = @"
<xsl:stylesheet xmlns:x=""http://www.w3.org/2001/XMLSchema"" xmlns:d=""http://schemas.microsoft.com/sharepoint/dsp"" version=""1.0"" exclude-result-prefixes=""xsl msxsl ddwrt"" xmlns:ddwrt=""http://schemas.microsoft.com/WebParts/v2/DataView/runtime"" xmlns:asp=""http://schemas.microsoft.com/ASPNET/20"" xmlns:__designer=""http://schemas.microsoft.com/WebParts/v2/DataView/designer"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"" xmlns:msxsl=""urn:schemas-microsoft-com:xslt"" xmlns:SharePoint=""Microsoft.SharePoint.WebControls"" xmlns:ddwrt2=""urn:frontpage:internal"" xmlns:o=""urn:schemas-microsoft-com:office:office"">
<xsl:include href=""/_layouts/xsl/main.xsl""/>
<xsl:include href=""/_layouts/xsl/internal.xsl""/>
<xsl:param name=""AllRows"" select=""/dsQueryResponse/Rows/Row[$EntityName = '' or (position() >= $FirstRow and position() <= $LastRow)]""/>
<xsl:param name=""dvt_apos"">'</xsl:param>
<xsl:template name=""FieldRef_ValueOf.Modified"" ddwrt:dvt_mode=""body"" ddwrt:ghost="""" xmlns:ddwrt2=""urn:frontpage:internal"">
<xsl:param name=""thisNode"" select="".""/>
<span style=""color: #FF00FF"">
<xsl:value-of select=""$thisNode/@*[name()=current()/@Name]"" />
</span>
</xsl:template>
</xsl:stylesheet>";
this.phRates.Controls.Add(xlvwpRates);
}
else
{
Label lbl = new Label();
lbl.Text = string.Format("Can not find the specified webpart view with name \"{0}\".",
System.Configuration.ConfigurationManager.AppSettings["WPComCalViewName"]);
this.phRates.Controls.Add(lbl);
}
}
else
{
Label lbl = new Label();
lbl.Text = string.Format("Can not find the specified webpart with name \"{0}\".",
System.Configuration.ConfigurationManager.AppSettings["WPRatesName"]);
this.phRates.Controls.Add(lbl);
}
}
#endregion
#endregion
}
private string GetCondition()
{
StringBuilder sWhere = new StringBuilder();
//ddlCategories
if (ddlCategories.SelectedIndex > 0)
{
string CategoryId = ddlCategories.SelectedValue;
sWhere.Append(" and CategoryId = '" + CategoryId + "'");
}
//Campaigns
if (ddlCampaigns.SelectedIndex > 0)
{
string CampainID = ddlCampaigns.SelectedValue;
sWhere.Append(" and CampaignId = '" + CampainID + "'");
}
// Alphabet
if (ddlAlphabet.SelectedIndex > 0)
{
string sAlphabet = ddlAlphabet.SelectedValue.ToLower();
sWhere.Append(" and lower(ClientName) like '" + sAlphabet + "%'");
}
// Status
if (ddlStatus.SelectedIndex > 0)
{
string sStatus = ddlStatus.SelectedItem.Text;
sWhere.Append(" and Status='" + sStatus + "'");
}
// Type
if (ddlType.SelectedIndex > 0)
{
string sType = ddlType.SelectedItem.Text;
sWhere.Append(" and Type='" + sType + "'");
}
// Started By
if (ddlStartedBy.SelectedIndex > 0)
{
string sStartedBy = ddlStartedBy.SelectedValue;
sWhere.Append(" and StartedBy='" + sStartedBy + "'");
}
#region FromDate and ToDate
string sFromDate = string.Empty;
string sToDate = string.Empty;
sFromDate = txbFromDate.Text.Trim();
sToDate = txbToDate.Text.Trim();
DateTime?FromDate = null;
DateTime?ToDate = null;
if (sFromDate != string.Empty)
{
DateTime FromDate1;
bool IsDate1 = DateTime.TryParse(sFromDate, out FromDate1);
if (IsDate1 == true)
{
FromDate = FromDate1;
}
}
if (sToDate != string.Empty)
{
DateTime ToDate1;
bool IsDate2 = DateTime.TryParse(sToDate, out ToDate1);
if (IsDate2 == true)
{
ToDate = ToDate1;
}
}
sWhere.Append(SqlTextBuilder.BuildDateSearchCondition("Started", FromDate, ToDate));
#endregion
return(sWhere.ToString());
}
private void LoadPartnerData()
{
string sDbTable = " (select ContactId, ContactCompanyId as ContactCompanyId2, LastName+', '+FirstName as FullName, CompanyName, ServiceType, BusinessPhone, Email from [dbo].[lpvw_PartnerContactsSearch] where ServiceType IS NOT NULL) as t";
// string sWhere0 = string.Format(@" AND (ContactRoleId=dbo.lpfn_GetBorrowerRoleId() OR ContactRoleId=dbo.lpfn_GetCoBorrowerRoleId() )
// AND FileId in (SELECT LoanID FROM dbo.[lpfn_GetUserLoans2] ('{0}', '{1}'))", CurrUser.iUserID, CurrUser.bAccessOtherLoans);
//string sWhere = sWhere0 + sWhere1 + sWhere2;
string sJointTable = " select DISTINCT a.ContactId from [dbo].[lpvw_PartnerContactsSearch] a inner join dbo.lpvw_GetLoanContactInfowRoles b on a.ContactId=b.ContactId inner join loans l on b.FileId=l.FileId inner join PointFiles pf on b.FileId=pf.FileId WHERE 1=1 ";
string sWhere1 = string.Empty;
string sWhere2 = string.Empty;
if (!string.IsNullOrEmpty(sCompany))
{
sWhere1 += string.Format(" AND a.CompanyName like '{0}%' ", sCompany);
}
if (!string.IsNullOrEmpty(sServiceType))
{
sWhere1 += string.Format(" AND a.ServiceTypeId='{0}' ", sServiceType);
}
if (!string.IsNullOrEmpty(sName))
{
sWhere1 += string.Format(" AND (a.LastName like '{0}%' OR a.FirstName like '{0}%')", SqlTextBuilder.ConvertQueryValue(sName));
}
if (!string.IsNullOrEmpty(sAddress))
{
sWhere1 += string.Format(" AND (a.MailingAddr like '%{0}%' OR a.CompanyAddress like '%{0}%')", SqlTextBuilder.ConvertQueryValue(sAddress));
}
if (!string.IsNullOrEmpty(sCity))
{
sWhere1 += string.Format(" AND (a.MailingCity like '%{0}%' OR a.CompanyCity like '%{0}%')", SqlTextBuilder.ConvertQueryValue(sCity));
}
if (!string.IsNullOrEmpty(sState))
{
sWhere1 += string.Format(" AND (a.MailingState='{0}' OR a.CompanyState='{0}')", SqlTextBuilder.ConvertQueryValue(sState));
}
if (!string.IsNullOrEmpty(sEmail))
{
sWhere1 += string.Format(" AND a.Email like'%{0}%' ", SqlTextBuilder.ConvertQueryValue(sEmail));
}
if (!string.IsNullOrEmpty(sPhone))
{
sWhere1 += string.Format(" AND a.BusinessPhone like'%{0}%' ", SqlTextBuilder.ConvertQueryValue(sPhone));
}
if (!string.IsNullOrEmpty(sLoanNumber))
{
sWhere1 += string.Format(" AND l.LoanNumber like '{0}%' ", SqlTextBuilder.ConvertQueryValue(sLoanNumber));
}
if (!string.IsNullOrEmpty(sRegion))
{
sWhere1 += string.Format(" AND l.RegionId={0} ", sRegion);
}
if (!string.IsNullOrEmpty(sDivision))
{
sWhere1 += string.Format(" AND l.DivisionId={0} ", sDivision);
}
if (!string.IsNullOrEmpty(sBranch))
{
sWhere1 += string.Format(" AND l.BranchId={0} ", sBranch);
}
if (!string.IsNullOrEmpty(sLoanOfficer))
{
sWhere1 += string.Format(" AND dbo.lpfn_GetLoanOfficerId(l.FileId)={0} ", sLoanOfficer);
}
if (!string.IsNullOrEmpty(sProcessor))
{
sWhere1 += string.Format(" AND dbo.lpfn_GetProcessorId(l.FileId)={0} ", sProcessor);
}
if (!string.IsNullOrEmpty(sFilename))
{
sWhere1 += string.Format(" AND pf.[Name] like '%{0}%' ", sFilename);
}
sWhere2 += BuildUserLoanQuery();
string sWhere3 = sWhere1 + sWhere2;
string sWhere = " AND ContactId in (" + sJointTable + sWhere3 + ")";
this.PartnerSqlDataSource.ConnectionString = LPWeb.DAL.DbHelperSQL.connectionString;
this.PartnerSqlDataSource.SelectParameters["DbTable"].DefaultValue = sDbTable;
int iRowCount = LPWeb.DAL.DbHelperSQL.Count(this.PartnerSqlDataSource.SelectParameters["DbTable"].DefaultValue, sWhere);
this.AspNetPager5.RecordCount = iRowCount;
this.PartnerSqlDataSource.SelectParameters["Where"].DefaultValue = sWhere;
this.gridPartnerList.DataBind();
}
private void LoadClientData()
{
string sDbTable = " (select ContactId, LastName+', '+FirstName+ISNULL(MiddleName, '') as FullName, DOB, Email, HomePhone, CellPhone from Contacts) as t";
// string sWhere0 = string.Format(@" AND (ContactRoleId=dbo.lpfn_GetBorrowerRoleId() OR ContactRoleId=dbo.lpfn_GetCoBorrowerRoleId() )
// AND FileId in (SELECT LoanID FROM dbo.[lpfn_GetUserLoans2] ('{0}', '{1}'))", CurrUser.iUserID, CurrUser.bAccessOtherLoans);
//string sWhere = sWhere0 + sWhere1 + sWhere2;
string sJointTable = " select DISTINCT a.ContactId from Contacts a inner join Prospect p on p.ContactId=a.ContactId left join LoanContacts b on a.ContactId=b.ContactId left join loans l on b.FileId=l.FileId inner join PointFiles pf on b.FileId=pf.FileId inner join ContactCompanies cc on cc.ContactCompanyId=dbo.lpfn_GetContactCompanyId(a.ContactId) WHERE 1=1 ";
string sWhere1 = string.Empty;
string sWhere2 = string.Empty;
if (!string.IsNullOrEmpty(sName))
{
sWhere1 += string.Format(" AND (a.LastName like '{0}%' OR a.FirstName like '{0}%')", SqlTextBuilder.ConvertQueryValue(sName));
}
if (!string.IsNullOrEmpty(sAddress))
{
sWhere1 += string.Format(" AND (a.MailingAddr like '%{0}%' OR cc.[Address] like '%{0}%')", SqlTextBuilder.ConvertQueryValue(sAddress));
}
if (!string.IsNullOrEmpty(sCity))
{
sWhere1 += string.Format(" AND (a.MailingCity like '%{0}%' OR cc.[City] like '%{0}%')", SqlTextBuilder.ConvertQueryValue(sCity));
}
if (!string.IsNullOrEmpty(sState))
{
sWhere1 += string.Format(" AND (a.MailingState='{0}' OR cc.[State]='{0}')", SqlTextBuilder.ConvertQueryValue(sState));
}
if (!string.IsNullOrEmpty(sEmail))
{
sWhere1 += string.Format(" AND a.Email like'%{0}%' ", SqlTextBuilder.ConvertQueryValue(sEmail));
}
if (!string.IsNullOrEmpty(sPhone))
{
sWhere1 += string.Format(" AND (a.BusinessPhone like'%{0}%' OR a.HomePhone like'%{0}%' OR a.CellPhone like'%{0}%') ", SqlTextBuilder.ConvertQueryValue(sPhone));
}
if (!string.IsNullOrEmpty(sLoanNumber))
{
sWhere1 += string.Format(" AND l.LoanNumber like '{0}%' ", SqlTextBuilder.ConvertQueryValue(sLoanNumber));
}
if (!string.IsNullOrEmpty(sRegion))
{
sWhere1 += string.Format(" AND l.RegionId={0} ", sRegion);
}
if (!string.IsNullOrEmpty(sDivision))
{
sWhere1 += string.Format(" AND l.DivisionId={0} ", sDivision);
}
if (!string.IsNullOrEmpty(sBranch))
{
sWhere1 += string.Format(" AND l.BranchId={0} ", sBranch);
}
if (!string.IsNullOrEmpty(sLoanOfficer))
{
sWhere1 += string.Format(" AND dbo.lpfn_GetLoanOfficerId(l.FileId)={0} ", sLoanOfficer);
}
if (!string.IsNullOrEmpty(sProcessor))
{
sWhere1 += string.Format(" AND dbo.lpfn_GetProcessorId(l.FileId)={0} ", sProcessor);
}
if (!string.IsNullOrEmpty(sFilename))
{
sWhere1 += string.Format(" AND pf.[Name] like '%{0}%' ", sFilename);
}
string sqlWhere1 = " AND (l.FileId in (select LoanId from {0}) ";
string sqlWhere2 = string.Empty;
if (CurrentUser.bIsCompanyExecutive || CurrentUser.bIsRegionExecutive || CurrentUser.bIsDivisionExecutive)
{
sqlWhere2 = string.Format(" dbo.[lpfn_GetUserLoans_Executive]({0})", CurrentUser.iUserID);
}
else if (CurrentUser.bIsBranchManager)
{
sqlWhere2 = string.Format(" dbo.[lpfn_GetUserLoans_Branch_Manager]({0})", CurrentUser.iUserID);
}
else
{
sqlWhere2 = string.Format(" dbo.[lpfn_GetUserLoans2]({0}, {1})", CurrentUser.iUserID, CurrentUser.bAccessOtherLoans ? 1 : 0);
}
string sqlWhere = string.Format(sqlWhere1, sqlWhere2);
if (CurrUser.bAccessOtherLoans || CurrUser.userRole.AccessUnassignedLeads)
{
sqlWhere += " OR l.BranchId IS NULL OR dbo.lpfn_GetLoanOfficerId(l.FileId) IS NULL ";
}
sqlWhere += ")";
string sWhere3 = sWhere1 + sWhere2 + sqlWhere;
string sWhere = " AND ContactId in (" + sJointTable + sWhere3 + ")";
#region get row count
int iRowCount = this.GetClientsCount(sDbTable, sWhere);
this.AspNetPager4.RecordCount = iRowCount;
#endregion
#region Calc. StartIndex and EndIndex
int iPageSize = this.AspNetPager4.PageSize;
int iPageIndex = 1;
if (this.Request.QueryString["PageIndex4"] != null)
{
iPageIndex = Convert.ToInt32(this.Request.QueryString["PageIndex4"]);
}
int iStartIndex = PageCommon.CalcStartIndex(iPageIndex, iPageSize);
int iEndIndex = PageCommon.CalcEndIndex(iStartIndex, iPageSize, iRowCount);
#endregion
#region 绑定Clients列表
DataTable ClientList = this.GetClientList(sDbTable, sWhere, iStartIndex, iEndIndex);
DataView ClientView = new DataView(ClientList);
DataTable ClientList_Distinct = ClientView.ToTable(true, "ContactId", "FullName", "DOB", "Email", "HomePhone", "CellPhone");
this.gridClientList.DataSource = ClientList_Distinct;
this.gridClientList.DataBind();
#endregion
}
protected void Page_Load(object sender, EventArgs e)
{
#region 接收页面参数
#region checkbox
bSearchLoans = true;
if (this.Request.QueryString["SearchLoans"] != null)
{
string sSearchLoans = this.Request.QueryString["SearchLoans"].ToString();
if (sSearchLoans != "true" && sSearchLoans != "false")
{
PageCommon.WriteJsEnd(this, "Invalid SearchLoans option.", "window.location.href = window.location.pathname;");
}
bSearchLoans = Boolean.Parse(sSearchLoans);
}
bSearchOpportunities = true;
if (this.Request.QueryString["SearchOpportunities"] != null)
{
string sSearchOpportunities = this.Request.QueryString["SearchOpportunities"].ToString();
if (sSearchOpportunities != "true" && sSearchOpportunities != "false")
{
PageCommon.WriteJsEnd(this, "Invalid SearchOpportunities option.", "window.location.href = window.location.pathname;");
}
bSearchOpportunities = Boolean.Parse(sSearchOpportunities);
}
bSearchArchivedLoans = true;
if (this.Request.QueryString["SearchArchivedLoans"] != null)
{
string sSearchArchivedLoans = this.Request.QueryString["SearchArchivedLoans"].ToString();
if (sSearchArchivedLoans != "true" && sSearchArchivedLoans != "false")
{
PageCommon.WriteJsEnd(this, "Invalid SearchArchivedLoans option.", "window.location.href = window.location.pathname;");
}
bSearchArchivedLoans = Boolean.Parse(sSearchArchivedLoans);
}
bSearchClients = true;
if (this.Request.QueryString["SearchClients"] != null)
{
string sSearchClients = this.Request.QueryString["SearchClients"].ToString();
if (sSearchClients != "true" && sSearchClients != "false")
{
PageCommon.WriteJsEnd(this, "Invalid SearchClients option.", "window.location.href = window.location.pathname;");
}
bSearchClients = Boolean.Parse(sSearchClients);
}
bSearchPartners = true;
if (this.Request.QueryString["SearchPartners"] != null)
{
string sSearchPartners = this.Request.QueryString["SearchPartners"].ToString();
if (sSearchPartners != "true" && sSearchPartners != "false")
{
PageCommon.WriteJsEnd(this, "Invalid SearchPartners option.", "window.location.href = window.location.pathname;");
}
bSearchPartners = Boolean.Parse(sSearchPartners);
}
#endregion
#region textbox
// Name
sName = string.Empty;
if (this.Request.QueryString["Name"] != null)
{
sName = this.Request.QueryString["Name"].ToString();
}
// Company
sCompany = string.Empty;
if (this.Request.QueryString["Company"] != null)
{
sCompany = this.Request.QueryString["Company"].ToString();
}
// Address
sAddress = string.Empty;
if (this.Request.QueryString["Address"] != null)
{
sAddress = this.Request.QueryString["Address"].ToString();
}
// City
sCity = string.Empty;
if (this.Request.QueryString["City"] != null)
{
sCity = this.Request.QueryString["City"].ToString();
}
// State
sState = string.Empty;
if (this.Request.QueryString["State"] != null)
{
sState = this.Request.QueryString["State"].ToString();
}
// Email
sEmail = string.Empty;
if (this.Request.QueryString["Email"] != null)
{
sEmail = this.Request.QueryString["Email"].ToString();
}
// Phone
sPhone = string.Empty;
if (this.Request.QueryString["Phone"] != null)
{
sPhone = this.Request.QueryString["Phone"].ToString();
}
// Loan Number
sLoanNumber = string.Empty;
if (this.Request.QueryString["LoanNumber"] != null)
{
sLoanNumber = this.Request.QueryString["LoanNumber"].ToString();
}
// Filename
sFilename = string.Empty;
if (this.Request.QueryString["Filename"] != null)
{
sFilename = this.Request.QueryString["Filename"].ToString();
}
#endregion
#region dropdown list
// Loan Officer
sLoanOfficer = string.Empty;
if (this.Request.QueryString["LoanOfficer"] != null)
{
string sLoanOfficerTemp = this.Request.QueryString["LoanOfficer"].ToString();
if (PageCommon.IsID(sLoanOfficerTemp) == false)
{
PageCommon.WriteJsEnd(this, "Invalid LoanOfficer id.", "window.location.href = window.location.pathname;");
}
sLoanOfficer = sLoanOfficerTemp;
}
// Processor
sProcessor = string.Empty;
if (this.Request.QueryString["Processor"] != null)
{
string sProcessorTemp = this.Request.QueryString["Processor"].ToString();
if (PageCommon.IsID(sProcessorTemp) == false)
{
PageCommon.WriteJsEnd(this, "Invalid Processor id.", "window.location.href = window.location.pathname;");
}
sProcessor = sProcessorTemp;
}
// Region
sRegion = string.Empty;
if (this.Request.QueryString["Region"] != null)
{
string sRegionTemp = this.Request.QueryString["Region"].ToString();
if (PageCommon.IsID(sRegionTemp) == false)
{
PageCommon.WriteJsEnd(this, "Invalid region id.", "window.location.href = window.location.pathname;");
}
sRegion = sRegionTemp;
}
// Division
sDivision = string.Empty;
if (this.Request.QueryString["Division"] != null)
{
string sDivisionTemp = this.Request.QueryString["Division"].ToString();
if (PageCommon.IsID(sDivisionTemp) == false)
{
PageCommon.WriteJsEnd(this, "Invalid division id.", "window.location.href = window.location.pathname;");
}
sDivision = sDivisionTemp;
}
// Branch
sBranch = string.Empty;
if (this.Request.QueryString["Branch"] != null)
{
string sBranchTemp = this.Request.QueryString["Branch"].ToString();
if (PageCommon.IsID(sBranchTemp) == false)
{
PageCommon.WriteJsEnd(this, "Invalid Branch id.", "window.location.href = window.location.pathname;");
}
sBranch = sBranchTemp;
}
// Service Type
sServiceType = string.Empty;
if (this.Request.QueryString["ServiceType"] != null)
{
string sServiceTypeTemp = this.Request.QueryString["ServiceType"].ToString();
if (PageCommon.IsID(sServiceTypeTemp) == false)
{
PageCommon.WriteJsEnd(this, "Invalid ServiceType id.", "window.location.href = window.location.pathname;");
}
sServiceType = sServiceTypeTemp;
}
#endregion
#endregion
CurrentUser = this.CurrUser;
#region Dropdown Lists
#region 加载Loan Officer List
if (CurrentUser.iRoleID > 2 && CurrentUser.userRole.OtherLoanAccess == false)
{
this.ddlLoanOfficer.Enabled = false;
}
else
{
DataTable LoanOfficerListData = this.GetLoanOfficerList(CurrentUser.iUserID);
DataRow NewLoanOfficerRow = LoanOfficerListData.NewRow();
NewLoanOfficerRow["UserId"] = DBNull.Value;
NewLoanOfficerRow["FullName"] = "All";
LoanOfficerListData.Rows.InsertAt(NewLoanOfficerRow, 0);
this.ddlLoanOfficer.DataSource = LoanOfficerListData;
this.ddlLoanOfficer.DataBind();
}
#endregion
#region 加载Processor List
if (CurrentUser.iRoleID > 2 && CurrentUser.userRole.OtherLoanAccess == false)
{
this.ddlProcessor.Enabled = false;
}
else
{
DataTable ProcessorListData = this.GetProcessorList(CurrentUser.iUserID);
DataRow NewProcessorRow = ProcessorListData.NewRow();
NewProcessorRow["UserId"] = DBNull.Value;
NewProcessorRow["FullName"] = "All";
ProcessorListData.Rows.InsertAt(NewProcessorRow, 0);
this.ddlProcessor.DataSource = ProcessorListData;
this.ddlProcessor.DataBind();
}
#endregion
#region 加载Region List
if (CurrentUser.bIsCompanyExecutive == true && CurrentUser.userRole.OtherLoanAccess == true)
{
#region 加载Region List
DataTable RegionListData = this.GetRegionList();
DataRow NewRegionRow = RegionListData.NewRow();
NewRegionRow["RegionId"] = DBNull.Value;
NewRegionRow["Name"] = "All";
RegionListData.Rows.InsertAt(NewRegionRow, 0);
this.ddlRegion.DataSource = RegionListData;
this.ddlRegion.DataBind();
#endregion
}
else
{
this.ddlRegion.Enabled = false;
}
#endregion
#region 加载Division List
if ((CurrentUser.bIsCompanyExecutive == true && CurrentUser.userRole.OtherLoanAccess == true) ||
(CurrentUser.bIsRegionExecutive == true && CurrentUser.userRole.OtherLoanAccess == true))
{
#region 加载Division List
DataTable DivisionListData = this.GetDivisionList(sRegion);
DataRow NewDivisionRow = DivisionListData.NewRow();
NewDivisionRow["DivisionId"] = DBNull.Value;
NewDivisionRow["Name"] = "All";
DivisionListData.Rows.InsertAt(NewDivisionRow, 0);
this.ddlDivision.DataSource = DivisionListData;
this.ddlDivision.DataBind();
#endregion
}
else
{
this.ddlDivision.Enabled = false;
}
#endregion
#region 加载Branch List
if ((CurrentUser.bIsCompanyExecutive == true && CurrentUser.userRole.OtherLoanAccess == true) ||
(CurrentUser.bIsRegionExecutive == true && CurrentUser.userRole.OtherLoanAccess == true) ||
(CurrentUser.bIsDivisionExecutive == true && CurrentUser.userRole.OtherLoanAccess == true))
{
#region 加载Branch List
DataTable BranchListData = this.GetBranchList(sDivision);
DataRow NewBranchRow = BranchListData.NewRow();
NewBranchRow["BranchId"] = DBNull.Value;
NewBranchRow["Name"] = "All";
BranchListData.Rows.InsertAt(NewBranchRow, 0);
this.ddlBranch.DataSource = BranchListData;
this.ddlBranch.DataBind();
#endregion
}
else
{
this.ddlBranch.Enabled = false;
}
#endregion
#region 加载ServiceType List
DataTable ServiceTypeListData = this.GetServiceTypeList();
DataRow NewServiceTypeRow = ServiceTypeListData.NewRow();
NewServiceTypeRow["ServiceTypeId"] = DBNull.Value;
NewServiceTypeRow["Name"] = "All";
ServiceTypeListData.Rows.InsertAt(NewServiceTypeRow, 0);
this.ddlServiceType.DataSource = ServiceTypeListData;
this.ddlServiceType.DataBind();
#endregion
#endregion
if (this.Request.QueryString.Count == 0)
{
this.divSearchResultTile.Visible = false;
this.divSearchResultContainer.Visible = false;
}
else
{
this.divActiveLoansContainer.Visible = bSearchLoans;
this.divOpportunitiesContainer.Visible = bSearchOpportunities;
this.divArchivedLoansContainer.Visible = bSearchArchivedLoans;
this.divClientsContainer.Visible = bSearchClients;
this.divPartnersContainer.Visible = bSearchPartners;
if (bSearchLoans == true || bSearchOpportunities == true || bSearchArchivedLoans == true)
{
#region sWhere
DataTable IDDataTable = new DataTable();
IDDataTable.Columns.Add("FileId", typeof(int));
// Name
//if (sName != string.Empty)
//{
//string sSql0 = "select distinct a.FileId from LoanContacts as a inner join Contacts as b on a.ContactId = b.ContactId "
// + "where lower(b.LastName) like lower('" + SqlTextBuilder.ConvertQueryValue(sName) + "%')"
// + " or lower(b.FirstName) like lower('" + SqlTextBuilder.ConvertQueryValue(sName) + "%') ";
//DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
////Fixed bug 779(20110501 Rocky)
//if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
//{
// DataRow drNew = IDs.NewRow();
// drNew["FileId"] = 0;
// IDs.Rows.Add(drNew);
//}
//IDDataTable.Merge(IDs);
//}
// Company
if (sCompany != string.Empty)
{
string sSql0 = "select distinct c.FileId from Contacts as a inner join ContactCompanies as b on a.ContactCompanyId = b.ContactCompanyId "
+ "inner join LoanContacts as c on a.ContactId = c.ContactId "
+ "where lower(b.Name) like lower('" + SqlTextBuilder.ConvertQueryValue(sCompany) + "%')";
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Address
if (sAddress != string.Empty)
{
string sSql0 = "select FileId from Loans where lower(PropertyAddr)=lower('" + SqlTextBuilder.ConvertQueryValue(sAddress) + "')";
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// City
if (sCity != string.Empty)
{
string sSql0 = "select FileId from Loans where lower(PropertyCity)=lower('" + SqlTextBuilder.ConvertQueryValue(sCity) + "')";
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// State
if (sState != string.Empty)
{
string sSql0 = "select FileId from Loans where PropertyState='" + SqlTextBuilder.ConvertQueryValue(sState) + "'";
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Email
if (sEmail != string.Empty)
{
string sSql0 = "select distinct a.FileId from LoanContacts as a inner join Contacts as b on a.ContactId = b.ContactId "
+ "where lower(b.Email)=lower('" + SqlTextBuilder.ConvertQueryValue(sEmail) + "')";
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Phone
if (sPhone != string.Empty)
{
string sSql0 = "select distinct a.FileId from LoanContacts as a inner join Contacts as b on a.ContactId = b.ContactId "
+ "where b.BusinessPhone='" + SqlTextBuilder.ConvertQueryValue(sPhone) + "' or b.CellPhone='" + SqlTextBuilder.ConvertQueryValue(sPhone) + "' or b.HomePhone='" + SqlTextBuilder.ConvertQueryValue(sPhone) + "'";
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Loan Number
if (sLoanNumber != string.Empty)
{
string sSql0 = "select FileId from Loans where LoanNumber='" + SqlTextBuilder.ConvertQueryValue(sLoanNumber) + "'";
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Filename
if (sFilename != string.Empty)
{
string sSql0 = "select FileId from PointFiles where Name='" + SqlTextBuilder.ConvertQueryValue(sFilename) + "'";
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Loan Officer
if (sLoanOfficer != string.Empty)
{
string sSql0 = "select distinct FileId from LoanTeam where RoleId=3 and UserId=" + sLoanOfficer;
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Processor
if (sProcessor != string.Empty)
{
string sSql0 = "select distinct FileId from LoanTeam where RoleId=5 and UserId=" + sProcessor;
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Region
if (sRegion != string.Empty)
{
string sSql0 = "select a.FileId from PointFiles as a inner join GroupFolder as b on a.FolderId = b.FolderId "
+ "inner join Branches as c on b.GroupID = c.GroupId "
+ "where c.RegionID=" + sRegion;
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Division
if (sDivision != string.Empty)
{
string sSql0 = "select a.FileId from PointFiles as a inner join GroupFolder as b on a.FolderId = b.FolderId "
+ "inner join Branches as c on b.GroupID = c.GroupId "
+ "where c.DivisionID=" + sDivision;
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Branch
if (sBranch != string.Empty)
{
string sSql0 = "select a.FileId from PointFiles as a inner join GroupFolder as b on a.FolderId = b.FolderId "
+ "inner join Branches as c on b.GroupID = c.GroupId "
+ "where c.BranchID=" + sBranch;
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
// Service Type
if (sServiceType != string.Empty)
{
string sSql0 = "select a.FileId from LoanContacts as a inner join Contacts as b on a.ContactId = b.ContactId "
+ "inner join ContactCompanies as c on b.ContactCompanyId = c.ContactCompanyId where c.ServiceTypeId=" + sServiceType;
DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
//Fixed bug 779(20110501 Rocky)
if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
{
DataRow drNew = IDs.NewRow();
drNew["FileId"] = 0;
IDs.Rows.Add(drNew);
}
IDDataTable.Merge(IDs);
}
//用户权限 gdc Bug #1669
//if (1 == 1)
//{
// string sSql0 = string.Format(" SELECT LoanID as FileId FROM dbo.[lpfn_GetUserLoans2] ('{0}', '{1}')", CurrUser.iUserID, CurrUser.bAccessOtherLoans);
// DataTable IDs = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql0);
// //Fixed bug 779(20110501 Rocky)
// if (IDs.Rows.Count == 0 && IDDataTable.Select("FileId=0").Length < 1)
// {
// DataRow drNew = IDs.NewRow();
// drNew["FileId"] = 0;
// IDs.Rows.Add(drNew);
// }
// IDDataTable.Merge(IDs);
//}
// distinct
DataView IDDataView = new DataView(IDDataTable);
DataTable DistinctIDList = IDDataView.ToTable(true, "FileId");
string sWhere = string.Empty;
if (!string.IsNullOrEmpty(sName))
{
sWhere += string.Format(" and FullName like '{0}%' ", SqlTextBuilder.ConvertQueryValue(sName));
}
if (DistinctIDList.Rows.Count > 0)
{
StringBuilder sbFileIDs = new StringBuilder();
for (int i = 0; i < DistinctIDList.Rows.Count; i++)
{
string sFileID = DistinctIDList.Rows[i]["FileId"].ToString();
//Fixed bug 779(20110501 Rocky)
if (sFileID == "0")
{
sbFileIDs.Append("0");
sWhere += " AND FileId = 0";
break;
}
if (i == 0)
{
sbFileIDs.Append(sFileID);
}
else
{
sbFileIDs.Append("," + sFileID);
}
}
//Fixed bug#114 by Rocky
sWhere += " AND FileId IN(" + sbFileIDs.ToString() + ")";
}
//用户权限 gdc Bug #1669 20120523
if (CurrUser.bIsCompanyExecutive || CurrUser.bIsRegionExecutive || CurrUser.bIsDivisionExecutive)
{
sWhere += string.Format(" and FileId in (Select LoanId as FileId from [dbo].[lpfn_GetUserLoans_Executive] ({0})) ", CurrUser.iUserID);
}
if (CurrUser.bIsBranchManager)
{
sWhere += string.Format(" and FileId in (Select LoanId as FileId from [dbo].[lpfn_GetUserLoans_Branch_Manager] ({0})) ", CurrUser.iUserID);
}
else
{
sWhere += string.Format(" and FileId in (Select LoanId as FileId from [dbo].[lpfn_GetUserLoans2]({0},{1})) ", CurrUser.iUserID, (CurrUser.bAccessOtherLoans) ? 1 : 0);
}
#endregion
#region 加载Active Loans
if (bSearchLoans == true)
{
this.ActiveLoanSqlDataSource.ConnectionString = LPWeb.DAL.DbHelperSQL.connectionString;
int iRowCount = LPWeb.DAL.DbHelperSQL.Count(this.ActiveLoanSqlDataSource.SelectParameters["DbTable"].DefaultValue, sWhere);
this.AspNetPager1.RecordCount = iRowCount;
#region Pipeline Link
if (iRowCount > 0)
{
string sFileIDs = this.GetFileIDs(this.ActiveLoanSqlDataSource.SelectParameters["DbTable"].DefaultValue, sWhere);
string sFileIDs_Encode = Encrypter.Base64Encode(sFileIDs);
this.aPipeline_ActiveLoans.HRef = "ProcessingPipelineSummary.aspx?Ads=" + sFileIDs_Encode;
}
else
{
this.aPipeline_ActiveLoans.HRef = "javascript:alert('There is no active loan meeting the criteria.')";
}
#endregion
this.ActiveLoanSqlDataSource.SelectParameters["Where"].DefaultValue = sWhere;
this.gridActiveLoanList.DataBind();
}
#endregion
#region 加载Opportunities
if (bSearchOpportunities == true)
{
this.OpportunitySqlDataSource.ConnectionString = LPWeb.DAL.DbHelperSQL.connectionString;
int iRowCount = LPWeb.DAL.DbHelperSQL.Count(this.OpportunitySqlDataSource.SelectParameters["DbTable"].DefaultValue, sWhere);
this.AspNetPager2.RecordCount = iRowCount;
#region Pipeline Link
if (iRowCount > 0)
{
string sFileIDs = this.GetFileIDs(this.OpportunitySqlDataSource.SelectParameters["DbTable"].DefaultValue, sWhere);
string sFileIDs_Encode = Encrypter.Base64Encode(sFileIDs);
this.aPipeline_Opportunities.HRef = "ProspectPipelineSummaryLoan.aspx?Ads=" + sFileIDs_Encode;
}
else
{
this.aPipeline_Opportunities.HRef = "javascript:alert('There is no prospect loan by criteria.')";
}
#endregion
this.OpportunitySqlDataSource.SelectParameters["Where"].DefaultValue = sWhere;
this.gridOpportunityList.DataBind();
}
#endregion
#region 加载Archived Loans
if (bSearchArchivedLoans == true)
{
this.ArchivedLoanSqlDataSource.ConnectionString = LPWeb.DAL.DbHelperSQL.connectionString;
int iRowCount = LPWeb.DAL.DbHelperSQL.Count(this.ArchivedLoanSqlDataSource.SelectParameters["DbTable"].DefaultValue, sWhere);
this.AspNetPager3.RecordCount = iRowCount;
#region Pipeline Link
if (iRowCount > 0)
{
string sFileIDs = this.GetFileIDs(this.ArchivedLoanSqlDataSource.SelectParameters["DbTable"].DefaultValue, sWhere);
string sFileIDs_Encode = Encrypter.Base64Encode(sFileIDs);
this.aPipeline_ArchivedLoans.HRef = "ProcessingPipelineSummary.aspx?Ads=" + sFileIDs_Encode + "&LoanStatus=Archived";
}
else
{
this.aPipeline_ArchivedLoans.HRef = "javascript:alert('There is no archived loan by criteria.')";
}
#endregion
this.ArchivedLoanSqlDataSource.SelectParameters["Where"].DefaultValue = sWhere;
this.gridArchivedLoanList.DataBind();
}
#endregion
}
if (bSearchClients == true)
{
LoadClientData();
}
if (bSearchPartners == true)
{
LoadPartnerData();
}
}
}
