3 Common SQL Script

1. Enable sql log

Configuration file：ecology\WEB-INF\prop\weaver_isSqlLog.properties

2. Get file path on server with doc id

select  filerealpath from  imagefile t1,docimagefile t2 
where t1.imagefileid=t2imagefileid  and t2.docid= Docment ID

3. Get month in the date

doFieldSQL("select RIGHT(SUBSTRING('Field id',0,8),2)  ")

4. Query workflow where operator is matrix

select a.workflowname Workflow,a.version Version,c.nodename Node,d.groupname Group,g.name Matrix
from workflow_base a
inner join workflow_flownode b on a.id=b.workflowid  and b.NODETYPE<>0 --Not creator
inner join workflow_nodebase c on b.nodeid = c.id --and  c.IsFreeNode is null
inner join workflow_nodegroup d on c.id=d.nodeid
inner join workflow_groupdetail e on d.id=e.groupid
inner join workflow_groupdetail_matrix f on f.groupdetailid = e.id
inner join matrixinfo g on g.id = f.matrix
where e.type=99  --Matrix type
and a.isvalid = '1' --Valid workflow
and f.matrix in (6)  --id
order by a.workflowname,c.nodename,d.groupname

5. Get last month

select  MONTH(DATEADD(MONTH,-1,GETDATE()))

6. Query all workflow and corresponding nodes that have a detailed horizontal scroll bar enabled

select workflowid,workflowname,b.nodeid,nodename from workflow_base a,
workflow_flownode b,workflow_nodebase c,workflow_nodeformgroup d 
where a.id=b.workflowid and b.nodeid=c.id and d.nodeid=b.nodeid and d.allowscroll='1'

7. Query which workflow with detail table (only for new forms)

select * from workflow_base a where isbill=1 and not exists 
(select 1 from workflow_billfield where billid=a.formid and viewtype=1)

8. Change website icon

Change favicon.ico in e-cology root directory

9. Get all workflow configure with Overtime

select * from workflow_base where id 
in(select distinct workflowid from workflow_nodeOverTime)

10. Query all completed workflow

select workflowtype,
       workflowid,
       viewtype,
       count(distinct requestid) workflowcount
  from workflow_currentoperator
 where isremark in ('2', '4')
   and iscomplete = 1
   and islasttimes = 1
   and userid = 1
   and usertype = 0
   and exists
 (select 1
          from workflow_requestbase c
         where (c.deleted <> 1 or c.deleted is null or c.deleted = '')
           and c.workflowid = workflow_currentoperator.workflowid
           and c.currentnodetype = '3'
           and c.requestid = workflow_currentoperator.requestid
           and (isnull(c.currentstatus, -1) = -1 or
               (isnull(c.currentstatus, -1) = 0 and c.creater = 1)))
 group by workflowtype, workflowid, viewtype
 order by workflowtype, workflowid

11. Show node submit time on the form

select top 1 operatedate+' '+operatetime from workflow_requestLog 
where requestid = $requestid$ and nodeid = 377 order by operatedate desc,
operatetime desc -- 377 is node id

How to get node id base on workflow id

select * from workflow_nodebase where id in (
select nodeid from workflow_flownode where workflowid = 73)  

12. Query all delete workflow but message not deleted

select * from ECOLOGY_MESSAGE_INFO_READ 
where targetid in (select requestid  from workflow_requestbase_dellog)

13. Export organization structure

select a.id as Department_ID,
       a.departmentcode as Department_code,
       a.departmentmark as Department_name,
       (select b.departmentmark from hrmdepartment b where b.id = a.supdepid) as Supior_Department,
       (select c.subcompanyname from hrmsubcompany c where c.id = a.subcompanyid1) as Divison,
  from hrmdepartment a
 where 1 = 1
 order by id asc;

14. Query the time consumption of all nodes

select b.lastname Name,
c.workflowname Workflow_type,
e.requestname Request_Title,
d.nodename Node,
a.receivedate Receive_Date,
a.receivetime Receive_Time,
a.operatedate Operate_Date,
a.operatetime Operate_Time,
DATEDIFF(mi,receivedate + ' ' + receivetime,operatedate + ' ' + operatetime) as 'Time/Mins'
from workflow_currentoperator a
left JOIN hrmresource b
on a.userid = b.id
left join workflow_base c
on a.workflowid = c.id
left join workflow_nodebase d
on a.nodeid = d.id
left join workflow_requestbase e
on a.requestid = e.requestid
where a.operatedate is not null and a.operatetime is not null and a.isremark in (2,4)
and a.receivedate >= '2019-01-01' and a.receivedate <= '2019-08-29' --Time Range Control
and a.userid = 3 --User Control
order by a.id asc

15. Add placeholder in textbox

<script type="text/javascript">
  jQuery(document).ready(function(){
    $("#field7126").attr('placeholder',"Please upload attachment in comments");
    $("#field7041").attr('placeholder',"Please upload attachment in comments");
    $("#field7079").attr('placeholder',"Please upload attachment in comments");
    $("#field7112").attr('placeholder',"Std: 0.1% per day");
    $("#field7113").attr('placeholder',"Std: 60 days");
    $("#field7070").attr('placeholder',"Std: on 5th/month");
    $("#field7114").attr('placeholder',"Std: 3-6 months");
    $("#field7115").attr('placeholder',"Std: 3 month");
    $("#field7076").attr('placeholder',"Std: Deposit");
  });
</script>

16. Check date and time

<script>
    jQuery(document).ready(function() {
        WfForm.bindFieldChangeEvent("field5866,field5870,field8239,field5871",
        function(obj, id, value) {
            console.log("WfForm.bindFieldChangeEvent--", obj, id, value);
            var startdate = WfForm.getFieldValue("field5866"); //Start Date Field
            var enddate = WfForm.getFieldValue("field8239"); //End Date Field
            var starttime = WfForm.getFieldValue("field5870"); // Start Time
            var endtime = WfForm.getFieldValue("field5871"); // End Time
            if (startdate != "" && enddate != "" && startdate > enddate) {
                WfForm.showMessage("Start Date later than end date！！！", 2, 5);
                WfForm.changeFieldValue("field5866", {
                    value: ""
                });
                WfForm.changeFieldValue("field8239", {
                    value: ""
                });
            } else if (startdate != "" && enddate != "" && startdate == enddate) {
                if (starttime != "" && endtime != "" && starttime >= endtime) {
                    WfForm.showMessage("Start date and end date is the same date, but start time later than end time！！！", 2, 5);
                    WfForm.changeFieldValue("field5870", {
                        value: ""
                    });
                    WfForm.changeFieldValue("field5871", {
                        value: ""
                    });
                } else {}
            } else {}
        });
    });
</script>

17. Query all users who did not login to system

select id as User_id, lastname as Name, loginid as Account
  from hrmresource
 where status in(0,1,2,3) and not exists--Who didnot have login log in last 3 month
 (select 1
          from hrmsysmaintenancelog
         where hrmresource.id = hrmsysmaintenancelog.relatedid
           and hrmsysmaintenancelog.operatedate >= ''2016-11-06''--Login start time
           and hrmsysmaintenancelog.operatedate < ''2017-02-07'')--Login ENd time

18. Workflow process log

select t1.clientip, t1.logtype,t1.operatedate,t1.operatetime,t1.workflowid,t1.operator,
t1.requestid, t2.creater, t2.requestname
from  workflow_requestLog t1,workflow_requestbase t2 
where t1.requestid=t2.requestid
order by t1.operatedate desc,t1.operatetime desc

19. Get exit condition

select a.workflowid,c.workflowname, a.nodeid,d.nodename,
a.linkname,b.rulename,b.condit  
from workflow_nodelink a,rule_base b ,workflow_base c,workflow_nodebase d
where  b.linkid=a.id and a.workflowid=c.id and a.nodeid =d.id 

20. Check any statement make CPU usage too high

20.1 Check field linkage = assign field

select *
  from workflow_base
 where id in
       (select distinct workflowid
          from (select main.id, entry.triggerfieldname, entry.workflowid
                  from workflow_datainput_entry entry
                  left join workflow_datainput_main main
                    on main.entryid = entry.id) t1,
               (select * from workflow_datainput_field where type = 2) t2
         where t2.datainputid = t1.id
           and t2.pagefieldname = t1.triggerfieldname)

20.2 check field attribute

select * from workflow_nodefieldattr where 
attrcontent like '%'+cast(fieldid as varchar)+'%'