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();
}
