目的: V40A – [過濾資料]鈕 – 顯示在同一頁 – 取得 SQL全部資料後, 過濾資料, 再顯示在同一頁
處理說明: 1> getGridData_M 取全部資料 , 不分頁(par_paging=false)
2> 取得 CaluField - SAP單況(I0001/I0002/..)
2> 取得 CaluField - SAP單況(I0001/I0002/..)
3> 過濾 SAP單況 , 符合條件的保留, 不符合的 dr.delete()
4> 傳回符合條件的 ds , 必需修改 ["T1C"] 欄位值(總筆數)
ds.Tables["T1C"].Rows[0]["TOTAL"] = ds.Tables["T1"].Rows.Count;
5> 不分頁, 全部顯示在同一頁,設定每頁筆數=9999(store.pageSize)
Ext.getCmp('grid_Single').store.pageSize = 9999;
6>[過濾SAP單況]鈕, 必需將過濾欄位值記錄為[過濾條件欄位(s_Filter_Str)]-隱藏欄位,
以便顯示時,依該過濾條件過濾
--> [資料顯示]時,再清除該欄位值(s_Filter_Str)
1>*.js
//[過濾 SAP單況]鈕
{
xtype: 'button', text: '確定', id: 'OkBtn_V40AB',
listeners: {
click: function (me, e, eOpts) {
//1>更新資料庫資料(AMM_COMPQTY)
var Tmp_Str = "";
if (!(Ext.getCmp("s_SAPSTAT1").getValue()) && !(Ext.getCmp("s_SAPSTAT2").getValue()) && !(Ext.getCmp("s_SAPSTAT3").getValue())) {
Tmp_Str = "SAP單況不可均不勾選<br>"
+ " 敬請檢核 !!";
mywarnalert(Tmp_Str);
return;
}
var Tmp_Filter_Str = "";
if (Ext.getCmp("s_SAPSTAT1").getValue()) {
Tmp_Filter_Str = Tmp_Filter_Str + "I0001" + ",";
}
if (Ext.getCmp("s_SAPSTAT2").getValue()) {
Tmp_Filter_Str = Tmp_Filter_Str + "E0014" + "," + "E0015" + ",";
}
if (Ext.getCmp("s_SAPSTAT3").getValue()) {
Tmp_Filter_Str = Tmp_Filter_Str + "I0002" + ",";
}
if (checkisnull(Tmp_Filter_Str)) {
Tmp_Filter_Str = Tmp_Filter_Str.substr(0, Tmp_Filter_Str.length - 1);
}
// 重新顯示 - 全部顯示在同一頁
var np = s_JSON('s_form'); //TMFunction.js, 組合form上的查詢條件為json參數傳遞
np["s_isSAPSTAT"] = Ext.getCmp('s_isSAPSTAT').getValue();
np["s_TYPEAF"] = Ext.getCmp('s_TYPEAF').getValue();
np["s_Filter_Str"] = Tmp_Filter_Str;
//console.log('1 gridstore: ', gridstore);
var gridstore = Ext.getCmp('grid_Single').store;
gridstore.getProxy().url = '../api/V40AAPI/getGridData_M?par_paging=false';
gridstore.getProxy().extraParams = np; //分頁OK,篩選條件OK
Ext.getCmp('grid_Single').store.pageSize = 9999;
Ext.getCmp('grid_Single').reloadGridData();
this.up("window").close();
this.up("window").destroy();
}
},
},
//[顯示資料]鈕, 一般正常分頁顯示
{
xtype: 'button',
id: 'btn_Show',
flex: 2,
border: 1,
text: '資料顯示',
iconCls: 'icon-search',
handler: function () {
// 更新 STP欄位值
var np = s_JSON('s_form'); //TMFunction.js, 組合form上的查詢條件為json參數傳遞
np["s_isSAPSTAT"] = Ext.getCmp('s_isSAPSTAT').getValue();
np["s_TYPEAF"] = Ext.getCmp('s_TYPEAF').getValue();
np["s_Filter_Str"] = ""; //[過濾SAP單況]鈕,才會有值過濾SAP單況條件空白
//console.log('1 gridstore: ', gridstore);
//var gridstore = Ext.getCmp('grid_Single').store;
gridstore.getProxy().url = '../api/V40AAPI/getGridData_M';
gridstore.getProxy().extraParams = np; //分頁OK,篩選條件OK
Ext.getCmp('grid_Single').store.pageSize = 30;
Ext.getCmp('grid_Single').reloadGridData();
}
}, // end of 顯示資料
2>V40A*.cs
[HttpPost]
public dynamic getGridData_M(bool par_paging = true)
{
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
//panel1
string Tmp_AMMNO = nvc["s_AMMNO"];
string Tmp_SAPNO = nvc["s_SAPNO"];
string Tmp_WBSNO = nvc["s_WBSNO"];
string Tmp_PN = nvc["s_PN"];
//panel2
string Tmp_OGONOAF = nvc["s_OGONOAF"]; //拆檢提領編號
string Tmp_NGONOAF = nvc["s_NGONOAF"]; //維修提領編號
string Tmp_MRPNOAF = nvc["s_MRPNOAF"]; //拆檢交修編號
string Tmp_RPNOAF = nvc["s_RPNOAF"]; //維修交修編號
//panel3
string Tmp_SAPMAN = nvc["s_SAPMAN"];
string Tmp_FIXSN = nvc["s_FIXSN"];
string Tmp_CREMPLYID = nvc["s_CREMPLYID"];
string Tmp_FACWC = nvc["s_FACWC"];
//panel4
string Tmp_isSAPSTAT = nvc["s_isSAPSTAT"]; //料號
string Tmp_TYPEAF = nvc["s_TYPEAF"]; //'3'/'4' : 拆檢約/維條約
string Tmp_Filter_Str = nvc["s_Filter_Str"]; //過濾 SAP單況
string Tmp_SAPDT1 = ""; //發工日期 - 起迄
string Tmp_SAPDT2 = ""; //發工日期 - 起迄
if (!myfunc.checkisnull(nvc["s_SAPDT1"]))
{
Tmp_SAPDT1 = string.Format("{0:yyyy/MM/dd}", DateTime.Parse(nvc["s_SAPDT1"]));
};
if (!myfunc.checkisnull(nvc["s_SAPDT2"]))
{
Tmp_SAPDT2 = string.Format("{0:yyyy/MM/dd}", DateTime.Parse(nvc["s_SAPDT2"]));
};
OracleCommand cmd = new OracleCommand();
string Tmp_Sql = " SELECT RNK, "
+ " AMMNO,SAPNO,PN,FIXSN,PNNAME,SAPMAN,SAPDT,WBS,QTY,UNIT, "
+ " FMNO,OGONOAF,NGONOAF,MRPNOAF,RPNOAF,TYPEAF,FACWC,"
+ " CREMPLYID,NEMPID,MEMPID "
+ " FROM ( "
+ " SELECT dense_rank() over(order by A.AMMNO,B.FMNO) as RNK,"
+ " A.AMMNO,A.SAPNO,A.PN,A.FIXSN,A.PNNAME,A.SAPMAN,A.SAPDT,A.WBS, A.QTY,A.UNIT, "
+ " B.FMNO,B.OGONOAF,B.NGONOAF,B.MRPNOAF,B.RPNOAF,B.TYPEAF,B.FACWC, "
+ " B.CREMPLYID,B.NEMPID,B.MEMPID "
+ " FROM AMM_SRO A, AMM_FM B "
+ " WHERE A.AMMNO=B.AMMNO "
+ " AND A.AMMSTS NOT IN ('A','B','F','G','H','I','W','X','Y','Z') "
+ " AND A.SAPNO IS NOT NULL "
+ " AND A.ISSYS='Y' ";
//AMM單號
if (!myfunc.checkisnull(Tmp_AMMNO))
{
Tmp_Sql = Tmp_Sql + " AND A.AMMNO = " + myfunc.AA(Tmp_AMMNO);
};
//工號
if (!myfunc.checkisnull(Tmp_SAPNO))
{
Tmp_Sql = Tmp_Sql + " AND A.SAPNO=" + myfunc.AA(Tmp_SAPNO);
};
//WBS
if (!myfunc.checkisnull(Tmp_WBSNO))
{
Tmp_Sql = Tmp_Sql + " AND A.WBS LIKE " + myfunc.AA("%"+Tmp_WBSNO+"%");
};
//件號
if (!myfunc.checkisnull(Tmp_PN))
{
Tmp_Sql = Tmp_Sql + " AND A.PN LIKE " + myfunc.AA("%" + Tmp_PN+"%");
};
//拆檢提領編號
if (!myfunc.checkisnull(Tmp_OGONOAF))
{
Tmp_Sql = Tmp_Sql + " AND B.OGONOAF LIKE " + myfunc.AA('%' + Tmp_OGONOAF + '%');
};
//維修提領編號
if (!myfunc.checkisnull(Tmp_NGONOAF))
{
Tmp_Sql = Tmp_Sql + " AND B.NGONOAF LIKE " + myfunc.AA('%' + Tmp_NGONOAF + '%');
};
//拆檢交修單號
if (!myfunc.checkisnull(Tmp_RPNOAF))
{
Tmp_Sql = Tmp_Sql + " AND B.RPNOAF LIKE " + myfunc.AA('%' + Tmp_RPNOAF + '%');
};
//維修交修單號
if (!myfunc.checkisnull(Tmp_MRPNOAF))
{
Tmp_Sql = Tmp_Sql + " AND B.MRPNOAF LIKE " + myfunc.AA('%' + Tmp_MRPNOAF + '%');
};
//發工人員
if (!myfunc.checkisnull(Tmp_SAPMAN))
{
Tmp_Sql = Tmp_Sql + " AND A.SAPMAN=" + myfunc.AA(Tmp_SAPMAN);
};
//維修件序號
if (!myfunc.checkisnull(Tmp_FIXSN))
{
Tmp_Sql = Tmp_Sql + " AND A.FIXSN LIKE " + myfunc.AA("%"+Tmp_FIXSN+ "%");
};
//管制人員
if (!myfunc.checkisnull(Tmp_CREMPLYID))
{
Tmp_Sql = Tmp_Sql + " AND B.CREMPLYID=" + myfunc.AA(Tmp_CREMPLYID);
};
//承修工場
if (!myfunc.checkisnull(Tmp_FACWC))
{
Tmp_Sql = Tmp_Sql + " AND B.FACWC= " + myfunc.AA(Tmp_FACWC);
};
//約別
if (!myfunc.checkisnull(Tmp_TYPEAF))
{
Tmp_Sql = Tmp_Sql + " AND B.TYPEAF IN (" + myfunc.AddQuoteStr(Tmp_TYPEAF)+")";
};
string Tmp_Sql1 = "";
//發工日期 - 起 - 迄
if ((!myfunc.checkisnull(Tmp_SAPDT1)) || (!myfunc.checkisnull(Tmp_SAPDT2)))
{
if (!myfunc.checkisnull(Tmp_SAPDT1))
{
Tmp_Sql1 = Tmp_Sql1 + " AND SAPDT>=" + myfunc.AA(Tmp_SAPDT1);
};
if (!myfunc.checkisnull(Tmp_SAPDT2))
{
Tmp_Sql1 = Tmp_Sql1 + " AND SAPDT<=" + myfunc.AA(Tmp_SAPDT2);
}
Tmp_Sql = Tmp_Sql + Tmp_Sql1;
};
Tmp_Sql = Tmp_Sql + " ) ";
cmd.CommandText = Tmp_Sql;
string countSql = " SELECT COUNT(*) as total FROM (" + Tmp_Sql + ")";
//匯出時 ,取全部資料, par_paging =false
DataSet ds = getDataTable(cmd, countSql, par_paging);
//////新增 CaluField 欄位
//- [約別(TYPEAF) ]
Tmp_Sql = " select KEY_CODE as TYPEAF, DESCPT "
+ " from KEYCODE "
+ " where TBL_NAME = 'AMM_FM' "
+ " and KEY_NAME = 'TYPEAF' ";
getKeyCode("TYPEAF", ref ds, Tmp_Sql);
////新增 CaluField [SAP工單單況_(AMM_SRO.SAPSTAT_)]
int Tmp_Cnt = ds.Tables["T1"].Rows.Count;
ds.Tables["T1"].Columns.Add(new DataColumn("SAPMAN_", typeof(string)));
ds.Tables["T1"].Columns.Add(new DataColumn("CREMPLYID_", typeof(string)));
ds.Tables["T1"].Columns.Add(new DataColumn("SAPSTAT_", typeof(string)));
ds.Tables["T1"].Columns.Add(new DataColumn("SAPSTAT__", typeof(string)));
string Tmp_SAPMAN_ = "";
string Tmp_CREMPLYID_ = "";
string Tmp_SAPNO1 = "";
string Tmp_SAPNO_ = "";
string Tmp_SAPSTAT_ = "", Tmp_SAPSTAT__ = "";
string Tmp_TYPEAF_ = "";
string Tmp_Str;
for (int i = 0; i < Tmp_Cnt; i++)
{
DataRow dr = ds.Tables["T1"].Rows[i];
//取得 發工人員/管制人員(SAPMAN/CREMPLYID).姓名
Tmp_SAPMAN_ = myfunc.getEmp_NM(dr["SAPMAN"].ToString());
Tmp_CREMPLYID_ = myfunc.getEmp_NM(dr["CREMPLYID"].ToString());
dr["SAPMAN_"] = Tmp_SAPMAN_;
dr["CREMPLYID_"] = Tmp_CREMPLYID_;
//取得 SAP工單狀態(SAPSTAT)
Tmp_SAPNO1 = dr["SAPNO"].ToString();
//轉換成 SAP系統的工號
Tmp_SAPNO_ = myfunc.get_SAPNO(Tmp_SAPNO1, "OR");
//取得 SAP工號的單況
if (Tmp_isSAPSTAT!="true") { //若!="true" , 則不顯示 [SAP工單狀態]
dr["SAPSTAT_"] = "";
continue;
}
Tmp_SAPSTAT_ = ""; Tmp_SAPSTAT__ = "";
if (!myfunc.checkisnull(Tmp_SAPNO_))
{
//Tmp_Sql = " SELECT OBJNR,STAT,INACT,CHGNR,UDATE,UTIME "
Tmp_Sql = " SELECT STAT "
+ " FROM SAPR3.JCDS@SAP "
+ " WHERE 1 = 1 "
+ " AND MANDT = '999' "
+ " AND OBJNR = " + myfunc.AA(Tmp_SAPNO_)
+ " AND INACT = ' ' "
+ " AND STAT IN('I0001','I0002', 'E0015', 'I0076', 'E0022', 'E0012', 'I0045', 'I0013', 'I0012', 'I0046') "
+ " AND (STAT, CHGNR) NOT IN "
+ " ( SELECT STAT, LPAD((to_number(CHGNR)-1),3,0) as CHGNR "
+ " FROM SAPR3.JCDS@SAP "
+ " WHERE 1 = 1 "
+ " AND MANDT = '999' "
+ " AND OBJNR = " + myfunc.AA(Tmp_SAPNO_)
+ " AND INACT = 'X' "
+ " AND STAT IN('I0001','I0002', 'E0015', 'I0076', 'E0022', 'E0012', 'I0045', 'I0013', 'I0012', 'I0046') "
+ " ) "
+ " ORDER BY UDATE DESC, UTIME DESC ";
Tmp_Str = myfunc.SqlValue(Tmp_Sql);
if (Tmp_Str.Length > 0)
{
Tmp_SAPSTAT_ = myfunc.StrExtract(Tmp_Str, 1, ";");
Tmp_SAPSTAT__ = get_SAPSTAT_NM(Tmp_SAPSTAT_);
}
} // if (!myfunc.checkisnull(Tmp_SAPNO_))
dr["SAPSTAT_"] = Tmp_SAPSTAT_; //SAP單況碼 : I0001/I0002/E0014/E0015
dr["SAPSTAT__"] = Tmp_SAPSTAT__; //SAP單況碼說明 : CREATE/RELEASE/ROFF //[過濾 SAP單況]鈕 , 若欲過濾 SAP單況 --> 即符合的單況才保留,其他刪除
} // end of for (int i = 0; i < Tmp_Cnt; i++)
//[過濾SAP單況]鈕, 處理
if (Tmp_Filter_Str.Length > 0)
{
for (int i = 0; i < Tmp_Cnt; i++)
{
DataRow dr = ds.Tables["T1"].Rows[i];
Tmp_SAPSTAT_ = dr["SAPSTAT_"].ToString();
//若i> 目前的資料數量,則離開
if (i >= ds.Tables["T1"].Rows.Count)
{
break;
}
if (myfunc.checkisnull(Tmp_SAPSTAT_))
{
dr.Delete();
continue;
}
if (Tmp_Filter_Str.IndexOf(Tmp_SAPSTAT_) == -1)
{
dr.Delete();
}
} // end of for (int i=0;
//重新設定總筆數
ds.Tables["T1"].AcceptChanges();
ds.Tables["T1C"].Rows[0]["TOTAL"] = ds.Tables["T1"].Rows.Count;
}// end of if (Tmp_Filter_Str.Length > 0)
//var Tmp_Filter = "SAPSTAT__='RELEASE'";
//ds.Tables["T1"].Select(Tmp_Filter);
return ds;
}
