Customer account statement Data provider class

 [SRSReportQueryAttribute(queryStr(CustomerAccountStatementQry)), 

SRSReportParameterAttribute(classStr(CustAcctStatementContract))] 

class CustAcctStatementDP extends SRSReportDataProviderBase 

{ 

    CustAcctStatementTmp             CustAcctStatementTmp; 

    CustAcctStatementContractT       contract; 

    FromDate                            fromDate; 

    ToDate                              toDate; 

    CustTable                           custTable; 

    CustTrans                           custTrans; 

    CustInvoiceTrans             custInovceTrans; 

    CustTransOpen                custTransOpen; 

    CustInvoiceAccount          documentNo; 

    T_FinancialDimensionCreditLimit     t_FinancialDimensionCreditLimit; 

    DimAttributeOMBusinessUnit       dimAttributeOMBusinessUnit; 

    ListIterator BusinessUnititerator,Postingprofileiterator; 

    str BUcreditlimt; 

    real     closeamt; 

         

    //NoYes                               onlyOpen; 

    NoYes                               includeReversed; 

    TransDate assessmentDate; 

  //  Integer numberOfTransOpen; 

    CustInvoiceJour         custInvoiceJour; 

    DimensionAttributeValueSetItemView  dimensionAttributeValueSetItemView; 

    DimensionAttributeValue             dimensionAttributeValue; 

    DimensionAttribute                  dimensionAttribute; 

    DimensionAttributeValueSet          dimensionAttributeValueSet; 

    DimensionAttributeValueSetItem      dimensionAttributeValueSetItem; 

    TransactionReversalTrans transactionReversalTrans; 

 

    TmpCustVendTransOpen tmpCustVendTransOpen; 

 

 

       

 

    [SRSReportDataSetAttribute(tableStr( CustAcctStatementTmp))] 

    public  CustAcctStatementTmp getCustAccountStatementTmp() 

    { 

        select  CustAcctStatementTmp; 

        return  CustAcctStatementTmp; 

    } 

 

    private TransDate assessmentDate() 

    { 

        return assessmentDate ? assessmentDate : toDate; 

    } 

 

    public Name BucreditLimitget(CustAccount _AccountNum) 

    { 

        List  Businessunit; 

        Businessunit = contract.parmBusineesUnit(); 

 

        if(Businessunit) 

        { 

                  

            BusinessUnititerator        = new ListIterator(Businessunit); 

            while(BusinessUnititerator.more()) 

            { 

                select firstonly   _FinancialDimensionCreditLimit  join dimAttributeOMBusinessUnit where 

                         _FinancialDimensionCreditLimit.SourceCriteria == dimAttributeOMBusinessUnit.Value &&  _FinancialDimensionCreditLimit.AccountNum==_AccountNum 

                        &&  _FinancialDimensionCreditLimit.SourceCriteria== BusinessUnititerator.value(); 

                  

                BUcreditlimt+=dimAttributeOMBusinessUnit.Name+'-'+ num2Str(( _FinancialDimensionCreditLimit.CreditLimit),0,0,0,0)+','; 

                BusinessUnititerator.next(); 

            } 

        

      } 

        return BUcreditlimt; 

    } 

 

    public void processreport() 

    { 

        contract = this.parmDataContract(); 

        DimensionAttribute  attribute = DimensionAttribute::findByName("BusinessUnit"); 

        Query query; 

        QueryRun qr; 

        QueryBuildDataSource qbds,qbds1,custTransDS,custInoviceTransDs,custInvoiceJourDS,DimensionAttributeValueSetDS,DimensionAttributeValueSetItemDS,DimensionAttributeValueDS,DimensionAttributeDS,custTableDs, custTransOpenDS; 

       // transactionReversalTransDataSource,ds, 

        CustAccount custValue; 

        QueryBuildLink  qbl; 

        List  Businessunit,Postingprofile; 

        custValue =contract.parmCustAccount(); 

        query  = new Query(); 

        

        quer 

 

         

        DimensionAttributeValueSetDS = qbds.addDataSource(tableNum(DimensionAttributeValueSet)); 

        DimensionAttributeValueSetDS.relations(true); 

        DimensionAttributeValueSetItemDS = DimensionAttributeValueSetDS.addDataSource(tablenum(DimensionAttributeValueSetItem)); 

        DimensionAttributeValueSetItemDS.relations(true); 

        DimensionAttributeValueDS = DimensionAttributeValueSetItemDS.addDataSource(tableNum(DimensionAttributeValue)); 

        DimensionAttributeValueDS.relations(true); 

        DimensionAttributeDS = DimensionAttributeValueDS.addDataSource(tableNum(DimensionAttribute)); 

        DimensionAttributeDS.relations(true); 

             

         

        

 

         

        //Now added 

        //if (!includeReversed) 

        //{ 

        //    ds = custTransDS.addDataSource(tableNum(TransactionReversalTrans)); 

        //    ds.joinMode(JoinMode::NoExistsJoin); 

        //    ds.addLink(fieldNum(CustTrans, TableId), fieldNum(TransactionReversalTrans, RefTableId)); 

        //    ds.addLink(fieldNum(CustTrans, RecId), fieldNum(TransactionReversalTrans, RefRecId)); 

        //    ds.addRange(fieldNum(TransactionReversalTrans, Reversed)).value(enum2str(NoYes::Yes)); 

        //} 

 

        if(custValue) 

        { 

            qbds.addRange(fieldNum(CustTable,AccountNum)).value(queryValue(custValue)); 

        } 

        else if (contract.parmCustGroup()) 

        { 

            qbds.addRange(fieldNum(CustTable,CustGroup)).value(queryValue(contract.parmCustGroup())); 

        } 

         

 

        

        if (contract.parmBusineesUnit()) 

        { 

            QueryBuildRange qbr,qbr1 ; 

            Businessunit = contract.parmBusineesUnit(); 

 

            if(Businessunit) 

            { 

                  

            BusinessUnititerator        = new ListIterator(Businessunit); 

            while(BusinessUnititerator.more()) 

            { 

                DimensionAttributeDS.addRange(fieldnum(DimensionAttribute,Name)).value('BusinessUnit'); 

                DimensionAttributeValueDS.addRange(fieldNum(DimensionAttributeValue,DisplayValue)).value(BusinessUnititerator.value()); 

                    

                BusinessUnititerator.next(); 

            } 

            } 

             

        } 

        if (contract.parmCus stingprofile()) 

        { 

            Postingprofile =contract.parmCus stingprofile(); 

            if(Postingprofile) 

            { 

                Postingprofileiterator = new ListIterator(Postingprofile); 

                while(Postingprofileiterator.more()) 

                { 

                    custTransDS.addRange(fieldNum(CustTrans,PostingProfile)).value(Postingprofileiterator.value()); 

                    Postingprofileiterator.next(); 

                } 

            } 

        } 

        custTransDS.addRange(fieldNum(CustTrans,TransDate)).value(sysQuery::range(contract.parmFromDate(),contract.parmToDate())); 

 

        // ///for open transactions 

        //if (contract.parmOnlyOpen()) 

        //{ 

        //    custTransOpenDS = custTransDS.addDataSource(tableNum(CustTransOpen)); 

        //    custTransOpenDS.relations(true); 

        //} 

         

        qr = new QueryRun(query); 

        while(qr.next()) 

        { 

            custTable = qr.get(tableNum(CustTable)); 

            custInovceTrans =qr.get(tableNum(CustInvoiceTrans)); 

            custTrans = qr.get(tableNum(CustTrans)); 

            dimensionAttributeValue = qr.get(tableNum(DimensionAttributeValue)); 

            custInvoiceJour = qr.get(tableNum(CustInvoiceJour)); 

            //custTransOpen = qr.get(tableNum(CustTransOpen)); 

            this.insert CustAcctStatementTmp(); 

        } 

         

// add now to get open bal 

 

    } 

 

    public void insert CustAcctStatementTmp() 

    { 

        contract =this.parmDataContract(); 

     

        Ledger                      ledger; 

        Currency                    currency; 

        LedgerJournalTrans          ledgerJournalTrans; 

        CustLedger                  custLedger; 

     //   CustInvoiceJour           custinvoiceJourloc; 

        DIRPARTYPOSTALADDRESSVIEW   dIRPARTYPOSTALADDRESSVIEW; 

         CustAcctStatementTmp      CustAcctStmntloc; 

        CustTrans                   custTransLoc; 

       

      

        real                        balance ; 

 

        DimensionAttribute  attribute = DimensionAttribute::findByName("BusinessUnit"); 

 

        select firstonly  CustAcctStmntloc where  CustAcctStmntloc.DocumentNo == custInvoiceJour.InvoiceId; 

        ///added now 

        /// 

           

         CustAcctStatementTmp.clear(); 

         CustAcctStatementTmp.TransDate =custInovceTrans.InvoiceDate; 

         CustAcctStatementTmp.DocumentNo =custInovceTrans.InvoiceId; 

         

 

        

         CustAcctStatementTmp.CustomerNo = custTable.AccountNum; 

         CustAcctStatementTmp.CustomerName = custTable.name(); 

         CustAcctStatementTmp.CreditLimit =custTable.CreditMax; 

 

         CustAcctStatementTmp.PaymentTerms=custTable.PaymTermId; 

         

            

           

         

         CustAcctStatementTmp.BUCreditLimt_ = this.BucreditLimitget(custTable.AccountNum); 

         

         

        /// Get the date from customer's first transaction 

        select firstonly TransDate from custTransLoc order by TransDate asc   

            where custTransLoc.AccountNum == custTable.AccountNum ; 

        //TransDate periodStartDate = custTransLoc.TransDate; 

 

        if (contract.parmFromDate()) 

        { 

            select sum(AmountMST) from custTransOpen where (custTransOpen.TransDate > custTransLoc.TransDate && custTransOpen.TransDate < contract.parmFromDate()-1); 

             CustAcctStatementTmp.OpendingBalance = custTransOpen.AmountMST; 

        } 

        else 

        { 

            select sum(AmountMST) from custTransOpen where ((custTransOpen.TransDate > custTransLoc.TransDate && custTransOpen.TransDate < systemDateGet()-1)); 

             CustAcctStatementTmp.OpendingBalance = custTransOpen.AmountMST; 

        } 

         

        if (contract.parmDetails()) 

        { 

             CustAcctStatementTmp.ItemName = custInovceTrans.itemName(); 

             CustAcctStatementTmp.UOM = custInovceTrans.SalesUnit; 

             CustAcctStatementTmp.Qty = custInovceTrans.Qty; 

             CustAcctStatementTmp.Amount = custInovceTrans.LineAmount; 

             CustAcctStatementTmp.UnitPrice = custInovceTrans.SalesPrice; 

        } 

        if (contract.parmDescription()) 

        { 

             CustAcctStatementTmp.Description =custInovceTrans.Name; 

        } 

         CustAcctStatementTmp.InvoiceAmount =custInvoiceJour.InvoiceAmount; 

 

        // CustAcctStatementTmp.TransOpen = numberOfTransOpen; 

 

        //if (numberOfTransOpen <= 1) 

        //{ 

        //     CustAcctStatementTmp.DueDate = custTrans.DueDate; 

 

        //    if (tmpCustVendTransOpen.RecId) 

        //    { 

        //         CustAcctStatementTmp.TmpDueDate = tmpCustVendTransOpen.DueDate; 

        //    } 

        //    else 

        //    { 

        //         CustAcctStatementTmp.TmpDueDate = custTrans.DueDate; 

        //    } 

        //} 

        //else 

        //{ 

        //     CustAcctStatementTmp.DueDate = dateNull(); 

        //     CustAcctStatementTmp.TmpDueDate = dateNull(); 

        //} 

        // CustAcctStatementTmp.TraceNum = transactionReversalTrans.TraceNum; 

        // CustAcctStatementTmp.Reversed = transactionReversalTrans.Reversed ? 'R' : ''; 

 

        //    CustAcctStatementTmp.OpendingBalance = 

        

        if (custInvoiceJour.InvoiceId != documentNo) 

        { 

             CustAcctStatementTmp.DueDate = custTrans.DueDate; 

             CustAcctStatementTmp.Currency = custTable.Currency; 

             

             

            if(contract.parmTransCurrency()) 

            { 

                if(custTrans.AmountCur>0) 

                { 

                     CustAcctStatementTmp.debit = custTrans.AmountCur; 

                } 

                if(custTrans.AmountCur<0) 

                { 

                     CustAcctStatementTmp.credit = custTrans.AmountCur; 

                } 

                if(contract.parmPrintNonZero()) 

                { 

                    balance =abs( CustAcctStatementTmp.OpendingBalance +abs( CustAcctStatementTmp.Debit- CustAcctStatementTmp.Credit)); 

                    if(balance>0) 

                    { 

                         CustAcctStatementTmp.Balance =balance; 

                    } 

                } 

                else 

                { 

                     CustAcctStatementTmp.Balance =abs( CustAcctStatementTmp.OpendingBalance +abs( CustAcctStatementTmp.Debit- CustAcctStatementTmp.Credit)); 

                } 

                    //this.openingBalance()+( CustAcctStatementTmp.Credit- CustAcctStatementTmp.Debit); 

            } 

             

            if(contract.parmAccountingCurrency()) 

            { 

                if(this.AccountCurr()>0) 

                { 

                     CustAcctStatementTmp.ACdebit =this.AccountCurr(); 

                } 

                if(this.AccountCurr()<0) 

                { 

                     CustAcctStatementTmp.ACcredit =this.AccountCurr(); 

                } 

                 CustAcctStatementTmp.ACBalance =abs( CustAcctStatementTmp.OpendingBalance +abs( CustAcctStatementTmp.ACdebit- CustAcctStatementTmp.ACcredit)); 

 

            } 

        } 

     

             

          //   CustAcctStatementTmp.ClosingBalance = custTrans.AmountMST; 

         

            select LOCATIONNAME,address from dIRPARTYPOSTALADDRESSVIEW where dIRPARTYPOSTALADDRESSVIEW.RECID == 22565423383; 

             CustAcctStatementTmp.CompanyName =dIRPARTYPOSTALADDRESSVIEW.LocationName; 

             CustAcctStatementTmp.CompanyAddress =dIRPARTYPOSTALADDRESSVIEW.Address; 

 

             

         CustAcctStatementTmp.ClosingBalance += CustAcctStatementTmp.Balance ; 

             CustAcctStatementTmp.PostingProfile = custTrans.PostingProfile; 

 

            //DimensionAttributeValueSetItemView dimensionAttributeValueSetItemView; 

            select DisplayValue from dimensionAttributeValueSetItemView 

            where dimensionAttributeValueSetItemView.DimensionAttributeValueSet == custTable.DefaultDimension 

                && dimensionAttributeValueSetItemView.DimensionAttribute    == attribute.RecId; 

             CustAcctStatementTmp.BusinessUnit  = dimensionAttributeValueSetItemView.DisplayValue ; 

             

             

         

 

            fromDate = contract.parmFromDate(); 

            toDate = contract.parmToDate(); 

             CustAcctStatementTmp.FromDate =  fromDate ; 

             CustAcctStatementTmp.ToDate = toDate; 

             CustAcctStatementTmp.BalanceAsOn = toDate; 

             CustAcctStatementTmp.BalanceOutstanding = toDate; 

           //  CustAcctStatementTmp.OpendingBalance = this.openingBalance(); 

        documentNo = custInovceTrans.InvoiceId; 

             CustAcctStatementTmp.insert(); 

 

        

        

 

    } 

 

    //public Amount openingBalance() 

    //{ 

    //    if (fromDate) 

    //    { 

    //        if (contract.parmOnlyOpen()) 

    //        { 

    //            return custTable.openBalanceMST(dateNull(), fromDate -1, assessmentDate); 

    //        } 

    //        else 

    //        { 

    //            return custTable.balanceMST(dateNull(), fromDate -1); 

    //        } 

    //    } 

    //    else 

    //    { 

    //        return 0; 

    //    } 

    //} 

 

//} 

 

    private Amount AccountCurr() 

    { 

        Ledger ledger; 

        Currency currency; 

        LedgerJournalTrans      ledgerJournalTrans; 

        CurrencyExchangeHelper currencyExchangeHelper; 

        CustLedger               custLedger; 

        select AccountingCurrency,ReportingCurrency from ledger 

       join currency 

               where ledger.AccountingCurrency==currency.CurrencyCode 

                && currency.CurrencyCode==custTable.Currency; 

 

 

        CurrencyCode transCurrency = ledger.ReportingCurrency; 

       AmountMst amountMST; 

        currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(), systemDateGet()); 

        amountMST = currencyExchangeHelper.calculateTransactionToAccounting(transCurrency, custTrans.AmountCur ,true); 

        return  amountMST; 

    } 

 

}