[ic] Advice needed for IC/eBay API integration

[Paul Jordan]

Grant <listbox at email.com> wrote:
>> Grant <listbox at email.com> wrote:
>>> I'm now very close to being finished with the IC/eBay API
>>> integration. eBay's developer testing site was broken for about 10
>>> days or so, but it started working again a couple days ago and I
>>> should be done very soon.  The integration will consist of
>>> something like this:
>>>
>>> - 5 UserTags
>>> - 7 jobs files
>>> - 7 jobs directives
>>> - 1 new page
>>> - 1 new variable table entry
>>> - new fields in transactions and inventory tables
>>> - new ebay table
>>> - log_transaction modifications
>>>
>>> It will be going up at Kevin Walsh's site (
>>> www.interchange.rtfm.info ) along with detailed info on how it all
>>> works and how to use it.  Of course, I will post here when I have
>>> it up.
>>>
>>> So I guess that was an update.
>>>
>>> Anyway, I'm facing an important decision in the design of the whole
>>> thing, and I'd really appreciate some advice on it.  Basically, I
>>> could change around the way it works and make it quite a bit cleaner
>>> and more extensible, but it would require adding every item listed
>>> on eBay as an entry in the new ebay table.  That could really get
>>> to be a lot of entries that aren't being used for anything unless
>>> the item ends up being sold.  Should I:
>>>
>>> a) not write every listed item as an entry in the ebay table and
>>> sacrifice some cleanliness and extensibility
>>> b) write every listed item as an entry in the ebay table, but delete
>>> each record that isn't needed after the listing has ended
>>> c) write every listed item as an entry in the ebay table and not
>>> worry about the bloated database
>>>
>>> I'm leaning toward b.  If you agree, how can a record be deleted
>>> from a table without going through the UI?
>>
>>
>> C is better, because every item, even if it sells, is an
>> accountable item. So,
>> there will be ebay list fees (possibly optional listing fees like
>> BOLD, etc),
>> and maybe even fees for using YOUR app, regardless if the item sells.
>>
>> Even if you add 500 items a week, that is only 6000 a year.
>> depending on how
>> you have the accounting setup, that means maybe at most 25,000
>> entries a year,
>> which isn't bad, especially if you placing 500 items a week on ebay.
>>
>> I would let the users flag an item for deletion, depending on
>> whether or not
>> they want to delete it. If you app is charging fees, they
>> shouldn't be able to
>> delete it obviously, as it would cause you more customer service
>> headaches later if they cannot go back and see their account
>> history, with associated item (visual proof you are not charging
>> them for bogus items). I
>> am guessing
>> your app hosts the images as well yes? I would have those deleted
>> every once in
>> a while, based on corresponding item rows end_date.
>>
>> If you will let them delete, have them flag it, then run through
>> with a weekly
>> cron to delete all rows flagged (and respective rows in other tables
>> if necessary). JOBS sounds like a timed run of ITL, which is
>> probably even easier.
>>
>> Of course without an overview of what you are doing with the
>> tables, what their
>> part is in the grand schema, and how this app should work, any
>> advice will be
>> general advice.
>>
>> HTH
>> Paul
>
> Paul, thanks for your response.  I should have included more info on
> how the app works in my initial post.
>
> The app will do the following, but any of the mentioned specifics can
> be easily changed:
>
> - list regular eBay listings:
> Every 3 days, the top 50 best-selling (based on existing "Generate
> others who..." functionality) qualified items will be listed as
> single-quantity, 3-day, fixed-price listings.


Note: There should be a flag column for this also, denoting the ability to
place on ebay. there may be situations where an item in a store is OK, but
maybe inapropriate for ebay, so one would not want it being automagically place
on ebay.



> - list eBay Store listings:
> Every hour, any products not currently listed in your eBay Store will
> be listed as "Good-Til-Canceled" listings.
>
> - associate orders with auctions:
> Auction winners are directed to your auction page where they can
> enter their auction number(s) and eBay user ID, and have their
> shopping cart populated with any items they purchased from your eBay
> listings.  They can then continue shopping or checkout right away,
> and the IC order is placed as normal, except it is associated with
> the auction number(s) they entered.


IMO, the ebay items should be immutable once in the cart. Adding other itmes is
fine, but deleting auction items may cause trouble for you.


>
> - leave positive feedback on eBay for transactions in which the item
> has been shipped: Every day, positive feedback will be left for eBay
> transactions in which the purchased items have been shipped to the
> buyer.
>
> - relist ended and unsold item listings:
> Every 3 days, any ended and unsold listings will attempt to be
> relisted. eBay's rules say unsold items can only be relisted once,
> and are not subject to the insertion fee.
>
> - send email notifications to the buyer (payment instructions,
> payment reminder): Every hour, emails are sent to any auction winners
> (once each) directing them to your auction page, and reminders are
> sent to auction winners that haven't placed their order through your
> IC store within 7 days of the auction's end (once each).
>
> - send email notifications to you (submit a Non-Paying Bidder form to
> eBay, submit a Final Value Fee Credit form to eBay):
> The submission of the Non-Paying Bidder and Final Value Fee Credit
> forms to eBay must be done manually so an email is sent to you with
> the necessary information and a link to the appropriate page on the
> eBay site.  NPB emails are sent 14 days after an auction's end if
> necessary, and FVFC emails are sent 21 days after an auction's end if
> necessary.
>
> The focus is on automation.  Off the top of my head, the only input
> that is required from you after setting it up is changing an order's
> status to "shipped" to trigger the positive feedback when the IC job
> comes along.  I think most people do that anyway.  I used a much more
> manual-type system to manage eBay listings with IC in the same way
> for many months before I dug into this project.  All bugs in the
> general design of the system have been ironed out.


This sounds like a pretty nice project Grant. One thing to make sure is that it
degrades nicely. If a bidder chooses to bypass the automation, will it be a
headache to get back on track? IOW I assume paymant can be accepted other ways
(mail-check, mail-money_order, fax-cc, paypal). If someone just goes to paypal
and sends you cash, will it be a pain to get the system back on track?

I know it may seem wierd, but I have been leary of sellers payment systems
before. It was probably not an issue, but ebay is getting an air of seedyness
about it. I almost hate selling now it is such a pain with NPB's.

The point is, I hope you are stll able to accept payment outside the app, or it
may detrimentally effect your auctions IMO for the foreseeable future.


> This will all be donated to the IC community.
>
> Now what do you think about writing all listed items to a database?
> If you think that should be done, should unsold item records be
> deleted once their use is up?  If so, how can that be done in IC
> using a DBM database?


why dbm? Not that it matters, but the more advanced IC gets, the sooner it will
have to (should) cut ties with non sql db's. It sounds like you have a pretty
advanced app, not something that would be utilized by users not using a sql db.
I mean, even the $4.95 Cpanel accounts come with a Mysql db 8-) actually...
they come with UNLIMTED Mysql db's... haha!

If this was a general "see what others are buying" type thing, that is one
thing, but there is no point in not optimizing what sounds like a very nice
app, and intend it for serious users.

I am on a couple projects right now that are very very slick, and they are SQL
only. I noticed your post the other day about accounting. Part of the project I
am doing will be going that direction. I still have not weighed the pros and
cons of integrating a pre-existing OS accounting app, or building it from
scratch. I would be interested in possibly kicking around some ideas, and maybe
we could help each other out if your interested.

WRT deleting the items, do you mean the item description only? or the
accompanying transaction rows? I assume inserting an item creates a transaction
row?

Anyways, you are familiar with a JOB... is it a timed ITL run? If so, just
query the database and find all items that were relisted and not sold (however
you are flagging that), iterate through them, and with the [query] tag, do a
DELETE. I assume that will work with dbm (and sql_statement).


Paul