public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByInHouseMolecularPending()
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "gwAccessionOrderListByInHouseMolecularPending_2";
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByPatientId(List <object> parameters)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "gwAccessionOrderListByPatientId_2";
cmd.Parameters.AddWithValue("PatientId", parameters[0].ToString());
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByDateRange(List <object> parameters)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "gwAccessionOrderListByCurrentMonthFill_2";
cmd.Parameters.AddWithValue("StartDate", parameters[0]);
cmd.Parameters.AddWithValue("EndDate", parameters[1]);
cmd.Parameters.AddWithValue("PanelSetId", parameters[2]);
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByPendingTests()
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT pso.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo " +
"FROM tblAccessionOrder a JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"WHERE pso.Final = 0 and pso.ExpectedFinalTime < curdate() order by pso.PanelSetId, pso.ReportNo;";
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByDrKurtzman()
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT pso.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"FROM tblAccessionOrder a " +
"join tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"where a.ClientId = 1520 Order By pso.OrderDate desc;";
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByITAudit(YellowstonePathology.Business.Test.ITAuditPriorityEnum itAuditPriority)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT a.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"FROM tblAccessionOrder a " +
"Left outer JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"Left Outer JOIN tblSystemUser su on pso.OrderedById = su.UserId " +
"WHERE a.ITAuditRequired = 1 and a.ITAudited = 0 and a.ITAuditPriority = @ITAuditPriority;";
cmd.Parameters.AddWithValue("@ITAuditPriority", (int)itAuditPriority);
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByChangesNotPosted()
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT pso.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"FROM tblAccessionOrder a JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"WHERE pso.Final = 1 and pso.IsPosted = 1 and pso.OrderDate >= '2018-06-01' and pso.IsBillable = 1 " +
"and exists (select null from tblPanelSetOrderCPTCode where postdate is null and ReportNo = pso.ReportNo) " +
"Order By pso.FinalDate, pso.PanelSetId, a.AccessionTime;";
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByPatientName(List <object> parameters)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "pReportSearchListByPatientName_2";
cmd.Parameters.AddWithValue("PLastName", parameters[0].ToString());
if (parameters[1] == null)
{
cmd.Parameters.AddWithValue("PFirstName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("PFirstName", parameters[1].ToString());
}
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByAliquotOrderId(string aliquotOrderId)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT pso.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"FROM tblAccessionOrder a " +
"JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"WHERE a.MasterAccessionNo in " +
"(Select MasterAccessionNo from tblSpecimenOrder so join tblAliquotOrder ao on so.SpecimenOrderId = ao.SpecimenOrderId " +
"where ao.AliquotOrderId = @AliquotOrderId);";
cmd.Parameters.AddWithValue("@AliquotOrderId", aliquotOrderId);
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByAccessionDate(DateTime accessionDate, List <int> panelSetIdList)
{
string panelSetIdString = YellowstonePathology.Business.Helper.IdListHelper.ToIdString(panelSetIdList);
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT pso.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"FROM tblAccessionOrder a JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"WHERE a.AccessionDate = @AccessionDate " +
"And pso.PanelSetId in (" + panelSetIdString + ")" +
"ORDER BY AccessionTime desc;";
cmd.Parameters.AddWithValue("@AccessionDate", accessionDate);
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByPostDate(DateTime postDate)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT distinct pso.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"FROM tblAccessionOrder a " +
"JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"join tblPanelSetOrderCPTCodeBill psocpt on pso.ReportNo = psocpt.ReportNo " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"WHERE pso.IsPosted = 1 and psocpt.PostDate = @PostDate Order By pso.FinalDate, pso.PanelSetId, a.AccessionTime;";
cmd.Parameters.AddWithValue("@PostDate", postDate);
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetPossibleRetrospectiveReviews(DateTime finalDate)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT pso.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"FROM tblAccessionOrder a " +
"JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"WHERE pso.FinalDate = @FinalDate and pso.PanelSetId = 13 " +
"and exists (select null from tblPanelSetOrderCPTCode where reportNo = pso.ReportNo and cptCode = '88305') " +
"and exists (select null from tblSpecimenOrder where masterAccessionNo = a.MasterAccessionNo and LOWER(description) REGEXP 'esophagus|ge junction|gastroesophageal junction|stomach|small bowel|duodenum|jejunum|ampulla|common bile duct|ileum|terminal ileum|ileocecal valve|cecum|colon|rectum|anus|cervix|endocervix|endometrium|v****a|vulva|perineum|labia majora|labia minora|ovary|fallopian tube|skin|lung|bronchus|larynx|vocal cord|oral mucosa|oral cavity|tongue|gingiva|pharynx|epiglottis|kidney|nasopharynx|oropharynx|peritoneum|pleura|tonsil|trachea|ureter|urethra|bladder') " +
"ORDER BY AccessionTime desc;";
cmd.Parameters.AddWithValue("@FinalDate", finalDate);
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByTest(int panelSetId, DateTime startDate, DateTime endDate)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT pso.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"FROM tblAccessionOrder a " +
"JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"WHERE pso.PanelSetId = @PanelSetId " +
"and pso.OrderDate between @StartDate and @EndDate;";
cmd.Parameters.AddWithValue("@PanelSetId", panelSetId);
cmd.Parameters.AddWithValue("@StartDate", startDate);
cmd.Parameters.AddWithValue("@EndDate", endDate);
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListBySVHFinalNotPosted()
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT pso.MasterAccessionNo, pso.ReportNo, ao.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(ao.PFirstName, ' ', ao.PLastName) AS PatientName, " +
"ao.PLastName, ao.PFirstName, ao.ClientName, ao.PhysicianName, ao.PBirthdate, pso.FinalTime FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"from tblAccessionOrder ao " +
"join tblPanelSetOrder pso on ao.MasterAccessionNo = pso.MasterAccessionNo " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"where pso.Final = 1 " +
"and ao.SvhMedicalRecord like 'V%' " +
"and exists (Select null from tblPanelSetOrderCPTCode where ReportNo = pso.ReportNo and postDate is null) " +
"and pso.panelSetId = 13 " +
"and pso.OrderDate >= '2014-1-1' " +
"Order By pso.FinalDate, pso.PanelSetId, ao.AccessionTime;";
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList BuildReportSearchList(MySqlCommand cmd)
{
Search.ReportSearchList result = new Search.ReportSearchList();
using (MySqlConnection cn = new MySqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
{
cn.Open();
cmd.Connection = cn;
using (MySqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Search.ReportSearchItem reportSearchItem = new Search.ReportSearchItem();
YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(reportSearchItem, dr);
sqlDataReaderPropertyWriter.WriteProperties();
result.Add(reportSearchItem);
}
}
}
return(result);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListByPositiveHPylori(DateTime startDate, DateTime endDate)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT distinct pso.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"FROM tblAccessionOrder a " +
"join tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"join tblPanelOrder po on pso.ReportNo = po.ReportNo " +
"join tblTestOrder t on po.panelOrderId = t.panelOrderId " +
"join tblStainResult sr on t.TestOrderId = sr.TestOrderId " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"where t.TestId = 107 and sr.Result = 'Positive' and a.AccessionDate between @StartDate and @EndDate;";
cmd.Parameters.AddWithValue("@StartDate", startDate);
cmd.Parameters.AddWithValue("@EndDate", endDate);
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
public static YellowstonePathology.Business.Search.ReportSearchList GetReportSearchListBySpecimenKeyword(string specimenDescription, DateTime startDate, DateTime endDate)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT pso.MasterAccessionNo, pso.ReportNo, a.AccessionTime AccessionDate, pso.PanelSetId, " +
"concat(a.PFirstName, ' ', a.PLastName) AS PatientName, " +
"a.PLastName, a.PFirstName, a.ClientName, a.PhysicianName, a.PBirthdate, pso.FinalDate, pso.PanelSetName, su.UserName as OrderedBy, " +
"'' ForeignAccessionNo, pso.IsPosted " +
"FROM tblAccessionOrder a " +
"JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"Left Outer Join tblSystemUser su on pso.OrderedById = su.UserId " +
"WHERE a.MasterAccessionNo in " +
"(Select MasterAccessionNo from tblSpecimenOrder so where so.masterAccessionNo = a.masterAccessionNo " +
"and locate(@SpecimenDescription, so.Description) > 0) " +
"and a.AccessionDate between @StartDate and @EndDate;";
cmd.Parameters.AddWithValue("@SpecimenDescription", specimenDescription);
cmd.Parameters.AddWithValue("@StartDate", startDate);
cmd.Parameters.AddWithValue("@EndDate", endDate);
Search.ReportSearchList reportSearchList = BuildReportSearchList(cmd);
return(reportSearchList);
}
private static YellowstonePathology.Business.Search.ReportSearchList BuildReportSearchList(SqlCommand cmd)
{
Search.ReportSearchList result = new Search.ReportSearchList();
using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
{
cn.Open();
cmd.Connection = cn;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Search.ReportSearchItem reportSearchItem = new Search.ReportSearchItem();
YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(reportSearchItem, dr);
sqlDataReaderPropertyWriter.WriteProperties();
result.Add(reportSearchItem);
}
}
}
return result;
}