// 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); }