private List <EthnicObj> GetdatabyZonecode(string pZonecode)
{
Console.Write("GetdatabyZonecode ==> ");
List <EthnicObj> listtemp = new List <EthnicObj>();
EthnicObj tempNationalObj = new EthnicObj();
string query = " Select t1.EthnicBackground, t1.Gender, (Count(*)* 100 /";
//query += " (Select Count(*) from sch_Student_t t1 INNER JOIN sch_PrimarySchool_t t2 on t1.SeedCode = t2.SeedCode where t2.Name in (\"" + mSchoolname + "\")))";
//query += " From sch_Student_t t1 INNER JOIN sch_PrimarySchool_t t2 on t1.SeedCode = t2.SeedCode where t2.Name in (\"" + mSchoolname + "\") Group By NationalIdentity, Gender ";
query += " (Select Count(*) from sch_Student_t t1 INNER JOIN CityShire t2 on t1.PostCode = t2.PostCode where DataZone in (\"" + pZonecode + "\") )) ";
query += " From sch_Student_t t1 INNER JOIN CityShire t2 on t1.PostCode = t2.PostCode where DataZone in (\"" + pZonecode + "\") Group By EthnicBackground, Gender";
//Select Count(*) from sch_Student_t t1 INNER JOIN sch_PrimarySchool_t t2 on t1.SeedCode = t2.SeedCode where t2.Name in ('Brimmond Primary School')
var listResult = rpGeneric.FindByNativeSQL(query);
if (listResult != null)
{
var DistinctItems = listResult.GroupBy(x => x.ElementAt(0).ToString()).ToList();
foreach (var Nationalcode in DistinctItems)
{
var templist2 = (from a in listResult where a.ElementAt(0).ToString().Equals(Nationalcode.Key) select a).ToList();
if (templist2.Count != 0)
{
tempNationalObj = new EthnicObj();
foreach (var itemRow in templist2)
{
tempNationalObj.EthinicCode = Convert.ToString(itemRow[0]);
tempNationalObj.EthinicName = GetDicEhtnicBG().ContainsKey(tempNationalObj.EthinicCode) ? GetDicEhtnicBG()[tempNationalObj.EthinicCode] : "NO NAME";
//tempEthnicObj.EthnicGender = Convert.ToString(itemRow[1]);
if ("F".Equals(Convert.ToString(itemRow[1])))
{
tempNationalObj.PercentageFemaleAllSchool = Convert.ToDouble(itemRow[2]);
}
else
{
tempNationalObj.PercentageMaleAllSchool = Convert.ToDouble(itemRow[2]);
}
}
listtemp.Add(tempNationalObj);
}
}
}
foreach (var itemRow in listtemp)
{
tempNationalObj = itemRow;
tempNationalObj.PercentageAllSchool = tempNationalObj.PercentageFemaleAllSchool + tempNationalObj.PercentageMaleAllSchool;
}
return(listtemp);
}
private List <EthnicObj> GetdatabySchCode(int pSchcode)
{
Console.Write("GetdatabyPostcode ==> ");
List <EthnicObj> listtemp = new List <EthnicObj>();
EthnicObj tempNationalObj = new EthnicObj();
//% for Specific Area like AB21
var listResult = rpGeneric.FindByNativeSQL("Select EthnicBackground,Gender, (Count(*)* 100 / (Select Count(*) From sch_Student_t_v2 where Seedcode =" + pSchcode + " )) From sch_Student_t_v2 where Seedcode =" + pSchcode + " Group By EthnicBackground, Gender ");
if (listResult != null)
{
var DistinctItems = listResult.GroupBy(x => x.ElementAt(0).ToString()).ToList();
foreach (var Nationalcode in DistinctItems)
{
var templist2 = (from a in listResult where a.ElementAt(0).ToString().Equals(Nationalcode.Key) select a).ToList();
if (templist2.Count != 0)
{
tempNationalObj = new EthnicObj();
foreach (var itemRow in templist2)
{
tempNationalObj.EthinicCode = Convert.ToString(itemRow[0]);
tempNationalObj.EthinicName = GetDicEhtnicBG().ContainsKey(tempNationalObj.EthinicCode) ? GetDicEhtnicBG()[tempNationalObj.EthinicCode] : "NO NAME";
//tempEthnicObj.EthnicGender = Convert.ToString(itemRow[1]);
if ("F".Equals(Convert.ToString(itemRow[1])))
{
tempNationalObj.PercentageFemaleAllSchool = Convert.ToDouble(itemRow[2]);
}
else
{
tempNationalObj.PercentageMaleAllSchool = Convert.ToDouble(itemRow[2]);
}
}
listtemp.Add(tempNationalObj);
}
}
}
foreach (var itemRow in listtemp)
{
tempNationalObj = itemRow;
tempNationalObj.PercentageAllSchool = tempNationalObj.PercentageFemaleAllSchool + tempNationalObj.PercentageMaleAllSchool;
}
return(listtemp);
}
//private readonly IGenericRepository rpGeneric;
//public BaseSchoolProfileController(IGenericRepository rpGeneric)
//{
// this.rpGeneric = rpGeneric;
//}
//public BaseSchoolProfileController()
//{
// // this.rpGeneric = rpGeneric;
//}
protected List <EthnicObj> GetEthnicityDatabySchoolname(IGenericRepository rpGeneric, string mSchoolname)
{
Console.Write("GetEthnicityData in BaseSchoolProfileController==> ");
//List<EthnicObj> listDataseries = new List<EthnicObj>();
List <EthnicObj> listtemp = new List <EthnicObj>();
//List<EthnicObj> listtemp1 = new List<EthnicObj>();
EthnicObj tempEthnicObj = new EthnicObj();
//% for All school
var listResult = rpGeneric.FindByNativeSQL("Select EthnicBackground,Gender,(Count(EthnicBackground)* 100 / (Select Count(*) From sch_Student_t)) From sch_Student_t Group By EthnicBackground, Gender ");
if (listResult != null)
{
var DistinctItems = listResult.GroupBy(x => x.ElementAt(0).ToString()).ToList();
foreach (var Ethniccode in DistinctItems)
{
var templist2 = (from a in listResult where a.ElementAt(0).ToString().Equals(Ethniccode.Key) select a).ToList();
if (templist2.Count != 0)
{
tempEthnicObj = new EthnicObj();
foreach (var itemRow in templist2)
{
tempEthnicObj.EthinicCode = Convert.ToString(itemRow[0]);
tempEthnicObj.EthinicName = GetDicEhtnicBG().ContainsKey(tempEthnicObj.EthinicCode) ? GetDicEhtnicBG()[tempEthnicObj.EthinicCode] : "NO NAME";
//tempEthnicObj.EthnicGender = Convert.ToString(itemRow[1]);
if ("F".Equals(Convert.ToString(itemRow[1])))
{
tempEthnicObj.PercentageFemaleAllSchool = Convert.ToDouble(itemRow[2]);
}
else
{
tempEthnicObj.PercentageMaleAllSchool = Convert.ToDouble(itemRow[2]);
}
}
listtemp.Add(tempEthnicObj);
}
}
}
//% for specific schoolname
string query = " Select EthnicBackground,Gender, (Count(EthnicBackground)* 100 /";
query += " (Select Count(*) from sch_Student_t t1 INNER JOIN sch_PrimarySchool_t t2 on t1.SeedCode = t2.SeedCode where t2.Name in (\"" + mSchoolname + "\")))";
query += " From sch_Student_t t1 INNER JOIN sch_PrimarySchool_t t2 on t1.SeedCode = t2.SeedCode where t2.Name in (\"" + mSchoolname + "\") Group By EthnicBackground, Gender ";
listResult = rpGeneric.FindByNativeSQL(query);
if (listResult != null)
{
// need to select only the Ethniccode that appear for this specific school
//var DistinctItems = listResult.GroupBy(x => x.ElementAt(0).ToString()).ToList();
//foreach (var Ethniccode in DistinctItems)
//{
// tempEthnicObj = listtemp.Find(x => x.EthinicCode.Equals(Ethniccode.Key));
// if (tempEthnicObj != null)
// listDataseries.Add(tempEthnicObj);
//}
foreach (var itemRow in listResult)
{
var x = (from a in listtemp where a.EthinicCode.Equals(Convert.ToString(itemRow[0])) select a).ToList();
if (x.Count != 0)
{
tempEthnicObj = x[0];
if ("F".Equals(Convert.ToString(itemRow[1])))
{
tempEthnicObj.PercentageFemaleInSchool = Convert.ToDouble(itemRow[2]);
}
else
{
tempEthnicObj.PercentageMaleInSchool = Convert.ToDouble(itemRow[2]);
}
//listDataseries.Add(tempEthnicObj);
}
}
}
foreach (var itemRow in listtemp)
{
tempEthnicObj = itemRow;
tempEthnicObj.PercentageInSchool = tempEthnicObj.PercentageFemaleInSchool + tempEthnicObj.PercentageMaleInSchool;
tempEthnicObj.PercentageAllSchool = tempEthnicObj.PercentageFemaleAllSchool + tempEthnicObj.PercentageMaleAllSchool;
}
return(listtemp);
}