/// <summary>
/// Get the vehicle model's based on the make.
/// e.g for make (BMW) fetch all the models that make 318i,320,535I..
/// </summary>
/// <param name="vehicleMake">vehicle make</param>
/// <returns>vehicle model's</returns>
public VehicleModelResponse GetVehicleModel(string vehicleMake)
{
try
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@Make", vehicleMake)
};
DataTable dt = BKICSQL.edt(StoredProcedures.BKICDropDowns.GetVehicleModel, param);
return(new VehicleModelResponse
{
IsTransactionDone = true,
VehicleModeldt = dt
});
}
catch (Exception ex)
{
return(new VehicleModelResponse
{
IsTransactionDone = false,
TransactionErrorMessage = ex.Message
});
}
}
/// <summary>
/// Travel endorsement operation - authorize or delete.
/// </summary>
/// <param name="request">Endorsement operation request.</param>
/// <returns></returns>
public TravelEndorsementOperationResponse EndorsementOperation(TravelEndorsementOperation request)
{
try
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@Type", request.Type),
new SqlParameter("@TravelEndorsementID", request.TravelEndorsementID),
new SqlParameter("@TravelID", request.TravelID),
new SqlParameter("@Agency", request.Agency),
new SqlParameter("@AgentCode", request.AgentCode),
new SqlParameter("@UpdatedBy", request.UpdatedBy),
};
DataTable dt = BKICSQL.edt(TravelEndorsementSP.TravelEndorsementOperation, paras);
if (request.Type == Constants.EndorsementOpeationType.Authorize)
{
try
{
new Task(() =>
{
OracleDBIntegration.DBObjects.TransactionWrapper oracleResult
= _oracleTravelEndorsement.MoveIntegrationToOracle(request.TravelEndorsementID);
}).Start();
}
catch (AggregateException ex)
{
foreach (Exception inner in ex.InnerExceptions)
{
_mail.SendMailLogError(ex.Message, "", "Travelendorsement", "", true);
}
}
}
return(new TravelEndorsementOperationResponse
{
IsTransactionDone = true
});
}
catch (Exception ex)
{
return(new TravelEndorsementOperationResponse()
{
IsTransactionDone = false,
TransactionErrorMessage = ex.Message
});
}
}
private void InsertCategory(BO.HomeInsurancePolicy policy, List <PolicyCategory> policyCategories)
{
if (policyCategories != null && policyCategories.Count > 0)
{
foreach (var dr in policyCategories)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@DocumentID", dr.DocumentID),
new SqlParameter("@InsuredCode", policy.InsuredCode),
new SqlParameter("@LinkID", dr.LinkID),
new SqlParameter("@DocumentNo", dr.DocumentNo),
new SqlParameter("@EndorsementNo", dr.EndorsementNo ?? string.Empty),
new SqlParameter("@EndorsementCount", dr.EndorsementCount),
new SqlParameter("@AgentCode", dr.AgentCode),
new SqlParameter("@LineNo", dr.LineNo),
new SqlParameter("@Category", dr.Category),
new SqlParameter("@Code", dr.Code),
new SqlParameter("@ValueType", dr.ValueType),
new SqlParameter("@Value", dr.Value),
new SqlParameter("@PremiumBeforeDiscount", dr.PremiumBeforeDiscount),
new SqlParameter("@PremiumAfterDiscount", dr.PremiumAfterDiscount),
new SqlParameter("@CommissionBeforeDiscount", dr.CommissionBeforeDiscount),
new SqlParameter("@CommissionAfterDiscount", dr.CommissionAfterDiscount),
new SqlParameter("@TaxOnPremiumBeforeDiscount", dr.TaxOnPremiumBeforeDiscount),
new SqlParameter("@TaxOnPremiumAfterDiscount", dr.TaxOnPremiumAfterDiscount),
new SqlParameter("@TaxOnCommissionBeforeDiscount", dr.TaxOnCommissionBeforeDiscount),
new SqlParameter("@TaxOnCommissionAfterDiscount", dr.TaxOnCommissionAfterDiscount),
new SqlParameter("@IsDeductable", dr.IsDeductable),
new SqlParameter("@RenewalCount", dr.RenewalCount),
new SqlParameter("@DomesticID", DBNull.Value),
new SqlParameter("@TravelID", DBNull.Value),
new SqlParameter("@HomeID", dr.HomeID),
new SqlParameter("@MotorID", DBNull.Value),
new SqlParameter("@MotorEndorsementID", DBNull.Value),
new SqlParameter("@TravelEndorsementID", DBNull.Value),
new SqlParameter("@HomeEndorsementID", DBNull.Value),
};
BKICSQL.edt(MotorInsuranceSP.PolicyCategoryInsert, paras);
}
}
}
/// <summary>
/// Get domestic policies by agency.
/// </summary>
/// <param name="req">domestic policy request.</param>
/// <returns>list of domestic policies by agency.</returns>
public AgencyDomesticPolicyResponse GetDomesticAgencyPolicy(AgencyDomesticRequest req)
{
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Agency", req.Agency ?? string.Empty),
new SqlParameter("@AgentCode", req.AgentCode ?? string.Empty),
new SqlParameter("@AgentBranch", req.AgentBranch ?? string.Empty),
new SqlParameter("@IncludeHIR", req.IncludeHIR),
new SqlParameter("@DocumentNo", req.DocumentNo ?? string.Empty)
};
DataTable dt = BKICSQL.edt(StoredProcedures.DomesticHelpInsuranceSP.GetDomesticAgencyPolicy, para);
List <AgencyDomesticPolicy> domesticHelpPolicies = new List <AgencyDomesticPolicy>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var res = new AgencyDomesticPolicy();
res.DomesticId = Convert.ToInt32(dr["DOMESTICID"]);
res.DocumentNo = Convert.ToString(dr["DOCUMENTNO"]);
res.InsuredCode = Convert.ToString(dr["INSUREDCODE"]);
domesticHelpPolicies.Add(res);
}
}
return(new AgencyDomesticPolicyResponse
{
DomesticAgencyPolicies = domesticHelpPolicies,
IsTransactionDone = true
});
}
catch (Exception ex)
{
return(new AgencyDomesticPolicyResponse
{
TransactionErrorMessage = ex.Message,
IsTransactionDone = false
});
}
}
/// <summary>
/// Get main report based on insurance type(Motor or Travel or Home)
/// </summary>
/// <param name="reportReq">Report request.</param>
/// <returns>Report details.</returns>
public MainReportResponse GetMainReport(AdminFetchReportRequest reportReq)
{
try
{
if (reportReq.InsuranceType == Constants.Insurance.Motor)
{
if (reportReq.ReportType == Constants.ReportType.MotorMainReport)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@Agency", reportReq.Agency),
new SqlParameter("@AgentCode", reportReq.AgentCode),
new SqlParameter("@DateFrom", reportReq.DateFrom),
new SqlParameter("@DateTo", reportReq.DateTo)
};
DataTable dt = BKICSQL.edt(StoredProcedures.ReportSP.GetMotorMainReport, paras);
List <MainReportDetails> response = new List <MainReportDetails>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var res = new MainReportDetails();
res.BranchCode = dr.IsNull("BranchCode") ? string.Empty : Convert.ToString(dr["BranchCode"]);
res.BranchName = dr.IsNull("BranchName") ? string.Empty : Convert.ToString(dr["BranchName"]);
res.InsuredName = dr.IsNull("InsuredName") ? string.Empty : Convert.ToString(dr["InsuredName"]);
res.AuthorizedUser = dr.IsNull("AuthorizedBy") ? string.Empty : Convert.ToString(dr["AuthorizedBy"]);
res.AuthorizedDate = dr.IsNull("AuthorizedDate") ? string.Empty : Convert.ToDateTime(dr["AuthorizedDate"]).Date.CovertToLocalFormat();
res.HandledBy = dr.IsNull("HandledBy") ? string.Empty : Convert.ToString(dr["HandledBy"]);
res.CommenceDate = dr.IsNull("CommenceDate") ? string.Empty : Convert.ToDateTime(dr["CommenceDate"]).Date.CovertToLocalFormat();
res.ExpiryDate = dr.IsNull("ExpiryDate") ? string.Empty : Convert.ToDateTime(dr["ExpiryDate"]).Date.CovertToLocalFormat();
res.PaymentMethod = dr.IsNull("PaymentMethod") ? string.Empty : Convert.ToString(dr["PaymentMethod"]);
res.PolicyNo = dr.IsNull("PolicyNo") ? string.Empty : Convert.ToString(dr["PolicyNo"]);
res.EndorsementNo = dr.IsNull("EndorsementNo") ? string.Empty : Convert.ToString(dr["EndorsementNo"]);
res.SubClass = dr.IsNull("SubClass") ? string.Empty : Convert.ToString(dr["SubClass"]);
res.Commission = dr.IsNull("Commission") ? decimal.Zero : Convert.ToDecimal(dr["Commission"]);
res.RefundCommision = dr.IsNull("RefundCommission") ? decimal.Zero : Convert.ToDecimal(dr["RefundCommission"]);
res.Discount = dr.IsNull("Discount") ? decimal.Zero : Convert.ToDecimal(dr["Discount"]);
res.PremiumLessCredit = dr.IsNull("PremiumLessCredit") ? decimal.Zero : Convert.ToDecimal(dr["PremiumLessCredit"]);
res.PremiumReference = dr.IsNull("PremiumReference") ? string.Empty : Convert.ToString(dr["PremiumReference"]);
res.CommisionReference = dr.IsNull("CommissionReference") ? string.Empty : Convert.ToString(dr["CommissionReference"]);
res.BatchDate = dr.IsNull("BatchDate") ? string.Empty : Convert.ToString(dr["BatchDate"]);
res.CPR = dr.IsNull("CPR") ? string.Empty : Convert.ToString(dr["CPR"]);
res.NewPremium = dr.IsNull("NewPremium") ? decimal.Zero : Convert.ToDecimal(dr["NewPremium"]);
res.RenewalPremium = dr.IsNull("RenewalPremium") ? decimal.Zero : Convert.ToDecimal(dr["RenewalPremium"]);
res.AdditionalPremium = dr.IsNull("AdditionalPremium") ? decimal.Zero : Convert.ToDecimal(dr["AdditionalPremium"]);
res.RefundPremium = dr.IsNull("RefundPremium") ? decimal.Zero : Convert.ToDecimal(dr["RefundPremium"]);
res.Vat = dr.IsNull("Vat") ? decimal.Zero : Convert.ToDecimal(dr["vat"]);
response.Add(res);
}
}
return(new MainReportResponse
{
IsTransactionDone = true,
MainReportDetails = response,
});
}
}
else if (reportReq.InsuranceType == Constants.Insurance.Travel &&
reportReq.ReportType == Constants.ReportType.TravelMainReport)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@Agency", reportReq.Agency),
new SqlParameter("@AgentCode", reportReq.AgentCode),
new SqlParameter("@DateFrom", reportReq.DateFrom),
new SqlParameter("@DateTo", reportReq.DateTo)
};
DataTable dt = BKICSQL.edt(StoredProcedures.ReportSP.GetTravelMainReport, paras);
List <MainReportDetails> response = new List <MainReportDetails>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var res = new MainReportDetails();
res.BranchCode = dr.IsNull("BranchCode") ? string.Empty : Convert.ToString(dr["BranchCode"]);
res.BranchName = dr.IsNull("BranchName") ? string.Empty : Convert.ToString(dr["BranchName"]);
res.InsuredName = dr.IsNull("InsuredName") ? string.Empty : Convert.ToString(dr["InsuredName"]);
res.PolicyNo = dr.IsNull("POLICYNO") ? string.Empty : Convert.ToString(dr["POLICYNO"]);
res.EndorsementNo = dr.IsNull("ENDORSEMENTNO") ? string.Empty : Convert.ToString(dr["ENDORSEMENTNO"]);
res.SubClass = dr.IsNull("SUBCLASS") ? string.Empty : Convert.ToString(dr["SUBCLASS"]);
res.AuthorizedUser = dr.IsNull("AuthorizedBy") ? string.Empty : Convert.ToString(dr["AuthorizedBy"]);
res.AuthorizedDate = dr.IsNull("AuthorizedDate") ? string.Empty : Convert.ToDateTime(dr["AuthorizedDate"]).Date.CovertToLocalFormat();
res.HandledBy = dr.IsNull("HandledBy") ? string.Empty : Convert.ToString(dr["HandledBy"]);
res.CommenceDate = dr.IsNull("CommenceDate") ? string.Empty : Convert.ToDateTime(dr["CommenceDate"]).Date.CovertToLocalFormat();
res.ExpiryDate = dr.IsNull("ExpiryDate") ? string.Empty : Convert.ToDateTime(dr["ExpiryDate"]).Date.CovertToLocalFormat();
res.PaymentMethod = dr.IsNull("PaymentMethod") ? string.Empty : Convert.ToString(dr["PaymentMethod"]);
res.Commission = dr.IsNull("Commission") ? decimal.Zero : Convert.ToDecimal(dr["Commission"]);
res.Discount = dr.IsNull("Discount") ? decimal.Zero : Convert.ToDecimal(dr["Discount"]);
res.PremiumLessCredit = dr.IsNull("PremiumLessCredit") ? decimal.Zero : Convert.ToDecimal(dr["PremiumLessCredit"]);
res.PremiumReference = dr.IsNull("PremiumReference") ? string.Empty : Convert.ToString(dr["PremiumReference"]);
res.CommisionReference = dr.IsNull("CommissionReference") ? string.Empty : Convert.ToString(dr["CommissionReference"]);
res.BatchDate = dr.IsNull("BatchDate") ? string.Empty : Convert.ToString(dr["BatchDate"]);
res.CPR = dr.IsNull("CPR") ? string.Empty : Convert.ToString(dr["CPR"]);
res.NewPremium = dr.IsNull("NewPremium") ? decimal.Zero : Convert.ToDecimal(dr["NewPremium"]);
res.RenewalPremium = dr.IsNull("RenewalPremium") ? decimal.Zero : Convert.ToDecimal(dr["RenewalPremium"]);
res.AdditionalPremium = dr.IsNull("AdditionalPremium") ? decimal.Zero : Convert.ToDecimal(dr["AdditionalPremium"]);
res.RefundPremium = dr.IsNull("RefundPremium") ? decimal.Zero : Convert.ToDecimal(dr["RefundPremium"]);
response.Add(res);
}
}
return(new MainReportResponse
{
IsTransactionDone = true,
MainReportDetails = response,
});
}
else if (reportReq.InsuranceType == Constants.Insurance.Home &&
reportReq.ReportType == Constants.ReportType.HomeMainReport)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@Agency", reportReq.Agency),
new SqlParameter("@AgentCode", reportReq.AgentCode),
new SqlParameter("@DateFrom", reportReq.DateFrom),
new SqlParameter("@DateTo", reportReq.DateTo)
};
DataTable dt = BKICSQL.edt(StoredProcedures.ReportSP.GetHomeMainReport, paras);
List <MainReportDetails> response = new List <MainReportDetails>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var res = new MainReportDetails();
res.BranchCode = dr.IsNull("BranchCode") ? string.Empty : Convert.ToString(dr["BranchCode"]);
res.BranchName = dr.IsNull("BranchName") ? string.Empty : Convert.ToString(dr["BranchName"]);
res.InsuredName = dr.IsNull("InsuredName") ? string.Empty : Convert.ToString(dr["InsuredName"]);
res.PolicyNo = dr.IsNull("POLICYNO") ? string.Empty : Convert.ToString(dr["POLICYNO"]);
res.EndorsementNo = dr.IsNull("ENDORSEMENTNO") ? string.Empty : Convert.ToString(dr["ENDORSEMENTNO"]);
res.SubClass = dr.IsNull("SUBCLASS") ? string.Empty : Convert.ToString(dr["SUBCLASS"]);
res.AuthorizedUser = dr.IsNull("AuthorizedBy") ? string.Empty : Convert.ToString(dr["AuthorizedBy"]);
res.AuthorizedDate = dr.IsNull("AuthorizedDate") ? string.Empty : Convert.ToDateTime(dr["AuthorizedDate"]).Date.CovertToLocalFormat();
res.HandledBy = dr.IsNull("HandledBy") ? string.Empty : Convert.ToString(dr["HandledBy"]);
res.CommenceDate = dr.IsNull("CommenceDate") ? string.Empty : Convert.ToDateTime(dr["CommenceDate"]).Date.CovertToLocalFormat();
res.ExpiryDate = dr.IsNull("ExpiryDate") ? string.Empty : Convert.ToDateTime(dr["ExpiryDate"]).Date.CovertToLocalFormat();
res.PaymentMethod = dr.IsNull("PaymentMethod") ? string.Empty : Convert.ToString(dr["PaymentMethod"]);
res.Commission = dr.IsNull("Commission") ? decimal.Zero : Convert.ToDecimal(dr["Commission"]);
res.RefundCommision = dr.IsNull("RefundCommission") ? decimal.Zero : Convert.ToDecimal(dr["RefundCommission"]);
res.Discount = dr.IsNull("Discount") ? decimal.Zero : Convert.ToDecimal(dr["Discount"]);
res.PremiumLessCredit = dr.IsNull("PremiumLessCredit") ? decimal.Zero : Convert.ToDecimal(dr["PremiumLessCredit"]);
res.PremiumReference = dr.IsNull("PremiumReference") ? string.Empty : Convert.ToString(dr["PremiumReference"]);
res.CommisionReference = dr.IsNull("CommissionReference") ? string.Empty : Convert.ToString(dr["CommissionReference"]);
res.BatchDate = dr.IsNull("BatchDate") ? string.Empty : Convert.ToString(dr["BatchDate"]);
res.CPR = dr.IsNull("CPR") ? string.Empty : Convert.ToString(dr["CPR"]);
res.NewPremium = dr.IsNull("NewPremium") ? decimal.Zero : Convert.ToDecimal(dr["NewPremium"]);
res.RenewalPremium = dr.IsNull("RenewalPremium") ? decimal.Zero : Convert.ToDecimal(dr["RenewalPremium"]);
res.AdditionalPremium = dr.IsNull("AdditionalPremium") ? decimal.Zero : Convert.ToDecimal(dr["AdditionalPremium"]);
res.RefundPremium = dr.IsNull("RefundPremium") ? decimal.Zero : Convert.ToDecimal(dr["RefundPremium"]);
res.Vat = dr.IsNull("Vat") ? decimal.Zero : Convert.ToDecimal(dr["Vat"]);
response.Add(res);
}
}
return(new MainReportResponse
{
IsTransactionDone = true,
MainReportDetails = response,
});
}
return(null);
}
catch (Exception ex)
{
return(new MainReportResponse
{
IsTransactionDone = false,
TransactionErrorMessage = ex.Message,
});
}
}
/// <summary>
/// Get home report based on type(Report by branch or user)
/// </summary>
/// <param name="reportReq">Report request.</param>
/// <returns>Report details.</returns>
public TravelHomeReportResponse GetHomeReport(AdminFetchReportRequest reportReq)
{
try
{
if (reportReq.ReportType == Constants.ReportType.HomeUserReport)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@Agency", reportReq.Agency),
new SqlParameter("@AgentCode", reportReq.AgentCode),
new SqlParameter("@AuthorizedUserID", reportReq.AuthorizedUserID),
new SqlParameter("@DateFrom", reportReq.DateFrom),
new SqlParameter("@DateTo", reportReq.DateTo)
};
DataTable dt = BKICSQL.edt(StoredProcedures.ReportSP.GetHomeUserReport, paras);
List <TravelHomeReportDetails> response = new List <TravelHomeReportDetails>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var res = new TravelHomeReportDetails();
res.AuthorizedCode = dr.IsNull("AuthorizedCode") ? string.Empty : Convert.ToString(dr["AuthorizedCode"]);
res.HandledBy = dr.IsNull("HandledBy") ? string.Empty : Convert.ToString(dr["HandledBy"]);
res.PolicyNo = dr.IsNull("Policy No") ? string.Empty : Convert.ToString(dr["Policy No"]);
res.EndorsementNo = dr.IsNull("EndorsementNo") ? string.Empty : Convert.ToString(dr["EndorsementNo"]);
res.SubClass = dr.IsNull("SubClass") ? string.Empty : Convert.ToString(dr["SubClass"]);
res.SumInsured = dr.IsNull("SumInsured") ? decimal.Zero : Convert.ToDecimal(dr["SumInsured"]);
res.NewPremium = dr.IsNull("NewPremium") ? decimal.Zero : Convert.ToDecimal(dr["NewPremium"]);
res.RenewalPremium = dr.IsNull("RenewalPremium") ? decimal.Zero : Convert.ToDecimal(dr["RenewalPremium"]);
res.AdditionalPremium = dr.IsNull("AdditionalPremium") ? decimal.Zero : Convert.ToDecimal(dr["AdditionalPremium"]);
res.RefundPremium = dr.IsNull("RefundPremium") ? decimal.Zero : Convert.ToDecimal(dr["RefundPremium"]);
res.Vat = dr.IsNull("Vat") ? decimal.Zero : Convert.ToDecimal(dr["vat"]);
response.Add(res);
}
}
return(new TravelHomeReportResponse
{
IsTransactionDone = true,
TravelHomeReportDetails = response
});
}
else if (reportReq.ReportType == Constants.ReportType.HomeBranchReport)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@Agency", reportReq.Agency),
new SqlParameter("@AgentCode", reportReq.AgentCode),
new SqlParameter("@BranchCode", reportReq.BranchCode),
new SqlParameter("@DateFrom", reportReq.DateFrom),
new SqlParameter("@DateTo", reportReq.DateTo)
};
DataTable dt = BKICSQL.edt(StoredProcedures.ReportSP.GetHomeBranchReport, paras);
List <TravelHomeReportDetails> response = new List <TravelHomeReportDetails>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var res = new TravelHomeReportDetails();
res.BranchCode = dr.IsNull("BranchCode") ? string.Empty : Convert.ToString(dr["BranchCode"]);
res.PolicyNo = dr.IsNull("Policy No") ? string.Empty : Convert.ToString(dr["Policy No"]);
res.EndorsementNo = dr.IsNull("EndorsementNo") ? string.Empty : Convert.ToString(dr["EndorsementNo"]);
res.SubClass = dr.IsNull("SubClass") ? string.Empty : Convert.ToString(dr["SubClass"]);
res.SumInsured = dr.IsNull("SumInsured") ? decimal.Zero : Convert.ToDecimal(dr["SumInsured"]);
res.NewPremium = dr.IsNull("NewPremium") ? decimal.Zero : Convert.ToDecimal(dr["NewPremium"]);
res.RenewalPremium = dr.IsNull("RenewalPremium") ? decimal.Zero : Convert.ToDecimal(dr["RenewalPremium"]);
res.AdditionalPremium = dr.IsNull("AdditionalPremium") ? decimal.Zero : Convert.ToDecimal(dr["AdditionalPremium"]);
res.RefundPremium = dr.IsNull("RefundPremium") ? decimal.Zero : Convert.ToDecimal(dr["RefundPremium"]);
res.Vat = dr.IsNull("Vat") ? decimal.Zero : Convert.ToDecimal(dr["vat"]);
response.Add(res);
}
}
return(new TravelHomeReportResponse
{
IsTransactionDone = true,
TravelHomeReportDetails = response
});
}
}
catch (Exception ex)
{
return(new TravelHomeReportResponse
{
IsTransactionDone = false,
TransactionErrorMessage = ex.Message,
});
}
return(null);
}