public bool SavedisbursalLetter(DisbursmentLetterDTO _DisbursmentLetterDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
string StrsaveDisbursalletter = string.Empty;
StrsaveDisbursalletter = "INSERT INTO tabapplicationdisbursementletter(applicationid, vchapplicationid, transdate, disbursementlettersent,sentthrough, applicantname, loanname, loanamount, interesttype,rateofinterest, tenureofloan, tenuretype, downpayment, termsandconditions,statusid, createdby, createddate,dibursementamount,modeofpayment,installmentamount,voucherno) VALUES(" + _DisbursmentLetterDTO.pApplicationId + ", '" + ManageQuote(_DisbursmentLetterDTO.pVchapplicationID) + "', current_date, 'Y','','" + ManageQuote(_DisbursmentLetterDTO.pApplicantname) + "', '" + ManageQuote(_DisbursmentLetterDTO.pLoanname) + "', " + _DisbursmentLetterDTO.pApprovedloanamount + ", '" + ManageQuote(_DisbursmentLetterDTO.pInteresttype) + "', " + _DisbursmentLetterDTO.pInterestRate + ", " + _DisbursmentLetterDTO.pTenureofloan + ", '" + _DisbursmentLetterDTO.pLoanpayin + "', " + _DisbursmentLetterDTO.pDownpayment + ", '', " + Convert.ToInt32(Status.Active) + ", " + _DisbursmentLetterDTO.pCreatedby + ", current_timestamp," + _DisbursmentLetterDTO.pDisbursalamount + ",'" + ManageQuote(_DisbursmentLetterDTO.pModeofpayment) + "'," + _DisbursmentLetterDTO.pInstallmentamount + ",'" + ManageQuote(_DisbursmentLetterDTO.pVoucherno) + "'); ";
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, StrsaveDisbursalletter);
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public bool SaveRoleSubModule(RolesubmodulesDTO _RolesubmodulesDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
_ = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText: "INSERT INTO tblmstmodules (modulename,moduledescription,parentmoduleid,parentmodulename,statusid,createdby,createddate) VALUES ('" + ManageQuote(_RolesubmodulesDTO.pSubmodulename).Trim() + "', '', " + _RolesubmodulesDTO.pModuleId + ", '" + ManageQuote(_RolesubmodulesDTO.pModulename).Trim() + "'," + Convert.ToInt32(Status.Active) + ", " + _RolesubmodulesDTO.pCreatedby + ", current_timestamp); ");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public bool SaveEmployeeRole(EmployeeRole modelRole, string connectionString)
{
int savedCount = 0;
try
{
if (!string.IsNullOrEmpty(modelRole.pEmployeeRoleName))
{
savedCount = NPGSqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, "insert into tblmstemployeerole(rolename,statusid,createdby,createddate) values('" + ManageQuote(modelRole.pEmployeeRoleName) + "'," + Convert.ToInt32(Status.Active) + "," + modelRole.pCreatedby + ",current_timestamp);");
}
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
}
}
return(savedCount > 0 ? true : false);
}
public bool SaveFunctionOrder(List <FunctionsDTO> FunctionDTO)
{
Boolean IsValid = false;
int Count = 0;
try
{
//select functionname,functionsortorder,functionurl,statusid,moduleid,createdby,createddate,modifiedby,modifieddate from tabrmsfunctions;
string strInser = "insert into tabfunctions(functionname,functionsortorder,functionurl,statusid,moduleid,createdby,createddate) values";
strInser = string.Empty;
foreach (var F in FunctionDTO)
{
Count = 0;
Count = Convert.ToInt16(NPGSqlHelper.ExecuteScalar(NPGSqlHelper.SQLConnString, CommandType.Text, "select count(*) from tabfunctions where functionname='" + F.FunctionName + "' and statusid=1 and functionid=" + F.FunctionId + " and moduleid=" + F.Moduleid + ";"));
if (Count > 0)
{
strInser += @"update tabfunctions set functionsortorder=" + F.FunctionOrder + " where functionname='" + F.FunctionName + "' and statusid=1 and functionid=" + F.FunctionId + " and moduleid=" + F.Moduleid + ";";
}
}
NPGSqlHelper.ExecuteNonQuery(NPGSqlHelper.SQLConnString, CommandType.Text, strInser);
IsValid = true;
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
return(IsValid);
}
public bool SaveModule(ModuleDTO1 objModule)
{
Boolean isvalid = false;
try
{
TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(NPGSqlHelper.SQLConnString, CommandType.Text, "select count(*) from tabmodules where statusid=1 and parentmoduleid=" + objModule.parentModuleId + " and modulename='" + textInfo.ToTitleCase(objModule.Modulename.Trim().ToUpper()) + "';"));
if (count == 0)
{
string strSave = string.Empty;
int moduleorder = 0;
//strSave = "INSERT INTO tabmodules(modulename, moduledescription, statusid, createdby, createddate, parentmodulename,moduletype) VALUES ('" + objModule.Modulename.Trim().ToUpper() + "',null,1,1,current_timestamp,'" + objModule.Modulename.Trim().ToUpper() + "','" + objModule.ModuleType + "');";
moduleorder = Convert.ToInt16(NPGSqlHelper.ExecuteScalar(NPGSqlHelper.SQLConnString, CommandType.Text, "select count(*) from tabmodules where statusid=1 and parentmoduleid=" + objModule.parentModuleId + ";"));
if (moduleorder == 0)
{
moduleorder = 1;
}
else
{
moduleorder = moduleorder + 1;
}
strSave = "INSERT INTO tabmodules(modulename,modulelevelno,modulesortorder, moduledescription, statusid, createdby, createddate, parentmoduleid,parentmodulename,moduletype) VALUES ('" + textInfo.ToTitleCase(objModule.Modulename.Trim().ToUpper()) + "'," + moduleorder + "," + moduleorder + ",'" + objModule.Modulename + "',1,1,current_timestamp," + objModule.parentModuleId + ",'" + objModule.ParentModulename.Trim().ToUpper() + "','" + objModule.ModuleType + "');";
NPGSqlHelper.ExecuteNonQuery(NPGSqlHelper.SQLConnString, CommandType.Text, strSave);
isvalid = true;
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
return(isvalid);
}
public bool SaveWorkGroupinRole(WorkGroupinrolesDTO _WorkGroupinrolesDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO tblmstgroup (groupname,groupdescription,issystemgroup,statusid,createdby,createddate) VALUES ('" + ManageQuote(_WorkGroupinrolesDTO.pGroupname).Trim().ToUpper() + "', '', 'N', " + Convert.ToInt32(Status.Active) + ", " + _WorkGroupinrolesDTO.pCreatedby + ", current_timestamp); ");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public bool SaveFunction(FunctionsDTO F)
{
Boolean IsValid = false;
try
{
//int count = Convert.ToInt16(NPGSqlHelper.ExecuteScalar(NPGSqlHelper.SQLConnString, CommandType.Text, "select * from tabrmsuserinfo where username='******' and userpassword='******' and statusid=1 and type='HRMS';"));
int count = Convert.ToInt16(NPGSqlHelper.ExecuteScalar(NPGSqlHelper.SQLConnString, CommandType.Text, "select count(*) from tabfunctions where functionname='" + F.FunctionName + "' and statusid=1 and moduleid=" + F.Moduleid + ";"));
if (count == 0)
{
string strSave = string.Empty;
//strSave = "insert into tabrmsuserinfo(username,applicationid,userpassword,usertype,issystemuser,staffid,statusid,createdby,createddate,type) ";
//strSave += " values('" + E.UserName.ToUpper().ToString() + "',1,'" + E.Password + "',1,0,0,1,1,current_timestamp,'HRMS');";
strSave = @"insert into tabfunctions(functionname,functionurl,statusid,moduleid,createdby,createddate,type) ";
strSave += " values('" + F.FunctionName.ToUpper().ToString() + "','" + F.FunctionUrl + "',1," + F.Moduleid + ",1,current_timestamp,'RMS');";
NPGSqlHelper.ExecuteNonQuery(NPGSqlHelper.SQLConnString, CommandType.Text, strSave);
IsValid = true;
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
return(IsValid);
}
public bool SaveChallanaEntry(ChallanaEntryDTO _ChallanaEntryDTO, string ConnectionString)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
string query = "";
long ChallanaId = 0;
if (string.IsNullOrEmpty(Convert.ToString(_ChallanaEntryDTO.pChallanaNo)))
{
_ChallanaEntryDTO.pChallanaNo = NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT FN_GENERATENEXTID('CHALLANA ENTRY', 'CHALLANA', current_date)").ToString();
//_ChallanaEntryDTO.pChallanaNo =Convert.ToString(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT FN_GENERATENEXTID('CHALLANA ENTRY','CHALLANA',current_date);"));
}
if (string.IsNullOrEmpty(_ChallanaEntryDTO.ptypeofoperation.ToString()))
{
_ChallanaEntryDTO.ptypeofoperation = "CREATE";
}
if (_ChallanaEntryDTO.ptypeofoperation.ToString().ToUpper() == "CREATE")
{
query = "insert into challana_entry( challana_no, from_date, to_date, company_type, tds_percent,calc_tds,actual_tds,paid_Amount,companyid)values('" + _ChallanaEntryDTO.pChallanaNo + "', '" + FormatDate(_ChallanaEntryDTO.pFromDate.ToString()) + "', '" + FormatDate(_ChallanaEntryDTO.pToDate.ToString()) + "', '" + _ChallanaEntryDTO.pCompanyType + "','" + _ChallanaEntryDTO.pTdsType + "'," + _ChallanaEntryDTO.pTotalTdsAmount + "," + _ChallanaEntryDTO.pActualTotalTdsAmount + "," + _ChallanaEntryDTO.pTotalPaidAmount + "," + _ChallanaEntryDTO.pCompanyId + ") returning challana_id";
ChallanaId = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, query));
}
if (_ChallanaEntryDTO._ChallanaEntryDetails != null)
{
for (int i = 0; i < _ChallanaEntryDTO._ChallanaEntryDetails.Count; i++)
{
sbinsert.Append("insert into challana_entry_details( challana_id, tds_voucher_id,parent_id,account_id,panno,amount,calc_tds,actual_tds,balance,paidamt,status)values (" + ChallanaId + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pTdsVoucherId + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pParentId + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pAccountId + ",'" + _ChallanaEntryDTO._ChallanaEntryDetails[i].pPanNo + "'," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pAmount + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pTdsAmount + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pActualTdsAmount + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pBalance + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pPaidAmount + ",'true');");
}
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
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);
}
public bool DeleteMemberType(int MemberID, string ConnectionString)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "update tblmstmembertypes set statusid= (select case when statusid=" + Convert.ToInt32(Status.Active) + " then " + Convert.ToInt32(Status.Inactive) + " when statusid=" + Convert.ToInt32(Status.Inactive) + " then " + Convert.ToInt32(Status.Active) + " end from tblmstmembertypes where membertypeid=" + MemberID + ") where membertypeid=" + MemberID + ";");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public bool SaveRole(RolesDTO _RolesDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (!string.IsNullOrEmpty(_RolesDTO.ptypeofoperation))
{
int RoleStatus = 2;
if (Convert.ToBoolean(_RolesDTO.pRoleactivestatus) == true)
{
RoleStatus = 1;
}
else
{
RoleStatus = 2;
}
if (_RolesDTO.ptypeofoperation.Trim().ToUpper() == "CREATE")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO tblmstgrouproles (roleid,groupid,groupname,rolename,roledescription,issystemrole,statusid,createdby,createddate) VALUES (" + _RolesDTO.pRoleID + "," + _RolesDTO.pGroupid + ", '" + ManageQuote(_RolesDTO.pGroupname) + "','" + ManageQuote(_RolesDTO.pRoledesignation).Trim().ToUpper() + "','', 'N', " + RoleStatus + ", " + _RolesDTO.pCreatedby + ", current_timestamp); ");
}
else if (_RolesDTO.ptypeofoperation.Trim().ToUpper() == "UPDATE")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "Update tblmstgrouproles set roleid=" + _RolesDTO.pRoleID + ",groupid=" + _RolesDTO.pGroupid + "," +
"groupname= '" + ManageQuote(_RolesDTO.pGroupname) + "', rolename= '" + ManageQuote(_RolesDTO.pRoledesignation).Trim().ToUpper() + "',statusid=" + RoleStatus + ",modifiedby=" + _RolesDTO.pCreatedby + ",modifieddate=current_timestamp where recordid=" + _RolesDTO.pRecordId + ";");
}
else if (_RolesDTO.ptypeofoperation.Trim().ToUpper() == "DELETE")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "Update tblmstgrouproles set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + _RolesDTO.pCreatedby + ",modifieddate=current_timestamp where recordid=" + _RolesDTO.pRecordId + ";");
}
trans.Commit();
Issaved = true;
}
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public bool SaveLienreleaseentry(LienreleaseSaveDTO _LienreleasesaveDTO, string connectionstring)
{
bool IsSaved = false;
StringBuilder sbinsert = new StringBuilder();
string qry = string.Empty;
try
{
con = new NpgsqlConnection(connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (_LienreleasesaveDTO.ListLienreleaseDTO != null)
{
for (int i = 0; i < _LienreleasesaveDTO.ListLienreleaseDTO.Count; i++)
{
if (Convert.ToString(_LienreleasesaveDTO.ListLienreleaseDTO[i].ptypeofoperation) == "CREATE")
{
sbinsert.Append("insert into tbltranslienrealse(lienid,lienrealsedate,statusid,createdby,createddate)values(" + _LienreleasesaveDTO.ListLienreleaseDTO[i].pLienid + ",'" + FormatDate(_LienreleasesaveDTO.ListLienreleaseDTO[i].pLienrealsedate.ToString()) + "'," + Convert.ToInt32(Status.Active) + ", " + _LienreleasesaveDTO.ListLienreleaseDTO[i].pCreatedby + ", current_timestamp);");
sbinsert.Append("update tbltranslienentry set lienstatus='Y' where lienid= " + _LienreleasesaveDTO.ListLienreleaseDTO[i].pLienid + ";");
}
else
{
sbinsert.Append("Update tbltranslienrealse set lienid = " + _LienreleasesaveDTO.ListLienreleaseDTO[i].pLienid + ",lienrealsedate = '" + FormatDate(_LienreleasesaveDTO.ListLienreleaseDTO[i].pLienrealsedate.ToString()) + "',modifiedby = '" + _LienreleasesaveDTO.ListLienreleaseDTO[i].pCreatedby + "',modifieddate= current_timestamp where lienid = " + _LienreleasesaveDTO.ListLienreleaseDTO[i].pLienid + ";");
}
}
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
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);
}
public bool UpdateMemberType(MemberTypeDTO MemberTypeDTO, string ConnectionString)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(MemberTypeDTO.pmembertype))
{
MemberTypeDTO.pmembertype = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pmembertypecode))
{
MemberTypeDTO.pmembertypecode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pcompanycode))
{
MemberTypeDTO.pcompanycode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pbranchcode))
{
MemberTypeDTO.pbranchcode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pseries))
{
MemberTypeDTO.pseries = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pmembercode))
{
MemberTypeDTO.pmembercode = "";
}
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "update tblmstmembertypes set membertype='" + ManageQuote(MemberTypeDTO.pmembertype) + "',membertypecode='" + ManageQuote(MemberTypeDTO.pmembertypecode) + "',companycode='" + ManageQuote(MemberTypeDTO.pcompanycode) + "',branchcode='" + ManageQuote(MemberTypeDTO.pbranchcode) + "',series='" + ManageQuote(MemberTypeDTO.pseries) + "',serieslength=" + MemberTypeDTO.pserieslength + ",membercode='" + ManageQuote(MemberTypeDTO.pmembercode) + "',issharesissueapplicable=" + MemberTypeDTO.pissharesissueapplicable + ",isaccounttypecreationapplicable=" + MemberTypeDTO.pisaccounttypecreationapplicable + ",ismembershipfeeapplicable=" + MemberTypeDTO.pismembershipfeeapplicable + " where membertypeid=" + MemberTypeDTO.pmembertypeid + " and statusid=" + Convert.ToInt32(Status.Active) + ";");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public bool SaveMemberType(MemberTypeDTO MemberTypeDTO, string ConnectionString)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
if (string.IsNullOrEmpty(MemberTypeDTO.pmembertype))
{
MemberTypeDTO.pmembertype = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pmembertypecode))
{
MemberTypeDTO.pmembertypecode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pcompanycode))
{
MemberTypeDTO.pcompanycode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pbranchcode))
{
MemberTypeDTO.pbranchcode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pseries))
{
MemberTypeDTO.pseries = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pmembercode))
{
MemberTypeDTO.pmembercode = "";
}
trans = con.BeginTransaction();
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO tblmstmembertypes(membertype,membertypecode,companycode,branchcode,series,serieslength,membercode,issharesissueapplicable,statusid,createdby,createddate,isaccounttypecreationapplicable,ismembershipfeeapplicable) VALUES('" + ManageQuote(MemberTypeDTO.pmembertype) + "','" + ManageQuote(MemberTypeDTO.pmembertypecode) + "','" + ManageQuote(MemberTypeDTO.pcompanycode) + "','" + ManageQuote(MemberTypeDTO.pbranchcode) + "','" + ManageQuote(MemberTypeDTO.pseries) + "'," + MemberTypeDTO.pserieslength + ",'" + ManageQuote(MemberTypeDTO.pmembercode) + "'," + MemberTypeDTO.pissharesissueapplicable + "," + Convert.ToInt32(Status.Active) + "," + MemberTypeDTO.pCreatedby + ",CURRENT_TIMESTAMP," + MemberTypeDTO.pisaccounttypecreationapplicable + "," + MemberTypeDTO.pismembershipfeeapplicable + ");");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public string SaveshareApplication(ShareApplicationDTO ShareApplicationDTO, string ConnectionString, out long pShareaccountid)
{
bool IsSaved = false;
StringBuilder sbInsert = new StringBuilder();
Int64 Maxsharetovalue = 0;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(ShareApplicationDTO.pshareapplicationid.ToString()) || ShareApplicationDTO.pshareapplicationid == 0)
{
Maxsharetovalue = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select coalesce(max(distinctive_to),0) from Share_Account_creation;"));
ShareApplicationDTO.pdistinctivefrom = Maxsharetovalue + 1;
// ShareApplicationDTO.pdistinctiveto = (Maxsharetovalue + ShareApplicationDTO.pnoofsharesissued) - 1;
ShareApplicationDTO.pdistinctiveto = (ShareApplicationDTO.pdistinctivefrom + ShareApplicationDTO.pnoofsharesissued);
ShareApplicationDTO.pShareAccountNo = NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "SELECT FN_GENERATENEXTID('SHARE CAPITAL','" + ShareApplicationDTO.psharename + "','" + ShareApplicationDTO.pTransdate + "')").ToString();
//ShareApplicationDTO.pShareAccountNo = "sd0001";
ShareApplicationDTO.pshareapplicationid = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "insert into Share_Account_creation(share_Account_number,share_config_id,share_name,member_type_id,member_id,member_name,applicant_type,Trans_date,reference_no,face_value,no_of_shares_issued,distinctive_from,distinctive_to,total_amount,shares_issue_date,is_member_fee_applicable,Bond_print_status,statusid,createdby,createddate)values('" + ShareApplicationDTO.pShareAccountNo + "'," + ShareApplicationDTO.pshareconfigid + ",'" + ManageQuote(ShareApplicationDTO.psharename) + "'," + Convert.ToInt64(ShareApplicationDTO.pmembertypeid) + "," + Convert.ToInt64(ShareApplicationDTO.pmemberid) + ", '" + ManageQuote(ShareApplicationDTO.pmembername) + "','" + ManageQuote(ShareApplicationDTO.pApplicanttype) + "','" + FormatDate(ShareApplicationDTO.pTransdate) + "','" + ManageQuote(ShareApplicationDTO.preferenceno) + "'," + Convert.ToDecimal(ShareApplicationDTO.pfacevalue) + "," + Convert.ToInt64(ShareApplicationDTO.pnoofsharesissued) + "," + Convert.ToInt64(ShareApplicationDTO.pdistinctivefrom) + "," + Convert.ToInt64(ShareApplicationDTO.pdistinctiveto) + "," + Convert.ToDecimal(ShareApplicationDTO.ptotalamount) + ",'" + FormatDate(ShareApplicationDTO.pshareissuedate) + "','" + ShareApplicationDTO.pismemberfeeapplicable + "','N'," + Convert.ToInt32(Status.Active) + "," + ShareApplicationDTO.pCreatedby + ",current_timestamp) returning share_account_id;"));
}
else
{
sbInsert.Append("update Share_Account_creation set is_member_fee_applicable='" + ShareApplicationDTO.pismemberfeeapplicable + "', member_type_id=" + Convert.ToInt64(ShareApplicationDTO.pmembertypeid) + ", member_id=" + Convert.ToInt64(ShareApplicationDTO.pmemberid) + ", share_config_id=" + Convert.ToInt64(ShareApplicationDTO.pshareconfigid) + ",reference_no='" + ManageQuote(ShareApplicationDTO.preferenceno) + "', face_value=" + Convert.ToDecimal(ShareApplicationDTO.pfacevalue) + ",no_of_shares_issued=" + Convert.ToInt64(ShareApplicationDTO.pnoofsharesissued) + ", distinctive_from=" + Convert.ToInt64(ShareApplicationDTO.pdistinctivefrom) + ", distinctive_to=" + Convert.ToInt64(ShareApplicationDTO.pdistinctiveto) + ", total_amount=" + Convert.ToDecimal(ShareApplicationDTO.ptotalamount) + ",Share_Joining_date='" + FormatDate(ShareApplicationDTO.pTransdate) + "', shares_issue_date='" + FormatDate(ShareApplicationDTO.pshareissuedate) + "', Bond_print_status='N',modifiedby=" + ShareApplicationDTO.pCreatedby + ",modifieddate=current_timestamp where share_account_id=" + ShareApplicationDTO.pShareaccountid + " and share_account_number=" + ShareApplicationDTO.pShareAccountNo + ";");
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
}
pShareaccountid = ShareApplicationDTO.pshareapplicationid;
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Convert.ToString(ShareApplicationDTO.pShareAccountNo));
}
public bool SaveFdReceipt(FDReceiptDTO ObjFdReceiptDTO, string ConnectionString, out string OUTReceiptid)
{
bool Issaved = false;
StringBuilder sbInsert = new StringBuilder();
bool IsAccountSaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (Savegenaralreceipt(ObjFdReceiptDTO, trans, out OUTReceiptid))
{
IsAccountSaved = true;
}
else
{
trans.Rollback();
return(IsAccountSaved);
}
string Receiptno = OUTReceiptid;
if (string.IsNullOrEmpty(ObjFdReceiptDTO.pRecordid.ToString()) || ObjFdReceiptDTO.pRecordid == 0)
{
sbInsert.Append("INSERT INTO public.fd_receipt(fd_receiptt_date,member_id, fd_account_id,deposit_type, instalment_amount, received_amount, mode_of_receipt,receipt_no,narration,status)VALUES('" + FormatDate(ObjFdReceiptDTO.pReceiptdate) + "'," + ObjFdReceiptDTO.pMemberid + "," + ObjFdReceiptDTO.pFdaccountid + ",'" + ObjFdReceiptDTO.pDeposittype + "'," + ObjFdReceiptDTO.pInstalmentamount + "," + ObjFdReceiptDTO.pReceivedamount + ",'" + ObjFdReceiptDTO.pModeofreceipt + "','" + Receiptno + "','" + ObjFdReceiptDTO.pNarration + "'," + ObjFdReceiptDTO.pStatus + ");");
}
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
if (ObjFdReceiptDTO.pModeofreceipt == "CASH")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "SELECT FN_PROMOTOR_SALARY_JV(" + ObjFdReceiptDTO.pFdaccountid + ",'" + FormatDate(ObjFdReceiptDTO.pReceiptdate) + "')");
}
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);
}
public bool SaveIdentificationdocuments(IdentificationDocumentsDTO obIdentificationDocuments, string connectionstring)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (obIdentificationDocuments.identificationdocumentsList != null)
{
//if (obIdentificationDocuments.ptypeofoperation == "UPDATE")
//{
//if (obIdentificationDocuments.identificationdocumentsList.Count > 0)
//{
sbinsert.Append("delete from tblmstloanwisedocumentproofs where loanid=" + obIdentificationDocuments.pSavingConfigid + ";");
//}
//}
for (int i = 0; i < obIdentificationDocuments.identificationdocumentsList.Count; i++)
{
if (obIdentificationDocuments.identificationdocumentsList[i].pDocumentRequired == true || obIdentificationDocuments.identificationdocumentsList[i].pDocumentMandatory == true)
{
sbinsert.Append("insert into tblmstloanwisedocumentproofs(loantypeid,loanid,contacttype,documentid,documentgroupid,isdocumentrequired,isdocumentmandatory,statusid,createdby,createddate) values(" + obIdentificationDocuments.pSavingConfigid + "," + obIdentificationDocuments.pSavingConfigid + ",'" + ManageQuote(obIdentificationDocuments.identificationdocumentsList[i].pContactType) + "'," + obIdentificationDocuments.identificationdocumentsList[i].pDocumentId + "," + obIdentificationDocuments.identificationdocumentsList[i].pDocumentgroupId + ",'" + obIdentificationDocuments.identificationdocumentsList[i].pDocumentRequired + "','" + obIdentificationDocuments.identificationdocumentsList[i].pDocumentMandatory + "'," + Convert.ToInt32(Status.Active) + "," + obIdentificationDocuments.identificationdocumentsList[i].pCreatedby + ",current_timestamp);");
}
}
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
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);
}
public bool saveGroupConfiguration(GroupCreationDTO groupDetails, string connectionString)
{
int groupSavedCount = 0;
long groupId;
StringBuilder saveGroupConfiguration = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
groupDetails.pMembersCount = Convert.ToString(groupDetails.pMembersCount) == string.Empty ? 0 : groupDetails.pMembersCount < 0 ? 0 : groupDetails.pMembersCount;
// Master Data
groupId = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "insert into tblmstgroupconfig(grouptype,groupname,groupcode,membercount,statusid,createdby,createddate) values('" + ManageQuote(groupDetails.pGroupType).Trim() + "','" + ManageQuote(groupDetails.pGroupName).Trim() + "','" + ManageQuote(groupDetails.pGroupCode).Trim() + "'," + groupDetails.pMembersCount + "," + Convert.ToInt32(Status.Active) + "," + groupDetails.pCreatedby + ",current_timestamp) returning groupid;"));
// groupId = 23;
// Child Details
if (groupDetails.pListGroupDetails != null && groupDetails.pListGroupDetails.Count > 0)
{
foreach (GroupCreation childDetails in groupDetails.pListGroupDetails)
{
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tblgroupnamewisemembers where contactid=" + childDetails.pContactID + " and groupname='" + ManageQuote(groupDetails.pGroupName).Trim() + "';"));
if (count == 0)
{
saveGroupConfiguration.Append("insert into tblgroupnamewisemembers(groupid,groupname,groupno,contactid,contactreferenceid,contactname,contactnumber,grouproleid,roleingroup,statusid,createdby,createddate) values(" + groupId + ",'" + ManageQuote(groupDetails.pGroupName).Trim() + "','" + ManageQuote(groupDetails.pGroupNo).Trim() + "'," + childDetails.pContactID + ",'" + ManageQuote(childDetails.pContactRefId) + "','" + ManageQuote(childDetails.pContactName).Trim() + "','" + ManageQuote(childDetails.pContactNo).Trim() + "'," + (childDetails.pGrouproleID) + ",'" + ManageQuote(childDetails.pRoleInGroup).Trim() + "'," + Convert.ToInt32(Status.Active) + "," + groupDetails.pCreatedby + ",current_timestamp);");
}
}
}
if (Convert.ToString(saveGroupConfiguration) != string.Empty)
{
groupSavedCount = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, saveGroupConfiguration.ToString());
trans.Commit();
}
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(groupSavedCount > 0 ? true : false);
}
public bool SaveReferralCommission(ReferralCommissionDTO objReferralCommission, string ConnectionString)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(objReferralCommission.pCommissionValue.ToString()))
{
objReferralCommission.pCommissionValue = 0;
}
if (string.IsNullOrEmpty(objReferralCommission.pTdspercentage.ToString()))
{
objReferralCommission.pTdspercentage = 0;
}
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tblmstSavingAccountConfigreferraldetails where savingconfigid=" + objReferralCommission.pSavingConfigid + " and statusid=" + Convert.ToInt32(Status.Active) + ""));
if (count != 0)
{
sbinsert.Append("Update tblmstSavingAccountConfigreferraldetails set isreferralcommissionapplicable=" + objReferralCommission.pIsreferralcommissionapplicable + ", referralcommissioncalfield='" + ManageQuote(objReferralCommission.pReferralcommissioncalfield) + "',referralcommissiontype='" + objReferralCommission.pReferralcommissiontype + "' ,commissionValue=" + objReferralCommission.pCommissionValue + ",istdsapplicable=" + objReferralCommission.pIstdsapplicable + ",tdsaccountid='" + ManageQuote(objReferralCommission.pTdsaccountid) + "',tdssection='" + ManageQuote(objReferralCommission.ptdssection) + "',tdspercentage=" + objReferralCommission.pTdspercentage + ",modifiedby =" + objReferralCommission.pCreatedby + ",modifieddate=current_timestamp where savingconfigid =" + objReferralCommission.pSavingConfigid + ";");
}
if (count == 0)
{
sbinsert.Append("insert into tblmstSavingAccountConfigreferraldetails(savingconfigid ,savingaccname,isreferralcommissionapplicable,referralcommissioncalfield,referralcommissiontype ,commissionValue,istdsapplicable,tdsaccountid,tdssection,tdspercentage,statusid,createdby,createddate) values(" + objReferralCommission.pSavingConfigid + ",'" + objReferralCommission.pSavingAccname + "'," + objReferralCommission.pIsreferralcommissionapplicable + ",'" + ManageQuote(objReferralCommission.pReferralcommissioncalfield) + "','" + objReferralCommission.pReferralcommissiontype + "'," + objReferralCommission.pCommissionValue + "," + objReferralCommission.pIstdsapplicable + ",'" + ManageQuote(objReferralCommission.pTdsaccountid) + "','" + ManageQuote(objReferralCommission.ptdssection) + "'," + objReferralCommission.pTdspercentage + "," + Convert.ToInt32(Status.Active) + "," + objReferralCommission.pCreatedby + ",current_timestamp);");
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
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);
}
public bool SaveLoanFacility(LoanFacilityDTO objLoanFacility, string connectionstring)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(objLoanFacility.pEligiblepercentage.ToString()))
{
objLoanFacility.pEligiblepercentage = 0;
}
if (string.IsNullOrEmpty(objLoanFacility.pAgeperiod.ToString()))
{
objLoanFacility.pAgeperiod = 0;
}
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tblmstSavingAccountLoansConfig where savingconfigid=" + objLoanFacility.pSavingConfigid + " and statusid=" + Convert.ToInt32(Status.Active) + ""));
if (count == 0)
{
sbinsert.Append("insert into tblmstSavingAccountLoansConfig(savingconfigid, savingaccname, isloanfacilityapplicable, eligiblepercentage, ageperiod, ageperiodtype, statusid, createdby, createddate)values(" + objLoanFacility.pSavingConfigid + ",'" + objLoanFacility.pSavingAccname + "'," + objLoanFacility.pIsloanfacilityapplicable + "," + objLoanFacility.pEligiblepercentage + "," + objLoanFacility.pAgeperiod + ",'" + objLoanFacility.pAgeperiodtype + "'," + Convert.ToInt32(Status.Active) + "," + objLoanFacility.pCreatedby + ",current_timestamp);");
}
if (count != 0)
{
sbinsert.Append("update tblmstSavingAccountLoansConfig set isloanfacilityapplicable=" + objLoanFacility.pIsloanfacilityapplicable + ", eligiblepercentage=" + objLoanFacility.pEligiblepercentage + ", ageperiod=" + objLoanFacility.pAgeperiod + ", ageperiodtype='" + objLoanFacility.pAgeperiodtype + "',modifiedby =" + objLoanFacility.pCreatedby + ",modifieddate=current_timestamp where savingconfigid =" + objLoanFacility.pSavingConfigid + ";");
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
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);
}
public bool SaveRoleFunction(MenuandNavigationDTO _MenuandNavigationDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (!string.IsNullOrEmpty(_MenuandNavigationDTO.ptypeofoperation))
{
string ParentFunctionid = Convert.ToString(_MenuandNavigationDTO.PFunctionParentID);
if (_MenuandNavigationDTO.PFunctionParentID == 0)
{
ParentFunctionid = "null";
}
if (_MenuandNavigationDTO.ptypeofoperation.Trim().ToUpper() == "CREATE")
{
_ = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText: "INSERT INTO tblmstfunctions (functionparentid,parentmoduleid,submoduleid,functionname,functiondescription,functionurl,isfunctionshowinnavigation,isfunctionallowinroles,statusid,createdby,createddate) VALUES (" + ParentFunctionid + "," + _MenuandNavigationDTO.pModuleId + "," + _MenuandNavigationDTO.pSubmoduleId + ", '" + ManageQuote(_MenuandNavigationDTO.pFunctionname).Trim() + "','','" + ManageQuote(_MenuandNavigationDTO.pFunctionurl).Trim() + "','" + _MenuandNavigationDTO.pIsfunctionshowinNavigation + "','" + _MenuandNavigationDTO.pIsFunctionshowinRoles + "'," + Convert.ToInt32(Status.Active) + ", " + _MenuandNavigationDTO.pCreatedby + ", current_timestamp); ");
}
else if (_MenuandNavigationDTO.ptypeofoperation.Trim().ToUpper() == "UPDATE")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "Update tblmstfunctions set parentmoduleid=" + _MenuandNavigationDTO.pModuleId + ",submoduleid= '" + ManageQuote(_MenuandNavigationDTO.pModulename).Trim() + "', functionname= '" + ManageQuote(_MenuandNavigationDTO.pFunctionname).Trim() + "',functionurl='" + ManageQuote(_MenuandNavigationDTO.pFunctionurl).Trim() + "',isfunctionshowinnavigation='" + _MenuandNavigationDTO.pIsfunctionshowinNavigation + "',isfunctionallowinroles='" + _MenuandNavigationDTO.pIsFunctionshowinRoles + "',modifiedby=" + _MenuandNavigationDTO.pCreatedby + ",modifieddate=current_timestamp,functionparentid=" + ParentFunctionid + " where functionid=" + _MenuandNavigationDTO.pFunctionId + ";");
}
else if (_MenuandNavigationDTO.ptypeofoperation.Trim().ToUpper() == "DELETE")
{
_ = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText: "update tblmstfunctions set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + _MenuandNavigationDTO.pCreatedby + ",modifieddate=current_timestamp where functionid=" + _MenuandNavigationDTO.pFunctionId + ";");
}
trans.Commit();
Issaved = true;
}
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public bool SaveMemberReceipt(MemberReceiptDTO ObjMemberReceiptDTO, string ConnectionString, out string OUTReceiptid)
{
bool Issaved = false;
StringBuilder sbInsert = new StringBuilder();
bool IsAccountSaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (Savegenaralreceipt(ObjMemberReceiptDTO, trans, out OUTReceiptid))
{
IsAccountSaved = true;
}
else
{
trans.Rollback();
return(IsAccountSaved);
}
string Receiptno = OUTReceiptid;
ObjMemberReceiptDTO.pReceiptno = Receiptno;
if (string.IsNullOrEmpty(ObjMemberReceiptDTO.pRecordid.ToString()) || ObjMemberReceiptDTO.pRecordid == 0)
{
sbInsert.Append("insert into Member_receipt (member_id, receipt_date, received_amount, mode_of_receipt, receipt_no, narration, status) values (" + ObjMemberReceiptDTO.pMemberid + ", '" + FormatDate(ObjMemberReceiptDTO.pReceiptdate) + "', " + ObjMemberReceiptDTO.pReceivedamount + ", '" + ObjMemberReceiptDTO.pModeofreceipt + "', '" + ObjMemberReceiptDTO.pReceiptno + "', '" + ObjMemberReceiptDTO.pNarration + "', true);");
}
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
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);
}
public bool SaveCinEntry(CinEntryDTO _CinEntryDTO, string ConnectionString)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
string query = "";
Int64 VoucherId = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select recordid from tbltranspaymentvoucher where paymentid = '" + _CinEntryDTO.pVoucherId + "'"));
if (string.IsNullOrEmpty(_CinEntryDTO.ptypeofoperation.ToString()))
{
_CinEntryDTO.ptypeofoperation = "CREATE";
}
if (_CinEntryDTO.ptypeofoperation.ToString().ToUpper() == "CREATE")
{
query = "insert into cin_entry( challana_id, voucher_id, reference_no, paid_date, paid_bank,bsr_code,challana_sl_no,challana_bank,challana_date)values(" + _CinEntryDTO.pChallanaId + "," + VoucherId + ",'" + _CinEntryDTO.pReferenceNo + "','" + FormatDate(_CinEntryDTO.pPaidDate.ToString()) + "','" + _CinEntryDTO.pPaidBank + "','" + _CinEntryDTO.pBsrCode + "','" + _CinEntryDTO.pChallanaSNO + "','" + _CinEntryDTO.pChallanaBank + "','" + FormatDate(_CinEntryDTO.pChallanaDate.ToString()) + "') ";
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, query);
}
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);
}
public bool SaveAddressType(contactAddressDTO addresstype, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "insert into tblmstaddresstypes(contacttype,addresstype,statusid,createdby,createddate)values('" + ManageQuote(addresstype.pContactType) + "','" + ManageQuote(addresstype.pAddressType.Trim()) + "'," + getStatusid(addresstype.pStatusname, ConnectionString) + "," + addresstype.pCreatedby + ",current_timestamp);");
isSaved = true;
}
catch (Exception)
{
throw;
}
return(isSaved);
}
public bool DeleteContact(ContactMasterDTO contact, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "update tblmstcontact set statusid=" + getStatusid(contact.pStatusname, ConnectionString) + ",modifiedby=" + contact.pCreatedby + ",modifieddate=current_timestamp where upper(contactreferenceid)='" + contact.pReferenceId.ToUpper() + "';");
isSaved = true;
}
catch (Exception ex)
{
throw ex;
}
return(isSaved);
}
public bool DeletePreclouserCharges(string ConnectionString, long Loanid, long Recordid, long userid)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "Update TBLMSTLOANPRECLOUSERCHARGES Set STATUSID=2,MODIFIEDBY=" + userid + ",MODIFIEDDATE=current_timestamp where loanid=" + Loanid + " and recordid=" + Recordid + " ;");
isSaved = true;
}
catch (Exception)
{
throw;
}
return(isSaved);
}
public bool UpdatePreclouserCharges(PreclouserchargesDTO PreclouserCharges, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "Update TBLMSTLOANPRECLOUSERCHARGES Set ISCHARGEAPPLICABLE='" + ManageQuote(PreclouserCharges.pIschargeapplicable) + "',CHARGECALTYPE='" + ManageQuote(PreclouserCharges.pChargecaltype) + "',CHARGECALONFIELD='" + ManageQuote(PreclouserCharges.pChargecalonfield) + "',CHARGESVALUE=" + PreclouserCharges.pChargesvalue + ",ISTAXAPPLICABLE='" + ManageQuote(PreclouserCharges.pIstaxapplicable) + "', TAXTYPE='" + ManageQuote(PreclouserCharges.pTaxtype) + "',TAXPERCENTAGE=" + PreclouserCharges.pTaxpercentage + ",LOCKINGPERIOD=" + PreclouserCharges.pLockingperiod + ",LOCKINGPERIODTYPE='" + ManageQuote(PreclouserCharges.pLockingperiodtype) + "',MODIFIEDBY=" + PreclouserCharges.pCreatedby + ",MODIFIEDDATE=current_timestamp where RECORDID=" + PreclouserCharges.pRecordid + " ;");
isSaved = true;
}
catch (Exception)
{
throw;
}
return(isSaved);
}
public bool SavePreclouserCharges(PreclouserchargesDTO PreclouserCharges, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "insert into TBLMSTLOANPRECLOUSERCHARGES(LOANTYPEID,LOANID,ISCHARGEAPPLICABLE,CHARGECALTYPE,CHARGECALONFIELD,CHARGESVALUE,ISTAXAPPLICABLE,TAXTYPE,TAXPERCENTAGE,LOCKINGPERIOD,LOCKINGPERIODTYPE,STATUSID,CREATEDBY,CREATEDDATE)values(" + PreclouserCharges.pLoantypeid + "," + PreclouserCharges.pLoanid + ",'" + ManageQuote(PreclouserCharges.pIschargeapplicable) + "','" + ManageQuote(PreclouserCharges.pChargecaltype) + "','" + ManageQuote(PreclouserCharges.pChargecalonfield) + "'," + PreclouserCharges.pChargesvalue + ",'" + ManageQuote(PreclouserCharges.pIstaxapplicable) + "','" + ManageQuote(PreclouserCharges.pTaxtype) + "'," + PreclouserCharges.pTaxpercentage + "," + PreclouserCharges.pLockingperiod + ",'" + ManageQuote(PreclouserCharges.pLockingperiodtype) + "',1," + PreclouserCharges.pCreatedby + ",current_timestamp);");
isSaved = true;
}
catch (Exception ex)
{
throw ex;
}
return(isSaved);
}
public bool SaveChargesName(ChargesMasterDTO charges, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "insert into TBLMSTCHARGESTYPES(CHARGENAME,statusid,createdby,createddate)values('" + ManageQuote(charges.pChargename) + "'," + getStatusid(charges.pStatusname, ConnectionString) + "," + charges.pCreatedby + ",current_timestamp);");
isSaved = true;
}
catch (Exception)
{
throw;
}
return(isSaved);
}
public bool DeleteChargesName(ChargesMasterDTO charges, string ConnectionString)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, "update TBLMSTCHARGESTYPES set statusid=" + getStatusid(charges.pStatusname, ConnectionString) + ",modifiedby=" + charges.pCreatedby + ",modifieddate=current_timestamp where chargeid=" + charges.pChargeid + ";");
isSaved = true;
}
catch (Exception ex)
{
throw ex;
}
return(isSaved);
}
public bool SaveAddressType(string addresstype)
{
bool isSaved = false;
try
{
NPGSqlHelper.ExecuteNonQuery(NPGSqlHelper.SQLConnString, CommandType.Text, "insert into tblmstaddresstypes(addresstype,statusid,createdby,createddate)values('" + addresstype + "',1,1,current_timestamp);");
isSaved = true;
}
catch (Exception)
{
throw;
}
return(isSaved);
}