Need Help With Query

(0) Share

Report

Posted on by Sachin Mittal

/// 
/// Vendor payment Emails Code
/// 
class OABPostEmail
{
    /// 
    /// VemdEmailID
    /// 
    /// VemdEmailID
    /// Email
    static LogisticsElectronicAddressLocator VendEmailId(VendAccount _vendAccount)
    {
        VendTable vendTable;
        DirPartyTable partyTable;
        LogisticsElectronicAddress logisticsElectronicAddress;
        select Firstonly  AccountNum ,party from vendTable where vendTable.AccountNum==_vendAccount
                    join Name,RecId from partyTable where partyTable.RecId==vendTable.Party
                    join Locator from logisticsElectronicAddress where logisticsElectronicAddress.RecId==partyTable.PrimaryContactEmail;
        return logisticsElectronicAddress.Locator;
    }

    static InvoiceId VendSettleAmountInvoice(RecId _recId)
    {
        VendSettlement vendSettlement;
      //  Str1260 body;
        InvoiceId invoiceId;
        while select  vendSettlement
                         where vendSettlement.TransRecId == _recId
        {
         
        invoiceId = vendSettlement.displayInvoiceId() ;
        }
        return invoiceId;
    }

    static AmountMST SettleAmountMST(RecId _recId)
    {
        VendSettlement vendSettlement;
      //  Str1260 body;
        AmountMST amountMST;
        while select  vendSettlement
                         where vendSettlement.TransRecId == _recId
        {
        
        amountMST = vendSettlement.SettleAmountMST ;
        }

        return amountMST;
    }

    /// 
    /// VendSettleAmountMST
    /// 
    /// VendSettleAmountMST
    /// Body
    static Str1260 VendSettleAmountMST(RecId _recId)
    {
        VendSettlement vendSettlement;
        Str1260 body;
        while select  vendSettlement
                         where vendSettlement.TransRecId == _recId
        {
            body  = strFmt("@OAB:OABVendEmailInvoice",vendSettlement.displayInvoiceId(), vendSettlement.SettleAmountMST);
        }
        return body;
    }

    /// 
    /// Vendor payment Emails Code
    /// 
    /// Current Date
    static   void sendMail(TransDate _FromDate)
    {
        Str1260				 body,Tbody;
        SysEmailParameters	 EmailParameters = SysEmailParameters::find();
        DirPartyTable partyTable;
        VendTable vendTable;
        VendTrans vendTrans;
        VendAccount localvendAccount;
        FromDate fromdate = _FromDate;
        LogisticsElectronicAddress logisticsElectronicAddress;
        SysMailerMessageBuilder mailer;
        VendTrans   vendTransGroup;
        VendSettlement  vendSettlementGroup;
        boolean isinLoop;

        while select AccountNum,RecId from vendTransGroup group by AccountNum,RecId
            join RecId from vendSettlementGroup
            where vendSettlementGroup.TransRecId==vendTransGroup.RecId &&
            vendTransGroup.TransDate==fromdate
        {
            isinLoop = true;
            if (localvendAccount != vendTransGroup.AccountNum)
            {
                if(localvendAccount!=null)
                {
                   
                    mailer.setBody(tbody,true) ;
                    SysMailerFactory::getNonInteractiveMailer().sendNonInteractive(mailer.getMessage()) ;
                    body ='';
                }
                str emails = OABPostEmail::VendEmailId(vendTransGroup.AccountNum);
                mailer = new SysMailerMessageBuilder();
                mailer.setFrom(EmailParameters.SMTPUserName);
                mailer.addTo(emails);
                mailer.setSubject(strFmt("@OAB:OABVendEmailSubject",OABPostEmail::VendAccountNum(vendTransGroup.AccountNum), OABPostEmail::VendAccountName(vendTransGroup.AccountNum)));
               
                Tbody = strFmt(" %1:-   %2 
", OABPostEmail::VendAccountNum(vendTransGroup.AccountNum), OABPostEmail::VendAccountName(vendTransGroup.AccountNum));
               
                Tbody  = "Payment Date Payment Amount Settled Against Invoice Number Settlement Amount Against Invoice Value ";
                select firstonly AccountNum,TransDate,AmountMST from vendTrans  where vendTrans.AccountNum==vendTransGroup.AccountNum && vendTrans.RecId==vendTransGroup.RecId;
             
                Tbody  = strFmt(" %1 %2 %3 %4",vendTrans.TransDate,vendTrans.AmountMST,OABPostEmail::VendSettleAmountInvoice(vendTransGroup.RecId),OABPostEmail::SettleAmountMST(vendTransGroup.RecId));
            }
        
            else
            {
                select firstonly AccountNum,TransDate,AmountMST from vendTrans  where vendTrans.AccountNum==vendTransGroup.AccountNum && vendTrans.RecId==vendTransGroup.RecId;
                Tbody  = strFmt("%1 %2 %3 %4",vendTrans.TransDate,vendTrans.AmountMST,OABPostEmail::VendSettleAmountInvoice(vendTransGroup.RecId),OABPostEmail::SettleAmountMST(vendTransGroup.RecId));
               
            }
    
            localvendAccount = vendTransGroup.AccountNum;
        }
        if (isinLoop)
        {
           
            mailer.setBody(Tbody,true) ;
            SysMailerFactory::getNonInteractiveMailer().sendNonInteractive(mailer.getMessage()) ;
        }
    }

}

Here For Same Payment Amount there Are two Invoices and two settlement Amount but with my code here it is showing only one

according to need , second row should also come with same date and payment amount but other invoice and settlement amount. Cant understand how to do that Fyi printing rows Data in Line 113 using tbody .there are two fields from amountmst and transdate from vendtrans and other two fields from vendsettlement table calling in 113 using a static function i.e vendsettleAmountInvoice and vendsettleamount

I have the same question (0)

All responses (7)

Answers (0)

Martin Dráb 238,782 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

It seems that most of code you shared isn't related to the topic of this thread. Could you please trrow everything irrelevant (such as all the logic about emails and unused variables), so we can focus on the actual problem? It will make debugging easier for you too.

Was this reply helpful? Yes No
Martin Dráb 238,782 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
By the way, I see a bug in settleAmountMST():

while select vendSettlement where vendSettlement.TransRecId == _recId { amountMST = vendSettlement.SettleAmountMST; }

I guess you wanted to summarize amount of all settlements with the given TransRecId, but you actually keep overwriting the value in the loop, therefore you'll get the value from the last record only. If you want a sum, do this instead:

static AmountMST settleAmountMST(RecId _transRecId) { VendSettlement vendSettlement; select sum(SettleAmountMST) from vendSettlement where vendSettlement.TransRecId == _transRecId; return vendSettlement.SettleAmountMST; }

Was this reply helpful? Yes No
Sachin Mittal 2 on at

Like (0)

Report
Copy link

Link copied!
static InvoiceId VendSettleAmountInvoice(RecId _recId) { VendSettlement vendSettlement; // Str1260 body; InvoiceId invoiceId; while select vendSettlement where vendSettlement.TransRecId == _recId { invoiceId = vendSettlement.displayInvoiceId() ; } return invoiceId; } static AmountMST SettleAmountMST(RecId _recId) { VendSettlement vendSettlement; // Str1260 body; AmountMST amountMST; while select vendSettlement where vendSettlement.TransRecId == _recId { amountMST = vendSettlement.SettleAmountMST ; } return amountMST; } mailer = new SysMailerMessageBuilder(); mailer.setFrom(EmailParameters.SMTPUserName); mailer.addTo(emails); mailer.setSubject(strFmt("@OAB:OABVendEmailSubject",OABPostEmail::VendAccountNum(vendTransGroup.AccountNum), OABPostEmail::VendAccountName(vendTransGroup.AccountNum))); Tbody = strFmt(" %1:- %2
", OABPostEmail::VendAccountNum(vendTransGroup.AccountNum), OABPostEmail::VendAccountName(vendTransGroup.AccountNum)); Tbody = "
Payment Date Payment Amount Settled Against Invoice Number Settlement Amount Against Invoice Value
"; select firstonly AccountNum,TransDate,AmountMST from vendTrans where vendTrans.AccountNum==vendTransGroup.AccountNum && vendTrans.RecId==vendTransGroup.RecId; Tbody = strFmt("
%1 %2 %3 %4
", vendTrans.TransDate,vendTrans.AmountMST,OABPostEmail::VendSettleAmountInvoice(vendTransGroup.RecId),OABPostEmail::SettleAmountMST(vendTransGroup.RecId)); }

i have only selected the code that needs , actually here for same amountMST and transdate there are two invoice and settlement amount but as i have added a Screenshot it is printing only one id and amount ... A second row should also come with same date and amount but differeent invoice and settle amount

thankyouuu

No the amountmst is already a sum amount

Was this reply helpful? Yes No
Sachin Mittal 2 on at

Like (0)

Report
Copy link

Link copied!

like here for same date and amountMSt there are two invoices and settled amount , but its printing first invoice and settled amount only

Was this reply helpful? Yes No
Martin Dráb 238,782 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You have the same bug in vendSettleAmountInvoice() too. You iterate settlements, but keep overwriting invoiceId. therefore you'll get invoiceId of the last one only.

Was this reply helpful? Yes No
Sachin Mittal 2 on at

Like (0)

Report
Copy link

Link copied!

can you help exactly sir , how it can be resolved

Was this reply helpful? Yes No
Martin Dráb 238,782 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
It depends on what you want to get. If you want to get multiple rows in the table, you don't have any logic for it in sendMail(). You would need a while select there (as you have in vendSettleAmountInvoice()) and put code for adding table rows inside this while loop. You'll throw away settleAmountMST() and vendSettleAmountInvoice(), because you'll get values directly from VendSettlement. For example:

while select vendSettlement where vendSettlement.TransRecId == vendTransGroup.RecId { tBody = OABPostEmail::buildHtmlInvoiceTableRow(vendTrans, vendSettlement); }

Was this reply helpful? Yes No