Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
        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("查询完成");
                });
            });
        }