/**
* RDeviceTransferDao.java 2010/09/06
*/
package com.ycsoft.business.dao.resource.device;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.stereotype.Component;
import com.ycsoft.beans.device.RDeviceTransfer;
import com.ycsoft.commons.constants.SystemConstants;
import com.ycsoft.commons.helper.StringHelper;
import com.ycsoft.daos.abstracts.BaseEntityDao;
import com.ycsoft.daos.core.JDBCException;
import com.ycsoft.daos.core.Pager;
import com.ycsoft.sysmanager.dto.depot.RDeviceTransferDto;
import com.ycsoft.sysmanager.dto.resource.DeviceDto;
/**
* RDeviceTransferDao -> R_DEVICE_TRANSFER table's operator
*/
@Component
public class RDeviceTransferDao extends BaseEntityDao<RDeviceTransfer> {
/**
*
*/
private static final long serialVersionUID = -8299100235500473992L;
/**
* default empty constructor
*/
public RDeviceTransferDao() {
}
/**
* 查询设备调拨信息
* @param deviceId
* @return
* @throws JDBCException
*/
public List<RDeviceTransfer> queryByDeviceId(String deviceId) throws JDBCException {
String sql = "select t.* from r_device_transfer t,r_device_done_deviceid di"
+" where di.device_done_code=t.device_done_code and di.device_id=?";
return this.createQuery(sql, deviceId).list();
}
public void editDeviceTransferHistory(Integer deviceDoneCode, String isHistory) throws JDBCException {
String sql = "update r_device_transfer set is_history=? where device_done_code=?";
this.executeUpdate(sql, isHistory, deviceDoneCode);
}
public void editTransferNo(Integer deviceDoneCode,String transferNo, String remark) throws Exception {
String sql = "update r_device_transfer set transfer_no=?,remark=? where device_done_code=?";
executeUpdate(sql, transferNo,remark, deviceDoneCode);
}
/**
* 查询入库的记录
*
* @param depotId
* @param status
* @return
* @throws JDBCException
*/
public List<RDeviceTransfer> queryInput(String depotId, String status,
boolean isToday) throws JDBCException {
String sql = "SELECT * FROM r_device_transfer t WHERE t.depot_order =? AND t.status=?";
if (isToday)
sql += " and t.create_time BETWEEN to_date(to_char(SYSDATE,'yyyymmdd')||' 00:00:00','yyyymmdd hh24:mi:ss') and to_date(to_char(SYSDATE,'yyyymmdd')||'23:59:59','yyyymmdd hh24:mi:ss')";
return createQuery(sql, depotId, status).list();
}
/**
* 查询出库的记录
*
* @param depotId
* @param status
* @param isToday
* @return
* @throws JDBCException
*/
public List<RDeviceTransfer> queryOutput(String depotId, String status,
boolean isToday) throws JDBCException {
String sql = "SELECT * FROM r_device_transfer t WHERE t.depot_source =? AND t.status=?";
if (isToday)
sql += " and t.create_time BETWEEN to_date(to_char(SYSDATE,'yyyymmdd')||' 00:00:00','yyyymmdd hh24:mi:ss') and to_date(to_char(SYSDATE,'yyyymmdd')||'23:59:59','yyyymmdd hh24:mi:ss')";
return createQuery(sql, depotId, status).list();
}
/**
* 查询仓库的流转记录
*
* @param depotId
* @throws JDBCException
*/
public Pager<RDeviceTransferDto> queryByDepot(String depotId, String query,
String startDate, String endDate, Integer start, Integer limit, String isHistory,String deviceModel)
throws JDBCException {
String cond = "";
if(StringHelper.isNotEmpty(query)){
cond = " and t.transfer_no like '%"+query+"%'";
}
if(StringHelper.isNotEmpty(startDate)){
cond += " and to_char(t.create_time,'yyyy-mm-dd') between '"+startDate+"' and '"+endDate+"'";
}
if(StringHelper.isNotEmpty(isHistory)){
if(!isHistory.equals("All")){
cond += " and t.is_history='"+isHistory+"'";
}
}else{
cond += " and t.is_history='F'";//默认查询正在执行的订单
}
if(StringHelper.isNotEmpty(deviceModel)){
cond += " and d.device_model='"+deviceModel+"' ";
}
String sql = "select * from ("+
" SELECT 'TRANIN' TRAN_TYPE,t.*,d.device_type,d.device_model,d.count" +
" FROM r_device_transfer t,r_device_done_detail d" +
" WHERE t.device_done_code=d.device_done_code(+) and t.depot_order=? "+
cond +
" union all" +
" SELECT 'TRANOUT' TRAN_TYPE,t.*,d.device_type,d.device_model,d.count" +
" FROM r_device_transfer t,r_device_done_detail d" +
" WHERE t.device_done_code=d.device_done_code(+) and t.depot_source=? "+
cond +
" ) order by create_time desc";
return createQuery(RDeviceTransferDto.class, sql, depotId, depotId)
.setStart(start).setLimit(limit).page();
}
public RDeviceTransfer queryByTransferNo(String transferNo) throws JDBCException {
RDeviceTransfer t = new RDeviceTransfer();
t.setTransfer_no(transferNo);
List<RDeviceTransfer> l = findByEntity(t);
return l.size()>0?l.get(0):null;
}
public List<RDeviceTransferDto> queryAllTransferDeviceDtail(
int deviceDoneCode, String deviceType, String deviceModel)
throws JDBCException {
String sql = returnSql(deviceType);
return createQuery(RDeviceTransferDto.class, sql, deviceDoneCode, deviceModel).list();
}
private String returnSql(String deviceType){
String sql = "";
if(deviceType.equals(SystemConstants.DEVICE_TYPE_STB)){
sql = StringHelper.append(
"select t.*,' ' tran_type,b.stb_id device_code,b.device_model,'STB' device_type",
" from r_device_done_deviceid di,r_device_transfer t,r_stb b"
);
}else if(deviceType.equals(SystemConstants.DEVICE_TYPE_CARD)){
sql = StringHelper.append(
"select t.*,' ' tran_type,b.card_id device_code,b.device_model,'CARD' device_type",
" from r_device_done_deviceid di,r_device_transfer t,r_card b"
);
}else if(deviceType.equals(SystemConstants.DEVICE_TYPE_MODEM)){
sql = StringHelper.append(
"select t.*,' ' tran_type,b.modem_mac device_code,b.device_model,'MODEM' device_type",
" from r_device_done_deviceid di,r_device_transfer t,r_modem b"
);
}
sql =StringHelper.append(
sql,
" where di.device_done_code=t.device_done_code",
" and di.device_id=b.device_id",
" and di.device_done_code=? and b.device_model=?",
" order by device_code"
);
return sql;
}
/**
* 转发之前查询信息,以前调拨了各个类型的设备多多少，现在可以转发的各个类型多少.
* @param doneCode
* @return
* @throws Exception
*/
public Map<String, Map> queryTransInfoByDoneCode(Integer doneCode) throws Exception{
Map<String, Map> map = new HashMap<String, Map>();
Map<String, String> old = new HashMap<String, String>();
Map<String, String> now = new HashMap<String, String>();
String oldCount = "select res.device_type,count(1) device_code from "+getTransInfoSql()+
" group by res.device_type ";
List<DeviceDto> list = createQuery(DeviceDto.class, oldCount, doneCode, doneCode, doneCode).list();
for(DeviceDto m:list){
old.put(m.getDevice_type(),m.getDevice_code());
}
String newCount = "select res.device_type,count(1) device_code from "+getTransInfoSql()+
" where res.tran_status = 'IDLE' and res.freezed = 'F' " +
" and res.depot_status = 'IDLE' " + " and res.device_status = 'ACTIVE' " +
" group by res.device_type ";
list.clear();
list = createQuery(DeviceDto.class, newCount, doneCode, doneCode, doneCode).list();
for(DeviceDto m:list){
now.put(m.getDevice_type(),m.getDevice_code());
}
map.put("old", old);
map.put("now", now);
return map;
}
private String getTransInfoSql(){
return " (" +
" select s.stb_id device_code,s.pair_card_id,s.pair_modem_id,t.* from r_device t ,r_stb s " +
" where s.device_id = t.device_id " +
" and t.device_id in ( select rd.device_id from r_device_done_deviceid rd where rd.device_done_code = ? ) " +
" union all " +
" select s.card_id device_code,'' pair_card_id,'' pair_modem_id,t.* from r_device t ,r_card s " +
" where s.device_id = t.device_id " +
" and t.device_id in ( select rd.device_id from r_device_done_deviceid rd where rd.device_done_code = ? ) " +
" union all " +
" select s.modem_mac device_code,'' pair_card_id,'' pair_modem_id,t.* from r_device t ,r_modem s " +
" where s.device_id = t.device_id " +
" and t.device_id in ( select rd.device_id from r_device_done_deviceid rd where rd.device_done_code = ? ) " +
" ) res " ;
}
public List<DeviceDto> queryReTransDevices(Integer device_done_code) throws Exception{
String sql = "select res.* from "+getTransInfoSql()+
" where res.tran_status = 'IDLE' and res.freezed = 'F' " +
" and res.depot_status = 'IDLE' " + " and res.device_status = 'ACTIVE' ";
return createQuery(DeviceDto.class,sql, device_done_code, device_done_code, device_done_code).list();
}
}