use DBI;

$dsn = "DBI:mysql:database=volkszaehler;host=localhost";
$dbh2 = DBI->connect($dsn, "vz", "hier_stand_mein_passwort");    
$dbh = DBI->connect( "dbi:SQLite:/tmp/HT3_db.sqlite" ) || die "Cannot connect: $DBI::errstr";


$limit = 10000000;
$dbh2->do("delete from data where channel_id in( 999,1000,1001,1002,1005, 1006, 1007, 1008, 1009, 1013, 1011, 1012)");

my $sth = $dbh->prepare("select distinct * from heizkreis2 order by utc limit $limit;");
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
	$utc = $ref->{'UTC'}*1000;
	if ($utc ne $utcold) {
 		$sql[$z++] =  "(1000,  $utc , ". $ref->{'T_ist_HK'}."),\n";
		$sql[$z++] =  "(1001,  $utc ,  ". $ref->{'T_vorlauf_misch_HK'}."),\n";
		$sql[$z++] =  "(1002,  $utc ,  ". $ref->{'V_mischerstellung'}."),\n";
		$sql[$z++] =  "(1005,  $utc ,  ". $ref->{'T_soll_HK'}."),\n";
	}
	else {
#		print "duplikat $utcold $utc\n";
	}
	$utcold = $utc;
#	if ($i++ % 100 == 0) {print $i . "hk \n";}
}
$sth->finish();

my $sth = $dbh->prepare("select distinct * from heizgeraet order by utc limit $limit;");
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
	$utc = $ref->{'UTC'}*1000;
	if ($utc ne $utcold) {
		$sql[$z++] = "(1006,  $utc , ". $ref->{'T_aussen'}."),\n";
		$sql[$z++] = "(1007,  $utc , ". $ref->{'T_vorlauf_soll'}."),\n";
		$sql[$z++] = "(1008,  $utc , ". $ref->{'T_vorlauf_ist'}."),\n";
		$sql[$z++] = "(1009,  $utc , ". $ref->{'T_ruecklauf'}."),\n";
	}
	else {
#		print "duplikat $utcold $utc\n";
	}
	$utcold = $utc;
#	if ($i++ % 1000 == 0) {print $i . "hz \n";}
}
$sth->finish();
my $sth = $dbh->prepare("select distinct * from warmwasser order by utc limit $limit;");
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
	$utc = $ref->{'UTC'}*1000;
	if ($utc ne $utcold) {
		$sql[$z++] = "(1011,  $utc , ". $ref->{'T_soll'}."),\n";
 		$sql[$z++] = "(1012,  $utc , ". $ref->{'T_ist'}."),\n";
		$sql[$z++] = "(1013,  $utc , ". $ref->{'T_speicher'}."),\n";
	}
	else {
#		print "duplikat $utcold $utc\n";
	}
	$utcold = $utc;
#	if ($i++ % 1000 == 0) {print $i . "ww \n";}
}
$sql2 = "";
$z=0;
foreach my $do (@sql)	{
	$sql2 .= $do;
	$z++;
	print $z . "\n";
if ($z == 10000) {
	$sql2 = "INSERT INTO `data` (`channel_id`, `timestamp`, `value`) VALUES ".$sql2;
	chop($sql2);
	chop($sql2);
	print $dbh2->do($sql2);
	$sql2 = "";
	$z = 0;
}
}

	$sql2 = "INSERT INTO `data` (`channel_id`, `timestamp`, `value`) VALUES ".$sql2;
	chop($sql2);
	chop($sql2);
	print $dbh2->do($sql2);

$dbh->disconnect;