package com.vst.dao.hibernate;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.FlushMode;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import com.vst.dao.ConstructionExampleDao;
import com.vst.model.BuildingObject;
import com.vst.model.ConstructionDefect;
import com.vst.model.ConstructionExample;
import com.vst.model.DangerCategory;
import com.vst.model.DefectType;
import com.vst.model.DefectVarity;
import com.vst.model.DefectZone;
import com.vst.model.ObjectConstruction;
public class ConstructionExampleDaoHibernate extends HibernateDaoSupport implements ConstructionExampleDao {
protected final Log log = LogFactory.getLog(getClass());
public Integer getMaxId(){
Integer max=(Integer) super.getSession().createQuery("select max(exampleId) from ConstructionExample").uniqueResult();
if(max==null){
return new Integer(1);
}
return (Integer)(max.intValue()+1);
}
public Integer getExampleDefectCount(String exampleId){
Long count=(Long)getSession().createQuery(
"select count(*) from ConstructionDefect constructionDefect where constructionDefect.exampleId=?")
.setString(0,exampleId.toString())
.uniqueResult();
return count==null?new Integer(0):count.intValue();
}
public Integer getExampleDefectZoneCount(String exampleId){
Long count=(Long)getSession().createQuery("select count(*) from ConstructionDefectZone constructionDefectZone where constructionDefectZone.exampleId=?").setString(0,exampleId.toString()).uniqueResult();
return count==null?new Integer(0):count.intValue();
}
public ConstructionExample getConstructionExampleForBreadCrump(String exampleId){
return (ConstructionExample)super.getSession().createQuery("select new ConstructionExample(exampleId, exampleRelativeNumber, exampleName, objectConstructionId) from ConstructionExample where exampleId=?").setString(0,exampleId).uniqueResult();
}
public List getExampleDefectsById(Integer exampleId){
return getSession().createQuery("from ConstructionDefect where exampleId=?").setInteger(0,exampleId.intValue()).list();
}
public List getExampleDefectsZoneById(Integer exampleId){
return getSession().createQuery("from ConstructionDefectZone where exampleId=?").setInteger(0,exampleId.intValue()).list();
}
public DangerCategory getConstructionRecommendedDangerCategory(String exampleId){
if((getHibernateTemplate().find(
"from ConstructionExample constructionExample inner join fetch constructionExample.exampleDefects as defects " +
" where (defects.dangerCategory.categoryName='A' OR defects.dangerCategory.categoryName='А') " +
" AND constructionExample.exampleId="+exampleId).size() +
getHibernateTemplate().find(
"from ConstructionExample constructionExample inner join fetch constructionExample.exampleDefectsZone as defects " +
" where (defects.dangerCategory.categoryName='A' OR defects.dangerCategory.categoryName='А') " +
" AND constructionExample.exampleId="+exampleId).size())!=0){
return (DangerCategory)super.getSession()
.createQuery("from DangerCategory dangerCategory where dangerCategory.categoryName='A' OR dangerCategory.categoryName='А'")
.uniqueResult();
}
else{ if((getHibernateTemplate().find(
"from ConstructionExample constructionExample inner join fetch constructionExample.exampleDefects as defects " +
" where (defects.dangerCategory.categoryName='B' OR defects.dangerCategory.categoryName='Б') " +
" AND constructionExample.exampleId="+exampleId).size() +
getHibernateTemplate().find(
"from ConstructionExample constructionExample inner join fetch constructionExample.exampleDefectsZone as defects " +
" where (defects.dangerCategory.categoryName='B' OR defects.dangerCategory.categoryName='Б') " +
" AND constructionExample.exampleId="+exampleId).size())!=0){
return (DangerCategory)super.getSession().createQuery(
"from DangerCategory dangerCategory where dangerCategory.categoryName='B' OR dangerCategory.categoryName='Б'")
.uniqueResult();
}
}
return (DangerCategory)super.getSession().createQuery("from DangerCategory dangerCategory where dangerCategory.categoryName='C' OR dangerCategory.categoryName='В'").uniqueResult();
}
public List getConstructionExamplesForObject(String objectId){
BuildingObject buildingObject=(BuildingObject)getHibernateTemplate().get(BuildingObject.class,new Integer(objectId));
List typeList=buildingObject.getConstructionTypes();
List resultList=new ArrayList();
for (int i = 0; i < typeList.size(); i++) {
ObjectConstruction objectConstruction = (ObjectConstruction) typeList.get(i);
resultList.addAll(objectConstruction.getConstructionExamples());
}
return resultList;
}
public List getConstructionExamples(final String typeId) {
ObjectConstruction objectConstruction=(ObjectConstruction)getHibernateTemplate().get(ObjectConstruction.class,new Integer(typeId));
return objectConstruction.getConstructionExamples();
/* Remove the line above and uncomment this code block if you want
to use Hibernate's Query by Example API.
if (constructionExample == null) {
return getHibernateTemplate().find("from ConstructionExample");
} else {
// filter on properties set in the constructionExample
HibernateCallback callback = new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Example ex = Example.create(constructionExample).ignoreCase().enableLike(MatchMode.ANYWHERE);
return session.createCriteria(ConstructionExample.class).add(ex).list();
}
};
return (List) getHibernateTemplate().execute(callback);
}*/
}
public List getConstructionExamplesForTable(Integer objectId, Integer typeId) {
return doBuildConstructionExamplesListForUI(objectId, typeId);
}
private String buildCondition(Integer objectId, Integer typeId){
String res = "";
if (objectId != null || typeId != null) {
int conditionCnt = 0;
if (typeId != null) {
res = res + " ce.objectConstructionId=:objectConstructionId ";
conditionCnt++;
}
if (objectId != null ) {
if (conditionCnt > 0){
res = res + " and ";
}
res = res + " ce.buildObjectId=:buildObjectId ";
}
}
return res;
}
private Query presetParams(Query q, Integer objectId, Integer typeId){
if (typeId != null) {
q.setInteger("objectConstructionId", typeId);
}
if (objectId != null ) {
q.setInteger("buildObjectId", objectId);
}
return q;
}
protected List doBuildConstructionExamplesListForUI(Integer objectId, Integer typeId) {
List ceList = new ArrayList<ConstructionExample>();
String hql = "select" +
" ce.exampleId, " +
" ce.exampleRelativeNumber, " +
" ce.exampleName, "+
" ce.dangerCategory, "+
" ce.noDeffects "+
"from ConstructionExample as ce left join ce.dangerCategory ";
if (objectId != null || typeId != null) {
hql = hql + " where ";
hql = hql + buildCondition(objectId, typeId);
}
Query q = getSession().createQuery(hql);
presetParams(q, objectId, typeId);
List rowList = q.list();
Map<Integer, ConstructionExample> ceMap =
new HashMap<Integer, ConstructionExample>();
for (Object n : rowList){
Object[] cols = (Object[]) n;
ConstructionExample ce = new ConstructionExample();
ce.setExampleId((Integer) cols[0]);
ce.setExampleRelativeNumber((Integer) cols[1]);
ce.setExampleName((String) cols[2]);
ce.setDangerCategory((DangerCategory) cols[3]);
ce.setNoDeffects((Boolean) cols[4]);
if (ce.getRecommendedDangerCategory() == null){
DangerCategory dc = new DangerCategory();
dc.setDangerCategoryId(-1);
dc.setCategoryName("В");
ce.setRecommendedDangerCategory(dc);
}
ce.setExampleDefects(null);
ce.setDefectCount(new Integer(0));
ce.setExampleDefects(new ArrayList<ConstructionDefect>());
ceMap.put(ce.getExampleId(), ce);
ceList.add(ce);
}
String hql2 =
"select "+
" cd.constructionDefectId, "+
" cd.comment, "+
" dt.defectTypeId, "+
" dt.defectTypeName,"+
" dv.varityId, "+
" dv.varityName,"+
" dz.defectZoneId, "+
" dz.defectZoneName,"+
" cd.dangerCategory,"+
" cd.exampleId"+
" from ConstructionDefectZone as cd left join cd.defectType as dt " +
" left join cd.defectZone as dz left join cd.defectVarity as dv " +
" left join cd.dangerCategory";
if (objectId != null || typeId != null) {
hql2 = hql2 + " where cd.exampleId in (select ce.exampleId " +
" from ConstructionExample as ce where ";
hql2 = hql2 + buildCondition(objectId, typeId) +")";
}
q = getSession().createQuery(hql2);
presetParams(q, objectId, typeId);
List res = q.list();
for (Object r : res){
Object[] rescolumns = (Object[]) r;
ConstructionDefect cd = new ConstructionDefect(
(Integer) rescolumns[0], (String) rescolumns[1]);
DefectType dt = new DefectType(
(Integer) rescolumns[2], (String) rescolumns[3]);
DefectVarity dv = new DefectVarity(
(Integer) rescolumns[4], (String) rescolumns[5]);
DefectZone dz = new DefectZone(
(Integer) rescolumns[6], (String) rescolumns[7]);
cd.setDefectType(dt);
cd.setDefectVarity(dv);
cd.setDefectZone(dz);
cd.setDangerCategory((DangerCategory)rescolumns[8]);
ConstructionExample ce =
ceMap.get((Integer) rescolumns[9]);
if (ce != null) {
if (compareDangerCategory(cd.getDangerCategory(), ce.getRecommendedDangerCategory()) > 0){
ce.setRecommendedDangerCategory(cd.getDangerCategory());
}
ce.getExampleDefects().add(cd);
System.out.println("EXample defects size:"+ce.getExampleDefects().size());
ce.setDefectCount(new Integer(ce.getExampleDefects().size()));
}
}
return ceList;
}
private static HashMap<String, Integer> dangerWeight = new HashMap<String, Integer>();
static {
dangerWeight.put("А", 3);
dangerWeight.put("Б", 2);
dangerWeight.put("С", 1);
dangerWeight.put("A", 3);
dangerWeight.put("B", 2);
dangerWeight.put("C", 1);
}
private int compareDangerCategory(DangerCategory dc1, DangerCategory dc2){
int weight1 = 0;
int weight2 = 0;
if (dc1!=null && dangerWeight.containsKey(dc1.getCategoryName())){
weight1 = dangerWeight.get(dc1.getCategoryName());
}
if (dc2!=null && dangerWeight.containsKey(dc2.getCategoryName())){
weight2 = dangerWeight.get(dc2.getCategoryName());
}
return weight1-weight2;
}
public List getConstructionExamplesLite(String typeId, String destination) {
// ObjectConstruction objectConstruction=(ObjectConstruction)super.getObject(ObjectConstruction.class,new Integer(typeId));
List ceList = null;
if (destination.equals("page")) {
ceList = getSession().createQuery
( // (Integer exampleId, String exampleNumber, String exampleName
"select new ConstructionExample(" +
" ce.exampleId, " +
" ce.exampleRelativeNumber, " +
" ce.exampleName," +
" ce.constructionType," +
" (select dangerCategory from ConstructionExample as ce2 where ce2.exampleId=ce.exampleId)," +
" ce.noDeffects "+
") " +
"from ConstructionExample as ce where ce.objectConstructionId=?"
).setString(0,typeId).list();
// int size = ceList.size();
// for(int i=0; i<size; i++)
// {
// ConstructionExample constructionExample=( (ConstructionExample) ceList.get(i));
// constructionExample.setDangerCategory((DangerCategory)getSession().createQuery("select dangerCategory from ConstructionExample where exampleId=?").setInteger(0,constructionExample.getExampleId().intValue()).uniqueResult());
// }
return ceList;
}
else if(destination.equals("tree")) {
ceList = doBuildConstructionExamplesListForUI(null, new Integer(typeId));
return ceList;
}
/*- OLD list produce steps
ceList = getSession().createQuery
( // (Integer exampleId, String exampleNumber, String exampleName
"select new ConstructionExample(" +
"ce.exampleId, " +
"ce.exampleRelativeNumber, " +
"ce.exampleName "+
") " +
"from ConstructionExample as ce where ce.objectConstructionId=?"
).setString(0,typeId).list();
//filling list with defects
for (int i = 0; i < ceList.size(); i++) {
ConstructionExample constructionExample = (ConstructionExample)ceList.get(i);
constructionExample.setExampleDefects(
getSession().createQuery(
"from ConstructionDefect " +
" constrDefect where constrDefect.exampleId=?")
.setString(0, String.valueOf(constructionExample.getExampleId())).list());
}
*/
/*
//System.out.println("OBJECT CONSTRUCTION TYPE ID ================ " + typeId);
//System.out.println("size ================ " + ceList.size());
for(int i=0; i<ceList.size(); i++) {
//System.out.println("C#### " + ((ConstructionExample)ceList.get(i)).getExampleId());
}
*/
return ceList;
}
/**
* @see com.vst.dao.ConstructionExampleDao#getConstructionExample(Integer exampleId)
*/
public ConstructionExample getConstructionExample(final Integer exampleId) {
ConstructionExample constructionExample = (ConstructionExample) getHibernateTemplate().get(ConstructionExample.class, exampleId);
if (constructionExample == null) {
log.warn("uh oh, constructionExample with exampleId '" + exampleId + "' not found...");
throw new ObjectRetrievalFailureException(ConstructionExample.class, exampleId);
}
return constructionExample;
}
/**
* @see com.vst.dao.ConstructionExampleDao#saveConstructionExample(ConstructionExample constructionExample)
*/
public void saveConstructionExample(final ConstructionExample constructionExample) {
System.out.println("C E O"+constructionExample);
if(constructionExample.getExampleId()!=null)
getHibernateTemplate().update(constructionExample);
else
getHibernateTemplate().saveOrUpdate(constructionExample);
getHibernateTemplate().flush();
}
/**
* @see com.vst.dao.ConstructionExampleDao#removeConstructionExample(Integer exampleId)
*/
public void removeConstructionExample(final Integer exampleId) {
//TODO find out the way for auto-updating position fields after element deleting from collection
ConstructionExample constructionExample=(ConstructionExample)getConstructionExample(exampleId);
getHibernateTemplate().delete(constructionExample);
super.getSession().createQuery("update ConstructionExample set constructionExamplePosition=constructionExamplePosition-1 where objectConstructionId=? AND constructionExamplePosition>?").setString(0, String.valueOf(constructionExample.getObjectConstructionId())).setString(1, String.valueOf(constructionExample.getConstructionExamplePosition())).executeUpdate();
}
private Connection getConnection(Properties props) {
String driverURL = "jdbc:mysql://localhost/vstbase?useUnicode=true&characterEncoding=UTF-8";
Connection dbConn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
dbConn = (Connection) DriverManager.getConnection(driverURL, props);
return dbConn;
}
catch (Exception e) {
e.printStackTrace();
return null;
}
}
public List getExamplesWithNoDefects(Integer buildingObjectId, Properties props) throws SQLException {
Connection dbConn = getConnection(props);
Statement st = (Statement) dbConn.createStatement();
System.out.println("select exampleId from constructionExamples where constructionExamples.noDeffects=false and objectConstructionId in (select typeId from objectConstructions where objectId="+buildingObjectId+")");
ResultSet rs = (ResultSet) st.executeQuery("select exampleId from constructionExamples where constructionExamples.noDeffects=false and objectConstructionId in (select typeId from objectConstructions where objectId="+buildingObjectId+")");
List list=new ArrayList();
while (rs.next()) {
list.add(new Integer(rs.getInt(1)));
}
rs.close();
st.close();
dbConn.close();
return list;
}
public Integer getExampleNumberByConstructionObjectId(Integer objectConstructionId){
try {
return (Integer)
(
(
(Integer)
super.getSession().createQuery("select max(exampleRelativeNumber) from ConstructionExample where objectConstructionId=?)")
.setInteger(0,objectConstructionId.intValue()).uniqueResult()
).intValue()+1
);
} catch (Exception e) {
e.printStackTrace();
return new Integer(1);
}
}
public void copyExample(final Integer srcExampleId, final int copyNumber) {
if (copyNumber < 1){
return;
}
getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
session.setFlushMode(FlushMode.MANUAL);
for(int i=0; i< copyNumber; i++){
Query q = session.createSQLQuery(
"insert into constructionexamples (" +
" exampleName," +
" defectEsckizBlob," +
" wayToDefectEsckiz," +
" powerEsckizBlob," +
" wayToPowerEsckiz," +
" objectId," +
" examplePosition," +
" buildObjectId," +
" objectConstructionId," +
" exampleNumber," +
" dangerCategoryId," +
" noDeffects," +
" typeId," +
" constructionExamplePosition," +
" defectEsckizWayImage," +
" powerEsckizWayImage," +
" exampleRelativeNumber) " +
"select " +
" 'конструкция N" + (i+1) + "'," +
" defectEsckizBlob," +
" wayToDefectEsckiz," +
" powerEsckizBlob," +
" wayToPowerEsckiz," +
" objectId," +
" examplePosition," +
" buildObjectId," +
" objectConstructionId," +
" exampleNumber," +
" dangerCategoryId," +
" noDeffects," +
" typeId," +
" constructionExamplePosition," +
" defectEsckizWayImage," +
" powerEsckizWayImage," +
" exampleRelativeNumber + "+ (i+1) +
" from constructionexamples where exampleId=?"
).setInteger(0, srcExampleId);
q.executeUpdate();
if ((i>10) && (i%10 == 0)) {
session.flush();
}
}
session.flush();
return null;
}
});
}
}