private void button4_Click(object sender, EventArgs e)
{
var sql = new Data.Sql.SqlQuery("TestInfo")
.Select("TestInfo.TestID")
.Select("TestInfo.SN")
.Select("ISNULL(SerialNumberInfo.CustomerSN,'未绑定') as CustomerSN1")
.Select("ISNULL(WorkInfo.WorkOrderID,'已删除') as WorkOrderID1")
.Select("ISNULL(WorkInfo.WorkInfoOperator,'已删除') as WorkInfoOperator1")
.Select("ISNULL(ProductInfo.ProductCode,'已删除') as ProductCode1")
.Select("ISNULL(StationInfo.StationName,'已删除') as StationName1")
.Select("ISNULL(SpecInfo.SeqName,'已删除') as SeqName1")
.Select("ISNULL(ResultSetting.ResultText,'错误信息') as ResultText1")
.Select("WorkInfo.ClientName")
.Select("TestInfo.StartTestDateTime")
.Select("TestInfo.EndTestDateTime")
.LeftOuterJoin("WorkInfo", new SqlExp("TestInfo.WorkInfoID = WorkInfo.WorkInfoID"))
.LeftOuterJoin("ProductInfo", new SqlExp("WorkInfo.ProductID = ProductInfo.ProductID"))
.LeftOuterJoin("StationInfo", new SqlExp("WorkInfo.StationID = StationInfo.StationID"))
.LeftOuterJoin("SpecInfo", new SqlExp("WorkInfo.SpecID = SpecInfo.SeqID"))
.LeftOuterJoin("ResultSetting", new SqlExp("ResultSetting.ResultID = TestInfo.TestResult"))
.LeftOuterJoin("SerialNumberInfo", new SqlExp("TestInfo.SN = SerialNumberInfo.SN AND WorkInfo.ProductID = SerialNumberInfo.ProductID"))
.OrderBy("TestInfo.StartTestDateTime", false);
if (!string.IsNullOrEmpty(textBox1.Text))
{
sql.Where(Exp.Like("TestInfo.SN", string.Format("%{0}%", textBox1.Text)));
}
var sid = cbStationID.SelectedValue == null ? Guid.Empty : cbStationID.SelectedValue.ToString().ExtendToGuid();
var pid = cbProductID.SelectedValue == null ? Guid.Empty : cbProductID.SelectedValue.ToString().ExtendToGuid();
if (sid != Guid.Empty)
{
sql.Where("WorkInfo.StationID", sid);
}
if (pid != Guid.Empty)
{
sql.Where("WorkInfo.ProductID", pid);
}
if (!string.IsNullOrEmpty(tbOperator.Text))
{
sql.Where("WorkInfo.WorkInfoOperator", tbOperator.Text);
}
if (!string.IsNullOrEmpty(tbWorkOrderID.Text))
{
sql.Where("WorkInfo.WorkOrderID", tbWorkOrderID.Text);
}
if (checkBox1.Checked)
{
sql.Where(Exp.Ge("TestInfo.StartTestDateTime", dateTimePicker1.Value.Date));
}
if (checkBox2.Checked)
{
sql.Where(Exp.Le("TestInfo.StartTestDateTime", dateTimePicker2.Value.AddDays(1).Date));
}
if (checkBox3.Checked)
{
sql.Where(Exp.Gt("TestInfo.TestResult", 0));
}
pageViwer1.SetColumnName("", "序列号", "钢管号", "工单", "操作员", "产品", "规格", "序列", "结果", "客户端", "开始时间", "结束时间");
pageViwer1.BindCommand(
Framework.MeasurementSystemSetting.GetDbManager(),
sql,
20,
1);
}
private void button4_Click(object sender, EventArgs e)
{
var sql = new Data.Sql.SqlQuery("TestInfo")
.Select("WorkInfo.WorkInfoOperator")
.LeftOuterJoin("WorkInfo", new SqlExp("TestInfo.WorkInfoID = WorkInfo.WorkInfoID"))
.LeftOuterJoin("ProductInfo", new SqlExp("WorkInfo.ProductID = ProductInfo.ProductID"))
.LeftOuterJoin("StationInfo", new SqlExp("WorkInfo.StationID = StationInfo.StationID"))
.GroupBy("WorkInfo.WorkInfoOperator ");
var sid = cbStationID.SelectedValue == null ? Guid.Empty : cbStationID.SelectedValue.ToString().ExtendToGuid();
var pid = cbProductID.SelectedValue == null ? Guid.Empty : cbProductID.SelectedValue.ToString().ExtendToGuid();
if (sid != Guid.Empty)
{
sql.Where("WorkInfo.StationID", sid);
}
if (pid != Guid.Empty)
{
sql.Where("WorkInfo.ProductID", pid);
}
if (!string.IsNullOrEmpty(tbOperator.Text))
{
sql.Where("WorkInfo.WorkInfoOperator", tbOperator.Text);
}
if (!string.IsNullOrEmpty(tbWorkOrderID.Text))
{
sql.Where("WorkInfo.WorkOrderID", tbWorkOrderID.Text);
}
if (checkBox1.Checked)
{
sql.Where(Exp.Ge("TestInfo.StartTestDateTime", dateTimePicker1.Value.Date));
}
if (checkBox2.Checked)
{
sql.Where(Exp.Le("TestInfo.StartTestDateTime", dateTimePicker2.Value.AddDays(1).Date));
}
var columnNames = new string[] { "作业员", "", "", "", "所有", "通过", "失败" };
var group = 0;
if (cbGroupType.SelectedValue != null)
{
group = cbGroupType.SelectedValue.CastTo(0);
switch (group)
{
case 1: //产品
sql.Select("ISNULL(ProductInfo.ProductCode,'已删除') as ProductCode1")
.Select("'' as StationName1")
.Select("'' as WorkOrderID1")
.GroupBy("ProductInfo.ProductCode");
columnNames[1] = "产品";
break;
case 2: //工位
sql.Select("ISNULL(ProductInfo.ProductCode,'已删除') as ProductCode1")
.Select("ISNULL(StationInfo.StationName,'已删除') as StationName1")
.Select("'' as WorkOrderID1")
.GroupBy("ProductInfo.ProductCode")
.GroupBy("StationInfo.StationName");
columnNames[1] = "产品";
columnNames[2] = "工站";
break;
case 3: //工单
sql.Select("ISNULL(ProductInfo.ProductCode,'已删除') as ProductCode1")
.Select("ISNULL(StationInfo.StationName,'已删除') as StationName1")
.Select("ISNULL(WorkInfo.WorkOrderID,'已删除') as WorkOrderID1")
.GroupBy("ProductInfo.ProductCode")
.GroupBy("StationInfo.StationName")
.GroupBy("WorkInfo.WorkOrderID");
columnNames[1] = "产品";
columnNames[2] = "工站";
columnNames[3] = "工单";
break;
default:
sql.Select("'' as ProductCode1")
.Select("'' as StationName1")
.Select("'' as WorkOrderID1");
break;
}
}
sql.Select("SUM(1) AS allQty")
.Select("SUM(case when TestInfo.TestResult > 0 then 1 else 0 end) AS passQty")
.Select("SUM(case when TestInfo.TestResult < 1 then 1 else 0 end) AS failQty");
pageViwer1.SetColumnName(columnNames);
pageViwer1.BindCommand(
Framework.MeasurementSystemSetting.GetDbManager(),
sql,
int.MaxValue,
1);
}
private void button4_Click(object sender, EventArgs e)
{
var sql = new Data.Sql.SqlQuery("TestInfo")
.Select("TestInfo.TestID")
.Select("TestInfo.SN")
.Select("ISNULL(WorkInfo.WorkOrderID,'已删除') as WorkOrderID1")
.Select("ISNULL(WorkInfo.WorkInfoOperator,'已删除') as WorkInfoOperator1")
.Select("ISNULL(ProductInfo.ProductCode,'已删除') as ProductCode1")
.Select("ISNULL(StationInfo.StationName,'已删除') as StationName1")
.Select("ISNULL(SpecInfo.SeqName,'已删除') as SeqName1")
.Select("ISNULL(ResultSetting.ResultText,'错误信息') as ResultText1")
.Select("WorkInfo.ClientName")
.Select("TestInfo.StartTestDateTime")
.Select("TestInfo.EndTestDateTime")
.LeftOuterJoin("WorkInfo", new SqlExp("TestInfo.WorkInfoID = WorkInfo.WorkInfoID"))
.LeftOuterJoin("ProductInfo", new SqlExp("WorkInfo.ProductID = ProductInfo.ProductID"))
.LeftOuterJoin("StationInfo", new SqlExp("WorkInfo.StationID = StationInfo.StationID"))
.LeftOuterJoin("SpecInfo", new SqlExp("WorkInfo.SpecID = SpecInfo.SeqID"))
.LeftOuterJoin("ResultSetting", new SqlExp("ResultSetting.ResultID = TestInfo.TestResult"))
.OrderBy("TestInfo.StartTestDateTime", false);
if (!string.IsNullOrEmpty(textBox1.Text))
{
sql.Where(Exp.Like("TestInfo.SN", string.Format("%{0}%", textBox1.Text)));
}
var sid = cbStationID.SelectedValue == null ? Guid.Empty : cbStationID.SelectedValue.ToString().ExtendToGuid();
var pid = cbProductID.SelectedValue == null ? Guid.Empty : cbProductID.SelectedValue.ToString().ExtendToGuid();
if (sid != Guid.Empty)
{
sql.Where("WorkInfo.StationID", sid);
}
if (pid != Guid.Empty)
{
sql.Where("WorkInfo.ProductID", pid);
}
if (!string.IsNullOrEmpty(tbOperator.Text))
{
sql.Where("WorkInfo.WorkInfoOperator", tbOperator.Text);
}
if (!string.IsNullOrEmpty(tbWorkOrderID.Text))
{
sql.Where("WorkInfo.WorkOrderID", tbWorkOrderID.Text);
}
if (checkBox1.Checked)
{
sql.Where(Exp.Ge("TestInfo.StartTestDateTime", dateTimePicker1.Value.Date));
}
if (checkBox2.Checked)
{
sql.Where(Exp.Le("TestInfo.StartTestDateTime", dateTimePicker2.Value.AddDays(1).Date));
}
if (checkBox3.Checked)
{
sql.Where(Exp.Gt("TestInfo.TestResult", 0));
}
var dataTable = new DataTable();
button4.Enabled = false;
button4.Text = "正在查询...";
System.Threading.ThreadPool.QueueUserWorkItem((o) =>
{
var db = Framework.MeasurementSystemSetting.GetDbManager();
var scount = db.ExecuteScalar <int>(sql.CountQuery());
if (scount > 5000)
{
AppFramework.Context.Execute(() =>
{
Display(dataTable);
AppFramework.Context.ShowError("查询数据大于5000行,请精准查询条件，减少记录");
});
return;
}
dataTable = db.ExecuteDataTable(sql);
if (dataTable == null)
{
AppFramework.Context.Execute(() =>
{
Display(dataTable);
AppFramework.Context.ShowError("查询失败");
});
return;
}
var repository = DbFactory.Repository <TestInfoRepository>(false);
foreach (DataRow row in dataTable.Rows)
{
var testID = row["TestID"].CastTo <Guid>();
var info = repository.GetData(testID);
if (info == null)
{
continue;
}
foreach (var item in info.TestItems)
{
var name = $"{item.ItemName} ({item.TestTemp})";
if (!dataTable.Columns.Contains(name))
{
dataTable.Columns.Add(name, typeof(string));
}
row[name] = item.ItemValue;
}
}
AppFramework.Context.Execute(() =>
{
Display(dataTable);
AppFramework.Context.ShowAlert("查询完成");
});
});
}