# 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
```sql
select filerealpath from imagefile t1,docimagefile t2
where t1.imagefileid=t2imagefileid and t2.docid= Docment ID
```
## 3. Get month in the date
```sql
doFieldSQL("select RIGHT(SUBSTRING('Field id',0,8),2) ")
```
## 4. Query workflow where operator is matrix
```sql
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
```sql
select MONTH(DATEADD(MONTH,-1,GETDATE()))
```
## 6. Query all workflow and corresponding nodes that have a detailed horizontal scroll bar enabled
```sql
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)
```sql
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
```sql
select * from workflow_base where id
in(select distinct workflowid from workflow_nodeOverTime)
```
## 10. Query all completed workflow
```sql
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
```sql
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
```sql
select * from workflow_nodebase where id in (
select nodeid from workflow_flownode where workflowid = 73)
```
## 12. Query all delete workflow but message not deleted
```sql
select * from ECOLOGY_MESSAGE_INFO_READ
where targetid in (select requestid from workflow_requestbase_dellog)
```
## 13. Export organization structure
```sql
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
```sql
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
```javascript
```
## 16. Check date and time
```javascript
```
## 17. Query all users who did not login to system
```sql
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
```sql
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
```sql
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**
```sql
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**
```sql
select * from workflow_nodefieldattr where
attrcontent like '%'+cast(fieldid as varchar)+'%'
```
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://ec9help.weaver.com.co/3-common-sql-script.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.