jxctotala.prg

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

* -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
*  文件名: JXCTOTALA.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 ( 8 ) , 上期数量  ;
      I , 入库数量 I , 出库数量 I , 销售数量 I , 销售金额 Y , 调出数量 I ,  ;
      退出数量 I , 库存数量 I , 库存金额 Y )
 COPY TO KCBJJTJ STRUCTURE 
 USE 
 CREATE CURSOR KCB0 ( 日期 T , 商品类别 C ( 1 ) , 厂商 C ( 8 ) , 品牌 C ( 8 ) , 上期数量  ;
      I , 入库数量 I , 出库数量 I , 销售数量 I , 销售金额 Y , 调出数量 I ,  ;
      退出数量 I , 库存数量 I , 库存金额 Y )
 COPY TO KCBTMP STRUCTURE 
 USE 
 USE EXCLUSIVE KCBJJTJ
 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
 CREATE CURSOR JJKCCURS ( ID C ( 8 ) , B C ( 8 ) , PJ Y , QCS I , RK I , DB I , TH I ,  ;
      XS I , XSJE Y , KC I , KCJE Y )
  USEDBFJJ()
 SELECT ID , B , COUN( * )  QCS WHERE  ;
      JCRQ <= DATEFROM AND ((CCRQ < '0001.01.01' .OR. CCRQ > DATEFROM) .OR. MF = ' ') GROUP BY ID , B ORDER BY ID , B INTO  ;
      TABLE JJKCTMP00 FROM JJ
 IF _TALLY > 0
    SELECT JJKCCURS
    APPEND FROM JJKCTMP00
 ENDIF 
 SELECT ID , B , COUN( * )  RK WHERE BETWEEN(JCRQ,DATEFROM,DATEEND) GROUP BY ID , B  ;
      ORDER BY ID , B INTO TABLE JJKCTMP00 FROM JJ
 IF _TALLY > 0
    SELECT JJKCCURS
    APPEND FROM JJKCTMP00
 ENDIF 
 SELECT ID , B , COUN( * )  XS , SUM(PJ)  XSJE WHERE  ;
      (BETWEEN(CCRQ,DATEFROM,DATEEND)) AND MF = 'Y' GROUP BY ID , B ORDER BY ID , B INTO TABLE JJKCTMP00 FROM  ;
      JJ
 IF _TALLY > 0
    SELECT JJKCCURS
    APPEND FROM JJKCTMP00
 ENDIF 
 SELECT ID , B , COUN( * )  DB WHERE (BETWEEN(CCRQ,DATEFROM,DATEEND)) AND MF = 'B'  ;
      GROUP BY ID , B ORDER BY ID , B INTO TABLE JJKCTMP00 FROM JJ
 IF _TALLY > 0
    SELECT JJKCCURS
    APPEND FROM JJKCTMP00
 ENDIF 
 SELECT ID , B , COUN( * )  TH WHERE  ;
      (BETWEEN(CCRQ,DATEFROM,DATEEND)) AND  .NOT. (MF = 'Y' .OR. MF = 'B' .OR. MF = ' ') GROUP BY ID , B ORDER BY ID , B INTO  ;
      TABLE JJKCTMP00 FROM JJ
 IF _TALLY > 0
    SELECT JJKCCURS
    APPEND FROM JJKCTMP00
 ENDIF 
 SELECT ID , B , COUN( * )  KC , SUM(PJ)  KCJE WHERE  ;
      JCRQ <= DATEEND AND ((CCRQ < '0001.01.01' .OR. CCRQ > DATEEND) .OR. MF = ' ') GROUP BY ID , B ORDER BY ID ,  ;
      B INTO TABLE JJKCTMP00 FROM JJ
 IF _TALLY > 0
    SELECT JJKCCURS
    APPEND FROM JJKCTMP00
 ENDIF 
 SELECT JJKCTMP00
 USE 
 DELETE File JJKCTMP00.DBF
 SELECT 'A'  商品类别 , ID  厂商 , B  品牌 , SUM(KC)  库存数量 , SUM(KCJE)  库存金额  ;
      GROUP BY ID , B ORDER BY ID , B INTO TABLE KCBTMP00 FROM JJKCCURS
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'A'  商品类别 , ID  厂商 , B  品牌 , SUM(RK)  入库数量 GROUP BY ID , B ORDER BY  ;
      ID , B INTO TABLE KCBTMP00 FROM JJKCCURS
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'A'  商品类别 , ID  厂商 , B  品牌 , SUM(XS)  销售数量 , SUM(XSJE)  销售金额  ;
      GROUP BY ID , B ORDER BY ID , B INTO TABLE KCBTMP00 FROM JJKCCURS
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'A'  商品类别 , ID  厂商 , B  品牌 , SUM(DB)  调出数量 GROUP BY ID , B ORDER BY  ;
      ID , B INTO TABLE KCBTMP00 FROM JJKCCURS
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT 'A'  商品类别 , ID  厂商 , B  品牌 , SUM(TH)  退出数量 GROUP BY ID , B ORDER BY  ;
      ID , B INTO TABLE KCBTMP00 FROM JJKCCURS
 IF _TALLY > 0
    SELECT KCBTMP
    APPEND FROM KCBTMP00
 ENDIF 
 SELECT JJKCCURS
 USE 
 SELECT KCBTMP00
 USE 
 DELETE File KCBTMP00.DBF
 SELECT DATE()  日期 , 商品类别 , 厂商 , 品牌 , SUM(上期数量)  上期数量 , SUM(入库数量)  ;
       入库数量 , 99999999  出库数量 , SUM(销售数量)  销售数量 ,  ;
      SUM(销售金额)  销售金额 , 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 KCBJJTJ
 APPEND FROM KCBTMP0
 SELECT JJ
 USE 
 SELECT KCBTMP
 USE 
 DELETE File KCBTMP.DBF
 SELECT KCBTMP0
 USE 
 DELETE File KCBTMP0.DBF
 SELECT KCBJJTJ
 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 进销存A PREVIEW NOCONSOLE FOR  ;
         日期 = DATE() AND (入库数量 <> 0 .OR. 出库数量 <> 0 .OR. 库存数量 <> 0)
    REPORT FORM 进销存A TO PRINTER PROMPT NOCONSOLE FOR  ;
         日期 = DATE() AND (入库数量 <> 0 .OR. 出库数量 <> 0 .OR. 库存数量 <> 0)
    USE 
 ELSE 
    = MESSAGEBOX('A类商品进销存统计数为零！    ',64,'信息')
 ENDIF 
 IF USED('KCBJJTJ')
    SELECT KCBJJTJ
    USE 
 ENDIF 
 DELETE File KCBJJTJ.DBF
 WAIT WINDOW NOWAIT TIMEOUT 3 '进销存统计完毕！'
 SELECT (LNSELE00)
 RETURN 
*