public void ResetTest() { var sc = new SmartCollection <TestItem, object>(new object()); var si1 = new TestItem(); var si2 = new TestItem(); var si3 = new TestItem(); sc.Add(si1); si2.Collection = sc; sc.Add(si3); Assert.AreEqual(sc, si1.Collection); Assert.AreEqual(sc, si2.Collection); Assert.AreEqual(sc, si3.Collection); Assert.IsTrue(sc.Contains(si1)); Assert.IsTrue(sc.Contains(si2)); Assert.IsTrue(sc.Contains(si3)); sc.Clear(); Assert.AreEqual(null, si1.Collection); Assert.AreEqual(null, si2.Collection); Assert.AreEqual(null, si3.Collection); Assert.IsFalse(sc.Contains(si1)); Assert.IsFalse(sc.Contains(si2)); Assert.IsFalse(sc.Contains(si3)); }
public SmartCollection<User> GetData(string token) { this.Token = token; if (AppLib.DesignMode) { SmartCollection<User> users = new SmartCollection<User>(); users.Add( new User() { UserId = 0, FirstName = "Jens", LastName = "Serna", Email = "*****@*****.**", Username = "******" }); return users; }else { try { using (UserDAO usersDao = new UserDAO()) { return usersDao.GetUsers(); } }catch (Exception) { throw; } } }
public override async void Execute(object parameter) { var items = (ObservableCollection <IVLCStorageItem>)parameter; var files = new List <VLCStorageFile>(); foreach (var item in items) { var file = item as VLCStorageFile; if (file == null) { continue; } if (file.Media != null || VLCFileExtensions.Supported.Contains((file.StorageItem as StorageFile)?.FileType.ToLower())) { files.Add((VLCStorageFile)item); } } var playlist = new SmartCollection <IMediaItem>(); foreach (var file in files) { if (VLCFileExtensions.AudioExtensions.Contains((file.StorageItem as StorageFile)?.FileType.ToLower())) { var trackItem = await Locator.MediaLibrary.GetTrackItemFromFile(file.StorageItem as StorageFile); playlist.Add(trackItem); } else if (VLCFileExtensions.VideoExtensions.Contains((file.StorageItem as StorageFile)?.FileType.ToLower())) { var video = await MediaLibraryHelper.GetVideoItem(file.StorageItem as StorageFile); playlist.Add(video); } else { var stream = MediaLibraryHelper.GetStreamItem(file); playlist.Add(stream); } } if (playlist.Count > 0) { Locator.NavigationService.GoOnPlaybackStarted(VLCPage.MusicPlayerPage); await Locator.PlaybackService.SetPlaylist(playlist); } }
private async Task RestorePlaylist() { try { var playlist = await BackgroundTrackRepository.LoadPlaylist(); if (!playlist.Any()) { return; } var trackIds = playlist.Select(node => node.TrackId); var restoredplaylist = new SmartCollection <IMediaItem>(); foreach (int trackId in trackIds) { var trackItem = await Locator.MediaLibrary.LoadTrackById(trackId); if (trackItem != null) { restoredplaylist.Add(trackItem); } } if (!ApplicationSettingsHelper.Contains(nameof(CurrentMedia))) { return; } var index = (int)ApplicationSettingsHelper.ReadSettingsValue(nameof(CurrentMedia)); if (restoredplaylist.Any()) { if (index == -1) { // Background Audio was terminated // We need to reset the playlist, or set the current track 0. ApplicationSettingsHelper.SaveSettingsValue(nameof(CurrentMedia), 0); index = 0; } SetCurrentMediaPosition(index); } if (CurrentMedia >= restoredplaylist.Count || CurrentMedia == -1) { CurrentMedia = 0; } if (restoredplaylist.Any()) { await SetPlaylist(restoredplaylist, true, false, restoredplaylist[CurrentMedia]); } } catch (Exception e) { Debug.WriteLine("Failed to restore the playlist"); } }
public void OneItemOneCollectionAddTest() { var sc = new SmartCollection <TestItem, object>(new object()); var si = new TestItem(); Assert.AreEqual(null, si.Collection); sc.Add(si); Assert.AreEqual(sc, si.Collection); Assert.IsTrue(sc.Contains(si)); }
public void OneItemTwoCollectionsAddTest() { var sc1 = new SmartCollection <TestItem, object>(new object()); var sc2 = new SmartCollection <TestItem, object>(new object()); var si = new TestItem(); sc1.Add(si); sc2.Add(si); Assert.AreEqual(sc2, si.Collection); Assert.IsFalse(sc1.Contains(si)); Assert.IsTrue(sc2.Contains(si)); }
public void Restore() { if (!ApplicationSettingsHelper.Contains(nameof(Index))) { return; } var playlist = BackgroundTrackRepository.LoadPlaylist(); if (!playlist.Any()) { return; } var trackIds = playlist.Select(node => node.TrackId); var restoredplaylist = new SmartCollection <IMediaItem>(); foreach (int trackId in trackIds) { var trackItem = Locator.MediaLibrary.LoadTrackById(trackId); if (trackItem != null) { restoredplaylist.Add(trackItem); } } if (restoredplaylist.Count == 0) { return; } clear(); _playlist = restoredplaylist; OnPlaylistChanged?.Invoke(); _index = (int)ApplicationSettingsHelper.ReadSettingsValue(nameof(Index)); OnCurrentMediaChanged?.Invoke(_playlist[_index], true); }
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<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; } }
private void ReadRawMessage(string rawMessage) { if (String.IsNullOrWhiteSpace(rawMessage)) { return; } lock ( lockRawMessage ) { const string jsonArgsRe = @".*args"":\[""(.*?)""\]}$"; if (rawMessage.Equals("1::")) { Chat.Status.IsConnected = true; } else if (rawMessage.Equals("2::")) { Thread.Sleep(random.Next(100, 1000)); webSocket.Send("2::"); return; } if (rawMessage.Contains(@":""message")) { var json = Re.GetSubString(rawMessage, jsonArgsRe); if (json == null) { return; } dynamic msg = this.With(x => JToken.Parse(json.Replace(@"\""", @"""").Replace(@"\\", @"\"))) .With(x => x.Value <dynamic>("params")); if (msg == null) { return; } if (rawMessage.Contains(@":\""loginMsg")) { timerEveryMinute.Change(500, 60000); var role = (string)msg.role; switch (role.ToLower()) { case "guest": if (!Chat.IsAnonymous) { Chat.Status.IsLoggedIn = false; if (!Chat.Status.IsLoginFailed) { Chat.Status.IsConnected = false; Chat.Status.IsLoggedIn = false; Chat.Status.IsLoginFailed = true; Chat.Status.IsStarting = false; Chat.Config.SetParameterValue("AuthToken", String.Empty); Chat.Restart(); } } else { Chat.Status.IsLoginFailed = false; } break; case "admin": { Chat.Status.IsLoggedIn = true; Chat.Status.IsLoginFailed = false; } break; case "anon": { Chat.Status.IsLoggedIn = true; Chat.Status.IsLoginFailed = false; } break; default: break; } var authToken = Chat.Config.GetParameterValue("AuthToken") as string; SendCredentials(Chat.NickName, ChannelName, authToken); } else if (rawMessage.Contains(@":\""chatMsg")) { var nickName = (string)msg.name; var text = (string)msg.text; if (String.IsNullOrWhiteSpace(nickName) || String.IsNullOrWhiteSpace(text)) { return; } if (ReadMessage != null) { ReadMessage(new ChatMessage() { Channel = ChannelName, ChatIconURL = Chat.IconURL, ChatName = Chat.ChatName, FromUserName = nickName, HighlyImportant = false, IsSentByMe = false, Text = text }); } } else if (rawMessage.Contains(@":\""userList")) { var data = msg.data; var guestsNumber = this.With(x => data.Guests as JArray).With(x => x.ToObject <string[]>()); var admins = this.With(x => data.admin as JArray).With(x => x.ToObject <string[]>()); var moderators = this.With(x => data.user as JArray).With(x => x.ToObject <string[]>()); var users = this.With(x => data.anon as JArray).With(x => x.ToObject <string[]>()); var followers = this.With(x => data.isFollower as JArray).With(x => x.ToObject <string[]>()); var subscribers = this.With(x => data.isSubscriber as JArray).With(x => x.ToObject <string[]>()); var staff = this.With(x => data.isStaff as JArray).With(x => x.ToObject <string[]>()); currentUserList.Clear(); foreach (var pair in new Dictionary <string, string[]> { { "Staff", staff }, { "Admins", admins }, { "Moderators", moderators }, { "Subscribers", subscribers }, { "Followers", followers }, { "Users", users } }) { if (pair.Value == null) { continue; } foreach (string userNickname in pair.Value) { currentUserList.Add(new ChatUser() { Channel = ChannelName, ChatName = Chat.ChatName, GroupName = pair.Key, NickName = userNickname, Badges = null, }); } } var oldUserList = (Chat as IChatUserList).ChatUsers; //Delete disconnected users UI.Dispatch(() => { oldUserList.ToList().Where(item => item.Channel.Equals(ChannelName) && item.ChatName.Equals(Chat.ChatName)) .Except(currentUserList, new LambdaComparer <ChatUser>((x, y) => x.NickName.Equals(y.NickName))) .ToList() .ForEach(item => oldUserList.Remove(item)); }); var newUserList = currentUserList .Where(item => item.Channel.Equals(ChannelName) && item.ChatName.Equals(Chat.ChatName)) .Except(oldUserList, new LambdaComparer <ChatUser>((x, y) => x.NickName.Equals(y.NickName))) .ToList(); lock (chatUsersLock) (Chat as IChatUserList).ChatUsers.AddRange(newUserList); newUserList = null; } } } }
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; }
private SmartCollection<Submission> GetWebSubmissions(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification) { try { SmartCollection<Submission> resultList = new SmartCollection<Submission>(); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.Parameters.Clear(); dbCommand.CommandText = "uspGetWebSubmissions"; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { Submission submission = new Submission(); submission.SubmissionID = Convert.ToInt32(row["RequestID"]); submission.ReceivedDate = Convert.ToDateTime(row["SubmissionDate"]); submission.ConfirmationString = row["ConfirmationString"] != null ? row["ConfirmationString"].ToString() : null; submission.Description = row["DrugDescription"] != null ? row["DrugDescription"].ToString() : null; submission.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : 0; submission.ClientName = row["Customer"].ToString(); submission.Client = row["Customer"].ToString() + " (" + submission.ClientId.ToString() + ")"; submission.RequestedStorageName = row["StorageName"] != null ? row["StorageName"].ToString() : null; submission.PONumber = row["PONumber"] != null ? row["PONumber"].ToString() : null; submission.LotNumber = row["LotNumber"] != null ? row["LotNumber"].ToString() : null; submission.CreatedUser = row["FirstName"].ToString() + " " + row["LastName"].ToString(); submission.CreatedDate = DateTime.Now; submission.ModifiedUser = row["FirstName"].ToString() + " " + row["LastName"].ToString(); submission.ModifiedDate = DateTime.Now; submission.Comments = row["Comments"] != null ? row["Comments"].ToString() : null; submission.ImportedYN = row["ImportedYN"] != DBNull.Value ? Convert.ToBoolean(row["ImportedYN"]) : false; submission.ARLNumber = row["ARLNumber"] != null ? row["ARLNumber"].ToString() : null; submission.Tests = row["Tests"] != null ? row["Tests"].ToString() : null; resultList.Add(submission); } return resultList; } catch { throw; } }
public string CreateMultiSampleInvoice(string arlNumbers, Identification identification) { int invoiceNumber = 0; List<string> tests = new List<string>(); string testIds = null; List<string> charges = new List<string>(); string chargeIds = null; try { //Data for invoice DbCommand.CommandType = CommandType.StoredProcedure; DbCommand.CommandText = "uspGetMultipleInvoicableSamples"; DbCommand.Parameters.Clear(); DbCommand.Parameters.Add("@ARLNumbers", System.Data.SqlDbType.NVarChar, 4000).Value = arlNumbers; DataTable returnDT = DbConnection.ExecuteQuery(DbCommand); Invoice invoice = new Invoice(); foreach (DataRow row in returnDT.Rows) { invoice.ARLNumber = Convert.ToInt32(row["ARLNumber"]); invoice.ClientName = row["ClientName"].ToString(); invoice.AccountingId = row["AccountingId"].ToString(); invoice.PONumber = row["PONumber"].ToString(); invoice.TermName = row["TermName"].ToString(); invoice.IncludeYN = (bool)false; } DbCommand.CommandType = CommandType.StoredProcedure; DbCommand.CommandText = "uspGetMultipleInvoicableSampleItems"; DbCommand.Parameters.Clear(); DbCommand.Parameters.Add("@ARLNumbers", System.Data.SqlDbType.NVarChar, 4000).Value = arlNumbers; DataTable returnDT2 = DbConnection.ExecuteQuery(dbCommand); SmartCollection<InvoiceItem> itemList = new SmartCollection<InvoiceItem>(); foreach (DataRow row in returnDT2.Rows) { InvoiceItem invoiceItem = new InvoiceItem(); invoiceItem.AccountingCode = row["AccountingCode"].ToString(); invoiceItem.Class = row["DepartmentName"].ToString(); invoiceItem.Other1 = row["TestName"].ToString(); invoiceItem.Other2 = row["AnalystName"].ToString(); invoiceItem.Description = row["Description"].ToString(); if (row["BasePrice"] != DBNull.Value) invoiceItem.Amount = Convert.ToDouble(row["BasePrice"]); if (row["Ordering"].ToString() != "5" && !tests.Contains(row["SampleTestId"].ToString())) tests.Add(row["SampleTestId"].ToString()); if (row["Ordering"].ToString() == "5" && !charges.Contains(row["SampleTestId"].ToString())) charges.Add(row["SampleTestId"].ToString()); itemList.Add(invoiceItem); } invoice.InvoiceItems = itemList; //SessionManager sessionManager = SessionManager.getInstance(); QBSessionManager sessionManager = new QBSessionManager(); sessionManager.OpenConnection2("", "LIMS2", ENConnectionType.ctLocalQBD); sessionManager.BeginSession("", ENOpenMode.omDontCare); //IMsgSetRequest requestMsgSet = sessionManager.getMsgSetRequest(); //IMsgSetRequest requestMsgSet = sessionManager.CreateMsgSetRequest("US", 13, 0); IMsgSetRequest requestMsgSet = getLatestMsgSetRequest(sessionManager); requestMsgSet.Attributes.OnError = ENRqOnError.roeContinue; // ERROR RECOVERY: // All steps are described in QBFC Developers Guide, on pg 41 // under section titled "Automated Error Recovery" // (1) Set the error recovery ID using ErrorRecoveryID function // Value must be in GUID format // You could use c:\Program Files\Microsoft Visual Studio\Common\Tools\GuidGen.exe // to create a GUID for your unique ID string errecid = "{C5F2272F-4DAD-435F-8ABB-EB1AC0802D52}"; sessionManager.ErrorRecoveryID.SetValue(errecid); // (2) Set EnableErrorRecovery to true to enable error recovery sessionManager.EnableErrorRecovery = true; // (3) Set SaveAllMsgSetRequestInfo to true so the entire contents of the MsgSetRequest // will be saved to disk. If SaveAllMsgSetRequestInfo is false (default), only the // newMessageSetID will be saved. sessionManager.SaveAllMsgSetRequestInfo = true; // (4) Use IsErrorRecoveryInfo to check whether an unprocessed response exists. // If IsErrorRecoveryInfo is true: if (sessionManager.IsErrorRecoveryInfo()) { //string reqXML; //string resXML; IMsgSetRequest reqMsgSet = null; IMsgSetResponse resMsgSet = null; // a. Get the response status, using GetErrorRecoveryStatus resMsgSet = sessionManager.GetErrorRecoveryStatus(); // resXML = resMsgSet.ToXMLString(); // MessageBox.Show(resXML); if (resMsgSet.Attributes.MessageSetStatusCode.Equals("600")) { // This case may occur when a transaction has failed after QB processed // the request but client app didn't get the response and started with // another company file. // MessageBox.Show("The oldMessageSetID does not match any stored IDs, and no newMessageSetID is provided."); int i = 0; } else if (resMsgSet.Attributes.MessageSetStatusCode.Equals("9001")) { // MessageBox.Show("Invalid checksum. The newMessageSetID specified, matches the currently stored ID, but checksum fails."); int i = 0; } else if (resMsgSet.Attributes.MessageSetStatusCode.Equals("9002")) { // Response was not successfully stored or stored properly // MessageBox.Show("No stored response was found."); int i = 0; } // 9003 = Not used else if (resMsgSet.Attributes.MessageSetStatusCode.Equals("9004")) { // MessageSetID is set with a string of size > 24 char //MessageBox.Show("Invalid MessageSetID, greater than 24 character was given."); int i = 0; } else if (resMsgSet.Attributes.MessageSetStatusCode.Equals("9005")) { //MessageBox.Show("Unable to store response."); int i = 0; } else { IResponse res = resMsgSet.ResponseList.GetAt(0); int sCode = res.StatusCode; //string sMessage = res.StatusMessage; //string sSeverity = res.StatusSeverity; //MessageBox.Show("StatusCode = " + sCode + "\n" + "StatusMessage = " + sMessage + "\n" + "StatusSeverity = " + sSeverity); if (sCode == 0) { //MessageBox.Show("Last request was processed and Invoice was added successfully!"); int i = 0; } else if (sCode > 0) { //MessageBox.Show("There was a warning but last request was processed successfully!"); int i = 0; } else { //MessageBox.Show("It seems that there was an error in processing last request"); // b. Get the saved request, using GetSavedMsgSetRequest reqMsgSet = sessionManager.GetSavedMsgSetRequest(); //reqXML = reqMsgSet.ToXMLString(); //MessageBox.Show(reqXML); // c. Process the response, possibly using the saved request resMsgSet = sessionManager.DoRequests(reqMsgSet); IResponse resp = resMsgSet.ResponseList.GetAt(0); int statCode = resp.StatusCode; if (statCode == 0) { string resStr = null; IInvoiceRet invRet = resp.Detail as IInvoiceRet; resStr = resStr + "Following invoice has been successfully submitted to QuickBooks:\n\n\n"; if (invRet.TxnNumber != null) resStr = resStr + "Txn Number = " + Convert.ToString(invRet.TxnNumber.GetValue()) + "\n"; } // if (statusCode == 0) } // else (sCode) } // else (MessageSetStatusCode) // d. Clear the response status, using ClearErrorRecovery sessionManager.ClearErrorRecovery(); //MessageBox.Show("Proceeding with current transaction."); } IInvoiceAdd invAdd = requestMsgSet.AppendInvoiceAddRq(); invAdd.TemplateRef.FullName.SetValue("Copy 2 : Intuit Service Invoice"); // CustomerRef -> FullName if (invoice.ClientName != "") { invAdd.CustomerRef.FullName.SetValue(invoice.AccountingId); } // PO Number if (invoice.PONumber != "") { invAdd.PONumber.SetValue(invoice.PONumber); } // Terms if (invoice.TermName != "") { invAdd.TermsRef.FullName.SetValue(invoice.TermName); DateTime DT_DueDate = System.DateTime.Today; double dueInDays = getDueInDays(invoice.TermName); DT_DueDate = DT_DueDate.AddDays(dueInDays); invAdd.DueDate.SetValue(DT_DueDate); } // TxnDate DateTime DT_TxnDate = System.DateTime.Today; invAdd.TxnDate.SetValue(DT_TxnDate); // CustomerMsgRef -> FullName invAdd.CustomerMsgRef.FullName.SetValue("Thank you for your business."); //Line Items foreach (InvoiceItem item in invoice.InvoiceItems) { IORInvoiceLineAdd invLineAdd = invAdd.ORInvoiceLineAddList.Append(); if (item.AccountingCode != "") { invLineAdd.InvoiceLineAdd.ItemRef.FullName.SetValue(item.AccountingCode); } if (item.Class != "") { invLineAdd.InvoiceLineAdd.ClassRef.FullName.SetValue(item.Class); } if (item.Other1 != "") { invLineAdd.InvoiceLineAdd.Other1.SetValue(item.Other1); } if (item.Other2 != "") { invLineAdd.InvoiceLineAdd.Other2.SetValue(item.Other2); } if (item.Description != "") { invLineAdd.InvoiceLineAdd.Desc.SetValue(item.Description); } //invLineAdd.InvoiceLineAdd. if (item.AccountingCode != "DISCOUNT") invLineAdd.InvoiceLineAdd.Quantity.SetValue(Convert.ToDouble(1)); if (item.Amount.IsNotNull()) { invLineAdd.InvoiceLineAdd.ORRatePriceLevel.Rate.SetValue(Convert.ToDouble(item.Amount)); } if (item.Amount.IsNotNull()) { invLineAdd.InvoiceLineAdd.Amount.SetValue(Convert.ToDouble(item.Amount)); } } // Execute request in QB IMsgSetResponse responseMsgSet = sessionManager.DoRequests(requestMsgSet); //IMsgSetResponse responseMsgSet = processRequestFromQB(sessionManager, requestMsgSet); //sessionManager.endSession(); //sessionManager.closeConnection(); sessionManager.EndSession(); sessionManager.CloseConnection(); sessionManager = null; string[] status = new string[3]; if (responseMsgSet != null) status = parseInvoiceAddRs(responseMsgSet); string msg = ""; if (responseMsgSet != null & status[0] == "0") { IInvoiceRet retInv = responseMsgSet.ResponseList.GetAt(0).Detail as IInvoiceRet; IQBIntType new_inv_num = retInv.TxnNumber as IQBIntType; invoiceNumber = Convert.ToInt32(new_inv_num.GetValue()) - 1; msg = "Invoice was added successfully!"; foreach (string test in tests) testIds = testIds + test.ToString() + ","; if (testIds != null) testIds = testIds.Substring(0, testIds.Length - 1); foreach (string charge in charges) chargeIds = chargeIds + charge.ToString() + ","; if (chargeIds != null) chargeIds = chargeIds.Substring(0, chargeIds.Length - 1); DbCommand.CommandType = CommandType.StoredProcedure; DbCommand.CommandText = "uspSaveInvoiceNumberForTests"; DbCommand.Parameters.Clear(); DbCommand.Parameters.Add("@Tests", System.Data.SqlDbType.NVarChar, 4000).Value = testIds; if (chargeIds != null) DbCommand.Parameters.Add("@Charges", System.Data.SqlDbType.NVarChar, 4000).Value = chargeIds; else DbCommand.Parameters.Add("@Charges", System.Data.SqlDbType.NVarChar, 4000).Value = ""; DbCommand.Parameters.Add("@QBInvoiceNumber", System.Data.SqlDbType.Int).Value = invoiceNumber; DbConnection.ExecuteQuery(DbCommand); } else { //throw new Exception(status[2]); msg = "Could not add invoice."; if (status[2].IndexOf("Message") > 0) throw new Exception("Customer Message not found in QuickBooks."); else if (status[2].IndexOf("Customer") > 0) throw new Exception("Client not found in QuickBooks."); else if (status[2].IndexOf("Item") > 0) { string sub = status[2].Substring(status[2].IndexOf("\"") + 1); int len = sub.IndexOf("\""); throw new Exception("Item " + status[2].Substring(status[2].IndexOf("\"") + 1, len) + " not found in QuickBooks."); } else throw new Exception(status[2]); } msg = msg + "\n\n"; msg = msg + "Status Code = " + status[0] + "\n"; msg = msg + "Status Severity = " + status[1] + "\n"; msg = msg + "Status Message = " + status[2] + "\n"; } catch { throw; } return invoiceNumber.ToString(); }
private SmartCollection<SampleTest> GetPendingFinalizations(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification) { try { SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>(); User currentUser = new User(); using (UserDAO userDao = new UserDAO()) { currentUser = userDao.GetUser(identification.UserId); } if (currentUser.IsNull() || currentUser.UserId.IsNull()) return resultList; string sqlDepartment = "AND sampleTest.DepartmentId = @DepartmentId"; // Check ApproveAll or Administrator rights if (AppLib.IsAuthorized(identification, SysLib.GetOptionName(ModuleNames.Samples, ModelNamesEnum.SampleTest, ModuleAction.FinalizeAll))) { sqlDepartment = "AND sampleTest.DepartmentId >= 0"; } string sql = @" SELECT sampleTest.id, sampleTest.parentid,sampleTest.status,sampleTest.sampleid,sampleTest.lab_number, sampleTest.priorityid,sampleTest.typeid,sampleTest.analyteid,sampleTest.testid, sampleTest.departmentid,sampleTest.analystid,sampleTest.method_name,sampleTest.method_number_name, sampleTest.low,sampleTest.high,sampleTest.test_minutes, sampleTest.equipment_minutes, sampleTest.accounting_code,sampleTest.begin_date,sampleTest.due_date,sampleTest.has_requirement_code, sampleTest.requirement_code,sampleTest.item_price,sampleTest.rush_charge,sampleTest.bill_groupid, sampleTest.catalogid, sampleTest.methodid, sampleTest.methodnumberid, sampleTest.is_per_analyte, sampleTest.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, sampleTest.endotoxin_limit,sampleTest.endotoxin_limit_uom, sampleTest.avg_weight, sampleTest.avg_weight_uom, sampleTest.dose_per_hour, sampleTest.dose_per_hour_uom,sampleTest.route_of_administration, sampleTest.articles,sampleTest.is_signed, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sampleTest.modified_by, sampleTest.modified_date, sampleTest.created_by, sampleTest.created_date FROM orders_samples_tests as sampleTest LEFT JOIN [User] as users ON sampleTest.modified_by = users.UserID LEFT JOIN [User] as users2 ON sampleTest.created_by = users2.UserID LEFT JOIN [User] as analyst ON sampleTest.analystid = analyst.UserID LEFT JOIN list.departments as dpart ON sampleTest.[departmentid] = dpart.[departmentid] LEFT JOIN list.tests as test ON sampleTest.[testid] = test.[testid] LEFT JOIN list.analyte as analyte ON sampleTest.[analyteid] = analyte.[analyteid] LEFT JOIN list.priorities as priorities ON sampleTest.[priorityid] = priorities.[priorityid] WHERE (sampleTest.status = @Status " + sqlDepartment + ") "; sql += "AND sampleTest.delete_date IS NULL"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.InProgress; dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = currentUser.DepartmentId; dbCommand.CommandText = sql; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { SampleTest sampleTest = new SampleTest(); resultList.Add(sampleTest); } returnDT = null; return resultList; } catch { throw; } }
private SmartCollection<SampleTest> GetSampleTests(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber, bool loadCanceled, Identification identification) { try { SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>(); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "uspGetSampleTests"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber; 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); } // Do not load predefined notes for canceled tests if (sampleTest.Status != SampleTestStatus.Cancelled) { using (SystemDAO systemDao = new SystemDAO()) { //sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem); } }*/ sampleTest.SampleTestId = row["SampleTestID"] != DBNull.Value ? (int)row["SampleTestID"] : new Int32(); sampleTest.ARLNumber = row["ARLNumber"] != DBNull.Value ? (int)row["ARLNumber"] : new Int32(); sampleTest.Status = row["Status"] != DBNull.Value ? (SampleTestStatus)row["Status"] : new Int32(); sampleTest.Stage = row["Stage"] != DBNull.Value ? (SampleTestStage)row["Stage"] : new Int32(); sampleTest.Type = row["Type"] != DBNull.Value ? (SampleTestType)row["Type"] : new Int32(); sampleTest.OriginalSampleTestId = row["OriginalSampleTestID"] != DBNull.Value ? (int?)row["OriginalSampleTestID"] : new Int32(); sampleTest.CatalogId = row["CatalogID"] != DBNull.Value ? (int)row["CatalogID"] : new Int32(); sampleTest.TestId = row["TestID"] != DBNull.Value ? (int)row["TestID"] : new Int32(); sampleTest.TestName = row["TestName"] != DBNull.Value ? row["TestName"].ToString() : null; sampleTest.AnalyteId = row["AnalyteID"] != DBNull.Value ? (int)row["AnalyteID"] : new Int32(); sampleTest.AnalyteName = row["AnalyteName"] != DBNull.Value ? row["AnalyteName"].ToString() : null; sampleTest.ControlledYN = row["ControlledYN"] != DBNull.Value ? (bool)row["ControlledYN"] : false; sampleTest.AntibioticYN = row["AntibioticYN"] != DBNull.Value ? (bool)row["AntibioticYN"] : false; if (sampleTest.TestId != null && sampleTest.TestId != 0) sampleTest.Test = new Test() { TestId = sampleTest.TestId, TestName = row["TestName"].ToString()}; sampleTest.DepartmentId = row["DepartmentID"] != DBNull.Value ? (int)row["DepartmentId"] : new Int32(); sampleTest.DepartmentName = row["DepartmentName"] != DBNull.Value ? row["DepartmentName"].ToString() : null; if (sampleTest.DepartmentId != null && sampleTest.DepartmentId != 0) sampleTest.Department = new Department() { DepartmentId = sampleTest.DepartmentId, DepartmentName = sampleTest.DepartmentName }; sampleTest.AnalystId = row["AnalystID"] != DBNull.Value ? (int)row["AnalystID"] : new Int32(); sampleTest.AnalystName = row["AnalystName"] != DBNull.Value ? row["AnalystName"].ToString() : null; if (sampleTest.AnalystId != null && sampleTest.AnalystId != 0) sampleTest.Analyst = new User() { UserId = sampleTest.AnalystId, FirstName = sampleTest.AnalystName.IndexOf(' ') > 0 ? sampleTest.AnalystName.Substring(0, sampleTest.AnalystName.IndexOf(' ')) : sampleTest.AnalystName, LastName = sampleTest.AnalystName.IndexOf(' ') > 0 ? sampleTest.AnalystName.Substring(sampleTest.AnalystName.IndexOf(' ') + 1, sampleTest.AnalystName.Length - sampleTest.AnalystName.IndexOf(' ') - 1) : "" }; sampleTest.Containers = row["Containers"] != DBNull.Value ? row["Containers"].ToString() : null; sampleTest.MethodId = row["MethodID"] != DBNull.Value ? (int)row["MethodID"] : new Int32(); sampleTest.MethodName = row["MethodName"] != DBNull.Value ? row["MethodName"].ToString() : null; sampleTest.MethodNumberId = row["MethodNumberID"] != DBNull.Value ? (int)row["MethodNumberID"] : new Int32(); sampleTest.MethodNumberName = row["MethodNumberName"] != DBNull.Value ? row["MethodNumberName"].ToString() : null; sampleTest.OutsourcedYN = row["OutsourcedYN"] != DBNull.Value ? (bool)row["OutsourcedYN"] : false; sampleTest.BasePrice = row["BasePrice"] != DBNull.Value ? (decimal)row["BasePrice"] : new Decimal(); if (row["LowSpec"] != DBNull.Value) sampleTest.LowSpec = Convert.ToDecimal(row["LowSpec"]); if (row["HighSpec"] != DBNull.Value) sampleTest.HighSpec = Convert.ToDecimal(row["HighSpec"]); sampleTest.CalculatePercentageExpectedYN = row["CalculatePercentageExpectedYN"] != DBNull.Value ? (bool)row["CalculatePercentageExpectedYN"] : false; sampleTest.OtherSpec = row["OtherSpec"] != DBNull.Value ? row["OtherSpec"].ToString() : null; sampleTest.TestMinutes = row["TestMinutes"] != DBNull.Value ? (short)row["TestMinutes"] : new Int16(); sampleTest.EquipmentMinutes = row["EquipmentMinutes"] != DBNull.Value ? (short)row["EquipmentMinutes"] : new Int16(); sampleTest.AccountingCode = row["AccountingCode"] != DBNull.Value ? row["AccountingCode"].ToString() : null; sampleTest.Instructions = row["Instructions"] != DBNull.Value ? row["Instructions"].ToString() : null; sampleTest.RequirementYN = row["RequirementYN"] != DBNull.Value ? (bool)row["RequirementYN"] : false; sampleTest.RequirementDescription = row["RequirementDescription"] != DBNull.Value ? row["RequirementDescription"].ToString() : null; sampleTest.EndotoxinLimit = row["EndotoxinLimit"] != DBNull.Value ? (decimal)row["EndotoxinLimit"] : new Decimal(); sampleTest.EndotoxinLimitUOM = row["EndotoxinLimitUOM"] != DBNull.Value ? row["EndotoxinLimitUOM"].ToString() : null; sampleTest.AverageWeight = row["AverageWeight"] != DBNull.Value ? (int)row["AverageWeight"] : new Int32(); sampleTest.AverageWeightUOM = row["AverageWeightUOM"] != DBNull.Value ? row["AverageWeightUOM"].ToString() : null; sampleTest.DosePerHour = row["DosePerHour"] != DBNull.Value ? (decimal)row["DosePerHour"] : new Decimal(); sampleTest.DosePerHourUOM = row["DosePerHourUOM"] != DBNull.Value ? row["DosePerHourUOM"].ToString() : null; sampleTest.RouteOfAdministration = row["RouteOfAdministration"] != DBNull.Value ? row["RouteOfAdministration"].ToString() : null; sampleTest.SignedYN = row["SignedYN"] != DBNull.Value ? (bool)row["SignedYN"] : false; sampleTest.Articles = row["Articles"] != DBNull.Value ? (int)row["Articles"] : new Int32(); sampleTest.SignedName = row["SignedName"] != DBNull.Value ? row["SignedName"].ToString() : null; sampleTest.PriorityId = row["PriorityID"] != DBNull.Value ? (int)row["PriorityID"] : new Int32(); if (sampleTest.PriorityId != null && sampleTest.PriorityId != 0) using (SystemDAO systemDao = new SystemDAO()) { sampleTest.Priority = systemDao.GetPriority(sampleTest.PriorityId); } sampleTest.AdditionalDays = row["AdditionalDays"] != DBNull.Value ? (int)row["AdditionalDays"] : new Int32(); sampleTest.PriceAdjustment = row["PriceAdjustment"] != DBNull.Value ? (decimal)row["PriceAdjustment"] : new Decimal(); sampleTest.DiscountAllowedYN = row["DiscountAllowedYN"] != DBNull.Value ? (bool)row["DiscountAllowedYN"] : false; sampleTest.BillGroup = row["BillGroup"] != DBNull.Value ? (int)row["BillGroup"] : new Int32(); sampleTest.BeginDate = row["BeginDate"] != DBNull.Value ? (DateTime)row["BeginDate"] : new DateTime(); sampleTest.DueDate = row["DueDate"] != DBNull.Value ? (DateTime)row["DueDate"] : new DateTime(); sampleTest.ApprovedBy = row["ApprovedBy"] != DBNull.Value ? (int)row["ApprovedBy"] : new Int32(); sampleTest.ApprovedDate = row["ApprovedDate"] != DBNull.Value ? (DateTime)row["ApprovedDate"] : new DateTime(); sampleTest.ModifiedUser = row["ModifiedUser"] != DBNull.Value ? row["ModifiedUser"].ToString() : null; sampleTest.CreatedUser = row["CreatedUser"] != DBNull.Value ? row["CreatedUser"].ToString() : null; sampleTest.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : new DateTime(); sampleTest.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : new Int32(); sampleTest.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : new DateTime(); sampleTest.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : new Int32(); sampleTest.Results = GetResultsForSampleTest((int)sampleTest.SampleTestId, identification); resultList.Add(sampleTest); } returnDT = null; return resultList; } catch { throw; } }
private SmartCollection<InvoiceItem> GetInvoicableSampleItems(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber, Identification identification) { try { SmartCollection<InvoiceItem> resultList = new SmartCollection<InvoiceItem>(); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "uspGetInvoicableSampleItems"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber; dbCommand.Parameters.Add("@IncludeAll", System.Data.SqlDbType.Bit).Value = showAll; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { InvoiceItem invoiceItem = new InvoiceItem(); /*using (CatalogDAO catalogDao = new CatalogDAO()) { sampleTest.CatalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification); } // Do not load predefined notes for canceled tests if (sampleTest.Status != SampleTestStatus.Cancelled) { using (SystemDAO systemDao = new SystemDAO()) { //sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem); } }*/ invoiceItem.AccountingCode = row["AccountingCode"].ToString(); invoiceItem.Class = row["DepartmentName"].ToString(); invoiceItem.Other1 = row["TestName"].ToString(); invoiceItem.Other2 = row["AnalystName"].ToString(); invoiceItem.Description = row["Description"].ToString(); if (row["BasePrice"] != DBNull.Value) invoiceItem.Amount = Convert.ToDouble(row["BasePrice"]); if (row["SampleTestID"] != DBNull.Value) invoiceItem.SampleTestId = (int)row["SampleTestID"]; if (row["Ordering"] != DBNull.Value) invoiceItem.Ordering = (int)row["Ordering"]; if (row["QBInvoiceNumber"] != DBNull.Value) invoiceItem.InvoiceId = (int)row["QBInvoiceNumber"]; resultList.Add(invoiceItem); } returnDT = null; return resultList; } 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<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; } }
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; } }
public SmartCollection<SampleTest> GetWorkOrders(List<string> recordList, Identification identification) { try { SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sqlSampleTests = string.Format("AND sampleTest.id IN ({0}) ", String.Join(",", recordList)); string sql = @" SELECT sampleTest.id, sampleTest.parentid,sampleTest.status,sampleTest.sampleid,sampleTest.lab_number, sampleTest.priorityid,sampleTest.typeid,sampleTest.analyteid,sampleTest.testid, sampleTest.departmentid,sampleTest.analystid,sampleTest.method_name,sampleTest.method_number_name, sampleTest.low,sampleTest.high,sampleTest.test_minutes, sampleTest.equipment_minutes, sampleTest.accounting_code,sampleTest.begin_date,sampleTest.due_date,sampleTest.has_requirement_code, sampleTest.requirement_code,sampleTest.item_price,sampleTest.rush_charge,sampleTest.bill_groupid, sampleTest.catalogid, sampleTest.methodid, sampleTest.methodnumberid, sampleTest.is_per_analyte, sampleTest.is_price_table,sampleTest.notes,sampleTest.is_discount_allowed, 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, sampleTest.endotoxin_limit,sampleTest.endotoxin_limit_uom, sampleTest.avg_weight, sampleTest.avg_weight_uom, sampleTest.dose_per_hour, sampleTest.dose_per_hour_uom,sampleTest.route_of_administration, sampleTest.articles,sampleTest.is_signed, orders_samples.status as sample_status, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sampleTest.modified_by, sampleTest.modified_date, sampleTest.created_by, sampleTest.created_date FROM orders_samples_tests as sampleTest LEFT JOIN orders_samples ON sampleTest.sampleid = orders_samples.id LEFT JOIN [User] as users ON sampleTest.modified_by = users.UserID LEFT JOIN [User] as users2 ON sampleTest.created_by = users2.UserID LEFT JOIN [User] as analyst ON sampleTest.analystid = analyst.UserID LEFT JOIN list.departments as dpart ON sampleTest.[departmentid] = dpart.[departmentid] LEFT JOIN list.tests as test ON sampleTest.[testid] = test.[testid] LEFT JOIN list.analyte as analyte ON sampleTest.[analyteid] = analyte.[analyteid] LEFT JOIN list.priorities as priorities ON sampleTest.[priorityid] = priorities.[priorityid] WHERE sampleTest.delete_date IS NULL "; sql += sqlSampleTests; sql += "ORDER BY sampleTest.id DESC"; DbCommand.CommandText = sql; 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); } // Do not load predefined notes for canceled tests if (sampleTest.Status != SampleTestStatus.Cancelled) { using (SystemDAO systemDao = new SystemDAO()) { //sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem); } } resultList.Add(sampleTest); } returnDT = null; } } else { throw new Exception("Unable to Connect"); } } return resultList; } catch { throw; } }
public SmartCollection<SampleTest> GetSampleTestsResultsList(SmartCollection<SampleTest> list, Identification identification) { try { string sql = string.Empty; SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { foreach (SampleTest sampleTestItem in list) { DbCommand.Parameters.Clear(); sql = @" SELECT 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 orders_samples_tests_containers AS containers ON containers.parentid = sampleTests.id 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] WHERE ((sampleTests.status = 2 OR sampleTests.status = 3) AND sampleTests.delete_date IS NULL AND oos.id IS NULL) AND sampleTests.lab_number = @LabNumber AND sampleTests.id = @SampleTestId ORDER BY sampleTests.id "; // Maybe Limit Results by Analyst and/or Department DbCommand.CommandText = sql; DbCommand.Parameters.Add("@LabNumber", System.Data.SqlDbType.Int).Value = sampleTestItem.ARLNumber; DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestItem.SampleTestId; DataTable returnDT = DbConnection.ExecuteQuery(DbCommand); foreach (DataRow row in returnDT.Rows) { SampleTest sampleTest = new SampleTest(); //sampleTest.Sample = this.GetSample(ref dbConnection, ref dbCommand, sampleTest.SampleId); //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<Invoice> GetPendingInvoices(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification) { try { SmartCollection<Invoice> resultList = new SmartCollection<Invoice>(); User currentUser = new User(); using (UserDAO userDao = new UserDAO()) { currentUser = userDao.GetUser(identification.UserId); } if (currentUser.IsNull() || currentUser.UserId.IsNull()) return resultList; dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "uspGetInvoicableSamples"; dbCommand.Parameters.Clear(); DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { Invoice invoice = new Invoice(); invoice.ARLNumber = Convert.ToInt32(row["ARLNumber"]); invoice.ClientName = row["ClientName"].ToString(); invoice.AccountingId = row["AccountingId"].ToString(); invoice.PONumber = row["PONumber"].ToString(); invoice.TermName = row["TermName"].ToString(); invoice.IncludeYN = (bool)false; invoice.InvoiceItems = this.GetInvoicableSampleItems((int)invoice.ARLNumber, showAll, identification); resultList.Add(invoice); } returnDT = null; return resultList; } catch { throw; } }
private SmartCollection<TimePoint> GetSampleTestOosTimePoints(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int id) { SmartCollection<TimePoint> resultList = new SmartCollection<TimePoint>(); try { string sql = @" SELECT timepoints.id, timepoints.parentid, timepoints.oosid, timepoints.timepoint_type,timepoints.status, timepoints.is_on_report, timepoints.report_by,timepoints.report_date, timepoints.report_type, timepoints.begindate_days, dbo.ReturnNextBusinessDay(tests.begin_date,timepoints.begindate_days) AS begin_date, timepointDetails.parentid as timepointDetailsParentID, timepointDetails.id as timepointDetailsId, timepointDetails.is_override_record, timepointDetails.limit, timepointDetails.result, timepointDetails.expected_result, timepointDetails.result_date, timepointDetails.is_oos,timepointDetails.created_by, timepointDetails.created_date, timepointDetails.modified_by, timepointDetails.modified_date, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser FROM orders_samples_tests_timepoints AS timepoints LEFT JOIN orders_samples_tests AS tests ON timepoints.parentid = tests.id LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointDetails ON timepoints.id = timepointDetails.parentid LEFT JOIN [User] AS users ON timepointDetails.modified_by = users.UserID LEFT JOIN [User] as users2 ON timepointDetails.created_by = users2.UserID WHERE timepoints.oosid = @Id ORDER BY timepoints.begindate_days ;"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id; dbCommand.CommandText = sql; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { TimePoint timePoint = new TimePoint(); timePoint.Id = Convert.ToInt32(row["Id"]); timePoint.ParentId = Convert.ToInt32(row["ParentId"]); timePoint.BeginDate = Convert.ToDateTime(row["begin_date"]); timePoint.TimepointType = row["timepoint_type"] != DBNull.Value ? (EnumTimePointType)row["timepoint_type"] : EnumTimePointType.Result; timePoint.IsOnReport = row["is_on_report"] != DBNull.Value ? Convert.ToBoolean(row["is_on_report"]) : false; timePoint.ReportBy = row["report_by"] != DBNull.Value ? (int)row["report_by"] : 0; timePoint.ReportDate = row["report_date"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(row["report_date"]) : (DateTime?)null; timePoint.BeginDateDays = Convert.ToInt32(row["begindate_days"]); timePoint.OosId = row["oosid"] != DBNull.Value ? Convert.ToInt32(row["oosid"]) : 0; timePoint.ResultDetail.Id = row["timepointDetailsId"] != DBNull.Value ? (int?)Convert.ToInt32(row["timepointDetailsId"]) : null; timePoint.ResultDetail.ParentId = row["timepointDetailsParentID"] != DBNull.Value ? (int?)Convert.ToInt32(row["timepointDetailsParentID"]) : null; timePoint.ResultDetail.Limit = row["limit"].ToString(); timePoint.ResultDetail.Result = row["result"].ToString(); timePoint.ResultDetail.IsOos = row["is_oos"] != DBNull.Value ? Convert.ToBoolean(row["is_oos"]) : false; timePoint.ResultDetail.IsOverrideRecord = row["is_override_record"] != DBNull.Value ? Convert.ToBoolean(row["is_override_record"]) : false; timePoint.ResultDetail.ExpectedResult = row["expected_result"] != DBNull.Value ? (decimal?)Convert.ToDecimal(row["expected_result"]) : null; timePoint.ResultDetail.ResultDate = row["result_date"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(row["result_date"]) : (DateTime?)null; timePoint.ResultDetail.CreatedBy = row["created_by"] != DBNull.Value ? Convert.ToInt32(row["created_by"]) : 0; timePoint.ResultDetail.CreatedUser = row["createduser"].ToString(); timePoint.ResultDetail.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null; timePoint.ResultDetail.ModifiedBy = row["modified_by"] != DBNull.Value ? Convert.ToInt32(row["modified_by"]) : 0; timePoint.ResultDetail.ModifiedUser = row["modifieduser"].ToString(); timePoint.ResultDetail.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null; resultList.Add(timePoint); } } catch { throw; } return resultList; }
private SmartCollection<Sample> GetPendingSubmissions(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification) { try { SmartCollection<Sample> resultList = new SmartCollection<Sample>(); SmartCollection<SampleTest> testList = new SmartCollection<SampleTest>(); User currentUser = new User(); using (UserDAO userDao = new UserDAO()) { currentUser = userDao.GetUser(identification.UserId); } if (currentUser.IsNull() || currentUser.UserId.IsNull()) return resultList; string sqlDepartment = "AND sampleTest.DepartmentId = @DepartmentId"; // Check ApproveAll or Administrator rights if (AppLib.IsAuthorized(identification, SysLib.GetOptionName(ModuleNames.Samples, ModelNamesEnum.SampleTest, ModuleAction.ApproveAll))) { sqlDepartment = "AND sampleTest.DepartmentId >= 0"; } string sql = @" SELECT sampleTest.id, sampleTest.parentid,sampleTest.status,sampleTest.sampleid,sampleTest.lab_number, sampleTest.priorityid,sampleTest.typeid,sampleTest.analyteid,sampleTest.testid, sampleTest.departmentid,sampleTest.analystid,sampleTest.method_name,sampleTest.method_number_name, sampleTest.low,sampleTest.high,sampleTest.test_minutes, sampleTest.equipment_minutes, sampleTest.accounting_code,sampleTest.begin_date,sampleTest.due_date,sampleTest.has_requirement_code, sampleTest.requirement_code,sampleTest.item_price,sampleTest.rush_charge,sampleTest.bill_groupid, sampleTest.catalogid, sampleTest.methodid, sampleTest.methodnumberid, sampleTest.is_per_analyte, sampleTest.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, sampleTest.endotoxin_limit,sampleTest.endotoxin_limit_uom, sampleTest.avg_weight, sampleTest.avg_weight_uom, sampleTest.dose_per_hour, sampleTest.dose_per_hour_uom,sampleTest.route_of_administration, sampleTest.articles,sampleTest.is_signed, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sampleTest.modified_by, sampleTest.modified_date, sampleTest.created_by, sampleTest.created_date FROM orders_samples_tests as sampleTest LEFT JOIN [User] as users ON sampleTest.modified_by = users.UserID LEFT JOIN [User] as users2 ON sampleTest.created_by = users2.UserID LEFT JOIN [User] as analyst ON sampleTest.analystid = analyst.UserID LEFT JOIN list.departments as dpart ON sampleTest.[departmentid] = dpart.[departmentid] LEFT JOIN list.tests as test ON sampleTest.[testid] = test.[testid] LEFT JOIN list.analyte as analyte ON sampleTest.[analyteid] = analyte.[analyteid] LEFT JOIN list.priorities as priorities ON sampleTest.[priorityid] = priorities.[priorityid] WHERE (sampleTest.status = @Status " + sqlDepartment + ") "; sql += "AND sampleTest.delete_date IS NULL"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.InProgress; dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = currentUser.DepartmentId; dbCommand.CommandText = sql; 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); } testList.Add(sampleTest); } returnDT = null; if (testList.Count > 0) { var sampleIds = testList.Select(x => x.ARLNumber).ToArray(); sql = string.Format(@" SELECT sample.id, sample.parentid,sample.sample_type_id,sample.name,sample.lab_number, sample.formulation_id,sample.quote_number,sample.project_number,sample.lot_number, sample.storage_id,sample.dosage_id,dosage.value as dosagevalue,dosage.active as dosageactive, sample.ndc_id, sample.articles,sample.container_type,sample.volume_amount, sample.volume_uom,sample.is_study,sample.is_gmp,sample.compounded_by,sample.compound_date,storage.description as storagedescription, storage.active as storageactive, storage.conditions as storageconditions, (SELECT COUNT(analytes.controlled) FROM orders_samples_analytes AS analytes WHERE analytes.controlled = 'true' and analytes.parentid = sample.id AND analytes.delete_date IS NULL) AS is_cds, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sample.modified_by, sample.modified_date, sample.created_by, sample.created_date FROM orders_samples as sample LEFT JOIN [User] as users ON sample.modified_by = users.UserID LEFT JOIN [User] as users2 ON sample.created_by = users2.UserID LEFT JOIN list.dosagetype as dosage ON sample.dosage_id = dosage.dosageid LEFT JOIN list.storage as storage ON sample.storage_id = storage.storageid WHERE sample.id IN ({0}) AND sample.delete_date IS NULL ;", String.Join(",", sampleIds)); dbCommand.Parameters.Clear(); dbCommand.CommandText = sql; returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { Sample sample = new Sample(); resultList.Add(sample); } foreach (Sample sampleItem in resultList) { sampleItem.SampleTests.AddRange(testList.Where(x => x.ARLNumber == sampleItem.ARLNumber).Select(s => s)); } } returnDT = null; return resultList; } catch { throw; } }
private SmartCollection<TimePoint> GetSampleTestTimePoints(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, SampleTest sampleTest, string[] useSql = null) { string sql = string.Empty; SmartCollection<TimePoint> result = new SmartCollection<TimePoint>(); try { SmartCollection<BusinessClosedDay> closedDays = new SmartCollection<BusinessClosedDay>(); using (SystemDAO systeDao = new SystemDAO()) { closedDays.AddRange(systeDao.GetBusinessClosedDays((DateTime)sampleTest.BeginDate, (DateTime)sampleTest.DueDate)); } sql = useSql != null && useSql[1].Length > 0 ? useSql[1] : @" SELECT timepoints.id, timepoints.parentid, DATEADD(day,timepoints.begindate_days, tests.begin_date) as begin_date, timepoints.timepoint_type,timepoints.status, timepoints.is_on_report, timepoints.report_by,timepoints.report_date, timepoints.report_type, timepoints.begindate_days, timepoints.oosid, timepointDetails.parentid as timepointDetailsParentID, timepointDetails.id as timepointDetailsId, timepointDetails.is_override_record, timepointDetails.limit, timepointDetails.result, timepointDetails.expected_result, timepointDetails.result_date, timepointDetails.is_oos,timepointDetails.created_by, timepointDetails.created_date, timepointDetails.modified_by, timepointDetails.modified_date, (users.firstname + ' ' + users.lastname) as modifieduser, (users1.firstname + ' ' + users1.lastname) as ReportedByUser, (users2.firstname + ' ' + users2.lastname) as createduser FROM orders_samples_tests_timepoints AS timepoints LEFT JOIN orders_samples_tests AS tests ON timepoints.parentid = tests.id LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointDetails ON timepoints.id = timepointDetails.parentid LEFT JOIN [User] AS users ON timepointDetails.modified_by = users.UserID LEFT JOIN [User] as users1 ON timepoints.report_by = users1.UserID LEFT JOIN [User] as users2 ON timepointDetails.created_by = users2.UserID WHERE timepoints.parentid = @ParentId ORDER BY timepoints.begindate_days ;"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@ParentId", System.Data.SqlDbType.Int).Value = sampleTest.SampleTestId; dbCommand.CommandText = sql; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { TimePoint timePoint = new TimePoint(); timePoint.Id = Convert.ToInt32(row["Id"]); timePoint.ParentId = Convert.ToInt32(row["ParentId"]); timePoint.BeginDate = AppLib.GetNextOpenBuisnessDay(Convert.ToDateTime(row["begin_date"]), closedDays); timePoint.TimepointType = row["timepoint_type"] != DBNull.Value ? (EnumTimePointType)row["timepoint_type"] : EnumTimePointType.Result; timePoint.IsOnReport = row["is_on_report"] != DBNull.Value ? Convert.ToBoolean(row["is_on_report"]) : false; timePoint.ReportBy = row["report_by"] != DBNull.Value ? (int)row["report_by"] : 0; timePoint.ReportedByUser = row["ReportedByUser"].ToString(); timePoint.ReportDate = row["report_date"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(row["report_date"]) : null; timePoint.BeginDateDays = Convert.ToInt32(row["begindate_days"]); timePoint.OosId = row["oosid"] != DBNull.Value ? Convert.ToInt32(row["oosid"]) : 0; timePoint.ResultDetail.Id = row["timepointDetailsId"] != DBNull.Value ? (int?)Convert.ToInt32(row["timepointDetailsId"]) : null; timePoint.ResultDetail.ParentId = row["timepointDetailsParentID"] != DBNull.Value ? (int?)Convert.ToInt32(row["timepointDetailsParentID"]) : null; timePoint.ResultDetail.Limit = row["limit"].ToString(); timePoint.ResultDetail.Result = row["result"].ToString(); timePoint.ResultDetail.IsOverrideRecord = row["is_override_record"] != DBNull.Value ? Convert.ToBoolean(row["is_override_record"]) : false; timePoint.ResultDetail.IsOos = row["is_oos"] != DBNull.Value ? Convert.ToBoolean(row["is_oos"]) : false; timePoint.ResultDetail.ExpectedResult = row["expected_result"] != DBNull.Value ? (decimal?)Convert.ToDecimal(row["expected_result"]) : null; timePoint.ResultDetail.ResultDate = row["result_date"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(row["result_date"]) : (DateTime?)null; timePoint.ResultDetail.CreatedBy = row["created_by"] != DBNull.Value ? Convert.ToInt32(row["created_by"]) : 0; timePoint.ResultDetail.CreatedUser = row["createduser"].ToString(); timePoint.ResultDetail.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null; timePoint.ResultDetail.ModifiedBy = row["modified_by"] != DBNull.Value ? Convert.ToInt32(row["modified_by"]) : 0; timePoint.ResultDetail.ModifiedUser = row["modifieduser"].ToString(); timePoint.ResultDetail.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null; result.Add(timePoint); } } catch { throw; } return result; }
private SmartCollection<Sample> GetReportableCOAs(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification, string departments) { try { SmartCollection<Sample> resultList = new SmartCollection<Sample>(); User currentUser = new User(); using (UserDAO userDao = new UserDAO()) { currentUser = userDao.GetUser(identification.UserId); } if (currentUser.IsNull() || currentUser.UserId.IsNull()) return resultList; dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "uspGetReportableCOAs"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@Departments", System.Data.SqlDbType.NVarChar, 40).Value = departments; 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.ClientName = row["ClientName"].ToString(); sample.LotNumber = row["LotNumber"].ToString(); sample.SampleTests = this.GetReportableCOATests((int)sample.ARLNumber, false, identification, departments); resultList.Add(sample); } returnDT = null; return resultList; } catch { throw; } }
private SmartCollection<SampleTest> GetWorkLoad(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, CatalogItem filterItem, SmartCollection<Analyte> filterAnalytes, DateTime beginStartDate, DateTime beginEndDate, Identification identification) { try { SmartCollection<SampleTest> resultList = new SmartCollection<SampleTest>(); string sqlOrderBy = string.Empty; string sqlTest = string.Empty; string sqlAnalyst = string.Empty; string sqlDepartment = string.Empty; string sqlBeginDate = string.Empty; string sqlAnalytes = string.Empty; if (filterItem.TestId > 0) { sqlTest = "AND sampleTest.testId = @TestId "; } else { sqlTest = "AND sampleTest.testId >= @TestId "; } if (filterItem.AnalystId > 0) { sqlAnalyst = "AND sampleTest.analystid = @AnalystId "; } else { sqlAnalyst = "AND sampleTest.analystid >= @AnalystId "; } if (filterItem.DepartmentId > 0) { sqlDepartment = "AND sampleTest.departmentid = @DepartmentId "; } else { sqlDepartment = "AND sampleTest.departmentid >= @DepartmentId "; } if (filterAnalytes != null || filterAnalytes.Count() > 0) { var analyteIds = filterAnalytes.Select(x => x.AnalyteId).ToArray(); if (analyteIds.Count() > 0) { sqlAnalytes = string.Format("AND sampleTest.analyteid IN ({0})", String.Join(",", analyteIds)); } } beginStartDate = DateEx.GetStartOfDay(beginStartDate); beginEndDate = DateEx.GetEndOfDay(beginEndDate); sqlBeginDate = " AND dbo.ReturnNextBusinessDay(sampleTest.begin_date,timepoints.begindate_days) BETWEEN @BeginStartDate AND @BeginEndDate "; //;DATEADD(day,timepoints.begindate_days, sampleTest.begin_date) BETWEEN @BeginStartDate AND @BeginEndDate "; sqlOrderBy = "ORDER BY begin_date"; string sql = @" SELECT sampleTest.id, sampleTest.parentid,sampleTest.status,sampleTest.sampleid,sampleTest.lab_number, sampleTest.priorityid,sampleTest.typeid,sampleTest.analyteid,sampleTest.testid, sampleTest.departmentid,sampleTest.analystid,sampleTest.method_name,sampleTest.method_number_name, sampleTest.low,sampleTest.high,sampleTest.test_minutes, sampleTest.equipment_minutes, sampleTest.accounting_code,dbo.ReturnNextBusinessDay(sampleTest.begin_date,timepoints.begindate_days) AS begin_date, sampleTest.due_date,sampleTest.has_requirement_code, sampleTest.requirement_code,sampleTest.item_price,sampleTest.rush_charge,sampleTest.bill_groupid, sampleTest.catalogid, sampleTest.methodid, sampleTest.methodnumberid, sampleTest.is_per_analyte, sampleTest.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, sampleTest.endotoxin_limit,sampleTest.endotoxin_limit_uom, sampleTest.avg_weight, sampleTest.avg_weight_uom, sampleTest.dose_per_hour, sampleTest.dose_per_hour_uom,sampleTest.route_of_administration, sampleTest.articles,sampleTest.is_signed, timepoints.begindate_days, timepoints.timepoint_type, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, sampleTest.modified_by, sampleTest.modified_date, sampleTest.created_by, sampleTest.created_date FROM orders_samples_tests as sampleTest LEFT JOIN [User] as users ON sampleTest.modified_by = users.UserID LEFT JOIN [User] as users2 ON sampleTest.created_by = users2.UserID LEFT JOIN [User] as analyst ON sampleTest.analystid = analyst.UserID LEFT JOIN list.departments as dpart ON sampleTest.[departmentid] = dpart.[departmentid] LEFT JOIN list.tests as test ON sampleTest.[testid] = test.[testid] LEFT JOIN list.analyte as analyte ON sampleTest.[analyteid] = analyte.[analyteid] LEFT JOIN list.priorities as priorities ON sampleTest.[priorityid] = priorities.[priorityid] LEFT JOIN orders_samples_tests_timepoints AS timepoints ON timepoints.parentid = sampleTest.id LEFT OUTER JOIN orders_samples_tests_timepoints_results AS timepointsResults ON timepoints.id = timepointsResults.parentid WHERE ((sampleTest.status = 2 or sampleTest.status = 3) AND sampleTest.delete_date IS NULL AND timepointsResults.result_date IS NULL) "; sql += sqlTest; sql += sqlAnalyst; sql += sqlDepartment; sql += sqlBeginDate; sql += sqlAnalytes; sql += sqlOrderBy; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)SampleTestStatus.InProgress; dbCommand.Parameters.Add("@TestId", System.Data.SqlDbType.Int).Value = filterItem.TestId ?? 0; dbCommand.Parameters.Add("@AnalystId", System.Data.SqlDbType.Int).Value = filterItem.AnalystId ?? 0; dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = filterItem.DepartmentId ?? 0; dbCommand.Parameters.Add("@BeginStartDate", System.Data.SqlDbType.DateTime).Value = beginStartDate; dbCommand.Parameters.Add("@BeginEndDate", System.Data.SqlDbType.DateTime).Value = beginEndDate; dbCommand.CommandText = sql; 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; } catch { throw; } }
private SmartCollection<Result> GetResultsForSampleTest(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int sampleTestId, Identification identification) { try { SmartCollection<Result> resultList = new SmartCollection<Result>(); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "uspGetResultsForSampleTest"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { Result result = new Result(); if (row["ResultID"] != DBNull.Value) result.ResultId = (int)row["ResultID"]; result.SampleTestId = row["SampleTestID"] != DBNull.Value ? (int)row["SampleTestID"] : new Int32(); result.ARLNumber = row["ARLNumber"] != DBNull.Value ? (int)row["ARLNumber"] : new Int32(); result.ResultName = row["ResultName"] != DBNull.Value ? row["ResultName"].ToString() : null; result.ResultValue = row["ResultValue"] != DBNull.Value ? row["ResultValue"].ToString() : null; result.TestId = row["TestID"] != DBNull.Value ? (int)row["TestID"] : new Int32(); result.TestName = row["TestName"] != DBNull.Value ? row["TestName"].ToString() : null; result.AnalyteId = row["AnalyteID"] != DBNull.Value ? (int)row["AnalyteID"] : new Int32(); result.AnalyteName = row["AnalyteName"] != DBNull.Value ? row["AnalyteName"].ToString() : null; if (result.AnalyteId != null && result.AnalyteId != 0) result.Analyte = new Analyte() { AnalyteId = result.AnalyteId, AnalyteName = result.AnalyteName }; if (result.TestId != null && result.TestId != 0) result.Test = new Test() { TestId = result.TestId, TestName = row["TestName"].ToString() }; result.DepartmentId = row["DepartmentID"] != DBNull.Value ? (int)row["DepartmentId"] : new Int32(); result.DepartmentName = row["DepartmentName"] != DBNull.Value ? row["DepartmentName"].ToString() : null; if (result.DepartmentId != null && result.DepartmentId != 0) result.Department = new Department() { DepartmentId = result.DepartmentId, DepartmentName = result.DepartmentName }; result.AnalystName = row["AnalystName"] != DBNull.Value ? row["AnalystName"].ToString() : null; result.MethodId = row["MethodID"] != DBNull.Value ? (int)row["MethodID"] : new Int32(); result.MethodName = row["MethodName"] != DBNull.Value ? row["MethodName"].ToString() : null; result.MethodNumberId = row["MethodNumberID"] != DBNull.Value ? (int)row["MethodNumberID"] : new Int32(); result.MethodNumberName = row["MethodNumberName"] != DBNull.Value ? row["MethodNumberName"].ToString() : null; if (result.MethodNumberName != null) result.MethodNumber = new MethodNumber() { MethodNumberId = result.MethodNumberId, MethodNumberName = result.MethodNumberName, Active = true }; if (row["Amount"] != DBNull.Value) result.Amount = (decimal)row["Amount"]; result.AmountUOM = row["AmountUOM"] != DBNull.Value ? row["AmountUOM"].ToString() : null; result.Concentration = row["Concentration"] != DBNull.Value ? row["Concentration"].ToString() : null; result.ConcentrationUOM = row["ConcentrationUOM"] != DBNull.Value ? row["ConcentrationUOM"].ToString() : null; if (row["LowSpec"] != DBNull.Value) result.LowSpec = Convert.ToDecimal(row["LowSpec"]); if (row["HighSpec"] != DBNull.Value) result.HighSpec = Convert.ToDecimal(row["HighSpec"]); result.CalculatePercentageExpectedYN = row["CalculatePercentageExpectedYN"] != DBNull.Value ? (bool)row["CalculatePercentageExpectedYN"] : false; result.OtherSpec = row["OtherSpec"] != DBNull.Value ? row["OtherSpec"].ToString() : null; result.OOS = row["OOSYN"] != DBNull.Value ? (bool)row["OOSYN"] : false; if (row["BeginDate"] != DBNull.Value) result.BeginDate = (DateTime)row["BeginDate"]; if (row["BeginDate"] != DBNull.Value) result.ScheduledReadDate = (DateTime)row["BeginDate"]; if (row["TestedDate"] != DBNull.Value) result.TestedDate = (DateTime)row["TestedDate"]; result.SecondAnalyst = row["SecondAnalyst"] != DBNull.Value ? row["SecondAnalyst"].ToString() : null; if (row["CalcAvgYN"] != DBNull.Value) result.CalcAvgYN = (bool)row["CalcAvgYN"]; else result.CalcAvgYN = false; if (row["ReportOnCOAYN"] != DBNull.Value) result.ReportYN = (bool)row["ReportOnCOAYN"]; else result.ReportYN = false; 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(); resultList.Add(result); } returnDT = null; return resultList; } catch { throw new Exception("Error in GetResults"); } }
private void LoadSampleTestMockData(ref SmartCollection<SampleTest> sampleTests) { sampleTests.Add( new SampleTest() { SampleTestId = 1, ARLNumber = 1, TestId = 1, //TestType = new TestType() { TestTypeId = SampleTypeEnum.Compound, TestName = "Sterility" }, RequirementYN = true, Articles = 1, Department = new Department() { DepartmentName = "Chemistry" }, }); }
public void AddSmile() { _items.Add(InstantiateSmile()); }
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; } }
private SmartCollection<SampleCharge> GetSampleCharges(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber) { try { SmartCollection<SampleCharge> resultList = new SmartCollection<SampleCharge>(); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "uspGetSampleCharges"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { SampleCharge charge = new SampleCharge(); charge.SampleChargeId = Convert.ToInt32(row["SampleChargeID"]); charge.ARLNumber = Convert.ToInt32(row["ARLNumber"]); charge.ChargeId = Convert.ToInt32(row["ChargeID"]); if (charge.ChargeId > 0) charge.Charge = new Charge() { ChargeId = charge.ChargeId, ChargeDescription = row["Description"].ToString(), AccountingCode = row["AccountingCode"].ToString(), Amount = (decimal)row["Amount"] }; charge.Description = row["Description"].ToString(); charge.AccountingCode = row["AccountingCode"].ToString(); charge.BillGroup = row["BillGroup"] != DBNull.Value ? Convert.ToInt32(row["BillGroup"]) : new Int32(); charge.Amount = row["Amount"] != DBNull.Value ? (decimal)row["Amount"] : new Decimal(); charge.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32(); charge.CreatedUser = row["CreatedUser"].ToString(); charge.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null; charge.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32(); charge.ModifiedUser = row["ModifiedUser"].ToString(); charge.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null; resultList.Add(charge); } returnDT = null; return resultList; } 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; } }
private SmartCollection<SampleContainer> GetSampleLabel(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int arlNumber) { SmartCollection<SampleContainer> returnList = new SmartCollection<SampleContainer>(); try { returnList = new SmartCollection<SampleContainer>(); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "uspGetSampleLabel"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { SampleContainer container = new SampleContainer(); container.SampleContainerId = Convert.ToInt32(row["SampleContainerID"]); container.Label = row["Label"].ToString(); container.ARLNumber = Convert.ToInt32(row["ARLNumber"]); container.ReceivedDate = row["ReceivedDate"].ToString(); container.StorageLocationCode = row["StorageLocationCode"].ToString(); returnList.Add(container); } } catch { throw; } return returnList; }
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; } }
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; } }
private SmartCollection<SampleAnalyte> GetSampleAnalytes(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber) { try { SmartCollection<SampleAnalyte> resultList = new SmartCollection<SampleAnalyte>(); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "uspGetSampleAnalytes"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { SampleAnalyte analyte = new SampleAnalyte(); analyte.SampleAnalyteId = Convert.ToInt32(row["SampleAnalyteID"]); analyte.ARLNumber = Convert.ToInt32(row["ARLNumber"]); analyte.AnalyteId = Convert.ToInt32(row["AnalyteID"]); analyte.AnalyteName = row["AnalyteName"].ToString(); if (analyte.AnalyteId > -1) analyte.Analyte = new Analyte() { AnalyteId = analyte.AnalyteId, AnalyteName = row["AnalyteName"].ToString(), Active = true, AntibioticYN = (bool)row["AntibioticYN"], ControlledYN = (bool)row["ControlledYN"] }; if (row["Amount"] != DBNull.Value) analyte.Amount = Convert.ToDecimal(row["Amount"]); analyte.AmountUOM = row["AmountUOM"].ToString(); if (analyte.AmountUOM != null) analyte.AmountUnitOfMeasure = new UnitOfMeasure { UomId = 0, Uom = analyte.AmountUOM }; if (row["Concentration"] != DBNull.Value) analyte.Concentration = Convert.ToDecimal(row["Concentration"]); analyte.ConcentrationUOM = row["ConcentrationUOM"].ToString(); if (analyte.ConcentrationUOM != null) analyte.ConcentrationUnitOfMeasure = new UnitOfMeasure { UomId = 0, Uom = analyte.ConcentrationUOM }; analyte.ControlledYN = row["ControlledYN"] != DBNull.Value ? Convert.ToBoolean(row["ControlledYN"]) : false; analyte.AntibioticYN = row["AntibioticYN"] != DBNull.Value ? Convert.ToBoolean(row["AntibioticYN"]) : false; analyte.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32(); analyte.CreatedUser = row["CreatedUser"].ToString(); analyte.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null; analyte.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32(); analyte.ModifiedUser = row["ModifiedUser"].ToString(); analyte.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null; resultList.Add(analyte); } returnDT = null; return resultList; } catch { throw; } }
public SmartCollection<SampleNote> GetSampleTestNotes(int arlNumber, int sampleTestId) { try { SmartCollection<SampleNote> resultList = new SmartCollection<SampleNote>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { DbCommand.CommandType = CommandType.StoredProcedure; DbCommand.CommandText = "uspGetSampleTestNotes"; DbCommand.Parameters.Clear(); DbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber; DbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId; DataTable notesDT = DbConnection.ExecuteQuery(DbCommand); foreach (DataRow row in notesDT.Rows) { SampleNote note = new SampleNote(); note.SampleNoteId = Convert.ToInt32(row["SampleNoteID"]); note.ARLNumber = row["ARLNumber"] != DBNull.Value ? Convert.ToInt32(row["ARLNumber"]) : new Int32(); note.SampleTestId = row["SampleTestID"] != DBNull.Value ? Convert.ToInt32(row["SampleTestID"]) : new Int32(); note.Note = row["Note"].ToString(); note.IncludeOnWOYN = row["IncludeOnWOYN"] != DBNull.Value ? Convert.ToBoolean(row["IncludeOnWOYN"]) : false; note.IncludeOnCOAYN = row["IncludeOnCOAYN"] != DBNull.Value ? Convert.ToBoolean(row["IncludeOnCOAYN"]) : false; note.BillGroup = row["BillGroup"] != DBNull.Value ? Convert.ToInt32(row["BillGroup"]) : new Int32(); note.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32(); 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"]) : new Int32(); 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; } }