protected virtual void WfIniSqlBody()
{
StringBuilder sbSql;
StringBuilder sbSqlSelect, sbSqlFrom, sbSqlInner, sbSqlWhere;
try
{
if (MsgInfoReturned == null)
{
throw new Exception("未實體化msgInfoReturned");
}
sbSql = new StringBuilder();
sbSql.AppendLine(string.Format(" SELECT * FROM vw_admi650 WHERE azp01='{0}' ", StrPickNo));
var drAzp = BoMaster.OfGetDataRow(sbSql.ToString());
if (drAzp == null)
{
throw new Exception("查無此pick_no!");
}
sbSql = new StringBuilder();
sbSql.AppendLine(string.Format(" SELECT * FROM vw_admi650s WHERE azq01='{0}' ", StrPickNo));
sbSql.AppendLine(string.Format(" ORDER BY azq02 "));
var dtAzq = BoMaster.OfGetDataTable(sbSql.ToString());
if (drAzp == null)
{
throw new Exception("查無此pick_no 明細資料!");
}
Admi650Model = drAzp.ToItem <vw_admi650>();
Admi650sList = dtAzq.ToList <vw_admi650s>();
//設定form名稱
this.Text = string.Format("{0} {1}", Admi650Model.azp01, Admi650Model.azp02);
#region 取得select body
sbSqlSelect = new StringBuilder();
sbSqlSelect.AppendLine(string.Format("SELECT "));
if (Admi650Model.azp06 == "Y")
{
sbSqlSelect.AppendLine(string.Format("DISTINCT"));
}
if (_iMaxPickRow != 1) //表示多選,增加is_pick_yn
{
sbSqlSelect.AppendLine(string.Format("convert(nvarchar(1),'N') is_pick,")); //0,1
}
//組合select 欄位,最後一欄不加,
for (int i = 0; i < Admi650sList.Count; i++)
{
var selectField = string.Format(Admi650sList[i].azq03 + "." + Admi650sList[i].azq04);
if (i != Admi650sList.Count - 1)
{
selectField += ",";
}
sbSqlSelect.AppendLine(selectField);
}
#endregion
#region 取得from body
sbSqlFrom = new StringBuilder();
sbSqlFrom.AppendLine(string.Format("FROM " + Admi650Model.azp03));
#endregion
#region 取得join
sbSqlInner = new StringBuilder();
//if (GlobalFn.isNullRet(drAzp["azp04"], "") != "")
if (GlobalFn.isNullRet(Admi650Model.azp04, "") != "")
{
//sbSqlInner.AppendLine(string.Format(GlobalFn.isNullRet(drAzp["azp04"], "")));
sbSqlInner.AppendLine(string.Format(GlobalFn.isNullRet(Admi650Model.azp04, "")));
}
#endregion
#region 取得where
sbSqlWhere = new StringBuilder();
if (GlobalFn.isNullRet(Admi650Model.azp05, "") != "")
{
sbSqlWhere.AppendLine(string.Format("WHERE " + GlobalFn.isNullRet(Admi650Model.azp05, "")));
}
else
{
sbSqlWhere.AppendLine(string.Format("WHERE 1=1 "));
}
#endregion
//組合好不含order by的字串
StrSqlBody = sbSqlSelect.ToString() + sbSqlFrom.ToString() + sbSqlInner.ToString() + sbSqlWhere.ToString();
if (MsgInfoReturned.StrWhereAppend != "")
{
StrSqlBody += MsgInfoReturned.StrWhereAppend;
}
if (MsgInfoReturned.ParamSearchList == null)
{
DtMaster = BoMaster.OfGetDataTable(StrSqlBody + " AND 1<>1 ");
}
else
{
DtMaster = BoMaster.OfGetDataTable(StrSqlBody + " AND 1<>1 ", MsgInfoReturned.ParamSearchList.ToArray());
}
//修改column 型別為string 並將實際的型別丟到 column.prefix
foreach (DataColumn dcTempColumn in DtMaster.Columns)
{
if (dcTempColumn.Prefix != "")
{
continue;
}
dcTempColumn.Prefix = dcTempColumn.DataType.Name;
dcTempColumn.DataType = typeof(string);
}
BindingMaster.DataSource = DtMaster;
uGrid_Master.DataSource = BindingMaster;
}
catch (Exception ex)
{
throw ex;
}
}
private void WfIniSqlBody()
{
StringBuilder sbSql;
StringBuilder sbSqlSelect, sbSqlFrom, sbSqlInner, sbSqlWhere;
//string masterTable;
//string distinctYn;
try
{
if (MsgInfoReturned == null)
{
throw new Exception("未實體化msgInfoReturned");
}
sbSql = new StringBuilder();
sbSql.AppendLine(string.Format(" SELECT * FROM azp_tb WHERE azp01='{0}' ", _strPickNo));
_drAzp = _boMaster.OfGetDataRow(sbSql.ToString());
if (_drAzp == null)
{
throw new Exception("查無此pick_no!");
}
sbSql = new StringBuilder();
sbSql.AppendLine(string.Format(" SELECT * FROM azq_tb WHERE azq01='{0}' ", _strPickNo));
sbSql.AppendLine(string.Format(" ORDER BY azq02 "));
_dtAzq = _boMaster.OfGetDataTable(sbSql.ToString());
if (_drAzp == null)
{
throw new Exception("查無此pick_no 明細資料!");
}
_azpModel = _drAzp.ToItem <azp_tb>();
_azqTbList = _dtAzq.ToList <azq_tb>();
//distinctYn = GlobalFn.isNullRet(_drAzp["azp06"], "N");
//masterTable = GlobalFn.isNullRet(_drAzp["azp03"], "");
//this.Text = GlobalFn.isNullRet(_drAzp["azp01"], "") + " " + GlobalFn.isNullRet(_drAzp["azp02"], "");
//設定form名稱
this.Text = string.Format("{0} {1}", _azpModel.azp01, _azpModel.azp02);
#region 取得select body
sbSqlSelect = new StringBuilder();
sbSqlSelect.AppendLine(string.Format("SELECT "));
if (_azpModel.azp06 == "Y")
{
sbSqlSelect.AppendLine(string.Format("DISTINCT"));
}
if (iMaxPickRow != 1) //表示多選,增加is_pick_yn
{
sbSqlSelect.AppendLine(string.Format("convert(nvarchar(1),'N') is_pick,")); //0,1
}
//組合select 欄位,最後一欄不加,
for (int i = 0; i < _azqTbList.Count; i++)
{
var selectField = string.Format(_azqTbList[i].azq03 + "." + _azqTbList[i].azq04);
if (i != _azqTbList.Count - 1)
{
selectField += ",";
}
sbSqlSelect.AppendLine(selectField);
}
#endregion
#region 取得from body
sbSqlFrom = new StringBuilder();
sbSqlFrom.AppendLine(string.Format("FROM " + _azpModel.azp03));
#endregion
#region 取得join
sbSqlInner = new StringBuilder();
if (GlobalFn.isNullRet(_drAzp["azp04"], "") != "")
{
sbSqlInner.AppendLine(string.Format(GlobalFn.isNullRet(_drAzp["azp04"], "")));
}
#endregion
#region 取得where
sbSqlWhere = new StringBuilder();
if (GlobalFn.isNullRet(_drAzp["azp05"], "") != "")
{
sbSqlWhere.AppendLine(string.Format("WHERE " + GlobalFn.isNullRet(_drAzp["azp05"], "")));
}
else
{
sbSqlWhere.AppendLine(string.Format("WHERE 1=1 "));
}
#endregion
//組合好不含order by的字串
_strSqlBody = sbSqlSelect.ToString() + sbSqlFrom.ToString() + sbSqlInner.ToString() + sbSqlWhere.ToString();
if (MsgInfoReturned.StrWhereAppend != "")
{
_strSqlBody += MsgInfoReturned.StrWhereAppend;
}
if (MsgInfoReturned.ParamSearchList == null)
{
_dtMaster = _boMaster.OfGetDataTable(_strSqlBody + " AND 1<>1 ");
}
else
{
_dtMaster = _boMaster.OfGetDataTable(_strSqlBody + " AND 1<>1 ", MsgInfoReturned.ParamSearchList.ToArray());
}
//修改column 型別為string 並將實際的型別丟到 column.prefix
foreach (DataColumn dcTempColumn in _dtMaster.Columns)
{
if (dcTempColumn.Prefix != "")
{
continue;
}
dcTempColumn.Prefix = dcTempColumn.DataType.Name;
dcTempColumn.DataType = typeof(string);
}
BindingMaster.DataSource = _dtMaster;
uGrid_Master.DataSource = BindingMaster;
}
catch (Exception ex)
{
throw ex;
}
}