logDB

package th.in.oneauthen.object;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;


@Entity
@Table(name="LOG")
@NamedQueries(value = {
        @NamedQuery(name="queryByUserId", query="SELECT sHistory.timeSign,sHistory.type,sHistory.status"
                + "                                 FROM LogReportDB sHistory "
                + "                                 WHERE sHistory.creator = :creator"
                + "                                 ORDER BY sHistory.timeSign DESC"
                + "") ,
        @NamedQuery(name="queryByMonthAndYear", query="SELECT DATE(timeSign),count(s),sum(case when s.status = 'success'then 1 else 0 end) as pass, sum(case when s.status = 'fail'then 1 else 0 end) as noPass "
                + "                                                 FROM LogReportDB s WHERE s.creator = :creator AND MONTH(timeSign) = :monthDoc AND YEAR(timeSign) = :yearDoc  "
                + "                                                 GROUP BY DATE(timeSign) "
                + "                                                 ORDER BY DATE(timeSign)"),
        @NamedQuery(name="queryByMonthAndYearTotal", query="SELECT sum(s.total),sum(case when s.status = 'success'then 1 else 0 end) as pass, sum(case when s.status = 'fail'then 1 else 0 end) as noPass "
                + "                                                         FROM LogReportDB s WHERE s.creator = :creator AND MONTH(timeSign) = :monthDoc AND YEAR(timeSign) = :yearDoc  "
                + "                                                         GROUP BY MONTH(timeSign),YEAR(timeSign)"),
        @NamedQuery(name="queryMinDate", query="from DocumentDB a " +
                                                "where a.timeSign in (" +
                                                "    select min(b.timeSign) " +
                                                "    from LogReportDB b " +
                                                "where b.creator = :creator"+
                                                ")"),
        @NamedQuery(name="queryBetweenDate", query="SELECT MONTH(timeSign),YEAR(timeSign),sum(s.total),sum(case when s.status = 'success'then 1 else 0 end) as pass, sum(case when s.status = 'fail'then 1 else 0 end) as noPass "
                                                    + "FROM LogReportDB s WHERE s.creator = :creator AND DATE(timeSign) BETWEEN :stDate AND :edDate "
                                                    + "GROUP BY MONTH(timeSign),YEAR(timeSign)"
                                                    ),
        @NamedQuery(name="queryInDate", query="SELECT DATE(timeSign),sum(s.total),sum(case when s.status = 'success'then 1 else 0 end) as pass, sum(case when s.status = 'fail'then 1 else 0 end) as noPass "
                                            + "FROM LogReportDB s WHERE s.creator = :creator AND DATE(timeSign) = :dateFind "
                                            )
})
public class LogReportDB implements Serializable {

    private static final long serialVersionUID = -935850171838295762L;

    /**
     *
     */

    @Id
    @GeneratedValue @Column(name ="ID")
    private int logId;

    @ManyToOne
    @JoinColumn(name = "CREATOR" , nullable = false)
    private UserUidDB creator;

    @Column(name = "TIME_SIGN")
    private Date timeSign;
    @Column(name = "STATUS")
    private String status;
    @Column(name = "TOTAL")
    private int total;
    @Column(name = "TYPE")
    private String type;
    @Column(name = "ISSUE")
    private String issue;
    @Column(name = "SIGNATURE_ID")
    private int signatureId;



    public int getSignatureId() {
        return signatureId;
    }
    public void setSignatureId(int signatureId) {
        this.signatureId = signatureId;
    }
    public int getLogId() {
        return logId;
    }
    public void setLogId(int logId) {
        this.logId = logId;
    }
    public UserUidDB getCreator() {
        return creator;
    }
    public void setCreator(UserUidDB creator) {
        this.creator = creator;
    }
    public Date getTimeSign() {
        return timeSign;
    }
    public void setTimeSign(Date timeSign) {
        this.timeSign = timeSign;
    }
    public String getStatus() {
        return status;
    }
    public void setStatus(String status) {
        this.status = status;
    }
    public int getTotal() {
        return total;
    }
    public void setTotal(int total) {
        this.total = total;
    }
    public String getType() {
        return type;
    }
    public void setType(String type) {
        this.type = type;
    }
    public String getIssue() {
        return issue;
    }
    public void setIssue(String issue) {
        this.issue = issue;
    }



}