/// <summary>BuildCustomerAccountContract - Populates customerAccountProfile.ContractDetails</summary>
/// <param name="siteId">Site Id for the customer</param>
/// <param name="accountNumber9">9 digit customer account</param>
/// <param name="dalCustomerAccount">Customer account data access</param>
/// <param name="customerAccountProfile">Customer account profile object</param>
/// <returns><void></returns>
private void BuildCustomerAccountContract(int siteId, string accountNumber9, DalCustomerAccount dalCustomerAccount, CustomerAccountProfile customerAccountProfile)
{
// call dal GetCustomerContract
CustomerAccountProfileSchema.CustomerContractDataTable customerContractDT = dalCustomerAccount.GetCustomerContract(siteId, accountNumber9);
if (customerContractDT != null && customerContractDT.Rows.Count > 0)
{
//Changes for adding contract start date starts here
DateTime contract_End_Date, contract_Start_Date;
customerAccountProfile.ContractDetails = new List <ContractDetail>();
for (int i = 0; i < customerContractDT.Count; ++i)
{
contract_End_Date = DateTime.MinValue;
if (customerContractDT[i].Contract_End_Date != null)
{
contract_End_Date = new Icoms1900Date(customerContractDT[i].Contract_End_Date);
}
contract_Start_Date = DateTime.MinValue;
if (customerContractDT[i].Contract_START_Date != null)
{
contract_Start_Date = new Icoms1900Date(customerContractDT[i].Contract_START_Date);
}
List <string> serviceCategory = new List <string>();
if (customerContractDT[i].Service_Category_Code != null)
{
string[] serviceCategoryCode = customerContractDT[i].Service_Category_Code.Split(',');
foreach (string s in serviceCategoryCode)
{
serviceCategory.Add(DalServiceCategory.Instance.GetServiceCategoryDesc(s));
}
}
customerAccountProfile.ContractDetails.Add(new ContractDetail(customerContractDT[i].Contract_Id.ToString(),
customerContractDT[i].Contract_Desc.ToString(), contract_End_Date, contract_Start_Date,
Convert.ToDouble(customerContractDT[i].Early_Term_Assesment_Amt),
serviceCategory));
//Changes for adding contract start date ends here
}
}
}
/// <summary>
/// Returns an equipment dataset containing the customer's equipment info
/// </summary>
/// <param name="siteId"></param>
/// <param name="accountNumber9"></param>
/// <returns></returns>
public EquipmentSchema GetEquipment(int siteId, string siteCode, string accountNumber9)
{
siteId = 541;
siteCode = "SAN";
accountNumber9 = "65297304";
OracleCommand cmd = null;
OracleCommand cmd2 = null;
OracleDataAdapter da = null;
OracleDataAdapter da2 = null;
try
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try{ oracleConn.Open(); }
catch { throw new LogonException(); }
//get customer equipment
StringBuilder equipmentSql = new StringBuilder();
equipmentSql.Append("select distinct serial_number, equipment_type_code ");
equipmentSql.AppendFormat("from {0}_EQUIPMENT ", siteCode);
//equipmentSql.Append( "where equipment_type_code != 'O' ");
equipmentSql.AppendFormat("where account_number = {0} and site_id = {1}", accountNumber9, siteId);
// build the command object
cmd = new OracleCommand(equipmentSql.ToString(), oracleConn);
cmd.CommandType = CommandType.Text;
// build the dataadapter
da = new OracleDataAdapter(cmd);
// create the dataset to fill
EquipmentSchema ds = new EquipmentSchema();
// fill equipment info
da.Fill(ds.CustomerEquipment);
//convert date to icoms date for query
Icoms1900Date now = new Icoms1900Date(DateTime.Now.Subtract(new TimeSpan(2500, 0, 0, 0)));
//Icoms1900Date now = new Icoms1900Date(1030925);
string time = now.Date.Hour.ToString() + now.Date.Minute.ToString();
//get ppv events for account
StringBuilder ppvSql = new StringBuilder();
ppvSql.Append("select distinct p.serial_number, t.event_title, p.event_start_date, ");
ppvSql.Append("p.event_start_time, d.end_date, d.end_time ");
ppvSql.AppendFormat("from {0}_title t, {0}_ppv_purchase p, {0}_pricing_group_dtl d ", siteCode);
ppvSql.Append("where p.title_code = t.title_code and p.event_start_date = d.start_date ");
ppvSql.Append("and p.event_start_time = d.start_time and p.showing_number = d.showing_number ");
ppvSql.AppendFormat("and p.ACCOUNT_NUMBER = {0} and p.SITE_ID = {1} ", accountNumber9, siteId);
ppvSql.Append("and p.purchase_status = 'P' ");
ppvSql.AppendFormat("and ((d.end_date > {0}) or (d.end_date = {0} and d.end_time >= {1}))", now.ToString(), time);
//Oracle does not support multiple recordsets like SQL Server does
//so we have to use another cmd and da...grr
// build the command object
cmd2 = new OracleCommand(ppvSql.ToString(), oracleConn);
cmd2.CommandType = CommandType.Text;
// build the dataadapter
da2 = new OracleDataAdapter(cmd2);
// now fill ppv dt
da2.Fill(ds.ActivePpvEvents);
// all done, return
return(ds);
}
}
catch (LogonException)
{
// just rethrow it. it is from our internal code block
throw;
}
catch (Exception ex)
{
// DataSourceException.
throw new DataSourceException(ex);
}
finally
{
//clean up
if (da != null)
{
da.Dispose();
}
if (da2 != null)
{
da2.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (cmd2 != null)
{
cmd2.Dispose();
}
}
}
/// <summary>
/// Fills the AccountAddress object.
/// </summary>
/// <param name="baseResponse"></param>
public void Fill(Account account)
{
try
{
// get address.
this.Fill((AccountAddress)account);
DalAccount dalAccount = new DalAccount();
// now get statement informatin.
CustomerAccountSchema.CustomerStatementsDataTable statements =
dalAccount.GetCustomerStatements(_siteId, _siteCode, _accountNumber.AccountNumber9);
// the customer statement data table returns back 1 record for each statementCode
// and serviceCode. However, our return sums up these results. this means we need
// to track when we have a new statement code.
int previousStatementCode = -1;
Statement statement = null;
for (int i = 0; i < statements.Count; i++)
{
CustomerAccountSchema.CustomerStatement stmnt = statements[i];
int statementCode = (int)stmnt.StatementCode;
// these values only change when the statement code changes.
if (previousStatementCode != statementCode)
{
// create a new statement and add it to the account.
double amountBilled = (double)stmnt.AmountBilled;
Icoms1900Date lastStatementDate = new Icoms1900Date((int)stmnt.LastStatementDate);
eStatementStatus statementStatus = (eStatementStatus)TypeDescriptor.GetConverter(
typeof(eStatementStatus)).ConvertFrom(stmnt.Status);
// ok, we care about all statements, except...
if (statementStatus == eStatementStatus.Cancelled ||
statementStatus == eStatementStatus.Disconnect)
{
//we want to show cancelled or disconnected
//statements that have been billed within the
//last 100 days or if the statement still has
//a balance. Otherwise we get the next statement
if ((!lastStatementDate.SpecialDate &&
(DateTime.Now - lastStatementDate).Days > __statementDays) &&
amountBilled <= 0)
{
continue; //get the next statement
}
}
// set this first off.
previousStatementCode = statementCode;
// need this for later.
string paddedStatementCode = statementCode.ToString().PadLeft(3, '0');
// create new statement object and add it to collection.
statement = new Statement();
account.Statements.Add(statement);
statement.AccountNumber16 = paddedStatementCode + _accountNumber.AccountNumber13;
statement.StatementCode = paddedStatementCode;
statement.AmountBilled = amountBilled;
statement.BillingOption = (eBillingOption)TypeDescriptor.GetConverter(
typeof(eBillingOption)).ConvertFrom(stmnt.BillHandlingCode);
// this is the balance outstanding net of any charges not billed.
Icoms1900Date dueDate = new Icoms1900Date((int)stmnt.DueDate);
statement.DueOnReceipt = dueDate.SpecialDate;
statement.DueDate = dueDate.SpecialDate == true?DateTime.MinValue:(DateTime)dueDate;
int mopCode = (int)stmnt.MopCode;
statement.EasyPayFlag = mopCode != 0;
statement.EasyPayMopType = statement.EasyPayFlag ?
(ePaymentType) new MopPaymentType(_userName, mopCode) :
ePaymentType.Unknown;
statement.LastStatementDate = new Icoms1900Date((int)stmnt.LastStatementDate);
// adjustment amounts
UnappliedAmounts unappliedAmounts = dalAccount.GetUnappliedPaymentAmount(
_siteId, _siteCode, _accountNumber.AccountNumber9, statementCode);
statement.UnappliedPaymentAmount = unappliedAmounts.Payments;
statement.UnappliedAdjustmentAmount = unappliedAmounts.NetAdjustments;
statement.UnappliedDebitAdjustmentAmount = unappliedAmounts.DebitAdjustments;
statement.UnappliedCreditAdjustmentAmount = unappliedAmounts.CreditAdjustments;
// pending amount
statement.PendingPaymentAmount = dalAccount.GetPendingPaymentAmount(
_siteId, _siteCode, _accountNumber.AccountNumber9, statementCode);
// now. let's start setting the fields that are a calculation of 1-N
// other fields. however, we only want to set that portion of the value
// that is calculated once per statement (remember GetCustomerStatements()
// may return multiple records for a single statement we return to a customer)
// This means if the calculated value of the field depends upon the following
// items, we want to include it in our calculation here:
// a) A PendingPayment
// b) UnappliedDebitAdjustment
// c) UnappliedCreditAdjustment
// d) UnappliedNetAdjustment(net of debit and credit).
// e) Any field that includes BALANCE_LAST_STATEMENT
// f) Any field that includes LAST_PAYMENT_AMOUNT1,2 or 3
statement.Status = statementStatus;
if (stmnt.LastPaymentDate1 > 0)
{
statement.RecentPayments.Add(
new PaymentItem((double)stmnt.LastPaymentAmount1,
new Icoms1900Date((int)stmnt.LastPaymentDate1)));
}
if (stmnt.LastPaymentDate2 > 0)
{
statement.RecentPayments.Add(
new PaymentItem((double)stmnt.LastPaymentAmount2,
new Icoms1900Date((int)stmnt.LastPaymentDate2)));
}
if (stmnt.LastPaymentDate3 > 0)
{
statement.RecentPayments.Add(
new PaymentItem((double)stmnt.LastPaymentAmount3,
new Icoms1900Date((int)stmnt.LastPaymentDate3)));
}
}
// HERE WE WANT TO SET THOSE VALUES THAT CHANGE ON A PER RECORD
// BASIS. REMEMBER THAT GetCustomerStatements() CAN RETURN MULTIPLE
// RECORDS PER STATEMENT DUE TO AR AGING TRACKING AMOUNTS AT THE
// PRODUCT LEVEL (E.G. CABLE, DATA, CALLING CARD, TELEPHONE).
// add service code to service code collection.
statement.ServiceCategories.Add((eServiceCategory)TypeDescriptor.GetConverter(
typeof(eServiceCategory)).ConvertFrom(stmnt.ServiceCode));
// modify deposit due.
statement.DepositDueAmount += (double)stmnt.DepositDueAmount;
// next writeoffamount.
statement.WriteOffAmount += (double)stmnt.WriteOffAmount;
// current ar balance.
statement.CurrentBalance += (double)stmnt.ArBalanceAmount;
// next adjust the current bucket.
statement.CurrentBucket += (double)stmnt.CurrentArBalanceAmount;
// are 1-30 bucket.
statement.AR1To30Amount += (double)stmnt.Ar1To30Amount;
// are 31-60 bucket.
statement.AR31To60Amount += (double)stmnt.Ar31To60Amount;
// are 61-90 bucket.
statement.AR61To90Amount += (double)stmnt.Ar61To90Amount;
// are 91-120 bucket.
statement.AR91To120Amount += (double)stmnt.Ar91To120Amount;
// are 121-150 bucket.
statement.AR121To150Amount += (double)stmnt.Ar121To150Amount;
// are 150 plus bucket.
statement.AR150PlusAmount += (double)stmnt.Ar150PlusAmount;
}
for (int j = 0; j < account.Statements.Count; j++)
{
Statement baseStatement = account.Statements[j];
double totalPending =
baseStatement.UnappliedPaymentAmount +
baseStatement.PendingPaymentAmount;
// set current balance
baseStatement.CurrentBalance =
Math.Round(baseStatement.CurrentBalance +
baseStatement.DepositDueAmount +
baseStatement.WriteOffAmount +
baseStatement.UnappliedAdjustmentAmount -
totalPending, 2);
// now set the different ar aging buckets. NOTE: I know that I am not
// using all of these fields at this time; however, we are trying to
// mimic the green screens and our needs may change down the road. So,
// I am calculating them now so some other poor soul doesn't have to
// go through it again. Also, I have set XmlIgnoreAttribute() on many
// of these fields to prevent them from being streamed to our SOA clients.
// if you want them to be streamed to the client merely change XmlIgnore()
// to XmlAttribute("NameOfProperty").
if (totalPending > 0)
{
double amt = baseStatement.DepositDueAmount;
bool keepGoing = netOutAmounts(ref amt, ref totalPending);
baseStatement.DepositDueAmount = Math.Round(amt, 2);
if (keepGoing)
{
amt = baseStatement.AR150PlusAmount;
keepGoing = netOutAmounts(ref amt, ref totalPending);
baseStatement.AR150PlusAmount = Math.Round(amt, 2);
}
if (keepGoing)
{
amt = baseStatement.AR121To150Amount;
keepGoing = netOutAmounts(ref amt, ref totalPending);
baseStatement.AR121To150Amount = Math.Round(amt, 2);
}
if (keepGoing)
{
amt = baseStatement.AR91To120Amount;
keepGoing = netOutAmounts(ref amt, ref totalPending);
baseStatement.AR91To120Amount = Math.Round(amt, 2);
}
if (keepGoing)
{
amt = baseStatement.AR61To90Amount;
keepGoing = netOutAmounts(ref amt, ref totalPending);
baseStatement.AR61To90Amount = Math.Round(amt, 2);
}
if (keepGoing)
{
amt = baseStatement.AR31To60Amount;
keepGoing = netOutAmounts(ref amt, ref totalPending);
baseStatement.AR31To60Amount = Math.Round(amt, 2);
}
if (keepGoing)
{
amt = baseStatement.AR1To30Amount;
keepGoing = netOutAmounts(ref amt, ref totalPending);
baseStatement.AR1To30Amount = Math.Round(amt, 2);
}
if (keepGoing)
{
baseStatement.CurrentBucket -= totalPending;
}
}
// now modify current bucket by unapplied debit adjustments.
baseStatement.CurrentBucket = Math.Round(
baseStatement.CurrentBucket +
baseStatement.UnappliedDebitAdjustmentAmount, 2);
// now set minimumDue.
baseStatement.MinimumDue = Math.Round(baseStatement.CurrentBalance -
baseStatement.CurrentBucket - baseStatement.AR1To30Amount, 2);
// make sure it doesn't go below 0
if (baseStatement.MinimumDue < 0)
{
statement.MinimumDue = 0;
}
}
}
catch (BusinessLogicLayerException)
{
// already handled
throw;
}
catch (DataSourceException dse)
{
// not handled. need to handle
throw new DataSourceUnavailableException(dse);
}
catch (Exception ex)
{
// not handled. need to handle
throw new UnexpectedSystemException(ex);
}
}
