销售机会保存后自动生成待采购询价单

一、demo脚本

DROP PROCEDURE IF EXISTS `dmk_autoaddform13_bypotential`;
DELIMITER ;;
CREATE PROCEDURE `dmk_autoaddform13_bypotential`(
    tmp_billid INT(19))
BEGIN

    -- 1、按行生成
    -- 2、16~20，22，24~44  传到待询价列表合并成1个字段
    DECLARE tmp_lineid INT(19) DEFAULT 0;
    DECLARE tmp_productid INT(19) DEFAULT 0;
    DECLARE tmp_quantity DECIMAL(25,2) DEFAULT 0.00;
    DECLARE tmp_miaoshu TEXT DEFAULT NULL;

    DECLARE MYCUSOR CURSOR FOR 
        SELECT
            a.lineitem_id,
            a.productid,
            a.quantity,
            a.comment
        FROM ld_potentialsdetail a
        LEFT JOIN ld_potentialsdetailscf b ON b.lineitem_id=a.lineitem_id
        WHERE a.id=tmp_billid
            AND b.sf12399=1
            AND a.lineitem_id NOT IN (SELECT y.parent_lineitem_id FROM ld_customform13 x LEFT JOIN ld_customform13detail y ON y.id=x.customform13id WHERE x.deleted=0 AND y.entitytypeid=a.id);

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tmp_lineid = NULL;

    OPEN MYCUSOR; 
    FETCH MYCUSOR INTO tmp_lineid,tmp_productid,tmp_quantity,tmp_miaoshu;
    WHILE(tmp_lineid IS NOT NULL) DO
        SET @tmp_lineid=tmp_lineid;
        SET @tmp_jishuyaoqiu=(
            SELECT
                GROUP_CONCAT(jishuyaoqiu SEPARATOR ';') 
            FROM (
                SELECT CONCAT('执行标准:',sf5034) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5034,'')<>''
                UNION
                SELECT CONCAT('表面加工要求:',sf5101) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5101,'')<>''
                UNION
                SELECT CONCAT('客户物料编码:',sf5168) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5168,'')<>''
                UNION
                SELECT CONCAT('纹理方向:',sf5235) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5235,'')<>''
                UNION
                SELECT CONCAT('是否切割:',sf5302) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5302,'')<>''

                UNION
                SELECT CONCAT('产地要求:',sf5436) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5436,'')<>''

                UNION
                SELECT CONCAT('净尺寸或毛尺寸:',sf5570) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5570,'')<>''
                UNION
                SELECT CONCAT('公差要求:',sf5637) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5637,'')<>''
                UNION
                SELECT CONCAT('包装要求:',sf5704) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5704,'')<>''
                UNION
                SELECT CONCAT('探伤等级:',sf5771) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5771,'')<>''
                UNION
                SELECT CONCAT('晶粒度:',sf5838) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5838,'')<>''
                UNION
                SELECT CONCAT('腐蚀要求:',sf5905) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5905,'')<>''
                UNION
                SELECT CONCAT('电导率（铝）:',sf5972) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf5972,'')<>''

                UNION
                SELECT CONCAT('纯净度:',sf6039) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6039,'')<>''
                UNION
                SELECT CONCAT('夹杂等级:',sf6106) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6106,'')<>''
                UNION
                SELECT CONCAT('膨胀系数:',sf6173) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6173,'')<>''
                UNION
                SELECT CONCAT('锻造比:',sf6240) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6240,'')<>''
                UNION
                SELECT CONCAT('热处理要求:',sf6307) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6307,'')<>''
                UNION
                SELECT CONCAT('弹性模量:',sf6374) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6374,'')<>''
                UNION
                SELECT CONCAT('抗拉强度:',sf6441) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6441,'')<>''
                UNION
                SELECT CONCAT('屈服强度:',sf6508) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6508,'')<>''
                UNION
                SELECT CONCAT('延伸率:',sf6575) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6575,'')<>''
                UNION
                SELECT CONCAT('硬度:',sf6642) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6642,'')<>''

                UNION
                SELECT CONCAT('磁性:',sf6709) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6709,'')<>''
                UNION
                SELECT CONCAT('切割方式:',sf6776) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6776,'')<>''
                UNION
                SELECT CONCAT('熔炼方式:',sf6843) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6843,'')<>''
                UNION
                SELECT CONCAT('废料比例:',sf6910) AS jishuyaoqiu FROM ld_potentialsdetailscf WHERE lineitem_id=@tmp_lineid AND IFNULL(sf6910,'')<>''
            ) a
        );

        
        -- 获取自动创建单据的 ID
        SET @autoadd_maxcrmid=(SELECT MAX(crmid) FROM ld_crmentity);
        INSERT INTO ld_crmentity(crmid,setype)
        SELECT @autoadd_maxcrmid+1,'CustomForm13';

        UPDATE ld_crmentity_seq SET id=(SELECT MAX(crmid) FROM ld_crmentity);

        INSERT INTO ld_customform13(
            customform13id,
            customform13_no,
            entitytype,
            source_list,

            smownerid,
            smcreatorid,
            modifiedby,
            createdtime,
            modifiedtime,

            currency_id,
            currency_symbol,
            conversion_rate,
            total,
            subtotal,
            discount_amount,    -- 折扣直接降价

            accountid,                -- 客户名称
            triggercount
        )
        SELECT
            @autoadd_maxcrmid+1,
            (SELECT CONCAT(prefix,cur_id) FROM ld_modentity_num WHERE semodule='customform13' AND active=1),
            'Potentials',
            a.potentialid,

            a.smownerid,
            a.smownerid,
            a.smownerid,
            NOW(),
            NOW(),

            a.currency_id,
            a.currency_symbol,
            a.conversion_rate,
            0,
            0,
            0,                    -- 折扣直接降价

            accountid,    -- 客户名称
            1
        FROM ld_potential a
        WHERE a.potentialid=tmp_billid;

        INSERT INTO ld_customform13scf(customform13id) SELECT @autoadd_maxcrmid+1;
        UPDATE ld_modentity_num SET cur_id=cur_id+1 WHERE semodule='CustomForm13' AND active=1;

        -- 插入产品明细
        INSERT INTO ld_customform13detail(
            id,
            productid,
            sequence_no,

            quantity,
            listprice,
            taxprice,
            tax1,
            tax2,
            tax_total,
            list_amount,
            tax_amount,

            comment,
            parent_lineitem_id,
            entitytype,
            entitytypeid
        )
        SELECT
            @autoadd_maxcrmid+1,
            tmp_productid,
            1,

            tmp_quantity,
            0,
            0,
            1,
            16,
            0,
            0,
            0,

            tmp_miaoshu,
            @tmp_lineid,
            'Potentials',
            tmp_billid;

        INSERT INTO ld_customform13detailscf(
            sf16425,            -- 技术要求
            lineitem_id
        )
        SELECT
            @tmp_jishuyaoqiu,            -- 技术要求
            a.lineitem_id
        FROM ld_customform13detail a
        WHERE a.id=@autoadd_maxcrmid+1;

        FETCH MYCUSOR INTO tmp_lineid,tmp_productid,tmp_quantity,tmp_miaoshu;
    END WHILE;
    CLOSE MYCUSOR;

END
;;
DELIMITER ;

CALL dmk_autoaddform13_bypotential(284903);

-- 添加工作流程定制调用
-- CALL dmk_autoaddform13_bypotential('{Record.ID}')

 

--添加工作流程定制采购询价单反写销售机会
UPDATE ld_purchaseinquiryorder a
LEFT JOIN ld_purchaseinquiryorderdetail b ON b.id=a.purchaseinquiryorderid
LEFT JOIN ld_purchaseinquiryorderdetailscf c ON c.lineitem_id=b.lineitem_id
LEFT JOIN ld_customform13detail d ON d.lineitem_id=b.parent_lineitem_id
INNER JOIN ld_potentialsdetail e ON e.lineitem_id=d.parent_lineitem_id
LEFT JOIN ld_potentialsdetailscf f ON f.lineitem_id=e.lineitem_id
SET 
    e.line_vendors_id=b.line_vendors_id,
    f.sf12597=c.sf12597,
    
    f.sf12003=c.sf12003,
    f.sf12135=c.sf12135,

    f.sf12069=c.sf12069,
    f.sf12201=c.sf12201,

    f.sf12531=c.sf12531,
    f.sf16492=c.sf16492,

    f.sf16558=c.sf16558,
    f.sf16624=c.sf16624,

    f.sf16690=c.sf16690,
    f.sf16756=c.sf16756
WHERE a.deleted=0
    AND b.entitytype='CustomForm13'
    AND a.purchaseinquiryorderid='{Record.ID}'

销售机会保存后自动生成待采购询价单.sql