<div class='codemain' style='height:200px;white-space:pre;overflow:auto'>require "mysql"
begin
DBNm = Array.new
DBNm[0] = "stcdata"
DBNm[1] = "stcdata"
DBNm[2] = "stcdata"
HostNm = Array.new
HostNm[0] = "192.168.13.5"
HostNm[1] = "192.168.15.221"
HostNm[2] = "192.168.16.50"
UsNm = Array.new
UsNm[0] = "voen_pas"
UsNm[1] = "voen_pas"
UsNm[2] = "voen_pas"
Pass = Array.new
Pass[0]="voya7341"
Pass[1]="voya7341"
Pass[2]="voya7341"
DBName="brep";#база
HostName="192.168.13.129";#сервер
UserName="dmitriy";#пользователь
Password="ScX5Rz7f";#пароль
t=Time.new
startsec=t.to_f
# connect to the MySQL server
m = Mysql.new(HostName, UserName, Password, DBName)
# get server version string and display it
#row = dbh.select_one("SELECT VERSION()")
#puts "Server version: " + row[0]
m.query("REPLACE INTO cron2 SET id=1, blok=1")
m.query("DELETE FROM `tpeodat2`")
m.query("DELETE FROM `tpeo2`")
m.query("DELETE FROM `tfuc2`")
m.query("DELETE FROM `tgrp2`")
m.query("DELETE FROM `trup2`")
m.query("DELETE FROM `tspe2`")
nook_tpd=0
ok_tpd=0
nook_tp=0
ok_tp=0
nook_tf=0
ok_tf=0
nook_tg=0
ok_tg=0
nook_tr=0
ok_tr=0
nook_ts=0
ok_ts=0
count_tpd=0
count_tp=0
error=0
servers=0
count_tf=0
count_tg=0
count_tr=0
count_ts=0
#pole_tpeodat=Array.new
#values_tpeodat=Array.new
DBNm.each_index do |x|
s = Mysql.new(HostNm[x], UsNm[x], Pass[x], DBNm[x]) rescue error=1
if error!=1
pole_tpeodat=Array.new
values_tpeodat=Array.new
r=s.query("SELECT tpd.* FROM tpeodat tpd, tpeo tp, tfuc tf WHERE tp.KOD=tpd.KOD AND tf.FKOD=tp.FKOD AND tf.WORK=1")
r.each_hash do |row|
k=Array.new
v=Array.new
row.each_pair do |key, val|
k<<key
v<<"'#{val}'"
end
pole_tpeodat<<k.join(', ')
values_tpeodat<<v.join(', ')
end
r.free
pole_tpeo=Array.new
values_tpeo=Array.new
r=s.query("SELECT tp.CMT, tp.FKOD, tp.FM, tp.IM, tp.OT, tp.GKOD, tp.GRNT, tp.KOD, tp.NZK, tp.PFLAG, tp.SKOD FROM tpeo tp, tpeodat tpd, tfuc tf WHERE tpd.KOD=tp.KOD AND tf.FKOD=tp.FKOD AND tf.WORK=1")
r.each_hash do |row|
k=Array.new
v=Array.new
row.each_pair do |key, val|
k<<key
v<<"'#{val}'"
end
pole_tpeo<<k.join(', ')
values_tpeo<<v.join(', ')
end
r.free
pole_tfuc=Array.new
values_tfuc=Array.new
r=s.query("SELECT * FROM tfuc WHERE WORK=1")
r.each_hash do |row|
k=Array.new
v=Array.new
row.each_pair do |key, val|
k<<key
v<<"'#{val}'"
end
pole_tfuc<<k.join(', ')
values_tfuc<<v.join(', ')
end
r.free
r=s.query("SELECT tp.GKOD as gk FROM tpeo tp, tpeodat tpd, tfuc tf WHERE tpd.KOD=tp.KOD AND tf.FKOD=tp.FKOD AND tf.WORK=1 GROUP BY tp.GKOD")
k=Array.new
r.each_hash do |row|
k<<"'#{row['gk']}'"
end
gkod=k.join(', ')
pole_tgrp=Array.new
values_tgrp=Array.new
r=s.query("SELECT * FROM tgrp WHERE GKOD IN(#{gkod})")
r.each_hash do |row|
k=Array.new
v=Array.new
row.each_pair do |key, val|
k<<key
v<<"'#{val}'"
end
pole_tgrp<<k.join(', ')
values_tgrp<<v.join(', ')
end
r.free
r=s.query("SELECT TRKOD FROM tgrp WHERE GKOD IN(#{gkod}) GROUP BY TRKOD")
k=Array.new
r.each_hash do |row|
k<<"'#{row['TRKOD']}'"
end
trkod=k.join(', ')
pole_trup=Array.new
values_trup=Array.new
r=s.query("SELECT * FROM trup WHERE TRKOD IN(#{trkod})")
r.each_hash do |row|
k=Array.new
v=Array.new
row.each_pair do |key, val|
k<<key
v<<"'#{val}'"
end
pole_trup<<k.join(', ')
values_trup<<v.join(', ')
end
r.free
r=s.query("SELECT SKOD FROM trup WHERE TRKOD IN(#{trkod}) GROUP BY SKOD")
k=Array.new
r.each_hash do |row|
k<<"'#{row['SKOD']}'"
end
skod=k.join(', ')
pole_tspe=Array.new
values_tspe=Array.new
r=s.query("SELECT * FROM tspe WHERE SKOD IN(#{skod})")
r.each_hash do |row|
k=Array.new
v=Array.new
row.each_pair do |key, val|
k<<key
v<<"'#{val}'"
end
pole_tspe<<k.join(', ')
values_tspe<<v.join(', ')
end
r.free
servers+=1
pole_tpeodat.each_index do |i|
count_tpd+=1
m.query("INSERT INTO tpeodat2 (#{pole_tpeodat}, server) VALUES(#{values_tpeodat}, #{x})") rescue error=1
if error==1
nook_tpd+=1
error=0
else
ok_tpd+=1
end
end
pole_tpeo.each_index do |i|
count_tp+=1
m.query("INSERT INTO tpeo2 (#{pole_tpeo}, server) VALUES(#{values_tpeo}, #{x})") rescue error=1
if error==1
nook_tp+=1
error=0
else
ok_tp+=1
end
end
pole_tfuc.each_index do |i|
count_tf+=1
m.query("INSERT INTO tfuc2 (#{pole_tfuc}, server) VALUES(#{values_tfuc}, #{x})") rescue error=1
if error==1
nook_tf+=1
error=0
else
ok_tf+=1
end
end
pole_tgrp.each_index do |i|
count_tg+=1
m.query("INSERT INTO tgrp2 (#{pole_tgrp}, server) VALUES(#{values_tgrp}, #{x})") rescue error=1
if error==1
nook_tg+=1
error=0
else
ok_tg+=1
end
end
pole_trup.each_index do |i|
count_tr+=1
m.query("INSERT INTO trup2 (#{pole_trup}, server) VALUES(#{values_trup}, #{x})") rescue error=1
if error==1
nook_tr+=1
error=0
else
ok_tr+=1
end
end
pole_tspe.each_index do |i|
count_ts+=1
m.query("INSERT INTO tspe2 (#{pole_tspe}, server) VALUES(#{values_tspe}, #{x})") rescue error=1
if error==1
nook_ts+=1
error=0
else
ok_ts+=1
end
end
else
puts "Сервер #{HostNm[x]} не доступен."
error=0
end
error=0
end
#m = Mysql.new(HostName, UserName, Password, DBName)
t=Time.new
m.query("REPLACE INTO cron2 SET id=1, date=#{t.to_i}, blok=0, servers=#{servers}")
puts "Отчет по tpeodat: Выбрано(#{count_tpd}), Записано(#{ok_tpd}), Не записано(#{nook_tpd})";
puts "Отчет по tpeo: Выбрано(#{count_tp}), Записано(#{ok_tp}), Не записано(#{nook_tp})";
puts "Отчет по tfuc: Выбрано(#{count_tf}), Записано(#{ok_tf}), Не записано(#{nook_tf})";
puts "Отчет по tgrp: Выбрано(#{count_tg}), Записано(#{ok_tg}), Не записано(#{nook_tg})";
puts "Отчет по trup: Выбрано(#{count_tr}), Записано(#{ok_tr}), Не записано(#{nook_tr})";
puts "Отчет по tspe: Выбрано(#{count_ts}), Записано(#{ok_ts}), Не записано(#{nook_ts})";
# disconnect from server
m.close if m
t=Time.new
endsec=t.to_f
totaltime=endsec-startsec
printf("Страница сгенерирована за %f секунд !\n", totaltime)
end
