Code: Select all
#!/usr/bin/perl -w
#- ShopHelper.pl
#- Justin Velluppillai
#
package ShopHelper;
use strict;
use DBI;
use DBD::mysql;
#############################
# #
# SET THESE TO YOUR STORE #
# #
#############################
use constant DATABASE => 'XXXXXXXXXXXXXXX';
use constant USERNAME => 'XXXXXXXXXXX';
use constant PASSWORD => 'XXXXXXXXXXX';
use constant HOSTNAME => 'localhost';
use constant PORTNUM => '3306';
##############################
# #
# OPENCART TABLE NAMES #
# #
##############################
use constant PRODUCT_T => 'product';
use constant PRODUCT_STORE_T => 'product_to_store';
use constant PRODUCT_DESC_T => 'product_description';
use constant PRODUCT_CATEGORY_T => 'product_to_category';
use constant MANU_T => 'manufacturer';
use constant MANU_STORE_T => 'manufacturer_to_store';
use constant CATEGORY_T => 'category';
use constant CATEGORY_DESC_T => 'category_description';
use constant CATEGORY_STORE_T => 'category_to_store';
#
# new
# Set up a new ShopHelper which will hold db info and connection
#
sub new {
my ($this) = @_;
my $class = ref($this) || $this;
my $self = {};
my $dsn = "dbi:mysql:" . DATABASE . ":" . HOSTNAME . ":" . PORTNUM;
$self->{dbi} = DBI->connect($dsn, USERNAME, PASSWORD);
bless ($self, $class);
return $self;
}
#
# AddProduct($self,$fields,$values)
# add a product to the store
# minimum required fields: sku,stock_status_id,model,status,quantity,cost,price,image,manufacturer_id,weight_class_id,length_class_id
#
sub AddProduct
{
my $self = shift;
my $sku = shift;
my $fields = shift;
my $values = shift;
#Add to the product table
my $query ="INSERT INTO " . PRODUCT_T . " $fields VALUES $values";
my $sth = $self->{dbi}->prepare($query);
my $success = $sth->execute();
if (!$success) {
$sth->finish();
return -1;
}
my $prodid = $sth->{mysql_insertid};
$sth->finish();
#Add to the product_to_store table
$query ="INSERT INTO " . PRODUCT_STORE_T . " (product_id,store_id) VALUES ($prodid,0)";
$sth = $self->{dbi}->prepare($query);
$success = $sth->execute();
$sth->finish();
if (!$success) {
return -1;
}
return $prodid;
}
#
# AddProductDescription($self,$fields,$values)
# add description info to a product
# possible fields: product_id, description, name, language_id, meta_keywords, meta_description
#
sub AddProductDescription
{
my $self = shift;
my $fields = shift;
my $values = shift;
#Add to the product_description table
my $query ="INSERT INTO " . PRODUCT_DESC_T . " $fields VALUES $values";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
}
#
# UpdateDescription($self,$sku,$new_desc)
#
sub UpdateDescription
{
my $self = shift;
my $sku = shift;
my $new_desc = shift;
my $prodid = $self->GetProductID($sku);
if ($prodid >= 0) {
my $query = "UPDATE " . PRODUCT_DESC_T . " SET description=" . $new_desc . " WHERE product_id=" . $prodid;
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
}
}
#
# UpdateTitle($self,$sku,$new_title)
#
sub UpdateTitle
{
my $self = shift;
my $sku = shift;
my $new_title = shift;
my $prodid = $self->GetProductID($sku);
if ($prodid >= 0) {
my $query = "UPDATE " . PRODUCT_DESC_T . " SET name=" . $new_title . ",meta_description=" . $new_title . " WHERE product_id=" . $prodid;
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
}
}
#
# DeleteProductByID($self,$prodid)
# delete a product from the store
#
sub DeleteProductByID
{
my $self = shift;
my $prodid = shift;
my $query = "DELETE from " . PRODUCT_T . " where 'product_id'=$prodid";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
$query = "DELETE from " . PRODUCT_STORE_T . " where 'product_id'=$prodid";
$sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
$query = "DELETE from " . PRODUCT_DESC_T . " where 'product_id'=$prodid";
$sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
$query = "DELETE from " . PRODUCT_CATEGORY_T . " where 'product_id'=$prodid";
$sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
# MAYBE OTHER TABLES TO DELETE THIS PRODUCT FROM TOO?
# offers, specials, related...
}
#
# FlagProductAsRemoved($self,$prodid)
# flag a product as removed from the store
#
sub FlagProductAsRemoved
{
my $self = shift;
my $prodid = shift;
my $query ="UPDATE " . PRODUCT_T . " SET stock_status_id=5 WHERE product_id = $prodid";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
}
#
# ZeroAllStock($self)
# set all stock to zero and stock_status_id to no stock
#
sub ZeroAllStock
{
my $self = shift;
my $query = "UPDATE " . PRODUCT_T . " SET stock_status_id=5,quantity=0";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
}
#
# GetProductID($self,$sku)
# gets a product id for a product that has a certain value in a certain field.
# mainly useful if you have a unique identifier in one of the other db fields
#
sub GetProductID
{
my $self = shift;
my $sku = shift;
if ($self->ProductExists($sku)) {
my $query ="SELECT product_id FROM " . PRODUCT_T . " WHERE sku = $sku";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
my $row = $sth->fetchall_arrayref();
$sth->finish();
return $row->[0]->[0];
}
else {
return -1;
}
}
#
# UpdateProduct($self,$sku,$fields_values)
#
sub UpdateProduct
{
my $self = shift;
my $sku = shift;
my $fields_values = shift;
my $query ="UPDATE " . PRODUCT_T . " SET $fields_values WHERE sku = $sku";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
#Timestamp the product
$query = "UPDATE " . PRODUCT_T . " SET date_modified=NOW() WHERE sku = $sku";
$sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
#doesn't matter if fails, so don't check success
}
#
# ProductExists($self,$sku)
# Checks whether or not a product already exists
#
sub ProductExists
{
my $self = shift;
my $sku = shift;
my $query = "SELECT * FROM " . PRODUCT_T . " WHERE sku = $sku";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
my $rs = $sth->fetchall_arrayref();
$sth->finish();
if ($rs->[0]) {
return 1;
}
else {
return 0;
}
}
#
# EmptyTable($self,$tablename)
#
sub EmptyTable
{
my $self = shift;
my $table = shift;
my $query ="TRUNCATE TABLE $table";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
}
#
# AddCategory($self,$catname)
# Add a category to the shop
#
sub AddCategory
{
my $self = shift;
my $catname = shift;
if ($self->CategoryExists($catname)) {
return $self->GetCategoryID($catname);
}
else {
my $query = "INSERT INTO " . CATEGORY_T . " (status) VALUES (1)";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
my $key = $sth->{mysql_insertid};
$sth->finish();
#Add the category name into category_description table
$query = "INSERT INTO " . CATEGORY_DESC_T . " (name,category_id,language_id) VALUES (" . $catname . ",$key,1)";
$sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
#Add the category to the category to store table
$query = "INSERT INTO " . CATEGORY_STORE_T . " (store_id,category_id) VALUES (0,$key)";
$sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
return $key;
}
}
#
# GetCategoryID($self,$catname)
# Get the category_id of a given category name
#
sub GetCategoryID
{
my $self = shift;
my $catname = shift;
if ($self->CategoryExists($catname)) {
my $query = "SELECT category_id FROM " . CATEGORY_DESC_T . " WHERE name = $catname";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
my $row = $sth->fetchall_arrayref();
$sth->finish();
return $row->[0]->[0];
}
return -1;
}
#
# CategoryExists($self,$catname)
# Checks whether or not a category already exists
#
sub CategoryExists
{
my $self = shift;
my $catname = shift;
my $query = "SELECT * FROM " . CATEGORY_DESC_T . " WHERE name = $catname";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
my $rs = $sth->fetchall_arrayref();
$sth->finish();
if ($rs->[0]) {
return 1;
}
else {
return 0;
}
}
#
# AddProductToCategoryName($self,$prodid,$catname)
#
sub AddProductToCategoryName
{
my $self = shift;
my $prodid = shift;
my $catname = shift;
my $catid = $self->GetCategoryID($catname);
$self->AddProductToCategoryID($prodid, $catid) if ($catid != -1);
}
#
# AddProductToCategoryID($self,$prodid,$catid)
#
sub AddProductToCategoryID
{
my $self = shift;
my $prodid = shift;
my $catid = shift;
my $query = "INSERT INTO " . PRODUCT_CATEGORY_T . " (product_id,category_id) VALUES ($prodid,$catid)";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
}
#
# AddManufacturer($self,$manuname)
# Adds a new manufacturer to the shop
#
sub AddManufacturer
{
my $self = shift;
my $manuname = shift;
if ($self->ManufacturerExists($manuname)) {
return $self->GetManufacturerID($manuname);
}
else {
#Add the manufacturer to the manufacturer table
my $query ="INSERT INTO " . MANU_T . " (name) VALUES ($manuname)";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
my $key = $sth->{mysql_insertid};
$sth->finish();
#Add the manufacturer to the manufacturer to store table
$query = "INSERT INTO " . MANU_STORE_T . " (manufacturer_id) VALUES ($key)";
$sth = $self->{dbi}->prepare($query);
$sth->execute();
$sth->finish();
return $key;
}
}
#
# GetManufacturerID($self,$manuname)
# get the manufacturer_id of this manufacturer name
#
sub GetManufacturerID
{
my $self = shift;
my $manuname = shift;
if ($self->ManufacturerExists($manuname)) {
my $query = "SELECT manufacturer_id FROM " . MANU_T . " WHERE name = $manuname";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
my $row = $sth->fetchall_arrayref();
$sth->finish();
return $row->[0]->[0];
}
else {
return -1;
}
}
#
# ManufacturerExists($self,$manuname)
# Checks whether or not a manufacturer already exists
#
sub ManufacturerExists
{
my $self = shift;
my $manuname = shift;
my $query = "SELECT * FROM " . MANU_T . " WHERE name = $manuname";
my $sth = $self->{dbi}->prepare($query);
$sth->execute();
my $rs = $sth->fetchall_arrayref();
$sth->finish();
if ($rs->[0]) {
return 1;
}
else {
return 0;
}
}
#
# DBSafe($self,$itemkey)
# returns a database safe version of the input string
#
sub DBSafe
{
my $self = shift;
my $word = shift;
return $self->{dbi}->quote($word);
}
1;
If I were to do something like this again though, I'd probably make use of the functions in admin/model/catalog/product.php (and the other files in that directory) rather than right my own like this. If you access those files directly via a script you'll be a bit safer.
Good luck.
