/// <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
}