ITextSharp 导出pdf

最新推荐文章于 2024-11-16 16:00:02 发布

wangjingjing1014

最新推荐文章于 2024-11-16 16:00:02 发布

阅读量902

点赞数

CC 4.0 BY-SA版权

分类专栏： jQuery

本文链接：https://blog.youkuaiyun.com/wangjingjing1014/article/details/54139085

jQuery 专栏收录该内容

27 篇文章

订阅专栏

本文介绍了一个基于iTextSharp的PDF生成方案，通过前后端交互实现结算清单的查询及导出功能。涵盖前端界面设计、JavaScript交互逻辑、后端处理流程及PDF文档的具体生成细节。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前台页面：

<div id="search01" style="padding:2px 5px;">
合同编号： <input class="easyui-textbox" style="width:80px" id="key_CCode">  
单位名称： <input class="easyui-textbox" style="width:80px" id="key_JiGouMingChen">  
健康顾问： <input class="easyui-textbox" style="width:90px" id="key_jkgw">  
体检日期： <input class="easyui-datebox" style="width:90px" id="key_tjrqA">—<input class="easyui-datebox" style="width:90px" id="key_tjrqB">  &nbsp<br />
收款日期： <input class="easyui-datebox" style="width:90px" id="key_skrqA">—<input class="easyui-datebox" style="width:90px" id="key_skrqB">  &nbsp
<a href="#" class="easyui-linkbutton" iconCls="icon-sum" style="width:120px" OnClick="$('#list_gd_02').datagrid('load');">查询</a>
<a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-redo',plain:true" style="float:right;" onclick="exportPDF();">导出到PDF</a>
<br/>
<font style="line-height:32px;">
应收金额（所有工单之和）：<input class="easyui-numberbox" style="width:90px" id="ysMoney" value="0.00" data-options="precision:2" readonly> 元  
已收金额（所有已收）：<input class="easyui-numberbox" style="width:90px" id="ssMoney" value="0.00" data-options="precision:2" readonly> 元  
公司尾款：<input class="easyui-numberbox" style="width:90px" id="wkMoney" value="0.00" data-options="precision:2" readonly> 元  
</font>
</div>

js方法：

function exportPDF() {
debugger;
$.ajax({
type: "POST",
url: '../module_gdgl/do.aspx?Action=list_js1&key_CCode=' + $('#key_CCode').val()
+ "&key_JiGouMingChen=" + $('#key_JiGouMingChen').val()
+ "&key_tjrqA=" + $('#key_tjrqA').datebox('getValue')
+ "&key_tjrqB=" + $('#key_tjrqB').datebox('getValue')
+ "&key_skrqA=" + $('#key_skrqA').datebox('getValue')
+ "&key_skrqB=" + $('#key_skrqB').datebox('getValue')

+ "&ysMoney=" + $('#ysMoney').val()
+ "&ssMoney=" + $('#ssMoney').val()
+ "&wkMoney=" + $('#wkMoney').val()
,
cache: false,
contentType: "application/json;charset=utf-8",
dataType: "json"
});

后台方法：

private string appPath = System.Configuration.ConfigurationManager.AppSettings["PDFFilePath"];

if (pAction.Equals("list_js1"))
{
Double ysMoney =Convert.ToDouble(HttpContext.Current.Request.QueryString["ysMoney"] ); // ??前null 则取后
Double ssMoney = Convert.ToDouble(HttpContext.Current.Request.QueryString["ssMoney"]); // ??前null 则取后
Double wkMoney = Convert.ToDouble(HttpContext.Current.Request.QueryString["wkMoney"]); // ??前null 则取后

DownloadPDF(ysMoney, ssMoney, wkMoney);
}

[WebMethod]
public void DownloadPDF(Double ysMoney, Double ssMoney, Double wkMoney)
{
try
{
string fileName = "JSQD"+DateTime.Now.ToString("yyyymmddhhmmss")+ ".pdf";
string filePath = appPath + "\\" + fileName;
if (!string.IsNullOrEmpty(filePath))
{
if (GeneralPDF.GeneralPDFFile(filePath, ysMoney,ssMoney,wkMoney))
{
//lblMsg.Text = "抱歉，数据或文件不齐备，暂时不能下载报告！";
}
//foreach (string file in System.IO.Directory.GetFiles(appPath))
//{
// if (file.IndexOf(filePre) >= 0)
// {
// filePath = file;
// fileName = new System.IO.FileInfo(file).Name;
// break;
// }
//}
}
if (File.Exists(filePath))
{
//lblMsg.Text = "报告下载完成！";

string customerID = "";
DownFile(filePath, fileName, customerID);
}
else
{
//lblMsg.Text = "文件路径不存在！";
//log4net.LogManager.GetLogger("ADVASite").Error("【操作日志 - 报告下载】filePath不存在，下载失败！");
}

}
catch (Exception ex)
{
log4net.LogManager.GetLogger("ADVASite").Error("【异常日志 - 报告下载】ReportDownload.btnDownload_Click出现异常： " + ex.Message.ToString() + "--------------StackTrace：" + ex.StackTrace.ToString());
if (ex.Message.Contains("(404) Not Found"))
{
//lblMsg.Text = "抱歉您的体检报告单数据尚未齐备，暂时不能下载，如有疑问请联系华兆客服：68335258。";
}
else
{
//lblMsg.Text = "电子报告下载异常：" + ex.Message.ToString();
}
}
}

/// <summary>
/// 从服务器下载文件
/// </summary>
/// <param name="filePath"></param>
/// <param name="fileName"></param>
private void DownFile(string filePath, string fileName, string customerID)
{
FileInfo fileInfo = new FileInfo(filePath);

System.IO.Stream iStream = null;
byte[] buffer = new Byte[10000];
int length;
long dataToRead;

try
{
iStream = new System.IO.FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read);
dataToRead = iStream.Length;
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.ContentType = "application/pdf"; //文件类型
Response.AddHeader("Content-Length", dataToRead.ToString());//添加文件长度，进而显示进度
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
while (dataToRead > 0)
{
if (Response.IsClientConnected)
{
length = iStream.Read(buffer, 0, 10000);
Response.OutputStream.Write(buffer, 0, length);
Response.Flush();

buffer = new Byte[10000];
dataToRead = dataToRead - length;
}
else
{
dataToRead = -1;
}
}
}
catch (Exception ex)
{
Response.Write("文件下载时出现错误!");
log4net.LogManager.GetLogger("ADVASite").Error("【异常日志 - 报告下载】ReportDownload.DownFile出现异常： " + ex.Message.ToString() + "--------------StackTrace：" + ex.StackTrace.ToString());
}
finally
{
if (iStream != null)
{
iStream.Close();
}
//结束响应，否则将导致网页内容被输出到文件，进而文件无法打开
try
{
Response.End();
}
catch
{
Server.ClearError();
}
}
}

GeneralPDF.cs 文件内容：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using iTextSharp.text.pdf;
using iTextSharp.text;
using iTextSharp;
using System.Runtime.InteropServices;

using System.Text;
using System.Net;

using System.Data;
using System.Data.SqlClient;
using System.IO;

/// <summary>
/// Summary description for GeneralPDF
/// </summary>
public static class GeneralPDF
{
private static string appPath = System.Configuration.ConfigurationManager.AppSettings["PDFFilePath"];

//下面是正式环境对应的数据库连接配置m_HzNewDB_Head、m_HZYS_Server_Head
//private String m_HzNewDB_Head = "[253HZNEWDB].";
private static String m_HZYS_Server_Head = "[80HZYS_Server].";
private static String m_HzNewDB_Head = "";

//下面是测试环境对应的数据库链接配置m_HzNewDB_Head、m_HZYS_Server_Head
//private String m_HzNewDB_Head = "";
//private String m_HZYS_Server_Head = "";

#region GeneralPDFCRYG
public static bool GeneralPDFFile(string fileName,Double ysMoney, Double ssMoney, Double wkMoney)
{
DataTable healthData = getListJsqd();

if (healthData == null )
return false;

//string fileName = appPath + ".Pdf";
File.Delete(fileName);

Document document = new Document(PageSize.A3, 70, 50, 50, 50);

#region font setting
BaseFont baseFont = BaseFont.CreateFont(appPath + @"\font\MSYH.TTF", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
BaseFont baseFontBD = BaseFont.CreateFont(appPath + @"\font\MSYHBD.TTF", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
BaseFont baseFontBD_ST = BaseFont.CreateFont(appPath + @"\font\SIMSUN.TTC,0", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);

iTextSharp.text.Font font9 = new iTextSharp.text.Font(baseFont, 9);
iTextSharp.text.Font font10 = new iTextSharp.text.Font(baseFont, 10);
iTextSharp.text.Font font11 = new iTextSharp.text.Font(baseFont, 11);
iTextSharp.text.Font font12 = new iTextSharp.text.Font(baseFontBD, 12);
iTextSharp.text.Font font24 = new iTextSharp.text.Font(baseFontBD, 24);
iTextSharp.text.Font font11BD = new iTextSharp.text.Font(baseFontBD, 11);
iTextSharp.text.Font font11BDST = new iTextSharp.text.Font(baseFontBD_ST, 11);
#endregion

try
{
PdfWriter writer = PdfWriter.GetInstance(document, new FileStream(fileName, FileMode.Create));
writer.PageEvent = new HeaderAndFooterEvent();
HeaderAndFooterEvent.WHETHER_SHOW_FOOTERHEADER = true;
HeaderAndFooterEvent.WHETHER_YG = true;
document.Open();
HeaderAndFooterEvent.tpl = writer.DirectContent.CreateTemplate(100, 100);

PdfContentByte cb = writer.DirectContent;
cb.AddTemplate(HeaderAndFooterEvent.tpl, 50, 50);

#region first page content
Paragraph p = new Paragraph("结算清单", font24);
p.Alignment = iTextSharp.text.Element.ALIGN_CENTER;
document.Add(p);


#region barcode
//Barcode39 bc39 = new Barcode39();
//bc39.Code = healthData.Customer.WorkNo;
//bc39.AltText = healthData.Customer.WorkNo;
//bc39.TextAlignment = Element.ALIGN_CENTER;
//bc39.Font = BaseFont.CreateFont(appPath + @"\font\MSYHBD.TTF", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
//document.Add(bc39.CreateImageWithBarcode(cb, null, null));
#endregion

iTextSharp.text.pdf.PdfPTable table = new PdfPTable(4);
table.WidthPercentage = 100;
table.HorizontalAlignment = 500;

table.SetWidths(new float[] { 5f, 10f, 5f, 10f });

iTextSharp.text.pdf.PdfPCell cell = new PdfPCell();



p = new Paragraph("\n");
document.Add(p);

#region YG consult
table = new PdfPTable(14);
table.WidthPercentage = 100;
table.HeaderRows = 1;
//table.SetWidths(new float[] { 10f, 5f, 5f, 5f, 5f, 5f });

p = new Paragraph("工单编号", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("单位名称", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("所在部门", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("客户姓名", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("性别", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("年龄", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("产品编号/加项内容", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("价格", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("结算方式", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("结算金额", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("已收金额", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("预约日期", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("体检日期", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

p = new Paragraph("发票号/收据号/结算清单号", font12);
p.Alignment = Element.ALIGN_CENTER;
cell = new PdfPCell();
cell.AddElement(p);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);

foreach (DataRow entity in healthData.Rows)
{
p = new Paragraph(entity["WKCode"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);
p = new Paragraph(entity["JiGouMingChen"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["Bumen"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["NAME"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["Sex"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["Birthday"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["WKProductID"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["WKPrice"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["WKJsfs"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["WKPriceJs"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["WKPricePayed"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["YuyueRiqi"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["TiJianRiQi"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);

p = new Paragraph(entity["CPName"].ToString(), font9);
cell = new PdfPCell();
cell.AddElement(p);
table.AddCell(cell);


}

//document.Add(table);


document.Add(table);
#endregion

p = new Paragraph("\n");
document.Add(p);

p = new Paragraph("应收金额（所有工单之和）：" + ysMoney.ToString(), font12);
document.Add(p);

p = new Paragraph("已收金额（所有已收）:" + ssMoney.ToString(), font12);
document.Add(p);

p = new Paragraph("公司尾款:" + wkMoney.ToString(), font12);
document.Add(p);

p = new Paragraph("\n");
document.Add(p);

p = new Paragraph("华兆益生健康管理机构",font24);
p.Alignment = Element.ALIGN_RIGHT;

document.Add(p);

//PdfContentByte over = stamp.getOverContent(2);
//over.beginText();
//BaseFont bf = BaseFont.createFont(BaseFont.HELVETICA, BaseFont.WINANSI,
// BaseFont.EMBEDDED);
//over.setFontAndSize(bf, 18);
//over.setTextMatrix(30, 30);
//over.showTextAligned(Element.ALIGN_LEFT, "DUPLICATE", 230, 430, 45);
//over.endText();

document.NewPage();

#endregion

writer.Flush();
writer.CloseStream = true;
document.Close();
}
catch (Exception ex)
{
throw ex;
return false;
}
finally
{
if (document.IsOpen())
document.Close();
}
return true;
}
#endregion

#region HeaderAndFooterEvent
public class HeaderAndFooterEvent : PdfPageEventHelper, IPdfPageEvent
{
public static PdfTemplate tpl = null;
public static bool WHETHER_SHOW_FOOTERHEADER = false;
public static bool WHETHER_YG = false;

public override void OnEndPage(PdfWriter writer, Document document)
{
if (WHETHER_SHOW_FOOTERHEADER)
{
BaseFont baseFont = BaseFont.CreateFont(appPath + @"\font\MSYH.TTF", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
iTextSharp.text.Font font = new iTextSharp.text.Font(baseFont, 10);

Phrase header = new Phrase("", font);
Phrase footer = new Phrase("第" + (writer.PageNumber) + "页/共页", font);
PdfContentByte cb = writer.DirectContent;
//模版显示总共页数
if (writer.PageNumber == 1 && WHETHER_YG == false)
{
cb.AddTemplate(tpl, document.Right - 76 - 50 + document.LeftMargin - 5, document.Bottom - 10);//调节模版显示的位置
//页脚显示的位置
ColumnText.ShowTextAligned(cb, Element.ALIGN_CENTER, footer, document.Right - 90 + document.LeftMargin - 50, document.Bottom - 10, 0);
}
else
{
cb.AddTemplate(tpl, document.Right - 76 + document.LeftMargin - 5, document.Bottom - 10);//调节模版显示的位置
//页眉显示的位置
//ColumnText.ShowTextAligned(cb, Element.ALIGN_CENTER, header, document.Right - 200 + document.LeftMargin, document.Top + 10, 0);
//页脚显示的位置
ColumnText.ShowTextAligned(cb, Element.ALIGN_CENTER, footer, document.Right - 90 + document.LeftMargin, document.Bottom - 10, 0);
}
}
}

//重写打开一个新页面时
public override void OnStartPage(PdfWriter writer, Document document)
{
/*if (WHETHER_SHOW_FOOTERHEADER)
{
writer.PageCount = writer.PageNumber - 1;
}*/
}

//关闭PDF文档时发生该事件
public override void OnCloseDocument(PdfWriter writer, Document document)
{
BaseFont bf = BaseFont.CreateFont(appPath + @"\font\MSYH.TTF", BaseFont.IDENTITY_H, false);

tpl.BeginText();
tpl.SetFontAndSize(bf, 10);//生成的模版的字体、颜色
tpl.ShowText((writer.PageNumber - 1).ToString());//模版显示的内容
tpl.EndText();
tpl.ClosePath();
}
}
#endregion

#region 检查FTP文件是否存在
public static string[] GetFileList(string ftpPath, string ftpUserName, string ftpPwd)
{
string[] downloadFiles;
StringBuilder result = new StringBuilder();
FtpWebRequest reqFTP;
try
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpPath));
reqFTP.UseBinary = true;
reqFTP.Credentials = new NetworkCredential(ftpUserName, ftpPwd);
reqFTP.Method = WebRequestMethods.Ftp.ListDirectory;

WebResponse response = reqFTP.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream());
string line = reader.ReadLine();

while (line != null)
{
result.Append(line);
result.Append(" ");
line = reader.ReadLine();
}
// to remove the trailing ' '
result.Remove(result.ToString().LastIndexOf(' '), 1);
reader.Close();
response.Close();
return result.ToString().Split(' ');
}
catch (Exception ex)
{
downloadFiles = null;
return downloadFiles;
}
}
#endregion

/// <summary>
/// 生成结算清单查询表单
/// </summary>
/// <returns></returns>
public static DataTable getListJsqd()
{
String ret = "";
String connString = "";
//读取连接字符串
connString = System.Configuration.ConfigurationManager.ConnectionStrings["IBMDB"].ToString();
String SQL = "";
SqlConnection conn;
DataSet myDataSet;
SqlDataAdapter adapter0;

try
{
String key_CCode = HttpContext.Current.Request.QueryString["key_CCode"] ?? "";
String subsql = "";
String sql = "";
String sqlstr = "";
if (HttpContext.Current.Request.QueryString["key_JiGouMingChen"] != null && !HttpContext.Current.Request.QueryString["key_JiGouMingChen"].ToString().Equals(""))
sql += " and J.JiGouMingChen like '%" + HttpContext.Current.Request.QueryString["key_JiGouMingChen"].ToString().Trim() + "%' ";

if (HttpContext.Current.Request.QueryString["key_jkgw"] != null && !HttpContext.Current.Request.QueryString["key_jkgw"].ToString().Equals(""))
sql += " and J.XiaoShouYuan like '%" + HttpContext.Current.Request.QueryString["key_jkgw"].ToString().Trim() + "%' ";

if (HttpContext.Current.Request.QueryString["key_tjrqA"] != null && !HttpContext.Current.Request.QueryString["key_tjrqA"].ToString().Equals(""))
sql += " and J.TiJianRiQi >= '" + HttpContext.Current.Request.QueryString["key_tjrqA"].ToString() + " 00:00:00' ";
if (HttpContext.Current.Request.QueryString["key_tjrqB"] != null && !HttpContext.Current.Request.QueryString["key_tjrqB"].ToString().Equals(""))
sql += " and J.TiJianRiQi <= '" + HttpContext.Current.Request.QueryString["key_tjrqB"].ToString() + " 23:59:59' ";

if (HttpContext.Current.Request.QueryString["key_skrqA"] != null && !HttpContext.Current.Request.QueryString["key_skrqA"].ToString().Equals(""))
sqlstr += " and I.ItemSkTime >= '" + HttpContext.Current.Request.QueryString["key_skrqA"].ToString() + " 00:00:00' ";
if (HttpContext.Current.Request.QueryString["key_skrqB"] != null && !HttpContext.Current.Request.QueryString["key_skrqB"].ToString().Equals(""))
sqlstr += " and I.ItemSkTime <= '" + HttpContext.Current.Request.QueryString["key_skrqB"].ToString() + " 23:59:59' ";

//建立连接
conn = new SqlConnection(connString);
// 创建DataSet，用于存储数据.
myDataSet = new DataSet();

ret += "{\"total\":100,\"rows\":[";

//分页信息
subsql = " from T_IBM_WorkForm as W left join " + m_HzNewDB_Head + "[HZNewDB].[dbo].[T_Guest_Info] as G on W.GID=G.Gid left join " + m_HzNewDB_Head + "[HZNewDB].[dbo].[T_TiJian] as J on J.HuZhaoHao=W.QzCode and W.WKType='2' where (W.WKType='1' or W.WKType='2') ";
subsql += " and W.WKCode like '" + HttpContext.Current.Request.QueryString["key_CCode"].ToString().Trim() + "%'" + sql;
String SQL1 = "select W.WKID,W.WKCode,J.JiGouMingChen,J.Bumen,G.NAME,G.Sex,datediff(yyyy,G.Birthday,GETDATE()) as Birthday,W.WKProductID,W.WKPrice,W.WKJsfs,W.WKPrice as WKPriceJs,W.WKPricePayed,convert(varchar(10),J.YuyueRiqi,121) as YuyueRiqi,convert(varchar(10),J.TiJianRiQi,121) as TiJianRiQi,( select PayCode +',' from T_IBM_Pay where WKCode=W.WKCode for xml path('')) as CPName,W.WKType " + subsql + "";
/* ,G.Mobile,G.PaperValue,W.WKDateCreate,W.WKBgjffs,W.WKGdjfdz,W.WKCpdj,W.WKDateStart,W.WKDateEnd,W.WKQzfs,W.WKYyfs,W.WKFwlx,W.REMARK,W.WKStatus,W.Deal_USER_Name,W.WKDateDeal,W.WKPrice */

//加项部分的查询结果
subsql = " from T_IBM_WorkForm as W inner join T_IBM_Item as I on W.WKCode=I.WKCode left join " + m_HzNewDB_Head + "[HZNewDB].[dbo].[T_Guest_Info] as G on W.GID=G.Gid left join " + m_HzNewDB_Head + "[HZNewDB].[dbo].[T_TiJian] as J on J.HuZhaoHao=W.QzCode and W.WKType='2' where (W.WKType='1' or W.WKType='2') ";
subsql += " and W.WKCode like '" + HttpContext.Current.Request.QueryString["key_CCode"].ToString().Trim() + "%'" + sql;

String SQL2 = "select W.WKID,W.WKCode,J.JiGouMingChen,J.Bumen,G.NAME,G.Sex,FLOOR(datediff(DY, (CASE G.Birthday WHEN '' THEN getdate() ELSE G.Birthday END), getdate())/365.25) as Birthday,I.GName,I.GPriced,I.ItemJsfs as WKJsfs,I.GPriced as GPriceJs,I.GPricePayed,convert(varchar(10),J.YuyueRiqi,121) as YuyueRiqi,convert(varchar(10),J.TiJianRiQi,121) as TiJianRiQi,I.ItemPayCode,'3' as WKType " + subsql + "" + sqlstr;

SQL = " ( " + SQL1 + " ) UNION ( " + SQL2 + " ) order by WKCode ASC,WKType ASC";

adapter0 = new SqlDataAdapter(SQL, conn);
adapter0.Fill(myDataSet, "T_IBM_Contract");

return myDataSet.Tables["T_IBM_Contract"];
}
catch
{
return new DataTable();
}
}
}