A list of SQL queries you can use when exporting to E-commerce sites

To export a single invoice record as an XML

 Select * from Invoice where InvUnique = xxx

To export invoices to E-commerce site with customer that has ‘Web’ as lookup words

Any invoices

 Select * from Invoice i where i.Acct in (Select a.aunique from Account a where a.aUnique in (Select l.kwunique from LkupWord l where l.FileNumber = 2 and l.word = 'Web'))

A/R type invoices

 Select * from Invoice i where NvType = 'A' and i.Acct in (Select a.aunique from Account a where a.aUnique in (Select l.kwunique from LkupWord l where l.FileNumber = 2 and l.word = 'Web'))

W type invoices

 Select * from Invoice i where NvType = 'W' and i.Acct in (Select a.aunique from Account a where a.aUnique in (Select l.kwunique from LkupWord l where l.FileNumber = 2 and l.word = 'Web'))

A/R and W type invoices

 Select * from Invoice i where NvType = 'A' or NvType = 'W' and i.Acct in (Select a.aunique from Account a where a.aUnique in (Select l.kwunique from LkupWord l where l.FileNumber = 2 and l.word = 'Web'))

To export a single inventory record

 Select InvUnique from Inventry where InvUnique = xxx

To export only the inventory marked for E-commerce

 Select InvUnique from Inventry where ECommerce = 'Y'

To remove items from the website

 Select InvUnique from Inventry where ECommerce = 'N'

To export all items marked for E-commerce in a specific category

Select InvUnique from Inventry where ECommerce = 'Y' and Cat = xxx

To remove items from the website in a specific category

Select InvUnique from Inventry where ECommerce = 'N' and Cat = xxx

To export a single customer record

Select * from Account where Aunique = xxx

Edit

Helpful SQL Commands for WECS

Edit

Here’s a list of SQL queries you can execute on the database (phpMyAdmin) to fix some issues (BACKUP FIRST)

To set all user’s credit limit back to 0.

update user set creditlimit = 0

Allow invoices on WECS to store Credit card information. The newly created invoices will import the credit card details and save it on the invoice comments tab (Non-PCI Compliant. Obsolete as of version 2.5.30)

Alter ABLE payment ADD COLUMN transactionid varchar(64) not null default ‘’;

Edit

Here’s the SQL query to “data restart” E-commerce websites. The SQL query needs to be executed on phpMyAdmin. This will not delete the settings and website configuration. (BACKUP FIRST)

SQL to clear inventories

delete from alternate_supplier; delete from cart; delete from cart_items; delete from categories; delete from categorymap; delete from free_form; delete from inventory; delete from inventory_hit; delete from inventory_locations; delete from inventory_lookupwords; delete from kit; delete from part_pics; delete from price_schedule;

SQL to clear customer records. This command will not remove userid 1 since that userid serves as the default template for customers for WECS

delete from cart; delete from cart_items; delete from cart_mods; delete from contractor; delete from invoice; delete from invoice_item; delete from payment; delete from pse_category; delete from pse_supplier; delete from userinfo; delete from userlkup; delete from user where userid > 1;