////////////////////////////////////////////////////////////////////////////
// インターフェイス名 ： CreateSqlCommand
/// <summary>
///
/// </summary>
/// <param name="searchCond"></param>
/// <history>
/// 日付 担当者 内容
/// 2014/08/08 AnhNV 新規作成
/// </history>
////////////////////////////////////////////////////////////////////////////
private SqlCommand CreateSqlCommand(TuckSealSearchCond searchCond)
{
SqlCommand command = new SqlCommand();
SqlParameterCollection commandParams = command.Parameters;
StringBuilder sqlContent = new StringBuilder();
// 業者マスタ search
if (searchCond.RdSelect == "1")
{
sqlContent.Append(" select ");
sqlContent.Append(" gm.GyoshaCd as CdCol, ");
sqlContent.Append(" '' as NengetsuCol, ");
sqlContent.Append(" '' as RenbanCol, ");
sqlContent.Append(" gm.GyoshaNm as NmCol, ");
sqlContent.Append(" gm.GyoshaZipCd as ZipNoCol, ");
sqlContent.Append(" gm.GyoshaAdr as AdrCol ");
sqlContent.Append(" from GyoshaMst gm ");
sqlContent.Append(" where ");
// 業者コードFROM ~ TO
sqlContent.Append(" gm.GyoshaCd " + DataAccessUtility.SetBetweenCommand(searchCond.CdFrom, searchCond.CdTo, 4));
if (!string.IsNullOrEmpty(searchCond.SearchNm))
{
// 業者名称
sqlContent.Append(" and gm.GyoshaNm like concat('%', @GyoshaNm, '%')");
commandParams.Add("@GyoshaNm", SqlDbType.VarChar).Value = (string)DataAccessUtility.EscapeSQLString(searchCond.SearchNm);
}
// ORDER BY
sqlContent.Append(" order by gm.GyoshaCd");
}
else if (searchCond.RdSelect == "2") // 保健所マスタ search
{
sqlContent.Append(" select ");
sqlContent.Append(" hm.HokenjoCd as CdCol, ");
sqlContent.Append(" '' as NengetsuCol, ");
sqlContent.Append(" '' as RenbanCol, ");
sqlContent.Append(" hm.HokenjoNm as NmCol, ");
sqlContent.Append(" hm.HokenjoZipCd as ZipNoCol, ");
sqlContent.Append(" hm.HokenjoAdr as AdrCol ");
sqlContent.Append(" from HokenjoMst hm ");
sqlContent.Append(" where 1=1 ");
// 保健所コードFROM ~ TO
if (!string.IsNullOrEmpty(searchCond.HokenjoCd))
{
sqlContent.Append(" and hm.HokenjoCd = @HokenjoCd ");
commandParams.Add("@HokenjoCd", SqlDbType.Char).Value = (string)searchCond.HokenjoCd;
}
else
{
sqlContent.Append(" and hm.HokenjoCd " + DataAccessUtility.SetBetweenCommand(searchCond.CdFrom, searchCond.CdTo, 2));
}
if (!string.IsNullOrEmpty(searchCond.SearchNm))
{
// 設置者氏名
sqlContent.Append(" and hm.HokenjoNm like concat('%', @HokenjoNm, '%')");
commandParams.Add("@HokenjoNm", SqlDbType.VarChar).Value = (string)DataAccessUtility.EscapeSQLString(searchCond.SearchNm);
}
// ORDER BY
sqlContent.Append(" order by hm.HokenjoCd");
}
else // 浄化槽台帳マスタ search
{
sqlContent.Append(" select ");
sqlContent.Append(" jdm.JokasoHokenjoCd as CdCol, ");
sqlContent.Append(" jdm.JokasoTorokuNendo as NengetsuCol, ");
sqlContent.Append(" jdm.JokasoRenban as RenbanCol, ");
sqlContent.Append(" jdm.JokasoSetchishaNm as NmCol, ");
sqlContent.Append(" jdm.JokasoSetchishaZipCd as ZipNoCol, ");
sqlContent.Append(" jdm.JokasoSetchishaAdr as AdrCol ");
sqlContent.Append(" from JokasoDaichoMst jdm ");
sqlContent.Append(" where ");
// 浄化槽台帳連番
sqlContent.Append(" jdm.JokasoRenban " + DataAccessUtility.SetBetweenCommand(searchCond.CdFrom, searchCond.CdTo, 5));
if (!string.IsNullOrEmpty(searchCond.HokenjoCd))
{
// 浄化槽台帳保健所コード
sqlContent.Append(" and jdm.JokasoHokenjoCd = @JokasoHokenjoCd");
commandParams.Add("@JokasoHokenjoCd", SqlDbType.VarChar).Value = (string)searchCond.HokenjoCd;
}
if (!string.IsNullOrEmpty(searchCond.Nengetsu))
{
// 浄化槽台帳年度
sqlContent.Append(" and jdm.JokasoTorokuNendo = @JokasoTorokuNengetsu");
commandParams.Add("@JokasoTorokuNengetsu", SqlDbType.VarChar).Value = (string)searchCond.Nengetsu;
}
if (!string.IsNullOrEmpty(searchCond.SearchNm))
{
// 設置者氏名
sqlContent.Append(" and jdm.JokasoSetchishaNm like concat('%', @JokasoSetchishaNm, '%')");
commandParams.Add("@JokasoSetchishaNm", SqlDbType.VarChar).Value = (string)DataAccessUtility.EscapeSQLString(searchCond.SearchNm);
}
// ORDER BY
sqlContent.Append(" order by jdm.JokasoHokenjoCd, jdm.JokasoTorokuNendo, jdm.JokasoRenban");
}
command.CommandText = sqlContent.ToString();
return command;
}
internal JokasoDaichoMstDataSet.TuckSealListDataTable GetDataBySearchCond(TuckSealSearchCond searchCond)
{
SqlCommand command = CreateSqlCommand(searchCond);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
JokasoDaichoMstDataSet.TuckSealListDataTable dataTable = new JokasoDaichoMstDataSet.TuckSealListDataTable();
adpt.Fill(dataTable);
return dataTable;
}
