display a report
- Partner Program
- 2022-05-18 12:44
0 - 49
select c.company_name ,
pr.descr, pr.project_code,
tt.ppd_id,
sum (coalesce (floor (tt.track_hour), 0)) as hours_,
sum (coalesce (tt.track_hour, 0)) as hoursMin,
sum (coalesce (floor (tt.overtime), 0)) as overtime_,
sum (coalesce (tt.overtime, 0)) as overTimeMin
from company c, ttc_time_track tt, project pr
where c.company_id = tt.company_id and
pr.project_id = tt.project_id
<cfif listLen (projectList) gt 0>
and tt.project_id in (<cfqueryparam cfsqltype="integer" list="yes" value="#projectList#">)
</cfif>
<cfif listLen (passedUserList) gt 0>
and tt.app_user_id in (<cfqueryparam cfsqltype="integer" list="yes" value="#passedUserList#">)
</cfif>
<cfif isDate (fromDate)>
and tt.track_date >= <cfqueryparam cfsqltype="cf_sql_date" value="#fromDate#">
</cfif>
<cfif isDate (toDate)>
and tt.track_date <= <cfqueryparam cfsqltype="cf_sql_date" value="#toDate#">
</cfif>
<cfif listLen (passedClientList) gt 0>
and tt.company_id in (#passedClientList#)
</cfif>
group by c.company_name, pr.descr, pr.project_code, tt.ppd_id
order by c.company_name, pr.descr
</cfquery>
<body class="ttc">
<cfform name="tt_repConsHoursByCompany" action="tt_disReport.cfm" method="post">
<br/>
<cfinclude template="tt_repHeader.cfm">
<br />
<cfif ttQ1.recordcount EQ 0>
<div style="position: absolute; left:20;" class="msgInfo">
<cfoutput>#application.stLangFormField[lang][formName][37].descr#</cfoutput></div>
<cfelse>
<cfsavecontent variable = variables.cfFormName>
<table border="0" cellspacing="0" cellpadding="0" bgcolor="#A0A0A0">
<tr>
<td align="left" valign="top"><cfoutput>
<table border="0" cellspacing="0" cellpadding="0" style="width:600">
<tr class="hdrBlueDarkLf13" style="height:24">
<td colspan="5"> #application.stLangFormField[lang][formName][79].descr#<!-Hours By Client, Project & Phase-> #dateInfo# </td>
<td><a onClick="openWindowScroll (750,600, 'tt_print.cfm?source=ttRepType1', 'newWin')"><img src='../images/icon_print.gif' border='0' width='21' height='21'></a></td>
</tr>
</table>
<table border="0" cellspacing="1" cellpadding="0" style="width:600">
<tr class="hdrBlueMedCt11" style="height:24">
<td style="width:100">#application.stLangFormField[lang][formName][11].descr#<!-Client-></td>
<td style="width:200">#application.stLangFormField[lang][formName][43].descr#<!-Project-></td>
<td style="width:200">#application.stLangFormField[lang][formName][14].descr#<!-Phase-></td>
<td style="width:100">#application.stLangFormField[lang][formName][16].descr#<!-Hours-></td>
<td style="width:100">#application.stLangFormField[lang][formName][17].descr#<!-Overtime-></td>
<td style="width:100">#application.stLangFormField[lang][formName][60].descr#<!-Total-></td>
</tr> </cfoutput>
<cfoutput query="ttQ1" group="company_name">
<tr>
<td colspan="6" class="hdrDarkGreyLfMd" style="height:22"> #company_name#</td>
<cfscript>
total_H = 0;
total_Hours = 0;
total_HMin = 0;
total_O = 0;
total_OTHours = 0;
total_OTHMin = 0;
</cfscript>
</tr>
<cfoutput>
<cfscript>
if (currentrow mod 2 eq 0)
tdClass = "hdrMedGreyRt";
else
tdClass="hdrMedWhiteRt";
minHours = (ttQ1.hoursMin - ttQ1.hours_) * 100;
hours = ttQ1.hours_ + minToHours (minHours);
OTminHours = (ttQ1.overTimeMin - ttQ1.overtime_) * 100;
overtime = ttQ1.overtime_ + minToHours (OTminHours);
if (trim (ttQ1.descr) is last_ or trim (ttQ1.descr) is last1_)
{
last_ = "";
last1_ = ttQ1.descr;
}
else
last_ = ttQ1.descr;
actTotal = hours + overtime;
</cfscript>
<tr class="#tdClass#" style="height:18">
<td colspan="2">#last_# <cfif len (last_) gt 0 and len (project_code) gt 0>(#project_code#) </cfif></td>
<td><cfif val (ppd_id) gt 0>#application.stPPDPhases[ppd_id].phase#</cfif> </td>
<td style="width:100" align="right">#replace (numberFormat (hours, '_____.__'), ".",":")# </td>
<td style="width:100" align="right">#replace (numberFormat (overtime, '_____.__'), ".",":")# </td>
<td style="width:100" class="lblHdrLgBlYelRt">#replace (formatHoursMin (actTotal), ".",":")# </td>
</tr>
<cfscript>
total_Hours = total_Hours + ttQ1.hours_;
total_HMin = total_HMin + ttQ1.hoursMin;
totalMinHours = (total_HMin - total_Hours) * 100;
total_H = total_Hours + minToHours (totalMinHours);
total_OTHours = total_OTHours + val (ttQ1.overtime_);
total_OTHMin = total_OTHMin + val (ttQ1.overTimeMin);
totalOTMinHours = (total_OTHMin - total_OTHours) * 100;
total_O = total_OTHours + minToHours (totalOTMinHours);
total = total_H + total_O;
</cfscript>
</cfoutput>
<tr style="height:20">
<td colspan="3" class="hdrMedDarkGreyLf"> #application.stLangFormField[lang][formName][58].descr#<!-Subtotal:-></td>
<td style="width:100" class="hdrMedDarkGreyRt">#replace (formatHoursMin (total_H), ".",":")# </td>
<td style="width:100" class="hdrMedDarkGreyRt">#replace (formatHoursMin (total_O), ".",":")# </td>
<td style="width:100" class="hdrMedDarkGreyRt">#replace (formatHoursMin (total), ".",":")# </td>
</tr>
</cfoutput>
</table>
</td>
</tr>
</table>
</cfsavecontent>
