public Policy GetStatusByFIOBirthday(int regionId, string lastname, string firstname, string secondname, DateTime?birthday)
{
PolicySearchCriteria criteria = new PolicySearchCriteria()
{
RegionId = regionId,
Lastname = lastname,
Firstname = firstname,
Secondname = secondname,
Birthday = birthday
};
List <Policy> listPolicy = clientBll.PolicyFind(criteria);
if (listPolicy.Count > 0)
{
Policy policy = listPolicy.LastOrDefault();
return(policy);
}
else
{
string errorMessage = string.Format("Policy not found");
throw new FaultException <DataObjectNotFoundFault>(
new DataObjectNotFoundFault()
{
ErrorMessage = errorMessage
}, errorMessage);
}
}
public PolicyInfo GetStatusByNumber(int regionId, string series, string number)
{
PolicySearchCriteria criteria = new PolicySearchCriteria()
{
RegionId = regionId,
PolicySeries = series,
PolicyNumber = number
};
List <Policy> listPolicy = clientBll.PolicyFind(criteria);
if (listPolicy.Count > 0)
{
Policy policy = listPolicy.LastOrDefault();
return(new PolicyInfo(policy));
}
else
{
string errorMessage = string.Format("Policy not found {0} {1}", series, number);
throw new FaultException <DataObjectNotFoundFault>(
new DataObjectNotFoundFault()
{
ErrorMessage = errorMessage
}, errorMessage);
}
}
public async Task <IActionResult> GetPoliciesByCriteria([FromBody] PolicySearchCriteria criteria)
{
HttpContext.User.Claims.ToList();
var p = await _policyManager.GetPoliciesByCriteria(criteria);
return(Ok(p));
}
public async Task <List <PolicyDetails> > GetPoliciesByCriteria(PolicySearchCriteria criteria)
{
if (criteria.StatusList == null)
{
criteria.StatusList = new int[] { };
}
if (criteria.CreatedByList == null)
{
criteria.CreatedByList = new int[] { };
}
if (criteria.VehicleTypeList == null)
{
criteria.VehicleTypeList = new int[] { };
}
if (criteria.PolicyTypesList == null)
{
criteria.PolicyTypesList = new int[] { };
}
if (criteria.FuelTypesList == null)
{
criteria.FuelTypesList = new int[] { };
}
if (criteria.IssueModesList == null)
{
criteria.IssueModesList = new string[] { };
}
return(await _policyRepository.GetPoliciesByCriteria(criteria));
}
/// <summary>
/// Returns sorted list of ClientVisitInfo by specified criteria
/// </summary>
/// <param name="criteria">Specified criteria</param>
/// <param name="sortCriteria">Sort criteria</param>
/// <param name="pageRequest">Page size and number</param>
/// <returns>List of ClientVisitInfo</returns>
public List <Policy> PolicyFind(PolicySearchCriteria criteria)
{
if (criteria == null)
{
throw new ArgumentNullException("criteria can't be null");
}
return(ClientDao.Instance.PolicyFind(criteria));
}
public async Task <List <PolicyDetails> > GetPoliciesByCriteria(PolicySearchCriteria criteria)
{
List <PolicyDetails> details = new List <PolicyDetails>();
using (IDbConnection dbConnection = this.GetConnection())
{
try
{
dbConnection.Open();
var result = await dbConnection.QueryMultipleAsync("GetPoliciesByCriteria", new
{
CreatedByList = Converter.CreateDataTable(criteria.CreatedByList.AsEnumerable()),
StatusList = Converter.CreateDataTable(criteria.StatusList.AsEnumerable()),
VehicleTypesList = Converter.CreateDataTable(criteria.VehicleTypeList.AsEnumerable()),
criteria.UserId,
criteria.DirectReportId,
PolicyTypesList = Converter.CreateDataTable(criteria.PolicyTypesList.AsEnumerable()),
FuelTypesList = Converter.CreateDataTable(criteria.FuelTypesList.AsEnumerable()),
IssueModesList = Converter.CreateDataTable(criteria.IssueModesList.AsEnumerable()),
criteria.VehicleNumber,
criteria.PolicyNumber,
criteria.InsuredName,
criteria.InsuredMobile,
criteria.RED_Start,
criteria.RED_End,
criteria.RSD_Start,
criteria.RSD_End,
criteria.IssueDate_End,
criteria.IssueDate_Start,
criteria.PageNumber,
criteria.PageSize
}, commandType : CommandType.StoredProcedure);
var pList = await result.ReadAsync <PolicyDetails>();
details = pList.ToList();
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbConnection.Close();
}
return(details);
}
}
public List <Policy> PolicyFind(PolicySearchCriteria criteria)
{
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.AddInputParameter("@RegionId", SqlDbType.BigInt, criteria.RegionId);
parameters.AddInputParameter("@ClientVisitDate", SqlDbType.DateTime, criteria.ClientVisitDate);
parameters.AddInputParameter("@PolicyNumber", SqlDbType.NVarChar, criteria.PolicyNumber);
parameters.AddInputParameter("@PolicySeries", SqlDbType.NVarChar, criteria.PolicySeries);
parameters.AddInputParameter("@TemporaryPolicyNumber", SqlDbType.NVarChar, criteria.TemporaryPolicyNumber);
parameters.AddInputParameter("@UnifiedPolicyNumber", SqlDbType.NVarChar, criteria.UnifiedPolicyNumber);
parameters.AddInputParameter("@Firstname", SqlDbType.NVarChar, criteria.Firstname);
parameters.AddInputParameter("@Secondname", SqlDbType.NVarChar, criteria.Secondname);
parameters.AddInputParameter("@Lastname", SqlDbType.NVarChar, criteria.Lastname);
parameters.AddInputParameter("@Birthday", SqlDbType.Date, criteria.Birthday);
parameters.AddInputParameter("@Sex", SqlDbType.Char, criteria.Sex);
parameters.AddInputParameter("@Phone", SqlDbType.NVarChar, criteria.Phone);
return(Execute_GetList(PolicyMaterializer.Instance, "Service_Client_Find", parameters));
}
public Person GetFIOByPhone(int regionId, string phone)
{
PolicySearchCriteria criteria = new PolicySearchCriteria()
{
RegionId = regionId,
Phone = phone
};
List <Policy> listPolicy = clientBll.PolicyFind(criteria);
if (listPolicy.Count > 0)
{
Policy policy = listPolicy.LastOrDefault();
return(new Person(policy));
}
else
{
string errorMessage = string.Format("Policy not found");
throw new FaultException <DataObjectNotFoundFault>(
new DataObjectNotFoundFault()
{
ErrorMessage = errorMessage
}, errorMessage);
}
}
public async Task <IActionResult> ExportPoliciesByCriteria([FromBody] PolicySearchCriteria criteria)
{
await Task.Yield();
var list = await _policyManager.GetPoliciesByCriteria(criteria);
var stream = new MemoryStream();
list.ForEach(policy =>
{
policy.documentLink = HttpContext.Request.Scheme + "://" + HttpContext.Request.Host + "/api/policy/policydocument?id=" + policy.Id;
});
MemberInfo[] membersToInclude = typeof(PolicyDetails)
.GetProperties(BindingFlags.Instance | BindingFlags.Public)
.Where(p => !Attribute.IsDefined(p, typeof(EpplusIgnore)))
.ToArray();
using (var package = new ExcelPackage(stream))
{
var workSheet = package.Workbook.Worksheets.Add("Policies");
workSheet.Cells.LoadFromCollection(list, true, OfficeOpenXml.Table.TableStyles.None, BindingFlags.Default, membersToInclude);
var start = workSheet.Dimension.Start;
var end = workSheet.Dimension.End;
for (int row = start.Row + 1; row <= end.Row; row++)
{
var excelRow = workSheet.Row(row);
workSheet.Cells["AG" + row].Formula = "HYPERLINK(\"" + workSheet.Cells["AG" + row].Value + "\",\"" + "Download Policy Doc" + "\")";
workSheet.Cells["AG" + row].Calculate();
workSheet.Cells["D" + row].Style.Numberformat.Format = "dd/MM/yyyy";
workSheet.Cells["D" + row].Calculate();
workSheet.Cells["D" + row].AutoFitColumns();
workSheet.Cells["E" + row].Style.Numberformat.Format = "dd/MM/yyyy";
workSheet.Cells["E" + row].Calculate();
workSheet.Cells["F" + row].AutoFitColumns();
workSheet.Cells["F" + row].Style.Numberformat.Format = "dd/MM/yyyy";
workSheet.Cells["F" + row].Calculate();
workSheet.Cells["F" + row].AutoFitColumns();
}
int totalCols = workSheet.Dimension.End.Column;
var headerCells = workSheet.Cells[1, 1, 1, totalCols];
headerCells.AutoFitColumns();
var headerFont = headerCells.Style.Font;
headerFont.Bold = true;
headerFont.Italic = true;
headerCells.Style.Fill.PatternType = ExcelFillStyle.Solid;
headerCells.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
headerCells.Style.Font.Color.SetColor(Color.White);
package.Save();
}
stream.Position = 0;
string excelName = $"Policy-List-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName));
}
