Ejemplo de módulo SQL list donde aplicar el uso de clases para establecer una línea de tiempo

Os dejo un ejemplo de módulo SQL list donde aplicar el uso de clases propias de flexygo para establecer una línea de tiempo.

En este caso estaremos visualizando las acciones pasadas de los empleados para una cuenta específica. Para más información puedes visualizar la formación de módulos sql list:  https://www.youtube.com/watch?v=pOiZSjfTWuM 

Este sería el resultado: 

SQL:

SELECT TOP 3 ACTION.action_id, ACTION.descrip, isnull(lastact.startdate,ACTION.scheduledstart) as scheduledstart, isnull(lastact.enddate,ACTION.scheduledend) as scheduledend, ACTION.state_id, ACTION.type_id, ACTION.employee_id
,a.type_id as account_type_id
,t.descrip as descriptype
, e.firstname, e.lastname 
,e.firstname + ' ' + ISNULL (e.lastname,'')  as employeename
,e.image as employeeimage
,a.name as accountname
,c.name as contactname
,isnull(c.email, a.email) as contactemail, isnull(c.phone,a.phone) as contactphone, a.address
,CONVERT(varchar(5),ACTION.scheduledstart,108) as schstarttime
,CONVERT(varchar(5),ACTION.scheduledend,108) as schendtime
,CASE 
    WHEN DATEDIFF(SECOND, isnull(lastact.enddate,ACTION.scheduledend), GETDATE()) < 60 THEN ' Just Posted' 
    WHEN DATEDIFF(MINUTE, isnull(lastact.enddate,ACTION.scheduledend), GETDATE()) < 60 THEN CAST(DATEDIFF(MINUTE, isnull(lastact.enddate,ACTION.scheduledend), GETDATE()) AS VARCHAR(10)) + ' Minutes ago'
    WHEN DATEDIFF(MINUTE, isnull(lastact.enddate,ACTION.scheduledend), GETDATE()) < 24 * 60 THEN CAST(FLOOR(DATEDIFF(MINUTE, isnull(lastact.enddate,ACTION.scheduledend), GETDATE())/60) AS VARCHAR(10)) + ' Hours ago'
    ELSE CAST(FLOOR(DATEDIFF(HOUR, isnull(lastact.enddate,ACTION.scheduledend), GETDATE())/24) AS VARCHAR(10)) + ' Days ago'
END AS postedon
,lastact.startdate, lastact.enddate, lastact.starttime, lastact.endtime
FROM ACTION
INNER JOIN ACTION_TYPE t on t.type_id=ACTION.type_id
LEFT JOIN ACCOUNT a on a.account_id=ACTION.account_id
LEFT JOIN CONTACT c on c.contact_id = ACTION.contact_id
LEFT JOIN EMPLOYEE e on e.employee_id=ACTION.employee_id
OUTER APPLY (Select top 1 startdate,CONVERT(varchar(5),startdate,108) as starttime, enddate ,CONVERT(varchar(5),enddate,108) as endtime from ACTION_TRACKING t where t.action_id= ACTION.action_id order by startdate desc) As lastact
WHERE ACTION.state_id > 2
ORDER BY ACTION.scheduledend DESC

TEMPLATE

HEADER:

<legend>{{translate|Past actions}}</legend>

HTML:

<div class="appointment-timeline-group">{{scheduledend|date:L}}</div>
<div class="appointment-timeline">
  <div class="col-3 col-s-1 appointment-timeline-height">
    <div class="appointment-timeline-entry">
      <span class="hidden-s"><span class="txt-outstanding">{{endtime|isnull:{{schendtime}}}}</span><br><b class="size-xs">{{postedon}}</b></span>
      <i class="appointment-timeline-icon hidden-l {{type_id|switch:[1:flx-icon icon-email-2,2:flx-icon icon-call1,3:flx-icon icon-car-1,4:flx-icon icon-meeting,5:fa fa-calendar-o,6:flx-icon icon-circle,else:flx-icon icon-text]}}"></i>
    </div>
  </div>
  <div class="col-9 col-s-11 ">           
    <div class="appointment-timeline-body ">
      <div class="col-2">
        <img class="img-responsive img-circle" src="{{employeeimage|isnull:Custom/contactNotFound.png}}" style="max-height:60px;" alt="profile">
      </div>
      <div class="col-10">
        <flx-navbutton class="clickable" type="openpage" pagetypeid="edit" objectname="Action" objectwhere="(ACTION.action_id={{action_id}})" defaults="" targetid="popup" excludehist="false">
          <h4 class="txt-outstanding">{{firstname}} <strong>{{lastname}}</strong></h4>
          <span class="block">{{descrip}}</span>                  
          <span class="block">
            <i class="flx-icon {{account_type_id|switch:[0:icon-focus-6 txt-warning,1:icon-user4 txt-outstanding,2:icon-providers txt-tools,else:icon-clients1 txt-primary]}} icon-margin-right"></i>
            <small>{{accountname|isnull:no account}}</small>                    
          </span>
        </flx-navbutton>
        <br>
        <ul class="list-unstyled text-muted size-s">
          <li><i class="flx-icon icon-contacts2 icon-margin-right"></i>{{contactname|isnull:no contact}}</li>
          <li><i class="flx-icon icon-phone icon-margin-right"></i><a class="txt-color-darken" href="phone:{{contactphone}}">{{contactphone|isnull:no phone}}</a></li>
          <li><i class="flx-icon icon-email-2 icon-margin-right"></i><a class="txt-color-darken" href="mailto:{{contactemail}}">{{contactemail|isnull:no email}}</a></li>
        </ul>
      </div>              
    </div>                      
  </div>         
</div>      

FOOTER:

<div>
  <flx-navbutton type="openpage" pagetypeid="list" objectname="Actions" objectwhere="(ACTION.account_id='{{account_id}}' and ACTION.state_id>2)" defaults="{'account_id':'{{account_id}}'}" targetid="popup" excludehist="false">
    <a href="#" class="pull-right">{{translate|View all}}</a>
  </flx-navbutton>
</div>

EMPTY:

<legend>{{translate|Past actions}}</legend>
<div class="bg-white">{{translate|No activity}}</div>

0 Votos