List of usage examples for org.hibernate SQLQuery list
List<R> 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 * * @param orderId/* w ww.java2s . c o m*/ * @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 .j a v a 2 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.StockGoodsTotalDAO.java
License:Open Source License
public List<StockGoodsInforDTO> getSumListGoodsAll(StockGoodsInforDTO stockGoodsInforDTO) { List lstParams = new ArrayList(); StringBuilder sql = new StringBuilder(); // /*from ww w . ja v a2s . c o m*/ sql.append("SELECT sg.cust_id custId,"); sql.append(" s.stock_id ownerId,"); sql.append(" sg.owner_type ownerType,"); sql.append(" s.code ownerCode,"); sql.append(" s.name ownerName,"); sql.append(" g.goods_type goodsType,"); sql.append(" g.goods_id goodsId,"); sql.append(" g.code goodsCode,"); sql.append(" g.name goodsName,"); sql.append(" sg.goods_state goodsState,"); sql.append(" SUM(sg.amount) amount,"); sql.append(" SUM(sg.amount_issue) amountIssue,"); sql.append(" g.unit_type unitType "); sql.append(" FROM stock_goods_total sg, goods g, stock s "); sql.append(" WHERE s.stock_id = sg.owner_id"); sql.append(" AND g.goods_id = sg.goods_id"); //ChuDV add 11/12/2015 --Lay cac mat hang con trong kho sql.append(" AND (sg.amount != 0 OR sg.amount_issue != 0) "); // //tim kiem theo dieu kien hang hoa co hieu luc sql.append(" AND g.status = ?"); lstParams.add(Constants.ACTIVE_STATUS); //Tim kiem theo khach hang if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getCustId())) { sql.append(" AND sg.cust_id = ?"); sql.append(" AND g.cust_id = ?"); lstParams.add(stockGoodsInforDTO.getCustId()); lstParams.add(stockGoodsInforDTO.getCustId()); } //Tim kiem theo kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getOwnerId())) { sql.append(" AND sg.owner_id = ?"); lstParams.add(stockGoodsInforDTO.getOwnerId()); } //Tim kiem theo loai kho if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getOwnerType())) { sql.append(" AND sg.owner_type = ?"); lstParams.add(stockGoodsInforDTO.getOwnerType()); } //Tim kiem theo nhom mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getGoodsType())) { sql.append(" AND g.goods_type = ?"); lstParams.add(stockGoodsInforDTO.getGoodsType()); } //Tim kiem theo mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getGoodsId())) { //sql.append(" AND g.goods_id = ?"); //lstParams.add(stockGoodsInforDTO.getGoodsId()); sql.append("AND g.goods_id IN ("); sql.append(stockGoodsInforDTO.getGoodsId()); sql.append(") "); } //Tim kiem theo trang thai hang if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getGoodsState())) { sql.append(" AND sg.goods_state = ?"); lstParams.add(stockGoodsInforDTO.getGoodsState()); } //Tim kiem theo don vi tinh if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getUnitType())) { sql.append(" AND g.unit_type = ?"); lstParams.add(stockGoodsInforDTO.getUnitType()); } // sql.append( " GROUP BY sg.cust_id,s.stock_id,sg.owner_type,s.code,s.name,g.goods_type,g.goods_id,g.code, g.name,sg.goods_state,g.unit_type "); sql.append(" ORDER BY sg.cust_id,s.code,g.goods_type,g.code,g.name,sg.goods_state,g.unit_type"); //Map DTO SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class)); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("ownerCode", new StringType()); query.addScalar("ownerName", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("amount", new StringType()); query.addScalar("amountIssue", new StringType()); query.addScalar("unitType", new StringType()); //Set cac gia tri tham so 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.StockGoodsTotalDAO.java
License:Open Source License
public List<StockGoodsInforDTO> getSumListGoodsByStaff(StockGoodsInforDTO stockGoodsInforDTO) { List lstParams = new ArrayList(); StringBuilder sql = new StringBuilder(); // /*w ww.j a va 2s . c o m*/ sql.append("SELECT sg.cust_id custId,"); sql.append(" s.stock_id ownerId,"); sql.append(" sg.owner_type ownerType,"); sql.append(" s.code ownerCode,"); sql.append(" s.name ownerName,"); sql.append(" g.goods_type goodsType,"); sql.append(" g.goods_id goodsId,"); sql.append(" g.code goodsCode,"); sql.append(" g.name goodsName,"); sql.append(" sg.goods_state goodsState,"); sql.append(" SUM(sg.amount) amount,"); sql.append(" SUM(sg.amount_issue) amountIssue,"); sql.append(" g.unit_type unitType,"); sql.append(" msg.staff_id staffId, msg.staff_code staffCode, msg.staff_name staffName "); sql.append(" FROM stock_goods_total sg, goods g, stock s, map_staff_goods msg "); sql.append(" WHERE s.stock_id = sg.owner_id"); sql.append(" AND g.goods_id = sg.goods_id"); sql.append(" AND sg.goods_id = msg.goods_id(+)"); // //ChuDV add 11/12/2015 --Lay cac mat hang con trong kho sql.append(" AND (sg.amount != 0 OR sg.amount_issue != 0) "); //Tim kiem theo nhan vien if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getStaffId())) { sql.append(" AND msg.staff_id = ?"); lstParams.add(stockGoodsInforDTO.getStaffId()); } //tim kiem theo dieu kien hang hoa co hieu luc sql.append(" AND g.status = ?"); lstParams.add(Constants.ACTIVE_STATUS); //Tim kiem theo khach hang if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getCustId())) { sql.append(" AND sg.cust_id = ?"); sql.append(" AND g.cust_id = ?"); lstParams.add(stockGoodsInforDTO.getCustId()); lstParams.add(stockGoodsInforDTO.getCustId()); } //Tim kiem theo kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getOwnerId())) { sql.append(" AND sg.owner_id = ?"); lstParams.add(stockGoodsInforDTO.getOwnerId()); } //Tim kiem theo loai kho if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getOwnerType())) { sql.append(" AND sg.owner_type = ?"); lstParams.add(stockGoodsInforDTO.getOwnerType()); } //Tim kiem theo nhom mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getGoodsType())) { sql.append(" AND g.goods_type = ?"); lstParams.add(stockGoodsInforDTO.getGoodsType()); } //Tim kiem theo mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getGoodsId())) { //sql.append(" AND g.goods_id = ?"); //lstParams.add(stockGoodsInforDTO.getGoodsId()); sql.append("AND g.goods_id IN ("); sql.append(stockGoodsInforDTO.getGoodsId()); sql.append(") "); } //Tim kiem theo trang thai hang if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getGoodsState())) { sql.append(" AND sg.goods_state = ?"); lstParams.add(stockGoodsInforDTO.getGoodsState()); } //Tim kiem theo don vi tinh if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getUnitType())) { sql.append(" AND g.unit_type = ?"); lstParams.add(stockGoodsInforDTO.getUnitType()); } // sql.append( " GROUP BY sg.cust_id,s.stock_id,sg.owner_type,s.code,s.name,g.goods_type,g.goods_id,g.code, g.name,sg.goods_state,g.unit_type, "); sql.append(" msg.staff_id, msg.staff_code, msg.staff_name "); sql.append(" ORDER BY sg.cust_id,s.code,g.goods_type,g.code,g.name,sg.goods_state,g.unit_type"); //Map DTO SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class)); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("ownerCode", new StringType()); query.addScalar("ownerName", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("amount", new StringType()); query.addScalar("amountIssue", new StringType()); query.addScalar("unitType", new StringType()); query.addScalar("staffId", new StringType()); query.addScalar("staffCode", new StringType()); query.addScalar("staffName", new StringType()); //Set cac gia tri tham so 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.StockTransDAO.java
License:Open Source License
public List<StockTransInforDTO> getListStockTransInfor(StockTransInforDTO stockTransInforDTO) { List<StockTransInforDTO> lstStockGoodsSerialStrip = null; StringBuffer sql = new StringBuffer(); List lstParams = new ArrayList(); ///*w w w. java 2s . c o m*/ sql.append("SELECT st.stock_trans_id stockTransId, "); sql.append(" to_char(st.stock_trans_date,'dd/mm/yyyy') stockTransDate, "); sql.append(" st.stock_trans_status stockTransStatus, "); sql.append(" st.cust_id custId, "); sql.append(" st.owner_id ownerId, "); sql.append(" st.owner_type ownerType, "); sql.append(" std.goods_id goodsId, "); sql.append(" std.goods_code goodsCode, "); sql.append(" std.goods_name goodsName, "); sql.append(" std.goods_is_serial goodsIsSerial, "); sql.append(" std.goods_is_serial_strip goodsIsSerialStrip, "); sql.append(" std.goods_state goodsState, "); sql.append(" '' barcode, "); sql.append(" '' cellCode "); sql.append(" FROM stock_trans st, stock_trans_detail std "); sql.append(" WHERE st.stock_trans_id = std.stock_trans_id "); sql.append(" AND st.stock_trans_date >= TO_DATE (?, 'dd/mm/yyyy') "); sql.append(" AND st.stock_trans_date < TO_DATE (?, 'dd/mm/yyyy') + 1 "); sql.append(" AND std.stock_trans_date >= TO_DATE (?, 'dd/mm/yyyy') "); sql.append(" AND std.stock_trans_date < TO_DATE (?, 'dd/mm/yyyy') + 1 "); sql.append(" AND std.goods_is_serial != '1' "); sql.append(" AND st.stock_trans_id = ? "); sql.append("UNION ALL "); sql.append("SELECT st.stock_trans_id stockTransId, "); sql.append(" to_char(st.stock_trans_date,'dd/mm/yyyy') stockTransDate, "); sql.append(" st.stock_trans_status stockTransStatus, "); sql.append(" st.cust_id custId, "); sql.append(" st.owner_id ownerId, "); sql.append(" st.owner_type ownerType, "); sql.append(" std.goods_id goodsId, "); sql.append(" std.goods_code goodsCode, "); sql.append(" std.goods_name goodsName, "); sql.append(" std.goods_is_serial goodsIsSerial, "); sql.append(" std.goods_is_serial_strip goodsIsSerialStrip, "); sql.append(" std.goods_state goodsState, "); sql.append(" '' barcode, "); sql.append(" '' cellCode "); sql.append(" FROM stock_trans st, stock_trans_detail std, stock_trans_serial sts "); sql.append(" WHERE st.stock_trans_id = sts.stock_trans_id "); sql.append(" AND std.stock_trans_detail_id = sts.stock_trans_detail_id "); sql.append(" AND st.stock_trans_date >= TO_DATE (?, 'dd/mm/yyyy') "); sql.append(" AND st.stock_trans_date < TO_DATE (?, 'dd/mm/yyyy') + 1 "); sql.append(" AND std.stock_trans_date >= TO_DATE (?, 'dd/mm/yyyy') "); sql.append(" AND std.stock_trans_date < TO_DATE (?, 'dd/mm/yyyy') + 1 "); sql.append(" AND sts.stock_trans_date >= TO_DATE (?, 'dd/mm/yyyy') "); sql.append(" AND sts.stock_trans_date < TO_DATE (?, 'dd/mm/yyyy') + 1 "); sql.append(" AND st.stock_trans_id = ? "); // lstParams.add(stockTransInforDTO.getFromDate()); lstParams.add(stockTransInforDTO.getToDate()); lstParams.add(stockTransInforDTO.getFromDate()); lstParams.add(stockTransInforDTO.getToDate()); lstParams.add(stockTransInforDTO.getStockTransId()); // lstParams.add(stockTransInforDTO.getFromDate()); lstParams.add(stockTransInforDTO.getToDate()); lstParams.add(stockTransInforDTO.getFromDate()); lstParams.add(stockTransInforDTO.getToDate()); lstParams.add(stockTransInforDTO.getFromDate()); lstParams.add(stockTransInforDTO.getToDate()); lstParams.add(stockTransInforDTO.getStockTransId()); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockTransInforDTO.class)); // query.addScalar("stockTransId", new StringType()); query.addScalar("stockTransDate", new StringType()); query.addScalar("stockTransStatus", new StringType()); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("goodsIsSerial", new StringType()); query.addScalar("goodsIsSerialStrip", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("barcode", new StringType()); query.addScalar("cellCode", new StringType()); // 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.StockTransDAO.java
License:Open Source License
public List<CardStockInforDTO> getListCardStockInfor(List<CardStockInforDTO> lstCardStockInfor) { List<CardStockInforDTO> lstCardStockInforDTO = new ArrayList(); StringBuffer sql = new StringBuffer(); List lstParams = null;/*from w ww . j a v a2 s. c o m*/ //hang co serial sql.append(" SELECT stock_trans_id stocktransid,"); sql.append(" goods_id goodsid,"); sql.append(" goods_code goodscode,"); sql.append(" goods_name goodsname,"); sql.append(" goods_state goodsstate,"); sql.append(" stock_trans_date stocktransdate,"); sql.append(" stock_trans_type stocktranstype,"); sql.append(" stock_trans_code stocktranscode,"); sql.append(" dept_id deptid,"); sql.append(" trans_user_id transuserid,"); sql.append(" notes notes,"); sql.append(" amount_remain amountremain,"); sql.append(" amount_import amountimport,"); sql.append(" amount_export amountexport "); sql.append(" FROM ( SELECT NULL stock_trans_id,"); sql.append(" sdr.goods_id,"); sql.append(" sdr.goods_code,"); sql.append(" sdr.goods_name,"); sql.append(" sdr.goods_state,"); sql.append(" remain_date stock_trans_date,"); sql.append(" NULL stock_trans_type,"); sql.append(" NULL stock_trans_code,"); sql.append(" NULL dept_id,"); sql.append(" NULL trans_user_id,"); sql.append(" NULL notes,"); sql.append(" NVL (SUM (amount), 0) amount_remain,"); sql.append(" 0 amount_import,"); sql.append(" 0 amount_export "); sql.append(" FROM stock_daily_remain sdr"); sql.append(" WHERE remain_date = TO_DATE (?, 'dd/mm/yyyy') - 1"); sql.append(" AND cust_id = ?"); sql.append(" AND owner_type = ?"); sql.append(" AND owner_id = ?"); sql.append(" AND (sdr.goods_id = ? AND sdr.goods_state = ?)"); //sql.append(" OR (sdr.goods_id = ? AND sdr.goods_state = '2'))"); sql.append(" GROUP BY remain_date,"); sql.append(" sdr.goods_id,"); sql.append(" sdr.goods_code,"); sql.append(" sdr.goods_name,"); sql.append(" sdr.goods_state"); sql.append(" UNION ALL"); sql.append(" SELECT st.stock_trans_id,"); sql.append(" std.goods_id,"); sql.append(" std.goods_code,"); sql.append(" std.goods_name,"); sql.append(" std.goods_state,"); sql.append(" st.stock_trans_date,"); sql.append(" st.stock_trans_type,"); sql.append(" st.stock_trans_code,"); sql.append(" st.dept_id,"); sql.append(" st.trans_user_id,"); sql.append(" st.notes,"); sql.append(" 0 amount_remain,"); sql.append(" (CASE"); sql.append(" WHEN (st.stock_trans_type = '1' OR st.stock_trans_type = '4')"); sql.append(" THEN"); sql.append(" SUM (std.amount_real)"); sql.append(" ELSE"); sql.append(" 0"); sql.append(" END)"); sql.append(" amount_import,"); sql.append(" (CASE"); sql.append(" WHEN (st.stock_trans_type = '2' OR st.stock_trans_type = '5')"); sql.append(" THEN"); sql.append(" SUM (std.amount_real)"); sql.append(" ELSE"); sql.append(" 0"); sql.append(" END)"); sql.append(" amount_export "); sql.append(" FROM stock_trans st,"); sql.append(" stock_trans_detail std"); sql.append(" WHERE st.stock_trans_id = std.stock_trans_id "); sql.append(" AND st.stock_trans_status = '1'"); sql.append(" AND std.amount_real > 0 "); sql.append(" AND st.cust_id = ?"); sql.append(" AND st.owner_type = ?"); sql.append(" AND st.owner_id = ?"); sql.append(" AND st.stock_trans_date >="); sql.append(" TO_DATE (?, 'dd/mm/yyyy')"); sql.append(" AND st.stock_trans_date <"); sql.append(" TO_DATE (?, 'dd/mm/yyyy') + 1"); sql.append(" AND std.stock_trans_date >="); sql.append(" TO_DATE (?, 'dd/mm/yyyy')"); sql.append(" AND std.stock_trans_date <"); sql.append(" TO_DATE (?, 'dd/mm/yyyy') + 1"); sql.append(" AND (std.goods_id = ? AND std.goods_state = ?)"); // sql.append(" OR (std.goods_id = ? AND std.goods_state = '2'))"); sql.append(" GROUP BY st.cust_id,"); sql.append(" st.owner_type,"); sql.append(" st.owner_id,"); sql.append(" st.stock_trans_id,"); sql.append(" std.goods_id,"); sql.append(" std.goods_code,"); sql.append(" std.goods_name,"); sql.append(" std.goods_state,"); sql.append(" st.stock_trans_date,"); sql.append(" st.stock_trans_code,"); sql.append(" st.dept_id,"); sql.append(" st.trans_user_id,"); sql.append(" st.notes,"); sql.append(" st.stock_trans_type)"); sql.append("ORDER BY goods_id,"); sql.append(" goods_state,"); sql.append(" stock_trans_date,"); sql.append(" stock_trans_id"); SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(CardStockInforDTO.class)); for (CardStockInforDTO cardStockInforDTO : lstCardStockInfor) { lstParams = new ArrayList(); // lstParams.add(cardStockInforDTO.getFromDate()); lstParams.add(cardStockInforDTO.getCustId()); lstParams.add(cardStockInforDTO.getOwnerType()); lstParams.add(cardStockInforDTO.getOwnerId()); lstParams.add(cardStockInforDTO.getGoodsId()); lstParams.add(cardStockInforDTO.getGoodsState()); //hang co serial lstParams.add(cardStockInforDTO.getCustId()); lstParams.add(cardStockInforDTO.getOwnerType()); lstParams.add(cardStockInforDTO.getOwnerId()); lstParams.add(cardStockInforDTO.getFromDate()); lstParams.add(cardStockInforDTO.getToDate()); lstParams.add(cardStockInforDTO.getFromDate()); lstParams.add(cardStockInforDTO.getToDate()); lstParams.add(cardStockInforDTO.getGoodsId()); lstParams.add(cardStockInforDTO.getGoodsState()); // // query.addScalar("stockTransId", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("stockTransDate", new StringType()); query.addScalar("stockTransType", new StringType()); query.addScalar("stockTransCode", new StringType()); query.addScalar("deptId", new StringType()); query.addScalar("transUserId", new StringType()); query.addScalar("notes", new StringType()); query.addScalar("amountRemain", new StringType()); query.addScalar("amountImport", new StringType()); query.addScalar("amountExport", new StringType()); // for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } lstCardStockInforDTO.addAll(query.list()); } return lstCardStockInforDTO; }
From source file:com.viettel.logistic.wms.dao.StockTransDAO.java
License:Open Source License
public List<StockTransDTO> getListStockTrans2Inventory(StockTransDTO stockTrans, String fromDate, String toDate, String stockTransType) {/*from ww w. j a va2 s .c om*/ List<StockTransDTO> lstReturns = null; try { StringBuilder sql = new StringBuilder(); sql.append("SELECT a.STOCK_TRANS_ID stockTransId, "); sql.append(" a.stock_trans_code stockTransCode, "); sql.append(" a.DEPT_ID deptId, "); sql.append(" a.STOCK_TRANS_TYPE stockTransType, "); sql.append(" to_char(a.stock_trans_date,'dd/MM/yyyy hh24:mi:ss') stockTransDate, "); sql.append(" a.OWNER_ID ownerId, "); sql.append(" a.owner_type ownerType, "); sql.append(" a.IE_OWNER_ID ieOwnerId, "); sql.append(" a.IE_OWNER_TYPE ieOwnerType, "); sql.append(" a.CUST_CODE custCode, "); sql.append(" a.CUST_ID custId, "); sql.append(" a.REASON_ID reasonId, "); sql.append(" a.STOCK_TRANS_STATUS stockTransStatus, "); sql.append(" a.FROM_STOCK_TRANS_ID fromStockTransId, "); sql.append(" a.NOTES notes, "); sql.append(" a.ORDER_ID_LIST orderIdList, "); sql.append(" to_char(a.ORDER_DATE,'dd/MM/yyyy hh24:mi:ss') orderDate, "); sql.append(" a.ORDER_CODE orderCode, "); sql.append(" to_char(a.CREATE_DATETIME,'dd/MM/yyyy hh24:mi:ss') createDatetime, "); sql.append(" a.CUST_NAME custName, "); sql.append(" a.IS_UPDATE_CELL isUpdateCell, "); sql.append(" a.PARTNER_ID partnerId, "); sql.append(" to_char(a.REAL_STOCK_TRANS_DATE,'dd/MM/yyyy hh24:mi:ss') realStockTransDate, "); sql.append(" a.ATTACH_FILE_NAME attachFileName, "); sql.append(" a.ORDER_ACTION_CODE orderActionCode, "); sql.append(" a.TRANS_USER_ID transUserId, "); sql.append(" a.TRANS_USER_NAME transUserName, "); sql.append(" a.SYN_TRANS_CODE synTransCode, "); sql.append(" a.ADD_INFOR addInfor, "); sql.append(" a.VOFFICE_TRANSACTION_CODE vofficeTransCode, "); sql.append(" a.ORDER_COMMAND_CODE orderCommandCode "); sql.append(" FROM stock_trans a "); sql.append(" WHERE a.CUST_ID = :idx0 "); sql.append(" AND a.OWNER_ID = :idx1 "); sql.append(" AND a.STOCK_TRANS_TYPE IN ( :idx2 ) "); sql.append(" AND a.STOCK_TRANS_STATUS IN ( :idx3 ) "); sql.append(" AND TRUNC(a.CREATE_DATETIME) >= TO_DATE(:idx4, 'dd/MM/yyyy') "); sql.append(" AND TRUNC(a.CREATE_DATETIME) <= TO_DATE(:idx5, 'dd/MM/yyyy') "); sql.append(" AND EXISTS "); sql.append(" (SELECT st.STOCK_TRANS_CODE "); sql.append(" FROM stock_trans st "); sql.append(" WHERE a.STOCK_TRANS_CODE = st.STOCK_TRANS_CODE "); sql.append(" GROUP BY st.STOCK_TRANS_CODE "); sql.append(" HAVING COUNT(st.STOCK_TRANS_CODE) <= 1 "); sql.append(" )"); sql.append(" ORDER By a.stock_trans_date asc, stockTransType asc "); SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockTransDTO.class)); //Truyen cac tham so List lstParams = new ArrayList(); lstParams.add(stockTrans.getCustId()); lstParams.add(stockTrans.getOwnerId()); lstParams.add(DataUtil.parseInputListString(stockTransType)); //Chi lay cac giao dich co trang thai = 1, 2 String stockTransStatus = "1,2"; lstParams.add(DataUtil.parseInputListString(stockTransStatus)); lstParams.add(fromDate); lstParams.add(toDate); //Kieu cua du lieu tra ra query.addScalar("stockTransId", new StringType()); query.addScalar("stockTransCode", new StringType()); query.addScalar("deptId", new StringType()); query.addScalar("stockTransType", new StringType()); query.addScalar("stockTransDate", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("ieOwnerId", new StringType()); query.addScalar("ieOwnerType", new StringType()); query.addScalar("custCode", new StringType()); query.addScalar("custId", new StringType()); query.addScalar("reasonId", new StringType()); query.addScalar("stockTransStatus", new StringType()); query.addScalar("fromStockTransId", new StringType()); query.addScalar("notes", new StringType()); query.addScalar("orderIdList", new StringType()); query.addScalar("orderDate", new StringType()); query.addScalar("orderCode", new StringType()); query.addScalar("createDatetime", new StringType()); query.addScalar("isUpdateCell", new StringType()); query.addScalar("partnerId", new StringType()); query.addScalar("realStockTransDate", new StringType()); query.addScalar("attachFileName", new StringType()); query.addScalar("orderActionCode", new StringType()); query.addScalar("transUserId", new StringType()); query.addScalar("transUserName", new StringType()); query.addScalar("synTransCode", new StringType()); query.addScalar("addInfor", new StringType()); query.addScalar("vofficeTransCode", new StringType()); query.addScalar("orderCommandCode", new StringType()); for (int i = 0; i < lstParams.size(); i++) { if (lstParams.get(i) instanceof String[]) { query.setParameterList("idx" + String.valueOf(i), (Object[]) lstParams.get(i)); } else { query.setParameter("idx" + String.valueOf(i), lstParams.get(i)); } } lstReturns = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstReturns; }
From source file:com.viettel.logistic.wms.dao.StockTransDetailDAO.java
License:Open Source License
public List<StockTransDetailDTO> getListStockTransDetailByOrderId(String orderId) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); sql.append(" SELECT std.stock_trans_detail_id stockTransDetailId,"); sql.append(" st.stock_trans_id stockTransId,"); sql.append(" std.goods_id goodsId,"); sql.append(" std.goods_code goodsCode,"); sql.append(" std.goods_name goodsName,"); sql.append(" std.goods_state goodsState,"); sql.append(" g.goods_type goodsType,"); sql.append(" std.goods_unit_type goodsUnitType,"); sql.append(" std.goods_unit_type_name goodsUnitTypeName,"); sql.append(" std.goods_is_serial goodsIsSerial,"); sql.append(" std.goods_is_serial_strip goodsIsSerialStrip,"); sql.append(" std.amount_order amountOrder,"); sql.append(" std.amount_real amountReal,"); sql.append(" std.create_datetime createDatetime"); sql.append(" FROM stock_trans st,"); sql.append(" goods g,"); sql.append(" stock_trans_detail std"); sql.append(" WHERE st.stock_trans_id = std.stock_trans_id"); sql.append(" AND std.goods_id = g.goods_id"); sql.append(" AND st.stock_trans_status <> 0"); sql.append(" AND st.order_id_list = ? "); lstParams.add(orderId);/* w w w . j a v a2 s. c o m*/ SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockTransDetailDTO.class)); query.addScalar("stockTransDetailId", new StringType()); query.addScalar("stockTransId", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("goodsUnitType", new StringType()); query.addScalar("goodsUnitTypeName", new StringType()); query.addScalar("goodsIsSerial", new StringType()); query.addScalar("goodsIsSerialStrip", new StringType()); query.addScalar("amountOrder", new StringType()); query.addScalar("amountReal", new StringType()); query.addScalar("createDatetime", new StringType()); // 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 w w w. ja va 2s . co m*/ 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; }
From source file:com.viettel.logistic.wms.dao.StockTransSerialDAO.java
License:Open Source License
public List<StockTransSerial> getListStockTransSerialBySerialStrip(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"); //// w w w. j a va 2s . co m lstParams.add(stockTransSerialDTO.getGoodsId()); lstParams.add(stockTransSerialDTO.getToSerial()); lstParams.add(stockTransSerialDTO.getFromSerial()); 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; }