From: iklaric <iklaric@8392b6e1-25fa-0310-8288-cc32f8e212ea>
Date: Thu, 3 May 2007 13:10:11 +0000 (+0000)
Subject: - new version of xls2conf script. it relies now on column names, instead of fixed... 
X-Git-Url: http://git.rot13.org/?p=BackupPC.git;a=commitdiff_plain;h=c8440780e6023b9657d2859ecc813ebf36f4e5f1

- new version of xls2conf script. it relies now on column names, instead of fixed column locations


git-svn-id: svn+ssh://llin/home/dpavlin/private/svn/BackupPC/trunk@368 8392b6e1-25fa-0310-8288-cc32f8e212ea
---

diff --git a/xls2conf/xls2conf_new.pl b/xls2conf/xls2conf_new.pl
new file mode 100755
index 0000000..c717024
--- /dev/null
+++ b/xls2conf/xls2conf_new.pl
@@ -0,0 +1,166 @@
+#!/usr/bin/perl -w
+
+use strict;
+use Spreadsheet::ParseExcel;
+
+my $xls_file = shift @ARGV || die "usage: $0 file.xls\n";
+
+my $oBook = Spreadsheet::ParseExcel::Workbook->Parse($xls_file);
+
+# BackupPC hosts file
+my %bpc_hosts;
+my %hosts;
+my %smb_share_name;
+my $rsync_share_name;
+my $column_data = {
+                    "ID" => {"name" => "InstrumentID_part2", "c_index" => -1},
+                    "name" => {"name" => "InstrumentID_part1", "c_index" => -1},
+                    "IP" => {"name" => "PC_IP", "c_index" => -1},
+                    "pc_name" => {"name" => "PC_Name", "c_index" => -1},
+                    "OS" => {"name" => "PC_OS", "c_index" => -1},
+                    "path" => {"name" => "Local export path", "c_index" => -1},
+                    "share" => {"name" => "Shared folder", "c_index" => -1}
+	    };
+                  
+outer_loop:
+foreach my $sheet (@{$oBook->{Worksheet}}) {
+	if ($sheet->{Name} !~ m/(Instruments|sheet1)/i) {
+		print "# skipped sheet ",$sheet->{Name},"\n";
+		next;
+	}
+	
+	# reset all column data
+	foreach my $key(keys(%{$column_data})) {
+		$column_data->{$key}->{c_index} = -1;
+  	}
+	
+	# retrieve all column data
+	for (my $column = $sheet->{MinCol}; defined($sheet->{MaxCol}) && $column <= $sheet->{MaxRow}; $column++) {
+		next if (!defined($sheet->{Cells}[0][$column]));
+		my $column_name = $sheet->{Cells}[0][$column]->Value;
+		
+		foreach my $key(keys(%{$column_data})) {
+			my $column_match = $column_data->{$key}->{name};
+			next if !defined($column_match);
+			if ($column_name =~ m/$column_match/i) {
+				$column_data->{$key}->{c_index} = $column;
+			}
+		}
+  	}
+  	
+	# if some columns are not defined, skip this sheet
+  	foreach my $key(keys(%{$column_data})) {
+    		if ($column_data->{$key}->{c_index} == -1) {
+			print "# skipped sheet ",$sheet->{Name}," because not all columns are properly defined.\n";
+			next outer_loop;
+		}
+  	}
+	
+	for(my $i = $sheet->{MinRow} ; defined $sheet->{MaxRow} && $i <= $sheet->{MaxRow} ; $i++) {
+		# IP
+		my $id = $sheet->{Cells}[$i][ $column_data->{ID}->{c_index} ]->{Val} || next;
+		my $ime = $sheet->{Cells}[$i][ $column_data->{name}->{c_index} ]->{Val} || next;
+
+		my $conf_name = lc($ime . '_' . $id);
+		$conf_name =~ s/\s+/_/g;
+
+		my $ip = $sheet->{Cells}[$i][ $column_data->{IP}->{c_index} ]->{Val} || next;
+		my $hostname = $sheet->{Cells}[$i][ $column_data->{pc_name}->{c_index} ]->Value || next;
+		my $os = $sheet->{Cells}[$i][ $column_data->{OS}->{c_index} ]->Value || next;
+		my $path = $sheet->{Cells}[$i][ $column_data->{path}->{c_index} ]->Value || next;
+		my $share = $sheet->{Cells}[$i][ $column_data->{share}->{c_index} ]->Value || next;
+
+		if ($ip !~ /\d+\.\d+\.\d+\.\d+/) {
+			print "# skipped $ip, $hostname, $os, $path, $share\n";
+			next;
+		}
+
+		print "$ip hostname[$hostname] os[$os] path[$path] share[$share]\n";
+
+		my $drive = lc(substr($path,0,1));
+
+		my $rsync_share;
+		if ($drive eq 'c' || $drive eq 'd') {
+			$rsync_share = $drive . 'Drive';
+		} else {
+			die "unknown drive '$drive' from path '$path'";
+		}
+
+		$path =~ s#^[cd]:\\#/#gi;
+		$path =~ s#\\#/#g;
+
+		open(O, "> conf/${conf_name}.pl") || die "can't open conf/${conf_name}.pl: $!";
+
+		if ($os =~ m#(98|95|9x)#i) {
+
+			print "$ip\tsmb://${share}\n";
+
+			push @{$smb_share_name{$conf_name}}, $share;
+
+print O qq`
+# $conf_name on $hostname $ip, $os
+\$Conf{XferMethod} = 'smb';
+\$Conf{SmbHostName} = '$hostname';
+\$Conf{SmbShareName} = [ '`,join("','", @{$smb_share_name{$conf_name}}),qq`' ];
+`;
+
+			if ($hosts{$ip}) {
+				$hosts{$ip} .= "\t$hostname" unless ($hosts{$ip} =~ m/$hostname/i);
+			} else {
+				$hosts{$ip} = "$ip\t$hostname";
+			}
+		} else {
+
+			sub case_insesitive {
+				my $t = shift || return;
+				return '[' . lc($t) . uc($t) . ']';
+			}
+
+			$path =~ s/([a-zA-z])/case_insesitive($1)/ge;
+
+			print "$ip\trsync://${share}::${path}\n";
+
+			$path =~ s/([ '])/\\$1/g;
+			push @{$rsync_share_name->{$conf_name}->{$rsync_share}}, $path;
+
+			my @shares = sort keys %{$rsync_share_name->{$conf_name}};
+			my $rsync_share_names = "'" . join("','", @shares) . "'";
+			my @backup_files_only;
+			foreach my $s (@shares) {
+				foreach my $p (@{$rsync_share_name->{$conf_name}->{$s}}) {
+					push @backup_files_only, "'$s' => '$p'";
+				}
+			}
+print O qq`
+# $conf_name on $hostname $ip, $os
+\$Conf{XferMethod} = 'rsyncd';
+\$Conf{RsyncShareName} = [ $rsync_share_names ];
+\$Conf{BackupFilesOnly} = [ `, join(",", @backup_files_only), qq` ];
+`;
+
+		}
+
+		close(O);
+
+		$bpc_hosts{$conf_name} = "$conf_name\t0\tasa";
+		if ($hosts{$ip}) {
+			$hosts{$ip} .= "\t$conf_name" unless ($hosts{$ip} =~ m/$conf_name/i);
+			$hosts{$ip} .= "\t$hostname" unless ($hosts{$ip} =~ m/$hostname/i);
+		} else {
+			$hosts{$ip} = "$ip\t$conf_name\t$hostname";
+		}
+	}
+
+}
+
+open(H, "> conf/hosts.backuppc") || die "hosts.backuppc $!";
+foreach my $k (sort keys %bpc_hosts) {
+	print H $bpc_hosts{$k},"\n";
+}
+close(H);
+
+open(H, "> conf/hosts.add") || die "hosts.add $!";
+foreach my $k (sort keys %hosts) {
+	print H $hosts{$k},"\n";
+}
+close(H);