scgrkb.rar
(46.15 KB)
2020-03-20 09:25
2020-03-20 22:12
2020-03-21 08:09
2020-03-22 14:42
2020-03-22 22:02
2020-03-22 22:49
2020-03-23 00:28
2020-03-23 16:09
程序代码：
LOCAL strName as String ,strTabName as String
strName = '陈羊成' && 教师姓名,当然,你也可能稍作修改,把这个教师姓名改为参数,便于循环调用本模块.
strTabName = strName + "课时分布表" && 最终输出表的名称,默认为:教师姓名 + 课时分布表,你也可以修改
* 运行以下语句前,假定已经打开了三个基本表:jsmd,jsrkb,rkzb201803,
* 如果未打开,自己添加一下打开的语句.
IF uGetKmbyXm (strName,'tmpTab1') THEN
= uTabZh ('tmpTab1','tmpTab2')
IF uGetksxx('tmpTab2') THEN
= uMakeKSB('tmpTab2',strTabName)
USE IN tmpTab1
USE IN tmpTab2
* 到此时,应该成功生成“XXX课时分布表”了
* ...把这个表填充入Word的后续动作...由你自己补充
ELSE
MESSAGEBOX('课时安排有冲突，请检查',16,'程序将返回')
ENDIF
ELSE
MESSAGEBOX('教师姓名=' + strName + ' 有误？，请检查',16,'程序将返回')
ENDIF
*======================*======================*======================*
FUNCTION uGetKmbyXm(paraJsxm as String,cTab as String) as Boolean
* 提取某教师的任课信息,多余信息清理掉
* 参数:教师姓名 ,临时表别名
LOCAL cFn as String
LOCAL iCount as Integer ,ii as Integer
LOCAL lDele as Boolean
SELECT jsmd
LOCATE FOR jsmd.xm = paraJsxm
IF FOUND()
IF USED(cTab) THEN
USE IN (cTab)
ENDIF
IF USED('rkb') THEN
USE IN rkb
ENDIF
SELECT jsrkb.* FROM jsrkb INTO CURSOR rkb READWRITE
SELECT rkb
iCount = FCOUNT('rkb')
FOR ii= iCount TO 3 STEP -1
cFn = fields(ii,'rkb')
SELECT nj,bj,(cFn) as km,COUNT(*) as ts FROM rkb WHERE (&cFn)=paraJsxm GROUP BY 1,2,3 INTO CURSOR tmpkm
IF RECCOUNT('tmpkm')=0 THEN
ALTER table rkb drop COLUMN (cFn)
ENDIF
SELECT rkb
USE IN tmpkm
ENDFOR
SELECT rkb
SCAN
lDele =.t.
FOR ii=3 TO FCOUNT('rkb')
cFn = fields(ii)
IF &cFn = paraJsxm THEN
lDele =.f.
ELSE
REPLACE &cFn WITH ''
ENDIF
ENDFOR
IF lDele =.t. THEN
REPLACE rkb.nj WITH 'xxx' ,rkb.bj WITH 'xxx'
ENDIF
ENDSCAN
SELECT * FROM rkb INTO CURSOR (cTab) WHERE rkb.nj<>'xxx' AND rkb.bj<>'xxx'
USE IN rkb
RETURN .t.
ELSE
RETURN .f.
ENDIF
ENDFUNC
PROCEDURE uTabZh(cTab as String ,cTabOut as String )
* 把某表横转竖. 最终获取某教师课时列表
* 参数1,要转换的表的别名.参数2,欲生成的表别名.
LOCAL xm as String ,km as String
LOCAL ii as Integer
IF cTabOut=" " THEN
cTabOut = cTab + "_lst"
ENDIF
IF USED(cTabOut) THEN
USE IN (cTabOut)
ENDIF
CREATE CURSOR (cTabOut) (nj c(10) ,bj c(10) ,jsxm c(10) ,kmmc c(10), ;
周1第几节 i ,周2第几节 i ,周3第几节 i ,周4第几节 i ,周5第几节 i )
SELECT (cTab)
SCAN
FOR ii=3 TO FCOUNT(cTab)
km = FIELD(ii)
xm = &km
m.nj1 = &cTab..nj
m.bj1 = &cTab..bj
IF xm<>" " THEN
INSERT INTO (cTabOut) (nj ,bj,jsxm,kmmc) VALUES (m.nj1,m.bj1,xm,km)
ENDIF
ENDFOR
ENDSCAN
ENDPROC
FUNCTION uGetksxx(cTab as String ) as Boolean
* 在取得教师本周课时列表的基础上,填充其具体的课时信息.
LOCAL ii as Integer
LOCAL cFn as String ,cFnItem as String ,qstr as String ,iday as String
LOCAL iks as Integer
IF USED('tmpzks') THEN
USE IN tmpzks
ENDIF
SELECT rkzb201803.* FROM rkzb201803 ;
inner JOIN (select &cTab..nj ,&cTab..bj FROM (cTab) DISTINCT ) as tmp11 ;
ON rkzb201803.nj = tmp11.nj AND rkzb201803.bj = tmp11.bj ;
INTO CURSOR tmpzks
SELECT tmpzks
SCAN
m.nj1 = nj
m.bj1 = bj
FOR ii=3 TO FCOUNT('tmpzks')
cFn = FIELD(ii,'tmpzks')
cFnItem = &cFn
iDay = SUBSTR(cFn,5,1)
iks = INT(VAL(SUBSTR(cFn,6,1)))
qstr = "Update " + cTab + " set " + cTab + ".周" + iday + "第几节 = " + ;
LTRIM(STR(iks)) + " Where " + cTab + ".nj = '" + m.nj1 + "' AND " + ;
cTab + ".bj = '" + m.bj1 + "' AND " + cTab + ".kmmc = '" + cFnItem + "'"
&qstr
SELECT tmpzks
ENDFOR
ENDSCAN
USE IN tmpzks
* 填充完毕,最好再检查一下有无冲突?比如同一天的节次现出两个,则不对.
FOR ii=1 TO 5
qstr="SELECT " + cTab + ".jsxm," + cTab + ".周" + LTRIM(STR(ii)) + "第几节 as kkc,COUNT(*) as ts FROM " + cTab + ;
" Where " + cTab + ".周" + LTRIM(STR(ii)) + "第几节>0 GROUP BY jsxm,周" + LTRIM(STR(ii)) + ;
"第几节 HAVING ts>1 INTO CURSOR tmp33 "
&qstr
IF RECCOUNT('tmp33')>0 THEN
MESSAGEBOX("教师：" +RTRIM(tmp33.jsxm) + "，周" + LTRIM(STR(ii)) + ;
"第" + LTRIM(STR(tmp33.kkc)) + "节课被重复安排了" + LTRIM(STR(tmp33.ts)) + "次？请检查！" ,;
16,"程序检测到课时冲突")
RETURN .f.
EXIT for
ELSE
USE IN tmp33
ENDIF
ENDFOR
RETURN .t.
ENDFUNC
PROCEDURE uMakeKSB(cTab as String ,cTabOut as String)
* 根据参数1(别名) 生成正式的课时表(参数2)
LOCAL II AS Integer ,ijc as Integer
LOCAL strItem as String ,cfn as String,xm as String ,qstr as String
IF USED(cTabOut) THEN
USE IN (cTabOut)
ENDIF
CREATE CURSOR (cTabOut) (教师姓名 c(20) ,午别 c(4) ,节次 i ,星期1 c(20),星期2 c(20),星期3 c(20),星期4 c(20),星期5 c(20))
FOR ii = 1 TO 6 && 学校一天有N节课,这里就循环N次
INSERT INTO (cTabOut) (午别 ,节次 ) VALUES (IIF(ii<=3,'上午','下午') , ii)
ENDFOR
SELECT (cTab)
GO TOP
SCAN
FOR ii=1 TO 5
cfn = "周" + LTRIM(STR(ii)) + "第几节"
ijc = &cfn
IF ijc<>0 THEN
xm = RTRIM(jsxm )
strItem = RTRIM(nj) + RTRIM(bj) + "," + RTRIM(kmmc)
qstr="UPDATE " + cTabOut + " SET 星期" + LTRIM(STR(ii)) + "='" + STRitem + ;
"', 教师姓名='" + xm + "' Where 节次=" + LTRIM(STR(ijc))
&qstr
ENDIF
ENDFOR
ENDSCAN
ENDPROC [此贴子已经被作者于2020-3-23 17:19编辑过]
2020-03-23 16:13