tcourseunit.pas

DELPHI计算机科学系教务管理系统,带论文设计,不错的参考

begin
  if messagebox(handle,'您确定要修改吗？','提示',MB_YESNO + MB_ICONQUESTION)=ID_yes  then
  begin
    ControlTheEnablePro(false);
    edttcno.Enabled:=false;
    edttcname.Enabled:=false;
    cbxtcscode.SetFocus;
  end;
  flag:=2;
end;

procedure TTCourseForm.BitBtn5Click(Sender: TObject);
begin
  ControlTheEnablePro(true);
end;

procedure TTCourseForm.BitBtn6Click(Sender: TObject);
var
  sqlStr,uno: string;
begin
  if cbxtcscode.Text='请选择' then
    showmessage('请选择教师编号!')
  else
  if cbxtclessonname.Text='请选择' then
    showmessage('请选择课程编号!')
  else
  if cbxtcsc.Text='请选择' then
    cbxtcsc.Text:='';
  if flag=1 then           // flag=1表示此时是增加状态
       if edttclessoncount.Text='' then
       begin
         showmessage('课时不能为空!');
       end
       else         
         if cbtcout.Checked then
            begin
                  adoquery1.Close;
                  uno := GetUno; // 自动获取用户编号
                  sqlStr :=  'INSERT TCourse(tcno,tcclass, tclessoncount,tccoefficient,tcclessons,tcsum,tcreduce,tcworksum,tcmemo,tctime,tcscode,tscode,tcsc)'
                              + 'Values('
                              + QuotedStr(uno)+','
                              + QuotedStr(edttcclass.Text)+','
                              + QuotedStr(edttclessoncount.Text)+','
                              + QuotedStr(edttccoefficient.Text)+','
                              + QuotedStr(edttcclessons.Text)+','
                              + QuotedStr(edttcsum.Text)+','
                              + QuotedStr(edttcreduce.Text)+','
                              + QuotedStr(edttcworksum.Text)+','
                              + QuotedStr(edttcmemo.Text)+','
                              + QuotedStr(datetostr(dtp.Date))+','
                              + QuotedStr(cbxtcscode.Text)+','
                              //+ QuotedStr(edttcdepartment.Text)+','
                              + QuotedStr(cbxtclessonname.Text)+','
                              + QuotedStr(cbxtcsc.Text)
                              +')';
                 ToExecSQL(sqlStr, DM.ADOConnection1); 
                 showmessage('添加成功!');
                        adoquery1.Close;
      adoquery1.Open;
      ControlTheEnablePro(true);
      flag := 0;
            end
            else
            begin
                  adoquery1.Close;
                  uno := GetUno; // 自动获取用户编号
                  sqlStr :=  'INSERT TCourse(tcno,tcclass, tclessoncount,tccoefficient,tcclessons,tcsum,tcreduce,tcworksum,tcmemo,tctime,tcscode,tscode,tcsc)'
                              + 'Values('
                              + QuotedStr(uno)+','
                              + QuotedStr(edttcclass.Text)+','
                              + QuotedStr(edttclessoncount.Text)+','
                              + QuotedStr(edttccoefficient.Text)+','
                              + QuotedStr(edttcclessons.Text)+','
                              + QuotedStr(edttcsum.Text)+','
                              + QuotedStr(edttcreduce.Text)+','
                              + QuotedStr(edttcworksum.Text)+','
                              + QuotedStr(edttcmemo.Text)+','
                              + QuotedStr(datetostr(dtp.Date))+','
                              + QuotedStr(cbxtcscode.Text)+','
                              //+ QuotedStr(edttcdepartment.Text)+','
                              + QuotedStr(cbxtclessonname.Text)+','
                              + QuotedStr(cbxtcsc.Text)
                              +')';
                 ToExecSQL(sqlStr, DM.ADOConnection1);
                 showmessage('添加成功!');
       adoquery1.Close;
      adoquery1.Open;
      ControlTheEnablePro(true);
      flag := 0;
         end;
     if flag=2 then // flag=2表示此时是修改状态
         if cbtcout.Checked then
           begin
                 sqlStr := 'UPDATE TCourse SET tcno=' + QuotedStr(edttcno.Text) +','
                              + 'tcclass=' + QuotedStr(edttcclass.Text)  +','
                              + 'tclessoncount=' + QuotedStr(edttclessoncount.Text)+ ','
                              + 'tccoefficient=' + QuotedStr(edttccoefficient.Text) + ','
                              + 'tcclessons=' + QuotedStr(edttcclessons.Text)  +','
                              + 'tcsum=' + QuotedStr(edttcsum.Text) + ','
                              + 'tcreduce=' + QuotedStr(edttcreduce.Text) + ','
                              + 'tcworksum=' + QuotedStr(edttcworksum.Text) + ','
                              + 'tcmemo=' + QuotedStr(edttcmemo.Text)  +','
                              + 'tctime=' + QuotedStr(datetostr(dtp.Date)) + ','
                              + 'tcscode=' + QuotedStr(cbxtcscode.Text) + ','
                              //+ 'tcdepartment=' + QuotedStr(edttcdepartment.Text)+ ','
                              + 'tscode=' + QuotedStr(cbxtclessonname.Text) + ','
                              + 'tcsc=' + QuotedStr(cbxtcsc.Text)
                              +' WHERE tcno=' + QuotedStr(adoquery1.fieldbyname('tcno').AsString);
                ToExecSQL(sqlStr, DM.ADOConnection1);
                showmessage('修改成功!');
       adoquery1.Close;
      adoquery1.Open;
      ControlTheEnablePro(true);
      flag := 0;
          end
          else
           begin
                 sqlStr := 'UPDATE TCourse SET tcno=' + QuotedStr(edttcno.Text) +','
                              + 'tcclass=' + QuotedStr(edttcclass.Text)  +','
                              + 'tclessoncount=' + QuotedStr(edttclessoncount.Text)+ ','
                              + 'tccoefficient=' + QuotedStr(edttccoefficient.Text) + ','
                              + 'tcclessons=' + QuotedStr(edttcclessons.Text)  +','
                              + 'tcsum=' + QuotedStr(edttcsum.Text) + ','
                              + 'tcreduce=' + QuotedStr(edttcreduce.Text) + ','
                              + 'tcworksum=' + QuotedStr(edttcworksum.Text) + ','
                              + 'tcmemo=' + QuotedStr(edttcmemo.Text)  +','
                              + 'tctime=' + QuotedStr(datetostr(dtp.Date)) + ','
                              + 'tcscode=' + QuotedStr(cbxtcscode.Text) + ','
                              //+ 'tcdepartment=' + QuotedStr(edttcdepartment.Text)+ ','
                              + 'tscode=' + QuotedStr(cbxtclessonname.Text) + ','
                              + 'tcsc=' + QuotedStr(cbxtcsc.Text)
                              +' WHERE tcno=' + QuotedStr(adoquery1.fieldbyname('tcno').AsString);
                ToExecSQL(sqlStr, DM.ADOConnection1);
                showmessage('修改成功!');
       adoquery1.Close;
      adoquery1.Open;
      ControlTheEnablePro(true);
      flag := 0;
          end;
//********************************************************************************

end;

procedure TTCourseForm.BitBtn7Click(Sender: TObject);
begin
  close;
end;

procedure TTCourseForm.btnFindClick(Sender: TObject);
var
  sqlStr:string;
begin

  sqlStr :=' select tc.tcno,tc.tcscode,tt.tname,tt.tpost,tt.tcdepartment,'
            +'tc.tcclass,tc.tscode,tl.tscode,tl.tlesson,tc.tclessoncount,tc.tccoefficient,'
            +'tc.tcsc,tc.tcclessons,tc.tcsum,tc.tcreduce,tc.tcworksum,tc.tcmemo,'
            +'tt.tbool,tc.tctime from TCourse tc inner join TTeacher tt on '
             +'tc.tcscode = tt.tno  inner join tlesson tl on tc.tscode = tl.tscode     WHERE 1=1';

  if combobox1.ItemIndex <> 0 then
    sqlStr := sqlstr+'AND tt.tbool=' + QuotedStr(combobox1.Text);

  if edtyear.Text <> '' then
    sqlStr := sqlstr+ ' AND tc.tctime like ''%'+ edtyear.Text + '%''';
  if cbmonth.ItemIndex <> 0 then
    sqlStr := sqlstr+ ' AND tc.tctime like ''%' + cbmonth.Text +'_' +'_' +'_''';

  if combobox2.ItemIndex <> 0 then
    sqlStr := sqlStr + ' AND tc.tcsc=' + QuotedStr(combobox2.Text);

  if combobox3.ItemIndex <> 0 then
    sqlStr := sqlStr + ' AND tt.tpost=' + QuotedStr(combobox3.Text);

   with adoquery1 do
   begin
     Sql.Text := sqlStr;
     Open;
   end;
   edtyear.Text:='';
   combobox1.ItemIndex:=0;
   cbmonth.ItemIndex:=0;
   combobox2.ItemIndex:=0;
   combobox3.ItemIndex:=0;
end;

procedure TTCourseForm.Button1Click(Sender: TObject);
begin
     CopyDbDataToExcel([DBGrid1]);
end;

procedure TTCourseForm.CopyDbDataToExcel(Args: array of const);
var
  iCount, jCount: Integer;
  XLApp: Variant;
  Sheet: Variant;
  I: Integer;
  qzw1:   Variant;
  term,year:string;
  tmonth :integer;
  ttime:string;
begin
  Screen.Cursor := crHourGlass;
  if not VarIsEmpty(XLApp) then
  begin
    XLApp.DisplayAlerts := False;
    XLApp.Quit;
    VarClear(XLApp);
  end;

  try
    XLApp := CreateOleObject('Excel.Application');
  except
    Screen.Cursor := crDefault;
    Exit;
  end;

  XLApp.WorkBooks.Add;
  XLApp.SheetsInNewWorkbook := High(Args) + 1;

  for I := Low(Args) to High(Args) do
  begin
      XLApp.WorkBooks[1].WorkSheets[I+1].Name :='课酬表';
      Sheet := XLApp.Workbooks[1].WorkSheets['课酬表'];
    if not TDBGrid(Args[I].VObject).DataSource.DataSet.Active then
    begin
      Screen.Cursor := crDefault;
      Exit;
    end;
    TDBGrid(Args[I].VObject).DataSource.DataSet.first;
    for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 do
      Sheet.Cells[4, iCount + 1] :=TDBGrid(Args[I].VObject).Columns.Items[iCount].Title.Caption;
    jCount := 4;
    while not TDBGrid(Args[I].VObject).DataSource.DataSet.Eof do
    begin
      for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 do
        Sheet.Cells[jCount + 1, iCount + 1] := TDBGrid(Args[I].VObject).Columns.Items[iCount].Field.AsString;
      Inc(jCount);
      TDBGrid(Args[I].VObject).DataSource.DataSet.Next;

    end;

// 控制表头
 //*********************************************  
      //合并单元格 根据DBgrid的 记录数来 决定 单元格第二个列 值
      qzw1:=Sheet.Range['A1','q1'];
      qzw1.Merge;
      // 更改 Excel 标题栏：
      if adoquery1.FieldByName('tbool').AsString='是' then
        XLApp.Caption := ' 外聘教师课酬表'
      else
      if adoquery1.FieldByName('tbool').AsString='否' then
        XLApp.Caption := ' 课酬表';
      year:=copy(adoquery1.FieldByName('tctime').AsString,1,4);
      tmonth:=monthof(strtodate(adoquery1.FieldByName('tctime').AsString));
      if (1< tmonth) and ( tmonth <9 ) then
        begin
         if adoquery1.FieldByName('tbool').AsString='是' then
           begin
            term:= '第二学期';
            XLApp.Cells[1,1].Value := '       '+adoquery1.FieldByName('tcdepartment').AsString + ' 教师工作量登记表 '+inttostr(strtoint(year)-1)+' 至 '+year+' 学年'+term+' 外聘教师课酬表';
           end
         else
         if adoquery1.FieldByName('tbool').AsString='否' then
           begin
            term:= '第二学期';
            XLApp.Cells[1,1].Value := '       '+adoquery1.FieldByName('tcdepartment').AsString + ' 教师工作量登记表 '+inttostr(strtoint(year)-1)+' 至 '+year+' 学年'+term+' 课酬表';
           end;
        end
      else
        begin
         if adoquery1.FieldByName('tbool').AsString='是' then
          begin
            term:= '第一学期';
            XLApp.Cells[1,1].Value := '       '+adoquery1.FieldByName('tcdepartment').AsString + ' 教师工作量登记表 '+year+' 至 '+inttostr(strtoint(year)+1)+' 学年'+term+' 外聘教师课酬表';
          end
         else
         if adoquery1.FieldByName('tbool').AsString='否' then
          begin
            term:= '第一学期';
            XLApp.Cells[1,1].Value := '       '+adoquery1.FieldByName('tcdepartment').AsString + ' 教师工作量登记表 '+year+' 至 '+inttostr(strtoint(year)+1)+' 学年'+term+' 课酬表';
          end;
        end;
      ttime:=adoquery1.FieldByName('tctime').AsString;
      //设置指定列的名称，以第2列为例：

      XLApp.Cells[2,1].Value :='月份：';
      XLApp.Cells[2,2].Value := tmonth;
      XLApp.Cells[2,3].Value :='  月';
      XLApp.Cells[2,12].Value := '登记日期';
      XLApp.Cells[2,13].Value :=ttime;
      XLApp.ActiveSheet.Rows[2].Font.Bold := True;
      XLApp.ActiveSheet.Rows[4].Font.Bold := True;

      //设置第一行字体属性：
      XLApp.ActiveSheet.Rows[1].Font.Name := '隶书';
      XLApp.ActiveSheet.Rows[1].Font.Color := clblack;
      XLApp.ActiveSheet.Rows[1].Font.Bold := True;
      XLApp.ActiveSheet.Rows[1].Font.size := 20;
      //设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第1行为例：
      XLApp.ActiveSheet.Rows[1].RowHeight := 1/0.035; // 1厘米
      //设置指定列的宽度（单位：字符个数），以第一列为例：
      //XLApp.ActiveSheet.Columns[1].ColumnsWidth := 10;

 //***************************************************************
    XlApp.Visible := True;

   end;
  Screen.Cursor := crDefault;

end;

procedure TTCourseForm.btnReportClick(Sender: TObject);
begin
  TCourseQRForm.QuickRep1.Preview;
end;


procedure TTCourseForm.cbxtclessonnameChange(Sender: TObject);
var
  adoqry:Tadoquery;
begin
  adoqry:=Tadoquery.Create(self);
  with adoqry do
  begin
    Connection:=dm.ADOConnection1;
    sql.Text:='select * from TLesson where tscode = '+quotedstr(cbxtclessonname.Text);
    Open;
      edttclesson.Text:=fieldByname('tlesson').AsString;
  end;
  adoqry.Free;
end;

procedure TTCourseForm.edttcreduceKeyPress(Sender: TObject; var Key: Char);
begin
  if not (key in ['0'..'9'])then
  begin
  showmessage('请输入数字!');
  edttcreduce.Text:='';
  end;
end;

procedure TTCourseForm.edttclessoncountKeyPress(Sender: TObject;
  var Key: Char);
begin
  if not (key in ['0'..'9'])then
  begin
  showmessage('请输入数字!');
  edttclessoncount.Text:='';
  end;
end;

procedure TTCourseForm.edttccoefficientKeyPress(Sender: TObject;
  var Key: Char);
begin
  if not (key in ['0'..'9'])then
  begin
   showmessage('请输入数字!');
   edttccoefficient.Text:='';
  end;
end;

procedure TTCourseForm.edttcsumKeyPress(Sender: TObject; var Key: Char);
begin
  if not (key in ['0'..'9'])then
  begin
   showmessage('请输入数字!');
   edttcsum.Text:='';
  end;
end;

procedure TTCourseForm.edttcworksumKeyPress(Sender: TObject;
  var Key: Char);
begin
  if not (key in ['0'..'9'])then
  begin
   showmessage('请输入数字!');
   edttcworksum.Text:='';
  end;
end;

procedure TTCourseForm.edttcclessonsKeyPress(Sender: TObject;
  var Key: Char);
begin
  if not (key in ['0'..'9'])then
  begin
   showmessage('请输入数字!');
   edttcclessons.Text:='';
  end;
end;

end.