public static Surgical.SurgicalMasterLogList GetSurgicalMasterLogList(DateTime reportDate)
{
Surgical.SurgicalMasterLogList result = new Surgical.SurgicalMasterLogList();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "declare @rpts table " +
"( " +
"AccessionTime datetime, " +
"ReportNo varchar(20), " +
"AccessioningFacilityId varchar(100), " +
"PFirstName varchar(100), " +
"PLastName varchar(100), " +
"PBirthdate datetime, " +
"PhysicianName varchar(100), " +
"ClientName varchar(100), " +
"AliquotCount int " +
") " +
"insert @rpts " +
"SELECT Distinct a.AccessionTime, pso.ReportNo, a.AccessioningFacilityId, a.PFirstName, a.PLastName, " +
"a.PBirthdate, a.PhysicianName, a.ClientName, Count(*) AliquotCount " +
"FROM tblAccessionOrder a JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo " +
"JOIN tblSpecimenORder so on a.MasterAccessionNo = so.MasterAccessionNo " +
"LEFT OUTER JOIN tblAliquotOrder ao on so.SpecimenOrderId = ao.SpecimenOrderId " +
"WHERE AccessionDate = @ReportDate and pso.PanelSetId in (13, 50) " +
" group by a.AccessionTime, pso.ReportNo, a.AccessioningFacilityId, a.PFirstName, a.PLastName, " +
"a.PBirthdate, a.PhysicianName, a.ClientName " +
"Order By AccessionTime " +
"SELECT rpts.* From @rpts rpts Order By AccessionTime " +
"Select ssr.DiagnosisId, so.Description, ssr.ReportNo " +
"FROM tblSurgicalSpecimen ssr " +
"JOIN tblSpecimenOrder so ON ssr.SpecimenOrderId = so.SpecimenOrderId " +
"join @rpts rpts on ssr.ReportNo = rpts.ReportNo order by 1";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@ReportDate", SqlDbType.VarChar, 20).Value = reportDate.ToShortDateString();
using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
{
cn.Open();
cmd.Connection = cn;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
YellowstonePathology.Business.Surgical.SurgicalMasterLogItem surgicalMasterLogItem = new Surgical.SurgicalMasterLogItem();
YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(surgicalMasterLogItem, dr);
sqlDataReaderPropertyWriter.WriteProperties();
result.Add(surgicalMasterLogItem);
}
dr.NextResult();
while (dr.Read())
{
YellowstonePathology.Business.Surgical.MasterLogItem masterLogItem = new Surgical.MasterLogItem();
YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(masterLogItem, dr);
sqlDataReaderPropertyWriter.WriteProperties();
foreach(Surgical.SurgicalMasterLogItem surgicalMasterLogItem in result)
{
if(masterLogItem.ReportNo == surgicalMasterLogItem.ReportNo)
{
surgicalMasterLogItem.MasterLogList.Add(masterLogItem);
break;
}
}
}
}
}
return result;
}
public static Surgical.SurgicalMasterLogList GetSurgicalMasterLogList(DateTime reportDate)
{
Surgical.SurgicalMasterLogList result = new Surgical.SurgicalMasterLogList();
StringBuilder xmlString = new StringBuilder();
SqlCommand cmd = new SqlCommand();
StringBuilder commandText = new StringBuilder();
commandText.Append("With rpts as(");
commandText.Append("SELECT Distinct a.AccessionTime, pso.ReportNo, a.AccessioningFacilityId, a.PFirstName, a.PLastName, a.PBirthdate, a.PhysicianName, a.ClientName, Count(*) AliquotCount ");
commandText.Append("FROM tblAccessionOrder a JOIN tblPanelSetOrder pso ON a.MasterAccessionNo = pso.MasterAccessionNo ");
commandText.Append("JOIN tblSpecimenORder so on a.MasterAccessionNo = so.MasterAccessionNo ");
commandText.Append("LEFT OUTER JOIN tblAliquotOrder ao on so.SpecimenOrderId = ao.SpecimenOrderId ");
commandText.Append("WHERE AccessionDate = @ReportDate and pso.PanelSetId in (13, 50) ");
commandText.Append(" group by a.AccessionTime, pso.ReportNo, a.AccessioningFacilityId, a.PFirstName, a.PLastName, a.PBirthdate, a.PhysicianName, a.ClientName");
commandText.Append(") ");
commandText.Append("SELECT rpts.*, ");
commandText.Append("(Select ssr.DiagnosisId, so.Description ");
commandText.Append("FROM tblSurgicalSpecimen ssr ");
commandText.Append("JOIN tblSpecimenOrder so ON ssr.SpecimenOrderId = so.SpecimenOrderId ");
commandText.Append("WHERE ssr.ReportNo = rpts.ReportNo for xml path('MasterLogItem'), type) MasterLogList ");
commandText.Append("From rpts Order By AccessionTime for xml path('SurgicalMasterLogItem'), type, root('SurgicalMasterLogList')");
cmd.CommandText = commandText.ToString();
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@ReportDate", SqlDbType.VarChar, 20).Value = reportDate.ToShortDateString();
using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.Properties.Settings.Default.CurrentConnectionString))
{
cn.Open();
cmd.Connection = cn;
using (XmlReader xmlReader = cmd.ExecuteXmlReader())
{
while (xmlReader.Read())
{
xmlString.Append(xmlReader.ReadOuterXml());
}
}
}
XElement resultElement = XElement.Parse(xmlString.ToString(), LoadOptions.PreserveWhitespace);
List<XElement> surgicalMasterLogItemElements = (from item in resultElement.Elements("SurgicalMasterLogItem")
select item).ToList<XElement>();
foreach (XElement surgicalMasterLogItemElement in surgicalMasterLogItemElements)
{
YellowstonePathology.Business.Surgical.SurgicalMasterLogItem surgicalMasterLogItem = new Surgical.SurgicalMasterLogItem();
YellowstonePathology.Business.Persistence.XmlPropertyWriter xmlPropertyWriter = new Persistence.XmlPropertyWriter(surgicalMasterLogItemElement, surgicalMasterLogItem);
xmlPropertyWriter.Write();
List<XElement> masterLogListElements = (from item in surgicalMasterLogItemElement.Elements("MasterLogList")
select item).ToList<XElement>();
foreach (XElement masterLogListElement in masterLogListElements)
{
List<XElement> masterLogItemElements = (from item in masterLogListElement.Elements("MasterLogItem")
select item).ToList<XElement>();
foreach (XElement masterLogItemElement in masterLogItemElements)
{
YellowstonePathology.Business.Surgical.MasterLogItem masterLogItem = new Surgical.MasterLogItem();
YellowstonePathology.Business.Persistence.XmlPropertyWriter xmlPropertyWriter1 = new Persistence.XmlPropertyWriter(masterLogItemElement, masterLogItem);
xmlPropertyWriter1.Write();
surgicalMasterLogItem.MasterLogList.Add(masterLogItem);
}
}
result.Add(surgicalMasterLogItem);
}
return result;
}