1 |
#!/usr/bin/perl -w |
2 |
|
3 |
use strict; |
4 |
use Spreadsheet::ParseExcel; |
5 |
|
6 |
my $xls_file = shift @ARGV || die "usage: $0 file.xls\n"; |
7 |
|
8 |
my $oBook = Spreadsheet::ParseExcel::Workbook->Parse($xls_file); |
9 |
|
10 |
# BackupPC hosts file |
11 |
my %bpc_hosts; |
12 |
my %hosts; |
13 |
my %smb_share_name; |
14 |
my $rsync_share_name; |
15 |
my $column_data = { |
16 |
"ID" => {"name" => "InstrumentID_part2", "c_index" => -1}, |
17 |
"name" => {"name" => "InstrumentID_part1", "c_index" => -1}, |
18 |
"IP" => {"name" => "PC_IP", "c_index" => -1}, |
19 |
"pc_name" => {"name" => "PC_Name", "c_index" => -1}, |
20 |
"OS" => {"name" => "PC_OS", "c_index" => -1}, |
21 |
"path" => {"name" => "Local export path", "c_index" => -1}, |
22 |
"share" => {"name" => "Shared folder", "c_index" => -1} |
23 |
}; |
24 |
|
25 |
outer_loop: |
26 |
foreach my $sheet (@{$oBook->{Worksheet}}) { |
27 |
if ($sheet->{Name} !~ m/(Instruments|sheet1)/i) { |
28 |
print "# skipped sheet ",$sheet->{Name},"\n"; |
29 |
next; |
30 |
} |
31 |
|
32 |
# reset all column data |
33 |
foreach my $key(keys(%{$column_data})) { |
34 |
$column_data->{$key}->{c_index} = -1; |
35 |
} |
36 |
|
37 |
# retrieve all column data |
38 |
for (my $column = $sheet->{MinCol}; defined($sheet->{MaxCol}) && $column <= $sheet->{MaxRow}; $column++) { |
39 |
next if (!defined($sheet->{Cells}[0][$column])); |
40 |
my $column_name = $sheet->{Cells}[0][$column]->Value; |
41 |
|
42 |
foreach my $key(keys(%{$column_data})) { |
43 |
my $column_match = $column_data->{$key}->{name}; |
44 |
next if !defined($column_match); |
45 |
if ($column_name =~ m/$column_match/i) { |
46 |
$column_data->{$key}->{c_index} = $column; |
47 |
} |
48 |
} |
49 |
} |
50 |
|
51 |
# if some columns are not defined, skip this sheet |
52 |
foreach my $key(keys(%{$column_data})) { |
53 |
if ($column_data->{$key}->{c_index} == -1) { |
54 |
print "# skipped sheet ",$sheet->{Name}," because not all columns are properly defined.\n"; |
55 |
next outer_loop; |
56 |
} |
57 |
} |
58 |
|
59 |
for(my $i = $sheet->{MinRow} ; defined $sheet->{MaxRow} && $i <= $sheet->{MaxRow} ; $i++) { |
60 |
# IP |
61 |
my $id = $sheet->{Cells}[$i][ $column_data->{ID}->{c_index} ]->{Val} || next; |
62 |
my $ime = $sheet->{Cells}[$i][ $column_data->{name}->{c_index} ]->{Val} || next; |
63 |
|
64 |
my $conf_name = lc($ime . '_' . $id); |
65 |
$conf_name =~ s/\s+/_/g; |
66 |
|
67 |
my $ip = $sheet->{Cells}[$i][ $column_data->{IP}->{c_index} ]->{Val} || next; |
68 |
my $hostname = $sheet->{Cells}[$i][ $column_data->{pc_name}->{c_index} ]->Value || next; |
69 |
my $os = $sheet->{Cells}[$i][ $column_data->{OS}->{c_index} ]->Value || next; |
70 |
my $path = $sheet->{Cells}[$i][ $column_data->{path}->{c_index} ]->Value || next; |
71 |
my $share = $sheet->{Cells}[$i][ $column_data->{share}->{c_index} ]->Value || next; |
72 |
|
73 |
if ($ip !~ /\d+\.\d+\.\d+\.\d+/) { |
74 |
print "# skipped $ip, $hostname, $os, $path, $share\n"; |
75 |
next; |
76 |
} |
77 |
|
78 |
print "$ip hostname[$hostname] os[$os] path[$path] share[$share]\n"; |
79 |
|
80 |
my $drive = lc(substr($path,0,1)); |
81 |
|
82 |
my $rsync_share; |
83 |
if ($drive eq 'c' || $drive eq 'd') { |
84 |
$rsync_share = $drive . 'Drive'; |
85 |
} else { |
86 |
die "unknown drive '$drive' from path '$path'"; |
87 |
} |
88 |
|
89 |
$path =~ s#^[cd]:\\#/#gi; |
90 |
$path =~ s#\\#/#g; |
91 |
|
92 |
open(O, "> conf/${conf_name}.pl") || die "can't open conf/${conf_name}.pl: $!"; |
93 |
|
94 |
if ($os =~ m#(98|95|9x)#i) { |
95 |
|
96 |
print "$ip\tsmb://${share}\n"; |
97 |
|
98 |
push @{$smb_share_name{$conf_name}}, $share; |
99 |
|
100 |
print O qq` |
101 |
# $conf_name on $hostname $ip, $os |
102 |
\$Conf{XferMethod} = 'smb'; |
103 |
\$Conf{SmbHostName} = '$hostname'; |
104 |
\$Conf{SmbShareName} = [ '`,join("','", @{$smb_share_name{$conf_name}}),qq`' ]; |
105 |
`; |
106 |
|
107 |
if ($hosts{$ip}) { |
108 |
$hosts{$ip} .= "\t$hostname" unless ($hosts{$ip} =~ m/$hostname/i); |
109 |
} else { |
110 |
$hosts{$ip} = "$ip\t$hostname"; |
111 |
} |
112 |
} else { |
113 |
|
114 |
sub case_insesitive { |
115 |
my $t = shift || return; |
116 |
return '[' . lc($t) . uc($t) . ']'; |
117 |
} |
118 |
|
119 |
$path =~ s/([a-zA-z])/case_insesitive($1)/ge; |
120 |
|
121 |
print "$ip\trsync://${share}::${path}\n"; |
122 |
|
123 |
$path =~ s/([ '])/\\$1/g; |
124 |
push @{$rsync_share_name->{$conf_name}->{$rsync_share}}, $path; |
125 |
|
126 |
my @shares = sort keys %{$rsync_share_name->{$conf_name}}; |
127 |
my $rsync_share_names = "'" . join("','", @shares) . "'"; |
128 |
my @backup_files_only; |
129 |
foreach my $s (@shares) { |
130 |
foreach my $p (@{$rsync_share_name->{$conf_name}->{$s}}) { |
131 |
push @backup_files_only, "'$s' => '$p'"; |
132 |
} |
133 |
} |
134 |
print O qq` |
135 |
# $conf_name on $hostname $ip, $os |
136 |
\$Conf{XferMethod} = 'rsyncd'; |
137 |
\$Conf{RsyncShareName} = [ $rsync_share_names ]; |
138 |
\$Conf{BackupFilesOnly} = [ `, join(",", @backup_files_only), qq` ]; |
139 |
`; |
140 |
|
141 |
} |
142 |
|
143 |
close(O); |
144 |
|
145 |
$bpc_hosts{$conf_name} = "$conf_name\t0\tasa"; |
146 |
if ($hosts{$ip}) { |
147 |
$hosts{$ip} .= "\t$conf_name" unless ($hosts{$ip} =~ m/$conf_name/i); |
148 |
$hosts{$ip} .= "\t$hostname" unless ($hosts{$ip} =~ m/$hostname/i); |
149 |
} else { |
150 |
$hosts{$ip} = "$ip\t$conf_name\t$hostname"; |
151 |
} |
152 |
} |
153 |
|
154 |
} |
155 |
|
156 |
open(H, "> conf/hosts.backuppc") || die "hosts.backuppc $!"; |
157 |
foreach my $k (sort keys %bpc_hosts) { |
158 |
print H $bpc_hosts{$k},"\n"; |
159 |
} |
160 |
close(H); |
161 |
|
162 |
open(H, "> conf/hosts.add") || die "hosts.add $!"; |
163 |
foreach my $k (sort keys %hosts) { |
164 |
print H $hosts{$k},"\n"; |
165 |
} |
166 |
close(H); |