Advertisement
Guest User

Untitled

a guest
Jan 17th, 2017
117
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Pascal 9.17 KB | None | 0 0
  1. unit Connection_u;
  2.  
  3. interface
  4.       uses
  5.        sysutils,Vcl.StdCtrls, Vcl.ExtCtrls,vcl.forms,winapi.windows,
  6.   FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf,
  7.   FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async,
  8.   FireDAC.Phys, FireDAC.Phys.FB, FireDAC.Phys.FBDef, Data.DB,
  9.   FireDAC.Comp.Client;
  10.  
  11.  
  12.  
  13. var
  14.   Fconnetion:TFDConnection;
  15.   FTransaction:TFDTransaction;
  16.  
  17.  
  18.   function ExecuteQuery(const queryStr:string):boolean;//execute query on database Err=0 its ok!
  19.   function ExecuteQueryGetId(const queryStr:string):integer;//get last id
  20.   procedure FillMarks(combo:TComboBox);
  21.   procedure FillSousFamille(combo:TComboBox);
  22.   procedure FillUnitys(combo:TComboBox);
  23.   procedure FillNomFamille(combo:Tcombobox);
  24.   function GetDEBTCLIENT(CodeClient: integer): real;
  25.   function GetDEBTFOURN(CODEFOURN: integer): real;
  26. implementation
  27.  
  28. uses Messagestring_u;
  29. function GetDEBTFOURN(CODEFOURN: integer): real;
  30.  var
  31.   query:TFDQuery;
  32. begin
  33.   result:=0;
  34.   query:=TFDQuery.Create(nil);
  35.  
  36.   try
  37.     if (not Assigned(Fconnetion)) or(not Fconnetion.Connected) then
  38.       begin
  39.         Application.MessageBox(Error_hapenNotConnected,'error',MB_OK+MB_ICONINFORMATION);
  40.        raise Exception.Create('(not Assigned(Fconnetion)) or(not Fconnetion.Connected)');
  41.  
  42.       end;
  43.  
  44.    query.Connection:=Fconnetion;
  45.    with query do
  46.    begin
  47.      sql.Clear;
  48.      sql.Add(' SELECT  coalesce(a.DEBTFOURN,0) as DEBTFOURN  FROM TBLFOURNISSEUR a ');
  49.      SQL.Add(' where a.CODEFOURN='+IntToStr(CODEFOURN));
  50.      open;
  51.    end;
  52.    if query.RecordCount>0 then
  53.     begin
  54.      query.First;
  55.      result:=query.FieldByName('DEBTFOURN').AsFloat;
  56.  
  57.     end;
  58.   finally
  59.     FreeAndNil(Query);
  60.   end;
  61.  
  62. end;
  63.  
  64. //---------------------------------------------
  65.  function GetDEBTCLIENT(CodeClient: integer): real;
  66.    var
  67.   query:TFDQuery;
  68. begin
  69.   result:=0;
  70.   query:=TFDQuery.Create(nil);
  71.  
  72.   try
  73.     if (not Assigned(Fconnetion)) or(not Fconnetion.Connected) then
  74.       begin
  75.         Application.MessageBox(Error_hapenNotConnected,'error',MB_OK+MB_ICONINFORMATION);
  76.        raise Exception.Create('(not Assigned(Fconnetion)) or(not Fconnetion.Connected)');
  77.  
  78.       end;
  79.  
  80.    query.Connection:=Fconnetion;
  81.    with query do
  82.    begin
  83.      sql.Clear;
  84.      sql.Add(' SELECT coalesce(a.DEBTCLIENT,0) as DEBTCLIENT  FROM TBLCLIENT a ');
  85.      SQL.Add(' where a.CODECLIENT='+IntToStr(CodeClient));
  86.      open;
  87.    end;
  88.    if query.RecordCount>0 then
  89.     begin
  90.      query.First;
  91.      result:=query.FieldByName('DEBTCLIENT').AsFloat;
  92.  
  93.     end;
  94.   finally
  95.     FreeAndNil(Query);
  96.   end;
  97.  
  98. end;
  99.  
  100. //------------------------------------------------------------------
  101.   procedure FillNomFamille(combo:Tcombobox);
  102.  
  103.  var
  104.   query:TFDQuery;
  105. begin
  106.   combo.Clear;
  107.   query:=TFDQuery.Create(nil);
  108.   try
  109.     if (not Assigned(Fconnetion)) or(not Fconnetion.Connected) then
  110.       begin
  111.         Application.MessageBox(Error_hapenNotConnected,'error',MB_OK+MB_ICONINFORMATION);
  112.        raise Exception.Create('(not Assigned(Fconnetion)) or(not Fconnetion.Connected)');
  113.  
  114.       end;
  115.  
  116.    query.Connection:=Fconnetion;
  117.    with query do
  118.    begin
  119.      sql.Clear;
  120.      sql.Add('SELECT a.NOMFAM FROM TBLFAMILLE a;');
  121.      open;
  122.    end;
  123.    if query.RecordCount>0 then
  124.     begin
  125.       query.First;
  126.       while not Query.Eof do
  127.       begin
  128.  
  129.         combo.Items.Add(query.FieldByName('NOMFAM').AsString);
  130.  
  131.         query.Next;
  132.       end;
  133.     end;
  134.   finally
  135.     FreeAndNil(Query);
  136.   end;
  137.  
  138. end;
  139.  
  140.  
  141. //------------------------------------------------------------------
  142.  
  143.  
  144. //------------------------------------------------------------------
  145.   procedure FillUnitys(combo:TComboBox);
  146.  var
  147.   query:TFDQuery;
  148. begin
  149.   combo.Clear;
  150.   query:=TFDQuery.Create(nil);
  151.   try
  152.     if (not Assigned(Fconnetion)) or(not Fconnetion.Connected) then
  153.       begin
  154.         Application.MessageBox(Error_hapenNotConnected,'error',MB_OK+MB_ICONINFORMATION);
  155.        raise Exception.Create('(not Assigned(Fconnetion)) or(not Fconnetion.Connected)');
  156.  
  157.       end;
  158.  
  159.    query.Connection:=Fconnetion;
  160.    with query do
  161.    begin
  162.      sql.Clear;
  163.      sql.Add('SELECT a.NAMEUNIT FROM TBLUNIT a;');
  164.      open;
  165.    end;
  166.    if query.RecordCount>0 then
  167.     begin
  168.       query.First;
  169.       while not Query.Eof do
  170.       begin
  171.  
  172.         combo.Items.Add(query.FieldByName('NAMEUNIT').AsString);
  173.  
  174.         query.Next;
  175.       end;
  176.     end;
  177.   finally
  178.     FreeAndNil(Query);
  179.   end;
  180.  
  181. end;
  182.  
  183.  
  184.  
  185.  
  186.  
  187.  
  188. //------------------------------------------------------------------
  189.  
  190.  
  191. //------------------------------------------------------------------
  192.   procedure FillMarks(combo:TComboBox);
  193.   var
  194.   query:TFDQuery;
  195. begin
  196.   combo.Clear;
  197.   query:=TFDQuery.Create(nil);
  198.   try
  199.     if (not Assigned(Fconnetion)) or(not Fconnetion.Connected) then
  200.       begin
  201.         Application.MessageBox(Error_hapenNotConnected,'error',MB_OK+MB_ICONINFORMATION);
  202.         raise Exception.Create('(not Assigned(Fconnetion)) or(not Fconnetion.Connected)');
  203.  
  204.       end;
  205.  
  206.    query.Connection:=Fconnetion;
  207.    with query do
  208.    begin
  209.      sql.Clear;
  210.      sql.Add('SELECT a.NOMMRQ FROM TBLMARQUE a;');
  211.      open;
  212.    end;
  213.    if query.RecordCount>0 then
  214.     begin
  215.       query.First;
  216.       while not Query.Eof do
  217.       begin
  218.  
  219.         combo.Items.Add(query.FieldByName('NOMMRQ').AsString);
  220.  
  221.         query.Next;
  222.       end;
  223.     end;
  224.   finally
  225.     FreeAndNil(Query);
  226.   end;
  227.  
  228. end;
  229. //------------------------------------------------------------------
  230.  
  231.  
  232. //------------------------------------------------------------------
  233.   procedure FillSousFamille(combo:TComboBox);
  234.   var
  235.   query:TFDQuery;
  236. begin
  237.   combo.Clear;
  238.   query:=TFDQuery.Create(nil);
  239.   try
  240.     if (not Assigned(Fconnetion)) or(not Fconnetion.Connected) then
  241.       begin
  242.         Application.MessageBox(Error_hapenNotConnected,'error',MB_OK+MB_ICONINFORMATION);
  243.         raise Exception.Create('(not Assigned(Fconnetion)) or(not Fconnetion.Connected)');
  244.  
  245.       end;
  246.  
  247.    query.Connection:=Fconnetion;
  248.    with query do
  249.    begin
  250.      sql.Clear;
  251.      sql.Add('SELECT  a.NOMSOUSFAM FROM TBLSOUSFAMILLE a;');
  252.      open;
  253.    end;
  254.    if query.RecordCount>0 then
  255.     begin
  256.       query.First;
  257.       while not Query.Eof do
  258.       begin
  259.  
  260.         combo.Items.Add(query.FieldByName('NOMSOUSFAM').AsString);
  261.  
  262.         query.Next;
  263.       end;
  264.     end;
  265.   finally
  266.     FreeAndNil(Query);
  267.   end;
  268.  
  269. end;
  270. //------------------------------------------------------------------
  271.  
  272.  
  273. //-----------------------------------------------------------------------------------
  274. function ExecuteQueryGetId(const queryStr:string):integer;//get last id
  275.  var
  276.   ObjQuery:TFDQuery;
  277. begin
  278.   result:=-1;
  279.   ObjQuery := TFDQuery.Create(nil);
  280.   try
  281.    try
  282.     if not Assigned(Fconnetion) then
  283.       raise Exception.Create('not Assigned(Fconnetion) ');
  284.     ObjQuery.Connection:=Fconnetion;
  285.     with ObjQuery do
  286.     begin
  287.       close;
  288.       SQL.Clear;
  289.       SQL.Add(queryStr);
  290.       open;
  291.     end;
  292.     if ObjQuery.RecordCount>0 then
  293.  
  294.     result:=ObjQuery.fields[0].AsInteger;
  295.  
  296.    except
  297.     on E:Exception do
  298.      begin
  299.       result:=-1;
  300.       Application.MessageBox(pwidechar(e.Message),'Error',mb_ok+MB_ICONINFORMATION);
  301.      end;
  302.    end;
  303.   finally
  304.     FreeAndNil(ObjQuery);
  305.   end;
  306.  
  307.  
  308.  
  309. end;
  310. //-----------------------------------------------------------------------------------
  311.  
  312.  
  313.  
  314.  
  315. //-----------------------------------------------------------------------------------
  316. function ExecuteQuery(const queryStr:string):boolean;
  317. var
  318.   ObjQuery:TFDQuery;
  319. begin
  320.   result:=false;
  321.   ObjQuery := TFDQuery.Create(nil);
  322.   try
  323.    try
  324.     if not Assigned(Fconnetion) then
  325.       raise Exception.Create('not Assigned(Fconnetion) ');
  326.     ObjQuery.Connection:=Fconnetion;
  327.     with ObjQuery do
  328.     begin
  329.       close;
  330.       SQL.Clear;
  331.       SQL.Add(queryStr);
  332.       open;
  333.     end;
  334.     if ObjQuery.RecordCount>0 then
  335.  
  336.     result:=ObjQuery.FieldByName('Err').AsInteger=0;
  337.  
  338.    except
  339.     on E:Exception do
  340.      begin
  341.       result:=false;
  342.       Application.MessageBox(pwidechar(e.Message),'Error',mb_ok+MB_ICONINFORMATION);
  343.      end;
  344.    end;
  345.   finally
  346.     FreeAndNil(ObjQuery);
  347.   end;
  348.  
  349.  
  350.  
  351. end;
  352. //-----------------------------------------------------------------------------------
  353.  
  354. initialization
  355.  
  356.  
  357.  
  358.   if not Assigned(FTransaction) then
  359.     begin
  360.       FTransaction:=TFDTransaction.Create(nil);
  361.       FTransaction.Options.AutoCommit:=True;
  362.       FTransaction.Options.AutoStart:=True;
  363.     end;
  364.   if not Assigned(Fconnetion) then
  365.   begin
  366.     Fconnetion:=TFDConnection.Create(nil);
  367.     Fconnetion.DriverName:='FB';
  368.     Fconnetion.Transaction:=FTransaction;
  369.     FTransaction.Connection:=Fconnetion;//transaction
  370.     Fconnetion.Params.UserName:='sysdba';
  371.     Fconnetion.Params.Password:='masterkey';
  372.     Fconnetion.Params.Add('Characterset=Utf8');//utf8
  373.     Fconnetion.LoginPrompt:=False;
  374.     Fconnetion.Params.Database:='';
  375.     Fconnetion.Connected:=false;
  376.     Fconnetion.Close;
  377.   end;
  378.  
  379. finalization
  380.   if Assigned(Fconnetion) then
  381.     begin
  382.       Fconnetion.Close;
  383.       FreeAndNil(Fconnetion);
  384.     end;
  385.     if Assigned(FTransaction) then
  386.     begin
  387.       FreeAndNil(FTransaction);
  388.     end;
  389. end.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement