Data.pas - master - harryd61/cub

Data.pas
859 строк33 KB
harryd61
Start ignoring
07 авг 2024, 16:21
07 авг 2024, 16:212e2e628
unit Data;

interface

uses
  SysUtils, Classes, DB, ADODB, BGH, Cport, ComObj, Variants ;

type
  TDataModule1 = class(TDataModule)
    con1: TADOConnection;
    qry1: TADOQuery;
    qry2: TADOQuery;
    qry3: TADOQuery;
    qry4: TADOQuery;
    qry5: TADOQuery;
    cubeport:TComPort;
    scanport: TComPort;

       procedure connect();
       procedure RefreshData(Userid:integer);
       procedure ExportToCSV(FilePathForAxapta,FilePathForOracle:string;UserId:Integer);
       procedure fillArtikel(kodAx,kodOR,NameArtikul,KodSupplier,Namesupplier,Barcode:string);
       procedure DataClose();
       function UpdateDataInDB(path , nameuser :string; userId:integer):Integer;
       function UpdateDataInDBForXLS(XLSpath , nameuser :string; userId:integer):Integer;
       function CheckLogin(kod:Integer;pass:string; var fullname:string; var company:string):Boolean;
       function Poisk(fbarcode,fkod,fName,fkodPost,fNamePost:string; Userid:integer):Integer;
       function PoiskPoBarcode(fbarcode:string; userid : integer):Integer;
       function Save(s:TBGH;idUser,compname:string; userid:integer):Integer;
       function DeletePos(id:integer):integer;
    procedure DataModuleCreate(Sender: TObject);
    procedure scanportRxChar(Sender: TObject; Count: Integer);
   private
    { Private declarations }
  public
    { Public declarations }

  end;

  TCubiscan = class
   private
    fCubLength:Integer;
    fCubWidth:Integer;
    fCubHeight:integer;
    fCubWeight:integer;
    fCubeFlag:Boolean;

    function  GetCubLength:integer;
    function  GetCubWidth:integer;
    function  GetCubHeight:integer;
    function  GetCubWeight:integer;

   public
    property CubLength:Integer read GetCubLength write fCubLength;
    property CubWidth:Integer read GetCubWidth write fCubWidth;
    property CubHeight:Integer read GetCubHeight write fCubHeight;
    property CubWeight:Integer read GetCubWeight write fCubWeight;
    property CubeFlag:boolean  read fCubeFlag write fCubeFlag;

    procedure GetDataFromCubiscan(CubikiFlag:boolean);
    procedure ChangeBGH(var s:string; cubikiFlag:boolean);

    constructor  create();


  end;

  TVGH = class
     private
        fKodOr : string;
        fName : string;
        fKodSuplaer : string;
        fNameSuplaer : string;
        fVPE : string;
        fBarcode : TStringList;
        fBarcodeCount : integer;
        fWidth : string;
        fLength : string;
        fHeight : string;
        fBrutto : string;

        function GetVPE:string;
        procedure setKodOr(s:string);

      public
         property kodOr : string read fKodOr write  setKodOr ;
         property ArtName : string read fName write  fName ;
         property kodSuplaer : string read fKodSuplaer write  fKodSuplaer ;
         property NameSuplaer : string read fNameSuplaer write  fNameSuplaer ;
         property VPE : string read GetVPE write  fVPE ;
         property BarcodeCount: integer read fBarcodeCount write fBarcodeCount ;
         property Width : string read fWidth write  fWidth ;
         property Lenght : string read fLength write  fLength ;
         property Height : string read fHeight write  fHeight ;
         property Brutto : string read fBrutto write  fBrutto ;

         constructor Create();
         procedure Reset();
         procedure SetBarcode(s:string);
         function  GetBarcode(index:integer):string;
  end;

  TPoiskPoPostavshiku = class
   private
    FVopross:boolean;
    FPoiskPoPostavshiku : boolean;
    FKodPostavsika : string;
    FNamePostavsika : string;

   public
     property Vopros : boolean read FVopross write FVopross;
     property PoiskPoPostavshiku:boolean read FPoiskPoPostavshiku  write FPoiskPoPostavshiku;
     property kodPostavsika : string read fKodPostavsika write fKodPostavsika;
     property NamePostavsika : string read FNamePostavsika write FNamePostavsika;
     procedure setPostavshik(kod,name:string);
     procedure resetPoisk();
  end;

var    DataModule1: TDataModule1;
       cube: TCubiscan;
       PoiskPoPostavshiku:TPoiskPoPostavshiku;
implementation
{$R *.dfm}
uses logTofile,ID,
     Dialogs
     ,Main;


procedure TDataModule1.ExportToCSV(FilePathForAxapta,FilePathForOracle:string;UserId:Integer);
var ExportFileAx,ExportFileOr:TFile;
    StrHeadAx,StrHeadOr,StrBodyAx,StrBodyOR,str : string;
    CARTONSPERLAYER, LAYERPERPALLET, CARTONSPERPALLET, APA_ALIBIID : string;
begin
  if not con1.Connected then connect();

  with qry5 do begin
  sql.clear();
  parameters.AddParameter.Name := 'ID';
  Parameters.ParamByName('ID').Value := UserId;
  sql.text := 'Exec exportToErp @UserId = :ID';
  end;

  try
  qry5.Open();
  except
    on E:Exception do begin
     logManager.WriteStrAndShow(E.ClassName+' ������ � ���������� : '+E.Message);
     Exit;
    end;
  end;
  if qry5.RecordCount > 0  then  begin
    ExportFileAx:= TFile.create(FilePathForAxapta);
    ExportFileOr:= TFile.create(FilePathForOracle);

    StrHeadAx:='ARTICLECODE;ARTICLENAME;VPE;ADDITIONALFIELD1;ADDITIONALFIELD2;BARCODE1;BARCODE2;BARCODE3;BARCODE4;BARCODE5;';
    StrHeadAx:=StrHeadAx+'CARTONSPERLAYER;LAYERPERPALLET;CARTONSPERPALLET;APA_DATE;APA_TIME;SOURCE;APA_ALIBIID;BARCODE;LENGTH_GROSS;WIDTH_GROSS;HEIGHT_GROSS;WEIGHT_GROSS;PIECES;CUBEVOL_GROSS;APA_TIMESTAMP';
    StrHeadOr:='CODE_CRYSTALL;'+StrHeadAx;

    ExportFileAx.AddString(StrHeadAx);
    ExportFileOr.AddString(StrHeadOr);

    qry5.First;
      while not qry5.Eof do begin
        with qry5 do begin
        str:='';
        if FieldByName('ARTICLECODEAX').AsString = '' then str:=str+FieldByName('ARTICLECODEOR').AsString+';'
        else str:=str+FieldByName('ARTICLECODEAX').AsString+';';
        str:=str+FieldByName('ARTICLENAME').AsString+';';

        //� ����� � ����� �������� ����� � ���� ������� �������

        CARTONSPERLAYER := '1';
        LAYERPERPALLET := '1';
        CARTONSPERPALLET := FieldByName('PIECES_BOX').AsString;
        if StrToInt(CARTONSPERPALLET) = 0 then CARTONSPERPALLET := '1';
        APA_ALIBIID  := FieldByName('PIECES_INBOX').AsString;
        if strToInt(APA_ALIBIID) = 0 then APA_ALIBIID := '1';


        ///���� ����� ��� ��� ������


        if FieldByName('CUBEVOL_PIECE').AsInteger > 0 then begin
           StrBodyAx:='';
           StrBodyOR:='';
           StrBodyAx:=StrBodyAx+'1;';
           StrBodyAx:=StrBodyAx + FieldByName('ADDITIONALFIELD1').AsString+';';
           StrBodyAx:=StrBodyAx + FieldByName('ADDITIONALFIELD2').AsString+';';
           StrBodyAx:=StrBodyAx + FieldByName('BARCODE1').AsString+';';
           StrBodyAx:=StrBodyAx + ';;;;';
           StrBodyAx:=StrBodyAx + CARTONSPERLAYER+';';
           StrBodyAx:=StrBodyAx + LAYERPERPALLET+';';
           StrBodyAx:=StrBodyAx + CARTONSPERPALLET+';';
           StrBodyAx:=StrBodyAx + FormatDateTime('ddmmyyyy',FieldByName('TS_STAMP').AsDateTime)+';';
           StrBodyAx:=StrBodyAx + FormatDateTime('hhnnss',FieldByName('TS_STAMP').AsDateTime)+';';
           StrBodyAx:=StrBodyAx + 'CUBI;';
           StrBodyAx:=StrBodyAx + APA_ALIBIID+';';
           if FieldByName('BARCODE').AsString = '' then  StrBodyAx:=StrBodyAx + FieldByName('BARCODE1').AsString+';'
             else StrBodyAx:=StrBodyAx + FieldByName('BARCODE').AsString+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('LENGTH_PIECE').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('WIDTH_PIECE').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('HEIGHT_PIECE').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('WEIGHT_PIECE').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FieldByName('PIECES_PIECE').AsString +';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('CUBEVOL_PIECE').AsInteger /1000)+';';
           StrBodyAx:=StrBodyAx + FormatDateTime('dd.mm.yyyy hh:nn:ss',Now)+';';
         //ExportFileAx.AddString(Str + StrBodyAx);

       //  CARTONSPERLAYER	int	������� � ����	          ������������	������� ���� � ����
       //   LAYERPERPALLET	int	���-�� ����� �� �������	  ������������	������� ����� �� �������
       // CARTONSPERPALLET	int	���-�� ������� �� �������	������������	������� ���� � �����



           StrBodyOR:=StrBodyOR+'1;';
           StrBodyOR:=StrBodyOR + FieldByName('ADDITIONALFIELD1').AsString+';';
           StrBodyOR:=StrBodyOR + FieldByName('ADDITIONALFIELD2').AsString+';';
           StrBodyOR:=StrBodyOR + FieldByName('BARCODE1').AsString+';';
           StrBodyOR:=StrBodyOR + ';;;;';
           StrBodyOR:=StrBodyOR + CARTONSPERLAYER+';';
           StrBodyOR:=StrBodyOR + LAYERPERPALLET+';';
           StrBodyOR:=StrBodyOR + CARTONSPERPALLET+';';
           StrBodyOR:=StrBodyOR + FormatDateTime('ddmmyyyy',FieldByName('TS_STAMP').AsDateTime)+';';
           StrBodyOR:=StrBodyOR + FormatDateTime('hhnnss',FieldByName('TS_STAMP').AsDateTime)+';';
           StrBodyOR:=StrBodyOR + 'CUBI;';
           StrBodyOR:=StrBodyOR + APA_ALIBIID+';';
           if FieldByName('BARCODE').AsString = '' then  StrBodyOR:=StrBodyOR + FieldByName('BARCODE1').AsString+';'
            else StrBodyOR:=StrBodyOR + FieldByName('BARCODE').AsString+';';
           StrBodyOR:=StrBodyOR + FloatToStr(FieldByName('LENGTH_PIECE').AsInteger / 1000)+';';
           StrBodyOR:=StrBodyOR + FloatToStr(FieldByName('WIDTH_PIECE').AsInteger / 1000)+';';
           StrBodyOR:=StrBodyOR + FloatToStr(FieldByName('HEIGHT_PIECE').AsInteger / 1000)+';';
           StrBodyOR:=StrBodyOR + FloatToStr(FieldByName('WEIGHT_PIECE').AsInteger / 1000)+';';
           StrBodyOR:=StrBodyOR + FieldByName('PIECES_PIECE').AsString +';';
           StrBodyOR:=StrBodyOR + FloatToStr(FieldByName('CUBEVOL_PIECE').AsInteger /1000)+';';
           StrBodyOR:=StrBodyOR + FormatDateTime('dd.mm.yyyy hh:nn:ss',Now)+';';
         //ExportFileOr.AddString(qry5.FieldByName('ARTICLECODEOR').AsString+';' + Str + StrBodyOR);
        end;

        if FieldByName('PIECES_BOX').AsInteger > 0 then begin
           StrBodyAx:='';
           StrBodyAx:=StrBodyAx+'2;';
           StrBodyAx:=StrBodyAx + FieldByName('ADDITIONALFIELD1').AsString+';';
           StrBodyAx:=StrBodyAx + FieldByName('ADDITIONALFIELD2').AsString+';';
           StrBodyAx:=StrBodyAx + FieldByName('BARCODE3').AsString+';';
           StrBodyAx:=StrBodyAx + ';;;;';
           StrBodyAx:=StrBodyAx + CARTONSPERLAYER+';';
           StrBodyAx:=StrBodyAx + LAYERPERPALLET+';';
           StrBodyAx:=StrBodyAx + CARTONSPERPALLET+';';
           StrBodyAx:=StrBodyAx + FormatDateTime('ddmmyyyy',FieldByName('TS_STAMP').AsDateTime)+';';
           StrBodyAx:=StrBodyAx + FormatDateTime('hhnnss',FieldByName('TS_STAMP').AsDateTime)+';';
           StrBodyAx:=StrBodyAx + 'CUBI;';
           StrBodyAx:=StrBodyAx + APA_ALIBIID+';';
           StrBodyAx:=StrBodyAx + FieldByName('BARCODE').AsString+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('LENGTH_BOX').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('WIDTH_BOX').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('HEIGHT_BOX').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('WEIGHT_BOX').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FieldByName('PIECES_BOX').AsString+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('CUBEVOL_BOX').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FormatDateTime('dd.mm.yyyy hh:nn:ss',Now)+';';

         ExportFileAx.AddString(Str + StrBodyAx);
         ExportFileOr.AddString(qry5.FieldByName('ARTICLECODEOR').AsString+';' + Str + StrBodyAx);
        end;

        if FieldByName('CUBEVOL_INBOX').AsInteger > 0 then begin
           StrBodyAx:='';
           StrBodyAx:=StrBodyAx+'3;';
           StrBodyAx:=StrBodyAx + FieldByName('ADDITIONALFIELD1').AsString+';';
           StrBodyAx:=StrBodyAx + FieldByName('ADDITIONALFIELD2').AsString+';';
           StrBodyAx:=StrBodyAx + FieldByName('BARCODE2').AsString+';';
           StrBodyAx:=StrBodyAx + ';;;;';
           StrBodyAx:=StrBodyAx + CARTONSPERLAYER+';';
           StrBodyAx:=StrBodyAx + LAYERPERPALLET+';';
           StrBodyAx:=StrBodyAx + CARTONSPERPALLET+';';
           StrBodyAx:=StrBodyAx + FormatDateTime('ddmmyyyy',FieldByName('TS_STAMP').AsDateTime)+';';
           StrBodyAx:=StrBodyAx + FormatDateTime('hhnnss',FieldByName('TS_STAMP').AsDateTime)+';';
           StrBodyAx:=StrBodyAx + 'CUBI;';
           StrBodyAx:=StrBodyAx + APA_ALIBIID+';';
           StrBodyAx:=StrBodyAx + FieldByName('BARCODE').AsString+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('LENGTH_INBOX').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('WIDTH_INBOX').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('HEIGHT_INBOX').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('WEIGHT_INBOX').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FieldByName('PIECES_INBOX').AsString+';';
           StrBodyAx:=StrBodyAx + FloatToStr(FieldByName('CUBEVOL_INBOX').AsInteger / 1000)+';';
           StrBodyAx:=StrBodyAx + FormatDateTime('dd.mm.yyyy hh:nn:ss',Now)+';';

         ExportFileAx.AddString(Str + StrBodyAx);
         ExportFileOr.AddString(qry5.FieldByName('ARTICLECODEOR').AsString+';' + Str + StrBodyAx);
        end;
          Edit;
          FieldByName('Export').AsInteger:=1;
          Post;
         Next();
       end;

      end;
     logManager.WriteStrAndShow('��� ������ ��������������...');
  end;

end;

procedure TDataModule1.connect;
begin
   try
    con1.Connected:=True;
  except
     on E : Exception do begin
       logManager.WriteStrAndShow(E.ClassName+' ������ � ���������� : '+E.Message);
     end;
  end;
end;


procedure TDataModule1.DataClose;
begin
  if con1.Connected then con1.Close();

end;

procedure TDataModule1.DataModuleCreate(Sender: TObject);
begin
  //������������� ����� ��� ���������
  if StrToBool(Fini.ReadString('����� ���������','�����������_���������','False')) then begin
      cubeport.Port:= FIni.ReadString('ComPort1','ComPort',cubeport.Port);
      cubeport.BaudRate:= strToBaudRate(FIni.ReadString('ComPort1','BaudRate',BaudRateToStr(cubeport.BaudRate)));
      cubeport.DataBits:= strToDataBits(FIni.ReadString('ComPort1','DataBits',DataBitsToStr(cubeport.DataBits)));
      cubeport.FlowControl.FlowControl:=StrToFlowControl(FIni.ReadString('ComPort1','FlowControl',FlowControlToStr(cubeport.FlowControl.FlowControl)));
  end;
  if StrToBool(Fini.ReadString('����� ���������','�����������_com-�������','False')) then begin
      if not DataModule1.scanport.Connected  then
      try
       DataModule1.scanport.open();
      except
        on e:Exception do logManager.WriteStrAndShow(e.Message);
      end;

      scanport.Port:= FIni.ReadString('ComPort2','ComPort',scanport.Port);
      scanport.BaudRate:= strToBaudRate(FIni.ReadString('ComPort2','BaudRate',BaudRateToStr(scanport.BaudRate)));
      scanport.DataBits:= strToDataBits(FIni.ReadString('ComPort2','DataBits',DataBitsToStr(scanport.DataBits)));
      scanport.FlowControl.FlowControl:=StrToFlowControl(FIni.ReadString('ComPort2','FlowControl',FlowControlToStr(scanport.FlowControl.FlowControl)));
  end;

  //������������� ������� �� ���� ����������
  PoiskPoPostavshiku := TPoiskPoPostavshiku.Create();

end;

procedure TDataModule1.RefreshData(userid:integer);
begin
 if not con1.Connected then connect();
 with qry4 do begin
   SQL.Clear;
   Parameters.AddParameter.Name:='id';
   Parameters.ParamByName('id').Value:=Userid;
   SQL.Add('Exec RefreshData @Userid = :id');
   try
     qry4.Open();
   except
     on E:Exception do begin
        logManager.Write(E.ClassName+' ������ � ���������� : '+E.Message);
     end;
   end;
 end;
end;

function TDataModule1.CheckLogin(kod:Integer; pass:string; var fullname:string;var company:string):Boolean;
begin
  Result:=False;
  if not con1.Connected then connect;
  with qry1 do begin
   SQL.Clear();
   Parameters.Clear();
   Parameters.AddParameter.Name:='id';
   Parameters.AddParameter.Name:='pass';
   Parameters.ParamByName('id').Value:=IntToStr(kod);
   Parameters.ParamByName('pass').Value:=pass;
   SQL.Add('Exec identy @id= :id, @pass= :pass');
   try
    qry1.Open();
    if qry1.RecordCount = 1 then  begin
      qry1.First();
      fullname:=qry1.FieldByName('name').Value +' '+qry1.FieldByName('Oname').Value +' '+
                      qry1.FieldByName('Fname').Value;
      company:=qry1.FieldByName('company').Value;
      Result:=True;
    end;
   except
    on E: EDatabaseError do begin
      logManager.WriteStrAndShow(E.ClassName+' ������ � ���������� : '+E.Message);
    end;
   end;
  end;
end;

procedure TDataModule1.fillArtikel(kodAx,kodOR,NameArtikul,KodSupplier,Namesupplier,Barcode:string);
begin
     //���������� ������� �������.
  BGH1.Artikel.KodAx:=KodAx;
  BGH1.Artikel.KodOracle:=KodOr;
  BGH1.Artikel.NameArtikel:=NameArtikul;
  BGH1.Artikel.KodSupplier:=KodSupplier;
  BGH1.Artikel.NameSupplier:=NameSupplier;
  BGH1.Artikel.putArtikelBarcode(Barcode);
end;

function TDataModule1.Poisk(fbarcode,fkod,fName,fkodPost,fNamePost:string; Userid:integer):Integer;
 begin
  Result:=0;
  if not con1.Connected then connect;
  with qry2 do begin
   SQL.Clear();
   Parameters.Clear();
   Parameters.AddParameter.Name:='barcode';
   Parameters.AddParameter.Name:='artikel';
   Parameters.AddParameter.Name:='artikelName';
   Parameters.AddParameter.Name:='kodPost';
   Parameters.AddParameter.Name:='namePost';
   Parameters.AddParameter.Name:='id';
   Parameters.ParamByName('barcode').Value:=fbarcode;
   Parameters.ParamByName('artikel').Value:=fkod;
   Parameters.ParamByName('artikelName').Value:=fName;

   if PoiskPoPostavshiku.PoiskPoPostavshiku then begin
     fkodPost := PoiskPoPostavshiku.kodPostavsika;
   end;
   Parameters.ParamByName('kodPost').Value:=fkodPost;
   Parameters.ParamByName('namePost').Value:=fNamePost;
   Parameters.ParamByName('id').Value:=IntTostr(UserId);

   SQL.text := 'Exec SearchPoz @SearchBarcode=:barcode, @Searchkod=:artikel, @SearchArtName=:artikelName, '+
                    '@SearchKodSupplaer=:kodPost, @SearchNameSupplaer=:namePost, @UserId = :id ' ;
    //ShowMessage(SQL.text);

   try
    qry2.Open();
    Result:=qry2.RecordCount;
   except
     On e : EDatabaseError do begin
       logManager.WriteStrAndShow(E.ClassName+' ������ � ���������� : '+E.Message);
     end;
   end;

   if Result = 1  then begin
      fillArtikel( fieldByName('ARTICLECODEAX').AsString,
                                    fieldByName('ARTICLECODEOR').AsString,
                                    fieldByName('ARTICLENAME').AsString,
                                    fieldByName('ADDITIONALFIELD1').AsString,
                                    fieldByName('ADDITIONALFIELD2').AsString,
                                    fieldByName('BARCODE').AsString
                                  );
   end;
  end;
 end;

function TDataModule1.PoiskPoBarcode(fbarcode:string; userid : integer):Integer;
begin
  //����� � ���� �� ���������.
  Result := Poisk(fbarcode,'','','','',userid);
end;

function TDataModule1.Save(s:TBGH;idUser,compname:string; userid:integer): Integer;
begin
Result := -1;

if not con1.Connected then connect;
 with qry3 do begin
   SQL.Clear;
   Parameters.Clear;
   Parameters.AddParameter.Name:='ARTICLECODEAX'; Parameters.ParamByName('ARTICLECODEAX').Value:=s.Artikel.KodAx;
   Parameters.AddParameter.Name:='ARTICLECODEOR'; Parameters.ParamByName('ARTICLECODEOR').Value:=s.Artikel.KodOracle;
   Parameters.AddParameter.Name:='ARTICLENAME'; Parameters.ParamByName('ARTICLENAME').Value:=s.Artikel.NameArtikel;
   Parameters.AddParameter.Name:='ADDITIONALFIELD1'; Parameters.ParamByName('ADDITIONALFIELD1').Value:=s.Artikel.KodSupplier;
   Parameters.AddParameter.Name:='ADDITIONALFIELD2'; Parameters.ParamByName('ADDITIONALFIELD2').Value:=s.Artikel.NameSupplier;
   Parameters.AddParameter.Name:='BARCODE'; Parameters.ParamByName('BARCODE').Value:=s.Artikel.getArtikelBarcode;
   Parameters.AddParameter.Name:='BARCODE1'; Parameters.ParamByName('BARCODE1').Value:=s.Piece.getBarcode;
   Parameters.AddParameter.Name:='BARCODE2'; Parameters.ParamByName('BARCODE2').Value:=s.Inbox.getBarcode;
   Parameters.AddParameter.Name:='BARCODE3'; Parameters.ParamByName('BARCODE3').Value:=s.Box.getBarcode;
   Parameters.AddParameter.Name:='CARTONSPERLAYER'; Parameters.ParamByName('CARTONSPERLAYER').Value:=IntToStr(s.Box.getboxInLayer);
   Parameters.AddParameter.Name:='LAYERPERPALLET'; Parameters.ParamByName('LAYERPERPALLET').Value:=IntToStr(s.Box.getcountLayer);
   Parameters.AddParameter.Name:='CARTONSPERPALLET'; Parameters.ParamByName('CARTONSPERPALLET').Value:=IntToStr(s.CountPiecesInPallete);
   Parameters.AddParameter.Name:='LENGTH_PIECE'; Parameters.ParamByName('LENGTH_PIECE').Value:=IntToStr(s.Piece.getLength);
   Parameters.AddParameter.Name:='WIDTH_PIECE'; Parameters.ParamByName('WIDTH_PIECE').Value:=IntToStr(s.Piece.getWidth);
   Parameters.AddParameter.Name:='HEIGHT_PIECE'; Parameters.ParamByName('HEIGHT_PIECE').Value:=IntToStr(s.Piece.getHeight);
   Parameters.AddParameter.Name:='WEIGHT_PIECE'; Parameters.ParamByName('WEIGHT_PIECE').Value:=IntToStr(s.Piece.getWeight);
   Parameters.AddParameter.Name:='CUBEVOL_PIECE'; Parameters.ParamByName('CUBEVOL_PIECE').Value:=IntToStr(s.Piece.getVolume);
   Parameters.AddParameter.Name:='LENGTH_INBOX'; Parameters.ParamByName('LENGTH_INBOX').Value:=IntToStr(s.Inbox.getLength);
   Parameters.AddParameter.Name:='WIDTH_INBOX'; Parameters.ParamByName('WIDTH_INBOX').Value:=IntToStr(s.Inbox.getWidth);
   Parameters.AddParameter.Name:='HEIGHT_INBOX'; Parameters.ParamByName('HEIGHT_INBOX').Value:=s.Inbox.getHeight;
   Parameters.AddParameter.Name:='WEIGHT_INBOX'; Parameters.ParamByName('WEIGHT_INBOX').Value:=s.Inbox.getWeight;
   Parameters.AddParameter.Name:='PIECES_INBOX'; Parameters.ParamByName('PIECES_INBOX').Value:=s.Inbox.getPiecesInbox;
   Parameters.AddParameter.Name:='CUBEVOL_INBOX'; Parameters.ParamByName('CUBEVOL_INBOX').Value:=s.Inbox.getVolume;
   Parameters.AddParameter.Name:='LENGTH_BOX'; Parameters.ParamByName('LENGTH_BOX').Value:=s.Box.getLength;
   Parameters.AddParameter.Name:='WIDTH_BOX'; Parameters.ParamByName('WIDTH_BOX').Value:=s.Box.getWidth;
   Parameters.AddParameter.Name:='HEIGHT_BOX'; Parameters.ParamByName('HEIGHT_BOX').Value:=s.Box.getHeight;
   Parameters.AddParameter.Name:='WEIGHT_BOX'; Parameters.ParamByName('WEIGHT_BOX').Value:=s.Box.getWeight;
   Parameters.AddParameter.Name:='CUBEVOL_BOX'; Parameters.ParamByName('CUBEVOL_BOX').Value:=s.Box.getVolume;
   Parameters.AddParameter.Name:='PIECES_BOX'; Parameters.ParamByName('PIECES_BOX').Value:=s.Box.getPiecesInbox;
   Parameters.AddParameter.Name:='USER_STAMP'; Parameters.ParamByName('USER_STAMP').Value:=idUser;
   Parameters.AddParameter.Name:='COMP_STAMP'; Parameters.ParamByName('COMP_STAMP').Value:=compname ;
   Parameters.AddParameter.Name:='id'; Parameters.ParamByName('id').Value:=UserId;



   SQL.Add('Exec savedata @ARTICLECODEAX=:ARTICLECODEAX,@ARTICLECODEOR=:ARTICLECODEOR,@ARTICLENAME=:ARTICLENAME,@ADDITIONALFIELD1=:ADDITIONALFIELD1,' +
              '@ADDITIONALFIELD2=:ADDITIONALFIELD2,@BARCODE=:BARCODE,' +
              '@BARCODE1=:BARCODE1, @BARCODE2=:BARCODE2, @BARCODE3=:BARCODE3,@CARTONSPERLAYER=:CARTONSPERLAYER,' +
              '@LAYERPERPALLET=:LAYERPERPALLET,@CARTONSPERPALLET=:CARTONSPERPALLET,@LENGTH_PIECE=:LENGTH_PIECE,' +
              '@WIDTH_PIECE=:WIDTH_PIECE,@HEIGHT_PIECE=:HEIGHT_PIECE,@WEIGHT_PIECE=:WEIGHT_PIECE, @PIECES_PIECE=1,@CUBEVOL_PIECE=:CUBEVOL_PIECE,' +
              '@LENGTH_INBOX=:LENGTH_INBOX,@WIDTH_INBOX=:WIDTH_INBOX,@HEIGHT_INBOX=:HEIGHT_INBOX,@WEIGHT_INBOX=:WEIGHT_INBOX,' +
              '@PIECES_INBOX=:PIECES_INBOX,@CUBEVOL_INBOX=:CUBEVOL_INBOX,@LENGTH_BOX=:LENGTH_BOX,@WIDTH_BOX=:WIDTH_BOX,' +
              '@HEIGHT_BOX=:HEIGHT_BOX,@WEIGHT_BOX=:WEIGHT_BOX,@CUBEVOL_BOX=:CUBEVOL_BOX,@PIECES_BOX=:PIECES_BOX,' +
              '@USER_STAMP=:USER_STAMP,@COMP_STAMP=:COMP_STAMP, @UserId = :id');
 end;

  Result:=qry3.ExecSQL();
end;



function TDataModule1.UpdateDataInDB(path , nameuser: string; userId:integer):Integer;
begin
 Result := 0;
 //���������� ��
 if not con1.Connected then connect;
 with qry1 do begin
   SQL.Clear;
   Parameters.Clear;
   Parameters.AddParameter.Name:='path';
   Parameters.AddParameter.Name:='name';
   Parameters.AddParameter.Name:='id';
   Parameters.ParamByName('path').Value:=path;
   Parameters.ParamByName('name').Value:=nameuser;
   Parameters.ParamByName('id').Value:=userId;

   SQL.Add('Exec ImportDataFromFileOld @path=:path, @UserId = :id');
   //SQL.Add('Exec ImportDataFromFile @path=:path , @user=:name');
   try
    Result:=qry1.ExecSQL();
   except
     On e : EDatabaseError do begin
       logManager.WriteStrAndShow(E.ClassName+' ������ � ���������� : '+E.Message);
     end;
   end;
 end;
end;

 //���������� �� ����� xls
function TDataModule1.UpdateDataInDBForXLS(XLSpath , nameuser: string; userId:integer):Integer;
const xlLastCell = $0000000B;
var xlApp, xlSheet : OLEVariant;
    vgh : TVGH;
    xlRow, xlCol, j, i, k ,InsertCount : integer;
begin
  Result:=0;
  vgh := TVGH.Create;
  //������ �� ����� xls
  xlApp := CreateOleObject('Excel.Application');
  xlApp.Visible := false;
  xlApp.Workbooks.open(XLSpath);
  xlSheet := xlApp.Workbooks[ExtractFileName(XLSpath)].WorkSheets[1];
  xlSheet.Cells.SpecialCells(xlLastCell).Activate;
  xlRow := xlApp.ActiveCell.Row;
  xlCol := xlApp.ActiveCell.Column;

  if not con1.Connected then connect;

  //���������� ������� VGH ������� �� xls �����;
  for i := 7 to xlRow  do begin
    for j := 1 to xlCol - 1 do begin
      case j of
        1: begin vgh.kodOr := xlSheet.Cells[i,j] end;
        4: begin vgh.ArtName := xlSheet.Cells[i,j] end;
        7: begin vgh.kodSuplaer := xlSheet.Cells[i,j] end;
        8: begin vgh.NameSuplaer := xlSheet.Cells[i,j] end;
        9: begin vgh.VPE := xlSheet.Cells[i,j] end;
        10: begin vgh.SetBarcode(xlSheet.Cells[i,j]) end;
        11: begin vgh.Width := xlSheet.Cells[i,j] end;
        12: begin vgh.Lenght := xlSheet.Cells[i,j] end;
        13: begin vgh.Height := xlSheet.Cells[i,j] end;
        14: begin vgh.Brutto := xlSheet.Cells[i,j] end;
      end;
    end;

    //��������� ������� ���� ����� ����� ��������� ����������, ��� ������� ���������;
    for k := 0 to vgh.BarcodeCount - 1 do begin
     //������� � ���� ;
    with qry1 do begin
     SQL.Clear;
     Parameters.Clear;
     Parameters.AddParameter.Name:='name';  Parameters.ParamByName('name').Value:=nameuser;
     Parameters.AddParameter.Name:='id';    Parameters.ParamByName('id').Value:=userId;
     Parameters.AddParameter.Name:='kodOr';  Parameters.ParamByName('kodOr').Value:=vgh.kodOr;
     Parameters.AddParameter.Name:='ArtName';  Parameters.ParamByName('ArtName').Value:=vgh.ArtName;
     Parameters.AddParameter.Name:='kodSuplaer';  Parameters.ParamByName('kodSuplaer').Value:=vgh.kodSuplaer;
     Parameters.AddParameter.Name:='NameSuplaer';  Parameters.ParamByName('NameSuplaer').Value:=vgh.NameSuplaer;
     Parameters.AddParameter.Name:='VPE';  Parameters.ParamByName('VPE').Value:=vgh.VPE;
     Parameters.AddParameter.Name:='Barcode';  Parameters.ParamByName('Barcode').Value:=vgh.GetBarcode(k);
     Parameters.AddParameter.Name:='Width';  Parameters.ParamByName('Width').Value:=vgh.Width;
     Parameters.AddParameter.Name:='Lenght';  Parameters.ParamByName('Lenght').Value:=vgh.Lenght;
     Parameters.AddParameter.Name:='Height';  Parameters.ParamByName('Height').Value:=vgh.Height;
     Parameters.AddParameter.Name:='Brutto';  Parameters.ParamByName('Brutto').Value:=vgh.Brutto;


     SQL.Text := 'Exec ImportDataFromXLS @name=:name, @id=:id, @kodOr=:kodOr, @ArtName=:ArtName, @kodSuplaer=:kodSuplaer, '+
                   '@NameSuplaer = :NameSuplaer, @VPE=:VPE,@Barcode=:Barcode, @Width=:Width, @Lenght=:Lenght, @Height=:Height, '+
                   '@Brutto=:Brutto';

     try
      InsertCount:=qry1.ExecSQL();
     except
       On e : EDatabaseError do begin
         logManager.WriteStrAndShow(E.ClassName+' ������ � ���������� : '+E.Message);
       end;
      end;
    end;
   end;
    InsertCount:=0;
    vgh.Reset();
    Result := Result + InsertCount;
  end;

  vgh.Free();
  xlApp.Quit;
  xlApp := Unassigned;
  xlSheet := Unassigned;
end;

function TDataModule1.DeletePos(id:integer):integer;
begin
 if not con1.Connected then connect;
 with qry3 do begin
   Result:=0;
   SQL.Clear;
   Parameters.Clear;
   Parameters.AddParameter.Name:='id';
   Parameters.ParamByName('id').Value:=id;

   SQL.Add('Exec deletePos @id=:id');

   try
    Result:=qry3.ExecSQL();
   except
     On e : EDatabaseError do begin
       logManager.WriteStrAndShow(E.ClassName+' ������ � ���������� : '+E.Message);
     end;
   end;
 end;

end;

procedure TDataModule1.scanportRxChar(Sender: TObject; Count: Integer);
var str:string;
    k : char;
begin
  k:=#13;
    //������������ ������ ��������
  scanport.ReadStr(str ,Count);
  str:=Copy(str,1,Length(str)-2);
  FormMane.edtArtikel.Text:=str;
  if FormMane.edtPieceBarcode.Focused then begin
         FormMane.edtPieceBarcode.text:=str;
         FormMane.edtPieceBarcodeChange(FormMane.edtPieceBarcode);
  end else if FormMane.edtInboxBarcode.Focused then begin
         FormMane.edtInboxBarcode.text:=str;
  end else if FormMane.edtBoxBarcode.Focused then begin
         FormMane.edtBoxBarcode.text:=str;
  end else FormMane.edtArtikelKeyPress(FormMane.edtArtikel,k);

  Scanport.ClearBuffer(true,true);
end;


{ TCubiscan }

constructor TCubiscan.create;
begin
 self.fCubLength:=0;
 self.fCubWidth:=0;
 self.fCubHeight:=0;
 self.fCubWeight:=0;
end;


function TCubiscan.GetCubHeight: integer;
begin
 result:= self.fCubHeight;
end;

function TCubiscan.GetCubLength: integer;
begin
  result:= self.fCubLength;
end;

function TCubiscan.GetCubWeight: integer;
begin
  result:= self.fCubWeight;
end;

function TCubiscan.GetCubWidth: integer;
begin
  result:= self.fCubWidth;
end;

procedure TCubiscan.GetDataFromCubiscan(CubikiFlag:boolean);
//��������� ��������� ������ �� ���������
const InData : string = #02#67#03#13#10;
var OutData:string;
begin
  if not DataModule1.cubeport.Connected  then
      try
       DataModule1.cubeport.open();
      except
        on e:Exception do logManager.WriteStrAndShow(e.Message);
      end;

  if not DataModule1.cubeport.Connected then begin
     DataModule1.cubeport.Port:=FIni.ReadString('ComPort1','ComPort',DataModule1.cubeport.Port);
     DataModule1.cubeport.BaudRate:=StrToBaudRate(FIni.ReadString('ComPort1','BaudRate','9600'));
     DataModule1.cubeport.DataBits:=StrToDataBits(FIni.ReadString('ComPort1','DataBits','8'));
     DataModule1.cubeport.FlowControl.FlowControl:=StrToFlowControl(FIni.ReadString('ComPort1','FlowControl','Hardware'));
     DataModule1.cubeport.Open();
  end;
    DataModule1.cubeport.WriteStr(Indata);
    Sleep(1200);
    DataModule1.cubeport.ReadStr(Outdata,256);
    DataModule1.cubeport.ClearBuffer(True,True);
    logManager.Write(OutData);
    ChangeBGH(Outdata,CubikiFlag);
end;

procedure TCubiscan.ChangeBGH(var s: string; cubikiFlag:boolean);
var sL,sW,sH,sK :string;
begin
   cube.CubLength := 0;
   cube.CubWidth := 0;
   cube.CubHeight := 0;
   cube.CubWeight := 0;
   cube.CubeFlag := cubikiFlag;

   if Pos(',L',s) > 0 then begin
     sL:=Copy(s,Pos(',L',s)+2,5);
     sL:= StringReplace(sL,'.',',',[rfReplaceAll]);
     if not (Pos('_',sL)>0) then
       if CubeFlag then Cube.CubLength := Round((strToFloat(sL)-4)*10)
          else Cube.CubLength:=Round(strToFloat(sL)*10)
     else Cube.CubLength := 0;
    end;

   if Pos(',W',s) > 0 then begin
     sW:=Copy(s,Pos(',W',s)+2,5);
     sW:= StringReplace(sW,'.',',',[rfReplaceAll]);
     if not (Pos('_',sW)>0) then
       if CubeFlag then Cube.CubWidth:=Round((StrToFloat(sW)-4)*10)
       else Cube.CubWidth:=Round(strToFloat(sW)*10)
     else Cube.CubWidth:=0;
   end;

   if Pos(',H',s) > 0 then begin
     sH:=Copy(s,Pos(',H',s)+2,5);
     sH:= StringReplace(sH,'.',',',[rfReplaceAll]);
     if not (Pos('_',sH)>0) then Cube.CubHeight:=Round(StrToFloat(sH)*10)
     else Cube.CubHeight:=0;
   end;

   if Pos(',K',s) > 0 then begin
    sK:=Copy(s,Pos(',K',s)+2,8);
    if pos(',D',sK)>0 then sK:=Copy(sK,1,Pos(',D',sK)-1);
    sK:= StringReplace(sK,'.',',',[rfReplaceAll]);
    if not (Pos('_',sK)>0) then
      if CubeFlag then Cube.CubWeight:=Round((strToFloat(sK)-60)*1000)
      else Cube.CubWeight:=Round(strToFloat(sK)*1000)
    else Cube.CubWeight:=0;
   end;
end;

{ TVGH }

constructor TVGH.Create;
begin
  fKodOr := '';
  fName := '';
  fKodSuplaer := '';
  fNameSuplaer := '';
  fVPE := '';
  fBarcodeCount := 0;
  fWidth := '';
  fLength := '';
  fHeight := '';
  fBrutto := '';
  fBarcode := TstringList.Create();
end;

procedure TVGH.Reset;
begin
  fKodOr := '';
  fName := '';
  fKodSuplaer := '';
  fNameSuplaer := '';
  fVPE := '';
  fBarcodeCount := 0;
  fWidth := '';
  fLength := '';
  fHeight := '';
  fBrutto := '';
  fBarcode.Clear();
end;

function TVGH.GetBarcode(index: integer): string;
begin
  Result := fBarcode[index];
end;

function TVGH.GetVPE: string;
begin
  if self.fVPE = 'EA' Then Result := '1'
  else if self.fVPE = 'CA' Then Result := '2'
  else if self.fVPE = 'IN' Then Result := '3'
  else Result := '0';
end;

procedure TVGH.SetBarcode(s: string);
var subStr:string;
    subPos:integer;
begin
  while Pos(',',s) > 0 do begin
    subPos := pos( ',' , s );
    subStr :=  copy(s,0, subPos - 1);
    delete(s,1, subPos+2);
    fBarcode.add(subStr);
  end;
  fBarcode.add(s);
  BarcodeCount := fBarcode.Count;
end;

procedure TVGH.setKodOr(s: string);
begin
  fKodOr := Trim(s);
end;

{ TPoiskPoPostavshiku }

procedure TPoiskPoPostavshiku.resetPoisk;
begin
  self.kodPostavsika := '';
  self.NamePostavsika := '';
  self.PoiskPoPostavshiku := false;
  self.Vopros := false;
end;

procedure TPoiskPoPostavshiku.setPostavshik(kod, name: string);
begin
  self.kodPostavsika := kod;
  self.NamePostavsika := name;
  self.PoiskPoPostavshiku := true;
end;

end.