public async Task <List <DocumentsMasterDTO> > GetDocumentGroupNames(string ConnectionString)
{
List <DocumentsMasterDTO> lstdocumentGroups = new List <DocumentsMasterDTO>();
await Task.Run(() =>
{
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select documentgroupid,documentgroupname from tblmstdocumentgroup"))
{
while (dr.Read())
{
DocumentsMasterDTO objGroupNames = new DocumentsMasterDTO();
objGroupNames.pDocumentGroupId = Convert.ToInt32(dr["documentgroupid"]);
objGroupNames.pDocumentGroup = dr["documentgroupname"].ToString();
lstdocumentGroups.Add(objGroupNames);
}
}
}
catch (Exception ex)
{
throw ex;
}
});
return(lstdocumentGroups);
}
public bool SaveIdentificationDocuments(DocumentsMasterDTO Documents)
{
try
{
return(obj.SaveIdentificationDocuments(Documents, Con));
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
}
public async Task <IActionResult> SaveIdentificationDocuments(DocumentsMasterDTO Documents)
{
bool isSaved = false;
try
{
isSaved = await obj.SaveIdentificationDocuments(Documents, Con);
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
return(Ok(isSaved));
}
public bool SaveIdentificationDocuments(DocumentsMasterDTO Documents, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "insert into tblmstdocumentproofs(documentgroupid,documentgroupname,documentname,statusid,createdby,createddate)values(" + Documents.pDocumentGroupId + ",'" + ManageQuote(Documents.pDocumentGroup) + "','" + ManageQuote(Documents.pDocumentName) + "'," + getStatusid(Documents.pStatusname, ConnectionString) + "," + Documents.pCreatedby + ",current_timestamp);");
isSaved = true;
}
catch (Exception)
{
throw;
}
return(isSaved);
}
public async Task <bool> DeleteIdentificationDocuments(DocumentsMasterDTO Documents, string ConnectionString)
{
bool isDelete = false;
await Task.Run(() =>
{
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "update tblmstdocumentproofs set statusid= " + getStatusid(Documents.pStatusname, ConnectionString) + " where documentid=" + Documents.pDocumentId + ";");
isDelete = true;
}
catch (Exception)
{
throw;
}
});
return(isDelete);
}
public async Task <bool> UpdateIdentificationDocuments(DocumentsMasterDTO Documents, string ConnectionString)
{
bool isUpdate = false;
await Task.Run(() =>
{
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "update tblmstdocumentproofs set documentgroupid=" + Documents.pDocumentGroupId + ",documentgroupname='" + ManageQuote(Documents.pDocumentGroup.Trim()) + "',documentname='" + ManageQuote(Documents.pDocumentName.Trim()) + "',modifiedby=" + Documents.pCreatedby + " where documentid=" + Documents.pDocumentId + ";");
isUpdate = true;
}
catch (Exception)
{
throw;
}
});
return(isUpdate);
}
public async Task <bool> SaveDocumentGroup(DocumentsMasterDTO Documents, string ConnectionString)
{
bool isSaved = false;
await Task.Run(() =>
{
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "insert into tblmstdocumentgroup(documentgroupname,statusid,createdby,createddate)values('" + ManageQuote(Documents.pDocumentGroup.Trim()) + "'," + getStatusid(Documents.pStatusname, ConnectionString) + "," + Documents.pCreatedby + ",current_timestamp);");
isSaved = true;
}
catch (Exception)
{
throw;
}
});
return(isSaved);
}
public List <DocumentsMasterDTO> Getdocumentidprofftypes(string ConnectionString, LoanIdDTO Documents)
{
List <DocumentsMasterDTO> lstdocumentidprofftypes = new List <DocumentsMasterDTO>();
try
{
dr1 = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select documentgroupid,documentgroupname from tblmstdocumentgroup");
if (Documents.pLoanId > 0)
{
dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select '' as contacttype,documentid,documentgroupid,documentgroupname,documentname,'false'::BOOLEAN as mandatory,'false'::BOOLEAN as required from tblmstdocumentproofs where statusid=1 and documentid not in(select documentid from tblmstloanwisedocumentproofs where statusid=1 and loanid=" + Documents.pLoanId + ") union select y.contacttype,x.documentid,x.documentgroupid,x.documentgroupname,x.documentname,y.isdocumentrequired as mandatory,y.isdocumentrequired required from tblmstdocumentproofs x right join tblmstloanwisedocumentproofs y on x.documentid = y.documentid where y.statusid = 1 and y.loanid = " + Documents.pLoanId + ";");
}
else
{
dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select '' as contacttype,documentid,documentgroupid,documentgroupname,documentname,'false'::BOOLEAN as mandatory,'false'::BOOLEAN as required from tblmstdocumentproofs where statusid=1");
}
while (dr1.Read())
{
DocumentsMasterDTO objdocumentidproofs = new DocumentsMasterDTO();
objdocumentidproofs.pDocumentGroupId = Convert.ToInt64(dr1["documentgroupid"]);
objdocumentidproofs.pDocumentGroup = dr1["documentgroupname"].ToString();
objdocumentidproofs.pDocumentsList = new List <pIdentificationDocumentsDTO>();
while (dr.Read())
{
if (dr1["documentgroupname"].ToString() == dr["documentgroupname"].ToString())
{
objdocumentidproofs.pDocumentsList.Add(new pIdentificationDocumentsDTO {
pContactType = dr["contacttype"].ToString(),
pDocumentId = Convert.ToInt64(dr["documentid"]), pDocumentName = dr["documentname"].ToString(),
pDocumentMandatory = Convert.ToBoolean(dr["mandatory"]),
pDocumentRequired = Convert.ToBoolean(dr["required"]), pDocumentgroupId = Convert.ToInt64(dr["documentgroupid"]),
pLoantypeId = Documents.pLoanId
});
}
}
lstdocumentidprofftypes.Add(objdocumentidproofs);
}
}
catch (Exception ex)
{
throw ex;
}
return(lstdocumentidprofftypes);
}
public List <DocumentsMasterDTO> GetDocumentGroupNames(string ConnectionString)
{
List <DocumentsMasterDTO> lstdocumentGroups = new List <DocumentsMasterDTO>();
try
{
dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select documentgroupid,documentgroupname from tblmstdocumentgroup");
while (dr.Read())
{
DocumentsMasterDTO objGroupNames = new DocumentsMasterDTO();
objGroupNames.pDocumentGroupId = Convert.ToInt32(dr["documentgroupid"]);
objGroupNames.pDocumentGroup = dr["documentgroupname"].ToString();
lstdocumentGroups.Add(objGroupNames);
}
}
catch (Exception ex)
{
throw ex;
}
return(lstdocumentGroups);
}
public List <DocumentsMasterDTO> Getdocumentidprofftypes(string ConnectionString, Int64 pLoanId)
{
List <DocumentsMasterDTO> lstdocumentidprofftypes = new List <DocumentsMasterDTO>();
string Query = string.Empty;
try
{
using (NpgsqlDataReader dr1 = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select documentgroupid,documentgroupname from tblmstdocumentgroup where documentgroupid in(select distinct documentgroupid from tblmstdocumentproofs tc join tblmststatus ts on tc.statusid = ts.statusid where tc.statusid = 1)"))
{
if (pLoanId > 0)
{
Query = "select '' as contacttype,documentid,documentgroupid,documentgroupname,documentname,'false'::BOOLEAN as mandatory,'false'::BOOLEAN as required from tblmstdocumentproofs where statusid=1 and documentid not in(select documentid from tblmstloanwisedocumentproofs where statusid=1 and loanid=" + pLoanId + ") union select y.contacttype,x.documentid,x.documentgroupid,x.documentgroupname,x.documentname,y.isdocumentmandatory as mandatory,y.isdocumentrequired required from tblmstdocumentproofs x right join tblmstloanwisedocumentproofs y on x.documentid = y.documentid where y.statusid = 1 and y.loanid = " + pLoanId + ";";
}
else
{
Query = "select '' as contacttype,documentid,documentgroupid,documentgroupname,documentname,'false'::BOOLEAN as mandatory,'false'::BOOLEAN as required,ts.statusname from tblmstdocumentproofs tc join tblmststatus ts on tc.statusid = ts.statusid where tc.statusid = 1;";
}
while (dr1.Read())
{
DocumentsMasterDTO objdocumentidproofs = new DocumentsMasterDTO();
objdocumentidproofs.pDocumentGroupId = Convert.ToInt64(dr1["documentgroupid"]);
objdocumentidproofs.pDocumentGroup = dr1["documentgroupname"].ToString();
objdocumentidproofs.pDocumentsList = new List <pIdentificationDocumentsDTO>();
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, Query))
{
if (pLoanId > 0)
{
while (dr.Read())
{
if (dr1["documentgroupname"].ToString() == dr["documentgroupname"].ToString())
{
objdocumentidproofs.pDocumentsList.Add(new pIdentificationDocumentsDTO
{
pDocumentGroupId = Convert.ToInt64(dr1["documentgroupid"]),
pDocumentGroup = dr1["documentgroupname"].ToString(),
pContactType = dr["contacttype"].ToString(),
pDocumentId = Convert.ToInt64(dr["documentid"]),
pDocumentName = dr["documentname"].ToString(),
pDocumentMandatory = Convert.ToBoolean(dr["mandatory"]),
pDocumentRequired = Convert.ToBoolean(dr["required"]),
pDocumentgroupId = Convert.ToInt64(dr["documentgroupid"]),
pLoanId = pLoanId,
// pLoantypeId = pLoanId,
});
}
}
}
else
{
while (dr.Read())
{
if (dr1["documentgroupname"].ToString() == dr["documentgroupname"].ToString())
{
objdocumentidproofs.pDocumentsList.Add(new pIdentificationDocumentsDTO
{
pDocumentGroupId = Convert.ToInt64(dr1["documentgroupid"]),
pDocumentGroup = dr1["documentgroupname"].ToString(),
pContactType = dr["contacttype"].ToString(),
pDocumentId = Convert.ToInt64(dr["documentid"]),
pDocumentName = dr["documentname"].ToString(),
pDocumentMandatory = Convert.ToBoolean(dr["mandatory"]),
pDocumentRequired = Convert.ToBoolean(dr["required"]),
pDocumentgroupId = Convert.ToInt64(dr["documentgroupid"]),
pStatusname = dr["statusname"].ToString(),
pLoanId = pLoanId,
// pLoantypeId = pLoanId,
});
}
}
}
}
lstdocumentidprofftypes.Add(objdocumentidproofs);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstdocumentidprofftypes);
}
