public int Execute()
{
using (IDbConnection conn = new SqlConnection(Connstirng))
{
var sSQL = SQLSyntaxHelper.ReadSQLFile(ScriptFile);
return(conn.Execute(sSQL));
}
}
public JArray GetPoleIO(string v_polesn = "")
{
v_sql = SQLSyntaxHelper.ReadSQLFile("INOUT_PICKLING.SQL");
v_sql = string.Format(v_sql, v_polesn).Replace("\r\n", " ");
//得到一個DataTable物件
DataTable dt = queryDataTable(v_sql, connStr);
// POLESN, POOLNO, STEP, BDT,EDT,LEAD_EDT,FINISHED,ROLLNO_1,ROLLNO_2,ROLLNO_3,ROLLNO_4
JArray MixArray = new JArray();
var detail = from p in dt.AsEnumerable()
select new
{
POLESN = p.Field <string>("POLESN"),
POLENO = p.Field <string>("POLENO"),
POOLNO = p.Field <string>("POOLNO"),
BDT = p.Field <DateTime?>("BDT"),
LEAD_EDT = p.Field <DateTime?>("LEAD_EDT"),
ROLLNO_1 = p.Field <string>("ROLLNO_1"),
ROLLNO_2 = p.Field <string>("ROLLNO_2"),
ROLLNO_3 = p.Field <string>("ROLLNO_3"),
ROLLNO_4 = p.Field <string>("ROLLNO_4"),
FINISHED = p.Field <string>("FINISHED"),
GAP_MIN = p.Field <Decimal?>("GAP_MIN"),
};
int totalCount = detail.Count();
foreach (var col in detail)
{
var colObject = new JObject
{
{ "POLESN", col.POLESN },
{ "POLENO", col.POLENO },
{ "POOLNO", col.POOLNO },
{ "BDT", col.BDT },
{ "LEAD_EDT", col.LEAD_EDT },
{ "ROLLNO_1", col.ROLLNO_1 },
{ "ROLLNO_2", col.ROLLNO_2 },
{ "ROLLNO_3", col.ROLLNO_3 },
{ "ROLLNO_4", col.ROLLNO_4 },
{ "FINISHED", col.FINISHED },
{ "GAP_MIN", col.GAP_MIN },
};
MixArray.Add(colObject);
}
return(MixArray);
}
public JArray GetPoleData(string v_type, string v_pole)
{
if (v_type.Equals("poleno"))
{
v_sql = SQLSyntaxHelper.ReadSQLFile("POLE_PICKLING_QUERY1.SQL");
}
else if (v_type.Equals("polesn"))
{
v_sql = SQLSyntaxHelper.ReadSQLFile("POLE_PICKLING_QUERY2.SQL");
}
v_sql = string.Format(v_sql, v_pole).Replace("\r\n", " ");
//得到一個DataTable物件
DataTable dt = queryDataTable(v_sql, connStr);
// POLESN, POLENO,ROLLNO_1,ROLLNO_2,ROLLNO_3,ROLLNO_4,ROLLNO_5
JArray MixArray = new JArray();
var detail = from p in dt.AsEnumerable()
select new
{
POLESN = p.Field <string>("POLESN"),
POLENO = p.Field <string>("POLENO"),
ROLLNO_1 = p.Field <string>("ROLLNO_1"),
ROLLNO_2 = p.Field <string>("ROLLNO_2"),
ROLLNO_3 = p.Field <string>("ROLLNO_3"),
ROLLNO_4 = p.Field <string>("ROLLNO_4"),
//ROLLNO_5 = p.Field<string>("ROLLNO_5"),
};
int totalCount = detail.Count();
foreach (var col in detail)
{
var colObject = new JObject
{
{ "POLESN", col.POLESN },
{ "POLENO", col.POLENO },
{ "ROLLNO_1", col.ROLLNO_1 },
{ "ROLLNO_2", col.ROLLNO_2 },
{ "ROLLNO_3", col.ROLLNO_3 },
{ "ROLLNO_4", col.ROLLNO_4 },
//{"ROLLNO_5",col.ROLLNO_5 },
};
MixArray.Add(colObject);
}
return(MixArray);
}
public String CheckProcess(string v_polesn)
{
String v_process = "";
v_sql = SQLSyntaxHelper.ReadSQLFile("CHK_PROCESS.SQL");
v_sql = string.Format(v_sql, v_polesn).Replace("\r\n", " ");
//得到一個DataTable物件
DataTable dt = queryDataTable(v_sql, connStr);
// 找該桿次最後製程是否為W2
if (dt.Rows.Count > 0)
{
v_process = dt.Select()[0]["POOLNO"].ToString();
}
return(v_process);
}
public JArray GetPolesnList(string keyword = "")
{
v_sql = SQLSyntaxHelper.ReadSQLFile("POLE_PICKLING_QUERY3.SQL");
v_sql = string.Format(v_sql, keyword).Replace("\r\n", " ");
//得到一個DataTable物件
DataTable dt = queryDataTable(v_sql, connStr);
//MACHINE_NAME,MO_LIST,TYPE1,SDT,TASK,COLOR
JArray MixArray = new JArray();
var detail = from p in dt.AsEnumerable()
select new
{
POLESN = p.Field <string>("POLESN"),
POLENO = p.Field <string>("POLENO"),
ROLLNO_1 = p.Field <string>("ROLLNO_1"),
ROLLNO_2 = p.Field <string>("ROLLNO_2"),
ROLLNO_3 = p.Field <string>("ROLLNO_3"),
ROLLNO_4 = p.Field <string>("ROLLNO_4"),
FINISHED = p.Field <string>("FINISHED"),
};
int totalCount = detail.Count();
foreach (var col in detail)
{
var colObject = new JObject
{
{ "POLESN", col.POLESN },
{ "POLENO", col.POLENO },
{ "ROLLNO_1", col.ROLLNO_1 },
{ "ROLLNO_2", col.ROLLNO_2 },
{ "ROLLNO_3", col.ROLLNO_3 },
{ "ROLLNO_4", col.ROLLNO_4 },
{ "FINISHED", col.FINISHED },
};
MixArray.Add(colObject);
}
return(MixArray);
}
// [ClassInitialize()]
public static void Initialize(TestContext testContext)
{
// TestDB
using (IDbConnection conn = new SqlConnection(connstirng1))
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
conn.Execute(SQLSyntaxHelper.ReadSQLFile(ccm_createTableFile), null, tran);
tran.Commit();
conn.Close();
} catch (Exception)
{
tran.Rollback();
throw;
}
}
// TestDB2
using (IDbConnection conn = new SqlConnection(connstirng2))
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
// ksc
conn.Execute(SQLSyntaxHelper.ReadSQLFile(ksc_createTableFile), null, tran);
// nbg
conn.Execute(SQLSyntaxHelper.ReadSQLFile(nbg_createTableFile), null, tran);
tran.Commit();
conn.Close();
} catch (Exception)
{
tran.Rollback();
throw;
}
}
}
public int Execute()
{
using (IDbConnection conn = new SqlConnection(Connstirng))
{
var sSQL = SQLSyntaxHelper.ReadSQLFile(InsertToTemp);
var query = conn.Query <string>(sSQL).ToList();
if (query.Count == 0)
{
return(-1);
}
// 2. 繁簡轉換
var sb = new StringBuilder();
query.ForEach(p => sb.Append(p));
sSQL = ConvertToGb(sb.ToString());
if (sSQL.Length != 0)
{
conn.Execute(sSQL);
}
// 3. 新增料號到正式區
sSQL = SQLSyntaxHelper.ReadSQLFile(InsertTempToFormal);
return(conn.Execute(sSQL));
}
}
public String GenPolesn(string poledata)
{
string v_polesn = "", v_poleno = "", v_rollno1 = "", v_rollno2 = "", v_rollno3 = "", v_rollno4 = "", v_rust = "", v_pdate = "";
v_sql = SQLSyntaxHelper.ReadSQLFile("GEN_POLESN.SQL");
v_sql = string.Format(v_sql).Replace("\r\n", " ");
//得到一個DataTable物件
DataTable dt = queryDataTable(v_sql, connStr);
// POLESN, POLENO,ROLLNO_1,ROLLNO_2,ROLLNO_3,ROLLNO_4,ROLLNO_5
var detail = (from p in dt.AsEnumerable()
select new
{
POLESN = p.Field <string>("POLESN"),
}).First();
v_polesn = detail.POLESN;
var queryParam = poledata.ToJObject();
if (!queryParam["poleno"].IsEmpty())
{
v_poleno = queryParam["poleno"].ToString();
}
if (!queryParam["rollno1"].IsEmpty())
{
v_rollno1 = queryParam["rollno1"].ToString();
}
if (!queryParam["rollno2"].IsEmpty())
{
v_rollno2 = queryParam["rollno2"].ToString();
}
if (!queryParam["rollno3"].IsEmpty())
{
v_rollno3 = queryParam["rollno3"].ToString();
}
if (!queryParam["rollno4"].IsEmpty())
{
v_rollno4 = queryParam["rollno4"].ToString();
}
if (!queryParam["rust"].IsEmpty())
{
v_rust = queryParam["rust"].ToString();
}
if (!queryParam["pdate"].IsEmpty())
{
v_pdate = queryParam["pdate"].ToString();
}
JArray InsertArray = new JArray();
var colObject = new JObject
{
{ "POLESN", v_polesn },
{ "POLENO", v_poleno },
{ "ROLLNO_1", v_rollno1 },
{ "ROLLNO_2", v_rollno2 },
{ "ROLLNO_3", v_rollno3 },
{ "ROLLNO_4", v_rollno4 },
{ "RUST", v_rust },
{ "PDATE", v_pdate },
};
InsertArray.Add(colObject);
// 寫入桿號卷號資料
var res = insertPole(InsertArray);
return(v_polesn);
}