public static YellowstonePathology.Business.Client.Model.ProviderClientCollection GetHomeBaseProviderClientListByProviderLastName(string physicianLastName)
{
YellowstonePathology.Business.Client.Model.ProviderClientCollection result = new Client.Model.ProviderClientCollection();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select p.*, " +
"(select pc.* from tblPhysicianClient pc where p.ObjectId = pc.ProviderId and pc.ClientId = p.HomeBaseClientId for xml Path('ProviderClient'), type), " +
"(select c.* from tblClient c where c.ClientId = p.HomeBaseClientId for xml Path('Client'), type) "+
"from tblPhysician p where p.LastName like @LastName + '%' order by p.LastName, p.FirstName for xml Path('Physician'), root('ProviderClientCollection')";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = physicianLastName;
XElement providerClientCollectionElement = GetXElementFromCommand(cmd);
if (providerClientCollectionElement != null)
{
List<XElement> providerElements = providerClientCollectionElement.Elements("Physician").ToList();
foreach (XElement providerElement in providerElements)
{
YellowstonePathology.Business.Client.Model.ProviderClient providerClient = BuildHomeBaseProviderClient(providerElement);
result.Add(providerClient);
}
}
return result;
}
/*private static YellowstonePathology.Business.Client.Model.ProviderClient BuildHomeBaseProviderClient(XElement providerElement)
{
YellowstonePathology.Business.Client.Model.ProviderClient result = new Client.Model.ProviderClient();
YellowstonePathology.Business.Domain.Physician physician = new Domain.Physician();
YellowstonePathology.Business.Persistence.XmlPropertyWriter xmlPropertyWriterP = new Persistence.XmlPropertyWriter(providerElement, physician);
xmlPropertyWriterP.Write();
result.Physician = physician;
XElement clientElement = providerElement.Element("Client");
if (clientElement != null)
{
YellowstonePathology.Business.Client.Model.Client client = new Client.Model.Client();
YellowstonePathology.Business.Persistence.XmlPropertyWriter xmlPropertyWriterC = new Persistence.XmlPropertyWriter(clientElement, client);
xmlPropertyWriterC.Write();
result.Client = client;
}
XElement providerClientElement = providerElement.Element("ProviderClient");
if (providerClientElement != null)
{
YellowstonePathology.Business.Persistence.XmlPropertyWriter xmlPropertyWriterPC = new Persistence.XmlPropertyWriter(providerClientElement, result);
xmlPropertyWriterPC.Write();
}
return result;
}*/
private static YellowstonePathology.Business.Client.Model.ProviderClientCollection BuildProviderClientCollection(SqlCommand cmd)
{
YellowstonePathology.Business.Client.Model.ProviderClientCollection result = new Client.Model.ProviderClientCollection();
YellowstonePathology.Business.Client.Model.ClientCollection clientCollection = new YellowstonePathology.Business.Client.Model.ClientCollection();
using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
{
cn.Open();
cmd.Connection = cn;
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo))
{
while (dr.Read())
{
YellowstonePathology.Business.Client.Model.ProviderClient providerClient = new Client.Model.ProviderClient();
YellowstonePathology.Business.Domain.Physician physician = new Domain.Physician();
Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(physician, dr);
sqlDataReaderPropertyWriter.WriteProperties();
providerClient.Physician = physician;
result.Add(providerClient);
}
dr.NextResult();
while (dr.Read())
{
YellowstonePathology.Business.Client.Model.Client client = new YellowstonePathology.Business.Client.Model.Client();
Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(client, dr);
sqlDataReaderPropertyWriter.WriteProperties();
clientCollection.Add(client);
}
dr.NextResult();
while (dr.Read())
{
string providerClientId = dr["PhysicianClientId"].ToString();
string physicianObjectId = dr["ProviderId"].ToString();
foreach(Client.Model.ProviderClient providerClient in result)
{
if(providerClient.Physician.ObjectId == physicianObjectId)
{
providerClient.PhysicianClientId = providerClientId;
break;
}
}
}
}
}
foreach (Client.Model.ProviderClient providerClient in result)
{
foreach (Client.Model.Client client in clientCollection)
{
if (client.ClientId == providerClient.Physician.HomeBaseClientId)
{
providerClient.Client = client;
break;
}
}
}
return result;
}
