public static DataTable PropertyReportSearch(ATTEmployeeDetailSearch objRptSearch)
{
GetConnection GetConn = new GetConnection();
try
{
string SearchSQL = "";
SearchSQL = QueryBuilder(objRptSearch);
OracleConnection DBConn = GetConn.GetDbConn(Module.PMS);
DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, SearchSQL);
DataTable tbl = new DataTable();
tbl = (DataTable)ds.Tables[0];
return(tbl);
}
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;
}
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;
}
}
public static DataTable GetReligions(int?relgId)
{
try
{
string SelectSql = "SP_GET_RELIGIONS";
OracleParameter[] ParamArray = new OracleParameter[2];
ParamArray[0] = Utilities.GetOraParam(":ReligionID", relgId, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output);
//DataSet ds=SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SelectSql, ParamArray);
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;
}
}
public static bool SaveOrgCaseType(List <ATTOrganizationCaseType> lstOrgCaseType, int caseTypeID, OracleTransaction Tran)
{
string InsertUpdateSQL = "SP_SAVE_ORG_CASE_TYPE";
try
{
foreach (ATTOrganizationCaseType objOrgCaseType in lstOrgCaseType)
{
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_ORG_ID", objOrgCaseType.OrgID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_CASE_TYPE_ID", caseTypeID, OracleDbType.Int32, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ACTIVE", objOrgCaseType.Active, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objOrgCaseType.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
SqlHelper.ExecuteNonQuery(Tran, System.Data.CommandType.StoredProcedure, InsertUpdateSQL, paramArray.ToArray());
}
return(true);
}
catch (OracleException oex)
{
PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError();
Tran.Rollback();
throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message));
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
}
public static DataTable GetAllOrganization(int? orgID, string orgType, string orgEquCode)
{
try
{
string SelectSql = "SP_GET_ORGNIZATIONS";
OracleParameter[] ParamArray = new OracleParameter[4];
ParamArray[0] = Utilities.GetOraParam(":p_ORG_ID", orgID, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":p_org_type", orgType, OracleDbType.Varchar2, ParameterDirection.Input);
ParamArray[2] = Utilities.GetOraParam(":p_org_equ_code", orgEquCode, OracleDbType.Varchar2, ParameterDirection.Input);
ParamArray[3] = 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;
}
}
public static DataTable GetWards(int?distCode, int?vdcCode)
{
try
{
string SelectSql = "SP_GET_WARDS";
OracleParameter[] ParamArray = new OracleParameter[3];
ParamArray[0] = Utilities.GetOraParam(":DistCode", distCode, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":VDCCode", vdcCode, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[2] = 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;
}
}
public static bool SaveBenchJudge(List <ATTBenchJudge> lstBenchJudge, OracleTransaction Tran, int seqNo)
{
string InsertUpdateSQL = "SP_ADD_BENCH_JUDGE";
try
{
foreach (ATTBenchJudge objBenchJudge in lstBenchJudge)
{
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_ORG_ID", objBenchJudge.OrgID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_BENCH_TYPE_ID", objBenchJudge.BenchTypeID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_BENCH_NO", objBenchJudge.BenchNo, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_FROM_DATE", objBenchJudge.FromDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_SEQ_NO", seqNo, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_JUDGE_ID", objBenchJudge.JudgeID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_BJ_FROM_DATE", objBenchJudge.BJFromDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objBenchJudge.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
SqlHelper.ExecuteNonQuery(Tran, System.Data.CommandType.StoredProcedure, InsertUpdateSQL, paramArray.ToArray());
}
return(true);
}
catch (OracleException oex)
{
PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError();
throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message));
}
}
public static DataTable GetOrgWithChilds(int orgID)
{
try
{
string SelectSql = "SP_GET_ORG_WITH_CHILDS";
OracleParameter[] ParamArray = new OracleParameter[2];
ParamArray[0] = Utilities.GetOraParam(":p_ORG_ID", orgID, 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;
}
}
public static bool AddEditDeleteTameliWitnessPerson(List <ATTTameliWitnessPerson> TameliWitPersonLIST, OracleTransaction Tran)
{
string InsertUpdateSQL = "";
List <OracleParameter> paramList = new List <OracleParameter>();
try
{
foreach (ATTTameliWitnessPerson TamWitPerson in TameliWitPersonLIST)
{
if (TamWitPerson.Action == "A")
{
InsertUpdateSQL = "SP_ADD_TAMELI_WIT_PERSON";
}
else if (TamWitPerson.Action == "E")
{
InsertUpdateSQL = "SP_EDIT_TAMELI_WIT_PERSON";
}
else if (TamWitPerson.Action == "D")
{
InsertUpdateSQL = "SP_DEL_TAMELI_WIT_PERSON";
}
if (TamWitPerson.Action == "A" || TamWitPerson.Action == "E")
{
paramList.Add(Utilities.GetOraParam(":P_CASE_ID", TamWitPerson.CaseID, OracleDbType.Int64, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_LITIGANT_ID", TamWitPerson.LitigantID, OracleDbType.Int64, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_ISSUE_DATE", TamWitPerson.IssuedDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_SEQ_NO", TamWitPerson.SeqNo, OracleDbType.Int64, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_WIT_SEQ_NO", TamWitPerson.WitSeqNo, OracleDbType.Int64, ParameterDirection.InputOutput));
paramList.Add(Utilities.GetOraParam(":P_PERSON_FULL_NAME", TamWitPerson.FullName, OracleDbType.Varchar2, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_PESON_POST", TamWitPerson.Post, OracleDbType.Varchar2, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_ENTRY_BY", TamWitPerson.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
}
if (TamWitPerson.Action == "D")
{
paramList.Add(Utilities.GetOraParam(":P_CASE_ID", TamWitPerson.CaseID, OracleDbType.Int64, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_LITIGANT_ID", TamWitPerson.LitigantID, OracleDbType.Int64, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_ISSUE_DATE", TamWitPerson.IssuedDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_SEQ_NO", TamWitPerson.SeqNo, OracleDbType.Int64, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_WIT_SEQ_NO", TamWitPerson.WitSeqNo, OracleDbType.Int64, ParameterDirection.InputOutput));
}
SqlHelper.ExecuteNonQuery(Tran, System.Data.CommandType.StoredProcedure, InsertUpdateSQL, paramList.ToArray());
paramList.Clear();
}
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;
}
}
public static DataTable GetMaagFaaramHead(ATTMaagFaaramHead objMaagFaaramHead)
{
string strSql = "SELECT * FROM VW_MAAG_FAARAM_HEAD WHERE 1=1";
List <OracleParameter> paramArray = new List <OracleParameter>();
if (objMaagFaaramHead.OrgID != null)
{
strSql += " AND ORG_ID = :P_ORG_ID";
paramArray.Add(Utilities.GetOraParam(":P_ORG_ID", objMaagFaaramHead.OrgID, OracleDbType.Int64, ParameterDirection.Input));
}
if (objMaagFaaramHead.UnitID != null)
{
strSql += " AND UNIT_ID = :P_UNIT_ID";
paramArray.Add(Utilities.GetOraParam(":P_UNIT_ID", objMaagFaaramHead.UnitID, OracleDbType.Int64, ParameterDirection.Input));
}
if (objMaagFaaramHead.SelectApproval == true)
{
if (objMaagFaaramHead.AppYesNo == null)
{
strSql += " AND APP_YES_NO IS NULL";
}
else
{
strSql += " AND APP_YES_NO = :P_APP_YES_NO";
paramArray.Add(Utilities.GetOraParam(":P_APP_YES_NO", objMaagFaaramHead.AppYesNo, OracleDbType.Varchar2, ParameterDirection.Input));
}
}
if (objMaagFaaramHead.SelectIssue == true)
{
if (objMaagFaaramHead.IssueFlag == null)
{
strSql += " AND ISSUE_FLAG IS NULL";
}
else
{
strSql += " AND ISSUE_FLAG = :P_ISSUE_FLAG";
paramArray.Add(Utilities.GetOraParam(":P_ISSUE_FLAG", objMaagFaaramHead.IssueFlag, OracleDbType.Varchar2, ParameterDirection.Input));
}
}
strSql += " ORDER BY ORG_ID,UNIT_ID,REQ_NO";
try
{
DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, strSql, Module.OAS, paramArray.ToArray());
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;
}
}
public static bool SaveEmployeePosting(List <ATTEmployeePosting> lstEmployeePosting, OracleTransaction Tran, double empID)
{
try
{
foreach (ATTEmployeePosting lst in lstEmployeePosting)
{
OracleParameter[] paramArray = new OracleParameter[18];
paramArray[0] = Utilities.GetOraParam(":p_EMP_ID", empID, OracleDbType.Double, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_ORG_ID", lst.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_DES_ID", lst.DesID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":p_CREATED_DATE", lst.CreatedDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":p_POST_ID", lst.PostID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":p_FROM_DATE", lst.FromDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":p_TO_DATE", lst.ToDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[7] = Utilities.GetOraParam(":p_JOINING_DATE", lst.JoiningDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[8] = Utilities.GetOraParam(":p_DECISION_DATE", lst.DecisionDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[9] = Utilities.GetOraParam(":p_LEAVE_DATE", lst.LeaveDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[10] = Utilities.GetOraParam(":p_POSTING_TYPE_ID", lst.PostingTypeID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[11] = Utilities.GetOraParam(":p_EMP_POSTING_SALARY", lst.EmpSalary, OracleDbType.Int64, ParameterDirection.Input);
paramArray[12] = Utilities.GetOraParam(":p_EMP_POSTING_ALLOWANCE", lst.EmpAllowance, OracleDbType.Int64, ParameterDirection.Input);
paramArray[13] = Utilities.GetOraParam(":p_EMP_POSTING_KITAAB_DARTA_NO", lst.EmpKitaabDartaNo, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[14] = Utilities.GetOraParam(":p_EMP_POSTING_REMARKS", lst.EmpPostingRemarks, OracleDbType.Varchar2, ParameterDirection.Input);
if (lst.PostingAttachmentDocs != null && lst.PostingAttachmentDocs.Length > 0)
{
paramArray[15] = Utilities.GetOraParam(":p_ATTACHMENT", lst.PostingAttachmentDocs, OracleDbType.Blob, ParameterDirection.Input);
}
else
{
paramArray[15] = Utilities.GetOraParam(":p_ATTACHMENT", null, OracleDbType.Blob, ParameterDirection.Input);
}
paramArray[16] = Utilities.GetOraParam(":p_ATTACHMENT_FILE_NAME", lst.PostingAttachmentContent, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[17] = Utilities.GetOraParam(":p_ENTRY_BY", lst.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
if (lst.Action == "A")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_EMP_POSTINGS", paramArray);
}
else if (lst.Action == "E")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_EMP_POSTINGS", paramArray);
}
}
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)
{
throw ex;
}
}
public static bool SaveApplication(ATTApplication objApplication)
{
string InsertUpdateSQL = "";
List <OracleParameter> paramArray = new List <OracleParameter>();
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
try
{
paramArray.Add(Utilities.GetOraParam(":P_APPLICATION_ID", objApplication.ApplicationID, OracleDbType.Int64, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":P_APPLICATION_NAME", objApplication.ApplicationName, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_APPLICATION_TYPE", objApplication.ApplicationType, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ACTIVE", objApplication.Active, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objApplication.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
if (objApplication.Action == "A")
{
InsertUpdateSQL = "SP_ADD_APPLICATION";
}
else if (objApplication.Action == "E")
{
InsertUpdateSQL = "SP_EDIT_APPLICATION";
}
SqlHelper.ExecuteNonQuery(Tran, System.Data.CommandType.StoredProcedure, InsertUpdateSQL, paramArray.ToArray());
objApplication.ApplicationID = int.Parse(paramArray[0].Value.ToString());
objApplication.Action = "";
if (objApplication.OrgApplicationLST != null)
{
DLLOrgApplication.SaveOrgApplication(objApplication.OrgApplicationLST, objApplication.ApplicationID, Tran);
}
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 SavePersonDocuments(List <ATTPersonDocuments> lstPersonDocuments, OracleTransaction Tran, double personID)
{
try
{
lstPersonDocuments.RemoveAll(delegate(ATTPersonDocuments obj)
{
return(obj.Action == "");
});
foreach (ATTPersonDocuments lst in lstPersonDocuments)
{
if (lst.Action == "D")
{
OracleParameter[] deleteparamArray = new OracleParameter[2];
deleteparamArray[0] = Utilities.GetOraParam(":p_P_ID", personID, OracleDbType.Double, ParameterDirection.Input);
deleteparamArray[1] = Utilities.GetOraParam(":p_DOC_TYPE_ID", lst.DocTypeID, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_DEL_PERSON_DOCUMENTS", deleteparamArray);
}
else
{
OracleParameter[] paramArray = new OracleParameter[9];
paramArray[0] = Utilities.GetOraParam(":p_P_ID", personID, OracleDbType.Double, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_DOC_TYPE_ID", lst.DocTypeID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_DOC_NUMBER", lst.DocNumber, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":p_ISSUED_FROM", lst.IssuedFrom, OracleDbType.Int64, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":p_ISSUED_ON", lst.IssuedOn, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":p_ISSUED_BY", lst.IssuedBy, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":p_ACTIVE", lst.Active, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[7] = Utilities.GetOraParam(":p_ENTRY_BY", lst.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[8] = Utilities.GetOraParam(":p_ENTRY_DATE", lst.EntryDate, OracleDbType.Date, ParameterDirection.Input);
if (lst.Action == "A")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_PERSON_DOCUMENTS", paramArray);
}
else if (lst.Action == "E")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_PERSON_DOCUMENTS", paramArray);
}
}
}
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)
{
throw ex;
}
}
public static bool SaveBeneficiary(List <ATTEmployeeBeneficiary> lstBeneficiaries, OracleTransaction Tran, double empID)
{
double benID = 0;
try
{
foreach (ATTEmployeeBeneficiary lst in lstBeneficiaries)
{
if (lst.Action != "")
{
List <OracleParameter> paramArray = new List <OracleParameter>();
if (lst.Action == "D")
{
paramArray.Add(Utilities.GetOraParam(":P_P_ID", lst.EmpId, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_BENEFICIARY_ID", lst.BenId, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_FROM_DATE", lst.FromDate, OracleDbType.Varchar2, ParameterDirection.Input));
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_DEL_BENEFICIARIES", paramArray.ToArray());
paramArray.Clear();
}
else
{
benID = DLLRelatives.SaveRelatives(lst.ObjRelatives, Tran, empID);
paramArray.Add(Utilities.GetOraParam(":P_P_ID", empID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_BENEFICIARY_ID", benID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_FROM_DATE", lst.FromDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_TO_DATE", lst.ToDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", lst.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
if (lst.Action == "A")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_BENEFICIARIES", paramArray.ToArray());
}
else if (lst.Action == "E")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_BENEFICIARIES", paramArray.ToArray());
}
paramArray.Clear();
}
}
}
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)
{
throw ex;
}
}
public static bool SaveCourtFee(List <ATTCourtFee> CourtFeeLST)
{
string InsertUpdateSQL = "";
List <OracleParameter> paramArray = new List <OracleParameter>();
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
try
{
foreach (ATTCourtFee objCourtFee in CourtFeeLST)
{
paramArray.Add(Utilities.GetOraParam(":P_FROM_DATE", null, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_FROM_AMOUNT", objCourtFee.FromAmount, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_TO_AMOUNT", objCourtFee.ToAmount, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_AMT_PER", objCourtFee.AmtPer, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_AMT_PER_TYPE", objCourtFee.AmtPerType, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objCourtFee.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
//if (objBenchType.Action == "A")
InsertUpdateSQL = "SP_ADD_COURT_FEE";
//else if (objBenchType.Action == "E")
// InsertUpdateSQL = "SP_EDIT_BENCH_TYPE";
SqlHelper.ExecuteNonQuery(Tran, System.Data.CommandType.StoredProcedure, InsertUpdateSQL, paramArray.ToArray());
//objBenchType.BenchTypeID = int.Parse(paramArray[0].Value.ToString());
//objBenchType.Action = "";
paramArray.Clear();
}
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 ApproveIssueMaag(ATTMaagFaaramHead objMaagFaaramHead)
{
GetConnection GetConn = new GetConnection();
OracleConnection DBConn = GetConn.GetDbConn(Module.OAS);
OracleTransaction Tran = DBConn.BeginTransaction();
string strSQL = "";
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_ORG_ID", objMaagFaaramHead.OrgID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_UNIT_ID", objMaagFaaramHead.UnitID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_REQ_NO", objMaagFaaramHead.ReqNo, OracleDbType.Double, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":P_APP_BY", objMaagFaaramHead.AppBy, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_APP_DATE", objMaagFaaramHead.AppDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_APP_YES_NO", objMaagFaaramHead.AppYesNo, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ISSUE_FLAG", objMaagFaaramHead.IssueFlag, OracleDbType.Varchar2, ParameterDirection.Input));
if (objMaagFaaramHead.Action == "APP")
{
strSQL = "SP_INV_APP_MAAG_FAARAM_HEAD";
}
else if (objMaagFaaramHead.Action == "DELI")
{
strSQL = "SP_INV_DELI_MAAG_FAARAM_HEAD";
}
try
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, strSQL, paramArray.ToArray());
paramArray.Clear();
if (objMaagFaaramHead.LstMaagFaaramDetail.Count > 0)
{
DLLMaagFaaramDetail.UpdateMaagFaaramDetAppQty(objMaagFaaramHead.LstMaagFaaramDetail, Tran);
}
Tran.Commit();
return(true);
}
catch (OracleException oex)
{
Tran.Rollback();
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 ProcessTameli(ATTTameli tameli)
{
string InsertUpdateSQL = "SP_PROCESS_TAMELI";
List <OracleParameter> paramList = new List <OracleParameter>();
GetConnection GetConn = new GetConnection();
OracleConnection DBConn = GetConn.GetDbConn(Module.CMS);
//OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
try
{
//foreach (ATTTameli tameli in TameliLIST)
//{
//if (tameli.Action == "A")
// InsertUpdateSQL = "SP_ADD_TAMELI";
//else if (tameli.Action == "E")
//InsertUpdateSQL = "SP_EDIT_TAMELI";
paramList.Add(Utilities.GetOraParam(":P_CASE_ID", tameli.CaseID, OracleDbType.Int64, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_LITIGANT_ID", tameli.LitigantID, OracleDbType.Int64, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_ISSUE_DATE", tameli.IssuedDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_SEQ_NO", tameli.SeqNo, OracleDbType.Int64, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_VERIFIED_DATE", tameli.VerifiedDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_VERIFIED_BY", tameli.VerifiedBy, OracleDbType.Double, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_VERIFIED_YES_NO", tameli.VerifiedYesNo, OracleDbType.Varchar2, ParameterDirection.Input));
paramList.Add(Utilities.GetOraParam(":P_VERIFIED_REMARKS", tameli.VerifiedRemarks, OracleDbType.Varchar2, ParameterDirection.Input));
SqlHelper.ExecuteNonQuery(DBConn, System.Data.CommandType.StoredProcedure, InsertUpdateSQL, paramList.ToArray());
paramList.Clear();
//}
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 AddPersonAddress(List <ATTPersonAddress> lstPersonAddress, OracleTransaction Tran, double personID)
{
try
{
foreach (ATTPersonAddress lst in lstPersonAddress)
{
if (lst.Action == "D")
{
OracleParameter[] deleteparamArray = new OracleParameter[3];
deleteparamArray[0] = Utilities.GetOraParam(":p_P_ID", personID, OracleDbType.Double, ParameterDirection.Input);
deleteparamArray[1] = Utilities.GetOraParam(":p_ADTYPE_ID", lst.AdTypeId, OracleDbType.Varchar2, ParameterDirection.Input);
deleteparamArray[2] = Utilities.GetOraParam(":p_AD_SNO", lst.AdSNo, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_DEL_PERSON_ADDRESS", deleteparamArray);
}
else
{
OracleParameter[] paramArray = new OracleParameter[10];
paramArray[0] = Utilities.GetOraParam(":p_P_ID", personID, OracleDbType.Double, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_ADTYPE_ID", lst.AdTypeId, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_AD_SNO", lst.AdSNo, OracleDbType.Int64, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":p_DISTRICT", lst.District, OracleDbType.Int64, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":p_VDC", lst.VDC, OracleDbType.Int64, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":p_WARD", lst.Ward, OracleDbType.Int64, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":p_TOLE", lst.Tole, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[7] = Utilities.GetOraParam(":p_ACTIVE", lst.Active, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[8] = Utilities.GetOraParam(":p_ENTRY_BY", lst.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[9] = Utilities.GetOraParam(":p_ENTRY_DATE", lst.EntryDate, OracleDbType.Date, ParameterDirection.Input);
if (lst.Action == "A")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_PERSON_ADDRESS", paramArray);
}
else if (lst.Action == "E")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_PERSON_ADDRESS", paramArray);
}
}
}
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)
{
throw ex;
}
}
public static bool AddEmployeeVisits(List <ATTEmployeeVisits> lstEmployeeVisits, OracleTransaction Tran, double empID)
{
try
{
foreach (ATTEmployeeVisits lst in lstEmployeeVisits)
{
if (lst.Action == "D")
{
OracleParameter[] deleteparamArray = new OracleParameter[2];
deleteparamArray[0] = Utilities.GetOraParam(":p_EMP_ID", empID, OracleDbType.Double, ParameterDirection.Input);
deleteparamArray[1] = Utilities.GetOraParam(":p_SEQ_NO", lst.SeqNo, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_DEL_EMP_VISITS", deleteparamArray);
}
else
{
OracleParameter[] paramArray = new OracleParameter[10];
paramArray[0] = Utilities.GetOraParam(":P_EMP_ID", empID, OracleDbType.Double, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":P_SEQ_NO", lst.SeqNo, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":P_PURPOSE", lst.Purpose, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":P_LOCATION", lst.Location, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":P_COUNTRY", lst.Country, OracleDbType.Int64, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":P_FROM_DATE", lst.FromDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":P_TO_DATE", lst.ToDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[7] = Utilities.GetOraParam(":P_REMARKS", lst.Remarks, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[8] = Utilities.GetOraParam(":P_VEHICLE", lst.Vehicle, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[9] = Utilities.GetOraParam(":P_ENTRY_BY", lst.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
if (lst.Action == "A")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_EMP_VISITS", paramArray);
}
else if (lst.Action == "E")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_EMP_VISITS", paramArray);
}
}
}
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)
{
throw ex;
}
}
public static bool AddPersonQualifications(List <ATTPersonQualification> lstPersonQualifications, OracleTransaction Tran, double pID)
{
try
{
foreach (ATTPersonQualification lst in lstPersonQualifications)
{
if (lst.Action == "D")
{
OracleParameter[] deleteparamArray = new OracleParameter[2];
deleteparamArray[0] = Utilities.GetOraParam(":p_PID", pID, OracleDbType.Double, ParameterDirection.Input);
deleteparamArray[1] = Utilities.GetOraParam(":p_SEQ_NO", lst.SeqNo, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_DEL_QUALIFICATIONS", deleteparamArray);
}
else
{
OracleParameter[] paramArray = new OracleParameter[11];
paramArray[0] = Utilities.GetOraParam(":p_PID", pID, OracleDbType.Double, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_SEQ_NO", lst.SeqNo, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_SUBJECT", lst.Subject, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":p_DEGREE_ID", lst.DegreeID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":p_INSTITUTION_ID", lst.InstitutionID, OracleDbType.Long, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":p_FROM_DATE", lst.FromDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":p_TO_DATE", lst.ToDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[7] = Utilities.GetOraParam(":p_GRADE", lst.Grade, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[8] = Utilities.GetOraParam(":p_PERCENTAGE", lst.Percentage, OracleDbType.Double, ParameterDirection.Input);
paramArray[9] = Utilities.GetOraParam(":p_REMARKS", lst.Remarks, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[10] = Utilities.GetOraParam(":p_ENTRY_BY", lst.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
if (lst.Action == "A")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_QUALIFICATIONS", paramArray);
}
else if (lst.Action == "E")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_QUALIFICATIONS", paramArray);
}
}
}
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)
{
throw ex;
}
}
public static bool SaveCaseLawyers(List <ATTCaseLaywer> CaseLawyerLST)
{
string InsertUpdateSQL = "";
List <OracleParameter> paramArray;
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
try
{
foreach (ATTCaseLaywer objCaseLawyer in CaseLawyerLST)
{
paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_CASE_ID", objCaseLawyer.CaseID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_LITIGANT_ID", objCaseLawyer.LitigantID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_LAWYER_ID", objCaseLawyer.LawyerID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_FROM_DATE", objCaseLawyer.FromDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_LICENCE_NO", objCaseLawyer.LicenceNo, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objCaseLawyer.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
if (objCaseLawyer.Action == "A")
{
InsertUpdateSQL = "SP_ADD_CASE_LAWYER";
}
else if (objCaseLawyer.Action == "E")
{
InsertUpdateSQL = "SP_EDIT_CASE_LAWYER";
}
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 SaveBench(List <ATTBench> lstBench)
{
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
string InsertUpdateSQL = "";
try
{
foreach (ATTBench objBench in lstBench)
{
if (objBench.Action == null || objBench.Action == "")
{
continue;
}
if (objBench.Action == "A")
{
InsertUpdateSQL = "SP_ADD_BENCH";
}
else if (objBench.Action == "E")
{
InsertUpdateSQL = "SP_EDIT_BENCH";
}
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_ORG_ID", objBench.OrgID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_BENCH_NO", objBench.BenchNo, OracleDbType.Int64, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":P_BENCH_DESC", objBench.BenchDesc, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ACTIVE", objBench.Active, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objBench.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
SqlHelper.ExecuteNonQuery(Tran, System.Data.CommandType.StoredProcedure, InsertUpdateSQL, paramArray.ToArray());
objBench.BenchNo = int.Parse(paramArray[1].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 AddPersonEMail(List <ATTPersonEMail> lstPersonEMail, OracleTransaction Tran, double personID)
{
try
{
foreach (ATTPersonEMail lst in lstPersonEMail)
{
if (lst.Action == "D")
{
OracleParameter[] deleteparamArray = new OracleParameter[3];
deleteparamArray[0] = Utilities.GetOraParam(":p_P_ID", personID, OracleDbType.Double, ParameterDirection.Input);
deleteparamArray[1] = Utilities.GetOraParam(":p_E_TYPE", lst.EType, OracleDbType.Varchar2, ParameterDirection.Input);
deleteparamArray[2] = Utilities.GetOraParam(":p_E_SNO", lst.ESNo, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_DEL_PERSON_EMAIL", deleteparamArray);
}
else
{
OracleParameter[] paramArray = new OracleParameter[8];
paramArray[0] = Utilities.GetOraParam(":p_P_ID", personID, OracleDbType.Double, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_E_TYPE", lst.EType, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_E_SNO", lst.ESNo, OracleDbType.Int64, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":p_EMAIL", lst.EMail, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":p_ACTIVE", lst.Active, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":p_REMARKS", lst.Remarks, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":p_ENTRY_BY", lst.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[7] = Utilities.GetOraParam(":p_ENTRY_DATE", lst.EntryDate, OracleDbType.Date, ParameterDirection.Input);
if (lst.Action == "A")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_PERSON_EMAIL", paramArray);
}
else if (lst.Action == "E")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_PERSON_EMAIL", paramArray);
}
}
}
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)
{
throw ex;
}
}
public static bool SaveFacultyMember(ATTFacultyMember objFacultyMember)
{
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.DLPDS).BeginTransaction();
double personID;
string InsertUpdatePostSql = "";
try
{
personID = DLLPerson.AddPersonnelDetails(objFacultyMember.ObjPerson, Tran);
if (objFacultyMember.LstParticipantPost.Count > 0)
{
DLLParticipantPost.SaveParticipantPost(objFacultyMember.LstParticipantPost, Tran, personID);
}
OracleParameter[] ParamArray = new OracleParameter[5];
ParamArray[0] = Utilities.GetOraParam(":p_ORG_ID", objFacultyMember.OrgID, OracleDbType.Int32, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":p_FACULTY_ID", objFacultyMember.FacultyID, OracleDbType.Int32, ParameterDirection.Input);
ParamArray[2] = Utilities.GetOraParam(":p_P_ID", personID, OracleDbType.Double, ParameterDirection.Input);
ParamArray[3] = Utilities.GetOraParam(":p_FROM_DATE", objFacultyMember.FromDate, OracleDbType.Varchar2, ParameterDirection.Input);
ParamArray[4] = Utilities.GetOraParam(":p_TO_DATE", objFacultyMember.ToDate, OracleDbType.Varchar2, ParameterDirection.Input);
if (objFacultyMember.PID == 0)
{
InsertUpdatePostSql = "SP_ADD_FACULTY_MEMBER";
}
else if (objFacultyMember.PID > 0)
{
InsertUpdatePostSql = "SP_EDIT_FACULTY_MEMBER";
}
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, InsertUpdatePostSql, ParamArray);
objFacultyMember.PID = personID;
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 AddEmployeeExperiences(List <ATTEmployeeExperience> lstEmployeeExperiences, OracleTransaction Tran, double empID)
{
try
{
foreach (ATTEmployeeExperience lst in lstEmployeeExperiences)
{
if (lst.Action == "D")
{
OracleParameter[] deleteparamArray = new OracleParameter[2];
deleteparamArray[0] = Utilities.GetOraParam(":p_EMP_ID", empID, OracleDbType.Double, ParameterDirection.Input);
deleteparamArray[1] = Utilities.GetOraParam(":p_SEQ_NO", lst.SeqNo, OracleDbType.Int64, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_DEL_EMP_EXPERIENCES", deleteparamArray);
}
else
{
OracleParameter[] paramArray = new OracleParameter[9];
paramArray[0] = Utilities.GetOraParam(":p_EMP_ID", empID, OracleDbType.Double, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_SEQ_NO", lst.SeqNo, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_FROM_DATE", lst.FromDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":p_TO_DATE", lst.ToDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":p_POSTING_LOCATION", lst.PostingLocation, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":p_JOB_LOCATION", lst.JobLocation, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":p_CLASSIFICATION", lst.Classification, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[7] = Utilities.GetOraParam(":p_REMARKS", lst.Remarks, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[8] = Utilities.GetOraParam(":p_ENTRY_BY", lst.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
if (lst.Action == "A")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_EMP_EXPERIENCES", paramArray);
}
else if (lst.Action == "E")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_EMP_EXPERIENCES", paramArray);
}
}
}
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)
{
throw ex;
}
}
public static bool SaveParticipant(ATTParticipant ObjParticipant)
{
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.DLPDS).BeginTransaction();
double personID;
string InsertUpdatePostSql = "";
try
{
personID = DLLPerson.AddPersonnelDetails(ObjParticipant.ObjPerson, Tran);
if (ObjParticipant.LstParticipantPost.Count > 0)
{
DLLParticipantPost.SaveParticipantPost(ObjParticipant.LstParticipantPost, Tran, personID);
}
OracleParameter[] ParamArray = new OracleParameter[4];
ParamArray[0] = Utilities.GetOraParam(":p_ORG_ID", ObjParticipant.OrgID, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[1] = Utilities.GetOraParam(":p_PROGRAM_ID", ObjParticipant.ProgramID, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[2] = Utilities.GetOraParam(":p_P_ID", personID, OracleDbType.Double, ParameterDirection.Input);
ParamArray[3] = Utilities.GetOraParam(":p_JOINING_DATE", ObjParticipant.JoiningDate, OracleDbType.Varchar2, ParameterDirection.Input);
if (ObjParticipant.PID == 0)
{
InsertUpdatePostSql = "SP_ADD_PARTICIPANT";
}
else if (ObjParticipant.PID > 0)
{
InsertUpdatePostSql = "SP_EDIT_PARTICIPANT";
}
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, InsertUpdatePostSql, ParamArray);
ObjParticipant.PID = personID;
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 SaveOrgCaseRegTypeCheckList(List <ATTOrgCaseRegTypeCheckList> lstOrgCaseRegTypeCheckList)
{
string InsertUpdateSQL = "";
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
try
{
foreach (ATTOrgCaseRegTypeCheckList objOrgCaseRegTypeCheckList in lstOrgCaseRegTypeCheckList)
{
if (objOrgCaseRegTypeCheckList.Action == "A")
{
InsertUpdateSQL = "SP_ADD_ORGCASEREGTYPE_CHKLST";
}
else if (objOrgCaseRegTypeCheckList.Action == "E")
{
InsertUpdateSQL = "SP_EDIT_ORGCASEREGTYPE_CHKLST";
}
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_ORG_ID", objOrgCaseRegTypeCheckList.OrgID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_CASE_TYPE_ID", objOrgCaseRegTypeCheckList.CaseTypeID, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_REG_TYPE_ID", objOrgCaseRegTypeCheckList.RegTypeID, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_CHECK_LIST_ID", objOrgCaseRegTypeCheckList.CheckListID, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ACTIVE", objOrgCaseRegTypeCheckList.Active, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objOrgCaseRegTypeCheckList.EntryBY, OracleDbType.Varchar2, ParameterDirection.Input));
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();
Tran.Rollback();
throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message));
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
public static bool SaveSectionClerkCase(List <ATTSectionClerkCase> lstSectionClerkCase)
{
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
string InsertUpdateSQL = "";
try
{
foreach (ATTSectionClerkCase objSectionClerkCase in lstSectionClerkCase)
{
if (objSectionClerkCase.Action == "A")
{
InsertUpdateSQL = "SP_ADD_SEC_CLERK_CASE";
}
else if (objSectionClerkCase.Action == "E")
{
InsertUpdateSQL = "SP_EDIT_SEC_CLERK_CASE";
}
List <OracleParameter> paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam(":P_ORG_ID", objSectionClerkCase.OrgID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_UNIT_ID", objSectionClerkCase.UnitID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_CASE_TYPE_ID", objSectionClerkCase.CaseTypeID, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_FROM_DATE", objSectionClerkCase.FromDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_CASE_ID", objSectionClerkCase.CaseID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_SEC_CLRK_ID", objSectionClerkCase.SectionClerkID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_SEC_CLRK_FROM_DATE", objSectionClerkCase.SectionClerkFromDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objSectionClerkCase.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
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 int AddCountries(ATTCountry objCountries)
{
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn().BeginTransaction();
int CountryID = 0;
try
{
string InsertUpdateSQL = "";
if (objCountries.CountryId <= 0)
{
InsertUpdateSQL = "SP_ADD_COUNTRIES";
}
else
{
InsertUpdateSQL = "SP_EDIT_COUNTRIES";
}
OracleParameter[] ParamArray = new OracleParameter[4];
ParamArray[0] = Utilities.GetOraParam(":p_COUNTRY_ID", objCountries.CountryId, OracleDbType.Int64, ParameterDirection.InputOutput);
ParamArray[1] = Utilities.GetOraParam(":p_COUNTRY_NEP_NAME", objCountries.CountryNepName, OracleDbType.Varchar2, ParameterDirection.Input);
ParamArray[2] = Utilities.GetOraParam(":p_COUNTRY_ENG_NAME", objCountries.CountryEngName, OracleDbType.Varchar2, ParameterDirection.Input);
ParamArray[3] = Utilities.GetOraParam(":p_COUNTRY_CODE", objCountries.CountryCode, OracleDbType.Varchar2, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, InsertUpdateSQL, ParamArray[0], ParamArray);
CountryID = int.Parse(ParamArray[0].Value.ToString());
Tran.Commit();
return(CountryID);
}
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 double SaveRelatives(ATTRelatives objRelatives, OracleTransaction Tran, double personID)
{
double relativeID = 0;
try
{
List <OracleParameter> paramArray = new List <OracleParameter>();
if (objRelatives.Action == "D")
{
paramArray.Add(Utilities.GetOraParam(":P_P_ID", objRelatives.PId, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_RELATIVE_ID", objRelatives.RelativeId, OracleDbType.Varchar2, ParameterDirection.Input));
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_DEL_RELATIVES", paramArray.ToArray());
paramArray.Clear();
}
else
{
relativeID = DLLPerson.AddPersonnelDetails(objRelatives.ObjPerson, Tran);
paramArray.Add(Utilities.GetOraParam(":P_P_ID", personID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_RELATIVE_ID", relativeID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_RELATION_TYPE_ID", objRelatives.RelationTypeId, OracleDbType.Int64, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_OCCUPATION", objRelatives.Occupation, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ACTIVE", objRelatives.Active, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objRelatives.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
if (objRelatives.Action == "A")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_RELATIVES", paramArray.ToArray());
}
else if (objRelatives.Action == "E")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_RELATIVES", paramArray.ToArray());
}
paramArray.Clear();
}
return(relativeID);
}
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;
}
}