// should not use this, will remove later
public void UpdatePhoneStatus(int accountid,
int slot,
string newString,
string oldString
)
{
var noteType = 220 + slot;
using (var conn = new SqlConnection())
{
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@AccountID", accountid),
DataUtilities.CreateNullableSqlParameter("@FieldName", "PhoneStatus" + slot.ToString()),
DataUtilities.CreateNullableSqlParameter("@FieldType", 0), //(int)fieldType
DataUtilities.CreateNullableSqlParameter("@TableNumber", 2), //account phone table = 2
DataUtilities.CreateNullableSqlParameter("@NewString", newString),
DataUtilities.CreateNullableSqlParameter("@OldString", oldString),
DataUtilities.CreateNullableSqlParameter("@UserID", "A"),
DataUtilities.CreateNullableSqlParameter("@PositionID", 3), //Quan positionid
DataUtilities.CreateNullableSqlParameter("@NoteType", noteType.ToString()),
DataUtilities.CreateNullableSqlParameter("@UpdateLastWorkDate", 0),
DataUtilities.CreateNullableSqlParameter("@IsUpdatePhoneStatusOK", 100)
};
int returnValue;
DataUtilities.SqlExecuteStoredProcedureNonQuery(ConnectionString, "spUpdateField", parameters, out returnValue);
if (returnValue != 0)
{
throw new UpdateFieldException(returnValue);
}
}
}
public List <JobQueueEntry> GetDependentJobs(JobQueueEntry job)
{
const string sql = @"select * from eCollect_v1_1.dbo.Jobqueue (nolock)
where dependsOnJobQueueId = @dependsOnJobQueueId ";
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@dependsOnJobQueueId", job.JobQueueId),
};
return(DataUtilities.SqlExecuteReader <JobQueueEntry>(ConnectionString, DataUtilities.CommandTimeout, sql, parameters));
}
public List <FileReceivedEntry> GetFileReceivedEntry(string fileName)
{
const string sql = @"select top 1 * from eCollect_v1_1.dbo.FileReceived (nolock)
where fileName = @fileName ";
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@fileName", fileName)
};
return(DataUtilities.SqlExecuteReader <FileReceivedEntry>(ConnectionString, DataUtilities.CommandTimeout, sql, parameters));
}
public JobQueueEntry GetJobQueueEntry(long jobQueueId)
{
const string sql = @"select * from eCollect_v1_1.dbo.JobQueue (nolock)
where jobQueueId = @jobQueueId";
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@jobQueueId", jobQueueId)
};
return(DataUtilities.SqlExecuteReader <JobQueueEntry>(ConnectionString, DataUtilities.CommandTimeout, sql, parameters).Single());
}
public List <string> GetListOfClientAccountsFromView(string tableView, string fieldName, long fileReceivedId)
{
var sql = @"select " + fieldName + " from "
+ tableView + " (nolock) where fileReceivedId = @fileReceivedId";
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@fileReceivedId", fileReceivedId),
};
return(DataUtilities.SqlExecuteReader <string>(ConnectionString, DataUtilities.CommandTimeout, sql, parameters));
}
public List <int> GetNewAccountId(string clientAccountNumber)
{
var encAccountNumber = Arx.Core.Utilities.AesEncryption.AesEncrypt(clientAccountNumber, false);
const string sql = @"select account from eCollect_v1_1.dbo.Accounts (nolock)
where clientAccount = @clientAccountNumber";
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@clientAccountNumber", encAccountNumber)
};
return(DataUtilities.SqlExecuteReader <int>(ConnectionString, DataUtilities.CommandTimeout, sql, parameters));
}
public List <JobQueueEntry> GetJobQueueBasedOnJobType(int clientFileTypeId, int jobType)
{
const string sql = @"select top 1 * from eCollect_v1_1.dbo.Jobqueue (nolock)
where clientFileTypeID = @clientFileTypeID
and jobType = @jobType
order by 1 desc";
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@clientFileTypeID", clientFileTypeId),
DataUtilities.CreateNullableSqlParameter("@jobType", jobType),
};
return(DataUtilities.SqlExecuteReader <JobQueueEntry>(ConnectionString, DataUtilities.CommandTimeout, sql, parameters));
}
public List <AccountPhonesRecords> GetAccountPhonesRecords(int accountId)
{
using (var conn = new SqlConnection())
{
const string sql = @"select * from eCollect_v1_1.dbo.accountphones where accountId = @accountId";
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@accountId", accountId)
};
return(DataUtilities.SqlExecuteReader <AccountPhonesRecords>(ConnectionString, DataUtilities.CommandTimeout, sql, parameters));
}
}
public List <FileSentRecord> GetFileList(int clientFileTypeId)
{
using (var conn = new SqlConnection())
{
const string sql = @"Select top 1 * from [eCollect_v1_1].[dbo].[fileSent] where clientFileTypeId = @clientFileTypeId order by 1 desc";
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@clientFileTypeId", clientFileTypeId)
};
return(DataUtilities.SqlExecuteReader <FileSentRecord>(ConnectionString, DataUtilities.CommandTimeout, sql, parameters));
}
}
public List <CitiPhoneTrackerRecords> GetCitiPhoneTrackers(int accountId)
{
using (var conn = new SqlConnection())
{
const string sql = @"Select * from eCollectApps.citi.PhoneTracker where accountId = @accountId";
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@accountId", accountId)
};
return(DataUtilities.SqlExecuteReader <CitiPhoneTrackerRecords>(
ConnectionString,
DataUtilities.CommandTimeout,
sql,
parameters));
}
}
public List <CitiExtrasOverflow> GetCitiExtrasOverflow(int accountId)
{
using (var conn = new SqlConnection())
{
const string sql = @"select e.AccountId, e.FieldCodeMappingId, f.Code as Field, e.Value
from eCollectApps.Citi.ExtrasOverflow e
join FieldCodesMapping f on e.FieldCodeMappingId = f.FieldCodesMappingId
where accountId = @accountId" ;
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@accountId", accountId)
};
return(DataUtilities.SqlExecuteReader <CitiExtrasOverflow>(
ConnectionString,
DataUtilities.CommandTimeout,
sql,
parameters));
}
}
private void UpdateField(
int accountId,
string fieldName,
PropertyMapping.UpdateFieldDataType fieldType,
PropertyMapping.UpdateFieldTable tableNumber,
string newString,
string oldString,
string userId,
int positionId)
{
var noteType = ChangedFieldCodeManager.GetChangedFieldCodes().First(code => TrimEquals(code.Name, fieldName) && TrimEquals(code.TableName, tableNumber.ToString()));
using (var conn = new SqlConnection())
{
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@AccountID", accountId),
DataUtilities.CreateNullableSqlParameter("@FieldName", fieldName),
DataUtilities.CreateNullableSqlParameter("@FieldType", fieldType), //(int)fieldType
DataUtilities.CreateNullableSqlParameter("@TableNumber", (int)tableNumber),
DataUtilities.CreateNullableSqlParameter("@NewString", newString),
DataUtilities.CreateNullableSqlParameter("@OldString", oldString),
DataUtilities.CreateNullableSqlParameter("@UserID", userId),
DataUtilities.CreateNullableSqlParameter("@PositionID", positionId),
DataUtilities.CreateNullableSqlParameter("@NoteType", noteType.CodeId),
DataUtilities.CreateNullableSqlParameter("@UpdateLastWorkDate", 0),
DataUtilities.CreateNullableSqlParameter("@IsUpdatePhoneStatusOK", 100)
};
int returnValue;
DataUtilities.SqlExecuteStoredProcedureNonQuery(ConnectionString, "spUpdateField", parameters, out returnValue);
if (returnValue != 0)
{
throw new UpdateFieldException(returnValue);
}
}
}
public void UpdatePhoneStatus(int accountid,
int slot,
// PropertyMapping.UpdateFieldDataType fieldType,
// PropertyMapping.UpdateFieldTable tableNumber,
string newString,
string oldString
)
{
var noteType = 220 + slot;
// var noteType = ChangedFieldCodes.First(code => TrimEquals(code.Name, fieldName) && TrimEquals(code.TableName, tableNumber.ToString()));
// var fieldName1 = field.GetUpdateFieldName(tableType, slot);
using (var conn = new SqlConnection())
{
var parameters = new[]
{
DataUtilities.CreateNullableSqlParameter("@AccountID", accountid),
DataUtilities.CreateNullableSqlParameter("@FieldName", "PhoneStatus" + slot.ToString()),
DataUtilities.CreateNullableSqlParameter("@FieldType", 0), //(int)fieldType
DataUtilities.CreateNullableSqlParameter("@TableNumber", 2), //account phone table = 2
DataUtilities.CreateNullableSqlParameter("@NewString", newString),
DataUtilities.CreateNullableSqlParameter("@OldString", oldString),
DataUtilities.CreateNullableSqlParameter("@UserID", "A"),
DataUtilities.CreateNullableSqlParameter("@PositionID", 3), //Quan positionid
DataUtilities.CreateNullableSqlParameter("@NoteType", noteType.ToString()),
DataUtilities.CreateNullableSqlParameter("@UpdateLastWorkDate", 0),
DataUtilities.CreateNullableSqlParameter("@IsUpdatePhoneStatusOK", 100)
};
int returnValue;
DataUtilities.SqlExecuteStoredProcedureNonQuery(ConnectionString, "spUpdateField", parameters, out returnValue);
if (returnValue != 0)
{
throw new UpdateFieldException(returnValue);
}
}
}
//todo: change all above methods to update field
public void UpdatePhone(Phone newPhone, Phone oldPhone, List <PropertyMapping> propertyMappings, string userId, int positionId)
{
UpdateField <Phone>(newPhone, oldPhone, propertyMappings, userId, positionId, newPhone.AccountId, newPhone.PhoneSlot);
var parameter = new[]
{
DataUtilities.CreateNullableSqlParameter("@AccountID", newPhone.AccountId),
DataUtilities.CreateNullableSqlParameter("@SlotNumber", newPhone.PhoneSlot),
DataUtilities.CreateNullableSqlParameter("@LocationType", newPhone.LocationType),
DataUtilities.CreateNullableSqlParameter("@ServiceType", newPhone.ServiceType),
DataUtilities.CreateNullableSqlParameter("@ConsentToCall", newPhone.ConsentToCall),
DataUtilities.CreateNullableSqlParameter("@ConsentToTextMessage ", newPhone.ConsentToTextMessage),
DataUtilities.CreateNullableSqlParameter("@ConsentToDialer ", newPhone.ConsentForAutomatedDialing),
DataUtilities.CreateNullableSqlParameter("@VerificationStatus", newPhone.VerificationStatus),
DataUtilities.CreateNullableSqlParameter("@ConsentedPreferredStartTime", newPhone.ConsentedPreferredStartTime),
DataUtilities.CreateNullableSqlParameter("@ConsentedPreferredEndTime", newPhone.ConsentedPreferredEndTime),
DataUtilities.CreateNullableSqlParameter("@LastWashSendDate", newPhone.LastWashSendDate),
DataUtilities.CreateNullableSqlParameter("@ExternalScore", newPhone.ExternalScore),
DataUtilities.CreateNullableSqlParameter("@UserID", userId),
DataUtilities.CreateNullableSqlParameter("@PositionID", positionId)
};
DataUtilities.SqlExecuteStoredProcedureNonQuery(ConnectionString, "spSaveAccountPhone", parameter);
}
