~openerp-chinese-team/openerp-china/openerp-china

« back to all changes in this revision

Viewing changes to shineit_mm/shineit_mm/report/@

  • Committer: JoshuaJan
  • Date: 2012-02-21 03:21:40 UTC
  • Revision ID: joshua@openerp.cn-20120221032140-4wwk95ftpf9x24ec
change the shipped date for report MM

Show diffs side-by-side

added added

removed removed

Lines of Context:
 
1
import time
 
2
import calendar
 
3
 
 
4
from report import report_sxw
 
5
 
 
6
class mm_report(report_sxw.rml_parse):
 
7
    def __init__(self, cr, uid, name, context=None):
 
8
        super(mm_report, self).__init__(cr, uid, name, context=context)
 
9
        self.localcontext.update({
 
10
            'time':time,
 
11
            'get_brands':self._get_brands,
 
12
            })
 
13
 
 
14
    def _get_inv_total(self, product_id, so_ids, brand):
 
15
        so_obj = self.pool.get('sale.order')
 
16
        po_obj = self.pool.get('purchase.order')
 
17
        sol_obj = self.pool.get('sale.order.line')
 
18
        pol_obj = self.pool.get('purchase.order.line')
 
19
        inv_obj = self.pool.get('account.invoice')
 
20
        invl_obj = self.pool.get('account.invoice.line')
 
21
 
 
22
        result = {
 
23
                'c_inv_total':0.0,
 
24
                's_inv_total':0.0,
 
25
                }
 
26
        i_so_ids = so_obj.search(self.cr, self.uid, [('id','in',so_ids),('brand','=',brand)])
 
27
        #print 'soid--->>>',so_ids
 
28
        for so in so_obj.browse(self.cr, self.uid, i_so_ids):
 
29
            so_inv_ids = inv_obj.search(self.cr,self.uid,[('origin','ilike',so.name),('state','in',['paid'])])
 
30
            so_invl_ids = invl_obj.search(self.cr, self.uid, [('product_id','=',product_id),('invoice_id','in',so_inv_ids)])
 
31
            print 'so_invl:%s,product_id:%s,so:%s'%(so_invl_ids,product_id,so.name)
 
32
            for so_invl in invl_obj.browse(self.cr, self.uid, so_invl_ids):
 
33
                #print 'so_invl--->>',so_invl
 
34
                result['c_inv_total'] += so_invl.price_subtotal or 0.0
 
35
            #Find the PO invoice
 
36
            po_ids = po_obj.search(self.cr, self.uid,[('origin','ilike',so.name)])
 
37
            #print 'po_ids:',po_ids
 
38
            if po_ids:
 
39
                po = po_obj.browse(self.cr, self.uid, po_ids)
 
40
                #print 'po:',po
 
41
                po_inv_ids = inv_obj.search(self.cr,self.uid,[('origin','ilike',po[0].name),('state','in',['paid','open'])])
 
42
                #print 'po_inv-->>>',po_inv_ids
 
43
                po_invl_ids = invl_obj.search(self.cr, self.uid, [('product_id','=',product_id),('invoice_id','in',po_inv_ids)])
 
44
                #print 'po_invl--->',po_invl_ids
 
45
                for po_invl in invl_obj.browse(self.cr, self.uid, po_invl_ids):
 
46
                    #print 'invl--->>',po_invl
 
47
                    result['s_inv_total'] += po_invl.price_subtotal or 0.0
 
48
        print 'result--->>>',result
 
49
        return result
 
50
            
 
51
     
 
52
    def _get_month_line(self, year, product_id, month, so_ids, brand):
 
53
        item = 0
 
54
        sqm = 0
 
55
        usd = 0
 
56
        s_sqm = 0
 
57
        s_usd = 0
 
58
        so_obj = self.pool.get('sale.order')
 
59
        sol_obj = self.pool.get('sale.order.line')
 
60
        sm_obj = self.pool.get('stock.move')
 
61
        m_so_ids = so_obj.search(self.cr, self.uid,[('id','in',so_ids),('date_order','<=',str(year)+'-'+str(month)+'-'+str(calendar.monthrange(int(year),month)[1])),('date_order','>=',str(year)+'-'+str(month)+'-1'),('brand','=',brand)])
 
62
        #print 'm_so_ids:',m_so_ids
 
63
        #print 'product_ids',product_id
 
64
        for m_so in so_obj.browse(self.cr, self.uid, m_so_ids):
 
65
            for m_sol in m_so.order_line:
 
66
                if m_sol.product_id.id == product_id:
 
67
                    item += 1
 
68
                    sqm  += m_sol.product_uom_qty
 
69
                    usd  += m_sol.price_subtotal
 
70
        
 
71
        result = {
 
72
                'item':item,
 
73
                'sqm':sqm,
 
74
                'usd':usd,
 
75
                }
 
76
        sm_ids = sm_obj.search(self.cr, self.uid, [('date','>=',str(year)+'-'+str(month)+'-1'),('date','<=',str(year)+'-'+str(month)+'-'+str(calendar.monthrange(int(year),month)[1])),('state','=','done')])
 
77
        #print 'sm_ids:',sm_ids
 
78
        for sm in sm_obj.browse(self.cr, self.uid, sm_ids):
 
79
            print 'brand1:',brand
 
80
            print 'brand:',sm.sale_line_id and sm.sale_line_id.order_id.brand
 
81
            if sm.product_id.id == product_id and sm.sale_line_id and (sm.sale_line_id.order_id.id in so_ids) and ((brand and sm.sale_line_id.order_id.brand == brand) or (brand == None and not sm.sale_line_id.order_id.brand)):
 
82
               if brand == None:
 
83
                  print 'NN'
 
84
               s_sqm += sm.product_qty
 
85
               s_usd += sm.sale_line_id.price_unit * (1 - (sm.sale_line_id.discount or 0.0) / 100.0) * sm.product_qty
 
86
        ship_res = self.cr.fetchall()
 
87
        #print 'ship_res',ship_res
 
88
        result['s_sqm'] = s_sqm
 
89
        result['s_usd'] = s_usd
 
90
        #print 'result:',result
 
91
        return result
 
92
 
 
93
    def _get_brands(self,data):
 
94
        cat_ids=[]
 
95
        res=[]
 
96
        pro_ids=[]
 
97
        so_obj = self.pool.get('sale.order')
 
98
        product_obj = self.pool.get('product.product')
 
99
        so_ids = so_obj.search(self.cr, self.uid, [('date_order','>=',data['year_start']),('date_order','<=',data['year_stop']),('state','in',['progress','done','manual','shipping_except','invoice_except','waiting_date']),('purpose','=',data['purpose'])]) 
 
100
        print 'so_ids:',so_ids
 
101
        if len(so_ids) <= 0:
 
102
           return res
 
103
        self.cr.execute('''SELECT sol.product_id \
 
104
                           FROM sale_order_line AS sol \
 
105
                           LEFT JOIN sale_order AS so ON (so.id = sol.order_id) \
 
106
                           WHERE so.id in (%s) \
 
107
                           GROUP BY(sol.product_id) '''%(','.join(map(str, so_ids))))
 
108
        product_ids = [pid[0] for pid in self.cr.fetchall()]
 
109
        #print 'prodcut_ids---->>>>',product_ids
 
110
        products = product_obj.browse(self.cr, self.uid , list(product_ids)) 
 
111
        for product in products:
 
112
            pro_ids.append(product.id)
 
113
            if product.categ_id.id not in cat_ids:
 
114
                cat_ids.append(product.categ_id.id)
 
115
 
 
116
       # cats = self.pool.get('product.category').name_get(self.cr, self.uid, cat_ids, context=self.localcontext)
 
117
        self.cr.execute('''SELECT DISTINCT brand  \
 
118
                           FROM sale_order_line AS sol \
 
119
                           LEFT JOIN sale_order AS so ON (so.id = sol.order_id) \
 
120
                           WHERE so.id in (%s) \
 
121
                           ORDER BY brand'''%(','.join(map(str, so_ids))))
 
122
        brands = [brand[0] for brand in self.cr.fetchall()]
 
123
       
 
124
        #print 'brands:%s',brands
 
125
        if not brands:
 
126
            return res
 
127
        year_total_month = {}
 
128
        year_total_item = 0.0
 
129
        year_total_sqm = 0.0
 
130
        year_total_usd = 0.0
 
131
        year_total_s_sqm = 0.0
 
132
        year_total_s_usd = 0.0
 
133
        year_total_c_inv = 0.0
 
134
        year_total_s_inv = 0.0
 
135
        for brand in brands:
 
136
            print 'brand:',brand
 
137
            self.cr.execute('''SELECT DISTINCT sol.product_id \
 
138
                               FROM sale_order_line AS sol \
 
139
                               LEFT JOIN sale_order AS so on(so.id=sol.order_id) \
 
140
                               WHERE so.id in (%s) \
 
141
                               AND %s '''%(','.join(map(str,so_ids)), 'so.brand'+(brand and "='"+brand+"'" or ' is null')))
 
142
            #product_ids=product_obj.search(self.cr, self.uid, [('id', 'in', pro_ids), ('categ_id', '=', cat[0])], context=self.localcontext)
 
143
            product_ids = [product_id[0] for product_id in self.cr.fetchall()]
 
144
            products = []
 
145
            cat_total_item = 0
 
146
            cat_total_sqm = 0
 
147
            cat_total_usd = 0
 
148
            cat_total_s_sqm = 0
 
149
            cat_total_s_usd = 0
 
150
            cat_total_month ={}
 
151
            cat_total_c_inv = 0.0
 
152
            cat_total_s_inv = 0.0
 
153
            for product in product_obj.read(self.cr, self.uid, product_ids, ['name', 'code'], context=self.localcontext):
 
154
                print 'product:',product
 
155
                inv_total = self._get_inv_total(product['id'], so_ids, brand)
 
156
                val = {
 
157
                     'id':product['id'],
 
158
                     'name':product['name'],
 
159
                     'product_total_item':0.0,
 
160
                     'product_total_sqm':0.0,
 
161
                     'product_total_usd':0.0,
 
162
                     'product_total_s_sqm':0.0,
 
163
                     'product_total_s_usd':0.0,
 
164
                     'c_inv_total':inv_total['c_inv_total'],
 
165
                     's_inv_total':inv_total['s_inv_total'],
 
166
                 }
 
167
                for month in range(1,13):
 
168
                    val[month]=self._get_month_line(data['year'], product['id'], month, so_ids, brand)
 
169
                    val['product_total_item'] += int(val[month].get('item',0))
 
170
                    val['product_total_sqm'] += int(val[month].get('sqm',0.0))
 
171
                    val['product_total_usd'] += int(val[month].get('usd',0.0))
 
172
                    val['product_total_s_sqm'] += int(val[month].get('s_sqm',0.0))
 
173
                    val['product_total_s_usd'] += int(val[month].get('s_usd',0.0))
 
174
                    if not cat_total_month.get(month,False):
 
175
                        cat_total_month.update({month:{'item':0,'sqm':0,'usd':0,'s_sqm':0,'s_usd':0}})
 
176
                    cat_total_month[month]['item'] += int(val[month].get('item',0)) 
 
177
                    cat_total_month[month]['sqm'] += int(val[month].get('sqm',0.0))
 
178
                    cat_total_month[month]['usd'] += int(val[month].get('usd',0.0))
 
179
                    cat_total_month[month]['s_sqm'] += int(val[month].get('s_sqm',0.0))
 
180
                    cat_total_month[month]['s_usd'] += int(val[month].get('s_usd',0.0))
 
181
                    if not year_total_month.get(month,False):
 
182
                        year_total_month.update({month:{'item':0,'sqm':0,'usd':0,'s_sqm':0,'s_usd':0}})
 
183
                    year_total_month[month]['item'] += int(val[month].get('item',0))
 
184
                    year_total_month[month]['sqm'] += int(val[month].get('sqm',0.0))
 
185
                    year_total_month[month]['usd'] += int(val[month].get('usd',0.0))
 
186
                    year_total_month[month]['s_sqm'] += int(val[month].get('s_sqm',0.0))
 
187
                    year_total_month[month]['s_usd'] += int(val[month].get('s_usd',0.0)) 
 
188
                    #print 'cat-total:',cat_total_month[month]
 
189
                    #print 'year_total_month[month]-->',year_total_month 
 
190
                    cat_total_item += int(val[month].get('item',0))
 
191
                    cat_total_sqm += int(val[month].get('sqm',0.0))
 
192
                    cat_total_usd += int(val[month].get('usd',0.0))
 
193
                    cat_total_s_sqm += int(val[month].get('s_sqm',0.0))
 
194
                    cat_total_s_usd += int(val[month].get('s_usd',0.0))
 
195
                cat_total_c_inv += inv_total['c_inv_total']
 
196
                cat_total_s_inv += inv_total['s_inv_total']
 
197
                #print 'year_month:',year_total_month
 
198
                products.append(val)
 
199
            year_total_item += cat_total_item
 
200
            year_total_sqm += cat_total_sqm
 
201
            year_total_c_inv += cat_total_c_inv
 
202
            year_total_s_inv += cat_total_s_inv
 
203
            year_total_usd += cat_total_usd
 
204
            year_total_s_sqm += cat_total_s_sqm
 
205
            year_total_s_usd += cat_total_s_usd
 
206
            res.append({
 
207
                        'name':brand,
 
208
                        'products':products,
 
209
                        'cat_total_item':cat_total_item,
 
210
                        'cat_total_sqm':cat_total_sqm,
 
211
                        'cat_total_usd':cat_total_usd,
 
212
                        'cat_total_s_sqm':cat_total_s_sqm,
 
213
                        'cat_total_s_usd':cat_total_s_usd,
 
214
                        'cat_total_month':cat_total_month,
 
215
                        'cat_total_c_inv':cat_total_c_inv,
 
216
                        'cat_total_s_inv':cat_total_s_inv,
 
217
                    })
 
218
        #print 'cat-->',res
 
219
        self.localcontext.update({
 
220
            'year_total_month':year_total_month,
 
221
            'year_total_c_inv':year_total_c_inv,
 
222
            'year_total_s_inv':year_total_s_inv,
 
223
            'year_total_item':year_total_item,
 
224
            'year_total_sqm':year_total_sqm,
 
225
            'year_total_usd':year_total_usd,
 
226
            'year_total_s_sqm':year_total_s_sqm,
 
227
            'year_total_s_usd':year_total_s_usd,
 
228
        })
 
229
        #print 'res-->',res
 
230
        return res
 
231
 
 
232
 
 
233
report_sxw.report_sxw('report.sample_mm','sale.order','my-addons/shineit_mm/report/simple_mm.rml',parser=mm_report,header=False)
 
234
report_sxw.report_sxw('report.production_mm','sale.order','my-addons/shineit_mm/report/production_mm.rml',parser=mm_report,header=False)
 
235
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: