package com.vsc.ejb.persistence.query;
import java.io.Serializable;
import java.sql.Timestamp;
import javax.persistence.CascadeType;
import javax.persistence.ColumnResult;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.SqlResultSetMappings;
import javax.persistence.Table;
import javax.persistence.TableGenerator;
import com.clindataware.cdms.ejb.persistence.study.Study;
import com.clindataware.cdms.ejb.persistence.study.Subject;
@Entity
@Table(name = "cdms_query")
//這里可以放多個具名查詢，有多個具名查詢必須要用到NamedQueries。
@NamedQueries( {
@NamedQuery(name = "Query.statisticByItem", query = "select count(query.itemoid) as sum,query.eventpath,query.formpath,query.itemoid,query.isprocessed"
+ " from Query query "
+ " where query.study.id=:studyid and query.isdisable='false'"
+ " group by query.eventpath,query.formpath,query.itemoid,query.isprocessed"),
// sum, pid, pname, isp?
@NamedQuery(name = "Query.statisticByInvestigator", query = "select count(query.participantid) as sum,user.fullname,query.participantid,query.isprocessed"
+ " from Query query,Participant part,User user"
+ " where query.study.id=:studyid "
+ " and part.studyid=:studyid "
+ " and query.participantid=part.user.id "
+ " and part.user.id=user.id "
+ " and query.isdisable='false' "
+ " group by query.participantid,query.isprocessed,user.fullname "),
})
//這里可以放多個原生的具名查詢，有多個原生的具名查詢必須要用到NamedNativeQueries。
@NamedNativeQueries({
// sum, pid, pname, isp?
@NamedNativeQuery(name = "Query.statisticBySite", query = "select count(b.id) as sum,b.name,b.id,a.isprocessed from " +
"(" +
" select query.isprocessed,org.siteid as org_siteid,subject.siteid as sub_siteid" +
" from cdms_Query query, cdms_Organization_Study org, cdms_Subject subject" +
" where query.studyid=:studyid and query.studyid=org.studyid " +
" and query.subjectid=subject.id and query.isdisable='false'" +
") a" +
" right join"+
" (select distinct site.* from cdms_Query query, cdms_Organization site, cdms_Organization_Study org, cdms_Subject subject" +
" where query.studyid=:studyid and site.id=org.siteid and query.studyid=org.studyid " +
" and site.category=1 and site.id=subject.siteid" +
" ) b on (a.org_siteid = b.id and a.sub_siteid = b.id) " +
" group by b.id, b.name, a.isprocessed",
resultSetMapping = "Query.statisticBySiteMapping"),
// sum, qtype, code, isp?
@NamedNativeQuery(name = "Query.statisticByCause", query = "select count(query.id) as sum,qtype.name as name,qtype.code as code,query.isprocessed as isprocessed from cdms_querytype qtype left join cdms_query query on ( query.studyid = :studyid and query.querytype = qtype.code and query.isdisable = 'false' ) group by qtype.name,qtype.code,query.isprocessed",
resultSetMapping = "Query.statisticByCauseMapping")
})
//SqlResultSetMapping這定結果集返回的實體類型或標量值。
@SqlResultSetMappings({
@SqlResultSetMapping(name = "Query.statisticByCauseMapping", columns = {
@ColumnResult(name = "sum"), @ColumnResult(name = "name"),
@ColumnResult(name = "code"), @ColumnResult(name = "isprocessed") }),
@SqlResultSetMapping(name = "Query.statisticBySiteMapping", columns = {
@ColumnResult(name = "sum"), @ColumnResult(name = "name"),
@ColumnResult(name = "id"), @ColumnResult(name = "isprocessed") })
})
public class Query implements Serializable {
private static final long serialVersionUID = 5025535683766463922L;
private Long id;
private String correctedvalue;
private String correctedcause;
private Boolean isdisable;
private Long lastmodifierid;
private String queryvalue;
private Subject subject;
private Integer querytype;
private Long creatorid;
private Study study;
private Boolean isprocessed;
private Timestamp lastmodifydate;
private String querynumber;
private Long participantid;
private Boolean iscorrected;
private Timestamp createdate;
private String querycause;
private Integer pagenumber;
private String itemoid;
private String itemname;
private String iteminformation;
private String eventpath;
private String formpath;
private Timestamp enddate;
public Query() {
super();
}
@TableGenerator(name = "queryGen", table = "cdms_seq", pkColumnName = "pkvalue", valueColumnName = "value", pkColumnValue = "cdms_query", allocationSize = 1)
@Id
@GeneratedValue(strategy = GenerationType.TABLE, generator = "queryGen")
public Long getId() {
return this.id;
}
public void setId(Long id) {
this.id = id;
}
public String getCorrectedvalue() {
return this.correctedvalue;
}
public void setCorrectedvalue(String correctedvalue) {
this.correctedvalue = correctedvalue;
}
public String getCorrectedcause() {
return this.correctedcause;
}
public void setCorrectedcause(String correctedcause) {
this.correctedcause = correctedcause;
}
public Boolean getIsdisable() {
return this.isdisable;
}
public void setIsdisable(Boolean isdisable) {
this.isdisable = isdisable;
}
public Long getLastmodifierid() {
return this.lastmodifierid;
}
public void setLastmodifierid(Long lastmodifierid) {
this.lastmodifierid = lastmodifierid;
}
public String getQueryvalue() {
return this.queryvalue;
}
public void setQueryvalue(String queryvalue) {
this.queryvalue = queryvalue;
}
@ManyToOne(cascade = CascadeType.REFRESH)
@JoinColumn(name = "subjectid", referencedColumnName = "id")
public Subject getSubject() {
return this.subject;
}
public void setSubject(Subject subject) {
this.subject = subject;
}
public Integer getQuerytype() {
return this.querytype;
}
public void setQuerytype(Integer querytype) {
this.querytype = querytype;
}
public Long getCreatorid() {
return this.creatorid;
}
public void setCreatorid(Long creatorid) {
this.creatorid = creatorid;
}
@ManyToOne(cascade = CascadeType.REFRESH)
@JoinColumn(name = "studyid", referencedColumnName = "id")
public Study getStudy() {
return this.study;
}
public void setStudy(Study study) {
this.study = study;
}
public Boolean getIsprocessed() {
return this.isprocessed;
}
public void setIsprocessed(Boolean isprocessed) {
this.isprocessed = isprocessed;
}
public Timestamp getLastmodifydate() {
return this.lastmodifydate;
}
public void setLastmodifydate(Timestamp lastmodifydate) {
this.lastmodifydate = lastmodifydate;
}
public String getQuerynumber() {
return this.querynumber;
}
public void setQuerynumber(String querynumber) {
this.querynumber = querynumber;
}
public Long getParticipantid() {
return this.participantid;
}
public void setParticipantid(Long participantid) {
this.participantid = participantid;
}
public Boolean getIscorrected() {
return this.iscorrected;
}
public void setIscorrected(Boolean iscorrected) {
this.iscorrected = iscorrected;
}
public Timestamp getCreatedate() {
return this.createdate;
}
public void setCreatedate(Timestamp createdate) {
this.createdate = createdate;
}
public String getQuerycause() {
return this.querycause;
}
public void setQuerycause(String querycause) {
this.querycause = querycause;
}
public String getIteminformation() {
return iteminformation;
}
public void setIteminformation(String iteminformation) {
this.iteminformation = iteminformation;
}
public String getItemname() {
return itemname;
}
public void setItemname(String itemname) {
this.itemname = itemname;
}
public String getItemoid() {
return itemoid;
}
public void setItemoid(String itemoid) {
this.itemoid = itemoid;
}
public Integer getPagenumber() {
return pagenumber;
}
public void setPagenumber(Integer pagenumber) {
this.pagenumber = pagenumber;
}
/**
* @return Returns the eventpath.
*/
public String getEventpath() {
return eventpath;
}
/**
* @param eventpath
* The eventpath to set.
*/
public void setEventpath(String eventpath) {
this.eventpath = eventpath;
}
/**
* @return Returns the formpath.
*/
public String getFormpath() {
return formpath;
}
/**
* @param formpath
* The formpath to set.
*/
public void setFormpath(String formpath) {
this.formpath = formpath;
}
public Timestamp getEnddate() {
return enddate;
}
public void setEnddate(Timestamp enddate) {
this.enddate = enddate;
}
}