1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
|
# -*- encoding:utf-8 -*-
import time
import calendar
from report import report_sxw
class mm_report(report_sxw.rml_parse):
def __init__(self, cr, uid, name, context=None):
super(mm_report, self).__init__(cr, uid, name, context=context)
self.localcontext.update({
'time':time,
'get_categories':self._get_categories,
})
def _get_inv_total(self, product_id,year_start,year_stop):
so_obj = self.pool.get('sale.order')
po_obj = self.pool.get('purchase.order')
sol_obj = self.pool.get('sale.order.line')
pol_obj = self.pool.get('purchase.order.line')
inv_obj = self.pool.get('account.invoice')
invl_obj = self.pool.get('account.invoice.line')
result = {
'c_inv_total':0.0,
's_inv_total':0.0,
}
so_ids = so_obj.search(self.cr, self.uid, [('date_order','>=',year_start),('date_order','<=',year_stop),('state','=','done')])
#print 'soid--->>>',so_ids
for so in so_obj.browse(self.cr, self.uid, so_ids):
so_inv_ids = inv_obj.search(self.cr,self.uid,[('origin','ilike',so.name),('state','in',['paid'])])
so_invl_ids = invl_obj.search(self.cr, self.uid, [('product_id','=',product_id),('invoice_id','in',so_inv_ids)])
#print 'so_invl--->',so_invl_ids
for so_invl in invl_obj.browse(self.cr, self.uid, so_invl_ids):
#print 'so_invl--->>',so_invl
result['c_inv_total'] += so_invl.price_subtotal or 0.0
#Find the PO invoice
po_ids = po_obj.search(self.cr, self.uid,[('origin','ilike',so.name)])
#print 'po_ids:',po_ids
if po_ids:
po = po_obj.browse(self.cr, self.uid, po_ids)
#print 'po:',po
po_inv_ids = inv_obj.search(self.cr,self.uid,[('origin','ilike',po[0].name),('state','in',['paid','open'])])
#print 'po_inv-->>>',po_inv_ids
po_invl_ids = invl_obj.search(self.cr, self.uid, [('product_id','=',product_id),('invoice_id','in',po_inv_ids)])
#print 'po_invl--->',po_invl_ids
for po_invl in invl_obj.browse(self.cr, self.uid, po_invl_ids):
#print 'invl--->>',po_invl
result['s_inv_total'] += po_invl.price_subtotal or 0.0
#print 'result--->>>',result
return result
def _get_month_line(self, year, product_id, month):
self.cr.execute('''SELECT count(sol.id),sum(sol.product_uom_qty) \
FROM sale_order_line AS sol \
LEFT JOIN sale_order AS so on(sol.order_id = so.id) \
WHERE '%s-%s-1'<=so.date_confirm \
AND so.date_confirm<='%s-%s-%s' \
AND product_id = %s'''%(year,month,year,month,calendar.monthrange(int(year),month)[1],product_id))
res = self.cr.fetchall()
#print 'res--->>>>',res
month={
'item':res[0][0] or 0.0,
'sqm':res[0][1] or 0.0,
}
#print 'month--->>>',month
return month
def _get_categories(self,data):
cat_ids=[]
res=[]
pro_ids=[]
product_obj = self.pool.get('product.product')
self.cr.execute('''SELECT sol.product_id FROM sale_order_line as sol LEFT JOIN sale_order AS so on (so.id = sol.order_id) WHERE (('%s'<=so.date_confirm and so.date_confirm<='%s') and so.state='done' and so.invoice_type_id='%s') GROUP BY(sol.product_id) '''%(data['year_start'],data['year_stop'],data['inv_type']))
product_ids = [pid[0] for pid in self.cr.fetchall()]
print 'prodcut_ids---->>>>',product_ids
products = product_obj.browse(self.cr, self.uid , list(product_ids))
for product in products:
pro_ids.append(product.id)
if product.categ_id.id not in cat_ids:
cat_ids.append(product.categ_id.id)
cats = self.pool.get('product.category').name_get(self.cr, self.uid, cat_ids, context=self.localcontext)
if not cats:
return res
year_total_month = {}
year_total_item = 0.0
year_total_sqm = 0.0
year_total_c_inv = 0.0
year_total_s_inv = 0.0
for cat in cats:
product_ids=product_obj.search(self.cr, self.uid, [('id', 'in', pro_ids), ('categ_id', '=', cat[0])], context=self.localcontext)
products = []
cat_total_item = 0
cat_total_sqm = 0
cat_total_month ={}
cat_total_c_inv = 0.0
cat_total_s_inv = 0.0
for product in product_obj.read(self.cr, self.uid, product_ids, ['name', 'code'], context=self.localcontext):
inv_total = self._get_inv_total(product['id'], data['year_start'], data['year_stop'])
val = {
'id':product['id'],
'name':product['name'],
'product_total_item':0.0,
'product_total_sqm':0.0,
'product_total_month_sqm':0.0,
'c_inv_total':inv_total['c_inv_total'],
's_inv_total':inv_total['s_inv_total'],
}
for month in range(1,13):
val[month]=self._get_month_line(data['year'], product['id'], month)
val['product_total_item'] += int(val[month].get('item',0))
val['product_total_sqm'] += int(val[month].get('sqm',0.0))
if not cat_total_month.get(month,False):
cat_total_month.update({month:{'item':0,'sqm':0}})
cat_total_month[month]['item'] += int(val[month].get('item',0))
cat_total_month[month]['sqm'] += int(val[month].get('sqm',0.0))
if not year_total_month.get(month,False):
year_total_month.update({month:{'item':0,'sqm':0}})
year_total_month[month]['item'] += int(val[month].get('item',0))
year_total_month[month]['sqm'] += int(val[month].get('sqm',0.0))
print 'year_total_month[month]-->',year_total_month
cat_total_item += int(val[month].get('item',0))
cat_total_sqm += int(val[month].get('sqm',0.0))
cat_total_c_inv += inv_total['c_inv_total']
cat_total_s_inv += inv_total['s_inv_total']
print 'year_month:',year_total_month
products.append(val)
year_total_item += cat_total_item
year_total_sqm += cat_total_sqm
year_total_c_inv += cat_total_c_inv
year_total_s_inv += cat_total_s_inv
res.append({
'name':cat[1],
'products':products,
'cat_total_item':cat_total_item,
'cat_total_sqm':cat_total_sqm,
'cat_total_month':cat_total_month,
'cat_total_c_inv':cat_total_c_inv,
'cat_total_s_inv':cat_total_s_inv,
})
#print 'cat---->>>>',res
self.localcontext.update({
'year_total_month':year_total_month,
'year_total_c_inv':year_total_c_inv,
'year_total_s_inv':year_total_s_inv,
'year_total_item':year_total_item,
'year_total_sqm':year_total_sqm,
})
return res
report_sxw.report_sxw('report.mm','sale.order','my-addons/shineit_mm/report/mm.rml',parser=mm_report,header=False)
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
|