public DataTable LoadDepartments_helper(InternalURL oj)
{
try
{
cmd = new OracleCommand();
cmd.InitialLONGFetchSize = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GSWS_GET_SERVICE_DETAILS";
cmd.Parameters.Add("FTYPE", OracleDbType.Varchar2, 12).Value = oj.TYPE;
cmd.Parameters.Add("FDEPT_ID", OracleDbType.Varchar2, 12).Value = oj.DEPARTMENT;
cmd.Parameters.Add("FHOD_ID", OracleDbType.Varchar2, 12).Value = oj.HOD;
cmd.Parameters.Add("FDIST_CODE", OracleDbType.Varchar2, 12).Value = oj.DISTRICT;
cmd.Parameters.Add("FMAND_CODE", OracleDbType.Varchar2, 12).Value = oj.MANDAL;
cmd.Parameters.Add("FVILL_CODE", OracleDbType.Varchar2, 12).Value = oj.PANCHAYAT;
cmd.Parameters.Add("P_URFLG", OracleDbType.Varchar2, 12).Value = oj.RUFLAG;
cmd.Parameters.Add("P_CUR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable dtstatus = comhel.GetgswsDataAdapter(cmd);
if (dtstatus != null && dtstatus.Rows.Count > 0)
{
return(dtstatus);
}
else
{
return(null);
}
}
catch (Exception ex)
{
string mappath = HttpContext.Current.Server.MapPath("InternalURLExceptionLogs");
Task WriteTask = Task.Factory.StartNew(() => new Logdatafile().Write_ReportLog_Exception(mappath, "Error From LoadDepartments:" + ex.Message.ToString()));
throw ex;
}
}
public DataTable GetSpandanaMasterDetails(Seccmastermodel oj)
{
try
{
cmd = new OracleCommand();
cmd.InitialLONGFetchSize = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "gsws_spandana_sec_mapping_proc";
cmd.Parameters.Add("ftype", OracleDbType.Varchar2, 12).Value = oj.Ftype;
cmd.Parameters.Add("fdist", OracleDbType.Varchar2, 12).Value = oj.Fdistrict;
cmd.Parameters.Add("fmandal", OracleDbType.Varchar2, 12).Value = oj.Fmandal;
cmd.Parameters.Add("fvillage", OracleDbType.Varchar2, 12).Value = oj.Fvillage;
cmd.Parameters.Add("frural", OracleDbType.Varchar2, 20).Value = oj.Fruflag;
cmd.Parameters.Add("P_CUR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable dtstatus = comhel.GetgswsDataAdapter(cmd);
if (dtstatus != null && dtstatus.Rows.Count > 0)
{
return(dtstatus);
}
else
{
return(null);
}
}
catch (Exception ex)
{
string mappath = HttpContext.Current.Server.MapPath("RevenueExceptionLogs");
Task WriteTask = Task.Factory.StartNew(() => new Logdatafile().Write_ReportLog_Exception(mappath, "Error From GetApplicantStatus:" + ex.Message.ToString()));
throw ex;
}
}
public DataTable GetApplicantStatus_helper(PENSIONModel oj)
{
try
{
cmd = new OracleCommand();
cmd.InitialLONGFetchSize = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "gsws_sp_check_status";
cmd.Parameters.Add("ftype", OracleDbType.Varchar2, 12).Value = "1";
cmd.Parameters.Add("fdpart_id ", OracleDbType.Varchar2, 20).Value = oj.PID;
cmd.Parameters.Add("fadhar_no", OracleDbType.Varchar2, 20).Value = oj.UID;
cmd.Parameters.Add("P_CUR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable dtstatus = comhel.GetgswsDataAdapter(cmd);
if (dtstatus != null && dtstatus.Rows.Count > 0)
{
return(dtstatus);
}
else
{
return(null);
}
}
catch (Exception ex)
{
string mappath = HttpContext.Current.Server.MapPath("PENSIONExceptionLogs");
Task WriteTask = Task.Factory.StartNew(() => new Logdatafile().Write_ReportLog_Exception(mappath, "Error From GetApplicantStatus:" + ex.Message.ToString()));
throw ex;
}
}
//Procedures
//procedure details
public DataTable GetHomeMaster_SP(HomeSPModel ObjLGD) //ftype=4,district,5-mandal 6-gp
{
try
{
cmd = new OracleCommand();
cmd.InitialLONGFetchSize = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GSWS_GET_HOME_DETAILS";
cmd.Parameters.Add("PTYPE", OracleDbType.Int32).Value = Convert.ToInt32(ObjLGD.PTYPE);
cmd.Parameters.Add("PDIST", OracleDbType.Int32).Value = Convert.ToInt32(ObjLGD.PDIST);
cmd.Parameters.Add("PRANGE_CD", OracleDbType.Int32).Value = Convert.ToInt32(ObjLGD.PRANGE_CD);
cmd.Parameters.Add("P_CUR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable dtLogin = clda.GetgswsDataAdapter(cmd);
if (dtLogin != null)
{
return(dtLogin);
}
else
{
return(null);
}
}
catch (Exception ex)
{
string mappath = HttpContext.Current.Server.MapPath("HOME_MAstersSP");
Task WriteTask = Task.Factory.StartNew(() => new Logdatafile().Write_ReportLog_Exception(mappath, "Error From GetHomeMaster_SP:" + ex.Message.ToString()));
throw ex;
}
}
public static DataTable clusterMappingProc(clusterMappingModel obj)
{
try
{
OracleCommand cmd = new OracleCommand();
cmd.InitialLONGFetchSize = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GSWS_VOLUNTEER_DATA_UPDATE";
cmd.Parameters.Add("P_TYPE", OracleDbType.Varchar2).Value = obj.type;
cmd.Parameters.Add("P_DISTRICT_ID", OracleDbType.Varchar2).Value = obj.districtId;
cmd.Parameters.Add("P_MANDAL_ID", OracleDbType.Varchar2).Value = obj.mandalId;
cmd.Parameters.Add("P_SEC_ID", OracleDbType.Varchar2).Value = obj.secId;
cmd.Parameters.Add("P_CLUSTER_ID", OracleDbType.Varchar2).Value = obj.clusterId;
cmd.Parameters.Add("P_FLAG_U_R", OracleDbType.Varchar2).Value = obj.ruralUrbanFlag;
cmd.Parameters.Add("P_CLUSTER_NAME", OracleDbType.Varchar2).Value = obj.clusterName;
cmd.Parameters.Add("P_VOL_NAME", OracleDbType.Varchar2).Value = obj.volName;
cmd.Parameters.Add("P_VOL_UID", OracleDbType.Varchar2).Value = obj.volUid;
cmd.Parameters.Add("P_VOL_MOBILE", OracleDbType.Varchar2).Value = obj.volMobile;
cmd.Parameters.Add("P_VOL_CFMS_ID", OracleDbType.Varchar2).Value = obj.volCfmsId;
cmd.Parameters.Add("P_VOL_TYPE", OracleDbType.Varchar2).Value = obj.volType;
cmd.Parameters.Add("P_USER_ID", OracleDbType.Varchar2).Value = obj.userId;
cmd.Parameters.Add("P_PWD", OracleDbType.Varchar2).Value = obj.password;
cmd.Parameters.Add("P_REASON", OracleDbType.Varchar2).Value = obj.reason;
cmd.Parameters.Add("P_REMARKS", OracleDbType.Varchar2).Value = obj.remarks;
cmd.Parameters.Add("p_cur", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable dtstatus = _Hel.GetgswsDataAdapter(cmd);
return(dtstatus);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetJobCardSP(JobCardModel ObjLGD)
{
try
{
CommonSPHel comhel = new CommonSPHel();
cmd = new OracleCommand();
cmd.InitialLONGFetchSize = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "PR_GET_GSWS_PRRD_JOB_CARD_DTLS";
cmd.Parameters.Add("P_TYPE", OracleDbType.Varchar2, 10).Value = ObjLGD.P_TYPE;
cmd.Parameters.Add("P_JC_ID", OracleDbType.Varchar2, 100).Value = ObjLGD.P_JC_ID;
cmd.Parameters.Add("P_MANDAL_ID", OracleDbType.Varchar2, 10).Value = ObjLGD.P_LGD_MANDAL_CODE;
cmd.Parameters.Add("P_GP_ID", OracleDbType.Varchar2, 10).Value = ObjLGD.P_GP_ID;
cmd.Parameters.Add("P_SACHIVALAYAM_ID", OracleDbType.Varchar2, 50).Value = ObjLGD.P_SACHIVALAYAM_ID;
cmd.Parameters.Add("P_UID_NO", OracleDbType.Varchar2, 50).Value = ObjLGD.P_UID_NO;
cmd.Parameters.Add("P_USER_ID", OracleDbType.Varchar2, 10).Value = ObjLGD.P_USER_ID;
cmd.Parameters.Add("P_STATUS", OracleDbType.Varchar2, 10).Value = ObjLGD.P_STATUS;
cmd.Parameters.Add("P_CUR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable dtLogin = comhel.GetgswsDataAdapter(cmd);
if (dtLogin != null)
{
return(dtLogin);
}
else
{
return(null);
}
}
catch (Exception ex)
{
string mappath = HttpContext.Current.Server.MapPath("JobCardExceptions");
Task WriteTask = Task.Factory.StartNew(() => new Logdatafile().Write_Log_Exception(mappath, "Error Getting GetJobCardSP:" + ex.Message.ToString()));
throw ex;
}
}
public DataTable CreateJobCard_SP(List <JobCardModel> Objitem)
{
// Start a local transaction
OracleTransaction transaction;
ConnectionHelper _conHe = new ConnectionHelper();
OracleConnection con = new OracleConnection(_conHe.Congsws);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
transaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
DataTable dtLogin = null;
try
{
foreach (var Obj in Objitem)
{
CommonSPHel comhel = new CommonSPHel();
cmd = new OracleCommand();
cmd.Transaction = transaction;
cmd.InitialLONGFetchSize = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "PR_INS_GSWS_PRRD_JOB_CARD_DTLS";
cmd.Parameters.Add("P_TYPE", OracleDbType.Varchar2, 10).Value = Obj.P_TYPE;
cmd.Parameters.Add("P_JC_ID", OracleDbType.Varchar2, 500).Value = Obj.P_JC_ID;
cmd.Parameters.Add("P_LGD_DISTRICT_CODE", OracleDbType.Varchar2, 10).Value = Obj.P_LGD_DISTRICT_CODE;
cmd.Parameters.Add("P_LGD_MANDAL_CODE", OracleDbType.Varchar2, 50).Value = Obj.P_LGD_MANDAL_CODE;
cmd.Parameters.Add("P_GP_ID", OracleDbType.Varchar2, 50).Value = Obj.P_GP_ID;
cmd.Parameters.Add("P_GP_NAME", OracleDbType.Varchar2, 50).Value = Obj.P_GP_NAME;
cmd.Parameters.Add("P_HB_CODE", OracleDbType.Varchar2, 10).Value = Obj.P_HB_CODE;
cmd.Parameters.Add("P_HB_NAME", OracleDbType.Varchar2, 10).Value = Obj.P_HB_NAME;
cmd.Parameters.Add("P_REGD_DATE", OracleDbType.Date, 50).Value = Obj.P_REGD_DATE;
cmd.Parameters.Add("P_APPLICATION_NUMBER", OracleDbType.Varchar2, 50).Value = Obj.P_APPLICATION_NUMBER;
cmd.Parameters.Add("P_GP_SECRETARY_NAME", OracleDbType.Varchar2, 10).Value = Obj.P_GP_SECRETARY_NAME;
cmd.Parameters.Add("P_CASTE", OracleDbType.Varchar2, 50).Value = Obj.P_CASTE;
cmd.Parameters.Add("P_ADDRESS", OracleDbType.Varchar2, 50).Value = Obj.P_ADDRESS;
cmd.Parameters.Add("P_ADDRESS_TEL", OracleDbType.Varchar2, 10).Value = Obj.P_ADDRESS_TEL;
cmd.Parameters.Add("P_RATIONCARD_NO", OracleDbType.Varchar2, 50).Value = Obj.P_RATIONCARD_NO;
cmd.Parameters.Add("P_BPL_NO", OracleDbType.Varchar2, 50).Value = Obj.P_BPL_NO;
cmd.Parameters.Add("P_KHATA_NO", OracleDbType.Varchar2, 10).Value = Obj.P_KHATA_NO;
cmd.Parameters.Add("P_LAND_OWNER", OracleDbType.Varchar2, 50).Value = Obj.P_LAND_OWNER;
cmd.Parameters.Add("P_LAND_OWNER_ACRES", OracleDbType.Varchar2, 50).Value = Obj.P_LAND_OWNER_ACRES;
cmd.Parameters.Add("P_ASSIGNED_LAND_BENEFICIARY", OracleDbType.Varchar2, 10).Value = Obj.P_ASSIGNED_LAND_BENEFICIARY;
cmd.Parameters.Add("P_IAY_BENEFICIARY", OracleDbType.Varchar2, 50).Value = Obj.P_IAY_BENEFICIARY;
cmd.Parameters.Add("P_MEMBER_NAME", OracleDbType.Varchar2, 50).Value = Obj.P_MEMBER_NAME;
cmd.Parameters.Add("P_MEMBER_NAME_TEL", OracleDbType.Varchar2, 50).Value = Obj.P_MEMBER_NAME_TEL;
cmd.Parameters.Add("P_SUR_NAME", OracleDbType.Varchar2, 50).Value = Obj.P_SUR_NAME;
cmd.Parameters.Add("P_SUR_NAME_TEL", OracleDbType.Varchar2, 50).Value = Obj.P_SUR_NAME_TEL;
cmd.Parameters.Add("P_FAMILY_HEAD", OracleDbType.Varchar2, 10).Value = Obj.P_FAMILY_HEAD;
cmd.Parameters.Add("P_RELATION_HOF", OracleDbType.Varchar2, 50).Value = Obj.P_RELATION_HOF;
cmd.Parameters.Add("P_GENDER", OracleDbType.Varchar2, 50).Value = Obj.P_GENDER;
cmd.Parameters.Add("P_AGE", OracleDbType.Varchar2, 10).Value = Obj.P_AGE;
cmd.Parameters.Add("P_HIV", OracleDbType.Varchar2, 50).Value = Obj.P_HIV;
cmd.Parameters.Add("P_DISABLED", OracleDbType.Varchar2, 50).Value = Obj.P_DISABLED;
cmd.Parameters.Add("P_SHG_MEMBER", OracleDbType.Varchar2, 10).Value = Obj.P_SHG_MEMBER;
cmd.Parameters.Add("P_SHG_ID", OracleDbType.Varchar2, 50).Value = Obj.P_SHG_ID;
cmd.Parameters.Add("P_SHG_NAME", OracleDbType.Varchar2, 50).Value = Obj.P_SHG_NAME;
cmd.Parameters.Add("P_PERMANENT_JOB_CARD", OracleDbType.Varchar2, 10).Value = Obj.P_PERMANENT_JOB_CARD;
cmd.Parameters.Add("P_MPHSS_ID", OracleDbType.Varchar2, 50).Value = Obj.P_MPHSS_ID;
cmd.Parameters.Add("P_PAYING_AGENCY_TYPE", OracleDbType.Varchar2, 50).Value = Obj.P_PAYING_AGENCY_TYPE;
cmd.Parameters.Add("P_PAYING_AGENCY_NAME", OracleDbType.Varchar2, 100).Value = Obj.P_PAYING_AGENCY_NAME;
cmd.Parameters.Add("P_BRANCH_NAME", OracleDbType.Varchar2, 50).Value = Obj.P_BRANCH_NAME;
cmd.Parameters.Add("P_IFSC_CODE", OracleDbType.Varchar2, 50).Value = Obj.P_IFSC_CODE;
cmd.Parameters.Add("P_BANK_ACC_NO", OracleDbType.Varchar2, 50).Value = Obj.P_BANK_ACC_NO;
cmd.Parameters.Add("P_BANK_ACC_NAME ", OracleDbType.Varchar2, 50).Value = Obj.P_BANK_ACC_NAME;
cmd.Parameters.Add("P_VOTER_ID", OracleDbType.Varchar2, 50).Value = Obj.P_VOTER_ID;
cmd.Parameters.Add("P_UID_NO", OracleDbType.Varchar2, 50).Value = Obj.P_UID_NO;
cmd.Parameters.Add("P_MOBILE_NO", OracleDbType.Varchar2, 50).Value = Obj.P_MOBILE_NO;
cmd.Parameters.Add("P_USER_ID", OracleDbType.Varchar2, 20).Value = Obj.P_USER_ID;
cmd.Parameters.Add("P_SACHIVALAYAM_ID", OracleDbType.Varchar2, 10).Value = Obj.P_SACHIVALAYAM_ID;
cmd.Parameters.Add("P_STATUS", OracleDbType.Varchar2, 10).Value = null;
cmd.Parameters.Add("P_PENSION_NUMBER", OracleDbType.Varchar2, 30).Value = Obj.P_PENSION_NUMBER;
cmd.Parameters.Add("P_SMARTCARD_NUMBER", OracleDbType.Varchar2, 30).Value = null;
cmd.Parameters.Add("P_UID_PATH", OracleDbType.Varchar2, 300).Value = Obj.P_UID_PATH;
cmd.Parameters.Add("P_PASSBOOK_PATH", OracleDbType.Varchar2, 300).Value = Obj.P_PASSBOOK_PATH;
cmd.Parameters.Add("P_F1_PATH", OracleDbType.Varchar2, 300).Value = Obj.P_F1_PATH;
cmd.Parameters.Add("P_CUR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
dtLogin = comhel.GetgswsDataAdapter(cmd);
}
transaction.Commit();
if (dtLogin != null)
{
return(dtLogin);
}
else
{
return(null);
}
}
catch (Exception ex)
{
transaction.Rollback();
string mappath = HttpContext.Current.Server.MapPath("JobCardExceptions");
Task WriteTask = Task.Factory.StartNew(() => new Logdatafile().Write_Log_Exception(mappath, "Error CreateJobCard_SP Saving jobcard into db" + ex.Message.ToString()));
throw ex;
throw ex;
}
}