public async Task <int> getCountAndSaveDataMarketListInProgres(Request selectedFilters)
{
int count;
int userId = selectedFilters.userId;
List <int> _selectedFiltersVal = selectedFilters.selectedfilters;
IEnumerable <FilterValueDto> filterValues = await _filterManager.GetFilterValuesByFilterValueIds(_selectedFiltersVal);
IEnumerable <FilterDto> filters = await _filterManager.GetFilterByFilterId(filterValues.Select(f => f.FilterId).ToList());
IEnumerable <GroupDto> groups = await _filterManager.GetGroupsByGroupsId(filters.Select(g => g.GroupId).ToList());
DatamartDto datamart = await _filterManager.GetDatamartByDatamartId(groups.First().DatamartId);
StringBuilder query = new StringBuilder();
StringBuilder whereClause = new StringBuilder();
query.AppendFormat("SELECT COUNT(Sequence) FROM");
query.AppendFormat(" {0}.{1} AS F", datamart.DatamartName, datamart.FactName);
int i = 0;
foreach (GroupDto item in groups)
{
query.AppendFormat(" LEFT JOIN {0}.{1} AS G{2} ON G{2}.id = F.{3}", datamart.DatamartName, item.GroupName, i, item.FactKey);
var currentFilterValues = from fv in filterValues
join f in filters on fv.FilterId equals f.FilterId
where f.GroupId == item.GroupId
select new
{
fv.FilterValueName,
f.FilterName
};
var operation = item.Operator;
StringBuilder groupWhereClause = new StringBuilder();
foreach (var filterValue in currentFilterValues)
{
if (!string.IsNullOrEmpty(groupWhereClause.ToString()))
{
groupWhereClause.AppendFormat(" {0}", item.Operator);
}
if (item.ParentGroup == 0)
{
groupWhereClause.AppendFormat(" G{0}.{1} = '{2}'", i, filterValue.FilterValueName, 1);
}
else
{
groupWhereClause.AppendFormat(" G{0}.{1} = '{2}'", i, filterValue.FilterName, filterValue.FilterValueName);
}
}
if (!string.IsNullOrEmpty(groupWhereClause.ToString()))
{
if (!string.IsNullOrEmpty(whereClause.ToString()))
{
whereClause.Append(" AND");
whereClause.AppendFormat(" (");
}
whereClause.AppendFormat(" {0}", groupWhereClause.ToString());
}
i++;
whereClause.AppendFormat(" )");
}
if (!string.IsNullOrEmpty(whereClause.ToString()))
{
query.AppendFormat(" WHERE ( {0}", whereClause.ToString());
}
SqlConnection countexecute = new SqlConnection(ConfigurationManager.ConnectionStrings["DataMarketConfiguratinConnectionString"].ConnectionString);
{
countexecute.Open();
using (SqlCommand cmd = new SqlCommand(query.ToString(), countexecute))
{
count = Convert.ToInt32(cmd.ExecuteScalar());
}
}
string tableName = string.Format("ListInProgress_{0}", userId);
SqlConnection conListInProgress = new SqlConnection(ConfigurationManager.ConnectionStrings["DataMarketListInProgressConnectionString"].ConnectionString);
conListInProgress.Open();
using (SqlCommand cmd = conListInProgress.CreateCommand())
{
cmd.CommandText = @"if exists(select 1 from sys.TABLES where NAME = '" + tableName + "') Drop table " + tableName + "";
cmd.ExecuteNonQuery();
}
using (SqlCommand cmd = conListInProgress.CreateCommand())
{
cmd.CommandText = @"if not exists(select 1 from sys.TABLES where NAME = '" + tableName + "') CREATE TABLE " + tableName + "(ListItemId int IDENTITY(1,1) NOT NULL PRIMARY KEY, FilterValueId int NOT NULL, FilterId int NOT NULL, GroupId int NOT NULL, AddedDate date)";
cmd.ExecuteNonQuery();
foreach (GroupDto item in groups)
{
var currentFilterValues = from fv in filterValues
join f in filters on fv.FilterId equals f.FilterId
where f.GroupId == item.GroupId
select new
{
fv.FilterValueId,
f.FilterId
};
foreach (var filterValue in currentFilterValues)
{
var addedDate = DateTime.Now.Date;
cmd.CommandText = "INSERT INTO " + tableName + "(FilterValueId, FilterId, GroupId,AddedDate) VALUES (" + filterValue.FilterValueId + "," + filterValue.FilterId + "," + item.GroupId + ",'" + addedDate + "')";
cmd.ExecuteNonQuery();
}
}
}
conListInProgress.Close();
return(count);
}