WebDesign 心得綜整

2025年4月9日星期三

V20305J –挑選xls檔案, 匯入2個Table(AMM_UOC,AMM_UOCD) - 清除cookie - Transaction - rollback - excuteSQLTran - loadmark -timer

目的:V20305J –[非TLS品項批次提料]鈕

–挑選xls檔案, 匯入2個Table(AMM_UOC,AMM_UOCD)
- Transaction - rollback - loadmark -timer

處理緣由: 1>因為採用 Transaction 方式,採用SQL_List方式批次寫入Table

2> 有可能兩筆資料在AMM_UOC PK重覆, 第一筆Insert, 但第二筆必需Update

-2.1>>準備SQL時,第一筆還沒寫入,所以判斷用Insert

-2.2>>實際執行Transaction時,第一筆寫入,

第二筆寫入時,PK已存在(第一筆剛寫入),又執行Insert SQL

---> AMM_UOC 會 Key Violation

-2.3 更新 AMM_UOC 改用 MERGE 方式,

--> 不用自行處理 - Tmp_AMM_UOC_LIST.IndexOf(Tmp_AMM_UOC_PK) ==-1 方式

3> 如上2>在AMM_UOCD.ITMUOCD=MAX(ITMUOCD)+1 ,

-3.1>>準備SQL時,第一筆還沒寫入,所以第一筆&第二筆.MAX(ITMUOCD)均=0

-3.2>>實際執行Transaction時,第一筆寫入,

第二筆.MAX(ITMUOCD)應該=1,而不是0

---> AMM_UOCD 會 Key Violation

4>Tmp_cnt=excuteSQLTran(SQL_List_A);

//批次執行 SQL 有錯誤

if (Tmp_cnt == -1)

{

Tmp_RtnMsg = getExcMsg();

raiseEXP(new Exception() , Tmp_RtnMsg);

}

處理說明: 2>兩筆相同資料寫入, AMM_UOC

將PK存入 PK_LIST

若PK未存在 PK_LIST ,則用 Insert SQL

若PK存在 PK_LIST (重覆) ,則改用 Update SQL

3>兩筆相同資料寫入, AMM_UOCD(ITMUOCD)

利用 INSERT INTO SELECT 語法, 即時取得 MAX(ITMUOCD)+1

Ex:

INSERT INTO AMM_UOCD

(RPNOAF, EIAC, EI_LCN, EI_ALC, NHA_LCN, NHA_ALC, LCN, ALC, PN,

ITMUOCD,

TMNOXX, AMMNO)

SELECT '20140214U320-A07','UH-1H','A','01','A0Y0U2H07','00','A0Y0U2H070C','00','CEC5091-0998-55',

(SELECT nvl(MAX(TO_NUMBER(ITMUOCD)),0) + 1

FROM AMM_UOCD

WHERE RPNOAF = '20140214U320-A07'

AND EIAC = 'UH-1H'

AND EI_LCN = 'A'

AND EI_ALC = '01'

AND NHA_LCN = 'A0Y0U2H07'

AND NHA_ALC = '00'

AND LCN = 'A0Y0U2H070C'

AND ALC = '00'

AND PN = 'CEC5091-0998-55'),

'TMNOXX1',

'AMMNO1'

FROM DUAL;

1>*.js

function V20305J_OK(me) {

console.log(" 1 inside Ok_process");

//取得 [檔案上傳].檔名

var Tmp_FName = Ext.getCmp('sub_File').getValue();

var np = {};

np["sub_FName"] = Tmp_FName;

var Tmp_Str = "";

//先清除 cookies 欄位值

Ext.util.Cookies.set("Rtn_Msg", "");

me.up("form").submit({

//standardSubmit: false, //default:false

url: '../api/V20305JAPI/INS_AMM_UOC_UOCD',

method: 'POST',

headers: { 'Content-type': 'multipart/form-data' },

params: np,

}); // end of me.up("form").submit({

console.log(" close window ");

var mask = new Ext.LoadMask(Ext.getBody(), {

msg: '資料處理中, 請稍待...'

});

mask.show();//使用 mask 需手動呼叫show() 方法下

//隔兩秒才顯示完成訊息

var timer = setInterval(function () {

var cookie_token = Ext.util.Cookies.get("Rtn_Msg");

if (!checkisnull(cookie_token)) {

clearInterval(timer);

mask.hide();

timer = null;

var rtn_msg = r_cookies("Rtn_Msg");

mysuccessalert(rtn_msg);

me.up("window").close();

me.up("window").destroy();

}

}, 2000); //等待 2000ms =1sec

}; // end of Ok_process() { //批次新增

2>*.cs

[HttpPost]

public void INS_AMM_UOC_UOCD()

{

HttpContext c = HttpContext.Current;

NameValueCollection nvc = c.Request.Form;

//OracleConnection conn = new OracleConnection(DBService.ConnectionString(("AMMEU"));//

OracleConnection conn = new OracleConnection(DBService.ConnectionString((DBLINK)));//

OracleCommand cmd = new OracleCommand();

DataSet ds = null;

//取得匯入檔案名稱 of filefield

string Tmp_FName = nvc["sub_FName"];

string Tmp_in_FName="", Tmp_out_FName="";

Workbook wk = null;

MemoryStream stream = new MemoryStream();

Worksheet ws=null;

HttpRequest Request = HttpContext.Current.Request;

var response = this.Request.CreateResponse();

//string file_Name = Tmp_in_FName;

string FILEDT = DateTime.Now.ToString("yyyy/MM/dd");

string Tmp_RtnMsg = "";

string Tmp_RtnMsg1 = "";

string Tmp_Sql = "", Tmp_Str = "";

int Tmp_cnt = 0;

int Tmp_rowcnt = 0;

HttpCookie MyCookie = new HttpCookie("Rtn_Msg");

conn.Open();

cmd.Connection = conn;

try

{

foreach (string cur_FName in Request.Files)

{

string fileType = Request.Files[cur_FName].ContentType;

Stream Tmp_in_Stream = Request.Files[cur_FName].InputStream;

Tmp_in_FName = Path.GetFileName(Request.Files[cur_FName].FileName);

Tmp_out_FName = "Log_" + Tmp_in_FName;

int fileSize = Request.Files[cur_FName].ContentLength;

byte[] fileRcrd = new byte[fileSize];

//更新資料庫欄位值

//匯入的 xls

wk = new Workbook();

wk.LoadFromStream(Tmp_in_Stream);

ws = wk.Worksheets[0];

//共 43 欄

//string[] outFieldArray = { 提領編號、交修單號、維修交修單號、工單號碼、序號、

展BOMM件號、展BOM件號名稱、圖號、索引、上層件號、

SBOM件號、SBOM件號中文名稱、規範(需求)件號、提料件號、每具數量、

需求數量、需求單位、更換情形、故障現象說明、可修件狀態(Y/N)、

可修件更換原因說明、超量原因、子件工單號碼、子件序號、特製件說明、

經常更換、配換率、QPEI、拆挪LRU交修單號、拆挪子件序號、

LCN、ALC、NHA_LCN、NHA_ALC、EIAC、

EI_LCN、EI_ALC、SBOM廠碼、規範廠碼、SBOM單位、

技令號碼、WP、SBOM件號英文名稱};

string Tmp_OGONOAF, Tmp_RPNOAF, Tmp_MRPNOAF, Tmp_SAPNO, Tmp_SN;//序號: Tmp_SN

string Tmp_SRO_PN, Tmp_SRO_C_NM, Tmp_FIG, Tmp_IND, Tmp_NHA_PN;

string Tmp_SBOM_PN, Tmp_SBOM_C_NM, Tmp_RQPN, Tmp_FACPN, Tmp_QPA;

string Tmp_NQTYAH, Tmp_C_UNIT, Tmp_RSTHAH, Tmp_FRMK, Tmp_FASTAT;

string Tmp_FACHRN, Tmp_OVERAHD, Tmp_FRNOAF_S, Tmp_SUB_SN, Tmp_NOTE; //子件序號: Tmp_SUB_SN

string Tmp_RATE_MORE, Tmp_RATE, Tmp_QPEI, Tmp_RMRPNOAF, Tmp_RMSBSN;

string Tmp_LCN, Tmp_ALC, Tmp_NHA_LCN, Tmp_NHA_ALC, Tmp_EIAC;

string Tmp_EI_LCN, Tmp_EI_ALC, Tmp_SBOM_CAGE, Tmp_RQPN_CAGE, Tmp_SBOM_UNIT;

string Tmp_TMNOXX, Tmp_WP, Tmp_SBOM_PN_E_NM,Tmp_FACPN_UNIT;

string Tmp_PN,Tmp_CAGE,Tmp_NSN,Tmp_AMMNO;

string Tmp_ITMUOCD, Tmp_NEXT_ITMUOCD;

string Tmp_AMM_UOC_PK, Tmp_AMM_UOCD_PK;

//請料者,請料日期,狀態,可修狀態

string Tmp_EMPLYID = User.Identity.Name;

string Tmp_RDUOCD = DateTime.Now.ToString("yyyy/MM/dd"); ;

string Tmp_STAUOCD = "A";

//List<String> Tmp_AMM_UOC_LIST 儲存 AMM_UOC.PK

List<String> Tmp_AMM_UOC_LIST = new List<string>();

//STEP1 檢核 XLS 欄位名稱資料是否正確 -取得標題 of 工號,處置說明

int cur_row=1;

Tmp_OGONOAF = ws.Range[myfunc.GetExcelPos(0, cur_row)].Value.Trim();

Tmp_RPNOAF = ws.Range[myfunc.GetExcelPos(1, cur_row)].Value.Trim();

Tmp_MRPNOAF = ws.Range[myfunc.GetExcelPos(2, cur_row)].Value.Trim();

Tmp_SAPNO = ws.Range[myfunc.GetExcelPos(3, cur_row)].Value.Trim();

Tmp_SN = ws.Range[myfunc.GetExcelPos(4, cur_row)].Value.Trim(); //序號

Tmp_SRO_PN= ws.Range[myfunc.GetExcelPos(5, cur_row)].Value.Trim();

Tmp_SRO_C_NM= ws.Range[myfunc.GetExcelPos(6, cur_row)].Value.Trim();

Tmp_FIG= ws.Range[myfunc.GetExcelPos(7, cur_row)].Value.Trim();

Tmp_IND= ws.Range[myfunc.GetExcelPos(8, cur_row)].Value.Trim();

Tmp_NHA_PN= ws.Range[myfunc.GetExcelPos(9, cur_row)].Value.Trim();

Tmp_SBOM_PN=ws.Range[myfunc.GetExcelPos(10, cur_row)].Value.Trim();

Tmp_SBOM_C_NM=ws.Range[myfunc.GetExcelPos(11, cur_row)].Value.Trim();

Tmp_RQPN=ws.Range[myfunc.GetExcelPos(12, cur_row)].Value.Trim();

Tmp_FACPN=ws.Range[myfunc.GetExcelPos(13, cur_row)].Value.Trim();

Tmp_QPA=ws.Range[myfunc.GetExcelPos(14, cur_row)].Value.Trim(); // (QPA,每具數量)

Tmp_NQTYAH=ws.Range[myfunc.GetExcelPos(15, cur_row)].Value.Trim();

Tmp_C_UNIT=ws.Range[myfunc.GetExcelPos(16, cur_row)].Value.Trim();

Tmp_RSTHAH=ws.Range[myfunc.GetExcelPos(17, cur_row)].Value.Trim();

Tmp_FRMK=ws.Range[myfunc.GetExcelPos(18, cur_row)].Value.Trim();

Tmp_FASTAT=ws.Range[myfunc.GetExcelPos(19, cur_row)].Value.Trim(); //可修件(y/n)

Tmp_FACHRN=ws.Range[myfunc.GetExcelPos(20, cur_row)].Value.Trim();

Tmp_OVERAHD=ws.Range[myfunc.GetExcelPos(21, cur_row)].Value.Trim();

Tmp_FRNOAF_S=ws.Range[myfunc.GetExcelPos(22, cur_row)].Value.Trim(); //子件工單號碼

Tmp_SUB_SN=ws.Range[myfunc.GetExcelPos(23, cur_row)].Value.Trim(); //子件序號

Tmp_NOTE=ws.Range[myfunc.GetExcelPos(24, cur_row)].Value.Trim(); //特製件說明

Tmp_RATE_MORE =ws.Range[myfunc.GetExcelPos(25, cur_row)].Value.Trim();

Tmp_RATE=ws.Range[myfunc.GetExcelPos(26, cur_row)].Value.Trim();

Tmp_QPEI=ws.Range[myfunc.GetExcelPos(27, cur_row)].Value.Trim();

Tmp_RMRPNOAF=ws.Range[myfunc.GetExcelPos(28, cur_row)].Value.Trim();

Tmp_RMSBSN=ws.Range[myfunc.GetExcelPos(29, cur_row)].Value.Trim(); //拆挪子件序號

Tmp_LCN= ws.Range[myfunc.GetExcelPos(30, cur_row)].Value.Trim();

Tmp_ALC =ws.Range[myfunc.GetExcelPos(31, cur_row)].Value.Trim();

Tmp_NHA_LCN=ws.Range[myfunc.GetExcelPos(32, cur_row)].Value.Trim();

Tmp_NHA_ALC=ws.Range[myfunc.GetExcelPos(33, cur_row)].Value.Trim();

Tmp_EIAC=ws.Range[myfunc.GetExcelPos(34, cur_row)].Value.Trim(); //EIAC

Tmp_EI_LCN=ws.Range[myfunc.GetExcelPos(35, cur_row)].Value.Trim();

Tmp_EI_ALC=ws.Range[myfunc.GetExcelPos(36, cur_row)].Value.Trim();

Tmp_SBOM_CAGE=ws.Range[myfunc.GetExcelPos(37, cur_row)].Value.Trim();

Tmp_RQPN_CAGE=ws.Range[myfunc.GetExcelPos(38, cur_row)].Value.Trim();

Tmp_SBOM_UNIT=ws.Range[myfunc.GetExcelPos(39, cur_row)].Value.Trim(); //SBOM單位

Tmp_TMNOXX=ws.Range[myfunc.GetExcelPos(40, cur_row)].Value.Trim();

Tmp_WP=ws.Range[myfunc.GetExcelPos(41, cur_row)].Value.Trim();

Tmp_SBOM_PN_E_NM=ws.Range[myfunc.GetExcelPos(42, cur_row)].Value.Trim();

Tmp_FACPN_UNIT = ws.Range[myfunc.GetExcelPos(43, cur_row)].Value.Trim(); //撥發單位

//xls 欄位名稱有問題

if ( (Tmp_OGONOAF != "提領編號") || (Tmp_RPNOAF != "交修單號") || ( Tmp_MRPNOAF!= "維修交修單號") || ( Tmp_SAPNO!= "工單號碼") || ( Tmp_SN!= "序號") ||

(Tmp_SRO_PN!= "展BOM件號") || ( Tmp_SRO_C_NM!= "展BOM件號名稱") || ( Tmp_FIG!= "圖號") || ( Tmp_IND!= "索引") || ( Tmp_NHA_PN!= "上層件號") ||

(Tmp_SBOM_PN!= "SBOM件號") || ( Tmp_SBOM_C_NM!= "SBOM件號中文名稱") || ( Tmp_RQPN!= "規範件號") || ( Tmp_FACPN!= "提料件號") || ( Tmp_QPA!= "每具數量") ||

(Tmp_NQTYAH!= "需求數量") || ( Tmp_C_UNIT!= "需求單位") || ( Tmp_RSTHAH!= "更換情形") || ( Tmp_FRMK!= "故障現象說明") || ( Tmp_FASTAT!= "可修件(Y/N)") ||

(Tmp_FACHRN != "可修件更換原因說明") || ( Tmp_OVERAHD!= "超量原因") || ( Tmp_FRNOAF_S!= "子件工單號碼") || ( Tmp_SUB_SN!= "子件序號") || ( Tmp_NOTE!= "特製件說明") ||

(Tmp_RATE_MORE != "經常更換") || ( Tmp_RATE!= "配換率") || ( Tmp_QPEI!= "QPEI") || ( Tmp_RMRPNOAF!= "拆挪LRU交修單號") || ( Tmp_RMSBSN!= "拆挪子件序號") ||

(Tmp_LCN != "LCN") || ( Tmp_ALC!= "ALC") || ( Tmp_NHA_LCN!= "NHA_LCN") || ( Tmp_NHA_ALC!= "NHA_ALC") || ( Tmp_EIAC!= "EIAC") ||

(Tmp_EI_LCN != "EI_LCN") || ( Tmp_EI_ALC!= "EI_ALC") || ( Tmp_SBOM_CAGE!= "SBOM廠碼") || ( Tmp_RQPN_CAGE!= "規範廠碼") || ( Tmp_SBOM_UNIT!= "SBOM單位") ||

(Tmp_TMNOXX != "技令號碼") || ( Tmp_WP!= "WP") || ( Tmp_SBOM_PN_E_NM!= "SBOM件號英文名稱") || ( Tmp_FACPN_UNIT!= "撥發單位") )

{

Tmp_RtnMsg = "匯入檔案欄位名稱必需如下: ";

//(Tmp_DTSOR != "資料來源") if (Tmp_OGONOAF != "拆檢提領編號") if (Tmp_NGONOAF != "維修提領編號") if (Tmp_RPNOAF != "拆檢交修單號") if (Tmp_MRPNOAF != "維修約交修單號") if

if (Tmp_OGONOAF != "提領編號")

Tmp_RtnMsg = Tmp_RtnMsg + "第1欄欄位名稱: [提領編號];";

if (Tmp_RPNOAF != "交修單號")

Tmp_RtnMsg = Tmp_RtnMsg + "第2欄欄位名稱: [交修單號];";

if (Tmp_MRPNOAF != "維修交修單號")

Tmp_RtnMsg = Tmp_RtnMsg + "第3欄欄位名稱: [維修交修單號];";

if (Tmp_SAPNO != "工單號碼")

Tmp_RtnMsg = Tmp_RtnMsg + "第4欄欄位名稱: [工單號碼];";

if (Tmp_SN != "序號")

Tmp_RtnMsg = Tmp_RtnMsg + "第5欄欄位名稱: [序號];";

if (Tmp_SRO_PN != "展BOM件號")

Tmp_RtnMsg = Tmp_RtnMsg + "第6欄欄位名稱: [展BOM件號];";

if (Tmp_SRO_C_NM != "展BOM件號名稱")

Tmp_RtnMsg = Tmp_RtnMsg + "第7欄欄位名稱: [展BOM件號名稱];";

if (Tmp_FIG != "圖號")

Tmp_RtnMsg = Tmp_RtnMsg + "第8欄欄位名稱: [圖號];";

if (Tmp_IND != "索引")

Tmp_RtnMsg = Tmp_RtnMsg + "第9欄欄位名稱: [索引];";

if (Tmp_NHA_PN != "上層件號")

Tmp_RtnMsg = Tmp_RtnMsg + "第10欄欄位名稱: [上層件號];";

if (Tmp_SBOM_PN != "SBOM件號")

Tmp_RtnMsg = Tmp_RtnMsg + "第11欄欄位名稱: [SBOM件號];";

if (Tmp_SBOM_C_NM != "SBOM件號中文名稱")

Tmp_RtnMsg = Tmp_RtnMsg + "第12欄欄位名稱: [SBOM件號中文名稱];";

if (Tmp_RQPN != "規範件號")

Tmp_RtnMsg = Tmp_RtnMsg + "第13欄欄位名稱: [規範件號];";

if (Tmp_FACPN != "提料件號")

Tmp_RtnMsg = Tmp_RtnMsg + "第14欄欄位名稱: [提料件號];";

if (Tmp_QPA != "每具數量")

Tmp_RtnMsg = Tmp_RtnMsg + "第15欄欄位名稱: [每具數量];";

if (Tmp_NQTYAH != "需求數量")

Tmp_RtnMsg = Tmp_RtnMsg + "第16欄欄位名稱: [需求數量];";

if (Tmp_C_UNIT != "需求單位")

Tmp_RtnMsg = Tmp_RtnMsg + "第17欄欄位名稱: [需求單位];";

if (Tmp_RSTHAH != "更換情形")

Tmp_RtnMsg = Tmp_RtnMsg + "第18欄欄位名稱: [更換情形];";

if (Tmp_FRMK != "故障現象說明")

Tmp_RtnMsg = Tmp_RtnMsg + "第19欄欄位名稱: [故障現象說明];";

if (Tmp_FASTAT != "可修件(Y/N)")

Tmp_RtnMsg = Tmp_RtnMsg + "第20欄欄位名稱: [可修件(Y/N)];";

if (Tmp_FACHRN != "可修件更換原因說明")

Tmp_RtnMsg = Tmp_RtnMsg + "第21欄欄位名稱: [可修件更換原因說明];";

if (Tmp_OVERAHD != "超量原因")

Tmp_RtnMsg = Tmp_RtnMsg + "第22欄欄位名稱: [超量原因];";

if (Tmp_FRNOAF_S != "子件工單號碼")

Tmp_RtnMsg = Tmp_RtnMsg + "第23欄欄位名稱: [子件工單號碼];";

if (Tmp_SUB_SN != "子件序號")

Tmp_RtnMsg = Tmp_RtnMsg + "第24欄欄位名稱: [子件序號];";

if (Tmp_NOTE != "特製件說明")

Tmp_RtnMsg = Tmp_RtnMsg + "第25欄欄位名稱: [特製件說明];";

if (Tmp_RATE_MORE != "經常更換")

Tmp_RtnMsg = Tmp_RtnMsg + "第26欄欄位名稱: [經常更換];";

if (Tmp_RATE != "配換率")

Tmp_RtnMsg = Tmp_RtnMsg + "第27欄欄位名稱: [配換率];";

if (Tmp_QPEI != "QPEI")

Tmp_RtnMsg = Tmp_RtnMsg + "第28欄欄位名稱: [QPEI];";

if (Tmp_RMRPNOAF != "拆挪LRU交修單號")

Tmp_RtnMsg = Tmp_RtnMsg + "第29欄欄位名稱: [拆挪LRU交修單號];";

if (Tmp_RMSBSN != "拆挪子件序號")

Tmp_RtnMsg = Tmp_RtnMsg + "第30欄欄位名稱: [拆挪子件序號];";

if (Tmp_LCN != "LCN")

Tmp_RtnMsg = Tmp_RtnMsg + "第31欄欄位名稱: [LCN];";

if (Tmp_ALC != "ALC")

Tmp_RtnMsg = Tmp_RtnMsg + "第32欄欄位名稱: [ALC];";

if (Tmp_NHA_LCN != "NHA_LCN")

Tmp_RtnMsg = Tmp_RtnMsg + "第33欄欄位名稱: [NHA_LCN];";

if (Tmp_NHA_ALC != "NHA_ALC")

Tmp_RtnMsg = Tmp_RtnMsg + "第34欄欄位名稱: [NHA_ALC];";

if (Tmp_EIAC != "EIAC")

Tmp_RtnMsg = Tmp_RtnMsg + "第35欄欄位名稱: [EIAC];";

if (Tmp_EI_LCN != "EI_LCN")

Tmp_RtnMsg = Tmp_RtnMsg + "第36欄欄位名稱: [EI_LCN];";

if (Tmp_EI_ALC != "EI_ALC")

Tmp_RtnMsg = Tmp_RtnMsg + "第37欄欄位名稱: [EI_ALC];";

if (Tmp_SBOM_CAGE != "SBOM廠碼")

Tmp_RtnMsg = Tmp_RtnMsg + "第38欄欄位名稱: [SBOM廠碼];";

if (Tmp_RQPN_CAGE != "規範廠碼")

Tmp_RtnMsg = Tmp_RtnMsg + "第39欄欄位名稱: [規範廠碼];";

if (Tmp_SBOM_UNIT != "SBOM單位")

Tmp_RtnMsg = Tmp_RtnMsg + "第40欄欄位名稱: [SBOM單位];";

if (Tmp_TMNOXX != "技令號碼")

Tmp_RtnMsg = Tmp_RtnMsg + "第41欄欄位名稱: [技令號碼];";

if (Tmp_WP != "WP")

Tmp_RtnMsg = Tmp_RtnMsg + "第42欄欄位名稱: [WP];";

if (Tmp_SBOM_PN_E_NM != "SBOM件號英文名稱")

Tmp_RtnMsg = Tmp_RtnMsg + "第43欄欄位名稱: [SBOM件號英文名稱];";

if (Tmp_FACPN_UNIT != "撥發單位")

Tmp_RtnMsg = Tmp_RtnMsg + "第44欄欄位名稱: [撥發單位];";

MyCookie.Value = HttpUtility.UrlEncode(Tmp_RtnMsg);

HttpContext.Current.Response.Cookies.Add(MyCookie);

HttpContext.Current.Response.End();

return;

};

//新增一欄位:[匯入結果說明]

ws.Range[myfunc.GetExcelPos(44, 0)].Value = "匯入結果說明";

//STEP2 取得xls.[目前件號].報工資料

// Transaction SQL List - 存放 Transaction 的 SQL

List<string> SQL_List_A = new List<string>();

int RowsCount = ws.LastRow;

//int Tmp_cnt;

for (int i = 0; i < RowsCount; i++)

{

cur_row = cur_row + 1;

Tmp_OGONOAF = ws.Range[myfunc.GetExcelPos(0, cur_row)].Value.Trim(); //提領編號

Tmp_RPNOAF = ws.Range[myfunc.GetExcelPos(1, cur_row)].Value.Trim(); //交修單號

Tmp_MRPNOAF = ws.Range[myfunc.GetExcelPos(2, cur_row)].Value.Trim();

Tmp_SAPNO = ws.Range[myfunc.GetExcelPos(3, cur_row)].Value.Trim();

Tmp_SN = ws.Range[myfunc.GetExcelPos(4, cur_row)].Value.Trim(); //序號: Tmp_SN

Tmp_SRO_PN = ws.Range[myfunc.GetExcelPos(5, cur_row)].Value.Trim();

Tmp_SRO_C_NM = ws.Range[myfunc.GetExcelPos(6, cur_row)].Value.Trim();

Tmp_FIG = ws.Range[myfunc.GetExcelPos(7, cur_row)].Value.Trim();

Tmp_IND = ws.Range[myfunc.GetExcelPos(8, cur_row)].Value.Trim();

Tmp_NHA_PN = ws.Range[myfunc.GetExcelPos(9, cur_row)].Value.Trim();

Tmp_SBOM_PN = ws.Range[myfunc.GetExcelPos(10, cur_row)].Value.Trim();

Tmp_SBOM_C_NM = ws.Range[myfunc.GetExcelPos(11, cur_row)].Value.Trim();

Tmp_RQPN = ws.Range[myfunc.GetExcelPos(12, cur_row)].Value.Trim();

Tmp_FACPN = ws.Range[myfunc.GetExcelPos(13, cur_row)].Value.Trim();

Tmp_QPA = ws.Range[myfunc.GetExcelPos(14, cur_row)].Value.Trim(); //(QPA, 每具數量)

Tmp_NQTYAH = ws.Range[myfunc.GetExcelPos(15, cur_row)].Value.Trim();

Tmp_C_UNIT = ws.Range[myfunc.GetExcelPos(16, cur_row)].Value.Trim();

Tmp_RSTHAH = ws.Range[myfunc.GetExcelPos(17, cur_row)].Value.Trim();

Tmp_FRMK = ws.Range[myfunc.GetExcelPos(18, cur_row)].Value.Trim();

Tmp_FASTAT = ws.Range[myfunc.GetExcelPos(19, cur_row)].Value.Trim(); //可修件(y/n)

Tmp_FACHRN = ws.Range[myfunc.GetExcelPos(20, cur_row)].Value.Trim();

Tmp_OVERAHD = ws.Range[myfunc.GetExcelPos(21, cur_row)].Value.Trim();

Tmp_FRNOAF_S = ws.Range[myfunc.GetExcelPos(22, cur_row)].Value.Trim(); //子件工單號碼

Tmp_SUB_SN = ws.Range[myfunc.GetExcelPos(23, cur_row)].Value.Trim(); //子件序號: Tmp_SUB_SN

Tmp_NOTE = ws.Range[myfunc.GetExcelPos(24, cur_row)].Value.Trim(); //特製件說明

Tmp_RATE_MORE = ws.Range[myfunc.GetExcelPos(25, cur_row)].Value.Trim();

Tmp_RATE = ws.Range[myfunc.GetExcelPos(26, cur_row)].Value.Trim();

Tmp_QPEI = ws.Range[myfunc.GetExcelPos(27, cur_row)].Value.Trim();

Tmp_RMRPNOAF = ws.Range[myfunc.GetExcelPos(28, cur_row)].Value.Trim();

Tmp_RMSBSN = ws.Range[myfunc.GetExcelPos(29, cur_row)].Value.Trim(); //拆挪子件序號

Tmp_LCN = ws.Range[myfunc.GetExcelPos(30, cur_row)].Value.Trim();

Tmp_ALC = ws.Range[myfunc.GetExcelPos(31, cur_row)].Value.Trim();

Tmp_NHA_LCN = ws.Range[myfunc.GetExcelPos(32, cur_row)].Value.Trim();

Tmp_NHA_ALC = ws.Range[myfunc.GetExcelPos(33, cur_row)].Value.Trim();

Tmp_EIAC = ws.Range[myfunc.GetExcelPos(34, cur_row)].Value.Trim(); //EIAC

Tmp_EI_LCN = ws.Range[myfunc.GetExcelPos(35, cur_row)].Value.Trim();

Tmp_EI_ALC = ws.Range[myfunc.GetExcelPos(36, cur_row)].Value.Trim();

Tmp_SBOM_CAGE = ws.Range[myfunc.GetExcelPos(37, cur_row)].Value.Trim();

Tmp_RQPN_CAGE = ws.Range[myfunc.GetExcelPos(38, cur_row)].Value.Trim();

Tmp_SBOM_UNIT = ws.Range[myfunc.GetExcelPos(39, cur_row)].Value.Trim(); //SBOM單位

Tmp_TMNOXX = ws.Range[myfunc.GetExcelPos(40, cur_row)].Value.Trim();

Tmp_WP = ws.Range[myfunc.GetExcelPos(41, cur_row)].Value.Trim();

Tmp_SBOM_PN_E_NM = ws.Range[myfunc.GetExcelPos(42, cur_row)].Value.Trim();

Tmp_FACPN_UNIT = ws.Range[myfunc.GetExcelPos(43, cur_row)].Value.Trim(); //撥發單位

Tmp_PN = Tmp_SBOM_PN;

Tmp_CAGE= Tmp_SBOM_CAGE;

Tmp_AMM_UOC_PK = Tmp_RPNOAF +","+ Tmp_EIAC +","+ Tmp_EI_LCN +","+ Tmp_EI_ALC +","+ Tmp_NHA_LCN +","+ Tmp_NHA_ALC +","+ Tmp_LCN +","+ Tmp_ALC +","+ Tmp_PN;

if (myfunc.checkisnull(Tmp_OGONOAF)) //若[提領編號]空白,則離開

{ break; }

Tmp_Sql=" SELECT COUNT(*) "

+" FROM AMM_FM "

+" WHERE OGONOAF=" + myfunc.AA(Tmp_OGONOAF);

Tmp_cnt = int.Parse(myfunc.SqlValue(Tmp_Sql));

if (Tmp_cnt == 0)

{

ws.Range[myfunc.GetExcelPos(44, cur_row)].Value = "提領編號("+Tmp_OGONOAF +")不存在AMM系統(AMM_FM)";

}

else

{

if (Tmp_rowcnt >= 6)

Tmp_rowcnt = Tmp_rowcnt;

//取得 AMMNO - 由SAPNO 至 AMM_SRO.AMMNO

Tmp_Sql = " SELECT AMMNO "

+ " FROM AMM_SRO "

+ " WHERE SAPNO=" + myfunc.AA(Tmp_SAPNO)

+" AND rownum=1 ";

Tmp_AMMNO = myfunc.SqlValue(Tmp_Sql);

//取得 NSN

Tmp_Sql =" SELECT NSN "

+" FROM SP_NSN@TLS_245 "

+" WHERE PN=" + myfunc.AA(Tmp_PN)

+" AND rownum=1 ";

Tmp_NSN = myfunc.SqlValue(Tmp_Sql);

//更新 AMM_UOC

Tmp_Sql=" SELECT COUNT(*) "

+" FROM AMM_UOC "

+ " WHERE RPNOAF=" + myfunc.AA(Tmp_RPNOAF)

+ " AND EIAC=" + myfunc.AA(Tmp_EIAC)

+ " AND EI_LCN=" + myfunc.AA(Tmp_EI_LCN)

+ " AND EI_ALC=" + myfunc.AA(Tmp_EI_ALC)

+ " AND NHA_LCN=" + myfunc.AA(Tmp_NHA_LCN)

+ " AND NHA_ALC=" + myfunc.AA(Tmp_NHA_ALC)

+ " AND LCN=" + myfunc.AA(Tmp_LCN)

+ " AND ALC=" + myfunc.AA(Tmp_ALC)

+ " AND PN=" + myfunc.AA(Tmp_PN);

Tmp_cnt = myfunc.str2int(myfunc.SqlValue(Tmp_Sql));

if (Tmp_cnt>0)

{

Tmp_Sql = " UPDATE AMM_UOC "

+ " SET AMMNO=" + myfunc.AA(Tmp_AMMNO) + ","

+ " RQPN= " + myfunc.AA(Tmp_RQPN) + ","

+ " FACPN= " + myfunc.AA(Tmp_RQPN) + ","

+ " CAGE= " + myfunc.AA(Tmp_SBOM_CAGE) + ","

+ " NSN= " + myfunc.AA(Tmp_NSN)

+ " WHERE RPNOAF=" + myfunc.AA(Tmp_RPNOAF)

+ " AND EIAC=" + myfunc.AA(Tmp_EIAC)

+ " AND EI_LCN=" + myfunc.AA(Tmp_EI_LCN)

+ " AND EI_ALC=" + myfunc.AA(Tmp_EI_ALC)

+ " AND NHA_LCN=" + myfunc.AA(Tmp_NHA_LCN)

+ " AND NHA_ALC=" + myfunc.AA(Tmp_NHA_ALC)

+ " AND LCN=" + myfunc.AA(Tmp_LCN)

+ " AND ALC=" + myfunc.AA(Tmp_ALC)

+ " AND PN=" + myfunc.AA(Tmp_PN);

Tmp_Str = "更新(AMM_UOC)";

}

else

{ //新增 AMM_UOC

//不在資料庫 && PK 未重覆,則新增

if (Tmp_AMM_UOC_LIST.IndexOf(Tmp_AMM_UOC_PK) == -1)

{

Tmp_Sql = " INSERT INTO AMM_UOC "

+ "( RPNOAF,EIAC,EI_LCN,EI_ALC,NHA_LCN,NHA_ALC,LCN,ALC,PN,"

+ " AMMNO,RQPN,FACPN,CAGE,NSN ) "

+ " VALUES "

+ "(" + myfunc.AA(Tmp_RPNOAF) + "," + myfunc.AA(Tmp_EIAC) + "," + myfunc.AA(Tmp_EI_LCN) + "," + myfunc.AA(Tmp_EI_ALC) + "," + myfunc.AA(Tmp_NHA_LCN) + "," + myfunc.AA(Tmp_NHA_ALC) + "," + myfunc.AA(Tmp_LCN) + "," + myfunc.AA(Tmp_ALC) + "," + myfunc.AA(Tmp_PN) + ","

+ myfunc.AA(Tmp_AMMNO) + "," + myfunc.AA(Tmp_RQPN) + "," + myfunc.AA(Tmp_FACPN) + "," + myfunc.AA(Tmp_CAGE) + "," + myfunc.AA(Tmp_NSN)

+ ")";

Tmp_Str = "新增(AMM_UOC)";

}

else

{ //不在資料庫 ,但是目前 PK 在(匯入檔案中) 已重覆,則更新

Tmp_Sql = " UPDATE AMM_UOC "

+ " SET AMMNO=" + myfunc.AA(Tmp_AMMNO) + ","

+ " RQPN= " + myfunc.AA(Tmp_RQPN) + ","

+ " FACPN= " + myfunc.AA(Tmp_RQPN) + ","

+ " CAGE= " + myfunc.AA(Tmp_SBOM_CAGE) + ","

+ " NSN= " + myfunc.AA(Tmp_NSN)

+ " WHERE RPNOAF=" + myfunc.AA(Tmp_RPNOAF)

+ " AND EIAC=" + myfunc.AA(Tmp_EIAC)

+ " AND EI_LCN=" + myfunc.AA(Tmp_EI_LCN)

+ " AND EI_ALC=" + myfunc.AA(Tmp_EI_ALC)

+ " AND NHA_LCN=" + myfunc.AA(Tmp_NHA_LCN)

+ " AND NHA_ALC=" + myfunc.AA(Tmp_NHA_ALC)

+ " AND LCN=" + myfunc.AA(Tmp_LCN)

+ " AND ALC=" + myfunc.AA(Tmp_ALC)

+ " AND PN=" + myfunc.AA(Tmp_PN);

Tmp_Str = "更新(AMM_UOC)";

}

SQL_List_A.Add(Tmp_Sql);

Tmp_AMM_UOC_LIST.Add(Tmp_AMM_UOC_PK); //處理完成,才將 PK 加入 String_List

//更新 AMM_UOCD

//取得 AMM_UOCD_PK - 因為無 ITMUOCD , 所以以原PK+Tmp_TMNOXX+Tmp_FIG+Tmp_IND+Tmp_WP 當成 PK

Tmp_AMM_UOCD_PK = Tmp_RPNOAF + "," + Tmp_EIAC + "," + Tmp_EI_LCN + "," + Tmp_EI_ALC + "," + Tmp_NHA_LCN + "," + Tmp_NHA_ALC + "," + Tmp_LCN + "," + Tmp_ALC + "," + Tmp_PN

+Tmp_TMNOXX+Tmp_FIG+Tmp_IND+Tmp_WP;

//判斷 AMM_UOCD 是否已存在原PK+Tmp_TMNOXX+Tmp_FIG+Tmp_IND+Tmp_WP

Tmp_Sql = " SELECT ITMUOCD "

+ " FROM AMM_UOCD "

+ " WHERE RPNOAF=" + myfunc.AA(Tmp_RPNOAF)

+ " AND EIAC=" + myfunc.AA(Tmp_EIAC)

+ " AND EI_LCN=" + myfunc.AA(Tmp_EI_LCN)

+ " AND EI_ALC=" + myfunc.AA(Tmp_EI_ALC)

+ " AND NHA_LCN=" + myfunc.AA(Tmp_NHA_LCN)

+ " AND NHA_ALC=" + myfunc.AA(Tmp_NHA_ALC)

+ " AND LCN=" + myfunc.AA(Tmp_LCN)

+ " AND ALC=" + myfunc.AA(Tmp_ALC)

+ " AND PN=" + myfunc.AA(Tmp_PN)

+ " AND TMNOXX=" + myfunc.AA(Tmp_TMNOXX)

+ " AND FIG=" + myfunc.AA(Tmp_FIG)

+ " AND IND=" + myfunc.AA(Tmp_IND)

+ " AND WP=" + myfunc.AA(Tmp_WP);

Tmp_ITMUOCD=myfunc.SqlValue(Tmp_Sql);

if (!myfunc.checkisnull(Tmp_ITMUOCD)) //Tmp_AMM_UOCD 已存在,則更新

{

Tmp_Sql = " UPDATE AMM_UOCD "

+ " SET EFF='Y' ,"

+ " AMMNO=" + myfunc.AA(Tmp_AMMNO) + ","

+ " TMNOXX= " + myfunc.AA(Tmp_TMNOXX) + ","

+ " FIG= " + myfunc.AA(Tmp_FIG) + ","

+ " IND= " + myfunc.AA(Tmp_IND) + ","

+ " WP= " + myfunc.AA(Tmp_WP) + ","

+ " OVERAHD= " + myfunc.AA(Tmp_OVERAHD) + ","

+ " RSTHAH= " + myfunc.AA(Tmp_RSTHAH) + ","

+ " FRMK= " + myfunc.AA(Tmp_FRMK) + ","

+ " FRNOAF_S= " + myfunc.AA(Tmp_FRNOAF_S) + ","

+ " SN= " + myfunc.AA(Tmp_SN) + ","

+ " FACHRN= " + myfunc.AA(Tmp_FACHRN) + ","

+ " RMRPNOAF= " + myfunc.AA(Tmp_RMRPNOAF) + ","

+ " RMSBSN= " + myfunc.AA(Tmp_RMSBSN) + ","

+ " NQTYAH= " + myfunc.AA(Tmp_NQTYAH) + ","

+ " C_UNIT= " + myfunc.AA(Tmp_C_UNIT) + ","

+ " EMPLYID= " + myfunc.AA(Tmp_EMPLYID) + ","

+ " RDUOCD= " + myfunc.AA(Tmp_RDUOCD) + ","

+ " STAUOCD= " + myfunc.AA(Tmp_STAUOCD) + ","

+ " FASTAT= " + myfunc.AA(Tmp_FASTAT)

+ " WHERE RPNOAF=" + myfunc.AA(Tmp_RPNOAF)

+ " AND EIAC=" + myfunc.AA(Tmp_EIAC)

+ " AND EI_LCN=" + myfunc.AA(Tmp_EI_LCN)

+ " AND EI_ALC=" + myfunc.AA(Tmp_EI_ALC)

+ " AND NHA_LCN=" + myfunc.AA(Tmp_NHA_LCN)

+ " AND NHA_ALC=" + myfunc.AA(Tmp_NHA_ALC)

+ " AND LCN=" + myfunc.AA(Tmp_LCN)

+ " AND ALC=" + myfunc.AA(Tmp_ALC)

+ " AND PN=" + myfunc.AA(Tmp_PN)

+ " AND ITMUOCD=" + myfunc.AA(Tmp_ITMUOCD);

Tmp_Str = Tmp_Str + "," + "更新(AMM_UOCD)";

}

else //不存在資料庫

{

Tmp_Sql= " INSERT INTO AMM_UOCD "

+" (RPNOAF, EIAC, EI_LCN, EI_ALC, NHA_LCN, NHA_ALC, LCN, ALC, PN,"

+" ITMUOCD,"

+ " EFF,AMMNO,TMNOXX,FIG,IND,WP, "

+ " OVERAHD,RSTHAH,FRMK,FRNOAF_S,SN, "

+ " FACHRN,RMRPNOAF,RMSBSN,NQTYAH,C_UNIT, "

+ " EMPLYID,RDUOCD,STAUOCD,FASTAT "

+ ") "

+" SELECT "+ myfunc.AA(Tmp_RPNOAF) + "," + myfunc.AA(Tmp_EIAC) + "," + myfunc.AA(Tmp_EI_LCN) + "," + myfunc.AA(Tmp_EI_ALC) + "," + myfunc.AA(Tmp_NHA_LCN) + "," + myfunc.AA(Tmp_NHA_ALC) + "," + myfunc.AA(Tmp_LCN) + "," + myfunc.AA(Tmp_ALC) + "," + myfunc.AA(Tmp_PN) + ","

//取得 MAX(ITMUOCD)+1

+ " ( SELECT nvl(MAX(TO_NUMBER(ITMUOCD)), 0) + 1 "

+" FROM AMM_UOCD "

+ " WHERE RPNOAF = " +myfunc.AA(Tmp_RPNOAF)

+" AND EIAC = "+ myfunc.AA(Tmp_EIAC)

+" AND EI_LCN = "+ myfunc.AA(Tmp_EI_LCN)

+" AND EI_ALC = " + myfunc.AA(Tmp_EI_ALC)

+ " AND NHA_LCN = " + myfunc.AA(Tmp_NHA_LCN)

+ " AND NHA_ALC = " + myfunc.AA(Tmp_NHA_ALC)

+" AND LCN ="+ myfunc.AA(Tmp_LCN)

+" AND ALC = "+ myfunc.AA(Tmp_ALC)

+" AND PN =" + myfunc.AA(Tmp_PN)+"),"

+ myfunc.AA("Y") + "," + myfunc.AA(Tmp_AMMNO) + "," + myfunc.AA(Tmp_TMNOXX) + "," + myfunc.AA(Tmp_FIG) + "," + myfunc.AA(Tmp_IND) + "," + myfunc.AA(Tmp_WP) + ","

+ myfunc.AA(Tmp_OVERAHD) + "," + myfunc.AA(Tmp_RSTHAH) + "," + myfunc.AA(Tmp_FRMK) + "," + myfunc.AA(Tmp_FRNOAF_S) + "," + myfunc.AA(Tmp_SN) + ","

+ myfunc.AA(Tmp_FACHRN) + "," + myfunc.AA(Tmp_RMRPNOAF) + "," + myfunc.AA(Tmp_RMSBSN) + "," + myfunc.AA(Tmp_NQTYAH) + "," + myfunc.AA(Tmp_C_UNIT) + ","

+ myfunc.AA(Tmp_EMPLYID) + "," + myfunc.AA(Tmp_RDUOCD) + "," + myfunc.AA(Tmp_STAUOCD) + "," + myfunc.AA(Tmp_FASTAT)

+" FROM DUAL ";

Tmp_Str = Tmp_Str + "," + "新增(AMM_UOCD)";

}

SQL_List_A.Add(Tmp_Sql); // 新增/更新 AMM_UOCD

Tmp_AMM_UOCD_LIST.Add(Tmp_AMM_UOCD_PK);

//myfunc.SqlExec(Tmp_Sql);

Tmp_Str = "更新完成!! [ " + Tmp_Str + " ] ";

ws.Range[myfunc.GetExcelPos(44, cur_row)].Value = Tmp_Str;

}

Tmp_rowcnt = Tmp_rowcnt + 1;

} // end of for (int i = 0; i < RowsCount; i++)

// 批次執行 SQL , 若有誤,則傳回 -1 , 若正確,則傳回 1

Tmp_Str = String.Join(";",SQL_List_A);

Tmp_cnt=excuteSQLTran(SQL_List_A);

//批次執行 SQL 有錯誤

if (Tmp_cnt == -1)

{

Tmp_RtnMsg = getExcMsg();

raiseEXP(new Exception() , Tmp_RtnMsg);

}

} //end of foreach (string cur_FName in Request.Files)

ws.AllocatedRange.AutoFitColumns();

wk.SaveToStream(stream, FileFormat.Version2007);

Tmp_Str = "[非TLS品項批次提料]完成!!(共更新" + Tmp_rowcnt.ToString() + "筆) "

+ "匯入檔案名稱(" + Tmp_FName + ")";

MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));

//MyCookie.Value = HttpUtility.UrlEncode(Tmp_Str);

HttpContext.Current.Response.Cookies.Add(MyCookie);

HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(Tmp_out_FName, System.Text.Encoding.UTF8) + "\"");

HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

HttpContext.Current.Response.BinaryWrite(stream.ToArray());

HttpContext.Current.Response.End();

} // end fo try{

catch (Exception e)

{

//Console.WriteLine(e);

Tmp_RtnMsg = "錯誤訊息如下:"

+ e.Message;

MyCookie.Value = HttpUtility.UrlEncode(Tmp_RtnMsg);

HttpContext.Current.Response.Cookies.Add(MyCookie);

HttpContext.Current.Response.End();

//throw;

}

finally

{

wk.Dispose();

//reader.Dispose();

cmd.Dispose();

conn.Dispose();

conn.Close();

}

//return response;

//Tmp_Cnt = 6;

} // end of INS_AMM_UOC_UOCD()

3>更新 AMM_UOC 改用 MERGE 方式, 不用自行處理 - Tmp_AMM_UOC_LIST.IndexOf(Tmp_AMM_UOC_PK)

//- 寫入資料庫時,若不存在,則 Insert ,

//若已存在,則Update - ORACLE 採用 MERGE 語法 - MYSQL 採用 ON DUPLICATE KEY UPDATE

//2025/04/10 改用 MERGE 語法處理 , 不採用自行處理 - Tmp_AMM_UOC_LIST.IndexOf(Tmp_AMM_UOC_PK)

Tmp_Sql = " MERGE INTO AMM_UOC B "

+" USING (SELECT "+myfunc.AA(Tmp_RPNOAF)+" AS RPNOAF, "

+myfunc.AA(Tmp_EIAC)+ " AS EIAC, "

+ myfunc.AA(Tmp_EI_LCN) + "AS EI_LCN,"

+ myfunc.AA(Tmp_EI_ALC) + " AS EI_ALC, "

+ myfunc.AA(Tmp_NHA_LCN) + " AS NHA_LCN,"

+ myfunc.AA(Tmp_NHA_ALC) + " AS NHA_ALC,"

+ myfunc.AA(Tmp_LCN) + " AS LCN,"

+ myfunc.AA(Tmp_ALC) + " AS ALC,"

+ myfunc.AA(Tmp_PN) + " AS PN,"

+ myfunc.AA(Tmp_AMMNO) + " AS AMMNO,"

+ myfunc.AA(Tmp_RQPN) + " AS RQPN, "

+ myfunc.AA(Tmp_FACPN) + " AS FACPN, "

+ myfunc.AA(Tmp_CAGE) + " AS CAGE, "

+ myfunc.AA(Tmp_NSN) + " AS NSN "

+" FROM dual) A "

+"ON ( B.RPNOAF = A.RPNOAF "

+" AND B.EIAC = A.EIAC "

+" AND B.EI_LCN = A.EI_LCN "

+" AND B.EI_ALC = A.EI_ALC "

+" AND B.NHA_LCN = A.NHA_LCN "

+" AND B.NHA_ALC = A.NHA_ALC "

+" AND B.LCN = A.LCN "

+" AND B.ALC = A.ALC "

+" AND B.PN = A.PN "

+" ) "

+" WHEN MATCHED THEN "

+" UPDATE SET B.AMMNO = A.AMMNO, "

+" B.RQPN = A.RQPN, "

+" B.FACPN = A.FACPN, "

+" B.CAGE = A.CAGE, "

+" B.NSN = A.NSN "

+" WHEN NOT MATCHED THEN "

+" INSERT(RPNOAF, EIAC, EI_LCN, EI_ALC, NHA_LCN, NHA_ALC, LCN, ALC, PN, AMMNO, RQPN,FACPN,CAGE,NSN) "

+" VALUES"

+"(A.RPNOAF, A.EIAC, A.EI_LCN, A.EI_ALC, A.NHA_LCN, A.NHA_ALC, A.LCN, A.ALC, A.PN, A.AMMNO, A.RQPN, A.FACPN,A.CAGE,A.NSN) ";

SQL_List_A.Add(Tmp_Sql);

Tmp_Str = "MERGE(AMM_UOC)";

2025年4月6日星期日

已核准的 [網路圖報工] 資料.修改報工年度月份

目的: 已核准報工.修改報工年度月份

處理說明: 1>挑選報工.[已核准]的資料

2>直接修改該筆報工.年度月份即可

Xls欄位換行串換: char(10) , \n (Escape n)

目的: Xls欄位換行串換: char(10)

處理說明: 1>換行字元: char(10)

2>串接函式: CONCATENATE

3>程式寫法: \n - Escape n - V20305D*.cs
if (myfunc.checkisnull(Tmp_C_UNIT))
Tmp_RtnMsg = Tmp_RtnMsg + "需求單位不可空白!!\n";

4>xls欄位格式: 自動換行,且調整行高

// 調整[匯入結果說明]欄位, 自動換行,且調整行高以適應內容

ws.Range[myfunc.GetExcelPos(44, cur_row)].Style.WrapText = true;

ws.AutoFitRow(1);

Tmp_RtnMsg = Tmp_RtnMsg + "更換情況(" + Tmp_RSTHAH + ")代碼不正確!!\n";

2025年3月26日星期三

V20305I –[附檔瀏覽] – 下載檔案 - downloadFile2 - 子畫面- 簡單 Grid

目的: V20305I –[附檔瀏覽] – 下載檔案

處理說明: 1>呼叫 downloadFile2

var Tmp_url = "../api/myAPI/downloadFile2?aTbl=" + Tmp_TblNM + "&Flds=" + Tmp_Flds + "&aCnd=" + Tmp_Cnd;

1>*.js

var Tmp_url = "../api/myAPI/downloadFile2?aTbl=" + Tmp_TblNM + "&Flds=" + Tmp_Flds + "&aCnd=" + Tmp_Cnd; document.location = Tmp_url;

2>*.cs

public myAPIController()

{

//DBTable = "";

DBLINK = "AMMEU";

}

[HttpGet]

public void downloadFile2(string aTbl, string Flds, string aCnd)

{

string[] Ary = Flds.Split(',');

string fnm = Ary[0]; //檔案名稱

string fbdy = Ary[1]; //檔案內容

string cnd = aCnd;

string Strsql = "select " + Flds + " from " + aTbl + " where 1=1 " + cnd;

string Tmp_CntSql = "select count(*) from " + aTbl + " where 1=1 " + cnd;

string Tmp_Str;

int Tmp_cnt;

HttpCookie MyCookie;

try

{

Tmp_cnt = int.Parse(myfunc.SqlValue(Tmp_CntSql));

if (Tmp_cnt >= 0)

{

//必需先加入 Cookie , 再 downloadFile

//--> 因 downloadFile 會 HttpContext.Current.Response.End(); , 所以要先加 Cookie , 再 downloadFile

Tmp_Str = "檔案下載完成!! ";

MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));

HttpContext.Current.Response.Cookies.Add(MyCookie);

HttpContext.Current.ApplicationInstance.CompleteRequest();

downloadFile(Strsql, fnm, fbdy); //將 filebody 欄位以 byte[] 傳至 response

// 接收方式:

//1>下載檔案 : document.location = Tmp_url;

//2>顯示照片檔 : Ext.getCmp('sub_V80403C1_img1').setSrc(Tmp_url);

}

else

{

Tmp_Str = "檔案不存在,請檢核!! ";

MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));

HttpContext.Current.Response.Cookies.Add(MyCookie);

HttpContext.Current.ApplicationInstance.CompleteRequest();

}

catch (Exception ex)

{

Tmp_Str = ex.Message;

if (Tmp_Str.IndexOf("DBNull") > -1)

Tmp_Str = "檔案內容為空白,敬請檢核!! ";

MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));

HttpContext.Current.Response.Cookies.Add(MyCookie);

HttpContext.Current.ApplicationInstance.CompleteRequest();

throw ex;

} //檔案無法讀取,仍會下載 byte=0

finally

{ }

}

2025年3月20日星期四

Web – V20305G –[物料清單匯出]鈕 – 套表匯出 / 不套表匯出 - get_SS_FILES - 取得套表檔案

目的: V20305G –[物料清單匯出]鈕 – 套表匯出 / 不套表匯出

- get_SS_FILES - 取得套表檔案

處理說明: 1>依勾選的資料s,當成參數,傳至後端(*.js)

//取得目前所勾選資料,再匯出

for (var i = 0; i < CHK_PK_OBJ.PK_LIST.length; i++) {

cur_rec = CHK_PK_OBJ.ALL_LIST[i];

if (!(JSON.stringify(cur_rec) === '{}')) {

Tmp_DataAry1.push(cur_rec);

}

//將目前子畫面的資料(Table)轉成 JSON字串

let Tmp_DataString1 = JSON.stringify(Tmp_DataAry1);

//因為[備註]欄位有/n .. 等特殊字元 , JSON.stringify 會 Error

var np = {};

np = {

DataString1: Tmp_DataString1,

};

2>取得套表檔案(*.cs)

string Tmp_FName = "V20305_物料清單匯出.xlsx"; //套表的檔案名稱 ,

string documentPath = HttpContext.Current.Server.MapPath("~") + "document\\";

string pathFName = documentPath + Tmp_FName;

FileStream fs = null;

Workbook wk = null;

fs = myfunc.get_SS_FILES(Tmp_FName);

wk = new Workbook();

wk.LoadFromStream(fs);

3>若不用套表(*.cs)
string[] outFieldArray = {"提領編號","交修單號","維修交修單號","工單號碼","序號",
"展BOM件號","展BOM件號名稱","圖號","索引","技令號碼",
"WP","提料件號","零件名稱","記錄項次","需求數量",
"需求單位","更換情形","超量原因","子件工單號碼","子件序號",
"備註","機種","可修件更換原因說明","拆挪LRU交修單號","拆挪子件序號",
"故障現象說明","請料者","狀態"};
for (int i = 0; i < outFieldArray.Length; i++)
{
ws.Range[myfunc.GetExcelPos(i, 0)].Text = outFieldArray[i];
}

1>*.js

//取得目前所勾選資料,再匯出

for (var i = 0; i < CHK_PK_OBJ.PK_LIST.length; i++) {

cur_rec = CHK_PK_OBJ.ALL_LIST[i];

if (!(JSON.stringify(cur_rec) === '{}')) {

Tmp_DataAry1.push(cur_rec);

}

let Tmp_DataString1 = JSON.stringify(Tmp_DataAry1);

//console.log("Tmp_DataString1:", Tmp_DataString1);

//因為[備註]欄位有/n .. 等特殊字元 , JSON.stringify 會 Error

var np = {};

np = {

DataString1: Tmp_DataString1,

};

2>*.cs

[HttpPost]

public void Material_XlsOut()

{

//取得參數值

var c = HttpContext.Current;

NameValueCollection nvc = c.Request.Form;

// 1>將 SS_FILES.FBOLD 存成 Local檔案

//取得目前子畫面的欄位值

string Tmp_DataString1 = nvc["DataString1"]; //Tmp_DataString: 將目前子畫面的資料. JSON字串

//將 table 字串轉成 DataTable

//DataTable DataTable1 = (DataTable)JsonConvert.DeserializeObject(Tmp_DataString1, (typeof(DataTable)));

DataTable dt1 = myfunc.JSON2dt(Tmp_DataString1);

string Tmp_Sql = "";

string Tmp_Str = "";

string Tmp_Str1 = "";

string Tmp_RtnMsg;

string Tmp_FName = "V20305_物料清單匯出.xlsx"; //套表的檔案名稱 ,

//documentPath = c:\\inetpub\wwwroot\TLS5\TLSWEB_AMM5\document\

string documentPath = HttpContext.Current.Server.MapPath("~") + "document\\"; //取得實實的路徑

string pathFName = documentPath + Tmp_FName;

//需要擷取大量資料時，DataReader 是很好的選擇，因為資料不會快取至記憶體。

//OracleDataReader reader;

FileStream fs = null;

Workbook wk = null;

//MemoryStream mstream = new MemoryStream();

string FileName = "V20305_物料清單匯出_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";

string FileName1 = documentPath + FileName;

MemoryStream mstream = new MemoryStream();

HttpCookie MyCookie;

int row = 0;

try

{

//套表設定 - 欄位寬度佳

// 取得 Template SS_FILES

fs = myfunc.get_SS_FILES(Tmp_FName);

wk = new Workbook();

wk.LoadFromStream(fs);

//2 > 主機.Local檔案 XLS 讀入 FStream

Worksheet ws = wk.Worksheets[0];//獲取第一個工作表

//非套表設定 - 欄位寬度不佳

//string[] outFieldArray = {"提領編號","交修單號","維修交修單號","工單號碼","序號",

// "展BOM件號","展BOM件號名稱","圖號","索引","技令號碼",

// "WP","提料件號","零件名稱","記錄項次","需求數量",

// "需求單位","更換情形","超量原因","子件工單號碼","子件序號",

// "備註","機種","可修件更換原因說明","拆挪LRU交修單號","拆挪子件序號",

// "故障現象說明","請料者","狀態"};

//for (int i = 0; i < outFieldArray.Length; i++)

//{

// ws.Range[myfunc.GetExcelPos(i, 0)].Text = outFieldArray[i];

//}

////AMM_UOC

string Tmp_RPNOAF, Tmp_OGONOAF, Tmp_MRPNOAF, Tmp_SAPNO_, Tmp_SEQOAF;

string Tmp_PN, Tmp_C_NM;

//AMM_UOCD

string Tmp_FIG, Tmp_IND, Tmp_TMNOXX, Tmp_WP, Tmp_FACPN, Tmp_RQCAGE;

string Tmp_C_NM_, Tmp_ITMUOCD, Tmp_NQTYAH, Tmp_C_UNIT, Tmp_RSTHAH, Tmp_RSTHAH_;

string Tmp_OVERAHD, Tmp_FRNOAF_S, Tmp_SN, Tmp_NOTE, Tmp_EIAC, Tmp_FACHRN;

string Tmp_RMRPNOAF, Tmp_RMSBSN, Tmp_FRMK, Tmp_EMPLYID, Tmp_EMPLYID_;

string Tmp_STAUOCD, Tmp_STAUOCD_;

int Tmp_cnt1 = dt1.Rows.Count;

int Tmp_cnt2;

DataTable dt2 = new DataTable();

for (int i = 0; i < dt1.Rows.Count; i++)

{

//PK AMM_FM

Tmp_RPNOAF = dt1.Rows[i]["RPNOAF"].ToString();

Tmp_OGONOAF = dt1.Rows[i]["OGONOAF"].ToString();

Tmp_MRPNOAF = dt1.Rows[i]["MRPNOAF"].ToString();

Tmp_SAPNO_ = dt1.Rows[i]["SAPNO_"].ToString();

Tmp_SEQOAF = dt1.Rows[i]["SEQOAF"].ToString();

Tmp_PN = dt1.Rows[i]["PN"].ToString();

Tmp_C_NM = dt1.Rows[i]["C_NM"].ToString();

Tmp_Sql = " SELECT dense_rank() over(order by A.PN,A.RPNOAF,A.EIAC,A.EI_LCN,A.EI_ALC,A.NHA_LCN,A.NHA_ALC,A.LCN,A.ALC) as RNK, "

+ " A.RPNOAF,A.AMMNO,A.EIAC,A.EI_LCN,A.EI_ALC,A.NHA_LCN,A.NHA_ALC,A.LCN,A.ALC,"

+ " A.PN,A.CAGE,A.NSN,A.RQPN,A.RQCAGE, A.FACPN,"

+ " B.FIG,B.IND,B.TMNOXX,B.WP,B.ITMUOCD, "

+ " B.NQTYAH,B.C_UNIT,B.RSTHAH,B.OVERAHD,B.FRNOAF_S,"

+ " B.SN,B.NOTE,B.EIAC,B.FACHRN,B.RMRPNOAF,B.RMSBSN,"

+ " B.FRMK,B.EMPLYID,B.STAUOCD "

+ " FROM AMM_UOC A, AMM_UOCD B "

+ " WHERE A.RPNOAF = B.RPNOAF "

+ " AND A.EIAC = B.EIAC "

+ " AND A.EI_LCN = B.EI_LCN "

+ " AND A.EI_ALC = B.EI_ALC "

+ " AND A.NHA_LCN = B.NHA_LCN "

+ " AND A.NHA_ALC = B.NHA_ALC "

+ " AND A.LCN = B.LCN "

+ " AND A.ALC = B.ALC "

+ " and B.EFF='Y' ";

//交修單號

if (!myfunc.checkisnull(Tmp_RPNOAF))

{

Tmp_Sql = Tmp_Sql + " AND A.RPNOAF =" + myfunc.AA(Tmp_RPNOAF);

};

dt2 = myfunc.SqlOpen(dt2, Tmp_Sql);

Tmp_cnt2 = dt2.Rows.Count;

for (int j = 0; j < dt2.Rows.Count; j++)

{

//Detail AMM_UOC,AMM_UOCD

Tmp_FIG = dt2.Rows[j]["FIG"].ToString();

Tmp_IND = dt2.Rows[j]["IND"].ToString();

Tmp_TMNOXX = dt2.Rows[j]["TMNOXX"].ToString();

Tmp_WP = dt2.Rows[j]["WP"].ToString();

Tmp_FACPN = dt2.Rows[j]["FACPN"].ToString();

Tmp_RQCAGE = dt2.Rows[j]["RQCAGE"].ToString();

Tmp_Sql = " SELECT C_NM "

+ " FROM SP_BASE@TLS_245 "

+ " WHERE PN=" + myfunc.AA(Tmp_FACPN)

+ " AND CAGE=" + myfunc.AA(Tmp_RQCAGE);

Tmp_C_NM_ = myfunc.SqlValue(Tmp_Sql);

Tmp_ITMUOCD = dt2.Rows[j]["ITMUOCD"].ToString();

Tmp_NQTYAH = dt2.Rows[j]["NQTYAH"].ToString();

Tmp_C_UNIT = dt2.Rows[j]["C_UNIT"].ToString();

Tmp_RSTHAH = dt2.Rows[j]["FIG"].ToString();

//public static string get_KEYCODE_NM(string par_TBLNM, string par_KEY_NAME, string par_KEY_CODE)

Tmp_RSTHAH_ = myfunc.get_KEYCODE_NM("AMM_UOCD", "RSTHAH", Tmp_RSTHAH);

Tmp_OVERAHD = dt2.Rows[j]["OVERAHD"].ToString();

Tmp_FRNOAF_S = dt2.Rows[j]["FRNOAF_S"].ToString();

Tmp_SN = dt2.Rows[j]["SN"].ToString();

Tmp_NOTE = dt2.Rows[j]["NOTE"].ToString();

Tmp_EIAC = dt2.Rows[j]["EIAC"].ToString();

Tmp_FACHRN = dt2.Rows[j]["FACHRN"].ToString();

Tmp_RMRPNOAF = dt2.Rows[j]["RMRPNOAF"].ToString();

Tmp_RMSBSN = dt2.Rows[j]["RMSBSN"].ToString();

Tmp_FRMK = dt2.Rows[j]["FRMK"].ToString();

Tmp_EMPLYID = dt2.Rows[j]["EMPLYID"].ToString();

Tmp_EMPLYID_ = myfunc.getEmp_NM(Tmp_EMPLYID);

Tmp_STAUOCD = dt2.Rows[j]["STAUOCD"].ToString();

Tmp_STAUOCD_ = myfunc.get_KEYCODE_NM("AMM_UOCD", "STAUOCD", Tmp_STAUOCD);

//寫入 EXCEL檔案

row = row + 1;

ws.Range[myfunc.GetExcelPos(0, row)].Text = Tmp_OGONOAF;

ws.Range[myfunc.GetExcelPos(1, row)].Text = Tmp_RPNOAF;

ws.Range[myfunc.GetExcelPos(2, row)].Text = Tmp_MRPNOAF;

ws.Range[myfunc.GetExcelPos(3, row)].Text = Tmp_SAPNO_;

ws.Range[myfunc.GetExcelPos(4, row)].Text = Tmp_SEQOAF;

ws.Range[myfunc.GetExcelPos(5, row)].Text = Tmp_PN;

ws.Range[myfunc.GetExcelPos(6, row)].Text = Tmp_C_NM;

ws.Range[myfunc.GetExcelPos(7, row)].Text = Tmp_FIG;

ws.Range[myfunc.GetExcelPos(8, row)].Text = Tmp_IND;

ws.Range[myfunc.GetExcelPos(9, row)].Text = Tmp_TMNOXX;

ws.Range[myfunc.GetExcelPos(10, row)].Text = Tmp_WP;

ws.Range[myfunc.GetExcelPos(11, row)].Text = Tmp_FACPN;

ws.Range[myfunc.GetExcelPos(12, row)].Text = Tmp_C_NM_;

ws.Range[myfunc.GetExcelPos(13, row)].Text = Tmp_ITMUOCD;

ws.Range[myfunc.GetExcelPos(14, row)].Text = Tmp_NQTYAH;

ws.Range[myfunc.GetExcelPos(15, row)].Text = Tmp_C_UNIT;

ws.Range[myfunc.GetExcelPos(16, row)].Text = Tmp_RSTHAH_;

ws.Range[myfunc.GetExcelPos(17, row)].Text = Tmp_OVERAHD;

ws.Range[myfunc.GetExcelPos(18, row)].Text = Tmp_FRNOAF_S;

ws.Range[myfunc.GetExcelPos(19, row)].Text = Tmp_SN;

ws.Range[myfunc.GetExcelPos(20, row)].Text = Tmp_NOTE;

ws.Range[myfunc.GetExcelPos(21, row)].Text = Tmp_EIAC;

ws.Range[myfunc.GetExcelPos(22, row)].Text = Tmp_FACHRN;

ws.Range[myfunc.GetExcelPos(23, row)].Text = Tmp_RMRPNOAF;

ws.Range[myfunc.GetExcelPos(24, row)].Text = Tmp_RMSBSN;

ws.Range[myfunc.GetExcelPos(25, row)].Text = Tmp_FRMK;

ws.Range[myfunc.GetExcelPos(26, row)].Text = Tmp_EMPLYID_;

ws.Range[myfunc.GetExcelPos(27, row)].Text = Tmp_STAUOCD_;

} // for j=

} // for i=

wk.SaveToStream(mstream, FileFormat.Version2007);

wk.SaveToFile(FileName1, FileFormat.Version2007);

} //try

catch (Exception e)

{

Console.WriteLine(e);

Tmp_Str = "錯誤:匯出檔案失敗,訊息如下: "

+ e.Message;

MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));

HttpContext.Current.Response.Cookies.Add(MyCookie);

HttpContext.Current.Response.End();

}

finally

{

wk.Dispose();

fs.Close();

fs.Dispose();

}

//Tmp_Cnt = 6;

Tmp_Str = "已匯出完成!!(" + row.ToString() + "筆) "

+ "檔案名稱(" + FileName + ")";

MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));

HttpContext.Current.Response.Cookies.Add(MyCookie);

HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + "\"");

HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

HttpContext.Current.Response.BinaryWrite(mstream.ToArray());

HttpContext.Current.Response.End();

}

Web V20301 – 複製 -目前勾選的資料, 非目前所在資料 - 移除原本Template eventhandler - 複製 Array -跳至最後一頁

目的: Web V20301 – 複製 -目前勾選的資料, 非目前所在資料

處理說明:1>將[複製]鈕 - Template 程式. disable
Ext.getCmp('btn_copy').setVisible(true);
Ext.getCmp('btn_copy').setText("複製");

Ext.getCmp('btn_copy').un("click", Ext.getCmp('btn_copy').events.click.listeners[0].fn);

2>處理勾選的資料s

if (CHK_PK_OBJ.PK_LIST.length == 0) {

buttonModel.modelType = 0;

mywarnalert('請先勾選要複製的資料!!');

}

else {

copy_recs = Array.from(CHK_PK_OBJ.ALL_LIST);

for (let i = 0; i <= CHK_PK_OBJ.PK_LIST.length - 1; i++) {

console.log("i:", i);

copy_rec = copy_recs[i];

Tmp_NEXT_FMNO = get_NEXT_FMNO();

var Tmp_cur_DT = DatetoStr(now, "Y/m/d");

var Tmp_MDDT = DatetoStr(copy_rec["MDDT"], "Y/m/d");

copy_rec["FMNO"]=Tmp_NEXT_FMNO;

3>複製後,停留在最新的一筆資料
gridstore.on('load', function (me, records, successful, eOpts) {
var Tmp_index = gridstore.find("FMNO", Tmp_NEXT_FMNO);
var sel_rec = gridstore.getAt(Tmp_index);
Ext.getCmp('grid_Single').getView().getSelectionModel().select(sel_rec);

//locate 後, reset load function
gridstore.un("load", gridstore.events.load.listeners[gridstore.events.load.listeners.length-1].fn);
}) // end of Ext.getCmp('grid_Single')..on("load")

1>*.js

Ext.getCmp("btn_copy").on('click', function (me, e, eOpts) {

//原始的Grid 選擇資料

//var cur_recs = Ext.getCmp('grid_Single').getSelectionModel().getSelection();

var cur_recs = [];

var cur_rec;

console.log("before copy_rec");

var copy_recs,copy_rec; //?? 如何產生另一 copy_rec 不和 cur_rec 相同

var np = {};

if (CHK_PK_OBJ.PK_LIST.length == 0) {

buttonModel.modelType = 0;

mywarnalert('請先勾選要複製的資料!!');

}

else {

me.delMessage = "確定複製勾選的資料嗎?";

Ext.Msg.confirm('提醒', me.delMessage, callBackFunc);

function callBackFunc(id) {

if (id == 'yes') {

gridstore.on('load', function (me, records, successful, eOpts) {

var Tmp_index = gridstore.find("FMNO", Tmp_NEXT_FMNO);

console.log("Tmp_index:", Tmp_index);

var sel_rec = gridstore.getAt(Tmp_index);

console.log("sel_rec:", sel_rec);

Ext.getCmp('grid_Single').getView().getSelectionModel().select(sel_rec);

console.log("gridstore.events:", gridstore.events);

gridstore.un("load", gridstore.events.load.listeners[gridstore.events.load.listeners.length-1].fn);

}) // end of Ext.getCmp('grid_Single')..on("load")

console.log("cur_recs.length:", cur_recs.length);

//複製新的 Array , 不覆蓋原本的 Array

copy_recs = Array.from(CHK_PK_OBJ.ALL_LIST);

for (let i = 0; i <= CHK_PK_OBJ.PK_LIST.length - 1; i++) {

copy_rec = copy_recs[i];

Tmp_NEXT_FMNO = get_NEXT_FMNO();

var Tmp_cur_DT = DatetoStr(now, "Y/m/d");

//var Tmp_MDDT = DatetoStr(copy_rec.data["MDDT"], "Y/m/d");

var Tmp_MDDT = DatetoStr(copy_rec["MDDT"], "Y/m/d");

console.log("Tmp_NEXT_FMNO:", Tmp_NEXT_FMNO);

copy_rec["FMNO"]=Tmp_NEXT_FMNO;

copy_rec["BTDT"] = Tmp_cur_DT;

copy_rec["MDDT"] = Tmp_cur_DT;

copy_rec["STATFM"] = "A";

copy_rec["DTSOR"] = "A";

copy_rec["OSM"] = "";

copy_rec["PAMMNO"] = "";

copy_rec["FRNOAF"] = "";

//新增資料庫資料

Copy_FMNO(copy_rec);

} // end of for (let i=0,)

//因為複製後, 按[顯示資料]鈕, 並不會顯示刪才新增的資料,& Local 新增的資料

Ext.getCmp('btn_Show').fireHandler();

Ext.getCmp('grid_Single').getView().refresh();

//跳至最後一頁

var Tmp_ptb = Ext.getCmp('grid_ptb');

Tmp_ptb.moveLast();

buttonModel.modelType = 0;

} // end if (id == 'yes')

} // end of callbackfunc

} //end of else {}

}

);

2025年2月25日星期二

V20305 – 零件需求登錄 –[編輯]鈕 - 前端取得 KEYCODE 名稱 -V120202A- 取得人員姓名

目的: V20305 – 零件需求登錄 –[編輯]鈕 - 前端取得 KEYCODE 名稱 - 取得人員姓名

處理說明: 1> 前端取得 KEYCODE 名稱
Tmp_RSTHAH = nulltoStr(cur_rec.data['RSTHAH']).toString();
Tmp_RSTHAH_ = get_KEY_CODE_NM("AMM_UOCD","RSTHAH",Tmp_RSTHAH);

2> 前端取得人員姓名
Tmp_LoginId = loginInfo.id;
Tmp_LoginNM = getEmp_NM(loginInfo.id);

1>*.js

//V20305A.js
Tmp_RSTHAH = nulltoStr(cur_rec.data['RSTHAH']).toString();
Tmp_RSTHAH_ = get_KEY_CODE_NM("AMM_UOCD","RSTHAH",Tmp_RSTHAH);

//V120202A.js

Tmp_LoginId = loginInfo.id;

Tmp_LoginNM = getEmp_NM(loginInfo.id);

2>myfunc.js - get_KEY_CODE_NM - getEmp_NM

* 傳入參數: par_TBL_NAME: Table名稱

* par_KEY_NAME: KEYCODE欄位名稱

* par_KEY_CODE: KEYCODE欄位值

SELECT KEY_CODE, DESCPT

FROM KEYCODE

WHERE TBL_NAME = 'AMM_UOCD'

AND KEY_NAME = 'RSTHAH'

AND KEY_CODE = 'A';

function get_KEY_CODE_NM(par_TBL_NAME,par_KEY_NAME,par_KEY_CODE) {

var obj = '';

var Tmp_KEY_CODE_NM = "";

if ( (par_TBL_NAME.length > 0) && (par_KEY_NAME.length > 0) && (par_KEY_CODE.length > 0) ){

Ext.Ajax.request({

method: "POST",

//url: "../api/V20302AAPI/getEmp_NM?EMPLYID=" + par_EMPLYID,

url: "../api/myAPI/get_KEY_CODE_NM?TBL_NAME=" + par_TBL_NAME+"&KEY_NAME="+par_KEY_NAME+"&KEY_CODE="+par_KEY_CODE,

async: false,

success: function (response, opts) {

obj_Temp = Ext.decode(response.responseText);

//console.log("obj_Temp:", obj_Temp);

if (obj_Temp.T1.length > 0) {

obj = obj_Temp;

Tmp_KEY_CODE_NM = obj.T1[0]["DESCPT"].toString();

return Tmp_KEY_CODE_NM;

}

)

}

return Tmp_KEY_CODE_NM;

};

// function名稱 : getEMP_NM

// 參數 : par_EMPLYID : 員工編號

// 傳回值: 傳入員工.姓名

function getEmp_NM(par_EMPLYID) {

var obj = '';

var Tmp_EMPLYNM = "";

if (par_EMPLYID.length > 0) {

Ext.Ajax.request({

method: "POST",

//url: "../api/V20302AAPI/getEmp_NM?EMPLYID=" + par_EMPLYID,

url: "../api/myAPI/getEmp_NM?EMPLYID=" + par_EMPLYID,

async: false,

success: function (response, opts) {

obj_Temp = Ext.decode(response.responseText);

//console.log("obj_Temp:", obj_Temp);

if (obj_Temp.T1.length > 0) {

obj = obj_Temp;

Tmp_EMPLYNM = obj.T1[0]["EMPLYNM"].toString();

return Tmp_EMPLYNM;

}

)

}

return Tmp_EMPLYNM;

};

3>myAPI.cs - get_KEY_CODE_NM

//取得 TABLE.KEYCODE 的名稱

[HttpPost]

public dynamic get_KEY_CODE_NM(string TBL_NAME,string KEY_NAME, string KEY_CODE)

{

var c = HttpContext.Current;

NameValueCollection nvc = c.Request.Form;

string Tmp_Sql = " SELECT KEY_CODE,DESCPT "

+ " FROM KEYCODE "

+ " WHERE 1=1 "

+" AND TBL_NAME="+myfunc.AA(TBL_NAME)

+" AND KEY_NAME=" + myfunc.AA(KEY_NAME)

+ " AND KEY_CODE=" + myfunc.AA(KEY_CODE);

if (myfunc.checkisnull(TBL_NAME) || myfunc.checkisnull(KEY_NAME) || myfunc.checkisnull(KEY_CODE))

{ return null; }

//else

//{ Tmp_Sql = Tmp_Sql + " AND EMPLYID=" + myfunc.AA(EMPLYID); }

try

{

OracleConnection conn = new OracleConnection(DBService.ConnectionString("AMMEU"));

OracleCommand cmd = new OracleCommand(Tmp_Sql, conn);

OracleDataAdapter da = new OracleDataAdapter(cmd);

DataSet ds = new DataSet();

conn.Open();

da.Fill(ds, "T1");

conn.Close();

return ds;

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}

} // end of GetEmp_NM

訂閱：文章 (Atom)

2025年4月9日星期三

V20305J –挑選xls檔案, 匯入2個Table(AMM_UOC,AMM_UOCD) - 清除cookie - Transaction - rollback - excuteSQLTran - loadmark -timer

2025年4月6日星期日

已核准的 [網路圖報工] 資料.修改報工年度月份

Xls欄位換行串換: char(10) , \n (Escape n)

2025年3月26日星期三

V20305I –[附檔瀏覽] – 下載檔案 - downloadFile2 - 子畫面- 簡單 Grid

2025年3月20日星期四

Web – V20305G –[物料清單匯出]鈕 – 套表匯出 / 不套表匯出 - get_SS_FILES - 取得套表檔案

Web V20301 – 複製 -目前勾選的資料, 非目前所在資料 - 移除原本Template eventhandler - 複製 Array -跳至最後一頁

2025年2月25日星期二

V20305 – 零件需求登錄 –[編輯]鈕 - 前端取得 KEYCODE 名稱 -V120202A- 取得人員姓名

WebDesign 心得綜整

追蹤者

網誌存檔

關於我自己

2025年4月9日 星期三

2025年4月6日 星期日

2025年3月26日 星期三

2025年3月20日 星期四

2025年2月25日 星期二

追蹤者

網誌存檔

關於我自己

2025年4月9日星期三

2025年4月6日星期日

2025年3月26日星期三

2025年3月20日星期四

2025年2月25日星期二