unit DatabaseOperator;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Grids, DBGrids, ADODB, DB, Buttons, ComCtrls, ExtCtrls, Mask,
  DBCtrls;

type
  TfrmDatabaseMgr = class(TForm)

    Conn: TADOConnection;
    Query: TADOQuery;
    DataSoures: TDataSource;
    panelMainMenu: TPanel;
    statusShowError: TStatusBar;
    Panel1: TPanel;
    lblALLCardCode: TLabel;
    editInputCardCode: TEdit;
    lalAllPassword: TLabel;
    editInputCardPass: TEdit;
    comboCardState: TComboBox;
    editOutDial: TEdit;
    lblOutDial: TLabel;
    lalCardState: TLabel;
    comboCardKind: TComboBox;
    lallCardKind: TLabel;
    lblRemark: TLabel;
    editRemark: TEdit;
    radiogrpShowCaller: TRadioGroup;
    radiogrpFeeCheck: TRadioGroup;
    Splitter1: TSplitter;
    Panel2: TPanel;
    btnQuery: TButton;
    btnAdd: TButton;
    btnModify: TButton;
    btnDelete: TButton;
    btnClear: TButton;
    btnExit: TButton;
    Panel3: TPanel;
    Splitter2: TSplitter;
    dbgridDateShow: TDBGrid;

    procedure btnQueryClick(Sender: TObject);
    procedure btnAddClick(Sender: TObject);
    procedure btnDeleteClick(Sender: TObject);
    procedure btnModifyClick(Sender: TObject);
    procedure btnExitClick(Sender: TObject);
    procedure DataSouresDataChange(Sender: TObject; Field: TField);

    procedure FormCreate(Sender: TObject);
    procedure btnClearClick(Sender: TObject);
  private
   { private declarations }
  public
    { Public declarations }
  end;

var
  frmDatabaseMgr: TfrmDatabaseMgr;
  strInputCardCode: String;
  SQLSelectTxt: String;

implementation

{$R *.DFM}

//查詢(xún)
procedure TfrmDatabaseMgr.btnQueryClick(Sender: TObject);
var
  SQLCardCode, SQLCardKind, SQLCardState, SQLCardStateKind: String;
begin

 SQLSelectTxt :=  'select ID as 卡類(lèi)型標(biāo)識(shí)號(hào), '
              + ' case KIND when 0 then ''記帳卡'' when 1 then ''智能IC卡'' when 2 then ''公司卡'' when 4 then ''酒店卡'' end as 卡種類(lèi), '
              + ' CARD_CODE as 卡接入號(hào),CARD_PASS as  卡管理員密碼, '
              + ' case CARD_STATE when 0 then ''未啟用'' when 1 then ''已開(kāi)通'' when 2 then ''已停機(jī)'' when 3 then ''因欠費(fèi)被停機(jī)'' when 4 then ''已注銷(xiāo)'' end as 卡狀態(tài), '
              + ' case FEE_CHECKED  when 0 then ''后付費(fèi)'' when 1 then ''預(yù)付費(fèi)'' end as 話(huà)費(fèi)檢測(cè)開(kāi)關(guān),'
              + ' case DISPLAY_CARD when 0 then ''顯示卡號(hào)'' when 1 then ''顯示主叫號(hào)碼'' end as  顯示接入號(hào)碼為主叫號(hào)碼的開(kāi)關(guān), '
              + ' HEAD_CHAR as 外撥默認(rèn)字頭, REMARK as 備注 from T_CARD_KIND ';


        //根據(jù)卡號(hào)查詢(xún)信息
        if editInputCardCode.Text <> '' then
        begin
          strInputCardCode := editInputCardCode.Text;
          SQLCardCode := ' WHERE CARD_CODE = ' + strInputCardCode + ' ';
          Query.Close;
          Query.SQL.Text := SQLSelectTxt + SQLCardCode;
          Query.Open;
        end

        //根據(jù)卡類(lèi)型查詢(xún)信息
        else  if (editInputCardCode.Text = '') and (comboCardKind.ItemIndex <> -1)
                                               and (comboCardState.ItemIndex = -1) then
        begin

          SQLCardKind := ' WHERE KIND = ' + Inttostr(comboCardKind.ItemIndex) + ' ';
          Query.Close;
          Query.SQL.Text := SQLSelectTxt +  SQLCardKind;
          Query.Open;
        end

        //根據(jù)卡狀態(tài)查詢(xún)信息
        else  if (editInputCardCode.Text = '') and (comboCardState.ItemIndex <> -1)
                                               and (comboCardKind.ItemIndex = -1) then
        begin

          SQLCardState := ' WHERE CARD_STATE = ' + Inttostr(comboCardState.ItemIndex) + '';
          Query.Close;
          Query.SQL.Text := SQLSelectTxt + SQLCardState;
          Query.Open;
        end

        //根據(jù)卡號(hào)密碼一起查詢(xún)
        else  if (editInputCardCode.Text = '') and (comboCardState.ItemIndex <> -1)
                                               and (comboCardKind.ItemIndex <> -1) then
        begin

          SQLCardStatekind := ' WHERE KIND = ' + Inttostr(comboCardKind.ItemIndex) + ' '
                           + ' and KIND = ' + Inttostr(comboCardKind.ItemIndex) + ' ';
          Query.Close;
          Query.SQL.Text := SQLSelectTxt + SQLCardStatekind;
          Query.Open;
        end

        //查詢(xún)所有卡信息
        else
        begin

          Query.Close;
          Query.SQL.Text := SQLSelectTxt;
          Query.Open;
        end;

     btnAdd.Enabled := true;
     btnClear.Enabled := true;
     btnModify.Enabled := true;
     btnDelete.Enabled := true;
end;

//新增
procedure TfrmDatabaseMgr.btnAddClick(Sender: TObject);
var
  SQLTxt: String;
begin
      if strInputCardCode = editInputCardCode.Text then
      begin
        ShowMessage('您輸入的卡號(hào)已經(jīng)存在^^');
        Exit;
      end;
      if editInputCardCode.Text = ''  then
      begin
        ShowMessage('輸入新增卡的卡號(hào)不能為空^^');
        Exit;
      end;

      if editInputCardPass.Text = ''  then
      begin
        ShowMessage('輸入新增卡的密碼不能為空^^');
        Exit;
      end;
      if (comboCardKind.ItemIndex = -1) or (comboCardState.ItemIndex = -1) then
      begin
        ShowMessage('請(qǐng)確認(rèn)卡類(lèi)型或者卡狀態(tài)輸入是否正確^^');
        Exit;
      end ;

      if (editInputCardCode.Text <> '') and (editInputCardPass.Text <> '')
                                        and (strInputCardCode <> editInputCardCode.Text) then
      begin

        SQLTxt := 'insert into T_CARD_KIND(CARD_CODE, CARD_PASS, KIND, FEE_CHECKED,'
                 + ' CARD_STATE, HEAD_CHAR, DISPLAY_CARD, REMARK)'
                 +'values(' + QuotedStr(editInputCardCode.Text) + ','
                 + QuotedStr(editInputCardPass.Text) + ','
                 + Inttostr(comboCardKind.ItemIndex) + ','
                 + Inttostr(radiogrpFeeCheck.ItemIndex) + ','
                 + Inttostr(comboCardState.ItemIndex) + ','
                 + QuotedStr(editOutDial.Text) + ','
                 + Inttostr(radiogrpShowCaller.ItemIndex) + ','
                 + QuotedStr(editRemark.Text) + ' )';

        Query.Close;
        Query.SQL.Text := SQLTxt;
        Query.ExecSQL;
        ShowMessage('新增成功^^');

      begin
        Query.Close;
        Query.SQL.Text := SQLSelectTxt;
        Query.open;
     end;
    end;
end;

//刪除
procedure TfrmDatabaseMgr.btnDeleteClick(Sender: TObject);
var
  SQLTxt, strMsg: String;
  nMsgReturn: Integer;
begin
   if editInputCardCode.Text = ''  then
    begin
      ShowMessage('輸入的卡號(hào)不能為空^^');
      Exit;
    end;
   if editInputCardCode.Text <> '' then
   begin
     strMsg := '確定刪除這條記錄嗎？';
     nMsgReturn := MessageBox(0,Pchar(strMsg),'刪除',MB_YESNO);
     if nMsgReturn = IDYES	 then
     begin
       SQLTxt := Format('delete from T_CARD_KIND where CARD_CODE = %s',
                  [QuotedStr(editInputCardCode.Text)]);

       Query.Close;
       Query.SQL.Text := SQLTxt;
       Query.ExecSQL;
       ShowMessage('刪除成功^^');
       editInputCardCode.Text := '';
       begin
         Query.Close;
         Query.SQL.Text := SQLSelectTxt;
         Query.Open;
       end;
     end;

   end;
end;

//修改
procedure TfrmDatabaseMgr.btnModifyClick(Sender: TObject);
var
  SQLTxt, strMsg: String;
  nMsgReturn: Integer;
begin
    if editInputCardCode.Text = ''  then
    begin
      ShowMessage('輸入卡號(hào)不能為空^^');
      Exit;
    end;
    if (editInputCardCode.Text <> DataSoures.DataSet.FindField('卡接入號(hào)').AsString) then
    begin
       ShowMessage('不能修改卡號(hào)^^');
       Exit;
    end;

    if (comboCardKind.ItemIndex = -1) or (comboCardState.ItemIndex = -1) then
    begin
     ShowMessage('請(qǐng)確認(rèn)卡類(lèi)型或者卡狀態(tài)輸入是否正確^^');
     Exit;
    end
    else
    begin
     strMsg := '確定修改這條記錄嗎？';
     nMsgReturn := MessageBox(0,Pchar(strMsg),'修改',MB_YESNO);
     if nMsgReturn = IDYES	 then
     begin
      SQLTxt := Format('update T_CARD_KIND SET CARD_PASS = '+ QuotedStr(editInputCardPass.Text) + ','
              + 'KIND = '+ Inttostr(comboCardKind.ItemIndex) + ','
              + 'HEAD_CHAR = '+ QuotedStr(editOutDial.Text) + ','
              + 'REMARK = '+ QuotedStr(editRemark.Text) + ','
              + 'DISPLAY_CARD = '+ Inttostr(radiogrpShowCaller.ItemIndex) + ','
              + 'FEE_CHECKED = '+ Inttostr(radiogrpFeeCheck.ItemIndex) + ','
              + 'CARD_STATE = '+ Inttostr(comboCardState.ItemIndex) +' where CARD_CODE = %s',
               [QuotedStr(editInputCardCode.Text)]);

         {
          Query.Edit;
          Query.FieldByName('卡管理員密碼').AsString := editInputCardPass.Text;
          Query.FieldByName('備注').AsString := editRemark.Text;
          Query.FieldByName('外撥默認(rèn)字頭').AsString := editOutDial.Text;
          Query.Post;
          }
      Query.Close;
      Query.SQL.Text := SQLTxt;
      Query.ExecSQL;

      ShowMessage('修改成功');

       begin
         Query.Close;
         Query.SQL.Text := SQLSelectTxt;
         Query.Open;
       end;
     end;
    end;
end;

//退出
procedure TfrmDatabaseMgr.btnExitClick(Sender: TObject);
begin
  close;
end;

procedure TfrmDatabaseMgr.DataSouresDataChange(Sender: TObject;
  Field: TField);
  var
  strShowCaller: String;
  strFeeCheck: String;
begin
    if [dsEdit, dsInsert] * [DataSoures.DataSet.State] <> [] then
        exit;

    with DataSoures.DataSet do
    begin
       if DataSoures.DataSet.Active then
       begin
          if not DataSoures.DataSet.Eof then
            editInputCardCode.Text := DataSoures.DataSet.FindField('卡接入號(hào)').AsString;
            editInputCardPass.Text := DataSoures.DataSet.FindField('卡管理員密碼').AsString;
            comboCardKind.ItemIndex := comboCardKind.Items.IndexOf(
                        DataSoures.DataSet.FindField('卡種類(lèi)').AsString);
            strShowCaller := DataSoures.DataSet.FindField('顯示接入號(hào)碼為主叫號(hào)碼的開(kāi)關(guān)').AsString;
           // radiogrpShowCaller.ItemIndex := radiogrpShowCaller.Items.IndexOf('  顯示卡號(hào)') ;
            if strShowCaller = '顯示卡號(hào)' then
              radiogrpShowCaller.ItemIndex := 0
            else
              radiogrpShowCaller.ItemIndex := 1;

            editRemark.Text := DataSoures.DataSet.FindField('備注').AsString;
            editOutDial.Text := DataSoures.DataSet.FindField('外撥默認(rèn)字頭').AsString;
            comboCardState.ItemIndex := comboCardState.Items.IndexOf(
                         DataSoures.DataSet.FindField('卡狀態(tài)').AsString);
            strFeeCheck := DataSoures.DataSet.FindField('話(huà)費(fèi)檢測(cè)開(kāi)關(guān)').AsString;

            if strFeeCheck = '后付費(fèi)' then
              radiogrpFeeCheck.ItemIndex := 0
            else
              radiogrpFeeCheck.ItemIndex := 1;

            // radiogrpFeeCheck.ItemIndex := radiogrpFeeCheck.Items.IndexOf('  后付費(fèi)') ;
            strInputCardCode := editInputCardCode.Text;
       end;
    end;

end;

procedure TfrmDatabaseMgr.FormCreate(Sender: TObject);
begin
    btnAdd.Enabled := false;
    btnModify.Enabled := false;
    btnDelete.Enabled := false;
    btnClear.Enabled := false;
end;

procedure TfrmDatabaseMgr.btnClearClick(Sender: TObject);
var
  strMsg: String;
  nMsgReturn: Integer;
begin
     strMsg := '確定要清空記錄嗎？';
     nMsgReturn := MessageBox(0,Pchar(strMsg),'清空',MB_YESNO);
     if nMsgReturn = IDYES	 then
      begin
        editInputCardCode.Clear;
        editInputCardPass.Clear;
        comboCardKind.ItemIndex := -1;
        comboCardState.ItemIndex := -1;
        editRemark.Clear;
        editOutDial.Clear;
      end;
end;
      
end.