private void Save()
{
try
{
using (SqlConnection conn = DbHelperSQL.GetOpenConnection())
{
try
{
SqlCommand cmd = new SqlCommand(@"INSERT INTO ZillowLogger ([date_created],[thread],[level],[logger],[message],[exception]) VALUES (@a, @b, @c, @d, @e, @f)", conn);
DbHelperSQL.AddSqlParameter(cmd, "@a", SqlDbType.DateTime, LogDate);
DbHelperSQL.AddSqlParameter(cmd, "@b", SqlDbType.NVarChar, Helpers.FormatSqlStringValue(Thread, 255));
DbHelperSQL.AddSqlParameter(cmd, "@c", SqlDbType.NVarChar, Helpers.FormatSqlStringValue(Level, 50));
DbHelperSQL.AddSqlParameter(cmd, "@d", SqlDbType.NVarChar, Helpers.FormatSqlStringValue(Logger, 255));
DbHelperSQL.AddSqlParameter(cmd, "@e", SqlDbType.NVarChar, Helpers.FormatSqlStringValue(Message, 4000));
DbHelperSQL.AddSqlParameter(cmd, "@f", SqlDbType.NVarChar, Helpers.FormatSqlStringValue(Exception, 4000));
DbHelperSQL.ExecuteNonQuery(cmd);
}
catch (Exception ex)
{
// ignore
}
}
}
catch (Exception ex)
{
//ignore
}
}
/// <summary>
///
/// </summary>
/// <param name="FileId"></param>
/// <param name="LoanConditionId"></param>
/// <param name="Note"></param>
/// <param name="ExternalViewing"></param>
/// <param name="Sender"></param>
public void InsertConditionNote(int FileId, int LoanConditionId, string Note, bool ExternalViewing, string Sender)
{
string sSql =
@"INSERT INTO LoanNotes
(FileId
,Created
,Sender
,Note
,Exported
,ExternalViewing
,LoanTaskId
,LoanConditionId)
VALUES
(" + FileId + @"
,getdate()
,@Sender
,@Note
,null
,@ExternalViewing
,null
," + LoanConditionId + @")";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Note", SqlDbType.NVarChar, Note);
DbHelperSQL.AddSqlParameter(SqlCmd, "@ExternalViewing", SqlDbType.Bit, ExternalViewing);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Sender", SqlDbType.NVarChar, Sender);
DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
private void SaveAttributes(SqlConnection conn, SqlTransaction tran)
{
try
{
foreach (ZillowAttribute attribute in ZillowAttributes)
{
SqlCommand cmd = new SqlCommand(@"insert into ZillowAttributes (ImportTransId, Version, GroupName, AttributeName, AttributeValue, ImportedFlag, BorrowerId, CoborrowerId, LoanId) values (@a, @b, @c, @d, @e, @f, @g, @h, @i)", conn);
DbHelperSQL.AddSqlParameter(cmd, "@a", SqlDbType.UniqueIdentifier, ImportTransId);
DbHelperSQL.AddSqlParameter(cmd, "@b", SqlDbType.NVarChar, Helpers.FormatSqlStringValue(XmlVersion, 10));
DbHelperSQL.AddSqlParameter(cmd, "@c", SqlDbType.NVarChar, Helpers.FormatSqlStringValue(attribute.GroupName, 255));
DbHelperSQL.AddSqlParameter(cmd, "@d", SqlDbType.NVarChar, Helpers.FormatSqlStringValue(attribute.AttributeName, 255));
DbHelperSQL.AddSqlParameter(cmd, "@e", SqlDbType.NVarChar, Helpers.FormatSqlStringValue(attribute.AttributeValue, 2000));
DbHelperSQL.AddSqlParameter(cmd, "@f", SqlDbType.Bit, attribute.ImportedFlag);
DbHelperSQL.AddSqlParameter(cmd, "@g", SqlDbType.Int, BorrowerId);
DbHelperSQL.AddSqlParameter(cmd, "@h", SqlDbType.Int, CoborrowerId);
DbHelperSQL.AddSqlParameter(cmd, "@i", SqlDbType.Int, LoanId);
DbHelperSQL.ExecuteNonQuery(cmd, tran);
}
}
catch (Exception ex)
{
NLogger.Error(MethodBase.GetCurrentMethod().DeclaringType.FullName, MethodBase.GetCurrentMethod().Name, ex.Message, ex);
throw;
}
}
/// <summary>
/// update workflow stage
/// neo 2011-02-16
/// </summary>
/// <param name="iWflStageID"></param>
/// <param name="sWflStageName"></param>
/// <param name="iSeq"></param>
/// <param name="bEnabled"></param>
/// <param name="iDaysFromEstClose"></param>
/// <param name="iDaysFromCreation"></param>
public void UpdateWflStageBase(int iWflStageID, Int16 iSeq, bool bEnabled, Int16 iDaysFromEstClose, Int16 iDaysFromCreation, Int16 iCalcMethod)
{
string sSql4 = "UPDATE Template_Wfl_Stages SET SequenceNumber = @SequenceNumber,Enabled = @Enabled,DaysFromEstClose = @DaysFromEstClose,DaysFromCreation = @DaysFromCreation, CalculationMethod=@CalculationMethod WHERE WflStageId=@WflStageId";
SqlCommand SqlCmd4 = new SqlCommand(sSql4);
DbHelperSQL.AddSqlParameter(SqlCmd4, "@SequenceNumber", SqlDbType.SmallInt, iSeq);
DbHelperSQL.AddSqlParameter(SqlCmd4, "@Enabled", SqlDbType.Bit, bEnabled);
if (iDaysFromEstClose == 0)
{
DbHelperSQL.AddSqlParameter(SqlCmd4, "@DaysFromEstClose", SqlDbType.SmallInt, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd4, "@DaysFromEstClose", SqlDbType.SmallInt, iDaysFromEstClose);
}
if (iDaysFromCreation == 0)
{
DbHelperSQL.AddSqlParameter(SqlCmd4, "@DaysFromCreation", SqlDbType.SmallInt, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd4, "@DaysFromCreation", SqlDbType.SmallInt, iDaysFromCreation);
}
DbHelperSQL.AddSqlParameter(SqlCmd4, "@CalculationMethod", SqlDbType.SmallInt, iCalcMethod);
DbHelperSQL.AddSqlParameter(SqlCmd4, "@WflStageId", SqlDbType.Int, iWflStageID);
DbHelperSQL.ExecuteNonQuery(SqlCmd4);
}
/// <summary>
/// get email log list for prospect
/// neo 2011-04-28
/// </summary>
/// <param name="sDbTable"></param>
/// <param name="iStartIndex"></param>
/// <param name="iEndIndex"></param>
/// <param name="strWhere"></param>
/// <param name="orderName"></param>
/// <param name="orderType"></param>
/// <returns></returns>
public DataTable GetProspectEmailLogListBase(string sDbTable, int iStartIndex, int iEndIndex, string strWhere, string orderName, int orderType)
{
string sAscOrDesc = string.Empty;
if (orderType == 0)
{
sAscOrDesc = "asc";
}
else
{
sAscOrDesc = "desc";
}
SqlCommand SqlCmd = new SqlCommand("lpsp_ExecSqlByPager");
SqlCmd.CommandType = CommandType.StoredProcedure;
DbHelperSQL.AddSqlParameter(SqlCmd, "@OrderByField", SqlDbType.NVarChar, orderName);
DbHelperSQL.AddSqlParameter(SqlCmd, "@AscOrDesc", SqlDbType.VarChar, sAscOrDesc);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Fields", SqlDbType.NVarChar, "*");
DbHelperSQL.AddSqlParameter(SqlCmd, "@DbTable", SqlDbType.NVarChar, sDbTable);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Where", SqlDbType.NVarChar, strWhere);
DbHelperSQL.AddSqlParameter(SqlCmd, "@StartIndex", SqlDbType.Int, iStartIndex);
DbHelperSQL.AddSqlParameter(SqlCmd, "@EndIndex", SqlDbType.Int, iEndIndex);
return(DbHelperSQL.ExecuteDataTable(SqlCmd));
}
/// <summary>
/// get related contacts
/// neo 2011-03-15
/// </summary>
/// <param name="ContactId"></param>
/// <param name="iStartIndex"></param>
/// <param name="iEndIndex"></param>
/// <returns></returns>
public DataTable GetRelatedContacts(int ContactId, int iStartIndex, int iEndIndex)
{
string sSql = "select 'To' as Direction, a.ToContactId as RelContactID, a.RelTypeId, c.RelToName as Relationship, b.LastName +', '+ b.FirstName + case when b.MiddleName is null then '' when b.MiddleName='' then '' else ' '+ b.MiddleName end as ContactName "
+ "from Contact_Relationship as a "
+ "inner join Contacts as b on a.ToContactId = b.ContactId "
+ "inner join RelationshipRoles as c on a.RelTypeId = c.RelTypeId "
+ "where a.FromContactId = " + ContactId + " "
+ "union "
+ "select 'From' as Direction, a.FromContactId as RelContactID, a.RelTypeId, c.RelFromName as Relationship, b.LastName +', '+ b.FirstName + case when b.MiddleName is null then '' when b.MiddleName='' then '' else ' '+ b.MiddleName end as ContactName "
+ "from Contact_Relationship as a "
+ "inner join Contacts as b on a.FromContactId = b.ContactId "
+ "inner join RelationshipRoles as c on a.RelTypeId = c.RelTypeId "
+ "where a.ToContactId = " + ContactId;
SqlCommand SqlCmd = new SqlCommand("lpsp_ExecSqlByPager");
SqlCmd.CommandType = CommandType.StoredProcedure;
DbHelperSQL.AddSqlParameter(SqlCmd, "@OrderByField", SqlDbType.NVarChar, "Direction, RelContactID");
DbHelperSQL.AddSqlParameter(SqlCmd, "@AscOrDesc", SqlDbType.NVarChar, "asc");
DbHelperSQL.AddSqlParameter(SqlCmd, "@Fields", SqlDbType.NVarChar, "*");
DbHelperSQL.AddSqlParameter(SqlCmd, "@DbTable", SqlDbType.NVarChar, "(" + sSql + ") as t");
DbHelperSQL.AddSqlParameter(SqlCmd, "@Where", SqlDbType.NVarChar, "");
DbHelperSQL.AddSqlParameter(SqlCmd, "@StartIndex", SqlDbType.Int, iStartIndex);
DbHelperSQL.AddSqlParameter(SqlCmd, "@EndIndex", SqlDbType.Int, iEndIndex);
return(DbHelperSQL.ExecuteDataTable(SqlCmd));
}
/// <summary>
/// update Contacts.UpdatePoint= 0 or 1
/// neo 2011-03-13
/// </summary>
/// <param name="iContactID"></param>
/// <param name="bUpdatePoint"></param>
public void UpdatePointBase(int iContactID, bool bUpdatePoint)
{
string sSql = "update Contacts set UpdatePoint=@UpdatePoint where ContactID=" + iContactID;
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@UpdatePoint", SqlDbType.Bit, bUpdatePoint);
DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
/// <summary>
///
/// </summary>
/// <param name="NoteIDs"></param>
/// <param name="bEnabled"></param>
public void EnableExternalViewing(string NoteIDs, bool bEnabled)
{
string sSql = "update LoanNotes set ExternalViewing=@ExternalViewing where NoteId in (" + NoteIDs + ")";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@ExternalViewing", SqlDbType.Bit, bEnabled);
DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
public void DeleteLoanContacts(int FileID, string Contacts)
{
Collection <SqlCommand> SqlCmdList = new Collection <SqlCommand>();
string[] Ids = Contacts.Split(",".ToCharArray());
foreach (string cids in Ids)
{
if (cids.Split("_".ToCharArray()).Length == 2)
{
string ContractID = cids.Split("_".ToCharArray())[0];
int ContractRoleID;
if (int.TryParse(cids.Split("_".ToCharArray())[1], out ContractRoleID))
{
SqlCommand SqlCmd = new SqlCommand("lpsp_RemoveLoanContacts");
SqlCmd.CommandType = CommandType.StoredProcedure;
DbHelperSQL.AddSqlParameter(SqlCmd, "@FileId", SqlDbType.Int, FileID);
DbHelperSQL.AddSqlParameter(SqlCmd, "@ContactId", SqlDbType.NVarChar, ContractID);
DbHelperSQL.AddSqlParameter(SqlCmd, "@ContactRoleId", SqlDbType.Int, ContractRoleID);
SqlCmdList.Add(SqlCmd);
}
}
}
#region 批量执行SQL
if (SqlCmdList.Count < 1)
{
return;
}
SqlConnection SqlConn = null;
SqlTransaction SqlTrans = null;
try
{
SqlConn = DbHelperSQL.GetOpenConnection();
SqlTrans = SqlConn.BeginTransaction();
foreach (SqlCommand xSqlCmd in SqlCmdList)
{
DbHelperSQL.ExecuteNonQuery(xSqlCmd, SqlTrans);
}
SqlTrans.Commit();
}
catch (Exception ex)
{
SqlTrans.Rollback();
throw ex;
}
finally
{
if (SqlConn != null)
{
SqlConn.Close();
}
}
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="iContactId"></param>
/// <param name="sFirstName"></param>
/// <param name="sLastName"></param>
/// <param name="sEmail"></param>
/// <param name="sCellPhone"></param>
/// <param name="sHomePhone"></param>
/// <param name="sWorkPhone"></param>
/// <param name="sDOB"></param>
/// <param name="sSSN"></param>
/// <param name="sFICO"></param>
/// <param name="sMailingStreetAddress1"></param>
/// <param name="sMailingStreetAddress2"></param>
/// <param name="sMailingCity"></param>
/// <param name="sMailingState"></param>
/// <param name="sMailingZip"></param>
/// <param name="sLeadSource"></param>
/// <param name="sReferralID"></param>
public void UpdateCoBorrower(int iContactId, string sFirstName, string sLastName, string sEmail, string sCellPhone, string sHomePhone,
string sWorkPhone, string sDOB, string sSSN, string sFICO,
string sMailingStreetAddress1, string sMailingStreetAddress2, string sMailingCity, string sMailingState, string sMailingZip,
string sLeadSource, string sReferralID)
{
string sSql = "update Contacts set FirstName=@FirstName, LastName=@LastName, Email=@Email, CellPhone=@CellPhone, HomePhone=@HomePhone, "
+ "BusinessPhone=@BusinessPhone, DOB=@DOB, SSN=@SSN, Experian=@Experian, "
+ "MailingAddr=@MailingAddr, MailingCity=@MailingCity, MailingState=@MailingState, MailingZip=@MailingZip where ContactId=" + iContactId + ";"
+ "update Prospect set LeadSource=@LeadSource, Referral=@Referral where ContactId=" + iContactId;
SqlCommand SqlCmd = new SqlCommand(sSql);
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@FirstName", SqlDbType.NVarChar, sFirstName);
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@LastName", SqlDbType.NVarChar, sLastName);
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@Email", SqlDbType.NVarChar, sEmail);
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@CellPhone", SqlDbType.NVarChar, sCellPhone);
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@HomePhone", SqlDbType.NVarChar, sHomePhone);
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@BusinessPhone", SqlDbType.NVarChar, sWorkPhone);
if (sDOB == string.Empty)
{
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@DOB", SqlDbType.DateTime, DBNull.Value);
}
else
{
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@DOB", SqlDbType.DateTime, Convert.ToDateTime(sDOB));
}
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@SSN", SqlDbType.NVarChar, sSSN);
if (sFICO == string.Empty)
{
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@Experian", SqlDbType.SmallInt, DBNull.Value);
}
else
{
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@Experian", SqlDbType.SmallInt, Convert.ToInt16(sFICO));
}
string sMailingAddr = (sMailingStreetAddress1 + " " + sMailingStreetAddress2).Trim();
DbHelperSQL.AddSqlParameter(SqlCmd, "@MailingAddr", SqlDbType.NVarChar, sMailingAddr);
DbHelperSQL.AddSqlParameter(SqlCmd, "@MailingCity", SqlDbType.NVarChar, sMailingCity);
DbHelperSQL.AddSqlParameter(SqlCmd, "@MailingState", SqlDbType.NVarChar, sMailingState);
DbHelperSQL.AddSqlParameter(SqlCmd, "@MailingZip", SqlDbType.NVarChar, sMailingZip);
#region prospect
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@LeadSource", SqlDbType.NVarChar, sLeadSource);
if (sReferralID == string.Empty)
{
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@Referral", SqlDbType.Int, DBNull.Value);
}
else
{
LPWeb.DAL.DbHelperSQL.AddSqlParameter(SqlCmd, "@Referral", SqlDbType.Int, Convert.ToInt32(sReferralID));
}
#endregion
LPWeb.DAL.DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
/// <summary>
///
/// </summary>
/// <param name="iFileId"></param>
/// <param name="sLockOption"></param>
public void UpdateLockOption(int iFileId, string sLockOption)
{
string sSql = "update LoanLocks set LockOption=@LockOption where FileId=" + iFileId;
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@LockOption", SqlDbType.NVarChar, sLockOption);
DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
/// <summary>
/// enable/disable rule in loan
/// neo 2011-01-15
/// </summary>
/// <param name="sLoanRuleIDs"></param>
/// <param name="bEnabled"></param>
public void EnableBase(string sLoanRuleIDs, bool bEnabled)
{
string sSql = "update LoanRules set Enabled=@Enabled where LoanRuleId in (" + sLoanRuleIDs + ")";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Enabled", SqlDbType.Bit, bEnabled);
DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
private void SaveImport(SqlConnection conn, SqlTransaction tran)
{
SqlCommand cmd = new SqlCommand(@"insert into ZillowImports (ImportDate, ReturnStatus, ZillowXml, ImportTransId) values (@a, @b, @c, @d)", conn);
DbHelperSQL.AddSqlParameter(cmd, "@a", SqlDbType.DateTime, DateTime.Now);
DbHelperSQL.AddSqlParameter(cmd, "@b", SqlDbType.NVarChar, Helpers.FormatSqlStringValue(ReturnStatus, 50));
DbHelperSQL.AddSqlParameter(cmd, "@c", SqlDbType.NText, ZillowXml);
DbHelperSQL.AddSqlParameter(cmd, "@d", SqlDbType.UniqueIdentifier, ImportTransId);
DbHelperSQL.ExecuteNonQuery(cmd, tran);
}
public void UpdateWorkflowType(int iWflTemplId, string sWorkflowType)
{
string sSql7 = "UPDATE Template_Workflow SET WorkflowType = @WorkflowType WHERE WflTemplId=@WflTemplId";
SqlCommand SqlCmd7 = new SqlCommand(sSql7);
DbHelperSQL.AddSqlParameter(SqlCmd7, "@WorkflowType", SqlDbType.NVarChar, sWorkflowType);
DbHelperSQL.AddSqlParameter(SqlCmd7, "@WflTemplId", SqlDbType.Int, iWflTemplId);
DbHelperSQL.ExecuteNonQuery(SqlCmd7);
}
/// <summary>
/// get default workflow template count
/// neo 2011-02-08
/// </summary>
/// <param name="sWorkflowType"></param>
/// <returns></returns>
public int GetDefaultWflTemplateCountBase(string sWorkflowType)
{
string sSql = "select count(1) from Template_Workflow where WorkflowType=@WorkflowType and [Default]=1";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@WorkflowType", SqlDbType.NVarChar, sWorkflowType);
int iCount = Convert.ToInt32(DbHelperSQL.ExecuteScalar(SqlCmd));
return(iCount);
}
/// <summary>
/// add rule group to loan
/// neo 2011-01-15
/// </summary>
/// <param name="RuleGroupIDs"></param>
/// <param name="iLoanID"></param>
/// <param name="iAppliedByID"></param>
public void AddRuleGroupToLoanBase(string[] RuleGroupIDs, int iLoanID, int iAppliedByID)
{
SqlCommand[] SqlCmds = new SqlCommand[RuleGroupIDs.Length];
string sSql = "insert into LoanRules (Fileid, RuleGroupId, RuleId, Applied, AppliedBy, Enabled) values (@Fileid, @RuleGroupId, null, getdate(), @AppliedBy, 1)";
int i = 0;
foreach (string sRuleGroupID in RuleGroupIDs)
{
int iRuleGroupID = Convert.ToInt32(sRuleGroupID);
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Fileid", SqlDbType.Int, iLoanID);
DbHelperSQL.AddSqlParameter(SqlCmd, "@RuleGroupId", SqlDbType.Int, iRuleGroupID);
DbHelperSQL.AddSqlParameter(SqlCmd, "@AppliedBy", SqlDbType.Int, iAppliedByID);
SqlCmds.SetValue(SqlCmd, i);
i++;
}
#region 批量执行SQL语句
SqlConnection SqlConn = null;
SqlTransaction SqlTrans = null;
try
{
SqlConn = DbHelperSQL.GetOpenConnection();
SqlTrans = SqlConn.BeginTransaction();
foreach (SqlCommand SqlCmd1 in SqlCmds)
{
DbHelperSQL.ExecuteNonQuery(SqlCmd1, SqlTrans);
}
SqlTrans.Commit();
}
catch (Exception ex)
{
SqlTrans.Rollback();
throw ex;
}
finally
{
if (SqlConn != null)
{
SqlConn.Close();
}
}
#endregion
}
/// <summary>
/// Create Division
/// </summary>
/// <param name="sGroupName"></param>
/// <returns></returns>
public int CreateBranch(string sBranchName)
{
string sSql1 = "insert into Branches (Name, Enabled) values (@BranchName, 1);select SCOPE_IDENTITY();";
SqlCommand SqlCmd1 = new SqlCommand(sSql1);
DbHelperSQL.AddSqlParameter(SqlCmd1, "@BranchName", SqlDbType.NVarChar, sBranchName);
object oNewID = DbHelperSQL.ExecuteScalar(SqlCmd1);
int iNewID = Convert.ToInt32(oNewID);
return(iNewID);
}
/// <summary>
/// delete all user related info
/// </summary>
/// <param name="UserId"></param>
/// <returns></returns>
public bool DeleteEmailTemplateInfo(int nId)
{
Collection <SqlCommand> SqlCmdList = new Collection <SqlCommand>();
// delete email template referenced info from table Template_Email_Recipients, EmailQue, Template_Wfl_Tasks, LoanTasks
SqlCommand cmdReferenced = new SqlCommand(@"DELETE Template_Email_Recipients WHERE TemplEmailId=@TemplEmailId;
DELETE Template_Wfl_CompletionEmails WHERE TemplEmailId=@TemplEmailId;
UPDATE EmailQue SET EmailTmplId=NULL WHERE EmailTmplId=@TemplEmailId;
UPDATE Template_Wfl_Tasks SET WarningEmailId=NULL WHERE WarningEmailId=@TemplEmailId;
UPDATE Template_Wfl_Tasks SET OverdueEmailId=NULL WHERE OverdueEmailId=@TemplEmailId;
UPDATE Template_Wfl_Tasks SET CompletionEmailId=NULL WHERE CompletionEmailId=@TemplEmailId;
UPDATE LoanTasks SET WarningEmailId=NULL WHERE WarningEmailId=@TemplEmailId;
UPDATE LoanTasks SET OverdueEmailId=NULL WHERE OverdueEmailId=@TemplEmailId;
UPDATE LoanTasks SET CompletionEmailId=NULL WHERE CompletionEmailId=@TemplEmailId;");
DbHelperSQL.AddSqlParameter(cmdReferenced, "@TemplEmailId", SqlDbType.Int, nId);
SqlCmdList.Add(cmdReferenced);
// delete email template table
SqlCommand cmdDeleteRecord = new SqlCommand("DELETE FROM Template_Email WHERE TemplEmailId=@TemplEmailId");
DbHelperSQL.AddSqlParameter(cmdDeleteRecord, "@TemplEmailId", SqlDbType.Int, nId);
SqlCmdList.Add(cmdDeleteRecord);
SqlConnection SqlConn = null;
SqlTransaction SqlTrans = null;
try
{
SqlConn = DbHelperSQL.GetOpenConnection();
SqlTrans = SqlConn.BeginTransaction();
foreach (SqlCommand xSqlCmd in SqlCmdList)
{
DbHelperSQL.ExecuteNonQuery(xSqlCmd, SqlTrans);
}
SqlTrans.Commit();
return(true);
}
catch (Exception ex)
{
SqlTrans.Rollback();
throw ex;
}
finally
{
if (SqlConn != null)
{
SqlConn.Close();
}
}
}
/// <summary>
/// delete workflow template
/// neo 2011-02-11
/// </summary>
/// <param name="iWorkflowTemplateID"></param>
public void DeleteWorkflowTemplateBase(int iWorkflowTemplateID)
{
string sSql = "update LoanTasks set WflTemplId=null, TemplTaskId=null where WflTemplId=@WflTemplId "
+ "update LoanStages set WflTemplId=null,WflStageId=null where WflTemplId=@WflTemplId "
+ "delete from LoanWflTempl where WflTemplId=@WflTemplId "
+ "delete from Template_Wfl_CompletionEmails where TemplTaskId in (select TemplTaskId from Template_Wfl_Tasks tt inner join Template_Wfl_Stages ts on tt.WflStageId=ts.WflStageId where ts.WflTemplId=@WflTemplId) "
+ "delete from Template_Wfl_Tasks where WflStageId in (select WflStageId from Template_Wfl_Stages where WflTemplId=@WflTemplId) "
+ "delete from Template_Wfl_Stages where WflTemplId=@WflTemplId "
+ "delete from Template_Workflow where WflTemplId=@WflTemplId";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@WflTemplId", SqlDbType.Int, iWorkflowTemplateID);
DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
//public Model.LoanPointFields GetModel(int iFileId, int iPointFieldId)
//{
// Model.LoanPointFields pointField = null;
// string sSql = "select * from LoanPointFields where FileId=" + iFileId + " and PointFieldId=" + iPointFieldId;
// DataTable dt = DbHelperSQL.ExecuteDataTable(sSql);
// if (dt == null || dt.Rows.Count <= 0)
// return pointField;
// DataRow dr = dt.Rows[0];
// if (dr == null)
// return pointField;
// pointField = new Model.LoanPointFields() {
// FileId = dr["FileId"] == DBNull.Value ? 0 : (int)dr["FileId"],
// PointFieldId = dr["PointFieldId"] == DBNull.Value ? 0 : (int)dr["PointFieldId"],
// CurrentValue = dr["CurrentValue"] == DBNull.Value ? string.Empty : dr["CurrentValue"].ToString(),
// PrevValue = dr["PrevValue"] == DBNull.Value ? string.Empty : dr["PrevValue"].ToString(),
// };
// if (pointField.FileId == 0 || pointField.PointFieldId == 0)
// return null;
// return pointField;
//}
/// <summary>
///
/// </summary>
/// <param name="iFileId"></param>
/// <param name="iPointFieldId"></param>
/// <param name="sCurrentValue"></param>
public void UpdatePointFieldValue(int iFileId, int iPointFieldId, string sCurrentValue)
{
string sSql = "update LoanPointFields set CurrentValue=@CurrentValue where FileId=" + iFileId + " and PointFieldId=" + iPointFieldId;
SqlCommand SqlCmd = new SqlCommand(sSql);
if (sCurrentValue == null)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@CurrentValue", SqlDbType.NVarChar, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@CurrentValue", SqlDbType.NVarChar, sCurrentValue);
}
DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
/// <summary>
/// 检查Division Name是否存在
/// </summary>
/// <param name="sGroupName"></param>
/// <returns></returns>
public bool IsExist_CreateBase(string sBranchName)
{
string sSql = "select count(1) from Branches where upper(Name)=@sBranchName";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@sBranchName", SqlDbType.NVarChar, sBranchName.ToUpper());
int iCount = Convert.ToInt32(DbHelperSQL.ExecuteScalar(SqlCmd));
if (iCount == 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 检查某个loan下任务名称是否重复
/// neo 2010-11-18
/// </summary>
/// <param name="iLoanID"></param>
/// <param name="iTaskID"></param>
/// <param name="sTaskName"></param>
/// <returns></returns>
public bool IsLoanTaskExists_UpdateBase(int iLoanID, int iTaskID, string sTaskName)
{
string sSql = "select count(1) from LoanTasks where FileID=" + iLoanID + " and LoanTaskId != " + iTaskID + " and Name=@Name";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Name", SqlDbType.NVarChar, sTaskName);
int iCount = Convert.ToInt32(DbHelperSQL.ExecuteScalar(SqlCmd));
if (iCount > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// insert contact company
/// neo 2011-04-08
/// </summary>
/// <param name="sCompanyName"></param>
/// <param name="iServiceTypeID"></param>
/// <param name="sServiceType"></param>
/// <param name="sAddress"></param>
/// <param name="sCity"></param>
/// <param name="sState"></param>
/// <param name="sZip"></param>
/// <param name="strUrl"></param>
public void InsertContactCompanyBase(string sCompanyName, int iServiceTypeID, string sServiceType, string sAddress, string sCity, string sState, string sZip, string strUrl)
{
string sSql = "INSERT INTO ContactCompanies (Name,ServiceTypes,Address,City,State,Zip,Website,ServiceTypeId,Enabled) VALUES (@Name,@ServiceTypes,@Address,@City,@State,@Zip,@Website,@ServiceTypeId,@Enabled)";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Name", SqlDbType.NVarChar, sCompanyName);
DbHelperSQL.AddSqlParameter(SqlCmd, "@ServiceTypes", SqlDbType.NVarChar, sServiceType);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Address", SqlDbType.NVarChar, sAddress);
DbHelperSQL.AddSqlParameter(SqlCmd, "@City", SqlDbType.NVarChar, sCity);
DbHelperSQL.AddSqlParameter(SqlCmd, "@State", SqlDbType.NVarChar, sState);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Zip", SqlDbType.NVarChar, sZip);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Website", SqlDbType.NVarChar, strUrl);
DbHelperSQL.AddSqlParameter(SqlCmd, "@ServiceTypeId", SqlDbType.Int, iServiceTypeID);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Enabled", SqlDbType.Bit, true);
DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
/// <summary>
/// 检查branch name是否存在
/// Alex 2011-04-14
/// </summary>
/// <param name="iRuleID"></param>
/// <param name="sRuleName"></param>
/// <returns></returns>
public bool IsExist_EditBase(int iBranchID, string sBranchName)
{
string sSql = "select count(1) from ContactBranches where Name = @Name and ContactBranchId != " + iBranchID;
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Name", SqlDbType.NVarChar, sBranchName);
int iCount = Convert.ToInt32(DbHelperSQL.ExecuteScalar(SqlCmd));
if (iCount > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 检查email template是否存在
/// neo 2010-12-07
/// </summary>
/// <param name="sEmailTemplateName"></param>
/// <returns></returns>
public bool IsExist_CreateBase(string sEmailTemplateName)
{
string sSql = "select count(1) from Template_Email where Name = @Name";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Name", SqlDbType.NVarChar, sEmailTemplateName);
int iCount = Convert.ToInt32(DbHelperSQL.ExecuteScalar(SqlCmd));
if (iCount > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 检查workflow template是否存在
/// neo 2011-02-08
/// </summary>
/// <param name="iRuleID"></param>
/// <param name="sRuleName"></param>
/// <returns></returns>
public bool IsExist_EditBase(int iWorkflowTemplateID, string sWorkflowTemplateNameName)
{
string sSql = "select count(1) from Template_Workflow where Name = @Name and WflTemplId != " + iWorkflowTemplateID;
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Name", SqlDbType.NVarChar, sWorkflowTemplateNameName);
int iCount = Convert.ToInt32(DbHelperSQL.ExecuteScalar(SqlCmd));
if (iCount > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="iFileId"></param>
/// <param name="sCompensationPlan"></param>
/// <param name="sLenderCredit"></param>
public void UpdateLoanProfit(int iFileId, string sCompensationPlan, string sLenderCredit)
{
string sSql = "update dbo.LoanProfit set CompensationPlan=@CompensationPlan, LenderCredit=@LenderCredit where FileId=" + iFileId;
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@CompensationPlan", SqlDbType.NVarChar, sCompensationPlan);
if (sLenderCredit == string.Empty)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@LenderCredit", SqlDbType.Decimal, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@LenderCredit", SqlDbType.Decimal, Convert.ToDecimal(sLenderCredit));
}
DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
/// <summary>
/// update contact company info
/// neo 2011-04-08
/// </summary>
/// <param name="iContactCompanyID"></param>
/// <param name="sCompanyName"></param>
/// <param name="iServiceTypeID"></param>
/// <param name="sServiceType"></param>
/// <param name="sAddress"></param>
/// <param name="sCity"></param>
/// <param name="sState"></param>
/// <param name="sZip"></param>
/// <param name="bEnabled"></param>
public void UpdateContactCompanyBase(int iContactCompanyID, string sCompanyName, int iServiceTypeID, string sServiceType, string sAddress, string sCity, string sState, string sZip, bool bEnabled, string strWebSite)
{
string sSql = "UPDATE ContactCompanies SET Name = @Name,Address = @Address,City = @City,State = @State,Zip = @Zip,Website = @Website,ServiceTypeId = @ServiceTypeId,Enabled = @Enabled,ServiceTypes = @ServiceTypes WHERE ContactCompanyId=@ContactCompanyId";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Name", SqlDbType.NVarChar, sCompanyName);
DbHelperSQL.AddSqlParameter(SqlCmd, "@ServiceTypes", SqlDbType.NVarChar, sServiceType);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Address", SqlDbType.NVarChar, sAddress);
DbHelperSQL.AddSqlParameter(SqlCmd, "@City", SqlDbType.NVarChar, sCity);
DbHelperSQL.AddSqlParameter(SqlCmd, "@State", SqlDbType.NVarChar, sState);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Zip", SqlDbType.NVarChar, sZip);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Website", SqlDbType.NVarChar, strWebSite);
DbHelperSQL.AddSqlParameter(SqlCmd, "@ServiceTypeId", SqlDbType.Int, iServiceTypeID);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Enabled", SqlDbType.Bit, bEnabled);
DbHelperSQL.AddSqlParameter(SqlCmd, "@ContactCompanyId", SqlDbType.Int, iContactCompanyID);
DbHelperSQL.ExecuteNonQuery(SqlCmd);
}
/// <summary>
/// insert task loan
/// neo 2010-11-17
/// </summary>
/// <param name="iLoanID"></param>
/// <param name="sTaskName"></param>
/// <param name="sDueDate"></param>
/// <param name="iOwerID"></param>
/// <param name="iLoanStageID"></param>
/// <param name="iPreTaskID"></param>
/// <param name="iSeq"></param>
/// <param name="iDaysToEstClose"></param>
/// <param name="iDaysAfterPre"></param>
/// <param name="iWarningEmailID"></param>
/// <param name="iOverdueEmailID"></param>
/// <param name="iCompletionEmailID"></param>
public void InsertLoanTaskBase(int iLoanID, string sTaskName, string sDueDate, int iOwerID, int iLoanStageID, int iPreTaskID, int iSeq, int iDaysToEstClose, int iDaysAfterPre, int iWarningEmailID, int iOverdueEmailID, int iCompletionEmailID)
{
#region build sql - inser
string sSql = "insert into LoanTasks (FileId,Name,Due,Completed,CompletedBy,LastModified,Created,Owner,LoanStageId,PrerequisiteTaskId,SequenceNumber,DaysDueFromEstClose,TemplTaskId,WflTemplId,DaysDueAfterPrerequisite,WarningEmailId,OverdueEmailId,CompletionEmailId) values (@FileId,@Name,@Due,@Completed,@CompletedBy,getdate(),getdate(),@Owner,@LoanStageId,@PrerequisiteTaskId,@SequenceNumber,@DaysDueFromEstClose,@TemplTaskId,@WflTemplId,@DaysDueAfterPrerequisite,@WarningEmailId,@OverdueEmailId,@CompletionEmailId)"
+ ";select SCOPE_IDENTITY();";
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@FileId", SqlDbType.Int, iLoanID);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Name", SqlDbType.NVarChar, sTaskName);
if (sDueDate == string.Empty)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@Due", SqlDbType.DateTime, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@Due", SqlDbType.DateTime, DateTime.Parse(sDueDate));
}
DbHelperSQL.AddSqlParameter(SqlCmd, "@Completed", SqlDbType.DateTime, DBNull.Value);
DbHelperSQL.AddSqlParameter(SqlCmd, "@CompletedBy", SqlDbType.Int, DBNull.Value);
//DbHelperSQL.AddSqlParameter(SqlCmd, "@LastModified", SqlDbType.DateTime, iLoginUser);
//DbHelperSQL.AddSqlParameter(SqlCmd, "@Created", SqlDbType.DateTime, Created);
if (iOwerID == 0)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@Owner", SqlDbType.Int, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@Owner", SqlDbType.Int, iOwerID);
}
DbHelperSQL.AddSqlParameter(SqlCmd, "@LoanStageId", SqlDbType.Int, iLoanStageID);
if (iPreTaskID == 0)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@PrerequisiteTaskId", SqlDbType.Int, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@PrerequisiteTaskId", SqlDbType.Int, iPreTaskID);
}
DbHelperSQL.AddSqlParameter(SqlCmd, "@SequenceNumber", SqlDbType.SmallInt, iSeq);
if (iDaysToEstClose == 0)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@DaysDueFromEstClose", SqlDbType.SmallInt, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@DaysDueFromEstClose", SqlDbType.SmallInt, iDaysToEstClose);
}
DbHelperSQL.AddSqlParameter(SqlCmd, "@TemplTaskId", SqlDbType.Int, DBNull.Value);
DbHelperSQL.AddSqlParameter(SqlCmd, "@WflTemplId", SqlDbType.Int, DBNull.Value);
if (iDaysAfterPre == 0)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@DaysDueAfterPrerequisite", SqlDbType.SmallInt, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@DaysDueAfterPrerequisite", SqlDbType.SmallInt, iDaysAfterPre);
}
if (iWarningEmailID == 0)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@WarningEmailId", SqlDbType.Int, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@WarningEmailId", SqlDbType.Int, iWarningEmailID);
}
if (iOverdueEmailID == 0)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@OverdueEmailId", SqlDbType.Int, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@OverdueEmailId", SqlDbType.Int, iOverdueEmailID);
}
if (iCompletionEmailID == 0)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@CompletionEmailId", SqlDbType.Int, DBNull.Value);
}
else
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@CompletionEmailId", SqlDbType.Int, iCompletionEmailID);
}
#endregion
#region build sql - CreateTaskAlert
SqlCommand SqlCmd2 = new SqlCommand("lpsp_CreateTaskAlerts");
SqlCmd2.CommandType = CommandType.StoredProcedure;
#endregion
#region build sql - update loan stage
string sSql3 = "UPDATE LoanStages SET Completed=NULL WHERE LoanStageId=@LoanStageId";
SqlCommand SqlCmd3 = new SqlCommand(sSql3);
DbHelperSQL.AddSqlParameter(SqlCmd3, "@LoanStageId", SqlDbType.Int, iLoanStageID);
#endregion
#region build sql - update loan
string sSql4 = "UPDATE Loans SET CurrentStage=dbo.lpfn_GetCurrentStage(@FileId) WHERE FileId=@FileId";
SqlCommand SqlCmd4 = new SqlCommand(sSql4);
DbHelperSQL.AddSqlParameter(SqlCmd4, "@FileId", SqlDbType.Int, iLoanID);
#endregion
#region 批量执行SQL语句
SqlConnection SqlConn = null;
SqlTransaction SqlTrans = null;
try
{
SqlConn = DbHelperSQL.GetOpenConnection();
SqlTrans = SqlConn.BeginTransaction();
int iNewTaskID = Convert.ToInt32(DbHelperSQL.ExecuteScalar(SqlCmd, SqlTrans));
DbHelperSQL.AddSqlParameter(SqlCmd2, "@LoanTaskId", iNewTaskID);
DbHelperSQL.ExecuteNonQuery(SqlCmd2, SqlTrans);
DbHelperSQL.ExecuteNonQuery(SqlCmd3, SqlTrans);
DbHelperSQL.ExecuteNonQuery(SqlCmd4, SqlTrans);
SqlTrans.Commit();
}
catch (Exception ex)
{
SqlTrans.Rollback();
throw ex;
}
finally
{
if (SqlConn != null)
{
SqlConn.Close();
}
}
#endregion
}
/// <summary>
/// 保存Branch和Members信息
/// </summary>
/// <param name="iGroupID"></param>
/// <param name="bEnabled"></param>
/// <param name="sGroupDesc"></param>
/// <param name="sOldGroupMemberIDs"></param>
/// <param name="sGroupMemberIDs"></param>
public void SaveBranchAndMembersBase(LPWeb.Model.Branches model, string sFolderIDs, string sManagers)
{
Collection <SqlCommand> SqlCmdList = new Collection <SqlCommand>();
#region SQL for 更新Branches信息
string sSql = string.Empty;
if (model.WebsiteLogo != null)
{
sSql = " UPDATE [Branches] SET [Name] = @Name,[Desc] = @Desc,[WebsiteLogo] = @WebsiteLogo,[Enabled] = @Enabled, [GroupID] = @GroupID, [BranchAddress] = @BranchAddress,[City] = @City,[BranchState] = @BranchState,[Zip] = @Zip,License1=@License1,License2=@License2,License3=@License3,License4=@License4,License5=@License5,Disclaimer=@Disclaimer,Phone=@Phone,Fax=@Fax,Email=@Email,WebURL=@WebURL,HomeBranch=@HomeBranch WHERE BranchId=@BranchId ";
}
else
{
sSql = " UPDATE [Branches] SET [Name] = @Name,[Desc] = @Desc,[Enabled] = @Enabled , [GroupID] = @GroupID, [BranchAddress] = @BranchAddress,[City] = @City,[BranchState] = @BranchState,[Zip] = @Zip,License1=@License1,License2=@License2,License3=@License3,License4=@License4,License5=@License5,Disclaimer=@Disclaimer,Phone=@Phone,Fax=@Fax,Email=@Email,WebURL=@WebURL,HomeBranch=@HomeBranch WHERE BranchId=@BranchId ";
}
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Name", SqlDbType.NVarChar, model.Name);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Desc", SqlDbType.NVarChar, model.Desc);
if (model.WebsiteLogo != null)
{
DbHelperSQL.AddSqlParameter(SqlCmd, "@WebsiteLogo", SqlDbType.VarBinary, model.WebsiteLogo);
}
DbHelperSQL.AddSqlParameter(SqlCmd, "@Enabled", SqlDbType.Bit, model.Enabled);
DbHelperSQL.AddSqlParameter(SqlCmd, "@GroupID", SqlDbType.Int, model.GroupID);
DbHelperSQL.AddSqlParameter(SqlCmd, "@BranchAddress", SqlDbType.NVarChar, model.BranchAddress);
DbHelperSQL.AddSqlParameter(SqlCmd, "@City", SqlDbType.NVarChar, model.City);
DbHelperSQL.AddSqlParameter(SqlCmd, "@BranchState", SqlDbType.NVarChar, model.BranchState);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Zip", SqlDbType.NVarChar, model.Zip);
DbHelperSQL.AddSqlParameter(SqlCmd, "@BranchId", SqlDbType.Int, model.BranchId);
DbHelperSQL.AddSqlParameter(SqlCmd, "@License1", SqlDbType.NVarChar, model.License1);
DbHelperSQL.AddSqlParameter(SqlCmd, "@License2", SqlDbType.NVarChar, model.License2);
DbHelperSQL.AddSqlParameter(SqlCmd, "@License3", SqlDbType.NVarChar, model.License3);
DbHelperSQL.AddSqlParameter(SqlCmd, "@License4", SqlDbType.NVarChar, model.License4);
DbHelperSQL.AddSqlParameter(SqlCmd, "@License5", SqlDbType.NVarChar, model.License5);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Disclaimer", SqlDbType.NVarChar, model.Disclaimer);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Phone", SqlDbType.NVarChar, model.Phone);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Fax", SqlDbType.NVarChar, model.Fax);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Email", SqlDbType.NVarChar, model.Email);
DbHelperSQL.AddSqlParameter(SqlCmd, "@WebURL", SqlDbType.NVarChar, model.WebURL);
DbHelperSQL.AddSqlParameter(SqlCmd, "@HomeBranch", SqlDbType.Bit, model.HomeBranch);
SqlCmdList.Add(SqlCmd);
#endregion
string sSql5 = " UPDATE [PointFolders] SET [BranchId] = null WHERE [BranchId]=@BranchId ";
SqlCommand SqlCmd5 = new SqlCommand(sSql5);
DbHelperSQL.AddSqlParameter(SqlCmd5, "@BranchId", SqlDbType.Int, model.BranchId);
SqlCmdList.Add(SqlCmd5);
string sSql2 = " UPDATE [PointFolders] SET [BranchId] = null WHERE [BranchId]=@BranchId ";
SqlCommand SqlCmd2 = new SqlCommand(sSql2);
DbHelperSQL.AddSqlParameter(SqlCmd2, "@BranchId", SqlDbType.Int, model.BranchId);
SqlCmdList.Add(SqlCmd2);
if (sFolderIDs != string.Empty)
{
string[] folders = sFolderIDs.Split("|".ToCharArray());
foreach (string folder in folders)
{
string[] folderstatus = folder.Split(",".ToCharArray());
if (folderstatus.Length == 2)
{
string sSql3 = " UPDATE [PointFolders] SET [BranchId] = @BranchId,LoanStatus=@LoanStatus WHERE FolderId = @FolderId";
SqlCommand SqlCmd3 = new SqlCommand(sSql3);
DbHelperSQL.AddSqlParameter(SqlCmd3, "@BranchId", SqlDbType.Int, model.BranchId);
DbHelperSQL.AddSqlParameter(SqlCmd3, "@LoanStatus", SqlDbType.Int, int.Parse(folderstatus[1]));
DbHelperSQL.AddSqlParameter(SqlCmd3, "@FolderId", SqlDbType.Int, int.Parse(folderstatus[0]));
SqlCmdList.Add(SqlCmd3);
}
}
}
#region 更新 group 表
string sSql9 = "EXEC GroupRelationInfoUpdate 'Branch'," + model.BranchId.ToString() + "," + model.GroupID.ToString();
SqlCommand sqlcmd9 = new SqlCommand(sSql9);
SqlCmdList.Add(sqlcmd9);
//LPWeb.Model.Branches model1 = GetModel(model.BranchId);
//string sSql9 = "UPDATE Groups SET RegionID=NULL,OrganizationType=NULL,DivisionID=NULL,BranchID=NULL WHERE BranchID=" + model.BranchId.ToString();
//SqlCommand sqlcmd9 = new SqlCommand(sSql9);
//SqlCmdList.Add(sqlcmd9);
////设置新的group属于当前region Division branch
//string sSql10 = string.Empty;
//if (model.GroupID.HasValue)
//{
// if (model1.RegionID.HasValue && model1.DivisionID.HasValue)
// {
// sSql10 = "UPDATE Groups SET RegionID=" + model1.RegionID.ToString() + ",OrganizationType='Branch',DivisionID=" + model1.DivisionID.ToString() + ",BranchID=" + model.BranchId.ToString() + " WHERE GroupID=" + model.GroupID.ToString();
// }
// else if (model1.RegionID.HasValue && !model1.DivisionID.HasValue)
// {
// sSql10 = "UPDATE Groups SET RegionID=" + model1.RegionID.ToString() + ",OrganizationType='Branch', BranchID=" + model.BranchId.ToString() + " WHERE GroupID=" + model.GroupID.ToString();
// }
// else if (!model1.RegionID.HasValue && model1.DivisionID.HasValue)
// {
// sSql10 = "UPDATE Groups SET OrganizationType='Branch',DivisionID=" + model1.DivisionID.ToString() + ",BranchID=" + model.BranchId.ToString() + " WHERE GroupID=" + model.GroupID.ToString();
// }
// else
// {
// sSql10 = "UPDATE Groups SET OrganizationType='Branch' ,BranchID=" + model.BranchId.ToString() + " WHERE GroupID=" + model.GroupID.ToString();
// }
// SqlCommand sqlcmd10 = new SqlCommand(sSql10);
// SqlCmdList.Add(sqlcmd10);
//}
#endregion
string sSql4 = "DELETE BranchManagers WHERE [BranchId] = @BranchId ";
SqlCommand SqlCmd4 = new SqlCommand(sSql4);
DbHelperSQL.AddSqlParameter(SqlCmd4, "@BranchId", SqlDbType.Int, model.BranchId);
SqlCmdList.Add(SqlCmd4);
if (sManagers != string.Empty)
{
string[] managers = sManagers.Split(",".ToCharArray());
foreach (string manager in managers)
{
string sSql5x = "INSERT INTO [BranchManagers]([BranchId],[BranchMgrId])VALUES(@BranchId,@BranchMgrId )";
SqlCommand SqlCmd5x = new SqlCommand(sSql5x);
DbHelperSQL.AddSqlParameter(SqlCmd5x, "@BranchId", SqlDbType.Int, model.BranchId);
DbHelperSQL.AddSqlParameter(SqlCmd5x, "@BranchMgrId", SqlDbType.Int, int.Parse(manager));
SqlCmdList.Add(SqlCmd5x);
}
}
#region 批量执行SQL
SqlConnection SqlConn = null;
SqlTransaction SqlTrans = null;
try
{
SqlConn = DbHelperSQL.GetOpenConnection();
SqlTrans = SqlConn.BeginTransaction();
foreach (SqlCommand xSqlCmd in SqlCmdList)
{
DbHelperSQL.ExecuteNonQuery(xSqlCmd, SqlTrans);
}
SqlTrans.Commit();
}
catch (Exception ex)
{
SqlTrans.Rollback();
throw ex;
}
finally
{
if (SqlConn != null)
{
SqlConn.Close();
}
}
#endregion
}
