Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: mk sharma
  Where is mk sharma?
 mumbai
 India
 mk sharma
 To: Jun Tangunan
  Where is Jun Tangunan?
 Cabanatuan
 Philippines
 Jun Tangunan
 Tags
Subject: RE: how to optimize/fast this
Thread ID: 268658 Message ID: 268756 # Views: 34 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, July 14, 2010 11:58:18 AM         
   


below code showing result in 3 seconds


CLOSE ALL
CLEAR ALL
SET TALK OFF
SET DELETED ON
SET DATE ITALIAN
PUBLIC zdatapath, zstartdate
zstartdate=CTOD('01-04-2010')
*zdatapath='1006\'
zdatapath=''

=SYS(3054,0)
xxsec=SECONDS()

*** Use Data Files

USE zdatapath+'inward' AGAIN IN SELECT('inward') ORDER billno
USE zdatapath+'inward1' AGAIN IN SELECT('inward1') ORDER date 
USE zdatapath+'pcode2' AGAIN IN SELECT('pcode2') order dnocode
USE rep AGAIN IN 0
USE zdatapath+'inward1' AGAIN IN SELECT('inward12') ALIAS inward12 ORDER reco1

SELECT beamno, pcode,yarnp,STATUS FROM inward1 WHERE INLIST(STATUS,'BO','BJ') INTO CURSOR inward12bo READWRITE
SELECT inward12bo
INDEX ON beamno TAG beamno

SELECT billno, issucode,accode,totcut,beamwt,STATUS FROM inward WHERE INLIST(STATUS,'BO','BJ','RB') INTO CURSOR inwardbo READWRITE
SELECT inwardbo
INDEX ON billno TAG billno

USE zdatapath+'inward1' AGAIN IN SELECT('inward12bi') ALIAS inward12bi
SELECT inward12bi
SET ORDER TO beamno
*********************************************

SELECT DATE,accode,STATUS,beamno,dnocode,taka,weight FROM inward1 WHERE INLIST(STATUS,'BI','RB','IW','IS','RW','RS','WW','SW','G1','G2','G3','G4') INTO CURSOR xxinward1 ORDER BY DATE

****** Cursor to Store Data
USE IN SELECT('wstkrep2')
USE IN SELECT('wstkrep')
CREATE CURSOR wstkrep (DATE d, dno c(26), pcode c(6), igroup c(35), beamno c(8), LOOMNO c(4), ;
accode c(7), millcode c(7), lotno c(10), ctnno c(10), ;
opbal N(10,3), opbalweft N(10,3), opbalwarp N(10,3), stkin N(10,3), stkinweft N(10,3), ;
stkinwarp N(10,3), prodin N(10,3), retin N(10,3), ;
retout N(10,3), retoutweft N(10,3), retoutwarp N(10,3), issout N(10,3), ;
issoutweft N(10,3), issoutwarp N(10,3), wast N(10,3),STATUS c(2),REM1 c(20))
SELECT wstkrep
INDEX ON DATE TO wstkrep2
INDEX ON accode+pcode+DTOS(DATE) TO wstkrep1
********************************************************

*** Start Loop

SELECT xxinward1
*SET ORDER TO DATE
LOCAL xxval1, xxval2, xxval3
m.xxval1=1
m.xxval2=1
m.xxval3=RECCOUNT()
GOTO TOP
DO WHILE NOT EOF('xxinward1')
   IF m.xxval1=m.xxval2
      WAIT WINDOW AT SROWS()/2, SCOLS()/2 NOWAIT ALLTRIM(STR(m.xxval1))+" ...Working... "+ALLTRIM(STR(m.xxval3))
      m.xxval1=m.xxval1+1000
   ENDIF
   IF INLIST(xxinward1.STATUS,'G1','G3')
      LOCAL xxcutbal, xxwtbal
      STORE 0 TO m.xxcutbal, m.xxwtbal
      IF SEEK(xxinward1.beamno,'inwardbo','billno')
         DO WHILE inwardbo.billno=xxinward1.beamno AND NOT EOF('inwardbo')
            DO CASE
            CASE inwardbo.issucode=xxinward1.accode AND INLIST(inwardbo.STATUS,'BO','BJ','RB')
               m.xxcutbal=m.xxcutbal+inwardbo.totcut
               m.xxwtbal=m.xxwtbal+inwardbo.beamwt
            CASE inwardbo.accode=xxinward1.accode AND inwardbo.STATUS='RB'
               m.xxcutbal=m.xxcutbal-inwardbo.totcut
               m.xxwtbal=m.xxwtbal-inwardbo.beamwt
            ENDCASE
            SKIP 1 IN inwardbo
         ENDDO
      ENDIF
      LOCAL xxavgwt,xxweftwt
      STORE 0 TO m.xxavgwt,m.xxweftwt
      m.xxavgwt=ROUND(m.xxwtbal/m.xxcutbal,3)*xxinward1.taka
      m.xxweftwt=(xxinward1.weight-m.xxavgwt)

      IF m.xxwtbal#0   && Replace from Transaction Data File
         IF SEEK(xxinward1.beamno,'inward12bo','beamno')
            DO WHILE inward12bo.beamno=xxinward1.beamno AND NOT EOF('inward12bo')
               IF (inward12bo.STATUS='BO' OR inward12bo.STATUS='BJ')
                  DO CASE
                  CASE BETWEEN(xxinward1.DATE,rep.ldate,rep.ldate1)    && New record
                     INSERT INTO wstkrep ;
                     (DATE,pcode,accode,issout,issoutwarp) ;
                     VALUES ;
                     (xxinward1.DATE,inward12bo.pcode,  xxinward1.accode, ROUND((m.xxavgwt*inward12bo.yarnp/100),3), ROUND((m.xxavgwt*inward12bo.yarnp/100),3) )

                  CASE xxinward1.DATE<rep.ldate   &&  Update Opening
                     mkey=xxinward1.accode+inward12bo.pcode+DTOS(rep.ldate-1)
                     IF NOT SEEK(mkey,'wstkrep','wstkrep1')
                        INSERT INTO wstkrep ;
                        (DATE,pcode,accode) ;
                        VALUES ;
                        (rep.ldate-1,inward12bo.pcode,  xxinward1.accode)
                     ENDIF
                     REPLACE ;
                     wstkrep.opbal WITH wstkrep.opbal+ROUND((m.xxavgwt*inward12bo.yarnp/100),3)  ,;
                     wstkrep.opbalwarp WITH wstkrep.opbalwarp +ROUND((m.xxavgwt*inward12bo.yarnp/100),3) IN wstkrep
                  ENDCASE
               ENDIF
               SKIP 1 IN inward12bo
            ENDDO
         ENDIF

         IF SEEK(xxinward1.dnocode,'pcode2','dnocode')
            DO WHILE pcode2.dnocode=xxinward1.dnocode AND NOT EOF('pcode2')
               IF UPPER(pcode2.yarntype)='WEFT'
                  DO CASE
                  CASE BETWEEN(xxinward1.DATE,rep.ldate,rep.ldate1)    && New record
                     INSERT INTO wstkrep ;
                     (DATE,pcode,accode,issout,issoutweft) ;
                     VALUES ;
                     (xxinward1.DATE,pcode2.yarncode,  xxinward1.accode, ROUND((m.xxavgwt*pcode2.yarnp/100),3), ;
                     ROUND((m.xxavgwt*pcode2.yarnp/100),3) )

                  CASE xxinward1.DATE<rep.ldate   &&  Add a new Record as Opening
                     mkey=xxinward1.accode+pcode2.yarncode+DTOS(rep.ldate-1)
                     IF NOT SEEK(mkey,'wstkrep','wstkrep1')
                        INSERT INTO wstkrep ;
                        (DATE,pcode,accode) ;
                        VALUES ;
                        (rep.ldate-1,pcode2.yarncode,  xxinward1.accode)
                     ENDIF
                     REPLACE ;
                     wstkrep.opbal WITH wstkrep.opbal+ROUND((m.xxweftwt*pcode2.yarnp/100),3)  ,;
                     wstkrep.opbalweft WITH wstkrep.opbalweft +ROUND((m.xxweftwt*pcode2.yarnp/100),3)  IN wstkrep
                  ENDCASE
               ENDIF
               SKIP 1 IN pcode2
            ENDDO
         ENDIF
      ENDIF
   ENDIF
   xxval2=xxval2+1
   SKIP 1 IN xxinward1
ENDDO
USE IN SELECT('INWARD12')
USE IN SELECT('INWARD12bo')
USE IN SELECT('INWARDbo')
USE IN SELECT('INWARD12bi')
SELECT wstkrep
*** Report Form ...
?SECONDS()-xxsec



warm regards,
mk.

ENTIRE THREAD

how to optimize/fast this Posted by mk sharma @ 7/13/2010 8:33:26 PM
RE: how to optimize/fast this Posted by Anil Sharma @ 7/13/2010 8:44:44 PM
RE: how to optimize/fast this Posted by mk sharma @ 7/13/2010 8:59:49 PM
RE: how to optimize/fast this Posted by Jun Tangunan @ 7/14/2010 3:17:33 AM
RE: how to optimize/fast this Posted by mk sharma @ 7/14/2010 8:18:12 AM
RE: how to optimize/fast this Posted by Jun Tangunan @ 7/14/2010 10:21:01 AM
RE: how to optimize/fast this Posted by Jun Tangunan @ 7/14/2010 10:59:55 AM
RE: how to optimize/fast this Posted by mk sharma @ 7/14/2010 11:58:18 AM
RE: how to optimize/fast this Posted by Jun Tangunan @ 7/14/2010 12:19:49 PM
RE: how to optimize/fast this Posted by Jun Tangunan @ 7/14/2010 12:23:28 PM
RE: how to optimize/fast this Posted by Binod Binani @ 7/14/2010 8:00:52 AM
RE: how to optimize/fast this Posted by Mike Yearwood @ 7/14/2010 10:40:06 PM
RE: how to optimize/fast this Posted by mk sharma @ 7/15/2010 8:36:51 AM
RE: how to optimize/fast this Posted by Mike Yearwood @ 7/15/2010 6:31:43 PM
RE: how to optimize/fast this Posted by mk sharma @ 7/16/2010 6:31:23 AM
RE: how to optimize/fast this Posted by Mike Yearwood @ 7/16/2010 6:59:23 AM
RE: how to optimize/fast this Posted by Samir H. @ 7/16/2010 7:43:23 AM
RE: how to optimize/fast this Posted by Mike Yearwood @ 7/16/2010 7:25:46 PM
RE: how to optimize/fast this Posted by mk sharma @ 7/16/2010 7:48:03 AM
RE: how to optimize/fast this Posted by tushar @ 7/16/2010 8:05:16 AM
RE: how to optimize/fast this Posted by Mike Yearwood @ 7/16/2010 7:30:36 PM
RE: how to optimize/fast this Posted by Binod Binani @ 7/15/2010 2:27:34 PM
RE: how to optimize/fast this Posted by Mike Yearwood @ 7/15/2010 6:30:23 PM
RE: how to optimize/fast this Posted by Binod Binani @ 7/17/2010 8:01:16 AM
RE: how to optimize/fast this Posted by Mike Yearwood @ 7/18/2010 7:30:27 PM
RE: how to optimize/fast this Posted by Binod Binani @ 7/19/2010 9:49:06 AM
RE: how to optimize/fast this Posted by Mike Yearwood @ 7/19/2010 3:03:16 PM
RE: how to optimize/fast this Posted by Samir H. @ 7/14/2010 3:51:21 AM