Reports for separate pricing per customer

[frog]

Hello!

after a long training in php/mysql and oscommerce i have an installation with separate pricing for customers contribution where a visitor can be setup into a certain group with a special code given from the merchant (me :lol: )

 

I give to my shopper a badge with a code (name of group) if the visitor enters that group (and the group exists actually...) he belongs to that group and so he inherit price.

 

Now I would like to setup a report where i can know how much money the group ordered, if the group orders more than 1000$ i would like to give a gift certificate or a special discount. So i began....

 

the query is more or less: (on webmin)

select c.customers_group_name, c.customers_lastname, c.customers_firstname, sum(op.products_quantity * op.final_price) as ordersum from customers c,orders_products op, orders o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC

 

this query is very similar to stats_customer.php and gives me groups and sum for customers

 

Now I want to sum group ordersum, but how? If I say in the query "group BY customers_group_name" it gives me errors and so it does not work

 

any hints or help?

thanks in advance

froggy

[frog]

nobody interested?

 

thanks

frog

[ianmel]

Hi Froggy,

 

I can't help with the coding at all i'm afraid but i'm very interested in your contribution! I am setting up an interchange store and i'm considering a switch to osc (as i was nudged this way by my hosting provider <g>) but i need the retail/wholesal pricing distinction. Could you give me any idea of how hard it is to setup and perhaps an URL where i can see it in action? I know the last is a stretch but i'd realy appreciate it if possible. Thanks!

 

.../Ian from Trinidad W.I.

 

Hello!

after a long training in php/mysql and oscommerce i have an installation with separate pricing for customers contribution where a visitor can be setup into a certain group with a special code given from the merchant (me :lol: )

 

I give to my shopper a badge with a code (name of group) if the visitor enters that group (and the group exists actually...) he belongs to that group and so he inherit price.

 

Now I would like to setup a report where i can know how much money the group ordered, if the group orders more than 1000$ i would like to give a gift certificate or a special discount. So i began....

 

the query is more or less: (on webmin)

select c.customers_group_name, c.customers_lastname, c.customers_firstname, sum(op.products_quantity * op.final_price) as ordersum from customers c,orders_products op, orders o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC

 

this query is very similar to stats_customer.php and gives me groups and sum for customers

 

Now I want to sum group ordersum, but how? If I say in the query "group BY customers_group_name" it gives me errors and so it does not work

 

any hints or help?

thanks in advance

froggy

[frog]

hello!

 

first of all i haven't any url at the moment cos it's running on my local machine, even if the store is quite ready.

 

Putting together the contributions was quite messy to a newbie like me, but i tried and tried and so i'm ready to go...... :P

 

the code I made is a little bit messy but it works.....it's not osc - standard , very dirty..... :lol:

 

I'll post a url when ready........

Bye