//[02-02-09] Start Changes for Q-matic
/// <summary>
/// Gets the account number and site Id matches for the input phoneNumber and street.
/// </summary>
/// <param name="phoneNumber10">Phone number passed in</param>
/// <param name="streetNumber">Street Number</param>
/// <returns></returns>
public CustomerAccountProfileSchema.AccountMatchesDataTable GetAccountMatches(string phoneNumber10, string streetNumber)
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try { oracleConn.Open(); }
catch (Exception ex)
{
throw new LogonException(ex);
}
string sqlProcedureName = "GETACCOUNTBYPHONEANDSTREET";
// build the command object
using (OracleCommand cmd = new OracleCommand(sqlProcedureName, oracleConn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("nbrPhoneNbrIn", OracleType.Number, 10).Value = Convert.ToUInt64(phoneNumber10);
OracleParameter param = new OracleParameter();
param.ParameterName = "street";
param.OracleType = OracleType.VarChar;
param.IsNullable = true;
if (!String.IsNullOrEmpty(streetNumber))
{
cmd.Parameters.Add(param).Value = streetNumber;
}
else
{
cmd.Parameters.Add(param).Value = System.DBNull.Value;
}
cmd.Parameters.Add("rfcAccountDetails", OracleType.Cursor);
cmd.Parameters["rfcAccountDetails"].Direction = ParameterDirection.Output;
OracleParameter errorCodeOut = cmd.Parameters.Add("errorCode", OracleType.Number);
errorCodeOut.Direction = ParameterDirection.Output;
OracleParameter errorTextOut = cmd.Parameters.Add("errorMessage", OracleType.VarChar, 2000);
errorTextOut.Direction = ParameterDirection.Output;
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
// create the dataset to fill
CustomerAccountProfileSchema ds = new CustomerAccountProfileSchema();
// now fill it
try
{
da.Fill(ds.AccountMatches);
}
catch (Exception ex)
{
throw new DataSourceException(ex);
}
// all done, return
return(ds.AccountMatches);
}
}
}
}
/// <summary>
/// Given a siteId (up to 3 digits) and the accountNumber (up to 9 digits), this method
/// contructs and fills a strongly typed CustomerContractDataTable. Using this DataTable
/// does not require any knowledge of the underlying datasource. Nor does it require knowledge
/// of the underlying data access libraries (e.g. SqlClient, OleDb, OracleClient, etc. ).
/// </summary>
/// <param name="siteId">Site Id for the customer</param>
/// <param name="accountNumber9">9 digit customer account</param>
/// <returns><CustomerAccountProfileSchema.CustomerContractDataTable></returns>
/// <exception cref="LogonException">Thrown when a problem occurred
/// trying to connect to the underlying datasource.</exception>
/// <exception cref="DataSourceException">Thrown when a problem occurred
/// trying to interact with the underlying datasource after a connection
/// has been established. Check the inner exception for further meaning
/// as to why the problem occurred.</exception>
public CustomerAccountProfileSchema.CustomerContractDataTable GetCustomerContract(int siteId, string accountNumber9)
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try { oracleConn.Open(); }
catch (Exception ex)
{
throw new LogonException(ex);
}
//Changes for adding service categories for contract starts here
string sql = "GETCUSTOMERCONTRACTINFO";
// build the command object
using (OracleCommand cmd = new OracleCommand(sql, oracleConn))
{
cmd.CommandType = CommandType.StoredProcedure;
//add the params
cmd.Parameters.Add("nbrSIDIn", OracleType.Number).Value = siteId;
cmd.Parameters.Add("nbrAccountNbrIn", OracleType.Number).Value = Convert.ToInt32(accountNumber9);
OracleParameter errorCodeOut = cmd.Parameters.Add("nbrErrorOut", OracleType.Number);
errorCodeOut.Direction = ParameterDirection.Output;
OracleParameter errorTextOut = cmd.Parameters.Add("vchErrorOut", OracleType.VarChar, 2000);
errorTextOut.Direction = ParameterDirection.Output;
cmd.Parameters.Add("rfcCustCntrctOut", OracleType.Cursor);
cmd.Parameters["rfcCustCntrctOut"].Direction = ParameterDirection.Output;
// build the dataadapter
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
// create the dataset to fill
CustomerAccountProfileSchema ds = new CustomerAccountProfileSchema();
// now fill it
try
{
da.Fill(ds.CustomerContract);
}
catch (Exception ex)
{
throw new DataSourceException(ex);
}
if (int.Parse(cmd.Parameters["nbrErrorOut"].Value.ToString()) != 0)
{
throw new DataSourceException(cmd.Parameters["vchErrorOut"].Value.ToString());
}
// all done, return
return(ds.CustomerContract);
}
}
}
}
//Added new method on 4-Feb-2010
/// <summary>
/// GetCustomerAccountProfile
/// </summary>
/// <param name="siteId"></param>
/// <param name="accountNumber9"></param>
/// <returns></returns>
private CustomerAccountProfile GetCustomerAccountProfile(int siteId, string accountNumber9)
{
/// set the siteid/sitecode information
PopulateSiteInfo(siteId);
DalAccount dalAccount = new DalAccount();
if (!dalAccount.IsAccountNumberValid(siteId, accountNumber9))
{
throw new Cox.BusinessLogic.Exceptions.InvalidAccountNumberException();
}
CompanyDivisionFranchise cdf = new CompanyDivisionFranchise();
try
{
cdf = dalAccount.GetCompanyDivisionFranchise(_siteId,
_siteCode, accountNumber9);
}
catch (Exception e)
{
throw new DataSourceUnavailableException(e);
}
// convert to customerAccountNumber object
CustomerAccountNumber accountNumber = new CustomerAccountNumber(
string.Empty, cdf.Company.ToString(), cdf.Division.ToString(),
accountNumber9);
DalCustomerAccount dalCustomerAccount = new DalCustomerAccount();
// call dal
//[28-01-2009] Start Changes to reflect services for an account
CustomerAccountProfileSchema customerAccountSchema = dalCustomerAccount.GetAccountProfile(siteId, accountNumber9);
CustomerAccountProfileSchema.CustomerAccountDataTable customerAccountDT = customerAccountSchema.CustomerAccount;
CustomerAccountProfileSchema.CustomerServicesDataTable customerServicesDataTable = customerAccountSchema.CustomerServices;
CustomerAccountProfile customerAccountProfile = new CustomerAccountProfile();
if (customerAccountDT != null && customerAccountDT.Rows.Count > 0)
{
BuildCustomerAccountBase(customerAccountDT, customerAccountProfile);
BuildCustomerAccountCampaign(siteId, accountNumber9, dalCustomerAccount, customerAccountProfile);
BuildCustomerAccountStatement(accountNumber9, accountNumber, customerAccountProfile);
BuildCustomerAccountContract(siteId, accountNumber9, dalCustomerAccount, customerAccountProfile);
BuildCustomerAccountPhones(siteId, accountNumber9, dalCustomerAccount, customerAccountProfile);
BuildCustomerAccountPrivacy(siteId, accountNumber9, dalCustomerAccount, customerAccountProfile);
BuildCustomerAccountCCRMInfo(siteId, accountNumber9, customerAccountProfile);
BuildContactEmail(siteId, accountNumber9, dalCustomerAccount, customerAccountProfile);
// [17-05-11] Changes starts here for price lock enhancement
BuildPriceLockDetails(siteId, accountNumber9, dalCustomerAccount, customerAccountProfile);
// [17-05-11] Changes ends here for price lock enhancement
}
if (customerServicesDataTable != null && customerServicesDataTable.Rows.Count > 0)
{
List <AvailableService> services = new List <AvailableService>();
foreach (CustomerAccountProfileSchema.CustomerServicesRow row in customerServicesDataTable.Rows)
{
services.Add(new AvailableService(DalServiceCategory.Instance.GetServiceCategoryDesc(row.Service_Category_Code), (ServiceStatus)TypeDescriptor.GetConverter(typeof(ServiceStatus)).ConvertFrom(row.ServiceStatus)));
}
customerAccountProfile.Services = services;
}
//[28-01-2009] End Changes to reflect services for an account
// Changes for Self Reg Ernest Griffin **START**//
//Account account = new Account();
//AccountActivity accountActivity = new AccountActivity(_userName);
//account = accountActivity.InquireAccount(accountNumber9, siteId);
//if (account.AllowOnlineOrdering)
//{
// customerAccountProfile.OnlineOrderDelinquentBalance = false;
//}
//else
//{
// customerAccountProfile.OnlineOrderDelinquentBalance = true;
//}
//if (account.OnlineOrderingOptOut != 0)
//{
// customerAccountProfile.OnlineOrderBlock = true;
//}
//else
//{
// customerAccountProfile.OnlineOrderBlock = false;
//}
return(customerAccountProfile);
}
/// <summary>
/// Given a siteId (up to 3 digits) and the accountNumber (up to 9 digits), this method
/// contructs and fills a strongly typed CustomerAccountProfileDataTable. Using this DataTable
/// does not require any knowledge of the underlying datasource. Nor does it require knowledge
/// of the underlying data access libraries (e.g. SqlClient, OleDb, OracleClient, etc. ).
/// </summary>
/// <param name="siteId">Site Id for the customer</param>
/// <param name="accountNumber9">9 digit customer account</param>
/// <returns><CustomerAccountProfileSchema.CustomerAccountDataTable></returns>
/// <exception cref="LogonException">Thrown when a problem occurred
/// trying to connect to the underlying datasource.</exception>
/// <exception cref="DataSourceException">Thrown when a problem occurred
/// trying to interact with the underlying datasource after a connection
/// has been established. Check the inner exception for further meaning
/// as to why the problem occurred.</exception>
public CustomerAccountProfileSchema GetAccountProfile(int siteId, string accountNumber9)
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try { oracleConn.Open(); }
catch (Exception ex)
{
throw new LogonException(ex);
}
//[30-10-2009] Start Changes for including Customer History Data
string sql = "GETACCOUNTPROFILEDETAILS";
//[30-10-2009] End Changes for including Customer History Data
// build the command object
using (OracleCommand cmd = new OracleCommand(sql, oracleConn))
{
cmd.CommandType = CommandType.StoredProcedure;
//add the params
cmd.Parameters.Add("nbrSIDIn", OracleType.Number).Value = siteId;
cmd.Parameters.Add("nbrAcctNbrIn", OracleType.Number).Value = Convert.ToInt32(accountNumber9);
OracleParameter errorCodeOut = cmd.Parameters.Add("nbrErrorOut", OracleType.Number);
errorCodeOut.Direction = ParameterDirection.Output;
OracleParameter errorTextOut = cmd.Parameters.Add("vchErrorOut", OracleType.VarChar, 2000);
errorTextOut.Direction = ParameterDirection.Output;
cmd.Parameters.Add("rfcAccoutProfileOut", OracleType.Cursor);
cmd.Parameters["rfcAccoutProfileOut"].Direction = ParameterDirection.Output;
//[28-01-2009] Start Changes to reflect active services for an account
cmd.Parameters.Add("rfcAccoutServices", OracleType.Cursor);
cmd.Parameters["rfcAccoutServices"].Direction = ParameterDirection.Output;
// build the dataadapter
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
// create the dataset to fill
CustomerAccountProfileSchema ds = new CustomerAccountProfileSchema();
// now fill it
try
{
da.TableMappings.Add("Table", "CustomerAccount");
da.TableMappings.Add("Table1", "CustomerServices");
da.Fill(ds);
}
catch (Exception ex)
{
throw new DataSourceException(ex);
}
if (int.Parse(cmd.Parameters["nbrErrorOut"].Value.ToString()) != 0)
{
throw new DataSourceException(cmd.Parameters["vchErrorOut"].Value.ToString());
}
// all done, return
return(ds);
//[28-01-2009] End Changes to reflect active services for an account
}
}
}
}
/// <summary>
/// Gets the account number and site Id matches for the input phoneNumber and street.
/// </summary>
/// <param name="phoneNumber10">Phone number passed in</param>
/// <param name="streetNumber">Street Number</param>
/// <returns></returns>
public CustomerAccountProfileSchema.AccountMatchesDataTable GetCustomerAccountMatches(string phoneNumber10, string streetNumber)
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try { oracleConn.Open(); }
catch (Exception ex)
{
throw new LogonException(ex);
}
// build the command object
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = oracleConn;
cmd.CommandType = CommandType.Text;
//Build query
StringBuilder sql = new StringBuilder();
sql.Append("select ");
sql.Append("Distinct SITE_ID , LPAD(ACCOUNT_NBR,9,'0') as Account_Number, '0' as customer_tn_flag from CDM.PHONE_MASTER_WS ");
sql.Append(" where ");
sql.Append(" ( ( account_status = 'A' ");
sql.Append(" and cust_telephone_status IN ( 'AC', 'NA' ) ) or ");
sql.Append(" ( cust_telephone_status IN ('Active','Suspended','Inactive') ) ) ");
sql.Append(" and phone_nbr = :nbrPhoneNumber ");
//Add street number condition in where clause only if street number is neither empty nor null.
if (!String.IsNullOrEmpty(streetNumber.Trim()))
{
sql.Append(" and trim(street_nbr) = :vchStreetNumber ");
//add parameter for street number.
cmd.Parameters.Add("vchStreetNumber", OracleType.VarChar, 50).Value = streetNumber.Trim();
}
cmd.CommandText = sql.ToString();
//add parameter for phone number
cmd.Parameters.Add("nbrPhoneNumber", OracleType.Number, 10).Value = Convert.ToUInt64(phoneNumber10);
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
// create the dataset to fill
CustomerAccountProfileSchema ds = new CustomerAccountProfileSchema();
// now fill it
try
{
da.Fill(ds.AccountMatches);
}
catch (Exception ex)
{
throw new DataSourceException(ex);
}
finally
{
oracleConn.Close();
}
// all done, return
return(ds.AccountMatches);
}
}
}
}
/// <summary>
/// Gets the account numbers and site Id matches for the input phoneNumber and street.
/// </summary>
/// <param name="phoneNumber10">Phone number passed in</param>
/// <param name="getNeverAndFormerAsWell">Flag which indicates whether to search former and never accounts or not.</param>
/// <returns></returns>
public CustomerAccountProfileSchema.AccountMatchesDataTable GetCustomerAccountMatches(string phoneNumber10, bool getNeverAndFormerAsWell)
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try { oracleConn.Open(); }
catch (Exception ex)
{
throw new LogonException(ex);
}
// build the command object
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = oracleConn;
cmd.CommandType = CommandType.Text;
//Build query
StringBuilder sql = new StringBuilder();
sql.Append("select ");
sql.Append(" Distinct SITE_ID , LPAD(ACCOUNT_NBR,9,'0') as Account_Number, DECODE(phone_type,'Cox Service Primary',1,'Cox Service Secondary Ring',1,0) customer_tn_flag from CDM.PHONE_MASTER_WS ");
sql.Append(" where ( ( ");
//Check if we need to get former and never accounts also
if (!getNeverAndFormerAsWell)
{
//If we donot need to search for former and never accounts,
//add active account status filter.
sql.Append(" account_status = 'A' and ");
}
sql.Append(" cust_telephone_status IN ( 'AC', 'NA') ) or ");
sql.Append(" ( cust_telephone_status IN ('Active','Suspended','Inactive') ) ) ");
sql.Append(" and phone_nbr = :nbrPhoneNumber ");
cmd.CommandText = sql.ToString();
//add parameter for phone number
cmd.Parameters.Add("nbrPhoneNumber", OracleType.Number, 10).Value = Convert.ToUInt64(phoneNumber10);
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
// create the dataset to fill
CustomerAccountProfileSchema ds = new CustomerAccountProfileSchema();
// now fill it
try
{
da.Fill(ds.AccountMatches);
}
catch (Exception ex)
{
throw new DataSourceException(ex);
}
finally
{
oracleConn.Close();
}
// all done, return
return(ds.AccountMatches);
}
}
}
}
