PhishThis!

If you followed the last post, this one is a continuation.

So, you’ve imported all of your products into the products table with the last script. Next, you need to add the information about the products to products_to_categories, so the categories are properly mapped, and to products_descriptions, so you have a name, etc.

 You’ll need the products_id of the FIRST product you inserted with the last script.

Check out this PowerShell script: More…

• Continue reading or Leave a comment... (0)

I’ve been busy tonight working on ChaosCollectibles, trying to get all of the products added. Since the first thing I did was grab all of the images, I was done “scanning.” Next, I needed to add them all to the site.

Dave had spent about 5 hours in total adding around 100 cards. We still had 140 to go, and let’s face it…I’m lazy. Well, not lazy. I just prefer scripting and saving time where I can. It’s like the old saying around work: “Work Smart.”

 Since I had a standard naming convention for the image files, I was able to use those as a basis for my import. My hosting provider give me SQL access through PHPMyAdmin, so I can run SQL scripts…I just needed some SQL queries and stuff to run.

Product information is stored in 3 tables in the database for ZenCart. These tables are “products”, “products_description”, and “product_to_categories.” Products has a TON of columns. The others have 5 and 2, respectively. Now, as I was saying, I had a standard image naming convention. That convention was CTDOP_cardname.jpg.

 Based on that, I could do some image manipulation, and fill out some basic data I needed for the product table. The products has a column called products_id that is auto-incremented. This column is referenced by the other two tables. To fill out the products table with the card-specific information, I needed the following:

Product name, image location, date added, model name, and a few other fields that I could have made static, but chose to use variables for. Since I used that handy naming convention above, I was able to manipulate the names of every image file into something I could insert into the database to keep with the other conventions. For example, my model name convention is CTDOPcardname (no underscores). My image location was…the name of the file. That’s easy enough. Then I had to format some dates, and output my SQL statement. So, I’m tired of blabbing. Here it is.

Edit: OK, I was having issues with my code formatting. I really need a new, code-friendly template. I’m open to suggestions.

Here’s the script: Add-Product.ps1 - PowerShell script to generate ZenCart products

So, you’ll be left with a long list of SQL statements. But, we’ve still got the other two tables. Import that list anyway. Make a note of the FIRST products_id that gets imported, after the import completes.

The next step involves checking that table you just imported all of those products into. Since products_id auto-increments, you need to see where you started, and where you finished and make a note of those numbers.

I’m going to end this one right here and add a second post. This is getting too long, I think. Check here.

• Leave a comment... (0)

I finally did it. I got the e-commerce bug and started my own shop. I’m working with Dave K on it, and we’re progressing quite well with a lot of mentoring from Bill and all of his XekoShop experience.

So, PhishThis will contain a lot of posts related to how I’m making like easier for myself as far as batch watermarking, getting images, etc.

Here’s one:

I need to scan all of my cards for Chaotic. But they have them all online and could save me some trouble. However, I don’t feel like right-click-saving 250 times. Enter, you guessed it, PowerShell.

I need to create a web client to connect to the site.
$wc = new-object system.net.webclient
Now, the site requires a login. You might say “CRAP!” However, the webclient supports passing credentials, as long as they’re typed as system.net.networkcredential

So, I can login to the site, from PowerShell with:
$wc.credentials = new-object system.net.networkcredential("username", "password")

Now, there were around 250 images, so I construct a simple for loop that uses $wc.downloadfile(”source”, “location”) and we end up with:

$wc = new-object system.net.webclient
$wc.credentials = new-object system.net.networkcredential("username", "password")
for ($i = 0; $i -lt 250;$i++)
{
  $wc.downloadfile("www.foobar.com/getimages.aspx?ImageID=$($i)", "C:\images\$($i).jpg")
}

This script saved me HOURS of scanning and stuff…and now, even if the card is out of stock, I still have the image. That makes sense in my head.  

After, I set my view for that folder to EXTRA LARGE icons. This shows a readable preview of the jpeg. I renamed the first one, and instead of hitting enter after each one, I hit tab, and it went to the next file to rename it. I didn’t know you could do that, and tried on a whim. Saved me many keystrokes.

Next, I’m going to need to add them all to Zen Cart. Since I’m lazy a script kiddy, I’m going to write a script that will look at all of the product images in a folder, and write my SQL queries based on them. Hopefully I’ll even get it to do all of my formatting, too. We’ll see. Look for that this weekend sometime.

• Leave a comment... (0)

Well, finally a post after the chaos that was the holidays. I was on vacation for 12 days and accomplished…nothing. Except for a lot of Counterstrike. And Team Fortress 2.

If you’re running SCOM 2007 and BizTalk 2006 (and have the SQL agent jobs properly configured) you may notice a warning in SCOM that says there are long running jobs. Upon inspection, you’ll discover that a single job (ManageRefCountLog) has been running since the SQL Agent last started (or the minute immediately following). I ignored this for quite some time, leaving the alert ACK’d the whole time because I couldn’t figure out why the job was running forever…that, and everything was working fine. I finally got fed up with it today and did a little digging. The error looks like this:

There are long running jobs on SQL instance MSSQLSERVER on computer SERVER.FOO.BAR.COM. This may indicate an issue with one or more jobs.

The ManageRefCountLog job has only one step that runs a stored proc on BizTalkMsgBoxDB called bts_ManageMessageRefCountLog. If you look at the store procedure itself, you’ll see:

WHILE (1 = 1)
BEGIN

So, the job is supposed to run forever…or until 1 != 1. Whichever comes first.I never would have noticed this job running forever, if not for SCOM2007 + the SQL 2005 management pack. Now I just need to add an exception. I’ll do that tomorrow…

• Leave a comment... (0)