public int GetDatabaseVersion()
{
try {
int result = 0;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
string sql = "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'dbversion') SELECT dbversion FROM dbversion ELSE SELECT 0 AS dbversion";
DbCommand.CommandText = sql;
object value = DbConnection.ExecuteScalar(DbCommand);
if (value != DBNull.Value)
result = (int)value;
}
}
else {
throw new Exception("Unable to Connect");
}
}
return result;
}
catch {
throw new Exception("The database server is offline or unreachable");
}
}
protected internal IPersistence Create(bool dropOnInit)
{
Connect();
_logger.LogInformation("Starting test #{number}", _testRunId);
_options = CreateOptions();
_sqlPersistence = new MsSqlPersistence(_options);
if (dropOnInit)
{
_sqlPersistence.DestroyAllAsync(CancellationToken.None).Wait();
}
_sqlPersistence.InitAsync(CancellationToken.None).Wait();
_logger.LogInformation("Test #{number} started", _testRunId);
return(_sqlPersistence);
}
private IPersistence Create()
{
_id = Interlocked.Increment(ref _staticId);
_logger.LogInformation("Starting test #{number}", _id);
_options = new MsSqlPersistenceOptions(LoggerFactory)
{
ConnectionString = ConnectionString,
StreamsTableName = "streams_" + _id + "_" + GetType().Name,
Serializer = new JsonMsSqlSerializer()
};
_sqlPersistence = new MsSqlPersistence(_options);
_sqlPersistence.DestroyAllAsync(CancellationToken.None).Wait();
_sqlPersistence.InitAsync(CancellationToken.None).Wait();
_logger.LogInformation("Test #{number} started", _id);
return(_sqlPersistence);
}
public static async Task <IPersistence> CreateSqlServerAsync(
string connectionString,
string tablename,
INStoreLoggerFactory loggerFactory)
{
var options = new MsSqlPersistenceOptions(loggerFactory)
{
ConnectionString = connectionString,
StreamsTableName = tablename,
Serializer = new JsonMsSqlSerializer()
};
var persistence = new MsSqlPersistence(options);
await persistence.DestroyAllAsync(CancellationToken.None);
await persistence.InitAsync(CancellationToken.None);
return(persistence);
}
protected internal IPersistence Create(bool dropOnInit)
{
Connect();
_logger.LogInformation("Starting test #{number}", _testRunId);
_options = new MsSqlPersistenceOptions(LoggerFactory)
{
ConnectionString = _connectionString,
StreamsTableName = "streams_" + _testRunId + "_" + GetType().Name,
Serializer = new JsonMsSqlSerializer()
};
_sqlPersistence = new MsSqlPersistence(_options);
if (dropOnInit)
{
_sqlPersistence.DestroyAllAsync(CancellationToken.None).Wait();
}
_sqlPersistence.InitAsync(CancellationToken.None).Wait();
_logger.LogInformation("Test #{number} started", _testRunId);
return(_sqlPersistence);
}
public Client GetClient(int id)
{
try {
Client result = new Client();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
result = this.GetClient(ref dbConnection, ref dbCommand, id);
}
}else {
throw new Exception("Unable to Connect");
}
}
return result;
}catch {
throw;
}
}
/// <summary>
/// Save Client
/// </summary>
/// <returns></returns>
public int SaveClient(Client client, Guid userToken, int userId)
{
try {
int returnValue = -1;
bool insertNewRecord = false;
string sql = string.Empty;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
try { // Try Catch here allows other exceptions to rollback transactions
if (client.IsDirty) {
SystemDAO.SaveChangeAudit<Client>(ref dbConnection, ref dbCommand,
GetClient(ref dbConnection, ref dbCommand, client.Pk ?? 0),
client,
ModuleNames.Clients,
client.Pk,
userId);
DbCommand.Parameters.Clear();
if (client.ClientId == null || client.ClientId <= 0) {
insertNewRecord = true;
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspInsertClient";
DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
DbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}else {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspUpdateClient";
DbCommand.Parameters.Add("@ClientID", System.Data.SqlDbType.Int).Value = client.ClientId;
DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
DbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
DbCommand.Parameters.Add("@ClientName", System.Data.SqlDbType.Text, 100).Value = client.ClientName != null ? client.ClientName : SqlString.Null;
DbCommand.Parameters.Add("@AccountingId", System.Data.SqlDbType.Text, 100).Value = client.AccountingId != null ? client.AccountingId : SqlString.Null;
DbCommand.Parameters.Add("@TermId", System.Data.SqlDbType.Int).Value = client.Term.TermId != null ? client.Term.TermId : (int?)SqlInt32.Null;
DbCommand.Parameters.Add("@CreditCheckYN", System.Data.SqlDbType.Bit).Value = client.CreditCheckYN != null ? client.CreditCheckYN : SqlBoolean.Null;
DbCommand.Parameters.Add("@CreditHoldYN", System.Data.SqlDbType.Bit).Value = client.CreditHoldYN != null ? client.CreditHoldYN : SqlBoolean.Null;
DbCommand.Parameters.Add("@WebClientYN", System.Data.SqlDbType.Bit).Value = client.WebClientYN != null ? client.WebClientYN : SqlBoolean.Null;
DbCommand.Parameters.Add("@GMPYN", System.Data.SqlDbType.Bit).Value = client.GMPYN != null ? client.GMPYN : SqlBoolean.Null;
DbCommand.Parameters.Add("@BillingAddress", System.Data.SqlDbType.Text, 100).Value = client.BillingAddress != null ? client.BillingAddress : SqlString.Null;
DbCommand.Parameters.Add("@BillingCity", System.Data.SqlDbType.Text, 100).Value = client.BillingCity != null ? client.BillingCity : SqlString.Null;
if (client.BillingState != null && client.BillingState.StateId != null)
DbCommand.Parameters.Add("@BillingStateId", System.Data.SqlDbType.Int).Value = client.BillingState.StateId;
DbCommand.Parameters.Add("@BillingZip", System.Data.SqlDbType.Text, 10).Value = client.BillingZip != null ? client.BillingZip : SqlString.Null;
if (client.BillingCountry != null && client.BillingCountry.CountryId != null)
DbCommand.Parameters.Add("@BillingCountryId", System.Data.SqlDbType.Int).Value = client.BillingCountry.CountryId;
DbCommand.Parameters.Add("@SameAsBillingYN", System.Data.SqlDbType.Bit).Value = client.SameAsBillingYN != null ? client.SameAsBillingYN : SqlBoolean.Null;
DbCommand.Parameters.Add("@ShippingAddress", System.Data.SqlDbType.Text, 100).Value = client.ShippingAddress != null ? client.ShippingAddress : SqlString.Null;
DbCommand.Parameters.Add("@ShippingCity", System.Data.SqlDbType.Text, 100).Value = client.ShippingCity != null ? client.ShippingCity : SqlString.Null;
if (client.ShippingState != null && client.ShippingState.StateId != null)
DbCommand.Parameters.Add("@ShippingStateId", System.Data.SqlDbType.Int).Value = client.ShippingState.StateId;
DbCommand.Parameters.Add("@ShippingZip", System.Data.SqlDbType.Text, 10).Value = client.ShippingZip != null ? client.ShippingZip : SqlString.Null;
if (client.ShippingCountry != null && client.ShippingCountry.CountryId != null)
DbCommand.Parameters.Add("@ShippingCountryId", System.Data.SqlDbType.Int).Value = client.ShippingCountry.CountryId;
if (client.ClientId > 0)
returnValue = DbConnection.ExecuteCommand(DbCommand);
else {
// returnValue = Primary Key Id
returnValue = (int)DbConnection.ExecuteScalar(DbCommand);
client.ClientId = returnValue;
}
}
// Save Contacts
using (ClientDAO contactDao = new ClientDAO())
contactDao.SaveContacts(ref dbConnection, ref dbCommand, ref client, userId);
// Save Pricing
this.SaveClientPricing(ref dbConnection, ref dbCommand, ref client, userId);
// Save Complaints
this.SaveClientComplaints(ref dbConnection, ref dbCommand, ref client, userId);
// Save Notes
this.SaveClientNotes(ref dbConnection, ref dbCommand, client, userId);
// Save Documents
this.SaveClientDocuments(ref dbConnection, ref dbCommand, client, userId);
// Accounting Interface
//if (insertNewRecord && Vars.AccountingSettings.InterfaceId > 0) {
// client = new ClientInterface().ClientAdd(client);
// if (!string.IsNullOrWhiteSpace(client.AccountingId)) {
// sql = @"
// UPDATE customers
// SET accountingid = @AccountingId
// WHERE id = @ID
// ";
// DbCommand.Parameters.Clear();
// DbCommand.CommandText = sql;
// DbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = client.ClientId;
// DbCommand.Parameters.Add("@AccountingId", System.Data.SqlDbType.Text, 100).Value = client.AccountingId ?? string.Empty;
// DbConnection.ExecuteCommand(DbCommand);
// }
//}
// Release Lock
using (SystemDAO systemDao = new SystemDAO()) {
systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.Client, client.ClientId.ToString(), userToken);
}
}catch {
if (DbConnection.IsConnected() && DbConnection.IsTransaction())
DbConnection.Rollback();
throw;
}
}
}else {
throw new Exception("Unable to Connect");
}
}
return returnValue;
}catch {
throw;
}
}
public SmartCollection<ClientPricing> GetClientPricings(int ClientId)
{
try {
SmartCollection<ClientPricing> resultList = new SmartCollection<ClientPricing>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientPricings";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = ClientId;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows) {
ClientPricing price = new ClientPricing();
price.ClientPricingId = Convert.ToInt32(row["ClientPricingID"]);
price.ClientId = Convert.ToInt32(row["ClientID"]);
if (row["MethodID"] != DBNull.Value)
price.MethodId = Convert.ToInt32(row["MethodID"]);
if (row["MethodNumberID"] != DBNull.Value)
price.MethodNumberId = Convert.ToInt32(row["MethodNumberID"]);
if (row["AnalyteID"] != DBNull.Value)
price.AnalyteId = Convert.ToInt32(row["AnalyteID"]);
price.Description = row["Description"].ToString();
price.Discount = row["Discount"] != DBNull.Value ? Convert.ToDouble(row["Discount"]) : 0;
price.FlatRate = row["FlatRate"] != DBNull.Value ? Convert.ToDouble(row["FlatRate"]) : 0;
price.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
price.CreatedUser = row["CreatedUser"].ToString();
price.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
price.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
price.ModifiedUser = row["ModifiedUser"].ToString();
price.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
if (price.MethodId.HasValue)
price.Method = new Method { MethodId = price.MethodId, MethodName = row["MethodName"].ToString() };
else
price.Method = null;
if (price.MethodNumberId.HasValue)
price.MethodNumber = new MethodNumber { MethodNumberId = price.MethodNumberId, MethodNumberName = row["MethodNumberName"].ToString() };
else
price.MethodNumber = null;
if (price.AnalyteId.HasValue)
price.AnalyteItem = new Analyte { AnalyteId = price.AnalyteId, AnalyteName = row["AnalyteName"].ToString() };
else
price.AnalyteItem = null;
resultList.Add(price);
}
returnDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return resultList;
}catch {
throw;
}
}
public SmartCollection<Client> GetClientsRecent(int userId)
{
try {
SmartCollection<Client> resultList = new SmartCollection<Client>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetClientsRecent";
DbCommand.Parameters.Clear();
DataTable clientDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in clientDT.Rows)
{
Client client = new Client();
client.ClientId = Convert.ToInt32(row["ClientID"]);
client.AccountingId = row["AccountingID"].ToString();
client.ClientName = row["ClientName"].ToString();
client.TermId = row["TermID"] != DBNull.Value ? (int)row["TermID"] : -1;
if (client.TermId != null && client.TermId != -1)
client.Term = new Term() { TermId = (int)row["TermID"], TermName = row["TermName"].ToString() };
client.WebClientYN = row["WebClientYN"] != DBNull.Value ? Convert.ToBoolean(row["WebClientYN"]) : false;
client.CreditCheckYN = row["CreditCheckYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditCheckYN"]) : false;
client.CreditHoldYN = row["CreditHoldYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditHoldYN"]) : false;
client.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
client.BillingAddress = row["BillingAddress"].ToString();
client.BillingCity = row["BillingCity"].ToString();
client.BillingStateId = row["BillingStateID"] != DBNull.Value ? (int)row["BillingStateID"] : -1;
if (client.BillingStateId != null && client.BillingStateId != -1)
client.BillingState = new State() { StateId = (int)row["BillingStateID"], StateName = row["BillingStateName"].ToString() };
client.BillingZip = row["BillingZip"].ToString();
client.BillingCountryId = row["BillingCountryID"] != DBNull.Value ? (int)row["BillingCountryID"] : -1;
if (client.BillingCountryId != null && client.BillingCountryId != -1)
client.BillingCountry = new Country() { CountryId = (int)row["BillingCountryID"], CountryName = row["BillingCountryName"].ToString() };
client.SameAsBillingYN = row["SameAsBillingYN"] != DBNull.Value ? Convert.ToBoolean(row["SameAsBillingYN"]) : false;
client.ShippingAddress = row["ShippingAddress"].ToString();
client.ShippingCity = row["ShippingCity"].ToString();
client.ShippingStateId = row["ShippingStateID"] != DBNull.Value ? (int)row["ShippingStateID"] : -1;
if (client.ShippingStateId != null && client.ShippingStateId != -1)
client.ShippingState = new State() { StateId = (int)row["ShippingStateID"], StateName = row["ShippingStateName"].ToString() };
client.ShippingZip = row["ShippingZip"].ToString();
client.ShippingCountryId = row["ShippingCountryID"] != DBNull.Value ? (int)row["ShippingCountryID"] : -1;
if (client.ShippingCountryId != null && client.ShippingCountryId != -1)
client.ShippingCountry = new Country() { CountryId = (int)row["ShippingCountryID"], CountryName = row["ShippingCountryName"].ToString() };
client.CreatedUser = row["CreatedUser"].ToString();
client.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : -1;
client.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
client.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : -1;
client.ModifiedUser = row["ModifiedUser"].ToString();
client.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
// Other client objects
client.Contacts = GetContacts(client.ClientId);
client.Prices = GetClientPricings(client.ClientId);
client.Documents = GetClientDocuments(client.ClientId);
client.Notes = GetClientNotes(client.ClientId);
client.Complaints = GetClientComplaints(client.ClientId);
resultList.Add(client);
}
clientDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return resultList;
}catch {
throw;
}
}
public SmartCollection<ClientDocument> GetClientDocuments(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int clientId)
{
var result = new SmartCollection<ClientDocument>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientDocuments";
dbCommand.Parameters.Clear();
dbCommand.Parameters.AddWithValue("@ClientId", clientId);
using (var reader = dbConnection.ExecuteReader(dbCommand)) {
while (reader.Read()) {
result.Add(new ClientDocument {
ClientDocumentId = (int)reader["ClientDocumentID"],
Filename = reader["Filename"].ToString(),
CreatedBy = reader["CreatedBy"] != DBNull.Value ? Convert.ToInt32(reader["CreatedBy"]) : -1,
CreatedUser = reader["CreatedUser"].ToString(),
CreatedDate = reader["CreatedDate"] != DBNull.Value ? (DateTime)reader["CreatedDate"] : (DateTime)SqlDateTime.Null,
ModifiedBy = reader["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(reader["ModifiedBy"]) : -1,
ModifiedUser = reader["ModifiedUser"].ToString(),
ModifiedDate = reader["ModifiedDate"] != DBNull.Value ? (DateTime)reader["ModifiedDate"] : (DateTime)SqlDateTime.Null
});
}
}
return result;
}
public ClientNote GetClientNote(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? noteId)
{
try {
ClientNote note = new ClientNote();
if (dbConnection.IsConnected()) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientNote";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = noteId;
DataTable notesDT = dbConnection.ExecuteQuery(dbCommand);
if (notesDT.Rows.Count == 1) {
DataRow row = notesDT.Rows[0];
note.ClientNoteId = Convert.ToInt32(row["ClientNoteID"]);
note.ClientId = Convert.ToInt32(row["ClientID"]);
note.Note = row["Note"].ToString();
note.CopyToSampleYN = Convert.ToBoolean(row["CopyToSampleYN"]);
note.IncludeOnCOAYN = Convert.ToBoolean(row["IncludeOnCOAYN"]);
note.CreatedUser = row["CreatedUser"].ToString();
note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
note.ModifiedUser = row["ModifiedUser"].ToString();
note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
notesDT = null;
}else {
notesDT = null;
return null;
}
}else {
throw new Exception("Unable to Connect");
}
return note;
}catch {
throw;
}
}
public SmartCollection<ClientComplaint> GetClientComplaintsOpen(Identification identification)
{
try
{
SmartCollection<ClientComplaint> resultList = new SmartCollection<ClientComplaint>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetClientComplaintsOpen";
DbCommand.Parameters.Clear();
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in returnDT.Rows)
{
ClientComplaint complaint = new ClientComplaint();
complaint.ClientComplaintId = Convert.ToInt32(row["ClientComplaintID"]);
complaint.ClientId = Convert.ToInt32(row["ClientID"]);
complaint.ClientName = row["ClientName"].ToString();
complaint.ClassificationId = row["ClassificationID"] != DBNull.Value ? Convert.ToInt32(row["ClassificationID"]) : -1;
if (complaint.ClassificationId.HasValue && complaint.ClassificationId != -1)
complaint.Classification = new Complaint { ComplaintId = complaint.ClassificationId, ComplaintName = row["ComplaintName"].ToString(), Active = true };
complaint.Description = row["Description"].ToString();
complaint.ARLNumber = row["ARLNumber"].ToString();
complaint.StatusYN = (bool)(row["StatusYN"] ?? false);
complaint.RootCause = row["RootCause"].ToString();
complaint.CorrectiveAction = row["CorrectiveAction"].ToString();
if (row["CorrectiveActionDate"] != DBNull.Value)
complaint.CorrectiveActionDate = (DateTime)row["CorrectiveActionDate"];
else
complaint.CorrectiveActionDate = null;
complaint.CorrectiveActionUserId = row["CorrectiveActionUserID"] != DBNull.Value ? Convert.ToInt32(row["CorrectiveActionUserID"]) : -1;
complaint.CorrectiveActionUser = row["CorrectiveUser"].ToString();
complaint.NotifyUserId = row["NotifyUserID"] != DBNull.Value ? Convert.ToInt32(row["NotifyUserID"]) : -1;
complaint.NotifyUser = row["NotifyUser"].ToString();
complaint.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
complaint.CreatedUser = row["CreatedUser"].ToString();
complaint.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
complaint.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
complaint.ModifiedUser = row["ModifiedUser"].ToString();
complaint.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(complaint);
}
returnDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
public byte[] GetClientDocumentData(int clientDocumentId)
{
try {
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetClientDocumentData";
DbCommand.Parameters.Clear();
DbCommand.Parameters.AddWithValue("@ClientDocumentId", clientDocumentId);
var result = DbConnection.ExecuteScalar(DbCommand);
if (result != null)
return result as byte[];
else
return new byte[0];
}
}
}
return null;
}catch {
throw;
}
}
// IDisposable
protected virtual void Dispose(bool disposing)
{
if (!this.disposedValue) {
if (disposing) {
if (this.dbConnectionSettings != null)
this.dbConnectionSettings = null;
if (this.dbConnection != null) {
this.dbConnection.Dispose();
this.dbConnection = null;
}
if (this.dbCommand != null) {
this.dbCommand.Dispose();
this.dbCommand = null;
}
}
}
this.disposedValue = true;
}
public int RemoveContacts(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int clientId, int userId)
{
try
{
int returnValue = 0;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspRemoveContacts";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = clientId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
returnValue = dbConnection.ExecuteCommand(dbCommand);
return returnValue;
}
catch
{
throw;
}
}
public int RemoveContact(int contactId, int userId)
{
try
{
DateTime deleteDate = System.DateTime.Now;
int result = -1;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspRemoveContact";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ContactId", System.Data.SqlDbType.Int).Value = contactId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
result = dbConnection.ExecuteCommand(dbCommand);
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return result;
}
catch
{
throw;
}
}
public Client GetClient(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? id)
{
try {
Client result = new Client();
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClient";
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = id;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
if (returnDT.Rows.Count == 1) {
DataRow row = returnDT.Rows[0];
result.ClientId = Convert.ToInt32(row["ClientID"]);
result.AccountingId = row["AccountingID"].ToString();
result.ClientName = row["ClientName"].ToString();
result.TermId = row["TermID"] != DBNull.Value ? (int)row["TermID"] : -1;
if (result.TermId != null && result.TermId != -1)
result.Term = new Term() { TermId = (int)row["TermID"], TermName = row["TermName"].ToString() };
result.WebClientYN = row["WebClientYN"] != DBNull.Value ? Convert.ToBoolean(row["WebClientYN"]) : false;
result.CreditCheckYN = row["CreditCheckYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditCheckYN"]) : false;
result.CreditHoldYN = row["CreditHoldYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditHoldYN"]) : false;
result.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
result.BillingAddress = row["BillingAddress"].ToString();
result.BillingCity = row["BillingCity"].ToString();
result.BillingStateId = row["BillingStateID"] != DBNull.Value ? (int)row["BillingStateID"] : -1;
if (result.BillingStateId != null && result.BillingStateId != -1)
result.BillingState = new State() { StateId = (int)row["BillingStateID"], StateName = row["BillingStateName"].ToString() };
result.BillingZip = row["BillingZip"].ToString();
result.BillingCountryId = row["BillingCountryID"] != DBNull.Value ? (int)row["BillingCountryID"] : -1;
if (result.BillingCountryId != null && result.BillingCountryId != -1)
result.BillingCountry = new Country() { CountryId = (int)row["BillingCountryID"], CountryName = row["BillingCountryName"].ToString() };
result.SameAsBillingYN = row["SameAsBillingYN"] != DBNull.Value ? Convert.ToBoolean(row["SameAsBillingYN"]) : false;
result.ShippingAddress = row["ShippingAddress"].ToString();
result.ShippingCity = row["ShippingCity"].ToString();
result.ShippingStateId = row["ShippingStateID"] != DBNull.Value ? (int)row["ShippingStateID"] : -1;
if (result.ShippingStateId != null && result.ShippingStateId != -1)
result.ShippingState = new State() { StateId = (int)row["ShippingStateID"], StateName = row["ShippingStateName"].ToString() };
result.ShippingZip = row["ShippingZip"].ToString();
result.ShippingCountryId = row["ShippingCountryID"] != DBNull.Value ? (int)row["ShippingCountryID"] : -1;
if (result.ShippingCountryId != null && result.ShippingCountryId != -1)
result.ShippingCountry = new Country() { CountryId = (int)row["ShippingCountryID"], CountryName = row["ShippingCountryName"].ToString() };
result.CreatedUser = row["CreatedUser"].ToString();
result.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : -1;
result.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
result.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : -1;
result.ModifiedUser = row["ModifiedUser"].ToString();
result.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
row = null;
}
returnDT = null;
return result;
}catch {
throw;
}
}
public int RemoveClientNote(int clientNoteId, int userId)
{
try {
int rowsAffected;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspRemoveClientNote";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = clientNoteId;
DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
rowsAffected = DbConnection.ExecuteCommand(DbCommand);
}
}else {
throw new Exception("Unable to Connect");
}
}
return rowsAffected;
}catch {
throw;
}
}
private string GetClientComplaintsOpenCount(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
bool canApproveComplaints = AppLib.IsAuthorized(identification, SysLib.GetOptionName(ModuleNames.Clients, ModelNamesEnum.Complaint, ModuleAction.Approve));
int returnValue = 0;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientComplaintsOpenCount";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = identification.UserId;
returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
return returnValue <= 0 ? string.Empty : returnValue.ToString();
}
catch
{
throw;
}
}
public int SaveClientComplaint(ClientComplaint complaint, Identification identification)
{
int result = 0;
try
{
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
result = this.SaveClientComplaint(ref dbConnection, ref dbCommand, complaint, identification);
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return result;
}
catch
{
throw;
}
}
public string GetClientComplaintsOpenCount(Identification identification)
{
try
{
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
return this.GetClientComplaintsOpenCount(ref dbConnection, ref dbCommand, identification);
}
}
else
{
throw new Exception("Unable to Connect");
}
}
}
catch
{
throw;
}
}
public int SaveClientComplaint(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ClientComplaint complaint, Identification identification)
{
try
{
int returnValue = 0;
if (complaint.IsDirty)
{
SystemDAO.SaveChangeAudit<ClientComplaint>(ref dbConnection, ref dbCommand,
GetClientComplaint(ref dbConnection, ref dbCommand, complaint.Pk),
complaint,
ModuleNames.Clients,
complaint.ClientId,
identification.UserId);
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspUpdateClientComplaint";
dbCommand.Parameters.Add("@ClientComplaintId", System.Data.SqlDbType.Int).Value = complaint.ClientComplaintId;
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = complaint.ClientId;
dbCommand.Parameters.Add("@ClassificationId", System.Data.SqlDbType.Int).Value = complaint.ClassificationId;
dbCommand.Parameters.Add("@StatusYN", System.Data.SqlDbType.Int).Value = (bool)complaint.StatusYN;
dbCommand.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 100).Value = complaint.Description;
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.NVarChar, 100).Value = complaint.ARLNumber;
dbCommand.Parameters.Add("@RootCause", System.Data.SqlDbType.NVarChar, 100).Value = complaint.RootCause;
dbCommand.Parameters.Add("@NotifyUserId", System.Data.SqlDbType.Int).Value = complaint.NotifyUserId;
dbCommand.Parameters.Add("@CorrectiveAction", System.Data.SqlDbType.NVarChar, 100).Value = complaint.CorrectiveAction;
dbCommand.Parameters.Add("@CorrectiveActionDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@CorrectiveActionUserId", System.Data.SqlDbType.Int).Value = complaint.CorrectiveActionUserId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = complaint.CorrectiveActionDate != null ? (SqlDateTime)complaint.CorrectiveActionDate : SqlDateTime.Null;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
return returnValue;
}
catch
{
throw;
}
}
public SmartCollection<ClientDocument> GetClientDocuments(int clientId)
{
try {
var result = new SmartCollection<ClientDocument>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
if (DbConnection.IsConnected())
using (DbCommand)
result.AddRange(GetClientDocuments(ref dbConnection, ref dbCommand, clientId));
return result;
}catch {
throw;
}
}
public int SaveClientComplaints(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Client client, int userId)
{
try {
int returnValue = 0;
string sql = string.Empty;
foreach (ClientComplaint complaint in client.Complaints)
{
if (complaint.IsDirty) {
SystemDAO.SaveChangeAudit<ClientComplaint>(ref dbConnection, ref dbCommand,
GetClientComplaint(ref dbConnection, ref dbCommand, complaint.Pk != null ? complaint.Pk : -1),
complaint,
ModuleNames.Clients,
client.Pk,
userId);
dbCommand.Parameters.Clear();
sql = string.Empty;
if (complaint.ClientComplaintId == null || complaint.ClientComplaintId <= 0) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspInsertClientComplaint";
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}else {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspUpdateClientComplaint";
dbCommand.Parameters.Add("@ClientComplaintId", System.Data.SqlDbType.Int).Value = complaint.ClientComplaintId;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = complaint.DeleteDate ?? SqlDateTime.Null;
}
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@ClassificationId", System.Data.SqlDbType.Int).Value = complaint.ClassificationId;
dbCommand.Parameters.Add("@StatusYN", System.Data.SqlDbType.Bit).Value = (bool)complaint.StatusYN;
dbCommand.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 100).Value = complaint.Description;
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.NVarChar, 100).Value = complaint.ARLNumber != "" ? complaint.ARLNumber : SqlString.Null;
dbCommand.Parameters.Add("@RootCause", System.Data.SqlDbType.NVarChar, 100).Value = complaint.RootCause;
dbCommand.Parameters.Add("@NotifyUserId", System.Data.SqlDbType.Int).Value = complaint.NotifyUserId;
dbCommand.Parameters.Add("@CorrectiveAction", System.Data.SqlDbType.NVarChar, 100).Value = complaint.CorrectiveAction;
dbCommand.Parameters.Add("@CorrectiveActionDate", System.Data.SqlDbType.DateTime).Value = complaint.CorrectiveActionDate != null ? (SqlDateTime)complaint.CorrectiveActionDate : SqlDateTime.Null;
dbCommand.Parameters.Add("@CorrectiveActionUserId", System.Data.SqlDbType.Int).Value = complaint.CorrectiveActionUserId;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}catch {
throw;
}
}
public int GetClientId(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int sampleId)
{
try {
int result = -1;
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientId";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@SampleId", System.Data.SqlDbType.Int).Value = sampleId;
result = (int)dbConnection.ExecuteScalar(dbCommand);
return result;
}catch {
throw;
}
}
public void SaveClientDocuments(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Client client, int userId)
{
try {
foreach (var doc in client.Documents) {
if (doc.ClientDocumentId < 0) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspInsertClientDocument";
dbCommand.Parameters.Clear();
dbCommand.Parameters.AddWithValue("@ClientId", client.ClientId);
dbCommand.Parameters.AddWithValue("@Filename", doc.Filename);
dbCommand.Parameters.AddWithValue("@DocumentData", Convert.FromBase64CharArray(doc.DocumentData.ToCharArray(), 0, doc.DocumentData.Length));
dbCommand.Parameters.AddWithValue("@CreatedBy", userId);
dbCommand.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
doc.ClientDocumentId = dbConnection.ExecuteCommand(dbCommand);
}else if (doc.IsDirty) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspUpdateClientDocument";
dbCommand.Parameters.Clear();
dbCommand.Parameters.AddWithValue("@ClientDocumentId", doc.ClientDocumentId);
dbCommand.Parameters.AddWithValue("@ClientId", client.ClientId);
dbCommand.Parameters.AddWithValue("@ModifiedBy", userId);
dbCommand.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
dbCommand.Parameters.AddWithValue("@DeleteDate", doc.DeleteDate);
dbConnection.ExecuteCommand(dbCommand);
}
}
}catch {
throw;
}
}
public SmartCollection<ClientNote> GetClientNotes(int clientId)
{
try {
SmartCollection<ClientNote> resultList = new SmartCollection<ClientNote>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientNotes";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = clientId;
DataTable notesDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in notesDT.Rows) {
ClientNote note = new ClientNote();
note.ClientNoteId = Convert.ToInt32(row["ClientNoteID"]);
note.ClientId = Convert.ToInt32(row["ClientID"]);
note.Note = row["Note"].ToString();
note.CopyToSampleYN = Convert.ToBoolean(row["CopyToSampleYN"]);
note.IncludeOnCOAYN = Convert.ToBoolean(row["IncludeOnCOAYN"]);
note.CreatedUser = row["CreatedUser"].ToString();
note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
note.ModifiedUser = row["ModifiedUser"].ToString();
note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(note);
}
notesDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return resultList;
}catch {
throw;
}
}
public int SaveClientNotes(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Client client, int userId)
{
try {
int returnValue = 0;
string sql = string.Empty;
foreach (ClientNote note in client.Notes) {
if (note.IsDirty) {
SystemDAO.SaveChangeAudit<ClientNote>(ref dbConnection, ref dbCommand,
GetClientNote(ref dbConnection, ref dbCommand, note.Pk),
note,
ModuleNames.Clients,
client.Pk,
userId);
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.Clear();
sql = string.Empty;
if (note.ClientNoteId <= 0) {
dbCommand.CommandText = "uspInsertClientNote";
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}else {
dbCommand.CommandText = "uspUpdateClientNote";
dbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = note.ClientNoteId;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = note.DeleteDate ?? SqlDateTime.Null;
}
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@Note", System.Data.SqlDbType.NVarChar, 4000).Value = note.Note;
dbCommand.Parameters.Add("@CopyToSampleYN", System.Data.SqlDbType.Bit).Value = (bool)note.CopyToSampleYN;
dbCommand.Parameters.Add("@IncludeOnCOAYN", System.Data.SqlDbType.Bit).Value = (bool)note.IncludeOnCOAYN;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}catch {
throw;
}
}
public SmartCollection<Client> GetClientsList()
{
try {
SmartCollection<Client> results = new SmartCollection<Client>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetClientsList";
DbCommand.Parameters.Clear();
var reader = DbConnection.ExecuteReader(DbCommand);
results.AddRange(AutoMap.MapReaderToList<Client>(reader));
}
}else {
throw new Exception("Unable to Connect");
}
}
return results;
}catch {
throw;
}
}
public int SaveClientPricing(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Client client, int userId)
{
try {
int returnValue = 0;
foreach (ClientPricing price in client.Prices) {
if (price.IsDirty) {
SystemDAO.SaveChangeAudit<ClientPricing>(ref dbConnection, ref dbCommand,
GetClientPricing(price.ClientPricingId != -1 ? price.ClientPricingId : -1),
price,
ModuleNames.Clients,
client.Pk,
userId);
dbCommand.Parameters.Clear();
if (price.ClientPricingId <= 0) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspInsertClientPricing";
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
}else {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspUpdateClientPricing";
dbCommand.Parameters.Add("@ClientPricingId", System.Data.SqlDbType.Int).Value = price.ClientPricingId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = price.DeleteDate;
}
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@MethodId", System.Data.SqlDbType.Int).Value = price.MethodId;
dbCommand.Parameters.Add("@MethodNumberId", System.Data.SqlDbType.Int).Value = price.MethodNumberId;
dbCommand.Parameters.Add("@AnalyteId", System.Data.SqlDbType.Int).Value = price.AnalyteId;
dbCommand.Parameters.Add("@Description", System.Data.SqlDbType.Text, 100).Value = price.Description;
dbCommand.Parameters.Add("@Discount", System.Data.SqlDbType.Decimal).Value = price.Discount ?? 0;
dbCommand.Parameters.Add("@FlatRate", System.Data.SqlDbType.Decimal).Value = price.FlatRate ?? 0;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}catch {
throw;
}
}
public Contact GetContact(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? contactId)
{
try
{
Contact contact = new Contact();
if (dbConnection.IsConnected())
{
dbCommand.Parameters.Clear();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetContact";
dbCommand.Parameters.Add("@ContactId", System.Data.SqlDbType.Int).Value = contactId;
DataTable contactDT = dbConnection.ExecuteQuery(dbCommand);
if (contactDT.Rows.Count == 1)
{
DataRow row = contactDT.Rows[0];
contact.ContactId = Convert.ToInt32(row["ContactID"]);
contact.ClientId = Convert.ToInt32(row["ClientID"]);
contact.FirstName = row["FirstName"].ToString();
contact.LastName = row["LastName"].ToString();
contact.PrimaryEmail = row["PrimaryEmail"].ToString();
contact.PrimaryEmailSendYN = row["PrimaryEmailSendYN"] != DBNull.Value ? (bool)row["PrimaryEmailSendYN"] : false;
contact.SecondaryEmail = row["SecondaryEmail"].ToString();
contact.SecondaryEmailSendYN = row["SecondaryEmailSendYN"] != DBNull.Value ? (bool)row["SecondaryEmailSendYN"] : false;
contact.PrimaryPhone = row["PrimaryPhone"].ToString();
contact.SecondaryPhone = row["SecondaryPhone"].ToString();
contact.Fax = row["Fax"].ToString();
contact.FaxSendYN = row["FaxSendYN"] != DBNull.Value ? (bool)row["FaxSendYN"] : false;
contact.Comments = row["Comments"].ToString();
contact.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
contact.CreatedUser = row["CreatedUser"].ToString();
contact.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
contact.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
contact.ModifiedUser = row["ModifiedUser"].ToString();
contact.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
contactDT = null;
}
else
{
contactDT = null;
return null;
}
}
else
{
throw new Exception("Unable to Connect");
}
return contact;
}
catch
{
throw;
}
}
public int SaveContacts(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Client client, int userId)
{
try
{
int returnValue = 0;
foreach (Contact contact in client.Contacts)
{
if (contact.IsDirty)
{
/*SystemDAO.SaveChangeAudit<Contact>(ref dbConnection, ref dbCommand,
GetContact(ref dbConnection, ref dbCommand, contact.Pk),
contact,
ModuleNames.Clients,
client.Pk,
userId); */
dbCommand.Parameters.Clear();
if (contact.ContactId <= 0)
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspInsertContact";
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
else
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspUpdateContact";
dbCommand.Parameters.Add("@ContactId", System.Data.SqlDbType.Int).Value = contact.ContactId;
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = contact.DeleteDate;
}
dbCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.Text, 100).Value = contact.FirstName;
dbCommand.Parameters.Add("@LastName", System.Data.SqlDbType.Text, 100).Value = contact.LastName;
dbCommand.Parameters.Add("@PrimaryEmail", System.Data.SqlDbType.Text, 100).Value = contact.PrimaryEmail;
dbCommand.Parameters.Add("@PrimaryEmailSendYN", System.Data.SqlDbType.Bit).Value = contact.PrimaryEmailSendYN;
dbCommand.Parameters.Add("@SecondaryEmail", System.Data.SqlDbType.Text, 100).Value = contact.SecondaryEmail;
dbCommand.Parameters.Add("@SecondaryEmailSendYN", System.Data.SqlDbType.Bit).Value = contact.SecondaryEmailSendYN;
dbCommand.Parameters.Add("@PrimaryPhone", System.Data.SqlDbType.Text, 20).Value = contact.PrimaryPhone;
dbCommand.Parameters.Add("@SecondaryPhone", System.Data.SqlDbType.Text, 20).Value = contact.SecondaryPhone;
dbCommand.Parameters.Add("@Fax", System.Data.SqlDbType.Text, 20).Value = contact.Fax;
dbCommand.Parameters.Add("@FaxSendYN", System.Data.SqlDbType.Bit).Value = contact.FaxSendYN;
dbCommand.Parameters.Add("@Comments", System.Data.SqlDbType.Text, 4000).Value = contact.Comments;
if (contact.ContactId > 0)
{
//returnValue = Number of Rows Affected
returnValue = dbConnection.ExecuteCommand(dbCommand);
}
else
{
// returnValue = Primary Key Id
returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
contact.ContactId = returnValue;
}
}
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}
catch
{
throw;
}
}
public SmartCollection<Contact> GetContacts(int clientId)
{
try
{
SmartCollection<Contact> resultList = new SmartCollection<Contact>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetContacts";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = clientId;
DataTable contactsDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in contactsDT.Rows)
{
Contact contact = new Contact();
contact.ContactId = Convert.ToInt32(row["ContactID"]);
contact.ClientId = Convert.ToInt32(row["ClientID"]);
contact.FirstName = row["FirstName"].ToString();
contact.LastName = row["LastName"].ToString();
contact.PrimaryEmail = row["PrimaryEmail"].ToString();
contact.PrimaryEmailSendYN = row["PrimaryEmailSendYN"] != DBNull.Value ? (bool)row["PrimaryEmailSendYN"] : false;
contact.SecondaryEmail = row["SecondaryEmail"].ToString();
contact.SecondaryEmailSendYN = row["SecondaryEmailSendYN"] != DBNull.Value ? (bool)row["SecondaryEmailSendYN"] : false;
contact.PrimaryPhone = row["PrimaryPhone"].ToString();
contact.SecondaryPhone = row["SecondaryPhone"].ToString();
contact.Fax = row["Fax"].ToString();
contact.FaxSendYN = row["FaxSendYN"] != DBNull.Value ? (bool)row["FaxSendYN"] : false;
contact.Comments = row["Comments"].ToString();
contact.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
contact.CreatedUser = row["CreatedUser"].ToString();
contact.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
contact.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
contact.ModifiedUser = row["ModifiedUser"].ToString();
contact.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(contact);
}
contactsDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
public ClientComplaint GetClientComplaint(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? clientComplaintId)
{
try {
ClientComplaint complaint = new ClientComplaint();
if (dbConnection.IsConnected()) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientComplaint";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientComplaintId", System.Data.SqlDbType.Int).Value = clientComplaintId;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
if (returnDT.Rows.Count == 1) {
DataRow row = returnDT.Rows[0];
complaint.ClientComplaintId = Convert.ToInt32(row["ClientComplaintID"]);
complaint.ClientId = Convert.ToInt32(row["ClientID"]);
complaint.ClientName = row["ClientName"].ToString();
complaint.ClassificationId = row["ClassificationID"] != DBNull.Value ? Convert.ToInt32(row["ClassificationID"]) : -1;
if (complaint.ClassificationId.HasValue && complaint.ClassificationId != -1)
complaint.Classification = new Complaint { ComplaintId = complaint.ClassificationId, ComplaintName = row["ComplaintName"].ToString(), Active = true };
complaint.Description = row["Description"].ToString();
complaint.ARLNumber = row["ARLNumber"].ToString();
complaint.StatusYN = (bool)(row["StatusYN"] ?? false);
complaint.RootCause = row["RootCause"].ToString();
complaint.CorrectiveAction = row["CorrectiveAction"].ToString();
complaint.CorrectiveActionDate = row["CorrectiveActionDate"] != DBNull.Value ? (DateTime)row["CorrectiveActionDate"] : (DateTime)SqlDateTime.Null;
complaint.CorrectiveActionUserId = row["CorrectiveActionUserID"] != DBNull.Value ? Convert.ToInt32(row["CorrectiveActionUserID"]) : -1;
complaint.CorrectiveActionUser = row["CorrectiveUser"].ToString();
complaint.NotifyUserId = row["NotifyUserID"] != DBNull.Value ? Convert.ToInt32(row["NotifyUserID"]) : -1;
complaint.NotifyUser = row["NotifyUser"].ToString();
complaint.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
complaint.CreatedUser = row["CreatedUser"].ToString();
complaint.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
complaint.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
complaint.ModifiedUser = row["ModifiedUser"].ToString();
complaint.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
returnDT = null;
}else {
returnDT = null;
return null;
}
}else {
throw new Exception("Unable to Connect");
}
return complaint;
}catch {
throw;
}
}
