jxctotald.prg

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

* -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
*  文件名: JXCTOTALD.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 ( 日期 T , 商品类别 C ( 1 ) , 品牌 C ( 8 ) , 型号 C ( 10 ) , 名称 C  ;
      ( 32 ) , 单价 Y , 上期数量 I , 入库数量 I , 出库数量 I , 销售数量 I ,  ;
      调出数量 I , 退出数量 I , 库存数量 I )
 COPY TO KCBYSTJ STRUCTURE 
 USE 
 CREATE CURSOR KCB0 ( 日期 T , 商品类别 C ( 1 ) , 品牌 C ( 8 ) , 型号 C ( 10 ) , 名称 C  ;
      ( 32 ) , 单价 Y , 上期数量 I , 入库数量 I , 出库数量 I , 销售数量 I ,  ;
      调出数量 I , 退出数量 I , 库存数量 I )
 COPY TO KCBTMP STRUCTURE 
 USE 
 USE EXCLUSIVE KCBYSTJ
 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
  USEDBFYSDHKC()
  USEDBFYSRK()
  USEDBFYSCK()
  USEDBFYSDC()
  USEDBFYSTH()
 CREATE CURSOR YSKCCURS ( ID C ( 8 ) , MC C ( 32 ) , PJ Y , QCS I , RK I , DB I , TH I ,  ;
      XS I )
 SELECT ID , MC + GG + DW  MC , PJ , 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  型号 , MAX(MC)  名称 , MAX(PJ)  单价 ,  ;
      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()  日期 , 商品类别 , 品牌 , 型号 , MAX(名称)  名称 , MAX(单价)  单价 ,  ;
      SUM(上期数量)  上期数量 , SUM(入库数量)  入库数量 , 99999999  出库数量 ,  ;
      SUM(销售数量)  销售数量 , SUM(调出数量)  调出数量 , SUM(退出数量)   ;
      退出数量 , SUM(库存数量)  库存数量 GROUP BY 1 , 2 , 3 , 4 ORDER BY 1 , 2 ,  ;
      3 , 4 INTO TABLE KCBTMP0 FROM KCBTMP
 REPLACE 出库数量 WITH (销售数量 + 退出数量 + 调出数量)
 REPLACE 上期数量 WITH (库存数量 + 出库数量 - 入库数量)
 SELECT KCBYSTJ
 APPEND FROM KCBTMP0
 SELECT KCBTMP
 USE 
 DELETE File KCBTMP.DBF
 SELECT KCBTMP0
 USE 
 DELETE File KCBTMP0.DBF
 SELECT KCBYSTJ
 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 进销存D PREVIEW NOCONSOLE FOR  ;
         日期 = DATE() AND (入库数量 <> 0 .OR. 出库数量 <> 0 .OR. 库存数量 <> 0)
    REPORT FORM 进销存D TO PRINTER PROMPT NOCONSOLE FOR  ;
         日期 = DATE() AND (入库数量 <> 0 .OR. 出库数量 <> 0 .OR. 库存数量 <> 0)
 ELSE 
    = MESSAGEBOX('D类商品进销存统计数为零！    ',64,'信息')
 ENDIF 
 USE 
 SELECT DHKC
 USE 
 SELECT YSRK
 USE 
 SELECT YSCK
 USE 
 SELECT YSDC
 USE 
 SELECT YSTH
 USE 
 DELETE File KCBYSTJ.DBF
 WAIT WINDOW NOWAIT TIMEOUT 3 '进销存统计完毕！'
 SELECT (LNSELE00)
 RETURN 
*