???File Path ========== ***...\Src\Account\PaidChequeReturn.cs ???Check the points ================== // ??? IMPORT:- Add the items top - Duplcation Avoid using System; using System.Data; using System.Collections.Generic; using Npgsql; using NpgsqlTypes; using TAAS.Db; using TAAS.Models; using TAAS.Models.Account; using TAAS.Helpers; // ??? CLASS ATTRIBUTES:- The code past top of the class(Attributes) - Duplcation Avoid public GlobalFunctions insGlobalFunctions = new GlobalFunctions(); DbService insDbService = new(); public List FnGetAllPaidChequeReturnBaseData() { List lstMdlPaidChequeReturnBaseIntId = new List(); // Get Data from DB DataSet objDataSet = this.insDbService.FnExcecuteSqlCommand("SELECT * FROM fn_get_all_paid_cheque_return()"); if (objDataSet != null) { foreach (DataRow objRecord in objDataSet.Tables[0].Rows) { MdlBaseIntId mdlBaseIntId = new MdlBaseIntId(); mdlBaseIntId.binPkId = (DBNull.Value != objRecord["pk_paid_cheque_return_id"]) ? (long)Convert.ToUInt64(objRecord["pk_paid_cheque_return_id"]) : mdlBaseIntId.binPkId; mdlBaseIntId.strCode = (DBNull.Value != objRecord[""]) ? Convert.ToString(objRecord[""]) : mdlBaseIntId.strCode; mdlBaseIntId.strName = (DBNull.Value != objRecord[""]) ? Convert.ToString(objRecord[""]) : mdlBaseIntId.strName; lstMdlPaidChequeReturnBaseIntId.Add(mdlBaseIntId); } } return lstMdlPaidChequeReturnBaseIntId; } public List FnGetAllPaidChequeReturnWithActionUser() { List lstMdlPaidChequeReturn = new List(); // Get Data from DB DataSet objDataSet = this.insDbService.FnExcecuteSqlCommand("SELECT * FROM fn_get_all_paid_cheque_return_with_action_user()"); if (objDataSet != null) { foreach (DataRow objRecord in objDataSet.Tables[0].Rows) { MdlPaidChequeReturn mdlPaidChequeReturn = new MdlPaidChequeReturn(); mdlPaidChequeReturn.binPaidChequeReturnId = (DBNull.Value != objRecord["pk_paid_cheque_return_id"]) ? (long)Convert.ToInt64(objRecord["pk_paid_cheque_return_id"]) : mdlPaidChequeReturn.binPaidChequeReturnId; mdlPaidChequeReturn.binDocumentsId = (DBNull.Value != objRecord["fk_documents_id"]) ? (long)Convert.ToInt64(objRecord["fk_documents_id"]) : mdlPaidChequeReturn.binDocumentsId; mdlPaidChequeReturn.binPaidChequeId = (DBNull.Value != objRecord["fk_paid_cheque_id"]) ? (long)Convert.ToInt64(objRecord["fk_paid_cheque_id"]) : mdlPaidChequeReturn.binPaidChequeId; mdlPaidChequeReturn.binPaidChequeDetailsId = (DBNull.Value != objRecord["fk_paid_cheque_details_id"]) ? (long)Convert.ToInt64(objRecord["fk_paid_cheque_details_id"]) : mdlPaidChequeReturn.binPaidChequeDetailsId; mdlPaidChequeReturn.binBankChargeId = (DBNull.Value != objRecord["fk_bank_charge_id"]) ? (long)Convert.ToInt64(objRecord["fk_bank_charge_id"]) : mdlPaidChequeReturn.binBankChargeId; mdlPaidChequeReturn.binBankCrCustomerId = (DBNull.Value != objRecord["fk_bank_cr_customer_id"]) ? (long)Convert.ToInt64(objRecord["fk_bank_cr_customer_id"]) : mdlPaidChequeReturn.binBankCrCustomerId; mdlPaidChequeReturn.binBankExpenseId = (DBNull.Value != objRecord["fk_bank_expense_id"]) ? (long)Convert.ToInt64(objRecord["fk_bank_expense_id"]) : mdlPaidChequeReturn.binBankExpenseId; mdlPaidChequeReturn.strBankCurrency = (DBNull.Value != objRecord["vhr_bank_currency"]) ? Convert.ToString(objRecord["vhr_bank_currency"]) : mdlPaidChequeReturn.strBankCurrency; mdlPaidChequeReturn.dblBankCurRoe = (DBNull.Value != objRecord["dbl_bank_cur_roe"]) ? (double)Convert.ToDouble(objRecord["dbl_bank_cur_roe"]) : mdlPaidChequeReturn.dblBankCurRoe; mdlPaidChequeReturn.dblBankCurBankCharge = (DBNull.Value != objRecord["dbl_bank_cur_bank_charge"]) ? (double)Convert.ToDouble(objRecord["dbl_bank_cur_bank_charge"]) : mdlPaidChequeReturn.dblBankCurBankCharge; mdlPaidChequeReturn.strCustCurrency = (DBNull.Value != objRecord["vhr_cust_currency"]) ? Convert.ToString(objRecord["vhr_cust_currency"]) : mdlPaidChequeReturn.strCustCurrency; mdlPaidChequeReturn.dblCustCurRoe = (DBNull.Value != objRecord["dbl_cust_cur_roe"]) ? (double)Convert.ToDouble(objRecord["dbl_cust_cur_roe"]) : mdlPaidChequeReturn.dblCustCurRoe; mdlPaidChequeReturn.dblCustCurCustCharge = (DBNull.Value != objRecord["dbl_cust_cur_cust_charge"]) ? (double)Convert.ToDouble(objRecord["dbl_cust_cur_cust_charge"]) : mdlPaidChequeReturn.dblCustCurCustCharge; mdlPaidChequeReturn.strBaseCurrency = (DBNull.Value != objRecord["vhr_base_currency"]) ? Convert.ToString(objRecord["vhr_base_currency"]) : mdlPaidChequeReturn.strBaseCurrency; mdlPaidChequeReturn.dblBaseCurBankCharge = (DBNull.Value != objRecord["dbl_base_cur_bank_charge"]) ? (double)Convert.ToDouble(objRecord["dbl_base_cur_bank_charge"]) : mdlPaidChequeReturn.dblBaseCurBankCharge; mdlPaidChequeReturn.dblBaseCurCustCharge = (DBNull.Value != objRecord["dbl_base_cur_cust_charge"]) ? (double)Convert.ToDouble(objRecord["dbl_base_cur_cust_charge"]) : mdlPaidChequeReturn.dblBaseCurCustCharge; mdlPaidChequeReturn.binServiceTaxId = (DBNull.Value != objRecord["fk_service_tax_id"]) ? (long)Convert.ToInt64(objRecord["fk_service_tax_id"]) : mdlPaidChequeReturn.binServiceTaxId; mdlPaidChequeReturn.dblSysPerceOfTax = (DBNull.Value != objRecord["dbl_sys_perce_of_tax"]) ? (double)Convert.ToDouble(objRecord["dbl_sys_perce_of_tax"]) : mdlPaidChequeReturn.dblSysPerceOfTax; mdlPaidChequeReturn.dblPerceOfTax = (DBNull.Value != objRecord["dbl_perce_of_tax"]) ? (double)Convert.ToDouble(objRecord["dbl_perce_of_tax"]) : mdlPaidChequeReturn.dblPerceOfTax; mdlPaidChequeReturn.dblLedgCurTaxBaseAmount = (DBNull.Value != objRecord["dbl_ledg_cur_tax_base_amount"]) ? (double)Convert.ToDouble(objRecord["dbl_ledg_cur_tax_base_amount"]) : mdlPaidChequeReturn.dblLedgCurTaxBaseAmount; mdlPaidChequeReturn.dblLedgCurTaxAmount = (DBNull.Value != objRecord["dbl_ledg_cur_tax_amount"]) ? (double)Convert.ToDouble(objRecord["dbl_ledg_cur_tax_amount"]) : mdlPaidChequeReturn.dblLedgCurTaxAmount; mdlPaidChequeReturn.dblBaseCurTaxBaseAmount = (DBNull.Value != objRecord["dbl_base_cur_tax_base_amount"]) ? (double)Convert.ToDouble(objRecord["dbl_base_cur_tax_base_amount"]) : mdlPaidChequeReturn.dblBaseCurTaxBaseAmount; mdlPaidChequeReturn.dblBaseCurTaxAmount = (DBNull.Value != objRecord["dbl_base_cur_tax_amount"]) ? (double)Convert.ToDouble(objRecord["dbl_base_cur_tax_amount"]) : mdlPaidChequeReturn.dblBaseCurTaxAmount; lstMdlPaidChequeReturn.Add(mdlPaidChequeReturn); } } return lstMdlPaidChequeReturn; } public MdlResPaidChequeReturn FnInsertOrUpdatePaidChequeReturn(MdlReqPaidChequeReturn mdlReqPaidChequeReturn) { // *** Input Validation INSERT and UPDATE Case[not duplicate model validation] // ??? Duplication Checking in DB FUCTION(Insert and Update) - CODE, NAME [, other unique fields] Already exists // ??? Count Exced or other checking License related in DB FUCTION(Insert) // ??? UPDATE Check already update another users in DB FUCTION(Update and Delete) // Set Create or modified User in DB FUCTION(Insert, Update and Delete) if (mdlReqPaidChequeReturn.mdlPaidChequeReturn.binPaidChequeReturnId == 0) { mdlReqPaidChequeReturn.mdlPaidChequeReturn.intSysActionId = 0; mdlReqPaidChequeReturn.mdlPaidChequeReturn.binCreatedUserId = mdlReqPaidChequeReturn.linLoginUserId; mdlReqPaidChequeReturn.mdlPaidChequeReturn.strCreatedUser = mdlReqPaidChequeReturn.strLoginUserName; mdlReqPaidChequeReturn.mdlPaidChequeReturn.dtmCreated = mdlReqPaidChequeReturn.dtmCurrentDateTime; } else { mdlReqPaidChequeReturn.mdlPaidChequeReturn.binLastModifiedUserId = mdlReqPaidChequeReturn.linLoginUserId; mdlReqPaidChequeReturn.mdlPaidChequeReturn.strLastModifiedUser = mdlReqPaidChequeReturn.strLoginUserName; mdlReqPaidChequeReturn.mdlPaidChequeReturn.dtmLastModified = mdlReqPaidChequeReturn.dtmCurrentDateTime; } // DB Actions NpgsqlParameter[] lstSqlParameter = { new NpgsqlParameter("mdlPaidChequeReturn", NpgsqlDbType.Jsonb) { Value = mdlReqPaidChequeReturn.mdlPaidChequeReturn, DataTypeName = "jsnPaidChequeReturn"}, new NpgsqlParameter("jsnSqlRes", NpgsqlDbType.Json) { DataTypeName = "jsnSqlRes", Direction = ParameterDirection.Output } }; MdlDbResponse mdlDbResponse = this.insDbService.FnExcecuteOutSqlCommand("CALL sp_insert_or_update_paidChequeReturn(:mdlPaidChequeReturn, NULL)", lstSqlParameter); // DB Error if (mdlDbResponse.sinSqlCustomStatus != 1) { if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "PAIDCHEQUERETURN.CS#004", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_VALIDATION_ERROR") { throw (new TaasDatabaseValidationException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "PAIDCHEQUERETURN.CS#002", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_SYSTEM_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "PAIDCHEQUERETURN.CS#003", mdlDbResponse.strSqlSysMessage))); } } // Saved MdlResPaidChequeReturn mdlResPaidChequeReturn = new MdlResPaidChequeReturn(); mdlResPaidChequeReturn.mdlPaidChequeReturn = mdlPaidChequeReturnReq.mdlPaidChequeReturn; if (mdlReqPaidChequeReturn.mdlPaidChequeReturn.binPaidChequeReturnId == 0) { mdlResPaidChequeReturn.mdlPaidChequeReturn.binPaidChequeReturnId = mdlDbResponse.binSqlPk ?? 0; mdlResPaidChequeReturn.mdlPaidChequeReturn.dtmCreated = mdlDbResponse.dtmCurrent; } else { mdlResPaidChequeReturn.mdlPaidChequeReturn.intSysActionId += 1; mdlResPaidChequeReturn.mdlPaidChequeReturn.dtmLastModified = mdlDbResponse.dtmCurrent; } mdlResPaidChequeReturn.sinStatus = 1; mdlResPaidChequeReturn.strStatusCode = "SUCCESS"; mdlResPaidChequeReturn.strMessage = "PAIDCHEQUERETURN has been Saved."; return mdlResPaidChequeReturn; } public MdlResBaseIntId FnDeletePaidChequeReturn(MdlReqBaseIntId mdlReqPaidChequeReturnBaseIntId) { // *** Input Validation DELETE Case[not duplicate model validation] // ??? FOREIGN KEY REFERENCES Ching in DB FUCTION(Delete) // ??? UPDATE Check already update another users in DB FUCTION(Update and Delete) // ??? Set Create or modified User in DB FUCTION(Insert, Update and Delete) // DB Actions NpgsqlParameter[] lstSqlParameter = { new NpgsqlParameter("binPaidChequeReturnId", NpgsqlDbType.Bigint) { Value = mdlReqPaidChequeReturnBaseIntId.mdlBaseIntId.binPkId, DataTypeName = "binPaidChequeReturnId"}, new NpgsqlParameter("intSysActionId", NpgsqlDbType.Integer) { Value = mdlReqPaidChequeReturnBaseIntId.mdlBaseIntId.binCustomValue, DataTypeName = "intSysActionId"}, new NpgsqlParameter("binDeletedUserId", NpgsqlDbType.Bigint) { Value = mdlReqPaidChequeReturnBaseIntId.linLoginUserId, DataTypeName = "binDeletedUserId"}, new NpgsqlParameter("jsnSqlRes", NpgsqlDbType.Json) { DataTypeName = "jsnSqlRes", Direction = ParameterDirection.Output } }; MdlDbResponse mdlDbResponse = this.insDbService.FnExcecuteOutSqlCommand("CALL sp_delete_paidChequeReturn(:binPaidChequeReturnId, :intSysActionId, :binDeletedUserId, NULL)", lstSqlParameter); // DB Error if (mdlDbResponse.sinSqlCustomStatus != 1) { if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "PAIDCHEQUERETURN.CS#004", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_VALIDATION_ERROR") { throw (new TaasDatabaseValidationException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "PAIDCHEQUERETURN.CS#005", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_SYSTEM_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "PAIDCHEQUERETURN.CS#006", mdlDbResponse.strSqlSysMessage))); } } // Deleted MdlResBaseIntId mdlResPaidChequeReturnIntId = new MdlResBaseIntId(); mdlResPaidChequeReturnIntId.mdlBaseIntId = mdlReqPaidChequeReturnBaseIntId.mdlBaseIntId; mdlResPaidChequeReturnIntId.sinStatus = 1; mdlResPaidChequeReturnIntId.strStatusCode = "SUCCESS"; mdlResPaidChequeReturnIntId.strMessage = "PAIDCHEQUERETURN has been deleted."; return mdlResPaidChequeReturnIntId; }