private void AttendanceInPeriodReport(object pFromDate, object pToDate)
{
try
{
string vFromDate;
string vToDate;
if (pFromDate != null)
{
vFromDate = "'" + pFromDate.ToString() + "'";
}
else
{
vFromDate = "NULL";
}
if (pToDate != null)
{
vToDate = "'" + pToDate.ToString() + "'";
}
else
{
vToDate = "NULL";
}
string statement = "";
statement = "" + "\n" +
"SELECT Distinct TBL_Meetings_Details.Pers_ID AS ID_Pers "+ "\n" +
" , TBL_Meetings.TDate "+ "\n" +
" , TBL_Meetings.Title ,TBL_Meetings_Details.AttendanceTime "+ "\n" +
" ,TBL_city .CityDesc AS City "+ "\n" +
" , TBL_MainPerson.Name "+ "\n" +
" ,TBL_MainPerson.Mobile "+ "\n" +
" ,TBL_Area.AreaDesc "+ "\n" +
" ,TBL_Street.StreetDesc "+ "\n" +
" ,TBL_MainPerson.Phone "+ "\n" +
" ,TBL_MainPerson.FloorNum "+ "\n" +
" ,TBL_MainPerson.BuildingNum, TBL_DayTypes.[Desc] As DayType "+ "\n" +
"FROM TBL_Meetings "+ "\n" +
"LEFT JOIN TBL_Meetings_Details "+ "\n" +
"ON TBL_Meetings.ID = TBL_Meetings_Details.Meetings_ID "+ "\n" +
"Left JOIN dbo.TBL_DayTypes "+ "\n" +
"ON dbo.TBL_Meetings.ID = dbo.TBL_DayTypes.ID "+ "\n" +
"LEFT JOIN TBL_MainPerson "+ "\n" +
"ON TBL_MainPerson.ID = TBL_Meetings_Details.Pers_ID "+ "\n" +
"Left JOIN TBL_City "+ "\n" +
"ON TBL_MainPerson.City_ID = TBL_City.ID "+ "\n" +
"LEFT JOIN TBL_Area "+ "\n" +
"ON TBL_Area.ID = TBL_MainPerson.Area_ID "+ "\n" +
"LEFT JOIN TBL_Street "+ "\n" +
"ON TBL_Street.ID = TBL_MainPerson.Street_ID "+ "\n" +
"WHERE 1=1 "+ "\n" +
"AND (MONTH(TBL_Meetings.TDate) >= " + vFromDate + " Or " + vFromDate + " Is Null ) " + "\n" +
"AND (MONTH(TBL_Meetings.TDate) <= " + vToDate + " Or " + vToDate + " Is Null ) " + "\n" +
" ";
SqlConnection conn = new SqlConnection(BasicClass.vConectionString);
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = statement;
da.SelectCommand = cmd;
Datasets.DS_Meetings ds = new Datasets.DS_Meetings();
conn.Open();
da.Fill(ds.Tables[0]);
conn.Close();
ReportDocument reportDocument = new AttendanceInMonth();
reportDocument.Load("AttendanceInMonth.rpt");
reportDocument.SetDataSource(ds);
reportDocument.SetParameterValue("Adress", "المواظبة فى فترة ");
CR.ReportSource = reportDocument;
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.Message, this.Name, "AttendanceInPeriodReport");
}
}
private void NoAttendanceInPeriodReport(object pFromDate, object pToDate)
{
try
{
string vFromDate = "";
string vToDate = "";
if (pFromDate != null)
{
vFromDate = "'" + pFromDate.ToString() + "'";
}
else
{
vFromDate = "NULL";
}
if (pToDate != null)
{
vToDate = "'" + pToDate.ToString() + "'";
}
else
{
vToDate = "NULL";
}
string statement = "";
statement = "" + "\n" +
"SELECT TBL_MainPerson.ID AS ID_Pers "+ "\n" +
" ,TBL_MainPerson.Name , TBL_MainPerson.Mobile "+ "\n" +
" ,TBL_city .CityDesc AS City "+ "\n" +
" ,TBL_Area.AreaDesc "+ "\n" +
" ,TBL_Church.ChurchName "+ "\n" +
" , TBL_MainPerson.BirthDate "+ "\n" +
" ,TBL_Street.StreetDesc "+ "\n" +
" ,TBL_MainPerson.BuildingNum "+ "\n" +
" ,TBL_MainPerson.FloorNum "+ "\n" +
"FROM TBL_MainPerson "+ "\n" +
"INNER JOIN TBL_Church "+ "\n" +
"ON TBL_Church.ID = TBL_MainPerson.Church_ID "+ "\n" +
"INNER JOIN TBL_City "+ "\n" +
"ON TBL_MainPerson.City_ID = TBL_City.ID "+ "\n" +
"INNER JOIN TBL_Area "+ "\n" +
"ON TBL_Area.ID = TBL_MainPerson.Area_ID "+ "\n" +
"INNER JOIN TBL_Street "+ "\n" +
"ON TBL_Street.ID = TBL_MainPerson.Street_ID "+ "\n" +
"WHERE TBL_MainPerson.ID "+ "\n" +
"NOT IN ( "+ "\n" +
"SELECT Pers_ID "+ "\n" +
"from TBL_Meetings "+ "\n" +
"INNER JOIN TBL_Meetings_Details "+ "\n" +
"ON TBL_Meetings.ID = TBL_Meetings_Details.Meetings_ID "+ "\n" +
"WHERE (MONTH(TBL_Meetings.TDate) >= " + vFromDate + " Or " + vFromDate + " Is Null ) " + "\n" +
"AND (MONTH(TBL_Meetings.TDate) <= " + vToDate + " Or " + vToDate + " Is Null ) " + "\n" +
" ) ";
SqlConnection conn = new SqlConnection(BasicClass.vConectionString);
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = statement;
da.SelectCommand = cmd;
Datasets.DS_Meetings ds = new Datasets.DS_Meetings();
conn.Open();
da.Fill(ds.Tables[0]);
conn.Close();
ReportDocument reportDocument = new ReportNoAttendanceInMonth();
reportDocument.Load("ReportNoAttendanceInMonth.rpt");
reportDocument.SetDataSource(ds);
reportDocument.SetParameterValue("Adress", "لم يحضر فى فترة ");
CR.ReportSource = reportDocument;
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.Message, this.Name, "NoAttendanceInPeriodReport");
}
}
//------------------------------------------------------
//--------------------------------------------------------------
#region Meetings Reports
#region Report HighestAttendance
private void HighestAttendanceReport(object pFromDate, object pToDate)
{
try
{
string vFromDate;
string vToDate;
DateTime vDate;
if (pFromDate != null)
{
vFromDate = "'" + pFromDate.ToString() + "'";
}
else
{
vFromDate = "NULL";
}
if (pToDate != null)
{
vToDate = "'" + pToDate.ToString() + "'";
}
else
{
vToDate = "NULL";
}
string statement = "";
statement = "" + "\n" +
"SELECT TBL_MainPerson.Name "+ "\n" +
" ,TBL_MainPerson.Mobile "+ "\n" +
" ,TBL_Area.AreaDesc "+ "\n" +
" ,TBL_Street.StreetDesc "+ "\n" +
" ,TBL_MainPerson.Phone "+ "\n" +
" ,TBL_MainPerson.FloorNum "+ "\n" +
" ,TBL_MainPerson.BuildingNum "+ "\n" +
" ,ISNULL(COUNT(TBL_Meetings_Details.Pers_ID),0) AS AttendanceNumber "+ "\n" +
" ,TBL_City.CityDesc As City "+ "\n" +
"FROM TBL_MainPerson "+ "\n" +
"LEFT JOIN TBL_Meetings_Details "+ "\n" +
"ON TBL_MainPerson.ID = TBL_Meetings_Details.Pers_ID "+ "\n" +
"LEFT JOIN TBL_Meetings "+ "\n" +
"ON TBL_Meetings.ID = TBL_Meetings_Details.Meetings_ID "+ "\n" +
"LEFT JOIN TBL_Area "+ "\n" +
"ON TBL_Area.ID = TBL_MainPerson.Area_ID "+ "\n" +
"LEFT JOIN TBL_City "+ "\n" +
"ON TBL_City.ID = TBL_MainPerson.City_ID "+ "\n" +
"LEFT JOIN TBL_Street "+ "\n" +
"ON TBL_Street.ID = TBL_MainPerson.Street_ID "+ "\n" +
"WHERE 1= 1 "+ "\n" +
"AND (MONTH(TBL_Meetings.TDate) >= " + vFromDate + " Or " + vFromDate + " Is Null ) " + "\n" +
"AND (MONTH(TBL_Meetings.TDate) <= " + vToDate + " Or " + vToDate + " Is Null ) " + "\n" +
"GROUP BY TBL_MainPerson.Name "+ "\n" +
" ,TBL_MainPerson.Mobile "+ "\n" +
" ,TBL_Area.AreaDesc "+ "\n" +
" ,TBL_Street.StreetDesc "+ "\n" +
" ,TBL_MainPerson.Phone "+ "\n" +
" ,TBL_MainPerson.FloorNum "+ "\n" +
" ,TBL_MainPerson.BuildingNum ,TBL_City.CityDesc "+ "\n" +
" ORDER BY 8 Desc ";
SqlConnection conn = new SqlConnection(BasicClass.vConectionString);
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = statement;
da.SelectCommand = cmd;
Datasets.DS_Meetings ds = new Datasets.DS_Meetings();
conn.Open();
da.Fill(ds.Tables[0]);
conn.Close();
ReportDocument reportDocument = new ReportHighestAttendance();
reportDocument.Load("ReportHighestAttendance.rpt");
reportDocument.SetDataSource(ds);
reportDocument.SetParameterValue("Adress", "اعلى نسبة حضور ");
CR.ReportSource = reportDocument;
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.Message, this.Name, "HighestAttendanceReport");
}
}
