MYSQL Patch
Dale Hege
fhege at lumenexus.net
Mon Jun 3 15:41:12 PDT 2002
Here is most of the MYSQL patch. I havn't installed pre13 yet so I didn't
fix the queued bug. But here are at least the searchs.
I'm sure I broke something but they seem to work for me. I havn't tested
them with PGSQL.
-Dale
-------------- next part --------------
--- search/cust_main.cgi Tue Apr 16 20:25:58 2002
+++ search/cust_main.cgi Sun May 26 12:13:55 2002
@@ -1,5 +1,7 @@
<%
+use FS::UID qw(datasrc);
+
my $conf = new FS::Conf;
my $maxrecords = $conf->config('maxsearchrecordsperpage');
@@ -80,23 +82,53 @@
my $ncancelled = '';
+ if ( datasrc =~ /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. " doing $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. " doing $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 ( datasrc =~ /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 +141,16 @@
}
$qual = " WHERE $qual" if $qual;
+ my $statement;
+ if ( datasrc =~ /mysql/ ) {
+ $statement = "SELECT COUNT(*) FROM cust_main";
+ $statement .= ", temp1_$$, temp2_$$ $qual" if $qual;
+ }else{
+ $statement = "SELECT COUNT(*) FROM cust_main $qual";
+ }
- my $statement = "SELECT COUNT(*) FROM cust_main $qual";
my $sth = dbh->prepare($statement)
- or die dbh->errstr. " doing $statement";
+ or die dbh->errstr. " doing $statement";
$sth->execute or die "Error executing \"$statement\": ". $sth->errstr;
$total = $sth->fetchrow_arrayref->[0];
@@ -124,10 +162,25 @@
$ncancelled = " WHERE $ncancelled";
}
}
- my @just_cust_main = qsearch('cust_main', \%search, '',
- "$ncancelled $orderby $limit"
- );
+ my @just_cust_main;
+
+ if ( datasrc =~ /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 ( datasrc =~ /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;
+ }
@cust_main = @just_cust_main;
# foreach my $cust_main ( @just_cust_main ) {
diff -ur search/cust_pkg.cgi search/cust_pkg.cgi
--- search/cust_pkg.cgi Fri May 3 21:11:04 2002
+++ search/cust_pkg.cgi Thu May 30 22:09:14 2002
@@ -1,5 +1,7 @@
<%
+use FS::UID qw(datasrc);
+
my $conf = new FS::Conf;
my $maxrecords = $conf->config('maxsearchrecordsperpage');
@@ -49,56 +51,100 @@
$sortby=\*pkgnum_sort;
} elsif ( $query eq 'APKG_pkgnum' ) {
+
$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);
- #foreach $cust_pkg (
- # qsearch('cust_pkg',{}, '', "ORDER BY pkgnum $limit" )
- #) {
- # my($flag)=0;
- # my($pkg_svc);
- # PKG_SVC:
- # foreach $pkg_svc (qsearch('pkg_svc',{ 'pkgpart' => $cust_pkg->pkgpart })) {
- # if ( $pkg_svc->quantity
- # > scalar(qsearch('cust_svc',{
- # 'pkgnum' => $cust_pkg->pkgnum,
- # 'svcpart' => $pkg_svc->svcpart,
- # }))
- # )
- # {
- # $flag=1;
- # last PKG_SVC;
- # }
- # }
- # push @cust_pkg, $cust_pkg if $flag;
- #}
-
+
+ if ( datasrc =~ /mysql/ ) {
+ my $tempscreate=1;
+ $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. " doing $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. " doing $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
+ )
+ )
+ ";
+
+ #@cust_pkg=();
+ ##perhaps this should go in cust_pkg as a qsearch-like constructor?
+ #my($cust_pkg);
+ #foreach $cust_pkg (
+ # qsearch('cust_pkg',{}, '', "ORDER BY pkgnum $limit" )
+ #) {
+ # my($flag)=0;
+ # my($pkg_svc);
+ # PKG_SVC:
+ # foreach $pkg_svc (qsearch('pkg_svc',{ 'pkgpart' => $cust_pkg->pkgpart })) {
+ # if ( $pkg_svc->quantity
+ # > scalar(qsearch('cust_svc',{
+ # 'pkgnum' => $cust_pkg->pkgnum,
+ # 'svcpart' => $pkg_svc->svcpart,
+ # }))
+ # )
+ # {
+ # $flag=1;
+ # last PKG_SVC;
+ # }
+ # }
+ # push @cust_pkg, $cust_pkg if $flag;
+ #}
+ }
+
} else {
die "Empty QUERY_STRING!";
}
my $statement = "SELECT COUNT(*) FROM cust_pkg $unconf";
+
my $sth = dbh->prepare($statement)
or die dbh->errstr. " doing $statement";
$sth->execute or die "Error executing \"$statement\": ". $sth->errstr;
$total = $sth->fetchrow_arrayref->[0];
-
- @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" );
+ if ( datasrc =~ /mysql/ ) {
+ @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf $limit" );
+ }else{
+ @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" );
+ }
+ if ( (datasrc =~ /mysql/) && $tempscreate) {
+ $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;
+ }
}
diff -ur search/svc_acct.cgi search/svc_acct.cgi
--- search/svc_acct.cgi Fri Mar 29 12:27:18 2002
+++ search/svc_acct.cgi Thu May 30 22:33:19 2002
@@ -1,5 +1,7 @@
<%
+use FS::UID qw(datasrc);
+
my $mydomain = '';
my $conf = new FS::Conf;
@@ -22,22 +24,29 @@
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 )
- )
- ";
+ if ( datasrc =~ /mysql/ ) {
+ $unlinked="left join cust_svc on cust_svc.svcnum = svc_acct.svcnum where cust_svc.pkgnum IS NULL";
+ }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 $tlbname='';
+if ( datasrc =~ /mysql/ ) {
+ $tblname="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';
@@ -54,9 +63,11 @@
$sth->execute or die "Error executing \"$statement\": ". $sth->errstr;
$total = $sth->fetchrow_arrayref->[0];
-
- @svc_acct = qsearch('svc_acct', {}, '', "$unlinked $orderby $limit");
-
+ if ( datasrc =~ /mysql/ ) {
+ @svc_acct = qsearch('svc_acct', {}, '', "$unlinked $limit");
+ }else{
+ @svc_acct = qsearch('svc_acct', {}, '', "$unlinked $orderby $limit");
+ }
}
if ( scalar(@svc_acct) == 1 ) {
More information about the freeside-devel
mailing list