public List <LienEntryDTO> GetLiendata(string Fdraccountno, string connectionstring)
{
List <LienEntryDTO> lstfdview = new List <LienEntryDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(connectionstring, CommandType.Text, "select lienid,to_char(liendate, 'dd/Mon/yyyy')liendate,membercode,fdaccountno,companybranch,lienadjuestto,lienamount,receipttype,lienstatus from tbltranslienentry where statusid = " + Convert.ToInt32(Status.Active) + " and fdaccountno='" + Fdraccountno + "' order by liendate desc;"))
{
while (dr.Read())
{
LienEntryDTO objfdview = new LienEntryDTO();
objfdview.pLienid = Convert.ToInt64(dr["lienid"]);
objfdview.pLiendate = Convert.ToString(dr["liendate"]);
objfdview.pMembercode = Convert.ToString(dr["membercode"]);
objfdview.pFdaccountno = Convert.ToString(dr["fdaccountno"]);
objfdview.pCompanybranch = Convert.ToString(dr["companybranch"]);
objfdview.pLienadjuestto = Convert.ToString(dr["lienadjuestto"]);
objfdview.pLienamount = Convert.ToInt64(dr["lienamount"]);
objfdview.pReceipttype = Convert.ToString(dr["receipttype"]);
objfdview.pLienstatus = Convert.ToString(dr["lienstatus"]);
lstfdview.Add(objfdview);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lstfdview);
}
public IActionResult SaveLienentry(LienEntryDTO _LienEntry)
{
bool isSaved = false;
try
{
isSaved = Objlienentry.SaveLienentry(_LienEntry, Con);
}
catch (Exception ex)
{
throw new FinstaAppException(ex.ToString());
}
return(Ok(isSaved));
}
public bool SaveLienentry(LienEntryDTO _lienentryDTO, string connectionstring)
{
bool IsSaved = false;
string qry = string.Empty;
try
{
con = new NpgsqlConnection(connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (_lienentryDTO.ptypeofoperation == "CREATE")
{
qry = "insert into tbltranslienentry(liendate,membercode,fdaccountno,lienamount,liencount,companyname,companybranch,lienadjuestto,receipttype,lienstatus,statusid,createdby,createddate)values('" + FormatDate(_lienentryDTO.pLiendate.ToString()) + "','" + _lienentryDTO.pMembercode + "','" + _lienentryDTO.pFdaccountno + "'," + _lienentryDTO.pLienamount + "," + _lienentryDTO.pLiencount + ",'" + _lienentryDTO.pCompanyname + "','" + _lienentryDTO.pCompanybranch + "','" + _lienentryDTO.pLienadjuestto + "','" + _lienentryDTO.pReceipttype + "','N'," + Convert.ToInt32(Status.Active) + ", " + _lienentryDTO.pCreatedby + ", current_timestamp);";
}
else
{
qry = "Update tbltranslienentry set liendate = '" + FormatDate(_lienentryDTO.pLiendate.ToString()) + "',membercode = '" + _lienentryDTO.pMembercode + "',fdaccountno ='" + _lienentryDTO.pFdaccountno + "',lienamount =" + _lienentryDTO.pLienamount + ",liencount = " + _lienentryDTO.pLiencount + ",companyname = '" + _lienentryDTO.pCompanyname + "',companybranch = '" + _lienentryDTO.pCompanybranch + "',lienadjuestto = '" + _lienentryDTO.pLienadjuestto + "',receipttype = '" + _lienentryDTO.pReceipttype + "',modifiedby = '" + _lienentryDTO.pCreatedby + "',modifieddate= current_timestamp where lienid = " + _lienentryDTO.pLienid + ";";
}
if (!string.IsNullOrEmpty(qry))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, qry);
}
trans.Commit();
IsSaved = true;
}
catch (Exception Ex)
{
trans.Rollback();
throw Ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(IsSaved);
}