public KeyValuePair <int, String> ReportFilters_Delete(ReportFilters model, Guid UserUpdate)
{
var param = new SqlParameter[] {
new SqlParameter("@ReportFiltersID", SqlDbType.UniqueIdentifier)
{
Value = model.ReportFiltersID
},
new SqlParameter("@ReportID", SqlDbType.UniqueIdentifier)
{
Value = model.ReportID
},
new SqlParameter("@UserUpdate", SqlDbType.UniqueIdentifier)
{
Value = UserUpdate
},
new SqlParameter("@RetVal", SqlDbType.Int)
{
Direction = ParameterDirection.Output
},
new SqlParameter("@Message", SqlDbType.NVarChar, 100)
{
Direction = ParameterDirection.Output
}
};
int rc = ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreCommand("exec [CRM].[ReportFilters_Delete] @ReportFiltersID, @ReportID, @UserUpdate, @RetVal OUTPUT, @Message OUTPUT", param);
int retVal = (int)param[3].Value;
string valueRes = param[4].Value.ToString();
return(new KeyValuePair <int, string>(retVal, valueRes));
}
public ActionResult Execute(ReportFilters model, string action)
{
Guid UserUpdate = new Guid(Session["CurrentUserID"].ToString());
KeyValuePair <int, string> results = new KeyValuePair <int, string>(1, "");
string message = Resources.ReportFilters.FilterSuccess;
if (action == "Create")
{
if (model.IsEditable == 0)
{
string[] acceptFilter = System.Configuration.ConfigurationManager.AppSettings["AcceptMasterFilter"].Split(';');
if (!acceptFilter.Contains(model.FilterType))
{
var jsonData = new { flag = false, Message = Resources.ReportFilters.FilterForbidden };
return(Json(jsonData));
}
}
var check = db.reportFilters.Where(x => x.ReportID == model.ReportID).Where(x => x.FilterType == model.FilterType);
int count = "StatusCode".Equals(model.FilterType) || "StateCode".Equals(model.FilterType)?
check.Where(x => x.FilterValue == model.FilterValue).Count() :
check.Where(x => x.FilterGUID == model.FilterGUID).Count();
if (count > 0)
{
var jsonData = new { flag = false, Message = Resources.ReportFilters.FilterSame };
return(Json(jsonData));
}
}
if (action == "Delete")
{
message = Resources.ReportFilters.FilterDelSuccess;
}
switch (action)
{
case "Delete":
results = db.ReportFilters_Delete(model, UserUpdate);
break;
case "Create":
model.ReportFiltersID = Guid.NewGuid();
results = db.ReportFilters_Insert(model, UserUpdate);
break;
}
if (results.Key == 0 || results.Key == 16 || results.Key == 6)
{
var jsonData = new { flag = true, Message = message };
return(Json(jsonData));
}
else
{
var jsonData = new { flag = false, Message = results.Value.ToString() };
return(Json(jsonData));
}
}
public KeyValuePair <int, String> ReportFilters_Insert(ReportFilters model, Guid UserUpdate)
{
var param = new SqlParameter[] {
new SqlParameter("@ReportFiltersID", SqlDbType.UniqueIdentifier)
{
Value = model.ReportFiltersID
},
new SqlParameter("@ReportID", SqlDbType.UniqueIdentifier)
{
Value = model.ReportID
},
new SqlParameter("@FilterType", SqlDbType.NVarChar, 50)
{
Value = model.FilterType
},
new SqlParameter("@FilterGUID", SqlDbType.UniqueIdentifier)
{
Value = CheckDefaultGuid(model.FilterGUID)
},
new SqlParameter("@FilterValue", SqlDbType.NVarChar, 200)
{
Value = CheckForDbNull(model.FilterValue)
},
new SqlParameter("@IsEditable", SqlDbType.Int)
{
Value = CheckForDbNull(model.IsEditable)
},
new SqlParameter("@UserUpdate", SqlDbType.UniqueIdentifier)
{
Value = UserUpdate
},
new SqlParameter("@RetVal", SqlDbType.Int)
{
Direction = ParameterDirection.Output
},
new SqlParameter("@Message", SqlDbType.NVarChar, 100)
{
Direction = ParameterDirection.Output
}
};
int rc = ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreCommand("exec [CRM].[ReportFilters_Insert] @ReportFiltersID, @ReportID, @FilterType, @FilterGUID, @FilterValue, @IsEditable, @UserUpdate, @RetVal OUTPUT, @Message OUTPUT", param);
int retVal = (int)param[7].Value;
string valueRes = param[8].Value.ToString();
return(new KeyValuePair <int, string>(retVal, valueRes));
}
/// <summary>
/// Gets the data.
/// </summary>
/// <param name="analyticReport">The analytic report.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
private void GetData(tbl_AnalyticReport analyticReport, DateTime?startDate, DateTime?endDate)
{
var query = analyticReport.tbl_Analytic.Query.Replace("#SiteID#", CurrentUser.Instance.SiteID.ToString());
query = query.Replace("#StartDate#", ((DateTime)startDate).ToString("yyyy-MM-dd"))
.Replace("#EndDate#", ((DateTime)endDate).ToString("yyyy-MM-dd"))
.Replace("#GroupByDays#", (endDate - startDate).Value.Days == 1 ? "0" : "1");
if (ReportFilters.Any())
{
var columns = new List <string>();
var values = new List <string>();
var operators = new List <int>();
var queryString = string.Empty;
foreach (var reportFilter in ReportFilters)
{
if (string.IsNullOrEmpty(reportFilter.Query) && reportFilter.Operator != FilterOperator.None)
{
columns.Add(reportFilter.ColumnName);
values.Add(reportFilter.Value);
operators.Add((int)reportFilter.Operator);
}
queryString += reportFilter.Query;
}
query = query.Replace("#FilterColumnName#", string.Join(",", columns))
.Replace("#FilterColumnValue#", string.Join(",", values))
.Replace("#FilterOperator#", string.Join(",", operators))
.Replace("#FilterQuery#", queryString);
}
else
{
query = query.Replace("#FilterColumnName#", string.Empty)
.Replace("#FilterColumnValue#", string.Empty)
.Replace("#FilterOperator#", string.Empty)
.Replace("#FilterQuery#", string.Empty);
}
query = query.Replace("#FilterQuery#", string.Empty);
Data = DataManager.AnalyticReport.SelectReportData(query);
}