public List <student_account_info> GetStudent_Account_Infos(Int32 stdid)
{
List <student_account_info> alist = new List <student_account_info>();
string q1 = "SELECT t2.stdId,t2.class,t2.pdate as Month,t2.pay as Payable,t1.paid as Paid,t2.pay-t1.paid as Dues,t1.paiddate FROM (select Stdid,FORMAT ( pdate , 'MM-yyyy' ) as pdate,pay,class from payable) t2 left JOIN(select t1.stdId,t1.paiddate,sum(t1.paid) as paid from(select Stdid,FORMAT ( pmonth , 'MM-yyyy' ) as paiddate,paid,class from payment group by stdId,class,paid,pmonth) t1 group by t1.stdId,t1.paiddate) t1 ON t2.stdId=t1.stdId and t2.pdate=t1.paiddate where t2.stdId=" + stdid + " group by t2.stdId,t2.class,t2.pdate,t2.pay,t1.paid,t1.paiddate order by t1.paiddate";
SqlCommand cmd1 = new SqlCommand(q1, cn.GetConnection());
SqlDataReader reader = cmd1.ExecuteReader();
while (reader.Read())
{
student_account_info student_Account_Info = new student_account_info();
student_Account_Info.stdId = Convert.ToInt32(reader["stdId"].ToString());
student_Account_Info.Class = Convert.ToInt32(reader["class"].ToString());
// student_Account_Info.paidmonth = Convert.ToDateTime(reader["paidmonth"]);
student_Account_Info.pmonth = Convert.ToDateTime(reader["Month"] == DBNull.Value ? null : (DateTime?)Convert.ToDateTime(reader["month"]));
//object v = (reader.IsDBNull(3) ? null : reader["paidmonth"]);
//student_Account_Info.paidmonth = Convert.ToDateTime((DateTime?)v);
//employee.FirstName = sqlreader[indexFirstName] as string;
//employee.Age = sqlreader[indexAge] as int? ?? default(int);
// string s = reader["pmonth"].ToString();
student_Account_Info.Month = Convert.ToDateTime(reader["Month"].ToString());
student_Account_Info.payable = Convert.ToInt32(reader["payable"]);
//d = Convert.ToDouble(reader["paid"] as float? ?? default(float));
student_Account_Info.paid = reader["paid"] as Int32? ?? default(int);
student_Account_Info.dues = reader["dues"] as int? ?? default(int);
alist.Add(student_Account_Info);
}
return(alist);
}
public List <student_account_info> payment_history(int stdId)
{
List <student_account_info> info = new List <student_account_info>();
string q = "select Stdid,pmonth,paid,class,Recepts_no from payment where stdId=" + stdId + " group by Stdid,pmonth,paid,class,Recepts_no order by pmonth";
SqlCommand cmd = new SqlCommand(q, cn.GetConnection());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
student_account_info student = new student_account_info();
student.stdId = Convert.ToInt32(reader["stdId"].ToString());
student.pmonth = Convert.ToDateTime(reader["pmonth"] == DBNull.Value ? null : (DateTime?)Convert.ToDateTime(reader["pmonth"]));
student.paid = reader["paid"] as int? ?? default(int);
student.Class = reader["class"] as int? ?? default(int);
student.Recpt = Convert.ToInt32(reader["Recepts_no"].ToString());
info.Add(student);
}
return(info);
}