查询数据库整理字段
这里是三个模板
声明 DataSet dss = new DataSet();
把数据都添加到dss里面
public string PrintAFlowView(string AFlowID = "0", int ifsee = 0)
{
string refs = "";
string WorkName = "";
string Tabstr = "";
string SHStr = "";
string BH = "";
string MJ = "";
string LWDW = "";
string SWRQ = "";
string LY = "";
string LWWH = "";
string WJFS = "";
string JJCD = "";
string WJBT = "";
string CBRYJ = "";
string ZWWJ = "";
string FJ = "";
//合同处理
string NBBH = "";
string HTBH = "";
string HZDW = "";
string NQRQ = "";
string XMMC = "";
string HTFS = "";
string HTMC = "";
string BZ = "";
string SBBM = "";
string HTJYNR = "";
//发文稿纸
string FWLX = "";
string KMDJ = "";
string JMCD = "";
string ZH = "";
string QF = "";
string CY = "";
string HK = "";
string NK = "";
string NKRQ = "";
string FWFS = "";
string ZS = "";
string CS = "";
string CB = "";
string NR = "";
string BT = "";
var user2 = OAFunction.GetUserInfo("");
string sql = @"select WorkName from VL_OA_AFlow where ID=" + AFlowID + "";
DataTable dtsWDa = DBCtr.SqlTable(sql);
if (dtsWDa.Rows.Count > 0)
{
WorkName = dtsWDa.Rows[0]["WorkName"].ToString();
}
sql = @"select VL_OA_AFlowColumnDetail.Name,[FCValue],DataType,IFSign
from VL_OA_AFlowColumnDetail inner join VL_OA_FlowTempColumn on VL_OA_FlowTempColumn.ID=VL_OA_AFlowColumnDetail.FCID
where FWID='" + AFlowID + "'";//不是签名的
DataTable dtsa = DBCtr.SqlTable(sql);
if (WorkName == "来文处理笺")
{
for (int i = 0; i < dtsa.Rows.Count; i++)
{
if (dtsa.Rows[i]["Name"].ToString() == "编号")
{
BH = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "密级")
{
MJ = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "来文单位")
{
LWDW = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "收文日期")
{
SWRQ = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "来源")
{
LY = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "来文字号")
{
LWWH = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "文件份数")
{
WJFS = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "紧急程度")
{
JJCD = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "文件标题")
{
WJBT = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "承办人意见")
{
CBRYJ = dtsa.Rows[i]["FCValue"].ToString();
}
}
}
else if (WorkName == "合同处理笺")
{
for (int i = 0; i < dtsa.Rows.Count; i++)
{
//合同处理笺
if (dtsa.Rows[i]["Name"].ToString() == "内部编号")
{
NBBH = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "合同编号")
{
HTBH = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "合作单位")
{
HZDW = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "拟签日期")
{
NQRQ = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "项目名称")
{
XMMC = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "合同份数")
{
HTFS = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "合同名称")
{
HTMC = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "备注")
{
BZ = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "上报部门")
{
SBBM = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "合同简要内容")
{
HTJYNR = dtsa.Rows[i]["FCValue"].ToString();
string newline = HTJYNR.Replace( "<br>","\r\n");
newline = newline.Replace("<br/>", "\r\n");
HTJYNR = newline;
}
else if (dtsa.Rows[i]["Name"].ToString() == "密级")
{
MJ = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "紧急程度")
{
JJCD = dtsa.Rows[i]["FCValue"].ToString();
}
}
}
else if (WorkName == "发文稿纸")
{
for (int i = 0; i < dtsa.Rows.Count; i++)
{
if (dtsa.Rows[i]["Name"].ToString() == "发文类型")
{
FWLX = dtsa.Rows[i]["FCValue"].ToString();
}
else if (dtsa.Rows[i]["Name"].ToString() == "快慢等级")
{
KMDJ = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "机密程度")
{
JMCD = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "字号")
{
ZH = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "签发")
{
QF = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "传阅")
{
CY = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "核稿")
{
HK = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "拟稿")
{
NK = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "拟稿日期")
{
NKRQ = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "发文份数")
{
FWFS = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "主送")
{
ZS = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "抄送")
{
CS = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "抄报")
{
CB = dtsa.Rows[i]["FCValue"].ToString();
}else if (dtsa.Rows[i]["Name"].ToString() == "内容")
{
NR = dtsa.Rows[i]["FCValue"].ToString();
BT = "$标题$";
}
else if (dtsa.Rows[i]["Name"].ToString() == "标题")
{
BT = dtsa.Rows[i]["FCValue"].ToString();
}
}
}
sql = @"select * from VL_OA_AFlowAtt where AFlowID=" + AFlowID + "";
DataTable dtatt = DBCtr.SqlTable(sql);
if (dtatt.Rows.Count > 0)
{
foreach (DataRow item in dtatt.Rows)
{
if (!string.IsNullOrWhiteSpace(item["GUID"].ToString()))
{
ZWWJ += item["FileName"].ToString() + "\r\n";
}
else if (true)
{
FJ += item["FileName"].ToString() + "\r\n";
}
}
}
List<object> list = new List<object>();
sql = @"select u.Name,a.* from VL_OA_AFlowReception a
left join VL_OA_UserInfo u on a.UserID=u.ID
where FID=" + AFlowID + "";
DataTable dtaru = DBCtr.SqlTable(sql);
if (dtaru.Rows.Count > 0)
{
if (WorkName == "来文处理笺")
{
foreach (DataRow item in dtaru.Rows)
{
PintOACF pint = new PintOACF();
pint.BH = BH;
pint.MJ = MJ;
pint.LWDW = LWDW;
pint.SWRQ = SWRQ;
pint.LY = LY;
pint.LWWH = LWWH;
pint.WJFS = WJFS;
pint.JJCD = JJCD;
pint.WJBT = WJBT;
pint.CBRYJ = CBRYJ;
pint.ZWWJ = ZWWJ;
pint.FJ = FJ;
pint.ActionName = item["ActionName"].ToString();
pint.Actions = item["Actions"].ToString();
pint.UsreName = item["Name"].ToString();
pint.ReadTime = item["ReadTime"].ToString();
pint.ProcessTime = item["ProcessTime"].ToString();
pint.Status = "未处理";
pint.ProcessText= item["ProcessText"].ToString();
switch (item["Status"].ToString())
{
case "0": pint.Status = "未处理"; break;
case "1": pint.Status = "已经通过"; break;
case "2": pint.Status = "拒绝"; break;
case "3": pint.Status = "退回"; break;
case "4": pint.Status = "已阅"; break;
case "5": pint.Status = "已销假"; break;
}
pint.ActionType = item["ActionType"].ToString();
switch (item["ActionType"].ToString())
{
case "1": pint.ActionType = "串签"; break;
case "2": pint.ActionType = "并签"; break;
}
list.Add(pint);
}
}
else if (WorkName == "合同处理笺")
{
foreach (DataRow item in dtaru.Rows)
{
PintOACF pint = new PintOACF();
pint.NBBH = NBBH;
pint.HTBH = HTBH;
pint.HZDW = HZDW;
pint.NQRQ = NQRQ;
pint.XMMC = XMMC;
pint.HTFS = HTFS;
pint.HTMC = HTMC;
pint.BZ = BZ;
pint.SBBM = SBBM;
pint.HTJYNR = HTJYNR;
pint.ZWWJ = ZWWJ;
pint.FJ = FJ;
pint.ActionName = item["ActionName"].ToString();
pint.Actions = item["Actions"].ToString();
pint.UsreName = item["Name"].ToString();
pint.ReadTime = item["ReadTime"].ToString();
pint.ProcessTime = item["ProcessTime"].ToString();
pint.Status = "未处理";
pint.ProcessText = item["ProcessText"].ToString();
switch (item["Status"].ToString())
{
case "0": pint.Status = "未处理"; break;
case "1": pint.Status = "已经通过"; break;
case "2": pint.Status = "拒绝"; break;
case "3": pint.Status = "退回"; break;
case "4": pint.Status = "已阅"; break;
case "5": pint.Status = "已销假"; break;
}
pint.ActionType = item["ActionType"].ToString();
switch (item["ActionType"].ToString())
{
case "1": pint.ActionType = "串签"; break;
case "2": pint.ActionType = "并签"; break;
}
list.Add(pint);
}
}
else if (WorkName == "发文稿纸")
{
foreach (DataRow item in dtaru.Rows)
{
PintOACF pint = new PintOACF();
pint.FWLX = FWLX;
pint.KMDJ = KMDJ;
pint.JMCD = JMCD;
pint.ZH = ZH;
pint.QF = QF;
pint.CY = CY;
pint.HK = HK;
pint.NK = NK;
pint.NKRQ = NKRQ;
pint.FWFS = FWFS;
pint.ZS = ZS;
pint.CS = CS;
pint.NR = NR;
pint.BT = BT;
pint.CB = CB;
pint.ZWWJ = ZWWJ;
pint.FJ = FJ;
pint.ActionName = item["ActionName"].ToString();
pint.Actions = item["Actions"].ToString();
pint.UsreName = item["Name"].ToString();
pint.ReadTime = item["ReadTime"].ToString();
pint.ProcessTime = item["ProcessTime"].ToString();
pint.Status = "未处理";
pint.ProcessText = item["ProcessText"].ToString();
switch (item["Status"].ToString())
{
case "0": pint.Status = "未处理"; break;
case "1": pint.Status = "已经通过"; break;
case "2": pint.Status = "拒绝"; break;
case "3": pint.Status = "退回"; break;
case "4": pint.Status = "已阅"; break;
case "5": pint.Status = "已销假"; break;
}
pint.ActionType = item["ActionType"].ToString();
switch (item["ActionType"].ToString())
{
case "1": pint.ActionType = "串签"; break;
case "2": pint.ActionType = "并签"; break;
}
list.Add(pint);
}
}
}
DataSet dss = new DataSet();
DataTable dataTable = ListToDataTable(list);
DataTable dt1 = new DataTable();
dt1 = dataTable.Copy();
dt1.TableName = WorkName;
dss.Tables.Add(dt1);
string PrintType = "v";
PrintResult print = new PrintResult();
print.pv = PrintType;
print.data = dss;
print.TN = WorkName;
string report = "";
string text = System.Web.HttpContext.Current.Server.MapPath("/PrintFile/");// CurrentDirectory + "\\PrintFile\\" ;
string path = text + print.TN + ".frl";
if (!System.IO.File.Exists(path))
{
path = text + print.TN + ".vlrep";
if (!System.IO.File.Exists(path))
{
report = "";
}
}
if (!System.IO.File.Exists(path))
{
report = "";
}
else
{
string text2 = System.IO.File.ReadAllText(path);
using (FileStream fileStream = new FileStream(path, FileMode.Open))
{
byte[] array = new byte[fileStream.Length];
fileStream.Read(array, 0, array.Length);
//report = Convert.ToBase64String(array);
}
}
print.report = report;
/*
if (NewHospital == "1")
{
//打印
refs = JsonConvert.SerializeObject(print);
return refs;
}
else
{*/
//转pdf
refs = JsonConvert.SerializeObject(print);
DateTime? effective = null;
return getExpPDF(dss, print.TN, effective);
}
转成pdf方法
public static string getExpPDF(DataSet dataSet, string TN, DateTime? effective = null)
{
string sql = "";
if (effective.HasValue)
{
sql = "SELECT TOP 1 FileURL FROM dbo.VL_Sys_ReportFile WHERE FileName='" + TN + "' AND effective<='" + effective + "' ORDER BY effective desc";
}
else
{
sql = "SELECT TOP 1 FileURL FROM dbo.VL_Sys_ReportFile WHERE FileName='" + TN + "' ORDER BY effective desc";
}
//string path = Path + "/PrintFile/" + TN + ".frl";
//string root = ;
string path = Path + "/PrintFile/" + TN + ".frl";
DataTable dts = DBCtr.SqlTable(sql);
if (dts.Rows.Count > 0)
{
path = dts.Rows[0][0].ToString();
}
else
{
/*if (File.Exists(path))
{*/
DBCtr.ExeScalar("INSERT INTO [VL_Sys_ReportFile]([FileName],[Version],[effective],[FileURL],[AddUser],[AddTime])VALUES('" + TN + "','1.0','2015-12-01','/PrintFile/" + TN + ".frl','sys',getdate())");
/* }*/
}
/*if (File.Exists(Path + path))
{
path = Path + path;
}
else
{
// path = System.Configuration.ConfigurationSettings.AppSettings["FJURL"].ToString() +"/"+ path; ;
}*/
path = Path + path;
Report report = new Report();
report.Load(path);
report.RegisterData(dataSet);
report.Prepare();
PDFExport export = new PDFExport();
string str = "/ExpReports/" + TN + DateTime.Now.ToString("yyMMddHHmmss") + ".pdf";
//string str = TN + DateTime.Now.ToString("yyMMddHHmmss") + ".pdf";
// export the report
report.Export(export, Path + str);
report.Dispose();
export.Dispose();
string obja = str;
return obja;
}
打印效果