public BaseOutput GetProviderReportsByYearAndMonths(Search search, out ProviderReportDTO itemOut)
{
ProviderRepository providerRepository = new ProviderRepository();
BaseOutput baseOutput;
try
{
ProviderRepository repository = new ProviderRepository();
var providerReportDTO = repository.SW_GetProviderReportsByYearAndMonths(search);
itemOut = providerReportDTO;
return(baseOutput = new BaseOutput(true, BOResultTypes.Success.GetHashCode(), BOBaseOutputResponse.SuccessResponse, ""));
}
catch (Exception ex)
{
itemOut = null;
return(baseOutput = new BaseOutput(false, BOResultTypes.Danger.GetHashCode(), BOBaseOutputResponse.DangerResponse, ex.Message));
}
}
public IHttpActionResult GetProviderReportsByYearAndMonths(Int64 providerID, int year, string months = "")
{
ProviderBusinessOperation businessOperation = new ProviderBusinessOperation();
Search search = new Search
{
ProviderID = providerID,
Year = year,
Months = months,
};
ProviderReportDTO itemOut = null;
BaseOutput baseOutput = businessOperation.GetProviderReportsByYearAndMonths(search, out itemOut);
if (baseOutput.ResultCode == 1)
{
return(Ok(itemOut));
}
else
{
return(Content(HttpStatusCode.BadRequest, baseOutput));
}
}
public IHttpActionResult GetProviderReportsByDatePeriod(Int64 providerID, DateTime fromDate, DateTime toDate)
{
ProviderBusinessOperation businessOperation = new ProviderBusinessOperation();
Search search = new Search
{
ProviderID = providerID,
FromtDate = fromDate,
ToDate = toDate,
};
ProviderReportDTO itemOut = null;
BaseOutput baseOutput = businessOperation.GetProviderReportsByDatePeriod(search, out itemOut);
if (baseOutput.ResultCode == 1)
{
return(Ok(itemOut));
}
else
{
return(Content(HttpStatusCode.BadRequest, baseOutput));
}
}
public ProviderReportDTO SW_GetProviderReportsByYearAndMonths(Search search)
{
ProviderReportDTO providerReportDTO = null;;
string head = "";
head = @" pr.ID,pr.Name,
(select Count(*)from tbl_Proposal prop where pr.ID=prop.ProviderID and prop.Status=1) as YerlesdirilmisXidmetSay,
(select Count(*) from tbl_Proposal prop, tbl_ProposalUserState pus
where prop.Status=1 and pus.Status=1 and
pr.ID=prop.ProviderID and prop.ID=pus.ProposalID and pus.UserStateType=19 ) as MuracietEdilmisXidmetSay,
(select Count(*) from tbl_Proposal prop, tbl_ProposalUserState pus
where prop.Status=1 and pus.Status=1 and
pr.ID=prop.ProviderID and prop.ID=pus.ProposalID and pus.UserStateType=20 and pus.ProviderStateType=15 ) as QebulEdilmisXidmetSay,
(select Count(*) from tbl_Proposal prop, tbl_ProposalUserState pus
where prop.Status=1 and pus.Status=1 and
pr.ID=prop.ProviderID and prop.ID=pus.ProposalID and pus.ProviderStateType=16 ) as RedEdilmisXidmetSay,
(select Count(*) from tbl_Proposal prop, tbl_ProposalUserState pus
where prop.Status=1 and pus.Status=1 and
pr.ID=prop.ProviderID and prop.ID=pus.ProposalID and pus.ProviderStateType=17 ) as GozlemedekiXidmetSay";
StringBuilder allQuery = new StringBuilder();
if (string.IsNullOrEmpty(search.Months))
{
var query = @"SELECT " + head + @" from tbl_Provider pr, tbl_proposal p
where pr.Status=1 and p.Status=1 and pr.ID=p.ProviderID
and pr.ID=" + search.ProviderID
+ " and YEAR(p.InsertDate)=" + search.Year
+ " group by pr.ID,pr.Name order by pr.Name";
allQuery.Append(query);
}
else
{
var query = @"SELECT " + head + @" from tbl_Provider pr, tbl_proposal p
where pr.Status=1 and p.Status=1 and pr.ID=p.ProviderID
and pr.ID=" + search.ProviderID
+ " and YEAR(p.InsertDate)=" + search.Year
+ " and MONTH(p.InsertDate) in (" + search.Months + ")"
+ " group by pr.ID,pr.Name order by pr.Name";
allQuery.Append(query);
}
using (var connection = new SqlConnection(ConnectionStrings.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(allQuery.ToString(), connection))
{
var reader = command.ExecuteReader();
while (reader.Read())
{
providerReportDTO = new ProviderReportDTO()
{
ProviderID = reader.GetInt64OrDefaultValue(0),
ProviderName = reader.GetStringOrEmpty(1),
DeclaredProposalCount = reader.GetInt32OrDefaultValue(2),
AppliedProposalCount = reader.GetInt32OrDefaultValue(3),
AccteptedProposalCount = reader.GetInt32OrDefaultValue(4),
RejectedProposalCount = reader.GetInt32OrDefaultValue(5),
WaitingProposalCount = reader.GetInt32OrDefaultValue(6),
};
}
}
connection.Close();
}
return(providerReportDTO);
}
public ProviderReportDTO SW_GetProviderReportsByDatePeriod(Search search)
{
ProviderReportDTO providerReportDTO = null;
string head = "";
head = @" pr.ID,pr.Name,
(select Count(*)from tbl_Proposal prop where pr.ID=prop.ProviderID and prop.Status=1) as YerlesdirilmisXidmetSay,
(select Count(*) from tbl_Proposal prop, tbl_ProposalUserState pus
where prop.Status=1 and pus.Status=1 and
pr.ID=prop.ProviderID and prop.ID=pus.ProposalID and pus.UserStateType=19 ) as MuracietEdilmisXidmetSay,
(select Count(*) from tbl_Proposal prop, tbl_ProposalUserState pus
where prop.Status=1 and pus.Status=1 and
pr.ID=prop.ProviderID and prop.ID=pus.ProposalID and pus.UserStateType=20 and pus.ProviderStateType=15 ) as QebulEdilmisXidmetSay,
(select Count(*) from tbl_Proposal prop, tbl_ProposalUserState pus
where prop.Status=1 and pus.Status=1 and
pr.ID=prop.ProviderID and prop.ID=pus.ProposalID and pus.ProviderStateType=16 ) as RedEdilmisXidmetSay,
(select Count(*) from tbl_Proposal prop, tbl_ProposalUserState pus
where prop.Status=1 and pus.Status=1 and
pr.ID=prop.ProviderID and prop.ID=pus.ProposalID and pus.ProviderStateType=17 ) as GozlemedekiXidmetSay";
StringBuilder allQuery = new StringBuilder();
var query = @"SELECT " + head + @" from tbl_Provider pr, tbl_proposal p
where pr.Status=1 and p.Status=1 and pr.ID=p.ProviderID
and pr.ID=@P_ProviderID and p.InsertDate between @P_FromDate and @P_ToDate
group by pr.ID,pr.Name order by pr.Name";
allQuery.Append(query);
using (var connection = new SqlConnection(ConnectionStrings.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(allQuery.ToString(), connection))
{
command.Parameters.AddWithValue("@P_ProviderID", search.ProviderID);
command.Parameters.AddWithValue("@P_FromDate", search.FromtDate.HasValue ? search.FromtDate.Value : DateTime.Now);
command.Parameters.AddWithValue("@P_ToDate", search.ToDate.HasValue ? search.ToDate.Value : DateTime.Now);
var reader = command.ExecuteReader();
while (reader.Read())
{
if (search.isCount == false)
{
providerReportDTO = new ProviderReportDTO()
{
ProviderID = reader.GetInt64OrDefaultValue(0),
ProviderName = reader.GetStringOrEmpty(1),
DeclaredProposalCount = reader.GetInt32OrDefaultValue(2),
AppliedProposalCount = reader.GetInt32OrDefaultValue(3),
AccteptedProposalCount = reader.GetInt32OrDefaultValue(4),
RejectedProposalCount = reader.GetInt32OrDefaultValue(5),
WaitingProposalCount = reader.GetInt32OrDefaultValue(6),
};
}
}
}
connection.Close();
}
return(providerReportDTO);
}
