public SmartCollection<SampleTest> GetData(string token, object recordIds)
{
SmartCollection<SampleTest> results = new SmartCollection<SampleTest>();
this.RecordList = new List<string>();
User user = new User();
int userId = 0;
this.Token = token;
this.RecordList.AddRange(((object[])recordIds).Select(x => x.ToString()).ToList());
if (AppLib.DesignMode) {
LoadSampleTestMockData(ref results);
}else {
try {
using (SystemDAO systemDao = new SystemDAO()) {
userId = systemDao.GetToken(new Guid(this.Token));
}
if (userId > 0) {
using (UserDAO userDao = new UserDAO()) {
user = userDao.GetUser(userId);
}
using (SampleDAO ordersDao = new SampleDAO()) {
results = ordersDao.GetWorkOrders(RecordList, new Identification() { Token = new Guid(this.Token), UserId = (int)user.UserId });
}
}
}catch (Exception) {
throw;
}
}
return results;
}
private void GetUserResponse(IAsyncResult result)
{
try {
Result = ((IUserService)result.AsyncState).EndGetUser(result);
}
catch (Exception ex) {
Error = ex;
}
finally {
service.Close();
service = null;
}
// Execute Last
Caliburn.Micro.Execute.OnUIThread(() => Completed(this, new ResultCompletionEventArgs()));
}
public static string GetLockMessage(User user)
{
string message = string.Empty;
if (user.UserId == (int)SessionInfo.Instance.CurrentUser.UserId)
message += "You already have this record open.";
else {
message += string.Format("{0} {1} is currently modifying this record.", user.FirstName, user.LastName);
}
return message;
}
private string GetReportableCOAsCount(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
int returnValue = 0;
User currentUser = new User();
using (UserDAO userDao = new UserDAO())
{
currentUser = userDao.GetUser(identification.UserId);
}
if (currentUser.IsNull() || currentUser.UserId.IsNull())
return string.Empty;
return returnValue <= 0 ? string.Empty : returnValue.ToString();
}
catch
{
throw;
}
}
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 string GetPendingSubmissionsCount(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Identification identification)
{
try
{
int returnValue = 0;
User currentUser = new User();
using (UserDAO userDao = new UserDAO())
{
currentUser = userDao.GetUser(identification.UserId);
}
if (currentUser.IsNull() || currentUser.UserId.IsNull())
return string.Empty;
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 Count(sampleTest.id)
FROM orders_samples_tests as sampleTest
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;
returnValue = (int)dbConnection.ExecuteScalar(dbCommand);
return returnValue <= 0 ? string.Empty : returnValue.ToString();
}
catch
{
throw;
}
}
public GetUserAuthenticationRequest(User user)
{
this.user = user;
}
public IAsyncResult BeginSaveUser(User user, Identification identification, AsyncCallback callback, object state)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
identification.UserId = AppLib.VerifyToken(identification.Token);
if (identification.UserId <= 0) {
throw new FaultException<ServiceFault>(new ServiceFault("Invalid Authentication", "Authorization"), new FaultReason("Unauthorized"));
}
var task = Task<int>.Factory.StartNew(process => DoSaveUser(user, identification), state);
return task.ContinueWith(res => callback(task));
}
/// <summary>
/// Save User record.
/// </summary>
/// <returns></returns>
public int SaveUser(User user, Guid userToken, int userId)
{
int returnValue = -1;
string sql = string.Empty;
using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
//Check New Record
if (user.Signature.IsDirty) if (user.Signature.SignatureId == Guid.Empty)
{
user.SignatureId = Guid.NewGuid();
}
if (user.IsDirty)
{
SystemDAO.SaveChangeAudit<User>(ref dbConnection, ref dbCommand,
GetUser(ref dbConnection, ref dbCommand, user.Pk ?? 0),
user,
ModuleNames.Users,
user.Pk,
userId
);
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.Parameters.Clear();
if (user.UserId == null)
{
DbCommand.CommandText = "uspInsertUser";
DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
DbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
else
{
DbCommand.CommandText = "uspUpdateUser";
DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = user.UserId;
DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
DbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
}
DbCommand.Parameters.Add("@RoleId", System.Data.SqlDbType.UniqueIdentifier).Value = user.Role.RoleId;
DbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = user.DepartmentId;
DbCommand.Parameters.Add("@AdministratorYN", System.Data.SqlDbType.Bit).Value = user.AdministratorYN;
DbCommand.Parameters.Add("@AdministratorKey", System.Data.SqlDbType.VarChar, 200).Value = user.AdministratorKey ?? string.Empty;
DbCommand.Parameters.Add("@Username", System.Data.SqlDbType.VarChar, 50).Value = user.Username;
if (user.Password.IsNotNull())
DbCommand.Parameters.Add("@Password", System.Data.SqlDbType.VarChar, 64).Value = user.Password;
DbCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.VarChar, 50).Value = user.FirstName ?? string.Empty;
DbCommand.Parameters.Add("@LastName", System.Data.SqlDbType.VarChar, 50).Value = user.LastName ?? string.Empty;
DbCommand.Parameters.Add("@Email", System.Data.SqlDbType.VarChar, 255).Value = user.Email ?? string.Empty;
DbCommand.Parameters.Add("@Title", System.Data.SqlDbType.VarChar, 255).Value = user.Title ?? string.Empty;
DbCommand.Parameters.Add("@SignatureId", System.Data.SqlDbType.UniqueIdentifier).Value = user.SignatureId;
if (user.UserId != null) returnValue = DbConnection.ExecuteCommand(DbCommand);
else
{
// returnValue = Primary Key Id
returnValue = (int)DbConnection.ExecuteScalar(DbCommand);
user.UserId = returnValue;
}
}
// Save Signature
if (user.Signature.IsDirty)
this.SaveSignature(ref dbConnection, ref dbCommand, user, (int)user.UserId);
// Release Lock
using (SystemDAO systemDao = new SystemDAO())
{
systemDao.ReleaseLock(ref dbConnection, ref dbCommand, (int)ModelNamesEnum.User, user.UserId.ToString(), userToken);
}
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return returnValue;
}
public User Authenticate(string username, string password)
{
try
{
User result = new User();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspAuthenticateUser";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@Username", System.Data.SqlDbType.VarChar, 50).Value = username.Trim() ?? string.Empty;
DbCommand.Parameters.Add("@Password", System.Data.SqlDbType.VarChar, 64).Value = password.Trim() ?? string.Empty;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
if (returnDT.Rows.Count == 1)
{
DataRow row = returnDT.Rows[0];
result.UserId = Convert.ToInt32(row["UserID"]);
result.RoleId = row["RoleID"] == DBNull.Value ? Guid.Empty : (Guid)row["RoleID"];
result.DepartmentId = row["DepartmentID"] == DBNull.Value ? SqlInt32.Null.Value : (int)row["DepartmentID"];
result.AdministratorYN = Convert.ToBoolean(row["AdministratorYN"]);
result.AdministratorKey = row["AdministratorKey"].ToString();
result.Username = row["Username"].ToString();
result.FirstName = row["FirstName"].ToString();
result.LastName = row["LastName"].ToString();
result.Email = row["Email"].ToString();
result.Title = row["Title"].ToString();
result.SignatureId = row["SignatureID"] == DBNull.Value ? Guid.Empty : (Guid)row["SignatureID"];
result.ModifiedBy = row["ModifiedBy"] == DBNull.Value ? SqlInt32.Null.Value : (int)row["ModifiedBy"];
result.ModifiedUser = row["ModifiedUser"].ToString();
result.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
if (result.RoleId != null)
result.Role = GetRole(ref dbConnection, ref dbCommand, result.RoleId);
if (result.DepartmentId != null)
result.Department = GetDepartment(ref dbConnection, ref dbCommand, result.DepartmentId);
result.BaseKey = AppVars.baseKey;
result.Token = Guid.NewGuid();
row = null;
// Validate Administrator / If Validation fails Set Active to false and Clear Token Assignment
if (result.AdministratorYN)
{
if (Security.IsAuthorized(result.AdministratorKey, result.Username, result.BaseKey) == false)
result.Token = Guid.Empty;
}
this.AddUserToken(ref dbConnection, ref dbCommand, (int)result.UserId, result.Token);
}
returnDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return result;
}
catch
{
throw;
}
}
public int SaveSignature(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, User user, int userId)
{
try {
int returnValue = -1;
string sql = string.Empty;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.Clear();
if (user.Signature.SignatureId == Guid.Empty) {
//user.Signature.SignatureId = user.SignatureId;
dbCommand.CommandText = "uspInsertSignature";
}else {
dbCommand.CommandText = "uspUpdateSignature";
}
dbCommand.Parameters.Add("@SignatureId", System.Data.SqlDbType.UniqueIdentifier).Value = user.SignatureId;
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@Signature", System.Data.SqlDbType.VarChar).Value = user.Signature.Signature;
returnValue = dbConnection.ExecuteCommand(dbCommand);
return returnValue;
}catch {
throw;
}
}
public SmartCollection<User> GetUsersRecentRecords(int userId)
{
try
{
SmartCollection<User> resultList = new SmartCollection<User>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetUsersRecent";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
if (returnDT.Rows.Count > 0)
{
foreach (DataRow row in returnDT.Rows)
{
User result = new User();
result.UserId = Convert.ToInt32(row["UserID"]);
result.RoleId = row["RoleID"] == DBNull.Value ? Guid.Empty : (Guid)row["RoleID"];
result.DepartmentId = row["DepartmentID"] == DBNull.Value ? SqlInt32.Null.Value : (int)row["DepartmentID"];
result.AdministratorYN = Convert.ToBoolean(row["AdministratorYN"]);
result.Username = row["Username"].ToString();
result.FirstName = row["FirstName"].ToString();
result.LastName = row["LastName"].ToString();
result.Email = row["Email"].ToString();
result.Title = row["Title"].ToString();
result.SignatureId = row["SignatureID"] == DBNull.Value ? Guid.Empty : (Guid)row["SignatureID"];
result.ModifiedBy = row["ModifiedBy"] == DBNull.Value ? -1 : (int)row["ModifiedBy"];
result.ModifiedUser = row["ModifiedUser"].ToString();
result.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
if (result.RoleId != null)
result.Role = GetRole(ref dbConnection, ref dbCommand, result.RoleId);
if (result.DepartmentId != null)
result.Department = GetDepartment(ref dbConnection, ref dbCommand, result.DepartmentId);
resultList.Add(result);
}
}
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
public User GetUser(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? userId)
{
try
{
User result = new User();
if (dbConnection.IsConnected())
{
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetUser";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
if (returnDT.Rows.Count == 1)
{
DataRow row = returnDT.Rows[0];
result.UserId = Convert.ToInt32(row["UserID"]);
result.RoleId = row["RoleID"] == DBNull.Value ? Guid.Empty : (Guid)row["RoleID"];
result.DepartmentId = row["DepartmentID"] == DBNull.Value ? SqlInt32.Null.Value : (int)row["DepartmentID"];
result.AdministratorYN = Convert.ToBoolean(row["AdministratorYN"]);
result.Username = row["Username"].ToString();
result.FirstName = row["FirstName"].ToString();
result.LastName = row["LastName"].ToString();
result.Email = row["Email"].ToString();
result.Title = row["Title"].ToString();
result.SignatureId = row["SignatureID"] == DBNull.Value ? Guid.Empty : (Guid)row["SignatureID"];
result.ModifiedBy = row["ModifiedBy"] == DBNull.Value ? SqlInt32.Null.Value : (int)row["ModifiedBy"];
result.ModifiedUser = row["ModifiedUser"].ToString();
result.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
if (result.RoleId != null)
result.Role = GetRole(ref dbConnection, ref dbCommand, result.RoleId);
if (result.DepartmentId != null)
result.Department = GetDepartment(ref dbConnection, ref dbCommand, result.DepartmentId);
if (result.SignatureId != null)
result.Signature = GetSignature(ref dbConnection, ref dbCommand, (int)result.UserId);
row = null;
returnDT = null;
}
else
{
returnDT = null;
return null;
}
}
else
{
throw new Exception("Unable to Connect");
}
return result;
}
catch
{
throw;
}
}
public Oos GetOosData(string token, object recordId)
{
Oos result = new Oos();
User user = new User();
int userId = 0;
this.Token = token;
if (AppLib.DesignMode)
{
return new Oos();
}
else
{
try
{
using (SystemDAO systemDao = new SystemDAO())
{
userId = systemDao.GetToken(new Guid(this.Token));
}
if (userId > 0)
{
using (UserDAO userDao = new UserDAO())
{
user = userDao.GetUser(userId);
}
using (SampleDAO ordersDao = new SampleDAO())
{
result = ordersDao.GetOOS(Convert.ToInt32(recordId), new Identification() { Token = new Guid(this.Token), UserId = (int)user.UserId });
}
}
}
catch (Exception)
{
throw;
}
}
return result;
}
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;
}
}
public Order GetSubmission(string token, object orderId)
{
Order result = new Order();
User user = new User();
int userId = 0;
this.Token = token;
Identification ident = null;
if (AppLib.DesignMode)
{
return result;
}
else
{
try
{
using (SystemDAO systemDao = new SystemDAO())
{
userId = systemDao.GetToken(new Guid(this.Token));
}
if (userId > 0)
{
using (UserDAO userDao = new UserDAO())
{
user = userDao.GetUser(userId);
}
ident = new Identification() { Token = new Guid(this.Token), UserId = (int)user.UserId };
using (SampleDAO ordersDao = new SampleDAO())
{
//result = ordersDao.GetOrder(Convert.ToInt32(orderId), true, false, ident);
foreach (Sample sample in result.Samples)
{
string getSampleNotesSql = @"
SELECT orders_notes.id, orders_notes.parentid,orders_notes.txt,orders_notes.is_show_on_report,
orders_notes.html, orders_notes.linked_type, orders_notes.linked_id, orders_notes.isnotify,
(users.firstname + ' ' + users.lastname) as modifieduser,
orders_notes.modified_by, orders_notes.modified_date, orders_notes.created_by,
orders_notes.created_date, (users2.firstname + ' ' + users2.lastname) as createduser
FROM orders_notes
LEFT JOIN users ON orders_notes.modified_by = users.id
LEFT JOIN users as users2 ON orders_notes.created_by = users2.id
WHERE parentid = @ID AND orders_notes.delete_date IS NULL
AND orders_notes.linked_id = " + sample.ARLNumber.Value +
@" AND orders_notes.linked_type = '" + EnumNoteLink.Sample.ToString() + @"' ";
}
foreach (SampleTest test in result.SampleTests)
{
string getTestNotesSql = @"
SELECT orders_notes.id, orders_notes.parentid,orders_notes.txt,orders_notes.is_show_on_report,
orders_notes.html, orders_notes.linked_type, orders_notes.linked_id, orders_notes.isnotify,
(users.firstname + ' ' + users.lastname) as modifieduser,
orders_notes.modified_by, orders_notes.modified_date, orders_notes.created_by,
orders_notes.created_date, (users2.firstname + ' ' + users2.lastname) as createduser
FROM orders_notes
LEFT JOIN users ON orders_notes.modified_by = users.id
LEFT JOIN users as users2 ON orders_notes.created_by = users2.id
WHERE parentid = @ID AND orders_notes.delete_date IS NULL
AND orders_notes.linked_id = " + test.SampleTestId.Value +
@" AND orders_notes.linked_type = '" + EnumNoteLink.Test.ToString() + @"' ";
//test.ReportNotes = ordersDao.GetOrderNotes(test.ARLNumber.Value, ident, getTestNotesSql);
}
string getOrderNotesSql = @"
SELECT orders_notes.id, orders_notes.parentid,orders_notes.txt,orders_notes.is_show_on_report,
orders_notes.html, orders_notes.linked_type, orders_notes.linked_id, orders_notes.isnotify,
(users.firstname + ' ' + users.lastname) as modifieduser,
orders_notes.modified_by, orders_notes.modified_date, orders_notes.created_by,
orders_notes.created_date, (users2.firstname + ' ' + users2.lastname) as createduser
FROM orders_notes
LEFT JOIN users ON orders_notes.modified_by = users.id
LEFT JOIN users as users2 ON orders_notes.created_by = users2.id
WHERE parentid = @ID AND orders_notes.delete_date IS NULL
AND orders_notes.linked_id = 0
AND orders_notes.linked_type = '" + EnumNoteLink.None.ToString() + @"' ";
//result.Notes = ordersDao.GetOrderNotes(result.Id.Value, ident, getOrderNotesSql);
}
}
}
catch (Exception)
{
throw;
}
}
return result;
}
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;
}
}
public IEnumerable<IResult> LoadViewGeneral()
{
if (this.IsLoadedGeneral)
yield break;
//TestSecurity();
BusyOn("Loading...");
GetRolesRequest getEmployeeRolesRequest = new GetRolesRequest(false);
yield return getEmployeeRolesRequest;
if (getEmployeeRolesRequest.Error != null) {
yield return new HandleExceptionResult(getEmployeeRolesRequest.Error);
BusyOff();
yield break;
}
EmployeeRoles.AddRange(getEmployeeRolesRequest.Result);
GetRolesRequest getClientRolesRequest = new GetRolesRequest(false);
yield return getClientRolesRequest;
if (getClientRolesRequest.Error != null)
{
yield return new HandleExceptionResult(getClientRolesRequest.Error);
BusyOff();
yield break;
}
ClientRoles.AddRange(getClientRolesRequest.Result);
// Initialize Objects here
switch (AssistantMode) {
case ViewMode.Add:
this.User = new User();
break;
case ViewMode.Edit:
break;
default:
break;
}
UpdateSummaryGeneral();
NotifyOfPropertyChange(() => User.Role);
this.User.IsTrackChange = true;
this.User.Signature.IsTrackChange = true;
BusyOff();
this.IsLoadedGeneral = true;
}
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;
}
}
public int DoSaveUser(User user, Identification identification)
{
logger.Log(LogLevel.Trace, AppLib.GetCaller(logger));
//Validate Object
if (!user.ValidateModel())
throw new FaultException<ServiceFault>(new ServiceFault(user.CurrentErrors), new FaultReason(SysVars.InvalidFormat));
if (user.UserId == null) {
using (UserDAO dao = new UserDAO()) {
if (dao.IsDuplicateUserName(user.Username))
throw new FaultException<ServiceFault>(new ServiceFault("Duplicate User Name", "Rejected"), new FaultReason("Duplicate User Name Found."));
}
}
using (UserDAO dao = new UserDAO()) {
return dao.SaveUser(user, identification.Token, identification.UserId);
}
}
public SaveUserRequest(User user)
{
this.user = user;
}