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