/// <summary>
/// Get the timetables associated to a tar_id.
/// </summary>
/// <param name="tariffs">ArrayList with CmpTariff.Tariff objects (obtained by CmpTariff::ObtainTariff())</param>
/// <returns>ArrayList of CmpTariff.TimeTable objects with all TimeTables contained by the Tarif passed and for the Date (and hour) specified</returns>
public ArrayList GetTimeTables(ArrayList tariffs)
{
ArrayList arTimeTables = new ArrayList();
// Foreach element of tariffs arraylist get the TIM_ID and check if the TIM_ID is for a TimeTable
// that contains _pInDate or is later (in time).
// First of all get all the aplicable timetables (that is all the timetables thaT apply for the current _pInDate)
CmpTimetablesDB cmpTimetablesDB = new CmpTimetablesDB();
DataTable dtTimetables = cmpTimetablesDB.GetData(null, "TIM_END > @TIMETABLES.TIM_END@ OR TIM_END IS NULL", "TIM_END ASC", new object[] { _pInDate.Hour });
// Ok... now do de iteration over tariffs elements
foreach (object o in tariffs)
{
// Get the TIM_ID specified on the tariff
int timid = ((Tariff)o).TAR_TIM_ID;
// And if it is found in dtTimeTables is added to the array
for (int i = 0; i < dtTimetables.Rows.Count; i++)
{
if (Convert.ToInt32(dtTimetables.Rows[i]["TIM_ID"]) == timid)
{
// Add the current timetable to the array of timetables specified...
arTimeTables.Add(new TimeTable(dtTimetables.Rows[i]));
}
}
}
return(arTimeTables);
}
public string SqlWhereWithoutTrunc(string dateFieldTable, string dateFieldName, ArrayList paramValues)
{
string sql = "";
string pField = "@" + dateFieldTable + "." + dateFieldName + "@";
if (_startDate != DateTime.MinValue)
{
sql += dateFieldName + " >= TRUNC(" + pField + ")";
paramValues.Add(_startDate);
}
if (_endDate != DateTime.MaxValue)
{
if (sql != "")
{
sql += " AND ";
}
sql += dateFieldName + " < TRUNC(" + pField + ")";
paramValues.Add(_endDate);
}
if (_dayDefId != null)
{
// Load info
CmpDaysDef cDayDef = new CmpDaysDef();
cDayDef.Load(_dayDefId);
int howManyDays = 0;
string sqlDayDefs = "";
DayOfWeek[] dow = new DayOfWeek[7] {
DayOfWeek.Monday,
DayOfWeek.Tuesday,
DayOfWeek.Wednesday,
DayOfWeek.Thursday,
DayOfWeek.Friday,
DayOfWeek.Saturday,
DayOfWeek.Sunday
};
for (int i = 1; i <= 7; i++) // Oracle days of week: 1 = Mon, 7 = Sun
{
bool isIncluded = cDayDef.IsDayIncluded(dow[i - 1]);
if (isIncluded)
{
if (howManyDays > 0)
{
sqlDayDefs += " OR ";
}
sqlDayDefs += "TO_CHAR(" + dateFieldName + ", 'D') = " + i.ToString();
howManyDays++;
}
}
if (howManyDays > 0)
{
sql += " AND (" + sqlDayDefs + ") ";
}
}
if (_timeTableId != null)
{
if (sql != "")
{
sql += " AND ";
}
CmpTimetablesDB cTimetable = new CmpTimetablesDB();
DataTable dtTd = cTimetable.GetData(null, "TIM_ID = @TIMETABLES.TIM_ID@", null, new object[] { _timeTableId });
int start = Convert.ToInt32(dtTd.Rows[0]["TIM_INI"]);
int end = Convert.ToInt32(dtTd.Rows[0]["TIM_END"]);
sql += " ( (TO_NUMBER(TO_CHAR(" + dateFieldName + ", 'HH24'))*60+ TO_NUMBER(TO_CHAR(" + dateFieldName + ", 'MI'))) >= " + start.ToString();
sql += " AND (TO_NUMBER(TO_CHAR(" + dateFieldName + ", 'HH24'))*60+ TO_NUMBER(TO_CHAR(" + dateFieldName + ", 'MI'))) <= " + end.ToString() + ")";
}
if (sql != "")
{
sql = "(" + sql + ")";
}
return(sql);
}
