269 lines
7.3 KiB
Perl
269 lines
7.3 KiB
Perl
|
sub db_min_upd {
|
||
|
|
|
||
|
|
my ($table,$where) = @_ ;
|
||
|
|
|
||
|
|
our $set = '' ;
|
||
|
|
|
||
|
|
foreach (keys %i) {
|
||
|
|
if (substr($_,0,3) eq 'new') { next ; }
|
||
|
|
if ($ignore{$_}) { next ; }
|
||
|
|
if ($hidden{$_} == 1) { next ; }
|
||
|
|
if ($i{$_} =~ /\"/) { $set .= qq(`$_`='$i{$_}',) ; } elsif ($i{$_} eq 'NULL') { $set .= qq(`$_`=$i{$_},) ; } else { $set .= qq(`$_`="$i{$_}",) ; }
|
||
|
|
}
|
||
|
|
|
||
|
|
$set = substr($set,0,-1) ;
|
||
|
|
|
||
|
|
unless ($set) { return ; }
|
||
|
|
|
||
|
|
&db_open_upd ;
|
||
|
|
|
||
|
|
my $sql = qq(UPDATE $table SET $set WHERE $where) ;
|
||
|
|
|
||
|
|
&common_debug($sql) ;
|
||
|
|
|
||
|
|
if ($testing == 1 && $useropts{it}{$username}) { &common_debug(">>>>>> *** TESTING, DB NOT UPDATED *** <<<<<<") ; return ; }
|
||
|
|
|
||
|
|
$sth = $dbh -> do ($sql) or die "could not execute :<BR>$sql<BR>$!" ;
|
||
|
|
|
||
|
|
&db_close_conn ;
|
||
|
|
|
||
|
|
$success = qq($i{id} SUCCESSFULLY SAVED) ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub db_min_insert {
|
||
|
|
|
||
|
|
my ($table) = @_ ;
|
||
|
|
|
||
|
|
my $columns = '' ;
|
||
|
|
my $values = '' ;
|
||
|
|
|
||
|
|
foreach (keys %i) {
|
||
|
|
if ($ignore{$_}) { next ; }
|
||
|
|
if ($hidden{$_} == 1) { next ; }
|
||
|
|
$columns .= qq(`$_`,) ;
|
||
|
|
if ($i{$_} =~ /\"/) { $values .= qq('$i{$_}',) ; } elsif ($i{$_} eq 'NULL') { $values .= qq(`$_`=$i{$_},) ; } else { $values .= qq("$i{$_}",) ; }
|
||
|
|
}
|
||
|
|
|
||
|
|
$columns = substr($columns,0,-1) ;
|
||
|
|
$values = substr($values,0,-1) ;
|
||
|
|
|
||
|
|
&db_open_upd ;
|
||
|
|
|
||
|
|
my $sql = qq(INSERT INTO $table ($columns) VALUES ($values) ;) ;
|
||
|
|
|
||
|
|
&common_debug($sql) ;
|
||
|
|
|
||
|
|
if ($testing == 1 && $useropts{it}{$username}) { &common_debug(">>>>>> *** TESTING, DB NOT UPDATED *** <<<<<<") ; return ; }
|
||
|
|
|
||
|
|
$sth = $dbh -> do ($sql) or die "could not execute :<BR>$sql<BR>$!" ;
|
||
|
|
our $new_row_id = $dbh->last_insert_id(undef, undef, $table, 'id') ; # or die "no insert id?";
|
||
|
|
|
||
|
|
&db_close_conn ;
|
||
|
|
|
||
|
|
$success = qq($i{id} SUCCESSFULLY SAVED) ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub db_min_delete {
|
||
|
|
|
||
|
|
my ($table,$where) = @_ ;
|
||
|
|
|
||
|
|
&db_open_upd ;
|
||
|
|
|
||
|
|
my $sql = qq(DELETE FROM $table WHERE $where) ;
|
||
|
|
|
||
|
|
&common_debug($sql) ;
|
||
|
|
|
||
|
|
if ($testing == 1 && $useropts{it}{$username}) { &common_debug(">>>>>> *** TESTING, DB NOT UPDATED *** <<<<<<") ; return ; }
|
||
|
|
|
||
|
|
$sth = $dbh -> do ($sql) or die "could not execute :<BR>$sql<BR>$!" ;
|
||
|
|
|
||
|
|
&db_close_conn ;
|
||
|
|
|
||
|
|
$success = qq($i{id} SUCCESSFULLY DELETED) ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub db_min_ro {
|
||
|
|
|
||
|
|
my ($table,$select,$where,$orderby,$limit) = @_ ;
|
||
|
|
|
||
|
|
if ($where) { $where = 'WHERE ' . $where ; }
|
||
|
|
if ($limit) { $limit = 'LIMIT ' . $limit ; }
|
||
|
|
if ($orderby) { $orderby = 'ORDER BY ' . $orderby ; }
|
||
|
|
|
||
|
|
&db_open_ro ;
|
||
|
|
|
||
|
|
my $sql = qq(SELECT $select FROM $table $where $orderby $limit) ;
|
||
|
|
|
||
|
|
&common_debug($sql) ;
|
||
|
|
|
||
|
|
my $sth = $dbh->prepare($sql) ;
|
||
|
|
|
||
|
|
$sth -> execute() or die "Could not execute SQL statement $sql ... maybe invalid? $!";
|
||
|
|
|
||
|
|
$col_cnt=0;
|
||
|
|
|
||
|
|
foreach $col (@{$sth->{NAME}}) { $col_name{$col_cnt}=$col; $col_cnt++; } # &common_debug($col); NB *** must be before fetchall_arrayref
|
||
|
|
|
||
|
|
my $rows_array_ref = $sth->fetchall_arrayref();
|
||
|
|
|
||
|
|
$sth->finish();
|
||
|
|
|
||
|
|
&db_close_conn ;
|
||
|
|
|
||
|
|
$db{$table} = () ; $done_col_val{$table} = () ;
|
||
|
|
|
||
|
|
foreach $row (@$rows_array_ref) {
|
||
|
|
for (0 .. $col_cnt){
|
||
|
|
if ($done_col_val{$table}{@$row[0]}{$col_name{$_}}) { next; }
|
||
|
|
# &common_debug("db_common_ro - [$_] [$table] [@$row[0]] [$col_name{$_}] [@$row[$_]]") ;
|
||
|
|
$db{$table}{@$row[0]}{$col_name{$_}} = @$row[$_] ;
|
||
|
|
$done_col_val{$table}{@$row[0]}{$col_name{$_}} = 1 ;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub db_min_raw {
|
||
|
|
|
||
|
|
my ($sql) = @_ ;
|
||
|
|
|
||
|
|
&common_debug($sql) ;
|
||
|
|
|
||
|
|
if ($testing == 1 && $useropts{it}{$username}) { &common_debug(">>>>>> *** TESTING, DB NOT UPDATED *** <<<<<<") ; return ; }
|
||
|
|
|
||
|
|
my $sth = $dbh->prepare($sql) ;
|
||
|
|
|
||
|
|
$sth->execute() or die "Could not execute SQL statement $sql ... maybe invalid? $!";
|
||
|
|
|
||
|
|
our $col_cnt=0;
|
||
|
|
|
||
|
|
foreach $col (@{$sth->{NAME}}) { $col_name{$col_cnt}=$col; $col_cnt++; } # &common_debug($col); NB *** must be before fetchall_arrayref
|
||
|
|
|
||
|
|
our $rows_array_ref = $sth->fetchall_arrayref();
|
||
|
|
|
||
|
|
$sth->finish();
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub db_min_copy {
|
||
|
|
|
||
|
|
my ($table,$id) = @_ ;
|
||
|
|
|
||
|
|
&db_open_upd ;
|
||
|
|
|
||
|
|
our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it
|
||
|
|
|
||
|
|
$i{id} = &db_min_get_max($table,'id') ;
|
||
|
|
|
||
|
|
my $add_upd_sql = '' ;
|
||
|
|
if ($table eq 'quotes' or $table eq 'event_quotes'){
|
||
|
|
$i{quote_nr} = &db_min_get_max($table,'quote_nr') ;
|
||
|
|
$add_upd_sql = qq~, `quote_nr`='$i{quote_nr}'~;
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($table eq 'cameras'){
|
||
|
|
$i{camera_nr} = &db_min_get_max_camera_nr($table,'camera_nr',$id) ;
|
||
|
|
$add_upd_sql = qq~, `camera_nr`='$i{camera_nr}'~;
|
||
|
|
}
|
||
|
|
|
||
|
|
my $sql = qq~CREATE TEMPORARY TABLE `#temp` SELECT * FROM $table WHERE `id`='$id';~ ;
|
||
|
|
|
||
|
|
&common_debug($sql) ;
|
||
|
|
|
||
|
|
$sth = $dbh -> do ($sql) or die "could not execute :<BR>$sql<BR>$!" ;
|
||
|
|
|
||
|
|
$sql = qq~UPDATE `#temp` SET `id`='$i{id}'$add_upd_sql WHERE `id`='$id';~ ;
|
||
|
|
|
||
|
|
&common_debug($sql) ;
|
||
|
|
|
||
|
|
$sth = $dbh -> do ($sql) or die "could not execute :<BR>$sql<BR>$!" ;
|
||
|
|
|
||
|
|
$sql = qq~INSERT INTO $table SELECT * FROM `#temp` WHERE `id`='$i{id}';~ ;
|
||
|
|
|
||
|
|
&common_debug($sql) ;
|
||
|
|
|
||
|
|
$sth = $dbh -> do ($sql) or die "could not execute :<BR>$sql<BR>$!" ;
|
||
|
|
|
||
|
|
$sql = qq~DROP TEMPORARY TABLE IF EXISTS `#temp`;~ ;
|
||
|
|
|
||
|
|
&common_debug($sql) ;
|
||
|
|
|
||
|
|
$sth = $dbh -> do ($sql) or die "could not execute :<BR>$sql<BR>$!" ;
|
||
|
|
|
||
|
|
$db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
|
||
|
|
|
||
|
|
&db_close_conn ;
|
||
|
|
|
||
|
|
$success = qq($id SUCCESSFULLY COPIED TO $i{id}) ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub db_min_get_max_camera_nr {
|
||
|
|
|
||
|
|
my ($table,$orderby,$camera_id) = @_ ;
|
||
|
|
|
||
|
|
&db_min_ro($table,'id,camera_system_id',"`id`='$camera_id'",'','') ;
|
||
|
|
# &db_min_ro($table,'id,camera_nr',"`camera_system_id`='$db{$table}{$camera_id}{camera_nr}'",'`id` DESC',1) ;
|
||
|
|
&db_min_ro($table,'id,camera_nr',"`camera_system_id`='$db{$table}{$camera_id}{camera_system_id}'",'`id` DESC',1) ;
|
||
|
|
|
||
|
|
my $max_cam_cnt = 0 ;
|
||
|
|
|
||
|
|
foreach my $id (keys %{$db{$table}}) {
|
||
|
|
($cam_type,$cam_cnt) = split(/\-/,$db{$table}{$id}{camera_nr});
|
||
|
|
$max_cam_cnt = $cam_cnt unless $max_cam_cnt > $cam_cnt;
|
||
|
|
}
|
||
|
|
|
||
|
|
$max_cam_cnt++;
|
||
|
|
|
||
|
|
my $camera_nr = $cam_type . '-' . sprintf("%04s", $max_cam_cnt) ; # e.g. S1-0001
|
||
|
|
|
||
|
|
return ($camera_nr) ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
sub db_min_get_max {
|
||
|
|
|
||
|
|
my ($table,$orderby,$where) = @_ ;
|
||
|
|
|
||
|
|
&db_open_ro ;
|
||
|
|
|
||
|
|
if ($where) { $where = 'WHERE ' . $where ; }
|
||
|
|
|
||
|
|
my $sql = qq~SELECT * FROM $table $where ORDER BY $orderby DESC LIMIT 1~ ;
|
||
|
|
|
||
|
|
&common_debug($sql) ;
|
||
|
|
|
||
|
|
my $sth = $dbh->prepare($sql) ;
|
||
|
|
|
||
|
|
$sth -> execute() or die "Could not execute SQL statement $sql ... maybe invalid? $!";
|
||
|
|
|
||
|
|
$col_cnt=0;
|
||
|
|
|
||
|
|
foreach $col (@{$sth->{NAME}}) { $col_name{$col_cnt}=$col; $col_cnt++; } # &common_debug($col); NB *** must be before fetchall_arrayref
|
||
|
|
|
||
|
|
my $rows_array_ref = $sth->fetchall_arrayref();
|
||
|
|
|
||
|
|
$sth->finish();
|
||
|
|
|
||
|
|
&db_close_conn ;
|
||
|
|
|
||
|
|
my $new_id = 1 ;
|
||
|
|
|
||
|
|
$db{$table} = () ;
|
||
|
|
|
||
|
|
foreach $row (@$rows_array_ref) {
|
||
|
|
for (0 .. $col_cnt){
|
||
|
|
$db{$table}{@$row[0]}{$col_name{$_}} = @$row[$_] ;
|
||
|
|
}
|
||
|
|
$new_id = $db{$table}{@$row[0]}{$orderby} ;
|
||
|
|
$new_id++ ;
|
||
|
|
}
|
||
|
|
|
||
|
|
return ($new_id) ;
|
||
|
|
|
||
|
|
} #------------------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
1;
|