"Shawn" <[EMAIL PROTECTED]> wrote privately:
> I would love to get the code from you to track inventory in the carts, I
> have been trying to do this but am not having any luck. I'm still new to
> PHP and MySQL and trying to come up to speed.
I have tried to separate my changes for this from the other
changes that I've made and I've given up. But I can summarize
the changes that I made.
include/init Disable the session and invoice expiration
actions/SUBMIT_ORDER Disable the inventory adjustments.
actions/ADD_BASKET Adjust inventory
actions/UPDATE_BASKET Adjust inventory
include/purgeInvoice Adjust inventory
I don't use actions/UPDATE_INVOICE_SKU, but a similar change
would have to be made there if you use it.
And then add a cron job which expires sessions and stale
inactive invoices and returns their inventory. I've
included my perl script below that does this.
I have a patch file where I was trying to put together the
changes for this but I'm sure there are unrelated changes,
assumptions that are specific to my installation, changes
that work for me that will break for you, and just out-n-out
bugs. If you still want it after that disclaimer, ask me.
--
Paul Chamberlain, [EMAIL PROTECTED]
#!/usr/bin/perl
# expiresessions: return items from carts to inventory
# Author: Paul Chamberlain ([EMAIL PROTECTED])
use Getopt::Std;
use DBI;
sub usage {
my $s = shift;
print STDERR "$0: $s\n";
print STDERR "Usage: $0 [options]\n";
print STDERR " -v verbose\n";
print STDERR " -n do nothing\n";
print STDERR " -d debug output\n";
exit(1);
}
if (!getopts("dnv")) {
&usage("Unexpected option");
}
# Reverse default for these options
$opt_v = !$opt_v;
#$opt_n = !$opt_n;
$opt_d = !$opt_d;
$dbh = DBI->connect("DBI:mysql:database=freetrade");
if (!$dbh) {
die "freetrade: " . $DBI::errstr . "\n";
}
&deletesessions();
&cleanbaskets();
&deleteinvoices();
exit(0);
sub deletesessions {
$sql = "DELETE FROM session WHERE"
. " unix_timestamp(LastAction) < unix_timestamp(now()) - 86400";
print "Running query: $sql\n" if ($opt_d);
$dbh->do($sql) if (!$opt_n);
}
sub cleanbaskets {
my $sql;
my $sth;
my $update;
my $delete;
my $row;
$update = $dbh->prepare("UPDATE inventory SET"
. " Available=Available+?"
. " WHERE SKU=?");
if (!$update) {
print STDERR "Error: " . $dbh->errstr;
return;
}
$delete = $dbh->prepare("DELETE FROM session_sku"
. " WHERE ID=?");
if (!$delete) {
print STDERR "Error: " . $dbh->errstr;
return;
}
# Get the list of session_sku's that have no session
$sql = "select s.* from session_sku s"
. " LEFT JOIN session ON s.Session=session.ID"
. " WHERE session.ID IS NULL";
print "Running query: $sql\n" if ($opt_d);
$sth = $dbh->prepare($sql);
if (!$sth || !$sth->execute) {
print STDERR "Query failed: $sql\n";
print STDERR "Error: " . $dbh->errstr;
return;
}
while ($row = $sth->fetchrow_hashref) {
$qty = $row->{Quantity};
if ($opt_v) {
print "ID " . $row->{ID} . ": Returning "
. $qty
. " to " . $row->{SKU}
. " (" . $row->{ExternalSKU} . ")\n";
}
if (!$opt_n) {
$update->execute($qty, $row->{SKU});
$delete->execute($row->{ID});
}
}
}
sub deleteinvoices {
my $row;
my $sth;
# Get the list of invoices that have were never completed
my $sql = "SELECT ID FROM invoice"
. " WHERE ACTIVE='N'"
. " AND unix_timestamp(Created) < unix_timestamp(now()) - 86400";
print "Running query: $sql\n" if ($opt_d);
$sth = $dbh->prepare($sql);
if (!$sth || !$sth->execute) {
print STDERR "Query failed: $sql\n";
print STDERR "Error: " . $dbh->errstr;
return;
}
while ($row = $sth->fetchrow_hashref) {
$id = $row->{ID};
if ($opt_v) {
print "Invoice $id\n";
}
$sql = "DELETE FROM billing WHERE Invoice=$id";
print "Running query: $sql\n" if ($opt_d);
$dbh->do($sql) if (!$opt_n);
$sql = "DELETE FROM shipping WHERE Invoice=$id";
print "Running query: $sql\n" if ($opt_d);
$dbh->do($sql) if (!$opt_n);
&deleteinvoiceskus($id);
$sql = "DELETE FROM invoice WHERE ID=$id";
print "Running query: $sql\n" if ($opt_d);
$dbh->do($sql) if (!$opt_n);
}
}
sub deleteinvoiceskus {
my $invoice = shift;
my $sql;
my $sth;
my $update;
my $delete;
my $row;
$update = $dbh->prepare("UPDATE inventory SET"
. " Available=Available+?"
. " WHERE SKU=?");
if (!$update) {
print STDERR "Error: " . $dbh->errstr;
return;
}
$delete = $dbh->prepare("DELETE FROM session_sku"
. " WHERE ID=?");
if (!$delete) {
print STDERR "Error: " . $dbh->errstr;
return;
}
# Get the list of session_sku's that have no session
$sql = "select * from invoice_sku"
. " WHERE Invoice=$invoice";
print "Running query: $sql\n" if ($opt_d);
$sth = $dbh->prepare($sql);
if (!$sth || !$sth->execute) {
print STDERR "Query failed: $sql\n";
print STDERR "Error: " . $dbh->errstr;
return;
}
while ($row = $sth->fetchrow_hashref) {
$qty = $row->{Quantity};
if ($opt_v) {
print "ID " . $row->{ID} . ": Returning "
. $qty
. " to " . $row->{SKU}
. " (" . $row->{ExternalSKU} . ")\n";
}
if (!$opt_n) {
$update->execute($qty, $row->{SKU});
$delete->execute($row->{ID});
}
}
}
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Site: http://www.working-dogs.com/freetrade/
Problems?: [EMAIL PROTECTED]
