List of usage examples for org.hibernate SQLQuery setResultTransformer
@Deprecated Query<R> setResultTransformer(ResultTransformer transformer);
From source file:com.viettel.logistic.wms.dao.SerialInventoryDAO.java
License:Open Source License
public List getListErrorSerialInventory(String inventoryCode) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); sql.append("SELECT "); sql.append(" a.ora_err_mesg$ addInfor,"); sql.append(" a.cust_id custId,"); sql.append(" a.stock_id ownerId,"); sql.append(" a.goods_id goodsId,"); sql.append(" a.goods_state goodsState,"); sql.append(" a.status status,"); sql.append(" a.from_serial fromSerial,"); sql.append(" a.to_serial toSerial,"); sql.append(" a.quantity quantity,"); sql.append(" a.barcode barcode,"); sql.append(" a.note notes,"); sql.append(" a.cell_code cellCode,"); sql.append(" a.goods_code goodsCode,"); sql.append(" a.goods_name goodsName "); sql.append(" FROM err$_serial_inventory a"); sql.append(" "); if (!DataUtil.isStringNullOrEmpty(inventoryCode)) { sql.append(" WHERE a.inventory_code = ? "); lstParams.add(inventoryCode);/* ww w . ja va 2 s. c om*/ } SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class)); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("status", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("quantity", new StringType()); query.addScalar("notes", new StringType()); query.addScalar("addInfor", new StringType()); query.addScalar("cellCode", new StringType()); query.addScalar("barcode", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", 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.StockGoodsDAO.java
License:Open Source License
public List<StockGoodsInforDTO> getSumListStockGoods(StockGoodsInforDTO stockGoodsInforDTO) { List lstParams = new ArrayList(); StringBuilder sql = new StringBuilder(); // //from ww w. j a v a 2s . co 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 "); // if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getStaffId())) { // sql.append(" "); //} 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(+)"); //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()); } //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.StockGoodsDAO.java
License:Open Source License
public List<StockGoodsInforDTO> getSumListStockGoodsExportExcel(StockGoodsSerialDTO stockGoodsSerialDTO, StockGoodsSerialStripDTO stockGoodsSerialStripDTO) { List lstParams = new ArrayList(); // /*from w w w.jav a 2 s . c o m*/ StringBuilder sql = new StringBuilder(); // //Tim kiem serial don le theo trang thai if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialDTO.getStatus())) { sql.append(" AND sgs.status = ?"); lstParams.add(stockGoodsSerialDTO.getStatus()); } //Tim kiem serial dai theo trang thai if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) { sql.append(" AND sgst.status = ?"); lstParams.add(stockGoodsSerialStripDTO.getStatus()); } //Map DTO SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class)); query.addScalar("zoneName", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("amount", 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.StockGoodsDAO.java
License:Open Source License
public List<StockGoodsSerialInforDTO> getStockGoodsForInventory(StockGoodsSerialInforDTO inforDTO) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); sql.append("SELECT NVL (a.goodsid, b.goodsid) goodsId,"); sql.append(" NVL (a.goodsCode, b.goodsCode) goodsCode,"); sql.append(" NVL (a.goodsName, b.goodsName) goodsName,"); //sql.append(" NVL (a.goodsstate, b.goodsstate) goodsState,"); sql.append(" NVL (a.goodsUnitType, b.goodsUnitType) goodsUnitType,"); sql.append(" NVL (a.amount, 0) amount,"); sql.append(" NVL (b.amount, 0) amountInventory,"); sql.append(" NVL (b.amount, 0) - NVL (a.amount, 0) AS amountFalse"); sql.append(" FROM ( SELECT sg.goods_id goodsid,"); //sql.append(" sg.goods_state goodsstate,"); sql.append(" g.code goodsCode,"); sql.append(" g.name goodsName,"); sql.append(" g.unit_Type goodsUnitType,"); sql.append(" SUM (sg.amount) amount"); sql.append(" FROM stock_goods sg, goods g"); sql.append(" WHERE sg.goods_id = g.goods_id "); if (!DataUtil.isStringNullOrEmpty(inforDTO.getCustId())) { sql.append(" AND sg.cust_id = ? "); lstParams.add(inforDTO.getCustId()); }/*from w ww . ja v a2 s .com*/ if (!DataUtil.isStringNullOrEmpty(inforDTO.getOwnerId())) { sql.append(" AND sg.owner_id = ? "); lstParams.add(inforDTO.getOwnerId()); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getGoodsCode())) { sql.append(" AND g.code IN ( "); sql.append(inforDTO.getGoodsCode()); sql.append(" ) "); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getStatus())) { sql.append(" AND sg.status IN ( "); sql.append(inforDTO.getStatus()); sql.append(" ) "); } sql.append(" GROUP BY sg.goods_id, g.code, g.name, g.unit_Type) a"); sql.append(" FULL JOIN"); sql.append(" ( SELECT sg.goods_id goodsid,"); //sql.append(" sg.goods_state goodsstate,"); sql.append(" g.code goodsCode,"); sql.append(" g.name goodsName,"); sql.append(" g.unit_Type goodsUnitType,"); sql.append(" SUM (sg.amount) amount"); sql.append(" FROM stock_goods sg, goods g"); sql.append(" WHERE sg.goods_id = g.goods_id "); if (!DataUtil.isStringNullOrEmpty(inforDTO.getCustIdInventory())) { sql.append(" AND sg.cust_id = ? "); lstParams.add(inforDTO.getCustIdInventory()); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getOwnerIdInventory())) { sql.append(" AND sg.owner_id = ? "); lstParams.add(inforDTO.getOwnerIdInventory()); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getGoodsCode())) { sql.append(" AND g.code IN ( "); sql.append(inforDTO.getGoodsCode()); sql.append(" ) "); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getStatus())) { sql.append(" AND sg.status IN ( "); sql.append(inforDTO.getStatus()); sql.append(" ) "); } sql.append(" GROUP BY sg.goods_id, g.code, g.name, g.unit_Type) b"); sql.append(" ON NVL (a.goodsCode, 'ABC') = NVL (b.goodsCode, 'ABC')"); //sql.append(" AND NVL (a.goodsstate,'1') = NVL (b.goodsstate,'1')"); sql.append(" ORDER BY goodsId"); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class)); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); //query.addScalar("goodsState", new StringType()); query.addScalar("goodsUnitType", new StringType()); query.addScalar("amount", new StringType()); query.addScalar("amountInventory", new StringType()); query.addScalar("amountFalse", 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.StockGoodsDAO.java
License:Open Source License
public List<StockGoodsSerialInforDTO> getStockGoodsSerialForInventory(StockGoodsSerialInforDTO inforDTO) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); sql.append(" SELECT NVL (a.goodsid, b.goodsid) goodsId,"); sql.append(" NVL (a.goodsCode, b.goodsCode) goodsCode,"); sql.append(" NVL (a.goodsName, b.goodsName) goodsName,"); //sql.append(" NVL (a.goodsState, b.goodsState) goodsState,"); sql.append(" NVL (a.goodsUnitType, b.goodsUnitType) goodsUnitType,"); sql.append(" NVL (a.serial, b.serial) fromSerial,"); sql.append(" NVL (a.serial, b.serial) toSerial,"); sql.append(" NVL (a.amount, 0) amount,"); sql.append(" NVL (b.amount, 0) amountInventory,"); sql.append(" NVL (b.amount, 0) - NVL (a.amount, 0) AS amountFalse"); sql.append(" FROM ( SELECT sgs.goods_id goodsid,"); sql.append(" g.code goodsCode,"); sql.append(" g.name goodsName,"); sql.append(" g.unit_Type goodsUnitType,"); //sql.append(" sgs.goods_state goodsstate,"); sql.append(" UPPER(sgs.serial) serial,"); sql.append(" TO_NUMBER(1) amount"); sql.append(" FROM stock_goods_serial sgs, goods g"); sql.append(" WHERE sgs .goods_id = g.goods_id "); if (!DataUtil.isStringNullOrEmpty(inforDTO.getCustId())) { sql.append(" AND sgs.cust_id = ? "); lstParams.add(inforDTO.getCustId()); }// www .jav a 2 s. c om if (!DataUtil.isStringNullOrEmpty(inforDTO.getOwnerId())) { sql.append(" AND sgs.owner_id = ? "); lstParams.add(inforDTO.getOwnerId()); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getGoodsCode())) { sql.append(" AND g.code IN ( "); sql.append(inforDTO.getGoodsCode()); sql.append(" ) "); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getStatus())) { sql.append(" AND sgs.status IN ( "); sql.append(inforDTO.getStatus()); sql.append(" ) "); } sql.append(" ) a "); sql.append(" FULL JOIN"); sql.append(" ( SELECT sgs.goods_id goodsid,"); sql.append(" g.code goodsCode,"); sql.append(" g.name goodsName,"); sql.append(" g.unit_Type goodsUnitType,"); //sql.append(" sgs.goods_state goodsstate,"); sql.append(" UPPER(sgs.serial) serial,"); sql.append(" TO_NUMBER(1) amount"); sql.append(" FROM stock_goods_serial sgs, goods g"); sql.append(" WHERE sgs .goods_id = g.goods_id "); if (!DataUtil.isStringNullOrEmpty(inforDTO.getCustIdInventory())) { sql.append(" AND sgs.cust_id = ? "); lstParams.add(inforDTO.getCustIdInventory()); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getOwnerIdInventory())) { sql.append(" AND sgs.owner_id = ? "); lstParams.add(inforDTO.getOwnerIdInventory()); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getGoodsCode())) { sql.append(" AND g.code IN ( "); sql.append(inforDTO.getGoodsCode()); sql.append(" ) "); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getStatus())) { sql.append(" AND sgs.status IN ( "); sql.append(inforDTO.getStatus()); sql.append(" ) "); } sql.append(" ) b"); sql.append(" ON NVL (a.goodsCode, 'ABC') = NVL (b.goodsCode, 'ABC')"); //sql.append(" AND NVL (a.goodsstate,'1') = NVL (b.goodsstate,'1') "); sql.append(" AND NVL (a.serial, 'ABC') = NVL (b.serial, 'ABC') "); sql.append(" ORDER BY goodsId, fromSerial"); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class)); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); //query.addScalar("goodsState", new StringType()); query.addScalar("goodsUnitType", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("amount", new StringType()); query.addScalar("amountInventory", new StringType()); query.addScalar("amountFalse", 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.StockGoodsDAO.java
License:Open Source License
public List<StockGoodsSerialInforDTO> getStockGoodsSerialForInventoryNotSerial( StockGoodsSerialInforDTO inforDTO) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); sql.append(" SELECT NVL (a.goodsid, b.goodsid) goodsid,"); sql.append(" NVL (a.goodscode, b.goodscode) goodscode,"); sql.append(" NVL (a.goodsname, b.goodsname) goodsname,"); //sql.append(" NVL (a.goodsstate, b.goodsstate) goodsState,"); sql.append(" NVL (a.goodsUnitType, b.goodsUnitType) goodsUnitType,"); sql.append(" NVL (a.amount, 0) amount,"); sql.append(" NVL (b.amount, 0) amountinventory,"); sql.append(" NVL (b.amount, 0) - NVL (a.amount, 0) AS amountfalse"); sql.append(" FROM (SELECT sgs.goods_id goodsid,"); sql.append(" g.code goodscode,"); sql.append(" g.name goodsname,"); sql.append(" g.unit_Type goodsUnitType,"); //sql.append(" sgs.goods_state goodsstate,"); sql.append(" Count(*) amount"); sql.append(" FROM stock_goods_serial sgs, goods g"); sql.append(" WHERE sgs.goods_id = g.goods_id"); if (!DataUtil.isStringNullOrEmpty(inforDTO.getCustId())) { sql.append(" AND sgs.cust_id = ? "); lstParams.add(inforDTO.getCustId()); }//from w ww .ja v a 2 s .com if (!DataUtil.isStringNullOrEmpty(inforDTO.getOwnerId())) { sql.append(" AND sgs.owner_id = ? "); lstParams.add(inforDTO.getOwnerId()); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getGoodsCode())) { sql.append(" AND g.code IN ("); sql.append(inforDTO.getGoodsCode()); sql.append(" ) "); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getStatus())) { sql.append(" AND sgs.status IN ( "); sql.append(inforDTO.getStatus()); sql.append(" ) "); } sql.append(" GROUP BY sgs.goods_id,g.code, g.name, g.unit_type ) a "); sql.append(" FULL JOIN"); sql.append(" (SELECT sgs.goods_id goodsid,"); sql.append(" g.code goodscode,"); sql.append(" g.name goodsname,"); sql.append(" g.unit_Type goodsUnitType,"); //sql.append(" sgs.goods_state goodsstate,"); sql.append(" Count(*) amount"); sql.append(" FROM stock_goods_serial sgs, goods g"); sql.append(" WHERE sgs.goods_id = g.goods_id"); if (!DataUtil.isStringNullOrEmpty(inforDTO.getCustIdInventory())) { sql.append(" AND sgs.cust_id = ? "); lstParams.add(inforDTO.getCustIdInventory()); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getOwnerIdInventory())) { sql.append(" AND sgs.owner_id = ? "); lstParams.add(inforDTO.getOwnerIdInventory()); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getGoodsCode())) { sql.append(" AND g.code IN ("); sql.append(inforDTO.getGoodsCode()); sql.append(" ) "); } if (!DataUtil.isStringNullOrEmpty(inforDTO.getStatus())) { sql.append(" AND sgs.status IN ( "); sql.append(inforDTO.getStatus()); sql.append(" ) "); } sql.append(" GROUP BY sgs.goods_id,g.code, g.name,g.unit_type ) b"); sql.append(" ON NVL (a.goodsCode, 'ABC') = NVL (b.goodsCode, 'ABC')"); //sql.append(" AND NVL (a.goodsstate,'1') = NVL (b.goodsstate,'1') "); sql.append(" ORDER BY goodsId"); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class)); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); //query.addScalar("goodsState", new StringType()); query.addScalar("goodsUnitType", new StringType()); query.addScalar("amount", new StringType()); query.addScalar("amountInventory", new StringType()); query.addScalar("amountFalse", 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.StockGoodsSerialDAO.java
License:Open Source License
public List getListErrorImportRevoke(String stockTransId) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); sql.append("SELECT "); sql.append(" a.ora_err_mesg$ addInfor,"); sql.append(" a.cust_id custId,"); sql.append(" a.owner_id ownerId,"); sql.append(" a.owner_type ownerType,"); sql.append(" a.goods_id goodsId,"); sql.append(" a.goods_state goodsState,"); sql.append(" a.status status,"); sql.append(" a.serial fromSerial,"); sql.append(" a.barcode barcode,"); sql.append(" a.cell_code cellCode,"); sql.append(" g.code goodsCode,"); sql.append(" g.name goodsName "); sql.append(" FROM err$_stock_goods_serial a, goods g"); sql.append(" WHERE a.goods_id = g.goods_id"); if (!DataUtil.isStringNullOrEmpty(stockTransId)) { sql.append(" AND a.import_stock_trans_id = ? "); lstParams.add(stockTransId);/* www . j a v a 2 s .c o m*/ } SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class)); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("status", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("addInfor", new StringType()); query.addScalar("cellCode", new StringType()); query.addScalar("barcode", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", 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.StockGoodsSerialDAO.java
License:Open Source License
/** * thienng1 lay danh sach hang theo theo dieu kien tim kiem *//* w w w .ja va 2 s. c o m*/ public List<StockGoodsSerialDTO> getListStockGoodsSerial(ChangeGoods changeGoods) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); StringBuilder path1 = new StringBuilder(); StringBuilder path2 = new StringBuilder(); StringBuilder path3 = new StringBuilder(); path1.append(" SELECT sts.id id, sts.cust_id custId, sts.owner_id ownerId, sts.goods_id goodsId,"); path1.append( " sts.goods_state goodsState, sts.status status, sts.serial serial, sts.cell_code cellCode "); path1.append(" FROM stock_goods_serial sts, goods g "); path2.append(" WHERE sts.goods_id = g.goods_id "); path3.append(" AND g.code = ? "); lstParams.add(changeGoods.getOldGoodsCode()); if (!DataUtil.isStringNullOrEmpty(changeGoods.getStockCode())) { path1.append(", stock s "); path2.append(" AND sts.owner_id = s.stock_id "); path3.append(" AND s.code = UPPER(?) "); lstParams.add(changeGoods.getStockCode()); } if (!DataUtil.isStringNullOrEmpty(changeGoods.getCellCode())) { path3.append(" AND sts.cell_code = UPPER(?) "); lstParams.add(changeGoods.getCellCode()); } if (!DataUtil.isStringNullOrEmpty(changeGoods.getCustId())) { path3.append(" AND sts.cust_id = ? "); lstParams.add(changeGoods.getCustId()); } if (!DataUtil.isStringNullOrEmpty(changeGoods.getOldFromSerial())) { path3.append(" AND sts.serial = UPPER(?) "); lstParams.add(changeGoods.getOldFromSerial()); } sql.append(path1); sql.append(path2); sql.append(path3); SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialDTO.class)); query.addScalar("id", new StringType()); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("status", new StringType()); query.addScalar("serial", new StringType()); query.addScalar("cellCode", 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.StockGoodsSerialDAO.java
License:Open Source License
public List<StockTransGoodsDTO> getListStockTransGoods2Report(String lstStockTransCodes) { StringBuilder sqlGetStockTransGoods = new StringBuilder(); sqlGetStockTransGoods.append("SELECT st.stock_trans_type stockTransType, "); sqlGetStockTransGoods.append(" st.stock_trans_status stockTransStatus, "); sqlGetStockTransGoods.append(" st.order_code orderCode, "); sqlGetStockTransGoods.append(" st.order_action_code orderActionCode, "); sqlGetStockTransGoods.append(" st.stock_trans_code stockTransCode, "); sqlGetStockTransGoods.append(" st.syn_trans_code synTransCode, "); sqlGetStockTransGoods/* w w w .j a v a2 s . c o m*/ .append(" TO_CHAR (st.real_stock_trans_date, 'dd/MM/yyyy') realStockTransDate, "); sqlGetStockTransGoods.append(" TO_CHAR (st.stock_trans_date, 'dd/MM/yyyy') stockTransDate, "); sqlGetStockTransGoods.append(" st.trans_user_name transUserName, "); sqlGetStockTransGoods.append(" st.notes notes, "); sqlGetStockTransGoods.append(" st.order_id_list orderId,"); sqlGetStockTransGoods.append(" st.owner_id stockId, "); sqlGetStockTransGoods.append(" a.code stockCode, "); sqlGetStockTransGoods.append(" o.receive_name receiveName, "); sqlGetStockTransGoods.append(" std.goods_code goodsCode, "); sqlGetStockTransGoods.append(" std.goods_name goodsName, "); sqlGetStockTransGoods.append(" g.unit_type goodsUnitType, "); sqlGetStockTransGoods.append(" std.goods_unit_type_name goodsUnitTypeName, "); sqlGetStockTransGoods.append(" std.amount_real amountReal "); sqlGetStockTransGoods.append(" FROM stock_trans st "); sqlGetStockTransGoods.append(" JOIN "); sqlGetStockTransGoods.append(" stock_trans_detail std "); sqlGetStockTransGoods.append(" ON std.stock_trans_id = st.stock_trans_id "); sqlGetStockTransGoods.append(" JOIN goods g "); sqlGetStockTransGoods.append(" ON std.goods_id = g.goods_id"); sqlGetStockTransGoods.append(" JOIN "); sqlGetStockTransGoods.append(" stock a "); sqlGetStockTransGoods.append(" ON a.stock_id = st.owner_id "); sqlGetStockTransGoods.append(" LEFT JOIN "); sqlGetStockTransGoods.append(" cms_owner.orders o "); sqlGetStockTransGoods.append(" ON o.order_id = st.order_id_list "); sqlGetStockTransGoods.append(" WHERE st.stock_trans_code in ( :idx0 ) "); sqlGetStockTransGoods.append("GROUP BY st.owner_id, "); sqlGetStockTransGoods.append(" st.order_id_list, "); sqlGetStockTransGoods.append(" o.receive_name, "); sqlGetStockTransGoods.append(" a.code, "); sqlGetStockTransGoods.append(" st.order_code, "); sqlGetStockTransGoods.append(" st.order_action_code, "); sqlGetStockTransGoods.append(" st.stock_trans_code, "); sqlGetStockTransGoods.append(" st.stock_trans_type, "); sqlGetStockTransGoods.append(" st.syn_trans_code, "); sqlGetStockTransGoods.append(" st.real_stock_trans_date, "); sqlGetStockTransGoods.append(" st.stock_trans_date, "); sqlGetStockTransGoods.append(" std.goods_code, "); sqlGetStockTransGoods.append(" std.goods_name, "); sqlGetStockTransGoods.append(" g.unit_type, "); sqlGetStockTransGoods.append(" std.goods_unit_type_name, "); sqlGetStockTransGoods.append(" std.amount_real, "); sqlGetStockTransGoods.append(" st.trans_user_name, "); sqlGetStockTransGoods.append(" st.notes, "); sqlGetStockTransGoods.append(" st.stock_trans_status "); sqlGetStockTransGoods.append("ORDER BY st.stock_trans_date desc, stockTransCode, goodsName "); List lstParams = new ArrayList<>(); if (lstStockTransCodes.contains(ParamUtils.SPLITTER)) { lstParams.add(lstStockTransCodes.split(ParamUtils.SPLITTER)); } else { lstParams.add(lstStockTransCodes); } SQLQuery query = getSession().createSQLQuery(sqlGetStockTransGoods.toString()); query.setResultTransformer(Transformers.aliasToBean(StockTransGoodsDTO.class)); query.addScalar("goodsName", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsUnitType", new StringType()); query.addScalar("goodsUnitTypeName", new StringType()); query.addScalar("amountReal", new DoubleType()); query.addScalar("stockTransType", new StringType()); query.addScalar("orderCode", new StringType()); query.addScalar("orderActionCode", new StringType()); query.addScalar("stockTransCode", new StringType()); query.addScalar("synTransCode", new StringType()); query.addScalar("realStockTransDate", new StringType()); query.addScalar("stockTransDate", new StringType()); query.addScalar("transUserName", new StringType()); query.addScalar("notes", new StringType()); query.addScalar("orderId", new StringType()); query.addScalar("stockId", new StringType()); query.addScalar("stockCode", new StringType()); query.addScalar("receiveName", new StringType()); query.addScalar("stockTransStatus", 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)); } } return query.list(); }
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 ww w .jav a 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; }