一、实现方案

触发器拼接 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"> </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),'  转账',(CASE WHEN b.cf_4634='转账' THEN '■' ELSE '□' END),'  未收',(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

打印模板案例