public List <SummaryDHdata> GetSummaryData(string dataset, string centretype)
{
IList <DatahubCentre> listCentres = null;
List <SummaryDHdata> listResult = new List <SummaryDHdata>();
List <DataSetDate> datasets = GetListDataSetDate().ToList();
DataSetDate selecteddataset = datasets.Where(x => x.code.Equals(dataset)).FirstOrDefault();
string query = "select * from datahub_summary where schooltype = " + centretype + " and Year = " + selecteddataset.year + " and month = " + selecteddataset.month;
if (centretype.Equals("32"))
{
listCentres = GetListNeighbourhoodsname(rpGeneric2nd);
listCentres.Add(new DatahubCentre("1002", "Aberdeen City", "1"));
//add city data to neighbourhoods
query = query + " union select * from datahub_summary where seedcode = 1002 and schooltype = 3 and Year = " + selecteddataset.year + " and month = " + selecteddataset.month;
}
else
{
listCentres = GetListSchoolname();
listCentres.Add(new DatahubCentre("1002", "Aberdeen City", "1"));
}
var listtemp = rpGeneric2nd.FindByNativeSQL(query);
foreach (var itemrow in listtemp)
{
SummaryDHdata temp = new SummaryDHdata();
if (itemrow != null)
{
temp.year = Convert.ToInt16(itemrow[0].ToString());
temp.month = Convert.ToInt16(itemrow[1].ToString());
temp.seedcode = itemrow[2].ToString();
temp.centrename = listCentres.Where(x => x.seedcode.Equals(temp.seedcode)).Select(x => x.name).First().ToString();
temp.listdata = new List <GenericData>();
temp.listdata.Add(new GenericData("Positive Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[4])));
temp.listdata.Add(new GenericData("Non-Positive Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[5])));
temp.listdata.Add(new GenericData("Unknown Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[6])));
temp.sdataset = datasets.Where(x => x.month.Equals(temp.month.ToString()) && x.year.Equals(temp.year.ToString())).FirstOrDefault();
listResult.Add(temp);
}
}
return(listResult.OrderBy(x => x.centrename).ToList());
}
public object Convert(object value, Type targetType, object parameter, string language)
{
if (value == null)
{
return("Unknown");
}
try
{
var inputDate = DateTime.Parse(value.ToString());
return(NumberFormatHelper.GetTimeDateString(inputDate, true));
}
catch
{
return("Unknown");
}
}
public object Convert(object value, Type targetType, object parameter, string language)
{
try
{
// Get our value
var inValue = value as double?;
// Does this null int have a value
if (inValue.HasValue)
return NumberFormatHelper.GetFormattedLargeNumber(inValue.Value);
}
catch (Exception)
{
return "0";
}
return "0";
}
protected GenericData GetUnknown(List <PupilsDataHubObj> listpupils)
{
List <PupilsDataHubObj> pupilsmoveoutScotland = listpupils.Where(x => x.status_code.Equals("000")).ToList();
listpupils = listpupils.Except(pupilsmoveoutScotland).ToList();
GenericData data = new GenericData();
data.Code = "Unknown";
data.count = listpupils.Where(x => x.status_code.Equals("999")).ToList().Count;
data.sum = listpupils.Count();
data.Percent = data.sum != 0? data.count * 100.0F / data.sum : 0.0f;
//data.sPercent = NumberFormatHelper.FormatNumber(data.Percent, 1).ToString();
data.sPercent = double.IsNaN(data.count * 100.0F / data.sum) ? "n/a" : NumberFormatHelper.FormatNumber(data.Percent, 1).ToString();
data.scount = data.sPercent.Equals("n/a") ? "n/a" : data.count.ToString();
return(data);
}
/// <summary>
/// Creates an <see cref="XMLGenName"/> instance from a <see cref="QName"/>.
/// </summary>
/// <returns>The created <see cref="XMLGenName"/> instance.</returns>
/// <param name="qname">A <see cref="QName"/> instance.</param>
/// <param name="bindOptions">The binding options used in the lookup for which a generalized
/// name must be created. The following flags are used here:
/// <see cref="BindOptions.ATTRIBUTE"/> and <see cref="BindOptions.RUNTIME_NAME"/>.</param>
public static XMLGenName fromQName(QName qname, BindOptions bindOptions)
{
bool isAttr = (bindOptions & BindOptions.ATTRIBUTE) != 0;
string?uri = qname.ns.uri;
string?localName;
if ((bindOptions & BindOptions.RUNTIME_NAME) != 0)
{
localName = ASString.AS_convertString(qname.localName);
}
else
{
localName = qname.localName;
}
if (localName == null || localName.Length == 0)
{
return(new XMLGenName(uri: uri, localName: localName, isAttr: isAttr));
}
char firstChar = localName[0];
if (qname.ns.isPublic && (uint)(firstChar - '0') <= 9)
{
NumberFormatHelper.parseArrayIndex(localName, allowLeadingZeroes: false, out uint arrindex);
if ((int)arrindex >= 0)
{
return(new XMLGenName(index: (int)arrindex, isIndex: true, isAttr: isAttr));
}
}
if (firstChar == '@' && (bindOptions & BindOptions.ATTRIBUTE) == 0)
{
isAttr = true;
localName = localName.Substring(1);
}
if (localName.Length == 1 && localName[0] == '*')
{
localName = null;
}
return(new XMLGenName(uri: uri, localName: localName, isAttr: isAttr));
}
/// <summary>
/// This function takes in a datetime string and converts it
/// into a friendly readable string for the UI.
/// </summary>
/// <returns>A human readable date time object</returns>
public object Convert(object value, Type targetType, object parameter, string language)
{
if (value == null)
{
return("Unknown");
}
try
{
// Convert to a datetime
var inputDate = DateTime.Parse(value.ToString());
// Return the formatted DateTime
return(NumberFormatHelper.GetTimeDateString(inputDate, true));
}
catch (Exception)
{
// There was an error either parsing the value or converting the
// date time. We will show a generic unknown message here.
return("Unknown");
}
}
public object Convert(object value, Type targetType, object parameter, string language)
{
return(NumberFormatHelper.FormatTimeString(System.Convert.ToDouble(value) * 1000));
}
public void Write(char *Buffer, ref int BufferLength)
{
NumberFormatHelper.Write(Number, Buffer, ref BufferLength, Base);
}
public double getcheckSumPercentage()
{
return(Convert.ToDouble(NumberFormatHelper.FormatNumber(this.listdata.Select(x => Convert.ToDouble(x.Percent)).Sum(), 1)));
}
//Get SchoolRoll data
private new SPSchoolRollForecast GetSchoolRollForecastData(IGenericRepository2nd rpGeneric2nd, School school)
{
SPSchoolRollForecast SchoolRollForecast = new SPSchoolRollForecast();
List <GenericSchoolData> tempdataActualnumber = new List <GenericSchoolData>();
//get actual number
var listResult = rpGeneric2nd.FindByNativeSQL("Select * from summary_schoolroll where seedcode = " + school.seedcode);
if (listResult != null)
{
foreach (var itemRow in listResult)
{
if (itemRow != null)
{
tempdataActualnumber.Add(new GenericSchoolData(new Year(itemRow[0].ToString()).academicyear, NumberFormatHelper.ConvertObjectToFloat(itemRow[4])));
}
}
}
SchoolRollForecast.ListActualSchoolRoll = tempdataActualnumber;
return(SchoolRollForecast);
}
public string DecimalDot(double val)
{
return(NumberFormatHelper.DecimalDot(val));
}
public string GetStringPercent()
{
return(NumberFormatHelper.FormatNumber(this.Percent, 1, "n/a").ToString());
}
protected List <SummaryDHdata> GetSummaryData(IGenericRepository2nd rpGeneric2nd, string seedcode)
{
List <SummaryDHdata> listResult = new List <SummaryDHdata>();
List <DataSetDate> dataset = GetListDataSetDate().ToList();
var listtemp = rpGeneric2nd.FindByNativeSQL("select * from datahub_summary where TRIM(SeedCode) like '" + seedcode + "'");
foreach (var itemrow in listtemp)
{
SummaryDHdata temp = new SummaryDHdata();
if (itemrow != null)
{
temp.year = Convert.ToInt16(itemrow[0].ToString());
temp.month = Convert.ToInt16(itemrow[1].ToString());
temp.seedcode = itemrow[2].ToString();
temp.listdata = new List <GenericData>();
temp.listdata.Add(new GenericData("Participating Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[4])));
temp.listdata.Add(new GenericData("Non-Participating Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[5])));
temp.listdata.Add(new GenericData("Unknown Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[6])));
temp.sdataset = dataset.Where(x => x.month.Equals(temp.month.ToString()) && x.year.Equals(temp.year.ToString())).FirstOrDefault();
listResult.Add(temp);
}
}
return(listResult.OrderBy(x => x.year).ThenBy(x => x.month).ToList());
}
public JsonResult GetData([System.Web.Http.FromUri] string sClientID, [System.Web.Http.FromUri] string sCategoryID, [System.Web.Http.FromUri] string sServiceType) // get selected list of school's id
{
try
{
object oResult = null;
var ListCategory = new[] { new { Code = "OOA", Name = "Out of Authority" }, new { Code = "CO", Name = "Commissioned" }, new { Code = "CD", Name = "Disability" }, new { Code = "SP", Name = "Special Parenting" } }.ToList();
var ListServiceType = new[] { new { Code = "SC", Name = "Secure Care" }, new { Code = "CH", Name = "Children's Home" }, new { Code = "RS", Name = "Residential School" }, new { Code = "CS", Name = "Crisis Support/Crisis Care" }, new { Code = "TBC", Name = "To Be Confirmed" } }.ToList();
Dictionary <string, string> DictSDataset = GetDatasets();
IList <ChildPlacements> listChild_id = GetListChildId(rpGeneric2nd);
var listChildAgreements = GetChildAgreementByClientID(rpGeneric2nd, sClientID);
var listChildPlacements = GetChildPlacementByClientID(rpGeneric2nd, sClientID);
oResult = new
{
ListClientID = listChild_id.Select(x => new
{
Client_Id = x.client_id,
PlacementCategory = x.placement_category,
ServiceType = x.service_type,
}).OrderBy(x => x.Client_Id),
ClientSelected = listChild_id.Where(x => x.client_id.Equals(sClientID)).Select(x => new
{
Client_Id = x.client_id,
PlacementCategory = x.placement_category,
ServiceType = x.service_type,
}).First(),
ListCategory = ListCategory,
ListCategorySelected = ListCategory.Where(x => x.Code.Equals(sCategoryID)).First(),
ListServiceType = ListServiceType,
ListServiceTypeSelected = ListServiceType.Where(x => x.Code.Equals(sServiceType)).First(),
listChildAgreements = listChildAgreements.Select(x => new
{
Client_Id = x.client_id,
Agreement_Id = x.agreement_id,
Agreement_Started = x.agreement_started.ToShortDateString(),
Agreement_Ended = x.agreement_ended.ToShortDateString(),
Active_Cost = NumberFormatHelper.FormatNumber(x.active_weeks_cost, 2).ToString(),
Actual_Cost = NumberFormatHelper.FormatNumber(x.actual_cost, 2).ToString(),
NumberofDays = x.numberofdays,
Supplier_Name = x.supplier_name,
Payattention = x.payattension,
DatasetDate = DictSDataset[x.dataset]
}).OrderBy(x => x.Agreement_Id).ThenBy(x => x.Agreement_Started),
listChildPlacements = listChildPlacements.Select(x => new
{
Client_Id = x.client_id,
DateOfBirth = x.dob.ToShortDateString(),
Gender = x.gender,
Age = GetAge(x.dob),
Placement_Id = x.placement_id,
Palcement_Started = x.placement_started.ToShortDateString(),
Palcement_Ended = x.placement_ended.ToShortDateString(),
Placement_Name = x.placement_name,
Payattention = x.payattension,
DatasetDate = DictSDataset[x.dataset]
}).OrderBy(x => x.Placement_Id).ThenBy(x => x.Palcement_Started),
Cost_OOA = NumberFormatHelper.FormatNumber(listChildAgreements.Where(x => x.Placement_Category.Equals("OOA") && x.payattension.Equals("")).Sum(x => x.actual_cost), 2).ToString(),
Cost_D = NumberFormatHelper.FormatNumber(listChildAgreements.Where(x => x.Placement_Category.Equals("CD") && x.payattension.Equals("")).Sum(x => x.actual_cost), 2).ToString(),
Cost_CO = NumberFormatHelper.FormatNumber(listChildAgreements.Where(x => x.Placement_Category.Equals("CO") && x.payattension.Equals("")).Sum(x => x.actual_cost), 2).ToString(),
Cost_SP = NumberFormatHelper.FormatNumber(listChildAgreements.Where(x => x.Placement_Category.Equals("SP") && x.payattension.Equals("")).Sum(x => x.actual_cost), 2).ToString(),
Cost_total = NumberFormatHelper.FormatNumber(listChildAgreements.Where(x => x.payattension.Equals("")).Sum(x => x.actual_cost), 2).ToString(),
Active_Cost_OOA = NumberFormatHelper.FormatNumber(listChildAgreements.Where(x => x.Placement_Category.Equals("OOA") && x.payattension.Equals("**")).Sum(x => x.actual_cost), 2).ToString(),
Active_Cost_D = NumberFormatHelper.FormatNumber(listChildAgreements.Where(x => x.Placement_Category.Equals("CD") && x.payattension.Equals("**")).Sum(x => x.actual_cost), 2).ToString(),
Active_Cost_CO = NumberFormatHelper.FormatNumber(listChildAgreements.Where(x => x.Placement_Category.Equals("CO") && x.payattension.Equals("**")).Sum(x => x.actual_cost), 2).ToString(),
Active_Cost_SP = NumberFormatHelper.FormatNumber(listChildAgreements.Where(x => x.Placement_Category.Equals("SP") && x.payattension.Equals("**")).Sum(x => x.actual_cost), 2).ToString(),
Active_Cost_total = NumberFormatHelper.FormatNumber(listChildAgreements.Where(x => x.payattension.Equals("**")).Sum(x => x.actual_cost), 2).ToString(),
listCostbySupplier = listChildAgreements.GroupBy(x => x.supplier_code).Select(r => new
{
Supplier_name = r.First().supplier_name,
Count = r.Count().ToString(),
Cost = NumberFormatHelper.FormatNumber(r.Where(x => x.payattension.Equals("")).Sum(xl => xl.actual_cost), 2).ToString(),
OngoingCost = NumberFormatHelper.FormatNumber(r.Where(x => x.payattension.Equals("**")).Sum(xl => xl.actual_cost), 2).ToString(),
}).ToList(),
ChartCostbyAuthority = GetChartCostByAuthorities(listChildAgreements),
ChartCostbySupplier = GetChartCostBySupplier(listChildAgreements),
};
return(Json(oResult, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(ThrowJsonError(ex));
}
}
protected List <GenericData> GetDestination(string seedcode, List <PupilsDataHubObj> listpupils, IList <Status> destinations)
{
List <PupilsDataHubObj> pupilsmoveoutScotland = listpupils.Where(x => x.Current_Status.ToLower().Equals("moved outwith scotland")).ToList();
listpupils = listpupils.Except(pupilsmoveoutScotland).ToList();
List <GenericData> listdata = new List <GenericData>();
GenericData tempobj = new GenericData();
foreach (Status item in destinations)
{
tempobj = new GenericData();
tempobj.Code = item.code;
tempobj.Name = item.name;
tempobj.count = listpupils.Where(x => (x.Current_Status != null && x.status_code.Equals(item.code))).ToList().Count;
tempobj.sum = listpupils.Count();
tempobj.Percent = tempobj.sum != 0 ? tempobj.count * 100.0F / tempobj.sum : 0.0f;;
tempobj.sPercent = double.IsNaN(tempobj.count * 100.0F / tempobj.sum) ? "n/a" : NumberFormatHelper.FormatNumber(tempobj.Percent, 1).ToString();
tempobj.scount = tempobj.sPercent.Equals("n/a") ? "n/a" : tempobj.count.ToString();
//tempobj.sPercent = NumberFormatHelper.FormatNumber(tempobj.Percent, 1).ToString();
listdata.Add(tempobj);
}
return(listdata);
}
public void Init(int bubblePower)
{
string bubbleNumberString = NumberFormatHelper.FormatNumberBubble(bubblePower);
Init(bubbleNumberString);
}
public List <SummaryDHdata> GetHeatMapdata(string dataset)
{
List <SummaryDHdata> listResult = new List <SummaryDHdata>();
List <DataSetDate> datasets = GetListDataSetDate().ToList();
DataSetDate selecteddataset = datasets.Where(x => x.code.Equals(dataset)).FirstOrDefault();
var listtemp = rpGeneric2nd.FindByNativeSQL("select * from datahub_summary where schooltype = 32 and Year = " + selecteddataset.year + " and month = " + selecteddataset.month);
foreach (var itemrow in listtemp)
{
SummaryDHdata temp = new SummaryDHdata();
if (itemrow != null)
{
temp.year = Convert.ToInt16(itemrow[0].ToString());
temp.month = Convert.ToInt16(itemrow[1].ToString());
temp.seedcode = itemrow[2].ToString();
temp.listdata = new List <GenericData>();
temp.listdata.Add(new GenericData("Participating Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[4])));
temp.listdata.Add(new GenericData("Non-Participating Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[5])));
temp.listdata.Add(new GenericData("Unknown Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[6])));
temp.sdataset = datasets.Where(x => x.month.Equals(temp.month.ToString()) && x.year.Equals(temp.year.ToString())).FirstOrDefault();
listResult.Add(temp);
}
}
return(listResult.OrderBy(x => x.year).ThenBy(x => x.month).ToList());
}
public JsonResult GetData([System.Web.Http.FromUri] string sReportID) // get selected list of school's id
{
try
{
var ListReports = new[] { new { Code = "1", Name = "Cost Breakdown By Child" }, new { Code = "2", Name = "Cost Breakdown By Supplier" }, new { Code = "3", Name = "Cost Breakdown By Placement Category" }, new { Code = "4", Name = "Cost Breakdown By Service Type" } }.ToList();
Dictionary <string, string> DictPlacementCategory = GetDictPlacementCategory();
Dictionary <string, string> DictServiceType = GetDictServiceType();
var listChildAgreements = GetChildAgreement(rpGeneric2nd);
var listResults = new { };
object oResult = null;
// Cost Breakdown By Child
if (sReportID.Equals("1"))
{
oResult = new
{
ListReports = ListReports,
ListReportSelected = ListReports.Where(x => x.Code.Equals(sReportID)).First(),
listResults = listChildAgreements.GroupBy(x => x.client_id).Select(r => new
{
Client_id = r.First().client_id,
Count = r.Count().ToString(),
CountClosed = r.Where(x => x.payattension.Equals("")).Count().ToString(),
CountOngoing = r.Where(x => x.payattension.Equals("**")).Count().ToString(),
CountChild = r.Count().ToString(),
TotalCost = NumberFormatHelper.FormatNumber(r.Sum(xl => xl.actual_cost), 2).ToString(),
ClosedCost = NumberFormatHelper.FormatNumber(r.Where(x => x.payattension.Equals("")).Sum(xl => xl.actual_cost), 2).ToString(),
OngoingCost = NumberFormatHelper.FormatNumber(r.Where(x => x.payattension.Equals("**")).Sum(xl => xl.actual_cost), 2).ToString()
}).OrderByDescending(x => x.TotalCost).ToList(),
ShowCountChild = false
};
}
else if (sReportID.Equals("2"))
{
oResult = new
{
ListReports = ListReports,
ListReportSelected = ListReports.Where(x => x.Code.Equals(sReportID)).First(),
listResults = listChildAgreements.GroupBy(x => x.supplier_code).Select(r => new
{
Client_id = r.First().supplier_name,
Count = r.Count().ToString(),
CountClosed = r.Where(x => x.payattension.Equals("")).Count().ToString(),
CountOngoing = r.Where(x => x.payattension.Equals("**")).Count().ToString(),
CountChild = r.Select(x => x.client_id).Distinct().Count().ToString(), //grp.Select(x => x.SomeField).Distinct().Count()
TotalCost = NumberFormatHelper.FormatNumber(r.Sum(xl => xl.actual_cost), 2).ToString(),
ClosedCost = NumberFormatHelper.FormatNumber(r.Where(x => x.payattension.Equals("")).Sum(xl => xl.actual_cost), 2).ToString(),
OngoingCost = NumberFormatHelper.FormatNumber(r.Where(x => x.payattension.Equals("**")).Sum(xl => xl.actual_cost), 2).ToString()
}).OrderByDescending(x => x.TotalCost).ToList(),
ShowCountChild = true
};
}
else if (sReportID.Equals("3"))
{
oResult = new
{
ListReports = ListReports,
ListReportSelected = ListReports.Where(x => x.Code.Equals(sReportID)).First(),
listResults = listChildAgreements.GroupBy(x => x.Placement_Category).Select(r => new
{
Client_id = DictPlacementCategory[r.First().Placement_Category],
Count = r.Count().ToString(),
CountChild = r.Select(x => x.client_id).Distinct().Count().ToString(),
CountClosed = r.Where(x => x.payattension.Equals("")).Count().ToString(),
CountOngoing = r.Where(x => x.payattension.Equals("**")).Count().ToString(),
TotalCost = NumberFormatHelper.FormatNumber(r.Sum(xl => xl.actual_cost), 2).ToString(),
ClosedCost = NumberFormatHelper.FormatNumber(r.Where(x => x.payattension.Equals("")).Sum(xl => xl.actual_cost), 2).ToString(),
OngoingCost = NumberFormatHelper.FormatNumber(r.Where(x => x.payattension.Equals("**")).Sum(xl => xl.actual_cost), 2).ToString()
// NumberFormatHelper.FormatNumber(r.Sum(xl => xl.actual_cost), 1).ToString()
}).OrderByDescending(x => x.TotalCost).ToList(),
ShowCountChild = true,
PieChartCost = GetPieChartCostByPlacementCategory(listChildAgreements),
PieChartAgreements = GetPieChartNoAgreementByPlacementCategory(listChildAgreements),
BarChartCost = GetChartCostByPlacementCategory(listChildAgreements),
BarChartNoAgreement = GetChartNoAgreementsByPlacementCategory(listChildAgreements)
};
}
else
{
oResult = new
{
ListReports = ListReports,
ListReportSelected = ListReports.Where(x => x.Code.Equals(sReportID)).First(),
listResults = listChildAgreements.GroupBy(x => x.Service_Type).Select(r => new
{
Client_id = DictServiceType[r.First().Service_Type],
Count = r.Count().ToString(),
CountChild = r.Select(x => x.client_id).Distinct().Count().ToString(),
CountClosed = r.Where(x => x.payattension.Equals("")).Count().ToString(),
CountOngoing = r.Where(x => x.payattension.Equals("**")).Count().ToString(),
TotalCost = NumberFormatHelper.FormatNumber(r.Sum(xl => xl.actual_cost), 2).ToString(),
ClosedCost = NumberFormatHelper.FormatNumber(r.Where(x => x.payattension.Equals("")).Sum(xl => xl.actual_cost), 2).ToString(),
OngoingCost = NumberFormatHelper.FormatNumber(r.Where(x => x.payattension.Equals("**")).Sum(xl => xl.actual_cost), 2).ToString()
// Cost = NumberFormatHelper.FormatNumber(r.Sum(xl => xl.actual_cost), 1).ToString()
// NumberFormatHelper.FormatNumber(r.Sum(xl => xl.actual_cost), 1).ToString()
}).OrderByDescending(x => x.TotalCost).ToList(),
ShowCountChild = true,
PieChartCost = GetPieChartCostByServiceType(listChildAgreements),
PieChartAgreements = GetPieChartNoAgreementByServiceType(listChildAgreements),
BarChartCost = GetChartCostByServiceType(listChildAgreements),
BarChartNoAgreement = GetChartNoAgreementsByServiceType(listChildAgreements)
};
}
return(Json(oResult, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(ThrowJsonError(ex));
}
}
