//[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); } } } }