開單統計表

我有兩個表﹐都在SQL中﹕一個是T_ORDER﹐另一個是T_ORDERC
其中T_ORDER表中有zzno(單號）,zzoperator(操作者),zzorddatet(開單日期)等字段﹐而T_ORDERC中有zzno,zzmainst（主石）等字段。
T_ORDER與T_ORDERC兩表是按T_ZZNO進行關聯的。
現在我要的結果是輸入一個日期﹐然后統計出每月每個人的總數（即T_ORDERC中滿足條件的記錄條數),
每月每個人的間色數（即T_ORDERC中滿足條件的記錄條數﹐只不過﹐此時的條件還要zzmainst='zz'）
最后生成的表的結果要如下所示（如我輸入的開始日期為2004年12月20號﹐結束日期為﹕2005年1月5日）
（我生成的表的字段都是要看輸入的日期來自動生成的）﹕

操作者 2004年12月總數  2004年12月間色數  2005年1月總數   2005年1月間色數
...      ......        ....              ....           ....
...      ......        ....              ....           ....
...      ......        ....              ....           ....

IF EMPTY(thisform.text1.Value) OR EMPTY(thisform.text2.Value)
   MESSAGEBOX("請輸入訂單起始日",64,"系統提示")
   RETURN
ENDIF
mi=MIN(THISFORM.text1.VALUE,THISFORM.text2.VALUE)
ma=MAX(THISFORM.text1.VALUE,THISFORM.text2.VALUE)
thisform.text1.Value=mi
thisform.text2.Value=ma
miyear=year(mi)
mayear=year(ma)
mimon=month(mi)
mamon=month(ma)
nwhere=" zzorddate>='"+DTOC(mi)+"' and zzorddate<='"+DTOC(ma)+"'"
listname="operator c(15),"
nlist=""
mamon=mamon+(mayear-miyear)*12
FOR I=mimon to mamon
    listname=listname+"zs"+alltrim(str(I-mimon+1))+" n(6,0),js"+alltrim(str(I-mimon+1))+" n(5,0),"
    nlist=nlist+"sum(zs"+alltrim(str(I-mimon+1))+"),sum(js"+alltrim(str(I-mimon+1))+"),"  
ENDFOR
listname=listname+"zshj n(7,0),jshj n(6,0)"
nlist=nlist+"sum(zshj) zshj,sum(jshj) jshj"
CREAT CURSOR kdb(&listname)
IF SQLEXEC(connect_sql,"select zzoperator as operator from t_order where "+nwhere,"aa")<0
   RETURN
ELSE
   SELE kdb
   APPE FROM DBF("aa")
ENDIF
nK=1
afieldlist=""
bfieldlist=""
FOR I=mimon to mamon
    DIME nArray1(nK),nArray2(nK)
 nyear=miyear+iif(I=1,0,int((I-1)/12))
 nmonth=iif(I%12=0,12,I%12)
 aname="zs"+alltrim(str(I-mimon+1))
 bname="js"+alltrim(str(I-mimon+1))
 afieldlist=afieldlist+"+"+aname
 bfieldlist=bfieldlist+"+"+bname
    IF SQLEXEC(connect_sql,"select cast(count_big(*) as int(6)) "+aname+",aa.zzoperator operator from t_order aa,t_orderc bb where aa.zzno=bb.zzno and datepart(year,aa.zzorddate)="+alltrim(str(nyear))+" and datepart(month,aa.zzorddate)="+alltrim(str(nmonth))+" group by aa.zzorddate,aa.zzoperator order by aa.zzorddate,aa.zzoperator","bb")<0
       RETURN              
    ENDIF
    SELE BB
    INDEX ON operator tag operator
    sele kdb
    set relation to operator into bb
    replace all &aname with bb.&aname
    IF SQLEXEC(connect_sql,"select cast(count_big(*) as int(5)) "+bname+",aa.zzoperator operator from t_order aa,t_orderc bb where aa.zzno=bb.zzno and zzmainst='ZZ' and datepart(year,aa.zzorddate)="+alltrim(str(nyear))+" and datepart(month,aa.zzorddate)="+alltrim(str(nmonth))+" group by aa.zzorddate,aa.zzoperator order by aa.zzorddate,aa.zzoperator","cc")<0
       RETURN              
    ENDIF
    SELE cc
    INDEX ON operator tag operator
    sele kdb
    set relation to operator into cc
    replace all &bname with cc.&bname
    nArray1(nK)=alltrim(str(nyear))+"年"+alltrim(str(nmonth))+"月總數"
 nArray2(nK)=alltrim(str(nyear))+"年"+alltrim(str(nmonth))+"月間色數"
    nK=nK+1
ENDFOR
sele kdb
repl all zshj with &afieldlist,jshj with &bfieldlist
DIME nArray(2*nK+1)
nArray(1)="操作者"
FOR I=1 to nK-1
    nArray(2*I)=nArray1(I)
    nArray(2*I+1)=nArray2(I)
ENDFOR
nArray(2*I)="總數合計"
nArray(2*I+1)="間色數合計"
sele operator,&nlist from kdb into cursor Temp1 group by operator
AFIELD(nStruArray)
CREAT CURSOR TempInvent FROM ARRAY nStruArray
SELE TempInvent
APPEND FROM DBF('Temp1')
sele "合計             " operator,&nlist from kdb into cursor temp2
scatter to bArray
insert into TempInvent from array bArray
SELE TempInvent
THISFORMSET.FORM4.GRID1.RECORDSOURCE='TempInvent'
THISFORMSET.FORM4.GRID1.COLUMNCOUNT=FCOUN('TempInvent')
FOR I=1 TO FCOUN('TempInvent')
    SELE TempInvent
    nColumnName='Column'+ALLT(STR(I))
    THISFORMSET.FORM4.GRID1.&nColumnName..CONTROLSOURCE=FIELD(I)
    THISFORMSET.FORM4.GRID1.&nColumnName..HEADER1.CAPTION=nArray(I)
    THISFORMSET.FORM4.GRID1.&nColumnName..WIDTH=MAX(LEN(FIELD(I)),FSIZE(FIELD(I)))*7
    IF THISFORMSET.FORM4.GRID1.&nColumnName..WIDTH>240
       THISFORMSET.FORM4.GRID1.&nColumnName..WIDTH=240
    ENDIF
    THISFORMSET.FORM4.GRID1.&nColumnName..HEADER1.ALIGNMENT=2
ENDFOR
SELE TempInvent
GO TOP
WAIT CLEA
THISFORMSET.FORM4.GRID1.SCROLLBARS=3
THISFORMSET.FORM4.CAPTION=THIS.CAPTION
THISFORMSET.FORM1.VISIBLE=.F.
THISFORMSET.FORM4.VISIBLE=.T.