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: 268713 # Views: 37 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, July 14, 2010 8:18:12 AM         
   


Below is the sample codes



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


*** Use Data Files
USE zdatapath+'inward' AGAIN IN SELECT('inward')
SELECT inward
USE zdatapath+'inward1' AGAIN IN SELECT('inward1')
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
SELECT inward12
SET ORDER TO reco1

USE zdatapath+'inward1' AGAIN IN SELECT('inward12bo') ALIAS inward12bo
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
SELECT inward12bi
SET ORDER TO beamno
*********************************************


****** 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 inward1
SET ORDER TO DATE
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
      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
      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
                     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')
                        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
                     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')
                        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 ...



warm regards,
mk.


> > i have stored required records in a cursor wstkrep and generated a stock report
> >
> > if the date is less 01-04-2010 then add/less in opening otherwise new record as inward/outward/wastage
> >
> >
> >
> > 
> > ---------------------------------------------------------------------------------------------
> > Date   |  Opening        |      Inward      |     Outward     | Wastage   |      Balance
> >        |-------------------------------------------------------------------------------------
> >        |  cat.1  | Cat.2 |  cat.1  | Cat.2  |  cat.1  | Cat.2 |           |  cat.1  | Cat.2 |
> > ---------------------------------------------------------------------------------------------
> > 

> >
> > warm regards,
> > mk.
> >
> >
> >
>
>
>
> Hello MK,
>
> I agree with Anil. Instead of reusing the tables again and again, it is simpler to create a new cursor out of that. Also with cursor, you can immediately create it only with your needs (just add the fields that is important to you), create a WHERE clause to include only the records that are needed, include ORDER BY to generate a sorted cursor, etc.
>
> That is way much faster than using the table again (which will have all fields and all records) then later filtering it. Filtering also goes very slow depending on the number of records so avoid using it as much as you can.
>
> Now to your need here, it would be very hard for us to study your codes considering that it is very long; so as much as we want to assist I am sure that most of us will simply skip trying to understand what you wanted to do here. So my suggestion is to simply create some sample data just like what Leonard Trevor later learns to do, then your expected output. And maybe we can do it step by step, not the whole expected output outright.
>
>
>
> 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