public async Task <Certificate> Select(CertificateOptions certificate)
{
var param = new DynamicParameters();
param.Add("@Number", certificate.Number);
param.Add("@Status", certificate.Status);
param.Add("@Scheme", certificate.Number);
param.Add("@SubScheme", certificate.SubScheme);
param.Add("@CustomerID", certificate.CustomerID);
param.Add("@PremiseID", certificate.PremiseID);
var mapper = new CertificateMapper();
return((await SqlMapper.QueryAsync(_unitOfWork.Connection,
"SelectCertificateFull",
new[]
{
typeof(Certificate),
typeof(Premise),
typeof(Customer)
},
obj =>
{
Certificate ce = obj[0] as Certificate;
Premise pr = obj[1] as Premise;
Customer cu = obj[2] as Customer;
return mapper.Map(ce, pr, cu);
},
param,
splitOn: "PremiseID,CustomerID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).FirstOrDefault());
}
public async Task <IList <Certificate> > CertDeliveryFilter(CertificateDeliveryFilter filter)
{
var param = new DynamicParameters();
var statusList = new DataTable();
statusList.Columns.Add("Val", typeof(int));
if (filter.Status?.Any() ?? false)
{
foreach (var status in filter.Status)
{
statusList.Rows.Add((int)status);
}
}
param.Add("@CustomerCode", filter.CustomerCode);
param.Add("@CustomerName", filter.CustomerName);
param.Add("@Premise", filter.Premise);
param.Add("@PostalCode", filter.Postal);
param.Add("@Status", statusList.AsTableValuedParameter("dbo.SmallIntType"));
param.Add("@IssuedOnFrom", filter.IssuedOnFrom);
param.Add("@IssuedOnTo", filter.IssuedOnTo);
param.Add("@SerialNo", filter.SerialNo);
var mapper = new CertificateMapper();
return((await SqlMapper.QueryAsync <Certificate>(_unitOfWork.Connection,
"GetCertificatesForDelivery",
new[]
{
typeof(Certificate),
typeof(Premise),
typeof(Premise),
typeof(Code)
},
obj =>
{
return mapper.Map(obj[0] as Certificate,
obj[1] as Premise,
obj[2] as Premise,
obj[3] as Code);
},
param,
splitOn: "ID,PremID,MailPremID,CodeID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).Distinct().ToList());
}
public async Task <IList <Certificate> > CertificateFilter(CertificateFilter filter)
{
var statusList = new DataTable();
statusList.Columns.Add("Val", typeof(long));
if (filter.IDs?.Any() ?? false)
{
foreach (var id in filter.IDs)
{
statusList.Rows.Add(id);
}
}
var param = new DynamicParameters();
param.Add("@IDs", statusList.AsTableValuedParameter("dbo.BigIntType"));
param.Add("@PremiseID", filter.PremiseID);
var mapper = new CertificateMapper();
return((await SqlMapper.QueryAsync <Certificate>(_unitOfWork.Connection,
"SelectCertificate",
new[]
{
typeof(Certificate),
typeof(Premise),
typeof(Premise),
typeof(Menu)
},
obj =>
{
return mapper.Map(obj[0] as Certificate,
obj[1] as Premise,
obj[2] as Premise,
obj[3] as Menu);
},
param,
splitOn: "ID,PremID,MailPremID,MenuID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).Distinct().ToList());
}