jxctotalb.prg

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

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