Datele din executia unui query afisate intr-o forma


Cum sa rulezi un query care sa aduca datele intr-o forma pentru export in excel:

1.Clasa in care se defineste interfata pentru query.

1 Defineste clasa si variabilele
2 Dialog

2Tabela temporara care contine structura ce tb exportata

Metoda care primeste parametrii din forma si care insereaza recorduri in tabela temporara

static BITTmpInvoiceAgingExchRate createtmpCustInvoiceAging(Query _newQuery, TransDate _transdateRunReport, TransDate _exchDate,

ForwardBackward _direction = ForwardBackward::Forward)

{

QueryRun qr;

CustTable custTableLocal;

CustTrans custTranslocal;

int dueDays, days;

AmountCur paymentAmount;

BITTmpInvoiceAgingExchRate tmpCustInvoiceAging;

CustInvoiceJour custInvoiceJour;

ExchRate localExchRate;

ExchRateHelper exchRateHelper;

CustTransDetails custTransDetails;

AmountCur amountCurSettled;

AmountMST amountMSTSettled;

AmountCur amountCurOpen;

AmountMST amountMSTOpen;

TransDate lDocumentdate;

;

qr = new QueryRun(_newQuery);

ttsbegin;

while(qr.next())

{

if(qr.changed(tablenum(CustTable)))

{

custTableLocal = qr.get(tablenum(CustTable));

}

if(qr.changed(tablenum(CustTrans)))

{

custTranslocal = qr.get(tablenum(CustTrans));

if(custTransLocal.DocumentDate != datenull())

{

lDocumentdate = custTransLocal.DocumentDate;

}

else

{

lDocumentdate = custTransLocal.TransDate;

}

// if(custTranslocal.TransDate <= _transdateRunReport && (custTranslocal.isInvoice() || custTranslocal.isCreditNote()))

if(lDocumentdate <= _transdateRunReport)

{

dueDays = _transdateRunReport – custTranslocal.DueDate;

// paymentAmount = custTransOpen::findRefId(custTranslocal.RecId).AmountCur;

tmpCustInvoiceAging.clear();

tmpCustInvoiceAging.LedgerTransType = LedgerTransType::Cust;

tmpCustInvoiceAging.AccountNum = custTableLocal.AccountNum;

tmpCustInvoiceAging.Name = custTableLocal.Name;

tmpCustInvoiceAging.DueDate = custTransLocal.DueDate;

tmpCustInvoiceAging.Dimensions = custTransLocal.Dimension;

tmpCustInvoiceAging.InvoiceId = custTransLocal.Invoice;

tmpCustInvoiceAging.Planner = custTableLocal.MainContactId;

if(custTransLocal.DocumentDate != datenull())

{

tmpCustInvoiceAging.DocumentDate = custTransLocal.DocumentDate;

}

else

{

tmpCustInvoiceAging.DocumentDate = custTransLocal.TransDate;

}

tmpCustInvoiceAging.InvoiceDate = custTransLocal.TransDate;

tmpCustInvoiceAging.CurrencyCode = custTransLocal.CurrencyCode;

//valoarea facturii in valuta

tmpCustInvoiceAging.InvoiceAmountCur = custTransLocal.AmountCur;

//valoarea facturii in RON la cursul istoric

tmpCustInvoiceAging.InvoiceAmountMST = custTransLocal.AmountMST;

//curs istoric

tmpCustInvoiceAging.ExchRate = custTransLocal.ExchRate/100;

// curs la data ceruta 3prin raport

exchRateHelper = ExchRateHelper::newExchDate(curext(), custTransLocal.CurrencyCode, _exchDate);

localExchRate = exchRateHelper.getExchRate()/100;

tmpCustInvoiceAging.ExchRate2 = localExchRate;

//valoarea facturii in RON la cursul de la data din parametru

tmpCustInvoiceAging.InvoiceAmountMST2 = custTransLocal.AmountCur * localExchRate;

//Sold factura in valuta la data rularii raportului

custTransDetails = new CustTransDetails(custTransLocal, _transdateRunReport);

custTransDetails.setCustVendTrans(custTransLocal);

custTransDetails.setTransDate(_transdateRunReport);

[amountCurSettled,amountMSTSettled] = custTransDetails.amountCurMSTSettled();

amountCurOpen = custTransLocal.AmountCur – amountCurSettled;

amountMSTOpen = custTransLocal.AmountMST – amountMSTSettled;

tmpCustInvoiceAging.InvoiceBalanceCur = amountCurOpen;

//Sold factura in RON la cursul de la data din paramertru

tmpCustInvoiceAging.InvoiceBalanceMST = amountCurOpen * localExchRate;

if( _transdateRunReport > CustTranslocal.DueDate)

{

tmpCustInvoiceAging.BalanceNotDue = 0;

tmpCustInvoiceAging.BalanceDue = amountCurOpen;

tmpCustInvoiceAging.DueDays = dueDays;

}

else

{

tmpCustInvoiceAging.BalanceNotDue = amountCurOpen;

tmpCustInvoiceAging.BalanceDue = 0;

tmpCustInvoiceAging.DueDays = dueDays;

}

switch(_direction)

{

case ForwardBackward::Backward : days = duedays; break;

//case ForwardBackward::Forward : days = -1 * duedays; break;

case ForwardBackward::Forward : days = duedays; break;

}

if( days<=30 && days >0)

{

tmpCustInvoiceAging.BalanceDue30 = amountCurOpen;

tmpCustInvoiceAging.BalanceDue60 = 0;

tmpCustInvoiceAging.BalanceDue90 = 0;

tmpCustInvoiceAging.BalanceDue180 = 0;

tmpCustInvoiceAging.BalanceDue270 = 0;

tmpCustInvoiceAging.BalanceDue271 = 0;

}

else

{

if(days > 30 && days <= 60)

{

tmpCustInvoiceAging.BalanceDue30 = 0;

tmpCustInvoiceAging.BalanceDue60 = amountCurOpen;

tmpCustInvoiceAging.BalanceDue90 = 0;

tmpCustInvoiceAging.BalanceDue180 = 0;

tmpCustInvoiceAging.BalanceDue270 = 0;

tmpCustInvoiceAging.BalanceDue271 = 0;

}

else

{

if(days > 60 && days <= 90)

{

tmpCustInvoiceAging.BalanceDue30 = 0;

tmpCustInvoiceAging.BalanceDue60 = 0;

tmpCustInvoiceAging.BalanceDue90 = amountCurOpen;

tmpCustInvoiceAging.BalanceDue180 = 0;

tmpCustInvoiceAging.BalanceDue270 = 0;

tmpCustInvoiceAging.BalanceDue271 = 0;

}

else

{

if(days >90 && days <= 180)

{

tmpCustInvoiceAging.BalanceDue30 = 0;

tmpCustInvoiceAging.BalanceDue60 = 0;

tmpCustInvoiceAging.BalanceDue90 = 0;

tmpCustInvoiceAging.BalanceDue180 = amountCurOpen;

tmpCustInvoiceAging.BalanceDue270 = 0;

tmpCustInvoiceAging.BalanceDue271 = 0;

}

else

{

if(days > 180 && days <= 270)

{

tmpCustInvoiceAging.BalanceDue30 = 0;

tmpCustInvoiceAging.BalanceDue60 = 0;

tmpCustInvoiceAging.BalanceDue90 = 0;

tmpCustInvoiceAging.BalanceDue180 = 0;

tmpCustInvoiceAging.BalanceDue270 = amountCurOpen;

tmpCustInvoiceAging.BalanceDue271 = 0;

}

else

{

if(days > 270)

{

tmpCustInvoiceAging.BalanceDue30 = 0;

tmpCustInvoiceAging.BalanceDue60 = 0;

tmpCustInvoiceAging.BalanceDue90 = 0;

tmpCustInvoiceAging.BalanceDue180 = 0;

tmpCustInvoiceAging.BalanceDue270 = 0;

tmpCustInvoiceAging.BalanceDue271 = amountCurOpen;

}

}

}

}

}

}

tmpCustInvoiceAging.insert();

}

}

}

ttscommit;

return tmpCustInvoiceAging;

}

3. Cum cele 2 sunt folosite impreuna in forma care prezinta informatiile

variabilele
Ruleaza in init functia care aduce datele
Construieste un query
Aduc datele din temporara

Lasă un răspuns

Completează mai jos detaliile despre tine sau dă clic pe un icon pentru autentificare:

WordPress.com Logo

Comentezi folosind contul tău WordPress.com. Log Out / Schimbă )

Twitter picture

Comentezi folosind contul tău Twitter. Log Out / Schimbă )

Facebook photo

Comentezi folosind contul tău Facebook. Log Out / Schimbă )

Google+ photo

Comentezi folosind contul tău Google+. Log Out / Schimbă )

Connecting to %s

Urmărește

Fiecare nou articol să fie livrat pe email.

%d blogeri au apreciat asta: