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