public string CountApplicationInInterwalTime(string dateFrom, string dateTo)
{
string contentCommand = "SELECT COUNT(DISTINCT IdTitle) as countIdTitle FROM alldate "
+ " INNER JOIN activeapplications ON activeapplications.Id = alldate.IdTitle "
+ " LEFT JOIN membership ON membership.Id = activeapplications.IdMembership "
+ " WHERE IdTitle > 2 AND (activeapplications.IdMembership IS NULL OR membership.AsOneApplication = 0) "
+ " AND " + SqlValidator.Validate_BETWEEN(ColumnNames.Date, dateFrom, dateTo).Replace("Date", "alldate.Date");
int returnCount = Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "countIdTitle")[0]);
contentCommand = "SELECT COUNT(DISTINCT activeapplications.IdMembership) as countIdTitle FROM alldate "
+ " INNER JOIN activeapplications ON activeapplications.Id = alldate.IdTitle "
+ " INNER JOIN membership ON membership.Id = activeapplications.IdMembership "
+ " WHERE IdTitle > 2 AND membership.AsOneApplication = 1 "
+ " AND " + SqlValidator.Validate_BETWEEN(ColumnNames.Date, dateFrom, dateTo).Replace("Date", "alldate.Date");
returnCount += Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "countIdTitle")[0]);
return(returnCount.ToString());
}
public static string CheckParameters(CommandParameters parameters)
{
string query = string.Empty;
if (parameters.ID.Any())
{
query += SqlValidator.AND + ColumnNames.ID + SqlValidator.Validate_IN(parameters.ID);
}
if (parameters.Name.Any())
{
query += SqlValidator.AND + ColumnNames.Name + SqlValidator.Validate_IN(parameters.Name);
}
if (!string.IsNullOrEmpty(parameters.ActivityTimeFrom) && !string.IsNullOrEmpty(parameters.ActivityTimeTo))
{
query += SqlValidator.AND + SqlValidator.Validate_BETWEEN(ColumnNames.ActivityTime, parameters.ActivityTimeFrom, parameters.ActivityTimeTo);
}
if (!string.IsNullOrEmpty(parameters.ActivityTimeFrom) && string.IsNullOrEmpty(parameters.ActivityTimeTo))
{
query += SqlValidator.AND + ColumnNames.ActivityTime + SqlValidator.FromValue + parameters.ActivityTimeFrom;
}
if (string.IsNullOrEmpty(parameters.ActivityTimeFrom) && !string.IsNullOrEmpty(parameters.ActivityTimeTo))
{
query += SqlValidator.AND + ColumnNames.ActivityTime + SqlValidator.ToValue + parameters.ActivityTimeTo;
}
if (!string.IsNullOrEmpty(parameters.StartDate) && !string.IsNullOrEmpty(parameters.EndDate))
{
query += SqlValidator.AND + SqlValidator.Validate_BETWEEN(ColumnNames.Date, parameters.StartDate, parameters.EndDate);
}
if (!string.IsNullOrEmpty(parameters.StartDate) && string.IsNullOrEmpty(parameters.EndDate))
{
query += SqlValidator.AND + ColumnNames.Date + SqlValidator.FromValue + SqlValidator.Validate(parameters.StartDate);
}
if (string.IsNullOrEmpty(parameters.StartDate) && !string.IsNullOrEmpty(parameters.EndDate))
{
query += SqlValidator.AND + ColumnNames.Date + SqlValidator.ToValue + SqlValidator.Validate(parameters.EndDate);
}
if (!string.IsNullOrEmpty(parameters.IfActive))
{
query += SqlValidator.AND + ColumnNames.IfActive + SqlValidator.ToValue + parameters.IfActive;
}
if (!string.IsNullOrEmpty(parameters.IfConfiguration))
{
query += SqlValidator.AND + ColumnNames.IfConfiguration + SqlValidator.ToValue + parameters.IfConfiguration;
}
if (!string.IsNullOrEmpty(parameters.IfActiveConfiguration))
{
query += SqlValidator.AND + ColumnNames.IfActiveConfiguration + SqlValidator.ToValue + parameters.IfActiveConfiguration;
}
if (parameters.IdMembership > 0)
{
query += SqlValidator.AND + ColumnNames.IdMembership + SqlValidator.ToValue + parameters.IdMembership;
}
if (parameters.IdMembership == -1)
{
query += SqlValidator.AND + ColumnNames.IdMembership + SqlValidator.ISNULL;
}
if (parameters.IdNameActivity > 0)
{
query += SqlValidator.AND + ColumnNames.IdNameActivity + SqlValidator.ToValue + (int)parameters.IdNameActivity;
}
return(query);
}
