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 |  |
|
