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

Completeaza detaliile de mai jos sau apasa click pe una din imagini pentru a te loga:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Schimbă )

Twitter picture

You are commenting using your Twitter account. Log Out / Schimbă )

Facebook photo

You are commenting using your Facebook account. Log Out / Schimbă )

Google+ photo

You are commenting using your Google+ account. Log Out / Schimbă )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: