tcourse2unit.pas

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

unit TCourse2Unit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Buttons, ExtCtrls,ComObj, DB, ADODB, Grids, DBGrids;

type
  TTCourse2Form = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    BitBtn6: TBitBtn;
    BitBtn7: TBitBtn;
    Panel3: TPanel;
    Panel4: TPanel;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Label1: TLabel;
    edttc2no: TEdit;
    Label2: TLabel;
    edttweekcount: TEdit;
    Label3: TLabel;
    edttname: TEdit;
    Label4: TLabel;
    edtadress: TEdit;
    Label5: TLabel;
    cbxtweek: TComboBox;
    ADOQuery1: TADOQuery;
    Edit1: TEdit;
    Label6: TLabel;
    Edit2: TEdit;
    Label7: TLabel;
    ComboBox1: TComboBox;
    Label8: TLabel;
    btnReport: TButton;
    Button1: TButton;
    btnFind: TBitBtn;
    cbxtcscode: TComboBox;
    Label12: TLabel;
    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure FormCreate(Sender: TObject);
    procedure ADOQuery1AfterScroll(DataSet: TDataSet);
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnFindClick(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure btnReportClick(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
    procedure cbxtcscodeChange(Sender: TObject);
  private
    procedure GridMouseWheel(Sender: TObject; Shift: TShiftState;
    WheelDelta: Integer; MousePos: TPoint; var Handled: Boolean);
    function GetUno: string;

    procedure ControlTheEnablePro(isSaved: Boolean);//控制按钮面板的Enable属性
//***********************************************************
    procedure CopyDbDataToExcel(Args: array of const); // DbDataToExcel
    { Private declarations }
  public
    { Public declarations }
  end;
    TControlAccess = class(TControl);
var
  TCourse2Form: TTCourse2Form;
  flag:integer;
implementation

uses DataModul, public_unit, LessonQRUnit;

{$R *.dfm}

procedure TTCourse2Form.DBGrid1DrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn;
  State: TGridDrawState);
begin
 if gdSelected in State then Exit;  //隔行改变网格背景色：
  if adoquery1.RecNo mod 2 = 0 then
    (Sender as TDBGrid).Canvas.Brush.Color := clinfobk //定义背景颜色
  else
    (Sender as TDBGrid).Canvas.Brush.Color := RGB(191, 255, 223);  //定义背景颜色

  //定义网格线的颜色：
  DBGrid1.DefaultDrawColumnCell(Rect,DataCol,Column,State);
  with (Sender as TDBGrid).Canvas do //画 cell 的边框
  begin
    Pen.Color := $00ff0000; //定义画笔颜色(蓝色)
    MoveTo(Rect.Left, Rect.Bottom); //画笔定位
    LineTo(Rect.Right, Rect.Bottom); //画蓝色的横线
    Pen.Color := clbtnface; //定义画笔颜色(兰色)
    MoveTo(Rect.Right, Rect.Top); //画笔定位
    LineTo(Rect.Right, Rect.Bottom); //画绿色
  end;
end;

procedure TTCourse2Form.GridMouseWheel(Sender: TObject; Shift: TShiftState;
  WheelDelta: Integer; MousePos: TPoint; var Handled: Boolean);
begin
  TDBGrid(Sender).DataSource.DataSet.MoveBy(-WheelDelta div WHEEL_DELTA);
  Handled := True;
end;

procedure TTCourse2Form.ADOQuery1AfterScroll(DataSet: TDataSet);
begin
  adoquery1.Open;
  with adoquery1 do
  begin
    edttc2no.Text := fieldByName('tcno').AsString;
    edttweekcount.Text := fieldByName('tweekcount').AsString;
    edttname.Text := fieldByName('tname').AsString;
    edtadress.Text := fieldByName('tadress').AsString;
    cbxtweek.Text := fieldByName('tweek').AsString;
    cbxtcscode.Text:=fieldByname('tno').AsString;
    edttname.Text:=fieldByname('tname').AsString;
  end;

end;




procedure TTCourse2Form.FormCreate(Sender: TObject);
var
 adoqry2:tadoquery;
begin
  adoqry2:=tadoquery.Create(self);
  with adoqry2 do
  begin
    Connection:=dm.ADOConnection1;
    sql.Text:='select * from tteacher' ;
    Open;
    while not eof do
    begin
      cbxtcscode.Items.Add(fieldByname('tno').AsString);
      next;
    end;
    close;
  end;

  TControlAccess(DBGrid1).OnMouseWheel := GridMouseWheel;
    ControlTheEnablePro(true);
end;

procedure TTCourse2Form.FormShow(Sender: TObject);
begin
  adoquery1.Open;
  with adoquery1 do
  begin
    edttc2no.Text := fieldByName('tcno').AsString;
    edttweekcount.Text := fieldByName('tweekcount').AsString;
    edttname.Text := fieldByName('tname').AsString;
    edtadress.Text := fieldByName('tadress').AsString;
    cbxtweek.ItemIndex := cbxtweek.Items.IndexOf(fieldbyname('tweek').AsString);
  end;
end;

procedure TTCourse2Form.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  adoquery1.Close;
  Action := caFree;
  TCourse2Form := nil;
end;

procedure TTCourse2Form.btnFindClick(Sender: TObject);
var
  sqlStr: string;
begin
  sqlStr :='select tcn.tcno,tcn.tweek,tcn.tweekcount,tcn.tno,tcn.tadress,tt.tno ,tt.tname '
           +'from TCoursen  tcn  inner join tteacher tt on tcn.tno = tt.tno WHERE 1=1';

  if edit1.Text <> '' then
    sqlStr := sqlstr+ ' AND tt.tname like ''%' + edit1.Text + '%''';

  if edit2.Text<> '' then
    sqlStr := sqlStr + ' AND tcn.tadress like ''%' + Edit2.Text + '%''';
  if combobox1.ItemIndex <> 0 then
    sqlStr := sqlStr + ' AND tcn.tweek=' + QuotedStr(combobox1.Text);

   with adoquery1 do
   begin
     Sql.Text := sqlStr;
     Open;
   end;
 edit1.Text:='';
 edit2.Text:='';
 combobox1.ItemIndex:=0;


end;

procedure TTCourse2Form.BitBtn2Click(Sender: TObject);
begin
  clearText(self);
  edttc2no.Text:= GetUno;
  ControlTheEnablePro(false); 
  edttweekcount.SetFocus;
  cbxtweek.ItemIndex := 0;
  flag:=1;
end;

function TTCourse2Form.GetUno: string;
var
  adoquery:Tadoquery;
  No: string;
  t: integer;
begin
  adoquery:=Tadoquery.Create(self);
  with adoquery do
  begin
    Connection:=dm.ADOConnection1;
    sql.Text := 'SELECT MAX(tcno) as tcno FROM TCoursen';
    Open;
    No := fieldbyName('tcno').AsString;
  end;
  t := StrToInt(No);
  t := t  + 1;
  No := IntToStr(t);
  Result := No;
  adoquery.Free;
end;

procedure TTCourse2Form.ControlTheEnablePro(isSaved: Boolean);
begin
  if isSaved = true then
  begin
    panel4.Enabled:=true;       Panel3.Enabled:=false;

    BitBtn2.Enabled:=True;      BitBtn3.Enabled:=true;
    BitBtn4.Enabled:=true;      BitBtn7.Enabled:=true;
    BitBtn5.Enabled:=false;     BitBtn6.Enabled:=false;

    edttc2no.Enabled:=false;       edttweekcount.Enabled:=false;
    edttname.Enabled:=false;       edtadress.Enabled:=false;
    cbxtweek.Enabled:=false;      cbxtcscode.Enabled:=false;

//********************************************************
  end
  else
  begin
    panel4.Enabled:=false;       Panel3.Enabled:=true;

    BitBtn2.Enabled:=false;      BitBtn3.Enabled:=false;
    BitBtn4.Enabled:=false;      BitBtn7.Enabled:=false;
    BitBtn5.Enabled:=true;       BitBtn6.Enabled:=true;

    //edttc2no.Enabled:=true;
    edttweekcount.Enabled:=true;
    //edttname.Enabled:=true;
    edtadress.Enabled:=true;
    cbxtweek.Enabled:=true;       cbxtcscode.Enabled:=true;
//********************************************************
  end;
end;

procedure TTCourse2Form.CopyDbDataToExcel(Args: array of const);
var
  iCount, jCount: Integer;
  XLApp: Variant;
  Sheet: Variant;
  I: Integer;
  qzw1:   Variant;
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 :=TDBGrid(Args[I].VObject).Name;
  //  Sheet := XLApp.Workbooks[1].WorkSheets[TDBGrid(Args[I].VObject).Name];

      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[2, iCount + 1] :=TDBGrid(Args[I].VObject).Columns.Items[iCount].Title.Caption;
    jCount := 2;
    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','e1'];
      qzw1.Merge;
      // 更改 Excel 标题栏：
      XLApp.Caption := '课程表';
      //设置指定列的名称，以第一列为例：
      XLApp.Cells[1,1].Value := '            课程表';
      //设置第一行字体属性：
      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 := 17;
      //设置指定行的高度（单位：磅）（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 TTCourse2Form.Button1Click(Sender: TObject);
begin
  CopyDbDataToExcel([DBGrid1]);
end;

procedure TTCourse2Form.btnReportClick(Sender: TObject);
begin
   LessonQRForm.QuickRep1.Preview;
end;

procedure TTCourse2Form.BitBtn5Click(Sender: TObject);
begin
  with adoquery1 do
  begin
    edttc2no.Text := fieldByName('tcno').AsString;
    edttweekcount.Text := fieldByName('tweekcount').AsString;
    edttname.Text := fieldByname('tname').AsString;
    edtadress.Text := fieldByName('tadress').AsString;
    cbxtweek.ItemIndex := cbxtweek.Items.IndexOf(fieldbyname('tweek').AsString);
    ControlTheEnablePro(true);
  end;
end;

procedure TTCourse2Form.BitBtn4Click(Sender: TObject);
begin
if messagebox(handle,'您确定要修改吗？','提示',MB_YESNO + MB_ICONQUESTION)=ID_yes  then
  begin
    flag:=2;
    ControlTheEnablePro(false);
  end;
end;
procedure TTCourse2Form.BitBtn3Click(Sender: TObject);
var
 adoqry:tadoquery;
begin
  if messagebox(handle,'您确定要删除吗？','提示',MB_YESNO + MB_ICONQUESTION)=ID_yes  then
  begin
   adoqry:=tadoquery.Create(self);
   with adoqry do
   begin
     connection:=dm.ADOConnection1;
     sql.Text:='delete from TCoursen where tcno='+quotedstr(edttc2no.Text);
     ExecSQL;
   end;
   adoqry.Free;
  end;
  adoquery1.Close;
  adoquery1.Open;
  ControlTheEnablePro(true);  
end;
procedure TTCourse2Form.BitBtn6Click(Sender: TObject);
var
  sqlStr,uno: string;
begin
    if edttweekcount.Text='' then
    begin
      application.MessageBox('时间不能为空','提示消息', mb_iconInformation+mb_ok);
      edttweekcount.SetFocus;
    end
  else
    if cbxtweek.Text = '请选择' then
    begin
      application.MessageBox('星期不能为空','提示消息', mb_iconInformation+mb_ok);
      cbxtweek.SetFocus;
    end
  else
    if edtadress.Text = '' then
    begin
      application.MessageBox('地点不能为空','提示消息', mb_iconInformation+mb_ok);
      edtadress.SetFocus;
    end
  else
    begin  
    // 数据库添加修改操作
//********************************************************************************
      if flag=1 then    // flag=1表示此时是增加状态
            begin
                  adoquery1.Close;
                  uno := GetUno; // 自动获取用户编号
                  sqlStr :=  'INSERT TCoursen(tcno,tweekcount, tno,tadress,tweek)'
                              + 'Values('
                              + QuotedStr(uno)+','
                              + QuotedStr(edttweekcount.Text)+','
                              + QuotedStr(cbxtcscode.Text)+','
                              + QuotedStr(edtadress.Text)+','
                              + QuotedStr(cbxtweek.Text)
                              +')';
                 ToExecSQL(sqlStr, DM.ADOConnection1);  //插入记录到用户表
                 showmessage('添加成功!');
            end;
     if flag=2 then // flag=2表示此时是修改状态
           begin
                 sqlStr := 'UPDATE TCoursen SET tcno=' + QuotedStr(edttc2no.Text) +','
                              + 'tweekcount=' + QuotedStr(edttweekcount.Text) + ','
                              + 'tno=' + QuotedStr(cbxtcscode.Text)  +','
                              + 'tadress=' + QuotedStr(edtadress.Text) + ','
                              + 'tweek=' + QuotedStr(cbxtweek.Text)
                              +' WHERE tcno=' + QuotedStr(adoquery1.fieldbyname('tcno').AsString);
                ToExecSQL(sqlStr, DM.ADOConnection1);
                showmessage('修改成功!');
          end;
//********************************************************************************
      adoquery1.Close;
      adoquery1.Open;
      ControlTheEnablePro(true);
      flag := 0;
    end;
end;

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

procedure TTCourse2Form.cbxtcscodeChange(Sender: TObject);
var
  adoqry:Tadoquery;
begin
  adoqry:=Tadoquery.Create(self);
  with adoqry do
  begin
    Connection:=dm.ADOConnection1;
    sql.Text:='select * from TTeacher where tno = '+quotedstr(cbxtcscode.Text);
    Open;
      edttname.Text:=fieldByname('tname').AsString;
  end;
  adoqry.Free;
end;

end.