이거 붙들구 몇일 동안..

이를 갈구 있씀돠..

다른 방법으로 해보아두..

잘 안되구...

2개의 db 그리드가 있구..

멀티 셀렉트가 가능하답니다.

왼쪽의 그리드에서..

선택을 해서

오른쪽 그리드로..

db에 저장을 해서 보여주는 플그림입니다.

근데..

primery key 값이 중복 될 경우..

update를 아닐 경우 insert를 하려 합니다.

아무리 헤딩을 해두 피밖에 안나는 군여..

도와 주세여~~

오늘 밤부턴..

서울 지방도 비가 온다구 하더군여..

제 맘속에..

벌써부터 비가 오고 있답니다.






unit uni_prg_grp;

interface

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

type
  Tfrm_prg_grp = class(TForm)
    Panel1: TPanel;
    BitBtn1: TBitBtn;
    btn_close: TBitBtn;
    Image1: TImage;
    Panel59: TPanel;
    sbtn_as: TSpeedButton;
    Panel19: TPanel;
    Panel58: TPanel;
    edt_find: TEdit;
    Panel37: TPanel;
    Panel39: TPanel;
    com_grp: TComboBox;
    pan_bottom: TPanel;
    Panel2: TPanel;
    chc_count: TCheckBox;
    pan_count: TPanel;
    Panel6: TPanel;
    Panel3: TPanel;
    Panel4: TPanel;
    Panel5: TPanel;
    Splitter1: TSplitter;
    btn_add: TButton;
    btn_del: TButton;
    DBG_find: TDBGrid;
    DBG_grp: TDBGrid;
    btn_alladd: TSpeedButton;
    btn_alldel: TSpeedButton;
    BitBtn3: TBitBtn;
    com_permission: TComboBox;
    rad_r: TRadioButton;
    rad_rw: TRadioButton;
    rad_rwd: TRadioButton;
    Panel7: TPanel;
    db: TDatabase;
    DbSource: TDataSource;
    qry_tmp: TQuery;
    qry_find: TQuery;
    Panel8: TPanel;
    chc_count2: TCheckBox;
    pan_count2: TPanel;
    qry_tmp2: TQuery;
    qry_insert: TQuery;
    qry_grp: TQuery;
    qry_del: TQuery;
    DbSource2: TDataSource;
    mem_grp: TMemo;
    procedure FormCreate(Sender: TObject);
    procedure btn_closeClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure sbtn_asClick(Sender: TObject);
    procedure chc_countClick(Sender: TObject);
    procedure btn_addClick(Sender: TObject);
    procedure com_grpClick(Sender: TObject);
    procedure chc_count2Click(Sender: TObject);
    procedure btn_delClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure com_insert(qry:TQuery; com:TCombobox);
    procedure permission_insert(qry:TQuery; com:TCombobox);
    procedure grp_insert(qry:TQuery);
    procedure grp_update(qry:TQuery);
    procedure grp_delete(qry:TQuery);
  end;

var
  frm_prg_grp: Tfrm_prg_grp;
  s : array[0..1000] of string;

implementation

uses  uni_prg_pro, func;

{$R *.dfm}


(************************************************************************
                        폼 생성 프로시저
************************************************************************)
procedure Tfrm_prg_grp.FormCreate(Sender: TObject);
begin
   db.Connected := true;                        
   com_insert(qry_tmp, com_grp);                
   permission_insert(qry_tmp2, com_permission);  
   mem_grp.Hide;
   width := 810;
   height := 606;
end;


(************************************************************************
                그룹 선택(ComboBox)에 값 넣는 프로시저
************************************************************************)
procedure Tfrm_prg_grp.com_insert(qry:TQuery; com:TCombobox);
var
  i:integer;
begin
  com.Items.Clear;
  com.Items.Add('');

  with qry do begin
     close;
     sql.Add('select * ');
     sql.Add('from tb_group');
     open;

     for i := 0 to recordcount-1 do begin
       com.Items.Add(FieldByName('group_id').AsString + '    :    ' + FieldByName('group_name').AsString);
       next;
     end;  { for end }
     close;
  end;     { with end }
end;       { procedure end }


(************************************************************************
                권한(ComboBox)에 값 넣는 프로시저
************************************************************************)
procedure Tfrm_prg_grp.permission_insert(qry:TQuery; com:TCombobox);
var
  i:integer;
begin
  com.Items.Clear;
  com.Items.Add('');

  with qry do begin
     close;
     sql.Add('select * ');
     sql.Add('from cd_codekind');
     sql.Add('where major = ''601'' and minor <> ''****''');
     open;

     for i := 0 to recordcount -1 do begin
       com.Items.Add(FieldByName('minor').AsString + '  :  ' + FieldByName('descrt').AsString);
       next;
     end;  { for end }
     close;
  end;     { with end }
end;       { procedure end }


(************************************************************************
                      폼 종료 버튼 클릭
************************************************************************)
procedure Tfrm_prg_grp.btn_closeClick(Sender: TObject);
begin
  close;
end;



(************************************************************************
               폼 Close 프로시저 (==> 메모리에서 해제한다.)
************************************************************************)
procedure Tfrm_prg_grp.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  qry_find.Close;
  Action := caFree;
end;

(************************************************************************
                         프로그램 리스트 조회
************************************************************************)
procedure Tfrm_prg_grp.sbtn_asClick(Sender: TObject);
begin
  with qry_find do begin
    close;
    sql.Clear;
    sql.Add('select prog_id, module');
    sql.Add('from tb_progname');
    sql.add('where prog_id is not null');

    if edt_find.Text <> '' then begin
       sql.Add('and prog_id like '''+ edt_find.Text +'%'+ '''');
    end;

    open;

   if chc_count.Checked then
    pan_count.Caption:= inttostr(recordcount)
   else
    pan_count.Caption:= '';
  end;
end;



(************************************************************************
                    프로그램명  Record 건수 조회
************************************************************************)
procedure Tfrm_prg_grp.chc_countClick(Sender: TObject);
begin
  if chc_count.Checked and qry_find.Active then
     pan_count.Caption:= inttostr(qry_find.recordcount);
end;



(************************************************************************
                   프로그램 그룹등록  버튼 클릭
************************************************************************)
procedure Tfrm_prg_grp.btn_addClick(Sender: TObject);
var
  i,j:integer;
begin
  // Multi Select 대비
  if DBG_find.SelectedRows.Count>0 then begin
     with DBG_find.DataSource.DataSet do begin
       for i := 0 to DBG_find.SelectedRows.Count-1 do begin
         GotoBookmark(pointer(DBG_find.SelectedRows.Items[i]));
         for j := 0 to FieldCount-2 do begin
           s[i] := Fields[j].AsString;
         end;            { for end }
       end;   { for end }
     end;    { with end }
  end; { if end }

  if qry_find.Active = false then begin
     Application.MessageBox('프로그램 조회후 작업하십시요','에러',MB_OK);
  end

  else if com_grp.Text = '' then begin
     Application.MessageBox('그룹선택후 작업하십시요','에러',MB_OK);
  end

  else  begin
    for i := 0 to DBG_find.SelectedRows.Count-1 do begin
       if frm_prg_grp.mem_grp.Lines.Strings[j] = '' then begin  // 오른쪽 그리드에 값이 없을 때..
          try
             db.StartTransaction;
             grp_insert(qry_insert);
             db.commit;
             showmessage('저장성공');
          except
            on E:Exception do begin
               if pos('Key violation', E.Message) > 0 then
//                  ShowMessage('키값이 중복되었습니다.!')
               else if pos('Update failed', E.Message) > 0 then
                  ShowMessage('저장 실패')
               else
                 ShowMessage(E.Message+'로 인하여 데이타베이스를 최종저장하기 전상태로 환원합니다');
               db.Rollback;
            end;
          end;     { try end }
       end;

       for j := 0 to frm_prg_grp.mem_grp.Lines.Count -1 do begin
         if  s[i] = frm_prg_grp.mem_grp.Lines.Strings[j] then begin   // key가 중복될 때
            try
               db.StartTransaction;
               grp_update(qry_insert);
               db.commit;
               showmessage('저장성공');
            except
              on E:Exception do begin
                 if pos('Key violation', E.Message) > 0 then
//                    ShowMessage('키값이 중복되었습니다.!')
                 else if pos('Update failed', E.Message) > 0 then
                    ShowMessage('저장실패')
                 else
                   ShowMessage(E.Message+'로 인하여 데이타베이스를 최종저장하기 전상태로 환원합니다');
                 db.Rollback;
              end;
            end;     { try end }
           end    { if end }

         else if s[i] <> frm_prg_grp.mem_grp.Lines.Strings[j] then begin   // key가 uniqe할 때
            try
               db.StartTransaction;
               grp_insert(qry_insert);
               db.commit;
               showmessage('저장성공');
            except
              on E:Exception do begin
                 if pos('Key violation', E.Message) > 0 then
//                    ShowMessage('키값이 중복되었습니다.!')
                 else if pos('Update failed', E.Message) > 0 then
                    ShowMessage('저장실패')
                 else
                   ShowMessage(E.Message+'로 인하여 데이타베이스를 최종저장하기 전상태로 환원합니다');
                 db.Rollback;
              end;
            end;     { try end }
           end    { else end }

       end;    { for end }
    end;       { for end }
  end;    { else end }

end;


(************************************************************************
                     그룹등록 (insert) 프로시저
************************************************************************)
procedure Tfrm_prg_grp.grp_insert(qry:TQuery);
var
  permission,gPermission: string;
  i, j: integer;
begin

  // Multi Select 대비
  if DBG_find.SelectedRows.Count>0 then begin
     with DBG_find.DataSource.DataSet do begin
       for i := 0 to DBG_find.SelectedRows.Count-1 do begin
         GotoBookmark(pointer(DBG_find.SelectedRows.Items[i]));
         for j := 0 to FieldCount-2 do begin
           s[i] := Fields[j].AsString;
         end;            { for end }
       end;   { for end }
     end;    { with end }
  end; { if end }

  if rad_r.Checked then begin
     permission := 'R'
  end

  else if rad_rw.Checked then begin
     permission := 'W'
  end

  else if rad_rwd.Checked then begin
     permission := 'A'
  end;

  gPermission := get_gubun(com_permission.Text,':','0');     //권한

  with qry do begin
     Close;
     sql.Clear;
     sql.Add('insert into tb_access( ');
     sql.Add(' group_id '    );
     sql.Add(',prog_id'      );
     sql.Add(',permission'   );
     sql.Add(',g_permission)');
     sql.Add(' values( ');
     sql.Add(' :group_id '     );
     sql.Add(',:prog_id '      );
     sql.Add(',:permission '   );
     sql.Add(',:g_permission )');

     ParamByName('group_id').AsString     :=  get_gubun(com_grp.Text,':','0');
     ParamByName('permission').AsString   :=  permission;
     ParamByName('g_permission').AsString :=  gPermission;
     for i := 0 to DBG_find.SelectedRows.Count-1 do begin
       ParamByName('prog_id').AsString      :=  s[i];
       ExecSQL;
     end;
     close;
  end;

  with qry_grp do begin
     Close;
     sql.Clear;
     sql.Add('select a.prog_id, p.module, a.permission, c.descrt as g_permission ');
     sql.Add(' from ' );
     sql.Add('  tb_progname p' );
     sql.Add(' ,tb_access a ' );
     sql.Add(' ,(select minor,descrt from cd_codekind where major = ''601'' and minor <> ''****'' ) c' );
     sql.Add(' where a.prog_id      = p.prog_id  ' );
     sql.Add(' and   a.g_permission = c.minor(+) ' );
     sql.Add(' and   a.group_id     = :group_id  ' );
     sql.Add(' order by ''2'' ' );
     ParamByName('group_id').AsString := get_gubun(com_grp.Text,':','0');
     Open;
  end;
end;


(************************************************************************
                     그룹 업데이트 (Update) 프로시저
************************************************************************)
procedure Tfrm_prg_grp.grp_update(qry:TQuery);
var
  permission, gpermission:string;
  i, j: integer;
begin

   // Multi Select 대비
   if DBG_find.SelectedRows.Count>0 then begin
      with DBG_find.DataSource.DataSet do begin
        for i := 0 to DBG_find.SelectedRows.Count-1 do begin
          GotoBookmark(pointer(DBG_find.SelectedRows.Items[i]));
          for j := 0 to FieldCount-2 do begin
            s[i] := Fields[j].AsString;
          end;            { for end }
        end;   { for end }
      end;    { with end }
   end; { if end }

   if rad_r.Checked then begin
      permission := 'R'
   end

   else if rad_rw.Checked then begin
      permission := 'W'
   end

   else if rad_rwd.Checked then begin
      permission := 'A'
   end;

   gPermission := get_gubun(com_permission.Text,':','0'); //권한

   with qry do begin
     Close;
     sql.Clear;
     sql.Add('update tb_access set');
     sql.Add(' permission     = :permission ');
     sql.Add(',g_permission   = :g_permission ');
     sql.Add(' where group_id = :group_id ');
     sql.Add(' and   prog_id  = :prog_id  ');
     ParamByName('permission').AsString   := permission;
     ParamByName('g_permission').AsString := gPermission;
     ParamByName('group_id').AsString     := get_gubun(com_grp.Text,':','0');

     for i := 0 to DBG_find.SelectedRows.Count-1 do begin
       ParamByName('prog_id').AsString      :=  s[i];
       ExecSQL;
     end;

     close;
   end;

   with qry_grp do begin
      Close;
      sql.Clear;
      sql.Add('select a.prog_id, p.module, a.permission, c.descrt as g_permission ');
      sql.Add(' from ' );
      sql.Add('  tb_progname p' );
      sql.Add(' ,tb_access a ' );
      sql.Add(' ,(select minor,descrt from cd_codekind where major = ''601'' and minor <> ''****'' ) c' );
      sql.Add(' where a.prog_id      = p.prog_id  ' );
      sql.Add(' and   a.g_permission = c.minor(+) ' );
      sql.Add(' and   a.group_id     = :group_id  ' );
      sql.Add(' order by ''2'' ' );
      ParamByName('group_id').AsString := get_gubun(com_grp.Text,':','0');
      Open;
   end;

end;


(************************************************************************
                     그룹삭제 버튼 클릭
************************************************************************)
procedure Tfrm_prg_grp.btn_delClick(Sender: TObject);
begin

   db.StartTransaction;
   try
     grp_delete(qry_del);  // 그룹삭제 (delete)

     db.commit;
     showmessage('삭제성공');
   except
     db.Rollback;
     showmessage('삭제실패');
   end;
end;

(************************************************************************
                     그룹삭제 (delete) 프로시저
************************************************************************)
procedure Tfrm_prg_grp.grp_delete(qry:Tquery);
var
  i, j: integer;
begin

   // Multi Select 대비
   if DBG_grp.SelectedRows.Count>0 then begin
      with DBG_grp.DataSource.DataSet do begin
        for i := 0 to DBG_grp.SelectedRows.Count-1 do begin
          GotoBookmark(pointer(DBG_grp.SelectedRows.Items[i]));
          for j := 0 to FieldCount-4 do begin
            s[i] := Fields[j].AsString;
          end;            { for end }
        end;   { for end }
      end;    { with end }
   end; { if end }

   with qry do begin
     Close;
     sql.Clear;
     sql.Add(' delete ' );
     sql.Add(' from tb_access ' );
     sql.Add(' where group_id = :group_id ');
     sql.Add('  and  prog_id  = :prog_id ');
     ParamByName('group_id').AsString := get_gubun(com_grp.Text,':','0');

     for i := 0 to DBG_grp.SelectedRows.Count-1 do begin
       ParamByName('prog_id').AsString      :=  s[i];
       ExecSQL;
      
     end;
     close;
   end;

   with qry_grp do begin
      Close;
      sql.Clear;
      sql.Add('select a.prog_id, p.module, a.permission, c.descrt as g_permission ');
      sql.Add(' from ' );
      sql.Add('  tb_progname p' );
      sql.Add(' ,tb_access a ' );
      sql.Add(' ,(select minor,descrt from cd_codekind where major = ''601'' and minor <> ''****'' ) c' );
      sql.Add(' where a.prog_id      = p.prog_id  ' );
      sql.Add(' and   a.g_permission = c.minor(+) ' );
      sql.Add(' and   a.group_id     = :group_id  ' );
      sql.Add(' order by ''2'' ' );
      ParamByName('group_id').AsString := get_gubun(com_grp.Text,':','0');
      Open;
   end;  
end;

(************************************************************************
        그룹선택(ComboBox)시 발생 이벤트 (==> 해당 프로그램 조회)
************************************************************************)
procedure Tfrm_prg_grp.com_grpClick(Sender: TObject);
var
  i:integer;
begin
   mem_grp.Clear;

   with qry_grp do begin
      Close;
      sql.Clear;
      sql.Add('select a.prog_id, p.module, a.permission, c.descrt as g_permission ');
      sql.Add(' from ' );
      sql.Add('  tb_progname p' );
      sql.Add(' ,tb_access a ' );
      sql.Add(' ,(select minor,descrt from cd_codekind where major = ''601'' and minor <> ''****'' ) c' );
      sql.Add(' where a.prog_id      = p.prog_id  ' );
      sql.Add(' and   a.g_permission = c.minor(+) ' );
      sql.Add(' and   a.group_id     = :group_id  ' );
      sql.Add(' order by ''2'' ' );
      ParamByName('group_id').AsString := get_gubun(com_grp.Text,':','0');
      Open;

      for i := 0 to recordcount-1 do begin
        mem_grp.Lines.Add(FieldByName('prog_id').AsString );
        next;
      end;

      if chc_count2.Checked then
         pan_count2.Caption:= inttostr(recordcount)
      else
         pan_count2.Caption:= '';

   end;
end;


(************************************************************************
                     그룹  Access Record 건수 조회
************************************************************************)
procedure Tfrm_prg_grp.chc_count2Click(Sender: TObject);
begin
  if chc_count2.Checked and qry_find.Active then
     pan_count2.Caption:= inttostr(qry_find.recordcount);
end;

end.