public TList_KitExtrator Select(Utils.TpBusca[] vBusca, int vTop, string vNM_Campo)
{
TList_KitExtrator lista = new TList_KitExtrator();
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = ExecutarBusca(SqlCodeBusca(vBusca, Convert.ToInt16(vTop), vNM_Campo));
try
{
while (reader.Read())
{
TRegistro_KitExtrator reg = new TRegistro_KitExtrator();
if (!reader.IsDBNull(reader.GetOrdinal("id_kit")))
{
reg.Id_kit = reader.GetInt32(reader.GetOrdinal("id_kit"));
}
if (!reader.IsDBNull(reader.GetOrdinal("nr_kit")))
{
reg.Nr_kit = reader.GetString(reader.GetOrdinal("nr_kit"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
deletarBanco_Dados();
}
}
return(lista);
}
public TList_KitExtrator Select(string Cd_empresa,
DateTime Dt_reserva,
DateTime Dt_prevretorno)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("declare @startDate datetime; ")
.AppendLine("declare @endDate datetime; ")
.AppendLine("set @startDate = '" + Dt_reserva.ToString("yyyyMMdd") + "';")
.AppendLine("set @endDate = '" + Dt_prevretorno.ToString("yyyyMMdd") + "';")
.AppendLine("with dateRange as ")
.AppendLine("(")
.AppendLine("select dt = @startDate ")
.AppendLine("where @startDate <= @endDate")
.AppendLine("union all")
.AppendLine("select dateadd(dd, 1, dt)")
.AppendLine("from dateRange")
.AppendLine("where dateadd(dd, 1, dt) <= @endDate")
.AppendLine(")")
.AppendLine("select count(1) as Qt_kit, ")
.AppendLine("Qt_reservada = isnull((select count(1) from TB_RES_ReservaChopp x ")
.AppendLine(" inner join VTB_RES_ItensReserva y ")
.AppendLine(" on x.CD_Empresa = y.CD_Empresa ")
.AppendLine(" and x.ID_Reserva = y.ID_Reserva ")
.AppendLine(" and isnull(x.ST_Registro, 'A') = 'A' ")
.AppendLine(" and (isnull(y.ST_Registro, '0') = '0' or isnull(y.ST_Registro, '0') = '1' or isnull(y.ST_Registro, '0') = '2') ")
.AppendLine(" and exists(select 1 from dateRange w where convert(datetime, floor(convert(decimal(30,10), w.dt))) ")
.AppendLine(" between convert(datetime, floor(convert(decimal(30,10), x.DT_Reserva))) ")
.AppendLine(" and convert(datetime, floor(convert(decimal(30,10), x.DT_PrevRetorno))))")
.AppendLine(" and x.cd_empresa = '"+ Cd_empresa.Trim() + "'")
.AppendLine(" and y.st_kitextrator = 1), 0)")
.AppendLine("from tb_res_kitextrator a ");
TList_KitExtrator lista = new TList_KitExtrator();
bool podeFecharBco = false;
if (Banco_Dados == null)
{
podeFecharBco = CriarBanco_Dados(false);
}
System.Data.SqlClient.SqlDataReader reader = ExecutarBusca(sql.ToString());
try
{
while (reader.Read())
{
TRegistro_KitExtrator reg = new TRegistro_KitExtrator();
if (!reader.IsDBNull(reader.GetOrdinal("Qt_kit")))
{
reg.Qtd_kit = reader.GetInt32(reader.GetOrdinal("Qt_kit"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Qt_reservada")))
{
reg.Qtd_reservada = reader.GetInt32(reader.GetOrdinal("Qt_reservada"));
}
lista.Add(reg);
}
}
finally
{
reader.Close();
reader.Dispose();
if (podeFecharBco)
{
deletarBanco_Dados();
}
}
return(lista);
}