public SmartCollection<Contact> DoGetContacts(int parentId) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (ClientDAO dao = new ClientDAO()) { return dao.GetContacts(parentId); } }
public string DoGetOpenComplaintsCount(Identification identification) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (ClientDAO dao = new ClientDAO()) { return dao.GetClientComplaintsOpenCount(identification); } }
public SmartCollection<Client> DoGetClients(string clientString) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (ClientDAO dao = new ClientDAO()) { return dao.GetClients(clientString); } }
public SmartCollection<Client> DoGetClientsRecent(int userId) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (ClientDAO dao = new ClientDAO()) { return dao.GetClientsRecent(userId); } }
/// <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 InventoryItem GetInventoryItemRecord(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? id, string useSql = null) { try { InventoryItem item = new InventoryItem(); string sql = useSql != null ? useSql : "SELECT " + baseInventorySql + @" WHERE inventory.id = @ID "; dbCommand.Parameters.Clear(); dbCommand.CommandText = sql; dbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = id; var table = dbConnection.ExecuteQuery(dbCommand); if (table.Rows.Count == 1) { DataRow row = table.Rows[0]; item.Id = Convert.ToInt32(row["Id"]); item.Active = Convert.ToBoolean(row["analyte_active"]); item.ItemType = row["item_type"] != DBNull.Value ? (EnumInventoryType)row["item_type"] : EnumInventoryType.Standard; item.LabNumber = row["lab_number"] != DBNull.Value ? Convert.ToInt32(row["lab_number"]) : 0; item.ClientId = Convert.ToInt32(row["customerid"]); item.AnalyteId = Convert.ToInt32(row["analyteid"]); if (item.AnalyteId > 0) item.AnalyteItem = new Analyte() { AnalyteId = item.AnalyteId, AnalyteName = row["analytename"].ToString(), Active = Convert.ToBoolean(row["analyte_active"]), ControlledYN = Convert.ToBoolean(row["analyte_controlled"]), AntibioticYN = Convert.ToBoolean(row["analyte_antibiotic"]) }; item.StorageId = row["storageid"] != DBNull.Value ? Convert.ToInt32(row["storageid"]) : 0; if (item.StorageId > 0) item.StorageLocation = new StorageLocation() { StorageLocationId = item.StorageId, Active = Convert.ToBoolean(row["storage_active"]), Description = row["storage_description"].ToString(), Conditions = row["storage_conditions"].ToString() }; item.PoNumber = row["po_number"].ToString(); item.Manufacturer = row["manufacturer"].ToString(); item.Lot = row["lot"].ToString(); item.Cas = row["cas"].ToString(); item.Catalog = row["catalog"].ToString(); item.Weight = row["weight"].ToString(); item.VolumeAmount = row["volume_amount"].ToString(); item.OrderedFor = row["ordered_for"].ToString(); item.Potency = Convert.ToDecimal(row["potency"] != DBNull.Value ? (decimal)row["potency"] : 0); item.Cost = Convert.ToDecimal(row["cost"] != DBNull.Value ? (decimal)row["cost"] : 0); item.ClientCost = Convert.ToDecimal(row["customer_cost"] != DBNull.Value ? (decimal)row["customer_cost"] : 0); item.ReceivedDate = row["received_date"] != DBNull.Value ? (DateTime?)row["received_date"] : null; item.DestroyedDate = row["destroyed_date"] != DBNull.Value ? (DateTime?)row["destroyed_date"] : null; item.ExpirationDate = row["expiration_date"] != DBNull.Value ? (DateTime?)row["expiration_date"] : null; item.CreatedUser = row["createduser"].ToString(); item.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0; item.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime?)row["created_date"] : null; item.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0; item.ModifiedUser = row["modifieduser"].ToString(); item.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime?)row["modified_date"] : null; using (ClientDAO dao = new ClientDAO()) { item.Client = dao.GetClient(ref dbConnection, ref dbCommand, item.ClientId); } } return item; } catch { throw; } }
public Client DoGetClient(int id, Guid userToken) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (ClientDAO dao = new ClientDAO()) { return dao.GetClient(id); } }
public int DoSaveClientComplaint(ClientComplaint complaint, Identification identification) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); //Validate Complaint Object if (!complaint.ValidateModel()) throw new FaultException<ServiceFault>(new ServiceFault(complaint.CurrentErrors), new FaultReason(SysVars.InvalidFormat)); using (ClientDAO dao = new ClientDAO()) { return dao.SaveClientComplaint(complaint, identification); } }
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; } }
private SmartCollection<Sample> GetSamples(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, string searchString, Identification identification) { try { SmartCollection<Sample> resultList = new SmartCollection<Sample>(); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "uspGetSamples"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@SearchString", System.Data.SqlDbType.NVarChar, 100).Value = searchString; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { Sample sample = new Sample(); sample.ARLNumber = Convert.ToInt32(row["ARLNumber"]); sample.Description = row["Description"].ToString(); sample.ReceivedDate = row["ReceivedDate"] != DBNull.Value ? Convert.ToDateTime(row["ReceivedDate"]) : new DateTime?(); sample.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : new Int32(); sample.ClientName = row["ClientName"].ToString(); if (sample.ClientId > -1) { //sample.Client = new Client() { ClientId = (int)sample.ClientId, ClientName = sample.ClientName }; using (ClientDAO clientDao = new ClientDAO()) { sample.Client = clientDao.GetClient((int)sample.ClientId); } } sample.Status = row["Status"] != DBNull.Value ? (EnumSampleStatus)row["Status"] : EnumSampleStatus.InProgress; sample.PONumber = row["PONumber"].ToString(); sample.FormulationId = row["FormulationID"].ToString(); sample.LotNumber = row["LotNumber"].ToString(); sample.ProjectNumber = row["ProjectNumber"].ToString(); sample.StorageLocationId = row["StorageLocationID"] != DBNull.Value ? Convert.ToInt32(row["StorageLocationID"]) : new Int32(); sample.StorageLocationName = row["StorageLocationName"].ToString(); sample.StorageLocationConditions = row["StorageLocationConditions"].ToString(); sample.StorageLocationCode = row["StorageLocationCode"].ToString(); if (sample.StorageLocationId > -1) sample.StorageLocation = new StorageLocation() { StorageLocationId = sample.StorageLocationId, Active = true, Description = row["StorageLocationName"].ToString(), Conditions = row["StorageLocationConditions"].ToString(), LocationCode = row["StorageLocationCode"].ToString() }; sample.RequestedStorageId = row["RequestedStorageID"] != DBNull.Value ? Convert.ToInt32(row["RequestedStorageID"]) : new Int32(); sample.RequestedStorageName = row["RequestedStorageName"].ToString(); if (sample.RequestedStorageId > -1) sample.RequestedStorageLocation = new StorageLocation() { StorageLocationId = sample.RequestedStorageId, Active = true, Description = row["RequestedStorageName"].ToString(), Conditions = "" }; sample.DosageId = row["DosageID"] != DBNull.Value ? Convert.ToInt32(row["DosageID"]) : new Int32(); sample.DosageName = row["DosageName"] != DBNull.Value ? row["DosageName"].ToString() : null; if (sample.DosageId > -1) sample.Dosage = new Dosage() { DosageId = sample.DosageId, Active = true, DosageName = row["DosageName"].ToString() }; sample.Containers = row["Containers"] != DBNull.Value ? Convert.ToInt32(row["Containers"]) : new Int32(); sample.ContainerDescription = row["ContainerDescription"].ToString(); sample.VolumeAmount = row["VolumeAmount"] != DBNull.Value ? Convert.ToDecimal(row["VolumeAmount"]) : new Decimal(); sample.VolumeUOMId = row["VolumeUOMID"] != DBNull.Value ? Convert.ToInt32(row["VolumeUOMID"]) : new Int32(); sample.VolumeUOM = row["VolumeUOM"].ToString(); sample.TimepointStudyYN = row["TimepointStudyYN"] != DBNull.Value ? Convert.ToBoolean(row["TimepointStudyYN"]) : false; sample.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false; sample.CompoundedBy = row["CompoundedBy"].ToString(); sample.CompoundedDate = row["CompoundedDate"] != DBNull.Value ? Convert.ToDateTime(row["CompoundedDate"]) : new DateTime?(); sample.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32(); sample.CreatedUser = row["CreatedUser"].ToString(); sample.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null; sample.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32(); sample.ModifiedUser = row["ModifiedUser"].ToString(); sample.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null; sample.SampleAnalytes = this.GetSampleAnalytes((int)sample.ARLNumber); sample.SampleCharges = this.GetSampleCharges((int)sample.ARLNumber); sample.SampleDocuments = this.GetSampleDocuments((int)sample.ARLNumber); sample.SampleNotes = this.GetSampleNotes((int)sample.ARLNumber, identification); sample.SampleTests = this.GetSampleTests((int)sample.ARLNumber, false, identification); resultList.Add(sample); } returnDT = null; 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 Sample GetSample(int? arlNumber, Identification identification) { try { Sample sample = new Sample(); DbCommand.CommandType = CommandType.StoredProcedure; DbCommand.CommandText = "uspGetSample"; DbCommand.Parameters.Clear(); DbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber; DataTable returnDT = DbConnection.ExecuteQuery(dbCommand); if (returnDT.Rows.Count > 0) { DataRow row = returnDT.Rows[0]; sample.ARLNumber = Convert.ToInt32(row["ARLNumber"]); sample.Description = row["Description"].ToString(); sample.ReceivedDate = row["ReceivedDate"] != DBNull.Value ? Convert.ToDateTime(row["ReceivedDate"]) : new DateTime?(); sample.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : new Int32(); sample.ClientName = row["ClientName"].ToString(); if (sample.ClientId > -1) { //sample.Client = new Client() { ClientId = (int)sample.ClientId, ClientName = sample.ClientName }; using (ClientDAO clientDao = new ClientDAO()) { sample.Client = clientDao.GetClient((int)sample.ClientId); } } sample.Status = row["Status"] != DBNull.Value ? (EnumSampleStatus)row["Status"] : EnumSampleStatus.InProgress; sample.PONumber = row["PONumber"].ToString(); sample.FormulationId = row["FormulationID"].ToString(); sample.LotNumber = row["LotNumber"].ToString(); sample.ProjectNumber = row["ProjectNumber"].ToString(); sample.StorageLocationId = row["StorageLocationID"] != DBNull.Value ? Convert.ToInt32(row["StorageLocationID"]) : new Int32(); sample.StorageLocationName = row["StorageLocationName"].ToString(); sample.StorageLocationConditions = row["StorageLocationConditions"].ToString(); sample.StorageLocationCode = row["StorageLocationCode"].ToString(); if (sample.StorageLocationId > -1) sample.StorageLocation = new StorageLocation() { StorageLocationId = sample.StorageLocationId, Active = true, Description = row["StorageLocationName"].ToString(), Conditions = row["StorageLocationConditions"].ToString(), LocationCode = row["StorageLocationCode"].ToString() }; sample.RequestedStorageId = row["RequestedStorageID"] != DBNull.Value ? Convert.ToInt32(row["RequestedStorageID"]) : new Int32(); sample.RequestedStorageName = row["RequestedStorageName"].ToString(); if (sample.RequestedStorageId > -1) sample.RequestedStorageLocation = new StorageLocation() { StorageLocationId = sample.RequestedStorageId, Active = true, Description = row["RequestedStorageName"].ToString(), Conditions = "" }; sample.DosageId = row["DosageID"] != DBNull.Value ? Convert.ToInt32(row["DosageID"]) : new Int32(); sample.DosageName = row["DosageName"] != DBNull.Value ? row["DosageName"].ToString() : null; if (sample.DosageId > -1) sample.Dosage = new Dosage() { DosageId = sample.DosageId, Active = true, DosageName = row["DosageName"].ToString() }; sample.Containers = row["Containers"] != DBNull.Value ? Convert.ToInt32(row["Containers"]) : new Int32(); sample.ContainerDescription = row["ContainerDescription"].ToString(); if (sample.ContainerDescription != null) sample.Container = new Container() { Description = sample.ContainerDescription }; sample.VolumeAmount = row["VolumeAmount"] != DBNull.Value ? Convert.ToDecimal(row["VolumeAmount"]) : new Decimal(); sample.VolumeUOMId = row["VolumeUOMID"] != DBNull.Value ? Convert.ToInt32(row["VolumeUOMID"]) : new Int32(); sample.VolumeUOM = row["VolumeUOM"].ToString(); if (sample.VolumeUOMId != null) sample.VolumeUnitOfMeasure = new UnitOfMeasure() { UomId = sample.VolumeUOMId, Uom = sample.VolumeUOM }; sample.TimepointStudyYN = row["TimepointStudyYN"] != DBNull.Value ? Convert.ToBoolean(row["TimepointStudyYN"]) : false; sample.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false; sample.CompoundedBy = row["CompoundedBy"].ToString(); sample.CompoundedDate = row["CompoundedDate"] != DBNull.Value ? Convert.ToDateTime(row["CompoundedDate"]) : new DateTime?(); sample.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32(); sample.CreatedUser = row["CreatedUser"].ToString(); sample.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null; sample.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32(); sample.ModifiedUser = row["ModifiedUser"].ToString(); sample.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null; sample.SampleAnalytes = this.GetSampleAnalytes((int)sample.ARLNumber); sample.SampleCharges = this.GetSampleCharges((int)sample.ARLNumber); sample.SampleDocuments = this.GetSampleDocuments((int)sample.ARLNumber); sample.SampleNotes = this.GetSampleNotes((int)sample.ARLNumber, identification); sample.SampleTests = this.GetSampleTests((int)sample.ARLNumber, false, identification); } returnDT = null; return sample; } catch { throw; } }
public SmartCollection<InventoryItem> SearchInventory(string searchString, IEnumerable<ISearchItem> searchItems, Identification identification) { try { SmartCollection<InventoryItem> resultList = new SmartCollection<InventoryItem>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sql = @" SELECT DISTINCT " + baseInventorySql + SysLib.BuildSearchAllWhereClause(searchString, searchItems); sql += " AND inventory.delete_date IS NULL "; sql += "ORDER BY inventory.modified_date DESC "; DbCommand.CommandText = sql; var table = DbConnection.ExecuteQuery(DbCommand); foreach (DataRow row in table.Rows) { InventoryItem item = new InventoryItem(); item.Id = Convert.ToInt32(row["Id"]); item.Active = Convert.ToBoolean(row["analyte_active"]); item.ItemType = row["item_type"] != DBNull.Value ? (EnumInventoryType)row["item_type"] : EnumInventoryType.Standard; item.LabNumber = row["lab_number"] != DBNull.Value ? Convert.ToInt32(row["lab_number"]) : 0; item.ClientId = Convert.ToInt32(row["customerid"]); item.AnalyteId = Convert.ToInt32(row["analyteid"]); if (item.AnalyteId > 0) item.AnalyteItem = new Analyte() { AnalyteId = item.AnalyteId, AnalyteName = row["analytename"].ToString(), Active = Convert.ToBoolean(row["analyte_active"]), ControlledYN = Convert.ToBoolean(row["analyte_controlled"]), AntibioticYN = Convert.ToBoolean(row["analyte_antibiotic"]) }; item.StorageId = row["storageid"] != DBNull.Value ? Convert.ToInt32(row["storageid"]) : 0; if (item.StorageId > 0) item.StorageLocation = new StorageLocation() { StorageLocationId = item.StorageId, Active = Convert.ToBoolean(row["storage_active"]), Description = row["storage_description"].ToString(), Conditions = row["storage_conditions"].ToString() }; item.PoNumber = row["po_number"].ToString(); item.Manufacturer = row["manufacturer"].ToString(); item.Lot = row["lot"].ToString(); item.Cas = row["cas"].ToString(); item.Catalog = row["catalog"].ToString(); item.Weight = row["weight"].ToString(); item.VolumeAmount = row["volume_amount"].ToString(); item.OrderedFor = row["ordered_for"].ToString(); item.Potency = Convert.ToDecimal(row["potency"] != DBNull.Value ? (decimal)row["potency"] : 0); item.Cost = Convert.ToDecimal(row["cost"] != DBNull.Value ? (decimal)row["cost"] : 0); item.ClientCost = Convert.ToDecimal(row["customer_cost"] != DBNull.Value ? (decimal)row["customer_cost"] : 0); item.ReceivedDate = row["received_date"] != DBNull.Value ? (DateTime?)row["received_date"] : null; item.DestroyedDate = row["destroyed_date"] != DBNull.Value ? (DateTime?)row["destroyed_date"] : null; item.ExpirationDate = row["expiration_date"] != DBNull.Value ? (DateTime?)row["expiration_date"] : null; item.CreatedUser = row["createduser"].ToString(); item.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0; item.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime?)row["created_date"] : null; item.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0; item.ModifiedUser = row["modifieduser"].ToString(); item.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime?)row["modified_date"] : null; using (ClientDAO dao = new ClientDAO()) { item.Client = dao.GetClient(ref dbConnection, ref dbCommand, item.ClientId); } resultList.Add(item); } } } else { throw new Exception("Unable to Connect"); } } return resultList; } catch { throw; } }
public SmartCollection<ClientComplaint> DoGetPendingComplaints(Identification identification) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (ClientDAO dao = new ClientDAO()) { return dao.GetClientComplaintsOpen(identification); } }
public byte[] DoGetClientDocumentData(int documentId) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (var dao = new ClientDAO()) { return dao.GetClientDocumentData(documentId); } }
public int DoRemoveCustomerNote(int id, Identification identification) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (ClientDAO dao = new ClientDAO()) { return dao.RemoveClientNote(id, identification.UserId); } }
public ClientPricing DoGetClientPricing(int clientPricingId) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (ClientDAO dao = new ClientDAO()) { return dao.GetClientPricing(clientPricingId); } }
public int DoCreateCoaReport(Sample sample, Identification identification) { ReportRecord reportRecord = null; logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (SampleDAO dao = new SampleDAO()) { reportRecord = dao.CreateCoaReport(sample, identification); } if (reportRecord.Id.IsNotNull() && reportRecord.Id > 0) { using (ClientDAO customerDao = new ClientDAO()) { reportRecord.ParentId = customerDao.GetClientId(reportRecord.ParentId.Value); } using (ReportDAO reportDao = new ReportDAO()) { ReportNotification reportNotification = new ReportNotification(); reportNotification.ReportId = reportRecord.Id.Value; reportNotification.CustomerId = reportRecord.ParentId.Value; reportNotification.DepartmentId = reportRecord.DepartmentId.Value; reportNotification.SubjectLine = reportRecord.SubjectLine; reportDao.SaveNotificationReports(reportDao.GetNotificationRecords(reportNotification)); } } return reportRecord.Id.Value; }
public SmartCollection<ClientPricing> DoGetClientPricings(int clientId) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (ClientDAO dao = new ClientDAO()) { return dao.GetClientPricings(clientId); } }
/// <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 void ProcessNotifications() { if (!AppVars.ReportNotificationsEnabled) return; if (SysVars.isConsole) Console.WriteLine("Processing Notifications"); ReportNotificationsActive = true; List<ReportNotification> reportNotifications = this.GetReportNotifications(); if (reportNotifications.Count > 0) { foreach (ReportNotification item in reportNotifications) { ContactNotify notify = null; using (ClientDAO cDao = new ClientDAO()) { notify = null; } //cDao.GetContact(item.NotificationId);} item.NotifyContact = notify; ReportRecord report = new ReportRecord(); using (SampleDAO oDao = new SampleDAO()) { report = oDao.GetOrdersReport(item.ReportId, null); } // Email if (notify.IsFax == false) { this.SendReportEmail(notify, item, report); } //Fax if (notify.IsFax == true) { this.ReportSendFaxAsync(notify, item, report); } } } ReportNotificationsActive = false; }