private List <NationalityObj> GetdatabyZonecode(string pZonecode)
{
Console.Write("GetdatabyZonecode ==> ");
List <NationalityObj> listtemp = new List <NationalityObj>();
NationalityObj tempNationalObj = new NationalityObj();
string query = " Select t1.LevelOfEnglish, 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 LevelOfEnglish, 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 NationalityObj();
foreach (var itemRow in templist2)
{
tempNationalObj.IdentityCode = Convert.ToString(itemRow[0]);
tempNationalObj.IdentityName = GetDicLevelEnglish().ContainsKey(tempNationalObj.IdentityCode) ? GetDicLevelEnglish()[tempNationalObj.IdentityCode] : "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 <NationalityObj> GetdatabySchCode(int pSchcode)
{
Console.Write("GetdatabyPostcode ==> ");
List <NationalityObj> listtemp = new List <NationalityObj>();
NationalityObj tempNationalObj = new NationalityObj();
//% for Specific Area like AB21
//var listResult = rpGeneric.FindByNativeSQL("Select NationalIdentity,Gender, (Count(NationalIdentity)* 100 / (Select Count(*) From sch_Student_t_v2 where PostOut in (\"" + pPostcode + "\") )) From sch_Student_t_v2 where PostOut in (\"" + pPostcode + "\") Group By NationalIdentity, Gender ");
var listResult = rpGeneric.FindByNativeSQL("Select NationalIdentity,Gender, (Count(NationalIdentity)* 100 / (Select Count(*) From sch_Student_t_v2 where Seedcode =" + pSchcode + ")) From sch_Student_t_v2 where Seedcode =" + pSchcode + " Group By NationalIdentity, 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 NationalityObj();
foreach (var itemRow in templist2)
{
tempNationalObj.IdentityCode = Convert.ToString(itemRow[0]);
tempNationalObj.IdentityName = GetDicNational().ContainsKey(tempNationalObj.IdentityCode) ? GetDicNational()[tempNationalObj.IdentityCode] : "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);
}
protected List <NationalityObj> GetLevelENDatabySchoolname(IGenericRepository rpGeneric, string mSchoolname)
{
Console.Write("GetLevelENDatabySchoolname ==> ");
//List<NationalityObj> listDataseries = new List<NationalityObj>();
List <NationalityObj> listtemp = new List <NationalityObj>();
NationalityObj tempNationalObj = new NationalityObj();
//% for All school
var listResult = rpGeneric.FindByNativeSQL("Select LevelOfEnglish,Gender, (Count(*)* 100 / (Select Count(*) From sch_Student_t)) From sch_Student_t Group By LevelOfEnglish, Gender ");
if (listResult != null)
{
var DistinctItems = listResult.GroupBy(x => x.ElementAt(0).ToString()).ToList();
foreach (var LevelENcode in DistinctItems)
{
var templist2 = (from a in listResult where a.ElementAt(0).ToString().Equals(LevelENcode.Key) select a).ToList();
if (templist2.Count != 0)
{
tempNationalObj = new NationalityObj();
foreach (var itemRow in templist2)
{
tempNationalObj.IdentityCode = Convert.ToString(itemRow[0]);
tempNationalObj.IdentityName = GetDicLevelEnglish().ContainsKey(tempNationalObj.IdentityCode) ? GetDicLevelEnglish()[tempNationalObj.IdentityCode] : "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);
}
}
}
//% for specific schoolname
string query = " Select LevelOfEnglish, 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 LevelOfEnglish, Gender ";
listResult = rpGeneric.FindByNativeSQL(query);
if (listResult != null)
{
foreach (var itemRow in listResult)
{
var x = (from a in listtemp where a.IdentityCode.Equals(Convert.ToString(itemRow[0])) select a).ToList();
if (x.Count != 0)
{
tempNationalObj = x[0];
if ("F".Equals(Convert.ToString(itemRow[1])))
{
tempNationalObj.PercentageFemaleInSchool = Convert.ToDouble(itemRow[2]);
}
else
{
tempNationalObj.PercentageMaleInSchool = Convert.ToDouble(itemRow[2]);
}
}
}
}
foreach (var itemRow in listtemp)
{
tempNationalObj = itemRow;
tempNationalObj.PercentageInSchool = tempNationalObj.PercentageFemaleInSchool + tempNationalObj.PercentageMaleInSchool;
tempNationalObj.PercentageAllSchool = tempNationalObj.PercentageFemaleAllSchool + tempNationalObj.PercentageMaleAllSchool;
}
return(listtemp);
}
