Exsisting mysql Appgini scripts help

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
jeffyork40
Posts: 3
Joined: 2013-07-17 17:19

Exsisting mysql Appgini scripts help

Post by jeffyork40 » 2013-07-18 04:21

I had some work performed by a contracted provider. I have a set of five scripts to update inventory on my website and the contractor left me high and dry and I need to get up to speed fast on how to run the scripts in order to update the inventory on my website. Some of the problem appears to be user settings in the AppGini Application that was written. I will call the app Inventory Master. It resides in a folder called INV on my web. Since I believe there is an Admin level that I dont seem to have access to, a admin user. I am also wondering if there is a way for me to find out what the highest level user log in is. Here is a sample of one of the five sql scripts.


use hardwat3_invdb;

UPDATE inv_master
INNER JOIN newskuinfo ON inv_master.sku = newskuinfo.sku
SET inv_master.sku = newskuinfo.sku,
inv_master.upc = newskuinfo.upc,
inv_master.status =
CASE
when newskuinfo.quantity = '0' and newskuinfo.status NOT LIKE 'CANCELLED' then 'INACTIVE'
else newskuinfo.status
END,
inv_master.ace_retail =
CASE
when newskuinfo.ace_retail LIKE '%A%' then replace(newskuinfo.ace_retail, 'A', '.')
when newskuinfo.ace_retail LIKE '%B%' then replace(newskuinfo.ace_retail, 'B', '.')
when newskuinfo.ace_retail LIKE '%C%' then replace(newskuinfo.ace_retail, 'C', '.')
ELSE newskuinfo.ace_retail
END,
inv_master.ace_qty = newskuinfo.quantity,
inv_master.ace_mult = newskuinfo.order_mult,
inv_master.order_mult = newskuinfo.order_mult,
inv_master.mfg = newskuinfo.mfg,
inv_master.vendor_name = newskuinfo.vendor_name,
inv_master.om_weight = newskuinfo.om_weight,
inv_master.brandname = newskuinfo.brandname,
inv_master.short_desc = newskuinfo.short_desc,
inv_master.each_cost =
CASE
when newskuinfo.each_cost LIKE '%A%' then replace(newskuinfo.each_cost, 'A', '.')
when newskuinfo.each_cost LIKE '%B%' then replace(newskuinfo.each_cost, 'B', '.')
when newskuinfo.each_cost LIKE '%C%' then replace(newskuinfo.each_cost, 'C', '.')
ELSE newskuinfo.each_cost
END,
/*inv_master.prod_class = newskuinfo.prod_class,*/
inv_master.prod_group = newskuinfo.prod_group,
inv_master.exp_desc = newskuinfo.exp_desc,
inv_master.ship_ups = newskuinfo.ship_ups,
inv_master.long_desc = newskuinfo.long_desc,
inv_master.tvo_retail =
CASE
when newskuinfo.tvo_retail LIKE '%A%' then replace(newskuinfo.tvo_retail, 'A', '.')
when newskuinfo.tvo_retail LIKE '%B%' then replace(newskuinfo.tvo_retail, 'B', '.')
when newskuinfo.tvo_retail LIKE '%C%' then replace(newskuinfo.tvo_retail, 'C', '.')
ELSE newskuinfo.tvo_retail
END,
inv_master.tvo_mult = newskuinfo.tvo_mult;

/*where inv_master.lock_data is null;*/


/* STEP 2 - Add new records from acenet to inventory where based on UPC code */
use hardwat3_invdb;
DROP TEMPORARY TABLE IF EXISTS new_inv;
CREATE TEMPORARY TABLE new_inv (
sku char(20),
skux char(20),
upc char(100),
status char(20),
ace_retail Char(10),
quantity int(20),
order_mult char(5),
mfg char(20),
vendor_name char(100),
om_weight char(4),
brandname char(100),
short_desc char(10),
each_cost char(100),
prod_class int(10),
prod_group int(10),
exp_desc char(200),
ship_ups char(10),
long_desc TEXT,
tvo_retail decimal(8,2),
tvo_mult int(4)

);

INSERT INTO new_inv (
skux,
sku,
upc,
status,
ace_retail,
quantity,
order_mult,
mfg,
vendor_name,
om_weight,
brandname,
short_desc,
each_cost,
prod_class,
prod_group,
exp_desc,
ship_ups,
long_desc,
tvo_retail,
tvo_mult
)

Select inv_master.sku,
newskuinfo.sku,
newskuinfo.upc,
CASE
when newskuinfo.quantity = '0' and newskuinfo.status NOT LIKE 'CANCELLED' then 'INACTIVE'
when newskuinfo.quantity is null and newskuinfo.status NOT LIKE 'CANCELLED' then 'INACTIVE'
else newskuinfo.status
END,
CASE
when newskuinfo.ace_retail LIKE '%A%' then replace(newskuinfo.ace_retail, 'A', '.')
when newskuinfo.ace_retail LIKE '%B%' then replace(newskuinfo.ace_retail, 'B', '.')
when newskuinfo.ace_retail LIKE '%C%' then replace(newskuinfo.ace_retail, 'C', '.')
ELSE newskuinfo.ace_retail
END,
/* qty = newskuinfo.qty / 10 (1-25) / newskuinfo.order_mult */
newskuinfo.quantity,
newskuinfo.order_mult,
newskuinfo.mfg,
newskuinfo.vendor_name,
newskuinfo.om_weight,
newskuinfo.brandname,
newskuinfo.short_desc,
CASE
when newskuinfo.each_cost LIKE '%A%' then replace(newskuinfo.each_cost, 'A', '.')
when newskuinfo.each_cost LIKE '%B%' then replace(newskuinfo.each_cost, 'B', '.')
when newskuinfo.each_cost LIKE '%C%' then replace(newskuinfo.each_cost, 'C', '.')
ELSE newskuinfo.each_cost
END,
newskuinfo.prod_class,
newskuinfo.prod_group,
newskuinfo.exp_desc,
newskuinfo.ship_ups,
newskuinfo.long_desc,
CASE
when newskuinfo.tvo_retail LIKE '%A%' then replace(newskuinfo.tvo_retail, 'A', '.')
when newskuinfo.tvo_retail LIKE '%B%' then replace(newskuinfo.tvo_retail, 'B', '.')
when newskuinfo.tvo_retail LIKE '%C%' then replace(newskuinfo.tvo_retail, 'C', '.')
ELSE newskuinfo.tvo_retail
END,
newskuinfo.tvo_mult

FROM newskuinfo Left OUTER JOIN inv_master ON newskuinfo.sku = inv_master.sku
where inv_master.sku is null;


select * from new_inv where skux is null;


INSERT INTO inv_master ( sku,
upc,
status,
ace_retail,
quantity,
mfg,
vendor_name,
om_weight,
brandname,
price,
AZ_ID,
EB_ID,
short_desc,
each_cost,
order_mult,
prod_class,
prod_group,
exp_desc,
ship_ups,
long_desc,
tvo_retail,
tvo_mult,
OC_ID
)
SELECT new_inv.sku,
new_inv.upc,
new_inv.status,
new_inv.ace_retail,
new_inv.quantity,
new_inv.mfg,
new_inv.vendor_name,
new_inv.om_weight,
new_inv.brandname,
1.00,
'AZa',
'EBa',
new_inv.short_desc,
new_inv.each_cost,
new_inv.order_mult,
new_inv.prod_class,
new_inv.prod_group,
new_inv.exp_desc,
new_inv.ship_ups,
new_inv.long_desc,
new_inv.tvo_retail,
new_inv.tvo_mult,
'N'
FROM new_inv;

User avatar
dilitimor
Veteran Member
Posts: 36
Joined: 2013-01-10 02:45
Location: Jakarta, Indonesia
Contact:

Re: Exsisting mysql Appgini scripts help

Post by dilitimor » 2013-07-23 05:30

Dear Jeff,

Just for a quick way, you can use phpmyadmin to execute those scripts.
It's provided by hosting site.

But, You could update directly without script if your application was build by appgini.

Post Reply