uas-eis/src/main/java/com/uas/eis/service/Impl/STKServiceImpl.java

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 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<DepartmentDTO> 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<DepartmentResp> departmentResps = DepartmentConvertor.toDepartmentRespListByDepartmentDTOS(departmentDTOS);
        Map<String,Object> 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<EmployeeDTO> 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<EmployeeResp> employeeResps = EmployeeConvertor.toEmployeeRespListByEmployeeDTOS(employeeDTOS);
        Map<String,Object> 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<BusinessChanceDTO> 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_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<BusinessChanceResp> businessChanceResps = BusinessChanceConvertor.toBusinessChanceRespListByBusinessChanceDTOS(businessChanceDTOS);
        Map<String,Object> 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<CustomerDTO> 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<CustomerResp> customerResps = CustomerConvertor.toCustomerRespListByCustomerDTOS(customerDTOS);
        Map<String,Object> 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<SaleDTO> 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 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<SaleDetailDTO> 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<BigDecimal, List<SaleDetailDTO>> saleDetailMap = saleDetailDTOS.stream().collect(Collectors.groupingBy(SaleDetailDTO::getSd_said));
        // 6. 组装数据
        List<SaleResp> saleResps = assembleOrderData(saleDTOS, saleDetailMap);

        int count = baseDao.getCount("select count(1) from Sale where sa_statuscode in ('AUDITED','FINISH')");

        Map<String,Object> map = new HashMap<>();
        map.put("pageNum", pageNum);
        map.put("pageSize", pageSize);
        map.put("size", count);
        map.put("list", saleResps);
        return Result.success(map);
    }

    /**
     * 组装订单主从表数据
     */
    private List<SaleResp> assembleOrderData(List<SaleDTO> orders, Map<BigDecimal, List<SaleDetailDTO>> itemsMap) {
        return orders.stream()
                .map(order -> {
                    SaleResp saleListResp = convertToVO(order);

                    // 设置订单明细
                    List<SaleDetailDTO> items = itemsMap.get(order.getSa_id());
                    if (!CollectionUtils.isEmpty(items)) {
                        List<SaleDetailResp> 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);
    }

    @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<ProductDTO> 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<BomDTO> 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<BomDetailDTO> 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<BomDetailResp> 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<BomDTO> 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<String,Object> 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<ProjectDTO> 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<ProjectResp> projectResps = ProjectConvertor.toProjectRespListByProjectDTOs(projectDTOList);

        Map<String,Object> 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<BomCostDTO> 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<String,Object> 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<String> sqls = new ArrayList<String>();
        List<AppraiseDetailDTO> 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<BomStructDTO> 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});

    }

}