tabel temporary pada foxpro lawas

Dalam database foxpro klasik sering kali kita memerlukan suatu tabel secara exclusive, untuk melakukan suatu proses transaksi atau pengolahan tabel, berikut salah satu cara membuat tabel exclusive secara temporary yang digunakan untuk merangkun dari tabel lain untuk mensummary kan report yang kita harapkan ;

logika nya adalah dengan membentuk tabel virtual seperti berikut ini :

 

store ‘q’+padl(allt(str(rand()*1000000,7)),7,’0′)+’.dbf’ to qTemp

 

semoga trik berikut membantu dalam pengembangan pada Visual Foxpro, rutin program di bawah masih menggunakan Foxpro DOS, semoga membantu… ada solusi lain?

 

 

 

 

 

* Author : MRafianto

* ————————

deac wind WSALES

 

do usercek with 1, “COE”, “Lap.Rutin Sales Aktual “+zcompid+’ U:’+ZXPASS

if xbenar = .f.

do tsiutil with 0,0,’Akses di tolak / Cencel’,0,0,0,0,’ERR’

return

endif

 

store date() to wsdate, wedate

store ‘V’ to mskup1

store ‘Y’ to mskup2

store ‘4’ to mst

store ‘1’ to mcn

do tsiutil with 0,0,’DATE RANGE’,11,40,18,69,’Misc’

@ 15,60 say ‘[Y/T]’

@ 12,41 say ” Starting Date:” get wsdate

@ 13,41 say ” Ending Date :” get wedate

@ 14,41 say ” Value/Quant :” get mskup1 pict ‘@!’ valid mskup1=’V’ .or. mskup1=’Q’

@ 15,41 say ” 4 Commodity :” get mskup2 pict ‘@!’ valid mskup2=’Y’ .or. mskup2=’T’

@ 16,41 say ” 1GM/2GS/3DS/4NS : “get mst pict ‘@!’ valid mst=’1′ .or. mst=’2′ .or. mst=’3′ .or. mst=’4′

@ 17,41 say ” 1.NS 2.CN Only : “get mcn pict ‘@!’ valid mcn=’1′ .or. mcn=’2′

read

if readkey() = 12 .or. readkey() = 268

do usercek with 2, “Cencel Proses Lap.Rutin Sales Aktual “+zcompid+’ U:’+ZXPASS

return

endif

public mketskup

store space(30) to mketskup

do case

case mst=’1′

mketskup = ‘NILAI GROSS MARGIN SALES’

case mst=’2′

mketskup = ‘NILAI GROSS SALES’

case mst=’3′

mketskup = ‘NILAI DISCOUNT’

case mst=’4′

mketskup = ‘NILAI NETS SALES’

endcase

store 20 to prtlen

store ‘A’ to prtcode

if mskup1=’V’

if mskup2 =’T’

do tsiprt with ‘sls987’,prtlen, prtcode

else

do tsiprt with ‘sls987a’,prtlen, prtcode

endif

else

if mskup2=’T’

do tsiprt with ‘sls9871’,prtlen, prtcode

else

do tsiprt with ‘sls9871a’,prtlen, prtcode

endif

endif

if prtcode <> ‘A’

save screen to s2oerpt

 

*STORE ‘Q’+SUBSTR(SYS(3),2,7) TO QTEMP

*STORE ‘Q’+SUBSTR(SYS(3),2,7) TO QTEMP1

*STORE ‘Q’+SUBSTR(SYS(3),2,7) TO QTEMP2

*STORE ‘Q’+SUBSTR(SYS(3),2,7) TO QTEMP3

*STORE ‘Q’+SUBSTR(SYS(3),2,7) TO QTabl1

*STORE ‘Q’+SUBSTR(SYS(3),2,7) TO QTabl2

*STORE ‘Q’+SUBSTR(SYS(3),2,7) TO QCN1

*STORE ‘Q’+SUBSTR(SYS(3),2,7) TO QCN2

 

store ‘q’+padl(allt(str(rand()*1000000,7)),7,’0’) to qTemp

store ‘q’+padl(allt(str(rand()*1000000,7)),7,’0’) to qTemp1

store ‘q’+padl(allt(str(rand()*1000000,7)),7,’0’) to qTemp2

store ‘q’+padl(allt(str(rand()*1000000,7)),7,’0’) to qTemp3

store ‘q’+padl(allt(str(rand()*1000000,7)),7,’0’) to qTabl1

store ‘q’+padl(allt(str(rand()*1000000,7)),7,’0’) to qTabl2

store ‘q’+padl(allt(str(rand()*1000000,7)),7,’0’) to qCn1

store ‘q’+padl(allt(str(rand()*1000000,7)),7,’0’) to qCn2

 

CREATE TABLE &qCN1 ;

(Salesman C(4),CnKode C(1),Desc C(30),Terr C(6),;

a1 N(9,4),a N(13,2),b1 N(9,4),b N(13,2),;

c1 N(9,4),c N(13,2),d1 N(9,4),d N(13,2),;

e1 N(9,4),e N(13,2),f1 N(9,4),f N(13,2),;

g1 N(9,4),g N(13,2),h1 N(9,4),h N(13,2),;

i1 N(9,4),i N(13,2),j1 N(9,4),j N(13,2),;

k1 N(9,4),k N(13,2),l1 N(9,4),l N(13,2),;

m1 N(9,4),m N(13,2),n1 N(9,4),n N(13,2),;

o1 N(9,4),o N(13,2),p1 N(9,4),p N(13,2),;

x1 N(9,4),x N(13,2))

 

index on salesman+Cnkode to &qCN1

 

 

CREATE TABLE &qTEMP ;

(nom N(3),Salesman C(4),Desc C(30),Terr C(6),;

a1 N(9,4),a N(13,2),b1 N(9,4),b N(13,2),;

c1 N(9,4),c N(13,2),d1 N(9,4),d N(13,2),;

e1 N(9,4),e N(13,2),f1 N(9,4),f N(13,2),;

g1 N(9,4),g N(13,2),h1 N(9,4),h N(13,2),;

i1 N(9,4),i N(13,2),j1 N(9,4),j N(13,2),;

k1 N(9,4),k N(13,2),l1 N(9,4),l N(13,2),;

m1 N(9,4),m N(13,2),n1 N(9,4),n N(13,2),;

o1 N(9,4),o N(13,2),p1 N(9,4),p N(13,2),;

x1 N(9,4),x N(13,2))

 

index on Salesman to &qTemp

 

select a

use shiphist index shiphist,shipord,shipcust,shipdate

select b

use Salesman index salesman

index on Salesman to &qTabl1

select c

use custclas index custclas

select d

use inv index invpn,invacct,invcmdty,invcycle

select e

use &qCN1 index &qCN1

select f

use &qTemp index &qTemp

 

store 0 to _mno

sele salesman

go top

do while !eof()

_ms = salesman->salesman

_ds = salesman->name

sele &qTemp

seek _ms

if !found()

do tsiutil with 0,0,’Setting Up ‘+alltrim(_ms),0,0,0,0,”Msg”

append blank

repl salesman with _ms

repl desc with _ds

endif

sele salesman

skip

enddo

 

select * from shiphist where ;

(shipdate >= wsdate .and. shipdate <= wedate);

into table &qTemp1

 

sele &qTemp1

go top

do while !eof()

_ms = &qTemp1->salesman

_pn = &qTemp1->pn

_mpn = alltrim(&qTemp1->pn)

if mcn=’2′

if d1(mst) > 0

skip

loop

endif

_mC = substr(oeno,5,1)

sele &qCN1

seek _ms+_mC

if !found()

append blank

repl Salesman with _ms

repl CnKode with _mC

else

* Proses

endif

 

sele &qTemp1

endif

 

sele inv

seek _pn

_mprd = alltrim(inv->comdty) && Cari Ke Inv Comodity terbarunya masuk apa?

do tsiutil with 0,0,’Tranfer Up ‘+alltrim(_ms+’ ‘+_pn),0,0,0,0,”Msg”

sele &qTemp

seek _ms

if found()

do case

case (_mprd=’FBRL’ .or. _mprd = ‘FBRL.0’)

repl a1 with a1+&qTemp1->qtyshp

* repl a with a+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl a with a+d1(mst)

case _mprd=’FBUK’

repl b1 with b1+&qTemp1->qtyshp

* repl b with b+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl b with b+d1(mst)

case _mprd=’FSDL’ .or. _mprd = ‘FSDL.0’ .or. _mprd = ‘FSDL.1’ .or. _mprd = ‘FSDX’ .or. _mprd = ‘FSVC’

repl c1 with c1+&qTemp1->qtyshp

* repl c with c+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl c with c+d1(mst)

case _mprd=’FSFS’

repl d1 with d1+&qTemp1->qtyshp

* repl d with d+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl d with d+d1(mst)

case _mprd=’FSHD’

repl e1 with e1+&qTemp1->qtyshp

* repl e with e+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl e with e+d1(mst)

case _mprd=’FSPB’

repl f1 with f1+&qTemp1->qtyshp

* repl f with f+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl f with f+d1(mst)

case _mprd=’FSPL’ && .or. _mprd = ‘FSMT’

repl g1 with g1+&qTemp1->qtyshp

* repl g with g+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl g with g+d1(mst)

case _mprd=’FSSN’

repl h1 with h1+&qTemp1->qtyshp

* repl h with h+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl h with h+d1(mst)

case _mprd=’FSUD’ .or. _mprd = ‘FSMT’

repl i1 with i1+&qTemp1->qtyshp

* repl i with i+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl i with i+d1(mst)

case _mprd=’FSUP’

repl j1 with j1+&qTemp1->qtyshp

* repl j with j+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl j with j+d1(mst)

case _mprd=’FTFC’

repl k1 with k1+&qTemp1->qtyshp

* repl k with k+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl k with k+d1(mst)

case _mprd=’FTHK’

repl l1 with l1+&qTemp1->qtyshp

* repl l with l+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl l with l+d1(mst)

case _mprd=’FTNP’

repl m1 with m1+&qTemp1->qtyshp

* repl m with m+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl m with m+d1(mst)

case _mprd=’FTPC’

repl n1 with n1+&qTemp1->qtyshp

* repl n with n+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl n with n+d1(mst)

case _mprd=’FTTR’

repl o1 with o1+&qTemp1->qtyshp

* repl o with o+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl o with o+d1(mst)

case _mprd=’FBWS’ && (_mpn=”FBSSL.100″ or _mpn=”FBSSL.200″ or _mpn=”FBSSM.100″ or _mpn=”FBSSM.200″ or _mpn=”FBSSX.080″ or _mpn=”FBSSX.160″)

repl p1 with p1+&qTemp1->qtyshp

* repl o with o+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl p with p+d1(mst)

otherwise

repl x1 with x1+&qTemp1->qtyshp

* repl x with x+((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

repl x with x+d1(mst)

endcase

endif

sele &qTemp1

skip

enddo

 

 

sele &qTemp

goto top

DELE ALL FOR (A+B+C+D+E+F+G+H+I+J+K+L+M+N+O+P+X) = 0

 

STORE 0 TO GT_TOTAL

 

GO TOP

do tsiutil with 0,0,’Printing….’,0,0,0,0,”Msg”

set print on

set console off

set escape on

if mskup1=’V’

if mskup2=’T’

report form sls987 noeject

else

report form sls987a noeject

endif

else

if mskup2=’T’

report form sls9871 noeject

else

report form sls9871a noeject

endif

endif

set console on

set escape off

set print off

eject

restore screen from s2oerpt

release s2oerpt

if prtcode = ‘S’

if mskup1=’V’

if mskup2=’T’

do tsiprt with ‘sls987’,prtlen, prtcode

else

do tsiprt with ‘sls987a’,prtlen, prtcode

endif

else

if mskup2=’T’

do tsiprt with ‘sls9871’,prtlen, prtcode

else

do tsiprt with ‘sls9871a’,prtlen, prtcode

endif

endif

endif

close all

endif

do usercek with 2, “COE”, “Final Lap.Rutin Sales Aktual “+zcompid+’ U:’+ZXPASS+’ Periode :’+dtoc(wsdate)+’ s/d ‘+dtoc(wedate)+’ > ‘+mketskup

 

 

***********

Function D1

***********

Para switch

public d_ok

store 0 to d_ok

do case

case switch=’1′

d_ok = &qTemp1->Qtyshp*(&qTemp1->usell-&qTemp1->ucost)

case switch=’2′

d_ok = &qTemp1->qtyshp*&qTemp1->usell

case switch=’3′

d_ok = ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)))))

case switch=’4′

d_ok = ((&qTemp1->QTYSHP*&qTemp1->USELL) – ((&qTemp1->DISCQTY * &qTemp1->USELL) + ((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)) + ((&qTemp1->discash/100)*(((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL)-((&qTemp1->discpct/100)*((&qTemp1->QTYSHP-&qTemp1->DISCQTY)*&qTemp1->USELL))))))

endcase

return (d_ok)

This entry was posted in Programming. Bookmark the permalink.

One Response to tabel temporary pada foxpro lawas

  1. pur says:

    forum ini sangat baik bagi pemula programer seperti saya

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s