傑佛瑞Blog TIPTOP ERP+ EasyFlow (BPM) + BI 系統整合開發經驗分享

 ●每月結案工單超領料統計表●

 public function StringToSQL(switchMode:String):String {

   var para1:String = (f_itemno.text =="")? "%" : searchAndReplace(f_itemno.text,"*","%"); 

   var para2:String = (f_sno.text =="")? "%" : searchAndReplace(f_sno.text,"*","%"); 

   /* BEGIN: 組合SQL命令  */ 

   var sql_str:String ="";    //要操作遠端物件去執行的 SQL命令字串  

sql_str += " select X0,X1,X2,X3,X3A,X4,decode(substr(X5,1,1),'.','0'||X5,X5) X5,decode(substr(X6,1,1),'.','0'||X6,X6) X6,decode(substr(X7,1,1),'.','0'||X7,X7) X7,decode(substr(round(X8,4),1,1),'.','0'||round(X8,4),round(X8,4)) X8,decode(substr(X9,1,1),'.','0'||X9,X9) X9,decode(substr(X10,1,1),'.','0'||X10,X10) X10,decode(substr(NVL(ccc23,0),1,1),'.','0'||ccc23,NVL(ccc23,0)) X11,decode(substr( (nvl(X7,0)*nvl(ccc23,0)),1,1),'.','0'||(nvl(X7,0)*nvl(ccc23,0)),(nvl(X7,0)*nvl(ccc23,0))) X12 from ( ";

sql_str += "select '"+plant.selectedItem.data+"' X0,sfa03 X1,ima02 X2,ima021 X3,NVL(imaud01,' ') X3A,ima07 X4,sum(sfa05)  X5,sum( (sfa06+sfa062-sfa07) ) X6,sum( ( (sfa06+sfa062-sfa07)-sfa05) ) X7 ";

sql_str += ", decode(sum(sfa05),0,0,( sum( ( (sfa06+sfa062-sfa07)-sfa05 ) )*100 / sum(sfa05)  ) ) X8,SUM(sfa063) X9,SUM((sfa06+sfa062-sfa07)-sfa05-sfa063) X10 ";

sql_str += "from "+plant.selectedItem.data+".sfa_file,"+plant.selectedItem.data+".sfb_file,"+plant.selectedItem.data+".ima_file ";

sql_str += "where sfa01 = sfb01 and sfa03 = ima01 ";

sql_str += "and sfb04='8' ";

sql_str += "and sfa03 like '"+para1+"' ";

sql_str += "and sfb38 between to_date('"+f_sdate.text+"','yyyy/mm/dd') and to_date('"+f_edate.text+"','yyyy/mm/dd') ";

sql_str += "and ((sfa06+sfa062-sfa07)-sfa05) > 0 ";

sql_str += "and sfa01 like '"+para2+"' ";

sql_str += "and ima07 like '"+f_abc.selectedItem.data+"' ";

sql_str += "group by sfa03,ima02,ima021,imaud01,ima07 ";

sql_str += ") t1, ";

sql_str += "( select ccc01,ccc23 from "+plant.selectedItem.data+".ccc_file where ccc02 ="+year.selectedItem.data+" and ccc03 ="+month.selectedItem.data+" ) t2 "; 

sql_str += "where t1.X1 = t2.ccc01(+)  "; 

sql_str += "order by X1,X3,X6 ";

  /* END: 組合SQL命令   要操作遠端物件去執行的 SQL命令字串  */ 

  return sql_str;

}