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