package cn.ksource.web.facade.supplier;
|
|
import cn.ksource.beans.SUPPLIER_MODIFY_RECORD;
|
import cn.ksource.core.dao.BaseDao;
|
import cn.ksource.core.dao.SqlParameter;
|
import cn.ksource.core.page.PageInfo;
|
import cn.ksource.core.util.ConvertUtil;
|
import cn.ksource.core.util.DateUtil;
|
import cn.ksource.core.util.StringUtil;
|
import cn.ksource.web.Constants;
|
|
import com.lowagie.text.pdf.PRAcroForm;
|
|
import org.apache.commons.lang.StringEscapeUtils;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
|
import java.sql.Struct;
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* Created by chenlong
|
* Date：2017/5/17
|
* time：8:59
|
*/
|
@Service
|
public class SupplierFacadeImpl implements SupplierFacade{
|
|
@Resource
|
private BaseDao baseDao;
|
|
@Override
|
public PageInfo getSupplierListData(PageInfo pageInfo, Map<String, String> params) {
|
String sql = getSupplierListSupport(params);
|
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
|
}
|
|
@Override
|
public Integer getSupplierListCount(Map<String, String> params) {
|
String sql = " select count(*) from ( " + getSupplierListSupport(params) + " ) t";
|
return baseDao.queryForInteger(sql,params);
|
}
|
|
String getSupplierListSupport(Map<String, String> params) {
|
StringBuilder sql = new StringBuilder(" select a.id,a.state,a.supplier_name,a.contacts,a.contacts_tel,a.province,a.city,a.county, " +
|
" a.create_name,a.gmt_create,b.cate_num from supplier_info a ," +
|
" ( select count(*) cate_num,supplier_id from supplier_spare_part group by supplier_id ) b " +
|
" where a.id = b.supplier_id ");
|
//供应商名称
|
if(StringUtil.isNotBlank(params.get("supplier_name"))){
|
sql.append(" and a.supplier_name like :supplier_name ");
|
params.put("supplier_name", "%" + params.get("supplier_name") + "%");
|
}
|
|
//联系人
|
if(StringUtil.isNotBlank(params.get("contacts"))){
|
sql.append(" and a.contacts like :contacts ");
|
params.put("contacts", "%" + params.get("contacts") + "%");
|
}
|
sql.append(" order by a.state desc,a.gmt_create desc ");
|
return sql.toString();
|
}
|
|
@Override
|
public void editSupplier(Map<String, String> params) {
|
String id = params.get("id");
|
params.put("gmt_create", DateUtil.getCurrentDate14().toString());
|
params.put("gmt_modified", DateUtil.getCurrentDate14().toString());
|
|
if(StringUtil.isBlank(params.get("gmt_register"))){
|
params.put("gmt_register", null);
|
}
|
|
StringBuilder sql = new StringBuilder();
|
if(StringUtil.isBlank(id)){//新增
|
id = StringUtil.getUUID();
|
params.put("id", id);
|
sql.append(" insert into supplier_info (id,supplier_name,gmt_register,legal_person,office_tel,email,contacts,contacts_tel,province,province_id,city,city_id,county,county_id,address,note,create_id,create_name,gmt_create,gmt_modified,state) ");
|
sql.append(" values (:id,:supplier_name,:gmt_register,:legal_person,:office_tel,:email,:contacts,:contacts_tel,:province,:province_id,:city,:city_id,:county,:county_id,:address,:note,:create_id,:create_name,:gmt_create,:gmt_modified,1) ");
|
}else{
|
sql.append(" update supplier_info set supplier_name=:supplier_name,gmt_register=:gmt_register,legal_person=:legal_person,office_tel=:office_tel," +
|
"email=:email,contacts=:contacts,contacts_tel=:contacts_tel,province=:province,province_id=:province_id,city=:city,city_id=:city_id,county=:county,county_id=:county_id," +
|
"address=:address,note=:note,gmt_modified=:gmt_modified where id=:id ");
|
}
|
|
//保存基本信息
|
baseDao.execute(sql.toString(), params);
|
SUPPLIER_MODIFY_RECORD supplier_modify_record=new SUPPLIER_MODIFY_RECORD();
|
supplier_modify_record.setModify_id(params.get("create_id"));
|
supplier_modify_record.setSupplier_id(id);
|
supplier_modify_record.setModify_name(params.get("create_name"));
|
supplier_modify_record.setGmt_modify(DateUtil.getCurrentDate14());
|
supplier_modify_record.setModify_type(Integer.valueOf(params.get("type")));
|
supplier_modify_record.insert();
|
//删除关联供货清单
|
baseDao.execute("delete from supplier_spare_part where supplier_id=:id ", params);
|
//添加供货信息
|
String cateIds = params.get("cate_id");
|
String offerPrices = params.get("offer_price");
|
if(StringUtil.isNotBlank(cateIds)){
|
sql.setLength(0);
|
sql.append(" insert into supplier_spare_part (id,cate_id,supplier_id,offer_price,state ) values (:id,:cate_id,:supplier_id,:offer_price,1 ) ");
|
|
List<SqlParameter> paramList = new ArrayList<SqlParameter>();
|
String[] cateIdAry = cateIds.split(",");
|
String[] offerPriceAry = offerPrices.split(",");
|
for (int i = 0; i < cateIdAry.length; i++) {
|
SqlParameter p = new SqlParameter();
|
p.addValue("id", StringUtil.getUUID())
|
.addValue("cate_id",cateIdAry[i])
|
.addValue("inventory_order_id",id)
|
.addValue("supplier_id",params.get("id"))
|
.addValue("offer_price",offerPriceAry[i]);
|
paramList.add(p);
|
}
|
baseDao.executeBatch(sql.toString(),paramList);
|
}
|
}
|
|
@Override
|
public List<Map> getCateList(Map<String, Object> params) {
|
StringBuilder sql = new StringBuilder("select a.id,a.lv1_name,a.lv2_name,a.lv3_name,a.code,(select code from cmdb_ci_category where id=a.lv1_id) code1, (select code from cmdb_ci_category where id=a.lv2_id) code2 from cmdb_ci_category a where a.cate_type=2 and a.lv=3 and a.state=1 ");
|
if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("cate_name")))) {
|
sql.append(" and a.name like :cate_name ");
|
params.put("cate_name", "%" + params.get("cate_name") + "%");
|
}
|
if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("code")))) {
|
sql.append(" and a.code like :code ");
|
params.put("code", "%" + params.get("code") + "%");
|
}
|
if(StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("cateIds")))){
|
sql.append(" and a.id not in (:cateIds) ");
|
params.put("cateIds", Arrays.asList(ConvertUtil.obj2StrBlank(params.get("cateIds")).split(",")));
|
}
|
|
return baseDao.queryForList(sql.toString(), params);
|
}
|
|
@Override
|
public Map getSupplierInfo(String id) {
|
SqlParameter param = new SqlParameter("id", id);
|
StringBuilder sql = new StringBuilder(" select * from supplier_info where id=:id ");
|
Map info = baseDao.queryForMap(sql.toString(), param);
|
|
sql.setLength(0);
|
sql.append(" select a.*,b.lv1_name,b.lv2_name,b.lv3_name,b.code,(select code from cmdb_ci_category where id=b.lv1_id) code1, (select code from cmdb_ci_category where id=b.lv2_id) code2 from supplier_spare_part a,cmdb_ci_category b where a.cate_id=b.id and a.supplier_id=:id order by a.id ");
|
List<Map> list = baseDao.queryForList(sql.toString(), param);
|
info.put("list", list);
|
|
return info;
|
}
|
|
@Override
|
public String delSupplier(String id,String state) {
|
|
String sqlString="select count(*) from SPARE_PART where SUPPLIER_ID=:supplier_id and state in(1,2,3,4,5)";
|
Map paramMap = new HashMap();
|
paramMap.put("supplier_id", id);
|
Integer aInteger=baseDao.queryForInteger(sqlString, paramMap);
|
if(aInteger!=0){
|
return "0";
|
}else if(state.equals("0")){
|
baseDao.execute(" update supplier_info set state=0 where id=:id ", new SqlParameter("id", id));
|
return "1";
|
}else if(state.equals("1")){
|
baseDao.execute(" update supplier_info set state=1 where id=:id ", new SqlParameter("id", id));
|
return "1";
|
}
|
return "1";
|
}
|
|
@Override
|
public PageInfo getSparePartListData(PageInfo pageInfo, Map<String, String> params) {
|
String sql = getSparePartListSupport(params);
|
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
|
}
|
|
@Override
|
public Integer getSparePartListCount(Map<String, String> params) {
|
String sql = " select count(*) from ( " + getSparePartListSupport(params) + " ) t";
|
return baseDao.queryForInteger(sql,params);
|
}
|
|
String getSparePartListSupport(Map<String, String> params) {
|
StringBuilder sql = new StringBuilder(" select a.id,a.supplier_name,b.offer_price,c.lv1_name,c.lv2_name,c.lv3_name,c.code,(select code from cmdb_ci_category where id=c.lv1_id) code1,(select code from cmdb_ci_category where id=c.lv2_id) code2," +
|
" a.gmt_modified,a.gmt_create from supplier_info a ," +
|
" supplier_spare_part b,cmdb_ci_category c " +
|
" where a.id = b.supplier_id and b.cate_id = c.id and c.cate_type=2 and a.state=1 and b.state=1 and c.state=1 ");
|
//供应商名称
|
if(StringUtil.isNotBlank(params.get("supplier_name"))){
|
sql.append(" and a.supplier_name like :supplier_name ");
|
params.put("supplier_name", "%" + params.get("supplier_name") + "%");
|
}
|
|
//备件分类
|
if(StringUtil.isNotBlank(params.get("cate_id"))){
|
sql.append(" and c.id like :cate_id ");
|
params.put("cate_id", "%" + params.get("cate_id") + "%");
|
}
|
|
//分类编码
|
if(StringUtil.isNotBlank(params.get("code"))){
|
sql.append(" and c.code like :code ");
|
params.put("code", "%" + params.get("code") + "%");
|
}
|
|
//开始日期
|
if(StringUtil.isNotBlank(params.get("startDate"))){
|
sql.append(" and a.gmt_create >= :startDate ");
|
params.put("startDate", params.get("startDate") + "000000");
|
}
|
//结束日期
|
if(StringUtil.isNotBlank(params.get("endDate"))){
|
sql.append(" and a.gmt_create <= :endDate ");
|
params.put("endDate", params.get("endDate") + "666666");
|
}
|
|
sql.append(" order by a.gmt_create desc ");
|
|
return sql.toString();
|
}
|
|
@Override
|
public PageInfo getSparePartSearchListData(PageInfo pageInfo, Map<String, String> params) {
|
String sql = getSparePartSearchListSupport(params);
|
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
|
}
|
|
@Override
|
public Integer getSparePartSearchListCount(Map<String, String> params) {
|
String sql = " select count(*) from ( " + getSparePartSearchListSupport(params) + " ) t";
|
return baseDao.queryForInteger(sql,params);
|
}
|
|
String getSparePartSearchListSupport(Map<String, String> params) {
|
StringBuilder sql = new StringBuilder(" select if(warranty_end>date_format(now(),'%Y%m%d'),1,2) over_flag,a.warranty_end,a.id,b.lv1_name,b.lv2_name,b.lv3_name,a.name,a.asset_no,a.entity_no,a.model_no,a.state, " +
|
" a.location,a.gmt_create from spare_part a ,cmdb_ci_category b " +
|
" where a.cate_id = b.id and b.cate_type=2 and b.state=1 ");
|
|
//备件名称
|
if(StringUtil.isNotBlank(params.get("name"))){
|
sql.append(" and a.name like :name ");
|
params.put("name", "%" + params.get("name") + "%");
|
}
|
|
//备件状态
|
if(StringUtil.isNotBlank(params.get("state"))){
|
sql.append(" and a.state = :state ");
|
}else{
|
sql.append(" and a.state in (1,2,3) ");
|
}
|
// //备件可用状态
|
// if(StringUtil.isNotBlank(params.get("ky_state"))){
|
// sql.append(" and a.ky_state = :ky_state ");
|
// }
|
|
//资产编码
|
if(StringUtil.isNotBlank(params.get("asset_no"))){
|
sql.append(" and a.asset_no like :asset_no ");
|
params.put("asset_no", "%" + params.get("asset_no") + "%");
|
}
|
|
//备件分类
|
if(StringUtil.isNotBlank(params.get("cate_id"))){
|
sql.append(" and b.id like :cate_id ");
|
params.put("cate_id", "%" + params.get("cate_id") + "%");
|
}
|
|
//实物编码
|
if(StringUtil.isNotBlank(params.get("entity_no"))){
|
sql.append(" and a.entity_no like :entity_no ");
|
params.put("entity_no", "%" + params.get("entity_no") + "%");
|
}
|
|
//开始日期
|
if(StringUtil.isNotBlank(params.get("startDate"))){
|
sql.append(" and a.gmt_create >= :startDate ");
|
params.put("startDate", params.get("startDate") + "000000");
|
}
|
//结束日期
|
if(StringUtil.isNotBlank(params.get("endDate"))){
|
sql.append(" and a.gmt_create <= :endDate ");
|
params.put("endDate", params.get("endDate") + "666666");
|
}
|
|
Long curDate = DateUtil.getCurrentDate8();
|
params.put("curDate", curDate.toString());
|
|
//是否过保
|
if(StringUtil.isNotBlank(params.get("overFlag"))){
|
if(params.get("overFlag").equals("1")){//未过保
|
sql.append(" and a.warranty_end >= :curDate ");
|
}else{//已过保
|
sql.append(" and a.warranty_end <= :curDate ");
|
}
|
}
|
|
//过保查询结束时间
|
if(StringUtil.isNotBlank(params.get("overEndDate"))){
|
sql.append(" and a.warranty_end <= :overEndDate ");
|
}
|
|
//过保状态
|
if(StringUtil.isNotBlank(params.get("overState"))){
|
String overState = params.get("overState");
|
if(overState.equals("oneMonth")){//一月内过保
|
sql.append(" and a.warranty_end <= DATE_FORMAT(date_add(:curDate,interval 1 month),'%Y%m%d') ");
|
}else if(overState.equals("twoMonth")){//两月内过保
|
sql.append(" and a.warranty_end <= DATE_FORMAT(date_add(:curDate,interval 2 month),'%Y%m%d') ");
|
}else if(overState.equals("threeMonth")){//三月内过保
|
sql.append(" and a.warranty_end <= DATE_FORMAT(date_add(:curDate,interval 3 month),'%Y%m%d') ");
|
}
|
}
|
sql.append(" order by a.gmt_create desc ");
|
return sql.toString();
|
}
|
|
@Override
|
public PageInfo getFinanceListData(PageInfo pageInfo, Map<String, String> params) {
|
String sql = getFinanceSupport(params);
|
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
|
}
|
|
@Override
|
public Integer getFinanceListCount(Map<String, String> params) {
|
String sql = " select count(*) from ( " + getFinanceSupport(params) + " ) t";
|
return baseDao.queryForInteger(sql,params);
|
}
|
|
String getFinanceSupport(Map<String, String> params) {
|
|
|
StringBuilder sql = new StringBuilder(" select d.id,d.customer_name,d.short_name,d.ywjl_name,IFNULL(f.use_num,0) use_num,IFNULL(f.sum_money,0) sum_money " +
|
" from sc_partner_customer_info d left join ( " +
|
" select c.cus_id,count(*) use_num,ifnull(sum(a.sale_price),0) sum_money" +
|
" from spare_part a,spare_part_apply_entity b,cmdb_ci_base c " +
|
" where a.id = b.entity_id and b.ci_id = c.id and b.state=2 ");
|
|
//开始时间
|
if(StringUtil.isNotBlank(params.get("startDate"))){
|
sql.append(" and b.gmt_usage >= :startDate ");
|
params.put("startDate", params.get("startDate")+"000000");
|
}
|
|
//结束时间
|
if(StringUtil.isNotBlank(params.get("endDate"))){
|
sql.append(" and b.gmt_usage <= :endDate ");
|
params.put("endDate", params.get("endDate")+"666666");
|
}
|
|
sql.append(" group by c.cus_id ) f on f.cus_id = d.id where 1=1");
|
|
//项目名称
|
if(StringUtil.isNotBlank(params.get("customer_id"))){
|
sql.append(" and d.id=:customer_id ");
|
params.put("customer_id", params.get("customer_id"));
|
}
|
|
//运维经理
|
if(StringUtil.isNotBlank(params.get("ywjl_name"))){
|
sql.append(" and d.ywjl_name like :ywjl_name ");
|
params.put("ywjl_name", "%" + params.get("ywjl_name") + "%");
|
}
|
|
sql.append(" and d.customer_state=1 order by sum_money desc ");
|
|
return sql.toString();
|
}
|
|
|
@Override
|
public PageInfo getReplaceInfoListData(PageInfo pageInfo, Map<String, String> params) {
|
String sql = getReplaceInfoListSupport(params);
|
return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
|
}
|
|
@Override
|
public Integer getReplaceInfoListCount(Map<String, String> params) {
|
String sql = " select count(*) from ( " + getReplaceInfoListSupport(params) + " ) t";
|
return baseDao.queryForInteger(sql,params);
|
}
|
|
String getReplaceInfoListSupport(Map<String, String> params) {
|
|
StringBuilder sql = new StringBuilder(" select d.id order_id,c.id ci_id,a.id spare_id,c.ciname,e.lv1_name,e.lv2_name,e.lv3_name,a.entity_no,a.name,a.asset_no,a.model_no,d.gmt_create " +
|
" from spare_part a,spare_part_apply_entity b,cmdb_ci_base c ,spare_part_apply d,cmdb_ci_category e " +
|
" where a.id = b.entity_id and b.ci_id = c.id and b.order_id=d.id and b.state=2 and a.cate_id =e.id ");
|
|
|
//项目
|
if(StringUtil.isNotBlank(params.get("cus_id"))){
|
sql.append(" and c.cus_id = :cus_id ");
|
}
|
|
//备件实物编码
|
if(StringUtil.isNotBlank(params.get("entity_no"))){
|
sql.append(" and a.entity_no like :entity_no ");
|
params.put("entity_no", "%" + params.get("entity_no") + "%");
|
}
|
|
//备件名称
|
if(StringUtil.isNotBlank(params.get("name"))){
|
sql.append(" and a.name like :name ");
|
params.put("name", "%" + params.get("name") + "%");
|
}
|
|
//配置名称
|
if(StringUtil.isNotBlank(params.get("ci_name"))){
|
sql.append(" and c.ciname like :ci_name ");
|
params.put("ci_name", "%" + params.get("ci_name") + "%");
|
}
|
|
sql.append(" order by a.gmt_create desc ");
|
return sql.toString();
|
}
|
|
@Override
|
public List<Map> getCateGorySpareListData(Map<String, String> params) {
|
String sql = this.getCateGorySpareList(params);
|
return baseDao.queryForList(sql,params);
|
}
|
|
@Override
|
public Integer getCateGorySpareListCount(Map<String, String> params) {
|
String sql = "select count(*) from ( "+this.getCateGorySpareList(params)+ " ) t";
|
return baseDao.queryForInteger(sql, params);
|
}
|
|
// 获得备件分类列表查询条件
|
private String getCateGorySpareList(Map<String,String> params){
|
|
StringBuilder sql = new StringBuilder("SELECT b.id,b.LV1_NAME,b.LV2_NAME,b.LV3_NAME,b.CODE,(select code from cmdb_ci_category where id=b.LV1_id) code1,(select code from cmdb_ci_category where id=b.LV2_id) code2 ,count(*) SL FROM "
|
+" cmdb_ci_category b left join spare_part a on b.ID = a.CATE_ID"
|
+" WHERE a.state = 1 and b.state = 1 and b.cate_type = 2 ");
|
String cate_id = params.get("cate_id"); //备件分类
|
String code = params.get("code"); //分类编号
|
String cateIds = params.get("cateIds"); //分类编号
|
if (StringUtil.notEmpty(cateIds)) {
|
String[] cateId_s = cateIds.split(",");
|
if (cateId_s.length == 1) {
|
sql.append(" AND b.id != :cmdb_ci_category_id");
|
params.put("cmdb_ci_category_id", cateId_s[0]);
|
} else {
|
sql.append(" AND b.id not IN (");
|
for (int i = 0; i < cateId_s.length; i++) {
|
sql.append(" :pri" + i);
|
sql.append(",");
|
params.put("pri" + i, cateId_s[i]);
|
}
|
sql.deleteCharAt(sql.lastIndexOf(","));
|
sql.append(")");
|
}
|
}
|
|
if (StringUtil.isNotBlank(cate_id)) {
|
sql.append(" and b.ID =:bjfl ");
|
params.put("bjfl",cate_id );
|
}
|
|
if (StringUtil.isNotBlank(code)) {
|
sql.append(" and b.code like :flbm ");
|
params.put("flbm","%"+code+"%" );
|
}
|
sql.append(" group by b.id");
|
return sql.toString();
|
|
}
|
|
@Override
|
public Map getReplaceDetail(Map<String,String> params) {
|
//查询申请单信息
|
StringBuilder sql = new StringBuilder(" select a.order_code,a.create_name,a.gmt_create,a.order_name,a.apply_reason," +
|
" n.current_dealer_name deal_name,dealtime deal_time " +
|
" from spare_part_apply a,workflow_base b,workflow_node n " +
|
" where a.flow_id=b.id and b.current_node_id = n.id and a.id=:order_id ");
|
Map info = baseDao.queryForMap(sql.toString(), params);
|
//查询备件信息
|
sql.setLength(0);
|
sql.append(" select a.entity_no,a.name spare_name,a.asset_no,a.model_no,b.lv1_name,b.lv2_name,b.lv3_name,a.sale_price,a.location " +
|
" from spare_part a,cmdb_ci_category b " +
|
" where a.cate_id =b.id and a.id=:spare_id ");
|
info.putAll(baseDao.queryForMap(sql.toString(), params));
|
//查询配置信息
|
sql.setLength(0);
|
sql.append(" select ciname,searchcode,lv1_name cate1_name,lv2_name cate2_name,lv3_name cate3_name,position from cmdb_ci_base where id=:ci_id ");
|
info.putAll(baseDao.queryForMap(sql.toString(), params));
|
|
return info;
|
}
|
|
@Override
|
public List<Map> getSparePartOverageSearchListData(
|
Map<String, String> params) {
|
StringBuilder sql = new StringBuilder(" select if(warranty_end>date_format(now(),'%Y%m%d'),1,2) over_flag,a.warranty_end,a.id,b.lv1_name,b.lv2_name,b.lv3_name,a.name,a.asset_no,a.entity_no,a.model_no,a.state, " +
|
" a.location,a.gmt_create from spare_part a ,cmdb_ci_category b " +
|
" where a.cate_id = b.id and b.cate_type=2 and b.state=1 and a.id=:id");
|
params.put("id", params.get("id"));
|
return baseDao.queryForList(sql.toString(), params);
|
}
|
|
/**
|
* 供应商处理记录
|
* @param id
|
* @return
|
*/
|
@Override
|
public List querySupplierModifyRecord(String id) {
|
String sqlString="select * from SUPPLIER_MODIFY_RECORD where supplier_id=:id ORDER BY GMT_MODIFY DESC";
|
Map map=new HashMap();
|
map.put("id", id);
|
List list=baseDao.queryForList(sqlString,map);
|
return list;
|
}
|
|
@Override
|
public void delSparePart(Map param) {
|
String sql="update spare_part set ky_state=:flag where id=:id";
|
baseDao.execute(sql, param);
|
}
|
}
|
