private List <StdStageObj> GetdatabyZonecode(string pZonecode)
{
Console.Write("GetdatabyZonecode ==> ");
List <StdStageObj> listtemp = new List <StdStageObj>();
StdStageObj tempNationalObj = new StdStageObj();
string query = " Select t1.StudentStage, 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 StudentStage, 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 StdStageObj();
foreach (var itemRow in templist2)
{
tempNationalObj.StageCode = Convert.ToString(itemRow[0]);
//tempNationalObj.EthinicName = GetDicNational().ContainsKey(tempNationalObj.EthinicCode) ? GetDicNational()[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 <StdStageObj> GetdatabySchCode(int pSchcode)
{
Console.Write("GetdatabyPostcode ==> ");
List <StdStageObj> listtemp = new List <StdStageObj>();
StdStageObj tempNationalObj = new StdStageObj();
//% for Specific Area like AB21
var listResult = rpGeneric.FindByNativeSQL("Select StudentStage,Gender, (Count(*)* 100 / (Select Count(*) From sch_Student_t_v2 where Seedcode =" + pSchcode + " )) From sch_Student_t_v2 where Seedcode =" + pSchcode + " Group By StudentStage, 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 StdStageObj();
foreach (var itemRow in templist2)
{
tempNationalObj.StageCode = 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);
}
protected List <StdStageObj> GetStudentStageDatabySchoolname(IGenericRepository rpGeneric, string mSchoolname)
{
Console.Write("GetStdStageDatabySchoolname in BaseSchoolProfileController==> ");
List <StdStageObj> listDataseries = new List <StdStageObj>();
List <StdStageObj> listtemp = new List <StdStageObj>();
List <StdStageObj> listtemp1 = new List <StdStageObj>();
StdStageObj tempStdStageObj = new StdStageObj();
//% for All school
//var listResult = rpGeneric.FindByNativeSQL("Select StudentStage,Gender,(Count(*)* 100 / (Select Count(*) From test_3)) From test_3 Group By StudentStage, Gender ");
var listResult = rpGeneric.FindByNativeSQL("Select StudentStage,Gender,(Count(*)* 100 / (Select Count(*) From sch_Student_t)) From sch_Student_t Group By StudentStage, Gender ");
if (listResult != null)
{
var DistinctItems = listResult.GroupBy(x => x.ElementAt(0).ToString()).ToList();
foreach (var StdStagecode in DistinctItems)
{
var templist2 = (from a in listResult where a.ElementAt(0).ToString().Equals(StdStagecode.Key) select a).ToList();
if (templist2.Count != 0)
{
tempStdStageObj = new StdStageObj();
foreach (var itemRow in templist2)
{
tempStdStageObj.StageCode = Convert.ToString(itemRow[0]);
//tempEthnicObj.EthnicGender = Convert.ToString(itemRow[1]);
if ("F".Equals(Convert.ToString(itemRow[1])))
{
tempStdStageObj.PercentageFemaleAllSchool = Convert.ToDouble(itemRow[2]);
}
else
{
tempStdStageObj.PercentageMaleAllSchool = Convert.ToDouble(itemRow[2]);
}
}
listtemp.Add(tempStdStageObj);
}
}
}
//% for specific schoolname
// old query
//string query = " Select StudentStage,Gender, (Count(*)* 100 /";
//query += " (Select Count(*) From test_3 where Name in (\"" + mSchoolname + "\")))";
//query += " From test_3 where Name in (\"" + mSchoolname + "\") Group By StudentStage, Gender ";
string query = " Select StudentStage,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 StudentStage, 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 StdStagecode in DistinctItems)
{
tempStdStageObj = listtemp.Find(x => x.StageCode.Equals(StdStagecode.Key));
if (tempStdStageObj != null)
{
listDataseries.Add(tempStdStageObj);
}
}
foreach (var itemRow in listResult)
{
var x = (from a in listtemp where a.StageCode.Equals(Convert.ToString(itemRow[0])) select a).ToList();
if (x.Count != 0)
{
tempStdStageObj = x[0];
if ("F".Equals(Convert.ToString(itemRow[1])))
{
tempStdStageObj.PercentageFemaleInSchool = Convert.ToDouble(itemRow[2]);
}
else
{
tempStdStageObj.PercentageMaleInSchool = Convert.ToDouble(itemRow[2]);
}
//listDataseries.Add(tempEthnicObj);
}
}
}
foreach (var itemRow in listDataseries)
{
tempStdStageObj = itemRow;
tempStdStageObj.PercentageInSchool = tempStdStageObj.PercentageFemaleInSchool + tempStdStageObj.PercentageMaleInSchool;
tempStdStageObj.PercentageAllSchool = tempStdStageObj.PercentageFemaleAllSchool + tempStdStageObj.PercentageMaleAllSchool;
}
return(listDataseries);
}