unit uBookClassSetting;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ComCtrls, StdCtrls, ExtCtrls, dbtables, Buttons;

type
  TfrmBookClassSetting = class(TForm)
    Panel4: TPanel;
    PageControl1: TPageControl;
    TabSheet2: TTabSheet;
    GroupBox2: TGroupBox;
    btnOK: TButton;
    GroupBox8: TGroupBox;
    edtClass: TEdit;
    edtClassName: TEdit;
    StaticText3: TStaticText;
    StaticText2: TStaticText;
    GroupBox7: TGroupBox;
    lstClassDetail: TListView;
    btnDelete: TButton;
    TabSheet4: TTabSheet;
    GroupBox4: TGroupBox;
    ListView3: TListView;
    GroupBox6: TGroupBox;
    StaticText5: TStaticText;
    StaticText6: TStaticText;
    StaticText7: TStaticText;
    StaticText8: TStaticText;
    Edit3: TEdit;
    Edit4: TEdit;
    Edit5: TEdit;
    Edit6: TEdit;
    btnCancel: TSpeedButton;
    Label1: TLabel;
    chkNewClass: TCheckBox;
    procedure lstClassDetailClick(Sender: TObject);
    procedure btnOKClick(Sender: TObject);
    procedure btnDeleteClick(Sender: TObject);
    procedure lstClassDetailChange(Sender: TObject; Item: TListItem;
      Change: TItemChange);
    procedure FormCreate(Sender: TObject);
    procedure btnCancelClick(Sender: TObject);
    procedure chkNewClassClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure  EditInit;
    procedure InsClass(ClassID , ClassName : string);
    procedure DelClass(ClassID : string);
    procedure UpDateClass(ClassID,ClassName : string);
    function CheckExistClass(ClassID : string):boolean;
    function GetClassName(ClassID : string):string;
    procedure CountInit;
    function GetIsClassBookCount(ClassID: string):integer;
    procedure ViewClass;
    function GetBookCount:integer;
    function GetCurrBookCount:integer;
    function GetLendedUserNum:integer;
    function GetTimeOutBookCount:integer;
    procedure ViewBook; 
  end;

var
  frmBookClassSetting: TfrmBookClassSetting;

implementation

uses main, uPublic;

{$R *.DFM}

procedure TfrmBookClassSetting.lstClassDetailClick(Sender: TObject);
begin
  if lstClassDetail.Selected <> nil then
  begin
    edtClass.Text := lstClassDetail.Selected.Caption;
    edtClassName.Text := lstClassDetail.Selected.SubItems.Strings[0];
  end;
end;

//書籍類別列表的初始化
procedure TfrmBookClassSetting.EditInit;
var
  Query : TQuery;
begin
  Query := TQuery.Create(nil);
  Query.DatabaseName := DBName;
  Query.SQL.Add('select * from Class');
  Query.Open;
  lstClassDetail.Items.Clear;
  while not Query.Eof do
  begin
    with lstClassDetail.Items.Add do
    begin
      Caption := Query.FieldByName('ClassID').AsString;
      SubItems.Add(Query.FieldByName('ClassName').AsString);
    end;
    Query.Next;
  end;
  Query.Close;
  Query.Free;
end;

//檢查某類別號是否重復
function TfrmBookClassSetting.CheckExistClass(ClassID: string): boolean;
var
  Query : TQuery;
begin
  try
    Query := TQuery.Create(nil);
    Query.DatabaseName := DBName;
    Query.SQL.Add('select * from Class where ClassID =:ClassID');
    Query.ParamByName('ClassID').AsString := ClassID;
    Query.Open;
    if Query.RecordCount = 0 then
      Result := False
    else Result := True;
    Query.Free;
  except
    Result := False;
  end;
end;

//刪除某類別
procedure TfrmBookClassSetting.DelClass(ClassID: string);
var
  Query : TQuery;
begin
  Query := TQuery.Create(nil);
  Query.DatabaseName := DBName;
  Query.SQL.Add('delete from Class where ClassID =:ClassID');
  Query.ParamByName('ClassID').AsString := ClassID;
  Query.ExecSQL;
  Query.Free;
end;

//增加某類別
procedure TfrmBookClassSetting.InsClass(ClassID, ClassName: string);
var
  Query : TQuery;
begin
  Query := TQuery.Create(nil);
  Query.DatabaseName := DBName;
  Query.SQL.Add('insert into Class(ClassID,ClassName) values(:ClassID,:ClassName)');
  Query.ParamByName('ClassID').AsString := ClassID;
    Query.ParamByName('ClassName').AsString := ClassName;
  Query.ExecSQL;
  Query.Free;
end;

//增加類別操作
procedure TfrmBookClassSetting.btnOKClick(Sender: TObject);
var
  i : integer;
begin
  //新增類別
  if chkNewclass.Checked then
  begin
    if Length(edtClass.Text)<>4 then
    begin
      MessageDlg('類別代碼錯誤',mtError,[mbok],0);
      Exit;
    end;
    if CheckExistClass(edtClass.Text) then
    begin
      MessageDlg('類別代碼已經存在',mtError,[mbok],0);
      Exit;
    end;
    try
      InsClass(edtClass.Text,edtClassName.Text);
      with lstClassDetail.Items.Add do
      begin
        Caption := edtClass.Text;
        SubItems.Add(edtClassName.Text);
      end;
      MessageBox(0,'添加類別成功',PChar('添加'+edtClass.Text),MB_OK);

    except
      MessageDlg('添加類別出錯',mtError,[mbok],0);
    end;
  end;

  //修改類別
  if chkNewclass.Checked=false then
  begin
    if Length(edtClass.Text)<>4 then
    begin
      MessageDlg('類別代碼錯誤',mtError,[mbok],0);
      Exit;
    end;
    try
      UpdateClass(edtClass.Text,edtClassName.Text);
      for i := 0 to lstClassDetail.Items.Count -1do
      begin
        if lstClassDetail.Items[i].Caption = edtClass.Text then
          lstClassDetail.Items[i].SubItems.Strings[0] := edtClassName.Text;
      end;
      MessageBox(0,'修改類別成功',PChar('修改'+edtClass.Text),MB_OK);
    except
      MessageDlg('修改類別出錯',mtError,[mbok],0);
    end;
  end;
end;

//修改類別信息操作
procedure TfrmBookClassSetting.UpDateClass(ClassID,ClassName: string);
var
  Query : TQuery;
begin
  Query := TQuery.Create(nil);
  Query.DatabaseName := DBName;
  Query.SQL.Add('update Class set ClassName=:ClassName where ClassID =:ClassID');
  Query.ParamByName('ClassID').AsString := ClassID;
    Query.ParamByName('ClassName').AsString := ClassName;
  Query.ExecSQL;
  Query.Free;
end;

//刪除某類別操作
procedure TfrmBookClassSetting.btnDeleteClick(Sender: TObject);
begin
  if lstClassDetail.Selected <> nil then
  try
    DelClass(lstClassDetail.Selected.Caption);
    lstClassDetail.Items.Delete(lstClassDetail.Selected.Index);
  except
    MessageDlg('刪除類別<'+lstClassDetail.Selected.Caption+'>',mtError,[mbok],0);
  end
  else MessageDlg('沒有選擇類別',mtWarning,[mbok],0);
end;

//根據類別號找到類別名稱
function TfrmBookClassSetting.GetClassName(ClassID: string): string;
var
  Query : TQuery;
begin
  try
    Query := TQuery.Create(nil);
    Query.DatabaseName := DBName;
    Query.SQL.Add('select ClassName from Class where ClassID =:ClassID');
    Query.ParamByName('ClassID').AsString := ClassID;
    Query.Open;
    if Query.RecordCount <> 0 then
      Result := Query.FieldByName('ClassName').AsString
    else Result := '未知';
    Query.Free;
  except
    Result := '未知';
  end;
end;

procedure TfrmBookClassSetting.lstClassDetailChange(Sender: TObject; Item: TListItem;
  Change: TItemChange);
begin
  lstClassDetailClick(Sender);
end;

procedure TfrmBookClassSetting.CountInit;
begin
  try
    ViewClass;
  except
    ListView3.Items.Clear;
  end;
  ViewBook;
end;

//統計某類別的書籍數目
function TfrmBookClassSetting.GetIsClassBookCount(ClassID: string): integer;
var
  Query : TQuery;
begin
  try
    Query := TQuery.Create(nil);
    Query.DatabaseName := DBName;
    Query.SQL.Add('select Count(Code) from Book where Class =:ClassID');
    Query.ParamByName('ClassID').AsString := ClassID;
    Query.Open;
    Result := Query.Fields[0].Value;
    Query.Free;
  except
    Result := -1;
  end;
end;

procedure TfrmBookClassSetting.ViewClass;
var
  Query : TQuery;
begin
  Query := TQuery.Create(nil);
  Query.DatabaseName := DBName;
  Query.SQL.Add('select * from Class');
  Query.Open;
  ListView3.Items.Clear;
  while not Query.Eof do
  begin
    with ListView3.Items.Add do
    begin
      Caption := Query.FieldByName('ClassID').AsString;
      SubItems.Add(Query.FieldByName('ClassName').AsString);
      SubItems.Add(IntToStr(GetIsClassBookCount(Caption)));
    end;
    Query.Next;
  end;
  Query.Free;
end;

//統計所有的書籍數目
function TfrmBookClassSetting.GetBookCount: integer;
var
  Query : TQuery;
begin
  try
    Query := TQuery.Create(nil);
    Query.DatabaseName := DBName;
    Query.SQL.Add('select Count(Code) from Book');
    Query.Open;
    Result := Query.Fields[0].Value;
    Query.Free;
  except
    Result := -1;
  end;
end;

//當前還有多少書籍在庫
function TfrmBookClassSetting.GetCurrBookCount: integer;
begin
  Result := GetBookCount - GetLendedUserNum;
end;

function TfrmBookClassSetting.GetLendedUserNum: integer;
var
  Query : TQuery;
begin
  try
    Query := TQuery.Create(nil);
    Query.DatabaseName := DBName;
    Query.SQL.Add('select Count(Code) from Owner');
    Query.Open;
    Result := Query.Fields[0].Value;
    Query.Free;
  except
    Result := -1;
  end;
end;

//統計過期不還的書籍數目
function TfrmBookClassSetting.GetTimeOutBookCount: integer;
var
  Query : TQuery;
begin
  try
    Query := TQuery.Create(nil);
    Query.DatabaseName := DBName;
    Query.SQL.Add('select Count(distinct(Code)) from Owner where LandDate <:LandDate');
    Query.ParamByName('LandDate').AsDateTime := Date-OutDay;
    Query.Open;
    Result := Query.Fields[0].Value;
    Query.Free;
  except
    Result := -1;
  end;
end;

//統計書籍的一些相關情況
procedure TfrmBookClassSetting.ViewBook;
begin
  Edit3.Text := '    '+ IntToStr(GetBookCount);
  Edit4.Text := '    '+IntToStr(GetCurrBookCount);
  Edit5.Text := '    '+IntToStr(GetLendedUserNum);
  Edit6.Text := '    '+IntToStr(GetTimeOutBookCount);
end;

procedure TfrmBookClassSetting.FormCreate(Sender: TObject);
begin
  try
    EditInit;
  except
    lstClassDetail.Items.Clear;
  end;
  CountInit;
end;

procedure TfrmBookClassSetting.btnCancelClick(Sender: TObject);
begin
    close;
end;

//選擇增加或是對原有信息的修改
procedure TfrmBookClassSetting.chkNewClassClick(Sender: TObject);
begin
    if chkNewclass.Checked = true then
    begin
        edtClass.Enabled := true;
        edtClass.Text :='';
        edtClassName.Text := '';
    end;
    if chkNewclass.Checked = false then
    begin
        edtClass.Enabled := false;
        edtClass.Text :='';
        edtClassName.Text := '';
    end;
end;

end.