protected void btn_query_Click(object sender, EventArgs e) { //strSelectedId=txt_samplequery.Text; string strSample = ""; //样品编号 string strDate = ""; //接样时间 string strStatus = ""; //样品状态 string strItem = ""; //项目类型 string strType = ""; //样品名称 string strClient = ""; //委托单位 string strAanlysisStatus = ""; //样品分析状态 string strUrgent = ""; //按紧急程度 if (Drop_Urgent.SelectedValue != "-1") { if (Drop_Urgent.SelectedValue == "1") { strUrgent = "and t_M_ReporInfo.Ulevel=1"; } else { strUrgent = "and (t_M_ReporInfo.Ulevel<>1 or t_M_ReporInfo.Ulevel is null)"; } } if (txt_samplequery.Text != "")//按样品编号 { strSample = "and t_M_SampleInfor.SampleID like'%" + txt_samplequery.Text + "%'"; } if (txt_QueryTime.Text.Trim() != "" && txt_QueryEndTime.Text.Trim() != "")//按采样时间 { DateTime start = DateTime.Parse(txt_QueryTime.Text.Trim() + " 00:00:00"); DateTime end = DateTime.Parse(txt_QueryEndTime.Text.Trim() + " 23:59:59"); strDate = " and t_M_SampleInfor.AccessDate between '" + start + "' and '" + end + "'"; //strDate = " and (year(AccessDate)= '" + DateTime.Parse(txt_QueryTime.Text.Trim() + " 00:00:00").Year + "' AND month(AccessDate)= '" + DateTime.Parse(txt_QueryTime.Text.Trim() + " 00:00:00").Month + "' and day(AccessDate)= '" + DateTime.Parse(txt_QueryTime.Text.Trim() + " 00:00:00").Day + "')"; } if (txt_item.SelectedValue != "-1")// 按项目类型 { strItem = " and t_M_ReporInfo.ItemType='" + txt_item.SelectedValue + "' "; } if (txt_type.SelectedValue != "-1")//按样品类型 { strType = " and t_M_SampleInfor.TypeID='" + txt_type.SelectedValue + "' "; } if (drop_client.SelectedValue != "-1")//按委托单位 { strClient = " and t_M_ReporInfo.ClientID='" + drop_client.SelectedValue + "' "; } ////按选中的分析项目查询 string strtitle = ""; string cstr = ""; if (cstr == "") { cstr = " and 1=1"; } cstr = strUrgent + strSample + strDate + strStatus + strItem + strType + strClient + strAanlysisStatus + strtitle; string strSql = "SELECT t_M_SampleInfor.SampleID AS 样品编号, t_M_ReporInfo.projectname 项目名称,t_M_SampleInfor.SampleDate AS 采样时间,t_M_SampleInfor.SampleProperty 样品性状,t_M_SampleInfor.SampleAddress 采样点 ," + "t_M_AnalysisMainClassEx.ClassName AS 样品类型, " + " t_M_SampleInfor.xcflag,t_M_SampleInfor.num 数量" + " FROM t_M_SampleInfor INNER JOIN" + " t_M_AnalysisMainClassEx ON " + " t_M_SampleInfor.TypeID = t_M_AnalysisMainClassEx.ClassID inner join t_M_ReporInfo on t_M_ReporInfo.id=t_M_SampleInfor.ReportID " + " WHERE 1=1 " + cstr + " ORDER BY t_M_SampleInfor.SampleID"; DataSet ds = new MyDataOp(strSql).CreateDataSet(); DataColumn dccc = new DataColumn("分析项目"); ds.Tables[0].Columns.Add(dccc); DataColumn dcc = new DataColumn("现场分析项目"); ds.Tables[0].Columns.Add(dcc); DataColumn dcc2 = new DataColumn("分析项目代码"); ds.Tables[0].Columns.Add(dcc2); string specialstr = "select * from t_SpecialItem"; DataSet ds_sitem = new MyDataOp(specialstr).CreateDataSet(); DataColumn dc = new DataColumn("标签打印项"); ds.Tables[0].Columns.Add(dc); foreach (DataRow dr in ds.Tables[0].Rows) { string getitemstr = "select AIName,AICode,MonitorItem,xcflag from t_MonitorItemDetail inner join t_M_ANItemInf on t_M_ANItemInf.id=t_MonitorItemDetail.MonitorItem where SampleID='" + dr["样品编号"].ToString() + "' and MonitorItem not in(select itemid from t_SpecialItem) and delflag=0 and xcflag=0"; DataSet dsitem = new MyDataOp(getitemstr).CreateDataSet(); if (dsitem != null && dsitem.Tables.Count > 0) { int num = 0; foreach (DataRow drr in dsitem.Tables[0].Rows) { dr["分析项目"] += drr[0].ToString() + ","; num++; if (num % 12 != 0) { if (drr[1].ToString() != "") { dr["分析项目代码"] += drr[1].ToString() + ","; } else { dr["分析项目代码"] += drr[0].ToString() + ","; } } else if (drr[1].ToString() != "") { dr["分析项目代码"] += drr[1].ToString() + ";"; } else { dr["分析项目代码"] += drr[0].ToString() + ";"; } } string temp = dr["分析项目代码"].ToString(); if (temp != "") { temp = temp.Substring(0, temp.Length - 1); dr["分析项目代码"] = temp.ToString(); } } string itemstr = "select AIName,AICode,MonitorItem,relatedItem,xcflag from t_MonitorItemDetail inner join t_M_ANItemInf on t_M_ANItemInf.id=t_MonitorItemDetail.MonitorItem inner join t_SpecialItem on t_SpecialItem.itemid=t_MonitorItemDetail.MonitorItem where SampleID='" + dr["样品编号"].ToString() + "' and delflag=0 and xcflag=0"; DataSet dssitem = new MyDataOp(itemstr).CreateDataSet(); string delindex = ""; if (dssitem != null && dssitem.Tables.Count > 0) { int i = 0; foreach (DataRow drr in dssitem.Tables[0].Rows) { if (dr["数量"].ToString() != "1") { if (drr[4].ToString() != "100") { dr["分析项目"] += drr[0].ToString() + ","; if (drr[1].ToString() != "") { dr["分析项目代码"] += ";" + drr[1].ToString(); } else { dr["分析项目代码"] += ";" + drr[0].ToString(); } if (drr["relatedItem"].ToString() != "") { string[] relatedItemlist = drr["relatedItem"].ToString().Split(',', ','); for (int l = 0; l < relatedItemlist.Length; l++) { if (relatedItemlist[l].ToString() != "") { DataRow[] drsecond = dssitem.Tables[0].Select("MonitorItem= '" + relatedItemlist[l] + "'"); if (drsecond.Length > 0) { drsecond[0]["xcflag"] = "100"; dr["分析项目"] += drsecond[0][0].ToString() + ","; if (drsecond[0][1].ToString() != "") { dr["分析项目代码"] += "," + drsecond[0][1].ToString(); } else { dr["分析项目代码"] += "," + drsecond[0][0].ToString(); } } } } } } i++; } else { //if (drr[4].ToString() != "100") // { dr["分析项目"] += drr[0].ToString() + ","; if (drr[1].ToString() != "") { dr["分析项目代码"] += "," + drr[1].ToString(); } else { dr["分析项目代码"] += "," + drr[0].ToString(); } } } } dr["标签打印项"] = dr["分析项目代码"].ToString() + delindex; } ds_dayin = ds.Copy(); DataRow[] drsele = ds_dayin.Tables[0].Select("数量<>1"); foreach (DataRow dr in drsele) { //int n = int.Parse(dr["数量"].ToString()); //int num = n; string[] list = dr["标签打印项"].ToString().Split(';'); //if (n > list.Length) // num = n; dr["标签打印项"] = list[0]; for (int i = 1; i < list.Length; i++) { DataRow drnew = ds_dayin.Tables[0].NewRow(); foreach (DataColumn dcname in ds_dayin.Tables[0].Columns) { drnew[dcname.ColumnName] = dr[dcname.ColumnName]; if (dcname.ColumnName == "标签打印项") { if (i < list.Length) { drnew[dcname.ColumnName] = list[i]; } } } ds_dayin.Tables[0].Rows.Add(drnew); } } ds_dayin.AcceptChanges(); grv_dayin.Visible = true; if (ds_dayin.Tables[0].Rows.Count == 0) { //没有记录仍保留表头 ds_dayin.Tables[0].Rows.Add(ds_dayin.Tables[0].NewRow()); grv_dayin.DataSource = ds_dayin; grv_dayin.DataBind(); int intColumnCount = grv_dayin.Rows[0].Cells.Count; grv_dayin.Rows[0].Cells.Clear(); grv_dayin.Rows[0].Cells.Add(new TableCell()); grv_dayin.Rows[0].Cells[0].ColumnSpan = intColumnCount; } else { grv_dayin.Visible = true; DataView dv = new DataView(); dv.Table = ds_dayin.Tables[0]; dv.Sort = "样品编号"; grv_dayin.DataSource = dv; grv_dayin.DataBind(); } ds.Dispose(); }
protected void btn_query_Click(object sender, EventArgs e) { //strSelectedId=txt_samplequery.Text; string strSample = ""; //样品编号 string strDate = ""; //接样时间 string strStatus = ""; //样品状态 string strItem = ""; //项目类型 string strType = ""; //样品名称 string strClient = ""; //委托单位 string strAanlysisStatus = ""; //样品分析状态 string strUrgent = ""; //按紧急程度 if (Drop_Urgent.SelectedValue != "-1") { if (Drop_Urgent.SelectedValue == "1") { strUrgent = "and t_M_ReporInfo.Ulevel=1"; } else { strUrgent = "and (t_M_ReporInfo.Ulevel<>1 or t_M_ReporInfo.Ulevel is null)"; } } if (txt_samplequery.Text != "")//按样品编号 { strSample = "and t_M_SampleInfor.SampleID like'%" + txt_samplequery.Text + "%'"; } if (txt_QueryTime.Text.Trim() != "" && txt_QueryEndTime.Text.Trim() != "")//按采样时间 { DateTime start = DateTime.Parse(txt_QueryTime.Text.Trim() + " 00:00:00"); DateTime end = DateTime.Parse(txt_QueryEndTime.Text.Trim() + " 23:59:59"); strDate = " and t_M_SampleInfor.AccessDate between '" + start + "' and '" + end + "'"; //strDate = " and (year(AccessDate)= '" + DateTime.Parse(txt_QueryTime.Text.Trim() + " 00:00:00").Year + "' AND month(AccessDate)= '" + DateTime.Parse(txt_QueryTime.Text.Trim() + " 00:00:00").Month + "' and day(AccessDate)= '" + DateTime.Parse(txt_QueryTime.Text.Trim() + " 00:00:00").Day + "')"; } if (txt_item.SelectedValue != "-1")// 按项目类型 { strItem = " and t_M_ReporInfo.ItemType='" + txt_item.SelectedValue + "' "; } if (txt_type.SelectedValue != "-1")//按样品类型 { strType = " and t_M_SampleInfor.TypeID='" + txt_type.SelectedValue + "' "; } if (drop_client.SelectedValue != "-1")//按委托单位 { strClient = " and t_M_ReporInfo.ClientID='" + drop_client.SelectedValue + "' "; } ////按选中的分析项目查询 string strtitle = ""; string cstr = ""; if (cstr == "") { cstr = " and 1=1"; } cstr = strUrgent + strSample + strDate + strStatus + strItem + strType + strClient + strAanlysisStatus + strtitle; string strSql = "SELECT t_M_SampleInfor.SampleID AS 样品编号, t_M_ReporInfo.projectname 项目名称,t_M_SampleInfor.SampleDate AS 采样时间,t_M_SampleInfor.SampleProperty 样品性状,t_M_SampleInfor.SampleAddress 采样点 ," + "t_M_AnalysisMainClassEx.ClassName AS 样品类型, " + " t_M_SampleInfor.xcflag,t_M_SampleInfor.num 数量" + " FROM t_M_SampleInfor INNER JOIN" + " t_M_AnalysisMainClassEx ON " + " t_M_SampleInfor.TypeID = t_M_AnalysisMainClassEx.ClassID inner join t_M_ReporInfo on t_M_ReporInfo.id=t_M_SampleInfor.ReportID " + " WHERE 1=1 " + cstr + " ORDER BY t_M_SampleInfor.SampleID"; DataSet ds = new MyDataOp(strSql).CreateDataSet(); DataColumn dccc = new DataColumn("分析项目"); ds.Tables[0].Columns.Add(dccc); DataColumn dcc = new DataColumn("现场分析项目"); ds.Tables[0].Columns.Add(dcc); DataColumn dcc2 = new DataColumn("分析项目代码"); ds.Tables[0].Columns.Add(dcc2); foreach (DataRow dr in ds.Tables[0].Rows) { string getitemstr = "select AIName,AICode,MonitorItem,xcflag from t_MonitorItemDetail inner join t_M_ANItemInf on t_M_ANItemInf.id=t_MonitorItemDetail.MonitorItem where SampleID='" + dr["样品编号"].ToString() + "' and delflag=0"; DataSet dsitem = new MyDataOp(getitemstr).CreateDataSet(); if (dsitem != null && dsitem.Tables.Count > 0) { foreach (DataRow drr in dsitem.Tables[0].Rows) { dr["分析项目"] += drr[0].ToString() + ","; if (drr[3].ToString() == "1") { dr["现场分析项目"] += drr[0].ToString() + ","; } else { if (drr[1].ToString() != "") { dr["分析项目代码"] += drr[1].ToString() + ","; } else { dr["分析项目代码"] += drr[0].ToString() + ","; } } } } } ds_dayin = ds.Copy(); DataColumn dc = new DataColumn("标签打印项"); ds_dayin.Tables[0].Columns.Add(dc); //string specialstr = "select AIName,AICode,MonitorItem from t_SpecialItem"; foreach (DataRow dr in ds_dayin.Tables[0].Rows) { dr["标签打印项"] = dr["分析项目代码"].ToString(); int n = int.Parse(dr["数量"].ToString()); if (n > 1) { for (int i = 1; i < n; i++) { DataRow drnew = ds.Tables[0].NewRow(); foreach (DataColumn dcname in ds.Tables[0].Columns) { drnew[dcname.ColumnName] = dr[dcname.ColumnName]; } ds.Tables[0].Rows.Add(drnew); } } } string specialstr = "select case when AICode is not null then AICode else AIName end 分析项目,relatedItem from t_M_ANItemInf inner join t_SpecialItem on t_SpecialItem.itemid=t_M_ANItemInf.id"; DataSet ds_sitem = new MyDataOp(specialstr).CreateDataSet(); DataRow[] drsele = ds_dayin.Tables[0].Select("数量<>1"); foreach (DataRow dr in drsele) { // string[] list = dr["分析项目代码"].ToString().Split(','); // string[] strlist = new string[list.Length]; // int m = 0; // int j = 0; //foreach(DataRow drspecial in ds_sitem.Tables[0].Rows) //{ // foreach (string str in list) // { // if (str != "") // { // if(str==drspecial[0].ToString()) // { // for (int k=0;k< list.Length;k++) // { // if(str==drspecial[1].ToString()) // { strlist.SetValue(drspecial[0]+","+drspecial[1], m++); // list.SetValue("", k); // } // else // { // strlist.SetValue(drspecial[0], m++); // list.SetValue("", j); // } // } // } // } //} // int n = int.Parse(dr["数量"].ToString()); // if (n > 1) // { // for (int i = 1; i < n; i++) // { // if (i < strlist.Length) // { // if (strlist[i] != "") // { // DataRow drnew = ds_dayin.Tables[0].NewRow(); // foreach (DataColumn dcname in ds_dayin.Tables[0].Columns) // { // if (dcname.ColumnName != "标签打印项") // drnew[dcname.ColumnName] = dr[dcname.ColumnName]; // else // { // if (i < strlist.Length) // { // if (strlist[i] != "") // drnew[dcname.ColumnName] = strlist[i - 1]; // } // else // drnew[dcname.ColumnName] = dr[dcname.ColumnName]; // } // } // DataRow[] checkrow = ds_dayin.Tables[0].Select("标签打印项='" + drnew["标签打印项"] + "'"); // if (checkrow.Length <= 0) // { // ds_dayin.Tables[0].Rows.Add(drnew); // } // } // else // continue; // } // } // } ////} //else //{ int n = int.Parse(dr["数量"].ToString()); if (n > 1) { for (int i = 1; i < n; i++) { DataRow drnew = ds_dayin.Tables[0].NewRow(); foreach (DataColumn dcname in ds_dayin.Tables[0].Columns) { drnew[dcname.ColumnName] = dr[dcname.ColumnName]; } ds_dayin.Tables[0].Rows.Add(drnew); } } } ds_dayin.AcceptChanges(); grv_dayin.Visible = true; if (ds_dayin.Tables[0].Rows.Count == 0) { //没有记录仍保留表头 ds_dayin.Tables[0].Rows.Add(ds_dayin.Tables[0].NewRow()); grv_dayin.DataSource = ds_dayin; grv_dayin.DataBind(); int intColumnCount = grv_dayin.Rows[0].Cells.Count; grv_dayin.Rows[0].Cells.Clear(); grv_dayin.Rows[0].Cells.Add(new TableCell()); grv_dayin.Rows[0].Cells[0].ColumnSpan = intColumnCount; } else { grv_dayin.Visible = true; DataView dv = new DataView(); dv.Table = ds_dayin.Tables[0]; dv.Sort = "样品编号"; grv_dayin.DataSource = dv; grv_dayin.DataBind(); } ds.Dispose(); }