请输入
菜单

送货单打印，A5纸张，每页7行产品，可选择是否打印单价、公章

下载

一、实现方案

触发器拼接 html代码，写到单据上的富文本字段上，打印这个字段

二、demo脚本

-- 1、自定义函数根据单据ID生成打印的HTML，并根据参数判断：是否要显示图章、是否显示单价
DROP FUNCTION IF EXISTS `hetao_print_songhuodan_new`;

CREATE FUNCTION `hetao_print_songhuodan_new`(
    in_billid INT(19),
    in_haveprice VARCHAR(10),
    in_havezhang VARCHAR(10)
)
 RETURNS longtext
BEGIN


    DECLARE tmp_billid INT(19) DEFAULT 0;
    DECLARE tmp_haveprice VARCHAR(10) DEFAULT '';
    DECLARE tmp_havezhang VARCHAR(10) DEFAULT '';
    DECLARE tmp_rs LONGTEXT CHARSET GBK DEFAULT '';

    SET tmp_billid = in_billid;
    SET tmp_haveprice = in_haveprice;
    SET tmp_havezhang = in_havezhang;

    SET tmp_rs=(
    SELECT
        GROUP_CONCAT(CONCAT('<table border="0" cellpadding="0" cellspacing="0" style="width:100%">',CHAR(13),
            SPACE(1),'<tr>',CHAR(13),
            SPACE(1),'<td style="width:96%; border:none">',CHAR(13),
            SPACE(2),'<table border="0" cellpadding="0" cellspacing="0" style="width:100%; line-height:8mm">',CHAR(13),
            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="left" style="width:30%; border:none"><span class="font03" style="margin-left:1mm">贺众牌 UNION</span></td>',CHAR(13),
            SPACE(4),'<td align="center" style="width:40%; border:none"><span class="font03">',IFNULL(b.cf_5656,''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="right" style="width:30%; border:none"><span class="font01" style="margin-right:1mm">NO: ',IFNULL(a.customform12_no,''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),
            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="left" style="border:none"><span class="font01" style="margin-left:1mm">站 名：',IFNULL((SELECT GROUP_CONCAT(groupname) FROM ld_groups WHERE FIND_IN_SET(groupid,b.cf_4617) ORDER BY depth,groupid),''),IFNULL(b.cf_5343,''),DATE_FORMAT(NOW(),' %y%m%d'),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center" style="border:none"><span class="font04">送货单</span></td>',CHAR(13),
            -- SPACE(4),'<td align="right" style="border:none"><span class="font01" style="letter-spacing:6mm">年月</span><span class="font01" style="margin-right:1mm">日</span></td>',CHAR(13),
            SPACE(4),'<td align="right" style="border:none"><span class="font01" style="margin-right:1mm">',CONCAT(YEAR(b.cf_4618),' 年 ',MONTH(b.cf_4618),' 月 ',DAY(b.cf_4618),' 日'),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),
            SPACE(2),'</table>',CHAR(13),

            SPACE(2),'<table border="1" cellpadding="0" cellspacing="0" style="width:100%; margin-top:2mm; line-height:6mm">',CHAR(13),
            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td colspan="2" align="center" style="width:10%"><span class="font01">客户名称</span></td>',CHAR(13),
            SPACE(4),'<td colspan="4" align="left" style="width:68%"><span class="font01" style="margin-left:1mm">',IFNULL(c.accountname,''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center" style="width:11%"><span class="font01">经办人</span></td>',CHAR(13),
            SPACE(4),'<td align="center" style="width:11%"><span class="font01">',IFNULL(d.lastname,''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td colspan="2" align="center"><span class="font01">送货地址</span></td>',CHAR(13),
            SPACE(4),'<td colspan="4" align="left"><span class="font01" style="margin-left:1mm">',IFNULL(b.cf_4632,''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">电话</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL(b.cf_4628,""),'</td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td colspan="4" align="center"><span class="font01">付款方式</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">营业员</span></td>',CHAR(13),
            SPACE(4),'<td align="left"><span class="font01" style="margin-left:1mm">',IFNULL(e.last_name,''),'</span></td>',CHAR(13),
            SPACE(4),'<td rowspan="2" align="center"><span class="font01">客户签字</span></td>',CHAR(13),
            SPACE(4),'<td rowspan="2" align="center"><span class="font01">&nbsp;</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td colspan="4" align="center"><span class="font01">',CONCAT('现金',(CASE WHEN b.cf_4634='现金' THEN '■' ELSE '□' END),'&nbsp;&nbsp;转账',(CASE WHEN b.cf_4634='转账' THEN '■' ELSE '□' END),'&nbsp;&nbsp;未收',(CASE WHEN b.cf_4634='未收' THEN '■' ELSE '□' END)),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">预计收款日期</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01" style="margin-left:1mm">',IFNULL(b.cf_4635,''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="center" style="width:6%"><span class="font01">序号</span></td>',CHAR(13),
            SPACE(4),'<td colspan="2" align="center" style="width:11%"><span class="font01">产品编码</span></td>',CHAR(13),
            SPACE(4),'<td align="center" style="width:27%"><span class="font01">产品名称</span></td>',CHAR(13),
            SPACE(4),'<td align="center" style="width:16%"><span class="font01">数量</span></td>',CHAR(13),
            SPACE(4),'<td align="center" style="width:11%"><span class="font01">实收数量</span></td>',CHAR(13),
            SPACE(4),'<td align="center" style="width:11%"><span class="font01">单价</span></td>',CHAR(13),
            SPACE(4),'<td align="center" style="width:18%"><span class="font01">合计</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">1</span></td>',CHAR(13),
            SPACE(4),'<td colspan="2" align="center"><span class="font01">',IFNULL((SELECT b.product_no FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+1),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT b.productname FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+1),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT FORMAT(quantity,1) FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+1),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">','</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(taxprice,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+1),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(tax_amount,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+1),''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">2</span></td>',CHAR(13),
            SPACE(4),'<td colspan="2" align="center"><span class="font01">',IFNULL((SELECT b.product_no FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+2),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT b.productname FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+2),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT FORMAT(quantity,1) FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+2),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">','</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(taxprice,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+2),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(tax_amount,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+2),''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">3</span></td>',CHAR(13),
            SPACE(4),'<td colspan="2" align="center"><span class="font01">',IFNULL((SELECT b.product_no FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+3),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT b.productname FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+3),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT FORMAT(quantity,1) FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+3),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">','</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(taxprice,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+3),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(tax_amount,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+3),''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">4</span></td>',CHAR(13),
            SPACE(4),'<td colspan="2" align="center"><span class="font01">',IFNULL((SELECT b.product_no FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+4),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT b.productname FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+4),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT FORMAT(quantity,1) FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+4),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">','</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(taxprice,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+4),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(tax_amount,2) ELSE '' END  FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+4),''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">5</span></td>',CHAR(13),
            SPACE(4),'<td colspan="2" align="center"><span class="font01">',IFNULL((SELECT b.product_no FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+5),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT b.productname FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+5),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT FORMAT(quantity,1) FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+5),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">','</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(taxprice,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+5),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(tax_amount,2) ELSE '' END  FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+5),''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">6</span></td>',CHAR(13),
            SPACE(4),'<td colspan="2" align="center"><span class="font01">',IFNULL((SELECT b.product_no FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+6),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT b.productname FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+6),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT FORMAT(quantity,1) FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+6),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">','</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(taxprice,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+6),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(tax_amount,2) ELSE '' END  FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+6),''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">7</span></td>',CHAR(13),
            SPACE(4),'<td colspan="2" align="center"><span class="font01">',IFNULL((SELECT b.product_no FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+7),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT b.productname FROM ld_customform12detail a LEFT JOIN ld_products b ON b.productid=a.productid WHERE a.id=tmp_billid AND a.sequence_no=(s.seq-1)*7+7),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT FORMAT(quantity,1) FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+7),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">','</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(taxprice,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+7),''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',IFNULL((SELECT CASE WHEN tmp_haveprice='yes' THEN FORMAT(tax_amount,2) ELSE '' END FROM ld_customform12detail WHERE id=tmp_billid AND sequence_no=(s.seq-1)*7+7),''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td colspan="6" align="left"><span class="font01" style="margin-left:1mm">兹收到上列货品无误。上列产品货款未结清时。此产品仍属本公司所有。</span></td>',CHAR(13),
            SPACE(4),'<td align="right"><span class="font01" style="margin-right:1mm">税金</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01"></span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="left"><span class="font01" style="margin-left:1mm">备注</span></td>',CHAR(13),
            SPACE(4),'<td colspan="5" align="left"><span class="font01" style="margin-left:1mm"></span></td>',CHAR(13),
            SPACE(4),'<td align="right"><span class="font01" style="margin-right:1mm">总计</span></td>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">',CASE WHEN tmp_haveprice='yes' THEN FORMAT(a.total,2) ELSE '' END,'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),
            SPACE(2),'</table>',CHAR(13),
        
            SPACE(2),'<table border="0" cellpadding="0" cellspacing="0" style="width:100%; margin-top:2mm; line-height:6mm">',CHAR(13),
            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="left" style="width:50%"><span class="font01" style="margin-left:1mm">地址：',IFNULL(t.dizhi,''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="left" style="width:30%"><span class="font01" style="margin-left:1mm">邮箱：',IFNULL(t.youxiang,''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="left" style="width:20%"><span class="font01" style="margin-left:1mm">微信客服：',IFNULL(t.kefu,''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="left"><span class="font01" style="margin-left:1mm">电话：',IFNULL(t.dianhua,''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="left"><span class="font01" style="margin-left:1mm">网址：',IFNULL(t.wangzhi,''),'</span></td>',CHAR(13),
            SPACE(4),'<td align="left"><span class="font01" style="margin-left:1mm">传真：',IFNULL(t.chuanzhen,''),'</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),

            SPACE(2),'</table>',CHAR(13),
            SPACE(1),'</td>',CHAR(13),

            SPACE(1),'<td rowspan="16" align="center" style="width:4%; border:none">',CHAR(13),
            SPACE(2),'<table border="0" cellpadding="0" cellspacing="0" style="width:100%; line-height:6mm">',CHAR(13),
            SPACE(3),'<tr>',CHAR(13),
            SPACE(4),'<td align="center"><span class="font01">①<br>白<br>存<br>根<br>②<br>红<br>仓<br>库<br>③<br>蓝<br>收<br>款<br>④<br>黄<br>客<br>户</span></td>',CHAR(13),
            SPACE(3),'</tr>',CHAR(13),
            SPACE(2),'</table>',CHAR(13),
            SPACE(1),'</td>',CHAR(13),
            SPACE(1),'</tr>',CHAR(13),    
            '</table>',CHAR(13),    
            IF((SELECT COUNT(id) FROM ld_field_signature WHERE fieldname='cf_4560' AND recordid=a.customform12id)>0,CONCAT('<div><img src="',IFNULL((SELECT CONCAT(path,`name`) FROM ld_field_signature WHERE fieldname='cf_4560' AND recordid=a.customform12id LIMIT 1),''),'" style="height:25mm; position:relative; z-index:-99; margin-left:86%; margin-top:-100mm; transform:rotate(-90deg);"></div>'),''),CHAR(13),    
            IF(tmp_havezhang='yes','<div><img src="templatePrintFile/zhang.png" style="width:177; height:106; position:relative; z-index:5; margin-left:60mm; margin-top:-30mm;"></div>',''),
            CHAR(13)
        ) SEPARATOR '<div class="pagebreak"> </div>') AS my_html
    FROM ld_customform12 a
    LEFT JOIN ld_customform12scf b ON b.customform12id=a.customform12id
    LEFT JOIN ld_account c ON c.accountid=a.accountid
    LEFT JOIN ld_contactdetails d ON d.contactid=a.contactid
    LEFT JOIN ld_users e ON e.id=a.smownerid
    LEFT JOIN (
        SELECT
            g.groupid,
            b.cf_5580 AS zhandian,
            b.cf_5581 AS dizhi,
            b.cf_5582 AS youxiang,
            b.cf_5583 AS kefu,
            b.cf_5584 AS dianhua,
            b.cf_5585 AS wangzhi,
            b.cf_5586 AS chuanzhen
        FROM ld_customform50 a
        LEFT JOIN ld_customform50scf b ON b.customform50id=a.customform50id
        INNER JOIN ld_groups g ON g.groupname=b.cf_5580
        WHERE a.deleted=0
            AND IFNULL(b.cf_5580,'')<>''
        GROUP BY b.cf_5580
    ) t ON t.groupid=b.cf_4617+0
    CROSS JOIN (
        SELECT
            seq
        FROM sequence
        WHERE seq>0
            AND seq<=(SELECT CEILING(COUNT(lineitem_id)/7) FROM ld_customform12detail WHERE id=tmp_billid)
    ) s 
    WHERE a.customform12id=tmp_billid
    );

    RETURN tmp_rs;

end;

 

DROP TRIGGER IF EXISTS `hetao_resetinfo_before_form12_update`;
DELIMITER ;;
CREATE TRIGGER `hetao_resetinfo_before_form12_update` BEFORE UPDATE ON `ld_customform12` FOR EACH ROW BEGIN

    IF NEW.triggercount<>OLD.triggercount THEN
        DELETE FROM ld_modtracker_basic WHERE id IN (SELECT id FROM ld_modtracker_detail WHERE fieldname IN ('print_info','print_info_noprice','print_nozhang','print_nozhangprice'));
        DELETE FROM ld_modtracker_detail WHERE fieldname IN ('print_info','print_info_noprice','print_nozhang','print_nozhangprice');

        -- 有重复产品合并
        SET @this_form12id=NEW.customform12id;
        IF EXISTS (SELECT 1 FROM ld_customform12detail WHERE id=@this_form12id GROUP BY productid,taxprice HAVING COUNT(productid)>1) THEN
            DELETE FROM ld_customform12detail_bak;
            INSERT INTO ld_customform12detail_bak(
                id,
                lineitem_id,
                sequence_no,
                productid,
                quantity,

                taxprice,
                tax_amount,
                entitytype,
                entitytypeid,
                parent_lineitem_id
            )
            SELECT
                id,
                lineitem_id,
                sequence_no,
                productid,
                quantity,

                taxprice,
                tax_amount,
                entitytype,
                entitytypeid,
                parent_lineitem_id
            FROM ld_customform12detail
            WHERE id=@this_form12id;

            DELETE FROM ld_customform12detailscf WHERE lineitem_id IN (SELECT lineitem_id FROM ld_customform12detail WHERE id=@this_form12id);
            DELETE FROM ld_customform12detail WHERE id=@this_form12id;

            SET @i=0;
            INSERT INTO ld_customform12detail(
                id,
                lineitem_id,
                sequence_no,
                productid,
                quantity,

                taxprice,
                tax_amount,
                entitytype,
                entitytypeid,
                parent_lineitem_id
            )
            SELECT
                id,
                lineitem_id,
                (@i:=@i+1),
                productid,
                SUM(quantity),

                SUM(tax_amount)/SUM(quantity),
                SUM(tax_amount),
                entitytype,
                entitytypeid,
                parent_lineitem_id
            FROM ld_customform12detail_bak
            WHERE id=@this_form12id
            GROUP BY productid,taxprice;

            INSERT INTO ld_customform12detailscf(lineitem_id) SELECT lineitem_id FROM ld_customform12detail WHERE id=@this_form12id;
        END IF;

        -- 重算打印信息
        SET @i=0;
        UPDATE ld_customform12detail SET sequence_no=(@i:=@i+1) WHERE id=NEW.customform12id ORDER BY sequence_no,lineitem_id;
        SET NEW.print_nozhang=hetao_print_songhuodan_new(NEW.customform12id,'yes','no');
        SET NEW.print_nozhangprice=hetao_print_songhuodan_new(NEW.customform12id,'no','no');

    END IF;

END
;;
DELIMITER ;

发货单打印每张纸7行产品.sql

上一个
报价单打印，需要在打印模版中附上公司简介和产品图片
下一个
其他常见问题
最近修改: 2025-02-05