anyone who has opinion on this?
thanks
james
On Sep 29, 10:01 am, james_027 <[EMAIL PROTECTED]> wrote:
> hi all
>
> the 2 methods below have no errors but are different in a way that the
> first one doesn't give me the resultset in the order that I want while
> the second method give me the resultset in the right order that I
> want.
>
> first method
>
> def sales(self, year=datetime.today().year, order_by='Customer'):
> cursor = connection.cursor()
> cursor.execute("""
> select c.id, c.name as Customer,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 1 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as January,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 2 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as February,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 3 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as March,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 4 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as April,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 5 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as May,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 6 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as June,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 7 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as July,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 8 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as August,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 9 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as September,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 10 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as October,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 11 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as November,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 12 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as December
> from ap_customer c
> order by %s desc
> """, [year]*12 + [order_by])
> return cursor.fetchall()
>
> second method
>
> def sales(self, year=datetime.today().year, order_by='Customer'):
> sql = """
> select c.id, c.name as Customer,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 1 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as January,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 2 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as February,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 3 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as March,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 4 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as April,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 5 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as May,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 6 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as June,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 7 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as July,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 8 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as August,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 9 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as September,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 10 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as October,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 11 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as November,
> (select sum(i.amount) from ap_invoice i where
> Month(i.date) = 12 and Year(i.date) = %s and i.customer_id = c.id and
> i.status != 'CAN') as December
> from ap_customer c
> order by %s desc
> """ % (year, year, year, year, year, year, year, year,
> year ,year, year, year, order_by)
> cursor = connection.cursor()
> cursor.execute(sql)
> return cursor.fetchall()
>
> both method return the same connection.queries, and the weirdest part
> is that while the first method doesn't work in my personal pc, but
> it's work on my client workstation. both has the same python, mysql,
> and pymysql version ...
>
> thanks
> james
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
