/// <summary> /// 組動態SQL進行查詢(Net) /// </summary> /// <param name="ParameterList"></param> /// <returns></returns> public DataTable PRINT_TRANS_CAR(ArrayList ParameterList) { #region try { VDS_TRN23_DBO DBO = new VDS_TRN23_DBO(ref USEDB); string strDynamicSQL1_1 = "", strDynamicSQL1_2 = "", strDynamicSQL2 = "", strDynamicSQL3 = ""; DataTable dtTemp = DBO.doQuery_Transfer_Post(); foreach (DataRow dr in dtTemp.Rows) { #region 【DynamicSQL1】動態SQL:轉運站、小計 /* -------------------------------------------------------------------dynamic sql 1 X.轉運站1, X.轉運站2, X.轉運站3, X.轉運站4, X.轉運站5, nvl(X.轉運站5,0)+nvl(X.轉運站4,0) +nvl(X.轉運站3,0)+nvl(X.轉運站2,0)+nvl(X.轉運站1,0) as 小計, ------------------------------------------------------------------- */ strDynamicSQL1_1 += string.Format( " X.{0}, ", dr["TRANS_MEMO"].ToString().Trim()); strDynamicSQL1_2 += string.Format( " nvl(X.{0},0)+ ", dr["TRANS_MEMO"].ToString().Trim()); #endregion #region 【DynamicSQL3】動態SQL:轉運站直轉橫 /* -------------------------------------------------------------------dynamic sql 3 ,(select nvl(count(d.trans_step_no),0) from vds_trn_trans_car_detl d where m.id=d.pid(+) and d.trans_step_no='TN1') as 轉運站1 ,(select nvl(count(d.trans_step_no),0) from vds_trn_trans_car_detl d where m.id=d.pid(+) and d.trans_step_no='TN2') as 轉運站2 ,(select nvl(count(d.trans_step_no),0) from vds_trn_trans_car_detl d where m.id=d.pid(+) and d.trans_step_no='TN3') as 轉運站3 ,(select nvl(count(d.trans_step_no),0) from vds_trn_trans_car_detl d where m.id=d.pid(+) and d.trans_step_no='TN4') as 轉運站4 ,(select nvl(count(d.trans_step_no),0) from vds_trn_trans_car_detl d where m.id=d.pid(+) and d.trans_step_no='TN5') as 轉運站5 ------------------------------------------------------------------- */ strDynamicSQL3 += string.Format( ",(select nvl(count(d.trans_step_no),0) from vds_trn_trans_car_detl d where m.id=d.pid(+) and d.trans_step_no=q'({0})') as {1} ", dr["TRANS_NO"].ToString().Trim(), dr["TRANS_MEMO"].ToString().Trim()); #endregion } for (Int32 i = dtTemp.Rows.Count - 1; i >= 0; i--) { #region 【DynamicSQL2】動態SQL:計價 /* -------------------------------------------------------------------dynamic sql 2 case when nvl(X.轉運站5,0) = 1 then '轉運站5' when nvl(X.轉運站4,0) = 1 then '轉運站4' when nvl(X.轉運站3,0) = 1 then '轉運站3' when nvl(X.轉運站2,0) = 1 then '轉運站2' when nvl(X.轉運站1,0) = 1 then '轉運站1' end as 計價, ------------------------------------------------------------------- */ strDynamicSQL2 += string.Format( " when nvl(X.{0},0) = 1 then q'({0})' ", dtTemp.Rows[i]["TRANS_MEMO"].ToString().Trim()); #endregion } if (strDynamicSQL1_2.Trim() != "") { strDynamicSQL1_2 = strDynamicSQL1_2.Substring(0, strDynamicSQL1_2.Length - 2); strDynamicSQL1_2 = strDynamicSQL1_2 + " as 小計, "; } if (strDynamicSQL2.Trim() != "") { strDynamicSQL2 = " case " + strDynamicSQL2 + " end as 計價, "; } ParameterList.Add(strDynamicSQL1_1 + strDynamicSQL1_2); ParameterList.Add(strDynamicSQL2); ParameterList.Add(strDynamicSQL3); return DBO.doPRINT_TRANS_CAR(ParameterList); } catch (Exception ex) { throw ex; } #endregion }
/// <summary> /// 組動態SQL進行查詢-2(Oracle) /// </summary> /// <param name="ParameterList"></param> /// <returns></returns> public DataTable PRINT_TRANS_CAR2(ArrayList ParameterList) { #region try { VDS_TRN23_DBO DBO = new VDS_TRN23_DBO(ref USEDB); return DBO.doPRINT_TRANS_CAR2(ParameterList); } catch (Exception ex) { throw ex; } #endregion }