public bool Checking(string addresse, int _month, int _year, int month, int year)
{
bool a1 = !((addresse[0] == '5' && addresse.Length == 8));
bool a2 = !ProvinceFilter(addresse);
bool a3 = SD.DateFilter(_month, _year, new int[] { month }, new int[] { year }, false);
return((a1 && a2) && a3);
}
// GET
public async Task <IActionResult> Index(int cpage, string phoneNumber, string employeeName, string dataPlan, string ccc, string ccName, string month, string year,
string minDataExc, string maxDataExc, string minPercent, string maxPercent,
string phoneNumberCheck, string employeeNameCheck, string dataPlanCheck, string cccCheck, string ccNameCheck, string monthCheck, string yearCheck,
string DataExcCheck, string PercentCheck, string page, string next, string previous)
{
var _month = (month != null && SD.months.ContainsKey(month)) ? SD.months[month] : -1;
if (_month == -1)
{
_month = Parse.IntTryParse(month);
}
var _year = Parse.IntTryParse(year);
var _minDataExc = Parse.FloatTryParse(minDataExc);
var _maxDataExc = Parse.FloatTryParse(maxDataExc);
var _minPercent = Parse.FloatTryParse(minPercent);
var _maxPercent = Parse.FloatTryParse(maxPercent);
//
var query1 = (from pls in _context.PhoneLineSummaries
join pe in _context.PhoneLineEmployees on pls.PhoneNumber equals pe.PhoneNumber
join e in _context.Employees on pe.EmployeeId equals e.EmployeeId
join dpa in _context.DataPlanAssignments on pls.PhoneNumber equals dpa.PhoneNumber
join dp in _context.DataPlans on dpa.DataPlanId equals dp.DataPlanId
join cc in _context.CostCenters on e.CostCenterCode equals cc.Code
where (pls.GprsExpenses > dp.Cost)
select new
{
EmployeeId = e.EmployeeId,
EmployeeName = e.Name,
CostCenter = cc.Name,
CC = cc.Code,
PhoneNumber = pls.PhoneNumber,
Month = pls.Month,
Year = pls.Year,
dpaMonth = dpa.Month,
dpaYear = dpa.Year,
DataPlanId = dp.DataPlanId,
DataExceeded = pls.GprsExpenses - dp.Cost,
PerCent = 0
}).ToList();
var query = query1.Where(a => SD.DateFilter(_month, _year, new int[] { a.Month, a.dpaMonth }, new int[] { a.Year, a.dpaYear }, true));
float total = query.Sum(a => a.DataExceeded);
var models = new List <UserExceededDataPlan>();
foreach (var item in query)
{
models.Add(new UserExceededDataPlan()
{
EmployeeId = item.EmployeeId,
EmployeeName = item.EmployeeName,
PhoneNumber = item.PhoneNumber,
CC = item.CC,
CostCenter = item.CostCenter,
Month = item.Month,
Year = item.Year,
DataPlanId = item.DataPlanId,
DataExceeded = item.DataExceeded,
PerCent = item.PerCent
});
}
foreach (var userExceededDataPlan in models)
{
userExceededDataPlan.PerCent = 100 * (userExceededDataPlan.DataExceeded / total);
}
//
Tuple <bool, string>[] show = SD.Show(new List <string>()
{
phoneNumberCheck, employeeNameCheck, dataPlanCheck, cccCheck, ccNameCheck, monthCheck, yearCheck, DataExcCheck, DataExcCheck, PercentCheck, PercentCheck
},
new List <string>()
{
phoneNumber, employeeName, dataPlan, ccc, ccName, month, year, minDataExc, maxDataExc, minPercent, maxPercent
});
ViewData["columns"] = show;
//
models = DataFilter <UserExceededDataPlan> .Filter(phoneNumber, (m) => m.PhoneNumber, models).ToList();
models = DataFilter <UserExceededDataPlan> .Filter(employeeName, (m) => m.EmployeeName, models).ToList();
models = DataFilter <UserExceededDataPlan> .Filter(dataPlan, (m) => m.DataPlanId, models).ToList();
models = DataFilter <UserExceededDataPlan> .Filter(_minDataExc, _maxDataExc, (m) => m.DataExceeded, models).ToList();
models = DataFilter <UserExceededDataPlan> .Filter(_minPercent, _maxPercent, (m) => m.PerCent, models).ToList();
//separar en paginas
models = models.OrderBy(m => m.EmployeeName).ToList();
var result = Paging <UserExceededDataPlan> .Pages(models, page, cpage, (next != null), (previous != null));
ViewData["top"] = result.Item2;
ViewData["mult"] = result.Item3;
ViewData["page"] = result.Item4;
bool[] mask = { phoneNumberCheck != null, employeeNameCheck != null, dataPlanCheck != null, cccCheck != null, ccNameCheck != null, monthCheck != null, yearCheck != null, DataExcCheck != null, false, PercentCheck != null, false };
string csv = CSVStringConstructor(show, mask, result.Item1);
//ViewData["csv"] = ss;
string httpSessionName = SD.HttpSessionString(new List <string> {
"UserExceededDataPlan", result.Item4.ToString(), phoneNumber, employeeName, dataPlan, ccc, ccName, month, year, minDataExc, maxDataExc, minPercent, maxPercent,
(phoneNumberCheck != null).ToString(), (employeeNameCheck != null).ToString(), (dataPlanCheck != null).ToString(), (cccCheck != null).ToString(), (ccNameCheck != null).ToString(), (monthCheck != null).ToString(), (yearCheck != null).ToString(), (DataExcCheck != null).ToString(), (PercentCheck != null).ToString()
});
HttpContext.Session.SetString(httpSessionName, csv);
return(View(result.Item1));
}
// GET
public async Task <IActionResult> Index(int cpage, string phoneNumber, string employeeName, string callingPlan, string smsPlan,
string minMinExc, string maxMinExc, string minSmsExc, string maxSmsExc, string minMinPercent, string maxMinPercent, string minSmsPercent, string maxSmsPercent,
string phoneNumberCheck, string employeeNameCheck, string callingPlanCheck, string smsPlanCheck, string minExcCheck, string smsExcCheck, string minPercentCheck,
string smsPercentCheck, string monthCheck, string yearCheck,
string month, string year, string page, string next, string previous)
{
var _month = (month != null && SD.months.ContainsKey(month)) ? SD.months[month] : -1;
if (_month == -1)
{
_month = Parse.IntTryParse(month);
}
var _year = Parse.IntTryParse(year);
var _minMinExc = Parse.FloatTryParse(minMinExc);
var _maxMinExc = Parse.FloatTryParse(maxMinExc);
var _minSmsExc = Parse.FloatTryParse(minSmsExc);
var _maxSmsExc = Parse.FloatTryParse(maxSmsExc);
var _minMinPercent = Parse.FloatTryParse(minMinPercent);
var _maxMinPercent = Parse.FloatTryParse(maxMinPercent);
var _minSmsPercent = Parse.FloatTryParse(minSmsPercent);
var _maxSmsPercent = Parse.FloatTryParse(maxSmsPercent);
var query = (from pls in _context.PhoneLineSummaries
join ple in _context.PhoneLineEmployees on pls.PhoneNumber equals ple.PhoneNumber
join e in _context.Employees on ple.EmployeeId equals e.EmployeeId
join mpcpa in _context.CallingPlanAssignments on pls.PhoneNumber equals mpcpa.PhoneNumber
join cp in _context.CallingPlans on mpcpa.CallingPlanId equals cp.CallingPlanId
join spa in _context.SmsPlanAssignments on ple.PhoneNumber equals spa.PhoneNumber
join sp in _context.SmsPlans on spa.SMSPlanId equals sp.SMSPlanId
where (pls.AirTime + pls.RoamingExpenses > cp.Cost || pls.SmsExpenses + pls.RoamingSmsExpenses > sp.Cost)
select new
{
EmployeeName = e.Name,
EmployeeId = e.EmployeeId,
PhoneNumber = pls.PhoneNumber,
CallingPlan = cp.CallingPlanId,
MinutesExceeded = Math.Max(pls.AirTime + pls.RoamingExpenses - cp.Cost, 0),
Month = pls.Month,
Year = pls.Year,
cpaMonth = mpcpa.Month,
cpaYear = mpcpa.Year,
spaMonth = spa.Month,
spaYear = spa.Year,
SmsPlan = sp.SMSPlanId,
MessagesExceeded = Math.Max(pls.SmsExpenses + pls.RoamingSmsExpenses - sp.Cost, 0),
PerCentCalls = 0,
PerCentSms = 0
}).ToList().Where(a => SD.DateFilter(_month, _year, new int[] { a.Month, a.cpaMonth, a.spaMonth }, new int[] { a.Year, a.cpaYear, a.spaYear }, false));
float totalCallCost = query.Sum(a => a.MinutesExceeded);
float totalMessagesCost = query.Sum(a => a.MessagesExceeded);
var models = new List <UserExceededCallingPlan>();
foreach (var item in query)
{
models.Add(new UserExceededCallingPlan()
{
EmployeeId = item.EmployeeId,
EmployeeName = item.EmployeeName,
PhoneNumber = item.PhoneNumber,
CallingPlan = item.CallingPlan,
MinutesExceeded = item.MinutesExceeded,
Month = item.Month,
Year = item.Year,
SmsPlan = item.SmsPlan,
MessagesExceeded = item.MessagesExceeded,
PerCentCalls = item.PerCentCalls,
PerCentSms = item.PerCentSms
});
}
foreach (var userExceededCallingPlan in models)
{
userExceededCallingPlan.PerCentCalls = 100 * (userExceededCallingPlan.MinutesExceeded / totalCallCost);
userExceededCallingPlan.PerCentSms = 100 * (userExceededCallingPlan.MessagesExceeded / totalMessagesCost);
}
//
Tuple <bool, string>[] show = SD.Show(new List <string>()
{
phoneNumberCheck, employeeNameCheck, callingPlanCheck, smsPlanCheck, minExcCheck, minExcCheck, smsExcCheck, smsExcCheck, monthCheck, yearCheck, minPercentCheck, minPercentCheck, smsPercentCheck, smsPercentCheck
},
new List <string>()
{
phoneNumber, employeeName, callingPlan, smsPlan, minMinExc, maxMinExc, minSmsExc, maxSmsExc, month, year, minMinPercent, maxMinPercent, minSmsPercent, maxSmsPercent
});
ViewData["columns"] = show;
//
models = DataFilter <UserExceededCallingPlan> .Filter(phoneNumber, (m) => m.PhoneNumber, models).ToList();
models = DataFilter <UserExceededCallingPlan> .Filter(employeeName, (m) => m.EmployeeName, models).ToList();
models = DataFilter <UserExceededCallingPlan> .Filter(callingPlan, (m) => m.CallingPlan, models).ToList();
models = DataFilter <UserExceededCallingPlan> .Filter(smsPlan, (m) => m.SmsPlan, models).ToList();
models = DataFilter <UserExceededCallingPlan> .Filter(_minMinExc, _maxMinExc, (m) => m.MinutesExceeded, models).ToList();
models = DataFilter <UserExceededCallingPlan> .Filter(_minSmsExc, _maxSmsExc, (m) => m.MessagesExceeded, models).ToList();
models = DataFilter <UserExceededCallingPlan> .Filter(_minMinPercent, _maxMinPercent, (m) => m.PerCentCalls, models).ToList();
models = DataFilter <UserExceededCallingPlan> .Filter(_minSmsPercent, _maxSmsPercent, (m) => m.PerCentSms, models).ToList();
//separar en paginas
models = models.OrderBy(m => m.EmployeeName).ToList();
var result = Paging <UserExceededCallingPlan> .Pages(models, page, cpage, (next != null), (previous != null));
ViewData["top"] = result.Item2;
ViewData["mult"] = result.Item3;
ViewData["page"] = result.Item4;
bool[] mask = { phoneNumberCheck != null, employeeNameCheck != null, callingPlanCheck != null, smsPlanCheck != null, minExcCheck != null, false, smsExcCheck != null, false, monthCheck != null, yearCheck != null, minPercentCheck != null, false, smsPercentCheck != null, false };
string csv = CSVStringConstructor(show, mask, result.Item1);
//ViewData["csv"] = ss;
string httpSessionName = SD.HttpSessionString(new List <string> {
"UserExceededCallingPlan", result.Item4.ToString(), phoneNumber, employeeName, callingPlan, smsPlan, minMinExc, maxMinExc, month, year, minSmsExc, maxSmsExc, minMinPercent, maxMinPercent, minSmsPercent, maxSmsPercent,
(phoneNumberCheck != null).ToString(), (employeeNameCheck != null).ToString(), (callingPlanCheck != null).ToString(), (smsPlanCheck != null).ToString(), (minExcCheck != null).ToString(), (monthCheck != null).ToString(),
(yearCheck != null).ToString(), (smsExcCheck != null).ToString(), (minPercentCheck != null).ToString(), (smsPercentCheck != null).ToString()
});
HttpContext.Session.SetString(httpSessionName, csv);
return(View(result.Item1));
}
public async Task <IActionResult> General(int cpage, string employeeName, string phoneNumber, string callingPlanId, string costCenterCode, string costCenterName,
string minCalls, string maxCalls, string minSms, string maxSms, string minGprs, string maxGprs, string minTotal, string maxTotal,
string employeeNameCheck, string phoneNumberCheck, string callingPlanIdCheck, string callsCheck, string smsCheck, string gprsCheck,
string totalCheck, string costCenterCodeCheck, string costCenterNameCheck, string monthCheck, string yearCheck, string month, string year, string page, string previous, string next)
{
var _month = (month != null && SD.months.ContainsKey(month)) ? SD.months[month] : -1;
if (_month == -1)
{
_month = Parse.IntTryParse(month);
}
var _year = Parse.IntTryParse(year);
var _minCalls = Parse.FloatTryParse(minCalls);
var _maxCalls = Parse.FloatTryParse(maxCalls);
var _minSms = Parse.FloatTryParse(minSms);
var _maxSms = Parse.FloatTryParse(maxSms);
var _minGprs = Parse.FloatTryParse(minGprs);
var _maxGprs = Parse.FloatTryParse(maxGprs);
var _minTotal = Parse.FloatTryParse(minTotal);
var _maxTotal = Parse.FloatTryParse(maxTotal);
var models = new List <CostCenterMobilePhoneGeneralExpenses>();
await Task.Delay(TimeSpan.FromSeconds(3));
var model = (from pls in _context.PhoneLineSummaries
join ple in _context.PhoneLineEmployees on pls.PhoneNumber equals ple.PhoneNumber
join e in _context.Employees on ple.EmployeeId equals e.EmployeeId
join cc in _context.CostCenters on e.CostCenterCode equals cc.Code
join cpa in _context.CallingPlanAssignments on pls.PhoneNumber equals cpa.PhoneNumber
join cp in _context.CallingPlans on cpa.CallingPlanId equals cp.CallingPlanId
select new
{
EmployeeName = e.Name,
PhoneNumber = pls.PhoneNumber,
CallingPlanId = cp.CallingPlanId,
CostCenterCode = cc.Code,
CostCenterName = cc.Name,
Gprs = pls.RoamingGprsExpenses + pls.GprsExpenses,
Minutes = pls.AirTime + pls.RoamingExpenses,
SMS = pls.SmsExpenses + pls.RoamingSmsExpenses,
Month = pls.Month,
Year = pls.Year,
cpaMonth = cpa.Month,
cpaYear = cpa.Year,
Total = pls.Total
}).ToList().Where(a => SD.DateFilter(_month, _year, new int[] { a.Month, a.cpaMonth }, new int[] { a.Year, a.cpaYear }, false, true));
foreach (var item in model)
{
var newModel = new CostCenterMobilePhoneGeneralExpenses()
{
EmployeeName = item.EmployeeName,
PhoneNumber = item.PhoneNumber,
CallingPlanId = item.CallingPlanId,
CostCenterCode = item.CostCenterCode,
CostCenterName = item.CostCenterName,
SMS = item.SMS,
Gprs = item.Gprs,
Minutes = item.Minutes,
Total = item.Total,
Month = item.Month,
Year = item.Year
};
models.Add(newModel);
}
//
Tuple <bool, string>[] show = SD.Show(new List <string>()
{
employeeNameCheck, phoneNumberCheck, callingPlanIdCheck, callsCheck, callsCheck, smsCheck, smsCheck, gprsCheck, gprsCheck, totalCheck, totalCheck, monthCheck, yearCheck, costCenterCodeCheck, costCenterNameCheck
},
new List <string>()
{
employeeName, phoneNumber, callingPlanId, minCalls, maxCalls, minSms, maxSms, minGprs, maxGprs, minTotal, maxTotal, month, year, costCenterCode, costCenterName
});
ViewData["columns"] = show;
//
List <CostCenterMobilePhoneGeneralExpenses> final_result = new List <CostCenterMobilePhoneGeneralExpenses>();
var employeeNameList = (employeeName != null) ? employeeName.Split(", ").ToList() : new List <string>();
models = DataFilter <CostCenterMobilePhoneGeneralExpenses> .Filter(employeeNameList, (m) => m.EmployeeName, models, true).ToList();
var phoneNumberList = (phoneNumber != null) ? phoneNumber.Split(", ").ToList() : new List <string>();
models = DataFilter <CostCenterMobilePhoneGeneralExpenses> .Filter(phoneNumberList, (m) => m.PhoneNumber, models, true).ToList();
var callingPlanIdList = (callingPlanId != null) ? callingPlanId.Split(", ").ToList() : new List <string>();
models = DataFilter <CostCenterMobilePhoneGeneralExpenses> .Filter(callingPlanIdList, (m) => m.CallingPlanId, models, true).ToList();
#region filter Min max
models = DataFilter <CostCenterMobilePhoneGeneralExpenses> .Filter(_minCalls, _maxCalls, (m) => m.Minutes, models).ToList();
models = DataFilter <CostCenterMobilePhoneGeneralExpenses> .Filter(_minSms, _maxSms, (m) => m.SMS, models).ToList();
models = DataFilter <CostCenterMobilePhoneGeneralExpenses> .Filter(_minGprs, _maxGprs, (m) => m.Gprs, models).ToList();
models = DataFilter <CostCenterMobilePhoneGeneralExpenses> .Filter(_minTotal, _maxTotal, (m) => m.Total, models).ToList();
#endregion
//separar en paginas
models = models.OrderBy(m => m.Year).ThenBy(m => m.Month).ToList();
var result = Paging <CostCenterMobilePhoneGeneralExpenses> .Pages(models, page, cpage, (next != null), (previous != null));
ViewData["top"] = result.Item2;
ViewData["mult"] = result.Item3;
ViewData["page"] = result.Item4;
return(View(result.Item1));
}
// TODO: Falta revisar detalles y hacer el de detais
// GET
public async Task <IActionResult> Index(int cpage, string costCenterCode, string costCenterName, string month, string year,
string minCalls, string maxCalls, string minSms, string maxSms, string minGprs, string maxGprs, string minTotal, string maxTotal, string minPercent, string maxPercent,
string costCenterCodeCheck, string costCenterNameCheck, string callsCheck, string smsCheck, string gprsCheck,
string totalCheck, string percentCheck, string monthCheck, string yearCheck, string page, string previous, string next)
{
var _month = (month != null && SD.months.ContainsKey(month)) ? SD.months[month] : -1;
if (_month == -1)
{
_month = Parse.IntTryParse(month);
}
var _year = Parse.IntTryParse(year);
var _minCalls = Parse.FloatTryParse(minCalls);
var _maxCalls = Parse.FloatTryParse(maxCalls);
var _minSms = Parse.FloatTryParse(minSms);
var _maxSms = Parse.FloatTryParse(maxSms);
var _minGprs = Parse.FloatTryParse(minGprs);
var _maxGprs = Parse.FloatTryParse(maxGprs);
var _minTotal = Parse.FloatTryParse(minTotal);
var _maxTotal = Parse.FloatTryParse(maxTotal);
var _minPercent = Parse.FloatTryParse(minPercent);
var _maxPercent = Parse.FloatTryParse(maxPercent);
var models = new List <CostCenterMobilePhoneExpense>();
await Task.Delay(TimeSpan.FromSeconds(3));
var query1 = (from pls in _context.PhoneLineSummaries
join ple in _context.PhoneLineEmployees on pls.PhoneNumber equals ple.PhoneNumber
join e in _context.Employees on ple.EmployeeId equals e.EmployeeId
join cc in _context.CostCenters on e.CostCenterCode equals cc.Code
where pls.Month == _month
select new
{
CostCenterName = cc.Name,
CostCenterCode = cc.Code,
Calls = pls.AirTime,
SMS = pls.SmsExpenses + pls.RoamingSmsExpenses,
GPRS = pls.GprsExpenses + pls.RoamingGprsExpenses,
Month = pls.Month,
Year = pls.Year,
Total = pls.Total
}).ToList().Where(a => SD.DateFilter(_month, _year, new int[] { a.Month }, new int[] { a.Year }, true));
var query11 = query1.ToList();
var query2 = query11.GroupBy(a => a.CostCenterCode);
float total = 0;
foreach (var costCenter in query2)
{
var newModel = new CostCenterMobilePhoneExpense()
{
CostCenterCode = costCenter.Key,
CostCenterName = costCenter.Select(a => a.CostCenterName).First(),
Calls = costCenter.Sum(a => a.Calls),
SMS = costCenter.Sum(a => a.SMS),
GPRS = costCenter.Sum(a => a.GPRS),
Total = costCenter.Sum(a => a.Total),
Month = costCenter.Select(a => a.Month).First(),
Year = costCenter.Select(a => a.Year).First(),
Percent = 0,
};
total += newModel.Total;
models.Add(newModel);
}
foreach (var model in models)
{
model.Percent = (100 * (model.Total / total));
}
//
Tuple <bool, string>[] show = SD.Show(new List <string>()
{
costCenterCodeCheck, costCenterNameCheck, callsCheck, callsCheck, smsCheck, smsCheck, gprsCheck, gprsCheck, totalCheck, totalCheck, percentCheck, percentCheck, monthCheck, yearCheck
},
new List <string>()
{
costCenterCode, costCenterName, minCalls, maxCalls, minSms, maxSms, minGprs, maxGprs, minTotal, maxTotal, minPercent, maxPercent, month, year
});
ViewData["columns"] = show;
//
models = DataFilter <CostCenterMobilePhoneExpense> .Filter(costCenterCode, (m) => m.CostCenterCode, models).ToList();
models = DataFilter <CostCenterMobilePhoneExpense> .Filter(costCenterName, (m) => m.CostCenterName, models).ToList();
models = DataFilter <CostCenterMobilePhoneExpense> .Filter(_minCalls, _maxCalls, (m) => m.Calls, models).ToList();
models = DataFilter <CostCenterMobilePhoneExpense> .Filter(_minSms, _maxSms, (m) => m.SMS, models).ToList();
models = DataFilter <CostCenterMobilePhoneExpense> .Filter(_minGprs, _maxGprs, (m) => m.GPRS, models).ToList();
models = DataFilter <CostCenterMobilePhoneExpense> .Filter(_minTotal, _maxTotal, (m) => m.Total, models).ToList();
models = DataFilter <CostCenterMobilePhoneExpense> .Filter(_minPercent, _maxPercent, (m) => m.Percent, models).ToList();
//separar en paginas
models = models.OrderBy(m => m.CostCenterCode).ToList();
var result = Paging <CostCenterMobilePhoneExpense> .Pages(models, page, cpage, (next != null), (previous != null));
ViewData["top"] = result.Item2;
ViewData["mult"] = result.Item3;
ViewData["page"] = result.Item4;
return(View(result.Item1));
}
