API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 9th, 2017
77
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 25.56 KB | None | 0 0
raw download clone embed print report
  1. unit principal;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8. Classes, SysUtils, db, sqldb, odbcconn, mysql50conn, mysql56conn, FileUtil,
  9. Forms, Controls, Graphics, Dialogs, StdCtrls, DBGrids, DbCtrls, ComCtrls,
  10. ExtCtrls, ZConnection, ZDataset, IniFiles, regexpr, LConvEncoding;
  11.  
  12. type
  13.  
  14. { TfrmMinerar }
  15.  
  16. TfrmMinerar = class(TForm)
  17. btnParametros: TButton;
  18. btnMarcas: TButton;
  19. btnModelos: TButton;
  20. btnPotencias: TButton;
  21. Button1: TButton;
  22. DModelo: TDataSource;
  23. DBLModelo: TDBLookupComboBox;
  24. DMarca: TDataSource;
  25. DPotencia: TDataSource;
  26. DBLMarca: TDBLookupComboBox;
  27. DBPotencia: TDBLookupComboBox;
  28. edtAnomes: TEdit;
  29. edtCNM: TEdit;
  30. Label1: TLabel;
  31. Label2: TLabel;
  32. Label3: TLabel;
  33. Potencia: TLabel;
  34. Marca: TLabel;
  35. Label4: TLabel;
  36. Label5: TLabel;
  37. lblcount: TLabel;
  38. ProgressBar1: TProgressBar;
  39. TimerPotencia: TTimer;
  40. TimerModelo: TTimer;
  41. TimerMarcas: TTimer;
  42. TimerParametros: TTimer;
  43. ZConnection1: TZConnection;
  44. ZModelomodelo: TStringField;
  45. ZPotenciapotencia: TStringField;
  46. ZQuery1: TZQuery;
  47. ZQuery1id: TLongintField;
  48. ZQuery1marca: TStringField;
  49. ZQuery1modelo: TStringField;
  50. ZQuery1nome: TStringField;
  51. ZQuery1potencia: TStringField;
  52. ZQuery2: TZQuery;
  53. ZQuery3: TZQuery;
  54. ZMarca: TZQuery;
  55. ZMarcamarca: TStringField;
  56. ZPotencia: TZQuery;
  57. ZModelo: TZQuery;
  58. ZQuery4: TZQuery;
  59.  
  60. procedure btnMarcasClick(Sender: TObject);
  61. procedure btnModelosClick(Sender: TObject);
  62. procedure btnParametrosClick(Sender: TObject);
  63. procedure btnPotenciasClick(Sender: TObject);
  64. procedure Button1Click(Sender: TObject);
  65. procedure Button2Click(Sender: TObject);
  66. procedure DBLMarcaChange(Sender: TObject);
  67. procedure FormCreate(Sender: TObject);
  68. function Check( palavra,texto:String):Boolean;
  69. function CheckMarca( palavra,texto:String):Boolean;
  70. function CheckNovo( palavra,texto:String):Boolean;
  71.  
  72. procedure TimerMarcasTimer(Sender: TObject);
  73. procedure TimerModeloTimer(Sender: TObject);
  74. procedure TimerParametrosTimer(Sender: TObject);
  75. procedure TimerPotenciaTimer(Sender: TObject);
  76.  
  77.  
  78.  
  79. private
  80. { private declarations }
  81. public
  82. { public declarations }
  83. end;
  84.  
  85. var
  86. frmMinerar: TfrmMinerar;
  87. ptotal,pcontador,registro,total:Integer;
  88. estatus,ativo:Boolean;
  89.  
  90. implementation
  91.  
  92. {$R *.lfm}
  93.  
  94. { TfrmMinerar }
  95.  
  96.  
  97.  
  98.  
  99.  
  100. procedure TfrmMinerar.FormCreate(Sender: TObject);
  101. var
  102.  
  103. ArqIni: TIniFile;
  104.  
  105. begin
  106.  
  107. try
  108.  
  109. ArqIni := TIniFile.Create('banco.ini');
  110.  
  111.  
  112. ZConnection1.HostName:= ArqIni.ReadString('DATABASE','host','');
  113. ZConnection1.User:= ArqIni.ReadString('DATABASE','user','');
  114. ZConnection1.Password:= ArqIni.ReadString('DATABASE','senha','');
  115. ZConnection1.Database:= ArqIni.ReadString('DATABASE','database','');
  116. ZConnection1.Protocol:= ArqIni.ReadString('DATABASE','driver','');
  117. ZConnection1.Port:= ArqIni.ReadInteger('DATABASE','porta',3306);
  118. ZConnection1.LibraryLocation:= ArqIni.ReadString('DATABASE','lib','');
  119. ZConnection1.Connect;
  120.  
  121.  
  122.  
  123. ZMarca.Open;
  124. ZModelo.Open;
  125. ZPotencia.Open;
  126. except
  127. ShowMessage('N�o foi possivel se cnectar ao banco');
  128. end;
  129.  
  130. end;
  131.  
  132. function TfrmMinerar.Check(palavra, texto: String): Boolean;
  133. var
  134. regex:TRegExpr;
  135. begin
  136. regex:=TRegExpr.Create;
  137. regex.Expression:= '.'+ISO_8859_15ToUTF8(UpperCase(palavra))+'.';
  138. // ShowMessage(BoolToStr(regex.Exec(texto)));
  139. result := regex.Exec(ISO_8859_15ToUTF8(UpperCase(texto)));
  140. end;
  141.  
  142. function TfrmMinerar.CheckMarca(palavra, texto: String): Boolean;
  143. var
  144. regex:TRegExpr;
  145. begin
  146. regex:=TRegExpr.Create;
  147. regex.Expression:= '.*'+UpperCase(ISO_8859_15ToUTF8(palavra))+'.*';
  148. // ShowMessage(BoolToStr(regex.Exec(texto)));
  149. result := regex.Exec(UpperCase(ISO_8859_15ToUTF8(texto)));
  150. end;
  151.  
  152. function TfrmMinerar.CheckNovo(palavra, texto: String): Boolean;
  153. var
  154. regex:TRegExpr;
  155. begin
  156. regex:=TRegExpr.Create;
  157. regex.Expression:= '\b'+UpperCase(ISO_8859_15ToUTF8(palavra))+'\b';
  158. // ShowMessage(BoolToStr(regex.Exec(texto)));
  159. result := regex.Exec(UpperCase(ISO_8859_15ToUTF8(texto)));
  160.  
  161. end;
  162.  
  163.  
  164.  
  165. procedure TfrmMinerar.TimerMarcasTimer(Sender: TObject);
  166. var
  167. lsql,nome,usql,descricao,atsql :string;
  168. i,k: integer;
  169. begin
  170.  
  171. if estatus then
  172. TimerMarcas.Interval:=200000000;
  173. begin
  174. estatus:= false;
  175. lsql := 'SELECT * FROM parametros where nome <> '+QuotedStr('vazio')+' AND nome <> '+QuotedStr('todos')+' AND tratado = 0 AND marca <> '+QuotedStr('')+' LIMIT 10 ';
  176.  
  177. ZQuery1.Close;
  178. ZQuery1.SQL.Clear;
  179. ZQuery1.SQL.Add(lsql);
  180. ZQuery1.Open;
  181. k:=ZQuery1.RecordCount;
  182. i:=0;
  183.  
  184. if k > 0 then
  185. begin
  186. while not ZQuery1.EOF do
  187.  
  188. begin
  189. inc(i);
  190. lsql := 'SELECT * from importcao where anomes = '+QuotedStr(edtAnomes.Text)+' AND codncm = '+QuotedStr(edtCNM.Text);
  191.  
  192. ZQuery2.Close;
  193. ZQuery2.SQL.Clear;
  194. ZQuery2.SQL.Add(lsql);
  195.  
  196. ZQuery2.Open;
  197.  
  198. nome := ZQuery1.FieldByName('nome').AsString;
  199.  
  200. while not ZQuery2.EOF do
  201. begin
  202. inc(registro);
  203. lblcount.Caption:= IntToStr(registro)+'/'+IntToStr(total);
  204. ProgressBar1.Position:= ProgressBar1.Position + 1; //sempre adciona um na contagem
  205. Application.ProcessMessages;
  206.  
  207. descricao:= ZQuery2.FieldByName('descricao_do_produto').AsString;
  208.  
  209.  
  210.  
  211. if(CheckMarca(nome,descricao)) then
  212. begin
  213.  
  214. usql := 'UPDATE importcao ';
  215.  
  216.  
  217. usql := usql+'set marca = :xmarca';
  218.  
  219.  
  220.  
  221. usql := usql+' where id = :xid';
  222.  
  223. ZQuery3.Close;
  224. ZQuery3.SQL.Clear;
  225. ZQuery3.SQL.Add(usql);
  226.  
  227.  
  228. ZQuery3.ParamByName('xmarca').Value:= ZQuery1marca .AsString;
  229.  
  230. ZQuery3.ParamByName('xid').Value:= ZQuery2.FieldByName('id').AsInteger;
  231.  
  232. ZQuery3.ExecSQL;
  233.  
  234. ZQuery3.SQL.SaveToFile('sql_saida.txt');
  235.  
  236. end;
  237.  
  238. ZQuery2.Next;
  239. end;
  240.  
  241. atsql := 'update parametros set tratado = 1 where id = '+ZQuery1.FieldByName('id').AsString;
  242.  
  243. ZQuery4.Close;
  244. ZQuery4.SQL.Clear;
  245. ZQuery4.SQL.Add(atsql);
  246. ZQuery4.ExecSQL;
  247.  
  248. if i = 10 then
  249. begin
  250.  
  251. TimerMarcas.Enabled:= false;
  252. TimerMarcas.Interval:=1000;
  253. TimerMarcas.Enabled:= true;
  254. estatus:= true;
  255. end;
  256. ZQuery1.Next;
  257. end;
  258. end
  259. else
  260. begin
  261. ShowMessage('Fim');
  262. ProgressBar1.Position := 0;
  263. lblcount.Caption:= '0/0';
  264.  
  265.  
  266. atsql := 'update parametros set tratado = 0 ';
  267.  
  268. ZQuery4.Close;
  269. ZQuery4.SQL.Clear;
  270. ZQuery4.SQL.Add(atsql);
  271. ZQuery4.ExecSQL;
  272. Application.ProcessMessages;
  273. TimerMarcas.Enabled:=false;
  274. end;
  275. end;
  276.  
  277.  
  278.  
  279. end;
  280.  
  281. procedure TfrmMinerar.TimerModeloTimer(Sender: TObject);
  282. var
  283. lsql,nome,usql,descricao,atsql :string;
  284. i,k: integer;
  285. begin
  286.  
  287. if estatus then
  288. TimerModelo.Interval:=200000000;
  289. begin
  290. estatus:= false;
  291. lsql := 'SELECT * FROM parametros where nome <> '+QuotedStr('vazio')+' AND nome <> '+QuotedStr('todos')+' AND tratado = 0 AND modelo <> '+QuotedStr('')+' LIMIT 10 ';
  292.  
  293. ZQuery1.Close;
  294. ZQuery1.SQL.Clear;
  295. ZQuery1.SQL.Add(lsql);
  296. ZQuery1.Open;
  297. k:=ZQuery1.RecordCount;
  298. i:=0;
  299.  
  300. if k > 0 then
  301. begin
  302. while not ZQuery1.EOF do
  303.  
  304. begin
  305. inc(i);
  306. lsql := 'SELECT * from importcao where anomes = '+QuotedStr(edtAnomes.Text)+' AND codncm = '+QuotedStr(edtCNM.Text);
  307.  
  308. ZQuery2.Close;
  309. ZQuery2.SQL.Clear;
  310. ZQuery2.SQL.Add(lsql);
  311.  
  312. ZQuery2.Open;
  313.  
  314. nome := ZQuery1.FieldByName('nome').AsString;
  315.  
  316. while not ZQuery2.EOF do
  317. begin
  318. inc(registro);
  319. lblcount.Caption:= IntToStr(registro)+'/'+IntToStr(total);
  320. ProgressBar1.Position:= ProgressBar1.Position + 1; //sempre adciona um na contagem
  321. Application.ProcessMessages;
  322.  
  323. descricao:= ZQuery2.FieldByName('descricao_do_produto').AsString;
  324.  
  325.  
  326.  
  327. if(CheckNovo(nome,descricao)) then
  328. begin
  329.  
  330. usql := 'UPDATE importcao set modelo = :xmodelo where id = :xid';
  331.  
  332. ZQuery3.Close;
  333. ZQuery3.SQL.Clear;
  334. ZQuery3.SQL.Add(usql);
  335.  
  336. ZQuery3.ParamByName('xmodelo').Value:= ZQuery1modelo.AsString;
  337. ZQuery3.ParamByName('xid').Value:= ZQuery2.FieldByName('id').AsInteger;
  338.  
  339. ZQuery3.ExecSQL;
  340.  
  341. ZQuery3.SQL.SaveToFile('sql_saida.txt');
  342.  
  343. end;
  344.  
  345. ZQuery2.Next;
  346. end;
  347.  
  348. atsql := 'update parametros set tratado = 1 where id = '+ZQuery1.FieldByName('id').AsString;
  349.  
  350. ZQuery4.Close;
  351. ZQuery4.SQL.Clear;
  352. ZQuery4.SQL.Add(atsql);
  353. ZQuery4.ExecSQL;
  354.  
  355. if i = 10 then
  356. begin
  357.  
  358. TimerModelo.Enabled:= false;
  359. TimerModelo.Interval:=1000;
  360. TimerModelo.Enabled:= true;
  361. estatus:= true;
  362. end;
  363. ZQuery1.Next;
  364. end;
  365. end
  366. else
  367. begin
  368. ShowMessage('Fim');
  369. ProgressBar1.Position := 0;
  370. lblcount.Caption:= '0/0';
  371. TimerModelo.Enabled:=false;
  372.  
  373. atsql := 'update parametros set tratado = 0 ';
  374.  
  375. ZQuery4.Close;
  376. ZQuery4.SQL.Clear;
  377. ZQuery4.SQL.Add(atsql);
  378. ZQuery4.ExecSQL;
  379.  
  380. end;
  381. end;
  382.  
  383.  
  384.  
  385. end;
  386.  
  387. procedure TfrmMinerar.TimerParametrosTimer(Sender: TObject);
  388. var
  389. lsql,nome,usql,descricao,atsql :string;
  390. i,k: integer;
  391. begin
  392.  
  393. if estatus then
  394. TimerParametros.Interval:=120000;
  395.  
  396.  
  397. begin
  398. estatus:= false;
  399. lsql := 'SELECT * FROM parametros where nome <> '+QuotedStr('vazio')+' AND nome <>'+QuotedStr('todos')+ ' AND tratado = 0 LIMIT 10';
  400.  
  401. ZQuery1.Close;
  402. ZQuery1.SQL.Clear;
  403. ZQuery1.SQL.Add(lsql);
  404. ZQuery1.Open;
  405. k:=ZQuery1.RecordCount;
  406. i:=0;
  407.  
  408. if k > 0 then
  409. begin
  410. while not ZQuery1.EOF do
  411.  
  412. begin
  413.  
  414.  
  415. inc(i);
  416. lsql := 'SELECT * from importcao where anomes = '+QuotedStr(edtAnomes.Text)+' AND codncm = '+QuotedStr(edtCNM.Text)+' AND tratado = 0 LIMIT 10000';
  417.  
  418. ZQuery2.Close;
  419. ZQuery2.SQL.Clear;
  420. ZQuery2.SQL.Add(lsql);
  421.  
  422. ZQuery2.Open;
  423.  
  424. nome := ZQuery1.FieldByName('nome').AsString;
  425.  
  426. while not ZQuery2.EOF do
  427. begin
  428.  
  429. inc(registro);
  430. lblcount.Caption:= IntToStr(registro)+'/'+IntToStr(total);
  431. ProgressBar1.Position:= ProgressBar1.Position + 1; //sempre adciona um na contagem
  432. Application.ProcessMessages;
  433.  
  434. descricao:= ZQuery2.FieldByName('descricao_do_produto').AsString;
  435.  
  436.  
  437.  
  438.  
  439. if(CheckNovo(nome,descricao)) then
  440. begin
  441.  
  442. usql := 'UPDATE importcao ';
  443.  
  444. if (ZQuery1marca .AsString <> '') then
  445. usql := usql+'set marca = '+QuotedStr(ZQuery1marca .AsString);
  446.  
  447. if (ZQuery1marca .AsString = '') and (ZQuery1modelo .AsString <> '') then
  448. usql := usql+'set modelo = '+QuotedStr(ZQuery1modelo .AsString);
  449.  
  450. if (ZQuery1marca .AsString <>'') and (ZQuery1modelo .AsString <> '') then
  451. usql := usql+' , modelo = '+QuotedStr(ZQuery1modelo .AsString);
  452.  
  453. if (ZQuery1potencia .AsString <> '') and (ZQuery1marca .AsString = '') and (ZQuery1modelo .AsString = '') then
  454. usql := usql+'set potencia = '+QuotedStr(ZQuery1potencia .AsString);
  455.  
  456. if (ZQuery1potencia .AsString <> '') and ((ZQuery1marca .AsString <> '') or (ZQuery1modelo .AsString <> '')) then
  457. usql := usql+', potencia = '+QuotedStr(ZQuery1potencia .AsString);
  458.  
  459. usql := usql+' where id = :xid';
  460.  
  461. ZQuery3.Close;
  462. ZQuery3.SQL.Clear;
  463. ZQuery3.SQL.Add(usql);
  464.  
  465.  
  466.  
  467. ZQuery3.ParamByName('xid').Value:= ZQuery2.FieldByName('id').AsInteger;
  468.  
  469. ZQuery3.ExecSQL;
  470.  
  471. // ZQuery3.SQL.SaveToFile('sql_saida.txt');
  472.  
  473. end;
  474.  
  475. ZQuery2.Next;
  476. end;
  477.  
  478. atsql := 'update parametros set tratado = 1 where id = '+ZQuery1.FieldByName('id').AsString;
  479.  
  480. ZQuery4.Close;
  481. ZQuery4.SQL.Clear;
  482. ZQuery4.SQL.Add(atsql);
  483. ZQuery4.ExecSQL;
  484.  
  485. if i = 10 then
  486. begin
  487.  
  488. TimerParametros.Enabled:= false;
  489. TimerParametros.Interval:=1000;
  490. TimerParametros.Enabled:= true;
  491. estatus:= true;
  492. end;
  493. ZQuery1.Next;
  494. end;
  495. end
  496. else
  497. begin
  498. ShowMessage('Fim');
  499. ProgressBar1.Position := 0;
  500. lblcount.Caption:= '0/0';
  501. atsql := 'update parametros set tratado = 0 ';
  502. ZQuery4.Close;
  503. ZQuery4.SQL.Clear;
  504. ZQuery4.SQL.Add(atsql);
  505. ZQuery4.ExecSQL;
  506.  
  507.  
  508. Application.ProcessMessages;
  509. TimerParametros.Enabled:=false;
  510. end;
  511. end;
  512.  
  513.  
  514. end;
  515.  
  516. procedure TfrmMinerar.TimerPotenciaTimer(Sender: TObject);
  517. var
  518. lsql,nome,usql,descricao,atsql :string;
  519. i,k: integer;
  520. begin
  521.  
  522. if estatus then
  523. TimerPotencia.Interval:=200000000;
  524. begin
  525. estatus:= false;
  526. lsql := 'SELECT * FROM parametros where nome <> '+QuotedStr('vazio')+' AND nome <> '+QuotedStr('todos')+' AND tratado = 0 AND potencia <> '+QuotedStr('')+' LIMIT 10 ';
  527.  
  528. ZQuery1.Close;
  529. ZQuery1.SQL.Clear;
  530. ZQuery1.SQL.Add(lsql);
  531. ZQuery1.Open;
  532. k:=ZQuery1.RecordCount;
  533. i:=0;
  534.  
  535. if k > 0 then
  536. begin
  537. while not ZQuery1.EOF do
  538.  
  539. begin
  540. inc(i);
  541. lsql := 'SELECT * from importcao where anomes = '+QuotedStr(edtAnomes.Text)+' AND codncm = '+QuotedStr(edtCNM.Text);
  542.  
  543. ZQuery2.Close;
  544. ZQuery2.SQL.Clear;
  545. ZQuery2.SQL.Add(lsql);
  546.  
  547. ZQuery2.Open;
  548.  
  549. nome := ZQuery1.FieldByName('nome').AsString;
  550.  
  551. while not ZQuery2.EOF do
  552. begin
  553. inc(registro);
  554. lblcount.Caption:= IntToStr(registro)+'/'+IntToStr(total);
  555. ProgressBar1.Position:= ProgressBar1.Position + 1; //sempre adciona um na contagem
  556. Application.ProcessMessages;
  557.  
  558. descricao:= ZQuery2.FieldByName('descricao_do_produto').AsString;
  559.  
  560.  
  561.  
  562.  
  563. if(CheckNovo(nome,descricao)) then
  564. begin
  565.  
  566. usql := 'UPDATE importcao set potencia = :xpotencia where id = :xid';
  567.  
  568. ZQuery3.Close;
  569. ZQuery3.SQL.Clear;
  570. ZQuery3.SQL.Add(usql);
  571.  
  572. ZQuery3.ParamByName('xpotencia').Value:= ZQuery1potencia.AsString;
  573. ZQuery3.ParamByName('xid').Value:= ZQuery2.FieldByName('id').AsInteger;
  574.  
  575. ZQuery3.ExecSQL;
  576.  
  577. ZQuery3.SQL.SaveToFile('sql_saida.txt');
  578.  
  579. end;
  580.  
  581. ZQuery2.Next;
  582. end;
  583.  
  584. atsql := 'update parametros set tratado = 1 where id = '+ZQuery1.FieldByName('id').AsString;
  585.  
  586. ZQuery4.Close;
  587. ZQuery4.SQL.Clear;
  588. ZQuery4.SQL.Add(atsql);
  589. ZQuery4.ExecSQL;
  590.  
  591. if i = 10 then
  592. begin
  593.  
  594. TimerPotencia.Enabled:= false;
  595. TimerPotencia.Interval:=1000;
  596. TimerPotencia.Enabled:= true;
  597. estatus:= true;
  598. end;
  599. ZQuery1.Next;
  600. end;
  601. end
  602. else
  603. begin
  604. ShowMessage('Fim');
  605. ProgressBar1.Position := 0;
  606. lblcount.Caption:= '0/0';
  607. TimerPotencia.Enabled:=false;
  608.  
  609. atsql := 'update parametros set tratado = 0 ';
  610.  
  611. ZQuery4.Close;
  612. ZQuery4.SQL.Clear;
  613. ZQuery4.SQL.Add(atsql);
  614. ZQuery4.ExecSQL;
  615.  
  616. end;
  617. end;
  618.  
  619.  
  620. end;
  621.  
  622.  
  623.  
  624.  
  625. procedure TfrmMinerar.btnParametrosClick(Sender: TObject);
  626. var
  627. lsql:string;
  628.  
  629. begin
  630. ativo:= true;
  631.  
  632. lsql := 'SELECT * FROM parametros where nome <> '+QuotedStr('vazio')+' AND nome <>'+QuotedStr('todos')+ ' AND tratado = 0';
  633. pcontador := 0;
  634. ZQuery1.Close;
  635. ZQuery1.SQL.Clear;
  636. ZQuery1.SQL.Add(lsql);
  637. ZQuery1.Open;
  638. ptotal:= ZQuery1.RecordCount;
  639.  
  640. lsql := 'SELECT * from importcao where anomes = '+QuotedStr(edtAnomes.Text)+' AND codncm = '+QuotedStr(edtCNM.Text);
  641. registro := 0;
  642. ZQuery1.Close;
  643. ZQuery1.SQL.Clear;
  644. ZQuery1.SQL.Text:= lsql ;
  645. ZQuery1.Open;
  646. registro:= ZQuery1.RecordCount;
  647.  
  648. total := ptotal * registro ;
  649.  
  650. ProgressBar1.Position:=0;//posiciona a progress bar na
  651. ProgressBar1.Max:=total;
  652. lblcount.Caption:= IntToStr(1)+'/'+IntToStr(total);
  653. estatus:= true;
  654. TimerParametros.Interval:= 1000;
  655. TimerParametros.Enabled:= true;
  656.  
  657. end;
  658.  
  659. procedure TfrmMinerar.btnPotenciasClick(Sender: TObject);
  660. var
  661. lsql:string;
  662.  
  663. begin
  664. lsql := 'SELECT * FROM parametros where nome <> '+QuotedStr('vazio')+' AND nome <> '+QuotedStr('todos')+'AND tratado = 0 AND potencia <> '+QuotedStr('');
  665. ptotal := 0;
  666. ZQuery1.Close;
  667. ZQuery1.SQL.Clear;
  668. ZQuery1.SQL.Add(lsql);
  669. ZQuery1.Open;
  670. ptotal:= ZQuery1.RecordCount;
  671.  
  672. lsql := 'SELECT * from importcao where anomes = '+QuotedStr(edtAnomes.Text)+' AND codncm = '+QuotedStr(edtCNM.Text);
  673. registro := 0;
  674. ZQuery1.Close;
  675. ZQuery1.SQL.Clear;
  676. ZQuery1.SQL.Add(lsql);
  677. ZQuery1.Open;
  678. registro:= ZQuery1.RecordCount;
  679.  
  680. total := ptotal * registro ;
  681.  
  682. ProgressBar1.Position:=0;//posiciona a progress bar na
  683. ProgressBar1.Max:=total;
  684. lblcount.Caption:= IntToStr(1)+'/'+IntToStr(total);
  685. estatus:= true;
  686. TimerPotencia.Interval:= 1000;
  687. TimerPotencia.Enabled:= true;
  688.  
  689. end;
  690.  
  691. procedure TfrmMinerar.btnMarcasClick(Sender: TObject);
  692.  
  693. var
  694. lsql:string;
  695.  
  696. begin
  697. lsql := 'SELECT * FROM parametros where nome <> '+QuotedStr('vazio')+' AND nome <> '+QuotedStr('todos')+'AND tratado = 0 AND marca <> '+QuotedStr('');
  698. ptotal := 0;
  699. ZQuery1.Close;
  700. ZQuery1.SQL.Clear;
  701. ZQuery1.SQL.Add(lsql);
  702. ZQuery1.Open;
  703. ptotal:= ZQuery1.RecordCount;
  704.  
  705. lsql := 'SELECT * from importcao where anomes = '+QuotedStr(edtAnomes.Text)+' AND codncm = '+QuotedStr(edtCNM.Text);
  706. registro := 0;
  707. ZQuery1.Close;
  708. ZQuery1.SQL.Clear;
  709. ZQuery1.SQL.Add(lsql);
  710. ZQuery1.Open;
  711. registro:= ZQuery1.RecordCount;
  712.  
  713. total := ptotal * registro ;
  714.  
  715. ProgressBar1.Position:=0;//posiciona a progress bar na
  716. ProgressBar1.Max:=total;
  717. lblcount.Caption:= IntToStr(1)+'/'+IntToStr(total);
  718. estatus:= true;
  719. TimerMarcas.Interval:= 1000;
  720. TimerMarcas.Enabled:= true;
  721.  
  722.  
  723. end;
  724.  
  725. procedure TfrmMinerar.btnModelosClick(Sender: TObject);
  726. var
  727. lsql:string;
  728.  
  729. begin
  730. lsql := 'SELECT * FROM parametros where nome <> '+QuotedStr('vazio')+' AND nome <> '+QuotedStr('todos')+'AND tratado = 0 AND modelo <> '+QuotedStr('');
  731. ptotal := 0;
  732. ZQuery1.Close;
  733. ZQuery1.SQL.Clear;
  734. ZQuery1.SQL.Add(lsql);
  735. ZQuery1.Open;
  736. ptotal:= ZQuery1.RecordCount;
  737.  
  738. lsql := 'SELECT * from importcao where anomes = '+QuotedStr(edtAnomes.Text)+' AND codncm = '+QuotedStr(edtCNM.Text);
  739. registro := 0;
  740. ZQuery1.Close;
  741. ZQuery1.SQL.Clear;
  742. ZQuery1.SQL.Add(lsql);
  743. ZQuery1.Open;
  744. registro:= ZQuery1.RecordCount;
  745.  
  746. total := ptotal * registro ;
  747.  
  748. ProgressBar1.Position:=0;//posiciona a progress bar na
  749. ProgressBar1.Max:=total;
  750. lblcount.Caption:= IntToStr(1)+'/'+IntToStr(total);
  751. estatus:= true;
  752. TimerModelo.Interval:= 1000;
  753. TimerModelo.Enabled:= true;
  754.  
  755. end;
  756.  
  757. procedure TfrmMinerar.Button1Click(Sender: TObject);
  758.  
  759.  
  760. var
  761. lsql,nome,usql,descricao :string;
  762. i,k: integer;
  763. begin
  764.  
  765.  
  766. lsql := 'SELECT * FROM `parametros` WHERE nome <> '+QuotedStr('')+' and nome <> '+QuotedStr('todos')+' and nome <> '+QuotedStr('') ;
  767.  
  768. if DBLMarca.Text <> '' then
  769. lsql := lsql+' AND marca = '+QuotedStr(DBLMarca.Text);
  770.  
  771. if DBLModelo.Text <> '' then
  772. lsql := lsql+' AND modelo = '+QuotedStr(DBLModelo.Text);
  773.  
  774. if DBPotencia.Text <> '' then
  775. lsql := lsql+' AND potencia = '+QuotedStr(DBPotencia.Text);
  776.  
  777.  
  778.  
  779. ZQuery1.Close;
  780. ZQuery1.SQL.Clear;
  781. ZQuery1.SQL.Add(lsql);
  782. ZQuery1.Open;
  783. k:= ZQuery1.RecordCount;
  784. i:=0;
  785. ProgressBar1.Position:=0;//posiciona a progress bar na posicao 0
  786. ProgressBar1.Max:= k;//quantidade maxima de numeros
  787. while not ZQuery1.EOF do
  788. begin
  789. inc(i);
  790. lblcount.Caption:= IntToStr(i)+'/'+IntToStr(k);
  791. ProgressBar1.Position:= ProgressBar1.Position + 1; //sempre adciona um na contagem
  792. Application.ProcessMessages;
  793. lsql := 'SELECT * from importcao where anomes = '+QuotedStr(edtAnomes.Text)+' AND codncm = '+QuotedStr(edtCNM.Text);
  794.  
  795. ZQuery2.Close;
  796. ZQuery2.SQL.Clear;
  797. ZQuery2.SQL.Add(lsql);
  798.  
  799. ZQuery2.Open;
  800.  
  801. nome := ZQuery1.FieldByName('nome').AsString;
  802.  
  803. while not ZQuery2.EOF do
  804. begin
  805. descricao:= ZQuery2.FieldByName('descricao_do_produto').AsString;
  806.  
  807.  
  808.  
  809. if(Check(nome,descricao)) then
  810. begin
  811.  
  812. usql := 'UPDATE importcao ';
  813.  
  814. if (DBLMarca.Text <> '') and (DBLModelo.Text = '') and (DBPotencia.Text = '') then
  815. begin
  816. if ZQuery1marca .AsString <> '' then
  817. usql := usql+'set marca = :xmarca';
  818.  
  819. if (ZQuery1marca .AsString = '') and (ZQuery1modelo .AsString <> '') then
  820. usql := usql+'set modelo = :xmodelo';
  821.  
  822. if (ZQuery1marca .AsString <>'') and (ZQuery1modelo .AsString <> '') then
  823. usql := usql+' , modelo = :xmodelo';
  824.  
  825. if (ZQuery1potencia .AsString <> '') and (ZQuery1marca .AsString = '') and (ZQuery1modelo .AsString = '') then
  826. usql := usql+'set potencia = :xpotencia';
  827.  
  828. if (ZQuery1potencia .AsString <> '') and ((ZQuery1marca .AsString <> '') or (ZQuery1modelo .AsString <> '')) then
  829. usql := usql+' , potencia = :xpotencia';
  830. end;
  831.  
  832.  
  833. // modelo
  834.  
  835. if (DBLModelo.Text <> '') and (DBLMarca.Text = '') and (DBPotencia.Text = '') then
  836. usql := usql+'set modelo = :xmodelo';
  837.  
  838.  
  839. if (DBPotencia.Text <> '') and (DBLModelo.Text = '') and (DBLMarca.Text = '') then
  840. usql := usql+'set potencia = :xpotencia';
  841.  
  842.  
  843.  
  844.  
  845.  
  846. usql := usql+' where id = :xid';
  847.  
  848. ZQuery3.Close;
  849. ZQuery3.SQL.Clear;
  850. ZQuery3.SQL.Add(usql);
  851.  
  852. if (DBLMarca.Text <> '') and (DBLModelo.Text = '') and (DBPotencia.Text = '') then
  853. begin
  854. if ZQuery1marca .AsString <> '' then
  855. ZQuery3.ParamByName('xmarca').Value:= ZQuery1marca .AsString;
  856.  
  857. if ZQuery1modelo .AsString <> '' then
  858. ZQuery3.ParamByName('xmodelo').Value:= ZQuery1modelo.AsString;
  859.  
  860. if ZQuery1potencia.AsString <> '' then
  861. ZQuery3.ParamByName('xpotencia').Value:= ZQuery1potencia.AsString;
  862. end;
  863.  
  864. if (DBLModelo.Text <> '') and (DBLMarca.Text = '') and (DBPotencia.Text = '') then
  865. begin
  866. if ZQuery1modelo .AsString <> '' then
  867. ZQuery3.ParamByName('xmodelo').Value:= ZQuery1modelo.AsString;
  868. end;
  869.  
  870. if (DBPotencia.Text <> '') and (DBLModelo.Text = '') and (DBLMarca.Text = '') then
  871. begin
  872. if ZQuery1potencia.AsString <> '' then
  873. ZQuery3.ParamByName('xpotencia').Value:= ZQuery1potencia.AsString;
  874. end;
  875.  
  876. ZQuery3.ParamByName('xid').Value:= ZQuery2.FieldByName('id').AsInteger;
  877.  
  878. ZQuery3.ExecSQL;
  879.  
  880. ZQuery3.SQL.SaveToFile('sql_saida.txt');
  881.  
  882. end;
  883.  
  884. ZQuery2.Next;
  885. end;
  886.  
  887. ZQuery1.Next;
  888.  
  889.  
  890. // zquery2.SQL.SaveToFile('sql_saida.txt');
  891. {
  892. ZQuery2.ParamByName('xmarca').Value:= ZQuery1marca .AsString;
  893. ZQuery2.ParamByName('xmodelo').Value:= ZQuery1modelo.AsString;
  894. ZQuery2.ParamByName('xpotencia').Value:= ZQuery1potencia.AsString;
  895. ZQuery2.ParamByName('xnome').AsString := QuotedStr('%'+ZQuery1.FieldByName('nome').AsString+'%');
  896. ZQuery2.ParamByName('xncm').AsString:= edtCNM.Text;
  897. }
  898.  
  899. end;
  900. ShowMessage('Fim');
  901. ProgressBar1.Position := 0;
  902. lblcount.Caption:= '0/0';
  903.  
  904. end;
  905.  
  906. procedure TfrmMinerar.Button2Click(Sender: TObject);
  907. begin
  908. if TimerParametros.Enabled then
  909. begin
  910. TimerParametros.Enabled := false;
  911. ativo:= false;
  912. ZConnection1.Disconnect;
  913.  
  914. ShowMessage('Minera��o pausada');
  915. end
  916. else
  917. begin
  918. ativo:= true;
  919. ZConnection1.Connect;
  920. TimerParametros.Enabled := false;
  921. TimerParametros.Interval:= 1000;
  922. TimerParametros.Enabled := true;
  923. ShowMessage('Minera��o em andamento');
  924. end;
  925.  
  926.  
  927. if TimerMarcas.Enabled then
  928. begin
  929. TimerMarcas.Enabled := false;
  930.  
  931. end
  932. else
  933. begin
  934. TimerMarcas.Enabled := false;
  935. TimerMarcas.Interval:= 1000;
  936. TimerMarcas.Enabled := true;
  937. end;
  938.  
  939. if TimerModelo.Enabled then
  940. begin
  941. TimerModelo.Enabled := false;
  942.  
  943. end
  944. else
  945. begin
  946. TimerModelo.Enabled := false;
  947. TimerModelo.Interval:= 1000;
  948. TimerModelo.Enabled := true;
  949. end;
  950.  
  951. if TimerPotencia.Enabled then
  952. begin
  953. TimerPotencia.Enabled := false;
  954.  
  955. end
  956. else
  957. begin
  958. TimerPotencia.Enabled := false;
  959. TimerPotencia.Interval:= 1000;
  960. TimerPotencia.Enabled := true;
  961. end;
  962.  
  963. end;
  964.  
  965. procedure TfrmMinerar.DBLMarcaChange(Sender: TObject);
  966. begin
  967.  
  968. end;
  969.  
  970.  
  971.  
  972.  
  973.  
  974.  
  975. end.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!