public override DataSet Clone()
{
ServiceabilitySchema cln = ((ServiceabilitySchema)(base.Clone()));
cln.InitVars();
return(cln);
}
/// <summary>
/// Searches an ICOMS <siteAbbr>_All_House_Master table for a given address
/// and returns serviceability for each address that was found
/// </summary>
/// <param name="siteAbbr"></param>
/// <param name="streetNumber"></param>
/// <param name="streetNumberFraction"></param>
/// <param name="preDirectional"></param>
/// <param name="streetName"></param>
/// <param name="Apt"></param>
/// <param name="zip5"></param>
/// <param name="zip4"></param>
/// <param name="productcodesDT"></param>
/// <param name="productcodesAC"></param>
/// <param name="productcodesHSD"></param>
/// <param name="productcodesDC"></param>
/// <param name="productcodesST"></param>
/// <param name="productcodesVT"></param>
/// <returns></returns>
public ServiceabilitySchema.ICOMSAddressesDataTable FindAddressAndServiceabilityInICOMS(
string siteAbbr, string streetNumber, string streetNumberFraction, string preDirectional,
string streetName, string Apt, string zip5, string zip4, string productcodesDT,
string productcodesAC, string productcodesHSD, string productcodesDC, string productcodesST,
string productcodesVT)
{
siteAbbr = siteAbbr.Trim();
try
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// create the sql statement
StringBuilder sql = new StringBuilder();
sql.Append("SELECT a.SITE_ID, a.COMPANY_NUMBER, a.DIVISION_NUMBER, a.FRANCHISE_NUMBER, a.HOUSE_NUMBER, ");
sql.Append(" a.ADDR_LOCATION, a.FRACTION, a.PRE_DIRECTIONAL, a.STREET, a.ADDR_POST_DIRECTIONAL, a.ADDRESS_LINE_1, a.ADDRESS_LINE_2, a.ADDRESS_LINE_3, a.ADDRESS_LINE_4, a.APARTMENT, a.BUILDING, a.ADDR_CITY, a.ADDR_STATE, a.ADDR_ZIP_5, a.ADDR_ZIP_4, ");
sql.Append(" a.DWELLING_TYPE, a.HOUSE_RESIDENT_NUMBER, ");
sql.Append(" CASE a.HOUSE_STATUS WHEN '2' THEN 1 ELSE 0 END AS HasCustomer, ");
sql.AppendFormat(" CASE WHEN (SELECT COUNT(*) FROM {0}_HOUSE_MASTER_SRV_CATEGORY b WHERE serviceable_status_code IN ({1}, {2}) AND a.HOUSE_NUMBER = b.HOUSE_NUMBER) > 0 THEN 1 ELSE 0 END AS HasBasicCable, ", siteAbbr, productcodesAC, productcodesDC);
sql.AppendFormat(" CASE WHEN (SELECT COUNT(*) FROM {0}_HOUSE_MASTER_SRV_CATEGORY b WHERE serviceable_status_code IN({1}) AND a.HOUSE_NUMBER = b.HOUSE_NUMBER) > 0 THEN 1 ELSE 0 END AS HasDigitalCable, ", siteAbbr, productcodesDC);
sql.AppendFormat(" CASE WHEN (SELECT COUNT(*) FROM {0}_HOUSE_MASTER_SRV_CATEGORY b WHERE serviceable_status_code IN({1}) AND a.HOUSE_NUMBER = b.HOUSE_NUMBER) > 0 THEN 1 ELSE 0 END AS HasHSI, ", siteAbbr, productcodesHSD);
sql.AppendFormat(" CASE WHEN (SELECT COUNT(*) FROM {0}_HOUSE_MASTER_SRV_CATEGORY b WHERE serviceable_status_code IN({1}) AND a.HOUSE_NUMBER = b.HOUSE_NUMBER) > 0 THEN 1 ELSE 0 END AS HasTelephony, ", siteAbbr, productcodesDT);
sql.AppendFormat(" CASE WHEN (SELECT COUNT(*) FROM {0}_HOUSE_MASTER_SRV_CATEGORY b WHERE serviceable_status_code IN({1}) AND a.HOUSE_NUMBER = b.HOUSE_NUMBER) > 0 THEN 1 ELSE 0 END AS HasSwitchedTelephony, ", siteAbbr, productcodesST);
sql.AppendFormat(" CASE WHEN (SELECT COUNT(*) FROM {0}_HOUSE_MASTER_SRV_CATEGORY b WHERE serviceable_status_code IN({1}) AND a.HOUSE_NUMBER = b.HOUSE_NUMBER) > 0 THEN 1 ELSE 0 END AS HasVoipTelephony ", siteAbbr, productcodesVT);
sql.AppendFormat("FROM {0}_HOUSE_MASTER a ", siteAbbr);
sql.Append("WHERE a.ADDR_LOCATION = :pStreetNumber ");
sql.Append(" AND UPPER(a.STREET) LIKE :pStreetName ");
sql.Append(" AND a.ADDR_ZIP_5 = :pZip5 ");
sql.Append(" AND a.UNSERVICEABLE_ADDRESS = 'N' ");
sql.Append(" AND a.DWELLING_TYPE IN ('A','C','H','L','N','T','U','V','W','1','2','3','4') ");
// conditional parameters
if (streetNumberFraction.Trim().Length > 0)
{
sql.Append("AND a.FRACTION = :pStreetFraction ");
}
if (preDirectional.Trim().Length > 0)
{
sql.Append("AND a.PRE_DIRECTIONAL LIKE :pPreDirectional ");
}
if (Apt.Trim().Length > 0)
{
sql.Append("AND a.APARTMENT LIKE :pApt ");
}
if (zip4.Trim().Length > 0)
{
sql.Append("AND a.ADDR_ZIP_4 = :pZip4 ");
}
// open connection
try{ oracleConn.Open(); }
catch { throw new LogonException(); }
// build the command object
using (OracleCommand cmd = new OracleCommand(sql.ToString(), oracleConn))
{
cmd.CommandType = CommandType.Text;
// build the dataadapter
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
//add parameters
string tempParamValue;
da.SelectCommand.Parameters.Add("pStreetNumber", OracleType.VarChar).Value = streetNumber.Trim().PadLeft(6, ' ');
tempParamValue = streetName.ToUpper().Trim() + "%";
da.SelectCommand.Parameters.Add("pStreetName", OracleType.VarChar).Value = tempParamValue;
da.SelectCommand.Parameters.Add("pZip5", OracleType.VarChar).Value = zip5.Trim();
//add conditional parameters
if (streetNumberFraction.Trim().Length > 0)
{
da.SelectCommand.Parameters.Add("pStreetFraction", OracleType.VarChar).Value = streetNumberFraction.Trim();
}
if (preDirectional.Trim().Length > 0)
{
tempParamValue = "%" + preDirectional.Trim();
da.SelectCommand.Parameters.Add("pPreDirectional", OracleType.VarChar).Value = tempParamValue;
}
if (Apt.Trim().Length > 0)
{
tempParamValue = "%" + Apt.Trim();
da.SelectCommand.Parameters.Add("pApt", OracleType.VarChar).Value = tempParamValue;
}
if (zip4.Trim().Length > 0)
{
da.SelectCommand.Parameters.Add("pZip4", OracleType.VarChar).Value = zip4.Trim();
}
// create the dataset to fill
ServiceabilitySchema ds = new ServiceabilitySchema();
// now fill it
da.Fill(ds.ICOMSAddresses);
// all done, return
return(ds.ICOMSAddresses);
}
}
}
}
catch (LogonException)
{
// just rethrow it. it is from our internal code block
throw;
}
catch (Exception ex)
{
// DataSourceException.
throw new DataSourceException(ex);
}
}
