//技令若有最新版本,則更新[技令版期(AMM_TMDWGED)]
--> [技令版期(AMM_TMDWGED)].CTRL= MAX(CTRL)+1 ,
--> 新增 TM_XY@TLS_245 的版本資料(TMNOXX+NATIXX+LGTPXX)
*/
[HttpPost]
public HttpResponseMessage UPDATE_AMM_TMDWGED()
{
//取得參數值
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
string Tmp_TMNO = nvc["TMNO"];
string Tmp_TMNOXX = nvc["TMNOXX"];
string Tmp_LGTPXX = nvc["LGTPXX"];
string Tmp_NATIXX = nvc["NATIXX"];
OracleCommand cmd = new OracleCommand();
var response = this.Request.CreateResponse();
//STEP 1>比對 TM_XY 的筆數是否和 AMM_TMDWGED的筆數相符
bool is_update = false;
string Tmp_RtnMsg;
string Tmp_MAX_CTRL="";
int Tmp_NEXT_CTRL=1;
int Tmp_XY_CNT, Tmp_TMDWGED_CNT;
string Tmp_Sql = " SELECT COUNT(*) "
+ " FROM TM_XY @TLS_245 "
+ " WHERE 1 = 1 "
+ " AND OWN_DEP = 'L' "
+ " AND TMNOXX = " + myfunc.AA(Tmp_TMNOXX)
+ " AND NATIXX = " + myfunc.AA(Tmp_NATIXX)
+ " AND LGTPXX = " + myfunc.AA(Tmp_LGTPXX);
Tmp_XY_CNT = int.Parse(myfunc.SqlValue(Tmp_Sql));
//沒有任何版期資料, 則返回, 不需更新
if (Tmp_XY_CNT == 0)
{
is_update = false;
}
else
{
Tmp_Sql = " SELECT MAX(B.CTRL) "
+ " FROM AMM_TMDWG A,AMM_TMDWGED B "
+ " WHERE A.TMNO = B.TMNO "
+ " AND A.OWN_DEP = 'L' "
+ " AND A.TMNO=" + myfunc.AA(Tmp_TMNO)
+ " AND A.TMNOXX = " + myfunc.AA(Tmp_TMNOXX)
+ " AND A.NATIXX = " + myfunc.AA(Tmp_NATIXX)
+ " AND A.LGTPXX = " + myfunc.AA(Tmp_LGTPXX);
Tmp_MAX_CTRL = myfunc.SqlValue(Tmp_Sql);
Tmp_Sql = " SELECT COUNT(*) "
+ " FROM AMM_TMDWG A,AMM_TMDWGED B "
+ " WHERE A.TMNO = B.TMNO "
+ " AND A.OWN_DEP = 'L' "
+ " AND A.TMNO=" + myfunc.AA(Tmp_TMNO)
+ " AND A.TMNOXX = " + myfunc.AA(Tmp_TMNOXX)
+ " AND A.NATIXX = " + myfunc.AA(Tmp_NATIXX)
+ " AND A.LGTPXX = " + myfunc.AA(Tmp_LGTPXX)
+ " AND B.CTRL =" + myfunc.AA(Tmp_MAX_CTRL);
Tmp_TMDWGED_CNT = int.Parse(myfunc.SqlValue(Tmp_Sql));
if (Tmp_TMDWGED_CNT != Tmp_XY_CNT)
{
is_update = true;
}
//STEP 2>比對 TM_XY.VNTPXD 的是否存在 AMM_TMDWGED.MAX_CTRL.VNTPXD的筆數相符
Tmp_Sql = " SELECT COUNT(*) "
+ " FROM TM_XY @TLS_245 "
+ " WHERE 1 = 1 "
+ " AND OWN_DEP = 'L' "
+ " AND TMNOXX = " + myfunc.AA(Tmp_TMNOXX)
+ " AND NATIXX = " + myfunc.AA(Tmp_NATIXX)
+ " AND LGTPXX = " + myfunc.AA(Tmp_LGTPXX)
+ " AND VNTPXD NOT IN ( SELECT B.VNTPXD "
+ " FROM AMM_TMDWG A, AMM_TMDWGED B "
+ " WHERE A.TMNO= B.TMNO "
+ " AND A.TMNO=" + myfunc.AA(Tmp_TMNO)
+ " AND A.TMNOXX = " + myfunc.AA(Tmp_TMNOXX)
+ " AND A.NATIXX = " + myfunc.AA(Tmp_NATIXX)
+ " AND A.LGTPXX = " + myfunc.AA(Tmp_LGTPXX)
+ " AND B.CTRL=" + myfunc.AA(Tmp_MAX_CTRL) + " )";
Tmp_XY_CNT = int.Parse(myfunc.SqlValue(Tmp_Sql));
if (Tmp_XY_CNT > 0)
{
is_update = true;
}
}// else {} if (Tmp_XY_CNT == 0)
if (is_update)
{
//更新資料 - insert AMM_TMDWGED ,
string Tmp_ITEMXY, Tmp_VNTPXD, Tmp_VNDTXY;
string Tmp_VNPGXY, Tmp_DOREXY, Tmp_REMKXY, Tmp_VNVFXY;
string Tmp_MKER, Tmp_MKDT;
Tmp_MKER = User.Identity.Name;
Tmp_MKDT = DateTime.Now.ToString("yyyy/MM/dd");
Tmp_NEXT_CTRL = int.Parse(Tmp_MAX_CTRL) + 1;
// insert AMM_TMDWGEND
DataTable dt = new DataTable();
Tmp_Sql = " SELECT NATIXX, TMNOXX, LGTPXX, OWN_DEP, ITEMXY, VNTPXD, VNDTXY, "
+ " VNPGXY,DOREXY,REMKXY,VNVFXY "
+ " FROM TM_XY @TLS_245 "
+ " WHERE 1 = 1 "
+ " AND OWN_DEP = 'L' "
+ " AND TMNOXX = " + myfunc.AA(Tmp_TMNOXX)
+ " AND NATIXX = " + myfunc.AA(Tmp_NATIXX)
+ " AND LGTPXX = " + myfunc.AA(Tmp_LGTPXX);
dt = myfunc.SqlOpen(dt, Tmp_Sql);
foreach (DataRow dr in dt.Rows)
{
Tmp_ITEMXY = dr["ITEMXY"].ToString();
Tmp_VNTPXD = dr["VNTPXD"].ToString();
//Tmp_VNDTXY = dr["VNDTXY"].ToString();
Tmp_VNDTXY = myfunc.getDateStr(dr["VNDTXY"].ToString());
Tmp_VNPGXY = dr["VNPGXY"].ToString();
//Tmp_DOREXY = dr["DOREXY"].ToString();
Tmp_DOREXY = myfunc.getDateStr(dr["DOREXY"].ToString());
Tmp_REMKXY = dr["REMKXY"].ToString();
Tmp_VNVFXY = dr["VNVFXY"].ToString();
Tmp_Sql = " INSERT INTO AMM_TMDWGED "
+ "( TMNO,CTRL,"
+ "ITEMXY,VNDTXY,VNTPXD,VNPGXY,DOREXY,"
+ "REMKXY,VNVFXY,MKER,MKDT) "
+ " VALUES "
+ "(" + myfunc.AA(Tmp_TMNO) + "," + myfunc.AA(Tmp_NEXT_CTRL.ToString()) + ","
+ myfunc.AA(Tmp_ITEMXY) + "," + myfunc.AA(Tmp_VNDTXY) + "," + myfunc.AA(Tmp_VNTPXD) + "," + myfunc.AA(Tmp_VNPGXY) + "," + myfunc.AA(Tmp_DOREXY) + ","
+ myfunc.AA(Tmp_REMKXY) + "," + myfunc.AA(Tmp_VNVFXY) + "," + myfunc.AA(Tmp_MKER) + "," + myfunc.AA(Tmp_MKDT) + ")";
myfunc.SqlExec(Tmp_Sql);
} // end of foreach (DataRow dr in dt)
Tmp_RtnMsg = "技令編號(" + Tmp_TMNOXX + ")國別碼(" + Tmp_NATIXX + ")中英文別(" + Tmp_LGTPXX + ")<br>"
+ "版期更新完成 !! <br>";
response.Content = new StringContent("{'success': true,'Rtn_Msg':'" + Tmp_RtnMsg + "'}"); // 回應內容
}
else
{
Tmp_RtnMsg = "技令編號(" + Tmp_TMNOXX + ")國別碼(" + Tmp_NATIXX + ")中英文別(" + Tmp_LGTPXX + ")<br>"
+ "無最新版期 , 需要更新 !! <br>";
response.Content = new StringContent("{'success': true,'Rtn_Msg':'" + Tmp_RtnMsg + "'}"); // 回應內容
}
return response;
//新增 [AMM_TMDWGED] , CTRL=MAX(CTRL)+1
} // end of UPDATE_AMM_TMDWGD()
--> 另一寫法
Tmp_cnt1 = Tmp_dt1.Rows.Count;
for (int j = 0; j < Tmp_cnt1; j++)
{
DataRow dr1 = Tmp_dt1.Rows[j];
string Tmp_VNTPXD= dr1["VNTPXD"].ToString();
