public object AttendanceAddEdit(JObject AttendanceInput)
{
dynamic TBAttendanceDetailsResult;
try
{
string jsonData = JsonConvert.SerializeObject(AttendanceInput);
RootObjectAttendance obj = JsonConvert.DeserializeObject <RootObjectAttendance>(jsonData);
if (obj != null)
{
int Result = Attendance.AttendanceAddEdit(obj.AttendanceAddEdit_Input);
if (Result != 0)
{
#region start members
if (obj.AttendanceMembers != null)
{
string ConnectionString = GlobalVar.strAppConn;
{
using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
{
List <string> NewRows = new List <string>();
StringBuilder NewMemberstr = new StringBuilder();
StringBuilder DeleteMemberstr = new StringBuilder();
if (obj.AttendanceMembers[0].newMembers.Count > 0)
{
NewMemberstr.Append("INSERT INTO tbl_attendancememberdetails (FK_AttendanceID, FK_MemberID) VALUES ");
for (int i = 0; i < obj.AttendanceMembers[0].newMembers.Count; i++)
{
NewRows.Add(string.Format("('{0}','{1}')", Result, obj.AttendanceMembers[0].newMembers[i].FK_MemberID));
}
}
if (obj.AttendanceMembers[0].deletedMembers.Count > 0)
{
for (int i = 0; i < obj.AttendanceMembers[0].deletedMembers.Count; i++)
{
DeleteMemberstr.Append("update tbl_attendancememberdetails set Isdeleted=1,deleted_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and FK_MemberID=" + obj.AttendanceMembers[0].deletedMembers[i].FK_MemberID + ";");
}
}
bool flag = false;
if (NewRows.Count > 0)
{
NewMemberstr.Append(string.Join(",", NewRows));
NewMemberstr.Append(";");
flag = true;
}
if (DeleteMemberstr.Length > 0)
{
NewMemberstr.Append(DeleteMemberstr);
flag = true;
}
if (flag == true)
{
//NewMemberstr.Append(string.Join(",", NewRows));
//NewMemberstr.Append(";");
//NewMemberstr.Append(DeleteMemberstr);
mConnection.Open();
using (MySqlCommand myCmd = new MySqlCommand(NewMemberstr.ToString(), mConnection))
{
myCmd.CommandType = CommandType.Text;
myCmd.ExecuteNonQuery();
}
if (mConnection.State == ConnectionState.Open)
{
mConnection.Close();
}
}
}
}
}
#endregion
#region start anns
if (obj.AttendanceAnns != null)
{
string ConnectionString = GlobalVar.strAppConn;
{
using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
{
List <string> NewRows = new List <string>();
StringBuilder NewAnns = new StringBuilder();
StringBuilder UpdatedAnns = new StringBuilder();
StringBuilder DeleteAnns = new StringBuilder();
if (obj.AttendanceAnns[0].newAnns.Count > 0)
{
NewAnns.Append("INSERT INTO attendanceannsdetails (FK_AttendanceID, AnnsName,created_date) VALUES ");
for (int i = 0; i < obj.AttendanceAnns[0].newAnns.Count; i++)
{
NewRows.Add(string.Format("('{0}','{1}',{2})", Result, MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceAnns[0].newAnns[i].AnnsName), "now()"));
}
}
if (obj.AttendanceAnns[0].UpdateAnns.Count > 0)
{
for (int i = 0; i < obj.AttendanceAnns[0].UpdateAnns.Count; i++)
{
UpdatedAnns.Append("update attendanceannsdetails set AnnsName='" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceAnns[0].UpdateAnns[i].AnnsName) + "',modified_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and PK_AttendanceAnnsID=" + obj.AttendanceAnns[0].UpdateAnns[i].PK_AttendanceAnnsID + ";");
}
}
if (obj.AttendanceAnns[0].deletedAnns.Count > 0)
{
for (int i = 0; i < obj.AttendanceAnns[0].deletedAnns.Count; i++)
{
DeleteAnns.Append("update attendanceannsdetails set Isdeleted=1,deleted_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and PK_AttendanceAnnsID=" + obj.AttendanceAnns[0].deletedAnns[i].PK_AttendanceAnnsID + ";");
}
}
bool flag = false;
if (NewRows.Count > 0)
{
NewAnns.Append(string.Join(",", NewRows));
NewAnns.Append(";");
flag = true;
}
if (UpdatedAnns.Length > 0)
{
NewAnns.Append(UpdatedAnns);
flag = true;
}
if (DeleteAnns.Length > 0)
{
NewAnns.Append(DeleteAnns);
flag = true;
}
if (flag == true)
{
//NewAnns.Append(string.Join(",", NewRows));
//NewAnns.Append(";");
//NewAnns.Append(UpdatedAnns);
//NewAnns.Append(DeleteAnns);
mConnection.Open();
using (MySqlCommand myCmd = new MySqlCommand(NewAnns.ToString(), mConnection))
{
myCmd.CommandType = CommandType.Text;
myCmd.ExecuteNonQuery();
}
if (mConnection.State == ConnectionState.Open)
{
mConnection.Close();
}
}
}
}
}
#endregion
#region start Annets
{
if (obj.AttendanceAnnets != null)
{
string ConnectionString = GlobalVar.strAppConn;
{
using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
{
List <string> NewRows = new List <string>();
StringBuilder NewAnnets = new StringBuilder();
StringBuilder UpdatedAnnets = new StringBuilder();
StringBuilder DeleteAnnets = new StringBuilder();
if (obj.AttendanceAnnets[0].newAnnets.Count > 0)
{
NewAnnets.Append("INSERT INTO tbl_attendanceannetsdetails (FK_AttendanceID, AnnetsName,created_date) VALUES ");
for (int i = 0; i < obj.AttendanceAnnets[0].newAnnets.Count; i++)
{
NewRows.Add(string.Format("('{0}','{1}',{2})", Result, MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceAnnets[0].newAnnets[i].AnnetsName), "now()"));
}
}
if (obj.AttendanceAnnets[0].UpdateAnnets.Count > 0)
{
for (int i = 0; i < obj.AttendanceAnnets[0].UpdateAnnets.Count; i++)
{
UpdatedAnnets.Append("update tbl_attendanceannetsdetails set AnnetsName='" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceAnnets[0].UpdateAnnets[i].AnnetsName) + "',modified_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and PK_AttendanceAnnetsID=" + obj.AttendanceAnnets[0].UpdateAnnets[i].PK_AttendanceAnnetsID + ";");
}
}
if (obj.AttendanceAnnets[0].deletedAnnets.Count > 0)
{
for (int i = 0; i < obj.AttendanceAnnets[0].deletedAnnets.Count; i++)
{
DeleteAnnets.Append("update tbl_attendanceannetsdetails set Isdeleted=1,deleted_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and PK_AttendanceAnnetsID=" + obj.AttendanceAnnets[0].deletedAnnets[i].PK_AttendanceAnnetsID + ";");
}
}
bool flag = false;
if (NewRows.Count > 0)
{
NewAnnets.Append(string.Join(",", NewRows));
NewAnnets.Append(";");
flag = true;
}
if (UpdatedAnnets.Length > 0)
{
NewAnnets.Append(UpdatedAnnets);
flag = true;
}
if (DeleteAnnets.Length > 0)
{
NewAnnets.Append(DeleteAnnets);
flag = true;
}
if (flag == true)
{
//NewAnnets.Append(string.Join(",", NewRows));
//NewAnnets.Append(";");
//NewAnnets.Append(UpdatedAnnets);
//NewAnnets.Append(DeleteAnnets);
mConnection.Open();
using (MySqlCommand myCmd = new MySqlCommand(NewAnnets.ToString(), mConnection))
{
myCmd.CommandType = CommandType.Text;
myCmd.ExecuteNonQuery();
}
if (mConnection.State == ConnectionState.Open)
{
mConnection.Close();
}
}
}
}
}
}
#endregion
#region start Visitors
{
if (obj.AttendanceVisitors != null)
{
string ConnectionString = GlobalVar.strAppConn;
{
using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
{
List <string> NewRows = new List <string>();
StringBuilder NewVisitors = new StringBuilder();
StringBuilder UpdatedVisitors = new StringBuilder();
StringBuilder DeleteVisitors = new StringBuilder();
if (obj.AttendanceVisitors[0].newVisitors.Count > 0)
{
NewVisitors.Append("INSERT INTO tbl_attendancevisitorsdetails (FK_AttendanceID, VisitorsName,Rotarian_whohas_Brought,created_date) VALUES ");
for (int i = 0; i < obj.AttendanceVisitors[0].newVisitors.Count; i++)
{
NewRows.Add(string.Format("('{0}','{1}','{2}',{3})", Result, MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceVisitors[0].newVisitors[i].VisitorsName), MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceVisitors[0].newVisitors[i].Rotarian_whohas_Brought), "now()"));
}
}
if (obj.AttendanceVisitors[0].UpdateVisitors.Count > 0)
{
for (int i = 0; i < obj.AttendanceVisitors[0].UpdateVisitors.Count; i++)
{
UpdatedVisitors.Append("update tbl_attendancevisitorsdetails set VisitorsName='" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceVisitors[0].UpdateVisitors[i].VisitorsName) + "',Rotarian_whohas_Brought='" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceVisitors[0].UpdateVisitors[i].Rotarian_whohas_Brought) + "',modified_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and PK_AttendanceVisitorID=" + obj.AttendanceVisitors[0].UpdateVisitors[i].PK_AttendanceVisitorID + ";");
}
}
if (obj.AttendanceVisitors[0].deletedVisitors.Count > 0)
{
for (int i = 0; i < obj.AttendanceVisitors[0].deletedVisitors.Count; i++)
{
DeleteVisitors.Append("update tbl_attendancevisitorsdetails set isdeleted=1,deleted_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and PK_AttendanceVisitorID=" + obj.AttendanceVisitors[0].deletedVisitors[i].PK_AttendanceVisitorID + ";");
}
}
bool flag = false;
if (NewRows.Count > 0)
{
NewVisitors.Append(string.Join(",", NewRows));
NewVisitors.Append(";");
flag = true;
}
if (UpdatedVisitors.Length > 0)
{
NewVisitors.Append(UpdatedVisitors);
flag = true;
}
if (DeleteVisitors.Length > 0)
{
NewVisitors.Append(DeleteVisitors);
flag = true;
}
if (flag == true)
{
mConnection.Open();
using (MySqlCommand myCmd = new MySqlCommand(NewVisitors.ToString(), mConnection))
{
myCmd.CommandType = CommandType.Text;
myCmd.ExecuteNonQuery();
}
if (mConnection.State == ConnectionState.Open)
{
mConnection.Close();
}
}
}
}
}
}
#endregion
#region start Rotarians
{
if (obj.AttendanceRotarians != null)
{
string ConnectionString = GlobalVar.strAppConn;
{
using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
{
List <string> NewRows = new List <string>();
StringBuilder NewRotarian = new StringBuilder();
StringBuilder UpdatedRotarian = new StringBuilder();
StringBuilder DeleteRotarian = new StringBuilder();
if (obj.AttendanceRotarians[0].newRotarians.Count > 0)
{
NewRotarian.Append("INSERT INTO tbl_attendancerotariansdetails (FK_AttendanceID, RotarianID,RotarianName,ClubName,created_date) VALUES ");
for (int i = 0; i < obj.AttendanceRotarians[0].newRotarians.Count; i++)
{
NewRows.Add(string.Format("('{0}','{1}','{2}','{3}',{4})", Result, MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceRotarians[0].newRotarians[i].RotarianID), MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceRotarians[0].newRotarians[i].RotarianName), MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceRotarians[0].newRotarians[i].ClubName), "now()"));
}
}
if (obj.AttendanceRotarians[0].UpdateRotarians.Count > 0)
{
for (int i = 0; i < obj.AttendanceRotarians[0].UpdateRotarians.Count; i++)
{
UpdatedRotarian.Append("update tbl_attendancerotariansdetails set RotarianID='" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceRotarians[0].UpdateRotarians[i].RotarianID) + "',RotarianName='" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceRotarians[0].UpdateRotarians[i].RotarianName) + "',ClubName='" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceRotarians[0].UpdateRotarians[i].ClubName) + "',modified_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and PK_AttendanceRotarianID=" + obj.AttendanceRotarians[0].UpdateRotarians[i].PK_AttendanceRotarianID + ";");
}
}
if (obj.AttendanceRotarians[0].deletedRotarians.Count > 0)
{
for (int i = 0; i < obj.AttendanceRotarians[0].deletedRotarians.Count; i++)
{
DeleteRotarian.Append("update tbl_attendancerotariansdetails set isdeleted=1,deleted_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and PK_AttendanceRotarianID=" + obj.AttendanceRotarians[0].deletedRotarians[i].PK_AttendanceRotarianID + ";");
}
}
bool flag = false;
if (NewRows.Count > 0)
{
NewRotarian.Append(string.Join(",", NewRows));
NewRotarian.Append(";");
flag = true;
}
if (UpdatedRotarian.Length > 0)
{
NewRotarian.Append(UpdatedRotarian);
flag = true;
}
if (DeleteRotarian.Length > 0)
{
NewRotarian.Append(DeleteRotarian);
flag = true;
}
if (flag == true)
{
mConnection.Open();
using (MySqlCommand myCmd = new MySqlCommand(NewRotarian.ToString(), mConnection))
{
myCmd.CommandType = CommandType.Text;
myCmd.ExecuteNonQuery();
}
if (mConnection.State == ConnectionState.Open)
{
mConnection.Close();
}
}
}
}
}
}
#endregion
#region start delegate
{
if (obj.AttendanceDistrictDelegate != null)
{
string ConnectionString = GlobalVar.strAppConn;
{
using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
{
List <string> NewRows = new List <string>();
StringBuilder NewDistrictDelegate = new StringBuilder();
StringBuilder UpdatedDistrictDelegate = new StringBuilder();
StringBuilder DeleteDistrictDelegate = new StringBuilder();
if (obj.AttendanceDistrictDelegate[0].newDistrictDelegate.Count > 0)
{
NewDistrictDelegate.Append("INSERT INTO tbl_attendancedistrictdelegatesdetails (FK_AttendanceID, RotarianName,DistrictDesignation,ClubName,created_date) VALUES ");
for (int i = 0; i < obj.AttendanceDistrictDelegate[0].newDistrictDelegate.Count; i++)
{
NewRows.Add(string.Format("('{0}','{1}','{2}','{3}',{4})", Result, MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceDistrictDelegate[0].newDistrictDelegate[i].RotarianName), MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceDistrictDelegate[0].newDistrictDelegate[i].DistrictDesignation), MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceDistrictDelegate[0].newDistrictDelegate[i].ClubName), "now()"));
}
}
if (obj.AttendanceDistrictDelegate[0].UpdateDistrictDelegate.Count > 0)
{
for (int i = 0; i < obj.AttendanceDistrictDelegate[0].UpdateDistrictDelegate.Count; i++)
{
UpdatedDistrictDelegate.Append("update tbl_attendancedistrictdelegatesdetails set RotarianName='" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceDistrictDelegate[0].UpdateDistrictDelegate[i].RotarianName) + "',DistrictDesignation='" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceDistrictDelegate[0].UpdateDistrictDelegate[i].DistrictDesignation) + "',ClubName='" + MySql.Data.MySqlClient.MySqlHelper.EscapeString(obj.AttendanceDistrictDelegate[0].UpdateDistrictDelegate[i].ClubName) + "',modified_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and PK_AttendanceDelegateID=" + obj.AttendanceDistrictDelegate[0].UpdateDistrictDelegate[i].PK_AttendanceDelegateID + ";");
}
}
if (obj.AttendanceDistrictDelegate[0].deletedDistrictDelegate.Count > 0)
{
for (int i = 0; i < obj.AttendanceDistrictDelegate[0].deletedDistrictDelegate.Count; i++)
{
DeleteDistrictDelegate.Append("update tbl_attendancedistrictdelegatesdetails set isdeleted=1,deleted_date=now() where FK_AttendanceID=" + Convert.ToInt32(Result) + " and PK_AttendanceDelegateID=" + obj.AttendanceDistrictDelegate[0].deletedDistrictDelegate[i].PK_AttendanceDelegateID + ";");
}
}
bool flag = false;
if (NewRows.Count > 0)
{
NewDistrictDelegate.Append(string.Join(",", NewRows));
NewDistrictDelegate.Append(";");
flag = true;
}
if (UpdatedDistrictDelegate.Length > 0)
{
NewDistrictDelegate.Append(UpdatedDistrictDelegate);
flag = true;
}
if (DeleteDistrictDelegate.Length > 0)
{
NewDistrictDelegate.Append(DeleteDistrictDelegate);
flag = true;
}
if (flag == true)
{
mConnection.Open();
using (MySqlCommand myCmd = new MySqlCommand(NewDistrictDelegate.ToString(), mConnection))
{
myCmd.CommandType = CommandType.Text;
myCmd.ExecuteNonQuery();
}
if (mConnection.State == ConnectionState.Open)
{
mConnection.Close();
}
}
}
}
}
}
#endregion
TBAttendanceDetailsResult = new { status = "0", message = "success", Result };
}
else
{
TBAttendanceDetailsResult = new { status = "1", message = "failed" };
}
}
else
{
TBAttendanceDetailsResult = new { status = "0", message = "No Input Data" };
}
}
catch
{
TBAttendanceDetailsResult = new { status = "1", message = "failed" };
}
return(new { TBAttendanceDetailsResult });
}