//
// When called, this will execute the matching query in the current campaign
// and build the information required for the QueryViewer to interact with it.
//
public void RunQuery(Campaign campaign, int queryID, int sortOn, int sortColumn, int sortDirection)
{
this.campaign = campaign;
dmo = DataManagerOption.GetInstance(campaign.Id, queryID, true);
Query query = new Query(campaign, queryID);
conn = new SqlConnection(campaign.ConnString);
conn.Open();
string queryStr = setupQuerySorting(query.QueryCondition, sortOn, sortColumn, sortDirection);
SqlCommand cmd = new SqlCommand(queryStr, conn);
SqlDataReader sdr = cmd.ExecuteReader();
contents = GetColumnHeaders(sdr);
rowCount = 1;
rowKeys = "rowKeys=new Array();\n";
contents += "<tbody>\n";
string curRow = "";
while (sdr.Read() && (rowCount <= dmo.RowLimit))
{
// Operate with a 1 row delay so we can detect the last row
contents += curRow;
curRow = GetRow(sdr);
}
contents += "</tbody><tfoot>";
contents += curRow;
contents += "</tfoot>";
sdr.Close();
cmd = new SqlCommand(countRowsQuery(query.QueryCondition), conn);
rowCount = (int)cmd.ExecuteScalar();
conn.Close();
}
// Creates a new instance.
static public DataManagerOption CreateInstance(long campaignId, long queryId)
{
DataManagerOption dmo = new DataManagerOption();
dmo.CampaignId = campaignId;
dmo.QueryId = queryId;
dmo.save();
return(dmo);
}
// Gets a named instance.
static public DataManagerOption GetInstance(long campaignId, string name, bool create)
{
List <RainmakerData> options = DataManagerOption.FetchDataSet(("name='" + name + "' AND is_named_query=1"), null);
if (options.Count >= 1)
{
return((DataManagerOption)options[0]);
}
else
{
return((create) ? DataManagerOption.CreateInstance(campaignId, 0) : null);
}
}
// Gets an instance associated with a campaign and query
static public DataManagerOption GetInstance(long campaignId, long queryId, bool create)
{
List <RainmakerData> options = DataManagerOption.FetchDataSet(("campaignid=" + campaignId + " AND queryid=" + queryId + " AND is_named_query=0"), null);
if (options.Count >= 1)
{
return((DataManagerOption)options[0]);
}
else
{
return((create) ? DataManagerOption.CreateInstance(campaignId, queryId) : null);
}
}
// Called each a new record has been loaded by the DataLoader().
private void RecordLoaded(SqlDataReader sdr)
{
DataManagerOption dmo = new DataManagerOption();
dmo.Id = (int)sdr[sdr.GetOrdinal("id")];
dmo.CampaignId = (int)sdr[sdr.GetOrdinal("campaignid")];
dmo.QueryId = (int)sdr[sdr.GetOrdinal("queryid")];
dmo.RowLimit = (int)sdr[sdr.GetOrdinal("rowlimit")];
dmo.SortColumn = (int)sdr[sdr.GetOrdinal("sortcolumn")];
dmo.ShowCSVHeaders = (int)sdr[sdr.GetOrdinal("showcsvheaders")];
dmo.SortActive = (int)sdr[sdr.GetOrdinal("sortactive")];
dmo.SortDirection = (int)sdr[sdr.GetOrdinal("sortdirection")];
dmo.Description = (string)sdr[sdr.GetOrdinal("description")];
Records.Add(dmo);
}
public DataManagerOption copy()
{
DataManagerOption dmo = CreateInstance(this.CampaignId, 0);
dmo.IsNamedQuery = this.IsNamedQuery;
dmo.Name = this.Name;
dmo.RowLimit = this.RowLimit;
dmo.ShowCSVHeaders = this.ShowCSVHeaders;
dmo.SortColumn = this.SortColumn;
dmo.SortActive = this.SortActive;
dmo.SortDirection = this.SortDirection;
dmo.Description = this.Description;
dmo.save();
copyColumns((int)dmo.Id);
return(dmo);
}
// Creates a CSV view of the data
//public string CreateCSVView(int queryID, string connString)
public string CreateCSVView(Campaign campaign, int queryID)
{
dmo = DataManagerOption.GetInstance(campaign.Id, queryID, true);
Query query = new Query(campaign, queryID);
conn = new SqlConnection(campaign.ConnString);
conn.Open();
string queryStr = reformatQuery(query.QueryCondition);
SqlCommand cmd = new SqlCommand(queryStr, conn);
SqlDataReader sdr = cmd.ExecuteReader();
string csv = "";
if (dmo.ShowCSVHeaders == 1)
{
csv = GetCSVColumnHeaders(sdr);
}
while (sdr.Read())
{
csv += GetCSVRow(sdr);
}
conn.Close();
return(csv);
}
// Gets a set of records from the Campaign table.
static public List <RainmakerData> FetchDataSet(string options, string order)
{
DataManagerOption rc = new DataManagerOption();
return(rc.GetDataSet(options, order));
}
// Gets a complete list of all the DataManagerOptions for the campaign
static public string FetchDataSetAsOptionString(string options, string order, int Id)
{
DataManagerOption dmo = new DataManagerOption();
return(dmo.GetDataSetAsOptionString(options, order, Id));
}
// Gets a DataManagerOption instance directly from its id
static public DataManagerOption GetInstance(int id)
{
List <RainmakerData> options = DataManagerOption.FetchDataSet(("id=" + id), null);
return((options.Count >= 1) ? (DataManagerOption)options[0] : null);
}