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
Helpful SQL Commands for WECS
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 ‘’;
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;