unit kkotch;

interface
uses
  Types,
  Graphics,
  StdCtrls,
  SysUtils,
  Classes,
  Dialogs,
  ExtCtrls,
  Controls,
  ScadaBase,
  Forms,
  FIBQuery, pFIBQuery, FIBDatabase, pFIBDatabase, DB, FIBDataSet, pFIBDataSet, kkotchform
  ;

const
  dt_format = 'yyyy.mm.dd hh:nn:ss';
  dataformat = 'yyyymmdd';
  debug_mode = false;

type
  TPar = record
    komp : integer;
    bunk : integer;
  end;

  TTabValue = record
    proc_ideal, proc_real:double;
    wes_real, wes_sum_real,
    rec_sum_wes_ideal,
    rec_wes_ideal : real;
    storid,prodid,recid:integer;
    storname, prodname, recname : string[128];
  end;


  Tkkotch = class( TCustomScadaObject )
  public
    url        : string;
    user, pass : string;
    Connected  : boolean;
    file_name  : string;


    WES        : TpFIBDatabase;
    Trans      : TpFIBTransaction;
    qry        : TpFIBQuery;

    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    procedure Connect;
    procedure Disconnect;

    procedure Dial (s1,s2:string);

    procedure LogDB(s: string);

    function GetDumpMain (d1,d2:string):integer;

    procedure ExceptionClose;
    function  IsConnected : boolean;

    procedure Init; override;
    procedure Process; override;

  end;




function createkkotch(var parent: TPanel; DBLink, DBuser, DBpass : string): Tkkotch;

implementation
uses
  RpVisualGlobal,
  TagStorage,
  Variants,
  HyperStr;



procedure Log(s: string);
var
  F: TextFile;
begin
  try
    AssignFile(F, CurDir + 'error.log');
    if not FileExists(CurDir + 'error.log') then
      rewrite(F)
    else begin
      append(F);
    end;

    Writeln(F, DateTimeToStr(now) + ': ' + s);
  finally
    try
      CloseFile(F);
    except
    end;
  end;
end;
//}

procedure Tkkotch.LogDB(s: string);
var
  F: TextFile;
  s2:string;
begin
  try
    DateTimeToString(s2, 'yyyy_mm_dd', now);


//    if NOT DirectoryExists(CurDir + '\lg') then
//       CreateDir(CurDir + '\lg');

    AssignFile(F, file_name);
    if not FileExists(file_name) then
      rewrite(F)
    else begin
      append(F);
    end;

//    Writeln(F, DateTimeToStr(now) + ': ' + s);
    Writeln(F,  s);
  finally
    try
      CloseFile(F);
    except
    end;
  end;
end;




function createkkotch(var parent: TPanel; DBLink, DBuser, DBpass : string): Tkkotch;
var  temp: Tkkotch;
begin
  temp := Tkkotch.Create(parent);
  temp.parent := parent;
  temp.url  := DBLink;
  temp.user := DBUser;
  temp.pass := DBPass;
  result := temp;
end;


function  Tkkotch.IsConnected : boolean;
begin
 result := Connected;
end;


constructor Tkkotch.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  Connected := false;
  WES        := TpFIBDatabase.Create(AOwner);
  Trans      := TpFIBTransaction.Create(AOwner);
  qry        := TpFIBQuery.Create(AOwner);

  file_name := 'C:\otch.csv';
end;


procedure Tkkotch.Dial (s1,s2 : string);
var  kkotchformForm: TkkotchformForm;
begin
   kkotchformForm := TkkotchformForm.Create(Self);

   kkotchformForm.rez := false;
   kkotchformForm.ff := file_name;
   kkotchformForm.Edit1.Text := file_name;
   kkotchformForm.ShowModal;

   if kkotchformForm.rez then
     begin
       ShowMessage('Начинаем формировать отчет, это надолго');
       file_name := kkotchformForm.ff;
       GetDumpMain(kkotchformForm.s1 , kkotchformForm.s2);
     //  ShowMessage('закончили формировать отчет');
     end;

   kkotchformForm.Destroy;
end;




destructor Tkkotch.Destroy;
begin
  WES.Destroy;
  Trans.Destroy;
  qry.Destroy;
  inherited Destroy;
end;


procedure Tkkotch.Process;
begin
  inherited;
  if not rvgLogIn then Exit;
  //
end;


procedure Tkkotch.ExceptionClose;
begin
 if Qry.Transaction.Active then
   begin
     Qry.Transaction.Rollback;
     qry.Close;
     qry.FreeHandle;
   end;
end;


procedure Tkkotch.Init;
begin
  inherited;
end;


function Tkkotch.GetDumpMain (d1,d2:string):integer;
var sql:string;
    SavedToDB:boolean;
    c : integer;
    s, s2 : string;
    x : array of TPar;
    y : array of integer;
    G : array of array of TTabValue;
    t1,t2:integer;
    itg, itg2 : double;

procedure Xchek (a,b:integer);
var w:boolean;
    i:integer;
begin
 w:=false;
 if x <> nil then
   begin
     for i:=Low(x) to High(x) do
       begin
         if (x[i].komp = a ) and (x[i].bunk = b ) then
           begin
             w:=true;
             break;
           end;
       end;
   end;

 if not w then
   begin
     SetLength(X, length(x)+1);
     x[length(x)-1].komp:=a;
     x[length(x)-1].bunk:=b;
   end;

end;

procedure Ychek (a:integer);
var w:boolean;
    i:integer;
begin
 w:=false;
 if y <> nil then
   begin
     for i:=Low(y) to High(y) do
       begin
         if (y[i] = a ) then
           begin
             w:=true;
             break;
           end;
       end;
   end;

 if not w then
   begin
     SetLength(Y, length(y)+1);
     y[length(y)-1]:=a;
   end;

end;

function getX (a,b:integer) : integer;
var i:integer;
begin
     for i:=Low(x) to High(x) do
       begin
         if (x[i].komp = a ) and (x[i].bunk = b ) then
           begin
             break;
           end;
       end;
     result := i;
end;


function getY (a:integer) : integer;
var i:integer;
begin
     for i:=Low(y) to High(y) do
       begin
         if (y[i] = a ) then
           begin
             break;
           end;
       end;
     result := i;
end;



begin
  if NOT IsConnected then
    begin
      result := -1;
      exit;
    end;

  SavedToDB:=false;
//  sql:='SELECT * FROM GET_MASTER_TAB(D1, D2) ORDER BY STORNAME, PRODNAME, RECNAME';
  sql:='SELECT * FROM GET_MASTER_TAB(''' + d1 +  ''', ''' + d2 + ''') ORDER BY RECNAME, PRODNAME, STORNAME ';



 try
   qry.SQL.text:=sql;
   qry.Transaction.StartTransaction;
   qry.ExecQuery;

   c:=0;
  // LogDB ('--------------------------------------------------------------------------------');

   if qry.Eof then begin
     c := 0;
   end else begin
      y:=nil;
      x:=nil;
      c := 1;
      while not qry.Eof do
         begin
            Xchek (qry.Fields[10].AsInteger, qry.Fields[9].AsInteger);
            Ychek (qry.Fields[11].AsInteger);
            c := c + 1;
            qry.Next;
         end;
      c := c - 1;

{      for c := low(x) to high(x) do
        begin
          s := 'K=' + inttostr(x[c].komp) + ' B=' + inttostr(x[c].bunk);
          LogDB (s);
        end;
}

      SetLength(G, length(x), length(y));

      for t1 := low(g) to high(g) do
        for t2:= low(g[t1]) to high(g[t1]) do
          begin
           G[t1, t2].recname := '';
           G[t1, t2].prodname := '';
           G[t1, t2].storname := '';

           G[t1, t2].proc_ideal := 0;
           G[t1, t2].proc_real  := 0;
           G[t1, t2].wes_real   := 0;

           G[t1, t2].wes_sum_real       := 0;
           G[t1, t2].rec_sum_wes_ideal  := 0;
           G[t1, t2].rec_wes_ideal      := 0;

           G[t1, t2].storid   := 0;
           G[t1, t2].prodid   := 0;
           G[t1, t2].recid    := 0;

          end;

      qry.ExecQuery;
      while not qry.Eof do
         begin
           t1 := GetX (qry.Fields[10].AsInteger, qry.Fields[9].AsInteger);
           t2 := GetY (qry.Fields[11].AsInteger);

           G[t1, t2].recname := qry.Fields[0].AsString;
           G[t1, t2].prodname := qry.Fields[1].AsString;
           G[t1, t2].storname := qry.Fields[2].AsString;

           G[t1, t2].proc_ideal := qry.Fields[3].AsDouble;
           G[t1, t2].proc_real  := qry.Fields[4].AsDouble;
           G[t1, t2].wes_real   := qry.Fields[5].AsDouble;

           G[t1, t2].wes_sum_real       := qry.Fields[6].AsDouble;
           G[t1, t2].rec_sum_wes_ideal  := qry.Fields[7].AsDouble;
           G[t1, t2].rec_wes_ideal      := qry.Fields[8].AsDouble;

           G[t1, t2].storid   := qry.Fields[9].AsInteger;
           G[t1, t2].prodid   := qry.Fields[10].AsInteger;
           G[t1, t2].recid    := qry.Fields[11].AsInteger;

           c := c + 1;
           qry.Next;
         end;


      s:='Интервал выборки с '+ d1 + ' по ' + d2;
      LogDB ( s );

      s:= ';'+';';
      for t2 := low(y) to high(y) do
        begin

           for t1 := low(x) to high(x) do
            if G[t1,t2].recname <> '' then
              begin
                s := s + G[t1,t2].recname + ';';
                s := s + G[t1,t2].recname + ';';
                s := s + G[t1,t2].recname + ';';
                Break;
              end;
//          s := s + G[0,t2].recname + ';'+';';

        end;

      s := s + 'Итого;';

      LogDB ( s );


      s:= ';'+';';
      for t2 := low(y) to high(y) do
        begin

          s := s + 'Масса;'+'План;'+'Факт;';

        end;

      LogDB ( s );



      itg2 := 0;
      for t1 := low(x) to high(x) do
        begin
          s := '';
          for t2:= low(y) to high(y) do
            if G[t1,t2].storname <> '' then
              begin
                s := s + G[t1,t2].storname + ';';
                s := s + G[t1,t2].prodname + ';';
                Break;
              end;

          itg := 0;

          for t2:= low(y) to high(y) do
             begin
               s  := s + floattostr(G[t1,t2].wes_real) + ';';
               s  := s + floattostr(G[t1,t2].proc_ideal) + ';' + floattostr(G[t1,t2].proc_real)+ ';';
               itg := itg + G[t1,t2].wes_real;
             end;

          s := s + floattostr(itg);

          LogDB (s);

          itg2:= itg2 + itg;
        end;


      s:= ';'+'Итого;';
      for t2 := low(y) to high(y) do
        begin
           itg := 0;


           for t1 := low(x) to high(x) do
             itg := itg + G[t1,t2].wes_real;

           s := s + FloatToStr(itg) + ';';
           s := s + ';';
           s := s + ';';
        end;
      s := s+ FloatToStr(itg2) + ';';
      LogDB ( s );



   end;

  // LogDB ('--------------------------------------------------------------------------------');
   SavedToDB := true;
   Qry.Transaction.Commit;
   qry.Close;
   qry.FreeHandle;
   showmessage ('Готово ' + file_name);
   result := c;

 except
   SavedToDB := false;
   log (DateTimeToStr(now) + 'Провал запроса: '+sql);
   ExceptionClose;
   result := -1;

 end;
end;









procedure Tkkotch.Connect;
var  w:boolean;
begin

  if not rvgLogIn then Exit;
  if Connected then exit;

  w := true;


  WES.DatabaseName := url;

  WES.DBParams.Add('lc_ctype=WIN1251');
  WES.DBParams.Add('user_name=' + user);
  WES.DBParams.Add('password=' + pass);

  WES.SQLDialect := 3;

  WES.DefaultTransaction := Trans;
  qry.Database := WES;
  qry.ParamCheck := true;
  qry.Transaction := Trans;


  if NOT w then
    begin
      Connected := false;
      exit;
    end;

  try
    WES.Connected := true;
    if WES.Connected then
       begin

       end
    else
       begin
         w := false;
       end;

  except
    WES.Connected := false;
    ShowMessage('Соединиться с БД не удалось');
  end;

  Connected := w;
end;

procedure Tkkotch.Disconnect;
begin
  Connected := false;
  if WES.Connected then WES.Connected := false;
end;


end.
