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


Here are some suggestions to, I believe, improve your codes presented here. There may be better ways than that but without your sample data, your desired output is hard to be pictured.

CLOSE ALL
CLEAR ALL
SET TALK OFF
SET DELETED ON
SET DATE ITALIAN
PUBLIC zdatapath, zstartdate

*!*	zstartdate=CTOD('01-04-2010')
zstartdate = DATE(2010,01,04)  && better use date() function

*zdatapath='1006\'
zdatapath=''


*** You don't need to make the tables active by issuing SELECT after USE since you are moving into a different table immediately.  
*** for your needs of setting the order into an index tag, you can do it on the same line of the USE command


*** Use Data Files
USE zdatapath+'inward' AGAIN IN SELECT('inward') ORDER billno && you can open the table again with the index tag as the active order
*!*	SELECT inward
USE zdatapath+'inward1' AGAIN IN SELECT('inward1') ORDER date
*!*	SELECT inward1

USE zdatapath+'pcode2' AGAIN IN SELECT('pcode2')
*!*	SELECT pcode2
USE rep AGAIN IN 0
*!*	SELECT rep
USE IN SELECT('yinrep')

*!*	SELECT inward
*!*	SET ORDER TO billno
*!*	GOTO TOP

USE zdatapath+'inward1' AGAIN IN SELECT('inward12') ALIAS inward12 ORDER reco1
*!*	SELECT inward12
*!*	SET ORDER TO reco1

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

USE zdatapath+'inward' AGAIN IN SELECT('inwardbo') ALIAS inwardbo
*!*	SELECT inwardbo
*!*	SET ORDER TO billno

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


****** Cursor to Store Data

* Doesn't need these as you issued CLOSE ALL above
*!*	USE IN SELECT('wstkrep2')
*!*	USE IN SELECT('wstkrep')


CREATE CURSOR wstkrep (DATE d, dno c(26), pcode c<img src='/emoticons/m21.gif' alt='devil' border='0' />, 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))

* Doesn't need this, since you issued a CREATE command, this will be the active workarea
*!*	SELECT wstkrep

INDEX ON DATE TO wstkrep2
INDEX ON accode+pcode+DTOS(DATE) TO wstkrep1
********************************************************

*** Start Loop

SELECT inward1    && Since I included the ORDER clause in USE above, then you don't need to set order below
*!*	SET ORDER TO DATE   

*** Remove mdot on the left side (setting a value to variables).  Please read my blog on Learning MDot Usage
LOCAL xxval1, xxval2, xxval3
m.xxval1=1
m.xxval2=1
m.xxval3=RECCOUNT()
GOTO TOP
DO WHILE NOT EOF('inward1')
   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(inward1.STATUS,'G1','G3')

      LOCAL xxcutbal, xxwtbal   && move local declaration anywhere above the DO while NOT EOF() so you won't have to re-declare it every now and then

      STORE 0 TO m.xxcutbal, m.xxwtbal
      IF SEEK(inward1.beamno,'inwardbo','billno')
         DO WHILE inwardbo.billno=inward1.beamno AND NOT EOF('inwardbo')
            DO CASE
            CASE inwardbo.issucode=inward1.accode AND INLIST(inwardbo.STATUS,'BO','BJ','RB')
               m.xxcutbal=m.xxcutbal+inwardbo.totcut
               m.xxwtbal=m.xxwtbal+inwardbo.beamwt
            CASE inwardbo.accode=inward1.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   && same comment as above

      STORE 0 TO m.xxavgwt,m.xxweftwt
      m.xxavgwt=ROUND(m.xxwtbal/m.xxcutbal,3)*inward1.taka
      m.xxweftwt=(inward1.weight-m.xxavgwt)

      IF m.xxwtbal#0   && Replace from Transaction Data File
         IF SEEK(inward1.beamno,'inward12bo','beamno')
            DO WHILE inward12bo.beamno=inward1.beamno AND NOT EOF('inward12bo')
               IF (inward12bo.STATUS='BO' OR inward12bo.STATUS='BJ')
                  DO CASE
                  CASE BETWEEN(inward1.DATE,rep.ldate,rep.ldate1)    && New record

* Might consider using INSERT INTO here instead of APPEND BLANK then REPLACE

                     APPEND BLANK IN wstkrep
                     REPLACE ;
                     wstkrep.DATE WITH inward1.DATE, ;
                     wstkrep.pcode WITH inward12bo.pcode, ;
                     wstkrep.accode WITH inward1.accode ,;
                     wstkrep.issout WITH wstkrep.issout +ROUND((m.xxavgwt*inward12bo.yarnp/100),3)  ,;
                     wstkrep.issoutwarp WITH wstkrep.issoutwarp +ROUND((m.xxavgwt*inward12bo.yarnp/100),3) IN wstkrep



                  CASE inward1.DATE<rep.ldate   &&  Update Opening
                     mkey=inward1.accode+inward12bo.pcode+DTOS(rep.ldate-1)
                     IF NOT SEEK(mkey,'wstkrep','wstkrep1')
     
* Same, use INSERT INTO     
                        APPEND BLANK IN wstkrep
                        REPLACE ;
                        wstkrep.DATE WITH rep.ldate-1, ;
                        wstkrep.pcode WITH inward12bo.pcode, ;
                        wstkrep.accode WITH inward1.accode IN wstkrep
                     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(inward1.dnocode,'pcode2','dnocode')
            DO WHILE pcode2.dnocode=inward1.dnocode AND NOT EOF('pcode2')
               IF UPPER(pcode2.yarntype)='WEFT'
                  DO CASE
                  CASE BETWEEN(inward1.DATE,rep.ldate,rep.ldate1)    && New record

* INSERT INTO
                     APPEND BLANK IN wstkrep
                     REPLACE ;
                     wstkrep.DATE WITH inward1.DATE, ;
                     wstkrep.pcode WITH pcode2.yarncode, ;
                     wstkrep.accode WITH inward1.accode ,;
                     wstkrep.issout WITH wstkrep.issout +ROUND((m.xxweftwt*pcode2.yarnp/100),3)  ,;
                     wstkrep.issoutweft WITH wstkrep.issoutweft +ROUND((m.xxweftwt*pcode2.yarnp/100),3)  IN wstkrep

                  CASE inward1.DATE<rep.ldate   &&  Add a new Record as Opening
                     mkey=inward1.accode+pcode2.yarncode+DTOS(rep.ldate-1)
                     IF NOT SEEK(mkey,'wstkrep','wstkrep1')
* INSERT INTO
                        APPEND BLANK IN wstkrep
                        REPLACE ;
                        wstkrep.DATE WITH rep.ldate-1, ;
                        wstkrep.pcode WITH pcode2.yarncode, ;
                        wstkrep.accode WITH inward1.accode IN wstkrep
                     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 inward1
ENDDO
USE IN SELECT('INWARD12')
USE IN SELECT('INWARD12bo')
USE IN SELECT('INWARDbo')
USE IN SELECT('INWARD12bi')
SELECT wstkrep
*** Report Form ...




http://www.junblogs.com/
http://weblogs.foxite.com/sandstorm36/default.aspx
http://www.coderisland.com/forum/viewforum.php?f=10

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