public static DataTable GetProgramCoordinatorTable(int orgID, int programID, int programCoordinatorID)
{
string SelectSP = "SP_GET_PROGRAM_COORDINATOR";
OracleParameter[] paramArray = new OracleParameter[4];
paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", orgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_PROGRAM_ID", programID, OracleDbType.Int64, ParameterDirection.Input);
if (programCoordinatorID > 0)
{
paramArray[2] = Utilities.GetOraParam(":p_PROGRAM_COORDINATOR_ID", programCoordinatorID, OracleDbType.Int64, ParameterDirection.Input);
}
else
{
paramArray[2] = Utilities.GetOraParam(":p_PROGRAM_COORDINATOR_ID", null, OracleDbType.Int64, ParameterDirection.Input);
}
paramArray[3] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output);
GetConnection GetConn = new GetConnection();
try
{
OracleConnection DBConn = GetConn.GetDbConn(Module.DLPDS);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SelectSP, paramArray);
OracleDataReader reader = ((OracleRefCursor)paramArray[3].Value).GetDataReader();
DataTable tbl = new DataTable();
tbl.Load(reader, LoadOption.OverwriteChanges);
return(tbl);
}
catch (Exception ex)
{
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool AddOrganizationTippaniSubject(List <ATTOrganizationTippaniSubject> lstATT)
{
PCS.COREDL.GetConnection Conn = new GetConnection();
OracleConnection DBConn;
try
{
DBConn = Conn.GetDbConn(Module.OAS);
string SPInsertUpdate = "";
foreach (ATTOrganizationTippaniSubject objATT in lstATT)
{
if (objATT.Action == "A")
{
SPInsertUpdate = "SP_ADD_ORG_TIPPANI";
}
else if (objATT.Action == "E")
{
SPInsertUpdate = "SP_EDIT_ORG_TIPPANI";
}
OracleParameter[] ParamArray = new OracleParameter[4];
ParamArray[0] = Utilities.GetOraParam(":P_ORG_ID", objATT.OrgID, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":P_TIPPANI_SUBJECT_ID", objATT.TippaniSubjectID, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[2] = Utilities.GetOraParam(":P_FROM_DATE", objATT.FromDate, OracleDbType.Varchar2, ParameterDirection.Input);
ParamArray[3] = Utilities.GetOraParam(":P_TO_DATE", objATT.ToDate, OracleDbType.Varchar2, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SPInsertUpdate, ParamArray);
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Conn.CloseDbConn();
}
}
public static bool SaveAccountType(ATTAccountType objAccountType)
{
string InsertUpdateSQL = "";
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_ACCOUNT_TYPE_ID", objAccountType.AccountTypeID, OracleDbType.Int64, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":P_ACCOUNT_TYPE_NAME", objAccountType.AccountTypeName, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ACTIVE", objAccountType.Active, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objAccountType.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
if (objAccountType.Action == "A")
{
InsertUpdateSQL = "SP_ADD_ACCOUNT_TYPE";
}
else if (objAccountType.Action == "E")
{
InsertUpdateSQL = "SP_EDIT_ACCOUNT_TYPE";
}
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
try
{
SqlHelper.ExecuteNonQuery(Tran, System.Data.CommandType.StoredProcedure, InsertUpdateSQL, paramArray.ToArray());
objAccountType.AccountTypeID = int.Parse(paramArray[0].Value.ToString());
Tran.Commit();
return(true);
}
catch (OracleException oex)
{
PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError();
throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message));
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
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 DataTable GetDartaaChalaani(int? docID, int? unitID, int? orgID)
//{
// string SelectSQL = "SP_GET_DOCUMENT";
// OracleParameter[] paramArray = new OracleParameter[4];
// paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", orgID, OracleDbType.Int16, ParameterDirection.Input);
// paramArray[1] = Utilities.GetOraParam(":p_UNIT_ID", unitID, OracleDbType.Int16, ParameterDirection.Input);
// paramArray[2] = Utilities.GetOraParam(":p_DOC_ID", docID, OracleDbType.Int16, ParameterDirection.Input);
// paramArray[3] = Utilities.GetOraParam(":p_RC", null, OracleDbType.RefCursor, ParameterDirection.Output);
// GetConnection GetConn = new GetConnection();
// try
// {
// OracleConnection DBConn = GetConn.GetDbConn(Module.OAS);
// SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SelectSQL, paramArray);
// OracleDataReader reader = ((OracleRefCursor)paramArray[3].Value).GetDataReader();
// DataTable tbl = new DataTable();
// tbl.Load(reader, LoadOption.OverwriteChanges);
// return tbl;
// }
// catch (Exception ex)
// {
// throw (ex);
// }
// finally
// {
// GetConn.CloseDbConn();
// }
//}
/// <summary>
/// Add ItemUnit object to database
/// </summary>
/// <param name="obj">ATTInvItemUnit object</param>
/// <returns>return bool</returns>
public static bool SaveDartaaChalaani(ATTDartaaChalaani obj)
{
GetConnection getConn = new GetConnection();
OracleConnection DBConn = getConn.GetDbConn(Module.OAS);
// OracleTransaction Tran = DBConn.BeginTransaction();
try
{
//foreach (ATTInvItems obj in lstItems)
//{
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":p_org_id", obj.OrgID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_reg_date", obj.RegDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_reg_no", obj.RegNo, OracleDbType.Double, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":p_reg_type", obj.RegType, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_subject", obj.Subject, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_description", obj.Description, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_phy_dig_flag", obj.PhysicalDigital, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_reg_file", obj.RegFile, OracleDbType.Blob, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_fwd_unit", obj.FwdUnit, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_fwd_person", obj.FwdPerson, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_send_org", obj.SendOrg, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_send_unit", obj.SendUnit, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_send_person", obj.SendPerson, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_send_date", obj.SendDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_entry_by", obj.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, "sp_add_dartaa_chalaani", paramArray.ToArray());
obj.RegNo = int.Parse(paramArray[2].Value.ToString());
paramArray.Clear();
//}
// Tran.Commit();
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
getConn.CloseDbConn();
}
}
public static bool AddUpdateItemCategory(ATTInvItemsCategory itemsCategory)
{
string sp = "";
if (itemsCategory.Action == "A")
{
sp = "sp_inv_add_items_category";
}
else if (itemsCategory.Action == "E")
{
sp = "sp_inv_edit_items_category";
}
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":p_items_category_id", itemsCategory.ItemsCategoryID, OracleDbType.Int64, System.Data.ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":p_items_category_name", itemsCategory.ItemsCategoryName, OracleDbType.Varchar2, System.Data.ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_active", itemsCategory.Active, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_entry_by", itemsCategory.EntryBy, OracleDbType.Varchar2, System.Data.ParameterDirection.Input));
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.OAS).BeginTransaction();
try
{
SqlHelper.ExecuteNonQuery(Tran, System.Data.CommandType.StoredProcedure, sp, paramArray.ToArray());
itemsCategory.ItemsCategoryID = int.Parse(paramArray[0].Value.ToString());
DLLInvItemSubCategory.AddUpdateDeleteItemSubCategory(itemsCategory.LstItemSubCategory, itemsCategory.ItemsCategoryID, Tran);
Tran.Commit();
return(true);
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool SaveTarekhCourtBato(ATTTarekhCourtBato objTarekhCourtBato)
{
string InsertUpdateSQL = "";
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_COURT_ID", objTarekhCourtBato.CourtID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_FROM_DATE", objTarekhCourtBato.FromDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_TOT_DAYS", objTarekhCourtBato.TotalDays, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_BATOKO_MYAAD", objTarekhCourtBato.BatoKoMayad, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objTarekhCourtBato.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
if (objTarekhCourtBato.Action == "A")
{
InsertUpdateSQL = "SP_ADD_TARIKH_COURT_BATO";
}
else if (objTarekhCourtBato.Action == "E")
{
InsertUpdateSQL = "SP_EDIT_TARIKH_COURT_BATO";
}
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
try
{
SqlHelper.ExecuteNonQuery(Tran, System.Data.CommandType.StoredProcedure, InsertUpdateSQL, paramArray.ToArray());
Tran.Commit();
return(true);
}
catch (OracleException oex)
{
PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError();
throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message));
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool SavePostingType(ATTPostingType ObjAtt)
{
GetConnection Conn = new GetConnection();
OracleConnection DBConn;
OracleTransaction Tran;
string InsertUpdatePostingType = "";
try
{
DBConn = Conn.GetDbConn(Module.PMS);
Tran = DBConn.BeginTransaction();
if (ObjAtt.PostingTypeID == 0)
{
InsertUpdatePostingType = "SP_ADD_POSTING_TYPES";
}
else
{
InsertUpdatePostingType = "SP_EDIT_POSTING_TYPES";
}
OracleParameter[] ParamArray = new OracleParameter[3];
ParamArray[0] = Utilities.GetOraParam(":p_POSTING_TYPE_ID", ObjAtt.PostingTypeID, OracleDbType.Int64, ParameterDirection.InputOutput);
ParamArray[1] = Utilities.GetOraParam(":p_POSTING_TYPE_NAME", ObjAtt.PostingTypeName, OracleDbType.Varchar2, ParameterDirection.Input);
ParamArray[2] = Utilities.GetOraParam(":p_ACTIVE", ObjAtt.Active, OracleDbType.Varchar2, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, InsertUpdatePostingType, ParamArray);
int PostingTypeID = int.Parse(ParamArray[0].Value.ToString());
ObjAtt.PostingTypeID = PostingTypeID;
Tran.Commit();
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Conn.CloseDbConn();
}
}
public static Dictionary <string, AccessColumn> GetUserApplicationMenu(string username, int applID)
{
Dictionary <string, AccessColumn> DList = new Dictionary <string, AccessColumn>();
GetConnection GetConn = new GetConnection();
try
{
OracleConnection DBConn = GetConn.GetDbConn();
string strUserMenus = "SP_GET_USER_MENUS";
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":p_user_name", username, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_appl_id", applID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_rc", null, OracleDbType.RefCursor, ParameterDirection.Output));
DataTable tbl = SqlHelper.ExecuteDataset(DBConn, CommandType.StoredProcedure, strUserMenus, paramArray.ToArray()).Tables[0];
foreach (DataRow row in tbl.Rows)
{
AccessColumn col = new AccessColumn();
col.PSelect = row["P_SELECT"].ToString();
col.PAdd = row["P_ADD"].ToString();
col.PEdit = row["P_EDIT"].ToString();
col.PDelete = row["P_DELETE"].ToString();
DList.Add(row["Menu_ID"].ToString(), col);
}
return(DList);
}
catch (Exception ex)
{
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool SaveBenchFormation(ATTBenchFormation objBenchFormation)
{
string InsertUpdateSQL = "SP_ADD_BENCH_FORMATION";
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_ORG_ID", objBenchFormation.OrgID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_BENCH_TYPE_ID", objBenchFormation.BenchTypeID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_BENCH_NO", objBenchFormation.BenchNo, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_FROM_DATE", objBenchFormation.FromDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_SEQ_NO", objBenchFormation.SeqNo, OracleDbType.Int64, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":P_TO_DATE", objBenchFormation.ToDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objBenchFormation.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
try
{
SqlHelper.ExecuteNonQuery(Tran, System.Data.CommandType.StoredProcedure, InsertUpdateSQL, paramArray.ToArray());
int seqNo = int.Parse(paramArray[4].Value.ToString());
DLLBenchJudge.SaveBenchJudge(objBenchFormation.LstBenchJudge, Tran, seqNo);
Tran.Commit();
return(true);
}
catch (OracleException oex)
{
PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError();
throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message));
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool SaveRelationType(ATTRelationType obj)
{
string SP = "";
if (obj.Action == "A")
{
SP = "SP_ADD_RELATION_TYPES";
}
else if (obj.Action == "E")
{
SP = "SP_EDIT_RELATION_TYPES";
}
List <OracleParameter> paramArray = new List <OracleParameter>();
GetConnection getConn = new GetConnection();
OracleTransaction Tran = getConn.GetDbConn().BeginTransaction();
paramArray.Add(Utilities.GetOraParam(":RELATION_TYPE_ID", obj.RelationTypeID, OracleDbType.Int64, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":RELATION_TYPE_NAME", obj.RelationTypeName, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":RELATION_TYPE_CARDINALITY", obj.RelationTypeCardinality, OracleDbType.Int64, ParameterDirection.Input));
try
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, SP, paramArray.ToArray());
int relationTypeID = int.Parse(paramArray[0].Value.ToString());
obj.RelationTypeID = relationTypeID;
Tran.Commit();
return(true);
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
finally
{
getConn.CloseDbConn();
}
}
public static bool SaveEvaluationGroup(ATTEvaluationGroup obj)
{
string SP = "";
if (obj.Action == "A")
{
SP = "SP_ADD_EVALUATION_GROUPS";
}
else if (obj.Action == "E")
{
SP = "SP_EDIT_EVALUATION_GROUPS";
}
List <OracleParameter> paramArray = new List <OracleParameter>();
GetConnection getConn = new GetConnection();
OracleTransaction Tran = getConn.GetDbConn(Module.PMS).BeginTransaction();
paramArray.Add(Utilities.GetOraParam("P_EVAL_GROUP_ID", obj.GroupID, OracleDbType.Int64, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam("P_EVAL_GROUP_NAME", obj.GroupName, OracleDbType.Varchar2, ParameterDirection.Input));
try
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, SP, paramArray.ToArray());
int groupID = int.Parse(paramArray[0].Value.ToString());
obj.GroupID = groupID;
Tran.Commit();
return(true);
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
finally
{
getConn.CloseDbConn();
}
}
public static DataTable GetMessageAttachmentListTable(string type, ATTUserLogin login)
{
GetConnection GetConn = new GetConnection();
try
{
string selectSQL = "SELECT ORG_ID,MSG_ID,msg_seq,MSG_ATTACH_ID,FILE_NAME,FILE_BYTES " +
"FROM VW_MESSAGE_DETAILS " +
" WHERE MSG_ATTACH_ID IS NOT NULL ";
if (login != null)
{
if (type == "IN")
{
selectSQL += " AND (GRP_RECEIVER_ID = " + login.PID + " OR OTHER_RECEIVER_ID = " + login.PID + ")";
}
else if (type == "OUT")
{
selectSQL += " AND SENDER_ID = " + login.PID;
}
}
OracleConnection DBConn = GetConn.GetDbConn(Module.OAS);
DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, selectSQL);
DataTable tbl = new DataTable();
tbl = (DataTable)ds.Tables[0];
return(tbl);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool SaveDesignationLevel(ATTDesignationLevel att_obj)
{
string InsertUpdateDL;
InsertUpdateDL = "";
if (att_obj.LevelID > 0)
{
InsertUpdateDL = "SP_EDIT_LEVEL";
}
else if (att_obj.LevelID == 0)
{
InsertUpdateDL = "SP_ADD_LEVEL";
}
OracleParameter[] paramArray = new OracleParameter[3];
paramArray[0] = Utilities.GetOraParam(":Level_ID", att_obj.LevelID, OracleDbType.Int64, ParameterDirection.InputOutput);
paramArray[1] = Utilities.GetOraParam(":Level_Name", att_obj.LevelName, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":Entry_By", att_obj.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
GetConnection conn = new GetConnection();
try
{
OracleConnection DBconn = conn.GetDbConn(Module.PMS);
SqlHelper.ExecuteNonQuery(DBconn, CommandType.StoredProcedure, InsertUpdateDL, paramArray);
att_obj.LevelID = int.Parse(paramArray[0].Value.ToString());
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.CloseDbConn();
}
}
public static bool AddGroup(ATTGroup obj)
{
string SP = "";
if (obj.Action == "A")
{
SP = "sp_add_group";
}
else
{
SP = "sp_edit_group";
}
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam("p_org_id", obj.OrgID, OracleDbType.Int16, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_group_id", obj.GroupID, OracleDbType.Int16, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam("p_group_name", obj.GroupName, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_description", obj.Description, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_active", obj.Active, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_type", obj.Type, OracleDbType.Char, ParameterDirection.Input));
GetConnection DBConn = new GetConnection();
try
{
OracleConnection Conn = DBConn.GetDbConn(Module.OAS);
SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, SP, paramArray.ToArray());
obj.GroupID = int.Parse(paramArray[1].Value.ToString());
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConn.CloseDbConn();
}
}
public static DataTable GetDurationTypeTable(int durationID)
{
string SelectSP = "SP_GET_DURATION_TYPE";
OracleParameter[] paramArray = new OracleParameter[2];
if (durationID > 0)
{
paramArray[0] = Utilities.GetOraParam(":p_DURATION_TYPE_ID", durationID, OracleDbType.Int64, ParameterDirection.Input);
}
else
{
paramArray[0] = Utilities.GetOraParam(":p_DURATON_TYPE_ID", null, OracleDbType.Int64, ParameterDirection.Input);
}
paramArray[1] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output);
GetConnection GetConn = new GetConnection();
try
{
OracleConnection DBConn = GetConn.GetDbConn(Module.DLPDS);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SelectSP, paramArray);
OracleDataReader reader = ((OracleRefCursor)paramArray[1].Value).GetDataReader();
DataTable tbl = new DataTable();
tbl.Load(reader, LoadOption.OverwriteChanges);
return(tbl);
}
catch (Exception ex)
{
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool AddRoles(ATTRoles objATT)
{
PCS.COREDL.GetConnection Conn = new GetConnection();
OracleConnection DBConn;
try
{
DBConn = Conn.GetDbConn();
string InsertUpdateSQL = "";
if (objATT.RoleID <= 0)
{
InsertUpdateSQL = "SP_ADD_ROLES";
}
else
{
InsertUpdateSQL = "SP_EDIT_ROLES";
}
OracleParameter[] ParamArray = new OracleParameter[4];
ParamArray[0] = Utilities.GetOraParam(":p_APPL_ID", objATT.ApplicationID, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":p_ROLE_ID", objATT.RoleID, OracleDbType.Int64, ParameterDirection.InputOutput);
ParamArray[2] = Utilities.GetOraParam(":p_ROLE_NAME", objATT.RoleName, OracleDbType.Varchar2, ParameterDirection.Input);
ParamArray[3] = Utilities.GetOraParam(":p_ROLE_DESCRIPTION", objATT.RoleDescription, OracleDbType.Varchar2, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, InsertUpdateSQL, ParamArray);
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Conn.CloseDbConn();
}
}
//Deleting publisher record
public static bool DeletePublisher(ATTPublisher objPT)
{
string DeleteSQL = "SP_DEL_PUBLISHER";
GetConnection GetConn = new GetConnection();
try
{
OracleConnection DBConn = GetConn.GetDbConn(Module.LIS);
OracleParameter[] paramArray = new OracleParameter[1];
paramArray[0] = Utilities.GetOraParam("p_PUBLISHER_ID", objPT.PublisherID, OracleDbType.Int16, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, DeleteSQL, paramArray);
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool AddMeetingType(ATTMeetingType obj)
{
string SP = "";
if (obj.Action == "A")
{
SP = "SP_ADD_MEETING_TYPE";
}
else if (obj.Action == "E")
{
SP = "SP_EDIT_MEETING_TYPE";
}
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam("p_mtype_id", obj.MeetingTypeID, OracleDbType.Int16, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam("p_mtype_name", obj.MeetingTypeName, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_desc", obj.MeetingTypeDesc, OracleDbType.Varchar2, ParameterDirection.Input));
GetConnection DBConn = new GetConnection();
try
{
OracleConnection Conn = DBConn.GetDbConn(Module.OAS);
SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, SP, paramArray.ToArray());
obj.MeetingTypeID = int.Parse(paramArray[0].Value.ToString());
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConn.CloseDbConn();
}
}
public static bool AddMeetingStatus(ATTMeetingStatus status)
{
string SP = "";
if (status.Action == "A")
{
SP = "sp_add_meeting_status";
}
else if (status.Action == "E")
{
SP = "sp_edit_meeting_status";
}
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam("p_ms_id", status.MeetingStatusID, OracleDbType.Int16, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam("p_ms_name", status.MeetingStatusName, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_ms_color", status.MeetingStatusColor, OracleDbType.Varchar2, ParameterDirection.Input));
GetConnection DBConn = new GetConnection();
try
{
OracleConnection Conn = DBConn.GetDbConn(Module.OAS);
SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, SP, paramArray.ToArray());
status.MeetingStatusID = int.Parse(paramArray[0].Value.ToString());
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConn.CloseDbConn();
}
}
public static int GetDateDifference(string NepaliToDate, string NepaliFromDate)
{
GetConnection DBConn = new GetConnection();
string SQL = "select get_date_diff('" + NepaliToDate + "','" + NepaliFromDate + "') from dual";
try
{
OracleConnection Conn = DBConn.GetDbConn(Module.OAS);
object o = SqlHelper.ExecuteScalar(Conn, CommandType.Text, SQL);
return(int.Parse(o.ToString()));
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConn.CloseDbConn();
}
}
public static bool AddUnit(ATTUnit unit)
{
string SP = "";
if (unit.Action == "A")
{
SP = "sp_add_units";
}
else if (unit.Action == "E")
{
SP = "sp_edit_units";
}
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam("P_UNIT_ID", unit.UnitID, OracleDbType.Int16, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam("P_UNIT_NAME", unit.UnitName, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_UNIT_ADDRESS", unit.UnitAddress, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_UNIT_PHONE", unit.UnitPhone, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_ACTIVE", unit.Active, OracleDbType.Varchar2, ParameterDirection.Input));
GetConnection DBConn = new GetConnection();
try
{
OracleConnection Conn = DBConn.GetDbConn(Module.LJMS);
SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, SP, paramArray.ToArray());
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConn.CloseDbConn();
}
}
public static bool AddLibrary(ATTLibrary obj, Previlege pobj)
{
string SQL;
SQL = "SP_ADD_LIBRARY";
OracleParameter[] paramArray = new OracleParameter[5];
paramArray[0] = Utilities.GetOraParam(":p_LIBRARY_ID", obj.LibraryID, OracleDbType.Int64, ParameterDirection.InputOutput);
paramArray[1] = Utilities.GetOraParam(":p_ORG_ID", obj.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_LIBRARY_NAME", obj.LibraryName, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":p_LOCATION", obj.Location, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":p_ENTRY_BY", obj.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
GetConnection GetConn = new GetConnection();
try
{
OracleConnection DBConn = GetConn.GetDbConn(Module.LIS);
if (Previlege.HasPrevilege(pobj, DBConn) == false)
{
throw new ArgumentException(Utilities.PreviledgeMsg + " add Libaray.");
}
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SQL, paramArray);
obj.LibraryID = int.Parse(paramArray[0].Value.ToString());
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static DataTable GetMessageReceiverListTable(string type, ATTUserLogin login)
{
GetConnection GetConn = new GetConnection();
try
{
string selectSQL = "SELECT distinct ORG_ID,MSG_ID,MSG_SEQ,IS_GRP_RECEIVER,GRP_ORG_ID,GRP_ID,GRP_RECEIVER_ID,GRP_FROM_DATE,OTHER_RECEIVER_ID,R_FIRST_NAME,R_MID_NAME,R_SUR_NAME,READ " +
"FROM VW_MESSAGE_DETAILS WHERE 1 = 1 ";
if (login != null)
{
if (type == "IN")
{
selectSQL += " AND (GRP_RECEIVER_ID = " + login.PID + " OR OTHER_RECEIVER_ID = " + login.PID + ")";
}
else if (type == "OUT")
{
selectSQL += " AND SENDER_ID = " + login.PID;
}
}
OracleConnection DBConn = GetConn.GetDbConn(Module.OAS);
DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, selectSQL);
DataTable tbl = new DataTable();
tbl = (DataTable)ds.Tables[0];
return(tbl);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
GetConn.CloseDbConn();
}
}
/// <summary>
/// Add author object to database
/// </summary>
/// <param name="obj">ATTAuthor object</param>
/// <returns>return bool</returns>
public static bool AddAuthor(ATTAuthor obj, Previlege pobj)
{
string InsertSP;
InsertSP = "SP_ADD_Author";
//Preparing oracle parameter with value
OracleParameter[] paramArray = new OracleParameter[3];
paramArray[0] = Utilities.GetOraParam(":p_AUTHOR_ID", obj.AuthorID, OracleDbType.Int64, ParameterDirection.InputOutput);
paramArray[1] = Utilities.GetOraParam(":p_AUTHOR_NAME", obj.AuthorName, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_ENTRY_BY", obj.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
GetConnection getConn = new GetConnection();
try
{
OracleConnection DBConn = getConn.GetDbConn(Module.LIS);
if (Previlege.HasPrevilege(pobj, DBConn) == false)
{
throw new ArgumentException(Utilities.PreviledgeMsg + " add Author.");
}
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, InsertSP, paramArray);
obj.AuthorID = int.Parse(paramArray[0].Value.ToString());
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
getConn.CloseDbConn();
}
}
public static DataTable GetEmployeeOrganisationInfoListTable(int?empID)
{
GetConnection Conn = new GetConnection();
try
{
string SelectSQL;
if (empID != null)
{
//SelectSQL = "SELECT * FROM vw_employee_organisation_info WHERE EMP_ID =" + empID;
//SelectSQL = "SELECT * FROM VW_EMP_POSTING WHERE EMP_ID =" + 86;
SelectSQL = "SELECT * FROM VW_EMP_POSTING WHERE EMP_ID =" + empID;
}
else
{
SelectSQL = "SELECT * FROM VW_EMP_POSTING";
}
//OracleConnection DBConn = Conn.GetDbConn("PMS_ADMIN", "PMS_ADMIN");
OracleConnection DBConn = Conn.GetDbConn(Module.PMS);
DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, SelectSQL);
DataTable tbl = new DataTable();
tbl = (DataTable)ds.Tables[0];
return(tbl);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
Conn.CloseDbConn();
}
}
/// <summary>
/// Add ItemUnit object to database
/// </summary>
/// <param name="obj">ATTInvItemUnit object</param>
/// <returns>return bool</returns>
public static bool SaveItemUnit(ATTInvItemUnit obj)
{
string InsertSP;
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":p_items_unit_id", obj.ItemUnitID, OracleDbType.Int64, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":p_items_unit_name", obj.ItemUnitName, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_active", obj.Active, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":p_entry_by", obj.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
GetConnection getConn = new GetConnection();
try
{
OracleConnection DBConn = getConn.GetDbConn(Module.OAS);
if (obj.Action == "A") //New Add
{
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, "sp_inv_add_items_unit", paramArray.ToArray());
}
else if (obj.Action == "E") //Update
{
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, "sp_inv_edit_items_unit", paramArray.ToArray());
}
obj.ItemUnitID = int.Parse(paramArray[0].Value.ToString());
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
getConn.CloseDbConn();
}
}
public static bool AddAppointmentStatus(ATTAppointmentStatus objAppntStatus)
{
string SPInsertUpdate = "";
if (objAppntStatus.Action == "A")
{
SPInsertUpdate = "SP_ADD_APPOINTMENT_STATUS";
}
else if (objAppntStatus.Action == "E")
{
SPInsertUpdate = "SP_EDIT_APPOINTMENT_STATUS";
}
OracleParameter[] paramArray = new OracleParameter[3];
paramArray[0] = Utilities.GetOraParam("P_STATUS_ID", objAppntStatus.AppointmentStatusID, OracleDbType.Int16, ParameterDirection.InputOutput);
paramArray[1] = Utilities.GetOraParam("P_STATUS_NAME", objAppntStatus.AppointmentStatusName, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam("P_STATUS_COLOR", objAppntStatus.AppointmentStatusColor, OracleDbType.Varchar2, ParameterDirection.Input);
GetConnection DBConn = new GetConnection();
OracleConnection Conn = DBConn.GetDbConn(Module.OAS);
try
{
SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, SPInsertUpdate, paramArray);
objAppntStatus.AppointmentStatusID = int.Parse(paramArray[0].Value.ToString());
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConn.CloseDbConn();
}
}
public static bool SaveParticipant(List <ATTParticipant> lstParticipant)
{
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.DLPDS).BeginTransaction();
string InsertUpdateParticipantSql = "SP_ADD_PARTICIPANT";
try
{
foreach (ATTParticipant attP in lstParticipant)
{
OracleParameter[] ParamArray = new OracleParameter[4];
ParamArray[0] = Utilities.GetOraParam(":P_ORG_ID", attP.OrgID, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":P_PROGRAM_ID", attP.ProgramID, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[2] = Utilities.GetOraParam(":P_P_ID", attP.PID, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[3] = Utilities.GetOraParam(":P_JOINING_DATE", attP.JoiningDate, OracleDbType.Varchar2, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, InsertUpdateParticipantSql, ParamArray);
}
Tran.Commit();
return(true);
}
catch (OracleException oex)
{
PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError();
throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message));
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool SavePropertyDetails(List <ATTPropertyDetails> lstPropertyDetails)
{
GetConnection Conn = new GetConnection();
try
{
string SaveSQL = "SP_ADD_PROPERTYDETAILS";
//OracleConnection DBConn = Conn.GetDbConn("PMS_ADMIN", "PMS_ADMIN");
OracleConnection DBConn = Conn.GetDbConn(Module.PMS);
foreach (ATTPropertyDetails objPD in lstPropertyDetails)
{
OracleParameter[] paramArray = new OracleParameter[6];
paramArray[0] = Utilities.GetOraParam(":P_EMP_ID", objPD.EmpID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_SUB_DATE", objPD.SubDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":P_PCAT_ID", objPD.PCatID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":P_COL_NO", objPD.ColNo, OracleDbType.Int64, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":P_ROW_NO", objPD.RowNo, OracleDbType.Int64, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":P_VALUE", objPD.Value, OracleDbType.Varchar2, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SaveSQL, paramArray);
}
return(true);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
Conn.CloseDbConn();
}
}