protected List <CurriculumObj> GetCurriculumDatabySchoolname(IGenericRepository rpGeneric, string mSchoolname, string colname)
{
Console.Write("GetCurriculumDatabySchoolname ==> ");
List <CurriculumObj> listtemp = new List <CurriculumObj>();
CurriculumObj tempCurriculumObj = new CurriculumObj();
//% for All school
var listTempStage = new List <string>()
{
"P1", "P2", "P3", "P4", "P5", "P6", "P7"
};
foreach (var item in listTempStage)
{
listtemp.Add(new CurriculumObj(item, "M"));
listtemp.Add(new CurriculumObj(item, "F"));
listtemp.Add(new CurriculumObj(item, "T"));
}
//get stage and gender from database but it is included SP stage
//var listResult = rpGeneric.FindByNativeSQL("Select distinct StudentStage,Gender From sch_Student_t Group By StudentStage, Gender ");
//if (listResult != null)
//{
// foreach (var itemRow in listResult)
// {
// listtemp.Add(new CurriculumObj(Convert.ToString(itemRow[0]), Convert.ToString(itemRow[1])));
// }
//}
//List<CurriculumObj> listtemp = listtempdata.OrderBy(x => x.stage).ToList();
string query = " Select StudentStage, Gender, " + colname + ", Count(*)";
query += " From test_3 where Name in (\"" + mSchoolname + "\") Group By StudentStage, Gender," + colname;
var listResult = rpGeneric.FindByNativeSQL(query);
if (listResult != null)
{
foreach (var itemRow in listResult)
{
var x = (from a in listtemp where a.stage.Equals(Convert.ToString(itemRow[0])) && a.gender.Equals(Convert.ToString(itemRow[1])) select a).ToList();
if (x.Count != 0)
{
tempCurriculumObj = x[0];
if (itemRow[2] == null)
{
tempCurriculumObj.blank = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("Early"))
{
tempCurriculumObj.early = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("Early Consolidating"))
{
tempCurriculumObj.earlyconsolidating = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("Early Developing"))
{
tempCurriculumObj.earlydeveloping = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("Early Secure"))
{
tempCurriculumObj.earlysecure = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("First Consolidating"))
{
tempCurriculumObj.firstconsolidating = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("First Developing"))
{
tempCurriculumObj.firstdeveloping = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("First Secure"))
{
tempCurriculumObj.firstsecure = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("Second Consolidating"))
{
tempCurriculumObj.secondconsolidating = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("Second Developing"))
{
tempCurriculumObj.seconddeveloping = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("Second Secure"))
{
tempCurriculumObj.secondsecure = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("Third Consolidating"))
{
tempCurriculumObj.thirdconsolidating = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("Third Developing"))
{
tempCurriculumObj.thirddeveloping = Convert.ToDouble(itemRow[3]);
}
else if (Convert.ToString(itemRow[2]).Equals("Third Secure"))
{
tempCurriculumObj.thirdsecure = Convert.ToDouble(itemRow[3]);
}
}
}
}
query = " Select StudentStage, Gender, Count(*) From test_3 where Name in (\"" + mSchoolname + "\") Group by StudentStage ,Gender";
listResult = rpGeneric.FindByNativeSQL(query);
if (listResult != null)
{
foreach (var itemRow in listResult)
{
var x = (from a in listtemp where a.stage.Equals(Convert.ToString(itemRow[0])) && a.gender.Equals(Convert.ToString(itemRow[1])) select a).ToList();
if (x.Count != 0)
{
tempCurriculumObj = x[0];
tempCurriculumObj.sumpupils = Convert.ToDouble(itemRow[2]);
}
}
}
//query = " Select StudentStage, " + colname + ", Count(*)";
//query += " From test_3 where DataZone in (\"" + pZonecode + "\") Group By StudentStage," + colname;
query = " Select StudentStage, " + colname + ", Count(*)";
query += " From test_3 where Name in (\"" + mSchoolname + "\") Group By StudentStage," + colname;
listResult = rpGeneric.FindByNativeSQL(query);
if (listResult != null)
{
foreach (var itemRow in listResult)
{
var x = (from a in listtemp where a.stage.Equals(Convert.ToString(itemRow[0])) && a.gender.Equals("T") select a).ToList();
if (x.Count != 0)
{
tempCurriculumObj = x[0];
if (itemRow[1] == null)
{
tempCurriculumObj.blank = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Early"))
{
tempCurriculumObj.early = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Early Consolidating"))
{
tempCurriculumObj.earlyconsolidating = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Early Developing"))
{
tempCurriculumObj.earlydeveloping = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Early Secure"))
{
tempCurriculumObj.earlysecure = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("First Consolidating"))
{
tempCurriculumObj.firstconsolidating = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("First Developing"))
{
tempCurriculumObj.firstdeveloping = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("First Secure"))
{
tempCurriculumObj.firstsecure = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Second Consolidating"))
{
tempCurriculumObj.secondconsolidating = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Second Developing"))
{
tempCurriculumObj.seconddeveloping = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Second Secure"))
{
tempCurriculumObj.secondsecure = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Third Consolidating"))
{
tempCurriculumObj.thirdconsolidating = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Third Developing"))
{
tempCurriculumObj.thirddeveloping = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Third Secure"))
{
tempCurriculumObj.thirdsecure = Convert.ToDouble(itemRow[2]);
}
}
}
// listtemp.Add(tempCurriculumObj);
}
query = " Select StudentStage, Count(*) From test_3 where Name in (\"" + mSchoolname + "\") Group by StudentStage";
listResult = rpGeneric.FindByNativeSQL(query);
if (listResult != null)
{
foreach (var itemRow in listResult)
{
var x = (from a in listtemp where a.stage.Equals(Convert.ToString(itemRow[0])) && a.gender.Equals("T") select a).ToList();
if (x.Count != 0)
{
tempCurriculumObj = x[0];
tempCurriculumObj.sumpupils = Convert.ToDouble(itemRow[1]);
}
}
}
List <CurriculumObj> listtemp1 = listtemp.OrderBy(x => x.stage).ToList();
foreach (var itemRow in listtemp1)
{
tempCurriculumObj = itemRow;
tempCurriculumObj.blank = (tempCurriculumObj.blank * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.early = (tempCurriculumObj.early * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.earlysecure = (tempCurriculumObj.earlysecure * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.earlyconsolidating = (tempCurriculumObj.earlyconsolidating * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.earlydeveloping = (tempCurriculumObj.earlydeveloping * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.firstdeveloping = (tempCurriculumObj.firstdeveloping * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.firstconsolidating = (tempCurriculumObj.firstconsolidating * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.firstsecure = (tempCurriculumObj.firstsecure * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.seconddeveloping = (tempCurriculumObj.seconddeveloping * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.secondconsolidating = (tempCurriculumObj.secondconsolidating * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.secondsecure = (tempCurriculumObj.secondsecure * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.thirddeveloping = (tempCurriculumObj.thirddeveloping * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.thirdconsolidating = (tempCurriculumObj.thirdconsolidating * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.thirdsecure = (tempCurriculumObj.thirdsecure * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.grandtotal = tempCurriculumObj.blank + tempCurriculumObj.early + tempCurriculumObj.earlysecure + tempCurriculumObj.earlydeveloping + tempCurriculumObj.earlyconsolidating + tempCurriculumObj.firstsecure + tempCurriculumObj.firstdeveloping + tempCurriculumObj.firstconsolidating + tempCurriculumObj.secondsecure + tempCurriculumObj.seconddeveloping + tempCurriculumObj.secondconsolidating + tempCurriculumObj.thirddeveloping + tempCurriculumObj.thirdconsolidating + tempCurriculumObj.thirdsecure;
}
return(listtemp1);
}
private List <CurriculumObj> GetdatabyZonecode(string pZonecode, string colname)
{
Console.Write("GetdatabyZonecode ==> ");
List <CurriculumObj> listtemp = new List <CurriculumObj>();
CurriculumObj tempCurriculumObj = new CurriculumObj();
var listTempStage = new List <string>()
{
"P1", "P2", "P3", "P4", "P5", "P6", "P7"
};
foreach (var item in listTempStage)
{
listtemp.Add(new CurriculumObj(item, "T"));
}
string query = " Select StudentStage, " + colname + ", Count(*)";
query += " From test_3 where DataZone in (\"" + pZonecode + "\") Group By StudentStage," + colname;
var listResult = rpGeneric.FindByNativeSQL(query);
if (listResult != null)
{
foreach (var itemRow in listResult)
{
var x = (from a in listtemp where a.stage.Equals(Convert.ToString(itemRow[0])) select a).ToList();
if (x.Count != 0)
{
tempCurriculumObj = x[0];
if (itemRow[1] == null)
{
tempCurriculumObj.blank = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Early"))
{
tempCurriculumObj.early = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Early Consolidating"))
{
tempCurriculumObj.earlyconsolidating = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Early Developing"))
{
tempCurriculumObj.earlydeveloping = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Early Secure"))
{
tempCurriculumObj.earlysecure = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("First Consolidating"))
{
tempCurriculumObj.firstconsolidating = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("First Developing"))
{
tempCurriculumObj.firstdeveloping = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("First Secure"))
{
tempCurriculumObj.firstsecure = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Second Consolidating"))
{
tempCurriculumObj.secondconsolidating = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Second Developing"))
{
tempCurriculumObj.seconddeveloping = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Second Secure"))
{
tempCurriculumObj.secondsecure = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Third Consolidating"))
{
tempCurriculumObj.thirdconsolidating = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Third Developing"))
{
tempCurriculumObj.thirddeveloping = Convert.ToDouble(itemRow[2]);
}
else if (Convert.ToString(itemRow[1]).Equals("Third Secure"))
{
tempCurriculumObj.thirdsecure = Convert.ToDouble(itemRow[2]);
}
}
}
// listtemp.Add(tempCurriculumObj);
}
query = " Select StudentStage, Count(*) From test_3 where DataZone in (\"" + pZonecode + "\") Group by StudentStage";
listResult = rpGeneric.FindByNativeSQL(query);
if (listResult != null)
{
foreach (var itemRow in listResult)
{
var x = (from a in listtemp where a.stage.Equals(Convert.ToString(itemRow[0])) select a).ToList();
if (x.Count != 0)
{
tempCurriculumObj = x[0];
tempCurriculumObj.sumpupils = Convert.ToDouble(itemRow[1]);
}
}
}
List <CurriculumObj> listtemp1 = listtemp.OrderBy(x => x.stage).ToList();
foreach (var itemRow in listtemp1)
{
tempCurriculumObj = itemRow;
tempCurriculumObj.blank = (tempCurriculumObj.blank * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.early = (tempCurriculumObj.early * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.earlysecure = (tempCurriculumObj.earlysecure * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.earlyconsolidating = (tempCurriculumObj.earlyconsolidating * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.earlydeveloping = (tempCurriculumObj.earlydeveloping * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.firstdeveloping = (tempCurriculumObj.firstdeveloping * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.firstconsolidating = (tempCurriculumObj.firstconsolidating * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.firstsecure = (tempCurriculumObj.firstsecure * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.seconddeveloping = (tempCurriculumObj.seconddeveloping * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.secondconsolidating = (tempCurriculumObj.secondconsolidating * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.secondsecure = (tempCurriculumObj.secondsecure * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.thirddeveloping = (tempCurriculumObj.thirddeveloping * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.thirdconsolidating = (tempCurriculumObj.thirdconsolidating * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.thirdsecure = (tempCurriculumObj.thirdsecure * 100) / tempCurriculumObj.sumpupils;
tempCurriculumObj.grandtotal = tempCurriculumObj.blank + tempCurriculumObj.early + tempCurriculumObj.earlysecure + tempCurriculumObj.earlydeveloping + tempCurriculumObj.earlyconsolidating + tempCurriculumObj.firstsecure + tempCurriculumObj.firstdeveloping + tempCurriculumObj.firstconsolidating + tempCurriculumObj.secondsecure + tempCurriculumObj.seconddeveloping + tempCurriculumObj.secondconsolidating + tempCurriculumObj.thirddeveloping + tempCurriculumObj.thirdconsolidating + tempCurriculumObj.thirdsecure;
}
return(listtemp1);
}
