public dynamic GetAllReportfilters([FromBody] FilterReports filters)
{
var userName = HttpContext.Current.GetUserName();
ReportsLayer reportsLayer = new ReportsLayer();
var response = reportsLayer.GetAllReportsFilters(filters, userName);
return(response);
}
public ReportRequestPostDTO(ReportType reportType, Sort sort = null)
{
DownloadFormat = DownloadReportFormat.CSV;
Filters = new FilterReports();
if (reportType == Core.Enumerations.ReportType.NotSpecified)
{
reportType = Core.Enumerations.ReportType.TRANSACTIONS;
}
ReportType = reportType;
if (sort == null)
{
sort = new Sort();
sort.AddField("CreationDate", SortDirection.asc);
}
Sort = sort.GetFields();
}
public static SqlCommand GetFiltersParameters(FilterReports filter, string spName, string userName, Period comparison = null)
{
var sqlComm = new SqlCommand();
sqlComm.CommandTimeout = int.MaxValue;
sqlComm.CommandText = spName;
sqlComm.CommandType = CommandType.StoredProcedure;
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
sqlComm.Parameters.AddWithValue("@userName", userName);
if (filter != null)
{
if (filter.filters.scorecards != null && filter.filters.scorecards.Count > 0)
{
var preparedLst = new StringBuilder();
foreach (var value in filter.filters.scorecards)
{
preparedLst.Append("'" + value + "',");
}
sqlComm.Parameters.AddWithValue("@scorecardIDs", preparedLst.ToString().Trim(Convert.ToChar(",")));
}
if (filter.filters.campaigns != null && filter.filters.campaigns.Count > 0)
{
var preparedLst = new StringBuilder();
foreach (var value in filter.filters.campaigns)
{
preparedLst.Append("'" + value + "',");
}
sqlComm.Parameters.AddWithValue("@campaignIDs", preparedLst.ToString().Trim(Convert.ToChar(",")));
}
if (filter.filters.groups != null && filter.filters.groups.Count > 0)
{
var preparedLst = new StringBuilder();
foreach (var value in filter.filters.groups)
{
preparedLst.Append("'" + value + "',");
}
sqlComm.Parameters.AddWithValue("@groupIDs", preparedLst.ToString().Trim(Convert.ToChar(",")));
}
if (filter.filters != null)
{
sqlComm.Parameters.AddWithValue("@filterByReviewDate", filter.filters.filterByReviewDate);
}
}
if (filter == null || filter.range.start == null || filter.range.start.Length < 4)
{
sqlComm.Parameters.AddWithValue("@Start", DateTime.Now.AddDays(-14));
}
else
{
sqlComm.Parameters.AddWithValue("@Start", DateTime.Parse(filter.range.start));
}
if (filter == null || filter.range.end == null || filter.range.end.Length < 4)
{
sqlComm.Parameters.AddWithValue("@end", DateTime.Now);
}
else
{
sqlComm.Parameters.AddWithValue("@end", DateTime.Parse(filter.range.end));
}
return(sqlComm);
}
//if (comparison != null)
//{
// if (filter == null || comparison.start == null || comparison.start.Length < 4)
// {
// sqlComm.Parameters.AddWithValue("@CompareStart", System.DateTime.Now.AddMonths(-1).AddDays(-14).ToString("d/M/YYYY"));
// }
// else
// {
// sqlComm.Parameters.AddWithValue("@CompareStart", DateTime.Parse(comparison.start));
// }
// if (filter == null || comparison.end == null || comparison.end.Length < 4)
// {
// sqlComm.Parameters.AddWithValue("@CompareEnd", System.DateTime.Now.AddMonths(-1).ToString("d/M/YYYY"));
// }
// else
// {
// sqlComm.Parameters.AddWithValue("@CompareEnd", DateTime.Parse(comparison.end));
// }
//}
}
public static FilterReportsTransport CreateFromBusinessObject(FilterReports filters)
{
FilterReportsTransport result = new FilterReportsTransport
{
AfterDate = filters.AfterDate,
BeforeDate = filters.BeforeDate,
MaxDebitedFundsAmount = filters.MaxDebitedFundsAmount,
MaxDebitedFundsCurrency = filters.MaxDebitedFundsCurrency,
MinDebitedFundsAmount = filters.MinDebitedFundsAmount,
MinDebitedFundsCurrency = filters.MinDebitedFundsCurrency,
MinFeesAmount = filters.MinFeesAmount,
MinFeesCurrency = filters.MinFeesCurrency,
MaxFeesAmount = filters.MaxFeesAmount,
MaxFeesCurrency = filters.MaxFeesCurrency,
MinBalanceAmount = filters.MinBalanceAmount,
MinBalanceCurrency = filters.MinBalanceCurrency,
MaxBalanceAmount = filters.MaxBalanceAmount,
MaxBalanceCurrency = filters.MaxBalanceCurrency,
Currency = filters.Currency,
OwnerId = filters.OwnerId,
AuthorId = filters.AuthorId,
WalletId = filters.WalletId
};
if (filters.Nature != null)
{
result.Nature = new String[filters.Nature.Count];
for (int i = 0; i < filters.Nature.Count; i++)
{
result.Nature[i] = filters.Nature[i].ToString();
}
}
if (filters.Status != null)
{
result.Status = new String[filters.Status.Count];
for (int i = 0; i < filters.Status.Count; i++)
{
result.Status[i] = filters.Status[i].ToString();
}
}
if (filters.Type != null)
{
result.Type = new String[filters.Type.Count];
for (int i = 0; i < filters.Type.Count; i++)
{
result.Type[i] = filters.Type[i].ToString();
}
}
if (filters.ResultCode != null)
{
result.ResultCode = new String[filters.ResultCode.Count];
for (int i = 0; i < filters.ResultCode.Count; i++)
{
result.ResultCode[i] = String.Format("{0:000000}", (int)filters.ResultCode[i]);
}
}
return(result);
}
public FilterReports GetBusinessObject()
{
FilterReports result = new FilterReports();
// Common report filters
result.BeforeDate = BeforeDate;
result.AfterDate = AfterDate;
// Transactions report filters
if (Type != null)
{
foreach (var t in Type)
{
result.Type.Add((TransactionType)Enum.Parse(typeof(TransactionType), t));
}
}
if (Status != null)
{
foreach (var s in Status)
{
result.Status.Add((TransactionStatus)Enum.Parse(typeof(TransactionStatus), s));
}
}
if (Nature != null)
{
foreach (var n in Nature)
{
result.Nature.Add((TransactionNature)Enum.Parse(typeof(TransactionNature), n));
}
}
result.MinDebitedFundsAmount = MinDebitedFundsAmount;
result.MinDebitedFundsCurrency = MinDebitedFundsCurrency;
result.MaxDebitedFundsAmount = MaxDebitedFundsAmount;
result.MaxDebitedFundsCurrency = MaxDebitedFundsCurrency;
result.MinFeesAmount = MinFeesAmount;
result.MinFeesCurrency = MinFeesCurrency;
result.MaxFeesAmount = MaxFeesAmount;
result.MaxFeesCurrency = MaxFeesCurrency;
result.AuthorId = AuthorId;
result.WalletId = WalletId;
if (ResultCode != null)
{
foreach (string rc in ResultCode)
{
if (String.IsNullOrEmpty(rc))
{
continue;
}
int enumInt = Int32.Parse(rc);
result.ResultCode.Add((ReportResultCode)enumInt);
}
}
#region Wallets report filters
result.OwnerId = OwnerId;
result.MinBalanceAmount = MinBalanceAmount;
result.MinBalanceCurrency = MinBalanceCurrency;
result.MaxBalanceAmount = MaxBalanceAmount;
result.MaxBalanceCurrency = MaxBalanceCurrency;
result.Currency = Currency;
#endregion
return(result);
}
/// <summary>
/// Get Filters for reports page
/// </summary>
/// <param name="filters"></param>
/// <param name="userName"></param>
/// <returns></returns>
public dynamic GetAllReportsFilters(FilterReports filters, string userName)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
FilterReports f = new FilterReports()
{
filters = filters.filters, range = filters.range
};
#pragma warning disable CS0436 // Type conflicts with imported type
SqlCommand sqlComm = ReportsHelper.GetFiltersParameters(f, "[GetAllReportsFilters]", userName);
#pragma warning restore CS0436 // Type conflicts with imported type
sqlComm.Connection = sqlCon;
FiltersFormDataReports pageFiltersData = new FiltersFormDataReports();
sqlCon.Open();
try
{
SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(sqlComm);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable tblAgentgroup = ds.Tables[0];
DataTable tblAgentCampain = ds.Tables[1];
DataTable tblAgentAPP = ds.Tables[2];
DataTable tblAgentScorecard = ds.Tables[3];
DataTable tblRangeCount = ds.Tables[4];
List <FilterGroupValue> AgentGrouplLst = new List <FilterGroupValue>();
List <FilterCampainValue> AgentCampainLst = new List <FilterCampainValue>();
List <FilterAppValue> AgentAppLst = new List <FilterAppValue>();
//List<FilterAgentValue> AgentAgentsLst = new List<FilterAgentValue>();
List <FilterScorecardValue> AgentScorecardLst = new List <FilterScorecardValue>();
//List<FilterQAValue> AgentQALst = new List<FilterQAValue>();
//List<FilterTeamLeadValue> teamLeads = new List<FilterTeamLeadValue>();
RangeCalls rangeCalls = new RangeCalls();
//Dim AgentMissedItemsLst As List(Of PageFiltersData.FilterValue) = New List(Of PageFiltersData.FilterValue)
foreach (DataRow row in tblAgentgroup.Rows)
{
AgentGrouplLst.Add(new FilterGroupValue()
{
name = row[0].ToString(),
id = row[0].ToString(),
count = int.Parse(row[1].ToString()),
top3Agents = null
});
}
pageFiltersData.groups = AgentGrouplLst;
foreach (DataRow row in tblAgentCampain.Rows)
{
AgentCampainLst.Add(new FilterCampainValue()
{
name = row[0].ToString(),
id = row[0].ToString(),
count = int.Parse(row[1].ToString())
});
}
pageFiltersData.campaigns = AgentCampainLst;
foreach (DataRow row in tblAgentAPP.Rows)
{
AgentAppLst.Add(new FilterAppValue()
{
name = row[0].ToString(),
id = row[0].ToString(),
count = int.Parse(row[1].ToString())
});
}
pageFiltersData.apps = AgentAppLst;
foreach (DataRow row in tblAgentScorecard.Rows)
{
try
{
int _failScore = 0;
int.TryParse(row[3].ToString(), out _failScore);
AgentScorecardLst.Add(new FilterScorecardValue()
{
id = int.Parse(row[0].ToString()),
name = row[2].ToString(),
count = int.Parse(row[1].ToString()),
failScore = _failScore,
});
}
catch (Exception ex1)
{
throw ex1;
}
}
pageFiltersData.scorecards = AgentScorecardLst;
if (tblRangeCount.Rows.Count > 0)
{
rangeCalls.total = int.Parse(tblRangeCount.Rows[0][0].ToString());
rangeCalls.filtered = int.Parse(tblRangeCount.Rows[0][1].ToString());
}
pageFiltersData.rangeCalls = rangeCalls;
return(pageFiltersData);
}
catch (Exception ex)
{
throw ex;
}
}
}