public async Task <Certificate360> GetCertificateByCertID(long ID)
{
await Task.CompletedTask;
var param = new DynamicParameters();
param.Add("@ID", ID);
var mapper = new Certificate360Mapper();
return((await SqlMapper.QueryAsync(
_unitOfWork.Connection,
"GetCertificate360ByID",
new[]
{
typeof(Certificate360),
typeof(Premise),
typeof(HalalTeam)
},
obj =>
{
var cert = obj[0] as Certificate360;
var certPremise = obj[1] as Premise;
var certHalalTeam = obj[2] as HalalTeam;
return mapper.Map(cert, certPremise, certHalalTeam);
},
param,
splitOn: "ID,PremiseID,HalalTeamID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).FirstOrDefault());
}
public async Task <IList <Certificate360> > QueryCustomer(CustomerOptions filter)
{
var statusTable = new DataTable();
statusTable.Columns.Add("Val", typeof(int));
if (filter.Status?.Any() ?? false)
{
foreach (var status in filter.Status)
{
statusTable.Rows.Add(status);
}
}
var param = new DynamicParameters();
param.Add("@ID", filter.ID);
param.Add("@Code", StringUtils.NullIfEmptyOrNull(filter.Code));
param.Add("@GroupCode", StringUtils.NullIfEmptyOrNull(filter.GroupCode));
param.Add("@Name", StringUtils.NullIfEmptyOrNull(filter.Name));
param.Add("@CertificateNo", StringUtils.NullIfEmptyOrNull(filter.CertificateNo));
param.Add("@Premise", StringUtils.NullIfEmptyOrNull(filter.Premise));
param.Add("@PremiseID", filter.PremiseID);
param.Add("@Status", statusTable.AsTableValuedParameter("dbo.SmallIntType"));
var mapper = new Certificate360Mapper();
return((await SqlMapper.QueryAsync(
_unitOfWork.Connection,
"SelectCustomer",
new[]
{
typeof(Certificate360),
typeof(Customer),
typeof(Code),
typeof(Code),
typeof(Premise),
typeof(Officer)
},
obj =>
{
var cert = obj[0] as Certificate360;
var certCustomer = obj[1] as Customer;
var certCustCode = obj[2] as Code;
var certCustGroupCode = obj[3] as Code;
var certPremise = obj[4] as Premise;
var certCustOfficer = obj[5] as Officer;
return mapper.Map(cert,
certCustomer,
certCustCode,
certCustGroupCode,
certCustOfficer,
certPremise);
},
param,
splitOn: "ID, CustID, CustCodeID, CustCodeGroupID, PremID, OfficerID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).ToList());
}
public async Task <IList <Certificate360> > GetCertificate360WithIngredient
(Certificate360IngredientFilter filter)
{
var param = new DynamicParameters();
param.Add("@Name", filter.Name);
param.Add("@Brand", filter.BrandName);
param.Add("@SupplierName", filter.SupplierName);
param.Add("@CertifyingBody", filter.CertifyingBodyName);
var mapper = new Certificate360Mapper();
return((await SqlMapper.QueryAsync(
_unitOfWork.Connection,
"GetCertificate360WithIngredient",
new[]
{
typeof(Certificate360),
typeof(Customer),
typeof(Code),
typeof(Code),
typeof(Officer),
typeof(Premise)
},
obj =>
{
var cert = obj[0] as Certificate360;
var certCustomer = obj[1] as Customer;
var certCustCode = obj[2] as Code;
var certCustGroupCode = obj[3] as Code;
var certCustOfficer = obj[4] as Officer;
var certPremise = obj[5] as Premise;
return mapper.Map(cert,
certCustomer,
certCustCode,
certCustGroupCode,
certCustOfficer,
certPremise);
},
param,
splitOn: "ID, CustID, CustCodeID, CustCodeGroupID, OfficerID, PremID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).ToList());
}
public async Task <Certificate360> GetCertificateByCertNo(string CertificateNo)
{
await Task.CompletedTask;
var param = new DynamicParameters();
param.Add("@CertificateNo", CertificateNo);
var mapper = new Certificate360Mapper();
return((await SqlMapper.QueryAsync(
_unitOfWork.Connection,
"GetCertificate360Detail",
new[]
{
typeof(Certificate360),
typeof(Certificate360History),
typeof(Menu),
typeof(Ingredient),
typeof(Premise),
typeof(Customer),
typeof(HalalTeam)
},
obj =>
{
var cert = obj[0] as Certificate360;
var certHistory = obj[1] as Certificate360History;
var certMenu = obj[2] as Menu;
var cetIng = obj[3] as Ingredient;
var certPremise = obj[4] as Premise;
var certCustomer = obj[5] as Customer;
var certHalalTeam = obj[6] as HalalTeam;
return mapper.Map(cert, certHistory, certMenu, cetIng,
certPremise, certCustomer, certHalalTeam);
},
param,
splitOn: "ID,CertificateHistoryID,MenuID," +
"IngredientID,PremiseID,CustomerID,HalalTeamID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).FirstOrDefault());
}
public async Task <IList <Certificate360> > Certificate360Filter(Certificate360Filter filter)
{
var tPremiseIDs = new DataTable();
tPremiseIDs.Columns.Add("Val", typeof(long));
if (filter.PremiseIDs?.Any() ?? false)
{
foreach (var id in filter.PremiseIDs)
{
tPremiseIDs.Rows.Add(id);
}
}
var param = new DynamicParameters();
param.Add("@PremiseIDs", tPremiseIDs.AsTableValuedParameter("dbo.BigIntType"));
var mapper = new Certificate360Mapper();
return((await SqlMapper.QueryAsync <Certificate360>(_unitOfWork.Connection,
"SelectCertificate360",
new[]
{
typeof(Certificate360),
typeof(Premise),
typeof(Menu)
},
obj =>
{
return mapper.Map(obj[0] as Certificate360,
obj[1] as Premise,
obj[2] as Menu);
},
param,
splitOn: "ID,PremID,MenuID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).Distinct().ToList());
}
public async Task <Certificate360> GetCertificate360ByNo(string certificateNo)
{
var param = new DynamicParameters();
param.Add("@Number", certificateNo);
var mapper = new Certificate360Mapper();
return((await SqlMapper.QueryAsync(_unitOfWork.Connection,
"GetCertificate360ByNo",
new[]
{
typeof(Certificate360)
},
obj =>
{
var cert = obj[0] as Certificate360;
return mapper.Map(cert);
},
param,
splitOn: "ID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).FirstOrDefault());
}
