databaseoperator.~pas

数据库操作

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}

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

 SQLSelectTxt :=  'select ID as 卡类型标识号, '
              + ' case KIND when 0 then ''记帐卡'' when 1 then ''智能IC卡'' when 2 then ''公司卡'' when 4 then ''酒店卡'' end as 卡种类, '
              + ' CARD_CODE as 卡接入号,CARD_PASS as  卡管理员密码, '
              + ' case CARD_STATE when 0 then ''未启用'' when 1 then ''已开通'' when 2 then ''已停机'' when 3 then ''因欠费被停机'' when 4 then ''已注销'' end as 卡状态, '
              + ' case FEE_CHECKED  when 0 then ''后付费'' when 1 then ''预付费'' end as 话费检测开关,'
              + ' case DISPLAY_CARD when 0 then ''显示卡号'' when 1 then ''显示主叫号码'' end as  显示接入号码为主叫号码的开关, '
              + ' HEAD_CHAR as 外拨默认字头, REMARK as 备注 from T_CARD_KIND ';


        //根据卡号查询信息
        if editInputCardCode.Text <> '' then
        begin
          strInputCardCode := editInputCardCode.Text;
          SQLCardCode := ' WHERE CARD_CODE = ' + strInputCardCode + ' ';
          Query.Close;
          Query.SQL.Text := SQLSelectTxt + SQLCardCode;
          Query.Open;
        end

        //根据卡类型查询信息
        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

        //根据卡状态查询信息
        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

        //根据卡号密码一起查询
        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

        //查询所有卡信息
        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('您输入的卡号已经存在^^');
        Exit;
      end;
      if editInputCardCode.Text = ''  then
      begin
        ShowMessage('输入新增卡的卡号不能为空^^');
        Exit;
      end;

      if editInputCardPass.Text = ''  then
      begin
        ShowMessage('输入新增卡的密码不能为空^^');
        Exit;
      end;
      if (comboCardKind.ItemIndex = -1) or (comboCardState.ItemIndex = -1) then
      begin
        ShowMessage('请确认卡类型或者卡状态输入是否正确^^');
        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('输入的卡号不能为空^^');
      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('输入卡号不能为空^^');
      Exit;
    end;
    if (editInputCardCode.Text <> DataSoures.DataSet.FindField('卡接入号').AsString) then
    begin
       ShowMessage('不能修改卡号^^');
       Exit;
    end;

    if (comboCardKind.ItemIndex = -1) or (comboCardState.ItemIndex = -1) then
    begin
     ShowMessage('请确认卡类型或者卡状态输入是否正确^^');
     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('外拨默认字头').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('卡接入号').AsString;
            editInputCardPass.Text := DataSoures.DataSet.FindField('卡管理员密码').AsString;
            comboCardKind.ItemIndex := comboCardKind.Items.IndexOf(
                        DataSoures.DataSet.FindField('卡种类').AsString);
            strShowCaller := DataSoures.DataSet.FindField('显示接入号码为主叫号码的开关').AsString;
           // radiogrpShowCaller.ItemIndex := radiogrpShowCaller.Items.IndexOf('  显示卡号') ;
            if strShowCaller = '显示卡号' then
              radiogrpShowCaller.ItemIndex := 0
            else
              radiogrpShowCaller.ItemIndex := 1;

            editRemark.Text := DataSoures.DataSet.FindField('备注').AsString;
            editOutDial.Text := DataSoures.DataSet.FindField('外拨默认字头').AsString;
            comboCardState.ItemIndex := comboCardState.Items.IndexOf(
                         DataSoures.DataSet.FindField('卡状态').AsString);
            strFeeCheck := DataSoures.DataSet.FindField('话费检测开关').AsString;

            if strFeeCheck = '后付费' then
              radiogrpFeeCheck.ItemIndex := 0
            else
              radiogrpFeeCheck.ItemIndex := 1;

            // radiogrpFeeCheck.ItemIndex := radiogrpFeeCheck.Items.IndexOf('  后付费') ;
            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.