private void cmbModel_SelectedIndexChanged_1(object sender, EventArgs e)
{
IPQC_Motor.TfSQL tf = new IPQC_Motor.TfSQL();
DataTable dtModel = new DataTable();
string sqlCmbModeSub = "select model_sub_cd,model_id from m_model where model_cd = '" + cmbModel.Text + "' order by model_sub_cd";
tf.sqlDataAdapterFillDatatable(sqlCmbModeSub, ref dtModel);
cmbSubModel.DataSource = dtModel;
cmbSubModel.ValueMember = "model_id";
cmbSubModel.DisplayMember = "model_sub_cd";
cmbSubModel.Text = "";
}
void buildDGV(ref DataGridView dgv)
{
//create datagipview
dtInspectItems = new DataTable();
dtInspectItems.Clear();
string sql = @"
select a.item_measure, a.item_detail, a.item_spec_x, a.item_lower, a.item_upper,
(a.item_upper - a.item_spec_x) as tolerance_up , (a.item_lower - a.item_spec_x) as tolerances_low,a.item_tool,
b.data_1, b.data_2, b.data_3, b.data_4, b.data_5, b.data_x, b.data_est, b.registration_date_time from m_item a left join
(select dwr_cd,dwr_id, user_dept_cd from m_drawing a, m_user b where a.user_id = b.user_id) c on a.dwr_id = c.dwr_id
left join m_data b on a.item_id = b.item_id where c.dwr_cd = '" + drawingcd + "' and c.user_dept_cd = (select distinct user_dept_cd from m_user where user_name = '" + username + @"')
order by registration_date_time desc, item_measure asc ";
IPQC_Motor.TfSQL con = new IPQC_Motor.TfSQL();
con.sqlDataAdapterFillDatatable(sql, ref dtInspectItems);
dgv.DataSource = dtInspectItems;
}
private void btnSearch_Click(object sender, EventArgs e)
{
IPQC_Motor.TfSQL tf = new IPQC_Motor.TfSQL();
dt = new DataTable();
string sqlSearch = "select t.model_id, t.model_cd, t.model_sub_cd from m_user m,(select model_id,model_cd,model_sub_cd, user_dept_cd from m_model a,m_user b where a.user_id = b.user_id ) t where m.user_dept_cd = t.user_dept_cd and m.user_name ='" + User + "' ";
if (!String.IsNullOrEmpty(txtModel.Text))
{
sqlSearch += " and t.model_cd = '" + txtModel.Text + "'";
}
if (!String.IsNullOrEmpty(txtModelSub.Text))
{
sqlSearch += " and t.model_sub_cd = '" + txtModelSub.Text + "'";
}
sqlSearch += " order by t.model_cd, t.model_id";
tf.sqlDataAdapterFillDatatable(sqlSearch, ref dt);
updateDGV(ref dgvModel, ref dt);
}
private void btnSearch_Click(object sender, EventArgs e)
{
IPQC_Motor.TfSQL tf = new IPQC_Motor.TfSQL();
dt = new DataTable();
string sqlSearch = @"select b.model_id, b.model_sub_cd, a.dwr_cd, a.dwr_name, a.doc_name, a.registration_date_time,a.dwr_id from m_drawing a left join m_model b on a.model_id = b.model_id where 1=1 ";
if (!String.IsNullOrEmpty(cmbModel.Text))
{
sqlSearch += " and b.model_cd = '" + cmbModel.Text + "'";
}
if (!String.IsNullOrEmpty(cmbSubModel.Text))
{
sqlSearch += " and b.model_sub_cd = '" + cmbSubModel.Text + "'";
}
if (!String.IsNullOrEmpty(txtDwr.Text))
{
sqlSearch += " and a.dwr_cd = '" + txtDwr.Text + "'";
}
sqlSearch += " order by b.model_id";
tf.sqlDataAdapterFillDatatable(sqlSearch, ref dt);
updateDGV(ref dgvDwr, ref dt);
}
private void TreeView2(string DwrCd, string dateFrom, string dateTo, int limit, bool showDrawing)
{
DataTable dtTV = new DataTable();
listTV.Nodes.Clear();
IPQC_Motor.TfSQL tfSql = new IPQC_Motor.TfSQL();
string sqlTreeDr = "select dwr_cd, dwr_name from m_drawing where model_id in (select model_id from m_model where model_sub_cd = '" + cmbSubModel.Text + "') ";
if (showDrawing)
{
sqlTreeDr += " and dwr_cd = '" + DwrCd + "' ";
}
sqlTreeDr += " order by dwr_cd";
tfSql.sqlDataAdapterFillDatatable(sqlTreeDr, ref dtTV);
if (dtTV.Rows.Count > 0)
{
TreeNode[] header = new TreeNode[dtTV.Rows.Count];
for (int k = 0; k < dtTV.Rows.Count; k++)
{
DataTable dtTreeNode = new DataTable();
TreeNode treeheader = new TreeNode
{
Text = dtTV.Rows[k]["dwr_cd"].ToString(),
Tag = dtTV.Rows[k]["dwr_cd"].ToString()
};
header[k] = treeheader;
if (showDrawing)
{
header[k].Expand();
}
StringBuilder sqlTV = new StringBuilder();
sqlTV.Append(@"select cast(a.registration_date_time as date) dates
from m_header a left join m_data b on a.page_id = b.page_id left join m_item c on b.item_id = c.item_id
where c.dwr_id = (select dwr_id from m_drawing where dwr_cd = '" + header[k].Tag + "') and a.registration_date_time >= '" + dateFrom.ToString() +
"' and cast(a.registration_date_time as date) <= '" + dateTo.ToString() + "' group by dates order by dates desc limit " + limit);
tfSql.sqlDataAdapterFillDatatable(sqlTV.ToString(), ref dtTreeNode);
if (dtTreeNode.Rows.Count > 0)
{
TreeNode[] headerN = new TreeNode[dtTreeNode.Rows.Count];
for (int i = 0; i < dtTreeNode.Rows.Count; i++)
{
TreeNode treeheaderN = new TreeNode
{
Text = DateTime.Parse(dtTreeNode.Rows[i]["dates"].ToString()).ToString("yyyy-MM-dd"),
};
headerN[i] = treeheaderN;
header[k].Nodes.Add(treeheaderN);
DataTable dtChildNode = new DataTable();
string sqlNodeChild = @"select * from (select a.page_id, header_machine, footer_result, cast(a.registration_date_time as date) dates,a.registration_date_time date
from m_header a left join m_data b on a.page_id = b.page_id left join m_item c on b.item_id = c.item_id where c.dwr_id = (select dwr_id from m_drawing where dwr_cd = '" + header[k].Tag + "') group by a.page_id,a.registration_date_time, header_machine, footer_result) tb where tb.dates = '" + headerN[i].Text + "'";
tfSql.sqlDataAdapterFillDatatable(sqlNodeChild, ref dtChildNode);
TreeNode[] headerchild = new TreeNode[dtChildNode.Rows.Count];
for (int j = 0; j < dtChildNode.Rows.Count; j++)
{
TreeNode treeheaderchild = new TreeNode
{
Text = "MM: " + dtChildNode.Rows[j]["header_machine"].ToString() + " -- Page Id: " + dtChildNode.Rows[j]["page_id"].ToString() + " " + dtChildNode.Rows[j]["footer_result"].ToString(),
Tag = dtChildNode.Rows[j]["page_id"].ToString()
};
headerchild[j] = treeheaderchild;
if (dtChildNode.Rows[j]["footer_result"].ToString() == "NG")
{
headerchild[j].BackColor = Color.Red;
}
else if (dtChildNode.Rows[j]["footer_result"].ToString() == "OK")
{
}
else
{
headerchild[j].BackColor = Color.Yellow;
}
headerN[i].Nodes.Add(treeheaderchild);
}
}
}
listTV.Nodes.Add(header[k]);
}
}
}