jxctotalabcd.prg

通过VFP编程的方式来统计指定数据库中的有关数据

* -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
*  文件名: JXCTOTALABCD.PRG <-- 本文件由 UnFoxAll 创建
* -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


 SET TALK OFF
 SET SAFETY OFF
 SET DELETED ON
 SET DATE TO ANSI
 SET CENTURY ON
 LNSELE00 = SELECT()
 DM = IIF(TYPE('SHORTNAME') = 'C',SHORTNAME,'')
 IF TYPE('DATEFROM') <> 'C'
    DATEEND0 = DATE()
    DATEEND = DTOC(DATE())
    IF DAY(DATE()) = 1
       DATEEND0 = DATE() - 1
       DATEEND = DTOC(DATE() - 1)
    ENDIF 
    DATEFROM0 = DATEEND0
    LNMONTH = MONTH(DATEFROM0)
    DO WHILE .T.
       DATEFROM0 = DATEFROM0 - 1
       IF MONTH(DATEFROM0) < LNMONTH
          DATEFROM0 = DATEFROM0 + 1
          EXIT 
       ENDIF 
    ENDDO 
    DATEFROM = DTOC(DATEFROM0)
 ENDIF 
 WAIT WINDOW NOWAIT TIMEOUT 60 '正在进行数据计算......'
 SELECT 0
 CREATE CURSOR KCB0 ( 日期 DATE , 商品类别 C ( 1 ) , 品牌 C ( 8 ) , 型号 C ( 10 ) ,  ;
      入库数量 I , 出库数量 I , 库存数量 I )
 COPY TO KCBMJ STRUCTURE 
 USE 
 CREATE CURSOR KCB0 ( 日期 DATE , 商品类别 C ( 1 ) , 品牌 C ( 8 ) , 型号 C ( 10 ) ,  ;
      入库数量 I , 出库数量 I , 库存数量 I )
 COPY TO KCBTMP STRUCTURE 
 USE 
 USE EXCLUSIVE KCBMJ
 DELETE ALL FOR  ;
      日期 = DATE() .OR.  ;
(日期 < DATE() - 40 .OR. 日期 > DATE() + 40) AND DAY(DATE()) <> 1 AND  ;
DAY(DATE()) <> 28 AND DAY(DATE()) <> 29 AND DAY(DATE()) <> 30 AND DAY(DATE()) <> 31 .OR. EMPTY(日期)
 PACK 
 SELECT 0
 USE KCBTMP
 LLJJ = 0
 IF  .NOT. USED('JJ')
     USEDBFJJ()
    LLJJ = 1
 ENDIF 
 LLJP = 0
 IF  .NOT. USED('JP')
     USEDBFJP()
    LLJP = 1
 ENDIF 
 LLYX = 0
 IF  .NOT. USED('YX')
     USEDBFYX()
    LLYX = 1
 ENDIF 
 LLDHKC = 0
 IF  .NOT. USED('DHKC')
     USEDBFYSDHKC()
    LLDHKC = 1
 ENDIF 
 LLYSRK = 0
 IF  .NOT. USED('YSRK')
     USEDBFYSRK()
    LLYSRK = 1
 ENDIF 
 LLYSCK = 0
 IF  .NOT. USED('YSCK')
     USEDBFYSCK()
    LLYSCK = 1
 ENDIF 
 LLYSDC = 0
 IF  .NOT. USED('YSDC')
     USEDBFYSDC()
    LLYSDC = 1
 ENDIF 
 LLYSTH = 0
 IF  .NOT. USED('YSTH')
     USEDBFYSTH()
    LLYSTH = 1
 ENDIF 
 SELECT 'A'  商品类别 , B  型号 , COUN( * )  库存数量 WHERE  ;
      JCRQ <= DATEEND AND ((CCRQ < '0001.01.01' .OR. CCRQ > DATEEND) .OR. MF = ' ') GROUP BY B ORDER BY  ;
      B INTO TABLE KCBTMP00 FROM JJ
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'A'  商品类别 , B  型号 , COUN( * )  入库数量 WHERE  ;
      JCRQ >= DATEFROM AND JCRQ <= DATEEND GROUP BY B ORDER BY B INTO TABLE KCBTMP00 FROM JJ
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'A'  商品类别 , B  型号 , COUN( * )  出库数量 WHERE  ;
      MF <> ' ' AND CCRQ >= DATEFROM AND CCRQ <= DATEEND GROUP BY B ORDER BY B INTO TABLE KCBTMP00 FROM  ;
      JJ
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'B'  商品类别 , B  品牌 , MODEL  型号 , COUN( * )  库存数量 WHERE  ;
      JCRQ <= DATEEND AND ((CCRQ < '0001.01.01' .OR. CCRQ > DATEEND) .OR. MF = ' ')  ;
      GROUP BY B , F1 ORDER BY B , F1 INTO TABLE KCBTMP00 FROM JP
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'B'  商品类别 , B  品牌 , MODEL  型号 , COUN( * )  入库数量 WHERE  ;
      JCRQ >= DATEFROM AND JCRQ <= DATEEND GROUP BY B , F1 ORDER BY B , F1 INTO TABLE  ;
      KCBTMP00 FROM JP
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'B'  商品类别 , B  品牌 , MODEL  型号 , COUN( * )  出库数量 WHERE  ;
      MF <> ' ' AND CCRQ >= DATEFROM AND CCRQ <= DATEEND GROUP BY B , F1 ORDER BY B , F1  ;
      INTO TABLE KCBTMP00 FROM JP
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'C'  商品类别 , B  品牌 , MODEL  型号 , COUN( * )  库存数量 WHERE  ;
      JCRQ <= DATEEND AND ((CCRQ < '0001.01.01' .OR. CCRQ > DATEEND) .OR. MF = ' ')  ;
      GROUP BY B , F1 ORDER BY B , F1 INTO TABLE KCBTMP00 FROM YX
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'C'  商品类别 , B  品牌 , MODEL  型号 , COUN( * )  入库数量 WHERE  ;
      JCRQ >= DATEFROM AND JCRQ <= DATEEND GROUP BY B , F1 ORDER BY B , F1 INTO TABLE  ;
      KCBTMP00 FROM YX
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'C'  商品类别 , B  品牌 , MODEL  型号 , COUN( * )  出库数量 WHERE  ;
      MF <> ' ' AND CCRQ >= DATEFROM AND CCRQ <= DATEEND GROUP BY B , F1 ORDER BY B , F1  ;
      INTO TABLE KCBTMP00 FROM YX
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 CREATE CURSOR YSKCCURS ( ID C ( 8 ) , QCS I , RK I , DB I , TH I , XS I )
 SELECT ID , SUM(QCS)  QCS GROUP BY ID ORDER BY ID INTO TABLE YSKCTMP00 FROM DHKC
 IF _TALLY > 0
    SELECT YSKCCURS
    APPEND FROM YSKCTMP00
 ENDIF 
 SELECT ID , SUM(SL)  RK WHERE RQ <= DATEEND GROUP BY ID ORDER BY ID INTO TABLE  ;
      YSKCTMP00 FROM YSRK
 IF _TALLY > 0
    SELECT YSKCCURS
    APPEND FROM YSKCTMP00
 ENDIF 
 SELECT ID , SUM(SL)  XS WHERE RQ <= DATEEND GROUP BY ID ORDER BY ID INTO TABLE  ;
      YSKCTMP00 FROM YSCK
 IF _TALLY > 0
    SELECT YSKCCURS
    APPEND FROM YSKCTMP00
 ENDIF 
 SELECT ID , SUM(SL)  DB WHERE RQ <= DATEEND GROUP BY ID ORDER BY ID INTO TABLE  ;
      YSKCTMP00 FROM YSDC
 IF _TALLY > 0
    SELECT YSKCCURS
    APPEND FROM YSKCTMP00
 ENDIF 
 SELECT ID , SUM(SL)  TH WHERE RQ <= DATEEND GROUP BY ID ORDER BY ID INTO TABLE  ;
      YSKCTMP00 FROM YSTH
 IF _TALLY > 0
    SELECT YSKCCURS
    APPEND FROM YSKCTMP00
 ENDIF 
 SELECT YSKCTMP00
 USE 
 DELETE File YSKCTMP00.DBF
 SELECT 'D'  商品类别 , LEFT(ID,2)  品牌 , ID  型号 , SUM(QCS + RK - DB - TH - XS)   ;
      库存数量 GROUP BY ID ORDER BY ID INTO TABLE KCBTMP00 FROM YSKCCURS
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'D'  商品类别 , LEFT(ID,2)  品牌 , ID  型号 , SUM(SL)  入库数量 WHERE  ;
      RQ >= DATEFROM AND RQ <= DATEEND GROUP BY ID ORDER BY ID INTO TABLE KCBTMP00  ;
      FROM YSRK
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'D'  商品类别 , LEFT(ID,2)  品牌 , ID  型号 , SUM(SL)  出库数量 WHERE  ;
      RQ >= DATEFROM AND RQ <= DATEEND GROUP BY ID ORDER BY ID INTO TABLE KCBTMP00  ;
      FROM YSCK
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'D'  商品类别 , LEFT(ID,2)  品牌 , ID  型号 , SUM(SL)  出库数量 WHERE  ;
      RQ >= DATEFROM AND RQ <= DATEEND GROUP BY ID ORDER BY ID INTO TABLE KCBTMP00  ;
      FROM YSDC
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'D'  商品类别 , LEFT(ID,2)  品牌 , ID  型号 , SUM(SL)  出库数量 WHERE  ;
      RQ >= DATEFROM AND RQ <= DATEEND GROUP BY ID ORDER BY ID INTO TABLE KCBTMP00  ;
      FROM YSTH
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT YSKCCURS
 USE 
 SELECT KCBTMP00
 USE 
 DELETE File KCBTMP00.DBF
 SELECT DATE()  日期 , 商品类别 , 品牌 , 型号 , SUM(入库数量)  入库数量 , SUM(出库数量)  ;
       出库数量 , SUM(库存数量)  库存数量 GROUP BY 1 , 2 , 3 , 4 ORDER BY 1 ,  ;
      2 , 3 , 4 INTO TABLE KCBTMP0 FROM KCBTMP
 SELECT KCBMJ
 APPEND FROM KCBTMP0
 SELECT KCBTMP
 USE 
 DELETE File KCBTMP.DBF
 SELECT KCBTMP0
 USE 
 DELETE File KCBTMP0.DBF
 SELECT KCBMJ
 SUM FOR 日期 = DATE() AND 商品类别 = 'A' TO TA0 库存数量
 SUM FOR 日期 = DATE() AND 商品类别 = 'B' TO TB0 库存数量
 SUM FOR 日期 = DATE() AND 商品类别 = 'C' TO TC0 库存数量
 SUM FOR 日期 = DATE() AND 商品类别 = 'D' TO TD0 库存数量
 COUNT TO LL
 IF LL > 0
    GO TOP
    DATETIME = DATETIME()
    REPORT FORM 进销存 PREVIEW NOCONSOLE FOR  ;
         日期 = DATE() AND (入库数量 <> 0 .OR. 出库数量 <> 0 .OR. 库存数量 <> 0)
    REPORT FORM 进销存 TO PRINTER PROMPT NOCONSOLE FOR  ;
         日期 = DATE() AND (入库数量 <> 0 .OR. 出库数量 <> 0 .OR. 库存数量 <> 0)
 ELSE 
    = MESSAGEBOX('进销存统计数为零！    ',64,'信息')
 ENDIF 
 USE 
 DELETE File KCBMJ.DBF
 WAIT WINDOW NOWAIT TIMEOUT 3 '进销存统计完毕！'
 IF LLJJ = 1
    SELECT JJ
    USE 
 ENDIF 
 IF LLJP = 1
    SELECT JP
    USE 
 ENDIF 
 IF LLYX = 1
    SELECT YX
    USE 
 ENDIF 
 IF LLDHKC = 1
    SELECT DHKC
    USE 
 ENDIF 
 IF LLYSRK = 1
    SELECT YSRK
    USE 
 ENDIF 
 IF LLYSCK = 1
    SELECT YSCK
    USE 
 ENDIF 
 IF LLYSDC = 1
    SELECT YSDC
    USE 
 ENDIF 
 IF LLYSTH = 1
    SELECT YSTH
    USE 
 ENDIF 
 SELECT (LNSELE00)
 RETURN 
*