Mysql 存储过程拼接多个HTML报告表格案例
该篇文章不多做介绍，是按照前几篇存储过程拼接HTML的一个总结案例，也是记录第一次将存储过程写的这么长，并没有什么具体的学习意义。
可以做的优化有：
1、将该存储过程各个报告模板的代码块写成单独的函数方便后期维护。
2、存储过程的变量字段进行调整长度。
3、减少On循环嵌套
以下为存储过程源代码
CREATE PROCEDURE contactHtml(
IN orderCode VARCHAR(255),
IN orderType INT,
OUT pReturn TEXT
)
BEGIN
/*
1、金属材料性能检测委托单
2、复合材料性能检测委托单
3、金属加工委托单
4、尺寸检测委托单
5、复合材料加工委托单
6、振动试验检测任务委托单
7、物理性能金属、复材委托单
8、高分子材料测试委托单
9、化学成分测试委托单
10、腐蚀与环境性能委托单
11、失效分析委托单
12、结构强度测试委托单
13、紧固件性能测试委托单
*/
/*----------------------------------------------------------------------------------------------------------------------------------------*/
-- 1、金属材料性能检测委托单
IF orderType = 1 THEN
BEGIN
-- 委托单主表部分
-- 委托单位
DECLARE client VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 地址
DECLARE address VARCHAR(255);
-- 联系方式
DECLARE contactNumber VARCHAR(255);
-- 样品处理
DECLARE productHandling VARCHAR(255);
-- 样品处理邮寄地址
DECLARE productPostalAddress VARCHAR(255);
-- 报告处理
DECLARE reportHandling VARCHAR(255);
-- 报告处理邮寄地址
DECLARE reportPostalAddress VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收料日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE requiredDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 备注
DECLARE note VARCHAR(255);
-- 材料样品表部分
-- 判断材料ID是否为0
DECLARE countMaterial INT;
-- 材料ID
DECLARE materialID INT;
-- 材料名称
DECLARE material VARCHAR(255);
-- 牌号
DECLARE grades VARCHAR(255);
-- 材料规格
DECLARE ticknessDiameter VARCHAR(255);
-- 品种
DECLARE form_material VARCHAR(255);
-- 材料炉批号
DECLARE batchNo varchar(255);
-- 材料热处理状态
DECLARE condition_material VARCHAR(255);
-- 技术状态
DECLARE technicalState VARCHAR(255);
-- 试样类型
DECLARE type_material VARCHAR(255);
-- 试样尺寸
DECLARE dimension VARCHAR(255);
-- 试样数量
DECLARE quantity_material VARCHAR(255);
-- 试样编号 暂时没有使用
DECLARE specimenNumber VARCHAR(255);
-- 试验要求
-- 检测项目
DECLARE testingItem TEXT DEFAULT '';
-- 检测标准
DECLARE _standard TEXT DEFAULT '';
-- 加载及测量参数
DECLARE loadingAndMeasurement TEXT DEFAULT '';
-- 试验环境
DECLARE _environment TEXT DEFAULT '';
-- 存储过程存放变量部分
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放HTML余料处理与任务产品处理部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放HTML任务名称部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 存放材料部分
DECLARE htmlOrder4 TEXT DEFAULT '';
-- 存放工作内容部分
DECLARE htmlOrder5 TEXT DEFAULT '';
DECLARE done INT DEFAULT 0;
-- 获取试样信息
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF137, ''), IFNULL(SKF138, ''), IFNULL(SKF139,''),IFNULL(SKF140,''),IFNULL(SKF141,''),
IFNULL(SKF142,''),IFNULL(SKF143,''),IFNULL(SKF144,''),IFNULL(SKF279,''),
IFNULL(SKF148,''),IFNULL(SKF150,0),IFNULL(SKF280, '')
FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
-- 主子游标循环判断,指定循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 获取报告的基础信息
SELECT IFNULL(SKF69, ''), IFNULL(SKF71, ''), IFNULL(SKF70, ''), IFNULL(SKF72, ''),
IFNULL(SKF73, ''), IFNULL(SKF74, ''),IFNULL(SKF75, ''), IFNULL(SKF76, ''),
IFNULL(SKF77, ''), IFNULL(SKF78, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''),
IFNULL(SKF81, ''), IFNULL(SKF82, ''), IFNULL(SKF220, '') INTO
client, contact, address, contactNumber,
productHandling, productPostalAddress,reportHandling,reportPostalAddress,
taskName, projectNumber, taskNumber, receivedDate,
requiredDate, agreedDate, note
FROM SKT8 WHERE SKF68 = orderCode;
-- 设置HTML订单前半部分
SET htmlOrder1 =
CONCAT(
'<!doctype html>
<html>
<head>
<meta charset="gb2312">
<title>金属材料性能检测委托单</title>
<style type="text/css">
body,td,h6 {
font-family: Verdana,
Arial, Helvetica,
sans-serif;
font-size: 18px;
color: #1d1007;
line-height:16px;
font-weight: 500;
text-align: center;
}
th,tr {
font-family: Verdana,
Arial, Helvetica,
sans-serif;
font-size: 18px;
color: #1d1007;
line-height:16px;
font-weight: 500;
text-align: center;
height: 40px;
}
</style>
</head>
<body>
<table width="100%" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th style="width: 15%;">委托单位<br>Client</th>
<th style="width: 35%;" colspan="2">',client,'</th>
<th style="width: 15%;">联系人<br>Contact</th>
<th colspan="4" colspan="2">',contact,'</th>
</tr>
<tr>
<th style="width: 15%;">地址<br>Address</th>
<th style="width: 35%;" colspan="2">',address,'</th>
<th style="width: 15%;">联系方式<br>Contact Number</th>
<th colspan="4" colspan="2">',contactNumber,'</th>
</tr>');
-- 样品处理部分
IF productHandling = '委托处理' THEN
SET htmlOrder2 =
'<tr>
<th style="width: 15%;">样品处理<br>Handling of Specimens</th>
<th colspan="5">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="委托处理" checked="checked">委托处理<br>Commissioned Handling</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick-up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮寄">邮寄(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr>';
ELSEIF productHandling = '自取' THEN
SET htmlOrder2 =
'<tr>
<th style="width: 15%;">样品处理<br>Handling of Specimens</th>
<th colspan="5">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="委托处理">委托处理<br>Commissioned Handling</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取" checked="checked">自取<br>Self-pick-up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮寄">邮寄(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr>';
ELSEIF productHandling = '邮寄' THEN
SET htmlOrder2 =
CONCAT(
'<tr>
<th style="width: 15%;">样品处理<br>Handling of Specimens</th>
<th colspan="5">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="委托处理">委托处理<br>Commissioned Handling</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick-up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮寄" checked="checked">邮寄(邮寄地址):',productPostalAddress,'<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr>');
-- 若为空，或者值不对，只显示表格信息
ELSE
SET htmlOrder2 =
' <tr>
<th style="width: 15%;">样品处理<br>Handling of Specimens</th>
<th colspan="5">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="委托处理">委托处理<br>Commissioned Handling</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick-up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮寄">邮寄(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr>';
END IF;
-- 去报告方式
IF reportHandling = '自取' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<th style="width: 15%;">取报告方式<br>Report pick up</th>
<th colspan="5">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取" checked="checked">自取<br>Self-pick up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="传真(号码)">传真（号码）<br>Fax（Number）</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮件">邮件(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr> ');
ELSEIF reportHandling = '传真' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<th style="width: 15%;">取报告方式<br>Report pick up</th>
<th colspan="5">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="传真(号码)" checked="checked">传真（号码）<br>Fax（Number）</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮件">邮件(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr> ');
ELSEIF reportHandling = '邮寄' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<th style="width: 15%;">取报告方式<br>Report pick up</th>
<th colspan="5">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="传真(号码)">传真（号码）<br>Fax（Number）</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮件" checked="checked">邮件(邮寄地址):',reportPostalAddress,'<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr> ');
ELSE
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<th style="width: 15%;">取报告方式<br>Report pick up</th>
<th colspan="5">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="传真(号码)">传真（号码）<br>Fax（Number）</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮件">邮件(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr> ');
END IF;
-- 任务名称部分
SET htmlOrder3 =
CONCAT(
'<tr>
<th style="width: 15%;">任务名称<br>Task Name</th>
<th>',taskName,'</th>
<th>项目令号<br>Project Number</th>
<th>',projectNumber,'</th>
<th>任务编号<br>Task Number</th>
<th>',taskNumber,'</th>
</tr>
<tr>
<th>收料日期<br>Received Date</th>
<th>',receivedDate,'</th>
<th>要求完成日期<br>Required Date</th>
<th>',requiredDate,'</th>
<th>商定完成日期<br>Agreed Date</th>
<th>',agreedDate,'</th>
</tr>');
-- 试样信息部分拼接
-- 获取材料ID数量
SELECT COUNT(SKF137) INTO countMaterial FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
IF countMaterial != 0 THEN
OPEN cur_1 ;
FETCH cur_1 INTO
materialID, material, grades ,ticknessDiameter ,form_material ,batchNo ,condition_material ,
technicalState ,type_material ,dimension ,quantity_material ,specimenNumber;
WHILE done != 1 DO
-- 检测项目
-- 检测标准
-- 加载及测量参数
-- 试验环境
SELECT IFNULL(GROUP_CONCAT(SKF175),''), IFNULL(GROUP_CONCAT(SKF174),''),IFNULL(GROUP_CONCAT(SKF187),''),
IFNULL(GROUP_CONCAT(SKF173),'')
INTO testingItem , _standard ,loadingAndMeasurement , _environment FROM SKT10
WHERE SKF388 = materialID;
SET htmlOrder4 =
CONCAT(htmlOrder4,
'<tr>
<th style="width: 15%;" rowspan="5">试样信息<br>Specimen Info.</th>
<th>材料名称/牌号<br>Material/Grades</th>
<th>',material,'/',grades,'</th>
<th>材料规格<br>Thickness or Diameter</th>
<th colspan="2">',ticknessDiameter,'</th>
</tr>
<tr>
<th>品种<br>Form</th>
<th>',form_material,'</th>
<th>材料炉批号<br>Heat No./Batch No.</th>
<th colspan="2">',batchNo,'</th>
</tr>
<tr>
<th>材料热处理状态<br>Condition</th>
<th>',condition_material,'</th>
<th>技术状态<br>Technical State</th>
<th colspan="2">',technicalState,'</th>
</tr>
<tr>
<th>试样类型<br>Type</th>
<th>试样尺寸<br>Dimension</th>
<th>试样数量<br>Quantity</th>
<th colspan="2">试样编号<br>Specimen Number</th>
</tr>
<tr>
<th>',type_material,'</th>
<th>',dimension,'</th>
<th>',quantity_material,'</th>
<th colspan="2">',specimenNumber,'</th>
</tr>
<tr>
<th rowspan="2">试验要求<br>Technical Requirements</th>
<th colspan="5">
试验及技术要求(检测项目，检测标准，加载及测量参数，试验环境等)<br>
Test and Technical Requirements (Testing Item, Standard, Loading and Measurement Parameters, Environment etc.)
</th>
</tr>
<tr>
<th style="height: 100px; text-align: left;" colspan="5">
<p>检测项目：',testingItem,'</p>
<p>检测标准：',_standard,'</p>
<p>加载及测量参数：',loadingAndMeasurement,'</p>
<p>试验环境：',_environment,'</p>
</th>
</tr>');
FETCH cur_1 INTO
materialID, material, grades ,ticknessDiameter ,form_material ,batchNo ,condition_material ,
technicalState ,type_material ,dimension ,quantity_material ,specimenNumber;
END WHILE;
CLOSE cur_1;
ELSE
SET htmlOrder4 =
CONCAT(htmlOrder4,
'<tr>
<th style="width: 15%;" rowspan="5">试样信息<br>Specimen Info.</th>
<th>材料名称/牌号<br>Material/Grades</th>
<th></th>
<th>材料规格<br>Thickness or Diameter</th>
<th colspan="2"></th>
</tr>
<tr>
<th>品种<br>Form</th>
<th></th>
<th>材料炉批号<br>Heat No./Batch No.</th>
<th colspan="2"></th>
</tr>
<tr>
<th>材料热处理状态<br>Condition</th>
<th></th>
<th>技术状态<br>Technical State</th>
<th colspan="2"></th>
</tr>
<tr>
<th>试样类型<br>Type</th>
<th>试样尺寸<br>Dimension</th>
<th>试样数量<br>Quantity</th>
<th colspan="2">试样编号<br>Specimen Number</th>
</tr>
<tr>
<th></th>
<th></th>
<th></th>
<th colspan="2"></th>
</tr>
<tr>
<th rowspan="2">试验要求<br>Technical Requirements</th>
<th colspan="5">
试验及技术要求(检测项目，检测标准，加载及测量参数，试验环境等)<br>
Test and Technical Requirements (Testing Item, Standard, Loading and Measurement Parameters, Environment etc.)
</th>
</tr>
<tr>
<th style="height: 100px; text-align: left;" colspan="5">
<p>检测项目：</p>
<p>检测标准：</p>
<p>加载及测量参数：</p>
<p>试验环境：</p>
</th>
</tr>');
END IF;
-- 尾部
SET htmlOrder5 =
CONCAT(htmlOrder5,
' <tr>
<th style="text-align: left; height: 80px;" colspan="6">
<p>委托单位代表 Client:</p>
<p>签字 Signature:</p>
<p>日期 Date:</p>
</th>
</tr>
<tr>
<th style="text-align: left; height: 80px;" colspan="6">
<p>承制单位代表 Undertaking Unit:</p>
<p>签字 Signature:</p>
<p>日期 Date:</p>
</th>
</tr>
<tr>
<th>承诺<br>Promise</th>
<th style="text-align: left" colspan="5">
委托单位保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.<br>
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical information provided by the client.
</th>
</tr>
</tbody>
</table>
</body>
</html>');
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3,htmlOrder4,htmlOrder5);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
/*----------------------------------------------------------------------------------------------------------------------------------------*/
-- 2、复合材料性能检测委托单
ELSEIF orderType = 2 THEN
BEGIN
-- 委托单位Client
DECLARE client VARCHAR(255);
-- 邮寄地址
DECLARE postalAddress VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 联系方式
DECLARE contactInformation VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 试验件接收日期
DECLARE receivedOn VARCHAR(255);
-- 要求完成日期
DECLARE finishBefore VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 试验件处理
DECLARE afterTest VARCHAR(255);
-- 去报告方式
DECLARE reportPickUp VARCHAR(255);
-- 试验标准是否一致
DECLARE isTestConsistent VARCHAR(255);
-- 试验要求和标准不一致说明
DECLARE differenceRequirements VARCHAR(255);
-- 试验环境
DECLARE testEnvironment VARCHAR(255);
-- 试验件状态调节
DECLARE specimenCondition VARCHAR(255);
-- 试验件环境调节要求
DECLARE specimenConditionRequired VARCHAR(255);
-- 其他要求
DECLARE otherRequirements VARCHAR(255);
-- 材料ID
DECLARE materialID VARCHAR(255);
-- 材料牌号
DECLARE materialGrade VARCHAR(255);
-- 预浸料名称
DECLARE prepregName VARCHAR(255);
-- 夹层结构
DECLARE sandwichStructure VARCHAR(255);
-- 面板信息
DECLARE panelInformation VARCHAR(255);
-- 其他信息
DECLARE otherInformation VARCHAR(255);
-- 测试类型
DECLARE testingType VARCHAR(255);
-- 试验件数量
DECLARE specimenQuantity VARCHAR(255);
-- 试验件编号
DECLARE specimenNumber VARCHAR(255);
-- 名义尺寸
DECLARE nominalDimension VARCHAR(255);
-- 铺层顺序
DECLARE plyConfiguration VARCHAR(255);
-- 检测标准
DECLARE testingStandard VARCHAR(255);
-- 检测项目
DECLARE testingItem VARCHAR(255);
-- 判断材料表是否为空
DECLARE countMaterial INT;
-- 判断任务要求表是否为空
DECLARE countTask INT;
-- 拼接部分定义
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放材料、任务要求部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放后半部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 游标定义
DECLARE done INT DEFAULT 0;
-- 定义材料游标
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF137,''),IFNULL(SKF139,''), IFNULL(SKF145, ''), IFNULL(SKF411,''),
IFNULL(SKF412,''), IFNULL(SKF146, '') FROM SKT9
WHERE SKF362 = orderCode AND SKF524 != 1;
-- 定义任务要求表游标
DECLARE cur_2 CURSOR FOR
SELECT IFNULL(SKF169, ''),IFNULL(SKF171, ''), IFNULL(SKF172, ''), IFNULL(SKF170, ''),
IFNULL(SKF414, ''), IFNULL(SKF174, ''), IFNULL(SKF175, '')
FROM SKT10 WHERE SKF388 = materialID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 委托单主表赋值
SELECT IFNULL(SKF69,''), IFNULL(SKF87,''), IFNULL(SKF71, ''), IFNULL(SKF72 ,''),
IFNULL(SKF77, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''), IFNULL(SKF81, ''),
IFNULL(SKF82, ''), IFNULL(SKF88, ''), IFNULL(SKF75, ''), IFNULL(SKF89, ''),
IFNULL(SKF90, ''), IFNULL(SKF91, ''), IFNULL(SKF92, ''), IFNULL(SKF93, ''),
IFNULL(SKF94, '') INTO client , postalAddress , contact , contactInformation ,
taskName , taskNumber ,receivedOn ,finishBefore ,agreedDate ,afterTest ,reportPickUp ,
isTestConsistent ,differenceRequirements ,testEnvironment ,specimenCondition ,
specimenConditionRequired ,otherRequirements
FROM SKT8 WHERE SKF68 = orderCode;
-- 设置HTML前半部分
SET htmlOrder1 =
CONCAT(
'<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>复合材料（夹层结构）性能检测委托单</title>
<style type="text/css">
/*设置表格最外层边框实线*/
/*solid为实线，dashed为虚线*/
body{
font-size: 18px;
}
div{
width: 100%;
border-spacing: 0em;
margin-left: 1px;
border: 1px solid #220808;
border-collapse: collapse;
}
table{
width: 100%;
border: 0px;
}
.my_div1_1{
width: 100%;
border: 2px dashed #220808;
}
.my_td1_1{
width: 30%;
text-align: left;
}
.my_td1_2{
width: 80%;
vertical-align: top;
border-top: 0px;
border-right: 0px;
border-left: 0px;
border-bottom: 2px dashed;
text-align: left;
}
.my_td2_1{
border-bottom: 2px dashed;
border-right: 2px dashed;
border-left: 0px;
border-top: 0px;
height: 50px;
}
.my_td2_2{
border-top: 2px dashed;
border-right: 2px dashed;
border-bottom: 0px;
border-left: 0px;
height: 50px;
}
.my_td3_1{
border-right: 2px dashed;
border-left: 0px;
border-top: 0px;
border-bottom: 0px;
height: 150px;
text-align: center;
/*文字顶格*/
vertical-align: text-top;
/*添加下划线*/
text-decoration: underline;
font-size: 20px;
font-weight: 550;
}
</style>
</head>
<body>
<div style="border: 2px dashed; font-size: 9pt;">
请将表格中以“*”标记的项目填写完整后用电子邮件发送该表格，并为所提供的任何潜在有害物质提供一份MSDS/GHS。我们将在收到<br>您的样品后，向您发送电子邮件，确认收样。<br>
Please fill out the items with “*” in this form and email this form to us including an MSDS/GHS for any potentially hazardous materials<br> submitted. You will receive an emailed confirmation after your samples have been received.
</div>
<label>委托信息 Request Info:</label>
<div>
<table cellpadding="0" cellspacing="0">
<tr>
<td class="my_td1_1">*委托单位 Client:</td>
<td class="my_td1_2" style="border-right: 0px;" colspan="5">',client,'</td>
</tr>
<tr>
<td class="my_td1_1">*邮寄地址 Postal Address</td>
<td class="my_td1_2" style="border-right: 0px;" colspan="5">',postalAddress,'</td>
</tr>
<tr>
<td class="my_td1_1">*联系人 Contact:</td>
<td class="my_td1_2" style="border-right: 0px;" colspan="5">',contact,'</td>
</tr>
<tr>
<td class="my_td1_1">*联系方式 Contact information</td>
<td class="my_td1_2" style="border-bottom: 1px; border-right: 0px;" colspan="5">',contactInformation,'</td>
</tr>
<tr>
<td class="my_td2_1" style="width: 20%; border-top: 2px solid;">*任务名称 Task name</td>
<td class="my_td2_1" colspan="2" style="border-top: 2px solid;">',taskName,'</td>
<td class="my_td2_1" style="width: 20%; border-top: 2px solid;">*任务编号 Task number</td>
<td class="my_td2_1" colspan="2" style="border-right: 0px; border-top: 2px solid;">',taskNumber,'</td>
</tr>
<tr>
<td class="my_td2_1">*试验件接收日期<br>Received on</td>
<td class="my_td2_1">',receivedOn,'</td>
<td class="my_td2_1">*要求完成日期<br>Finish before</td>
<td class="my_td2_1">',finishBefore,'</td>
<td class="my_td2_1">商定完成日期<br>Agreed Date</td>
<td class="my_td2_1" style="border-right: 0px;">',agreedDate,'</td>
</tr>');
-- 拼接试验件处理部分
IF afterTest = '放弃' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="my_td2_1">*试验件后处理 After test</td>
<td class="my_td2_1"><input type="checkbox" disabled="disabled" value="放弃" checked="checked">放弃 Give up</td>
<td class="my_td2_1" colspan="2"><input type="checkbox" disabled="disabled" value="自取">自取 Self-pick up</td>
<td class="my_td2_1" colspan="2" style="border-right: 0px;"><input type="checkbox" disabled="disabled" value="邮寄">邮寄 Post</td>
</tr>');
ELSEIF afterTest = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="my_td2_1">*试验件后处理 After test</td>
<td class="my_td2_1"><input type="checkbox" disabled="disabled" value="放弃">放弃 Give up</td>
<td class="my_td2_1" colspan="2"><input type="checkbox" disabled="disabled" value="自取" checked="checked">自取 Self-pick up</td>
<td class="my_td2_1" colspan="2" style="border-right: 0px;"><input type="checkbox" disabled="disabled" value="邮寄">邮寄 Post</td>
</tr>');
ELSEIF afterTest = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="my_td2_1">*试验件后处理 After test</td>
<td class="my_td2_1"><input type="checkbox" disabled="disabled" value="放弃">放弃 Give up</td>
<td class="my_td2_1" colspan="2"><input type="checkbox" disabled="disabled" value="自取">自取 Self-pick up</td>
<td class="my_td2_1" colspan="2" style="border-right: 0px;"><input type="checkbox" disabled="disabled" value="邮寄" checked="checked">邮寄 Post</td>
</tr>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="my_td2_1">*试验件后处理 After test</td>
<td class="my_td2_1"><input type="checkbox" disabled="disabled" value="放弃">放弃 Give up</td>
<td class="my_td2_1" colspan="2"><input type="checkbox" disabled="disabled" value="自取">自取 Self-pick up</td>
<td class="my_td2_1" colspan="2" style="border-right: 0px;"><input type="checkbox" disabled="disabled" value="邮寄">邮寄 Post</td>
</tr>');
END IF;
-- 取报告方式拼接部分
IF reportPickUp = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="my_td2_1" style="border-bottom: 0px;">*取报告方式 Report Pick up</td>
<td class="my_td2_1" style="border-bottom: 0px;"><input type="checkbox" disabled="disabled" value="自取" checked="checked">自取 Self-pick up</td>
<td class="my_td2_1" style="border-bottom: 0px;" colspan="2"><input type="checkbox" disabled="disabled" value="传真">传真 Fax</td>
<td class="my_td2_1" style="border-bottom: 0px; border-right: 0px;" colspan="2"><input type="checkbox" disabled="disabled" value="邮寄">邮寄 Post</td>
</tr>
</table>
</div>');
ELSEIF reportPickUp ='传真' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="my_td2_1" style="border-bottom: 0px;">*取报告方式 Report Pick up</td>
<td class="my_td2_1" style="border-bottom: 0px;"><input type="checkbox" disabled="disabled" value="自取">自取 Self-pick up</td>
<td class="my_td2_1" style="border-bottom: 0px;" colspan="2"><input type="checkbox" disabled="disabled" value="传真" checked="checked">传真 Fax</td>
<td class="my_td2_1" style="border-bottom: 0px; border-right: 0px;" colspan="2"><input type="checkbox" disabled="disabled" value="邮寄">邮寄 Post</td>
</tr>
</table>
</div>');
ELSEIF reportPickUp = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="my_td2_1" style="border-bottom: 0px;">*取报告方式 Report Pick up</td>
<td class="my_td2_1" style="border-bottom: 0px;"><input type="checkbox" disabled="disabled" value="自取">自取 Self-pick up</td>
<td class="my_td2_1" style="border-bottom: 0px;" colspan="2"><input type="checkbox" disabled="disabled" value="传真">传真 Fax</td>
<td class="my_td2_1" style="border-bottom: 0px; border-right: 0px;" colspan="2"><input type="checkbox" disabled="disabled" value="邮寄" checked="checked">邮寄 Post</td>
</tr>
</table>
</div>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="my_td2_1" style="border-bottom: 0px;">*取报告方式 Report Pick up</td>
<td class="my_td2_1" style="border-bottom: 0px;"><input type="checkbox" disabled="disabled" value="自取">自取 Self-pick up</td>
<td class="my_td2_1" style="border-bottom: 0px;" colspan="2"><input type="checkbox" disabled="disabled" value="传真">传真 Fax</td>
<td class="my_td2_1" style="border-bottom: 0px; border-right: 0px;" colspan="2"><input type="checkbox" disabled="disabled" value="邮寄">邮寄 Post</td>
</tr>
</table>
</div>');
END IF;
-- 设置HTML试验件基本信息以及试验要求部分
-- 判断材料是否为空
SELECT COUNT(SKF137) INTO countMaterial FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
IF countMaterial > 0 THEN
OPEN cur_1;
FETCH cur_1 INTO materialID, materialGrade, prepregName, sandwichStructure,
panelInformation,otherInformation;
WHILE done != 1 DO
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<label>试验件基本信息Specimen Information</label>
<div>
<table>
<tbody>
<tr>
<td class="my_td2_1" style="width: 25%;">材料牌号 Material Grade</td>
<td class="my_td2_1" style="border-right: 0px;">',materialGrade,'</td>
</tr>
<tr>
<td class="my_td2_1">*预浸料名称（增强体/基体）<br> Prepregname (Reinforcement/Matrix)</td>
<td class="my_td2_1" style="border-right: 0px;">',prepregName,'</td>
</tr>
<tr>
<td class="my_td2_1">*夹层结构芯子、面板信息</td>
<td class="my_td2_1" style="border-right: 0px;">',sandwichStructure,',',panelInformation,'</td>
</tr>
<tr>
<td class="my_td2_1" style="border-bottom: 0px;">其他信息（other information）</td>
<td class="my_td2_1" style="border-bottom: 0px; border-right: 0px;">',otherInformation,'</td>
</tr>
</tbody>
</table>
</div>
试验要求Testing Requirements：
<div>
<table>
<tbody>
<tr>
<td class="my_td2_2" style="width: 18%; border-top: 0px;">*测试类型<br>Testing Type</td>
<td class="my_td2_2" style="width: 18%; border-top: 0px;">*试验件数量（批X组X数量）<br>Specimen Quantity<br>(Batch × Lot × Quantity)</td>
<td class="my_td2_2" style="width: 13%; border-top: 0px;">*试验件编号<br>Specimen Number:</td>
<td class="my_td2_2" style="width: 13%; border-top: 0px;">名义尺寸<br>Nominal Dimension:</td>
<td class="my_td2_2" style="width: 13%; border-top: 0px;">铺层顺序<br>Ply Configuration:</td>
<td class="my_td2_2" style="width: 13%; border-top: 0px;">*检测标准<br>Testing Standard:</td>
<td class="my_td2_2" style="width: 13%; border-top: 0px; border-right: 0px;">*检测项目<br>Testing Item</td>
</tr>');
-- 开始拼接任务要求部分
SELECT COUNT(skf168) INTO countTask FROM SKT10 WHERE SKF388 = materialID;
IF countTask > 0 THEN
SET done = 0 ;
OPEN cur_2;
FETCH cur_2 INTO testingType, specimenQuantity ,specimenNumber ,nominalDimension ,plyConfiguration ,
testingStandard ,testingItem ;
WHILE done != 1 DO
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="my_td2_2">',testingType,'</td>
<td class="my_td2_2">',specimenQuantity,'</td>
<td class="my_td2_2">',specimenNumber,'</td>
<td class="my_td2_2">',nominalDimension,'</td>
<td class="my_td2_2">',plyConfiguration,'</td>
<td class="my_td2_2">',testingStandard,'</td>
<td class="my_td2_2" style="border-right: 0px;">',testingItem,'</td>
</tr>');
FETCH cur_2 INTO testingType,specimenQuantity ,specimenNumber ,nominalDimension ,plyConfiguration ,
testingStandard ,testingItem;
END WHILE;
CLOSE cur_2;
ELSE
SET htmlOrder2 =
CONCAT(htmlOrder2,
' <tr>
<td class="my_td2_2"></td>
<td class="my_td2_2"></td>
<td class="my_td2_2"></td>
<td class="my_td2_2"></td>
<td class="my_td2_2"></td>
<td class="my_td2_2"></td>
<td class="my_td2_2" style="border-right: 0px;"></td>
</tr>');
END IF;
FETCH cur_1 INTO materialID, materialGrade, prepregName, sandwichStructure,
panelInformation,otherInformation;
END WHILE;
CLOSE cur_1;
ELSE
SET htmlOrder2 =
'<label>试验件基本信息Specimen Information</label>
<div>
<table>
<tbody>
<tr>
<td class="my_td2_1" style="width: 25%;">材料牌号 Material Grade</td>
<td class="my_td2_1" style="border-right: 0px;"></td>
</tr>
<tr>
<td class="my_td2_1">*预浸料名称（增强体/基体）<br> Prepregname (Reinforcement/Matrix)</td>
<td class="my_td2_1" style="border-right: 0px;"></td>
</tr>
<tr>
<td class="my_td2_1">*夹层结构芯子、面板信息</td>
<td class="my_td2_1" style="border-right: 0px;"></td>
</tr>
<tr>
<td class="my_td2_1" style="border-bottom: 0px;">其他信息（other information）</td>
<td class="my_td2_1" style="border-bottom: 0px; border-right: 0px;"></td>
</tr>
</tbody>
</table>
</div>
试验要求Testing Requirements：
<div>
<table>
<tbody>
<tr>
<td class="my_td2_2" style="width: 18%; border-top: 0px;">*测试类型Testing Type</td>
<td class="my_td2_2" style="width: 18%; border-top: 0px;">*试验件数量（批X组X数量）<br>Specimen Quantity<br>(Batch × Lot × Quantity)</td>
<td class="my_td2_2" style="width: 13%; border-top: 0px;">*试验件编号<br>Specimen Number:</td>
<td class="my_td2_2" style="width: 13%; border-top: 0px;">名义尺寸<br>Nominal Dimension:</td>
<td class="my_td2_2" style="width: 13%; border-top: 0px;">铺层顺序<br>Ply Configuration:</td>
<td class="my_td2_2" style="width: 13%; border-top: 0px;">*检测标准<br>Testing Standard:</td>
<td class="my_td2_2" style="width: 13%; border-top: 0px; border-right: 0px;">*检测项目<br>Testing Item</td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2"></td>
<td class="my_td2_2"></td>
<td class="my_td2_2"></td>
<td class="my_td2_2"></td>
<td class="my_td2_2"></td>
<td class="my_td2_2" style="border-right: 0px;"></td>
</tr>';
END IF;
-- HTML后半部分拼接
-- 获取
IF isTestConsistent = '是' THEN
SET htmlOrder3 =
'<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" style="border-right: 0px;" colspan="5">备注：1、试验件如果需要粘贴加强片，请另外填写《复合材料试验件制备委托单》。
<br>
Notes：If tabbing was required, please fill in “Composite Specimen Preparation Order” additionally.
</td>
<td class="my_td2_2" style="border-right: 0px;"></td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" colspan="5">*试验要求是否和标准一致？ *Are the testing requirements consistent with the specified standards?</td>
<td class="my_td2_2" style="border-right: 0px;">
<table width="100%" height="100%" border="0" cellspacing="0" cellpadding="0">
<td style="border-right: 2px dashed;"><input type="checkbox" disabled="disabled" value="是" checked="checked">是</td>
<td><input type="checkbox" disabled="disabled" value="否">否</td>
</table>
</td>
</tr>';
ELSEIF isTestConsistent = '否' THEN
SET htmlOrder3 =
'<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" style="border-right: 0px;" colspan="5">备注：1、试验件如果需要粘贴加强片，请另外填写《复合材料试验件制备委托单》。
<br>
Notes：If tabbing was required, please fill in “Composite Specimen Preparation Order” additionally.
</td>
<td class="my_td2_2" style="border-right: 0px;"></td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" colspan="5">*试验要求是否和标准一致？ *Are the testing requirements consistent with the specified standards?</td>
<td class="my_td2_2" style="border-right: 0px;">
<table width="100%" height="100%" border="0" cellspacing="0" cellpadding="0">
<td style="border-right: 2px dashed;"><input type="checkbox" disabled="disabled" value="是">是</td>
<td><input type="checkbox" disabled="disabled" value="否" checked="checked">否</td>
</table>
</td>
</tr>';
ELSE
SET htmlOrder3 =
'<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" style="border-right: 0px;" colspan="5">备注：1、试验件如果需要粘贴加强片，请另外填写《复合材料试验件制备委托单》。
<br>
Notes：If tabbing was required, please fill in “Composite Specimen Preparation Order” additionally.
</td>
<td class="my_td2_2" style="border-right: 0px;"></td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" colspan="5">*试验要求是否和标准一致？ *Are the testing requirements consistent with the specified standards?</td>
<td class="my_td2_2" style="border-right: 0px;">
<table width="100%" height="100%" border="0" cellspacing="0" cellpadding="0">
<td style="border-right: 2px dashed;"><input type="checkbox" disabled="disabled" value="是">是</td>
<td><input type="checkbox" disabled="disabled" value="否">否</td>
</table>
</td>
</tr>';
END IF;
IF specimenCondition = '是' THEN
SET htmlOrder3 =
CONCAT(htmlOrder3,
' <tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" colspan="6" style="border-right: 0px;">*试验要求和标准不一致的说明*Please indicate the difference between the testing requirements and the specified standards.<br>ces </td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2">*试验环境 Testing environment</td>
<td class="my_td2_2" style="border-right: 0px;" colspan="5">',testEnvironment,'</td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2">*试验件状态调节 Specimen Condition</td>
<td class="my_td2_2" style="border-right: 0px;" colspan="5">
1、试验件是否需要进行状态调节？If specimen condition was required？
<input type="checkbox" disabled="disabled" value="是" checked="checked"> 是
<input type="checkbox" disabled="disabled" value="否"> 否
<br>
2、如果需要进行试验件状态调节，明确试验件状态调节的环境要求。Please indicate the environments if specimen condition was required.<br>
</td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" style="border-bottom: 0px;">其他要求Other Requirements</td>
<td class="my_td2_2" style="border-right: 0px; border-bottom: 0px;" colspan="5">',otherRequirements,'</td>
</tr>
</tbody>
</table>
</div>
<br>');
ELSEIF specimenCondition = '否' THEN
SET htmlOrder3 =
CONCAT(htmlOrder3,
' <tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" colspan="6" style="border-right: 0px;">*试验要求和标准不一致的说明*Please indicate the difference between the testing requirements and the specified standards.<br>ces </td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2">*试验环境 Testing environment</td>
<td class="my_td2_2" style="border-right: 0px;" colspan="5">',testEnvironment,'</td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2">*试验件状态调节 Specimen Condition</td>
<td class="my_td2_2" style="border-right: 0px;" colspan="5">
1、试验件是否需要进行状态调节？If specimen condition was required？
<input type="checkbox" disabled="disabled" value="是"> 是
<input type="checkbox" disabled="disabled" value="否" checked="checked"> 否
<br>
2、如果需要进行试验件状态调节，明确试验件状态调节的环境要求。Please indicate the environments if specimen condition was required.
</td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" style="border-bottom: 0px;">其他要求Other Requirements</td>
<td class="my_td2_2" style="border-right: 0px; border-bottom: 0px;" colspan="5">',otherRequirements,'</td>
</tr>
</tbody>
</table>
</div>
<br>');
ELSE
SET htmlOrder3 =
CONCAT(htmlOrder3,
' <tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" colspan="6" style="border-right: 0px;">*试验要求和标准不一致的说明*Please indicate the difference between the testing requirements and the specified standards.<br>ces </td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2">*试验环境 Testing environment</td>
<td class="my_td2_2" style="border-right: 0px;" colspan="5">',testEnvironment,'</td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2">*试验件状态调节 Specimen Condition</td>
<td class="my_td2_2" style="border-right: 0px;" colspan="5">
1、试验件是否需要进行状态调节？If specimen condition was required？
<input type="checkbox" disabled="disabled" value="是"> 是
<input type="checkbox" disabled="disabled" value="否"> 否
<br>
2、如果需要进行试验件状态调节，明确试验件状态调节的环境要求。Please indicate the environments if specimen condition was required.
</td>
</tr>
<tr>
<td class="my_td2_2"></td>
<td class="my_td2_2" style="border-bottom: 0px;">其他要求Other Requirements</td>
<td class="my_td2_2" style="border-right: 0px; border-bottom: 0px;" colspan="5">',otherRequirements,'</td>
</tr>
</tbody>
</table>
</div>
<br>');
END IF;
SET htmlOrder3 =
CONCAT(htmlOrder3,
' <div class="my_div1_1">
<p>
委托方保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.<br>
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical information <br>provided by the client.
</p>
</div>
<br>
<div>
<table>
<tbody>
<tr>
<td class="my_td3_1">委托方代表 - Client Signature / Date:</td>
<td class="my_td3_1" style="border-right: 0px;">承接方代表 - SAMST Signature / Date:</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>');
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
/*----------------------------------------------------------------------------------------------------------------------------------------*/
-- 3、金属加工委托单
ELSEIF orderType = 3 THEN
BEGIN
-- 委托单主表部分
-- 委托单位
DECLARE CLIENT VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 地址
DECLARE address VARCHAR(255);
-- 联系方式
DECLARE contactNumber VARCHAR(255);
-- 余料处理
DECLARE surplusHandling VARCHAR(255);
-- 余料处理邮寄地址
DECLARE surplusPostalAddress VARCHAR(255);
-- 产品处理
DECLARE productHandling VARCHAR(255);
-- 产品处理邮寄地址
DECLARE productPostalAddress VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收料日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE requiredDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 备注
DECLARE note VARCHAR(255);
-- 材料样品表部分
-- 序号
DECLARE no_material INT DEFAULT 0;
-- 名称
DECLARE name_material VARCHAR(255);
-- 品种
DECLARE variety VARCHAR(255);
-- 规格
DECLARE specification VARCHAR(255);
-- 尺寸
DECLARE DIMENSION VARCHAR(255);
-- 热处理要求
DECLARE heatTreatment VARCHAR(255);
-- 数量
DECLARE quantity_material INT;
-- 工作内容表内容部分
-- 序号
DECLARE no_task INT DEFAULT 0;
-- 获取任务的ID
DECLARE id_task INT;
-- 产品
DECLARE product VARCHAR(255);
-- 数量
DECLARE quantity_task INT;
-- 下料图纸名称
DECLARE samplingDrawingName VARCHAR(255);
-- 下料图纸图号
DECLARE samplingDrawingNumber VARCHAR(255);
-- 加工图纸图号
DECLARE machiningDrawingNumber VARCHAR(255);
-- 编号要求
DECLARE numberingRequirement VARCHAR(255);
-- 存储过程存放变量部分
-- 获得材料的总个数
DECLARE sumMaterial INT;
-- 获得工作内容的总个数
DECLARE sumTask INT;
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放HTML余料处理部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放HTML产品处理部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 存放HTML拼接的订单后半部分
DECLARE htmlOrder4 TEXT DEFAULT '';
-- 存放HTM
DECLARE htmlOrder5 TEXT DEFAULT '';
-- 存放HTML拼接的材料样品部分
DECLARE htmlMaterial TEXT DEFAULT '';
-- 存放HTML拼接的材料样品子信息部分;
DECLARE htmlMaterial1 TEXT DEFAULT '';
-- 存放HTML拼接的工作内容部分
DECLARE htmlTask TEXT DEFAULT '';
-- 存放HTML拼接的工作内容子信息部分
DECLARE htmlTask1 TEXT DEFAULT '';
-- 游标部分
-- 循环游标判定变量
DECLARE done INT DEFAULT 0;
-- 材料样品表游标部分
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF138, ''), IFNULL(SKF141, ''), IFNULL(SKF140, ''), IFNULL(SKF148, ''),
IFNULL(SKF149, ''), IFNULL(SKF150, '') FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
-- 工作内容表游标部分
DECLARE cur_2 CURSOR FOR
SELECT SKF207, SKF208, SKF209, SKF210, SKF211, SKF212, SKF213
FROM SKT12 WHERE SKF391 = orderCode;
-- 主子游标循环判断,指定循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 获取报告的基础信息
SELECT IFNULL(SKF69, ''), IFNULL(SKF71, ''), IFNULL(SKF70, ''), IFNULL(SKF72, ''),
IFNULL(SKF95, ''), IFNULL(SKF96, ''), IFNULL(SKF97, ''), IFNULL(SKF98, ''),
IFNULL(SKF77, ''), IFNULL(SKF78, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''),
IFNULL(SKF81, ''), IFNULL(SKF82, ''), IFNULL(SKF220, '') INTO
CLIENT, contact, address, contactNumber, surplusHandling, surplusPostalAddress,
productHandling, productPostalAddress, taskName, projectNumber, taskNumber,
receivedDate, requiredDate, agreedDate, note FROM SKT8 WHERE SKF68 = orderCode;
-- 设置HTML订单前半部分
SET htmlOrder1 =
CONCAT(
'<!doctype html>
<html>
<head>
<meta charset="gb2312">
<title>金属加工任务委托单</title>
<style type="text/css">
body,td,th,h6 {
font-family: Verdana,
Arial, Helvetica,
sans-serif;
font-size: 18px;
color: #1d1007;
line-height:14px;
font-weight: 700;}
</style>
</head>
<body>
<table width="100%" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th style="width: 15%; height: 40px; text-align: center;">委托单位<br>Client</th>
<th style="width: 35%; text-align: center;" colspan="3">',Client,'</th>
<th style="width: 15%; text-align: center;" colspan="2">联系人<br>Contact</th>
<th style="text-align: center;">',Contact,'</th>
</tr>
<tr>
<th style="width: 15%; height: 40px; text-align: center;">地址<br>Address</th>
<th style="width: 35%; text-align: center" colspan="3">',address,'</th>
<th style="width: 15%; text-align: center;" colspan="2">联系方式<br>Contact Number</th>
<th style="text-align: center;">',contactNumber,'</th>
</tr>');
-- 余料处理打勾部分
IF surplusHandling = '委托处理' THEN
SET htmlOrder2 =
'<tr>
<th style="width: 15%; height: 40px; text-align: center;">余料处理<br>Handling of<br>Surplus Materials</th>
<th style="text-align: center" colspan="6">
<table width="85%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="委托处理" checked="checked">委托处理<br>Commissioned Handling</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮件">邮件(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</tr>';
ELSEIF surplusHandling = '自取' THEN
SET htmlOrder2 =
'<tr>
<th style="width: 15%; height: 40px; text-align: center;">余料处理<br>Handling of<br>Surplus Materials</th>
<th style="text-align: center" colspan="6">
<table width="85%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="委托处理">委托处理<br>Commissioned Handling</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取" checked="checked">自取<br>Self-pick up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮件">邮件(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</tr>';
ELSEIF surplusHandling = '邮件' THEN
SET htmlOrder2 =
CONCAT(
'<tr>
<th style="width: 15%; height: 40px; text-align: center;">余料处理<br>Handling of<br>Surplus Materials</th>
<th style="text-align: center" colspan="6">
<table width="85%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="委托处理" checked="checked">委托处理<br>Commissioned Handling</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮件" checked="checked">邮件(邮寄地址):',surplusPostalAddress,'<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</tr>');
ELSE
SET htmlOrder2 =
'<tr>
<th style="width: 15%; height: 40px; text-align: center;">余料处理<br>Handling of<br>Surplus Materials</th>
<th style="text-align: center" colspan="6">
<table width="85%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="委托处理">委托处理<br>Commissioned Handling</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮件">邮件(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</tr>';
END IF;
-- 产品处理打勾部分
IF productHandling = '自取' THEN
SET htmlOrder3 =
'<tr>
<th style="width: 15%; height: 40px; text-align: center;">产品处理<br>Product Handling</th>
<th style="text-align: center" colspan="6">
<table width="85%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取" checked="checked">自取<br>Self-pick-up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮寄">邮寄(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr>';
ELSEIF productHandling = '邮寄' THEN
SET htmlOrder3 =
CONCAT(
'<tr>
<th style="width: 15%; height: 40px; text-align: center;">产品处理<br>Product Handling</th>
<th style="text-align: center" colspan="6">
<table width="85%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick-up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮寄" checked="checked">邮寄(邮寄地址):',productPostalAddress,'<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr>');
-- 若为空，或者值不对，只显示表格信息
ELSE
SET htmlOrder3 =
'<tr>
<th style="width: 15%; height: 40px; text-align: center;">产品处理<br>Product Handling</th>
<th style="text-align: center" colspan="6">
<table width="85%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="自取">自取<br>Self-pick-up</p></th>
<th scope="col"><p><input type="checkbox" disabled="disabled" value="邮寄">邮寄(邮寄地址):<br>Post(Postal Address)</p></th>
</tr>
</tbody>
</table>
</th>
</tr>';
END IF;
-- 任务信息等
SET htmlOrder4 =
CONCAT(
'<tr>
<th style="width: 15%; height: 40px; text-align: center;">任务名称<br>Task Name</th>
<th style="width: 17%; text-align: center;">',taskName,'</th>
<th style="width: 17%; text-align: center;">项目令号<br>Project Number</th>
<th style="width: 17%; text-align: center;" colspan="2">',projectNumber,'</th>
<th style="width: 17%; text-align: center;">任务编号<br>Task Number</th>
<th style="text-align: center;">',taskNumber,'</th>
</tr>
<tr>
<th style="width: 15%; height: 40px; text-align: center;">收料日期<br>Received Date</th>
<th style="width: 17%; text-align: center;">',receivedDate,'</th>
<th style="width: 17%; text-align: center;">要求完成日期<br>Required Date</th>
<th style="width: 17%; text-align: center;" colspan="2">',requiredDate,'</th>
<th style="width: 17%; text-align: center;">商定完成日期<br>Agreed Date</th>
<th style="text-align: center;" >',agreedDate,'</th>
</tr>');
-- 材料样品部分拼接
-- 获取材料样品的总数
SELECT COUNT(SKF138) INTO sumMaterial FROM SKT9
WHERE SKF362 = orderCode AND SKF524 != 1;
-- 初始化HTML材料样品
-- 若材料样品是否为空
IF sumMaterial != 0 THEN
SET htmlMaterial =
CONCAT(
'<tr>
<!--需要判断材料的数量，若材料种类数量为1则，rowspan="2"-->
<th style="width: 15%; height: 40px; text-align: center;" rowspan="',sumMaterial+1,'">材料<br>Material</th>
<th style="width: 10%; text-align: center;">序号<br>No.</th>
<th style="width: 15%; text-align: center;">名称<br>Name</th>
<th style="width: 20%; text-align: center;" colspan="2">品种/规格/尺寸<br>Variety/Specification/Dimension</th>
<th style="width: 20%; text-align: center;">热处理要求<br>Heat Treatment Requirements</th>
<th style="text-align: center;">数量<br>Quantity</th>
</tr>');
ELSE
SET htmlMaterial =
CONCAT(
'<!--材料信息部分-->
<table width="100%" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<!--需要判断材料的数量，若材料种类数量为1则，rowspan="2"-->
<th style="width: 15%; height: 40px; text-align: center;" rowspan="',2 , '">材料<br>Material</th>
<th style="width: 10%; text-align: center;">序号<br>No.</th>
<th style="width: 15%; text-align: center;">名称<br>Name</th>
<th style="width: 20%; text-align: center;">品种/规格/尺寸<br>Variety/Specification/Dimension</th>
<th style="width: 20%; text-align: center;">热处理要求<br>Heat Treatment Requirements</th>
<th style="text-align: center;">数量<br>Quantity</th>
</tr>');
END IF;
-- 开启材料游标
OPEN cur_1;
WHILE done != 1 DO
FETCH cur_1 INTO name_material, variety, specification,
DIMENSION, heatTreatment, quantity_material;
-- 设置序号加 1
SET no_material = no_material + 1;
-- 设置HTML材料样品子信息部分
IF no_material <= sumMaterial THEN
SET htmlMaterial1 =
CONCAT(htmlMaterial1,
'<tr>
<th style="height: 40px; text-align: center;">', no_material, '</th>
<th style="height: 40px; text-align: center;">', name_material, '</th>
<th style="height: 40px; text-align: center;" colspan="2">',variety, ' /', specification, ' /', DIMENSION,'</th>
<th style="height: 40px; text-align: center;">', heatTreatment, '</th>
<th style="height: 40px; text-align: center;">', quantity_material, '</th>
</tr>'
);
END IF;
END WHILE;
-- 若材料总数为0，则新增一行空数据
IF sumMaterial = 0 THEN
SET htmlMaterial1 =
'<tr>
<th style="height: 40px; text-align: center;"></th>
<th style="height: 40px; text-align: center;"></th>
<th style="height: 40px; text-align: center;" colspan="2"></th>
<th style="height: 40px; text-align: center;"></th>
<th style="height: 40px; text-align: center;"></th>
</tr>';
END IF;
-- 开始拼接材料部分
SET htmlMaterial = CONCAT(htmlMaterial, htmlMaterial1);
-- 关闭材料游标
CLOSE cur_1;
-- 回复done默认值
SET done = 0;
-- 工作内容部分拼接
-- 判定工作内容的行数
SELECT COUNT(SKF208) INTO sumTask FROM SKT12
WHERE SKF391 = orderCode;
-- 初始化HTML工作内容表
IF sumTask != 0 THEN
SET htmlTask =
CONCAT(
'<tr>
<th style="width: 15%; height: 40px; text-align: center;" rowspan="', sumTask + 1, '">工作内容<br>Task</th>
<th style="width: 10%; text-align: center;">序号<br>No.</th>
<th style="width: 10%; text-align: center;">产品<br>Product</th>
<th style="width: 10%; text-align: center;">数量<br>Quantity</th>
<th style="width: 10%; text-align: center;">下料图纸<br>名称/图号<br>Name/Numberof<br>Sampling Drawing</th>
<th style="width: 10%; text-align: center;">加工图纸<br>名称/图号<br>Name/Number<br>ofMaching<br>Drawing</th>
<th style="width: 10%; text-align: center;">编号要求<br>Numbering<br>Requirement</th>
</tr>');
ELSE
SET htmlTask =
CONCAT(
'<tr>
<th style="width: 15%; height: 40px; text-align: center;" rowspan="2">工作内容<br>Task</th>
<th style="width: 10%; text-align: center;">序号<br>No.</th>
<th style="width: 10%; text-align: center;">产品<br>Product</th>
<th style="width: 10%; text-align: center;">数量<br>Quantity</th>
<th style="width: 10%; text-align: center;">下料图纸<br>名称/图号<br>Name/Numberof<br>Sampling Drawing</th>
<th style="width: 10%; text-align: center;">加工图纸<br>名称/图号<br>Name/Number<br>ofMaching<br>Drawing</th>
<th style="width: 10%; text-align: center;">编号要求<br>Numbering<br>Requirement</th>
</tr>');
END IF;
IF sumTask != 0 THEN
-- 开启工作内容表游标
OPEN cur_2;
WHILE done != 1 DO
FETCH cur_2 INTO
id_task, product, quantity_task, samplingDrawingName, samplingDrawingNumber,
machiningDrawingNumber, numberingRequirement;
-- 设置序号加1
SET no_task = no_task + 1;
IF no_task <= sumTask THEN
-- 生成工作内容表的基础信息
SET htmlTask1 =
CONCAT(htmlTask1,
'<tr>
<th style="height: 40px; text-align: center;">', no_task, '</th>
<th style="height: 40px; text-align: center;">', product, '</th>
<th style="height: 40px; text-align: center;">', quantity_task, '</th>
<th style="height: 40px; text-align: center;">', samplingDrawingName ,'/',samplingDrawingNumber, '</th>
<th style="height: 40px; text-align: center;">', machiningDrawingNumber, '</th>
<th style="height: 40px; text-align: center;">', numberingRequirement, '</th>
</tr>');
END IF;
END WHILE;
-- 关闭工作内容表游标
CLOSE cur_2;
ELSE
-- 如果工作内容为0，则添加一行空数据
SET htmlTask1 =
'<tr>
<th style="height: 40px; text-align: center;"></th>
<th style="height: 40px; text-align: center;"></th>
<th style="height: 40px; text-align: center;"></th>
<th style="height: 40px; text-align: center;"></th>
<th style="height: 40px; text-align: center;"></th>
<th style="height: 40px; text-align: center;"></th>
</tr>';
END IF;
-- 开始拼接工作内容
SET htmlTask = CONCAT(htmlTask,htmlTask1);
SET htmlOrder5 =
CONCAT(
' <tr>
<th style="width: 15%; height: 80px; text-align: center;">备注<br>Note</th>
<th colspan="6">',note,'</th>
</tr>
<tr>
<th style="text-align: left; height: 80px;" colspan="7">
<p>委托单位代表 Client:</p>
<p>签字 Signature:</p>
<p>日期 Date:</p>
</th>
</tr>
<tr>
<th style="text-align: left; height: 80px;" colspan="7">
<p>承制单位代表 Undertaking Unit:</p>
<p>签字 Signature:</p>
<p>日期 Date:</p>
</th>
</tr>
</tbody>
</table>
</body>
</html>');
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3,htmlOrder4,htmlMaterial,htmlTask,htmlOrder5);
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
END IF;
COMMIT;
END;
/*----------------------------------------------------------------------------------------------------------------------------------------*/
-- 4、尺寸检测委托单
ELSEIF orderType = 4 THEN
BEGIN
-- 委托单位Client
DECLARE client VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 单位地址
DECLARE unitAddress VARCHAR(255);
-- 联系方式
DECLARE contactInformation VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 试样材料
DECLARE sampleMaterial VARCHAR(255);
-- 收样日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE finishDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 试样序号
DECLARE sampleNo INT DEFAULT 1;
-- 判断试样是否存在
DECLARE isSampleExist INT;
-- 试样类型
DECLARE sampleType VARCHAR(255);
-- 试样数量
DECLARE sampleQuantity VARCHAR(255);
-- 图纸
DECLARE drawings VARCHAR(255);
-- 试样编号
DECLARE sampleNumber VARCHAR(255);
-- 拼接部分定义
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放材料、任务要求部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放后半部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 任务要求表游标
DECLARE done INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF169, ''),IFNULL(SKF171, ''),IFNULL(SKF176, ''),
IFNULL(SKF172, '') FROM SKT10 WHERE SKF386 = orderCode;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 委托单主表信息赋值
SELECT IFNULL(SKF69, ''), IFNULL(SKF71, ''), IFNULL(SKF70, ''),
IFNULL(SKF72, ''), IFNULL(SKF78, ''), IFNULL(SKF99, ''),
IFNULL(SKF80, ''), IFNULL(SKF81, ''), IFNULL(SKF82, '') INTO
client, contact, unitAddress, contactInformation,
projectNumber, sampleMaterial, receivedDate, finishDate,
agreedDate
FROM SKT8 WHERE SKF68 = orderCode;
-- HTML拼接前半部分
SET htmlOrder1 =
CONCAT(
'<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>尺寸检测委托单</title>
<style type="text/css">
body {
font-size: 18px;
}
table {
width: 100%;
border: 1px;
}
td {
height: 50px;
}
.td_center{
text-align: center;
}
</style>
</head>
<body>
<!-- cellpadding="0" cellspacing="0" 用于解决表格中的空袭 -->
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">委托单位</td>
<td colspan="4" >',client,'</td>
<td colspan="2">联系人</td>
<td colspan="2">',contact,'</td>
</tr>
<tr>
<td>地址</td>
<td colspan="4">',unitAddress,'</td>
<td colspan="2">联系方式</td>
<td colspan="2">',contactInformation,'</td>
</tr>
<tr>
<td>项目令号</td>
<td colspan="4">',projectNumber,'</td>
<td colspan="2">试样材料</td>
<td colspan="2">',sampleMaterial,'</td>
</tr>
<tr>
<td width="20%">收样日期</td>
<td>',receivedDate,'</td>
<td>要求完成日期</td>
<td colspan="3">',finishDate,'</td>
<td colspan="2">商定完成日期</td>
<td>',agreedDate,'</td>
</tr>');
-- 开始拼接任务要求部分
-- 获取当前任务要求数量
SELECT COUNT(SKF168) INTO isSampleExist FROM SKT10 WHERE SKF386 = orderCode;
IF isSampleExist > 0 THEN
OPEN cur_1;
FETCH cur_1 INTO sampleType, sampleQuantity, drawings, sampleNumber;
WHILE done != 1 DO
IF sampleNo = 1 THEN
SET htmlOrder2 =
CONCAT(
'<tr>
<td style="width: 20%;" rowspan="',isSampleExist + 3,'">任务要求</td>
<td>',sampleNo,'.试样类型</td>
<td>',sampleType,'</td>
<td>试样数量</td>
<td>',sampleQuantity,'</td>
<td>图纸</td>
<td>',drawings,'</td>
<td>试样编号</td>
<td style="text-align: center;">',sampleNumber,'</td>
</tr> ');
ELSE
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td>',sampleNo,'.试样类型</td>
<td>',sampleType,'</td>
<td>试样数量</td>
<td>',sampleQuantity,'</td>
<td>图纸</td>
<td>',drawings,'</td>
<td>试样编号</td>
<td style="text-align: center;">',sampleNumber,'</td>
</tr> ');
END IF;
FETCH cur_1 INTO sampleType, sampleQuantity, drawings, sampleNumber;
SET sampleNo = sampleNo + 1;
END WHILE;
CLOSE cur_1;
ELSE
SET htmlOrder2 =
'<tr>
<td style="width: 20%;" rowspan="3">任务要求</td>
<td>1.试样类型</td>
<td></td>
<td>试样数量</td>
<td></td>
<td>图纸</td>
<td></td>
<td>试样编号</td>
<td style="text-align: center;"></td>
</tr>';
END IF;
-- 任务要求选择框部分
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="td_center">
实际尺寸<br><input type="checkbox" disabled="disabled" name="实际尺寸" value="实际尺寸">
</td>
<td class="td_center">
表面状态<br><input type="checkbox" disabled="disabled" name="表面状态" value="表面状态">
</td>
<td class="td_center" colspan="2">
表面粗糙度<br><input type="checkbox" disabled="disabled" name="表面粗糙度" value="表面粗糙度">
</td>
<td class="td_center" colspan="2">
无损探伤<br><input type="checkbox" disabled="disabled" name="无损探伤" value="无损探伤">
</td>
<td class="td_center">
平行度<br><input type="checkbox" disabled="disabled" name="平行度" value="平行度">
</td>
<td class="td_rigth" style="text-align: center;">
垂直度<br><input type="checkbox" disabled="disabled" name="垂直度" value="垂直度">
</td>
</tr>
<tr>
<td style="text-align: center;">
同轴度<br><input type="checkbox" disabled="disabled" name="同轴度" value="同轴度">
</td>
<td></td>
<td colspan="2"></td>
<td colspan="2"></td>
<td></td>
<td></td>
</tr>');
-- HTML拼接后半部分
SET htmlOrder3 =
' <tr>
<td colspan="9">
委托单代表<br>
签字：<br>
日期：
</td>
</tr>
<tr>
<td colspan="9">
承制单位代表<br>
签字：<br>
日期：
</td>
</tr>
<tr>
<td>承诺<br>Promise</td>
<td colspan="8">
委托单位保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.<br>
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical <br>
information provided by the client.
</td>
</tr>
</tbody>
</table>
</body>
</html>';
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
/*----------------------------------------------------------------------------------------------------------------------------------------*/
-- 5、复合材料加工委托单
ELSEIF orderType = 5 THEN
BEGIN
-- 委托单主表部分
-- 委托单位
DECLARE client VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 地址
DECLARE address VARCHAR(255);
-- 联系方式
DECLARE contactNumber VARCHAR(255);
-- 余料处理
DECLARE surplusHandling VARCHAR(255);
-- 余料处理邮寄地址
DECLARE surplusPostalAddress VARCHAR(255);
-- 产品处理
DECLARE productHandling VARCHAR(255);
-- 产品处理邮寄地址
DECLARE productPostalAddress VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收料日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE requiredDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 备注
DECLARE note VARCHAR(255);
-- 加工方法
DECLARE machiningMethod VARCHAR(255);
-- 材料样品表部分
-- 序号
DECLARE no_material INT DEFAULT 0;
-- 名称
DECLARE name_material VARCHAR(255);
-- 材料类型
DECLARE type_material VARCHAR(255);
-- 试板品种
DECLARE variety VARCHAR(255);
-- 规格
DECLARE specification VARCHAR(255);
-- 尺寸
DECLARE dimension VARCHAR(255);
-- 铺层顺序
DECLARE plyConfiguration VARCHAR(255);
-- 数量
DECLARE quantity_material VARCHAR(255);
-- 有无加工基线
DECLARE isDatumLine VARCHAR(255);
-- 无基边的处理方法
DECLARE treatmentMethods VARCHAR(255);
-- 是否粘贴加强片
DECLARE isSpecimenTabs VARCHAR(255);
-- 试验件固化温度
DECLARE specimenTemperature VARCHAR(255);
-- 加强片材料
DECLARE tabsMaterials VARCHAR(255);
-- 加强片图纸名称
DECLARE tabsDrawing VARCHAR(255);
-- 粘接剂名
DECLARE adhesivesName VARCHAR(55);
-- 固化温度
DECLARE curingTemperature VARCHAR(255);
-- 工作内容表内容部分
-- 序号
DECLARE no_task INT DEFAULT 0;
-- 产品
DECLARE product VARCHAR(255);
-- 数量
DECLARE quantity_task VARCHAR(255);
-- 下料图纸名称
DECLARE samplingDrawingName VARCHAR(255);
-- 下料图纸图号
DECLARE samplingDrawingNumber VARCHAR(255);
-- 加工图纸图号
DECLARE machiningDrawingNumber VARCHAR(255);
-- 编号要求
DECLARE numberingRequirement VARCHAR(255);
-- 存储过程存放变量部分
-- 获得材料的总个数
DECLARE sumMaterial INT;
-- 获得工作内容的总个数
DECLARE sumTask INT;
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放HTML余料处理与任务产品处理部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放HTML任务名称部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 存放材料部分
DECLARE htmlOrder4 TEXT DEFAULT '';
-- 存放工作内容部分
DECLARE htmlOrder5 TEXT DEFAULT '';
-- 存放HTML表格备注到签字部分
DECLARE htmlOrder6 TEXT DEFAULT '';
-- 游标部分
-- 循环游标判定变量
DECLARE done INT DEFAULT 0;
-- 工作内容表游标部分
DECLARE cur_2 CURSOR FOR
SELECT SKF208, SKF209, SKF210, SKF211, SKF212, SKF213
FROM SKT12 WHERE SKF391 = orderCode;
-- 主子游标循环判断,指定循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 获取报告的基础信息
SELECT IFNULL(SKF69, ''), IFNULL(SKF71, ''), IFNULL(SKF70, ''), IFNULL(SKF72, ''),
IFNULL(SKF95, ''), IFNULL(SKF96, ''), IFNULL(SKF97, ''), IFNULL(SKF98, ''),
IFNULL(SKF77, ''), IFNULL(SKF78, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''),
IFNULL(SKF81, ''), IFNULL(SKF82, ''), IFNULL(SKF220, '') ,IFNULL(SKF111, '')INTO
client, contact, address, contactNumber, surplusHandling, surplusPostalAddress,
productHandling, productPostalAddress, taskName, projectNumber, taskNumber,
receivedDate, requiredDate, agreedDate, note, machiningMethod
FROM SKT8 WHERE SKF68 = orderCode;
-- 设置HTML订单前半部分
SET htmlOrder1 =
CONCAT(
'<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>复合材料加工委托单</title>
<style type="text/css">
div{
border: 1px solid;
width: 100%
}
body{
font-size: 18px;
}
td{
height: 45px;
}
table{
width: 100%
border:0px;
}
.td_border1{
border-bottom: 1px solid;
}
</style>
</head>
<body>
委托单信息 Request Info
<div>
<table>
<tbody>
<tr>
<td width="25%">委托单位 Client:</td>
<td width="25%">',client,'</td>
<td width="25%">联系人 Contact:</td>
<td>',Contact,'</td>
</tr>
<tr>
<td>地址 Address:</td>
<td>',address,'</td>
<td>联系方式 Contact Number:</td>
<td>',contactNumber,'</td>
</tr>
</tbody>
</table>
<br>
</div>');
-- 产品处理打勾部分
IF productHandling = '自取' THEN
SET htmlOrder2 =
'<br>
<div>
<table>
<tbody>
<tr>
<td>
产品处理 Product Handling
</td>
<td>
<input type="checkbox" disabled="disabled" name="自取 Self-pick up" checked="checked">自取 Self-pick up
</td>
<td>
<input type="checkbox" disabled="disabled" name="自取 Self-pick up">邮寄（邮寄地址）:Post (Postal Address)
</td>
</tr>';
ELSEIF productHandling = '邮寄' THEN
SET htmlOrder2 =
'<br>
<div>
<table>
<tbody>
<tr>
<td>
产品处理 Product Handling
</td>
<td>
<input type="checkbox" disabled="disabled" name="自取 Self-pick up">自取 Self-pick up
</td>
<td>
<input type="checkbox" disabled="disabled" name="自取 Self-pick up" checked="checked">邮寄（邮寄地址）:Post (Postal Address)
</td>
</tr>';
-- 若为空，或者值不对，只显示表格信息
ELSE
SET htmlOrder2 =
'<br>
<div>
<table>
<tbody>
<tr>
<td>
产品处理 Product Handling
</td>
<td>
<input type="checkbox" disabled="disabled" name="自取 Self-pick up">自取 Self-pick up
</td>
<td>
<input type="checkbox" disabled="disabled" name="自取 Self-pick up">邮寄（邮寄地址）:Post (Postal Address)
</td>
</tr>';
END IF;
-- 余料处理打勾部分
IF surplusHandling = '委托处理' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td rowspan="2">余料处理 Handling of Surplus Materials</td>
<td>
<input type="checkbox" disabled="disabled" name="委托处理 Commissioned Handling">委托处理 Commissioned Handling
</td>
<td>
<input type="checkbox" disabled="disabled" name="自取 Self-pick up">自取 Self-pick up
</td>
</tr>
<tr>
<td colspan="2">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）Post (Postal Address)：">邮寄（邮寄地址）Post (Postal Address)：
</td>
</tr>
</tbody>
</table>
</div>');
ELSEIF surplusHandling = '自取' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
' <tr>
<td rowspan="2">余料处理 Handling of Surplus Materials</td>
<td>
<input type="checkbox" disabled="disabled" name="委托处理 Commissioned Handling">委托处理 Commissioned Handling
</td>
<td>
<input type="checkbox" disabled="disabled" name="自取 Self-pick up">自取 Self-pick up
</td>
</tr>
<tr>
<td colspan="2">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）Post (Postal Address)：">邮寄（邮寄地址）Post (Postal Address)：
</td>
</tr>
</tbody>
</table>
</div>');
ELSEIF surplusHandling = '邮件' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
' <tr>
<td rowspan="2">余料处理 Handling of Surplus Materials</td>
<td>
<input type="checkbox" disabled="disabled" name="委托处理 Commissioned Handling">委托处理 Commissioned Handling
</td>
<td>
<input type="checkbox" disabled="disabled" name="自取 Self-pick up">自取 Self-pick up
</td>
</tr>
<tr>
<td colspan="2">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）Post (Postal Address)：">邮寄（邮寄地址）Post (Postal Address)：
</td>
</tr>
</tbody>
</table>
</div>');
ELSE
SET htmlOrder2 =
CONCAT(htmlOrder2,
' <tr>
<td rowspan="2">余料处理 Handling of Surplus Materials</td>
<td>
<input type="checkbox" disabled="disabled" name="委托处理 Commissioned Handling">委托处理 Commissioned Handling
</td>
<td>
<input type="checkbox" disabled="disabled" name="自取 Self-pick up">自取 Self-pick up
</td>
</tr>
<tr>
<td colspan="2">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）Post (Postal Address)：">邮寄（邮寄地址）Post (Postal Address)：
</td>
</tr>
</tbody>
</table>
</div>');
END IF;
-- 任务名称部分
SET htmlOrder3 =
CONCAT(
'<div>
<table>
<tbody>
<tr>
<td>*任务名称 Task name</td>
<td colspan="2">',taskName,'</td>
<td>*任务编号 Task number</td>
<td colspan="2">',taskNumber,'</td>
</tr>
<tr>
<td>收料日期Received on</td>
<td>',receivedDate,'</td>
<td>*要求完成日期 Finish before</td>
<td>',requiredDate,'</td>
<td>商定完成日期 Agreed Date</td>
<td>',agreedDate,'</td>
</tr>
</tbody>
</table>
</div>');
-- 材料样品部分拼接
-- 获取材料样品的总数
SELECT COUNT(SKF138) INTO sumMaterial FROM SKT9
WHERE SKF362 = orderCode AND SKF524 != 1;
IF sumMaterial != 0 THEN
SELECT GROUP_CONCAT(IFNULL(SKF138, '')) , GROUP_CONCAT(IFNULL(SKF141, '')) , GROUP_CONCAT(IFNULL(SKF148, '')) ,
GROUP_CONCAT(IFNULL(SKF152, '')) ,GROUP_CONCAT(IFNULL(SKF150, ''))
INTO name_material, variety, dimension, plyConfiguration, quantity_material
FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
SELECT IFNULL(SKF153, '') ,IFNULL(SKF155, '')
INTO isDatumLine, isSpecimenTabs
FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1 LIMIT 1;
-- 是否有基线
IF isDatumLine = '有' THEN
SET htmlOrder4 =
CONCAT(
' 试板基本信息 Panel Information
<div>
<table>
<tbody>
<tr>
<td>材料名称 Material</td>
<td>',name_material,'</td>
</tr>
<tr>
<td>试板类型 Panel Type</td>
<td>',variety,'</td>
</tr>
<tr>
<td>试板尺寸 Panel Dimension</td>
<td>',dimension,'</td>
</tr>
<tr>
<td>铺层顺序 Ply Configuration</td>
<td>',plyConfiguration,'</td>
</tr>
<tr>
<td>数量 Quantity</td>
<td>',quantity_material,'</td>
</tr>
<tr>
<td>
*有无加工基准线
<br>
Is the machining baseline specified?
</td>
<td>
<input type="checkbox" disabled="disabled" name="是" checked="checked">是
<input type="checkbox" disabled="disabled" name="否">否
<br>
注：必须标明加工基准
<br>
Notes：Please specify the machining baseline.
</td>
</tr>');
ELSE
SET htmlOrder4 =
CONCAT(
' 试板基本信息 Panel Information
<div>
<table>
<tbody>
<tr>
<td>材料名称 Material</td>
<td>',name_material,'</td>
</tr>
<tr>
<td>试板类型 Panel Type</td>
<td>',variety,'</td>
</tr>
<tr>
<td>试板尺寸 Panel Dimension</td>
<td>',dimension,'</td>
</tr>
<tr>
<td>铺层顺序 Ply Configuration</td>
<td>',plyConfiguration,'</td>
</tr>
<tr>
<td>数量 Quantity</td>
<td>',quantity_material,'</td>
</tr>
<tr>
<td>
*有无加工基准线
<br>
Is the machining baseline specified?
</td>
<td>
<input type="checkbox" disabled="disabled" name="是">是
<input type="checkbox" disabled="disabled" name="否" checked="checked">否
<br>
注：必须标明加工基准
<br>
Notes：Please specify the machining baseline.
</td>
</tr>');
END IF;
-- 是否有加强片
IF isSpecimenTabs = '有' THEN
SET htmlOrder4 =
CONCAT(htmlOrder4,
' <tr>
<td>
*是否需要粘贴加强片？
<br>
Are tabbing required?
</td>
<td>
<input type="checkbox" disabled="disabled" name="是" checked="checked">是
<input type="checkbox" disabled="disabled" name="否">否
注：如果需要粘贴加强片，请完善以下信息。<br>
Notes：If tabbing was required, please complete the following information.<br>
1、试板的固化温度Curing temperature of the panel：<br>
2、要求的加强片材料Required tabbing materials：<br>
3、图纸Drawing：<br>
4、要求的粘贴用胶黏剂Required adhesive：<br>
注：如果不要求特定牌号的胶黏剂，将根据提供的试板的固化温度选择合适的胶黏剂。<br>
Notes：If a specific grade of adhesive is not required, the appropriate adhesive will be selected according to the provided curing <br>temperature of the test panel.
</td>
</tr>
</tbody>
</table>
</div>
工作内容 Machining Task:
<div>
<table width="100%" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td class="td_border1">序号<br>No.</td>
<td class="td_border1">试验件类型<br>Specimen</td>
<td class="td_border1">数量<br>Quantity</td>
<td class="td_border1">下料图编号<br>Number of Cutting Diagram</td>
<td class="td_border1">试验件图纸<br>Number of Specimen Drawing</td>
<td class="td_border1">试验件编号要求 Numbering</td>
</tr>');
ELSE
SET htmlOrder4 =
CONCAT(htmlOrder4,
' <tr>
<td>
*是否需要粘贴加强片？
<br>
Are tabbing required?
</td>
<td>
<input type="checkbox" disabled="disabled" name="是">是
<input type="checkbox" disabled="disabled" name="否" checked="checked">否
注：如果需要粘贴加强片，请完善以下信息。<br>
Notes：If tabbing was required, please complete the following information.<br>
1、试板的固化温度Curing temperature of the panel：<br>
2、要求的加强片材料Required tabbing materials：<br>
3、图纸Drawing：<br>
4、要求的粘贴用胶黏剂Required adhesive：<br>
注：如果不要求特定牌号的胶黏剂，将根据提供的试板的固化温度选择合适的胶黏剂。<br>
Notes：If a specific grade of adhesive is not required, the appropriate adhesive will be selected according to the provided curing <br>temperature of the test panel.
</td>
</tr>
</tbody>
</table>
</div>
工作内容 Machining Task:
<div>
<table width="100%" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td class="td_border1">序号<br>No.</td>
<td class="td_border1">试验件类型<br>Specimen</td>
<td class="td_border1">数量<br>Quantity</td>
<td class="td_border1">下料图编号<br>Number of Cutting Diagram</td>
<td class="td_border1">试验件图纸<br>Number of Specimen Drawing</td>
<td class="td_border1">试验件编号要求 Numbering</td>
</tr>');
END IF;
END IF;
-- 工作内容部分
-- 初始化done值
SET done = 0;
-- 获取工作内容表的数量
SELECT COUNT(SKF208) INTO sumTask FROM SKT12
WHERE SKF391 = orderCode;
IF sumTask > 0 THEN
-- 开始工作内容循环部分
-- 开启游标
OPEN cur_2;
FETCH cur_2 INTO product, quantity_task, samplingDrawingName, samplingDrawingNumber,
machiningDrawingNumber, numberingRequirement;
SET no_task = 1;
WHILE done != 1 DO
SET htmlOrder5 =
CONCAT(htmlOrder5,
'<tr>
<th>',no_task,'</th>
<th>',product,'</th>
<th>',quantity_task,'</th>
<th>',samplingDrawingName,'/',samplingDrawingNumber,'</th>
<th>',machiningDrawingNumber,'</th>
<th>',numberingRequirement,'</th>
</tr>');
FETCH cur_2 INTO product, quantity_task, samplingDrawingName, samplingDrawingNumber,
machiningDrawingNumber, numberingRequirement;
SET no_task = no_task +1;
END WHILE;
CLOSE cur_2;
ELSE
SET htmlOrder5 =
CONCAT(htmlOrder5,
' <tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>');
END IF;
IF machiningMethod = '磨削' THEN
SET htmlOrder6 =
CONCAT(
' <tr>
<td colspan="3">加工方法 Machining Method</td>
<td colspan="3">
<input type="checkbox" disabled="disabled" name="磨削" checked="checked">磨削 Grinding
<input type="checkbox" disabled="disabled" name="高速铣">高速铣 Milling
</td>
</tr>
<tr>
<td colspan="3">备注 Notes</td>
<td colspan="3">',note,'</td>
</tr>
</tbody>
</table>
</div>
<br>
<div>
<table width="100%">
<tbody>
<tr>
<td>委托单位代表 Client:</td>
<td>承制单位代表 Undertaking Unit:</td>
</tr>
<tr>
<td>签字 Signature: </td>
<td>签字 Signature:</td>
</tr>
<tr>
<td>日期 Date:</td>
<td>日期 Date:</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>');
ELSEIF machiningMethod = '高速铣' THEN
SET htmlOrder6 =
CONCAT(
' <tr>
<td colspan="3">加工方法 Machining Method</td>
<td colspan="3">
<input type="checkbox" disabled="disabled" name="磨削">磨削 Grinding
<input type="checkbox" disabled="disabled" name="高速铣" checked="checked">高速铣 Milling
</td>
</tr>
<tr>
<td colspan="3">备注 Notes</td>
<td colspan="3">',note,'</td>
</tr>
</tbody>
</table>
</div>
<div>
<table>
<tbody width="100%">
<tr>
<td>委托单位代表 Client:</td>
<td>承制单位代表 Undertaking Unit:</td>
</tr>
<tr>
<td>签字 Signature: </td>
<td>签字 Signature:</td>
</tr>
<tr>
<td>日期 Date:</td>
<td>日期 Date:</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>');
ELSE
SET htmlOrder6 =
CONCAT(
' <tr>
<td colspan="3">加工方法 Machining Method</td>
<td colspan="3">
<input type="checkbox" disabled="disabled" name="磨削">磨削 Grinding
<input type="checkbox" disabled="disabled" name="高速铣">高速铣 Milling
</td>
</tr>
<tr>
<td colspan="3">备注 Notes</td>
<td colspan="3">',note,'</td>
</tr>
</tbody>
</table>
</div>
<div>
<table width="100%">
<tbody>
<tr>
<td>委托单位代表 Client:</td>
<td>承制单位代表 Undertaking Unit:</td>
</tr>
<tr>
<td>签字 Signature: </td>
<td>签字 Signature:</td>
</tr>
<tr>
<td>日期 Date:</td>
<td>日期 Date:</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>');
END IF;
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3,htmlOrder4,htmlOrder5,htmlOrder6);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
SET pReturn = 0;
END IF;
COMMIT;
END;
/*------------------------------------------------------------------------------------------------------------------*/
-- 6、振动试验检测任务委托单
ELSEIF orderType = 6 THEN
BEGIN
-- 委托单位Client
DECLARE client VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 单位地址
DECLARE address VARCHAR(255);
-- 联系方式
DECLARE contactInformation VARCHAR(255);
-- 样品处理
DECLARE handlingSpecimens VARCHAR(255);
-- 样品处理邮寄地址
DECLARE handlingPostalAddress VARCHAR(255);
-- 取报告方式
DECLARE reportPickUp VARCHAR(255);
-- 取报告邮寄地址
DECLARE reportPostalAddress VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收样日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE finishDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 材料表信息
-- 材料ID
DECLARE eutID VARCHAR(255);
-- 试样名称
DECLARE eutName VARCHAR(255);
-- 试样数量
DECLARE eutNumber VARCHAR(255);
-- 型号/规格
DECLARE modelType VARCHAR(255);
-- 试样编号
DECLARE eutNo VARCHAR(255);
-- 检测依据
DECLARE testStandard VARCHAR(255);
-- 夹具信息
DECLARE fixtureInformation VARCHAR(255);
-- 任务要求
-- 检测目的
DECLARE testAim VARCHAR(255);
-- 检测项目
DECLARE testItem VARCHAR(255);
-- 检测方法/条件
DECLARE testMethod VARCHAR(255);
-- 合格判断
DECLARE criteriaConformity VARCHAR(255);
-- 其他
DECLARE taskRequirements VARCHAR(255);
-- 判断材料样品是否存在
DECLARE countMaterial INT;
-- 拼接部分定义
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放材料、任务要求部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放后半部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 材料信息表游标
DECLARE done INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF137, ''), IFNULL(SKF138, ''),IFNULL(SKF150,''),
IFNULL(SKF140, ''), IFNULL(SKF280, ''), IFNULL(SKF426, ''),
IFNULL(SKF425, '')
FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 主表信息赋值
SELECT IFNULL(SKF69,''), IFNULL(SKF71,''),IFNULL(SKF70, ''), IFNULL(SKF72, ''),
IFNULL(SKF73,''), IFNULL(SKF74, ''), IFNULL(SKF75, ''), IFNULL(SKF76, ''),
IFNULL(SKF77,''), IFNULL(SKF78, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''),
IFNULL(SKF81, ''), IFNULL(SKF82, '') INTO
client, contact, address, contactInformation,
handlingSpecimens, handlingPostalAddress, reportPickUp,reportPostalAddress,
taskName, projectNumber, taskNumber, receivedDate,
finishDate, agreedDate FROM SKT8 WHERE SKF68 = orderCode;
SET htmlORder1 =
CONCAT(
'<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>振动可靠性检测委托单</title>
<style type="text/css">
div{
width: 100%;
}
table {
width: 100%;
}
body {
font-size 18px;
}
td {
text-align: center;
height: 45px;
}
.td_rigth{
border-bottom: 0px;
}
.td_rigth3_1{
height: 50px;
text-align: left;
}
.td_top1_1{
text-align: center;
}
.td_top_right1_1{
}
.td_hide_border1{
text-align: center;
border: 0px;
}
.td_top1_2{
text-align: center;
width: 16%;
}
.td_top_right1_2{
text-align: center;
width: 16%;
}
.div_in{
}
.td_top2_1{
text-align: center;
width: 20%;
}
.td_top_right2_1{
text-align: center;
width: 20%;
}
</style>
</head>
<body>
<!-- cellpadding="0" cellspacing="0" 用于解决表格中的空袭 -->
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">委托单位<br>Client</td>
<td colspan="3">',client,'</td>
<td>联系人<br>Contact</td>
<td>',contact,'</td>
</tr>
<tr>
<td>地址<br>Address</td>
<td colspan="3">',address,'</td>
<td>联系方式<br>Contact Number</td>
<td>',contactInformation,'</td>
</tr>');
IF handlingSpecimens = '委托处理' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td style="width: 20%">样品处理<br>Handling of Specimens</td>
<td class="td_hide_border1" style="border-left: 1px solid; border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="委托处理" value="委托处理" checked="checked">委托处理<br>Commissioned Handling
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="3">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）上海市松江区<br>Post (Postal Address)
</td>
</tr> ');
ELSEIF handlingSpecimens = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td style="width: 20%">样品处理<br>Handling of Specimens</td>
<td class="td_hide_border1" style="border-left: 1px solid; border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="3">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）上海市松江区<br>Post (Postal Address)
</td>
</tr> ');
ELSEIF handlingSpecimens = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td style="width: 20%">样品处理<br>Handling of Specimens</td>
<td class="td_hide_border1" style="border-left: 1px solid; border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="3">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）上海市松江区<br>Post (Postal Address)
</td>
</tr> ');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td style="width: 20%">样品处理<br>Handling of Specimens</td>
<td class="td_hide_border1" style="border-left: 1px solid; border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="3">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）上海市松江区<br>Post (Postal Address)
</td>
</tr> ');
END IF;
IF reportPickUp = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td>取报告方式<br>Report pick up</td>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up
</td>
<td class="td_hide_border1" style="border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）
</td>
<td class="td_hide_border1" style="border-bottom: 1px solid; border-right: 1px solid; border-top: 1px solid;" colspan="3">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）上海市松江区<br>Post (Postal Address)
</td>
</tr>');
ELSEIF reportPickUp = '传真' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td>取报告方式<br>Report pick up</td>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td class="td_hide_border1" style="border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）" checked="checked">传真（号码）<br>Fax（Number）
</td>
<td class="td_hide_border1" style="border-bottom: 1px solid; border-right: 1px solid; border-top: 1px solid;" colspan="3">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）上海市松江区<br>Post (Postal Address)
</td>
</tr>');
ELSEIF reportPickUp = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td>取报告方式<br>Report pick up</td>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td class="td_hide_border1" style="border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）
</td>
<td class="td_hide_border1" style="border-bottom: 1px solid; border-right: 1px solid; border-top: 1px solid;" colspan="3">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）上海市松江区<br>Post (Postal Address)
</td>
</tr>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td>取报告方式<br>Report pick up</td>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td class="td_hide_border1" style="border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）
</td>
<td class="td_hide_border1" style="border-bottom: 1px solid; border-right: 1px solid; border-top: 1px solid;" colspan="3">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）上海市松江区<br>Post (Postal Address)
</td>
</tr>');
END IF;
-- 任务名称、收样日期等拼接
SET htmlOrder1 =
CONCAT(htmlORder1,
'<tr>
<td class="td_top1_2" style="width: 20%;">任务名称<br>Task Name</td>
<td class="td_top1_2">',taskName,'</td>
<td class="td_top1_2">项目令号<br>Project Number</td>
<td class="td_top1_2">',projectNumber,'</td>
<td class="td_top1_2">任务编号<br>Task Number</td>
<td class="td_top_right1_2">',taskNumber,'</td>
</tr>
<tr>
<td>收样日期<br>Received Date</td>
<td>',receivedDate,'</td>
<td>要求完成日期<br>Required Date</td>
<td>',finishDate,'</td>
<td>商定完成日期<br>Agreed Date</td>
<td>',agreedDate,'</td>
</tr>');
-- 材料和任务部分拼接
-- 判断材料是否存在
SELECT COUNT(SKF137) INTO countMaterial FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
IF countMaterial > 0 THEN
OPEN cur_1;
FETCH cur_1 INTO eutID, eutName, eutNumber, modelType,
eutNo, testStandard, fixtureInformation;
WHILE done != 1 DO
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td style="width: 20%;" rowspan="5">检测信息<br>Test Information</td>
<td>试样名称<br>Name of EUT</td>
<td colspan="2">',eutNumber,'</td>
<td>试样数量<br>Number of EUT</td>
<td >',eutNumber,'</td>
</tr>
<tr>
<td>型号/规格<br>Model/Type</td>
<td colspan="2">',modelType,'</td>
<td>试样编号<br>No.of EUT</td>
<td>',eutNo,'</td>
</tr>
<tr>
<td>检测依据<br>Test Standard<br>Specification</td>
<td colspan="4">',testStandard,'</td>
</tr>');
IF fixtureInformation = '已有' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td rowspan="2">夹具信息<br>Fixture Information</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-left: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="已有" value="已有" checked="checked">已有
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="新制" value="新制">新制
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="2">
<input type="checkbox" disabled="disabled" name="客户提供" value="客户提供">客户提供
</td>
</tr>
<tr>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="邮寄" value="邮寄">邮寄（邮寄地址）
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;"></td>
<td class="td_hide_border1" colspan="2" style="border-top: 1px solid;border-bottom: 1px solid;border-right: 1px solid;">
<input type="checkbox" disabled="disabled" name="实验室存储" value="实验室存储">实验室存储
</td>
</tr>');
ELSEIF fixtureInformation = '新制' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td rowspan="2">夹具信息<br>Fixture Information</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-left: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="已有" value="已有">已有
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="新制" value="新制" checked="checked">新制
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="2">
<input type="checkbox" disabled="disabled" name="客户提供" value="客户提供">客户提供
</td>
</tr>
<tr>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="邮寄" value="邮寄">邮寄（邮寄地址）
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;"></td>
<td class="td_hide_border1" colspan="2" style="border-top: 1px solid;border-bottom: 1px solid;border-right: 1px solid;">
<input type="checkbox" disabled="disabled" name="实验室存储" value="实验室存储">实验室存储
</td>
</tr>');
ELSEIF fixtureInformation = '客户提供' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td rowspan="2">夹具信息<br>Fixture Information</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-left: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="已有" value="已有">已有
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="新制" value="新制">新制
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="2">
<input type="checkbox" disabled="disabled" name="客户提供" value="客户提供" checked="checked">客户提供
</td>
</tr>
<tr>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="邮寄" value="邮寄">邮寄（邮寄地址）
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;"></td>
<td class="td_hide_border1" colspan="2" style="border-top: 1px solid;border-bottom: 1px solid;border-right: 1px solid;">
<input type="checkbox" disabled="disabled" name="实验室存储" value="实验室存储">实验室存储
</td>
</tr>');
ELSEIF fixtureInformation = '邮寄' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td rowspan="2">夹具信息<br>Fixture Information</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-left: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="已有" value="已有">已有
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="新制" value="新制">新制
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="2">
<input type="checkbox" disabled="disabled" name="客户提供" value="客户提供">客户提供
</td>
</tr>
<tr>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="邮寄" value="邮寄" checked="checked">邮寄（邮寄地址）
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;"></td>
<td class="td_hide_border1" colspan="2" style="border-top: 1px solid;border-bottom: 1px solid;border-right: 1px solid;">
<input type="checkbox" disabled="disabled" name="实验室存储" value="实验室存储">实验室存储
</td>
</tr>');
ELSEIF fixtureInformation = '实验室存储' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td rowspan="2">夹具信息<br>Fixture Information</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-left: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="已有" value="已有">已有
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="新制" value="新制">新制
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="2">
<input type="checkbox" disabled="disabled" name="客户提供" value="客户提供">客户提供
</td>
</tr>
<tr>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="邮寄" value="邮寄">邮寄（邮寄地址）
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;"></td>
<td class="td_hide_border1" colspan="2" style="border-top: 1px solid;border-bottom: 1px solid;border-right: 1px solid;">
<input type="checkbox" disabled="disabled" name="实验室存储" value="实验室存储" checked="checked">实验室存储
</td>
</tr>');
ELSE
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td rowspan="2">夹具信息<br>Fixture Information</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-left: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="已有" value="已有">已有
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="新制" value="新制">新制
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="2">
<input type="checkbox" disabled="disabled" name="客户提供" value="客户提供">客户提供
</td>
</tr>
<tr>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="邮寄" value="邮寄">邮寄（邮寄地址）
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;"></td>
<td class="td_hide_border1" colspan="2" style="border-top: 1px solid;border-bottom: 1px solid;border-right: 1px solid;">
<input type="checkbox" disabled="disabled" name="实验室存储" value="实验室存储">实验室存储
</td>
</tr>');
END IF;
-- 任务要求表
SELECT IFNULL(GROUP_CONCAT(SKF180), ''), IFNULL(GROUP_CONCAT(SKF175), ''),
IFNULL(GROUP_CONCAT(SKF181), ''),IFNULL(GROUP_CONCAT(SKF182), ''),
IFNULL(GROUP_CONCAT(SKF183), '') INTO testAim, testItem, testMethod,
criteriaConformity, taskRequirements
FROM SKT10 WHERE SKF388 = eutID;
-- 任务要求表拼接
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="td_top1_1" rowspan="6" style="width: 20%;">任务要求<br>Task</td>
<td class="td_top_right1_1" colspan="5">
试验及技术要求（检测目的,检测项目,检测方法/条件,合格判据等）<br>
Test and Technical Requirements (The aim of Test, Test Item, Test Method/Condition, Criteria for Conformity etc.)
</td>
</tr>
<td class="td_rigth3_1" colspan="5">
检测目的：',testAim,'
</td>
<tr>
<td class="td_rigth3_1" colspan="5">
检测项目：',testItem,'
</td>
</tr>
<tr>
<td class="td_rigth3_1" colspan="5">
检测方法/条件：',testMethod,'
</td>
</tr>
<tr>
<td class="td_rigth3_1" colspan="5">
合格判据：',criteriaConformity,'
</td>
</tr>
<tr>
<td class="td_rigth3_1" colspan="5">
其他：',taskRequirements,'
</td>
</tr>');
FETCH cur_1 INTO eutID, eutName, eutNumber, modelType,
eutNo, testStandard, fixtureInformation;
END WHILE;
CLOSE cur_1;
ELSE
SET htmlOrder2 =
' <tr>
<td style="width: 20%;" rowspan="5">检测信息<br>Test Information</td>
<td>试样名称<br>Name of EUT</td>
<td colspan="2"></td>
<td>试样数量<br>Number of EUT</td>
<td ></td>
</tr>
<tr>
<td>型号/规格<br>Model/Type</td>
<td colspan="2"></td>
<td>试样编号<br>No.of EUT</td>
<td></td>
</tr>
<tr>
<td>检测依据<br>Test Standard<br>Specification</td>
<td colspan="4"></td>
</tr>
<tr>
<td rowspan="2">夹具信息<br>Fixture Information</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-left: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="已有" value="已有">已有
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;">
<input type="checkbox" disabled="disabled" name="新制" value="新制">新制
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-right: 1px solid; border-bottom: 1px solid;" colspan="2">
<input type="checkbox" disabled="disabled" name="客户提供" value="客户提供">客户提供
</td>
</tr>
<tr>
<td class="td_hide_border1" style="border-left: 1px solid; border-bottom: 1px solid; border-top: 1px solid;">
<input type="checkbox" disabled="disabled" name="邮寄" value="邮寄">邮寄（邮寄地址）
</td>
<td class="td_hide_border1" style="border-top: 1px solid; border-bottom: 1px solid;"></td>
<td class="td_hide_border1" colspan="2" style="border-top: 1px solid;border-bottom: 1px solid;border-right: 1px solid;">
<input type="checkbox" disabled="disabled" name="实验室存储" value="实验室存储">实验室存储
</td>
</tr>
<tr>
<td class="td_top1_1" rowspan="6" style="width: 20%;">任务要求<br>Task</td>
<td class="td_top_right1_1" colspan="5">
试验及技术要求（检测目的,检测项目,检测方法/条件,合格判据等）<br>
Test and Technical Requirements (The aim of Test, Test Item, Test Method/Condition, Criteria for Conformity etc.)
</td>
</tr>
<td class="td_rigth3_1" colspan="5">
检测目的：
</td>
<tr>
<td class="td_rigth3_1" colspan="5">
检测项目：
</td>
</tr>
<tr>
<td class="td_rigth3_1" colspan="5">
检测方法/条件：
</td>
</tr>
<tr>
<td class="td_rigth3_1" colspan="5">
合格判据：
</td>
</tr>
<tr>
<td class="td_rigth3_1" colspan="5">
其他：
</td>
</tr>';
END IF;
-- html后半部分
SET htmlOrder3 =
' <tr>
<td class="td_rigth3_1" colspan="6">
委托单代表<br>
签字：<br>
日期：
</td>
</tr>
<tr>
<td class="td_rigth3_1" colspan="6">
承制单位代表<br>
签字：<br>
日期：
</td>
</tr>
<tr>
<td>承诺<br>Promise</td>
<td class="td_rigth3_1" colspan="5">
委托单位保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.<br>
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical <br>
information provided by the client.
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>';
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
/*----------------------------------------------------------------------------------------------------------------------*/
-- 7、物理性能金属、复材委托单
ELSEIF orderType = 7 THEN
BEGIN
-- 委托单位
DECLARE client VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 地址
DECLARE address VARCHAR(255);
-- 联系方式
DECLARE contactNumber VARCHAR(255);
-- 样品处理
DECLARE productHandling VARCHAR(255);
-- 样品处理邮寄地址
DECLARE productPostalAddress VARCHAR(255);
-- 报告处理
DECLARE reportHandling VARCHAR(255);
-- 报告处理邮寄地址
DECLARE reportPostalAddress VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收料日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE requiredDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 材料ID
DECLARE materialID VARCHAR(255);
-- 样品材料名称
DECLARE materialName VARCHAR(255);
-- 牌号
DECLARE grades VARCHAR(255);
-- 样品规格
DECLARE thicknessDiameter VARCHAR(255);
-- 材料热处理状态
DECLARE heatCondition VARCHAR(255);
-- 成型工艺
DECLARE process VARCHAR(255);
-- 材料炉批号
DECLARE batchNo VARCHAR(255);
-- 预浸料名称
DECLARE prepregName VARCHAR(255);
-- 状态调节要求
DECLARE conditionRequirement VARCHAR(255);
-- 试样类型
DECLARE materialType VARCHAR(255);
-- 试样数量
DECLARE quantity VARCHAR(255);
-- 试样尺寸
DECLARE Dimension VARCHAR(255);
-- 样品编号
DECLARE specimenNumber VARCHAR(255);
-- 材料是否存在
DECLARE countMaterial INT;
-- 检测项目
DECLARE testItem VARCHAR(255);
-- 检测标准
DECLARE testStandard VARCHAR(255);
-- 拼接部分定义
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放材料、任务要求部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放后半部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 材料表游标
DECLARE done INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF137, ''), IFNULL(SKF138, ''), IFNULL(SKF139, ''),
IFNULL(SKF140, ''), IFNULL(SKF143, ''), IFNULL(SKF278, ''),
IFNULL(SKF142, ''), IFNULL(SKF145, ''), IFNULL(SKF162, ''),IFNULL(SKF279, ''),
IFNULL(SKF150, ''),IFNULL(SKF148, ''),IFNULL(SKF280, '')
FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 获取报告的基础信息
SELECT IFNULL(SKF69, ''), IFNULL(SKF71, ''), IFNULL(SKF70, ''), IFNULL(SKF72, ''),
IFNULL(SKF73, ''), IFNULL(SKF74, ''),IFNULL(SKF75, ''), IFNULL(SKF76, ''),
IFNULL(SKF77, ''), IFNULL(SKF78, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''),
IFNULL(SKF81, ''), IFNULL(SKF82, '') INTO
client, contact, address, contactNumber,
productHandling, productPostalAddress,reportHandling,reportPostalAddress,
taskName, projectNumber, taskNumber, receivedDate,
requiredDate, agreedDate
FROM SKT8 WHERE SKF68 = orderCode;
SET htmlOrder1 =
CONCAT(
'<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>物理性能检测委托单</title>
<style type="text/css">
body{
font-size: 18px;
text-align: center;
}
table{
width: 100%;
}
.td_1_checkbox{
width: 25%;
}
.table_1_checkbox{
font-size: 18px;
}
.table_NoTopBorder{
border-top: 0px;
}
</style>
</head>
<body>
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td>委托单位<br>Client</td>
<td colspan="3">',client,'</td>
<td>联系人<br>Contact</td>
<td>',contact,'</td>
</tr>
<tr>
<td>地址<br>Address</td>
<td colspan="3">',address,'</td>
<td>联系方式<br>Contact Number</td>
<td>',contactNumber,'</td>
</tr>');
IF productHandling = '委托处理' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td class="td_1_checkbox" style="border-right: 0px;">
<input type="checkbox" disabled="disabled" name="委托处理" value="委托处理" checked="checked">委托处理<br>Commissioned Handling
</td>
<td class="td_1_checkbox" colspan="2" style="border-left: 0px; border-right: 0px;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td colspan="2" style="border-left: 0px;">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)
</td>
</tr> ');
ELSEIF productHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td class="td_1_checkbox" style="border-right: 0px;">
<input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling
</td>
<td class="td_1_checkbox" colspan="2" style="border-left: 0px; border-right: 0px;">
<input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up
</td>
<td colspan="2" style="border-left: 0px;">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)
</td>
</tr> ');
ELSEIF productHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td class="td_1_checkbox" style="border-right: 0px;">
<input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling
</td>
<td class="td_1_checkbox" colspan="2" style="border-left: 0px; border-right: 0px;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td colspan="2" style="border-left: 0px;">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',productPostalAddress,'<br>Post (Postal Address)
</td>
</tr> ');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td class="td_1_checkbox" style="border-right: 0px;">
<input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling
</td>
<td class="td_1_checkbox" colspan="2" style="border-left: 0px; border-right: 0px;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td colspan="2" style="border-left: 0px;">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）<br>Post (Postal Address)
</td>
</tr> ');
END IF;
IF reportHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td>取报告方式<br>Report pick up</td>
<td class="td_1_checkbox" style="border-right: 0px;">
<input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up
</td>
<td class="td_1_checkbox" colspan="2" style="border-right: 0px;border-left: 0px;">
<input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）
</td>
<td colspan="2" style="border-left: 0px;">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)
</td>
</tr>');
ELSEIF reportHandling = '传真' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td>取报告方式<br>Report pick up</td>
<td class="td_1_checkbox" style="border-right: 0px;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td class="td_1_checkbox" colspan="2" style="border-right: 0px;border-left: 0px;">
<input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）" checked="checked">传真（号码）<br>Fax（Number）
</td>
<td colspan="2" style="border-left: 0px;">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" >邮寄（邮寄地址）<br>Post (Postal Address)
</td>
</tr>');
ELSEIF reportHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td>取报告方式<br>Report pick up</td>
<td class="td_1_checkbox" style="border-right: 0px;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td class="td_1_checkbox" colspan="2" style="border-right: 0px;border-left: 0px;">
<input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）
</td>
<td colspan="2" style="border-left: 0px;">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',reportPostalAddress,'<br>Post (Postal Address)
</td>
</tr>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td>取报告方式<br>Report pick up</td>
<td class="td_1_checkbox" style="border-right: 0px;">
<input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up
</td>
<td class="td_1_checkbox" colspan="2" style="border-right: 0px;border-left: 0px;">
<input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）
</td>
<td colspan="2" style="border-left: 0px;">
<input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)
</td>
</tr>');
END IF;
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td style="width: 20%;">任务名称<br>Task Name</td>
<td style="width: 13%;">',taskName,'</td>
<td style="width: 20%;">项目令号<br>Project Number</td>
<td style="width: 13%;">',projectNumber,'</td>
<td style="width: 20%;">任务编号<br>Task Number</td>
<td>',taskNumber,'</td>
</tr>
<tr>
<td>收样日期<br>Received Date</td>
<td>',receivedDate,'</td>
<td>要求完成日期<br>Required Date</td>
<td>',requiredDate,'</td>
<td>商定完成日期<br>Agreed Date</td>
<td>',agreedDate,'</td>
</tr>');
-- 判断材料表是否存在
SELECT COUNT(SKF137) INTO countMaterial FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
IF countMaterial > 0 THEN
OPEN cur_1;
FETCH cur_1 INTO materialID ,materialName ,grades ,thicknessDiameter ,
heatCondition ,process ,batchNo ,prepregName ,conditionRequirement ,
materialType ,quantity ,Dimension ,specimenNumber ;
WHILE done != 1 DO
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td style="width: 20%;" rowspan="5">试样信息<br>Material</td>
<td style="width: 20%;">样品名称/牌号<br>Material/Grades</td>
<td style="width: 20%;">',materialName,'/',grades,'</td>
<td style="width: 20%;">样品规格<br>Thickness or Diameter</td>
<td colspan="2">',thicknessDiameter,'</td>
</tr>
<tr>
<td>材料热处理状态/成型工艺<br>Heat Condition/Process</td>
<td>',heatCondition,'/',process,'</td>
<td>材料炉批号<br>Heat No./Batch No.</td>
<td colspan="2">',batchNo,'</td>
</tr>
<tr>
<td>预浸料名称(增强体/基体)<br>Prepreg name<br>(Reinforcement/Matrix)</td>
<td>',prepregName,'</td>
<td>状态调节要求<br>Condition Requirement</td>
<td colspan="2">',conditionRequirement,'</td>
</tr>
<tr>
<td>试样类型<br>Type</td>
<td>',materialType,'</td>
<td>样品数量<br>Quantity</td>
<td colspan="2">',quantity,'</td>
</tr>
<tr>
<td>试样尺寸<br>Dimension</td>
<td>',Dimension,'</td>
<td>试样编号<br>Specimen Number</td>
<td colspan="2">',specimenNumber,'</td>
</tr>');
-- 获取任务表信息
SELECT IFNULL(GROUP_CONCAT(SKF175), ''), IFNULL(GROUP_CONCAT(SKF174), '')
INTO testItem, testStandard
FROM SKT10 WHERE SKF388 = materialID;
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td style="width: 20%;" rowspan="2">试验要求<br>Task</td>
<td colspan="5">
试验及技术要求（检测项目、 检测标准、检测目的及用途等）<br>
Test and Technical Requirements (Testing Item, Standard, Loading and Measurement Parameters, Environment etc.)
</td>
</tr>
<tr>
<td style="height: 85px; text-align: left" colspan="5">
检测项目：',testItem,'<br>
检测标准：',testStandard,'<br>
</td>
</tr>');
FETCH cur_1 INTO materialID ,materialName ,grades ,thicknessDiameter ,
heatCondition ,process ,batchNo ,prepregName ,conditionRequirement ,
materialType ,quantity ,Dimension ,specimenNumber ;
END WHILE;
ELSE
SET htmlOrder2 =
' <tr>
<td style="width: 20%;" rowspan="5">试样信息<br>Material</td>
<td style="width: 20%;">样品名称/牌号<br>Material/Grades</td>
<td style="width: 20%;"></td>
<td style="width: 20%;">样品规格<br>Thickness or Diameter</td>
<td colspan="2"></td>
</tr>
<tr>
<td>材料热处理状态/成型工艺<br>Heat Condition/Process</td>
<td></td>
<td>材料炉批号<br>Heat No./Batch No.</td>
<td colspan="2"></td>
</tr>
<tr>
<td>预浸料名称(增强体/基体)<br>Prepreg name<br>(Reinforcement/Maxtrix)</td>
<td></td>
<td>状态调节要求<br>Condition Requirement</td>
<td colspan="2"></td>
</tr>
<tr>
<td>试样类型<br>Type</td>
<td>三得利</td>
<td>样品数量<br>Quantity</td>
<td colspan="2"></td>
</tr>
<tr>
<td>试样尺寸<br>Dimension</td>
<td></td>
<td>试样编号<br>Specimen Number</td>
<td colspan="2"></td>
</tr>
<tr>
<td style="width: 20%;" rowspan="2">试验要求<br>Task</td>
<td colspan="5">
试验及技术要求（检测项目、 检测标准、检测目的及用途等）<br>
Test and Technical Requirements (Testing Item, Standard, Loading and Measurement Parameters, Environment etc.)
</td>
</tr>
<tr>
<td style="height: 85px; text-align: left" colspan="5">
检测项目：<br>
检测标准：<br>
</td>
</tr>';
END IF;
SET htmlOrder3 =
' <tr>
<td style="text-align: left;" colspan="6">
委托单位代表 Client：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="text-align: left;" colspan="6">
承制单位代表 Undertaking Unit：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="width: 10%;">
承诺<br>
Promise
</td>
<td style="text-align: left;" colspan="5">
委托单位保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical information provided by the client.
</td>
</tr>
</tbody>
</table>
</body>
</html>';
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
/*------------------------------------------------------------------------------------------------------------------------------*/
-- 8、高分子材料测试委托单
ELSEIF orderType = 8 THEN
BEGIN
-- 委托单位
DECLARE client VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 地址
DECLARE address VARCHAR(255);
-- 联系方式
DECLARE contactNumber VARCHAR(255);
-- 样品处理
DECLARE productHandling VARCHAR(255);
-- 样品处理邮寄地址
DECLARE productPostalAddress VARCHAR(255);
-- 报告处理
DECLARE reportHandling VARCHAR(255);
-- 报告处理邮寄地址
DECLARE reportPostalAddress VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收料日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE requiredDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 材料ID
DECLARE materialID VARCHAR(255);
-- 样品材料名称
DECLARE materialName VARCHAR(255);
-- 牌号
DECLARE grades VARCHAR(255);
-- 样品规格
DECLARE thicknessDiameter VARCHAR(255);
-- 品种
DECLARE materialForm VARCHAR(255);
-- 材料批号
DECLARE batchNo VARCHAR(255);
-- 存储环境
DECLARE storeEnvironment VARCHAR(255);
-- 试样状态调节
DECLARE specimenCondition VARCHAR(255);
-- 试样类型
DECLARE materialType VARCHAR(255);
-- 试样数量
DECLARE quantity VARCHAR(255);
-- 试样尺寸
DECLARE Dimension VARCHAR(255);
-- 样品编号
DECLARE specimenNumber VARCHAR(255);
-- 材料是否存在
DECLARE countMaterial INT;
-- 检测项目
DECLARE testItem VARCHAR(255);
-- 检测标准
DECLARE testStandard VARCHAR(255);
-- 加载参数
DECLARE loadParameters VARCHAR(255);
-- 试验环境
DECLARE environment VARCHAR(255);
-- 拼接部分定义
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放材料、任务要求部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放后半部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 材料表游标
DECLARE done INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF137, ''), IFNULL(SKF138, ''), IFNULL(SKF139, ''),
IFNULL(SKF140, ''), IFNULL(SKF141, ''), IFNULL(SKF142, ''), IFNULL(SKF161, ''),
IFNULL(SKF162, ''), IFNULL(SKF279, ''),IFNULL(SKF150, ''),IFNULL(SKF148, ''),IFNULL(SKF280, '')
FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 获取报告的基础信息
SELECT IFNULL(SKF69, ''), IFNULL(SKF71, ''), IFNULL(SKF70, ''), IFNULL(SKF72, ''),
IFNULL(SKF73, ''), IFNULL(SKF74, ''),IFNULL(SKF75, ''), IFNULL(SKF76, ''),
IFNULL(SKF77, ''), IFNULL(SKF78, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''),
IFNULL(SKF81, ''), IFNULL(SKF82, '') INTO
client, contact, address, contactNumber,
productHandling, productPostalAddress,reportHandling,reportPostalAddress,
taskName, projectNumber, taskNumber, receivedDate,
requiredDate, agreedDate
FROM SKT8 WHERE SKF68 = orderCode;
SET htmlOrder1 =
CONCAT(
'<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>高分子材料检测委托单</title>
<style type="text/css">
body{
font-size: 18px;
text-align: center;
}
table{
width: 100%;
}
.td_1_checkbox{
width: 25%;
}
.table_1_checkbox{
font-size: 18px;
}
.table_NoTopBorder{
border-top: 0px;
}
</style>
</head>
<body>
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">委托单位<br>Client</td>
<td style="width: 40%;">',client ,'</td>
<td style="width: 20%;">联系人<br>Contact</td>
<td>',contact ,'</td>
</tr>
<tr>
<td>地址<br>Address</td>
<td>',address ,'</td>
<td>联系方式<br>Contact Number</td>
<td>',contactNumber ,'</td>
</tr>');
IF productHandling = '委托处理' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理" checked="checked">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSEIF productHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSEIF productHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',productPostalAddress,'<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
END IF;
IF reportHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSEIF reportHandling = '传真' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）" checked="checked">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSEIF reportHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',reportPostalAddress,'<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
END IF;
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">任务名称<br>Task Name</td>
<td style="width: 13%;">',taskName,'</td>
<td style="width: 20%;">项目令号<br>Project Number</td>
<td style="width: 13%;">',projectNumber,'</td>
<td style="width: 20%;">任务编号<br>Task Number</td>
<td>',taskNumber,'</td>
</tr>
<tr>
<td>收样日期<br>Received Date</td>
<td>',receivedDate,'</td>
<td>要求完成日期<br>Required Date</td>
<td>',requiredDate,'</td>
<td>商定完成日期<br>Agreed Date</td>
<td>',agreedDate,'</td>
</tr>
</tbody>
</table>');
-- 判断材料表是否存在
SELECT COUNT(SKF137) INTO countMaterial FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
IF countMaterial > 0 THEN
OPEN cur_1;
FETCH cur_1 INTO materialID ,materialName ,grades ,thicknessDiameter ,
materialForm ,batchNo ,storeEnvironment ,specimenCondition ,materialType ,
quantity ,Dimension ,specimenNumber;
WHILE done != 1 DO
SET htmlOrder2 =
CONCAT(htmlOrder2,
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="5">试样信息<br>Material</td>
<td style="width: 20%;">样品名称/牌号<br>Material/Grades</td>
<td style="width: 20%;">', materialName, '/', grades,'</td>
<td style="width: 20%;">样品规格<br>Thickness or Diameter</td>
<td>',thicknessDiameter,'</td>
</tr>
<tr>
<td>品种<br>Form</td>
<td>',materialForm,'</td>
<td>材料批号<br>Batch No.</td>
<td>',batchNo,'</td>
</tr>
<tr>
<td>存储条件<br>Store Environment</td>
<td>',storeEnvironment,'</td>
<td>试样状态调节<br>Specimen Condition</td>
<td>',specimenCondition,'</td>
</tr>
<tr>
<td>试样类型<br>Type</td>
<td>',materialType,'</td>
<td>样品数量<br>Quantity</td>
<td>',quantity,'</td>
</tr>
<tr>
<td>试样尺寸<br>Dimension</td>
<td>',Dimension,'</td>
<td>试样编号<br>Specimen Number</td>
<td>',specimenNumber,'</td>
</tr>
</tbody>
</table>');
-- 获取任务表信息
SELECT IFNULL(GROUP_CONCAT(SKF175), ''), IFNULL(GROUP_CONCAT(SKF174), ''),
IFNULL(GROUP_CONCAT(SKF187), ''), IFNULL(GROUP_CONCAT(SKF173), '')
INTO testItem, testStandard,loadParameters ,environment
FROM SKT10 WHERE SKF388 = materialID;
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="2">试验要求<br>Task</td>
<td>
试验及技术要求（检测项目, 检测标准, 加载及测量参数, 试验环境等）<br>
Test and Technical Requirements (Testing Item, Standard, Loading and Measurement Parameters, Environment etc.)
</td>
</tr>
<tr>
<td style="height: 85px; text-align: left">
检测项目：',testItem,'<br>
检测标准：',testStandard,'<br>
加载及测量参数：',loadParameters,'<br>
试验环境：',environment,'
</td>
</tr>
</tbody>
</table>');
FETCH cur_1 INTO materialID ,materialName ,grades ,thicknessDiameter ,
materialForm ,batchNo ,storeEnvironment ,specimenCondition ,materialType ,
quantity ,Dimension ,specimenNumber ;
END WHILE;
ELSE
SET htmlOrder2 =
'<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="5">试样信息<br>Material</td>
<td style="width: 20%;">样品名称/牌号<br>Material/Grades</td>
<td style="width: 20%;"></td>
<td style="width: 20%;">样品规格<br>Thickness or Diameter</td>
<td></td>
</tr>
<tr>
<td>品种<br>Form</td>
<td></td>
<td>材料批号<br>Batch No.</td>
<td></td>
</tr>
<tr>
<td>存储条件<br>Store Environment</td>
<td></td>
<td>试样状态调节<br>Specimen Condition</td>
<td></td>
</tr>
<tr>
<td>试样类型<br>Type</td>
<td></td>
<td>样品数量<br>Quantity</td>
<td></td>
</tr>
<tr>
<td>试样尺寸<br>Dimension</td>
<td></td>
<td>试样编号<br>Specimen Number</td>
<td></td>
</tr>
</tbody>
</table>
<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="2">任务要求<br>Task</td>
<td>
试验及技术要求（检测项目, 检测标准, 加载及测量参数, 试验环境等）<br>
Test and Technical Requirements (Testing Item, Standard, Loading and Measurement Parameters, Environment etc.)
</td>
</tr>
<tr>
<td style="height: 85px;">
</td>
</tr>
</tbody>
</table>';
END IF;
SET htmlOrder3 =
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="text-align: left;" colspan="2">
委托单位代表 Client：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="text-align: left;" colspan="2">
承制单位代表 Undertaking Unit：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="width: 10%;">
承诺<br>
Promise
</td>
<td style="text-align: left;">
委托单位保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical information provided by the client.
</td>
</tr>
</tbody>
</table>
</body>
</html>';
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
/*-----------------------------------------------------------------------------------------------------------------------------------*/
-- 9、化学成分测试委托单
ELSEIF orderType = 9 THEN
BEGIN
-- 委托单位
DECLARE client VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 地址
DECLARE address VARCHAR(255);
-- 联系方式
DECLARE contactNumber VARCHAR(255);
-- 样品处理
DECLARE productHandling VARCHAR(255);
-- 样品处理邮寄地址
DECLARE productPostalAddress VARCHAR(255);
-- 报告处理
DECLARE reportHandling VARCHAR(255);
-- 报告处理邮寄地址
DECLARE reportPostalAddress VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收料日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE requiredDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 材料ID
DECLARE materialID VARCHAR(255);
-- 样品材料名称
DECLARE materialName VARCHAR(255);
-- 牌号
DECLARE grades VARCHAR(255);
-- 样品规格
DECLARE thicknessDiameter VARCHAR(255);
-- 品种
DECLARE materialForm VARCHAR(255);
-- 材料批号
DECLARE batchNo VARCHAR(255);
-- 材料热处理状态
DECLARE heatCondition VARCHAR(255);
-- 技术状态
DECLARE technicalState VARCHAR(255);
-- 试样类型
DECLARE materialType VARCHAR(255);
-- 试样数量
DECLARE quantity VARCHAR(255);
-- 试样尺寸
DECLARE Dimension VARCHAR(255);
-- 样品编号
DECLARE specimenNumber VARCHAR(255);
-- 材料是否存在
DECLARE countMaterial INT;
-- 检测元素及预估范围
DECLARE detectionElement VARCHAR(255);
-- 检测标准
DECLARE testStandard VARCHAR(255);
-- 检测方法
DECLARE testMethod VARCHAR(255);
-- 检测依据
DECLARE judge VARCHAR(255);
-- 判定要求
DECLARE judgeReference VARCHAR(255);
-- 拼接部分定义
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放材料、任务要求部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放后半部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 材料表游标
DECLARE done INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF137, ''), IFNULL(SKF138, ''), IFNULL(SKF139, ''),
IFNULL(SKF140, ''), IFNULL(SKF141, ''), IFNULL(SKF142, ''), IFNULL(SKF143, ''),
IFNULL(SKF144, ''), IFNULL(SKF279, ''),IFNULL(SKF150, ''),IFNULL(SKF148, ''),IFNULL(SKF280, '')
FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 获取报告的基础信息
SELECT IFNULL(SKF69, ''), IFNULL(SKF71, ''), IFNULL(SKF70, ''), IFNULL(SKF72, ''),
IFNULL(SKF73, ''), IFNULL(SKF74, ''),IFNULL(SKF75, ''), IFNULL(SKF76, ''),
IFNULL(SKF77, ''), IFNULL(SKF78, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''),
IFNULL(SKF81, ''), IFNULL(SKF82, '') INTO
client, contact, address, contactNumber,
productHandling, productPostalAddress,reportHandling,reportPostalAddress,
taskName, projectNumber, taskNumber, receivedDate,
requiredDate, agreedDate
FROM SKT8 WHERE SKF68 = orderCode;
SET htmlOrder1 =
CONCAT(
'<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>化学成分检测委托单</title>
<style type="text/css">
body{
font-size: 18px;
text-align: center;
}
table{
width: 100%;
}
.td_1_checkbox{
width: 25%;
}
.table_1_checkbox{
font-size: 18px;
}
.table_NoTopBorder{
border-top: 0px;
}
</style>
</head>
<body>
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">委托单位<br>Client</td>
<td style="width: 40%;">',client ,'</td>
<td style="width: 20%;">联系人<br>Contact</td>
<td>',contact ,'</td>
</tr>
<tr>
<td>地址<br>Address</td>
<td>',address ,'</td>
<td>联系方式<br>Contact Number</td>
<td>',contactNumber ,'</td>
</tr>');
IF productHandling = '委托处理' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理" checked="checked">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSEIF productHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSEIF productHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',productPostalAddress,'<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
END IF;
IF reportHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSEIF reportHandling = '传真' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）" checked="checked">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSEIF reportHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',reportPostalAddress,'<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
END IF;
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">任务名称<br>Task Name</td>
<td style="width: 13%;">',taskName,'</td>
<td style="width: 20%;">项目令号<br>Project Number</td>
<td style="width: 13%;">',projectNumber,'</td>
<td style="width: 20%;">任务编号<br>Task Number</td>
<td>',taskNumber,'</td>
</tr>
<tr>
<td>收样日期<br>Received Date</td>
<td>',receivedDate,'</td>
<td>要求完成日期<br>Required Date</td>
<td>',requiredDate,'</td>
<td>商定完成日期<br>Agreed Date</td>
<td>',agreedDate,'</td>
</tr>
</tbody>
</table>');
-- 判断材料表是否存在
SELECT COUNT(SKF137) INTO countMaterial FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
IF countMaterial > 0 THEN
OPEN cur_1;
FETCH cur_1 INTO materialID ,materialName ,grades ,thicknessDiameter ,
materialForm ,batchNo ,heatCondition, technicalState ,materialType ,
quantity ,Dimension ,specimenNumber;
WHILE done != 1 DO
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="5">试样信息<br>Material</td>
<td style="width: 20%;">样品名称/牌号<br>Material/Grades</td>
<td style="width: 20%;">',materialName,'/',grades,'</td>
<td style="width: 20%;">样品规格<br>Thickness or Diameter</td>
<td>',thicknessDiameter,'</td>
</tr>
<tr>
<td>品种<br>Form</td>
<td>',materialForm,'</td>
<td>材料批号<br>Batch No.</td>
<td>',batchNo,'</td>
</tr>
<tr>
<td>材料热处理状态<br>Condition</td>
<td>',heatCondition,'</td>
<td>技术状态<br>Technical State</td>
<td>',technicalState,'</td>
</tr>
<tr>
<td>试样类型<br>Type</td>
<td>',materialType,'</td>
<td>试样数量<br>Quantity</td>
<td>',quantity,'</td>
</tr>
<tr>
<td>试样尺寸/重量<br>Dimension/mass</td>
<td>',Dimension,'</td>
<td>试样编号<br>Specimen Number</td>
<td>',specimenNumber,'</td>
</tr>
</tbody>
</table>');
-- 获取任务表信息
SELECT IFNULL(GROUP_CONCAT(SKF420), ''), IFNULL(GROUP_CONCAT(SKF174), ''),
IFNULL(GROUP_CONCAT(SKF189), ''), IFNULL(GROUP_CONCAT(SKF179), ''),
IFNULL(GROUP_CONCAT(SKF421), '')
INTO detectionElement,testStandard,testMethod,judge,judgeReference
FROM SKT10 WHERE SKF388 = materialID;
SET htmlOrder2 =
CONCAT(htmlOrder2,
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="2">试验要求<br>Task</td>
<td>
试验及技术要求（检测元素及估计范围, 检测标准/方法、判定依据及要求等）<br>
Test and Technical Requirements (Testing Item, Estimated range, Standard/ method, Judge Reference etc.)
</td>
</tr>
<tr>
<td style="height: 85px; text-align: left;">
检测元素及预估范围：',detectionElement,' <br>
检测标准：',testStandard,' <br>
检测方法：',testMethod,' <br>
判定依据：',judge,' <br>
判定要求：',judgeReference,'
</td>
</tr>
</tbody>
</table>');
FETCH cur_1 INTO materialID ,materialName ,grades ,thicknessDiameter ,
materialForm ,batchNo ,heatCondition, technicalState ,materialType ,
quantity ,Dimension ,specimenNumber;
END WHILE;
ELSE
SET htmlOrder2 =
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="5">试样信息<br>Material</td>
<td style="width: 20%;">样品名称/牌号<br>Material/Grades</td>
<td style="width: 20%;"></td>
<td style="width: 20%;">样品规格<br>Thickness or Diameter</td>
<td></td>
</tr>
<tr>
<td>品种<br>Form</td>
<td></td>
<td>材料批号<br>Batch No.</td>
<td></td>
</tr>
<tr>
<td>材料热处理状态<br>Condition</td>
<td></td>
<td>技术状态<br>Technical State</td>
<td></td>
</tr>
<tr>
<td>试样类型<br>Type</td>
<td></td>
<td>试样数量<br>Quantity</td>
<td></td>
</tr>
<tr>
<td>试样尺寸/重量<br>Dimension/mass</td>
<td></td>
<td>试样编号<br>Specimen Number</td>
<td></td>
</tr>
</tbody>
</table>
<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="2">试验要求<br>Task</td>
<td>
试验及技术要求（检测元素及估计范围, 检测标准/方法、判定依据及要求等）<br>
Test and Technical Requirements (Testing Item, Estimated range, Standard/ method, Judge Reference etc.)
</td>
</tr>
<tr>
<td style="height: 85px;">
</td>
</tr>
</tbody>
</table>';
END IF;
SET htmlOrder3 =
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="text-align: left;" colspan="2">
委托单位代表 Client：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="text-align: left;" colspan="2">
承制单位代表 Undertaking Unit：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="width: 10%;">
承诺<br>
Promise
</td>
<td style="text-align: left;">
委托单位保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical information provided by the client.
</td>
</tr>
</tbody>
</table>
</body>
</html>';
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
/*---------------------------------------------------------------------------------------------------------------------*/
-- 10、腐蚀与环境性能委托单
ELSEIF orderType = 10 THEN
BEGIN
-- 委托单位
DECLARE client VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 地址
DECLARE address VARCHAR(255);
-- 联系方式
DECLARE contactNumber VARCHAR(255);
-- 样品处理
DECLARE productHandling VARCHAR(255);
-- 样品处理邮寄地址
DECLARE productPostalAddress VARCHAR(255);
-- 报告处理
DECLARE reportHandling VARCHAR(255);
-- 报告处理邮寄地址
DECLARE reportPostalAddress VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收料日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE requiredDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 材料ID
DECLARE materialID VARCHAR(255);
-- 材料名称
DECLARE material VARCHAR(255);
-- 牌号
DECLARE grades VARCHAR(255);
-- 材料规格
DECLARE thicknessDiameter VARCHAR(255);
-- 材料热处理状态
DECLARE heatCondition VARCHAR(255);
-- 成型工艺
DECLARE process VARCHAR(255);
-- 材料炉批号
DECLARE batchNo VARCHAR(255);
-- 试样类型
DECLARE materialType VARCHAR(255);
-- 试样数量
DECLARE quantity VARCHAR(255);
-- 试样尺寸
DECLARE dimension VARCHAR(255);
-- 试样编号
DECLARE specimenNumber VARCHAR(255);
-- 材料是否存在
DECLARE countMaterial INT;
-- 检测项目
DECLARE testItem VARCHAR(255);
-- 检测标准
DECLARE testStandard VARCHAR(255);
-- 加载参数
DECLARE loadParameter VARCHAR(255);
-- 试验环境
DECLARE environment VARCHAR(255);
-- 拼接部分定义
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放材料、任务要求部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放后半部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 材料表游标
DECLARE done INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF137, ''), IFNULL(SKF138, ''), IFNULL(SKF139, ''),
IFNULL(SKF140, ''), IFNULL(SKF143, ''), IFNULL(SKF278, ''),
IFNULL(SKF142, ''), IFNULL(SKF279, ''), IFNULL(SKF150, ''),
IFNULL(SKF148, ''), IFNULL(SKF280, '')
FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 获取报告的基础信息
SELECT IFNULL(SKF69, ''), IFNULL(SKF71, ''), IFNULL(SKF70, ''), IFNULL(SKF72, ''),
IFNULL(SKF73, ''), IFNULL(SKF74, ''),IFNULL(SKF75, ''), IFNULL(SKF76, ''),
IFNULL(SKF77, ''), IFNULL(SKF78, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''),
IFNULL(SKF81, ''), IFNULL(SKF82, '') INTO
client, contact, address, contactNumber,
productHandling, productPostalAddress,reportHandling,reportPostalAddress,
taskName, projectNumber, taskNumber, receivedDate,
requiredDate, agreedDate
FROM SKT8 WHERE SKF68 = orderCode;
SET htmlOrder1 =
CONCAT(
'<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>腐蚀与环境性能检测委托</title>
<style type="text/css">
body{
font-size: 18px;
text-align: center;
}
table{
width: 100%;
}
.td_1_checkbox{
width: 25%;
}
.table_1_checkbox{
font-size: 18px;
}
.table_NoTopBorder{
border-top: 0px;
}
</style>
</head>
<body>
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">委托单位<br>Client</td>
<td style="width: 40%;">',client ,'</td>
<td style="width: 20%;">联系人<br>Contact</td>
<td>',contact ,'</td>
</tr>
<tr>
<td>地址<br>Address</td>
<td>',address ,'</td>
<td>联系方式<br>Contact Number</td>
<td>',contactNumber ,'</td>
</tr>');
IF productHandling = '委托处理' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理" checked="checked">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSEIF productHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSEIF productHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',productPostalAddress,'<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
END IF;
IF reportHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSEIF reportHandling = '传真' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）" checked="checked">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSEIF reportHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',reportPostalAddress,'<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
END IF;
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">任务名称<br>Task Name</td>
<td style="width: 13%;">',taskName,'</td>
<td style="width: 20%;">项目令号<br>Project Number</td>
<td style="width: 13%;">',projectNumber,'</td>
<td style="width: 20%;">任务编号<br>Task Number</td>
<td>',taskNumber,'</td>
</tr>
<tr>
<td>收样日期<br>Received Date</td>
<td>',receivedDate,'</td>
<td>要求完成日期<br>Required Date</td>
<td>',requiredDate,'</td>
<td>商定完成日期<br>Agreed Date</td>
<td>',agreedDate,'</td>
</tr>
</tbody>
</table>');
-- 判断材料表是否存在
SELECT COUNT(SKF137) INTO countMaterial FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
IF countMaterial > 0 THEN
OPEN cur_1;
FETCH cur_1 INTO materialID ,material ,grades ,thicknessDiameter ,heatCondition ,process ,
batchNo ,materialType ,quantity ,dimension ,specimenNumber ;
WHILE done != 1 DO
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="4">试样信息<br>Material</td>
<td style="width: 20%;">材料名称/牌号<br>Material/Grades</td>
<td style="width: 20%;">',material,'/',grades,'</td>
<td style="width: 20%;">材料规格<br>Thickness or Diameter</td>
<td>',thicknessDiameter,'</td>
</tr>
<tr>
<td>材料热处理状态/成型工艺<br>Heat Condition/Process</td>
<td>',heatCondition,'/',process,'</td>
<td>材料炉批号<br>Heat No./Batch No.</td>
<td>',batchNo,'</td>
</tr>
<tr>
<td>试样类型<br>Type</td>
<td>',materialType,'</td>
<td>试样数量<br>Quantity</td>
<td>',quantity,'</td>
</tr>
<tr>
<td>试样尺寸<br>Dimension</td>
<td>',dimension,'</td>
<td>试样编号<br>Specimen Number</td>
<td>',specimenNumber,'</td>
</tr>
</tbody>
</table>');
-- 获取任务表信息
SELECT IFNULL(GROUP_CONCAT(SKF175),''), IFNULL(GROUP_CONCAT(SKF174), ''),
IFNULL(GROUP_CONCAT(SKF187), ''), IFNULL(GROUP_CONCAT(SKF173) , '')
INTO testItem, testStandard,loadParameter,environment
FROM SKT10 WHERE SKF388 = materialID;
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="2">任务要求<br>Task</td>
<td>
试验及技术要求（检测项目, 检测标准, 加载参数, 试验环境等）<br>
Test and Technical Requirements (Testing Item, Standard, Loading Parameters, Environment etc.)
</td>
</tr>
<tr>
<td style="height: 85px; text-align: left">
检测项目：',testItem,'<br>
检测标准：',testStandard,'<br>
加载参数：',loadParameter,'<br>
试验环境：',environment,'<br>
</td>
</tr>
</tbody>
</table>');
FETCH cur_1 INTO materialID ,material ,grades ,thicknessDiameter ,heatCondition ,process ,
batchNo ,materialType ,quantity ,dimension ,specimenNumber ;
END WHILE;
ELSE
SET htmlOrder2 =
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="4">试样信息<br>Material</td>
<td style="width: 20%;">材料名称/牌号<br>Material/Grades</td>
<td style="width: 20%;"></td>
<td style="width: 20%;">材料规格<br>Thickness or Diameter</td>
<td></td>
</tr>
<tr>
<td>材料热处理状态/成型工艺<br>Heat Condition/Process</td>
<td></td>
<td>材料炉批号<br>Heat No./Batch No.</td>
<td></td>
</tr>
<tr>
<td>试样类型<br>Type</td>
<td></td>
<td>试样数量<br>Quantity</td>
<td></td>
</tr>
<tr>
<td>试样尺寸<br>Dimension</td>
<td></td>
<td>试样编号<br>Specimen Number</td>
<td></td>
</tr>
</tbody>
</table>
<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="2">任务要求<br>Task</td>
<td>
试验及技术要求（检测项目, 检测标准, 加载参数, 试验环境等）<br>
Test and Technical Requirements (Testing Item, Standard, Loading Parameters, Environment etc.)
</td>
</tr>
<tr>
<td style="height: 85px;"></td>
</tr>
</tbody>
</table>';
END IF;
SET htmlOrder3 =
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="text-align: left;" colspan="2">
委托单位代表 Client：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="text-align: left;" colspan="2">
承制单位代表 Undertaking Unit：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="width: 10%;">
承诺<br>
Promise
</td>
<td style="text-align: left;">
委托单位保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical information provided by the client.
</td>
</tr>
</tbody>
</table>
</body>
</html>';
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
/*-------------------------------------------------------------------------------------------------------*/
-- 11、失效分析委托单
ELSEIF orderType = 11 THEN
BEGIN
-- 委托单位
DECLARE client VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 地址
DECLARE address VARCHAR(255);
-- 联系方式
DECLARE contactNumber VARCHAR(255);
-- 样品处理
DECLARE productHandling VARCHAR(255);
-- 样品处理邮寄地址
DECLARE productPostalAddress VARCHAR(255);
-- 报告处理
DECLARE reportHandling VARCHAR(255);
-- 报告处理邮寄地址
DECLARE reportPostalAddress VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收料日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE requiredDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 材料ID
DECLARE materialID VARCHAR(255);
-- 样品名称
DECLARE specimenName VARCHAR(255);
-- 样品规格
DECLARE thicknessDiameter VARCHAR(255);
-- 样品类型
DECLARE materialType VARCHAR(255);
-- 样品材料名称
DECLARE materialName VARCHAR(255);
-- 牌号
DECLARE grades VARCHAR(255);
-- 材料热处理状态
DECLARE heatCondition VARCHAR(255);
-- 材料炉批号
DECLARE batchNo VARCHAR(255);
-- 样品数量
DECLARE quantity VARCHAR(255);
-- 样品编号
DECLARE specimenNumber VARCHAR(255);
-- 材料是否存在
DECLARE countMaterial INT;
-- 检测项目
DECLARE testItem VARCHAR(255);
-- 检测标准
DECLARE testStandard VARCHAR(255);
-- 检测目的
DECLARE testPurpose VARCHAR(255);
-- 用途
DECLARE testUse VARCHAR(255);
-- 拼接部分定义
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放材料、任务要求部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放后半部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 材料表游标
DECLARE done INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF137, ''), IFNULL(SKF281, ''), IFNULL(SKF283, ''),
IFNULL(SKF285, ''), IFNULL(SKF138, ''), IFNULL(SKF139, ''),
IFNULL(SKF143, ''), IFNULL(SKF142, ''), IFNULL(SKF150, ''),IFNULL(SKF284, '')
FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 获取报告的基础信息
SELECT IFNULL(SKF69, ''), IFNULL(SKF71, ''), IFNULL(SKF70, ''), IFNULL(SKF72, ''),
IFNULL(SKF73, ''), IFNULL(SKF74, ''),IFNULL(SKF75, ''), IFNULL(SKF76, ''),
IFNULL(SKF77, ''), IFNULL(SKF78, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''),
IFNULL(SKF81, ''), IFNULL(SKF82, '') INTO
client, contact, address, contactNumber,
productHandling, productPostalAddress,reportHandling,reportPostalAddress,
taskName, projectNumber, taskNumber, receivedDate,
requiredDate, agreedDate
FROM SKT8 WHERE SKF68 = orderCode;
SET htmlOrder1 =
CONCAT(
'<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>失效分析检测委托单</title>
<style type="text/css">
body{
font-size: 18px;
text-align: center;
}
table{
width: 100%;
}
.td_1_checkbox{
width: 25%;
}
.table_1_checkbox{
font-size: 18px;
}
.table_NoTopBorder{
border-top: 0px;
}
</style>
</head>
<body>
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">委托单位<br>Client</td>
<td style="width: 40%;">',client ,'</td>
<td style="width: 20%;">联系人<br>Contact</td>
<td>',contact ,'</td>
</tr>
<tr>
<td>地址<br>Address</td>
<td>',address ,'</td>
<td>联系方式<br>Contact Number</td>
<td>',contactNumber ,'</td>
</tr>');
IF productHandling = '委托处理' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理" checked="checked">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSEIF productHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSEIF productHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',productPostalAddress,'<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
END IF;
IF reportHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSEIF reportHandling = '传真' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）" checked="checked">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSEIF reportHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',reportPostalAddress,'<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
END IF;
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">任务名称<br>Task Name</td>
<td style="width: 13%;">',taskName,'</td>
<td style="width: 20%;">项目令号<br>Project Number</td>
<td style="width: 13%;">',projectNumber,'</td>
<td style="width: 20%;">任务编号<br>Task Number</td>
<td>',taskNumber,'</td>
</tr>
<tr>
<td>收样日期<br>Received Date</td>
<td>',receivedDate,'</td>
<td>要求完成日期<br>Required Date</td>
<td>',requiredDate,'</td>
<td>商定完成日期<br>Agreed Date</td>
<td>',agreedDate,'</td>
</tr>
</tbody>
</table>');
-- 判断材料表是否存在
SELECT COUNT(SKF137) INTO countMaterial FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
IF countMaterial > 0 THEN
OPEN cur_1;
FETCH cur_1 INTO materialID ,specimenName ,thicknessDiameter ,materialType,
materialName ,grades ,heatCondition,
batchNo ,quantity ,specimenNumber ;
WHILE done != 1 DO
SET htmlOrder2 =
CONCAT(htmlOrder2,
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="4">样品信息<br>Material</td>
<td style="width: 20%;">样品名称<br>Specimen Name</td>
<td style="width: 20%;">',specimenName,'</td>
<td style="width: 20%;">样品规格<br>Thickness or Diameter</td>
<td>',thicknessDiameter,'</td>
</tr>
<tr>
<td>样品类型<br>Type</td>
<td>',materialType,'</td>
<td>样品材料名称/牌号<br>Material/Grades</td>
<td>',materialName,'/',grades,'</td>
</tr>
<tr>
<td>材料热处理状态<br>Heat Treating Condition</td>
<td>',heatCondition,'</td>
<td>材料炉批号<br>Heat No./Batch No.</td>
<td>',batchNo,'</td>
</tr>
<tr>
<td>样品数量<br>Quantity</td>
<td>',quantity,'</td>
<td>样品编号<br>Specimen Number</td>
<td>',specimenNumber,'</td>
</tr>
</tbody>
</table>');
-- 获取任务表信息
SELECT IFNULL(GROUP_CONCAT(SKF175), ''), IFNULL(GROUP_CONCAT(SKF174), ''),
IFNULL(GROUP_CONCAT(SKF180), ''), IFNULL(GROUP_CONCAT(SKF290), '')
INTO testItem, testStandard,testPurpose,testUse
FROM SKT10 WHERE SKF388 = materialID;
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="2">试验要求<br>Task</td>
<td>
试验及技术要求（检测项目、 检测标准、检测目的及用途等）<br>
Test and Technical Requirements (Testing Item, Standard, Loading and Measurement Parameters, Environment etc.)
</td>
</tr>
<tr>
<td style="height: 85px; text-align: left">
注：提供失效样品原材料合格证、样品制造文件、产品安装过程记录、运行工况、运行记录等<br>
检测项目：',testItem,'<br>
检测标准：',testStandard,'<br>
检测目的：',testPurpose,'<br>
检测用途：',testUse ,'
</td>
</tr>
</tbody>
</table>');
FETCH cur_1 INTO materialID ,specimenName ,thicknessDiameter ,materialType,
materialName ,grades ,heatCondition,batchNo ,quantity ,specimenNumber ;
END WHILE;
ELSE
SET htmlOrder2 =
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="4">样品信息<br>Material</td>
<td style="width: 20%;">样品名称<br>Specimen Name</td>
<td style="width: 20%;"></td>
<td style="width: 20%;">样品规格<br>Thickness or Diameter</td>
<td></td>
</tr>
<tr>
<td>样品类型<br>Type</td>
<td></td>
<td>样品材料名称/牌号<br>Material/Grades</td>
<td></td>
</tr>
<tr>
<td>材料热处理状态<br>Heat Treating Condition</td>
<td></td>
<td>材料炉批号<br>Heat No./Batch No.</td>
<td></td>
</tr>
<tr>
<td>样品数量<br>Quantity</td>
<td></td>
<td>样品编号<br>Specimen Number</td>
<td></td>
</tr>
</tbody>
</table>
<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="2">试验要求<br>Task</td>
<td>
试验及技术要求（检测项目、 检测标准、检测目的及用途等）<br>
Test and Technical Requirements (Testing Item, Standard, Loading and Measurement Parameters, Environment etc.)
</td>
</tr>
<tr>
<td style="height: 85px;">
注：提供失效样品原材料合格证、样品制造文件、产品安装过程记录、运行工况、运行记录等<br>
</td>
</tr>
</tbody>
</table>';
END IF;
SET htmlOrder3 =
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="text-align: left;" colspan="2">
委托单位代表 Client：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="text-align: left;" colspan="2">
承制单位代表 Undertaking Unit：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="width: 10%;">
承诺<br>
Promise
</td>
<td style="text-align: left;">
委托单位保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical information provided by the client.
</td>
</tr>
</tbody>
</table>
</body>
</html>';
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
/*------------------------------------------------------------------------------------------------------------------------*/
-- 12、结构强度测试委托单
ELSEIF orderType = 12 THEN
BEGIN
-- 委托单位
DECLARE client VARCHAR(255);
-- 邮寄地址
DECLARE postalAddress VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 联系方式
DECLARE contactNumber VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收样日期
DECLARE receivedOn VARCHAR(255);
-- 要求完成日期
DECLARE finishBefore VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 试验件后处理
DECLARE afterTest VARCHAR(255);
-- 取报告方式
DECLARE reportPickUp VARCHAR(255);
-- 试验件序号
DECLARE sampleID VARCHAR(255);
-- 试验件名称
DECLARE sampleName VARCHAR(255);
-- 试验件编号
DECLARE sampleNumber VARCHAR(255);
-- 材料类型
DECLARE materialsType VARCHAR(255);
-- 生产商名称
DECLARE manufacturer VARCHAR(255);
-- 产品批号
DECLARE batchNumber VARCHAR(255);
-- 其他需要
DECLARE otherDescription VARCHAR(255);
-- 判断试验件是否存在
DECLARE isSampleExist INT;
-- 试验依据
DECLARE testBasis VARCHAR(255);
-- 测试项目
DECLARE testItem VARCHAR(255);
-- 载荷加载方式
DECLARE loadMode VARCHAR(255);
-- 载荷控制方式
DECLARE loadControl VARCHAR(255);
-- 试验件安装要求和安装方案
DECLARE installScheme VARCHAR(255);
-- 夹具信息
DECLARE fixtureInfo VARCHAR(255);
-- 试验环境
DECLARE environment VARCHAR(255);
-- 应变片粘贴方案和采集要求
DECLARE strainGauge VARCHAR(255);
-- 试验过程影像采集要求
DECLARE imageAcquisition VARCHAR(255);
-- 其他要求
DECLARE otherRequirements VARCHAR(255);
-- 拼接部分定义
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放材料、任务要求部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放后半部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 试验件游标定义
DECLARE done INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR SELECT
IFNULL(SKF137,''), IFNULL(SKF138, '') ,IFNULL(SKF280, ''),
IFNULL(SKF151,''), IFNULL(SKF423, ''), IFNULL(SKF424, ''),
IFNULL(SKF146, '') FROM SKT9 WHERE SKF362 = orderCode
AND SKF524 != 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 委托单主表信息
SELECT IFNULL(SKF69, ''), IFNULL(SKF87, ''), IFNULL(SKF71 ,''),
IFNULL(SKF72, ''),IFNULL(SKF77, ''), IFNULL(SKF79,''),
IFNULL(SKF80, ''),IFNULL(SKF81,''),IFNULL(SKF82,''),
IFNULL(SKF88, ''),IFNULL(SKF75,'') INTO
client ,postalAddress ,contact ,contactNumber ,taskName ,
taskNumber ,receivedOn ,finishBefore ,agreedDate ,afterTest ,
reportPickUp FROM SKT8 WHERE SKF68 = orderCode;
SET htmlOrder1 =
CONCAT('<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>结构强度检测委托单</title>
<style type="text/css">
td{
font-size: 18px;
text-align: left;
padding-top: 10px;
padding-bottom: 0px;
}
table{
width: 100%;
border-collapse: collapse;
border: 1px solid;
margin-bottom: 10px;
margin-top: 5px;
}
.table_1{
border: 1px dashed;
text-align: left;
width: 100%;
font-size: 9pt;
}
.label_1{
text-align: left;
font-size: 18px;
font-weight: 600;
text-decoration: underline;
}
.lable_2{
text-align: left;
font-size: 18px;
font-weight: 500;
background-color: yellow;
}
.td_1{
width: 25%;
text-align: left;
}
.td_1_bottomLine{
width: 25%;
border-bottom: 1px solid;
text-align: left;
}
.td_2_bottomLine{
width: 75%;
border-bottom: 1px solid;
text-align: left;
}
</style>
</head>
<body>
<table class="table_1" width="100%" cellpadding="0" cellspacing="0">
<tbody>
<td>
请将表格中以“*”标记的项目填写完整后用电子邮件发送该表格，并为所提供的任何潜在有害物质提供一份MSDS/GHS。我们将在收<br>
到您的样品后，向您发送电子邮件，确认收样。<br>
Please fill out the items with “*” in this form and email this form to us including an MSDS/GHS for any potentially hazardous materials <br>
submitted. You will receive an emailed confirmation after your samples have been received.
</td>
</tbody>
</table>
<label class="label_1">委托信息 - Request info.</label>
<table cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td class="td_1">*委托单位 Client：</td>
<td class="td_1_bottomLine" colspan="3">',client,'</td>
</tr>
<tr>
<td class="td_1">*邮寄地址 Postal Address：</td>
<td class="td_1_bottomLine" colspan="3">',postalAddress,'</td>
</tr>
<tr>
<td class="td_1">*联系人 Contact：</td>
<td class="td_1_bottomLine">',contact,'</td>
<td class="td_1">*联系方式 Contact Number：</td>
<td class="td_1_bottomLine">',contactNumber,'</td>
</tr>
</tbody>
</table>
<table>
<tbody>
<tr>
<td class="td_1">任务名称 Task Name：</td>
<td class="td_1_bottomLine">',taskName,'</td>
<td class="td_1">任务编号 Task Number：</td>
<td class="td_1_bottomLine">',taskNumber,'</td>
</tr>
<tr>
<td class="td_1">收样日期 Received On：</td>
<td class="td_1_bottomLine">',receivedOn,'</td>
<td></td>
<td></td>
</tr>
<tr>
<td class="td_1">*要求完成日期 Finish Before：</td>
<td class="td_1_bottomLine">',finishBefore,'</td>
<td class="td_1">商定完成日期Agreed Date：</td>
<td class="td_1_bottomLine">',agreedDate,'</td>
</tr>');
-- 试验件后处理部分
IF afterTest = '放弃' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_1">*试验件后处理 After Test：</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="放弃" checked="checked">放弃 Give up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="自取">自取 Self-pick up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="邮寄">邮寄 Post</td>
</tr>');
ELSEIF afterTest = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_1">*试验件后处理 After Test：</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="放弃" >放弃 Give up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="自取" checked="checked">自取 Self-pick up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="邮寄">邮寄 Post</td>
</tr>');
ELSEIF afterTest = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_1">*试验件后处理 After Test：</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="放弃" >放弃 Give up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="自取">自取 Self-pick up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="邮寄" checked="checked">邮寄 Post</td>
</tr>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_1">*试验件后处理 After Test：</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="放弃">放弃 Give up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="自取">自取 Self-pick up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" name="邮寄">邮寄 Post</td>
</tr>');
END IF;
-- 取报告方式部分
IF reportPickUp = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="td_1">*取报告方式 Report Pick up：</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="自取" checked="checked">自取 Self-pick up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="传真">传真 Fax</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="邮寄">邮寄 Post</td>
</tr>
</tbody>
</table>
</div>');
ELSEIF reportPickUp = '传真' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="td_1">*取报告方式 Report Pick up：</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="自取">自取 Self-pick up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="传真" checked="checked">传真 Fax</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="邮寄">邮寄 Post</td>
</tr>
</tbody>
</table>
</div>');
ELSEIF reportPickUp = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="td_1">*取报告方式 Report Pick up：</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="自取">自取 Self-pick up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="传真">传真 Fax</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="邮寄" checked="checked">邮寄 Post</td>
</tr>
</tbody>
</table>
</div>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td class="td_1">*取报告方式 Report Pick up：</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="自取">自取 Self-pick up</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="传真">传真 Fax</td>
<td class="td_1"><input type="checkbox" disabled="disabled" value="邮寄">邮寄 Post</td>
</tr>
</tbody>
</table>
</div>');
END IF;
-- 判断试验件是否存在
SELECT COUNT(SKF137) INTO isSampleExist FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
IF isSampleExist > 0 THEN
OPEN cur_1;
FETCH cur_1 INTO sampleID ,sampleName ,sampleNumber ,materialsType ,manufacturer ,
batchNumber ,otherDescription;
WHILE done != 1 DO
SELECT IFNULL(GROUP_CONCAT(SKF179), ''), IFNULL(GROUP_CONCAT(SKF175), ''), IFNULL(GROUP_CONCAT(SKF415), ''),
IFNULL(GROUP_CONCAT(SKF416), ''),IFNULL(GROUP_CONCAT(SKF417), '') , IFNULL(GROUP_CONCAT(SKF351), ''),
IFNULL(GROUP_CONCAT(SKF173), ''),IFNULL(GROUP_CONCAT(SKF418), ''),IFNULL(GROUP_CONCAT(SKF419), ''),
IFNULL(GROUP_CONCAT(SKF186), '') INTO
testBasis, testItem ,loadMode ,
loadControl,installScheme ,fixtureInfo ,
environment ,strainGauge ,imageAcquisition ,
otherRequirements
FROM SKT10 WHERE SKF388 = sampleID;
SET htmlOrder2 =
CONCAT(htmlOrder2,
' <label class="label_1">试验件基本信息 Specimen info.</label>
<table>
<tbody>
<tr>
<td class="td_1">试验件名称：</td>
<td class="td_1_bottomLine">',sampleName,'</td>
<td class="td_1">*试验件编号 Sample Number：</td>
<td class="td_1_bottomLine">',sampleNumber,'</td>
</tr>');
IF materialsType = '金属' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="td_1">*材料类型 Materals type：</td>
<td class="td_1_bottomLine">
<input type="checkbox" disabled="disabled" name="金属" checked="checked">金属
<input type="checkbox" disabled="disabled" name="非金属">非金属
<input type="checkbox" disabled="disabled" name="其他">其他
</td>
</tr>');
ELSEIF materialsType = '非金属' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="td_1">*材料类型 Materals type：</td>
<td class="td_1_bottomLine">
<input type="checkbox" disabled="disabled" name="金属">金属
<input type="checkbox" disabled="disabled" name="非金属" checked="checked">非金属
<input type="checkbox" disabled="disabled" name="其他">其他
</td>
</tr>');
ELSEIF materialsType = '其他' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="td_1">*材料类型 Materals type：</td>
<td class="td_1_bottomLine">
<input type="checkbox" disabled="disabled" name="金属">金属
<input type="checkbox" disabled="disabled" name="非金属">非金属
<input type="checkbox" disabled="disabled" name="其他" checked="checked">其他
</td>
</tr>');
ELSE
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="td_1">*材料类型 Materals type：</td>
<td class="td_1_bottomLine">
<input type="checkbox" disabled="disabled" name="金属">金属
<input type="checkbox" disabled="disabled" name="非金属">非金属
<input type="checkbox" disabled="disabled" name="其他">其他
</td>
</tr>');
END IF;
SET htmlOrder2 =
CONCAT(htmlOrder2,
' <tr>
<td class="td_1">*生产商名称 Manufacturer：</td>
<td class="td_1_bottomLine">',manufacturer,'</td>
<td class="td_1">*产品批号 Batch/Lot Number：</td>
<td class="td_1_bottomLine">',batchNumber,'</td>
</tr>
<tr>
<td class="td_1">其他需要在检测报告中说明的试验件信息：</td>
<td class="td_1_bottomLine" colspan="3">',otherDescription,'</td>
</tr>
</tbody>
</table>
<label class="label_1">试验要求 - Test Requirements</label>
<table>
<tbody>
<tr>
<td class="td_1">*试验依据（提供试验大纲）：</td>
<td class="td_2_bottomLine">',testBasis,'</td>
</tr>
<tr>
<td class="td_1">*测试项目：</td>
<td class="td_2_bottomLine">',testItem,'</td>
</tr>');
IF loadMode LIKE '%连续加载%' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'
<tr>
<td class="td_1">*载荷加载方式：</td>
<td class="td_2_bottomLine">
<input type="checkbox" disabled="disabled" name="连续加载；" checked="checked">连续加载；
<input type="checkbox" disabled="disabled" name="分级加载（需提级加载步骤）">分级加载（需提级加载步骤）
</td>
</tr>');
ELSEIF loadMode LIKE '%分级加载%' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'
<tr>
<td class="td_1">*载荷加载方式：</td>
<td class="td_2_bottomLine">
<input type="checkbox" disabled="disabled" name="连续加载；">连续加载；
<input type="checkbox" disabled="disabled" name="分级加载（需提级加载步骤）" checked="checked">分级加载（需提级加载步骤）
</td>
</tr>');
ELSE
SET htmlOrder2 =
CONCAT(htmlOrder2,
'
<tr>
<td class="td_1">*载荷加载方式：</td>
<td class="td_2_bottomLine">
<input type="checkbox" disabled="disabled" name="连续加载；">连续加载；
<input type="checkbox" disabled="disabled" name="分级加载（需提级加载步骤）">分级加载（需提级加载步骤）
</td>
</tr>');
END IF;
SET htmlOrder2 =
CONCAT(htmlOrder2,
'
<tr>
<td class="td_1">*载荷控制方式：</td>
<td class="td_2_bottomLine">',loadControl,'</td>
</tr>
<tr>
<td class="td_1">*试验件安装要求和安装方案：</td>
<td class="td_2_bottomLine">',installScheme,'</td>
</tr>');
IF fixtureInfo = '已有' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="td_1">*夹具信息 Fixture Info.：</td>
<td class="td_2_bottomLine">
<input type="checkbox" disabled="disabled" name="已有" checked="checked">已有
<input type="checkbox" disabled="disabled" name="新制">新制
<input type="checkbox" disabled="disabled" name="客户提供">客户提供
</td>
</tr>');
ELSEIF fixtureInfo = '新制' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="td_1">*夹具信息 Fixture Info.：</td>
<td class="td_2_bottomLine">
<input type="checkbox" disabled="disabled" name="已有">已有
<input type="checkbox" disabled="disabled" name="新制" checked="checked">新制
<input type="checkbox" disabled="disabled" name="客户提供">客户提供
</td>
</tr>');
ELSEIF fixtureInfo = '客户提供' THEN
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="td_1">*夹具信息 Fixture Info.：</td>
<td class="td_2_bottomLine">
<input type="checkbox" disabled="disabled" name="已有">已有
<input type="checkbox" disabled="disabled" name="新制">新制
<input type="checkbox" disabled="disabled" name="客户提供" checked="checked">客户提供
</td>
</tr>');
ELSE
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<tr>
<td class="td_1">*夹具信息 Fixture Info.：</td>
<td class="td_2_bottomLine">
<input type="checkbox" disabled="disabled" name="已有">已有
<input type="checkbox" disabled="disabled" name="新制">新制
<input type="checkbox" disabled="disabled" name="客户提供">客户提供
</td>
</tr>');
END IF;
SET htmlOrder2 =
CONCAT(htmlOrder2,
' <tr>
<td class="td_1">*试验环境 Environment：</td>
<td class="td_2_bottomLine">',environment,'</td>
</tr>
<tr>
<td class="td_1">*应变黏贴方案和采集要求：</td>
<td class="td_2_bottomLine">',strainGauge,'</td>
</tr>
<tr>
<td class="td_1">*试验过程影像采集要求：</td>
<td class="td_2_bottomLine">',imageAcquisition,'</td>
</tr>
<tr>
<td class="td_1">*其他要求Other Requirements：</td>
<td class="td_2_bottomLine">',otherRequirements,'</td>
</tr>
<tr>
<td colspan="2"><h5 class="lable_2">注：具体大纲方案要求等请以附件形式附在委托单后。</h5></td>
</tr>
</tbody>
</table>');
FETCH cur_1 INTO sampleID ,sampleName ,sampleNumber ,materialsType ,manufacturer ,
batchNumber ,otherDescription;
END WHILE;
CLOSE cur_1;
ELSE
SET htmlOrder2 =
'<label class="label_1">试验件基本信息 Specimen info.</label>
<table>
<tbody>
<tr>
<td class="td_1">试验件名称：</td>
<td class="td_1_bottomLine"></td>
<td class="td_1">*试验件编号 Sample Number：</td>
<td class="td_1_bottomLine"></td>
</tr>
<tr>
<td class="td_1">*材料类型 Materals type：</td>
<td class="td_1_bottomLine">
<input type="checkbox" disabled="disabled" name="金属">金属
<input type="checkbox" disabled="disabled" name="非金属">非金属
<input type="checkbox" disabled="disabled" name="其他">其他
</td>
</tr>
<tr>
<td class="td_1">*生产商名称 Manufacturer：</td>
<td class="td_1_bottomLine"></td>
<td class="td_1">*产品批号 Batch/Lot Number：</td>
<td class="td_1_bottomLine"></td>
</tr>
<tr>
<td class="td_1">其他需要在检测报告中说明的试验件信息：</td>
<td class="td_1_bottomLine" colspan="3"></td>
</tr>
</tbody>
</table>
<label class="label_1">试验要求 - Test Requirements</label>
<table>
<tbody>
<tr>
<td class="td_1">*试验依据（提供试验大纲）：</td>
<td class="td_2_bottomLine"></td>
</tr>
<tr>
<td class="td_1">*测试项目：</td>
<td class="td_2_bottomLine"></td>
</tr>
<tr>
<td class="td_1">*载荷加载方式：</td>
<td class="td_2_bottomLine">
<input type="checkbox" disabled="disabled" name="连续加载；">连续加载；
<input type="checkbox" disabled="disabled" name="分级加载（需提级加载步骤）">分级加载（需提级加载步骤）
</td>
</tr>
<tr>
<td class="td_1">*载荷控制方式：</td>
<td class="td_2_bottomLine">力控 N/min； 位控 mm/min</td>
</tr>
<tr>
<td class="td_1">*试验件安装要求和安装方案：</td>
<td class="td_2_bottomLine"></td>
</tr>
<tr>
<td class="td_1">*夹具信息 Fix Info.：</td>
<td class="td_2_bottomLine">
<input type="checkbox" disabled="disabled" name="已有">已有
<input type="checkbox" disabled="disabled" name="新制">新制
<input type="checkbox" disabled="disabled" name="客户提供">客户提供
</td>
</tr>
<tr>
<td class="td_1">*试验环境 Environment：</td>
<td class="td_2_bottomLine"></td>
</tr>
<tr>
<td class="td_1">*应变黏贴方案和采集要求：</td>
<td class="td_2_bottomLine"></td>
</tr>
<tr>
<td class="td_1">*试验过程影像采集要求：</td>
<td class="td_2_bottomLine"></td>
</tr>
<tr>
<td class="td_1">*其他要求Other Requirements：</td>
<td class="td_2_bottomLine"></td>
</tr>
<tr>
<td colspan="2"><label class="lable_2">注：具体大纲方案要求等请以附件形式附在委托单后。</label></td>
</tr>
</tbody>
</table>';
END IF;
SET htmlOrder3 =
' <table border="1">
<tbody>
<tr>
<td>
委托方保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.<br>
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical information provided by the client.
</td>
</tr>
</tbody>
</table>
<table border="1">
<tbody>
<tr>
<td width="50%" style="font-size: 18px;font-weight: 600;text-decoration: underline; height: 100px; vertical-align: top;text-align: center;">
委托方代表 - Client Signature / Date:
</td>
<td style="font-size: 18px;font-weight: 600;text-decoration: underline; height: 100px;vertical-align: top;text-align: center;">
上航检测代表 - SAMST Signature / Date:
</td>
</tr>
</tbody>
</table>
</body>
</html>';
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
/*----------------------------------------------------------------------------------------------------------------*/
-- 13、紧固件性能测试委托单
ELSEIF orderType = 13 THEN
BEGIN
-- 委托单位
DECLARE client VARCHAR(255);
-- 联系人
DECLARE contact VARCHAR(255);
-- 地址
DECLARE address VARCHAR(255);
-- 联系方式
DECLARE contactNumber VARCHAR(255);
-- 样品处理
DECLARE productHandling VARCHAR(255);
-- 样品处理邮寄地址
DECLARE productPostalAddress VARCHAR(255);
-- 报告处理
DECLARE reportHandling VARCHAR(255);
-- 报告处理邮寄地址
DECLARE reportPostalAddress VARCHAR(255);
-- 任务名称
DECLARE taskName VARCHAR(255);
-- 项目令号
DECLARE projectNumber VARCHAR(255);
-- 任务编号
DECLARE taskNumber VARCHAR(255);
-- 收料日期
DECLARE receivedDate VARCHAR(255);
-- 要求完成日期
DECLARE requiredDate VARCHAR(255);
-- 商定完成日期
DECLARE agreedDate VARCHAR(255);
-- 材料ID
DECLARE materialID VARCHAR(255);
-- 样品名称
DECLARE specimenName VARCHAR(255);
-- 样品规格
DECLARE thicknessDiameter VARCHAR(255);
-- 样品材料名称
DECLARE materialName VARCHAR(255);
-- 牌号
DECLARE grades VARCHAR(255);
-- 材料批号
DECLARE batchNo VARCHAR(255);
-- 材料热处理状态
DECLARE heatCondition VARCHAR(255);
-- 成型工艺
DECLARE process VARCHAR(255);
-- 技术状态
DECLARE technicalState VARCHAR(255);
-- 试样数量
DECLARE quantity VARCHAR(255);
-- 样品编号
DECLARE specimenNumber VARCHAR(255);
-- 材料是否存在
DECLARE countMaterial INT;
-- 检测项目
DECLARE testItem VARCHAR(255);
-- 检测标准
DECLARE testStandard VARCHAR(255);
-- 加载及测量参数
DECLARE loadParameters VARCHAR(255);
-- 试验环境
DECLARE environment VARCHAR(255);
-- 拼接部分定义
-- 存放HTML
DECLARE htmlMain TEXT DEFAULT '';
-- 存放HTML拼接的订单前半部分
DECLARE htmlOrder1 TEXT DEFAULT '';
-- 存放材料、任务要求部分
DECLARE htmlOrder2 TEXT DEFAULT '';
-- 存放后半部分
DECLARE htmlOrder3 TEXT DEFAULT '';
-- 材料表游标
DECLARE done INT DEFAULT 0;
DECLARE cur_1 CURSOR FOR
SELECT IFNULL(SKF137, ''), IFNULL(SKF281, ''), IFNULL(SKF283, ''),
IFNULL(SKF138, ''), IFNULL(SKF139, ''), IFNULL(SKF142, ''), IFNULL(SKF143, ''),
IFNULL(SKF278, ''), IFNULL(SKF144, ''),IFNULL(SKF150, ''),IFNULL(SKF280, '')
FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 获取报告的基础信息
SELECT IFNULL(SKF69, ''), IFNULL(SKF71, ''), IFNULL(SKF70, ''), IFNULL(SKF72, ''),
IFNULL(SKF73, ''), IFNULL(SKF74, ''),IFNULL(SKF75, ''), IFNULL(SKF76, ''),
IFNULL(SKF77, ''), IFNULL(SKF78, ''), IFNULL(SKF79, ''), IFNULL(SKF80, ''),
IFNULL(SKF81, ''), IFNULL(SKF82, '') INTO
client, contact, address, contactNumber,
productHandling, productPostalAddress,reportHandling,reportPostalAddress,
taskName, projectNumber, taskNumber, receivedDate,
requiredDate, agreedDate
FROM SKT8 WHERE SKF68 = orderCode;
SET htmlOrder1 =
CONCAT(
'<!DOCTYPE html>
<html>
<head>
<meta charset="gb2312">
<title>紧固件性能检测委托单</title>
<style type="text/css">
body{
font-size: 18px;
text-align: center;
}
table{
width: 100%;
}
.td_1_checkbox{
width: 25%;
}
.table_1_checkbox{
font-size: 18px;
}
.table_NoTopBorder{
border-top: 0px;
}
</style>
</head>
<body>
<table border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">委托单位<br>Client</td>
<td style="width: 40%;">',client ,'</td>
<td style="width: 20%;">联系人<br>Contact</td>
<td>',contact ,'</td>
</tr>
<tr>
<td>地址<br>Address</td>
<td>',address ,'</td>
<td>联系方式<br>Contact Number</td>
<td>',contactNumber ,'</td>
</tr>');
IF productHandling = '委托处理' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理" checked="checked">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSEIF productHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSEIF productHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',productPostalAddress,'<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
'<tr>
<td class="td_top1_1" style="width: 20%">样品处理<br>Handling of Specimens</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="委托处理" value="委托处理">委托处理<br>Commissioned Handling</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>');
END IF;
IF reportHandling = '自取' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取" checked="checked">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSEIF reportHandling = '传真' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）" checked="checked">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSEIF reportHandling = '邮寄' THEN
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）" checked="checked">邮寄（邮寄地址）',reportPostalAddress,'<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
ELSE
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <tr>
<td>取报告方式<br>Report pick up</td>
<td colspan="3">
<table class="table_1_checkbox">
<tbody>
<tr>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="自取" value="自取">自取<br>Self-pick up</td>
<td class="td_1_checkbox"><input type="checkbox" disabled="disabled" name="传真（号码）" value="传真（号码）">传真（号码）<br>Fax（Number）</td>
<td><input type="checkbox" disabled="disabled" name="邮寄（邮寄地址）" value="邮寄（邮寄地址）">邮寄（邮寄地址）<br>Post (Postal Address)</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>');
END IF;
SET htmlOrder1 =
CONCAT(htmlOrder1,
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;">任务名称<br>Task Name</td>
<td style="width: 13%;">',taskName,'</td>
<td style="width: 20%;">项目令号<br>Project Number</td>
<td style="width: 13%;">',projectNumber,'</td>
<td style="width: 20%;">任务编号<br>Task Number</td>
<td>',taskNumber,'</td>
</tr>
<tr>
<td>收样日期<br>Received Date</td>
<td>',receivedDate,'</td>
<td>要求完成日期<br>Required Date</td>
<td>',requiredDate,'</td>
<td>商定完成日期<br>Agreed Date</td>
<td>',agreedDate,'</td>
</tr>
</tbody>
</table>');
-- 判断材料表是否存在
SELECT COUNT(SKF137) INTO countMaterial FROM SKT9 WHERE SKF362 = orderCode AND SKF524 != 1;
IF countMaterial > 0 THEN
OPEN cur_1;
FETCH cur_1 INTO materialID ,specimenName ,thicknessDiameter ,materialName ,
grades ,batchNo ,heatCondition, process,technicalState ,quantity ,specimenNumber;
WHILE done != 1 DO
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="4">样品信息<br>Material</td>
<td style="width: 20%;">样品名称<br>Specimen Name</td>
<td style="width: 20%;">',specimenName,'</td>
<td style="width: 20%;">样品规格<br>Thickness or Diameter</td>
<td>',thicknessDiameter,'</td>
</tr>
<tr>
<td>样品材料名称/牌号<br>Materials/Grades</td>
<td>',materialName,'/',grades,'</td>
<td>材料炉批号<br>Batch No.</td>
<td>',batchNo,'</td>
</tr>
<tr>
<td>材料热处理状态/成型工艺<br>Condition/Process</td>
<td>',heatCondition,'/',process,'</td>
<td>技术状态<br>Technical State</td>
<td>',technicalState,'</td>
</tr>
<tr>
<td>试样数量<br>Quantity</td>
<td>',quantity,'</td>
<td>试样编号<br>Specimen Number</td>
<td>',specimenNumber,'</td>
</tr>
</tbody>
</table>');
-- 获取任务表信息
SELECT IFNULL(GROUP_CONCAT(SKF175), ''), IFNULL(GROUP_CONCAT(SKF174), ''),
IFNULL(GROUP_CONCAT(SKF187), ''), IFNULL(GROUP_CONCAT(SKF173), '')
INTO testItem,testStandard,loadParameters,environment
FROM SKT10 WHERE SKF388 = materialID;
SET htmlOrder2 =
CONCAT(htmlOrder2,
'<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="2">任务要求<br>Task</td>
<td>
试验及技术要求（检测项目, 检测标准, 加载及测量参数, 试验环境等）<br>
Test and Technical Requirements (Testing Item, Standard, Loading and Measurement Parameters, Environment etc.)
</td>
</tr>
<tr>
<td style="height: 85px; text-align: left;">
检测项目： ',testItem,'<br>
检测标准： ',testStandard,'<br>
加载及测量参数： ',loadParameters,'<br>
试验环境： ',environment,'
</td>
</tr>
</tbody>
</table>');
FETCH cur_1 INTO materialID ,specimenName ,thicknessDiameter ,materialName ,
grades ,batchNo ,heatCondition, process,technicalState ,quantity ,specimenNumber;
END WHILE;
ELSE
SET htmlOrder2 =
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="4">样品信息<br>Material</td>
<td style="width: 20%;">样品名称<br>Specimen Name</td>
<td style="width: 20%;"></td>
<td style="width: 20%;">样品规格<br>Thickness or Diameter</td>
<td></td>
</tr>
<tr>
<td>样品材料名称/牌号<br>Materials/Grades</td>
<td></td>
<td>材料炉批号<br>Batch No.</td>
<td></td>
</tr>
<tr>
<td>材料热处理状态/成型工艺<br>Condition/Process</td>
<td></td>
<td>技术状态<br>Technical State</td>
<td></td>
</tr>
<tr>
<td>试样数量<br>Quantity</td>
<td></td>
<td>试样编号<br>Specimen Number</td>
<td></td>
</tr>
</tbody>
</table>
<table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 20%;" rowspan="2">任务要求<br>Task</td>
<td>
试验及技术要求（检测项目, 检测标准, 加载及测量参数, 试验环境等）<br>
Test and Technical Requirements (Testing Item, Standard, Loading and Measurement Parameters, Environment etc.)
</td>
</tr>
<tr>
<td style="height: 85px; text-align: left;">
</td>
</tr>
</tbody>
</table>';
END IF;
SET htmlOrder3 =
' <table class="table_NoTopBorder" border="1" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="text-align: left;" colspan="2">
委托单位代表 Client：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="text-align: left;" colspan="2">
承制单位代表 Undertaking Unit：<br>
签字 Signature：<br>
日期 Date：
</td>
</tr>
<tr>
<td style="width: 10%;">
承诺<br>
Promise
</td>
<td style="text-align: left;">
委托单位保证对所提供的一切资料、实物的真实性负责。<br>
本公司保证检测的公正性，对检测数据负责，对委托单位所提供的技术资料保密。<br>
The client should be responsible for the truth of all documents and objects provided.
Our company guarantees the impartiality of the test, responsible for the accuracy of testing data and confidentiality of technical information provided by the client.
</td>
</tr>
</tbody>
</table>
</body>
</html>';
-- 开始拼接最终HTML页面
SET htmlMain = CONCAT(htmlOrder1,htmlOrder2,htmlOrder3);
-- 插入到html表
START TRANSACTION;
IF htmlMain IS NOT NULL AND htmlMain != '' THEN
DELETE FROM SKT32 WHERE SKF571 = orderCode;
INSERT INTO SKT32 (SKF571, SKF572) VALUE (orderCode, htmlMain);
SET pReturn = 1;
ELSE
-- 设置返回值为0
SET pReturn = 0;
END IF;
COMMIT;
END;
-- 委托单类型不存在
ELSE
SET pReturn = 0;
END IF;
END;