private string GetUserName(int UserID)
{
Users user = new Users();
try
{
LPWeb.Model.Users model = user.GetModel(UserID);
return(model.LastName + ", " + model.FirstName);
}
catch
{
return(string.Empty);
}
}
private decimal GetRegionMgrCompRate(int UserID)
{
LPWeb.BLL.Users bllUsers = new LPWeb.BLL.Users();
LPWeb.Model.Users userInfo = bllUsers.GetModel(UserID);
if (userInfo != null && userInfo.RegionMgrComp != null)
{
return(userInfo.RegionMgrComp);
}
else
{
return(0.000M);
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public List <LPWeb.Model.Users> DataTableToList(DataTable dt)
{
List <LPWeb.Model.Users> modelList = new List <LPWeb.Model.Users>();
int rowsCount = dt.Rows.Count;
if (rowsCount > 0)
{
LPWeb.Model.Users model;
for (int n = 0; n < rowsCount; n++)
{
model = new LPWeb.Model.Users();
if (dt.Rows[n]["UserId"].ToString() != "")
{
model.UserId = int.Parse(dt.Rows[n]["UserId"].ToString());
}
if (dt.Rows[n]["UserEnabled"].ToString() != "")
{
if ((dt.Rows[n]["UserEnabled"].ToString() == "1") || (dt.Rows[n]["UserEnabled"].ToString().ToLower() == "true"))
{
model.UserEnabled = true;
}
else
{
model.UserEnabled = false;
}
}
//model.Prefix=dt.Rows[n]["Prefix"].ToString();
model.Username = dt.Rows[n]["Username"].ToString();
model.EmailAddress = dt.Rows[n]["EmailAddress"].ToString();
//model.UserPictureFile = (byte[])dt.Rows[n]["UserPictureFile"];
model.UserPictureFile = DBNull.Value == dt.Rows[n]["UserPictureFile"] ? null : (byte[])dt.Rows[n]["UserPictureFile"];
model.FirstName = dt.Rows[n]["FirstName"].ToString();
model.LastName = dt.Rows[n]["LastName"].ToString();
if (dt.Rows[n]["RoleId"].ToString() != "")
{
model.RoleId = int.Parse(dt.Rows[n]["RoleId"].ToString());
}
model.Password = dt.Rows[n]["Password"].ToString();
if (dt.Rows[n]["LoansPerPage"].ToString() != "")
{
model.LoansPerPage = int.Parse(dt.Rows[n]["LoansPerPage"].ToString());
}
modelList.Add(model);
}
}
return(modelList);
}
private void BindGrid()
{
//int pageSize = AspNetPager1.PageSize;
try
{
if (CurrUser != null)
{
LPWeb.BLL.Users users = new LPWeb.BLL.Users();
LPWeb.Model.Users u = users.GetModel(CurrUser.iUserID);
if (u != null)
{
//pageSize = u.LoansPerPage;
AspNetPager1.PageSize = u.LoansPerPage;
}
}
}
catch (Exception exception)
{
LPLog.LogMessage(exception.Message);
}
string sWhere = GetCondition();
this.MarketingActivitySqlDataSource.ConnectionString = LPWeb.DAL.DbHelperSQL.connectionString;
string sDbTable = "(select a.*,i.CategoryId, b.CampaignName, f.LastName +', '+ f.FirstName + case when ISNULL(f.MiddleName, '') != '' then ' '+ f.MiddleName else '' end as ClientName, "
+ " c.Status+'-'+Substring(d.Name,Charindex('\',d.Name,2)+1,len(d.Name)-Charindex('\',d.Name,2)) as LoanName, "
+ " g.LastName +', '+g.FirstName as StartedByName, h.Success, h.Error "
+ " from LoanMarketing as a inner join MarketingCampaigns as b on a.CampaignId=b.CampaignId "
+ " inner join MarketingCategory as i on b.CategoryId=i.CategoryId "
+ " inner join Loans as c on a.FileId=c.FileId "
+ " inner join PointFiles as d on c.FileId=d.FileId "
+ " inner join LoanContacts as e on d.FileId=e.FileId "
+ " inner join Contacts as f on e.ContactId=f.ContactId "
+ " left outer join Users as g on a.StartedBy=g.UserId "
+ " left outer join MarketingLog h on a.LoanMarketingId=h.LoanMarketingId) as t ";
this.MarketingActivitySqlDataSource.SelectParameters["DbTable"].DefaultValue = sDbTable;
int iRowCount1 = LPWeb.DAL.DbHelperSQL.Count(this.MarketingActivitySqlDataSource.SelectParameters["DbTable"].DefaultValue, sWhere);
this.AspNetPager1.RecordCount = iRowCount1;
this.MarketingActivitySqlDataSource.SelectParameters["Where"].DefaultValue = sWhere;
this.gridMarketingActivityList.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
#region 校验页面参数
string sCloseDialogCodes = this.GetCloseDialogJs();
if (this.Request.QueryString["LoanID"] == null &&
this.Request.QueryString["ProspectID"] == null &&
this.Request.QueryString["ContactID"] == null &&
this.Request.QueryString["ProspectAlertID"] == null)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery1", "$('#divContainer').hide();alert('Missing required query string.');" + sCloseDialogCodes, true);
return;
}
if (this.Request.QueryString["LoanID"] != null)
{
#region LoanID
bool bIsValid = PageCommon.ValidateQueryString(this, "LoanID", QueryStringType.ID);
if (bIsValid == false)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery2", "$('#divContainer').hide();alert('Missing required query string.');" + sCloseDialogCodes, true);
return;
}
this.iLoanID = Convert.ToInt32(this.Request.QueryString["LoanID"]);
#endregion
}
else if (this.Request.QueryString["ProspectID"] != null)
{
#region ProspectID
bool bIsValid = PageCommon.ValidateQueryString(this, "ProspectID", QueryStringType.ID);
if (bIsValid == false)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery2", "$('#divContainer').hide();alert('Missing required query string.');" + sCloseDialogCodes, true);
return;
}
this.iProspectID = Convert.ToInt32(this.Request.QueryString["ProspectID"]);
#endregion
}
else if (this.Request.QueryString["ContactID"] != null)
{
#region ContactID
bool bIsValid = PageCommon.ValidateQueryString(this, "ContactID", QueryStringType.ID);
if (bIsValid == false)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery2", "$('#divContainer').hide();alert('Missing required query string.');" + sCloseDialogCodes, true);
return;
}
this.iContactID = Convert.ToInt32(this.Request.QueryString["ContactID"]);
#endregion
}
else if (this.Request.QueryString["ProspectAlertID"] != null)
{
#region ProspectAlertID
bool bIsValid = PageCommon.ValidateQueryString(this, "ProspectAlertID", QueryStringType.ID);
if (bIsValid == false)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery2", "$('#divContainer').hide();alert('Missing required query string.');" + sCloseDialogCodes, true);
return;
}
this.iProspectAlertID = Convert.ToInt32(this.Request.QueryString["ProspectAlertID"]);
#endregion
#region 获取ProspectID
ProspectAlerts ProspectAlertManager = new ProspectAlerts();
DataTable ProspectAlertInfo = ProspectAlertManager.GetProspectAlertInfo(this.iProspectAlertID);
if (ProspectAlertInfo.Rows.Count == 0)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery3", "$('#divContainer').hide();alert('Invalid prospect alert id.');" + sCloseDialogCodes, true);
return;
}
#endregion
}
#endregion
#region EWS
Company_Web CompanyWebManager = new Company_Web();
LPWeb.Model.Company_Web Company_Web_Model = CompanyWebManager.GetModel();
Users UserManager = new Users();
LPWeb.Model.Users UserModel = UserManager.GetModel(this.CurrUser.iUserID);
if (Company_Web_Model.SendEmailViaEWS && UserModel.Password == string.Empty)
{
this.hdnUseEWS.Value = "True";
}
else
{
this.hdnUseEWS.Value = "False";
}
#endregion
if (this.IsPostBack == false)
{
#region 加载email template
Template_Email EmailTempManager = new Template_Email();
DataTable EmailTemplates = EmailTempManager.GetEmailTemplate(" and Enabled = 1 "); //gdc 20130528 取消 order by [Name] asc 有人在方法里加入了排序
this.ddlEmailTemplateList.DataSource = EmailTemplates;
this.ddlEmailTemplateList.DataBind();
#endregion
#region 加载To(Dropdown List)
if (this.Request.QueryString["LoanID"] != null)
{
#region LoanID
string sSql = "select 'User'+convert(varchar, a.UserId) as UserID, c.Name +' - '+ b.LastName +', '+b.FirstName as RoleAndFullName from LoanTeam as a "
+ "inner join Users as b on a.UserId = b.UserId "
+ "inner join Roles as c on a.RoleId = c.RoleId "
+ "where FileId=" + this.iLoanID + " "
+ "union "
+ "select 'Contact'+convert(varchar, a.ContactId) as UserID, c.Name +' - '+ b.LastName +', '+ b.FirstName + case when ISNULL(b.MiddleName, '') != '' then ' '+ b.MiddleName else '' end as RoleAndFullName from LoanContacts as a "
+ "inner join Contacts as b on a.ContactId = b.ContactId "
+ "inner join ContactRoles as c on a.ContactRoleId = c.ContactRoleId "
+ "where FileId=" + this.iLoanID + " "
+ "union "
+ "select 'User'+convert(varchar, BranchMgrId) as UserID, 'Branch Manager - ' + b.LastName +', '+b.FirstName as RoleAndFullName from BranchManagers as a "
+ "inner join Users as b on a.BranchMgrId = b.UserId "
+ "where BranchId = ( "
+ "select b.BranchId from PointFiles as a "
+ "inner join PointFolders as b on a.FolderId = b.FolderId "
+ "where FileId=" + this.iLoanID + ")";
SqlCommand SqlCmd = new SqlCommand(sSql);
DataTable ToListData = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd);
this.ddlToList.DataSource = ToListData;
this.ddlToList.DataBind();
string sSql2 = "select 'User'+convert(varchar, a.UserId) as UserID, b.LastName +', '+b.FirstName as FullName from LoanTeam as a "
+ "inner join Users as b on a.UserId = b.UserId "
+ "inner join Roles as c on a.RoleId = c.RoleId "
+ "where FileId=" + this.iLoanID + " "
+ "union "
+ "select 'Contact'+convert(varchar, a.ContactId) as UserID, b.LastName +', '+ b.FirstName + case when ISNULL(b.MiddleName, '') != '' then ' '+ b.MiddleName else '' end as FullName from LoanContacts as a "
+ "inner join Contacts as b on a.ContactId = b.ContactId "
+ "inner join ContactRoles as c on a.ContactRoleId = c.ContactRoleId "
+ "where FileId=" + this.iLoanID + " "
+ "union "
+ "select 'User'+convert(varchar, BranchMgrId) as UserID, b.LastName +', '+b.FirstName as FullName from BranchManagers as a "
+ "inner join Users as b on a.BranchMgrId = b.UserId "
+ "where BranchId = ( "
+ "select b.BranchId from PointFiles as a "
+ "inner join PointFolders as b on a.FolderId = b.FolderId "
+ "where FileId=" + this.iLoanID + ")";
SqlCommand SqlCmd2 = new SqlCommand(sSql2);
DataTable ToList_FullName_Data = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd2);
this.ddlToList_FullName.DataSource = ToList_FullName_Data;
this.ddlToList_FullName.DataBind();
string sSql3 = "select 'User'+convert(varchar, a.UserId) as UserID, isnull(b.EmailAddress,'') as Email from LoanTeam as a "
+ "inner join Users as b on a.UserId = b.UserId "
+ "inner join Roles as c on a.RoleId = c.RoleId "
+ "where FileId=" + this.iLoanID + " "
+ "union "
+ "select 'Contact'+convert(varchar, a.ContactId) as UserID, isnull(b.Email,'') as Email from LoanContacts as a "
+ "inner join Contacts as b on a.ContactId = b.ContactId "
+ "inner join ContactRoles as c on a.ContactRoleId = c.ContactRoleId "
+ "where FileId=" + this.iLoanID + " "
+ "union "
+ "select 'User'+convert(varchar, BranchMgrId) as UserID, isnull(b.EmailAddress,'') as Email from BranchManagers as a "
+ "inner join Users as b on a.BranchMgrId = b.UserId "
+ "where BranchId = ( "
+ "select b.BranchId from PointFiles as a "
+ "inner join PointFolders as b on a.FolderId = b.FolderId "
+ "where FileId=" + this.iLoanID + ")";
SqlCommand SqlCmd3 = new SqlCommand(sSql3);
DataTable ToList_Email_Data = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd3);
this.ddlToList_Email.DataSource = ToList_Email_Data;
this.ddlToList_Email.DataBind();
#endregion
}
else if (this.Request.QueryString["ContactID"] != null)
{
#region ContactID
string sSql = "select 'Contact'+convert(varchar, a.ContactId) as UserID, a.LastName +', '+ a.FirstName + case when ISNULL(a.MiddleName, '') != '' then ' '+ a.MiddleName else '' end as RoleAndFullName from Contacts as a "
+ "where ContactId=" + this.iContactID;
SqlCommand SqlCmd = new SqlCommand(sSql);
DataTable ToListData = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd);
this.ddlToList.DataSource = ToListData;
this.ddlToList.DataBind();
string sSql2 = "select 'Contact'+convert(varchar, a.ContactId) as UserID, a.LastName +', '+ a.FirstName + case when ISNULL(a.MiddleName, '') != '' then ' '+ a.MiddleName else '' end as FullName from Contacts as a "
+ "where ContactId=" + this.iContactID;
SqlCommand SqlCmd2 = new SqlCommand(sSql2);
DataTable ToList_FullName_Data = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd2);
this.ddlToList_FullName.DataSource = ToList_FullName_Data;
this.ddlToList_FullName.DataBind();
string sSql3 = "select 'Contact'+convert(varchar, a.ContactId) as UserID, isnull(a.Email,'') as Email from Contacts as a "
+ "where ContactId=" + this.iContactID;
SqlCommand SqlCmd3 = new SqlCommand(sSql3);
DataTable ToList_Email_Data = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd3);
this.ddlToList_Email.DataSource = ToList_Email_Data;
this.ddlToList_Email.DataBind();
#endregion
}
else
{
#region ProspectID or ProspectAlertID
string sSql = "select 'User'+convert(varchar, a.Loanofficer) as UserID, c.Name +' - '+ b.LastName +', '+b.FirstName as RoleAndFullName "
+ "from Prospect as a inner join Users as b on a.Loanofficer = b.UserId "
+ "inner join Roles as c on b.RoleId = c.RoleId "
+ "where a.Contactid=" + this.iProspectID + " "
+ "union "
+ "select 'Contact'+convert(varchar, a.ContactId) as UserID, d.Name +' - '+ b.LastName +', '+ b.FirstName + case when ISNULL(b.MiddleName, '') != '' then ' '+ b.MiddleName else '' end as RoleAndFullName "
+ "from Prospect as a inner join Contacts as b on a.Contactid = b.ContactId "
+ "inner join LoanContacts as c on a.Contactid = c.ContactId "
+ "inner join ContactRoles as d on c.ContactRoleId = d.ContactRoleId "
+ "where a.Contactid=" + this.iProspectID + " "
+ "union "
+ "select 'User'+convert(varchar, a.UserId) as UserID, d.Name +' - '+ a.LastName +', '+a.FirstName as RoleAndFullName "
+ "from Users as a "
+ "inner join GroupUsers as b on a.UserId = b.UserID "
+ "inner join Groups as c on b.GroupID = c.GroupId "
+ "inner join Roles as d on a.RoleId = d.RoleId "
+ "where c.Enabled = 1 and a.UserEnabled=1 "
+ "and c.BranchId in (select * from lpfn_GetUserBranches_UserList((select Loanofficer from Prospect where Contactid=" + this.iProspectID + ")))";
SqlCommand SqlCmd = new SqlCommand(sSql);
DataTable ToListData = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd);
this.ddlToList.DataSource = ToListData;
this.ddlToList.DataBind();
string sSql2 = "select 'User'+convert(varchar, a.Loanofficer) as UserID, b.LastName +', '+b.FirstName as FullName "
+ "from Prospect as a inner join Users as b on a.Loanofficer = b.UserId "
+ "where a.Contactid=" + this.iProspectID + " "
+ "union "
+ "select 'Contact'+convert(varchar, a.ContactId) as UserID, b.LastName +', '+ b.FirstName + case when ISNULL(b.MiddleName, '') != '' then ' '+ b.MiddleName else '' end as FullName "
+ "from Prospect as a inner join Contacts as b on a.Contactid = b.ContactId "
+ "where a.Contactid=" + this.iProspectID + " "
+ "union "
+ "select 'User'+convert(varchar, a.UserId) as UserID, a.LastName +', '+a.FirstName as FullName "
+ "from Users as a "
+ "inner join GroupUsers as b on a.UserId = b.UserID "
+ "inner join Groups as c on b.GroupID = c.GroupId "
+ "where c.Enabled = 1 and a.UserEnabled=1 "
+ "and c.BranchId in (select * from lpfn_GetUserBranches_UserList((select Loanofficer from Prospect where Contactid=" + this.iProspectID + ")))";
SqlCommand SqlCmd2 = new SqlCommand(sSql2);
DataTable ToList_FullName_Data = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd2);
this.ddlToList_FullName.DataSource = ToList_FullName_Data;
this.ddlToList_FullName.DataBind();
string sSql3 = "select 'User'+convert(varchar, a.Loanofficer) as UserID, isnull(b.EmailAddress,'') as Email "
+ "from Prospect as a inner join Users as b on a.Loanofficer = b.UserId "
+ "where a.Contactid=" + this.iProspectID + " "
+ "union "
+ "select 'Contact'+convert(varchar, a.ContactId) as UserID, isnull(b.Email,'') as Email "
+ "from Prospect as a inner join Contacts as b on a.Contactid = b.ContactId "
+ "where a.Contactid=" + this.iProspectID + " "
+ "union "
+ "select 'User'+convert(varchar, a.UserId) as UserID, isnull(a.EmailAddress,'') as Email "
+ "from Users as a "
+ "inner join GroupUsers as b on a.UserId = b.UserID "
+ "inner join Groups as c on b.GroupID = c.GroupId "
+ "where c.Enabled = 1 and a.UserEnabled=1 "
+ "and c.BranchId in (select * from lpfn_GetUserBranches_UserList((select Loanofficer from Prospect where Contactid=" + this.iProspectID + ")))";
SqlCommand SqlCmd3 = new SqlCommand(sSql3);
DataTable ToList_Email_Data = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd3);
this.ddlToList_Email.DataSource = ToList_Email_Data;
this.ddlToList_Email.DataBind();
#endregion
}
#endregion
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(LPWeb.Model.Users model)
{
dal.Update(model);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(LPWeb.Model.Users model)
{
return(dal.Add(model));
}
protected void Page_Load(object sender, EventArgs e)
{
#region 校验页面参数
string sCloseDialogCodes = this.GetCloseDialogJs();
if (this.Request.QueryString["ContactID"] == null)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery1", "$('#divContainer').hide();alert('Missing required query string.');" + sCloseDialogCodes, true);
return;
}
if (this.Request.QueryString["ContactID"] != null)
{
#region ContactID
bool bIsValid = PageCommon.ValidateQueryString(this, "ContactID", QueryStringType.ID);
if (bIsValid == false)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery2", "$('#divContainer').hide();alert('Missing required query string.');" + sCloseDialogCodes, true);
return;
}
this.iContactID = Convert.ToInt32(this.Request.QueryString["ContactID"]);
#endregion
}
#endregion
#region EWS
Company_Web CompanyWebManager = new Company_Web();
LPWeb.Model.Company_Web Company_Web_Model = CompanyWebManager.GetModel();
Users UserManager = new Users();
LPWeb.Model.Users UserModel = UserManager.GetModel(this.CurrUser.iUserID);
if (Company_Web_Model.SendEmailViaEWS && UserModel.Password == string.Empty)
{
this.hdnUseEWS.Value = "True";
}
else
{
this.hdnUseEWS.Value = "False";
}
#endregion
if (this.IsPostBack == false)
{
#region 加载email template
Template_Email EmailTempManager = new Template_Email();
DataTable EmailTemplates = EmailTempManager.GetEmailTemplate(" and Enabled = 1 ");
this.ddlEmailTemplateList.DataSource = EmailTemplates;
this.ddlEmailTemplateList.DataBind();
#endregion
#region 加载To(Dropdown List)
if (this.Request.QueryString["ContactID"] != null)
{
#region ContactID
string sSql = "select 'Contact'+convert(varchar, ContactId) as UserID, LastName +', '+ FirstName + case when ISNULL(MiddleName, '') != '' then ' '+ MiddleName else '' end as RoleAndFullName from Contacts "
+ "where ContactId=" + this.iContactID;
if (CurrUser.bIsCompanyUser)
{
// All Contact
sSql += "union select 'Contact'+convert(varchar, a.ContactId) as UserID, a.LastName +', '+ a.FirstName + case when ISNULL(a.MiddleName, '') != '' then ' '+ a.MiddleName else '' end as RoleAndFullName from Contacts as a "
+ "where a.ContactId<>" + this.iContactID;
}
else if (CurrUser.bIsRegionUser)
{
sSql += "union select 'Contact'+convert(varchar, a.ContactId) as UserID, a.LastName +', '+ a.FirstName + case when ISNULL(a.MiddleName, '') != '' then ' '+ a.MiddleName else '' end as RoleAndFullName from Contacts as a "
+ "where a.ContactBranchId in (SELECT BranchId FROM Branches WHERE RegionID IN (Select RegionID from Regions where GroupID in (select GroupID from GroupUsers where UserID=" + CurrUser.iUserID + "))) and a.[Enabled]=1 and a.ContactId<>" + this.iContactID;
}
else if (CurrUser.bIsDivisionUser)
{
sSql += "union select 'Contact'+convert(varchar, a.ContactId) as UserID, a.LastName +', '+ a.FirstName + case when ISNULL(a.MiddleName, '') != '' then ' '+ a.MiddleName else '' end as RoleAndFullName from Contacts as a "
+ " where a.ContactBranchId in (SELECT BranchId FROM Branches WHERE DivisionID IN (Select DivisionID from Divisions where GroupID in (select GroupID from GroupUsers where UserID=" + CurrUser.iUserID + "))) and a.[Enabled]=1 and a.ContactId<>" + this.iContactID;
}
else
{
sSql += "union select 'Contact'+convert(varchar, a.ContactId) as UserID, a.LastName +', '+ a.FirstName + case when ISNULL(a.MiddleName, '') != '' then ' '+ a.MiddleName else '' end as RoleAndFullName from Contacts as a "
+ "where a.ContactBranchId in (Select BranchId from Branches where GroupID in (select GroupID from GroupUsers where UserID=" + CurrUser.iUserID + ")) and a.[Enabled]=1 and a.ContactId<>" + this.iContactID;
}
string sNewSql = "select * from (" + sSql + ") as t order by RoleAndFullName";
SqlCommand SqlCmd = new SqlCommand(sNewSql);
DataTable ToListData = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd);
this.ddlToList.DataSource = ToListData;
this.ddlToList.DataBind();
this.ddlToList.SelectedValue = "Contact" + this.iContactID.ToString();
string sSql2 = "select 'Contact'+convert(varchar, ContactId) as UserID, LastName +', '+ FirstName + case when ISNULL(MiddleName, '') != '' then ' '+ MiddleName else '' end as FullName from Contacts "
+ "where ContactId=" + this.iContactID;
if (CurrUser.bIsCompanyUser)
{
// All Contact
sSql2 += "Union select 'Contact'+convert(varchar, a.ContactId) as UserID, a.LastName +', '+ a.FirstName + case when ISNULL(a.MiddleName, '') != '' then ' '+ a.MiddleName else '' end as FullName from Contacts as a "
+ " where a.ContactId<>" + this.iContactID;
}
else if (CurrUser.bIsRegionUser)
{
sSql2 += "Union select 'Contact'+convert(varchar, a.ContactId) as UserID, a.LastName +', '+ a.FirstName + case when ISNULL(a.MiddleName, '') != '' then ' '+ a.MiddleName else '' end as FullName from Contacts as a "
+ " where a.ContactBranchId in (SELECT BranchId FROM Branches WHERE RegionID IN (Select RegionID from Regions where GroupID in (select GroupID from GroupUsers where UserID=" + CurrUser.iUserID + "))) and a.[Enabled]=1 and a.ContactId<>" + this.iContactID;
}
else if (CurrUser.bIsDivisionUser)
{
sSql2 += "Union select 'Contact'+convert(varchar, a.ContactId) as UserID, a.LastName +', '+ a.FirstName + case when ISNULL(a.MiddleName, '') != '' then ' '+ a.MiddleName else '' end as FullName from Contacts as a "
+ " where a.ContactBranchId in (SELECT BranchId FROM Branches WHERE DivisionID IN (Select DivisionID from Divisions where GroupID in (select GroupID from GroupUsers where UserID=" + CurrUser.iUserID + "))) and a.[Enabled]=1 and a.ContactId<>" + this.iContactID;
}
else
{
sSql2 += "Union select 'Contact'+convert(varchar, a.ContactId) as UserID, a.LastName +', '+ a.FirstName + case when ISNULL(a.MiddleName, '') != '' then ' '+ a.MiddleName else '' end as FullName from Contacts as a "
+ " where a.ContactBranchId in (Select BranchId from Branches where GroupID in (select GroupID from GroupUsers where UserID=" + CurrUser.iUserID + ")) and a.[Enabled]=1 and a.ContactId<>" + this.iContactID;
}
SqlCommand SqlCmd2 = new SqlCommand(sSql2);
DataTable ToList_FullName_Data = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd2);
this.ddlToList_FullName.DataSource = ToList_FullName_Data;
this.ddlToList_FullName.DataBind();
string sSql3 = "select 'Contact'+convert(varchar, ContactId) as UserID, isnull(Email,'') as Email from Contacts "
+ "where ContactId=" + this.iContactID;
if (CurrUser.bIsCompanyUser)
{
// All Contact
sSql3 += "Union select 'Contact'+convert(varchar, a.ContactId) as UserID, isnull(a.Email,'') as Email from Contacts as a "
+ " where a.ContactId<>" + this.iContactID;
}
else if (CurrUser.bIsRegionUser)
{
sSql3 += "Union select 'Contact'+convert(varchar, a.ContactId) as UserID, isnull(a.Email,'') as Email from Contacts as a "
+ " where a.ContactBranchId in (SELECT BranchId FROM Branches WHERE RegionID IN (Select RegionID from Regions where GroupID in (select GroupID from GroupUsers where UserID=" + CurrUser.iUserID + "))) and a.[Enabled]=1 and a.ContactId<>" + this.iContactID;
}
else if (CurrUser.bIsDivisionUser)
{
sSql3 += "Union select 'Contact'+convert(varchar, a.ContactId) as UserID, isnull(a.Email,'') as Email from Contacts as a "
+ " where a.ContactBranchId in (SELECT BranchId FROM Branches WHERE DivisionID IN (Select DivisionID from Divisions where GroupID in (select GroupID from GroupUsers where UserID=" + CurrUser.iUserID + "))) and a.[Enabled]=1 and a.ContactId<>" + this.iContactID;
}
else
{
sSql3 += "Union select 'Contact'+convert(varchar, a.ContactId) as UserID, isnull(a.Email,'') as Email from Contacts as a "
+ " where a.ContactBranchId in (Select BranchId from Branches where GroupID in (select GroupID from GroupUsers where UserID=" + CurrUser.iUserID + ")) and a.[Enabled]=1 and a.ContactId<>" + this.iContactID;
}
SqlCommand SqlCmd3 = new SqlCommand(sSql3);
DataTable ToList_Email_Data = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(SqlCmd3);
this.ddlToList_Email.DataSource = ToList_Email_Data;
this.ddlToList_Email.DataBind();
#endregion
}
#endregion
}
}
protected void Page_Load(object sender, EventArgs e)
{
#region 校验页面参数
string sErrorJs = "$('#divContainer').hide();alert('Missing required query string.');window.parent.CloseDialog_SendCompletionEmail();";
bool bIsValid = PageCommon.ValidateQueryString(this, "LoanID", QueryStringType.ID);
if (bIsValid == false)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery1", sErrorJs, true);
return;
}
this.iLoanID = Convert.ToInt32(this.Request.QueryString["LoanID"]);
bIsValid = PageCommon.ValidateQueryString(this, "EmailTemplateID", QueryStringType.ID);
if (bIsValid == false)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery2", sErrorJs, true);
return;
}
this.iEmailTemplateID = Convert.ToInt32(this.Request.QueryString["EmailTemplateID"]);
string sTaskID = "";
sTaskID = this.Request.QueryString["TaskID"];
if ((sTaskID != null) && (sTaskID != ""))
{
this.iTaskID = Convert.ToInt32(sTaskID);
}
#endregion
#region EWS
Company_Web CompanyWebManager = new Company_Web();
LPWeb.Model.Company_Web Company_Web_Model = CompanyWebManager.GetModel();
Users UserManager = new Users();
LPWeb.Model.Users UserModel = UserManager.GetModel(this.CurrUser.iUserID);
if (Company_Web_Model.SendEmailViaEWS && UserModel.Password == string.Empty)
{
this.hdnUseEWS.Value = "True";
}
else
{
this.hdnUseEWS.Value = "False";
}
#endregion
#region 旧代码
//#region 加载email template info
//Template_Email EmailTempManager = new Template_Email();
//DataTable EmailTemplateInfo = EmailTempManager.GetEmailTemplateInfo(this.iEmailTemplateID);
//if (EmailTemplateInfo.Rows.Count == 0)
//{
// this.ClientScript.RegisterStartupScript(this.GetType(), "_InvalidQuery3", "$('#divContainer').hide();alert('The email template does not exist.');window.parent.CloseDialog_SendCompletionEmail();", true);
// return;
//}
//string sEmailTemplate = EmailTemplateInfo.Rows[0]["Name"].ToString();
//this.lbEmailTemplate.Text = sEmailTemplate;
//string sFromUserRoleID = EmailTemplateInfo.Rows[0]["FromUserRoles"].ToString();
//if (sFromUserRoleID != string.Empty)
//{
// #region 获取Loan Team和Users信息
// string sSql = "select * from LoanTeam as a inner join Users as b on a.UserId = b.UserId where a.FileId=" + this.iLoanID + " and a.RoleId=" + sFromUserRoleID;
// DataTable SenderInfo = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql);
// if (SenderInfo.Rows.Count == 0)
// {
// this.lbSender.Text = "There is no sender, so can't send email.";
// this.lbSender.ForeColor = System.Drawing.Color.Red;
// }
// else
// {
// string sFirstName = SenderInfo.Rows[0]["FirstName"].ToString();
// string sLastName = SenderInfo.Rows[0]["LastName"].ToString();
// string sFullName = sLastName + ", " + sFirstName;
// this.lbSender.Text = sFullName;
// }
// #endregion
//}
//else
//{
// string sFromEmailAddress = EmailTemplateInfo.Rows[0]["FromEmailAddress"].ToString();
// if (sFromEmailAddress != string.Empty)
// {
// this.lbSender.Text = sFromEmailAddress;
// }
// else
// {
// this.lbSender.Text = "There is no sender.";
// this.lbSender.ForeColor = System.Drawing.Color.Red;
// }
//}
//#endregion
//if (this.IsPostBack == false)
//{
// #region 加载Recipient列表
// #region 获取Email Template的Recipient(s)
// Template_Email EmailTemplateManager = new Template_Email();
// DataTable RecipientList = EmailTemplateManager.GetRecipientList(this.iEmailTemplateID);
// #endregion
// #region 获取Loan Team
// string sSql = "select * from LoanTeam as a inner join Users as b on a.UserId = b.UserId where a.FileId=" + this.iLoanID;
// DataTable LoanTeamList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql);
// #endregion
// #region 获取Contacts
// string sSql2 = "select * from LoanContacts as a inner join Contacts as b on a.ContactId = b.ContactId where a.FileId=" + this.iLoanID;
// DataTable ContactList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql2);
// #endregion
// DataTable RecipientListData = this.BuildRecipientDataTable();
// #region To
// DataRow[] ToRecipient = RecipientList.Select("RecipientType='To'");
// if (ToRecipient.Length > 0)
// {
// string sEmailList_To = ToRecipient[0]["EmailAddr"].ToString();
// string sContactList_To = ToRecipient[0]["ContactRoles"].ToString();
// string sUserRoleList_To = ToRecipient[0]["UserRoles"].ToString();
// string sTaskOwner = ToRecipient[0]["TaskOwner"].ToString();
// #region Emails
// if (sEmailList_To != string.Empty)
// {
// string[] EmailArray_To = sEmailList_To.Split(';');
// foreach (string sEmailTo in EmailArray_To)
// {
// this.AddNewRecipientRow(RecipientListData, "To", string.Empty, sEmailTo);
// }
// }
// #endregion
// #region User IDs
// if (sUserRoleList_To != string.Empty)
// {
// string[] UserRoleArray_To = sUserRoleList_To.Split(';');
// foreach (string sUserRoleIDTo in UserRoleArray_To)
// {
// int iUserRoleIDTo = Convert.ToInt32(sUserRoleIDTo);
// DataRow[] LoanTeamRows = LoanTeamList.Select("RoleId=" + iUserRoleIDTo);
// foreach (DataRow LoanTeamRow in LoanTeamRows)
// {
// string sFullName = LoanTeamRow["LastName"].ToString() + ", " + LoanTeamRow["FirstName"].ToString();
// string sEmailAddress = LoanTeamRow["EmailAddress"].ToString();
// if (sEmailAddress == string.Empty)
// {
// this.AddNewRecipientRow(RecipientListData, "To", sFullName, "There is no emaill address in the user account.");
// }
// else
// {
// this.AddNewRecipientRow(RecipientListData, "To", sFullName, sEmailAddress);
// }
// }
// }
// }
// #endregion
// #region Contact IDs
// if (sContactList_To != string.Empty)
// {
// string[] ContactArray_To = sContactList_To.Split(';');
// foreach (string sContactIDTo in ContactArray_To)
// {
// int iContactRoleIDTo = Convert.ToInt32(sContactIDTo);
// DataRow[] ContactRows = ContactList.Select("ContactRoleId=" + iContactRoleIDTo);
// foreach (DataRow ContactRow in ContactRows)
// {
// #region Build FullName
// string sFirstName = ContactRow["FirstName"].ToString();
// string sLastName = ContactRow["LastName"].ToString();
// string sMiddleName = ContactRow["MiddleName"].ToString();
// string sFullName = string.Empty;
// if (sMiddleName != string.Empty)
// {
// sFullName = sLastName + ", " + sFirstName + " " + sMiddleName;
// }
// else
// {
// sFullName = sLastName + ", " + sFirstName;
// }
// #endregion
// string sEmailAddress = ContactRow["Email"].ToString();
// if (sEmailAddress == string.Empty)
// {
// this.AddNewRecipientRow(RecipientListData, "To", sFullName, "There is no emaill address in the contact account.");
// }
// else
// {
// this.AddNewRecipientRow(RecipientListData, "To", sFullName, sEmailAddress);
// }
// }
// }
// }
// #endregion
// #region TaskOwner
// if ((sTaskOwner != string.Empty) && (sTaskOwner != null))
// {
// if (sTaskOwner == "True")
// {
// string sSql_LoanTasks = "select Owner from LoanTasks where LoanTaskId=" + this.iTaskID;
// DataTable LoanTasks_List = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql_LoanTasks);
// string sOwnerId = LoanTasks_List.Rows[0]["Owner"].ToString();
// if ((sTaskOwner != string.Empty) && (sTaskOwner != null))
// {
// int iOwnerId = Convert.ToInt32(sOwnerId);
// string sSql_Users = "select EmailAddress, LastName, FirstName from Users where UserId=" + iOwnerId;
// DataTable Users_List = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql_Users);
// string Owner_EmailAddress = Users_List.Rows[0]["EmailAddress"].ToString();
// if ((Owner_EmailAddress != string.Empty) && (Owner_EmailAddress != null))
// {
// string sOwnerFullName = Users_List.Rows[0]["LastName"].ToString() + ", " + Users_List.Rows[0]["FirstName"].ToString();
// this.AddNewRecipientRow(RecipientListData, "To", sOwnerFullName, Owner_EmailAddress);
// }
// }
// }
// }
// #endregion
// }
// #endregion
// #region CC
// DataRow[] CCRecipient = RecipientList.Select("RecipientType='CC'");
// if (CCRecipient.Length > 0)
// {
// string sEmailList_CC = CCRecipient[0]["EmailAddr"].ToString();
// string sContactList_CC = CCRecipient[0]["ContactRoles"].ToString();
// string sUserRoleList_CC = CCRecipient[0]["UserRoles"].ToString();
// #region Emails
// if (sEmailList_CC != string.Empty)
// {
// string[] EmailArray_CC = sEmailList_CC.Split(';');
// foreach (string sEmailCC in EmailArray_CC)
// {
// this.AddNewRecipientRow(RecipientListData, "CC", string.Empty, sEmailCC);
// }
// }
// #endregion
// #region User IDs
// if (sUserRoleList_CC != string.Empty)
// {
// string[] UserRoleArray_CC = sUserRoleList_CC.Split(';');
// foreach (string sUserRoleIDCC in UserRoleArray_CC)
// {
// int iUserRoleIDCC = Convert.ToInt32(sUserRoleIDCC);
// DataRow[] LoanTeamRows = LoanTeamList.Select("RoleId=" + iUserRoleIDCC);
// foreach (DataRow LoanTeamRow in LoanTeamRows)
// {
// string sFullName = LoanTeamRow["LastName"].ToString() + ", " + LoanTeamRow["FirstName"].ToString();
// string sEmailAddress = LoanTeamRow["EmailAddress"].ToString();
// if (sEmailAddress == string.Empty)
// {
// this.AddNewRecipientRow(RecipientListData, "CC", sFullName, "There is no emaill address in the user account.");
// }
// else
// {
// this.AddNewRecipientRow(RecipientListData, "CC", sFullName, sEmailAddress);
// }
// }
// }
// }
// #endregion
// #region Contact IDs
// if (sContactList_CC != string.Empty)
// {
// string[] ContactArray_CC = sContactList_CC.Split(';');
// foreach (string sContactIDCC in ContactArray_CC)
// {
// int iContactRoleIDCC = Convert.ToInt32(sContactIDCC);
// DataRow[] ContactRows = ContactList.Select("ContactRoleId=" + iContactRoleIDCC);
// foreach (DataRow ContactRow in ContactRows)
// {
// #region Build FullName
// string sFirstName = ContactRow["FirstName"].ToString();
// string sLastName = ContactRow["LastName"].ToString();
// string sMiddleName = ContactRow["MiddleName"].ToString();
// string sFullName = string.Empty;
// if (sMiddleName != string.Empty)
// {
// sFullName = sLastName + ", " + sFirstName + " " + sMiddleName;
// }
// else
// {
// sFullName = sLastName + ", " + sFirstName;
// }
// #endregion
// string sEmailAddress = ContactRow["Email"].ToString();
// if (sEmailAddress == string.Empty)
// {
// this.AddNewRecipientRow(RecipientListData, "CC", sFullName, "There is no emaill address in the contact account.");
// }
// else
// {
// this.AddNewRecipientRow(RecipientListData, "CC", sFullName, sEmailAddress);
// }
// }
// }
// }
// #endregion
// }
// #endregion
// this.gridRecipientList.DataSource = RecipientListData;
// this.gridRecipientList.DataBind();
// #endregion
//}
#endregion
#region 新页面信息绑定 Borrower/Task/EmailTemplate
#region 加载Borrower Info
string sSql5 = "select * from LoanContacts as b1 inner join Contacts as b2 on b1.ContactId = b2.ContactId where b1.FileId=" + this.iLoanID + " and b1.ContactRoleId=dbo.lpfn_GetBorrowerRoleId() ";
DataTable BorrowerInfo = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql5);
#endregion
#region 绑定Borrower信息
this.lbBorrower.Text = "";
if (BorrowerInfo.Rows.Count > 0)
{
string sBorrowerLastName = BorrowerInfo.Rows[0]["LastName"].ToString();
string sBorrowerFristName = BorrowerInfo.Rows[0]["FirstName"].ToString();
string sBorrowerMiddleName = BorrowerInfo.Rows[0]["MiddleName"].ToString();
StringBuilder sbBorrowerName = new StringBuilder();
sbBorrowerName.Append(sBorrowerLastName);
if (sBorrowerFristName != string.Empty)
{
sbBorrowerName.Append(", " + sBorrowerFristName);
}
if (sBorrowerMiddleName != string.Empty)
{
sbBorrowerName.Append(" " + sBorrowerMiddleName);
}
this.lbBorrower.Text = sbBorrowerName.ToString();
}
#endregion
#region 加载Task Info
string sSql6 = "SELECT [Name] FROM [LoanTasks] WHERE LoanTaskId=" + this.iTaskID;
DataTable TaskInfo = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql6);
#endregion
#region 绑定Task Info信息
this.lbTask.Text = "";
if (TaskInfo.Rows.Count > 0)
{
string taskName = TaskInfo.Rows[0]["Name"].ToString();
this.lbTask.Text = taskName;
}
#endregion
#region 加载email template
Template_Email EmailTempManager = new Template_Email();
EmailTemplates = EmailTempManager.GetEmailTemplate(" and Enabled = 1");
DataRow NoneEmailTemplateRow = EmailTemplates.NewRow();
NoneEmailTemplateRow["TemplEmailId"] = 0;
NoneEmailTemplateRow["Name"] = "None";
EmailTemplates.Rows.InsertAt(NoneEmailTemplateRow, 0);
#endregion
#region 加载 任务邮件模板 并绑定
LPWeb.BLL.LoanTask_CompletionEmails bllLT_com_Emails = new LoanTask_CompletionEmails();
var ltceDS = bllLT_com_Emails.GetList("[Enabled]=1 AND LoanTaskid=" + this.iTaskID);
gridCompletetionEmails.DataSource = ltceDS;//new List<string>(){"t1","t2"};
gridCompletetionEmails.DataBind();
#endregion
#endregion
}
public void BindData()
{
LPWeb.BLL.Loans bllLoans = new LPWeb.BLL.Loans();
LPWeb.BLL.LoanTeam bllLoanTeam = new LPWeb.BLL.LoanTeam();
LPWeb.BLL.Users bllUsers = new LPWeb.BLL.Users();
LPWeb.Model.Loans loanInfo = bllLoans.GetModel(LoanID);
DataTable LoanInfo_datatable = bllLoans.GetLoanInfo(LoanID);
decimal loanAmount = 0;
LoanPointFields LoanPointFieldsMgr = new LoanPointFields();
//LPWeb.Model.LoanPointFields PointFieldInfo = LoanPointFieldsMgr.GetPointFieldInfo(LoanID, 21017);
LPWeb.Model.LoanPointFields PointFieldInfo = LoanPointFieldsMgr.GetModel(LoanID, 21017);
if (PointFieldInfo != null && !string.IsNullOrEmpty(PointFieldInfo.CurrentValue))
{
decimal.TryParse(PointFieldInfo.CurrentValue, out loanAmount);
}
if (loanAmount <= 0 && LoanInfo_datatable != null && LoanInfo_datatable.Rows.Count > 0)
{
loanAmount = LoanInfo_datatable.Rows[0]["LoanAmount"] == DBNull.Value ? 0 : (decimal)LoanInfo_datatable.Rows[0]["LoanAmount"];
}
this.labLoanamount.Text = loanAmount.ToString("n2");
var list = new List <ViewCompDetail>();
var Lo = new ViewCompDetail();
Lo.Type = "Loan Officer";
var LOUserId = bllLoanTeam.GetLoanOfficerID(LoanID);
Lo.Name = bllLoanTeam.GetLoanOfficer(LoanID);
Lo.Rate = GetUserCompRate(LOUserId);
Lo.Amount = Lo.Rate * loanAmount / 100.00M;
list.Add(Lo);
loanInfo.BranchID = loanInfo.BranchID != null ? loanInfo.BranchID : 0;
var branchM = new ViewCompDetail();
branchM.Type = "Branch Manager";
LPWeb.BLL.BranchManagers bllbranM = new LPWeb.BLL.BranchManagers();
var branchUserId = 0;
var branchObj = bllbranM.GetModelList("BranchId =" + loanInfo.BranchID).FirstOrDefault();
if (branchObj != null)
{
branchUserId = branchObj.BranchMgrId;
}
LPWeb.Model.Users userBrM = bllUsers.GetModel(branchUserId);
if (userBrM != null && userBrM.UserId == branchUserId)
{
branchM.Name = userBrM.LastName + "," + userBrM.FirstName;
branchM.Rate = GetBranchMgrCompRate(LOUserId);
branchM.Amount = branchM.Rate * loanAmount / 100.00M;
}
list.Add(branchM);
loanInfo.DivisionID = loanInfo.DivisionID != null ? loanInfo.DivisionID : 0;
var divisionM = new ViewCompDetail();
divisionM.Type = "Division Manager";
LPWeb.BLL.DivisionExecutives bllDivM = new LPWeb.BLL.DivisionExecutives();
var DivMUserId = 0;
var divobj = bllDivM.GetModelList("DivisionId =" + loanInfo.DivisionID).FirstOrDefault();
if (divobj != null)
{
DivMUserId = divobj.ExecutiveId;
}
LPWeb.Model.Users userDivM = bllUsers.GetModel(DivMUserId);
if (userDivM != null && userDivM.UserId == DivMUserId)
{
divisionM.Name = userDivM.LastName + "," + userDivM.FirstName;
divisionM.Rate = GetDivisionMgrCompRate(LOUserId);
divisionM.Amount = divisionM.Rate * loanAmount / 100.00M;
}
list.Add(divisionM);
loanInfo.RegionID = loanInfo.RegionID != null ? loanInfo.RegionID : 0;
var RegionM = new ViewCompDetail();
RegionM.Type = "Region Manager";
LPWeb.BLL.RegionExecutives bllRegionM = new LPWeb.BLL.RegionExecutives();
var RegionMUserId = 0;
var regionObj = bllRegionM.GetModelList("RegionId =" + loanInfo.RegionID).FirstOrDefault();
if (regionObj != null)
{
RegionMUserId = regionObj.ExecutiveId;
}
LPWeb.Model.Users userRegionM = bllUsers.GetModel(RegionMUserId);
if (userRegionM != null && userRegionM.UserId == RegionMUserId)
{
RegionM.Name = userRegionM.LastName + "," + userRegionM.FirstName;
RegionM.Rate = GetRegionMgrCompRate(LOUserId);
RegionM.Amount = RegionM.Rate * loanAmount / 100.00M;
}
list.Add(RegionM);
gvCompDetail.DataSource = list;
gvCompDetail.DataBind();
}