protected void btn_Export_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlSheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 7]).set_MergeCells(true);
xlSheet.ActiveSheet.Cells[1, 1] = "加班记录(" + txts_time1.Text + "至" + txts_time2.Text + ")";
//xlSheet.get_Range(xlSheet.Cells[2, 13], xlSheet.Cells[2, 20]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[2, 1]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 2], xlSheet.Cells[1, 4]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 5], xlSheet.Cells[1, 7]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 8], xlSheet.Cells[1, 10]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 11], xlSheet.Cells[1, 13]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 14], xlSheet.Cells[1, 16]).set_MergeCells(true);
xlSheet.ActiveSheet.Cells[2, 1] = "序号";
xlSheet.ActiveSheet.Cells[2, 2] = "部门";
xlSheet.ActiveSheet.Cells[2, 3] = "加班日期";
xlSheet.ActiveSheet.Cells[2, 4] = "加班人员";
xlSheet.ActiveSheet.Cells[2, 5] = "加班时间段";
xlSheet.ActiveSheet.Cells[2, 6] = "加班小时数";
xlSheet.ActiveSheet.Cells[2, 7] = "工作内容";
for (int i = 0; i < grdvw_List.Rows.Count; i++)
{
if (grdvw_List.Rows[i].Cells[0].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 1] = grdvw_List.Rows[i].Cells[0].Text;
}
if (grdvw_List.Rows[i].Cells[2].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 2] = grdvw_List.Rows[i].Cells[2].Text;
}
if (grdvw_List.Rows[i].Cells[3].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 3] = grdvw_List.Rows[i].Cells[3].Text;
}
if (grdvw_List.Rows[i].Cells[4].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 4] = grdvw_List.Rows[i].Cells[4].Text;
}
if (grdvw_List.Rows[i].Cells[5].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 5] = grdvw_List.Rows[i].Cells[5].Text;
}
if (grdvw_List.Rows[i].Cells[6].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 6] = grdvw_List.Rows[i].Cells[6].Text;
}
if (grdvw_List.Rows[i].Cells[7].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 7] = grdvw_List.Rows[i].Cells[7].Text;
}
}
try
{
string strFileName = "加班记录(" + txts_time1.Text + "至" + txts_time2.Text + ").xls";
xlSheet.Export(Server.MapPath(".") + "\\" + strFileName, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "window.location.href='ShowXls.aspx?file_name=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + "';", true);
RemoveFiles(Server.MapPath("."));
}
catch
{
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Session["PatientID"] != null)
{
try
{
patientid = Convert.ToInt32(Session["PatientId"]);
IReports theQBuilderReports = (IReports)ObjectFactory.CreateInstance("BusinessProcess.Reports.BReports, BusinessProcess.Reports");
theRepDS = theQBuilderReports.GetbluecartIEFUinfo(patientid);
// theRepDS.WriteXml("c:\\TestHL7.xml");
#region "TableNames"
// Moh reg
theRepDS.Tables[0].TableName = "patientinfo";
theRepDS.Tables[1].TableName = "EmergContact";
theRepDS.Tables[2].TableName = "Referred";
theRepDS.Tables[3].TableName = "PrevARV";
theRepDS.Tables[4].TableName = "HIVDiagnosis";
theRepDS.Tables[5].TableName = "patientAllergy";
theRepDS.Tables[6].TableName = "FamilyInfo";
theRepDS.Tables[7].TableName = "patientlabresults";
theRepDS.Tables[8].TableName = "firstgegimen";
// theRepDS.Tables[9].TableName = "secondregimen";
// Moh visit
theRepDS.Tables[9].TableName = "IEFuinfopervisit";
theRepDS.Tables[10].TableName = "Disclosure";
theRepDS.Tables[11].TableName = "NewOisOtherProblems";
// theRepDS.Tables[12].TableName = "Assessment";
theRepDS.Tables[12].TableName = "CotrimoxazoleAdherence";
theRepDS.Tables[13].TableName = "INHdrug";
theRepDS.Tables[14].TableName = "sideeffect";
theRepDS.Tables[15].TableName = "Patientvisitinfo";
theRepDS.Tables[16].TableName = "Tuberclulosis";
theRepDS.Tables[17].TableName = "otherMedication";
theRepDS.Tables[18].TableName = "labinvestigation";
theRepDS.Tables[19].TableName = "ArvdrugAdherence";
theRepDS.Tables[20].TableName = "HIVTest";
#endregion
// Use For HL7 add namespace <ClinicalDocument xmlns=""urn:hl7-org:v3"">")
StringBuilder a = new StringBuilder();
StringBuilder b = new StringBuilder();
StringBuilder c = new StringBuilder();
StringBuilder d = new StringBuilder();
b.Append(@"<?xml version=""1.0""?><ClinicalDocument xmlns=""urn:hl7-org:v3"">");
d.Append("</ClinicalDocument>");
//Response.Redirect("..\\ExcelFiles\\TZNACPMonthlyReport.xls");
theRepDS.WriteXml(Server.MapPath("..\\XMLFiles\\HL7\\" + patientid.ToString() + "HLData.xml"), XmlWriteMode.WriteSchema);
// Use For HL7 new xml file with namespace
XmlDocument doc1 = new XmlDocument();
doc1.Load(Server.MapPath("..\\XMLFiles\\HL7\\" + patientid.ToString() + "HLData.xml"));
XmlNode rootNode = doc1.SelectSingleNode("NewDataSet");
a.Append(rootNode.OuterXml.ToString());
c.Append(b).Append(a).Append(d);
doc1 = new XmlDocument();
doc1.LoadXml(c.ToString());
doc1.Save(Server.MapPath("..\\XMLFiles\\HL7\\" + patientid.ToString() + "HLData.xml"));
//theRepDS.WriteXml(Server.MapPath("..\\XMLFiles\\HL7\\" + patientid.ToString() + "HLData.xml"));
Excel.SpreadsheetClass theApp = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
string theFilePath = Server.MapPath("..\\ExcelFiles\\Templates\\Bluecard.xml");
theApp.XMLURL = theFilePath;
fillMohRegSheet(theApp);
fillMohVisitsheet(theApp);
// theApp.Export(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionOpenInExcel, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportAsAppropriate);
theApp.Export(Server.MapPath("..\\ExcelFiles\\Bluecard.xls"), Excel.SheetExportActionEnum.ssExportActionNone, Excel.SheetExportFormat.ssExportXMLSpreadsheet);
//theUtils.OpenExcelFile(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Response);
IQWebUtils theUtl = new IQWebUtils();
theUtl.ShowExcelFile(Server.MapPath("..\\ExcelFiles\\Bluecard.xls"), Response);
releaseObject(theApp);
}
catch (System.IO.DirectoryNotFoundException exd)
{
MsgBuilder theBuilder = new MsgBuilder();
theBuilder.DataElements["MessageText"] = exd.Message.ToString();
IQCareMsgBox.Show("#C1", theBuilder, this);
}
catch (COMException cex)
{
throw cex;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
private void Query(int Export)
{
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlSheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[1, 1] = "月份";
xlSheet.ActiveSheet.Cells[1, 2] = "例行监测";
xlSheet.ActiveSheet.Cells[1, 3] = "委托监测";
xlSheet.ActiveSheet.Cells[1, 4] = "数据总量";
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 4]).Font.set_Bold(true);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 4]).Font.set_Size(10);
}
DateTime dtStartTime, dtEndTime;
DateTime dt = Convert.ToDateTime(txt_StartTime.Text);
DateTime dt2 = Convert.ToDateTime(txt_EndTime.Text);
dtStartTime = Convert.ToDateTime(dt.Year + "-" + dt.Month + "-1");
dtEndTime = Convert.ToDateTime(dt2.Year + "-" + dt2.Month + "-1");
dtEndTime = dtEndTime.AddMonths(1);
int subMonth = int.Parse(dt2.Month.ToString()) - int.Parse(dt.Month.ToString()) + 1;
strTable = "<table id='tableid' class='listTable' border='0' cellspacing='1' width='90%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 监测数据统计表</b></font></caption><tbody><tr align='center'><th>月份</th><th>监测报告</th><th>测试报告</th><th>数据总量</th></tr>";
strTableP = "<table id='tableid' style= 'BORDER-COLLAPSE:collapse' borderColor= '#00000' border= '1' width='95%'><caption><FONT style='size=3 HEIGHT: 50px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 监测数据统计表</b></font></caption><tbody><tr align='center'><th>月份</th><th>监测报告</th><th>测试报告</th><th>数据总量</th></tr>";
//string strSql = "select m as [Date],";
//strSql += "SUM(CASE WHEN datepart(month, AccessDate) = m and ItemType <> 13 THEN 1 ELSE 0 END) AS 监测报告,";
//strSql += "SUM(CASE WHEN datepart(month, AccessDate) = m and ItemType = 13 THEN 1 ELSE 0 END) AS 测试报告, ";
//strSql += "SUM(CASE WHEN datepart(month, AccessDate) = m and ItemType <> '' THEN 1 ELSE 0 END) AS 数据总量 ";
//strSql += "from t_M_SampleInfor c,( select month('" + dtStartTime + "') m ";
string strSql = "select m as [Date],";
strSql += "SUM(CASE WHEN datepart(month, n.fxdate) = m and r.rwclass =0 THEN 1 ELSE 0 END) AS 例行监测,";
strSql += "SUM(CASE WHEN datepart(month, n.fxdate) = m and r.rwclass =1 THEN 1 ELSE 0 END) AS 委托监测,";
strSql += "SUM(CASE WHEN datepart(month, n.fxdate) = m and r.ItemType <> '' THEN 1 ELSE 0 END) AS 数据总量 ";
strSql += "from t_M_ReporInfo r,t_m_sampleinfor s,t_MonitorItemDetail n,( select month('" + dtStartTime + "') m ";
for (int mth = 1; mth < subMonth; mth++)
{
strSql += " union all select " + (int.Parse(dt.Month.ToString()) + mth).ToString();
}
strSql += ") aa ";
strSql += "where n.fxdate >= '" + dtStartTime + "' and n.fxdate < '" + dtEndTime + "' ";
strSql += "and r.id = s.reportid ";
strSql += "and s.SampleID = n.sampleid ";
strSql += "GROUP BY m";
DataSet ds = new MyDataOp(strSql).CreateDataSet();
int m = ds.Tables[0].Rows.Count;
if (m != 0)
{
string theMonths = "";
string jcReportsN = "";
string csReportsN = "";
string sumReportsN = "";
int jcSum = 0;
int csSum = 0;
for (int i = 0; i < m; i++)
{
theMonths = ds.Tables[0].Rows[i][0].ToString() + "月份";
jcReportsN = ds.Tables[0].Rows[i][1].ToString();
csReportsN = ds.Tables[0].Rows[i][2].ToString();
sumReportsN = ds.Tables[0].Rows[i][3].ToString();
jcSum += int.Parse(jcReportsN);
csSum += int.Parse(csReportsN);
strTableC += "<tr align='center'><td>" + theMonths + "</td><td>" + jcReportsN + "</td><td>" + csReportsN + "</td><td>" + sumReportsN + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[i + 2, 1] = theMonths;
xlSheet.ActiveSheet.Cells[i + 2, 2] = jcReportsN;
xlSheet.ActiveSheet.Cells[i + 2, 3] = csReportsN;
xlSheet.ActiveSheet.Cells[i + 2, 4] = sumReportsN;
}
}
strTableC += "<tr align='center'><td>总计</td><td>" + jcSum.ToString() + "</td><td>" + csSum.ToString() + "</td><td>" + (jcSum + csSum).ToString() + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[m + 2, 1] = "总计";
xlSheet.ActiveSheet.Cells[m + 2, 2] = jcSum.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 3] = csSum.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 4] = (jcSum + csSum).ToString();
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m + 2, 4]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m + 2, 4]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
else
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='90%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + txt_StartTime.Text.Trim() + " 00时至" + txt_EndTime.Text.Trim() + " 24时 监测数据统计表</b></font></caption><tbody><tr align='center'><th>月份</th><th>监测报告</th><th>测试报告</th><th>数据总量</th></tr>";
strTableC += "<tr align='center'><td>总计</td><td>-</td><td>-</td><td>-</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[2, 1] = "总计";
xlSheet.ActiveSheet.Cells[2, 2] = "-";
xlSheet.ActiveSheet.Cells[2, 3] = "-";
xlSheet.ActiveSheet.Cells[2, 4] = "-";
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[2, 4]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[2, 4]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
strTableC += "</tbody></table>";
strTable = strTable + strTableC;
strTableP = strTableP + strTableC;
if (Export == 1)
{
//导出报表
try
{
RemoveFiles(Server.MapPath("."));
string strFileName = "监测数据统计表_" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
xlSheet.Export(Server.MapPath(".") + "\\" + strFileName, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
System.Web.UI.ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "window.open('ShowXls.aspx?file_name=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + "');", true);
}
catch
{
}
}
}
private void Query(int Export)
{
string strtitle = "select * from t_R_Menu inner join t_chart_main on uid=t_R_Menu.id where type=3 order by orderid";
// string strtitle = "select * from t_R_Menu where fatherID=30 and id!=49 order by OrderID";
DataSet dstitle = new MyDataOp(strtitle).CreateDataSet();
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlSheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[1, 1] = "序号";
xlSheet.ActiveSheet.Cells[1, 2] = "报告标识";
xlSheet.ActiveSheet.Cells[1, 3] = "项目名称";
xlSheet.ActiveSheet.Cells[1, 4] = "委托单位";
}
int i = 5;
if (txt_StartTime.Text.Trim() != "" && txt_EndTime.Text.Trim() != "")
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='98%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月dd日") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月dd日") + " 受理委托监测列表</b></font></caption><tbody><tr align='center'>";
strTableP = strTable;
}
else if (txt_StartTime.Text.Trim() != "")
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='98%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月dd日") + "之后受理委托监测列表</b></font></caption><tbody><tr align='center'>";
strTableP = strTable;
}
else if (txt_EndTime.Text.Trim() != "")
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='98%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月dd日") + "之前受理委托监测列表</b></font></caption><tbody><tr align='center'>";
strTableP = strTable;
}
else
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='98%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>受理委托监测列表</b></font></caption><tbody><tr align='center'>";
strTableP = strTable;
}
strTable += "<th>序号</th>";
strTable += "<th>报告标识</th>";
strTable += "<th>项目名称</th>";
strTable += "<th>委托单位</th>";
// strTableP = "<table id='tableid' width='98%' style= 'BORDER-COLLAPSE:collapse' borderColor= '#00000' border= '1'> <caption><FONT style='size=3 HEIGHT: 50px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月dd日") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月dd日") + " 受理委托监测列表</b></font></caption><tbody><tr align='center'>";
strTableP += "<th>序号</th>";
strTableP += "<th>报告标识</th>";
strTableP += "<th>项目名称</th>";
strTableP += "<th>委托单位</th>";
foreach (DataRow dr in dstitle.Tables[0].Rows)
{
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[1, i++] = dr["Name"].ToString();
}
strTable += "<th>" + dr["Name"].ToString() + "</th>";
strTableP += "<th>" + dr["Name"].ToString() + "</th>";
}
if (Export == 1)
{
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, i]).Font.set_Bold(true);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, i]).Font.set_Size(10);
}
strTable += "</tr>";
strTableP += "</tr>";
#region 受理流程
string condstr = "";
if (txt_StartTime.Text.Trim() != "")
{
condstr += " and t.ReportAccessDate >= '" + txt_StartTime.Text.Trim() + " 0:00:00" + "'";
}
if (txt_EndTime.Text.Trim() != "")
{
condstr += " and t.ReportAccessDate <= '" + txt_EndTime.Text.Trim() + " 23:59:59" + "'";
}
if (txt_wtQuery.Text.Trim() != "")
{
condstr += " and wtdepart like '%" + txt_wtQuery.Text.Trim() + "%'";
}
if (txt_bsQuery.Text.Trim() != "")
{
condstr += " and ReportName like '%" + txt_bsQuery.Text.Trim() + "%'";
}
if (drop_fanganQuery.SelectedValue.ToString().Trim() != "")
{
condstr += " and hanwether=1 and statusID>'0'";
}
if (drop_statusQuery.SelectedIndex != drop_statusQuery.Items.Count - 1)
{
switch (drop_statusQuery.SelectedValue.ToString())
{
case "0":
condstr += " and statusID='0' and wether=0"; //受理中
break;
case "1":
condstr += " and statusID='1'"; //初审中
break;
case "1.5":
condstr += " and statusID='1.5' and hanwether=0"; break; //指派中（不出方案）
case "1.6":
condstr += " and statusID='1.5' and hanwether=1"; //指派中（出方案）
break;
case "1.7":
condstr += " and statusID='0' and wether=1"; //初审不通过
break;
case "2":
condstr += " and statusID='2' and wether=0 and hanwether=1"; //踏勘中
break;
case "2.5":
condstr += " and statusID='3' and tkwether=1 and hanwether=1"; //函编写
break;
case "3":
condstr += " and statusID='3' and tkwether=0 and hanwether=1"; //方案编写中
break;
case "4":
condstr += " and statusID='4' and tkwether=0 and hanwether=1"; //报告编制中
break;
case "5":
condstr += " and statusID='5' and tkwether=0 and hanwether=1"; //报告编制完成
break;
case "6":
condstr += " and statusID='5' and tkwether=1 and hanwether=1"; //函编写完成
break;
}
}
string strSql = "select * from t_Y_FlowInfo t where 1=1 " + condstr + " order by t.ReportAccessDate desc";
DataSet ds = new MyDataOp(strSql).CreateDataSet();
int m = ds.Tables[0].Rows.Count;
if (m != 0)
{
string wtdw = "";
string itemname = "";
string accessman = "";
string accessdate = "";
string accessremark = "";
string varman1 = "";
string vardate1 = "";
string varremark1 = "";
string varman2 = "";
string vardate2 = "";
string varremark2 = "";
string varman3 = "";
string vardate3 = "";
string varremark3 = "";
string varman4 = "";
string vardate4 = "";
string varremark4 = "";
string reportbs = "";
//string vardate5 = "";
//string varremark5 = "";
string varman0 = "";
string vardate0 = "";
string varremark0 = "";
double status = 0;
string strtemp = "select Name,UserID from t_R_UserInfo";
DataSet ds_User = new MyDataOp(strtemp).CreateDataSet();
for (int j = 0; j < m; j++)
{
wtdw = "";
reportbs = "";
itemname = "";
accessman = "";
accessdate = "";
accessremark = "";
varman0 = "";
varman1 = "";
vardate1 = "";
varremark1 = "";
varman2 = "";
vardate2 = "";
varremark2 = "";
varman3 = "";
vardate3 = "";
varremark3 = "";
varman4 = "";
vardate4 = "";
varremark4 = "";
vardate0 = "";
varremark0 = "";
int flag = 1; //是否出方案
int wether = 1; //初审是否通过
int tkwether = 1; //踏勘是否通过
try
{
flag = int.Parse(ds.Tables[0].Rows[j]["hanwether"].ToString());
wether = int.Parse(ds.Tables[0].Rows[j]["wether"].ToString());
tkwether = int.Parse(ds.Tables[0].Rows[j]["tkwether"].ToString());
}
catch
{ }
DataRow[] drs = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["UserID"].ToString() + "'");
if (drs.Length == 1)
{
accessman = drs[0]["Name"].ToString();
}
DataRow[] drvarman0 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["kschargeman"].ToString() + "'");
if (drvarman0.Length == 1)
{
varman0 = drvarman0[0]["Name"].ToString();
}
DataRow[] drvarman1 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["chargeman"].ToString() + "'");
if (drvarman1.Length == 1)
{
varman1 = drvarman1[0]["Name"].ToString();
}
DataRow[] drvarman2 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["varman2"].ToString() + "'");
if (drvarman2.Length == 1)
{
varman2 = drvarman2[0]["Name"].ToString();
}
DataRow[] drvarman3 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["varman3"].ToString() + "'");
if (drvarman3.Length == 1)
{
varman3 = drvarman3[0]["Name"].ToString();
}
if (ds.Tables[0].Rows[j]["varman4"].ToString() != "")
{
DataRow[] drvarman4 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["varman4"].ToString() + "'");
if (drvarman4.Length > 0)
{
varman4 += " 报告编制：" + drvarman4[0]["Name"].ToString();;
}
}
if (ds.Tables[0].Rows[j]["ReportdataUser"].ToString() != "")
{
DataRow[] dryj4 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["ReportdataUser"].ToString() + "'");
if (dryj4.Length > 0)
{
varman4 += "数据移交：" + dryj4[0]["Name"].ToString();;;
}
}
wtdw = ds.Tables[0].Rows[j]["wtdepart"].ToString();
itemname = ds.Tables[0].Rows[j]["Projectname"].ToString();
reportbs = ds.Tables[0].Rows[j]["ReportName"].ToString();
accessdate = ds.Tables[0].Rows[j]["ReportAccessDate"].ToString();
accessremark = ds.Tables[0].Rows[j]["urgent"].ToString();
// varman1 = ds.Tables[0].Rows[j]["varman1"].ToString();
vardate1 = ds.Tables[0].Rows[j]["vardate1"].ToString();
varremark1 = ds.Tables[0].Rows[j]["varremark1"].ToString();
// varman2 = ds.Tables[0].Rows[j]["varman2"].ToString();
vardate2 = ds.Tables[0].Rows[j]["vardate2"].ToString();
varremark2 = ds.Tables[0].Rows[j]["varremark2"].ToString();
// varman3 = ds.Tables[0].Rows[j]["varman3"].ToString();
vardate3 = ds.Tables[0].Rows[j]["vardate3"].ToString();
varremark3 = ds.Tables[0].Rows[j]["varremark3"].ToString();
if (ds.Tables[0].Rows[j]["ReportdataDate"].ToString() != "")
{
vardate4 += "数据移交：" + ds.Tables[0].Rows[j]["ReportdataDate"].ToString();
}
if (ds.Tables[0].Rows[j]["vardate4"].ToString() != "")
{
vardate4 += " 报告编制：" + ds.Tables[0].Rows[j]["vardate4"].ToString();
}
varremark4 += ds.Tables[0].Rows[j]["varremark4"].ToString();
status = double.Parse(ds.Tables[0].Rows[j]["StatusID"].ToString());
strTableC += "<tr><td rowspan = '3'>" + (j + 1).ToString() + "</td><td rowspan = '3' " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ reportbs + "</td><td rowspan = '3' " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ itemname + "</td><td rowspan = '3' " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ wtdw + "</td><td " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ accessman + "</td><td " + BgStyle(status, 1, flag, wether, tkwether) + ">"
+ varman0 + "</td><td " + BgStyle(status, 1.5, flag, wether, tkwether) + ">"
+ varman1 + "</td><td " + BgStyle(status, 2, flag, wether, tkwether) + ">"
+ varman2 + "</td><td " + BgStyle(status, 3, flag, wether, tkwether) + ">"
+ varman3 + "</td>";
strTableC += "<td " + BgStyle(status, 4, flag, wether, tkwether) + ">"
+ varman4 + "</td>";
strTableC += "</td></tr><tr><td " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ accessdate + "</td><td " + BgStyle(status, 1, flag, wether, tkwether) + ">"
+ vardate0 + "</td><td " + BgStyle(status, 1.5, flag, wether, tkwether) + ">"
+ vardate1 + "</td><td " + BgStyle(status, 2, flag, wether, tkwether) + ">"
+ vardate2 + "</td><td " + BgStyle(status, 3, flag, wether, tkwether) + ">"
+ vardate3 + "</td><td " + BgStyle(status, 4, flag, wether, tkwether) + ">"
+ vardate4 + "</td>";
strTableC += "</td></tr><tr><td " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ accessremark + "</td><td " + BgStyle(status, 1, flag, wether, tkwether) + ">"
+ varremark0 + "</td><td " + BgStyle(status, 1.5, flag, wether, tkwether) + ">"
+ varremark1 + "</td><td " + BgStyle(status, 2, flag, wether, tkwether) + ">"
+ varremark2 + "</td><td " + BgStyle(status, 3, flag, wether, tkwether) + ">"
+ varremark3 + "</td>";
strTableC +=
"<td " + BgStyle(status, 4, flag, wether, tkwether) + ">"
+ varremark4 + "</td>";
strTableC += "</tr>";
if (Export == 1)
{
xlSheet.get_Range(xlSheet.Cells[j * 3 + 2, 1], xlSheet.Cells[j * 3 + 4, 1]).set_MergeCells(true);
xlSheet.get_Range(xlSheet.Cells[j * 3 + 2, 2], xlSheet.Cells[j * 3 + 4, 2]).set_MergeCells(true);
xlSheet.get_Range(xlSheet.Cells[j * 3 + 2, 3], xlSheet.Cells[j * 3 + 4, 3]).set_MergeCells(true);
xlSheet.get_Range(xlSheet.Cells[j * 3 + 2, 4], xlSheet.Cells[j * 3 + 4, 4]).set_MergeCells(true);
xlSheet.ActiveSheet.Cells[j * 3 + 2, 1] = (j + 1).ToString();
xlSheet.ActiveSheet.Cells[j * 3 + 2, 2] = reportbs;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 3] = itemname;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 4] = wtdw;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 5] = accessman;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 6] = varman0;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 7] = varman1;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 8] = varman2;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 9] = varman3;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 10] = varman4;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 5] = accessdate;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 6] = vardate0;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 7] = vardate1;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 8] = vardate2;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 9] = vardate3;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 10] = vardate4;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 5] = accessremark;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 6] = varremark0;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 7] = varremark1;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 8] = varremark2;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 9] = varremark3;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 10] = varremark4;
//xlSheet.ActiveSheet.Cells[j * 3 + 4, 9] = varremark4;
}
}
if (Export == 1)
{
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m * 3 + 1, 10]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m * 3 + 1, 10]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
else
{
strTableC += "<tr>";
for (int p = 0; p <= 9; p++)
{
strTableC += "<td>-</td>";
}
strTableC += "</tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[2, 1] = "-";
xlSheet.ActiveSheet.Cells[2, 2] = "-";
xlSheet.ActiveSheet.Cells[2, 3] = "-";
xlSheet.ActiveSheet.Cells[2, 4] = "-";
xlSheet.ActiveSheet.Cells[2, 5] = "-";
xlSheet.ActiveSheet.Cells[2, 6] = "-";
xlSheet.ActiveSheet.Cells[2, 7] = "-";
xlSheet.ActiveSheet.Cells[2, 8] = "-";
xlSheet.ActiveSheet.Cells[2, 9] = "-";
xlSheet.ActiveSheet.Cells[2, 10] = "-";
}
}
#endregion
strTableC += "</tbody></table>";
strTable = strTable + strTableC;
strTableP = strTableP + strTableC;
if (Export == 1)
{
//导出报表
try
{
RemoveFiles(Server.MapPath("."));
string strFileName = "委托监测列表_" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
xlSheet.Export(Server.MapPath(".") + "\\" + strFileName, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
System.Web.UI.ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "window.open('ShowXls.aspx?file_name=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + "');", true);
}
catch
{
}
}
}
private void Query(int Export)
{
int totalM = 0;
string strSql = "";
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlSheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[1, 1] = "样品类型";
xlSheet.ActiveSheet.Cells[1, 2] = "月份";
xlSheet.ActiveSheet.Cells[1, 3] = "例行监测";
xlSheet.ActiveSheet.Cells[1, 4] = "委托监测";
xlSheet.ActiveSheet.Cells[1, 5] = "数据总量";
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 5]).Font.set_Bold(true);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 5]).Font.set_Size(10);
}
DateTime dtStartTime, dtEndTime;
DateTime dt = Convert.ToDateTime(txt_StartTime.Text);
DateTime dt2 = Convert.ToDateTime(txt_EndTime.Text);
dtStartTime = Convert.ToDateTime(dt.Year + "-" + dt.Month + "-1");
dtEndTime = Convert.ToDateTime(dt2.Year + "-" + dt2.Month + "-1");
dtEndTime = dtEndTime.AddMonths(1);
int subMonth = int.Parse(dt2.Month.ToString()) - int.Parse(dt.Month.ToString()) + 1;
DataSet dstype = new MyDataOp("select * from t_M_AnalysisMainClassEx where 1=1 order by orderid").CreateDataSet();
if (dstype != null && dstype.Tables.Count > 0)
{
int p = 0;
foreach (DataRow dr in dstype.Tables[0].Rows)
{
//string type = "";
//DataSet dstypeitem = new MyDataOp("select * from t_M_AnalysisMainClassEx where class='" + dr["ClassID"] + "'").CreateDataSet();
//if (dstypeitem != null && dstypeitem.Tables.Count > 0 && dstypeitem.Tables[0].Rows.Count == 0)
//{
// type = dr["ClassID"].ToString();
//}
//else
//{
// foreach (DataRow drr in dstypeitem.Tables[0].Rows)
// {
// type += drr["ClassID"].ToString() + ",";
// }
// if(type!=""&&type.Length>0)
// type=type.Substring(0,type.Length-1);
//}
strSql = "select m as [Date],s.typeid,";
strSql += "sum(CASE WHEN datepart(month, n.fxdate) = m and r.rwclass =0 THEN 1 ELSE 0 END) AS 例行监测,";
strSql += "sum(CASE WHEN datepart(month, n.fxdate) = m and r.rwclass = 1 THEN 1 ELSE 0 END) AS 委托监测,";
strSql += "sum(CASE WHEN datepart(month, n.fxdate) = m THEN 1 ELSE 0 END) AS 数据总量 ";
strSql += "from t_M_ReporInfo r,t_m_sampleinfor s,t_MonitorItemDetail n,( select month('" + dtStartTime + "') m ";
for (int mth = 1; mth < subMonth; mth++)
{
strSql += " union all select " + (int.Parse(dt.Month.ToString()) + mth).ToString();
}
strSql += ") aa ";
strSql += "where n.fxdate >= '" + dtStartTime + "' and n.fxdate < '" + dtEndTime + "' ";
strSql += "and s.typeid =(" + dr["ClassID"].ToString() + ") ";
strSql += "and r.id = s.reportid ";
strSql += "and s.SampleID = n.SampleID ";
strSql += "GROUP BY m,s.typeid order by m";
DataSet ds = new MyDataOp(strSql).CreateDataSet();
int m = ds.Tables[0].Rows.Count;
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='90%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 监测数据组成表</b></font></caption><tbody><tr align='center'><th>样品类型</th><th>月份</th><th>例行监测</th><th>委托监测</th><th>数据总量</th></tr>";
strTableP = "<table id='tableid' style= 'BORDER-COLLAPSE:collapse' borderColor= '#00000' border= '1' width='95%'><caption><FONT style='size=3 HEIGHT: 50px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 监测数据组成表</b></font></caption><tbody><tr align='center'><th>样品类型</th><th>月份</th><th>例行监测</th><th>委托监测</th><th>数据总量</th></tr>";
#region 地表水
strTableC += "<tr align='center'><td rowspan = '" + (subMonth + 1).ToString() + "'>" + dr["ClassName"].ToString() + "</td>";
if (m != 0)
{
if (Export == 1)
{
// xlSheet.get_Range(xlSheet.Cells[2 + m * p, 1], xlSheet.Cells[subMonth + 2, 1]).set_MergeCells(true);
xlSheet.ActiveSheet.Cells[2 + m * p, 1] = dr["ClassName"].ToString();
}
string theMonths = "";
string jcReportsN = "";
string csReportsN = "";
string sumReportsN = "";
int jcSum = 0;
int csSum = 0;
strTableC += "<td>"
+ ds.Tables[0].Rows[0][0].ToString() + "月份</td><td>"
+ ds.Tables[0].Rows[0][2].ToString() + "</td><td>"
+ ds.Tables[0].Rows[0][3].ToString() + "</td><td>"
+ ds.Tables[0].Rows[0][4].ToString() + "</tr>";
jcSum = int.Parse(ds.Tables[0].Rows[0][2].ToString());
csSum = int.Parse(ds.Tables[0].Rows[0][3].ToString());
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[2 + m * p, 2] = ds.Tables[0].Rows[0][0].ToString() + "月份";
xlSheet.ActiveSheet.Cells[2 + m * p, 3] = ds.Tables[0].Rows[0][2].ToString();
xlSheet.ActiveSheet.Cells[2 + m * p, 4] = ds.Tables[0].Rows[0][3].ToString();
xlSheet.ActiveSheet.Cells[2 + m * p, 5] = ds.Tables[0].Rows[0][4].ToString();
}
for (int i = 1; i < m; i++)
{
theMonths = ds.Tables[0].Rows[i][0].ToString() + "月份";
jcReportsN = ds.Tables[0].Rows[i][2].ToString();
csReportsN = ds.Tables[0].Rows[i][3].ToString();
sumReportsN = ds.Tables[0].Rows[i][4].ToString();
jcSum += int.Parse(jcReportsN);
csSum += int.Parse(csReportsN);
strTableC += "<tr align='center'><td>" + theMonths + "</td><td>" + jcReportsN + "</td><td>" + csReportsN + "</td><td>" + sumReportsN + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[i * p + 2, 2] = theMonths;
xlSheet.ActiveSheet.Cells[i * p + 2, 3] = jcReportsN;
xlSheet.ActiveSheet.Cells[i * p + 2, 4] = csReportsN;
xlSheet.ActiveSheet.Cells[i * p + 2, 5] = sumReportsN;
}
}
strTableC += "<tr align='center'><td>总计</td><td>" + jcSum.ToString() + "</td><td>" + csSum.ToString() + "</td><td>" + (jcSum + csSum).ToString() + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[m * p + 2, 2] = "总计";
xlSheet.ActiveSheet.Cells[m * p + 2, 3] = jcSum.ToString();
xlSheet.ActiveSheet.Cells[m * p + 2, 4] = csSum.ToString();
xlSheet.ActiveSheet.Cells[m * p + 2, 5] = (jcSum + csSum).ToString();
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m * p + 2, 5]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m * p + 2, 5]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
else
{
strTableC += "<td>" + dt.Month.ToString() + "月份</td><td>0</td><td>0</td><td>0</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[2 + m * p, 2] = dt.Month.ToString() + "月份";
xlSheet.ActiveSheet.Cells[2 + m * p, 3] = "0";
xlSheet.ActiveSheet.Cells[2 + m * p, 4] = "0";
xlSheet.ActiveSheet.Cells[2 + m * p, 5] = "0";
}
for (int mth = 1; mth < subMonth; mth++)
{
strTableC += "<tr align='center'><td>" + (int.Parse(dt.Month.ToString()) + mth).ToString() + "月份</td><td>0</td><td>0</td><td>0</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[mth + 2, 2] = (int.Parse(dt.Month.ToString()) + mth).ToString() + "月份";
xlSheet.ActiveSheet.Cells[mth + 2, 3] = "0";
xlSheet.ActiveSheet.Cells[mth + 2, 4] = "0";
xlSheet.ActiveSheet.Cells[mth + 2, 5] = "0";
}
}
strTableC += "<tr align='center'><td>总计</td><td>0</td><td>0</td><td>0</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[m * p + 2, 2] = "总计";
xlSheet.ActiveSheet.Cells[m * p + 2, 3] = "0";
xlSheet.ActiveSheet.Cells[m * p + 2, 4] = "0";
xlSheet.ActiveSheet.Cells[m * p + 2, 5] = "0";
}
}
totalM += subMonth;
#endregion
p++;
}
}
#region
#endregion
strTableC += "</tbody></table>";
strTable = strTable + strTableC;
strTableP = strTableP + strTableC;
if (Export == 1)
{
//导出报表
try
{
RemoveFiles(Server.MapPath("."));
string strFileName = "监测数据组成表_" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
xlSheet.Export(Server.MapPath(".") + "\\" + strFileName, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
System.Web.UI.ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "window.open('ShowXls.aspx?file_name=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + "');", true);
}
catch
{
}
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
if (validation() == false)
{
return;
}
IQCareUtils theUtils = new IQCareUtils();
if ((ddQuarter.SelectedValue.ToString() != "0") && (txtyears.Text.Trim() != ""))
{
IReports ReportDetails = (IReports)ObjectFactory.CreateInstance("BusinessProcess.Reports.BReports,BusinessProcess.Reports");
theRepQuarterDS = (DataSet)ReportDetails.GetNACPQuarterlyReportData(Convert.ToInt32(ddQuarter.SelectedValue.ToString()), Convert.ToInt32(txtyears.Text.Trim()), Convert.ToInt32(Session["AppLocationId"]));
#region "TableNames"
theRepQuarterDS.Tables[0].TableName = "FacilityInfo1";
theRepQuarterDS.Tables[1].TableName = "Table1Data1";
theRepQuarterDS.Tables[2].TableName = "Table2Data1";
#endregion
Excel.SpreadsheetClass theApp = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
string theFilePath = Server.MapPath("..\\ExcelFiles\\Templates\\TZNACPQuarterlyReport.xml");
theApp.XMLURL = theFilePath;
writeCellWiseInExcel(theApp);
//theApp.Export(Server.MapPath("..\\ExcelFiles\\TZNACPQuarterlyReport.xls"), Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionOpenInExcel, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
theApp.Export(Server.MapPath("..\\ExcelFiles\\TZNACPQuarterlyReport.xls"), Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
//theUtils.OpenExcelFile(Server.MapPath("..\\ExcelFiles\\TZNACPQuarterlyReport.xls"),Response);
IQWebUtils theUtl = new IQWebUtils();
theUtl.ShowExcelFile(Server.MapPath("..\\ExcelFiles\\TZNACPQuarterlyReport.xls"), Response);
}
else if ((ddMonth.SelectedValue.ToString() != "0") && (txtYear.Text.Trim() != ""))
{
int years = Convert.ToInt32(txtYear.Text);
int Months = Convert.ToInt32(ddMonth.SelectedItem.Value);
IReports ReportDetails = (IReports)ObjectFactory.CreateInstance("BusinessProcess.Reports.BReports,BusinessProcess.Reports");
theRepDS = (DataSet)ReportDetails.GetNACPMonthlyReportData(Months, years, Convert.ToInt32(Session["AppLocationId"]));
#region "TableNames"
theRepDS.Tables[0].TableName = "FacilityInfo";
theRepDS.Tables[1].TableName = "Table1Data";
theRepDS.Tables[2].TableName = "Table2Data";
#endregion
//Response.Redirect("..\\ExcelFiles\\TZNACPMonthlyReport.xls");
Excel.SpreadsheetClass theApp = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
string theFilePath = Server.MapPath("..\\ExcelFiles\\Templates\\TanzaniaNACPMonthlyreport.xml");
theApp.XMLURL = theFilePath;
writeCellWiseInExcel(theApp);
// theApp.Export(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionOpenInExcel, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportAsAppropriate);
theApp.Export(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
//theUtils.OpenExcelFile(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Response);
IQWebUtils theUtl = new IQWebUtils();
theUtl.ShowExcelFile(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Response);
}
}
catch (Exception err)
{
MsgBuilder theBuilder = new MsgBuilder();
theBuilder.DataElements["MessageText"] = err.Message.ToString();
IQCareMsgBox.Show("#C1", theBuilder, this);
return;
}
}
private void Query(int Export)
{
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlSheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[1, 1] = "月份";
xlSheet.ActiveSheet.Cells[1, 2] = "南湖区";
xlSheet.ActiveSheet.Cells[1, 3] = "秀洲区";
xlSheet.ActiveSheet.Cells[1, 4] = "联合污水";
xlSheet.ActiveSheet.Cells[1, 5] = "五县";
xlSheet.ActiveSheet.Cells[1, 6] = "其他";
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 6]).Font.set_Bold(true);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 6]).Font.set_Size(10);
}
DateTime dtStartTime, dtEndTime;
DateTime dt = Convert.ToDateTime(txt_StartTime.Text);
DateTime dt2 = Convert.ToDateTime(txt_EndTime.Text);
dtStartTime = Convert.ToDateTime(dt.Year + "-" + dt.Month + "-1");
dtEndTime = Convert.ToDateTime(dt2.Year + "-" + dt2.Month + "-1");
dtEndTime = dtEndTime.AddMonths(1);
int subMonth = int.Parse(dt2.Month.ToString()) - int.Parse(dt.Month.ToString()) + 1;
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='90%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 测试报告数据组成表</b></font></caption><tbody><tr align='center'><th>月份</th><th>南湖区</th><th>秀洲区</th><th>联合污水</th><th>五县</th><th>其他</th></tr>";
strTableP = "<table id='tableid' style= 'BORDER-COLLAPSE:collapse' borderColor= '#00000' border= '1' width='95%'><caption><FONT style='size=3 HEIGHT: 50px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 测试报告数据组成表</b></font></caption><tbody><tr align='center'><th>月份</th><th>南湖区</th><th>秀洲区</th><th>联合污水</th><th>五县</th><th>其他</th></tr>";
string strSql = "select m as [Date],";
strSql += "SUM(CASE WHEN datepart(month, n.ReportDate) = m and r.ClientID = 1 and r.ItemType = 13 THEN n.num ELSE 0 END) AS 南湖区,";
strSql += "SUM(CASE WHEN datepart(month,n.ReportDate) = m and r.ClientID = 2 and r.ItemType = 13 THEN n.num ELSE 0 END) AS 秀洲区,";
strSql += "SUM(CASE WHEN datepart(month, n.ReportDate) = m and r.ClientID = 6 and r.ItemType = 13 THEN n.num ELSE 0 END) AS 联合污水,";
strSql += "SUM(CASE WHEN datepart(month, n.ReportDate) = m and r.ClientID = 5 and r.ItemType = 13 THEN n.num ELSE 0 END) AS 五县,";
strSql += "SUM(CASE WHEN datepart(month, n.ReportDate) = m and r.ClientID <> '' and r.ItemType = 13 THEN n.num ELSE 0 END) AS 总量 ";
strSql += "from t_M_ReporInfo r,t_m_sampleinfor s,t_m_monitoritem n,( select month('" + dtStartTime + "') m ";
for (int mth = 1; mth < subMonth; mth++)
{
strSql += " union all select " + (int.Parse(dt.Month.ToString()) + mth).ToString();
}
strSql += ") aa ";
strSql += "where n.ReportDate >= '" + dtStartTime + "' and n.ReportDate < '" + dtEndTime + "' ";
strSql += "and r.id = s.reportid ";
strSql += "and s.id = n.sampleid ";
strSql += "GROUP BY m";
DataSet ds = new MyDataOp(strSql).CreateDataSet();
int m = ds.Tables[0].Rows.Count;
if (m != 0)
{
string theMonths = "";
string nh = "";
string xz = "";
string lh = "";
string wx = "";
string qt = "";
int nhN = 0;
int xzN = 0;
int lhN = 0;
int wxN = 0;
int qtN = 0;
for (int i = 0; i < m; i++)
{
theMonths = ds.Tables[0].Rows[i][0].ToString() + "月份";
nh = ds.Tables[0].Rows[i][1].ToString();
xz = ds.Tables[0].Rows[i][2].ToString();
lh = ds.Tables[0].Rows[i][3].ToString();
wx = ds.Tables[0].Rows[i][4].ToString();
qt = (int.Parse(ds.Tables[0].Rows[i][5].ToString()) - (int.Parse(nh) + int.Parse(xz) + int.Parse(lh) + int.Parse(wx))).ToString();
nhN += int.Parse(nh);
xzN += int.Parse(xz);
lhN += int.Parse(lh);
wxN += int.Parse(wx);
qtN += int.Parse(qt);
strTableC += "<tr align='center'><td>" + theMonths + "</td><td>" + nh + "</td><td>" + xz + "</td><td>" + lh + "</td><td>" + wx + "</td><td>" + qt + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[i + 2, 1] = theMonths;
xlSheet.ActiveSheet.Cells[i + 2, 2] = nh;
xlSheet.ActiveSheet.Cells[i + 2, 3] = xz;
xlSheet.ActiveSheet.Cells[i + 2, 4] = lh;
xlSheet.ActiveSheet.Cells[i + 2, 5] = wx;
xlSheet.ActiveSheet.Cells[i + 2, 6] = qt;
}
}
strTableC += "<tr align='center'><td>总计</td><td>" + nhN.ToString() + "</td><td>" + xzN.ToString() + "</td><td>" + lhN.ToString() + "</td><td>" + wxN.ToString() + "</td><td>" + qtN.ToString() + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[m + 2, 1] = "总计";
xlSheet.ActiveSheet.Cells[m + 2, 2] = nhN.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 3] = xzN.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 4] = lhN.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 5] = wxN.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 6] = qtN.ToString();
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m + 2, 6]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m + 2, 6]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
else
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='90%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 测试报告数据组成表</b></font></caption><tbody><tr align='center'><th>月份</th><th>南湖区</th><th>秀洲区</th><th>联合污水</th><th>五县</th><th>其他</th></tr>";
strTableC += "<tr align='center'><td>总计</td><td>-</td><td>-</td><td>-</td><td>-</td><td>-</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[2, 1] = "总计";
xlSheet.ActiveSheet.Cells[2, 2] = "-";
xlSheet.ActiveSheet.Cells[2, 3] = "-";
xlSheet.ActiveSheet.Cells[2, 4] = "-";
xlSheet.ActiveSheet.Cells[2, 5] = "-";
xlSheet.ActiveSheet.Cells[2, 6] = "-";
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[2, 6]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[2, 6]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
strTableC += "</tbody></table>";
strTable = strTable + strTableC;
strTableP = strTableP + strTableC;
if (Export == 1)
{
//导出报表
try
{
RemoveFiles(Server.MapPath("."));
string strFileName = "测试报告数据组成表_" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
xlSheet.Export(Server.MapPath(".") + "\\" + strFileName, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
System.Web.UI.ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "window.open('ShowXls.aspx?file_name=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + "');", true);
}
catch
{
}
}
}