public int SaveOrderInvoiceItems(Invoice invoice, Identification identification) { try { using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { return this.SaveOrderInvoiceItems(ref dbConnection, ref dbCommand, invoice, identification); } } else { throw new Exception("Unable to Connect"); } } } 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 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; } }
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<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<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; } }
public SmartCollection<SampleTest> SearchResultSampleTests(string searchString, Identification identification) { try { SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>(); string containerSearch = string.Empty; int? labNumber = null; int? sampleTestId = null; try { if (searchString.Contains("-")) { containerSearch = searchString; } else if (searchString.Contains(",")) { char[] charSeparators = new char[] { ',' }; string[] sepString = searchString.Split(charSeparators, StringSplitOptions.None); labNumber = Convert.ToInt32(sepString[0].Trim()); sampleTestId = Convert.ToInt32(sepString[1].Trim()); } else { labNumber = Convert.ToInt32(searchString.Trim()); } } catch (Exception) { throw new Exception("Search value is an invalid format"); } using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sql = @" SELECT DISTINCT sampleTests.id,timepoints.id as timePointId, sampleTests.parentid,sampleTests.status,sampleTests.sampleid,sampleTests.lab_number, sampleTests.priorityid,sampleTests.typeid,sampleTests.analyteid,sampleTests.testid, sampleTests.departmentid,sampleTests.analystid,sampleTests.method_name,sampleTests.method_number_name, sampleTests.low,sampleTests.high,sampleTests.test_minutes, sampleTests.equipment_minutes, sampleTests.accounting_code,dbo.ReturnNextBusinessDay(sampleTests.begin_date,timepoints.begindate_days) AS begin_date, sampleTests.due_date,sampleTests.has_requirement_code, sampleTests.requirement_code,sampleTests.item_price,sampleTests.rush_charge,sampleTests.bill_groupid, sampleTests.catalogid, sampleTests.methodid, sampleTests.methodnumberid, sampleTests.is_per_analyte, sampleTests.is_price_table, priorities.value as priorityname, priorities.active as priorityactive,dpart.department_name,dpart.result_template, test.testname,test.active as testactive,analyte.analytename,analyte.controlled, analyte.active as analyteactive,analyst.firstname, analyst.lastname, sampleTests.endotoxin_limit,sampleTests.endotoxin_limit_uom, sampleTests.avg_weight, sampleTests.avg_weight_uom, sampleTests.dose_per_hour, sampleTests.dose_per_hour_uom,sampleTests.route_of_administration, sampleTests.articles,sampleTests.is_signed, timepoints.begindate_days, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sampleTests.modified_by, sampleTests.modified_date, sampleTests.created_by, sampleTests.created_date, timepoints.timepoint_type FROM orders_samples_tests AS sampleTests RIGHT JOIN orders_samples_tests_timepoints AS timepoints ON timepoints.id = ( SELECT TOP 1 timepoints.id FROM orders_samples_tests_timepoints AS timepoints LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointsResults ON timepoints.id = timepointsResults.parentid WHERE timepointsResults.result_date IS NULL AND timepoints.parentid = sampleTests.id ORDER BY timepoints.begindate_days ) LEFT JOIN orders_samples_tests_timepoints_oos AS oos ON oos.id = timepoints.oosid AND oos.is_testing_complete = 'true' -- Continue Testing LEFT JOIN [User] as users ON sampleTests.modified_by = users.UserID LEFT JOIN [User] as users2 ON sampleTests.created_by = users2.UserID LEFT JOIN [User] as analyst ON sampleTests.analystid = analyst.UserID LEFT JOIN list.departments as dpart ON sampleTests.[departmentid] = dpart.[departmentid] LEFT JOIN list.tests as test ON sampleTests.[testid] = test.[testid] LEFT JOIN list.analyte as analyte ON sampleTests.[analyteid] = analyte.[analyteid] LEFT JOIN list.priorities as priorities ON sampleTests.[priorityid] = priorities.[priorityid] LEFT JOIN orders_samples_tests_containers AS containers ON containers.parentid = sampleTests.id WHERE ((sampleTests.status = 2 OR sampleTests.status = 3) AND sampleTests.delete_date IS NULL AND oos.id IS NULL) "; // Maybe Limit Results by Analyst and/or Department DbCommand.Parameters.Clear(); if (!String.IsNullOrWhiteSpace(containerSearch)) { sql += "AND containers.containerid = @ContainerId "; DbCommand.Parameters.Add("@ContainerId", System.Data.SqlDbType.VarChar, 100).Value = containerSearch; } else if (labNumber != null && sampleTestId != null) { sql += "AND sampleTests.lab_number = @LabNumber AND sampleTests.id = @SampleTestId "; DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = labNumber; DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId; } else { sql += "AND sampleTests.lab_number = @LabNumber "; DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = labNumber; } sql += " ORDER BY sampleTests.id"; DbCommand.CommandText = sql; //DbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.Approved; DataTable returnDT = DbConnection.ExecuteQuery(DbCommand); foreach (DataRow row in returnDT.Rows) { SampleTest sampleTest = new SampleTest(); using (CatalogDAO catalogDao = new CatalogDAO()) { //sampleTest.CatalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification); } using (SystemDAO systemDao = new SystemDAO()) { //sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem); } resultList.Add(sampleTest); } returnDT = null; } return resultList; } else { throw new Exception("Unable to Connect"); } } } catch { throw; } }
private SmartCollection<Oos> GetOOSs(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification) { try { SmartCollection<Oos> resultList = new SmartCollection<Oos>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { DbCommand.CommandType = CommandType.StoredProcedure; DbCommand.CommandText = "uspGetOOSs"; DbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@IncludeAll", System.Data.SqlDbType.Bit).Value = showAll; DataTable returnDT = DbConnection.ExecuteQuery(DbCommand); foreach (DataRow row in returnDT.Rows) { Oos result = new Oos(); result.OosId = Convert.ToInt32(row["OOSID"]); if (row["ARLNumber"] != DBNull.Value) result.ARLNumber = (int)row["ARLNumber"]; if (row["SampleTestId"] != DBNull.Value) result.SampleTestId = (int)row["SampleTestId"]; result.ClientName = row["ClientName"].ToString(); result.Status = row["status"] != DBNull.Value ? (SampleTestStatus)row["status"] : SampleTestStatus.UnderInvestigation; result.TestName = row["TestName"].ToString(); result.AnalyteName = row["AnalyteName"].ToString(); result.PriorityName = row["PriorityName"].ToString(); if (row["DueDate"] != DBNull.Value) result.DueDate = (DateTime)row["DueDate"]; result.DepartmentName = row["DepartmentName"].ToString(); if (row["TimepointStudyYN"] != DBNull.Value) result.TimepointStudyYN = (bool)row["TimepointStudyYN"]; if (row["NextTimepoint"] != DBNull.Value) result.NextTimepoint = (DateTime)row["NextTimepoint"]; result.ModifiedUser = row["ModifiedUser"] != DBNull.Value ? row["ModifiedUser"].ToString() : null; result.CreatedUser = row["CreatedUser"] != DBNull.Value ? row["CreatedUser"].ToString() : null; result.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : new DateTime(); result.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : new Int32(); result.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : new DateTime(); result.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : new Int32(); // result.Results = resultList.Add(result); } returnDT = null; } } else { throw new Exception("Unable to Connect"); } } return resultList; } catch { throw; } }
public SmartCollection<Order> SearchModuleOrders(int? customerId, DateTime? orderReceivedStartDate, DateTime? orderReceivedEndDate, int? testId, int? analyteId) { try { var searchCustomers = customerId != null && customerId > 0; var searchDates = orderReceivedStartDate != null || orderReceivedEndDate != null; var searchTests = testId != null && testId > 0; var serachAnalytes = analyteId != null && analyteId > 0; var result = new SmartCollection<Order>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sql = @" select distinct orders.id, orders.parentid, customers.customer_name, orders.received_date, orders.status from orders LEFT JOIN customers ON customers.id = orders.parentid "; if (searchTests) { sql += @" LEFT JOIN orders_samples as samples ON samples.parentid = orders.id LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id "; } if (serachAnalytes) { // NOTE: joining to seperate order_samples / order_samples_tests table aliases // is so searching tests and analytes can be be done independently or concurrently sql += @" LEFT JOIN orders_samples as samples2 ON samples2.parentid = orders.id LEFT JOIN orders_samples_tests as tests2 ON tests2.parentid = orders.id LEFT JOIN orders_samples_analytes as analytes ON analytes.parentid = tests2.id "; } sql += @" WHERE orders.delete_date IS NULL "; if (searchCustomers) { sql += @" AND customers.id = @customerId "; } if (searchDates) { sql += @" AND (received_date >= @orderReceivedStartDate and received_date <= @orderReceivedEndDate) "; } if (searchTests) { sql += @" AND tests.testid = @testId "; } if (serachAnalytes) { sql += @" AND analytes.analyteid = @analyteId "; } sql += @" ORDER BY orders.received_date DESC "; DbCommand.CommandText = sql; if (searchCustomers) { DbCommand.Parameters.AddWithValue("@customerId", customerId); } if (searchDates) { DbCommand.Parameters.AddWithValue("@orderReceivedStartDate", DateEx.GetStartOfDay(orderReceivedStartDate.Value)); DbCommand.Parameters.AddWithValue("@orderReceivedEndDate", DateEx.GetEndOfDay(orderReceivedEndDate.Value)); } if (searchTests) { DbCommand.Parameters.AddWithValue("@testId", testId); } if (serachAnalytes) { DbCommand.Parameters.AddWithValue("@analyteId", analyteId); } var reader = DbConnection.ExecuteReader(DbCommand); result.AddRange(AutoMap.MapReaderToList<Order>(reader)); foreach (var order in result) { using (var dao = new ClientDAO()) order.Client = dao.GetClient(ref dbConnection, ref dbCommand, order.ParentId); } } } else { throw new Exception("Unable to Connect"); } } return result; } catch { throw; } }
public SmartCollection<Order> SearchOrders(string searchString, IEnumerable<ISearchItem> searchItems, Identification identification) { try { SmartCollection<Order> resultList = new SmartCollection<Order>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sql = @" select distinct orders.id,orders.parentid,orders.ponumber, orders.received_date,orders.start_date, orders.due_date,orders.report_date, orders.status, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, orders.modified_by, orders.modified_date, orders.created_by, orders.created_date, (select sum(amount) from orders_charges where parentid = orders.id and delete_date IS NULL) as ChargesTotal, (select sum(orders_samples_tests.item_price) from orders_samples_tests where orders_samples_tests.parentid = orders.id and orders_samples_tests.delete_date IS NULL and orders_samples_tests.status < 7 ) as TestsTotal from orders LEFT JOIN orders_samples as samples ON samples.parentid = orders.id LEFT JOIN orders_samples_containers as containers ON containers.parentid = samples.id LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id LEFT JOIN list.departments as dept ON dept.departmentid = tests.departmentid LEFT JOIN customers ON customers.id = orders.parentid LEFT JOIN [User] AS users ON orders.modified_by = users.UserID LEFT JOIN [User] as users2 ON orders.created_by = users2.UserID " + SysLib.BuildSearchAllWhereClause(searchString, searchItems); if (identification.ClientYN) { sql += " and customers.id = @companyId "; DbCommand.Parameters.Add("@companyId", System.Data.SqlDbType.Int).Value = identification.ClientId; } sql += " AND orders.delete_date IS NULL "; sql += "ORDER BY orders.modified_date DESC "; DbCommand.CommandText = sql; DataTable customerDT = DbConnection.ExecuteQuery(DbCommand); foreach (DataRow row in customerDT.Rows) { Order order = new Order(); order.Id = Convert.ToInt32(row["Id"]); order.ParentId = row["parentid"] != DBNull.Value ? (int)row["parentid"] : 0; order.Status = row["status"] != DBNull.Value ? (EnumOrderStatus)row["status"] : EnumOrderStatus.Open; order.PoNumber = row["ponumber"].ToString(); order.OrderTotal = Convert.ToDecimal(row["ChargesTotal"] != DBNull.Value ? (decimal)row["ChargesTotal"] : 0) + Convert.ToDecimal(row["TestsTotal"] != DBNull.Value ? (decimal)row["TestsTotal"] : 0); if (row["received_date"] != DBNull.Value) order.ReceivedDate = (DateTime)row["received_date"]; if (row["start_date"] != DBNull.Value) order.StartDate = (DateTime)row["start_date"]; if (row["due_date"] != DBNull.Value) order.DueDate = (DateTime)row["due_date"]; if (row["report_date"] != DBNull.Value) order.ReportDate = (DateTime)row["report_date"]; order.CreatedUser = row["createduser"].ToString(); order.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0; order.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null; order.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0; order.ModifiedUser = row["modifieduser"].ToString(); order.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null; order.Samples = this.GetSamples(ref dbConnection, ref dbCommand, "", identification); order.SampleTests = this.GetSampleTests(ref dbConnection, ref dbCommand, order.Id, true, identification); using (ClientDAO dao = new ClientDAO()) { order.Client = dao.GetClient(ref dbConnection, ref dbCommand, order.ParentId); } resultList.Add(order); } customerDT = null; } } else { throw new Exception("Unable to Connect"); } } return resultList; } catch { throw; } }
/// <summary> /// Save Sample /// </summary> /// <returns></returns> public int SaveSample(Sample sample, Identification identification) { try { int returnValue = -1; 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 (sample.IsDirty) { if (sample.ARLNumber != 0 && sample.ARLNumber != null) /*SystemDAO.SaveChangeAudit<Order>(ref dbConnection, ref dbCommand, GetSample(sample.ARLNumber, identification), sample, ModuleNames.Samples, sample.Pk, identification.UserId); */ DbCommand.CommandType = CommandType.StoredProcedure; DbCommand.Parameters.Clear(); if (sample.ARLNumber == 0 || sample.ARLNumber == null) { DbCommand.CommandText = "uspInsertSample"; DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = identification.UserId; DbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now; } else { DbCommand.CommandText = "uspUpdateSample"; DbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = sample.ARLNumber; //DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = sample.DeleteDate.HasValue ? sample.DeleteDate.Value : SqlDateTime.Null; DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId; DbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now; } DbCommand.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 100).Value = (sample.Description != null ? sample.Description.Trim() : string.Empty).Trim(); DbCommand.Parameters.Add("@ReceivedDate", System.Data.SqlDbType.DateTime).Value = sample.ReceivedDate.HasValue ? sample.ReceivedDate.Value : SqlDateTime.Null; DbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = sample.ClientId.HasValue ? sample.ClientId.Value : SqlInt32.Null; DbCommand.Parameters.Add("@ClientName", System.Data.SqlDbType.NVarChar, 100).Value = sample.ClientId.HasValue ? sample.Client.ClientName.Trim() : SqlString.Null; DbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)sample.Status; DbCommand.Parameters.Add("@PONumber", System.Data.SqlDbType.NVarChar, 25).Value = sample.PONumber ?? string.Empty; DbCommand.Parameters.Add("@FormulationId", System.Data.SqlDbType.NVarChar, 50).Value = sample.FormulationId != null ? sample.FormulationId.Trim() : SqlString.Null; DbCommand.Parameters.Add("@LotNumber", System.Data.SqlDbType.NVarChar, 50).Value = sample.LotNumber != null ? sample.LotNumber.Trim() : SqlString.Null; DbCommand.Parameters.Add("@ProjectNumber", System.Data.SqlDbType.NVarChar, 50).Value = sample.ProjectNumber != null ? sample.ProjectNumber.Trim() : SqlString.Null; DbCommand.Parameters.Add("@StorageLocationId", System.Data.SqlDbType.Int).Value = sample.StorageLocation.StorageLocationId != null ? (SqlInt32)sample.StorageLocation.StorageLocationId : SqlInt32.Null; DbCommand.Parameters.Add("@StorageLocationName", System.Data.SqlDbType.NVarChar, 50).Value = sample.StorageLocation.StorageLocationId != null ? sample.StorageLocation.Description : SqlString.Null; DbCommand.Parameters.Add("@StorageLocationConditions", System.Data.SqlDbType.NVarChar, 50).Value = sample.StorageLocation.StorageLocationId != null ? sample.StorageLocation.Conditions : SqlString.Null; DbCommand.Parameters.Add("@StorageLocationCode", System.Data.SqlDbType.NVarChar, 50).Value = sample.StorageLocation.StorageLocationId != null ? sample.StorageLocation.LocationCode : SqlString.Null; DbCommand.Parameters.Add("@RequestedStorageId", System.Data.SqlDbType.Int).Value = sample.RequestedStorageLocation.StorageLocationId != null ? (SqlInt32)sample.RequestedStorageLocation.StorageLocationId : SqlInt32.Null; DbCommand.Parameters.Add("@RequestedStorageName", System.Data.SqlDbType.NVarChar, 50).Value = sample.RequestedStorageLocation.StorageLocationId != null ? sample.RequestedStorageLocation.Description : SqlString.Null; DbCommand.Parameters.Add("@DosageId", System.Data.SqlDbType.Int).Value = sample.DosageId ?? SqlInt32.Null; DbCommand.Parameters.Add("@DosageName", System.Data.SqlDbType.NVarChar, 50).Value = sample.DosageId.HasValue ? sample.Dosage.DosageName : SqlString.Null; DbCommand.Parameters.Add("@Containers", System.Data.SqlDbType.Int).Value = sample.Containers ?? SqlInt32.Null; DbCommand.Parameters.Add("@ContainerDescription", System.Data.SqlDbType.NVarChar, 255).Value = sample.ContainerDescription != null ? sample.ContainerDescription : SqlString.Null; DbCommand.Parameters.Add("@VolumeAmount", System.Data.SqlDbType.Decimal).Value = sample.VolumeAmount ?? SqlDecimal.Null; DbCommand.Parameters.Add("@VolumeUOMID", System.Data.SqlDbType.Int).Value = sample.VolumeUnitOfMeasure != null ? (SqlInt32)sample.VolumeUnitOfMeasure.UomId : SqlInt32.Null; DbCommand.Parameters.Add("@VolumeUOM", System.Data.SqlDbType.NVarChar, 50).Value = sample.VolumeUnitOfMeasure != null ? sample.VolumeUnitOfMeasure.Uom : SqlString.Null; DbCommand.Parameters.Add("@TimepointStudyYN", System.Data.SqlDbType.Bit).Value = sample.TimepointStudyYN; DbCommand.Parameters.Add("@GMPYN", System.Data.SqlDbType.Bit).Value = sample.GMPYN; DbCommand.Parameters.Add("@CompoundedBy", System.Data.SqlDbType.NVarChar, 50).Value = sample.CompoundedBy != null ? sample.CompoundedBy : SqlString.Null; DbCommand.Parameters.Add("@CompoundedDate", System.Data.SqlDbType.DateTime).Value = sample.CompoundedDate.HasValue ? sample.CompoundedDate.Value : SqlDateTime.Null; if (sample.ARLNumber > 0) returnValue = DbConnection.ExecuteCommand(DbCommand); else { // returnValue = Primary Key Id returnValue = (int)DbConnection.ExecuteScalar(DbCommand); sample.ARLNumber = returnValue; } } // Save Order Sample Analytes this.SaveSampleAnalytes(ref dbConnection, ref dbCommand, ref sample, identification.UserId); // Save Order Charges this.SaveSampleCharges(ref dbConnection, ref dbCommand, ref sample, identification.UserId); // Save Documents this.SaveSampleDocuments(ref dbConnection, ref dbCommand, sample, identification.UserId); // Save Notes this.SaveSampleNotes(ref dbConnection, ref dbCommand, ref sample, identification); // Save Sample Tests this.SaveSampleTests(ref dbConnection, ref dbCommand, ref sample, identification); // Release Lock using (SystemDAO systemDao = new SystemDAO()) { systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.Sample, sample.ARLNumber.ToString(), identification.Token); } } catch { if (DbConnection.IsConnected() && DbConnection.IsTransaction()) DbConnection.Rollback(); throw; } } } else { throw new Exception("Unable to Connect"); } } return returnValue; } catch { throw; } }
public int SaveReport(ReportRecord report) { int returnValue = -1; try { string sql = string.Empty; using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) { if (DbConnection.IsConnected()) { using (DbCommand) { sql = @" INSERT INTO orders_reports ( parentid, referenceid,departmentid,report_type, report_name, report_data, created_by,created_date) VALUES ( @ParentId,@ReferenceId,@DepartmentId, @ReportType,@ReportName, @ReportData, @CreatedBy, @CreatedDate); SELECT id FROM orders_reports WHERE (id = SCOPE_IDENTITY()) "; DbCommand.Parameters.Clear(); DbCommand.CommandText = sql; DbCommand.Parameters.Add("@ParentId", System.Data.SqlDbType.Int).Value = report.ParentId; DbCommand.Parameters.Add("@ReferenceId", System.Data.SqlDbType.Int).Value = report.ReferenceId; DbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = report.DepartmentId ?? 0; DbCommand.Parameters.Add("@ReportType", System.Data.SqlDbType.VarChar, 3).Value = report.ReportType; DbCommand.Parameters.Add("@ReportName", System.Data.SqlDbType.VarChar, 30).Value = report.ReportName; DbCommand.Parameters.Add("@ReportData", System.Data.SqlDbType.VarBinary, -1).Value = report.ReportData; DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = report.CreatedBy; DbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = report.CreatedDate; returnValue = (int)DbConnection.ExecuteScalar(DbCommand); } } else { throw new Exception("Unable to Connect"); } } } catch { throw; } return returnValue; }
public int SaveOrderSampleTestOos(Oos oos, Identification identification) { try { using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) { if (DbConnection.IsConnected()) { using (DbCommand) { return this.SaveOrderSampleTestOos(ref dbConnection, ref dbCommand, oos, identification); } } else { throw new Exception("Unable to Connect"); } } } 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 Boolean RemoveAllTokens() { Boolean result = false; try { using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sql = "DELETE FROM tokens;"; dbCommand.CommandText = sql; dbConnection.ExecuteCommand(dbCommand); result = true; } } else { throw new Exception("Unable to Connect"); } } return result; } 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; } }
public int SetDatabaseVersion(int currentVersion) { try { int result = 0; using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sql = "UPDATE dbversion " + "SET dbversion = @Version "; DbCommand.CommandText = sql; DbCommand.Parameters.Add("@Version", System.Data.SqlDbType.Int).Value = currentVersion; result = DbConnection.ExecuteCommand(DbCommand); } } 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 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"); } }
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; } }
/// <summary> /// Remove Client /// </summary> /// <returns></returns> public int RemoveClient(int ClientId, int userId) { try { int rowsAffected; using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) { if (DbConnection.IsConnected()) { using (DbCommand) { DbCommand.CommandType = CommandType.StoredProcedure; DbCommand.CommandText = "uspRemoveClient"; DbCommand.Parameters.Clear(); DbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = ClientId; DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId; DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now; rowsAffected = DbConnection.ExecuteCommand(DbCommand); // ToDo Mark all Related Data Deleted using (ClientDAO contactDao = new ClientDAO()) { contactDao.RemoveContacts(ref dbConnection, ref dbCommand, ClientId, userId); } this.RemoveClientPricings(ref dbConnection, ref dbCommand, ClientId, userId); this.RemoveClientNotes(ref dbConnection, ref dbCommand, ClientId, userId); } }else { throw new Exception("Unable to Connect"); } } return rowsAffected; }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 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; } }
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 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 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; } }
/// <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<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 int ReturnToOos(TimePoint timePoint, SampleNote note, Identification identification) { int rowsAffected; using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) { try { if (DbConnection.IsConnected()) { using (DbCommand) { SystemDAO.SaveChangeAudit<TimePoint>(ref dbConnection, ref dbCommand, "Return to OOS #:" + timePoint.OosId.ToString(), "Status", "Approved", "Pending Investigation", ModuleNames.Samples, timePoint.ParentId, timePoint.Pk, identification.UserId); this.SaveSampleNote(ref dbConnection, ref dbCommand, ref note, (int)timePoint.ParentId, identification); string sql = string.Empty; sql = "UPDATE orders_samples_tests_timepoints_oos " + "SET status = @Status " + "WHERE id = @ID"; DbCommand.Parameters.Clear(); DbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = timePoint.OosId; DbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)EnumOosStatus.Open; DbCommand.CommandText = sql; rowsAffected = DbConnection.ExecuteCommand(DbCommand); OrderSampleTestsSetStatus(ref dbConnection, ref dbCommand, (int)timePoint.ParentId, identification); } } else { throw new Exception("Unable to Connect"); } return rowsAffected; } catch { throw; } } }