private void buttonExecute_Click(object sender, EventArgs e)
{
try
{
bingSource.DataSource = _db.GetDataTable(this.richTextBoxSqlCmd.Text);
this.dataGridView1.DataSource = bingSource;
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
return;
}
}
public TypeSelector(DbProc db)
{
_proc = db;
this.ShowIcon = false;
this.ShowInTaskbar = false;
this.StartPosition = FormStartPosition.CenterParent;
this.Size = new System.Drawing.Size(600, 400);
panel = new Panel();
panel.Dock = DockStyle.Bottom;
panel.Padding = new System.Windows.Forms.Padding(10);
panel.Height = 60;
this.Controls.Add(panel);
grid = new PropertyGrid();
grid.Dock = DockStyle.Fill;
this.Controls.Add(grid);
btnOk = new Button();
btnOk.Text = "Ok";
btnOk.Dock = DockStyle.Right;
btnOk.Margin = new System.Windows.Forms.Padding(20, 0, 0, 20);
panel.Controls.Add(btnOk);
btnCancel = new Button();
btnCancel.Text = "Cancel";
btnCancel.Dock = DockStyle.Right;
btnCancel.Margin = new System.Windows.Forms.Padding(20, 0, 0, 20);
panel.Controls.Add(btnCancel);
this.AcceptButton = btnOk;
this.CancelButton = btnCancel;
itemType = new ItemType();
grid.SelectedObject = itemType;
db.time_start = Properties.Settings.Default.DefaultDateTimeStart;
db.time_end = Properties.Settings.Default.DefaultDateTimeEnd;
string sql = string.Format("select distinct(test_item_name) from test_item_values"
+ " where test_time between #{0}# and #{1}#", db.time_start, db.time_end);
DataTable dt = db.GetDataTable(sql);
List <string> ls = new List <string>();
foreach (DataRow row in dt.Rows)
{
ls.Add((string)row["test_item_name"]);
}
itemType.Items = ls.ToArray();
grid.ExpandAllGridItems();
}
private void ReadData()
{
DataTable table = new DataTable("results");
List <DataColumn> columns = new List <DataColumn>();
if (this.checkBoxAddTestTime.Checked)
{
table.Columns.Add("TestTime", typeof(DateTime));
columns.Add(table.Columns["TestTime"]);
}
table.Columns.Add("Sn");
columns.Add(table.Columns["Sn"]);
if (this.checkBoxAddTestItemName.Checked)
{
table.Columns.Add("TestItemName");
columns.Add(table.Columns["TestItemName"]);
}
table.PrimaryKey = columns.ToArray();
foreach (ListViewItem item in listView2.Items)
{
table.Columns.Add(item.Text);
}
List <DataTable> dts = new List <DataTable>();
int maxLine = 0;
foreach (ListViewItem item in listView2.Items)
{
string sqlCmd = string.Format("select t1.test_time,t1.product_sn,t1.test_item_name,t1.item_value from TEST_ITEM_VALUES t1, TEST_RESULTS t2 ");
InvokeOnLog("create sql cmd...\n");
string matchString = "";
if (Properties.Settings.Default.AutoWildcard)
{
matchString = "%" + item.Text + "%";
}
else
{
matchString = item.Text;
}
if (Properties.Settings.Default.DefaultRemoveRepeat)
{
InvokeOnLog("remove repeats\n");
sqlCmd += " ,("
+ " select max(tt1.test_time) as test_time2,tt1.product_sn,tt1.test_item_name "
+ " from TEST_ITEM_VALUES tt1,test_results tt2"
+ " where tt1.test_item_name like '" + matchString + "' "
+ " and tt1.test_id = tt2.test_id "
+ " and tt2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ " and tt2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' "
+ " group by tt1.product_sn,tt1.test_item_name "
+ ") t3"
+ " where t2.test_time = t3.test_time2 and t1.test_item_name = t3.test_item_name and ";
}
else
{
sqlCmd += " where ";
}
sqlCmd += " t2.test_time>=#" +
Properties.Settings.Default.DefaultDateTimeStart.ToString("yyyy-MM-dd HH:mm:ss") + "# "
+ "and t2.test_time<=#" +
Properties.Settings.Default.DefaultDateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss") + "# "
+ "and t1.TEST_ITEM_NAME like '" + matchString + "' "
+ "and t1.TEST_ID = t2.TEST_ID "
+ "and t2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ "and t2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' ";
if (Properties.Settings.Default.DefaultRemovePassData)
{
InvokeOnLog("remove pass data\n");
sqlCmd += " and t1.pass_state <> 0";
}
if (Properties.Settings.Default.DefaultRemoveFailData)
{
InvokeOnLog("remove fail data\n");
sqlCmd += " and t1.pass_state = 0";
}
//if (this.toolStripMenuItem_RemoveRepeats.Checked)
//{
// sqlCmd += " group by product_sn)";
//}
if (Properties.Settings.Default.DefaultRemoveSpecialData)
{
sqlCmd += " and t1.item_value>" + Properties.Settings.Default.AbnormalLowData
+ " and t1.item_value<" + Properties.Settings.Default.AbnormalUpData;
}
DataTable dt = _db.GetDataTable(sqlCmd);
dts.Add(dt);
if (maxLine < dt.Rows.Count)
{
maxLine = dt.Rows.Count;
}
}
for (int i = 0; i < maxLine; i++)
{
//table.Rows.Add(table.NewRow());
for (int j = 0; j < dts.Count; j++)
{
if (dts[j].Rows.Count <= i)
{
break;
}
DataRow row;
List <object> keys = new List <object>();
int offset = 1;
if (checkBoxAddTestTime.Checked)
{
keys.Add(dts[j].Rows[i]["test_time"]);
offset++;
}
keys.Add(dts[j].Rows[i]["product_sn"].ToString().Trim());
if (checkBoxAddTestItemName.Checked)
{
keys.Add(dts[j].Rows[i]["test_item_name"].ToString().Trim());
offset++;
}
row = table.Rows.Find(keys.ToArray());
if (row != null)
{
if (this.checkBoxAddTestItemName.Checked)
{
row[j + offset] = dts[j].Rows[i]["item_value"];
}
else
{
row[j + offset] = dts[j].Rows[i]["item_value"];
}
}
else
{
row = table.NewRow();
if (checkBoxAddTestTime.Checked)
{
row["TestTime"] = dts[j].Rows[i]["test_time"];
}
row["Sn"] = row[1] = dts[j].Rows[i]["product_sn"].ToString().Trim();
if (checkBoxAddTestItemName.Checked)
{
row["TestItemName"] = dts[j].Rows[i]["test_item_name"].ToString().Trim();
}
row[j + offset] = dts[j].Rows[i]["item_value"];
table.Rows.Add(row);
}
}
}
this.gridView.DataSource = null;
this.gridView.Rows.Clear();
this.gridView.Columns.Clear();
this.gridView.DataSource = table;
this.gridView.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
this.gridView.ColumnHeadersDefaultCellStyle.Font = new Font("Arial", 12, FontStyle.Bold);
this.gridView.AutoResizeColumnHeadersHeight();
this.gridView.AutoResizeColumns();
}
void dlg_OnAction(object sender, EventArgs e)
{
DbProc proc1 = new DbProc();
DbProc proc2 = new DbProc();
proc2.Conn = new OleDbConnection(ConnectionBuilder.Instance.Conn);
bool isCancel = false;
this.Invoke(new Action(delegate()
{
if (ConnectionBuilder.Instance.ShowDialog(this) == System.Windows.Forms.DialogResult.Cancel)
{
isCancel = true;
}
}));
if (isCancel)
{
return;
}
proc1.Conn = new OleDbConnection(ConnectionBuilder.Instance.Conn);
ConnectionBuilder.Instance.Conn = proc2.Conn.ConnectionString;
MergeOptions options = new MergeOptions();
this.Invoke(new Action(() =>
{
options.ShowDialog(this);
}));
try
{
Stop = false;
string sqlCmd;
DataTable table, table2;
//if (options.Options[MergeOptions.FailCodeTable])
{
mprintf("正在拷贝fail_code_table...");
table = proc1.GetDataTable("select * from FAIL_CODE_TABLE");
//DataColumn[] primKeys = new DataColumn[2];
//primKeys[0] = table2.Columns["product_name"];
//primKeys[1] = table2.Columns["fail_code"];
//table2.PrimaryKey = primKeys;
foreach (DataRow row in table.Rows)
{
if (Stop)
{
break;
}
//object[] keys = new object[2];
//keys[0] = row["product_name"];
//keys[1] = row["fail_code"];
table2 = proc2.GetDataTable("select * from FAIL_CODE_TABLE where product_name=? and fail_code=?",
row["product_name"], row["fail_code"]);
if (table2.Rows.Count == 0)
{
sqlCmd = "insert into FAIL_CODE_TABLE(PRODUCT_NAME,FAIL_CODE,DESCRIPTION,UPLOAD_STATE) "
+ "values(?,?,?,?)";
using (OleDbCommand odc = new OleDbCommand(sqlCmd, proc2.Conn))
{
odc.Parameters.Add(new OleDbParameter("@p1", OleDbType.Char)).Value = row["product_name"];
if (row["fail_code"] is System.DBNull)
{
odc.Parameters.Add(new OleDbParameter("@p2", OleDbType.Integer)).Value = 0;
}
else
{
odc.Parameters.Add(new OleDbParameter("@p2", OleDbType.Integer)).Value = row["fail_code"];
}
odc.Parameters.Add(new OleDbParameter("@p3", OleDbType.VarChar)).Value = row["description"];
odc.Parameters.Add(new OleDbParameter("@p4", OleDbType.TinyInt)).Value = row["upload_state"];
if (odc.ExecuteNonQuery() == 1)
{
mprintf("合并数据 FailCode {0}{1}成功!", row["product_name"], row["fail_code"]);
}
else
{
mprintf("合并数据 FailCode {0}{1}失败!", row["product_name"], row["fail_code"]);
}
}
}
}
}
//mprintf(string.Format("拷贝完成,共计{0}条数据", table2.Rows.Count));
mprintf("正在拷贝test_results...");
table = proc1.GetDataTable("select * from test_results");
foreach (DataRow proc1_row in table.Rows)
{
if (Stop)
{
break;
}
sqlCmd = "select * from test_results where product_sn = ? and test_time = ?";
using (OleDbCommand odc = new OleDbCommand(sqlCmd, proc2.Conn))
{
OleDbParameter pa = new OleDbParameter("@1", proc1_row["PRODUCT_SN"]);
odc.Parameters.Add(pa);
pa = new OleDbParameter("@2", OleDbType.Date);
pa.Value = proc1_row["TEST_TIME"];
odc.Parameters.Add(pa);
var reader = odc.ExecuteReader();
if (reader.Read())
{
reader.Close();
reader.Dispose();
continue;
}
reader.Close();
reader.Dispose();
}
sqlCmd = "insert into test_results(PRODUCT_SN,TEST_TIME,TESTER,STATION,PRODUCT_NAME,FAIL_CODE) "
+ "values(?,?,?,?,?,?)";
using (OleDbCommand odc = new OleDbCommand(sqlCmd, proc2.Conn))
{
odc.Parameters.Add(new OleDbParameter("@p1", proc1_row["PRODUCT_SN"]));
OleDbParameter pa = new OleDbParameter("@P2", OleDbType.Date);
pa.Value = proc1_row["TEST_TIME"];
odc.Parameters.Add(pa);
odc.Parameters.Add(new OleDbParameter("@p3", proc1_row["TESTER"]));
odc.Parameters.Add(new OleDbParameter("@p4", proc1_row["STATION"]));
odc.Parameters.Add(new OleDbParameter("@p5", proc1_row["PRODUCT_NAME"]));
odc.Parameters.Add(new OleDbParameter("@p6", proc1_row["FAIL_CODE"]));
if (odc.ExecuteNonQuery() == 1)
{
mprintf("合并数据 TestResults {0}{1}成功!", proc1_row["PRODUCT_SN"], proc1_row["TEST_TIME"]);
}
else
{
mprintf("合并数据 TestResults {0}{1}失败!", proc1_row["PRODUCT_SN"], proc1_row["TEST_TIME"]);
}
}
var proc1_table2 = proc1.GetDataTable(string.Format("select * from test_item_values where test_id = {0}", proc1_row["TEST_ID"]));
int lastRecordId = 0;
sqlCmd = "select @@identity";
using (OleDbCommand odc = new OleDbCommand(sqlCmd, proc2.Conn))
{
lastRecordId = int.Parse(odc.ExecuteScalar().ToString());
}
#region 拷贝test_item_values
sqlCmd = "insert into test_item_values(test_id,PRODUCT_SN,TEST_TIME,TEST_ITEM_NAME,ITEM_VALUE,LOW_LIMIT,UP_LIMIT) "
+ "values(?,?,?,?,?,?,?)";
if (!proc2.Conn.Provider.Contains("Jet.OLEDB"))
{
foreach (DataRow row in proc1_table2.Rows)
{
using (OleDbCommand odc = new OleDbCommand(sqlCmd, proc2.Conn))
{
odc.Parameters.Add(new OleDbParameter("@p1", OleDbType.BigInt)).Value = lastRecordId;
odc.Parameters.Add(new OleDbParameter("@p2", OleDbType.Char)).Value = row["PRODUCT_SN"];
odc.Parameters.Add(new OleDbParameter("@p3", OleDbType.Date)).Value = row["TEST_TIME"];
odc.Parameters.Add(new OleDbParameter("@p4", OleDbType.Char)).Value = row["TEST_ITEM_NAME"];
if (double.IsInfinity((double)row["ITEM_VALUE"]))
{
odc.Parameters.Add(new OleDbParameter("@p5", OleDbType.Double)).Value = double.MaxValue;
}
else
{
odc.Parameters.Add(new OleDbParameter("@p5", OleDbType.Double)).Value = row["ITEM_VALUE"];
}
odc.Parameters.Add(new OleDbParameter("@p6", OleDbType.Double)).Value = row["LOW_LIMIT"];
odc.Parameters.Add(new OleDbParameter("@p7", OleDbType.Double)).Value = row["UP_LIMIT"];
if (odc.ExecuteNonQuery() == 1)
{
mprintf("合并数据 Test_Item_Values {0}{1}成功!", row["PRODUCT_SN"], row["TEST_TIME"]);
}
else
{
mprintf("合并数据 Test_Item_Values {0}{1}失败!", row["PRODUCT_SN"], row["TEST_TIME"]);
}
}
}
}
else
{
sqlCmd = "select * from test_item_values where test_id=" + lastRecordId;
using (OleDbDataAdapter oda = new OleDbDataAdapter(sqlCmd, proc2.Conn))
{
using (OleDbCommandBuilder odb = new OleDbCommandBuilder(oda))
{
DataSet ds = new DataSet();
oda.Fill(ds, "1");
foreach (DataRow row in proc1_table2.Rows)
{
DataRow nr = ds.Tables[0].NewRow();
nr["test_id"] = lastRecordId;
nr["PRODUCT_SN"] = row["PRODUCT_SN"];
nr["TEST_TIME"] = row["TEST_TIME"];
nr["TEST_ITEM_NAME"] = row["TEST_ITEM_NAME"];
nr["PASS_STATE"] = row["PASS_STATE"];
if (double.IsInfinity((double)row["ITEM_VALUE"]))
{
if (double.IsNegativeInfinity((double)row["ITEM_VALUE"]))
{
nr["ITEM_VALUE"] = double.MinValue;
}
else
{
nr["ITEM_VALUE"] = double.MaxValue;
}
}
else
{
nr["ITEM_VALUE"] = row["ITEM_VALUE"];
}
nr["LOW_LIMIT"] = row["LOW_LIMIT"];
nr["UP_LIMIT"] = row["UP_LIMIT"];
ds.Tables[0].Rows.Add(nr);
}
oda.Update(ds, "1");
ds.Dispose();
}
}
}
#endregion 拷贝test_item_values
if (options.Options[MergeOptions.TestTime])
{
proc1_table2 = proc1.GetDataTable(string.Format("select * from TEST_TIME_DISTRIBUTION where test_id = {0}", proc1_row["TEST_ID"]));
sqlCmd = "insert into TEST_TIME_DISTRIBUTION(test_id,ITEM_NAME,USED_TIME) "
+ "values(?,?,?)";
foreach (DataRow row in proc1_table2.Rows)
{
using (OleDbCommand odc = new OleDbCommand(sqlCmd, proc2.Conn))
{
odc.Parameters.Add(new OleDbParameter("@p1", OleDbType.BigInt)).Value = lastRecordId;
odc.Parameters.Add(new OleDbParameter("@p2", OleDbType.Char)).Value = row["ITEM_NAME"];
odc.Parameters.Add(new OleDbParameter("@p3", OleDbType.Double)).Value = row["USED_TIME"];
if (odc.ExecuteNonQuery() == 1)
{
mprintf("合并数据 TEST_TIME_DISTRIBUTION {0}{1}成功!", row["ITEM_NAME"], row["USED_TIME"]);
}
else
{
mprintf("合并数据 TEST_TIME_DISTRIBUTION {0}{1}失败!", row["ITEM_NAME"], row["USED_TIME"]);
}
}
}
}
}
mprintf("拷贝完成");
}
catch (Exception exp)
{
mprintf("拷贝失败-->{0}", exp.Message);
}
}
private void ReadDataFromDatabase()
{
DataTable dt = new DataTable();
string matchString = "";
if (Properties.Settings.Default.AutoWildcard)
{
matchString = "%" + Properties.Settings.Default.DefaultTestItem + "%";
}
else
{
matchString = Properties.Settings.Default.DefaultTestItem;
}
string sqlCmd = "SELECT avg(t1.item_value) as avgVal,stdev(t1.item_value) as stdVal,"
+ "min(t1.item_value) as minVal,max(t1.item_value) as maxVal,count(t1.item_value) as totalVal,"
+ "min(t1.Low_Limit) as lowLimit,max(t1.up_limit) as upLimit "
+ "from TEST_ITEM_VALUES t1, TEST_RESULTS t2 ";
InvokeOnLog("create sql cmd...\n");
if (Properties.Settings.Default.DefaultRemoveRepeat)
{
InvokeOnLog("remove repeats\n");
sqlCmd += " ,("
+ " select max(tt1.test_time) as test_time2,tt1.product_sn,tt1.test_item_name "
+ " from TEST_ITEM_VALUES tt1,test_results tt2"
+ " where tt1.test_item_name like '" + matchString + "' "
+ " and tt1.test_id = tt2.test_id "
+ " and tt2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ " and tt2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' "
+ " group by tt1.product_sn,tt1.test_item_name "
+ ") t3"
+ " where t2.test_time = t3.test_time2 and t1.test_item_name = t3.test_item_name and ";
}
else
{
sqlCmd += " where ";
}
sqlCmd += " t2.test_time>=#" +
Properties.Settings.Default.DefaultDateTimeStart.ToString("yyyy-MM-dd HH:mm:ss") + "# "
+ "and t2.test_time<=#" +
Properties.Settings.Default.DefaultDateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss") + "# "
+ "and t1.TEST_ITEM_NAME like '" + matchString + "' "
+ "and t1.TEST_ID = t2.TEST_ID "
+ "and t2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ "and t2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' ";
if (Properties.Settings.Default.DefaultRemovePassData)
{
InvokeOnLog("remove pass data\n");
sqlCmd += " and t1.pass_state <> 0";
}
if (Properties.Settings.Default.DefaultRemoveFailData)
{
InvokeOnLog("remove fail data\n");
sqlCmd += " and t1.pass_state = 0";
}
//if (this.toolStripMenuItem_RemoveRepeats.Checked)
//{
// sqlCmd += " group by product_sn)";
//}
if (Properties.Settings.Default.DefaultRemoveSpecialData)
{
sqlCmd += " and t1.item_value>" + Properties.Settings.Default.AbnormalLowData
+ " and t1.item_value<" + Properties.Settings.Default.AbnormalUpData;
}
dt = _db.GetDataTable(sqlCmd);
InvokeOnLog("query sucess\n");
_max = dt.Rows[0]["maxVal"].ToString();
_min = dt.Rows[0]["minVal"].ToString();
_lsl = double.IsInfinity(double.Parse(dt.Rows[0]["lowLimit"].ToString())) ? "-100" : dt.Rows[0]["lowLimit"].ToString();
_usl = double.IsInfinity(double.Parse(dt.Rows[0]["upLimit"].ToString())) ? "100" : dt.Rows[0]["upLimit"].ToString();
_totalCount = dt.Rows[0]["totalVal"].ToString();
_average = dt.Rows[0]["avgVal"].ToString();
_sigma = dt.Rows[0]["stdVal"].ToString();
_target = _average;
{
double a, s;
if (!double.TryParse(_average, out a))
{
return;
}
if (!double.TryParse(_sigma, out s))
{
return;
}
_p3Sigma = (a + s * 3).ToString();
_m3Sigma = (a - s * 3).ToString();
double dlsl = double.Parse(_lsl);
double dusl = double.Parse(_usl);
if (dlsl < a - s * 6)
{
dlsl = a - s * 6;
}
if (dusl > a + s * 6)
{
dusl = a + s * 6;
}
_lsl = dlsl.ToString();
_usl = dusl.ToString();
}
sqlCmd = "SELECT t1.item_value "
+ "from TEST_ITEM_VALUES t1, TEST_RESULTS t2 ";
InvokeOnLog("create sql command...\n");
if (Properties.Settings.Default.DefaultRemoveRepeat)
{
InvokeOnLog("remove repeats\n");
sqlCmd += " ,("
+ " select max(tt1.test_time) as test_time2,tt1.product_sn,tt1.test_item_name "
+ " from TEST_ITEM_VALUES tt1,test_results tt2"
+ " where tt1.test_item_name like '" + matchString + "' "
+ " and tt1.test_id = tt2.test_id "
+ " and tt2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ " and tt2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' "
+ " group by tt1.product_sn,tt1.test_item_name "
+ ") t3"
+ " where t2.test_time = t3.test_time2 and t1.test_item_name = t3.test_item_name and ";
}
else
{
sqlCmd += " where ";
}
sqlCmd += " t2.test_time>=#" +
Properties.Settings.Default.DefaultDateTimeStart.ToString("yyyy-MM-dd HH:mm:ss") + "# "
+ "and t2.test_time<=#" +
Properties.Settings.Default.DefaultDateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss") + "# "
+ "and t1.TEST_ITEM_NAME like '" + matchString + "' "
+ "and t1.PRODUCT_SN = t2.PRODUCT_SN and t1.TEST_TIME=t2.TEST_TIME "
+ "and t2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ "and t2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' ";
if (Properties.Settings.Default.DefaultRemovePassData)
{
InvokeOnLog("remove pass data\n");
sqlCmd += " and t1.pass_state <> 0";
}
if (Properties.Settings.Default.DefaultRemoveFailData)
{
InvokeOnLog("remove fail data\n");
sqlCmd += " and t1.pass_state = 0";
}
//if (this.toolStripMenuItem_RemoveRepeats.Checked)
//{
// sqlCmd += " group by product_sn)";
//}
if (Properties.Settings.Default.DefaultRemoveSpecialData)
{
sqlCmd += " and t1.item_value>" + Properties.Settings.Default.AbnormalLowData
+ " and t1.item_value<" + Properties.Settings.Default.AbnormalUpData;
}
dt.Clear();
dt = _db.GetDataTable(sqlCmd);
this._data = new double[dt.Rows.Count];
for (int i = 0; i < _data.Length; i++)
{
this._data[i] = double.Parse(dt.Rows[i]["item_value"].ToString());
}
}