/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyResponses table.
/// </summary>
public static IDataReader GetAll(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_surveyresponses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("surveyguid = :surveyguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyPages table.
/// </summary>
public static IDataReader GetAll(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT sp.*, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveyquestions sq WHERE sp.pageguid = sq.pageguid) AS questioncount ");
sqlCommand.Append("FROM mp_surveypages sp ");
sqlCommand.Append("WHERE sp.surveyguid = :surveyguid ");
sqlCommand.Append("ORDER BY sp.pageorder; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Selects a particular Track from the doan_MediaTracks table.
/// </summary>
/// <param name="trackID">The ID of the track.</param>
/// <returns>An IDataReader containing the MediaTrack data.</returns>
public static IDataReader Select(int trackId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_mediatrack ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("trackid = :trackid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("trackid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyQuestionAnswers table.
/// </summary>
public static IDataReader GetOneResult(Guid responseGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_surveys s ");
sqlCommand.Append("JOIN mp_surveyresponses sr ");
sqlCommand.Append("ON s.surveyguid = sr.SurveyGuid ");
sqlCommand.Append("JOIN mp_surveypages sp ");
sqlCommand.Append("ON sr.surveyguid = sp.SurveyGuid ");
sqlCommand.Append("JOIN mp_surveyquestions sq ");
sqlCommand.Append("ON sp.pageguid = sq.pageguid ");
sqlCommand.Append("LEFT JOIN mp_SurveyQuestionAnswers qa ");
sqlCommand.Append("ON sq.questionguid = qa.questionguid ");
sqlCommand.Append("AND sr.responseguid = qa.responseguid ");
sqlCommand.Append("WHERE sr.responseguid = :responseguid ");
sqlCommand.Append("AND sr.complete = true ");
sqlCommand.Append("AND sp.pageenabled = true ");
sqlCommand.Append("ORDER BY sp.pageorder, sq.questionorder; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("responseguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = responseGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows from the mp_Polls table matching the siteGuid.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public static IDataReader GetPolls(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_polls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid = :siteguid ");
sqlCommand.Append("ORDER BY activefrom DESC, question ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader from the mp_PollOptions table.
/// </summary>
/// <param name="optionGuid"> pollGuid </param>
public static IDataReader GetPollOptions(Guid pollGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_polloptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pollguid = :pollguid ");
sqlCommand.Append("ORDER BY \"order\", answer ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("pollguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pollGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_Surveys table.
/// </summary>
public static IDataReader GetAll(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("s.surveyguid, ");
sqlCommand.Append("s.siteguid, ");
sqlCommand.Append("s.surveyname, ");
sqlCommand.Append("s.creationdate, ");
sqlCommand.Append("s.startpagetext, ");
sqlCommand.Append("s.endpagetext, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveypages sp WHERE sp.surveyguid = s.surveyguid) AS pagecount, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveyresponses sr WHERE sr.surveyguid = s.surveyguid) AS responsecount ");
sqlCommand.Append(" ");
sqlCommand.Append(" ");
sqlCommand.Append(" ");
sqlCommand.Append("FROM mp_surveys s ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("s.siteguid = :siteguid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("s.surveyname ");
//sqlCommand.Append(" ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_Polls table.
/// </summary>
/// <param name="pollGuid"> pollGuid </param>
public static IDataReader GetPoll(Guid pollGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT p.*, ");
sqlCommand.Append("(SELECT SUM(votes) FROM mp_polloptions WHERE mp_polloptions.pollguid = :pollguid) As totalvotes ");
sqlCommand.Append("FROM mp_polls p ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("p.pollguid = :pollguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("pollguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pollGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all items for a single definition.
/// </summary>
/// <param name="itemID"> itemID </param>
public static IDataReader GetAllForDefinition(Guid definitionGuid, Guid siteGuid)
{
string sqlCommand = @"select
siteguid,
featureguid,
i.moduleguid,
i.moduleid,
definitionguid,
itemguid,
itemid,
i.sortorder,
createdutc,
lastmodutc,
ms.settingvalue as globalviewsortorder
from i7_sflexi_items i
left join mp_modulesettings ms on ms.moduleguid = i.moduleguid
where definitionguid = :defguid and i.siteguid = :siteguid and ms.settingname = 'GlobalViewSortOrder'
order by globalviewsortorder asc, i.moduleid asc, sortorder asc, createdutc asc;";
var sqlParam = new List <NpgsqlParameter> {
new NpgsqlParameter(":defguid", NpgsqlDbType.Varchar)
{
Direction = ParameterDirection.Input, Value = definitionGuid
},
new NpgsqlParameter(":siteguid", NpgsqlDbType.Varchar)
{
Direction = ParameterDirection.Input, Value = siteGuid
}
};
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand,
sqlParam.ToArray()));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_Surveys table.
/// </summary>
/// <param name="surveyGuid"> surveyGuid </param>
public static IDataReader GetOne(
Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT s.*, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveypages sp WHERE sp.surveyguid = s.surveyguid) AS pagecount, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveyresponses sr WHERE sr.surveyguid = s.surveyguid) AS responsecount ");
sqlCommand.Append("FROM mp_surveys s ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("s.surveyguid = :surveyguid; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetByGuidForModule(Guid fieldGuid, Guid moduleGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("select * from i7_sflexi_values where moduleguid = :moduleguid and fieldguid = :fieldguid;");
var sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter(":moduleguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = moduleGuid
},
new NpgsqlParameter(":fieldguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = fieldGuid
}
};
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
sqlParams.ToArray()));
}
public static IDataReader GetPageForDefinition(
Guid defGuid,
Guid siteGuid,
int pageNumber,
int pageSize,
string searchTerm = "",
string searchField = "",
//string sortField = "",
bool descending = false)
{
StringBuilder sqlCommand = new StringBuilder();
if (String.IsNullOrWhiteSpace(searchField) && !String.IsNullOrWhiteSpace(searchTerm))
{
sqlCommand.Append(@"select row_number() over (order by sortorder) as rowid
, count(*) over() as totalrows
, i.*
from i7_sflexi_items i
join(
select distinct itemguid
from i7_sflexi_values
where fieldvalue like '%:searchterm%'
) v on v.itemguid = i.itemguid
where definitionguid = ':defguid' and siteguid = ':siteguid'
order by sortorder :sortdirection
limit :pagesize " + (pageNumber > 1 ? "offset :offsetrows;" : ";"));
}
else if (!String.IsNullOrWhiteSpace(searchField) && !String.IsNullOrWhiteSpace(searchTerm))
{
sqlCommand.Append(@"select row_number() over (order by sortorder) as rowid
, count(*) over() as totalrows
, i.*
from i7_sflexi_items i
join(
select distinct itemguid, fieldguid
from i7_sflexi_values
where fieldvalue like '%:searchterm%'
) v on v.itemguid = i.itemguid
join(
select distinct fieldguid
from i7_sflexi_fields
where name = :searchfield
) f on f.fieldguid = v.fieldguid
where definitionguid = ':defguid' and siteguid = ':siteguid'
order by sortorder :sortdirection
limit :pagesize " + (pageNumber > 1 ? "offset :offsetrows;" : ";"));
}
else
{
sqlCommand.Append(@"select row_number() over (order by sortorder) as rowid
, count(*) over() as totalrows
, i.*
from i7_sflexi_items i
where definitionguid = ':defguid' and siteguid = ':siteguid'
order by sortorder :sortdirection
limit :pagesize " + (pageNumber > 1 ? "offset :offsetrows;" : ";"));
}
int offsetRows = (pageSize * pageNumber) - pageSize;
var sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter(":pagesize", NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = pageSize
},
new NpgsqlParameter(":offsetrows", NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = offsetRows
},
new NpgsqlParameter(":searchterm", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = searchTerm
},
new NpgsqlParameter(":searchfield", NpgsqlDbType.Varchar, 50)
{
Direction = ParameterDirection.Input, Value = searchField
},
new NpgsqlParameter(":defguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = defGuid
},
new NpgsqlParameter(":siteguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = siteGuid
},
new NpgsqlParameter(":sortdirection", NpgsqlDbType.Varchar, 4)
{
Direction = ParameterDirection.Input, Value = descending ? "desc" : "asc"
}
};
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
sqlParams.ToArray()));
}
public static IDataReader GetPageOfValuesForField(
Guid moduleGuid,
Guid definitionGuid,
string field,
int pageNumber,
int pageSize,
string searchTerm = "",
//string searchField = "",
bool descending = false)
{
StringBuilder sqlCommand = new StringBuilder();
int offsetRows = (pageSize * pageNumber) - pageSize;
if (!String.IsNullOrWhiteSpace(searchTerm))
{
sqlCommand.Append(@"select sql_calc_found_rows found_rows() as totalrows, v.*
from i7_sflexi_values v
join(
select distinct fieldguid
from i7_sflexi_fields
where name = ':field'
and definitionguid = :definitionguid
) f on f.fieldguid = v.fieldguid
where moduleguid = :moduleguid
and v.fieldvalue like '%:searchterm%'
order by id :sortdirection
limit :pagesize " + (pageNumber > 1 ? "offset :offsetrows;" : ";"));
}
else
{
sqlCommand.Append(@"select sql_calc_found_rows found_rows() as totalrows, v.*
from i7_sflexi_values v
join(
select distinct fieldguid
from i7_sflexi_fields
where name = ':field'
and definitionguid = :definitionguid
) f on f.fieldguid = v.fieldguid
where moduleguid = :moduleguid
order by id :sortdirection
limit :pagesize " + (pageNumber > 1 ? "offset :offsetrows;" : ";"));
}
var sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter(":pagesize", NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = pageSize
},
new NpgsqlParameter(":offsetrows", NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = offsetRows
},
new NpgsqlParameter(":searchterm", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = searchTerm
},
new NpgsqlParameter(":field", NpgsqlDbType.Varchar, 50)
{
Direction = ParameterDirection.Input, Value = field
},
new NpgsqlParameter(":moduleguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = moduleGuid
},
new NpgsqlParameter(":sortdirection", NpgsqlDbType.Varchar, 4)
{
Direction = ParameterDirection.Input, Value = descending ? "DESC" : "ASC"
}
};
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
sqlParams.ToArray()));
}