Good job Paul. There's just one change I'd make. Instead of having the
cron job call a Perl script, I'd have a cron job use Lynx to make a hit
on the Web site with an appropriate use of htaccess. This way all of
your code is in the same language ;)
Cheers,
-jj
Paul Chamberlain wrote:
>
> "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]
--
if (shannon - jj) * behrens == webEngineer["CLEAR INK�"]:
print "<i>imagination is the only real medium(sm)</i><br>"
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Site: http://www.working-dogs.com/freetrade/
Problems?: [EMAIL PROTECTED]
