private void Tcustomer_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
SqlConnection con=new SqlConnection(ConfigurationSettings.AppSettings["cnn"]);
con.Open();
string sq1l = "select count(Field_Value) from I_defaultvalue where Table_Name='SYSTEM' and Table_Field='SYSTEM_INIT_SETUP' and Field_Value='T' ";
SqlCommand cmd1 = new SqlCommand(sq1l, con); //檢測是否初始化
string sq12 = "select count(WRFAC) from I_CustomOrder where WRFAC in ( select WRFAC from I_defaultvalue where Table_Name='SYSTEM' and Table_Field='CUSTOMER_CODE') ";
SqlCommand cmd2 = new SqlCommand(sq12, con); //檢測是否有資料
if((int)cmd1.ExecuteScalar()<= 0)
{
Response.Write("<script language=javascript>alert('系統沒初始化，點此返回！')</script>");
return;
}
if((int)cmd2.ExecuteScalar()<= 0)
{
Response.Write("<script language=javascript>alert('本廠ID沒有相關資料，點此返回')</script>");
return;
}
//取得唯一產品編號
SqlCommand cmd3 = new SqlCommand("select Field_Value from I_defaultvalue where Table_Name='SYSTEM' and Table_Field='CUT_CUSTOM_ORDER_RECORD'", con);
string Field_Value=cmd3.ExecuteScalar().ToString();
this.Label1.Text=Field_Value.ToString();
SqlDataReader dr;
if (Field_Value.ToString()== "0")
{
SqlCommand cmd4 = new SqlCommand( "select WRPROD,WRTOOL into tbb from I_CustomOrder group by WRPROD,WRTOOL ", con);
dr=cmd4.ExecuteReader();
}
else
{
//取得唯一產品編號
SqlCommand cmd5 = new SqlCommand( "select top '"+Field_Value+"' WRPROD,WRTOOL into tbb from I_CustomOrder group by WRPROD,WRTOOL ", con);
dr=cmd5.ExecuteReader();
}
dr.Close();
//從I-toolplan中取出以上編號的資料到臨時表tbb2
SqlDataReader dr2;
SqlCommand cmd6 = new SqlCommand("select I_ToolPlan.* into tbb2 from I_ToolPlan,tbb where I_ToolPlan.WTPROD=tbb.WRPROD and I_ToolPlan.WTTOOL=tbb.WRTOOL ", con);
dr2=cmd6.ExecuteReader();
dr2.Close();
//取WTTEQU不為空的數據到臨時表tbb3
SqlDataReader dr3;
SqlCommand cmd6a = new SqlCommand("select I_ToolLoc.TLLOC as TLLOCC,tbb2.* into tbb3 from I_ToolLoc,tbb2 where tbb2.WTTEQU=I_ToolLoc.TLTOOL ", con);
dr3=cmd6a.ExecuteReader();
dr3.Close();
//取WTTEQU為空的數據tbb4
SqlDataReader dr4;
SqlCommand cmd6b = new SqlCommand("select * into tbb4 from tbb2 where WTTEQU is null", con);
dr4=cmd6b.ExecuteReader();
dr4.Close();
//取WTMACH的倍數值
SqlCommand cmda1 = new SqlCommand("select Field_Value from I_defaultvalue where Table_Name='SYSTEM' and Table_Field='TOOLPLAN_SIZE_QTY'", con);
string Field_Value1=cmda1.ExecuteScalar().ToString();
this.Label2.Text=Field_Value1.ToString();
//取WTSCYL的倍數值
SqlCommand cmda2 = new SqlCommand("select Field_Value from I_defaultvalue where Table_Name='SYSTEM' and Table_Field='TOOLPLAN_CYCLE_QTY'", con);
string Field_Value2=cmda2.ExecuteScalar().ToString();
this.Label3.Text=Field_Value2.ToString();
//WTTEQU不為空時先到I-toolloc找出相關倉位 再找出不完整的數據
string sql1=" select WTTEQU,WTPLAS,WTPIGM,WTMACH=WTMACH*'"+Field_Value1+"',WTSCYL=WTSCYL*'"+Field_Value2+"',WTPROD,WTTOOL,WTIDES,WTPDES,WTLOC,A+B+C+D+E+F mm into tdd from( "
+" select *,(case when TLLOCC is null or TLLOCC='' then 'TLLOCC為空;' else '' end) A "
+" ,(case when WTTEQU is null or WTTEQU='' then 'WTTEQU為空;' else '' end) B"
+" ,(case when WTPLAS is null or WTPLAS='' then 'WTPLAS為空;' else '' end) C"
+",(case when WTPIGM is null or WTPIGM='' then 'WTPIGM為空;' else '' end) D"
+" ,(case when WTMACH is null or WTMACH='' then 'WTMACH為空;' else '' end) E"
+" ,(case when WTSCYL is null or WTSCYL='' then 'WTSCYL為空;' else '' end) F"
+" from tbb3 )tbb3 ";
SqlDataReader dr5;
SqlCommand cmd6c = new SqlCommand(sql1, con);
dr5=cmd6c.ExecuteReader();
dr5.Close();
SqlDataReader dr6;
string sqlt="select * into tddx from tdd where mm like '%空%' ";
SqlCommand cmd6r = new SqlCommand(sqlt, con);
dr6=cmd6r.ExecuteReader();
dr6.Close();
//WTTEQU為空找出不完整的數據
string sql1a=" select WTTEQU,WTPLAS,WTPIGM,WTMACH=WTMACH*'"+Field_Value1+"',WTSCYL=WTSCYL*'"+Field_Value2+"',WTPROD,WTTOOL,WTIDES,WTPDES,WTLOC,A+B+C+D+E mm into tddk from( "
+" select *,(case when WTTEQU is null or WTTEQU='' then 'WTTEQU為空;' else '' end) A"
+" ,(case when WTPLAS is null or WTPLAS='' then 'WTPLAS為空;' else '' end) B"
+",(case when WTPIGM is null or WTPIGM='' then 'WTPIGM為空;' else '' end) C"
+" ,(case when WTMACH is null or WTMACH='' then 'WTMACH為空;' else '' end) D"
+" ,(case when WTSCYL is null or WTSCYL='' then 'WTSCYL為空;' else '' end) E"
+" from tbb4 )tbb4 ";
SqlDataReader dr5a;
SqlCommand cmd6ca = new SqlCommand(sql1a, con);
dr5a=cmd6ca.ExecuteReader();
dr5a.Close();
SqlDataReader dr6a;
string sqlta="select * into tddxk from tddk where mm like '%空%' ";
SqlCommand cmd6ra = new SqlCommand(sqlta, con);
dr6a=cmd6ra.ExecuteReader();
dr6a.Close();
//兩表合并不完整數據 union all
string sql="select * from tddx union all select * from tddxk ";
SqlDataAdapter ada = new SqlDataAdapter(sql,con);
DataSet ds = new DataSet();
ada.Fill(ds,"temp");
DataGrid1.DataSource=ds.Tables["temp"].DefaultView;
DataGrid1.DataBind();
ada.Dispose();
SqlDataReader asd;
string sqlk="Drop Table tbb"+"\n"+"Drop Table tbb2"+"\n"+"Drop Table tbb3"+"\n"+"Drop Table tbb4"+"\n"+"Drop Table tdd"+"\n"+"Drop Table tddx"+"\n"+"Drop Table tddxk"+"\n"+"Drop Table tddk"+"\n";
SqlCommand fgh = new SqlCommand(sqlk, con);
asd=fgh.ExecuteReader();
asd.Close();
con.Close();
}