public static DataTable GetPrivateLawyerDetailsTable(double PID)
{
GetConnection GetConn = new GetConnection();
try
{
string selectSQL = " SELECT DISTINCT P_ID,LAWYER_TYPE_ID,lawyer_type_description,LICENSE_NO,P_FROM_DATE,UNIT_ID,unit_name FROM vw_lawyer_info_details " +
" WHERE P_FROM_DATE IS NOT NULL AND UNIT_ID IS NOT NULL ";
if (PID > 0)
{
selectSQL += " AND P_ID = " + PID;
}
OracleConnection DBConn = GetConn.GetDbConn(Module.LJMS);
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 DataTable GetLibraryNameListTable()
{
GetConnection GetConn = new GetConnection();
try
{
//string SelectSQL = "select * from library";
string SelectSQL = "select distinct Library_ID,Library_Name,org_id from vw_library_info order by library_Name ASC";
//OracleConnection DBConn = GetConn.GetDbConn("LIS_ADMIN", "LIS_ADMIN");
OracleConnection DBConn = GetConn.GetDbConn(Module.LIS);
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 DataTable GetDocumentsType(int?docTypeId)
{
GetConnection GetCon = new GetConnection();
OracleConnection DBConn;
OracleCommand Cmd;
try
{
DBConn = GetCon.GetDbConn();
Cmd = new OracleCommand();
string SelectSql = "SP_GET_DOCUMENTS_TYPE";
OracleParameter[] ParamArray = new OracleParameter[2];
ParamArray[0] = Utilities.GetOraParam(":p_DOC_TYPE_ID", docTypeId, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output);
DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, SelectSql, ParamArray);
return((DataTable)ds.Tables[0]);
}
catch (OracleException oex)
{
PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError();
throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Edit author object to database
/// </summary>
/// <param name="obj">ATTAuthor object</param>
/// <returns>return bool</returns>
public static bool EditAuthor(ATTAuthor obj, Previlege pobj)
{
string EditSP;
EditSP = "SP_EDIT_AUTHOR";
//Preparing oracle parameter with value
OracleParameter[] paramArray = new OracleParameter[2];
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);
GetConnection getConn = new GetConnection();
try
{
OracleConnection DBConn = getConn.GetDbConn(Module.LIS);
if (Previlege.HasPrevilege(pobj, DBConn) == false)
{
throw new ArgumentException(Utilities.PreviledgeMsg + " update Author.");
}
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, EditSP, paramArray);
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
getConn.CloseDbConn();
}
}
public static bool DeletePost(int PostID)
{
GetConnection Conn = new GetConnection();
OracleConnection DBConn;
OracleTransaction Tran;
string DeletePostSql = "SP_DEL_POST ";
try
{
DBConn = Conn.GetDbConn(Module.DLPDS);
Tran = DBConn.BeginTransaction();
OracleParameter[] ParamArray = new OracleParameter[1];
ParamArray[0] = Utilities.GetOraParam(":p_POST_ID", PostID, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, DeletePostSql, ParamArray);
Tran.Commit();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
//public static DataTable SearchDocumentListTable(int? orgID, int? unitID, int? docID, string docName,string status,int queryID)
public static DataTable SearchDocumentListTable(int?orgID, int?unitID, int?docID, string docName, string status)
{
GetConnection GetConn = new GetConnection();
try
{
//QueryID = queryID;
string SearchSQL;
SearchSQL = QueryBuilder(orgID, unitID, docID, docName, status);
OracleConnection DBConn = GetConn.GetDbConn(Module.OAS);
DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, SearchSQL);
DataTable tbl = new DataTable();
tbl = (DataTable)ds.Tables[0];
return(tbl);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
GetConn.CloseDbConn();
}
}
public static DataTable GetDesignationLevelTable()
{
string SelectDL;
SelectDL = "SP_GET_LEVEL";
OracleParameter[] paramArray = new OracleParameter[1];
//paramArray[0] = Utilities.GetOraParam(":Level_ID", levelID, OracleDbType.Int64, ParameterDirection.InputOutput);
//paramArray[1] = Utilities.GetOraParam(":Level_Name", levelName, OracleDbType.Varchar2, ParameterDirection.Output);
paramArray[0] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output);
GetConnection conn = new GetConnection();
try
{
OracleConnection DBconn = conn.GetDbConn(Module.PMS);
SqlHelper.ExecuteNonQuery(DBconn, CommandType.StoredProcedure, SelectDL, paramArray);
OracleDataReader rdr = ((OracleRefCursor)paramArray[0].Value).GetDataReader();
DataTable tbl = new DataTable();
tbl.Load(rdr, LoadOption.OverwriteChanges);
return(tbl);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.CloseDbConn();
}
}
public static DataTable GetMeetingResponseTable(ATTMeetingResponse objMResponse)
{
//string SelectSQL = "SP_GET_PART_RESPONSE";
string SelectSQL = "SP_GET_MINUTE_NOTE";
OracleParameter[] paramArray = new OracleParameter[3];
paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", objMResponse.OrgID, OracleDbType.Int16, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_MEETING_ID", objMResponse.MeetingID, OracleDbType.Int16, ParameterDirection.Input);
//paramArray[2] = Utilities.GetOraParam(":p_PARTICIPANT_ID",objMResponse.ParticipantID, OracleDbType.Int16, ParameterDirection.Input);
//paramArray[3] = Utilities.GetOraParam(":p_RESPONSE_ID",objMResponse.ResponseID, OracleDbType.Int16, ParameterDirection.Input);
paramArray[2] = 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[2].Value).GetDataReader();
DataTable tbl = new DataTable();
tbl.Load(reader, LoadOption.OverwriteChanges);
return(tbl);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool DeleteInstitution(int InstitutionID)
{
GetConnection Conn = new GetConnection();
OracleConnection DBConn = Conn.GetDbConn();
OracleTransaction Tran = DBConn.BeginTransaction();
string DeleteInstitutionSql = "SP_DEL_INSTITUTIONS";
try
{
OracleParameter[] ParamArray = new OracleParameter[1];
ParamArray[0] = Utilities.GetOraParam(":p_INSTITUTION_ID", InstitutionID, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, DeleteInstitutionSql, ParamArray);
Tran.Commit();
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Conn.CloseDbConn();
}
}
public static DataTable GetEmployeePostingHistory(int empID, int?orgID)
{
GetConnection GetConn = new GetConnection();
try
{
string selectSQL = " SELECT EMP_ID,ORG_ID,ORG_NAME,DES_ID,DES_NAME,POSTING_TYPE_ID,POSTING_TYPE_NAME,FROM_DATE,CREATED_DATE,POST_ID " +
" FROM VW_EMP_POSTING_HISTORY WHERE EMP_ID=" + empID;
if (orgID != null)
{
selectSQL = selectSQL + " AND ORG_ID=" + orgID;
}
OracleConnection DBConn = GetConn.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
{
GetConn.CloseDbConn();
}
}
public static bool SaveMeetingResponse(ATTMeetingResponse objMResponse)
{
string SaveSQL = "SP_UPDATE_MINUTE_NOTE";
OracleParameter[] paramArray = new OracleParameter[5];
paramArray[0] = Utilities.GetOraParam(":P_ORG_ID", objMResponse.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":P_MEETING_ID", objMResponse.MeetingID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":P_PART_ID", objMResponse.ParticipantID, OracleDbType.Int64, ParameterDirection.Input);
//paramArray[3] = Utilities.GetOraParam(":P_RESPONSE_ID", null, OracleDbType.Int64, ParameterDirection.InputOutput);
paramArray[3] = Utilities.GetOraParam(":P_NOTE", objMResponse.Response, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":P_IS_AGREE", objMResponse.IsAgree, OracleDbType.Varchar2, ParameterDirection.Input);
GetConnection GetConn = new GetConnection();
OracleConnection DBConn = GetConn.GetDbConn(Module.OAS);
OracleTransaction Tran = DBConn.BeginTransaction();
try
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, SaveSQL, paramArray);
Tran.Commit();
return(true);
}
catch (Exception ex)
{
Tran.Rollback();
throw (ex);
}
finally
{
GetConn.CloseDbConn();
}
}
public static DataTable GetEmployeeWithPostingListTable(int?orgID)
{
GetConnection GetConn = new GetConnection();
try
{
string selectSQL = " SELECT ORG_ID,P_ID,FIRST_NAME,MID_NAME,SUR_NAME,DES_ID,DES_NAME " +
" FROM vw_employee_with_posting";
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 string GetEmployeeCurrentPosting(double empId)
{
try
{
GetConnection Conn = new GetConnection();
OracleConnection DBConn;
DBConn = Conn.GetDbConn(Module.PMS);
object obj = DBConn;
string SelectSql = "SP_GET_EMP_CURRENT_POST";
OracleParameter[] ParamArray = new OracleParameter[3];
ParamArray[0] = Utilities.GetOraParam(":p_EMP_ID", empId, OracleDbType.Double, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":P_DES_ID", null, OracleDbType.Int64, ParameterDirection.InputOutput);
ParamArray[2] = Utilities.GetOraParam(":P_DES_NAME", null, OracleDbType.Varchar2, ParameterDirection.InputOutput);
ParamArray[2].Size = 100;
SqlHelper.ExecuteNonQuery((OracleConnection)obj, CommandType.StoredProcedure, SelectSql, ParamArray);
int DesgID = int.Parse(ParamArray[1].Value.ToString());
string DesName = ParamArray[2].Value.ToString();
return(DesgID.ToString() + " " + DesName);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable SearchEmployee(ATTOrgUnitHead objOrgUnitHead)
{
try
{
string strSelect = "";
strSelect = "SELECT * FROM VW_EMP_ORG_UNIT_HEAD WHERE 1=1 ";
List <OracleParameter> ParamList = new List <OracleParameter>();
if (objOrgUnitHead.OrgID > 0)
{
strSelect += "AND ORG_ID = :OrgID";
ParamList.Add(Utilities.GetOraParam(":OrgID", objOrgUnitHead.OrgID, OracleDbType.Int64, ParameterDirection.Input));
}
strSelect += " ORDER BY ORG_ID";
GetConnection conn = new GetConnection();
OracleConnection obj = conn.GetDbConn(Module.PMS);
DataSet ds = SqlHelper.ExecuteDataset(obj, CommandType.Text, strSelect, ParamList.ToArray());
return((DataTable)ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable GetEmpAttendance(int orgid, int desid, string yearmonth)
{
GetConnection conn = new GetConnection();
OracleConnection obj = conn.GetDbConn(Module.PMS);
string SelectSP = "SP_GET_EMP_ATTENDANCE";
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam("P_ORG_ID", orgid, OracleDbType.Int32, ParameterDirection.Input));
if (desid == 0)
{
paramArray.Add(Utilities.GetOraParam("P_DES_ID", null, OracleDbType.Int32, ParameterDirection.Input));
}
else if (desid > 0)
{
paramArray.Add(Utilities.GetOraParam("P_DES_ID", desid, OracleDbType.Int32, ParameterDirection.Input));
}
paramArray.Add(Utilities.GetOraParam("P_YEARMONTH", yearmonth, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_RC", null, OracleDbType.RefCursor, ParameterDirection.InputOutput));
try
{
return(SqlHelper.ExecuteDataset(CommandType.StoredProcedure, SelectSP, Module.PMS, paramArray.ToArray()).Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable GetEmployeeLeave(int?empID, string REQ_REC_APP)
{
try
{
GetConnection conn = new GetConnection();
OracleConnection obj = conn.GetDbConn(Module.PMS);
string GetLeaveTypeEmployeeSQL = "";
if (REQ_REC_APP == "REQ")
{
GetLeaveTypeEmployeeSQL = "SP_GET_EMP_LEAVE_REQ";
}
else if (REQ_REC_APP == "REC")
{
GetLeaveTypeEmployeeSQL = "SP_GET_EMP_LEAVE_REC";
}
else if (REQ_REC_APP == "APP")
{
GetLeaveTypeEmployeeSQL = "SP_GET_EMP_LEAVE_APP";
}
OracleParameter[] ParamArray = new OracleParameter[2];
ParamArray[0] = Utilities.GetOraParam(":P_EMP_ID", empID, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output);
DataSet ds = SqlHelper.ExecuteDataset(obj, CommandType.StoredProcedure, GetLeaveTypeEmployeeSQL, ParamArray);
return((DataTable)ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
}
public static bool UpdateMaterialCategory(ATTMaterialCategory objMC, Previlege pobj)
{
string UpdateSQL;
UpdateSQL = "SP_EDIT_MATERIAL_CATEGORY";
GetConnection GetConn = new GetConnection();
try
{
OracleConnection DBConn = GetConn.GetDbConn(Module.LIS);
if (Previlege.HasPrevilege(pobj, DBConn) == false)
{
throw new ArgumentException(Utilities.PreviledgeMsg + " update Material Category.");
}
OracleParameter[] paramArray = new OracleParameter[3];
paramArray[0] = Utilities.GetOraParam(":p_CATEGORY_ID", objMC.CategoryID, OracleDbType.Int16, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_CATEGORY_NAME", objMC.CategoryName, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_CATEGORY_DESCRIPTION", objMC.CategoryDescription, OracleDbType.Varchar2, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, UpdateSQL, paramArray);
return(true);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
GetConn.CloseDbConn();
}
}
public static object GetLoginEmpType(int orgID, int subjectID, double empID)
{
string SQL = "SP_GET_LOGIN_EMP_TYPE";
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam("p_org_id", orgID, OracleDbType.Int16, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_sub_id", subjectID, OracleDbType.Int16, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_emp_id", empID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_type", "m", OracleDbType.Varchar2, ParameterDirection.InputOutput));
paramArray[3].Size = 10;
GetConnection DBConn = new GetConnection();
try
{
OracleConnection Conn = DBConn.GetDbConn(Module.OAS);
SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, SQL, paramArray.ToArray());
object obj = paramArray[3].Value;
return(obj);
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConn.CloseDbConn();
}
}
//public static int QueryID;
public static DataTable GetDocumentNameListTable(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();
}
}
public static int CheckLoginUserIsChannelMember(int orgId, int subjectID, double empID)
{
string SQL = "SP_CHK_LOGIN_USER_IS_CHNL_PER";
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam("p_org_id", orgId, OracleDbType.Int16, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_subject_id", subjectID, OracleDbType.Int16, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_emp_id", empID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("p_count", null, OracleDbType.Int32, ParameterDirection.InputOutput));
paramArray[3].Size = 4;
GetConnection DBConn = new GetConnection();
try
{
OracleConnection Conn = DBConn.GetDbConn(Module.OAS);
SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, SQL, paramArray.ToArray());
object obj = paramArray[3].Value;
return(int.Parse(obj.ToString()));
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConn.CloseDbConn();
}
}
public static bool UpdateResourcePersonMarks(List <ATTSessionCourseMember> lst)
{
string UpdateSP = "";
foreach (ATTSessionCourseMember obj in lst)
{
UpdateSP = "SP_UPDATE_RP_MARKS";
OracleParameter[] paramArray = new OracleParameter[7];
paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", obj.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_PROGRAM_ID", obj.ProgramID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_SESSION_ID", obj.SessionID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":p_COURSE_ID", obj.CourseID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":p_FACULTY_ID", obj.FacultyID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":p_PID", obj.PID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":p_MARKS_OBTAINED", obj.MarksObtained, OracleDbType.Int64, ParameterDirection.Input);
GetConnection GetConn = new GetConnection();
try
{
OracleConnection DBConn = GetConn.GetDbConn(Module.DLPDS);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, UpdateSP, paramArray);
}
catch (Exception ex)
{
throw ex;
}
}
return(true);
}
public static DataTable GetMelMilaapKartaaOath(int orgID, double mmkID)
{
GetConnection GetConn = new GetConnection();
OracleConnection DBConn = GetConn.GetDbConn(Module.CMS);
string SelectSQL = "SP_GET_MM_KARTAA_OATH";
try
{
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_ORG_ID", orgID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_PERSON_ID", mmkID, OracleDbType.Double, ParameterDirection.Input));
//paramArray.Add(Utilities.GetOraParam(":P_FROM_DATE", attMMK.FromDate, OracleDbType.Varchar2, ParameterDirection.Input));
//paramArray.Add(Utilities.GetOraParam(":P_JUDGE_ID", attMMK.PID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output));
DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.StoredProcedure, SelectSQL, paramArray.ToArray());
DataTable tbl = new DataTable();
tbl = (DataTable)ds.Tables[0];
return(tbl);
}
catch (Exception ex)
{
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static DataTable GetItemCategory(int?itemCategoryID, string itemCatActive)
{
string SelectSP;
SelectSP = "sp_inv_get_items_category"; //p_items_category_id, p_active, p_rc
OracleParameter[] paramArray = new OracleParameter[3];
paramArray[0] = Utilities.GetOraParam(":p_items_category_id", itemCategoryID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_active", itemCatActive, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[2] = 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, SelectSP, paramArray);
OracleDataReader reader = ((OracleRefCursor)paramArray[2].Value).GetDataReader();
DataTable tbl = new DataTable();
tbl.Load(reader, LoadOption.OverwriteChanges);
return(tbl);
}
catch (Exception ex)
{
throw ex;
}
finally
{
getConn.CloseDbConn();
}
}
public static int CheckExistsOrgItems(ATTInvOrgItems objInvOItems)
{
try
{
GetConnection GetConn = new GetConnection();
OracleConnection DBConn = GetConn.GetDbConn(Module.OAS);
string sql = "SELECT CHECK_EXISTS_INV_ORG_ITEM(" + objInvOItems.OrgID + ","
+ objInvOItems.ItemsCategoryID + ","
+ objInvOItems.ItemsSubCategoryID + ",'"
+ objInvOItems.ItemsID + "')" +
"FROM DUAL";
DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, sql);
DataTable tbl = new DataTable();
tbl = (DataTable)ds.Tables[0];
int existsOrgItems = int.Parse(tbl.Rows[0][0].ToString());
return(existsOrgItems);
}
catch (Exception ex)
{
throw (ex);
}
}
public static DataTable GetItemsSubCategory(int?itemCategoryID, string active)
{
List <OracleParameter> param = new List <OracleParameter>();
param.Add(Utilities.GetOraParam(":p_items_category_id", itemCategoryID, OracleDbType.Int64, ParameterDirection.Input));
param.Add(Utilities.GetOraParam(":p_active", active, OracleDbType.Varchar2, ParameterDirection.Input));
param.Add(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, "sp_inv_get_items_sub_category", param.ToArray());
OracleDataReader reader = ((OracleRefCursor)param[2].Value).GetDataReader();
DataTable tbl = new DataTable();
tbl.Load(reader, LoadOption.OverwriteChanges);
return(tbl);
}
catch (Exception ex)
{
throw ex;
}
finally
{
getConn.CloseDbConn();
}
}
public static bool SaveSubmissionDetails(ATTSubmissionDetails objSD)
{
GetConnection Conn = new GetConnection();
try
{
string SaveSQL = "SP_ADD_SUBMISSIONDETAILS";
//OracleConnection DBConn = Conn.GetDbConn("PMS_ADMIN", "PMS_ADMIN");
OracleConnection DBConn = Conn.GetDbConn(Module.PMS);
OracleParameter[] paramArray = new OracleParameter[4];
paramArray[0] = Utilities.GetOraParam(":P_EMP_ID", objSD.EmpID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_SUB_DATE", objSD.SubmissionDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_SUB_OFFICE", objSD.SubmissionOffice, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam("::p_SUB_OFFICE_PLACE", objSD.SubmissionPlace, OracleDbType.Varchar2, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SaveSQL, paramArray);
return(true);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
Conn.CloseDbConn();
}
}
public static DataTable GetLibraryTable(int orgID, int?libraryID)
{
string SelectSQL = "SP_GET_LIBRARY";
OracleParameter[] paramArray = new OracleParameter[3];
paramArray[0] = Utilities.GetOraParam(":p_LIBRARY_ID", libraryID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_ORG_ID", orgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output);
GetConnection GetConn = new GetConnection();
try
{
OracleConnection DBConn = GetConn.GetDbConn(Module.LIS);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SelectSQL, paramArray);
OracleDataReader reader = ((OracleRefCursor)paramArray[2].Value).GetDataReader();
DataTable tbl = new DataTable();
tbl.Load(reader, LoadOption.OverwriteChanges);
return(tbl);
}
catch (Exception ex)
{
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool DeleteAppointmentEvents(ATTAppointment objAppointment)
{
string deleteSQL = "SP_DEL_APPOINTMENT";
int countAppointee = objAppointment.LstAppointee.Count;
OracleParameter[] paramArray = new OracleParameter[2];
paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", objAppointment.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_APPOINTMENT_ID", objAppointment.AppointmentID, OracleDbType.Int64, ParameterDirection.InputOutput);
GetConnection GetConn = new GetConnection();
OracleConnection DBConn = GetConn.GetDbConn(Module.OAS);
OracleTransaction Tran = DBConn.BeginTransaction();
try
{
if (countAppointee > 0)
{
DLLAppointee.DeleteAppointee(objAppointment, null, 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 DeleteLeaveType(int LeaveTypeID)
{
GetConnection Conn = new GetConnection();
OracleConnection DBConn;
OracleTransaction Tran;
string DeleteLeaveTypeSql = "SP_DEL_LEAVE_TYPES";
try
{
DBConn = Conn.GetDbConn(Module.PMS);
Tran = DBConn.BeginTransaction();
OracleParameter[] ParamArray = new OracleParameter[1];
ParamArray[0] = Utilities.GetOraParam(":p_LEAVE_TYPE_ID", LeaveTypeID, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, DeleteLeaveTypeSql, ParamArray);
Tran.Commit();
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Conn.CloseDbConn();
}
}
public static bool SavePropertyCategoryCols(List <ATTPropertyCategoryColumns> lstPCCols)
{
GetConnection Conn = new GetConnection();
try
{
string SaveSQL = "SP_ADD_PROPERTY_CAT_COL";
OracleConnection DBConn = Conn.GetDbConn(Module.PMS);
foreach (ATTPropertyCategoryColumns objPCCols in lstPCCols)
{
OracleParameter[] paramArray = new OracleParameter[5];
paramArray[0] = Utilities.GetOraParam(":P_PCAT_ID ", objPCCols.PCategoryID, OracleDbType.Int64, ParameterDirection.InputOutput);
paramArray[1] = Utilities.GetOraParam(":P_COL_NAME ", objPCCols.ColName, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":P_COL_NO ", objPCCols.ColNo, OracleDbType.Int64, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":P_COL_DATA_TYPE ", objPCCols.ColDataType, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":P_ACTIVE ", objPCCols.Active, OracleDbType.Varchar2, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SaveSQL, paramArray);
}
return(true);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
Conn.CloseDbConn();
}
}
