public Photo [] QueryUntagged(DateRange range, RollSet importidrange)
{
StringBuilder query_builder = new StringBuilder();
query_builder.Append("SELECT * FROM photos WHERE id NOT IN " +
"(SELECT DISTINCT photo_id FROM photo_tags) ");
bool added_where = true;
if (range != null)
{
query_builder.Append(String.Format("AND photos.time >= {0} AND photos.time <= {1} ",
DbUtils.UnixTimeFromDateTime(range.Start),
DbUtils.UnixTimeFromDateTime(range.End)));
added_where = true;
}
if (importidrange != null)
{
query_builder.Append(AddLastImportFilter(importidrange, added_where));
added_where = true;
}
query_builder.Append("ORDER BY time");
return(Query(query_builder.ToString()));
}
private string AddLastImportFilter(RollSet roll_set, bool added_where)
{
if (roll_set == null)
{
return(null);
}
return(String.Format(" {0}{1}", added_where ? " AND " : " WHERE ", roll_set.SqlClause()));
}
public Photo [] Query (Tag [] tags, string extra_condition, DateRange range, RollSet importidrange, RatingRange ratingrange)
{
return Query (FSpot.OrTerm.FromTags(tags), extra_condition, range, importidrange, ratingrange);
}
public void QueryToTemp (string temp_table, Tag [] tags, string extra_condition, DateRange range, RollSet importidrange, RatingRange ratingrange, OrderByTime orderbytime)
{
QueryToTemp (temp_table, FSpot.OrTerm.FromTags(tags), extra_condition, range, importidrange, ratingrange, orderbytime);
}
public Photo [] Query (Term searchexpression, string extra_condition, DateRange range, RollSet importidrange, RatingRange ratingrange)
{
bool hide = (extra_condition == null);
// The SQL query that we want to construct is:
//
// SELECT photos.id
// photos.time
// photos.uri,
// photos.description,
// photos.roll_id,
// photos.default_version_id
// photos.rating
// FROM photos, photo_tags
// WHERE photos.time >= time1 AND photos.time <= time2
// AND photos.rating >= rat1 AND photos.rating <= rat2
// AND photos.id NOT IN (select photo_id FROM photo_tags WHERE tag_id = HIDDEN)
// AND photos.id IN (select photo_id FROM photo_tags where tag_id IN (tag1, tag2..)
// AND extra_condition_string
// GROUP BY photos.id
StringBuilder query_builder = new StringBuilder ();
ArrayList where_clauses = new ArrayList ();
query_builder.Append ("SELECT id, " +
"time, " +
"uri, " +
"description, " +
"roll_id, " +
"default_version_id, " +
"rating, " +
"md5_sum " +
"FROM photos ");
if (range != null) {
where_clauses.Add (String.Format ("time >= {0} AND time <= {1}",
DbUtils.UnixTimeFromDateTime (range.Start),
DbUtils.UnixTimeFromDateTime (range.End)));
}
if (ratingrange != null) {
where_clauses.Add (ratingrange.SqlClause ());
}
if (importidrange != null) {
where_clauses.Add (importidrange.SqlClause ());
}
if (hide && Core.Database.Tags.Hidden != null) {
where_clauses.Add (String.Format ("id NOT IN (SELECT photo_id FROM photo_tags WHERE tag_id = {0})",
FSpot.Core.Database.Tags.Hidden.Id));
}
if (searchexpression != null) {
where_clauses.Add (searchexpression.SqlCondition ());
}
if (extra_condition != null && extra_condition.Trim () != String.Empty) {
where_clauses.Add (extra_condition);
}
if (where_clauses.Count > 0) {
query_builder.Append (" WHERE ");
query_builder.Append (String.Join (" AND ", ((String []) where_clauses.ToArray (typeof(String)))));
}
query_builder.Append (" ORDER BY time");
return Query (query_builder.ToString ());
}
public static IEnumerable<RollDay> GetSet(RollSet set_)
{
switch (set_)
{
case RollSet.DU:
return DU_Roll;
case RollSet.OAT:
return OAT_Roll;
case RollSet.OE:
return OE_Roll;
case RollSet.RX:
return RX_Roll;
case RollSet.UB:
return UB_Roll;
case RollSet.FV:
return FV_Roll;
case RollSet.TU:
return TU_Roll;
case RollSet.TY:
return TY_Roll;
case RollSet.US:
return US_Roll;
default:
throw new NotImplementedException(string.Format("{0} not implemented", set_));
}
}
public Photo [] Query(Term searchexpression, string extra_condition, DateRange range, RollSet importidrange)
{
bool hide = (extra_condition == null);
// The SQL query that we want to construct is:
//
// SELECT photos.id
// photos.time
// photos.directory_path,
// photos.name,
// photos.description,
// photos.roll_id,
// photos.default_version_id
// FROM photos, photo_tags
// WHERE photos.id = photo_tags.photo_id
// AND (photo_tags.tag_id = cat1tag1
// OR photo_tags.tag_id = cat1tag2 )
// AND (photo_tags.tag_id = cat2tag1
// OR photo_tags.tag_id = cat2tag2 )
// AND (photos.roll_id = roll_id1
// OR photos.roll_id = roll_id2 ...)
// GROUP BY photos.id
StringBuilder query_builder = new StringBuilder();
query_builder.Append("SELECT photos.id, " +
"photos.time, " +
"photos.directory_path, " +
"photos.name, " +
"photos.description, " +
"photos.roll_id, " +
"photos.default_version_id " +
"FROM photos ");
bool where_statement_added = false;
if (range != null)
{
query_builder.Append(String.Format("WHERE photos.time >= {0} AND photos.time <= {1} ",
DbUtils.UnixTimeFromDateTime(range.Start),
DbUtils.UnixTimeFromDateTime(range.End)));
where_statement_added = true;
}
if (importidrange != null)
{
query_builder.Append(AddLastImportFilter(importidrange, where_statement_added));
where_statement_added = true;
}
if (hide && Core.Database.Tags.Hidden != null)
{
query_builder.Append(String.Format("{0} photos.id NOT IN (SELECT photo_id FROM photo_tags WHERE tag_id = {1}) ",
where_statement_added ? " AND " : " WHERE ", Core.Database.Tags.Hidden.Id));
where_statement_added = true;
}
if (searchexpression != null)
{
query_builder.Append(String.Format("{0} {1}",
where_statement_added ? " AND " : " WHERE ",
searchexpression.SqlCondition()));
where_statement_added = true;
}
if (extra_condition != null && extra_condition.Length != 0)
{
query_builder.Append(String.Format("{0} {1} ",
where_statement_added ? " AND " : " WHERE ",
extra_condition));
where_statement_added = true;
}
query_builder.Append("ORDER BY photos.time");
Console.WriteLine("Query: {0}", query_builder.ToString());
return(Query(query_builder.ToString()));
}
public Photo [] Query(Tag [] tags, string extra_condition, DateRange range, RollSet importidrange)
{
return(Query(OrTerm.FromTags(tags), extra_condition, range, importidrange));
}
public void PublishHourlyVolumes(RollSet set)
{
ReadFromCSV();
var con = new ConstructGen<double>(new[] { MonthFrom, MonthTo, "Spread" });
var priorVolume = new DatedDataCollectionGen<double>(Lines.Dates,
Lines.Data.Select(x => x.PriorContractVolume).ToArray())
.ToGenPeriod(
periodDateFunction_: x => x,
areSamePeriodFunction_: functionForSameVolumeBucketing(),
collationFunction_: x => x.Sum());
con.SetColumnValues(0, priorVolume);
var newVolume = new DatedDataCollectionGen<double>(Lines.Dates,
Lines.Data.Select(x => x.NewContractVolume).ToArray())
.ToGenPeriod(
periodDateFunction_: x=>x,
areSamePeriodFunction_: functionForSameVolumeBucketing(),
collationFunction_: x => x.Sum());
con.SetColumnValues(1, newVolume);
var spreadVolume = new DatedDataCollectionGen<double>(Lines.Dates,
Lines.Data.Select(x => x.SpreadVolume).ToArray())
.ToGenPeriod(
periodDateFunction_: x => x,
areSamePeriodFunction_: functionForSameVolumeBucketing(),
collationFunction_: x => x.Sum());
con.SetColumnValues(2, spreadVolume);
con.WriteToCSV(string.Format(@"{4}\{0}_{1}_{2}_{3}_HourlyVolume.csv", Rolldate.ToString("yyyyMMdd"), set,
MonthFrom, MonthTo,DIR));
}
public Photo [] Query (Term searchexpression, string extra_condition, DateRange range, RollSet importidrange)
{
bool hide = (extra_condition == null);
// The SQL query that we want to construct is:
//
// SELECT photos.id
// photos.time
// photos.directory_path,
// photos.name,
// photos.description,
// photos.roll_id,
// photos.default_version_id
// FROM photos, photo_tags
// WHERE photos.id = photo_tags.photo_id
// AND (photo_tags.tag_id = cat1tag1
// OR photo_tags.tag_id = cat1tag2 )
// AND (photo_tags.tag_id = cat2tag1
// OR photo_tags.tag_id = cat2tag2 )
// AND (photos.roll_id = roll_id1
// OR photos.roll_id = roll_id2 ...)
// GROUP BY photos.id
StringBuilder query_builder = new StringBuilder ();
query_builder.Append ("SELECT photos.id, " +
"photos.time, " +
"photos.directory_path, " +
"photos.name, " +
"photos.description, " +
"photos.roll_id, " +
"photos.default_version_id " +
"FROM photos ");
bool where_statement_added = false;
if (range != null) {
query_builder.Append (String.Format ("WHERE photos.time >= {0} AND photos.time <= {1} ",
DbUtils.UnixTimeFromDateTime (range.Start),
DbUtils.UnixTimeFromDateTime (range.End)));
where_statement_added = true;
}
if (importidrange != null) {
query_builder.Append (AddLastImportFilter (importidrange, where_statement_added));
where_statement_added = true;
}
if (hide && Core.Database.Tags.Hidden != null) {
query_builder.Append (String.Format ("{0} photos.id NOT IN (SELECT photo_id FROM photo_tags WHERE tag_id = {1}) ",
where_statement_added ? " AND " : " WHERE ", Core.Database.Tags.Hidden.Id));
where_statement_added = true;
}
if (searchexpression != null) {
query_builder.Append (String.Format ("{0} {1}",
where_statement_added ? " AND " : " WHERE ",
searchexpression.SqlCondition()));
where_statement_added = true;
}
if (extra_condition != null && extra_condition.Length != 0) {
query_builder.Append (String.Format ("{0} {1} ",
where_statement_added ? " AND " : " WHERE ",
extra_condition));
where_statement_added = true;
}
query_builder.Append ("ORDER BY photos.time");
Console.WriteLine("Query: {0}", query_builder.ToString());
return Query (query_builder.ToString ());
}
public Photo [] QueryUntagged (DateRange range, RollSet importidrange)
{
StringBuilder query_builder = new StringBuilder ();
query_builder.Append ("SELECT * FROM photos WHERE id NOT IN " +
"(SELECT DISTINCT photo_id FROM photo_tags) ");
bool added_where = true;
if (range != null) {
query_builder.Append (String.Format ("AND photos.time >= {0} AND photos.time <= {1} ",
DbUtils.UnixTimeFromDateTime (range.Start),
DbUtils.UnixTimeFromDateTime (range.End)));
added_where = true;
}
if (importidrange != null) {
query_builder.Append (AddLastImportFilter (importidrange, added_where));
added_where = true;
}
query_builder.Append("ORDER BY time");
return Query (query_builder.ToString ());
}
private string AddLastImportFilter (RollSet roll_set, bool added_where)
{
if (roll_set == null)
return null;
return String.Format (" {0}{1}", added_where ? " AND " : " WHERE ", roll_set.SqlClause () );
}
public Photo [] Query(Term searchexpression, string extra_condition, DateRange range, RollSet importidrange, RatingRange ratingrange)
{
bool hide = (extra_condition == null);
// The SQL query that we want to construct is:
//
// SELECT photos.id
// photos.time
// photos.uri,
// photos.description,
// photos.roll_id,
// photos.default_version_id
// photos.rating
// FROM photos, photo_tags
// WHERE photos.time >= time1 AND photos.time <= time2
// AND photos.rating >= rat1 AND photos.rating <= rat2
// AND photos.id NOT IN (select photo_id FROM photo_tags WHERE tag_id = HIDDEN)
// AND photos.id IN (select photo_id FROM photo_tags where tag_id IN (tag1, tag2..)
// AND extra_condition_string
// GROUP BY photos.id
StringBuilder query_builder = new StringBuilder();
ArrayList where_clauses = new ArrayList();
query_builder.Append("SELECT id, " +
"time, " +
"uri, " +
"description, " +
"roll_id, " +
"default_version_id, " +
"rating, " +
"md5_sum " +
"FROM photos ");
if (range != null)
{
where_clauses.Add(String.Format("time >= {0} AND time <= {1}",
DbUtils.UnixTimeFromDateTime(range.Start),
DbUtils.UnixTimeFromDateTime(range.End)));
}
if (ratingrange != null)
{
where_clauses.Add(ratingrange.SqlClause());
}
if (importidrange != null)
{
where_clauses.Add(importidrange.SqlClause());
}
if (hide && Core.Database.Tags.Hidden != null)
{
where_clauses.Add(String.Format("id NOT IN (SELECT photo_id FROM photo_tags WHERE tag_id = {0})",
FSpot.Core.Database.Tags.Hidden.Id));
}
if (searchexpression != null)
{
where_clauses.Add(searchexpression.SqlCondition());
}
if (extra_condition != null && extra_condition.Trim() != String.Empty)
{
where_clauses.Add(extra_condition);
}
if (where_clauses.Count > 0)
{
query_builder.Append(" WHERE ");
query_builder.Append(String.Join(" AND ", ((String [])where_clauses.ToArray(typeof(String)))));
}
query_builder.Append(" ORDER BY time");
return(Query(query_builder.ToString()));
}
public void QueryToTemp(string temp_table, Tag [] tags, string extra_condition, DateRange range, RollSet importidrange, RatingRange ratingrange, OrderByTime orderbytime)
{
QueryToTemp(temp_table, FSpot.OrTerm.FromTags(tags), extra_condition, range, importidrange, ratingrange, orderbytime);
}
