package com.uas.eis.service.Impl; import com.uas.eis.beans.result.Result; import com.uas.eis.convertor.*; import com.uas.eis.dao.BaseDao; import com.uas.eis.dao.SqlRowList; import com.uas.eis.sdk.dto.*; import com.uas.eis.sdk.resp.*; import com.uas.eis.service.RequestSTKService; import com.uas.eis.service.STKService; import com.uas.eis.utils.BaseUtil; import org.apache.commons.collections.CollectionUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletRequest; import java.math.BigDecimal; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.stream.Collectors; /** * @author * @date 2024-10-12 */ @Service public class STKServiceImpl implements STKService { private final Logger logger = LoggerFactory.getLogger(this.getClass()); @Autowired private BaseDao baseDao; @Autowired private RequestSTKService requestSTKService; @Override public Result getJobList(ProductPageDTO productPageDTO) { int pageNum = 1; int pageSize = 20; if (productPageDTO !=null){ pageNum = Integer.valueOf(productPageDTO.getPageNum()); pageSize = Integer.valueOf(productPageDTO.getPageSize()); }else{ return Result.error("参数错误!"); } int start = ((pageNum - 1) * pageSize + 1); int end = pageNum * pageSize; List jobDTOS = baseDao.query("select * from (select rownum rn,AA.* from (select jo_id,jo_code,jo_name,jo_headmancode,jo_headmanname,jo_orgid,jo_orgcode,jo_orgname,jo_subof,jo_pcode,jo_parentname,jo_level,jo_status,jo_statuscode,isagent from job order by jo_id desc) AA ) where rn>="+start+" and rn<="+end , JobDTO.class); if (jobDTOS.size() == 0 ){ return Result.error("岗位资料无数据!"); } int count = baseDao.getCount("select count(1) from job"); List JobResps = JobConvertor.toJobRespListByJobDTOS(jobDTOS); Map map = new HashMap<>(); map.put("pageNum", pageNum); map.put("pageSize", pageSize); map.put("size", count); map.put("list", JobResps); return Result.success(map); } @Override public Result getDepartmentList(ProductPageDTO productPageDTO) { int pageNum = 1; int pageSize = 20; if (productPageDTO !=null){ pageNum = Integer.valueOf(productPageDTO.getPageNum()); pageSize = Integer.valueOf(productPageDTO.getPageSize()); }else{ return Result.error("参数错误!"); } int start = ((pageNum - 1) * pageSize + 1); int end = pageNum * pageSize; List departmentDTOS = baseDao.query("select * from (select rownum rn,AA.* from (select dp_id,dp_code,dp_name,dp_isleaf,dp_status,dp_headmancode,dp_headmanname,dp_pcode,dp_parentdpname,dp_level,dp_subof,dp_statuscode from department order by dp_id desc) AA ) where rn>="+start+" and rn<="+end , DepartmentDTO.class); if (departmentDTOS.size() == 0 ){ return Result.error("部门资料无数据!"); } int count = baseDao.getCount("select count(1) from department"); List departmentResps = DepartmentConvertor.toDepartmentRespListByDepartmentDTOS(departmentDTOS); Map map = new HashMap<>(); map.put("pageNum", pageNum); map.put("pageSize", pageSize); map.put("size", count); map.put("list", departmentResps); return Result.success(map); } @Override public Result getEmployeeList(ProductPageDTO productPageDTO) { int pageNum = 1; int pageSize = 20; if (productPageDTO !=null){ pageNum = Integer.valueOf(productPageDTO.getPageNum()); pageSize = Integer.valueOf(productPageDTO.getPageSize()); }else{ return Result.error("参数错误!"); } int start = ((pageNum - 1) * pageSize + 1); int end = pageNum * pageSize; List employeeDTOS = baseDao.query("select * from (select rownum rn,AA.* from (select em_code,em_name,em_sex,em_depart,em_defaultorname,em_position,em_cscode,em_cop,em_class,em_status,em_type,em_mobile,em_id,em_indate,em_recorder,em_departmentcode,em_defaultorcode,em_qywx,em_password from employee where em_class<>'离职' and em_statuscode='AUDITED' order by em_id desc) AA ) where rn>="+start+" and rn<="+end , EmployeeDTO.class); if (employeeDTOS.size() == 0 ){ return Result.error("员工资料无数据!"); } int count = baseDao.getCount("select count(1) from employee where em_class<>'离职' and em_statuscode='AUDITED'"); List employeeResps = EmployeeConvertor.toEmployeeRespListByEmployeeDTOS(employeeDTOS); Map map = new HashMap<>(); map.put("pageNum", pageNum); map.put("pageSize", pageSize); map.put("size", count); map.put("list", employeeResps); return Result.success(map); } @Override public Result getBusinessChanceList(ProductPageDTO productPageDTO) { int pageNum = 1; int pageSize = 20; if (productPageDTO !=null){ pageNum = Integer.valueOf(productPageDTO.getPageNum()); pageSize = Integer.valueOf(productPageDTO.getPageSize()); }else{ return Result.error("参数错误!"); } int start = ((pageNum - 1) * pageSize + 1); int end = pageNum * pageSize; List businessChanceDTOS = baseDao.query("select * from (select rownum rn,AA.* from (select bc_code,bc_description,bc_lockstatus,bc_khlx_user,bc_nichehouse,bc_currentprocess,bc_status,bc_agency,bt_tel,bc_desc8,bc_doman,bc_lastdate,bc_custcode,bc_custname,bc_position,bc_recorder,bc_recorddate,bc_id,bc_recorderid,bc_statuscode,bc_remark,bc_contact,bc_desc4,bc_tel,bc_desc5,bc_attach,bc_address,bc_desc6,bc_domancode,bc_lockdate,bc_decisionmanphone,bc_designname,bc_domandepart,bc_from from BusinessChance where bc_statuscode in ('AUDITED','FINISH') order by bc_id desc) AA ) where rn>="+start+" and rn<="+end , BusinessChanceDTO.class); if (businessChanceDTOS.size() == 0 ){ return Result.error("商机资料无数据!"); } int count = baseDao.getCount("select count(1) from BusinessChance where bc_statuscode in ('AUDITED','FINISH')"); List businessChanceResps = BusinessChanceConvertor.toBusinessChanceRespListByBusinessChanceDTOS(businessChanceDTOS); Map map = new HashMap<>(); map.put("pageNum", pageNum); map.put("pageSize", pageSize); map.put("size", count); map.put("list", businessChanceResps); return Result.success(map); } @Override public Result getCustomerList(ProductPageDTO productPageDTO) { int pageNum = 1; int pageSize = 20; if (productPageDTO !=null){ pageNum = Integer.valueOf(productPageDTO.getPageNum()); pageSize = Integer.valueOf(productPageDTO.getPageSize()); }else{ return Result.error("参数错误!"); } int start = ((pageNum - 1) * pageSize + 1); int end = pageNum * pageSize; List customerDTOS = baseDao.query("select * from (select rownum rn,AA.* from (select cu_code,cu_name,cu_agency,cu_shortname,cu_auditstatus,cu_status,cu_kind,cu_sellercode,cu_sellername,cu_currency,cu_taxrate,cu_payments,cu_arname,cu_shcustname,cu_add1,cu_recordman,cu_recorddate,cu_cop,cu_remark,cu_id,cu_enablecredit,cu_credit,cu_uu,cu_contact2,cu_tel2 from customer where cu_auditstatuscode in ('AUDITED','DISABLE') order by cu_id desc) AA ) where rn>="+start+" and rn<="+end , CustomerDTO.class); if (customerDTOS.size() == 0 ){ return Result.error("员工资料无数据!"); } int count = baseDao.getCount("select count(1) from customer where cu_auditstatuscode in ('AUDITED','DISABLE')"); List customerResps = CustomerConvertor.toCustomerRespListByCustomerDTOS(customerDTOS); Map map = new HashMap<>(); map.put("pageNum", pageNum); map.put("pageSize", pageSize); map.put("size", count); map.put("list", customerResps); return Result.success(map); } @Override public Result getSaleList(ProductPageDTO productPageDTO) { int pageNum = 1; int pageSize = 20; if (productPageDTO !=null){ pageNum = Integer.valueOf(productPageDTO.getPageNum()); pageSize = Integer.valueOf(productPageDTO.getPageSize()); }else{ return Result.error("参数错误!"); } int start = ((pageNum - 1) * pageSize + 1); int end = pageNum * pageSize; List saleDTOS = baseDao.query("select * from (select rownum rn,AA.* from (select sa_code,sa_date,sa_transport,sa_kind,sa_status,sa_custcode,sa_custname,sa_currency,sa_rate,sa_salemethod,sa_apcustcode,sa_apcustname,sa_need4,sa_need10,sa_paymentscode,sa_payments,sa_shcustcode,sa_shcustname,sa_toplace,sa_contact,sa_contacttel,sa_pocode,sa_cop,sa_departmentcode,sa_departmentname,sa_need11,sa_need5,sa_quyu_user,sa_sellercode,sa_seller,sa_need2,sa_need3,sa_need1,sa_chfs,sa_prepayamount,sa_sourcecode,sa_sourcetype,sa_turnstatus,sa_sendstatus,sa_printstatus,sa_count,sa_recorder,sa_recorddate,sa_updateman,sa_updatedate,sa_auditman,sa_auditdate,sa_remark,sa_getprice,sa_custid,sa_statuscode,sa_id,sa_sourceid,sa_recorderid,sa_paymentsid,sa_sync,sa_bcid,sa_minus,sa_b2cpucode,sa_total,sa_ts_user,sa_1_user,sa_sjbh_user" + ",(select to_char(max(pi_date),'yyyy-mm-dd') pidate_max from prodinout left join prodiodetail on pi_id=pd_piid where pd_ordercode=sa_code and pi_statuscode='POSTED') pidate_max" + ",sa_yfbl_user||'/'||sa_tsh_user sa_tsh_user, sa_fhbl_user||'/'||sa_zqit_user sa_zqit_user, sa_ysbil_user||'/'||sa_yasntt_user sa_yasntt_user, sa_zbbli_user||'/'||sa_zbtas_user sa_zbtas_user" + " from Sale where sa_statuscode in ('AUDITED','FINISH') order by sa_id desc) AA ) where rn>="+start+" and rn<="+end , SaleDTO.class); if (saleDTOS.size() == 0 ){ return Result.error("销售订单无数据!"); } // 4. 批量查询订单明细 List saleDetailDTOS = baseDao.query("select sd_id,sd_said,sd_detno,sd_prodcode,pr_detail,pr_spec,pr_unit,sd_qty,sd_purcprice,sd_price,sd_discount,sd_total,sd_taxrate,sd_remark,sd_costprice,sd_taxtotal,sd_costingprice,sd_isspecial,sd_bonded,sd_delivery,sd_pmcdate,sd_pmcremark,sd_atpdelivery,sd_custprodcode,sd_custproddetail,sd_prodcustcode,pr_vendprodcode,sd_noforecast,sd_forecastcode,sd_forecastdetno,sd_yqty,sd_sendqty,sd_qty-nvl(sd_sendqty,0) sd_leaveassign,sd_leadtime,sd_bgprice,sd_description,sd_remark2,sd_bomid,sd_originaldetno,sd_originalqty,sd_status,sd_barcode,sd_tomakeqty,sd_packagedate,sd_vendorrate,sd_bodycost,sd_minus,sd_bomprice,pr_location from SaleDetail left join Product on sd_prodcode=pr_code where sd_said in (select sa_id from (select rownum rn,AA.* from (select * from Sale where sa_statuscode in ('AUDITED','FINISH') order by sa_id desc) AA) where rn>="+start+" and rn<="+end+") order by sd_said desc,sd_detno" , SaleDetailDTO.class); // 5. 按订单ID分组订单明细 Map> saleDetailMap = saleDetailDTOS.stream().collect(Collectors.groupingBy(SaleDetailDTO::getSd_said)); // 6. 组装数据 List saleResps = assembleOrderData(saleDTOS, saleDetailMap); int count = baseDao.getCount("select count(1) from Sale where sa_statuscode in ('AUDITED','FINISH')"); Map map = new HashMap<>(); map.put("pageNum", pageNum); map.put("pageSize", pageSize); map.put("size", count); map.put("list", saleResps); return Result.success(map); } @Override public Result getRecBalanceNoticeList(ProductPageDTO productPageDTO) { int pageNum = 1; int pageSize = 20; if (productPageDTO !=null){ pageNum = Integer.valueOf(productPageDTO.getPageNum()); pageSize = Integer.valueOf(productPageDTO.getPageSize()); }else{ return Result.error("参数错误!"); } int start = ((pageNum - 1) * pageSize + 1); int end = pageNum * pageSize; List recBalanceNoticeDTOS = baseDao.query("select * from (select rownum rn,AA.* from (select rb_code,rb_kind,rb_date,rb_status,rb_custid,rb_custcode,rb_custname,rb_currency,rb_rate,rb_amount,rb_cmcurrency,rb_cmamount,rb_getdate,rb_counterfee,rb_monthly,rb_yamount,rb_sellercode,rb_sellername,rb_departmentcode,rb_departmentname,rb_zat_user,rb_attention,rb_remark,rb_recorder,rb_recorddate,rb_auditer,rb_auditdate,rb_attach,rb_actamount,rb_cateid,rb_catecode,rb_catename,rb_statuscode,rb_id from RecBalanceNotice where rb_kind='预收款' and rb_statuscode='AUDITED' order by rb_id desc) AA ) where rn>="+start+" and rn<="+end , RecBalanceNoticeDTO.class); if (recBalanceNoticeDTOS.size() == 0 ){ return Result.error("回款通知(预收)无数据!"); } // 4. 批量查询回款通知明细 List recBalanceNoticeDetailDTOS = baseDao.query("select rbd_id,rbd_rbid,rbd_detno,rbd_sacode,rbd_date,rbd_currency,rbd_amount,rbd_sellercode,rbd_sellername,rbd_payments,rbd_ordertype,rbd_remark,sa_total,sa_prepayamount,sa_quyu_user from RECBALANCENOTICEDetail LEFT JOIN Sale on rbd_sacode=sa_code where rbd_rbid in (select rb_id from (select rownum rn,AA.* from (select * from RecBalanceNotice where rb_kind='预收款' and rb_statuscode in ('AUDITED') order by rb_id desc) AA) where rn>="+start+" and rn<="+end+") order by rbd_rbid desc,rbd_detno" , RecBalanceNoticeDetailDTO.class); // 5. 按回款通知ID分组订单明细 Map> recBalanceNoticeDetailMap = recBalanceNoticeDetailDTOS.stream().collect(Collectors.groupingBy(RecBalanceNoticeDetailDTO::getRbd_rbid)); // 6. 组装数据 List recBalanceNoticeResps = assembleRecBalanceNoticeData(recBalanceNoticeDTOS, recBalanceNoticeDetailMap); int count = baseDao.getCount("select count(1) from RecBalanceNotice where rb_kind='预收款' and rb_statuscode in ('AUDITED') "); Map map = new HashMap<>(); map.put("pageNum", pageNum); map.put("pageSize", pageSize); map.put("size", count); map.put("list", recBalanceNoticeResps); return Result.success(map); } @Override public Result createVisitRecord(VisitRecordDTO customerVisitDTO) { if(customerVisitDTO == null) { return Result.error("参数错误!"); } if(customerVisitDTO.getType() == null || "".equals(customerVisitDTO.getType())) { return Result.error("拜访类型不能为空!"); } String caller; if ("客户拜访".equals(customerVisitDTO.getType())){ caller="OfficeClerk"; }else if ("原厂拜访".equals(customerVisitDTO.getType())){ caller="VisitRecord!Vender"; } String code = baseDao.sGetMaxNumber("VisitRecord", 2); int id = baseDao.getSeqId("VisitRecord_SEQ"); List Sql = new ArrayList<>(); Sql.add("insert into VisitRecord(vr_id,vr_code,vr_recorder,vr_recordercode,vr_recorddate,vr_status,vr_statuscode," + "vr_visittime,vr_visitend,vr_nexttime,vr_visitplace,vr_khsj_user," + "vr_nichecode,vr_nichename,vr_cuuu,vr_cuname,vr_cucontact,vr_zhwu_user,vr_tel,vr_jtfs_user,vr_title,vr_class,vr_nichestep,vr_detail,vr_attach)" + "values("+id+",'"+code+"','"+customerVisitDTO.getRecorder()+"','"+customerVisitDTO.getRecordercode()+"',to_date('"+customerVisitDTO.getRecorddate()+"','yyyy-mm-dd hh24:mi:ss'),'已审核','AUDITED'," + "to_date('"+customerVisitDTO.getVisittime()+"','yyyy-mm-dd hh24:mi:ss'),to_date('"+customerVisitDTO.getVisitend()+"','yyyy-mm-dd hh24:mi:ss'),"+customerVisitDTO.getNexttime() == null || "".equals(customerVisitDTO.getNexttime()) ? "''" : "to_date('"+customerVisitDTO.getNexttime()+"','yyyy-mm-dd hh24:mi:ss')," + "'"+customerVisitDTO.getVisitplace()+"','"+customerVisitDTO.getKhsj_user()+"'," + "'"+customerVisitDTO.getNichecode()+"','"+customerVisitDTO.getNichename()+"','"+customerVisitDTO.getCuuu()+"','"+customerVisitDTO.getCuname()+"','"+customerVisitDTO.getCucontact()+"'," + "'"+customerVisitDTO.getZhwu_user()+"','"+customerVisitDTO.getTel()+"','"+customerVisitDTO.getJtfs_user()+"','"+customerVisitDTO.getTitle()+"','"+customerVisitDTO.getType()+"'," + "'"+customerVisitDTO.getNichestep()+"','"+customerVisitDTO.getDetail()+"','"+customerVisitDTO.getAttach()+"')"); List visitEmp = customerVisitDTO.getVisitEmp(); for (VisitEmpDTO emp : visitEmp){ Sql.add("insert into Players(pl_id,pl_vrid,pl_detno,pl_code,pl_name,pl_sex,pl_position,pl_tel,pl_email,pl_remark)" + "values(Players_seq.nextval,"+id+","+emp.getDetno()+",'"+emp.getCode()+"','"+emp.getName()+"','"+emp.getSex()+"','"+emp.getPosition()+"','"+emp.getTel()+"','"+emp.getEmail()+"','"+emp.getRemark()+"')"); } baseDao.execute(Sql); Map map = new HashMap<>(); map.put("id", id); map.put("code", code); return Result.success(map); } @Override public Result createWorkDaily(WorkDailyDTO workDailyDTO) { if(workDailyDTO == null) { return Result.error("参数错误!"); } String code = baseDao.sGetMaxNumber("WorkDaily", 2); int id = baseDao.getSeqId("VisitRecord_SEQ"); List Sql = new ArrayList<>(); Sql.add("insert into WorkDaily(wd_code,wd_entrydate,wd_date,wd_empcode,wd_emp,wd_id,wd_depart,wd_hrorg,wd_joname," + "wd_status,wd_statuscode,wd_comment,wd_plan,wd_experience,wd_context,wd_empid,wd_attachs,wd_tip)" + " values('"+code+"',to_date('"+workDailyDTO.getEntrydate()+"','yyyy-mm-dd'),to_date('"+workDailyDTO.getDate()+"','yyyy-mm-dd')," + "'"+workDailyDTO.getEmpcode()+"','"+workDailyDTO.getEmp()+"',"+id+",'"+workDailyDTO.getDepart()+"',''," + "'"+workDailyDTO.getJoname()+"','已审核','AUDITED','"+workDailyDTO.getComment()+"','"+workDailyDTO.getPlan()+"','"+workDailyDTO.getExperience()+"'," + "'"+workDailyDTO.getContext()+"',"+workDailyDTO.getEmpid()+",'"+workDailyDTO.getAttachs()+"','"+workDailyDTO.getTip()+"')"); baseDao.execute(Sql); Map map = new HashMap<>(); map.put("id", id); map.put("code", code); return Result.success(map); } @Override public Result businessChanceDistribute(List businessChanceDistributeDTOS) { if (businessChanceDistributeDTOS.isEmpty()){ return Result.error("参数错误!"); } List Sql = new ArrayList<>(); for (BusinessChanceDistributeDTO b : businessChanceDistributeDTOS){ if(b.getDoman() == null || "".equals(b.getDoman())){ Sql.add("update BusinessChance set bc_nichehouse='贝腾CRM公海', bc_domancode='', bc_doman='', bc_lastdate=to_date('"+b.getLastdate()+"','yyyy-mm-dd') where bc_id="+b.getId()+" and bc_code ='"+b.getCode()+"'"); }else{ Sql.add("update BusinessChance set bc_domancode='"+b.getDomancode()+"', bc_doman='"+b.getDoman()+"', bc_lastdate=to_date('"+b.getLastdate()+"','yyyy-mm-dd') where bc_id="+b.getId()+" and bc_code ='"+b.getCode()+"'"); } Sql.add("insert into messagelog(ML_ID,ML_DATE,ML_MAN,ML_CONTENT,ML_RESULT,ML_SEARCH,CODE) " + "select messageLog_SEQ.nextval,sysdate,'管理员','商机分配','商机分配','BusinessChance|bc_id='||bc_id,bc_code from BusinessChance where bc_id in(" + b.getId() + ")"); } baseDao.execute(Sql); return Result.success(); } /** * 组装订单主从表数据 */ private List assembleOrderData(List orders, Map> itemsMap) { return orders.stream() .map(order -> { SaleResp saleListResp = convertToVO(order); // 设置订单明细 List items = itemsMap.get(order.getSa_id()); if (!CollectionUtils.isEmpty(items)) { List itemVOS = items.stream() .map(this::convertToItemVO) .collect(Collectors.toList()); saleListResp.setSaleItems(itemVOS); } else { saleListResp.setSaleItems(new ArrayList<>()); } return saleListResp; }) .collect(Collectors.toList()); } /** * 转换订单主表为VO */ private SaleResp convertToVO(SaleDTO order) { return SaleConvertor.toSaleResp(order); } /** * 转换订单明细为VO */ private SaleDetailResp convertToItemVO(SaleDetailDTO item) { return SaleDetailConvertor.toSaleDetailResp(item); } private List assembleRecBalanceNoticeData(List orders, Map> itemsMap) { return orders.stream() .map(order -> { RecBalanceNoticeResp recBalanceNoticeListResp = convertToVOByRecBalanceNotice(order); // 设置订单明细 List items = itemsMap.get(order.getRb_id()); if (!CollectionUtils.isEmpty(items)) { List itemVOS = items.stream() .map(this::convertToItemVOByRecBalanceNotice) .collect(Collectors.toList()); recBalanceNoticeListResp.setItems(itemVOS); } else { recBalanceNoticeListResp.setItems(new ArrayList<>()); } return recBalanceNoticeListResp; }) .collect(Collectors.toList()); } /** * 转换订单主表为VO */ private RecBalanceNoticeResp convertToVOByRecBalanceNotice(RecBalanceNoticeDTO order) { return RecBalanceNoticeConvertor.toRecBalanceNoticeResp(order); } /** * 转换订单明细为VO */ private RecBalanceNoticeDetailResp convertToItemVOByRecBalanceNotice(RecBalanceNoticeDetailDTO item) { return RecBalanceNoticeDetailConvertor.toRecBalanceNoticeDetailResp(item); } @Override public Result getProduct(HttpServletRequest request, ProductReq req) { String code = req.getCode(); // int pageNum = Integer.valueOf(req.getPageNum()); // int pageSize = Integer.valueOf(req.getPageSize()); // int start = ((pageNum - 1) * pageSize + 1); // int end = pageNum * pageSize; List productDTOS = baseDao.query("select * from ( SELECT pr_status,pr_serial,pr_code,pr_detail,pr_spec,pr_speccs,pr_remark_warehouse,pr_remark_sale,pr_unit,pr_kind,pr_manutype,pr_dhzc,pr_supplytype,pr_material,pr_level,pr_acceptmethod,pr_whcode,pr_ifbarcodecheck,pr_planner,pr_buyername,pr_cop,pr_recordman,pr_docdate,pr_sourcecode,pr_checkstatus,pr_id,pr_uuid,pr_sendstatus,pr_stockcatecode,pr_smtpoint,pr_safetystock,pr_zxbzs,pr_zxdhl,pr_purcmergedays,pr_stockcatename,pr_tracekind,pr_brand,pr_leadtime " + " from Product WHERE pr_code in ('"+code+"') order by pr_id desc)" , ProductDTO.class); if (productDTOS.size() == 0 ){ return Result.error("物料资料不存在"); } ProdcutResp prodcutResp = ProductConvertor.toProdcutRespByProductDTOs(productDTOS); return Result.success(prodcutResp); } @Override public Result getBom(BomReq bomReq) { String bomId = bomReq.getBomId(); List bomDTOList = baseDao.query("select * from ( SELECT bo_id,bo_version,bo_status,bo_level,bo_mothercode,pr_detail,pr_spec,pr_unit,bo_remark,bo_wcname,bo_ispast,bo_cop,bo_recorder,bo_date,bo_auditman,bo_auditdate,bo_isextend,bo_refbomid,bo_refcode,bo_refname,bo_refspec,bo_style,bo_flowstyle " + " from Bom LEFT JOIN Product ON bo_mothercode=pr_code WHERE bo_id in ("+bomId+") order by bo_id desc)" , BomDTO.class); if (bomDTOList.size() == 0 ){ return Result.error("BOM资料不存在"); } BomResp bomResp = BomConvertor.toBomRespListByBomDTOs(bomDTOList); BomReadResp bomReadResp = new BomReadResp(); bomReadResp.setMain(bomResp); List bomDetailDTOList = baseDao.query("select * from ( SELECT bd_id,bd_bomid,bd_detno,bd_soncode,bd_baseqty,bd_location,bd_remark,bd_ecncode,bd_ifrep,bd_repcode,bd_usestatus " + " from BomDetail WHERE bd_bomid in ("+bomId+") order by bd_detno)" , BomDetailDTO.class); if (bomDTOList.size() > 0 ){ List bomDetailResps = BomDetailConvertor.toBomDetailRespListByBomDetailDTOs(bomDetailDTOList); bomReadResp.setDetail(bomDetailResps); } return Result.success(bomReadResp); } @Override public Result getBomList(ProductPageDTO productPageDTO) { int pageNum = 1; int pageSize = 20; if (productPageDTO !=null){ pageNum = Integer.valueOf(productPageDTO.getPageNum()); pageSize = Integer.valueOf(productPageDTO.getPageSize()); }else{ return Result.error("参数错误!"); } int start = ((pageNum - 1) * pageSize + 1); int end = pageNum * pageSize; List bomDTOList = baseDao.query("select * from (select rownum rn,AA.* from ( SELECT bo_id,bo_version,bo_status,bo_level,bo_mothercode,pr_detail,pr_spec,pr_unit,bo_remark,bo_wcname,bo_ispast,bo_cop,bo_recorder,bo_date,bo_auditman,bo_auditdate,bo_isextend,bo_refbomid,bo_refcode,bo_refname,bo_refspec,bo_style,bo_flowstyle " + " from Bom LEFT JOIN Product ON bo_mothercode=pr_code order by bo_id desc) AA ) where rn>="+start+" and rn<="+end , BomDTO.class); if (bomDTOList.size() == 0 ){ return Result.error("BOM资料无数据!"); } int count = baseDao.getCount("select count(1) from Bom"); Map map = new HashMap<>(); map.put("pageNum", pageNum); map.put("pageSize", pageSize); map.put("size", count); map.put("list", bomDTOList); return Result.success(map); } @Override public Result getProject(ProductPageDTO productPageDTO) { int pageNum = 1; int pageSize = 20; if (productPageDTO !=null){ pageNum = Integer.valueOf(productPageDTO.getPageNum()); pageSize = Integer.valueOf(productPageDTO.getPageSize()); }else{ return Result.error("参数错误!"); } int start = ((pageNum - 1) * pageSize + 1); int end = pageNum * pageSize; List projectDTOList = baseDao.query("select * from (select rownum rn,AA.* from ( " + " select prj_id,prj_code,prj_name,prj_sourcecode,prj_customername,prj_sourcetype,prj_customercode,prj_status,prj_producttype,prj_person,prj_assignto,prj_description,prj_start,prj_end,prj_recordate,prj_auditstatus,prj_remark,pt_code from Project left join ProjectMainTask on prj_code=pt_prjcode order by prj_id desc " + " ) AA ) where rn>="+start+" and rn<="+end , ProjectDTO.class); if (projectDTOList.size() == 0 ){ return Result.error("项目申请单无数据!"); } int count = baseDao.getCount("select count(1) from Project"); List projectResps = ProjectConvertor.toProjectRespListByProjectDTOs(projectDTOList); Map map = new HashMap<>(); map.put("pageNum", pageNum); map.put("pageSize", pageSize); map.put("size", count); map.put("list", projectResps); return Result.success(map); } @Override public Result getBomCost(BomReq bomReq) { String bomId = bomReq.getBomId(); Object bo_mothercode = null; if (bomId == null || Integer.valueOf(bomId) == 0){ return Result.error("BOMID不存在!"); }else{ bo_mothercode = baseDao.getFieldDataByCondition("bom", "bo_mothercode", "bo_id=" + bomId); } if(bo_mothercode==null){ return Result.error("BOM母件编号不存在!"); } int count = baseDao.getCount("select count(1) from bomcostMsgLog where trunc(costcountdate_)=trunc(sysdate) and bomid_=" + bomId); if (count==0) { baseDao.procedure("SP_COSTCOUNT", new Object[] { bomId, bo_mothercode, "最新采购单价" }); bomPrint(Integer.valueOf(bomId),"BOM!BOMCostDetail!Print"); } int pageNum = 1; int pageSize = 20; if (bomReq !=null){ pageNum = Integer.valueOf(bomReq.getPageNum()); pageSize = Integer.valueOf(bomReq.getPageSize()); }else{ return Result.error("参数错误!"); } int start = ((pageNum - 1) * pageSize + 1); int end = pageNum * pageSize; List bomCostDTOList = baseDao.query("select * from (select rownum rn,AA.* from ( " + " select BS_LEVEL,BS_SONCODE,Product.PR_DETAIL,Product.PR_SPEC,bd_location,Product.PR_SMTPOINT,BS_BASEQTY,BS_CURRENCY, " + " BS_RATE,case when BS_CURRENCY='RMB' then round(BS_PURCPRICE,8) else round(BS_PURCPRICE*CR_RATE,8) end BS_PURCPRICE,round(BS_M,8) BS_M,Product.pr_brand," + " (select pd_price from (select pd_price,pd_prodcode prodcode,row_number() over(partition by pd_prodcode order by pu_auditdate desc) row_number from purchasedetail left join purchase on pd_puid=pu_id where pu_statuscode in ('AUDITED','FINISH')) t where row_number=1 and prodcode=Product.pr_code) price " + " from BOMStruct " + " LEFT JOIN Currencys ON BOMStruct.BS_CURRENCY=Currencys.CR_NAME" + " LEFT JOIN Vendor ON BOMStruct.BS_VENDNAME=Vendor.VE_NAME" + " LEFT JOIN Product ON BOMStruct.BS_SONCODE=Product.PR_CODE" + " LEFT JOIN BOM ON BOMStruct.BS_TOPBOMID=BOM.BO_ID" + " LEFT JOIN Product Product_1 ON BOM.BO_MOTHERCODE=Product_1.PR_CODE" + " LEFT JOIN BOMDETAIL ON bd_bomid=bo_id AND Product.PR_CODE=BOMDETAIL.bd_soncode" + " where bo_id="+bomId+ " ORDER BY BOMStruct.BS_IDCODE " + " ) AA ) ", BomCostDTO.class); if (bomCostDTOList.size() == 0 ){ return Result.error("BOM成本无数据!"); } Map map = new HashMap<>(); map.put("pageNum", pageNum); map.put("pageSize", pageSize); map.put("size", bomCostDTOList.size()); map.put("list", bomCostDTOList); return Result.success(map); } @Override public Result insertOrUpdateAppraise(AppraiseReq appraiseReq) { String code = appraiseReq.getCode(); String recorder = appraiseReq.getRecorder(); if(code==null){ return Result.error("估价申请编号参数错误!"); } List sqls = new ArrayList(); List items = appraiseReq.getItems(); int count = baseDao.getCount("select count(1) from CUSTOMTABLE where ct_caller='STW-0005' and ct_code='" + code + "'"); if (count>0) { count = baseDao.getCount("select count(1) from CUSTOMTABLE where ct_statuscode='ENTERING' and ct_caller='STW-0005' and ct_code='" + code + "'"); //修改 if (count>0) { Object ctid = baseDao.getFieldDataByCondition("CUSTOMTABLE", "ct_id", "ct_statuscode='ENTERING' and ct_caller='STW-0005' and ct_code='" + code + "'"); Object cd_detno_max = baseDao.getFieldDataByCondition("CUSTOMTABLEDETAIL", "max(cd_detno)", "cd_ctid=" + ctid); int detno = 1; for(AppraiseDetailDTO detailDTO : items){ if(detailDTO.getCd_id()>0){ String DetailSql ="update CUSTOMTABLEDETAIL set cd_varchar50_2='"+detailDTO.getCd_number_2()+"',cd_varchar50_3='"+detailDTO.getCd_varchar50_3()+"',cd_varchar50_1='"+detailDTO.getCd_varchar50_1()+ "',cd_number_1="+detailDTO.getCd_number_1()+",cd_number_2="+detailDTO.getCd_number_2()+",cd_number_3="+detailDTO.getCd_number_3()+",cd_number_4="+detailDTO.getCd_number_4()+ " where cd_id="+detailDTO.getCd_id()+" and exists (select 1 from CUSTOMTABLE where ct_id=cd_ctid and ct_statuscode='ENTERING' and ct_caller='STW-0005' and ct_code='"+code+"')"; sqls.add(DetailSql); }else{ //新增 if (cd_detno_max !=null && detno == 1){ detno = Integer.valueOf(cd_detno_max.toString()) + 1 ; } String DetailSql = "insert into CUSTOMTABLEDETAIL(cd_id,cd_ctid,cd_detno,cd_varchar50_2,cd_varchar50_3,cd_varchar50_1,cd_number_1,cd_number_2,cd_number_3,cd_number_4)" + " values(CUSTOMTABLEDETAIL_seq.nextval,"+ctid+","+detno+",'"+detailDTO.getCd_varchar50_2()+"','"+detailDTO.getCd_varchar50_3()+"','"+detailDTO.getCd_varchar50_1()+ "',"+detailDTO.getCd_number_1()+","+detailDTO.getCd_number_2()+","+detailDTO.getCd_number_3()+","+detailDTO.getCd_number_4()+")"; sqls.add(DetailSql); detno++; } } } }else{ //新增 int ct_id = baseDao.getSeqId("CUSTOMTABLE_SEQ"); int detno = 1; String FormSql= "insert into CUSTOMTABLE(CT_ID,CT_CALLER,CT_STATUSCODE,CT_STATUS,CT_CODE,ct_recorder,ct_sourcekind,ct_recorddate)" + " values("+ct_id+",'STW-0005','ENTERING','在录入','"+code+"','"+recorder+"','估价申请',sysdate)"; sqls.add(FormSql); for(AppraiseDetailDTO detailDTO : items){ String DetailSql = "insert into CUSTOMTABLEDETAIL(cd_id,cd_ctid,cd_detno,cd_varchar50_2,cd_varchar50_3,cd_varchar50_1,cd_number_1,cd_number_2,cd_number_3,cd_number_4)" + " values(CUSTOMTABLEDETAIL_seq.nextval,"+ct_id+","+detno+",'"+detailDTO.getCd_varchar50_2()+"','"+detailDTO.getCd_varchar50_3()+"','"+detailDTO.getCd_varchar50_1()+ "',"+detailDTO.getCd_number_1()+","+detailDTO.getCd_number_2()+","+detailDTO.getCd_number_3()+","+detailDTO.getCd_number_4()+")"; sqls.add(DetailSql); detno++; } } baseDao.execute(sqls); return Result.success(); } @Override public Result getBomAndSon(BomReq bomReq) { String bomId = bomReq.getBomId(); Object bo_mothercode = null; if (bomId == null || Integer.valueOf(bomId) == 0){ return Result.error("BOMID不存在!"); }else{ bo_mothercode = baseDao.getFieldDataByCondition("bom", "bo_mothercode", "bo_id=" + bomId); } if(bo_mothercode==null){ return Result.error("BOM母件编号不存在!"); } if (bomId != null && !bomId.equals("") && bomId.matches("\\d*") && bo_mothercode != null && !bo_mothercode.equals("")) { SqlRowList rs = baseDao .queryForRowSet("select bo_id,pr_specdescription from product left join bom on (pr_code=bo_mothercode or pr_refno=bo_mothercode) where" + " pr_code='" + bo_mothercode + "' and bo_id=" + bomId); if (rs.next()) { String res = baseDao.callProcedure("MM_SetProdBomStruct", new Object[] { rs.getInt("bo_id"), rs.getString("pr_specdescription") }); if (res != null && res.length() > 0) { BaseUtil.showError(res); } } else { return Result.error("BOM母件编号不存在!"); } } List bomStructDTOList = baseDao.query("select * from (select bs_idcode,bs_level,bs_bomid,bs_sonbomid,bs_mothercode,bs_bddetno,bs_soncode,pr_detail,pr_spec,pr_speccs,pr_unit,bs_baseqty,bs_location,bs_usestatus,bs_ecncode,bs_remark,bs_ifrep,ifrep,pr_lossrate,pr_leadtime,pr_zxdhl,po_onhand-nvl(po_defectonhand,0) po_onhand,PR_STANDARDIZED,pr_material,pr_sqrq,bo_level,bo_wccode,bo_ispast " + " from MA_BOMSTRUCT_VIEW where bs_topbomid="+bomId+" AND (bs_topmothercode='"+bo_mothercode+"') " + " order by bs_topbomid,bs_idcode,ifrep desc,bs_soncode )" , BomStructDTO.class); if (bomStructDTOList.size() > 0 ){ return Result.success(bomStructDTOList); }else{ return Result.error("BOM母件编号不存在!"); } } /** * bom成本计算 打印前执行方法 * @param id */ public void bomPrint(Integer id,String caller){ String SQLStr = null, thisMonthercode = null; Object ob = baseDao.getFieldDataByCondition("bom", "bo_mothercode", "bo_id="+id); if(ob != null){ thisMonthercode = ob.toString(); } // 取出当月汇率 double ThisUSDRate =Double.parseDouble(baseDao.getFieldDataByCondition("Currencys","nvl(max(cr_rate),0)"," cr_name='USD' and nvl(cr_status,' ')<>'已禁用'").toString()); double ThisHKDRate =Double.parseDouble(baseDao.getFieldDataByCondition("Currencys","nvl(max(cr_rate),0)"," cr_name='HKD' and nvl(cr_status,' ')<>'已禁用'").toString()); /*if (ThisUSDRate == 0) { BaseUtil.showError("币别表未设置美金汇率!"); } xzx 2017090191号问题反馈*/ SQLStr = "update BomStruct set bs_osprice=0 where bs_topbomid=" + id + " and bs_osprice is null "; baseDao.execute(SQLStr); SQLStr = "merge into BomStruct using (select cr_rate,cr_name from currencys where nvl(cr_status,' ')<>'已禁用') src on( bs_currency=cr_name) when matched then update set bs_l=(CASE WHEN bs_currency='RMB' then bs_purcprice/(1+bs_rate/100) ELSE bs_purcprice*cr_rate END) where bs_topbomid=" + id + " and bs_topmothercode='"+thisMonthercode+"' and (nvl(bs_sonbomid,0)=0 or nvl(bs_sonbomid,0) in (select bo_id from bom left join bomlevel on bl_code=bo_level where nvl(bo_level,' ')='外购件BOM' or nvl(bl_ifpurchase,0)<>0 )) "; baseDao.execute(SQLStr); SQLStr = "update BomStruct set bs_m=bs_l*bs_baseqty where bs_topbomid=" + id + " and bs_topmothercode='"+thisMonthercode+"' and (nvl(bs_sonbomid,0)=0 or nvl(bs_sonbomid,0) in (select bo_id from bom left join bomlevel on bl_code=bo_level where nvl(bo_level,' ')='外购件BOM' or nvl(bl_ifpurchase,0)<>0 ))"; baseDao.execute(SQLStr); SQLStr = "merge into BomStruct using(select cr_rate,cr_name from currencys where nvl(cr_status,' ')<>'已禁用') src on(bs_currency=cr_name)when matched then update set bs_j=(CASE WHEN bs_currency='RMB' then bs_purcprice ELSE bs_purcprice*cr_rate END) where bs_topbomid=" + id + " and bs_topmothercode='"+thisMonthercode+"' and (nvl(bs_sonbomid,0)=0 or nvl(bs_sonbomid,0) in (select bo_id from bom left join bomlevel on bl_code=bo_level where nvl(bo_level,' ')='外购件BOM' or nvl(bl_ifpurchase,0)<>0 )) "; baseDao.execute(SQLStr); SQLStr = "update BomStruct set bs_k=bs_j*bs_baseqty where bs_topbomid=" + id + " and (nvl(bs_sonbomid,0)=0 or nvl(bs_sonbomid,0) in (select bo_id from bom left join bomlevel on bl_code=bo_level where nvl(bo_level,' ')='外购件BOM' or nvl(bl_ifpurchase,0)<>0 )) "; baseDao.execute(SQLStr); SQLStr = "update BomStruct set bs_n=CASE WHEN bs_currency='RMB' then bs_l ELSE bs_purcprice END where bs_topbomid=" + id + " and bs_topmothercode='"+thisMonthercode+"' and (nvl(bs_sonbomid,0)=0 or nvl(bs_sonbomid,0) in (select bo_id from bom left join bomlevel on bl_code=bo_level where nvl(bo_level,' ')='外购件BOM' or nvl(bl_ifpurchase,0)<>0 )) "; baseDao.execute(SQLStr); SQLStr = "update BomStruct set bs_o=bs_n*bs_baseqty where bs_topbomid=" + id + " and bs_topmothercode='"+thisMonthercode+"' and (nvl(bs_sonbomid,0)=0 or nvl(bs_sonbomid,0) in (select bo_id from bom left join bomlevel on bl_code=bo_level where nvl(bo_level,' ')='外购件BOM' or nvl(bl_ifpurchase,0)<>0 )) "; baseDao.execute(SQLStr); SQLStr = "update BomStruct set bs_remark='' where bs_topbomid=" + id+" and bs_topmothercode='"+thisMonthercode+"' "; baseDao.execute(SQLStr); SQLStr = "update BomStruct set bs_usdrate=" + ThisUSDRate + ",bs_hkdrate=" + ThisHKDRate + " where bs_topbomid=" + id+" and bs_topmothercode='"+thisMonthercode+"' "; baseDao.execute(SQLStr); SQLStr = "update BomStruct set bs_remark='father' where bs_topbomid=" + id + " and bs_topmothercode='"+thisMonthercode+"' and nvl(bs_sonbomid,0)>0 "; baseDao.execute(SQLStr); SQLStr = "update BomStruct set bs_remark='father' where bs_topbomid=" + id + " and bs_soncode='" + thisMonthercode + "' "; baseDao.execute(SQLStr); SQLStr = "update BomStruct set bs_currency='RMB',bs_purcprice=bs_osprice,bs_purcpricermb=0,bs_totalpurcpricermb=0,bs_totalpurcpriceusd=0 where bs_topbomid=" + id + " and bs_topmothercode='"+thisMonthercode+"' and (nvl(bs_sonbomid,0)>0 or bs_soncode='" + thisMonthercode + "') and nvl(bs_sonbomid,0) not in (select bo_id from bom left join bomlevel on bl_code=bo_level where nvl(bo_level,' ')='外购件BOM' or nvl(bl_ifpurchase,0)<>0 ) "; baseDao.execute(SQLStr); SQLStr = "select bs_idcode,bs_soncode from BomStruct where bs_topbomid=" + id + " and bs_topmothercode='"+thisMonthercode+"' and nvl(bs_sonbomid,0)>0 and nvl(bs_sonbomid,0) not in (select bo_id from bom left join bomlevel on bl_code=bo_level where nvl(bo_level,' ')='外购件BOM' or nvl(bl_ifpurchase,0)<>0 ) ORDER BY bs_level"; SqlRowList rs = baseDao.queryForRowSet(SQLStr); while (rs.next()) {//bs_osprice 在存储过程中计算出来的值是含税的委外单价 SQLStr = "SELECT sum(nvl(bs_m,0)),sum(nvl(bs_k,0)),sum(bs_o) from BomStruct WHERE bs_topbomid=" + id + " and bs_topmothercode='"+thisMonthercode+"' and bs_mothercode='" + rs.getString("bs_soncode") + "' "; SqlRowList rsthis = baseDao.queryForRowSet(SQLStr); if (rsthis.next()) { SQLStr = "update bomstruct set bs_m=round((" + rsthis.getString(1) + "+nvl(bs_osprice,0)/(1+nvl(bs_rate,0)/100)),8)*bs_baseqty,bs_k=round((" + rsthis.getString(2) + "+nvl(bs_osprice,0)),8)*bs_baseqty,bs_o=" + rsthis.getString(3) + " where bs_topbomid="+id+" and bs_idcode=" + rs.getString("bs_idcode"); baseDao.execute(SQLStr); } } // 当前计算的主件自身 SQLStr = "SELECT bs_topmothercode from BomStruct WHERE bs_topbomid=" + id + " and bs_topmothercode='"+thisMonthercode+"' and bs_mothercode='" + thisMonthercode + "' "; SqlRowList rss = baseDao.queryForRowSet(SQLStr); if (rss.next()) {//bs_m 不含税成本 ，bs_k 含税成本 Object a = baseDao.getFieldDataByCondition("BomStruct", "round(sum(nvl(bs_m,0)),8)", " bs_topbomid=" + id + " and bs_mothercode='" + thisMonthercode + "'"); Object b = baseDao.getFieldDataByCondition("BomStruct", "round(sum(nvl(bs_k,0)),8)", " bs_topbomid=" + id + " and bs_mothercode='" + thisMonthercode + "'"); Object c = baseDao.getFieldDataByCondition("BomStruct", "round(sum(nvl(bs_o,0)),8)", " bs_topbomid=" + id + " and bs_mothercode='" + thisMonthercode + "'"); SQLStr = "update bomstruct set bs_m=round((?+nvl(bs_osprice,0)/(1+nvl(bs_rate,0)/100)),8)*bs_baseqty,bs_k=round((?+nvl(bs_osprice,0)),8)*bs_baseqty,bs_o=? where bs_topbomid="+id+" and bs_soncode='" + thisMonthercode + "' "; baseDao.execute(SQLStr,new Object[]{a,b,c}); } SQLStr = "update BomStruct set bs_m=0 where bs_topbomid=" + id + " and bs_m is null "; baseDao.execute(SQLStr); //调用存储过程每个客户特殊的代码更新 baseDao.procedure("SP_COSTCOUNT_AFTER_MSG", new Object[] { id}); } }