Создание базы данных средствами СУБД MS SQL Server 2000 и разработка клиентского приложения для работы с БД

       Param_ost.Visible:=False;

       Param_podlic.Visible:=True;

       Param_podr.Visible:=False;

       Param_sch.Visible:=False;

     end;

 

  3: begin

       ShowTabl(rg_tab.ItemIndex);

       Param_banki.Visible:=False;

       Param_doc.Visible:=False;

       Param_dvig.Visible:=False;

       Param_kas.Visible:=False;

       Param_ost.Visible:=False;

       Param_podlic.Visible:=False;

       Param_podr.Visible:=False;

       Param_sch.Visible:=True;

     end;

 

  4: begin

       ShowTabl(rg_tab.ItemIndex);

       Param_banki.Visible:=False;

       Param_doc.Visible:=False;

       Param_dvig.Visible:=True;

       Param_kas.Visible:=False;

       Param_ost.Visible:=False;

       Param_podlic.Visible:=False;

       Param_podr.Visible:=False;

       Param_sch.Visible:=False;

     end;

 

  5: begin

       ShowTabl(rg_tab.ItemIndex);

       Param_banki.Visible:=False;

       Param_doc.Visible:=False;

       Param_dvig.Visible:=False;

       Param_kas.Visible:=False;

       Param_ost.Visible:=True;

       Param_podlic.Visible:=False;

       Param_podr.Visible:=False;

       Param_sch.Visible:=False;

     end;

 

  6: begin

       ShowTabl(rg_tab.ItemIndex);

       Param_banki.Visible:=True;

       Param_doc.Visible:=False;

       Param_dvig.Visible:=False;

       Param_kas.Visible:=False;

       Param_ost.Visible:=False;

       Param_podlic.Visible:=False;

       Param_podr.Visible:=False;

       Param_sch.Visible:=False;

     end;

 

  7: begin

       ShowTabl(rg_tab.ItemIndex);

       Param_banki.Visible:=False;

       Param_doc.Visible:=False;

       Param_dvig.Visible:=False;

       Param_kas.Visible:=False;

       Param_ost.Visible:=False;

       Param_podlic.Visible:=False;

       Param_podr.Visible:=True;

       Param_sch.Visible:=False;

     end;

   end;

   show_info;

end;

 

 

 

procedure TTable_ko.ShowTabl(id: integer);

begin

  Table_ko.Adotable_t.Active :=false;

  Table_ko.Adotable_t.Connection := Base.ADOConnection1;

  Table_ko.DataSource_t.DataSet := Adotable_t;

  Table_ko.DBGrid1.DataSource := DataSource_t;

  Adotable_t.TableName := Naz_tab[id];

  Adotable_t.Active := true;

end;

 

procedure TTable_ko.BitBtn1Click(Sender: TObject);

begin

  Table_ko.Close;

end;

 

procedure TTable_ko.DBGrid1CellClick(Column: TColumn);

begin

   show_info;

end;

 

procedure TTable_ko.Prev_recClick(Sender: TObject);

begin

  if not dataSource_t.DataSet.Bof then

  begin

    DataSource_t.DataSet.Prior;

    show_info;

  end;

 

end;

 

procedure TTable_ko.Next_recClick(Sender: TObject);

begin

   if not DataSource_t.DataSet.Eof then

  begin

    DataSource_t.DataSet.Next;

    show_info;

  end;

 

end;

 

procedure TTable_ko.Edit_recClick(Sender: TObject);

begin

  save_rec;

end;

 

procedure TTable_ko.Del_recClick(Sender: TObject);

begin

    if MessageBox(Table_ko.Handle,'Вы действительно хотите удалить запись?','Кассовые операции', MB_YESNO or  MB_ICONQUESTION)= mryes

then

  begin

    try

      DataSource_t.DataSet.Delete;

      show_info;

    except

      begin

        MessageBox(Base.handle,'Невозможно удалить запись, она используется в других таблицах!','Ошибка!', MB_DEFBUTTON3 or  MB_ICONWARNING);

        Table_ko.SetFocus;

      end; 

    end;

  end;

end;

 

procedure TTable_ko.Insert_recClick(Sender: TObject);

begin

  DataSource_t.DataSet.Insert;

  clear_info;

  Savebase.Enabled:=True;

  Insert_rec.Enabled:=False;

end;

 

procedure TTable_ko.SavebaseClick(Sender: TObject);

begin

  save_rec;

  Savebase.Enabled:=False;

  Insert_rec.Enabled:=True;

end;

 

end.

 

unit Zaprosi;

 

interface

 

uses

  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

  Dialogs, StdCtrls, ExtCtrls, Grids, DBGrids, DB, ADODB, Mask, Kassovie_operacii, Buttons;

 

type

  TZaprosi_ko = class(TForm)

    rg_zapr: TRadioGroup;

    ADOTable_z: TADOTable;

    ADOQuery_z: TADOQuery;

    DataSource_z: TDataSource;

    DBGrid1: TDBGrid;

    Param_zap: TPanel;

    Label1: TLabel;

    Date_doc: TMaskEdit;

    Label2: TLabel;

    word: TEdit;

    Out_zapr: TBitBtn;

    BitBtn1: TBitBtn;

    procedure rg_zaprClick(Sender: TObject);

    procedure wordKeyPress(Sender: TObject; var Key: Char);

    procedure Out_zaprClick(Sender: TObject);

    procedure BitBtn1Click(Sender: TObject);

    procedure FormKeyDown(Sender: TObject; var Key: Word;

      Shift: TShiftState);

    procedure FormCreate(Sender: TObject);

  private

    { Private declarations }

    procedure  Ochistka;

    procedure Zapros1;

    procedure Zapros2;

    procedure Zapros3;

    procedure Zapros4;

    procedure Zapros5;

  public

    { Public declarations }

  end;

 

var

  Zaprosi_ko: TZaprosi_ko;

 

implementation

 

{$R *.dfm}

 

 

procedure  TZaprosi_ko.Ochistka;

begin

       Date_doc.Clear;

       word.Clear;

end;

 

procedure TZaprosi_ko.rg_zaprClick(Sender: TObject);

begin

  case rg_zapr.ItemIndex of

  0: begin

       Ochistka;

       Label1.Visible:=true;

       Label2.Visible:=false;

       Date_doc.Visible:=true;

       word.Visible:=false;

     end;

 

  1: begin

       Param_zap.Visible:=False

     end;

 

  2: begin

       Param_zap.Visible:=False

     end;

 

  3: begin

       Ochistka;

       Param_zap.Visible:=True;

       Label1.Visible:=false;

       Label2.Visible:=true;

       Date_doc.Visible:=false;

       word.Visible:=true;

     end;

 

  4: begin

       Param_zap.Visible:=False

     end;

 

   end;

end;

 

procedure TZaprosi_ko.Zapros5;

begin

datasource_z.Enabled:=false;

datasource_z.DataSet:=ADOQuery_z;

adoQuery_z.Active:=false;

Adoquery_z.SQL.Clear;

adoquery_z.SQL.Add('SELECT Ostatki.Date, FIO_kas, (Ostatki.ost_na_nach + Ostatki.sum_prih - Ostatki.sum_rash)ost_kon_dnya  FROM Ostatki, Kassiri WHERE Kassiri.Kod_kas=Ostatki.Kod_kas');

adoquery_z.Active:=true;

datasource_z.Enabled:=true;

end;

 

procedure TZaprosi_ko.Zapros1;

var

date_z: string;

 

begin

 

date_z:= Date_doc.Text;

datasource_z.Enabled:=false;

datasource_z.DataSet:=ADOQuery_z;

adoQuery_z.Active:=false;

Adoquery_z.SQL.Clear;

adoquery_z.SQL.Add('SELECT Doc.Naimen_doc, Dvigenie.Date FROM Doc, Dvigenie WHERE Doc.Kod_doc=Dvigenie.Kod_doc AND Dvigenie.Date=cast('+quotedstr(date_z)+' as smalldatetime)' );

adoquery_z.Active:=true;

datasource_z.Enabled:=true;

end;

 

 

procedure TZaprosi_ko.Zapros2;

begin

datasource_z.Enabled:=false;

datasource_z.DataSet:=ADOQuery_z;

adoQuery_z.Active:=false;

Adoquery_z.SQL.Clear;

adoquery_z.SQL.Add('SELECT Kassiri.FIO_kas, sum(Ostatki.sum_prih)obw_sum_prih  FROM Kassiri, Ostatki WHERE Kassiri.Kod_kas=Ostatki.Kod_kas AND Month(Ostatki.Date)=Month(GetDate()) AND Year(Ostatki.Date)=Year(GetDate()) GROUP BY Kassiri.FIO_kas');

adoquery_z.Active:=true;

datasource_z.Enabled:=true;

end;

 

 

procedure TZaprosi_ko.Zapros3;

var

num: string;

 

begin

num:='1';

datasource_z.Enabled:=false;

datasource_z.DataSet:=ADOQuery_z;

adoQuery_z.Active:=false;

Adoquery_z.SQL.Clear;

adoquery_z.SQL.Add('SELECT Podotch_lic.FIO_sotr FROM Podotch_lic, Dvigenie, Doc WHERE Dvigenie.Kod_doc=cast('+quotedstr(num)+' as smallint) AND Podotch_lic.Kod_sotr=Dvigenie.Kod_sotr GROUP BY Podotch_lic.FIO_sotr');

adoquery_z.Active:=true;

datasource_z.Enabled:=true;

end;

 

 

procedure TZaprosi_ko.Zapros4;

var

   slovo: string;

 

begin

slovo:=word.Text;

datasource_z.Enabled:=false;

datasource_z.DataSet:=ADOQuery_z;

adoQuery_z.Active:=false;

Adoquery_z.SQL.Clear;

adoquery_z.SQL.Add('SELECT Doc.Naimen_doc FROM Doc WHERE Naimen_doc LIKE '''+'%'+slovo+'%'+'''');

adoquery_z.Active:=true;

datasource_z.Enabled:=true;

 

end;

 

 

procedure TZaprosi_ko.Out_zaprClick(Sender: TObject);

begin

case rg_zapr.ItemIndex of

  0: begin

      Zapros1;

 

     end;

 

  1: begin

       Zapros2;

     end;

 

  2: begin

       Zapros3;

     end;

 

  3: begin

       Zapros4;

     end;

 

  4: begin

       Zapros5;

     end;

 

   end;

end;

 

procedure TZaprosi_ko.BitBtn1Click(Sender: TObject);

begin

  zaprosi_ko.Close;

end;

end.

 

unit Otcheti;

 

interface

 

uses

  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

  Dialogs, StdCtrls, ExtCtrls, ADODB, DB, Mask, DBCtrls, Buttons, Kassovie_operacii,

  Grids, DBGrids, OleCtnrs, ComObj;

 

type

  TOtcheti_ko = class(TForm)

    rg_ot: TRadioGroup;

    DataSource_o: TDataSource;

    ADOTable_o: TADOTable;

    ADOQuery_o: TADOQuery;

    Param_otch: TPanel;

    Label1: TLabel;

    Label2: TLabel;

    Data: TMaskEdit;

    id_kas: TEdit;

    Label3: TLabel;

    id_podr: TEdit;

    Out_otch: TBitBtn;

    DBGrid1: TDBGrid;

    BitBtn1: TBitBtn;

    procedure rg_otClick(Sender: TObject);

    procedure id_podrKeyPress(Sender: TObject; var Key: Char);

    procedure id_kasKeyPress(Sender: TObject; var Key: Char);

    procedure Out_otchClick(Sender: TObject);

    procedure BitBtn1Click(Sender: TObject);

    procedure FormKeyDown(Sender: TObject; var Key: Word;

      Shift: TShiftState);

    procedure FormCreate(Sender: TObject);

  private

    { Private declarations }

     procedure  Ochistka;

     procedure  Otchet1;

     procedure  Otchet2;

     procedure  Otchet3;

     procedure  Otchet_to_Exel;

 

  public

    { Public declarations }

 

  end;

 

var

  Otcheti_ko: TOtcheti_ko;

 

implementation

 

{$R *.dfm}

 

procedure  TOtcheti_ko.Ochistka;

begin

       Data.Clear;

       id_kas.Clear;

       id_podr.Clear;

end;

 

procedure TOtcheti_ko.rg_otClick(Sender: TObject);

begin

  case  rg_ot.ItemIndex of

  0: begin

       Ochistka;

       Label1.Visible:=true;

       Label2.Visible:=true;

       Label3.Visible:=false;

       Data.Visible:=true;

       id_kas.Visible:=true;

       id_podr.Visible:=false;

       Out_otch.Visible:=true;

     end;

 

  1: begin

       Ochistka;

       Label1.Visible:=false;

       Label2.Visible:=false;

       Label3.Visible:=true;

       Data.Visible:=false;

       id_kas.Visible:=false;

       id_podr.Visible:=true;

       Out_otch.Visible:=true;

     end;

 

  2: begin

       Ochistka;

       Label1.Visible:=false;

       Label2.Visible:=true;

      Label3.Visible:=false;

       Data.Visible:=true;

       id_kas.Visible:=false;

       id_podr.Visible:=false;

       Out_otch.Visible:=true;

     end;

   end;

end;

 

procedure TOtcheti_ko.id_podrKeyPress(Sender: TObject; var Key: Char);

begin

  case Key of

  '0'..'9':  ;

 

  else

     Key:=Chr(0);

  end;

end;

 

procedure TOtcheti_ko.id_kasKeyPress(Sender: TObject; var Key: Char);

begin

  case Key of

  '0'..'9':  ;

 

  else

     Key:=Chr(0);

  end;

end;

 

procedure TOtcheti_ko.Otchet1;

var

kkas: string;

date_o: string;

 

begin

date_o:= Data.Text;

kkas:= id_kas.Text;

datasource_o.Enabled:=false;

datasource_o.DataSet:=ADOQuery_o;

adoQuery_o.Active:=false;

Adoquery_o.SQL.Clear;

adoquery_o.SQL.Add('select date, fio_kas, priz_oper, naimen_sch, tip_oper, fio_sotr, Dvigenie.sum, naimen_doc, num_doc from Dvigenie, Kassiri, podotch_lic, doc, buh_sch where Dvigenie.Kod_kas=cast('+quotedstr(kkas)+' as smallint) and Kassiri.Kod_kas=Dvigenie.Kod_kas and Dvigenie.Date=cast('+quotedstr(date_o)+' as smalldatetime) and doc.Kod_doc=Dvigenie.Kod_doc and buh_sch.Kod_sch=Dvigenie.Kod_sch and podotch_lic.Kod_sotr=Dvigenie.Kod_sotr');

adoquery_o.Active:=true;

datasource_o.Enabled:=true;

end;

 

procedure TOtcheti_ko.Otchet2;

var

kpodr: string;

 

begin

kpodr:= id_podr.Text;

datasource_o.Enabled:=false;

datasource_o.DataSet:=ADOQuery_o;

adoQuery_o.Active:=false;

Adoquery_o.SQL.Clear;

adoquery_o.SQL.Add('select fio_sotr from dvigenie, podotch_lic where kod_podr=cast('+quotedstr(kpodr)+' as smallint) and Year(dvigenie.date)=Year(GetDate()) and podotch_lic.kod_sotr=dvigenie.kod_sotr group by fio_sotr ');

adoquery_o.Active:=true;

datasource_o.Enabled:=true;

end;

 

procedure TOtcheti_ko.Otchet3;

var

date_o: string;

 

begin

date_o:= Data.Text;

datasource_o.Enabled:=false;

datasource_o.DataSet:=ADOQuery_o;

adoQuery_o.Active:=false;

Adoquery_o.SQL.Clear;

adoquery_o.SQL.Add('select fio_kas, ost_na_nach, sum_prih, sum_rash, (ost_na_nach + sum_prih - sum_rash)ost_kon_dnya from Ostatki, Kassiri where Kassiri.Kod_kas=Ostatki.Kod_kas and Ostatki.Date=cast('+quotedstr(date_o)+' as smalldatetime)' );

adoquery_o.Active:=true;

datasource_o.Enabled:=true;

end;

 

procedure TOtcheti_ko.Otchet_to_Exel;

var

  i,j,index: Integer;

  ExcelApp,sheet: Variant;

 

begin

  ExcelApp := CreateOleObject('Excel.Application');

  ExcelApp.Visible := true;

  ExcelApp.WorkBooks.Add(-4167);

  ExcelApp.WorkBooks[1].WorkSheets[1].name := 'Отчет';

  sheet:=ExcelApp.WorkBooks[1].WorkSheets['Отчет'];

 

  index:=4;

  with DataSource_o.DataSet do

 

  begin

    case rg_ot.ItemIndex of

  0: begin

     sheet.rows[1].Font.Bold:=True;

     sheet.rows[1].Font.Color:= clBlue;

     sheet.rows[1].Font.Size:= 14;

     sheet.rows[3].Font.Bold:=True;

     for j:=1 to 9 do sheet.columns[j].ColumnWidth:= 25;

     sheet.cells[1,1]:= rg_ot.Items[0];

     sheet.cells[3,1]:= 'Дата';

     sheet.cells[3,2]:= 'ФИО кассира';

     sheet.cells[3,3]:= 'Признак операции';

     sheet.cells[3,4]:= 'Наименование  счета';

     sheet.cells[3,5]:= 'Тип  операции';

     sheet.cells[3,6]:= 'ФИО  сотрудника';

     sheet.cells[3,7]:= 'Сумма';

     sheet.cells[3,8]:= 'Наименование  документа';

     sheet.cells[3,9]:= 'Номер  документа';

     end;

 

  1: begin

       sheet.rows[1].Font.Bold:=True;

       sheet.rows[1].Font.Color:= clBlue;

       sheet.rows[1].Font.Size:= 14;

       sheet.rows[3].Font.Bold:=True;

       sheet.columns[1].ColumnWidth:= 40;

       sheet.cells[1,1]:= rg_ot.Items[1];

       sheet.cells[3,1]:= 'ФИО подотченого лица';

     end;

 

  2: begin

     sheet.columns[1].ColumnWidth:= 40;

     sheet.columns[2].ColumnWidth:= 25;

     sheet.columns[3].ColumnWidth:= 25;

     sheet.columns[4].ColumnWidth:= 25;

     sheet.columns[5].ColumnWidth:= 25;

     sheet.rows[1].Font.Bold:=True;

     sheet.rows[1].Font.Color:= clBlue;

     sheet.rows[1].Font.Size:= 14;

     sheet.rows[3].Font.Bold:=True;

     sheet.cells[1,1]:= rg_ot.Items[2];

     sheet.cells[3,1]:= 'ФИО кассира';

     sheet.cells[3,2]:= 'Остаток на начало дня';

     sheet.cells[3,3]:= 'Сумма прихода';

     sheet.cells[3,4]:= 'Сумма расхода';

     sheet.cells[3,5]:= 'Остаток на конец дня';

     end;

 

end;

 

    for i:=1 to  RecordCount do

    begin

      for j:=1 to FieldCount do

       sheet.cells[index,j]:= fields[j-1].asstring;

      inc(index);

       Next;

    end;

  Next;

 

  end;

end;

 

procedure TOtcheti_ko.Out_otchClick(Sender: TObject);

begin

case rg_ot.ItemIndex of

  0: begin

       Otchet1;

       Otchet_to_Exel;

     end;

 

  1: begin

       Otchet2;

       Otchet_to_Exel;

     end;

 

  2: begin

       Otchet3;

       Otchet_to_Exel;

     end;

 

end;

end;

 

procedure TOtcheti_ko.BitBtn1Click(Sender: TObject);

begin

  Otcheti_ko.Close;

end;

 

procedure TOtcheti_ko.FormKeyDown(Sender: TObject; var Key: Word;

  Shift: TShiftState);

begin

  if key = vk_Escape then BitBtn1.Click;

  if key = vk_F1 then Base.N7.Click;

end;

 

procedure TOtcheti_ko.FormCreate(Sender: TObject);

var

  h: THandle;

begin

  //Использовать анимированный  курсор.

  h := LoadImage(0, 'Справка\cursor.ani', IMAGE_CURSOR, 0, 0, LR_DEFAULTSIZE or LR_LOADFROMFILE);

  Screen.Cursors[crArrow] := h;

end;

end.

 

 

unit HranProc;

 

interface

 

uses

  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

  Dialogs, StdCtrls, ExtCtrls, DB, ADODB, Grids, DBGrids, Buttons, Mask, Kassovie_operacii;

 

type

  THranproc_ko = class(TForm)

    rg_hp: TRadioGroup;

    DBGrid1: TDBGrid;

    ADOTable_hp: TADOTable;

    ADOQuery_hp: TADOQuery;

    DataSource_hp: TDataSource;

   Param_hp: TPanel;

    Label1: TLabel;

    Label2: TLabel;

    id_sch: TEdit;

    id_doc: TEdit;

    Label3: TLabel;

    id_kas: TEdit;

    Label4: TLabel;

    Data: TMaskEdit;

    Out_hp: TBitBtn;

    BitBtn1: TBitBtn;

    procedure rg_hpClick(Sender: TObject);

    procedure id_kasKeyPress(Sender: TObject; var Key: Char);

    procedure id_docKeyPress(Sender: TObject; var Key: Char);

    procedure id_schKeyPress(Sender: TObject; var Key: Char);

    procedure Out_hpClick(Sender: TObject);

    procedure BitBtn1Click(Sender: TObject);

    procedure FormKeyDown(Sender: TObject; var Key: Word;

      Shift: TShiftState);

    procedure FormCreate(Sender: TObject);

  private

    { Private declarations }

    procedure  Ochistka;

    procedure HP1;

    procedure HP2;

    procedure HP3;

    procedure HP4;

  public

    { Public declarations }

  end;

 

var

  Hranproc_ko: THranproc_ko;

 

implementation

 

uses Tables;

 

{$R *.dfm}

 

procedure  THranproc_ko.Ochistka;

begin

       id_doc.Clear;

       id_sch.Clear;

       id_kas.Clear;

       Data.Clear;

end;

 

procedure THranproc_ko.rg_hpClick(Sender: TObject);

begin

case rg_hp.ItemIndex of

  0: begin

       Param_hp.Visible:=False

     end;

 

  1: begin

       Ochistka;

       Param_hp.Visible:=True;

       Label1.Visible:=true;

       Label2.Visible:=true;

       Label3.Visible:=false;

       Label4.Visible:=false;

       id_doc.Visible:=true;

       id_sch.Visible:=true;

       Data.Visible:=false;

       id_kas.Visible:=false;