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");
}
}
private void Toolbar_Options_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e)
{
try
{
switch (e.Tool.Key)
{
case "BTN_Delete":
GRD_City.ActiveRow.Delete(false);
//select last row
GRD_City.ActiveRow = GRD_City.Rows[GRD_City.Rows.Count - 1];
break;
case "BTN_New":
if (GRD_City.ActiveRow.Band.ToString() == "TBL_City")
{
//bindingNavigatorAddNewItem.PerformClick();
////select last row
//GRD_City.ActiveRow = GRD_City.Rows[GRD_City.Rows.Count - 1];
////select cell
//GRD_City.ActiveCell = GRD_City.ActiveRow.Cells[1];
////enter edit mode
//GRD_City.PerformAction(Infragistics.Win.UltraWinGrid.UltraGridAction.EnterEditMode);
GRD_City.DisplayLayout.Bands["TBL_City"].AddNew();
}
else if (GRD_City.ActiveRow.Band.ToString() == "FK_TBL_Area_TBL_City")
{
GRD_City.DisplayLayout.Bands["FK_TBL_Area_TBL_City"].AddNew();
}
else if (GRD_City.ActiveRow.Band.ToString() == "FK_TBL_Street_TBL_Area")
{
GRD_City.DisplayLayout.Bands["FK_TBL_Street_TBL_Area"].AddNew();
}
break;
case "BTN_Save":
tBL_CityBindingNavigatorSaveItem.PerformClick();
break;
case "BTN_Next":
bindingNavigatorMoveNextItem.PerformClick();
break;
case "BTN_Previous":
bindingNavigatorMovePreviousItem.PerformClick();
//GRD_City.ActiveRow = GRD_City.Rows[GRD_City.Rows.Count - 1];
//GRD_City.ActiveRow = GRD_City.Rows[GRD_City.DisplayLayout.Bands["TBL_City"].Rows.Count - 1];
//GRD_City.DisplayLayout.Bands[].
//GRD_City.DisplayLayout.Bands["TBL_City"].
break;
}
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.Message, "FRM_Students", "Toolbar_Options_ToolClick");
}
}
private void Toolbar_Options_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e)
{
try
{
switch (e.Tool.Key)
{
case "BTN_Delete":
bindingNavigatorDeleteItem.PerformClick();
break;
case "BTN_New":
bindingNavigatorAddNewItem.PerformClick();
//to disable services when church not saved
GRD_Services.Enabled = false;
break;
case "BTN_Save":
//to get row index number befor save
int x = GRD_Churches.ActiveRow.Index;
//Save
tBL_ChurchBindingNavigatorSaveItem.PerformClick();
//select Same row
GRD_Churches.ActiveRow = GRD_Churches.Rows[x];
break;
case "BTN_Next":
bindingNavigatorMoveNextItem.PerformClick();
if (GRD_Churches.Rows.Count != GRD_Churches.ActiveRow.Index + 1)
{
int N = GRD_Churches.Rows.Count;
GRD_Churches.ActiveRow = GRD_Churches.Rows[GRD_Churches.ActiveRow.Index + 1];
}
//enable and reload data in services table
GRD_Services.Enabled = true;
this.tBL_ServicesTableAdapter.FillByChurch(this.adressDataSet.TBL_Services, Convert.ToInt64(TXT_ID.Text));
//Defultvalue by ID to insert with same key
GRD_Services.DisplayLayout.Bands[0].Columns["Church_ID"].DefaultCellValue = Convert.ToInt64(TXT_ID.Text);
break;
case "BTN_Previous":
bindingNavigatorMovePreviousItem.PerformClick();
//if active row firest row dont previouse
if (GRD_Churches.ActiveRow.Index != 0)
{
GRD_Churches.ActiveRow = GRD_Churches.Rows[GRD_Churches.ActiveRow.Index - 1];
}
//enable and reload data in services table
GRD_Services.Enabled = true;
this.tBL_ServicesTableAdapter.FillByChurch(this.adressDataSet.TBL_Services, Convert.ToInt64(TXT_ID.Text));
//Defultvalue by ID to insert with same key
GRD_Services.DisplayLayout.Bands[0].Columns["Church_ID"].DefaultCellValue = Convert.ToInt64(TXT_ID.Text);
break;
}
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.Message, "FRM_Students", "Toolbar_Options_ToolClick");
}
}
private void EXP_MainItems_ItemClick(object sender, Infragistics.Win.UltraWinExplorerBar.ItemEventArgs e)
{
try
{
LblCity.Visible = false;
CMX_CIty.Visible = false;
BtnLoadCity.Visible = false;
LBL_Area.Visible = false;
CMX_Area.Visible = false;
BtnLoadArea.Visible = false;
LblStreet.Visible = false;
CMX_Street.Visible = false;
BtnLoadStreet.Visible = false;
LblChurch.Visible = false;
CMX_Chruch.Visible = false;
BtnLoadFrChurch.Visible = false;
LblFather.Visible = false;
CMX_FatherName.Visible = false;
BtnLoadFather.Visible = false;
LblServices.Visible = false;
CMX_Services.Visible = false;
BtnLoadServices.Visible = false;
LblEduc.Visible = false;
CMX_Educ.Visible = false;
BtnLoadEduc.Visible = false;
LblStatus.Visible = false;
CMX_Status.Visible = false;
BtnLoadStatus.Visible = false;
LblWork.Visible = false;
CMX_Work.Visible = false;
BtnWork.Visible = false;
switch (e.Item.Key)
{
case "RepPeopleDataByCity":
LblCity.Visible = true;
CMX_CIty.Visible = true;
BtnLoadCity.Visible = true;
LBL_Area.Visible = true;
CMX_Area.Visible = true;
BtnLoadArea.Visible = true;
LblStreet.Visible = true;
CMX_Street.Visible = true;
BtnLoadStreet.Visible = true;
break;
case "RepPeopleDataByArea":
LblCity.Visible = true;
CMX_CIty.Visible = true;
BtnLoadCity.Visible = true;
LBL_Area.Visible = true;
CMX_Area.Visible = true;
BtnLoadArea.Visible = true;
LblStreet.Visible = true;
CMX_Street.Visible = true;
BtnLoadStreet.Visible = true;
break;
case "RepPeopleDataByStreet":
LblCity.Visible = true;
CMX_CIty.Visible = true;
BtnLoadCity.Visible = true;
LBL_Area.Visible = true;
CMX_Area.Visible = true;
BtnLoadArea.Visible = true;
LblStreet.Visible = true;
CMX_Street.Visible = true;
BtnLoadStreet.Visible = true;
break;
case "RepPeopleDataByGodFather":
LblChurch.Visible = true;
CMX_Chruch.Visible = true;
//BtnLoadFrChurch.Visible = true;
LblFather.Visible = true;
CMX_FatherName.Visible = true;
BtnLoadFather.Visible = true;
break;
case "RepPeopleDataByFatherChurch":
LblChurch.Visible = true;
CMX_Chruch.Visible = true;
//BtnLoadFrChurch.Visible = true;
LblFather.Visible = true;
CMX_FatherName.Visible = true;
BtnLoadFather.Visible = true;
break;
case "RepPeopleDataByService":
LblChurch.Visible = true;
CMX_Chruch.Visible = true;
//BtnLoadFrChurch.Visible = true;
LblServices.Visible = true;
CMX_Services.Visible = true;
BtnLoadServices.Visible = true;
break;
case "RepPeopleDataByEduLevel":
LblEduc.Visible = true;
CMX_Educ.Visible = true;
BtnLoadEduc.Visible = true;
break;
case "RepPeopleDataByStatus":
LblStatus.Visible = true;
CMX_Status.Visible = true;
BtnLoadStatus.Visible = true;
break;
case "RepPeopleDataByWorkStatus":
LblCity.Visible = true;
CMX_CIty.Visible = true;
BtnLoadCity.Visible = true;
LblWork.Visible = true;
CMX_Work.Visible = true;
BtnWork.Visible = true;
break;
}
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.Message, this.Name, "EXP_MainItems_ItemClick");
}
}
public void TAB_Main_TabClosing(object sender, Infragistics.Win.UltraWinTabControl.TabClosingEventArgs e)
{
try
{
switch (e.Tab.Key)
{
case "City":
if (Adress_FRM.fCancelTransaction())
{
Adress_FRM.Close();
}
else
{
e.Cancel = true;
}
break;
case "Chur":
if (Church_FRM.fCancelTransaction())
{
Church_FRM.Close();
}
else
{
e.Cancel = true;
}
break;
case "Serv":
break;
case "Edu":
break;
case "Pers":
if (Persons_FRM.fCancelTransaction())
{
Persons_FRM.Close();
}
else
{
e.Cancel = true;
}
break;
case "Meet":
break;
case "Aten":
if (Meeting_FRM.fCancelTransaction())
{
Meeting_FRM.Close();
}
else
{
e.Cancel = true;
}
break;
}
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.Message, this.Name, "Tab_InventoryOptions_TabClosed");
}
}