From: ivan Date: Tue, 4 Jun 2002 14:02:18 +0000 (+0000) Subject: mysql support! thanks to Donald Greer for the SQL and X-Git-Tag: freeside_1_4_0_pre14~43 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=7fc18b419e3cec20eb7c898017673b40a7424cde mysql support! thanks to Donald Greer for the SQL and Dale Hege for the patches --- diff --git a/CREDITS b/CREDITS index 3a356f935..8f077cd8f 100644 --- a/CREDITS +++ b/CREDITS @@ -97,5 +97,9 @@ Luke Pfeifer contributed the "subscription" price plan. Noment Networks, LLC sponsored ICRADIUS/FreeRADIUS groups, message catalogs, and signup server enhancements. +Donald Greer provided the SQL to work around MySQL's lack +of subqueries, and Dale Hege provided the patches. +Thanks! + Everything else is my (Ivan Kohler ) fault. diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 2e255cfa2..b4565601b 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -80,23 +80,52 @@ if ( $cgi->param('browse') my $ncancelled = ''; + if ( driver_name eq 'mysql' ) { + + my $query = "CREATE TEMPORARY TABLE temp1_$$ TYPE=MYISAM + SELECT cust_pkg.custnum,COUNT(*) as count + FROM cust_pkg,cust_main + WHERE cust_pkg.custnum = cust_main.custnum + AND ( cust_pkg.cancel IS NULL + OR cust_pkg.cancel = 0 ) + GROUP BY cust_pkg.custnum"; + my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + $query = "CREATE TEMPORARY TABLE temp2_$$ TYPE=MYISAM + SELECT cust_pkg.custnum,COUNT(*) as count + FROM cust_pkg,cust_main + WHERE cust_pkg.custnum = cust_main.custnum + GROUP BY cust_pkg.custnum"; + my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + } + if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me || ( $conf->exists('hidecancelledcustomers') && ! $cgi->param('showcancelledcustomers') ) ) { #grep { $_->ncancelled_pkgs || ! $_->all_pkgs } - #needed for MySQL??? OR cust_pkg.cancel = \"\" - $ncancelled = " - 0 < ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - AND ( cust_pkg.cancel IS NULL - OR cust_pkg.cancel = 0 - ) - ) - OR 0 = ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - ) - "; + if ( driver_name eq 'mysql' ) { + $ncancelled = " + temp1_$$.custnum = cust_main.custnum + AND temp2_$$.custnum = cust_main.custnum + AND (temp1_$$.count > 0 + OR temp2_$$.count = 0 ) + "; + } else { + $ncancelled = " + 0 < ( SELECT COUNT(*) FROM cust_pkg + WHERE cust_pkg.custnum = cust_main.custnum + AND ( cust_pkg.cancel IS NULL + OR cust_pkg.cancel = 0 + ) + ) + OR 0 = ( SELECT COUNT(*) FROM cust_pkg + WHERE cust_pkg.custnum = cust_main.custnum + ) + "; + } + } #EWWWWWW @@ -109,10 +138,14 @@ if ( $cgi->param('browse') } $qual = " WHERE $qual" if $qual; - - my $statement = "SELECT COUNT(*) FROM cust_main $qual"; - my $sth = dbh->prepare($statement) - or die dbh->errstr. " doing $statement"; + my $statement; + if ( driver_name eq 'mysql' ) { + $statement = "SELECT COUNT(*) FROM cust_main"; + $statement .= ", temp1_$$, temp2_$$ $qual" if $qual; + } else { + $statement = "SELECT COUNT(*) FROM cust_main $qual"; + } + my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; $total = $sth->fetchrow_arrayref->[0]; @@ -124,10 +157,20 @@ if ( $cgi->param('browse') $ncancelled = " WHERE $ncancelled"; } } - my @just_cust_main = qsearch('cust_main', \%search, '', - "$ncancelled $orderby $limit" - ); + my @just_cust_main; + if ( driver_name eq /mysql/ ) { + @just_cust_main = qsearch('cust_main', \%search, 'cust_main.*', + ",temp1_$$,temp2_$$ $ncancelled $orderby $limit"); + } else { + @just_cust_main = qsearch('cust_main', \%search, '', + "$ncancelled $orderby $limit" ); + } + if ( driver_name eq 'mysql' ) { + $query = "DROP TABLE temp1_$$,temp2_$$;"; + my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + } @cust_main = @just_cust_main; # foreach my $cust_main ( @just_cust_main ) { diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index ec1bda900..7dfacf189 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -34,8 +34,7 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { #false laziness with below my $statement = "SELECT COUNT(*) FROM cust_pkg $range"; warn $statement; - my $sth = dbh->prepare($statement) - or die dbh->errstr. " doing $statement"; + my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; $total = $sth->fetchrow_arrayref->[0]; @@ -52,17 +51,6 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $sortby=\*pkgnum_sort; - $unconf = " - WHERE 0 < - ( SELECT count(*) FROM pkg_svc - WHERE pkg_svc.pkgpart = cust_pkg.pkgpart - AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc - WHERE cust_svc.pkgnum = cust_pkg.pkgnum - AND cust_svc.svcpart = pkg_svc.svcpart - ) - ) - "; - #@cust_pkg=(); ##perhaps this should go in cust_pkg as a qsearch-like constructor? #my($cust_pkg); @@ -86,20 +74,74 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { # } # push @cust_pkg, $cust_pkg if $flag; #} + + if ( driver_name eq 'mysql' ) { + #$query = "DROP TABLE temp1_$$,temp2_$$;"; + #my $sth = dbh->prepare($query); + #$sth->execute; + + $query = "CREATE TEMPORARY TABLE temp1_$$ TYPE=MYISAM + SELECT cust_svc.pkgnum,cust_svc.svcpart,COUNT(*) as count + FROM cust_pkg,cust_svc,pkg_svc + WHERE cust_pkg.pkgnum = cust_svc.pkgnum + AND cust_svc.svcpart = pkg_svc.svcpart + AND cust_pkg.pkgpart = pkg_svc.pkgpart + GROUP BY cust_svc.pkgnum,cust_svc.svcnum"; + $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + + $query = "CREATE TEMPORARY TABLE temp2_$$ TYPE=MYISAM + SELECT cust_pkg.pkgnum FROM cust_pkg + LEFT JOIN pkg_svc ON (cust_pkg.pkgpart=pkg_svc.pkgpart) + LEFT JOIN temp1_$$ ON (cust_pkg.pkgnum = temp1_$$.pkgnum + AND pkg_svc.svcpart=temp1_$$.svcpart) + WHERE ( pkg_svc.quantity > temp1_$$.count + OR temp1_$$.pkgnum IS NULL ) + AND pkg_svc.quantity != 0;"; + $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + $unconf = " LEFT JOIN temp2_$$ ON cust_pkg.pkgnum = temp2_$$.pkgnum + WHERE temp2_$$.pkgnum IS NOT NULL"; + + } else { + + $unconf = " + WHERE 0 < + ( SELECT count(*) FROM pkg_svc + WHERE pkg_svc.pkgpart = cust_pkg.pkgpart + AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc + WHERE cust_svc.pkgnum = cust_pkg.pkgnum + AND cust_svc.svcpart = pkg_svc.svcpart + ) + ) + "; + + } } else { die "Empty QUERY_STRING!"; } my $statement = "SELECT COUNT(*) FROM cust_pkg $unconf"; - my $sth = dbh->prepare($statement) - or die dbh->errstr. " doing $statement"; + my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; $total = $sth->fetchrow_arrayref->[0]; - + + #if ( driver_name eq 'mysql' ) { #remove ORDER BY for mysql? hua? + # @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf $limit" ); + #} else { + # @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); + #} @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); + if ( driver_name eq 'mysql' ) { + $query = "DROP TABLE temp1_$$,temp2_$$;"; + my $sth = dbh->prepare($query) or die dbh->errstr. " doing $query"; + $sth->execute; # or die "Error executing \"$query\": ". $sth->errstr; + } + } if ( scalar(@cust_pkg) == 1 ) { diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index e28e00e61..eba032d52 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -21,26 +21,35 @@ $query ||= ''; #to avoid use of unitialized value errors my $unlinked = ''; if ( $query =~ /^UN_(.*)$/ ) { $query = $1; - my $empty = driver_name =~ /^Pg$/i ? qq('') : qq(""); - $unlinked = " - WHERE 0 < - ( SELECT count(*) FROM cust_svc - WHERE cust_svc.svcnum = svc_acct.svcnum - AND ( pkgnum IS NULL OR pkgnum = 0 OR pkgnum = $empty ) - ) - "; + my $empty = driver_name eq 'Pg' ? qq('') : qq(""); + if ( driver_name eq 'mysql' ) { + $unlinked = "LEFT JOIN cust_svc ON cust_svc.svcnum = svc_acct.svcnum + WHERE cust_svc.pkgnum IS NULL + OR cust_svc.pkgnum = 0 + OR cust_svc.pkgnum = $empty"; + } else { + $unlinked = " + WHERE 0 < + ( SELECT count(*) FROM cust_svc + WHERE cust_svc.svcnum = svc_acct.svcnum + AND ( pkgnum IS NULL OR pkgnum = 0 OR pkgnum = $empty ) + ) + "; + } } +my $tblname = driver_name eq 'mysql' ? 'svc_acct.' : ''; my(@svc_acct, $sortby); if ( $query eq 'svcnum' ) { $sortby=\*svcnum_sort; - $orderby = 'ORDER BY svcnum'; + $orderby = "ORDER BY ${tblname}svcnum"; } elsif ( $query eq 'username' ) { $sortby=\*username_sort; - $orderby = 'ORDER BY username'; + $orderby = "ORDER BY ${tblname}username"; } elsif ( $query eq 'uid' ) { $sortby=\*uid_sort; - $orderby = ( $unlinked ? 'AND' : 'WHERE' ). ' uid IS NOT NULL ORDER BY uid'; + $orderby = ( $unlinked ? 'AND' : 'WHERE' ). + " ${tblname}uid IS NOT NULL ORDER BY ${tblname}uid"; } else { $sortby=\*uid_sort; @svc_acct = @{&usernamesearch}; @@ -55,6 +64,11 @@ if ( $query eq 'svcnum' || $query eq 'username' || $query eq 'uid' ) { $total = $sth->fetchrow_arrayref->[0]; + #if ( driver_name eq 'mysql' ) { #remove ORDER BY for mysql? hua? + # @svc_acct = qsearch('svc_acct', {}, '', "$unlinked $limit"); + #} else { + # @svc_acct = qsearch('svc_acct', {}, '', "$unlinked $orderby $limit"); + #} @svc_acct = qsearch('svc_acct', {}, '', "$unlinked $orderby $limit"); }