[IMP]:hr_timesheet_sheet:Improved SQL report.(Timesheet).

[odoo/odoo.git] / addons / hr_timesheet_sheet / report / timesheet_report.py

diff --git a/addons/hr_timesheet_sheet/report/timesheet_report.py b/addons/hr_timesheet_sheet/report/timesheet_report.py
index cd657e7..7b79470 100644 (file)
--- a/addons/hr_timesheet_sheet/report/timesheet_report.py
+++ b/addons/hr_timesheet_sheet/report/timesheet_report.py
@@ -24,38 +24,68 @@ from osv import fields,osv
 
 class timesheet_report(osv.osv):
     _name = "timesheet.report"
-    _description = "Timesheet by month "
+    _description = "Timesheet"
     _auto = False
     _columns = {
+        'year': fields.char('Year',size=64,required=False, readonly=True),
         'month':fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'),
             ('05','May'), ('06','June'), ('07','July'), ('08','August'), ('09','September'),
             ('10','October'), ('11','November'), ('12','December')], 'Month',readonly=True),
-        'no_of_timesheet': fields.integer('Total Timesheet',readonly=True),
-        'total_att': fields.float('Total Attendance'),
-        'total_ts': fields.float('Total Timesheet'),
-        'year': fields.char('Remaining leaves', size=4),
-        'name': fields.char('Name', size=64),
-        'user_id': fields.many2one('res.users','User'),
-        'leave_type': fields.char('Leave Type',size=64),
-        'month':fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'), ('05','May'), ('06','June'),
-                          ('07','July'), ('08','August'), ('09','September'), ('10','October'), ('11','November'), ('12','December')],'Month',readonly=True),
+        'day': fields.char('Day', size=128, readonly=True),
+        'name': fields.char('Description', size=64,readonly=True),
+        'user_id': fields.many2one('res.users', 'User',readonly=True),
+        'nbr': fields.integer('#Nbr',readonly=True),
+        'company_id': fields.many2one('res.company', 'Company',readonly=True),
+        'department_id':fields.many2one('hr.department','Department',readonly=True),
+        'date_from': fields.date('Date from',readonly=True,),
+        'date_to': fields.date('Date to',readonly=True),
+        'date_current': fields.date('Current date', required=True),
+        'state' : fields.selection([
+            ('new', 'New'),
+            ('draft','Draft'),
+            ('confirm','Confirmed'),
+            ('done','Done')], 'State', readonly=True),
+        'quantity': fields.float('#Quantity',readonly=True),
+        'cost': fields.float('#Cost',readonly=True),
         }
 
     def init(self, cr):
         tools.drop_view_if_exists(cr, 'timesheet_report')
         cr.execute("""
             create or replace view timesheet_report as (
-                    SELECT sheet.name as name, 
-                           min(sheet.id) as id,
-                           to_char(sheet.date_current, 'YYYY') as year,
-                           to_char(sheet.date_current, 'MM') as month,
-                           sum(day.total_attendance) as total_att,
-                           sum(day.total_timesheet) as total_ts,
-                           sheet.user_id as user_id
-                    FROM hr_timesheet_sheet_sheet AS sheet
-                    LEFT JOIN hr_timesheet_sheet_sheet_day AS day
-                    ON (sheet.id = day.sheet_id)
-                    GROUP BY sheet.name, year, month, user_id
-                    ) """)
-
-timesheet_report()
+                    select
+                        min(aal.id) as id,
+                        htss.date_current,
+                        htss.name,
+                        htss.date_from,
+                        htss.date_to,
+                        to_char(htss.date_current,'YYYY') as year,
+                        to_char(htss.date_current,'MM') as month,
+                        to_char(htss.date_current, 'YYYY-MM-DD') as day,
+                        count(*) as nbr,
+                        sum(aal.unit_amount) as quantity,
+                        sum(aal.amount) as cost,
+                        aal.account_id,
+                        htss.user_id,
+                        htss.company_id,
+                        htss.department_id,
+                        htss.state
+                    from account_analytic_line as aal
+                    left join hr_analytic_timesheet as hat ON (hat.line_id=aal.id)
+                    left join hr_timesheet_sheet_sheet as htss ON (hat.line_id=htss.id)
+                    group by
+                        to_char(htss.date_current,'YYYY'),
+                        to_char(htss.date_current,'MM'),
+                        to_char(htss.date_current, 'YYYY-MM-DD'),
+                        aal.account_id,
+                        htss.date_from,
+                        htss.date_to,
+                        htss.date_current,
+                        htss.name,
+                        htss.company_id,
+                        htss.state,
+                        htss.department_id,
+                        htss.user_id
+            )
+        """)
+timesheet_report()
\ No newline at end of file