List of usage examples for org.hibernate.type LongType LongType
public LongType()
From source file:br.gov.jfrj.siga.wf.relatorio.RelTempoDoc.java
License:Open Source License
/** * Retorna os docs no perodo indicado, ordenadas pelo tempo de * demora, podendo estar ou no finalizados. * //from www . ja v a2 s .com * Exemplo da query: * * SELECT PI.START_,PI.END_,VI.STRINGVALUE_,PI.ID_ FROM * SIGAWF.JBPM_PROCESSINSTANCE PI, (SELECT DISTINCT PROCESSINSTANCE_, * STRINGVALUE_ FROM SIGAWF.JBPM_VARIABLEINSTANCE WHERE NAME_ LIKE 'doc_%' * AND STRINGVALUE_ LIKE '%-_' AND STRINGVALUE_ IS NOT NULL) VI, (SELECT * * FROM SIGAWF.JBPM_PROCESSDEFINITION WHERE NAME_ = 'Contratao: fase de * anlise') PD WHERE PI.PROCESSDEFINITION_=PD.ID_ AND PI.END_ IS NOT NULL * AND PI.ID_ = VI.PROCESSINSTANCE_ AND (PI.START_ >= To_Date('01/03/2011') * and PI.START_ <= To_Date('31/03/2011')) AND (PI.END_ >= * To_Date('01/03/2011') and PI.END_ <= To_Date('31/05/2011')) ; * * * @param dataInicial * @param dataFinal * @param dataFinalAte * @param dataFinalDe * @param incluirAbertos * @return */ private Set<Doc> consultarDocs(String nomeProcedimento, Date dataInicialDe, Date dataInicialAte, Date dataFinalDe, Date dataFinalAte, Boolean incluirAbertos) { SQLQuery query = null; if (incluirAbertos) { query = (SQLQuery) WfDao.getInstance().getSessao() .createSQLQuery(getSQLConsultarDocumentosFinalizadosEAbertosNoPeriodo()); } else { query = (SQLQuery) WfDao.getInstance().getSessao() .createSQLQuery(getSQLConsultarDocumentosFinalizadosNoPeriodo()); } query.addScalar("START_", new CalendarType()); query.addScalar("END_", new CalendarType()); query.addScalar("STRINGVALUE_", new StringType()); query.addScalar("ID_", new LongType()); dataInicialAte = inclusiveData(dataInicialAte); dataFinalAte = inclusiveData(dataFinalAte); query.setString("nomeProcedimento", nomeProcedimento); query.setDate("dataInicialDe", dataInicialDe); query.setDate("dataInicialAte", dataInicialAte); query.setDate("dataFinalDe", dataFinalDe); query.setDate("dataFinalAte", dataFinalAte); List<Object[]> resultado = query.list(); Set<Doc> docs = new TreeSet<Doc>(new DocComparator()); for (Object[] o : resultado) { Doc s = new Doc(); Calendar inicio = (Calendar) o[0]; Calendar fim = (Calendar) o[1]; s.setNumeroDoc(o[2].toString()); s.setInicio(inicio); s.setFim(fim); s.setProcessInstanceID((Long) (o[3])); docs.add(s); } return docs; }
From source file:com.thoughtworks.go.server.persistence.MaterialRepository.java
License:Apache License
private List<Long> fromInclusiveModificationsForPipelineRange(Session session, String pipelineName, Integer fromCounter, Integer toCounter) { String pipelineIdsSql = queryExtensions.queryFromInclusiveModificationsForPipelineRange(pipelineName, fromCounter, toCounter);/*from w ww . j a v a 2s. c o m*/ SQLQuery pipelineIdsQuery = session.createSQLQuery(pipelineIdsSql); final List ids = pipelineIdsQuery.list(); if (ids.isEmpty()) { return new ArrayList<>(); } String minMaxQuery = " SELECT mods1.materialId as materialId, min(mods1.id) as min, max(mods1.id) as max" + " FROM modifications mods1 " + " INNER JOIN pipelineMaterialRevisions pmr ON (mods1.id >= pmr.actualFromRevisionId AND mods1.id <= pmr.toRevisionId) AND mods1.materialId = pmr.materialId " + " WHERE pmr.pipelineId IN (:ids) " + " GROUP BY mods1.materialId"; SQLQuery query = session .createSQLQuery("SELECT mods.id " + " FROM modifications mods" + " INNER JOIN (" + minMaxQuery + ") as edges on edges.materialId = mods.materialId and mods.id >= min and mods.id <= max" + " ORDER BY mods.materialId ASC, mods.id DESC"); query.addScalar("id", new LongType()); query.setParameterList("ids", ids); return query.list(); }
From source file:com.thoughtworks.go.server.persistence.MaterialRepository.java
License:Apache License
public Map<Long, List<ModificationForPipeline>> findModificationsForPipelineIds(final List<Long> pipelineIds) { final int MODIFICATION = 0; final int RELEVANT_PIPELINE_ID = 1; final int RELEVANT_PIPELINE_NAME = 2; final int MATERIAL_TYPE = 3; final int MATERIAL_FINGERPRINT = 4; //noinspection unchecked return (Map<Long, List<ModificationForPipeline>>) getHibernateTemplate() .execute((HibernateCallback) session -> { if (pipelineIds.isEmpty()) { return new HashMap<Long, List<ModificationForPipeline>>(); }//from ww w. ja v a 2 s . c om Map<PipelineId, Set<Long>> relevantToLookedUpMap = relevantToLookedUpDependencyMap(session, pipelineIds); SQLQuery query = session.createSQLQuery( "SELECT mods.*, pmr.pipelineId as pmrPipelineId, p.name as pmrPipelineName, m.type as materialType, m.fingerprint as fingerprint" + " FROM modifications mods " + " INNER JOIN pipelineMaterialRevisions pmr ON (mods.id >= pmr.fromRevisionId AND mods.id <= pmr.toRevisionId) AND mods.materialId = pmr.materialId " + " INNER JOIN pipelines p ON pmr.pipelineId = p.id" + " INNER JOIN materials m ON mods.materialId = m.id" + " WHERE pmr.pipelineId IN (:ids)"); @SuppressWarnings({ "unchecked" }) List<Object[]> allModifications = query.addEntity("mods", Modification.class) .addScalar("pmrPipelineId", new LongType()) .addScalar("pmrPipelineName", new StringType()) .addScalar("materialType", new StringType()).addScalar("fingerprint", new StringType()) .setParameterList("ids", relevantToLookedUpMap.keySet().stream() .map(PipelineId::getPipelineId).collect(Collectors.toList())) .list(); Map<Long, List<ModificationForPipeline>> modificationsForPipeline = new HashMap<>(); CollectionUtil.CollectionValueMap<Long, ModificationForPipeline> modsForPipeline = CollectionUtil .collectionValMap(modificationsForPipeline, new CollectionUtil.ArrayList<>()); for (Object[] modAndPmr : allModifications) { Modification mod = (Modification) modAndPmr[MODIFICATION]; Long relevantPipelineId = (Long) modAndPmr[RELEVANT_PIPELINE_ID]; String relevantPipelineName = (String) modAndPmr[RELEVANT_PIPELINE_NAME]; String materialType = (String) modAndPmr[MATERIAL_TYPE]; String materialFingerprint = (String) modAndPmr[MATERIAL_FINGERPRINT]; PipelineId relevantPipeline = new PipelineId(relevantPipelineName, relevantPipelineId); Set<Long> longs = relevantToLookedUpMap.get(relevantPipeline); for (Long lookedUpPipeline : longs) { modsForPipeline.put(lookedUpPipeline, new ModificationForPipeline(relevantPipeline, mod, materialType, materialFingerprint)); } } return modificationsForPipeline; }); }
From source file:com.thoughtworks.go.server.persistence.MaterialRepository.java
License:Apache License
private Map<PipelineId, Set<Long>> relevantToLookedUpDependencyMap(Session session, List<Long> pipelineIds) { final int LOOKED_UP_PIPELINE_ID = 2; final int RELEVANT_PIPELINE_ID = 0; final int RELEVANT_PIPELINE_NAME = 1; String pipelineIdsSql = queryExtensions.queryRelevantToLookedUpDependencyMap(pipelineIds); SQLQuery pipelineIdsQuery = session.createSQLQuery(pipelineIdsSql); pipelineIdsQuery.addScalar("id", new LongType()); pipelineIdsQuery.addScalar("name", new StringType()); pipelineIdsQuery.addScalar("lookedUpId", new LongType()); final List<Object[]> ids = pipelineIdsQuery.list(); Map<Long, List<PipelineId>> lookedUpToParentMap = new HashMap<>(); CollectionUtil.CollectionValueMap<Long, PipelineId> lookedUpToRelevantMap = CollectionUtil .collectionValMap(lookedUpToParentMap, new CollectionUtil.ArrayList<>()); for (Object[] relevantAndLookedUpId : ids) { lookedUpToRelevantMap.put((Long) relevantAndLookedUpId[LOOKED_UP_PIPELINE_ID], new PipelineId((String) relevantAndLookedUpId[RELEVANT_PIPELINE_NAME], (Long) relevantAndLookedUpId[RELEVANT_PIPELINE_ID])); }//from www .j a v a 2 s.c o m return CollectionUtil.reverse(lookedUpToParentMap); }
From source file:com.thoughtworks.go.server.persistence.MaterialRepository.java
License:Apache License
public Long latestModificationRunByPipeline(final CaseInsensitiveString pipelineName, final Material material) { final long materialId = findMaterialInstance(material).getId(); String key = cacheKeyForLatestPmrForPipelineKey(materialId, pipelineName.toLower()); Long modificationId = (Long) goCache.get(key); if (modificationId == null) { synchronized (key) { modificationId = (Long) goCache.get(key); if (modificationId == null) { modificationId = (Long) getHibernateTemplate().execute((HibernateCallback) session -> { SQLQuery sqlQuery = session.createSQLQuery("SELECT MAX(pmr.toRevisionId) toRevisionId " + "FROM (SELECT torevisionid, pipelineid FROM pipelineMaterialRevisions WHERE materialid = :material_id) AS pmr\n" + "INNER JOIN pipelines p ON ( p.name = :pipeline_name AND p.id = pmr.pipelineId)"); sqlQuery.setParameter("material_id", materialId); sqlQuery.setParameter("pipeline_name", pipelineName.toString()); sqlQuery.addScalar("toRevisionId", new LongType()); return sqlQuery.uniqueResult(); });//w ww. j a v a 2 s . c o m if (modificationId == null) { modificationId = -1L; } goCache.put(key, modificationId); } } } return modificationId; }
From source file:com.viettel.logistic.wms.dao.ProposedReceiptDAO.java
License:Open Source License
public List<ProposedReceipt> getListProposedReceiptByCondition(ProposedReceiptDTO proposedReceiptDTO) { List<ProposedReceipt> lstProposedReceiptDTOs = new ArrayList<>(); try {//from w ww. j a v a2 s. c o m StringBuilder sql = new StringBuilder(); List params = new ArrayList(); sql.append(" SELECT a.proposed_receipt_id proposedReceiptId,"); sql.append(" a.shipment_code shipmentCode,"); sql.append(" a.proposed_receipt_code proposedReceiptCode,"); sql.append(" a.stock_id stockId,"); sql.append(" a.customer_id customerId,"); sql.append(" a.number_invoice numberInvoice,"); sql.append(" a.expected_date expectedDate,"); sql.append(" a.create_date createDate,"); sql.append(" a.implementer_name implementerName,"); sql.append(" a.note note,"); sql.append(" a.contact_person contactPerson,"); sql.append(" a.status status,"); sql.append(" a.receive_location receiveLocation,"); sql.append(" a.attach_file_list attachFileList,"); sql.append(" a.recent_history_id recentHistoryId"); sql.append(" FROM proposed_receipt a"); sql.append(" WHERE 1=1"); //Tim kiem theo mat hang if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getGoodsName())) { sql.append(" AND a.proposed_receipt_id IN (SELECT DISTINCT c.request_id "); sql.append(" FROM goods_handover c"); sql.append(" WHERE lower(c.goods_name) LIKE lower(?) "); sql.append(" AND c.type = '1')"); params.add("%" + proposedReceiptDTO.getGoodsName() + "%"); // } //Tim kiem theo nguoi tao if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getImplementerName())) { sql.append(" AND a.implementer_name = ?"); params.add(proposedReceiptDTO.getImplementerName()); } //Tim kiem theo kho if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getStockId())) { sql.append(" AND a.stock_id = ?"); params.add(proposedReceiptDTO.getStockId()); } //Tim kiem theo khach hang if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getCustomerId())) { sql.append(" AND a.customer_id = ?"); params.add(proposedReceiptDTO.getCustomerId()); } //Tim kiem theo trang thai if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getStatus())) { sql.append(" AND a.status = ?"); params.add(proposedReceiptDTO.getStatus()); } //Tim kiem theo ngay du kien nhap tu ngay if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getExpectedDate())) { sql.append(" AND a.expected_date >= to_date(?,'dd/mm/yyyy')"); params.add(proposedReceiptDTO.getExpectedDate()); } //Tim kiem theo ngay du kien nhap den ngay if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getExpectedDateToDate())) { sql.append(" AND a.expected_date <= to_date(?,'dd/mm/yyyy') + 1"); params.add(proposedReceiptDTO.getExpectedDateToDate()); } //Tim kiem theo ngay tao tu ngay if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getCreateDate())) { sql.append(" AND a.create_date >= to_date(?,'dd/mm/yyyy')"); params.add(proposedReceiptDTO.getCreateDate()); } //Tim kiem theo ngay tao den ngay if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getCreateDateToDate())) { sql.append(" AND a.create_date <= to_date(?,'dd/mm/yyyy') + 1"); params.add(proposedReceiptDTO.getCreateDateToDate()); } //Tim kiem theo ma phieu if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getProposedReceiptCode())) { sql.append(" AND lower(a.proposed_receipt_code) like lower(?)"); params.add("%" + proposedReceiptDTO.getProposedReceiptCode() + "%"); } //Tim kiem theo ma lo hang if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getShipmentCode())) { sql.append(" AND lower(a.shipment_code) like lower(?)"); params.add("%" + proposedReceiptDTO.getShipmentCode() + "%"); } //Tim kiem theo so invoice if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getNumberInvoice())) { sql.append(" AND lower(a.number_invoice) like lower(?) "); params.add("%" + proposedReceiptDTO.getNumberInvoice() + "% \n"); } //Sap xep theo ma phieu sql.append(" ORDER BY a.create_date DESC"); //Tao cau lenh sql SQLQuery query = getSession().createSQLQuery(sql.toString()); //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong query.setResultTransformer(Transformers.aliasToBean(ProposedReceipt.class)); query.addScalar("proposedReceiptId", new LongType()); query.addScalar("shipmentCode", new StringType()); query.addScalar("proposedReceiptCode", new StringType()); query.addScalar("stockId", new LongType()); query.addScalar("customerId", new LongType()); query.addScalar("numberInvoice", new StringType()); query.addScalar("expectedDate", new DateType()); query.addScalar("createDate", new DateType()); query.addScalar("implementerName", new StringType()); query.addScalar("note", new StringType()); query.addScalar("contactPerson", new StringType()); query.addScalar("status", new StringType()); query.addScalar("receiveLocation", new StringType()); query.addScalar("attachFileList", new StringType()); query.addScalar("recentHistoryId", new LongType()); //Truyen cac tham so truyen vao de thuc hien tim kiem for (int i = 0; i < params.size(); i++) { query.setParameter(i, params.get(i)); } //Day du lieu ra danh sach doi tuong lstProposedReceiptDTOs = query.list(); } catch (Exception e) { lstProposedReceiptDTOs = new ArrayList<>(); e.printStackTrace(); } return lstProposedReceiptDTOs; }
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialStripDAO.java
License:Open Source License
public List<StockGoodsSerialStrip> getListStockGoodsSerial(StockGoodsSerialStripDTO stockGoodsSerialStripDTO) { List<StockGoodsSerialStrip> lstStockGoodsSerialStrip = null; StringBuffer sql = new StringBuffer(); List lstParams = new ArrayList(); ///*from w w w. java 2s.c om*/ sql.append("SELECT cust_id custId,"); sql.append(" owner_id ownerId,"); sql.append(" owner_type ownerType,"); sql.append(" cell_code cellCode,"); sql.append(" goods_id goodsId,"); sql.append(" state goodsState,"); sql.append(" status,"); sql.append(" channel_type_id channelTypeId,"); sql.append(" from_serial fromSerial,"); sql.append(" to_serial toSerial, quantity "); sql.append(" FROM table(show_serial_strip (cursor ( SELECT sts.cust_id,"); sql.append(" sts.owner_id,"); sql.append(" sts.owner_type,"); sql.append(" sts.cell_code,"); sql.append(" sts.goods_id,"); sql.append(" sts.goods_state,"); sql.append(" sts.status,"); sql.append(" sts.channel_type_id,"); sql.append(" sts.serial"); sql.append(" FROM stock_goods_serial sts"); sql.append(" WHERE 1 = 1 "); //Tim kiem theo khach hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) { sql.append("AND sts.cust_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getCustId()); } //Tim kiem theo kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) { sql.append("AND sts.owner_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getOwnerId()); } //Tim kiem theo loai kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) { sql.append("AND sts.owner_type = ? "); lstParams.add(stockGoodsSerialStripDTO.getOwnerType()); } //Tim kien theo mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) { sql.append("AND sts.goods_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getGoodsId()); } //Tim theo tinh trang hang hoa if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsState())) { sql.append("AND sts.goods_state = ? "); lstParams.add(stockGoodsSerialStripDTO.getGoodsState()); } //Tim kiem trang thai serial hang hoa if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) { sql.append("AND sts.status = ? "); lstParams.add(stockGoodsSerialStripDTO.getStatus()); } //Tim kiem theo Kenh if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getChannelTypeId())) { sql.append("AND sts.channel_type_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getChannelTypeId()); } //Tim kiem theo from serial if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getFromSerial())) { sql.append("AND sts.serial >= ? "); lstParams.add(stockGoodsSerialStripDTO.getFromSerial()); } //Tim kiem theo to serial if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getToSerial())) { sql.append("AND sts.serial <= ? "); lstParams.add(stockGoodsSerialStripDTO.getToSerial()); } //Tim theo cell if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) { sql.append("AND sts.cell_code = ? "); lstParams.add(stockGoodsSerialStripDTO.getCellCode()); } //Tim theo Barcode if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBarcode())) { sql.append("AND sts.barcode = ? "); lstParams.add(stockGoodsSerialStripDTO.getBarcode()); } //Tim theo Bincode if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBincode())) { sql.append("AND sts.bincode = ? "); lstParams.add(stockGoodsSerialStripDTO.getBincode()); } // sql.append(" ORDER BY cust_id,owner_id,owner_type,cell_code,goods_id,goods_state,status,serial),"); sql.append(" TO_NUMBER (?))) a"); lstParams.add(stockGoodsSerialStripDTO.getQuantity()); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialStrip.class)); query.addScalar("custId", new LongType()); query.addScalar("ownerId", new LongType()); query.addScalar("ownerType", new StringType()); query.addScalar("cellCode", new StringType()); query.addScalar("goodsId", new LongType()); query.addScalar("goodsState", new StringType()); query.addScalar("status", new StringType()); query.addScalar("channelTypeId", new StringType()); // sua lai LongType -> StringType (tiepnv6) query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("quantity", new LongType()); // for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } lstStockGoodsSerialStrip = query.list(); // return lstStockGoodsSerialStrip; }
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialStripDAO.java
License:Open Source License
/** * Thienng1 addby 28/03/2016 Kiem tra xem Kit da co trong kho hay khong * * @param orderId/*from w w w. j av a2 s . c om*/ * @param stockTransSerialDTO * @param session * @return */ //Tim kiem serial theo dai theo session public List<StockGoodsSerialStrip> getListSerialStripKitInStockByOrderId(String orderId, StockTransSerialDTO stockTransSerialDTO, Session session) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); // sql.append(" SELECT sts.goods_id goodsId, sts.goods_state goodsState,"); sql.append( " sts.status status, sts.from_serial fromSerial, sts.to_serial toSerial, sts.quantity quantity "); sql.append(" FROM Stock_Goods_Serial_Strip sts WHERE 1=1 "); sql.append("AND sts.status = 1 ");//trang thai cho kho if (!StringUtils.isStringNullOrEmpty(orderId)) { sql.append("AND sts.order_id = ? "); lstParams.add(orderId); } //Tim kien theo mat hang if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getGoodsId())) { sql.append("AND sts.goods_id = ? "); lstParams.add(stockTransSerialDTO.getGoodsId()); } //Tim theo tinh trang hang hoa if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getGoodsState())) { sql.append("AND sts.goods_state = ? "); lstParams.add(stockTransSerialDTO.getGoodsState()); } //Tim kiem theo from serial if (!StringUtils.isNullOrEmpty(stockTransSerialDTO.getFromSerial())) { sql.append("AND sts.to_serial >= ? "); lstParams.add(stockTransSerialDTO.getFromSerial()); } //Tim kiem theo to serial if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getToSerial())) { sql.append("AND sts.from_serial <= ? "); lstParams.add(stockTransSerialDTO.getToSerial()); } SQLQuery query = session.createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialStrip.class)); query.addScalar("goodsId", new LongType()); query.addScalar("goodsState", new StringType()); query.addScalar("status", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("quantity", new LongType()); for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } // return query.list(); }
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialStripDAO.java
License:Open Source License
/** * Thienng1 addby 28/03/2016 Kiem tra xem Kit da co trong kho hay khong(hang * serial don le)/* w w w .ja va2 s . c om*/ * * @param stockTransSerialDTO * @param orderId * @param session * @return */ //Tim kiem serial theo dai theo session public List<StockGoodsSerial> getListSerialKitInStockByOrderId(String orderId, StockTransSerialDTO stockTransSerialDTO, Session session) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); // sql.append(" SELECT * FROM sts.goods_id goodsId, sts.goods_state goodsState, "); sql.append(" sts.status status, sts.serial serial, sts.quantity quantity"); sql.append("Stock_Goods_Serial sts WHERE 1=1 "); sql.append("AND sts.status = 1 "); //tim kiem theo yeu cau if (!StringUtils.isStringNullOrEmpty(orderId)) { sql.append("AND sts.order_id = ? "); lstParams.add(orderId); } //Tim kien theo mat hang if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getGoodsId())) { sql.append("AND sts.goods_id = ? "); lstParams.add(stockTransSerialDTO.getGoodsId()); } //Tim theo tinh trang hang hoa if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getGoodsState())) { sql.append("AND sts.goods_state = ? "); lstParams.add(stockTransSerialDTO.getGoodsState()); } //Tim kiem theo serial if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getFromSerial())) { sql.append("AND sts.serial = ? "); lstParams.add(stockTransSerialDTO.getFromSerial()); } SQLQuery query = session.createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerial.class)); query.addScalar("goodsId", new LongType()); query.addScalar("goodsState", new StringType()); query.addScalar("status", new StringType()); query.addScalar("serial", new StringType()); query.addScalar("quantity", new LongType()); for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } // return query.list(); }
From source file:com.viettel.logistic.wms.dao.StockTransSerialDAO.java
License:Open Source License
public List<StockTransSerial> getListStockTransSerialBySerial(StockTransSerialDTO stockTransSerialDTO) { List<StockTransSerial> lst = new ArrayList(); StringBuffer sql = new StringBuffer(); List lstParams = new ArrayList(); sql.append("SELECT a.stock_trans_serial_id stockTransSerialId,"); sql.append(" a.stock_trans_id stockTransId,"); sql.append(" a.stock_trans_detail_id stockTransDetailId,"); sql.append(" a.stock_trans_date stockTransDate,"); sql.append(" a.goods_id goodsId,"); sql.append(" a.goods_code goodsCode,"); sql.append(" a.goods_name goodsName,"); sql.append(" a.goods_state goodsState,"); sql.append(" a.goods_unit_type goodsUnitType,"); sql.append(" a.goods_unit_type_name goodsUnitTypeName,"); sql.append(" a.from_serial fromSerial,"); sql.append(" a.to_serial toserial,"); sql.append(" a.amount_order amountOrder,"); sql.append(" a.amount_real amountReal,"); sql.append(" a.bincode bincode,"); sql.append(" a.barcode barcode,"); sql.append(" a.cell_code cellCode,"); sql.append(" a.notes notes,"); sql.append(" a.create_datetime createDatetime,"); sql.append(" a.add_infor addInfor"); sql.append(" FROM stock_trans_serial a"); sql.append(" WHERE 1 = 1 AND"); sql.append(" goods_id = ?"); sql.append(" AND"); sql.append(" from_serial <= ?"); sql.append(" AND"); sql.append(" to_serial >= ?"); sql.append(" AND stock_trans_date >= TO_DATE(?,'dd/mm/yyyy')"); sql.append(" AND stock_trans_date <= TO_DATE(?,'dd/mm/yyyy') + 1"); sql.append(" Order by stock_trans_date ASC"); ///*from ww w . j a va 2 s . c om*/ lstParams.add(stockTransSerialDTO.getGoodsId()); lstParams.add(stockTransSerialDTO.getFromSerial()); lstParams.add(stockTransSerialDTO.getToSerial()); lstParams.add(stockTransSerialDTO.getFromDateSearch()); lstParams.add(stockTransSerialDTO.getToDateSearch()); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockTransSerial.class)); // query.addScalar("stockTransSerialId", new LongType()); query.addScalar("stockTransId", new LongType()); query.addScalar("stockTransDetailId", new LongType()); query.addScalar("stockTransDate", new DateType()); query.addScalar("goodsId", new LongType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("goodsUnitType", new StringType()); query.addScalar("goodsUnitTypeName", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("amountOrder", new LongType()); query.addScalar("amountReal", new LongType()); query.addScalar("bincode", new StringType()); query.addScalar("barcode", new StringType()); query.addScalar("cellCode", new StringType()); query.addScalar("notes", new StringType()); query.addScalar("createDatetime", new DateType()); query.addScalar("addInfor", new StringType()); // for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } lst = query.list(); return lst; }