import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;
import com.yundaex.common.dao.impl.BaseDaoImpl;
import com.yundaex.wms.basicdata.constant.RivPrintHistoryLSqlConstant;
import com.yundaex.wms.basicdata.dao.RivPrintHistoryLDao;
import com.yundaex.wms.basicdata.po.RivPrintHistoryL;
import com.yundaex.wms.basicdata.vo.DifferentUserQtyVO;
@Repository("printHistoryLDao")
public class RivPrintHistoryLDaoImpl extends BaseDaoImpl<RivPrintHistoryL> implements RivPrintHistoryLDao {
@Override
public List<DifferentUserQtyVO> queryTopQtyUser(String beginDate, String endDate, Integer orgId) {
MapSqlParameterSource map = new MapSqlParameterSource();
map.addValue("beginDate", beginDate);
map.addValue("endDate", endDate);
map.addValue("orgId", orgId);
List<DifferentUserQtyVO> results = this.jdbcTemplate.query(RivPrintHistoryLSqlConstant.PRINT_HISTORY_TOP_QTY_USER_SQL,map,
new BeanPropertyRowMapper<DifferentUserQtyVO>(DifferentUserQtyVO.class));
return results;
}
}
public class RivPrintHistoryLSqlConstant {
public static String PRINT_HISTORY_TOP_QTY_USER_SQL =
" SELECT "
+ " '打印' AS operateType, "
+ " tab1.PHL_PRINT_USER_ID AS userId, "
+ " ( "
+ " SELECT "
+ " UP_NAME "
+ " FROM "
+ " tab_user_profile "
+ " WHERE "
+ " UP_USER_ID = tab1.PHL_PRINT_USER_ID "
+ " ) AS userName, "
+ " tab1.qty "
+ " FROM "
+ " ( "
+ " SELECT "
+ " PHL_PRINT_USER_ID, "
+ " count(1) AS qty "
+ " FROM "
+ " riv_print_history_l "
+ " WHERE "
+ " CREATE_TIME >= :beginDate "
+ " AND CREATE_TIME < :endDate "
+ " GROUP BY "
+ " PHL_PRINT_USER_ID "
+ " ORDER BY "
+ " qty DESC "
+ " LIMIT 1 "
+ " ) tab1 "
+ " WHERE "
+ " tab1.PHL_PRINT_USER_ID IN ( "
+ " SELECT "
+ " u.usr_id "
+ " FROM "
+ " tab_user u "
+ " INNER JOIN tab_user_role ur ON ur.ur_user_id = u.usr_id "
+ " INNER JOIN tab_role r ON ur.ur_role_id = r.rl_id "
+ " INNER JOIN tab_role_data_authority rda ON rda.rda_role_id = r.rl_id "
+ " WHERE "
+ " rda.rda_dimension_code = 'organization' "
+ " AND rda.rda_entity_id = :orgId "
+ " UNION "
+ " SELECT "
+ " u.usr_id "
+ " FROM "
+ " tab_user u "
+ " INNER JOIN tab_user_profile up ON up.UP_USER_ID = u.usr_id "
+ " INNER JOIN tab_domain dmn ON dmn.dmn_id = up.up_domain "
+ " INNER JOIN tab_domain_role dr ON dr.dr_domain_id = dmn.dmn_id "
+ " INNER JOIN tab_role r ON dr.dr_role_id = r.rl_id "
+ " INNER JOIN tab_role_data_authority rda ON rda.rda_role_id = r.rl_id "
+ " WHERE "
+ " rda.rda_dimension_code = 'organization' "
+ " AND rda.rda_entity_id = :orgId "
+ " UNION "
+ " SELECT "
+ " up.up_id "
+ " FROM "
+ " tab_user_profile up "
+ " WHERE "
+ " up.up_user_prop = 'admin' "
+ " ) "
;
}
public List<Map<String, Object>> queryOrgInfo(List<String> orgParam) {
Map<String, Object> params = new HashMap<String, Object>();
String sql = "select org.org_id, org.org_code from riv_organization org where org.org_code in (:orgCodes)";
params.put("orgCodes", orgParam);
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, params);
return list;
}
import java.util.List;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;
import com.yundaex.common.dao.impl.BaseDaoImpl;
import com.yundaex.wms.outbound.dao.RivMainpickStartPrintTemplateDao;
import com.yundaex.wms.outbound.po.RivMainpickStartPrintTemplate;
@Repository
public class RivMainpickStartPrintTemplateDaoImpl extends BaseDaoImpl<RivMainpickStartPrintTemplate> implements RivMainpickStartPrintTemplateDao {
@Override
public List<Integer> queryExistRecordsByOnhIds(List<Integer> onhIds) {
MapSqlParameterSource map = new MapSqlParameterSource();
map.addValue("onhIds", onhIds);
String sql = "select ONH_ID from riv_mainpick_start_print_template where ONH_ID in(:onhIds)";
List<Integer> results = this.jdbcTemplate.queryForList(sql,map,Integer.class);
return results;
}
}
