public static int CheckVenueAvailability(ATTMeeting objMeeting, OracleTransaction Tran)
{
try
{
/*string chkVenueSQL = "SELECT CHECK_VENUE_AVAILABILITY(" + objMeeting.OrgID + ",'"
+ objMeeting.MeetingDate + "',"
+ int.Parse(objMeeting.VenueData.ToString()) + ",'"
+ objMeeting.StartTime + "','"
+ objMeeting.EndTime + "')" +
+ "FROM DUAL";*/
string chkVenueSQL = "SELECT CHECK_VENUE_AVAILABILITY(" + objMeeting.OrgID + ",'"
+ objMeeting.MeetingDate + "',"
+ objMeeting.VenueBookingID + ",'"
+ objMeeting.StartTime + "','"
+ objMeeting.EndTime + "')" +
"FROM DUAL";
DataSet ds = SqlHelper.ExecuteDataset(Tran, CommandType.Text, chkVenueSQL);
DataTable tbl = new DataTable();
tbl = (DataTable)ds.Tables[0];
int bookedVenueCount = int.Parse(tbl.Rows[0][0].ToString());
return(bookedVenueCount);
}
catch (Exception ex)
{
throw(ex);
}
}
//public static string SaveMeetingAgenda(int orgID,int meetingID, List<ATTMeetingAgenda> lstMeetingAgenda,OracleTransaction Tran)
public static string SaveMeetingAgenda(ATTMeeting objMeeting, OracleTransaction Tran)
{
try
{
string saveSQL = "SP_ADD_MEETING_AGENDA";
string agendaIDs = "";
List <OracleParameter> paramArray = new List <OracleParameter>();
foreach (ATTMeetingAgenda objMeetingAgenda in objMeeting.LstMeetingAgenda)
{
paramArray.Add(Utilities.GetOraParam(":p_ORG_ID", objMeeting.OrgID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_MEETING_ID", objMeeting.MeetingID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_AGENDA_ID", null, OracleDbType.Int64, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":P_AGENDA", objMeetingAgenda.Agenda, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objMeetingAgenda.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_ON", objMeetingAgenda.EntryOn, OracleDbType.Date, ParameterDirection.Input));
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, saveSQL, paramArray.ToArray());
agendaIDs += paramArray[2].Value.ToString() + "/";
paramArray.Clear();
}
return(agendaIDs);
}
catch (Exception ex)
{
throw (ex);
}
}
public static List <ATTMeeting> SetMeeting(DataRow row, string dateString, ATTUserLogin login)
{
List <ATTMeeting> lstMeetingEvents = new List <ATTMeeting>();
int?calledByPID = null;
int?calledBy = null;
if (row["IS_GRP_CALLER"].ToString().Trim() == "N")
{
calledBy = null;
calledByPID = int.Parse(row["CALLED_BY_P_ID"].ToString());
}
else
{
calledBy = int.Parse(row["called_by"].ToString());
calledByPID = null;
}
ATTMeeting objME = new ATTMeeting();
objME.OrgID = int.Parse(row["Org_id"].ToString());
objME.MeetingID = int.Parse(row["meeting_id"].ToString());
objME.MeetingTypeID = int.Parse(row["mtype_id"].ToString());
objME.VenueID = int.Parse(row["venue_id"].ToString());
objME.Subject = row["meeting_subject"].ToString();
objME.MeetingDate = row["meeting_date"].ToString();
objME.IsGrpCaller = row["IS_GRP_CALLER"].ToString().Trim();
objME.CalledBy = calledBy;
objME.CalledByPID = calledByPID;
objME.StartTime = row["start_time"].ToString();
objME.EndTime = row["end_time"].ToString();
objME.Status = int.Parse(row["status"].ToString());
objME.VenueType = row["venue_type"].ToString();
objME.VenueData = row["venue_data"].ToString().Trim();
if (row["venue_booking_id"].ToString().Trim() != "")
{
objME.VenueBookingID = int.Parse(row["venue_booking_id"].ToString());
}
if (tblMA.Rows.Count > 0)
{
objME.LstMeetingAgenda = SetMeetingAgenda(tblMA, int.Parse(row["Org_id"].ToString()), int.Parse(row["meeting_id"].ToString()));
}
if (tblMP.Rows.Count > 0)
{
objME.LstMeetingParticipant = SetMeetingParticipant(tblMP, int.Parse(row["Org_id"].ToString()), int.Parse(row["meeting_id"].ToString()));
}
lstMeetingEvents.Add(objME);
return(lstMeetingEvents);
}
public static string UpdateMeetingEvents(ATTMeeting objMeeting)
{
try
{
return(DLLMeeting.UpdateMeetingEvents(objMeeting));
}
catch (Exception ex)
{
throw ex;
}
}
public static bool DeleteMeetingEvent(ATTMeeting objMeeting)
{
try
{
DLLMeeting.DeleteMeetingEvents(objMeeting);
return(true);
}
catch (Exception ex)
{
throw (ex);
}
}
public static bool DeleteMeetingEvents(ATTMeeting objMeeting)
{
string deleteSQL = "SP_DEL_MEETING";
int CountMeetingAgenda = objMeeting.LstMeetingAgenda.Count;
int CountMeetingParticipant = objMeeting.LstMeetingParticipant.Count;
OracleParameter[] paramArray = new OracleParameter[2];
paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", objMeeting.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_MEETING_ID", objMeeting.MeetingID, OracleDbType.Int64, ParameterDirection.Input);
GetConnection GetConn = new GetConnection();
OracleConnection DBConn = GetConn.GetDbConn(Module.OAS);
OracleTransaction Tran = DBConn.BeginTransaction();
try
{
if (CountMeetingAgenda > 0)
{
DLLMeetingAgenda.DeleteMeetingAgenda(objMeeting, Tran);
}
if (CountMeetingParticipant > 0)
{
DLLMeetingParticipant.DeleteMeetingParticipant(objMeeting, Tran);
}
DLLMeetingMinute.DeleteMeetingMinute(objMeeting, Tran);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, deleteSQL, paramArray);
Tran.Commit();
return(true);
}
catch (Exception ex)
{
Tran.Rollback();
throw (ex);
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool DeleteMeetingMinute(ATTMeeting objMeeting, OracleTransaction Tran)
{
try
{
string deleteSQL = "SP_DEL_MEETING_MINUTE";
OracleParameter[] paramArray = new OracleParameter[3];
paramArray[0] = Utilities.GetOraParam(":P_ORG_ID", objMeeting.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":P_MEETING_ID", objMeeting.MeetingID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":P_MINUTE_ID", null, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, deleteSQL, paramArray);
return(true);
}
catch (Exception ex)
{
throw (ex);
}
}
public static bool DeleteMeetingParticipant(ATTMeeting objMeeting, OracleTransaction Tran)
{
try
{
string deleteSQL = "SP_DEL_MEETING_PARTICIPANT";
foreach (ATTMeetingParticipant objMeetingParticipant in objMeeting.LstMeetingParticipant)
{
OracleParameter[] paramArray = new OracleParameter[3];
paramArray[0] = Utilities.GetOraParam(":P_ORG_ID", objMeeting.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":P_MEETING_ID", objMeeting.MeetingID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":P_PARTICIPANT_ID", objMeetingParticipant.ParticipantID, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, deleteSQL, paramArray);
}
return(true);
}
catch (Exception ex)
{
throw (ex);
}
}
public static string SaveMeetingParticipant(ATTMeeting objMeeting, OracleTransaction Tran)
{
try
{
string SaveSQL = "SP_ADD_MEETING_PARTICIPANT";
string participantIDs = "";
int? meetingMemPosID;
int? desID;
foreach (ATTMeetingParticipant objMeetingParticipant in objMeeting.LstMeetingParticipant)
{
OracleParameter[] paramArray = new OracleParameter[12];
if (objMeetingParticipant.Action == "A")
{
if (objMeetingParticipant.MeetingMemPosID == 0)
{
meetingMemPosID = null;
}
else
{
meetingMemPosID = objMeetingParticipant.MeetingMemPosID;
}
if (objMeetingParticipant.IsGrpCaller == "N")
{
desID = objMeetingParticipant.PositionID;
//meetingMemPosID = null;
}
else
{
desID = null;
}
paramArray[0] = Utilities.GetOraParam(":P_ORG_ID", objMeeting.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":P_MEETING_ID", objMeeting.MeetingID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":P_P_ID", objMeetingParticipant.ParticipantID, OracleDbType.Int64, ParameterDirection.InputOutput);
paramArray[3] = Utilities.GetOraParam(":P_PART_ORG_ID", objMeetingParticipant.ParticipantOrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":P_GROUP_ID", objMeetingParticipant.GroupID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":P_NOTE", objMeetingParticipant.Note, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":P_ENTRY_BY", objMeetingParticipant.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[7] = Utilities.GetOraParam(":P_ENTRY_ON", objMeetingParticipant.EntryOn, OracleDbType.Date, ParameterDirection.Input);
paramArray[8] = Utilities.GetOraParam(":P_IS_GRP_PART", objMeetingParticipant.IsGrpParticipant, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[9] = Utilities.GetOraParam(":P_POSITION_ID", meetingMemPosID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[10] = Utilities.GetOraParam(":P_IS_PRESENT", objMeetingParticipant.IsPresent, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[11] = Utilities.GetOraParam(":P_P_DES_ID", desID, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, SaveSQL, paramArray);
objMeetingParticipant.Action = "";
participantIDs += paramArray[2].Value.ToString() + "/";
}
}
return(participantIDs);
}
catch (Exception ex)
{
throw (ex);
}
}
public static string SaveMeetingEvents(ATTMeeting objMeeting)
{
GetConnection GetConn = new GetConnection();
OracleConnection DBConn = GetConn.GetDbConn(Module.OAS);
OracleTransaction Tran = DBConn.BeginTransaction();
if (objMeeting.VenueType == "INS")
{
int bookedVenueCount = CheckVenueAvailability(objMeeting, Tran);
if (bookedVenueCount < 1)
{
Tran.Commit();
return("-1");
}
int venueInUseCount = CheckVenueInUse(objMeeting, Tran);
if (venueInUseCount > 0)
{
Tran.Commit();
return("-2");
}
}
string SaveSQL = "SP_ADD_MEETING";
string eventIDs = "";
int CountMeetingAgenda = objMeeting.LstMeetingAgenda.Count;
int CountMeetingParticipant = objMeeting.LstMeetingParticipant.Count;
OracleParameter[] paramArray = new OracleParameter[18];
paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", objMeeting.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_MEETING_ID", null, OracleDbType.Int64, ParameterDirection.Output);
paramArray[2] = Utilities.GetOraParam(":P_MTYPE_ID", objMeeting.MeetingTypeID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":P_VENUE_ID", objMeeting.VenueID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":P_MEETING_SUBJECT", objMeeting.Subject, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":P_MEETING_DATE", objMeeting.MeetingDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":P_CALLED_BY", objMeeting.CalledBy, OracleDbType.Int64, ParameterDirection.Input);
paramArray[7] = Utilities.GetOraParam(":P_START_TIME", objMeeting.StartTime, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[8] = Utilities.GetOraParam(":P_END_TIME", objMeeting.EndTime, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[9] = Utilities.GetOraParam(":P_ENTRY_BY", objMeeting.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[10] = Utilities.GetOraParam(":P_ENTRY_ON", objMeeting.EntryOn, OracleDbType.Date, ParameterDirection.Input);
paramArray[11] = Utilities.GetOraParam(":P_STATUS", objMeeting.Status, OracleDbType.Int64, ParameterDirection.Input);
paramArray[12] = Utilities.GetOraParam(":P_REMARKS", objMeeting.Remark, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[13] = Utilities.GetOraParam(":P_IS_GRP_CALLER ", objMeeting.IsGrpCaller, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[14] = Utilities.GetOraParam(":P_CALLED_BY_P_ID", objMeeting.CalledByPID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[15] = Utilities.GetOraParam(":P_VENUE_TYPE", objMeeting.VenueType, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[16] = Utilities.GetOraParam(":P_VENUE_DATA", objMeeting.VenueData, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[17] = Utilities.GetOraParam(":P_VENUE_BOOKING_ID", objMeeting.VenueBookingID, OracleDbType.Int64, ParameterDirection.Input);
try
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, SaveSQL, paramArray);
objMeeting.MeetingID = int.Parse(paramArray[1].Value.ToString());
eventIDs += paramArray[1].Value.ToString() + "/";
if (CountMeetingAgenda > 0)
{
eventIDs += DLLMeetingAgenda.SaveMeetingAgenda(objMeeting, Tran);
}
if (CountMeetingParticipant > 0)
{
eventIDs += DLLMeetingParticipant.SaveMeetingParticipant(objMeeting, Tran);
}
Tran.Commit();
if (eventIDs.Length > 0)
{
eventIDs = eventIDs.Substring(0, eventIDs.Length - 1);
}
return(eventIDs);
}
catch (Exception ex)
{
Tran.Rollback();
throw (ex);
}
finally
{
GetConn.CloseDbConn();
}
}
public static ObjectValidation ValidateMeetingEntry(ATTMeeting objMeeting)
{
ObjectValidation OV = new ObjectValidation();
if (objMeeting.OrgID <= 0)
{
OV.IsValid = false;
OV.ErrorMessage = "Please select Organization.";
return(OV);
}
if (objMeeting.Status <= 0)
{
OV.IsValid = false;
OV.ErrorMessage = "Please select Status.";
return(OV);
}
//if (objMeeting.StartTime == 0.0)
if (objMeeting.StartTime == "")
{
OV.IsValid = false;
OV.ErrorMessage = "Please Enter Start Time.";
return(OV);
}
//if (objMeeting.EndTime == 0.0)
if (objMeeting.EndTime == "")
{
OV.IsValid = false;
OV.ErrorMessage = "Please Enter End Time.";
return(OV);
}
if (objMeeting.MeetingTypeID <= 0)
{
OV.IsValid = false;
OV.ErrorMessage = "Please select Meeting Type.";
return(OV);
}
//if (objMeeting.VenueID <= 0)
//{
// OV.IsValid = false;
// OV.ErrorMessage = "Please select Venue.";
// return OV;
//}
if (objMeeting.CalledBy <= 0)
{
OV.IsValid = false;
OV.ErrorMessage = "Please select Called By.";
return(OV);
}
if (objMeeting.Subject == "")
{
OV.IsValid = false;
OV.ErrorMessage = "Please Enter Subject.";
return(OV);
}
return(OV);
}
