API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 12th, 2017
72
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.54 KB | None | 0 0
raw download clone embed print report
  1. #!/home/y/bin/perl -w
  2. use DBI;
  3.  
  4. my %db_bar_L2;
  5. my @unique_L2_barcodes; ### unique L2 barcodes
  6. my @L2_barcodes_last_write_time;
  7. my @found_L2_barcodes_smurfdb;
  8. my @notfound_L2_barcodes_smurfdb;
  9.  
  10. my @L_barcode_media_tapes; ## barcodes L2 convert to L
  11. my @L_barcodes_last_write_time;
  12. my @found_L_barcodes_smurfdb;
  13. my @notfound_L_barcodes_smurfdb;
  14.  
  15. my @noL2_barcodes_media_tapes;
  16. my @noL2_barcodes_last_write_time;
  17. my @found_noL2_barcodes_smurfdb;
  18. my @notfound_noL2_barcodes_smurfdb;
  19. #####
  20.  
  21. sub main{
  22. &check_duplicate_L2_barcodes;
  23. &L2_barcodes_last_write_time;
  24.  
  25. # &convert_L2_to_L_barcodes;
  26. # &check_L_barcodes_last_write_time;
  27.  
  28. # &convert_to_noL2_barcodes;
  29. # &check_noL2_barcodes_last_write_time;
  30.  
  31. #merge_all_found_
  32. #get unique media_group
  33. #check last_write_time + media_group<today date
  34. #count =18153
  35. }
  36.  
  37. &main;
  38.  
  39. sub check_duplicate_L2_barcodes{
  40. my $count_media=`more media_tapes.txt|wc -l`;
  41. chomp($count_media);
  42. open(OUTPUT,">output\/unique_L2_media_tapes.txt");
  43. my $file='media_tapes.txt';
  44. open(INPUT,$file) or die;
  45. open(OUTPUT2,">output\/duplicated_L2_barcodes.txt");
  46. print "Searching $file ($count_media barcode count)...\n";
  47. while(defined(my $barcode=<INPUT>)){
  48. chomp($barcode);
  49. if(!exists($db_bar_L2{$barcode})){
  50. $db_bar_L2{$barcode}="$barcode";
  51. push(@unique_L2_barcodes,$barcode);
  52. print OUTPUT "$barcode\n";
  53. }else{
  54. print "found duplicated $barcode\n";
  55. print OUTPUT2 "$barcode\n";
  56. }
  57. }
  58. }
  59. close(OUTPUT);
  60. close(OUTPUT2);
  61.  
  62. sub L2_barcodes_last_write_time{
  63. my $db="";
  64. my $user="";
  65. my $password="";
  66. my $dbh = DBI->connect ("DBI:mysql:database=$db:",
  67. $user,
  68. $password)
  69. or die "Can't connect to database: $DBI::err";
  70.  
  71. open(OUTPUT,">output\/found_L2_barcodes_smurfdb.csv");
  72. print OUTPUT "host_id,hostname,path,job_id,unique_id_media,barcode,container_id,last_write_time,today_date,media_group,media_type\n";
  73. open(OUTPUT2,">output\/notfound_L2_barcodes_smurfdb.txt");
  74.  
  75. my $total_tapes=@unique_L2_barcodes;
  76.  
  77. print "get L2_barcodes_last_write_time ($total_tapes barcode count)...\n";
  78.  
  79. my $count=0;
  80. foreach my $barcode(@unique_L2_barcodes){
  81. $found=0;
  82. my $sql="select
  83. jh.host_id,jh.hostname,jh.path,jm.job_id,left(m.media_id,40) as media_id,
  84. m.barcode,m.container_id,date_format(from_unixtime(m.last_write_time),\'%Y-%m-%d\') as last_write_time,
  85. date(now()) as today_date,
  86. m.media_group,m.media_type
  87. from backupdb.jobHistory_media jm
  88. JOIN jobHistory jh on (jm.job_id=jh.job_id AND jm.instance_id=jh.instance_id AND jm.phase_id=jh.phase_id)
  89. JOIN backupdb.media m on (m.server_id=jm.server_id AND left(m.media_id,40)=left(jm.media_id,40) AND m.last_write_time!=0
  90. AND m.media_group!=\'VTL\' AND m.barcode not like \'%VTL%\')
  91. where m.barcode=\'$barcode\' order by m.last_write_time desc limit 1";
  92.  
  93. my $sth=$dbh->prepare($sql);
  94. $sth->execute();
  95.  
  96. while(my($host_id,$hostname,$path,$job_id,$unique_id_media,$barcode,$container_id,$last_write_time,$today_date,$media_group,$media_type)=$sth->fetchrow_array()){
  97. push(@L2_barcodes_last_write_time,"$barcode|$last_write_time");
  98. print "======== $count/$total_tapes $barcode|$last_write_time =========\n";
  99. $found=1;
  100. $count=$count+1;
  101. }
  102. if(!$found){
  103. $count=$count+1;
  104. push(@notfound_L2_barcodes_smurfdb,$barcode);
  105. print "$count/$total_tapes $barcode was not found\n";
  106. print OUTPUT2 "$barcode\n";
  107. }
  108. }
  109. foreach my $line(@L2_barcodes_last_write_time){
  110. chomp($line);
  111. my($barcode,$last_write_time)=split(/\|/,$line);
  112.  
  113. $db="";
  114. $user="";
  115. $password="";
  116. $dbh = DBI->connect ("DBI:mysql:database=$db:",
  117. $user,
  118. $password)
  119. or die "Can't connect to database: $DBI::err";
  120.  
  121. my $sql2="select
  122. jh.host_id,jh.hostname,jh.path,jm.job_id,
  123. left(m.media_id,40) as media_id,m.barcode,m.container_id,
  124. date_format(from_unixtime(m.last_write_time),\'%Y-%m-%d\') as last_write_time,
  125. date(now()) as today_date,
  126. m.media_group,m.media_type
  127. from backupdb.jobHistory_media jm
  128. JOIN jobHistory jh on (jm.job_id=jh.job_id AND jm.instance_id=jh.instance_id AND jm.phase_id=jh.phase_id)
  129. JOIN backupdb.media m on (m.server_id=jm.server_id AND left(m.media_id,40)=left(jm.media_id,40) AND m.last_write_time!=0
  130. AND m.media_group!=\'VTL\' AND m.barcode not like \'%VTL%\')
  131. where m.barcode=\'$barcode\' and date_format(from_unixtime(m.last_write_time),\'%Y-%m-%d\')=\'$last_write_time\'";
  132.  
  133. my $sth2=$dbh->prepare($sql2);
  134. $sth2->execute();
  135.  
  136. print "\/output\/found_L2_barcodes_smurfdb.csv generated...\n";
  137. while(my($host_id,$hostname,$path,$job_id,$unique_id_media,$barcode,$container_id,$last_write_time,$today_date,$media_group,$media_type)=$sth2->fetchrow_array()){
  138. push(@found_L2_barcodes_smurfdb,"$host_id|$hostname|$path|$job_id|$unique_id_media|$barcode|$container_id|$last_write_time|$today_date|$media_group|$media_type");
  139. print "$host_id|$hostname|$path|$job_id|$unique_id_media|$barcode|$container_id|$last_write_time|$today_date|$media_group|$media_type\n";
  140. print OUTPUT "$host_id,$hostname,$path,$job_id,=\"$unique_id_media\",$barcode,$container_id,$last_write_time,$today_date,$media_group,$media_type\n";
  141. }
  142. print "\/output\/notfound_L2_barcodes_smurfdb.txt generated...\n";
  143. }
  144. $dbh->disconnect();
  145. }
  146. close(OUTPUT);
  147. close(OUTPUT2);
  148.  
  149. ################# convert L2 to L ###################
  150.  
  151. sub convert_L2_to_L_barcodes{
  152. my $notfound_L2=@notfound_L2_barcodes_smurfdb;
  153. open(OUTPUT,">output\/L_barcodes_media_tapes.txt");
  154. print "######## check barcode ending with L #########\n";
  155. print "######## Total tapes: $notfound_L2 #########\n";
  156. $count=0;
  157. foreach my $barcode(@notfound_L2_barcodes_smurfdb){
  158. if($barcode=~/[L2]$/){
  159. $count=$count+1;
  160. chop($barcode);
  161. print "==== $count/$notfound_L2 $barcode ===\n";
  162. push(@L_barcodes_media_tapes,$barcode);
  163. print OUTPUT "$barcode\n";
  164. }else{
  165. print "This barcode: $barcode does not have L2 at the end\n";
  166. }
  167. }
  168.  
  169. }
  170. close(OUTPUT);
  171.  
  172. sub check_L_barcodes_last_write_time{
  173. $db="";
  174. $user="";
  175. $password="";
  176. $dbh = DBI->connect ("DBI:mysql:database=$db:",
  177. $user,
  178. $password)
  179. or die "Can't connect to database: $DBI::err";
  180.  
  181. open(OUTPUT,">output\/found_L_barcodes_smurfdb.csv");
  182. print OUTPUT "host_id,hostname,path,job_id,unique_id_media,barcode,container_id,last_write_time,today_date,media_group,media_type\n";
  183. open(OUTPUT2,">output\/notfound_L_barcodes_smurfdb.txt");
  184.  
  185. print "get L_barcodes_last_write_time...\n";
  186.  
  187. my $count=0;
  188. my $notfound_L2=@notfound_L2_barcodes_smurfdb;
  189. foreach my $barcode(@L_barcodes_media_tapes){
  190. $count=$count+1;
  191. $found=0;
  192.  
  193. my $sql="select
  194. jh.host_id,jh.hostname,jh.path,jm.job_id,
  195. left(m.media_id,40) as media_id,m.barcode,m.container_id,
  196. date_format(from_unixtime(m.last_write_time),\'%Y-%m-%d\') as last_write_time,
  197. date(now()) as today_date,
  198. m.media_group,m.media_type
  199. from backupdb.jobHistory_media jm
  200. JOIN jobHistory jh on (jm.job_id=jh.job_id AND jm.instance_id=jh.instance_id AND jm.phase_id=jh.phase_id)
  201. JOIN backupdb.media m on (m.server_id=jm.server_id AND left(m.media_id,40)=left(jm.media_id,40) AND m.last_write_time!=0
  202. AND m.media_group!=\'VTL' AND m.barcode not like \'%VTL%\')
  203. where m.barcode=\'$barcode\' order by m.last_write_time desc limit 1";
  204.  
  205. my $sth=$dbh->prepare($sql);
  206. $sth->execute();
  207.  
  208. while(my($host_id,$hostname,$path,$job_id,$unique_id_media,$barcode,$container_id,$last_write_time,$today_date,$media_group,$media_type)=$sth->fetchrow_array()){
  209. push(@L_barcodes_last_write_time,"$barcode|$last_write_time");
  210. print "======== $count/$notfound_L2 $host_id|$hostname|$path|$job_id|$unique_id_media|$barcode|$container_id|$last_write_time|$today_date|$media_group|$media_type =========\n";
  211. $found=1;
  212. }
  213. if(!$found){
  214. push(@notfound_L_barcodes_smurfdb,$barcode);
  215. print "$count/$notfound_L2 $barcode was not found\n";
  216. print OUTPUT2 "$barcode\n";
  217. }
  218. }
  219. foreach my $line(@L_barcodes_last_write_time){
  220. chomp($line);
  221. my($barcode,$last_write_time)=split(/\|/,$line);
  222.  
  223. my $sql2="select
  224. jh.host_id,jh.hostname,jh.path,jm.job_id,
  225. left(m.media_id,40) as media_id,m.barcode,m.container_id,date_format(from_unixtime(m.last_write_time),\'%Y-%m-%d\') as last_write_time,
  226. date(now()) as today_date,
  227. m.media_group,m.media_type
  228. from backupdb.jobHistory_media jm
  229. JOIN jobHistory jh on (jm.job_id=jh.job_id AND jm.instance_id=jh.instance_id AND jm.phase_id=jh.phase_id)
  230. JOIN backupdb.media m on (m.server_id=jm.server_id AND left(m.media_id,40)=left(jm.media_id,40) AND m.last_write_time!=0
  231. AND m.media_group!=\'VTL' AND m.barcode not like \'%VTL%\')
  232. where m.barcode=\'$barcode\' and date_format(from_unixtime(m.last_write_time),\'%Y-%m-%d\')=\'$last_write_time\'";
  233.  
  234. my $sth2=$dbh->prepare($sql2);
  235. $sth2->execute();
  236.  
  237. while(my($host_id,$hostname,$path,$job_id,$unique_id_media,$barcode,$container_id,$last_write_time,$today_date,$media_group,$media_type)=$sth2->fetchrow_array()){
  238. push(@found_L_barcodes_smurfdb,"$host_id|$hostname|$path|$job_id|$unique_id_media|$barcode|$container_id|$last_write_time|$today_date|$media_group|$media_type");
  239. print "$host_id|$hostname|$job_id|$path|$unique_id_media|$barcode|$container_id|$last_write_time|$today_date|$media_group|$media_type\n";
  240. print OUTPUT "$host_id,$hostname,$path,$job_id,=\"$unique_id_media\",$barcode,=\"$container_id\",$last_write_time,$today_date,$media_group,$media_type\n";
  241. }
  242. }
  243. print "\/output\/notfound_L_barcodes_smurfdb.txt generated...\n";
  244. $dbh->disconnect();
  245. }
  246. close(OUTPUT);
  247. close(OUTPUT2);
  248.  
  249. ####### convert to no L2 barcodes ########
  250.  
  251. sub convert_to_noL2_barcodes{
  252. my $notfound_L=@notfound_L_barcodes_smurfdb;
  253. $count=0;
  254. foreach my $barcode(@notfound_L_barcodes_smurfdb){
  255. #print "$count $barcode\n";
  256. if($barcode=~/[L]$/){
  257. $count=$count+1;
  258. chop($barcode);
  259. print "==== $count/$notfound_L $barcode ===\n";
  260. push(@noL2_barcodes_media_tapes,$barcode);
  261. }
  262. }
  263. }
  264.  
  265. sub check_noL2_barcodes_last_write_time{
  266. $db="";
  267. $user="";
  268. $password="";
  269. $dbh = DBI->connect ("DBI:mysql:database=$db:",
  270. $user,
  271. $password)
  272. or die "Can't connect to database: $DBI::err";
  273.  
  274. open(OUTPUT,">output\/noL2_barcodes_smurfdb.csv");
  275. print OUTPUT "host_id,hostname,job_id,path,unique_id_media,barcode,container_id,last_write_time,today_date,media_group,media_type\n";
  276. open(OUTPUT2,">output\/notfound_noL2_barcodes_smurfdb.txt");
  277.  
  278. print "get noL2_barcodes_last_write_time...\n";
  279.  
  280. my $notfound_noL2=@noL2_barcodes_media_tapes;
  281. my $count=0;
  282. foreach my $barcode(@noL2_barcodes_media_tapes){
  283. $count=$count+1;
  284. $found=0;
  285.  
  286. my $sql="select
  287. jh.host_id,jh.hostname,jh.path,jm.job_id,
  288. left(m.media_id,40) as media_id,m.barcode,m.container_id,date_format(from_unixtime(m.last_write_time),\'%Y-%m-%d\') as last_write_time,
  289. date(now()) as today_date,
  290. m.media_group,m.media_type
  291. from backupdb.jobHistory_media jm
  292. JOIN jobHistory jh on (jm.job_id=jh.job_id AND jm.instance_id=jh.instance_id AND jm.phase_id=jh.phase_id)
  293. JOIN backupdb.media m on (m.server_id=jm.server_id AND left(m.media_id,40)=left(jm.media_id,40) AND m.last_write_time!=0
  294. AND m.media_group!=\'VTL' AND m.barcode not like \'%VTL%\')
  295. where m.barcode=\'$barcode\' order by m.last_write_time desc limit 1";
  296.  
  297. my $sth=$dbh->prepare($sql);
  298. $sth->execute();
  299.  
  300. while(my($host_id,$hostname,$path,$job_id,$unique_id_media,$barcode,$container_id,$last_write_time,$today_date,$media_group,$media_type)=$sth->fetchrow_array()){
  301. push(@noL2_barcodes_last_write_time,"$barcode|$last_write_time");
  302. print "======== $count/$notfound_noL2 $host_id|$hostname|$path|$job_id|$unique_id_media|$barcode|$container_id|$last_write_time|$today_date|$media_group|$media_type =========\n";
  303. $found=1;
  304. }
  305. if(!$found){
  306. push(@notfound_noL2_barcodes_smurfdb,$barcode);
  307. print "$count/$notfound_noL2 $barcode was not found\n";
  308. print OUTPUT2 "$barcode\n";
  309. }
  310. }
  311. foreach my $line(@noL2_barcodes_last_write_time){
  312. chomp($line);
  313. my($barcode,$last_write_time)=split(/\|/,$line);
  314.  
  315. my $sql2="select
  316. jh.host_id,jh.hostname,jh.path,jm.job_id,
  317. left(m.media_id,40) as media_id,m.barcode,m.container_id,date_format(from_unixtime(m.last_write_time),\'%Y-%m-%d\') as last_write_time,
  318. date(now()) as today_date,
  319. m.media_group,m.media_type
  320. from backupdb.jobHistory_media jm
  321. JOIN jobHistory jh on (jm.job_id=jh.job_id AND jm.instance_id=jh.instance_id AND jm.phase_id=jh.phase_id)
  322. JOIN backupdb.media m on (m.server_id=jm.server_id AND left(m.media_id,40)=left(jm.media_id,40) AND m.last_write_time!=0
  323. AND m.media_group!=\'VTL' AND m.barcode not like \'%VTL%\')
  324. where m.barcode=\'$barcode\' and date_format(from_unixtime(m.last_write_time),\'%Y-%m-%d\')=\'$last_write_time\'";
  325.  
  326. my $sth2=$dbh->prepare($sql2);
  327. $sth2->execute();
  328.  
  329. while(my($host_id,$hostname,$path,$job_id,$unique_id_media,$barcode,$container_id,$last_write_time,$today_date,$media_group,$media_type)=$sth2->fetchrow_array()){
  330. push(@found_noL2_barcodes_smurfdb,"$host_id|$hostname|$path|$job_id|$unique_id_media|$barcode|$container_id|$last_write_time|$today_date|$media_group|$media_type");
  331. print "$host_id|$hostname|$path|$job_id|$unique_id_media|$barcode|$container_id|$last_write_time|$today_date|$media_group|$media_type\n";
  332. print OUTPUT "$host_id,$hostname,$job_id,$path,=\"$unique_id_media\",$barcode,=\"$container_id\",$last_write_time,$today_date,$media_group,$media_type\n";
  333. }
  334. }
  335. print "\/output\/notfound_noL2_barcodes_smurfdb.txt generated...\n";
  336. }
  337. close(OUTPUT);
  338. close(OUTPUT2);
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!