public static DataTable SurveyAverageRatingReport(ReportParameters parameters)
{
int F_SurveyYear = DateTime.Today.Year;
string F_SurveyPlannerIDs = "";
if (parameters.GetString("F_SurveyYear") != "")
F_SurveyYear = parameters.GetInteger("F_SurveyYear").Value;
if (parameters.GetList("F_SurveyPlannerIDs").Count > 0)
{
F_SurveyPlannerIDs = "(";
for (int i = 0; i < parameters.GetList("F_SurveyPlannerIDs").Count; i++)
{
if (i == ((parameters.GetList("F_SurveyPlannerIDs").Count) - 1))
F_SurveyPlannerIDs += "'" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "')";
else
F_SurveyPlannerIDs += "'" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "',";
}
}
DateTime Year = new DateTime(F_SurveyYear, 1, 1);
SqlParameter p1 = new SqlParameter("Year", Year);
//SqlParameter p2 = new SqlParameter("SurveyPlannerID", F_SurveyPlanner);
DataSet dts = Connection.ExecuteQuery("#database",
@"
declare @sdate datetime
declare @edate datetime
set @sdate = @Year
set @edate = DateAdd(year,1,@sdate)
declare @tbl table(CheckListName nvarchar(255),CheckListID uniqueidentifier,TotalSurvey int, TotalResponde int,spYear int)
insert into @tbl
select cl.ObjectName,cl.ObjectID,(select count(ObjectID) from Survey where SurveyPlannerID = pl.ObjectID) as 'TotalSurvey',
(select count(ObjectID) from Survey where SurveyPlannerID = pl.ObjectID and Status in (2,3)) as 'TotalRespond',
DatePart(year,pl.ValidStartDate) as 'Year'
from SurveyPlanner pl
--left join SurveyGroupServiceLevel sgl on pl.ObjectID = sgl.SurveyPlannerID
--left join SurveyGroup sg on sgl.SurveyGroupID = sg.ObjectID
left join Survey s on s.SurveyPlannerID = pl.ObjectID
left join SurveyCheckListItem scli on scli.SurveyID = s.ObjectID
left join CheckList cl on scli.CheckListID = cl.ObjectID
where (
(pl.ValidStartDate >= @sdate and pl.ValidEndDate <= @edate)
--or (pl.ValidStartDate >= dateadd(year,-1,@sdate) and pl.ValidEndDate <= dateadd(year,-1,@edate))
)
and pl.IsDeleted = 0 and s.IsDeleted = 0 and cl.IsDeleted = 0 and scli.IsDeleted = 0
and pl.Objectid in " + F_SurveyPlannerIDs + @"
order by pl.CreatedDateTime
select CheckListName as 'CheckListItem',spYear as 'Year',
Convert(decimal(19,2),Convert(decimal(19,2),sum(totalResponde))/Convert(decimal(19,2),sum(totalSurvey))*100) as 'Percentage'
from @tbl
group by CheckListID,CheckListName,spYear
order by spYear", p1);
//DataTable dttemp = dts.Tables[0];
DataTable dt = new DataTable();
dt.Columns.Add("Year", typeof(int));
dt.Columns.Add("Percentage", typeof(Decimal));
dt.Columns.Add("CheckListItem");
dt = dts.Tables[0];
//foreach (DataRow row in dttemp.Rows)
//{
// DataRow r = dt.NewRow();
// r["Year"] = row["Year"];
// r["Percentage"] = row["Percentage"];
// r["CheckListItem"] = row["CheckListItem"];
//}
return dt;
}
/// <summary>
/// Returns a Data Set containing multiple Data Tables reresenting sub-reports table structures.
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataSet ACGReport(ReportParameters parameters)
{
OApplicationSetting appconfig = OApplicationSetting.Current;
DataSet ACGDataSet = new DataSet("ACG Report");
DateTime? filterYTD = parameters.GetDateTime("FILTER_YTD");
DateTime YTD = (filterYTD != null) ? filterYTD.Value : DateTime.Now;
Guid filterLocation = new Guid(parameters.GetString("FILTER_Location"));
OLocation location = TablesLogic.tLocation.Load(filterLocation);
DataTable LocationName = new DataTable();
LocationName.Columns.Add("Location", typeof(string));
LocationName.Rows.Add(location.ObjectName.ToUpper());
LocationName.TableName = "Location";
DataTable BudgetDetailSummaryRRAccounts = BudgetDetailSummaryReport(location, YTD, "Repairs & Replacements", true, 0);
DataTable BudgetDetailSummaryMiscAccounts = BudgetDetailSummaryReport(location, YTD, "Miscellaneous", false, 0);
DataTable BudgetDetailSummaryStatutoryAccounts = BudgetDetailSummaryReport(location, YTD, "Statutory Fee", true, 0);
DataTable BudgetDetailSummary = BudgetDetailSummaryRRAccounts.Clone();
BudgetDetailSummary.Merge(BudgetDetailSummaryRRAccounts);
BudgetDetailSummary.Merge(BudgetDetailSummaryMiscAccounts);
BudgetDetailSummary.Merge(BudgetDetailSummaryStatutoryAccounts);
DataTable BudgetDetailSummaryTenancyAccount = BudgetDetailSummaryReport(location, YTD, "Tenancy Works", false, 0);
BudgetDetailSummaryTenancyAccount.TableName = "BudgetDetailSummaryTenancyAccount";
DataTable BudgetDetailSummaryTermContractAccount = BudgetDetailSummaryReport(location, YTD, "Term Contract", true, 0);
BudgetDetailSummaryTermContractAccount.TableName = "BudgetDetailSummaryTermContractAccount";
DataTable Capex = CapexReport(location, YTD);
//DataTable PreventiveMaintenanceProgramme = PreventiveMaintenanceReport(YTD);
DataTable PreventiveMaintenanceProgramme = new DataTable("PreventiveMaintenanceProgramme");
DataTable ServiceContracts = ServiceContractsReport(location, YTD);
DataTable ServiceToTenants = ServiceToTenantsReport(location, YTD);
ACGDataSet.Tables.Add(BudgetDetailSummary);
ACGDataSet.Tables.Add(Capex);
ACGDataSet.Tables.Add(PreventiveMaintenanceProgramme);
ACGDataSet.Tables.Add(ServiceContracts);
ACGDataSet.Tables.Add(ServiceToTenants);
ACGDataSet.Tables.Add(new DataTable("Dummy"));
ACGDataSet.Tables.Add(LocationName);
ACGDataSet.Tables.Add(BudgetDetailSummaryTenancyAccount);
ACGDataSet.Tables.Add(BudgetDetailSummaryTermContractAccount);
return ACGDataSet;
}
/// <summary>
/// Datatable for Report that return AnalysisOfResponsesOfIndividualQuestions
/// </summary>
/// <returns>DataTable</returns>
public static DataSet AnalysisOfResponsesOfIndividualQuestions(ReportParameters parameters)
{
string F_SurveyGroupID = "";
string F_EvaluatedPartyID = "";
string F_SurveyPlannerID = "";
string F_Questions = "";
if (parameters.GetString("F_SurveyGroupID") != "")
F_SurveyGroupID = parameters.GetString("F_SurveyGroupID");
if (parameters.GetString("F_EvaluatedPartyID") != "")
F_EvaluatedPartyID = parameters.GetString("F_EvaluatedPartyID");
if (parameters.GetString("F_SurveyPlannerID") != "")
F_SurveyPlannerID = parameters.GetString("F_SurveyPlannerID");
if (parameters.GetList("F_Questions").Count > 0)
{
F_Questions = "(";
for (int i = 0; i < parameters.GetList("F_Questions").Count; i++)
{
if (i == ((parameters.GetList("F_Questions").Count) - 1))
F_Questions += "'" + parameters.GetList("F_Questions")[i].ToString() + "')";
else
F_Questions += "'" + parameters.GetList("F_Questions")[i].ToString() + "',";
}
}
else
{
F_Questions = "('')";
}
if (F_SurveyPlannerID == "" || F_SurveyGroupID == "" || F_Questions == "('')")
{
DataSet ds = new DataSet();
DataTable ErrorDT = new DataTable("AnalysisResponse");
ErrorDT.Columns.Add("Error Message");
ErrorDT.Rows.Add(new Object[] { "Please select all filters in order to generate the report correctly." });
ds.Tables.Add(ErrorDT);
ds.Tables.Add(new DataTable("Statistics"));
return ds;
}
DataSet dts = Connection.ExecuteQuery("#database",
@"
select
*,
cast(case when t2.totalexpectedrespondent = 0
then 0
else 100*cast(t2.numberofrespondent as decimal(19,2))/cast(t2.totalexpectedrespondent as decimal(19,2))
end as decimal(19,2))
as percentage,
1 as orderpriority
from
(
select
*,
isnull(
(select
count(scicr.checklistresponseid)
from
surveychecklistitemchecklistresponse scicr
where
scicr.checklistresponseid = t1.cr_objectid
and scicr.surveychecklistitemid in
(select objectid from surveychecklistitem sci
where sci.isdeleted = 0
-- 2011.03.23, Kim Foong, added the following line:
and sci.objectname = t1.sci_objectname
and sci.surveyplannerid = t1.sp_objectid
and ((sci.evaluatedpartyname = t1.evaluatedname and sci.evaluatedpartyid is null) or
((sci.evaluatedpartyname <> t1.evaluatedname or sci.evaluatedpartyname is null) and sci.evaluatedpartyid = t1.evaluatedpartyid and sci.evaluatedpartyid is not null))
-- 2011.03.23, Kim Foong, changed isoverall = 1 to isoverall = 0
and sci.checklistitemtype = 0 and sci.isoverall = 0)
group by
scicr.checklistresponseid)
,0)
as numberofrespondent,
(select count(objectid) from surveychecklistitem sci
where sci.isdeleted = 0
and sci.surveyplannerid = t1.sp_objectid
and ((sci.evaluatedpartyname = t1.evaluatedname and sci.evaluatedpartyid is null) or
((sci.evaluatedpartyname <> t1.evaluatedname or sci.evaluatedpartyname is null) and sci.evaluatedpartyid = t1.evaluatedpartyid and sci.evaluatedpartyid is not null))
and sci.checklistitemtype = 0
and sci.isoverall = 1)
as totalexpectedrespondent
from
(
select
sci.objectname
as sci_objectname,
cr.objectid
as cr_objectid,
cr.scorenumerator
as cr_scorenumerator,
cr.objectname
as cr_objectname,
case when srt.contractmandatory = 0 then sci.evaluatedpartyname
else v.objectname
end
as evaluatedname,
sci.evaluatedpartyid
as evaluatedpartyid,
sp.objectid
as sp_objectid,
sp.performanceperiodfrom
as sp_performanceperiodfrom
from
[surveychecklistitem] sci
left join [surveyplanner] sp on (sp.isdeleted = 0 and sci.surveyplannerid = sp.objectid)
left join [surveyresponseto] srt on (srt.isdeleted = 0 and sci.surveyresponsetoid = srt.objectid)
left join [surveytrade] st on (st.isdeleted = 0 and srt.surveytradeid = st.objectid)
left join [checklistresponseset] crs on (crs.isdeleted = 0 and sci.checklistresponsesetid = crs.objectid)
left join [checklistresponse] cr on (cr.isdeleted = 0 and cr.checklistresponsesetid = crs.objectid)
left join [contract] c on (c.isdeleted = 0 and srt.contractid = c.objectid)
left join [vendor] v on (v.isdeleted = 0 and c.vendorid = v.objectid)
where
sci.isdeleted = 0
and sci.surveyplannerid = '" + F_SurveyPlannerID + @"'
and sci.checklistitemtype = 0
and sci.objectname in " + F_Questions + @"
and st.surveygroupid = '" + F_SurveyGroupID + @"'
and (('" + F_EvaluatedPartyID + @"' = '' and srt.contractmandatory = 0) or ('" + F_EvaluatedPartyID + @"' <> '' and sci.evaluatedpartyid = '" + F_EvaluatedPartyID + @"'))
group by sci.objectname,cr.objectid,cr.scorenumerator,cr.objectname,sci.evaluatedpartyname,v.objectname,srt.contractmandatory,sp.objectid,sp.performanceperiodfrom,sci.evaluatedpartyid
) t1
) t2
order by t2.sp_performanceperiodfrom,t2.sp_objectid,t2.sci_objectname, cr_scorenumerator asc
"
);
if (dts.Tables.Count > 0 && dts.Tables[0].Rows.Count > 0)
{
DataTable dt = dts.Tables[0];
DataTable dt2 = dt.Clone();
string sci_objectname = dt.Rows[0]["sci_objectname"].ToString();
string evaluatedname = dt.Rows[0]["evaluatedname"].ToString();
string sp_objectid = dt.Rows[0]["sp_objectid"].ToString();
DateTime sp_performanceperiodfrom = (DateTime)dt.Rows[0]["sp_performanceperiodfrom"];
int NumberOfNoResponse = (int)dt.Rows[0]["totalexpectedrespondent"];
int totalexpectedrespondent = (int)dt.Rows[0]["totalexpectedrespondent"];
decimal PercentageOfNoResponse = 100M;
foreach (DataRow dr in dt.Rows)
{
if (dr["sp_objectid"].ToString() == sp_objectid && dr["sci_objectname"].ToString() == sci_objectname)
{
NumberOfNoResponse -= (int)dr["numberofrespondent"];
PercentageOfNoResponse -= (decimal)dr["percentage"];
}
else
{
DataRow new_dr = dt2.NewRow();
new_dr["sci_objectname"] = sci_objectname;
new_dr["cr_objectid"] = Guid.Empty;
new_dr["cr_scorenumerator"] = (int)0;
new_dr["cr_objectname"] = "No Response";
new_dr["evaluatedname"] = evaluatedname;
new_dr["sp_objectid"] = sp_objectid;
new_dr["sp_performanceperiodfrom"] = sp_performanceperiodfrom;
new_dr["numberofrespondent"] = NumberOfNoResponse;
new_dr["totalexpectedrespondent"] = totalexpectedrespondent;
new_dr["percentage"] = PercentageOfNoResponse;
new_dr["orderpriority"] = (int)0;
dt2.Rows.Add(new_dr);
sci_objectname = dr["sci_objectname"].ToString();
evaluatedname = dr["evaluatedname"].ToString();
sp_objectid = dr["sp_objectid"].ToString();
sp_performanceperiodfrom = (DateTime)dr["sp_performanceperiodfrom"];
NumberOfNoResponse = (int)dr["totalexpectedrespondent"];
totalexpectedrespondent = (int)dr["totalexpectedrespondent"];
PercentageOfNoResponse = 100M;
NumberOfNoResponse -= (int)dr["numberofrespondent"];
PercentageOfNoResponse -= (decimal)dr["percentage"];
}
}
DataRow lastnew_dr = dt2.NewRow();
lastnew_dr["sci_objectname"] = sci_objectname;
lastnew_dr["cr_scorenumerator"] = (int)0;
lastnew_dr["cr_objectname"] = "No Response";
lastnew_dr["evaluatedname"] = evaluatedname;
lastnew_dr["sp_objectid"] = sp_objectid;
lastnew_dr["sp_performanceperiodfrom"] = sp_performanceperiodfrom;
lastnew_dr["numberofrespondent"] = NumberOfNoResponse;
lastnew_dr["totalexpectedrespondent"] = totalexpectedrespondent;
lastnew_dr["percentage"] = PercentageOfNoResponse;
lastnew_dr["orderpriority"] = (int)0;
dt2.Rows.Add(lastnew_dr);
dt.Merge(dt2);
dt.DefaultView.Sort = "sp_performanceperiodfrom asc, sp_objectid asc, orderpriority asc, cr_scorenumerator asc";
dt.DefaultView.ToTable();
dt.TableName = "AnalysisResponse";
dts.Tables.Add(Reports.Statistics("('"+F_SurveyPlannerID+"')", F_SurveyGroupID, F_EvaluatedPartyID, parameters.GetList("F_Questions")));
return dts;
}
else
{
DataSet ds = new DataSet();
DataTable ErrorDT = new DataTable("AnalysisResponse");
ErrorDT.Columns.Add("Error Message");
ErrorDT.Rows.Add(new Object[] { "Query returns zero table." });
ds.Tables.Add(ErrorDT);
ds.Tables.Add(new DataTable("Statistics"));
return ds;
}
}
public static DataTable SurveySummaryReport(ReportParameters parameters)
{
int F_SurveyYear = DateTime.Today.Year;
string F_SurveyPlannerIDs = "";
if (parameters.GetString("F_SurveyYear") != "")
F_SurveyYear = parameters.GetInteger("F_SurveyYear").Value;
if (parameters.GetList("F_SurveyPlannerIDs").Count > 0)
{
F_SurveyPlannerIDs = "(";
for (int i = 0; i < parameters.GetList("F_SurveyPlannerIDs").Count; i++)
{
if (i == ((parameters.GetList("F_SurveyPlannerIDs").Count) - 1))
F_SurveyPlannerIDs += "'" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "')";
else
F_SurveyPlannerIDs += "'" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "',";
}
}
DateTime Year = new DateTime(F_SurveyYear, 1, 1);
SqlParameter p1 = new SqlParameter("Year", Year);
DataSet dts = Connection.ExecuteQuery("#database",
@"
declare @sdate datetime
declare @edate datetime
set @sdate = @Year
set @edate = DateAdd(year,1,@sdate)
select cl.ObjectName as CheckListItem,scli.ObjectName as Question,
sum(case when clrp.ScoreNumerator = 1 then 1 else 0 end) as Poor ,
sum(case when clrp.ScoreNumerator = 2 then 1 else 0 end) as Average ,
sum(case when clrp.ScoreNumerator = 3 then 1 else 0 end) as Good ,
sum(case when clrp.ScoreNumerator = 4 then 1 else 0 end) as Excellent,
DatePart(year,@Year) as Year
from SurveyPlanner sp
left join Survey s on s.SurveyPlannerID = sp.ObjectID
left join SurveyCheckListItem scli on scli.SurveyID = s.ObjectID
left join CheckList cl on scli.CheckListID = cl.ObjectID
left join SurveyChecklistItemChecklistResponse scrp on scrp.SurveyCheckListItemID = scli.ObjectID
left join ChecklistResponse clrp on clrp.ObjectID = scrp.CheckListResponseID
where (sp.ValidStartDate >= @sdate and sp.ValidEndDate <= @edate)
and sp.IsDeleted = 0
and sp.ObjectID in " + F_SurveyPlannerIDs + @"
group by
scli.CheckListID,scli.ObjectName,cl.ObjectName", p1);
DataTable dt = new DataTable();
dt.Columns.Add("CheckListItem", typeof(string));
dt.Columns.Add("Question", typeof(string));
dt.Columns.Add("Poor", typeof(int));
dt.Columns.Add("Average", typeof(int));
dt.Columns.Add("Good", typeof(int));
dt.Columns.Add("Excellent", typeof(int));
dt.Columns.Add("Year", typeof(int));
dt = dts.Tables[0];
return dt;
}
/// <summary>
/// Datatable for Report that return AverageRatingReport
/// </summary>
/// <returns>DataTable</returns>
public static DataTable AverageRatingReport(ReportParameters parameters)
{
string F_SurveyGroupID = "";
string F_EvaluatedPartyID = "";
string F_SurveyPlannerIDs = "";
if (parameters.GetString("F_SurveyGroupID") != "")
F_SurveyGroupID = parameters.GetString("F_SurveyGroupID");
if (parameters.GetString("F_EvaluatedPartyID") != "")
F_EvaluatedPartyID = parameters.GetString("F_EvaluatedPartyID");
if (parameters.GetList("F_SurveyPlannerIDs").Count > 0)
{
F_SurveyPlannerIDs = "(";
for (int i = 0; i < parameters.GetList("F_SurveyPlannerIDs").Count; i++)
{
if (i == ((parameters.GetList("F_SurveyPlannerIDs").Count) - 1))
F_SurveyPlannerIDs += "'" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "')";
else
F_SurveyPlannerIDs += "'" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "',";
}
}
if (F_SurveyPlannerIDs == "" || F_SurveyGroupID == "")
{
DataTable ErrorDT = new DataTable();
ErrorDT.Columns.Add("Error Message");
ErrorDT.Rows.Add(new Object[] { "Please select all filters in order to generate the report correctly." });
return ErrorDT;
}
DataSet dts = Connection.ExecuteQuery("#database",
@"
select
*,
cast(case when t2.totalexpectedrespondent = 0
then 0
else 100*cast(t2.numberofrespondent as decimal(19,2))/cast(t2.totalexpectedrespondent as decimal(19,2))
end as decimal(19,2))
as percentage
from
(
select
*,
isnull(
(select
count(scicr.checklistresponseid)
from
surveychecklistitemchecklistresponse scicr
where
scicr.checklistresponseid = t1.cr_objectid
and scicr.surveychecklistitemid = t1.sci_objectid)
,0)
as isselected,
isnull(
(select
count(scicr.checklistresponseid)
from
surveychecklistitemchecklistresponse scicr
where
scicr.checklistresponseid = t1.cr_objectid
and scicr.surveychecklistitemid in
(select objectid from surveychecklistitem sci
where sci.isdeleted = 0
and sci.surveyplannerid = sp_objectid
and ((sci.evaluatedpartyname = t1.evaluatedname and sci.evaluatedpartyid is null) or
((sci.evaluatedpartyname <> t1.evaluatedname or sci.evaluatedpartyname is null) and sci.evaluatedpartyid = t1.evaluatedpartyid and sci.evaluatedpartyid is not null))
and sci.isoverall = 1
and sci.checklistitemtype = 0)
group by
scicr.checklistresponseid)
,0)
as numberofrespondent,
(select count(objectid) from surveychecklistitem sci
where sci.isdeleted = 0
and sci.surveyplannerid = sp_objectid
and ((sci.evaluatedpartyname = t1.evaluatedname and sci.evaluatedpartyid is null) or
((sci.evaluatedpartyname <> t1.evaluatedname or sci.evaluatedpartyname is null) and sci.evaluatedpartyid = t1.evaluatedpartyid and sci.evaluatedpartyid is not null))
and sci.isoverall = 1
and sci.checklistitemtype = 0)
as totalexpectedrespondent
from
(
select
sci.objectid
as sci_objectid,
sci.objectname
as sci_objectname,
cr.objectid
as cr_objectid,
cr.scorenumerator
as cr_scorenumerator,
cr.objectname
as cr_objectname,
case when srt.contractmandatory = 0 then sci.evaluatedpartyname
else v.objectname
end
as evaluatedname,
sci.evaluatedpartyid
as evaluatedpartyid,
sp.objectid
as sp_objectid,
sp.performanceperiodfrom
as sp_performanceperiodfrom
from
[surveychecklistitem] sci
left join [surveyplanner] sp on (sp.isdeleted = 0 and sci.surveyplannerid = sp.objectid)
left join [surveyresponseto] srt on (srt.isdeleted = 0 and sci.surveyresponsetoid = srt.objectid)
left join [surveytrade] st on (st.isdeleted = 0 and srt.surveytradeid = st.objectid)
left join [checklistresponseset] crs on (crs.isdeleted = 0 and sci.checklistresponsesetid = crs.objectid)
left join [checklistresponse] cr on (cr.isdeleted = 0 and cr.checklistresponsesetid = crs.objectid)
left join [contract] c on (c.isdeleted = 0 and srt.contractid = c.objectid)
left join [vendor] v on (v.isdeleted = 0 and c.vendorid = v.objectid)
where
sci.isdeleted = 0
and sci.surveyplannerid in " + F_SurveyPlannerIDs + @"
and sci.checklistitemtype = 0
and sci.isoverall = 1
and st.surveygroupid = '" + F_SurveyGroupID + @"'
and (('" + F_EvaluatedPartyID + @"' = '' and srt.contractmandatory = 0) or ('" + F_EvaluatedPartyID + @"' <> '' and sci.evaluatedpartyid = '" + F_EvaluatedPartyID + @"'))
group by sci.objectid,sci.objectname,cr.objectid,cr.scorenumerator,cr.objectname,sci.evaluatedpartyname,sci.evaluatedpartyid,v.objectname,srt.contractmandatory,sp.objectid,sp.performanceperiodfrom
) t1
) t2
order by t2.sp_performanceperiodfrom,t2.sp_objectid,t2.sci_objectname asc
"
);
if (dts.Tables.Count > 0 && dts.Tables[0].Rows.Count > 0)
{
return dts.Tables[0];
}
else
{
DataTable ErrorDT = new DataTable();
ErrorDT.Columns.Add("Error Message");
ErrorDT.Rows.Add(new Object[] { "Query returns zero table." });
return ErrorDT;
}
}
/// <summary>
/// list down all works with scheduled end date has passed current date and has not been closed within certain milestone
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable GetWorkAgingReport(ReportParameters parameters)
{
ExpressionCondition basicCond = (parameters.GetString("TreeviewObject") == "LOCATION" || parameters.GetString("TreeviewObject") == "" ?
(parameters.GetString("TreeviewID") == "" ? GetAccessibleCondition(parameters.GetString("UserID"),true, TablesLogic.tWork.Location.HierarchyPath,"") :
TablesLogic.tWork.Location.HierarchyPath.Like(TablesLogic.tLocation.Load(new Guid(parameters.GetString("TreeviewID"))).HierarchyPath + "%")) :
(parameters.GetString("TreeviewID") == "" ? GetAccessibleCondition(parameters.GetString("UserID"),true, TablesLogic.tWork.Equipment.HierarchyPath,"") :
TablesLogic.tWork.Equipment.HierarchyPath.Like(TablesLogic.tEquipment.Load(new Guid(parameters.GetString("TreeviewID"))).HierarchyPath + "%")))
& TablesLogic.tWork.CurrentActivity.ObjectName != "WORK_REJECTED" & TablesLogic.tWork.CurrentActivity.ObjectName != "WORK_CLOSED" & TablesLogic.tWork.IsDeleted == 0
//for some works at certain status without type of service and type of work
& TablesLogic.tWork.TypeOfWorkID != null & TablesLogic.tWork.TypeOfServiceID != null
& (parameters.GetString("TypeOfWork") == "" ? Query.True : TablesLogic.tWork.TypeOfWorkID == parameters.GetString("TypeOfWork"))
& (parameters.GetString("TypeofService") == "" ? Query.True : TablesLogic.tWork.TypeOfServiceID == parameters.GetString("TypeofService"));
DataTable dt = TablesLogic.tWork.SelectDistinct(TablesLogic.tWork.TypeOfWorkID, TablesLogic.tWork.TypeOfWork.ObjectName.As("WorkType"), TablesLogic.tWork.TypeOfService.ObjectName.As("TypeofService"), TablesLogic.tWork.TypeOfServiceID)
.Where(TablesLogic.tWork.ScheduledEndDateTime != null & basicCond)
.OrderBy(TablesLogic.tWork.TypeOfWork.ObjectName.Asc, TablesLogic.tWork.TypeOfService.ObjectName.Asc);
DataTable returnDt = new DataTable();
returnDt.Columns.Add("WorkType");
returnDt.Columns.Add("TypeofService");
returnDt.Columns.Add("One", typeof(int));
returnDt.Columns.Add("Two", typeof(int));
returnDt.Columns.Add("Three", typeof(int));
returnDt.Columns.Add("Four", typeof(int));
returnDt.Columns.Add("FivePlus", typeof(int));
// unclosed within one day
DataTable oneday = TablesLogic.tWork.Select(TablesLogic.tWork.ObjectNumber.Count(), TablesLogic.tWork.TypeOfServiceID)
.Where(TablesLogic.tWork.ScheduledEndDateTime >= DateTime.Now.AddDays(-1) & TablesLogic.tWork.ScheduledEndDateTime <= DateTime.Now & basicCond)
.GroupBy(TablesLogic.tWork.TypeOfServiceID);
// unclosed within two day
DataTable twoDay = TablesLogic.tWork.Select(TablesLogic.tWork.ObjectNumber.Count(), TablesLogic.tWork.TypeOfServiceID)
.Where(TablesLogic.tWork.ScheduledEndDateTime >= DateTime.Now.AddDays(-2) & TablesLogic.tWork.ScheduledEndDateTime < DateTime.Now.AddDays(-1) & basicCond)
.GroupBy(TablesLogic.tWork.TypeOfServiceID); ;
// unclosed within three day
DataTable threeDay = TablesLogic.tWork.Select(TablesLogic.tWork.ObjectNumber.Count(), TablesLogic.tWork.TypeOfServiceID)
.Where(TablesLogic.tWork.ScheduledEndDateTime >= DateTime.Now.AddDays(-3) & TablesLogic.tWork.ScheduledEndDateTime < DateTime.Now.AddDays(-2) & basicCond)
.GroupBy(TablesLogic.tWork.TypeOfServiceID); ;
//unclosed within four day
DataTable fourDay = TablesLogic.tWork.Select(TablesLogic.tWork.ObjectNumber.Count(), TablesLogic.tWork.TypeOfServiceID)
.Where(TablesLogic.tWork.ScheduledEndDateTime >= DateTime.Now.AddDays(-4) & TablesLogic.tWork.ScheduledEndDateTime < DateTime.Now.AddDays(-3) & basicCond)
.GroupBy(TablesLogic.tWork.TypeOfServiceID); ;
//all unclosed
DataTable fivePlus = TablesLogic.tWork.Select(TablesLogic.tWork.ObjectNumber.Count(), TablesLogic.tWork.TypeOfServiceID)
.Where(TablesLogic.tWork.ScheduledEndDateTime < DateTime.Now.AddDays(-4) & basicCond)
.GroupBy(TablesLogic.tWork.TypeOfServiceID); ;
foreach (DataRow row in dt.Rows)
{
int one = 0, two = 0, three = 0, four = 0, five = 0;
//search for the correct type of service
foreach (DataRow details in oneday.Rows)
if (details["TypeOfServiceID"].ToString() == row["TypeOfServiceID"].ToString() && details[0] != DBNull.Value)
{
one = (int)details[0];
break;
}
foreach (DataRow details in twoDay.Rows)
if (details["TypeOfServiceID"].ToString() == row["TypeOfServiceID"].ToString() && details[0] != DBNull.Value)
{
two = (int)details[0];
break;
}
foreach (DataRow details in threeDay.Rows)
if (details["TypeOfServiceID"].ToString() == row["TypeOfServiceID"].ToString() && details[0] != DBNull.Value)
{
three = (int)details[0];
break;
}
foreach (DataRow details in fourDay.Rows)
if (details["TypeOfServiceID"].ToString() == row["TypeOfServiceID"].ToString() && details[0] != DBNull.Value)
{
four = (int)details[0];
break;
}
foreach (DataRow details in fivePlus.Rows)
if (details["TypeOfServiceID"].ToString() == row["TypeOfServiceID"].ToString() && details[0] != DBNull.Value)
{
five = (int)details[0];
break;
}
//to avoid default value case. Set it to 0
returnDt.Rows.Add(new Object[] { row["WorkType"], row["TypeOfService"], one, two, three, four, five });
}
return returnDt;
}
public static DataTable GetVendorReport(ReportParameters parameters)
{
DataTable dtReturn = new DataTable();
dtReturn.Columns.Add("Vendor Name", typeof(string));
dtReturn.Columns.Add("Vendor Type", typeof(string));
dtReturn.Columns.Add("Vendor Classification", typeof(string));
dtReturn.Columns.Add("Operation Address", typeof(string));
dtReturn.Columns.Add("Operation Cellphone", typeof(string));
dtReturn.Columns.Add("Operation Email", typeof(string));
dtReturn.Columns.Add("Operation Phone", typeof(string));
dtReturn.Columns.Add("Operation Contact Person", typeof(string));
dtReturn.Columns.Add("Debarred", typeof(string));
dtReturn.Columns.Add("Debarment Start", typeof(DateTime));
dtReturn.Columns["Debarment Start"].ExtendedProperties["DataFormatString"] = "{0:dd-MMM-yyyy}";
dtReturn.Columns.Add("Debarment End", typeof(DateTime));
dtReturn.Columns["Debarment End"].ExtendedProperties["DataFormatString"] = "{0:dd-MMM-yyyy}";
dtReturn.Columns.Add("Billing Location Contact Person", typeof(string));
dtReturn.Columns.Add("Billing Location Address", typeof(string));
dtReturn.Columns.Add("Billing Location Phone", typeof(string));
dtReturn.Columns.Add("Billing Location Country", typeof(string));
dtReturn.Columns.Add("Billing Location State", typeof(string));
dtReturn.Columns.Add("Billing Location City", typeof(string));
dtReturn.Columns.Add("Billing Location Cellphone", typeof(string));
dtReturn.Columns.Add("Billing Location Email", typeof(string));
dtReturn.Columns.Add("Billing Location Fax", typeof(string));
dtReturn.Columns.Add("Operation Location Country", typeof(string));
dtReturn.Columns.Add("Operation Location State", typeof(string));
dtReturn.Columns.Add("Operating Location City", typeof(string));
dtReturn.Columns.Add("Operation Location Fax", typeof(string));
DataTable dt = TablesLogic.tVendor.SelectDistinct(
TablesLogic.tVendor.ObjectID,
TablesLogic.tVendor.ObjectName,
TablesLogic.tVendor.VendorClassification.ObjectName.As("VendorClassification"),
TablesLogic.tVendor.OperatingAddress,
TablesLogic.tVendor.OperatingCellPhone,
TablesLogic.tVendor.OperatingEmail,
TablesLogic.tVendor.OperatingPhone,
TablesLogic.tVendor.OperatingContactPerson,
TablesLogic.tVendor.IsDebarred,
TablesLogic.tVendor.DebarmentStartDate,
TablesLogic.tVendor.DebarmentEndDate,
TablesLogic.tVendor.BillingContactPerson,
TablesLogic.tVendor.BillingAddress,
TablesLogic.tVendor.BillingPhone,
TablesLogic.tVendor.BillingAddressCountry,
TablesLogic.tVendor.BillingAddressState,
TablesLogic.tVendor.BillingAddressCity,
TablesLogic.tVendor.BillingCellPhone,
TablesLogic.tVendor.BillingEmail,
TablesLogic.tVendor.BillingFax,
TablesLogic.tVendor.OperatingAddressCountry,
TablesLogic.tVendor.OperatingAddressState,
TablesLogic.tVendor.OperatingAddressCity,
TablesLogic.tVendor.OperatingFax)
.Where(
TablesLogic.tVendor.IsDeleted == 0
& (parameters.GetString("FILTER_DEBARRED") == "" ? Query.True : TablesLogic.tVendor.IsDebarred == parameters.GetString("FILTER_DEBARRED"))
& (parameters.GetString("FILTER_DEBARMENTSTARTFROM") == "" ? Query.True : TablesLogic.tVendor.DebarmentStartDate >= parameters.GetDateTime("FILTER_DEBARMENTSTARTFROM"))
& (parameters.GetString("FILTER_DEBARMENTSTARTTO") == "" ? Query.True : TablesLogic.tVendor.DebarmentStartDate <= parameters.GetDateTime("FILTER_DEBARMENTSTARTTO"))
& (parameters.GetString("FILTER_DEBARMENTENDFROM") == "" ? Query.True : TablesLogic.tVendor.DebarmentEndDate >= parameters.GetDateTime("FILTER_DEBARMENTENDFROM"))
& (parameters.GetString("FILTER_DEBARMENTENDTO") == "" ? Query.True : TablesLogic.tVendor.DebarmentEndDate <= parameters.GetDateTime("FILTER_DEBARMENTENDTO"))
& TablesLogic.tVendor.OperatingAddress.Like("%" + parameters.GetString("FILTER_OPERATIONADDRESS") + "%")
& TablesLogic.tVendor.BillingAddress.Like("%" + parameters.GetString("FILTER_BILLINGADDRESS") + "%")
& TablesLogic.tVendor.ObjectName.Like("%" + parameters.GetString("FILTER_VENDORNAME") + "%")
& (parameters.GetString("FILTER_VENDORTYPE") == "" ? Query.True : TablesLogic.tVendor.VendorTypes.ObjectID == parameters.GetString("FILTER_VENDORTYPE"))
& (parameters.GetString("FILTER_VENDORCLASSIFICATION") == "" ? Query.True : TablesLogic.tVendor.VendorClassificationID == parameters.GetString("FILTER_VENDORCLASSIFICATION"))
);
foreach (DataRow row in dt.Rows)
{
DataRow returnRow = dtReturn.NewRow();
returnRow["Vendor Name"] = row["ObjectName"];
returnRow["Vendor Classification"] = row["VendorClassification"];
returnRow["Operation Address"] = row["OperatingAddress"];
returnRow["Operation Cellphone"] = row["OperatingCellPhone"];
returnRow["Operation Email"] = row["OperatingEmail"];
returnRow["Operation Phone"] = row["OperatingPhone"];
returnRow["Operation Contact Person"] = row["OperatingContactPerson"];
if (row["IsDebarred"].ToString() == "1")
returnRow["Debarred"] = "Yes";
if (row["IsDebarred"].ToString() == "0")
returnRow["Debarred"] = "No";
returnRow["Debarment Start"] = row["DebarmentStartDate"];
returnRow["Debarment End"] = row["DebarmentEndDate"];
returnRow["Billing Location Contact Person"] = row["BillingContactPerson"];
returnRow["Billing Location Address"] = row["BillingAddress"];
returnRow["Billing Location Phone"] = row["BillingPhone"];
returnRow["Billing Location Country"] = row["BillingAddressCountry"];
returnRow["Billing Location State"] = row["BillingAddressState"];
returnRow["Billing Location City"] = row["BillingAddressCity"];
returnRow["Billing Location Cellphone"] = row["BillingCellPhone"];
returnRow["Billing Location Email"] = row["BillingEmail"];
returnRow["Billing Location Fax"] = row["BillingFax"];
returnRow["Operation Location Country"] = row["OperatingAddressCountry"];
returnRow["Operation Location State"] = row["OperatingAddressState"];
returnRow["Operating Location City"] = row["OperatingAddressCity"];
returnRow["Operation Location Fax"] = row["OperatingFax"];
OVendor vendortype = TablesLogic.tVendor[new Guid(row["ObjectID"].ToString())];
string vendortypestring = "";
foreach (OCode codename in vendortype.VendorTypes)
{
vendortypestring = vendortypestring + codename.ObjectName + ", ";
}
if(vendortypestring != "")
returnRow["Vendor Type"] = vendortypestring.Remove(vendortypestring.Length - 2);
dtReturn.Rows.Add(returnRow);
}
return dtReturn;
}
public static DataTable GetContractReport(ReportParameters parameters)
{
DataTable dtReturn = new DataTable();
dtReturn.Columns.Add("Contract Name", typeof(string));
dtReturn.Columns["Contract Name"].ExtendedProperties["Width"] = "500px";
dtReturn.Columns.Add("Contract Description", typeof(string));
dtReturn.Columns["Contract Description"].ExtendedProperties["Width"] = "500px";
dtReturn.Columns.Add("Contract Start Date", typeof(DateTime));
dtReturn.Columns["Contract Start Date"].ExtendedProperties["DataFormatString"] = "{0:dd-MMM-yyyy}";
dtReturn.Columns.Add("Contract End Date", typeof(DateTime));
dtReturn.Columns["Contract End Date"].ExtendedProperties["DataFormatString"] = "{0:dd-MMM-yyyy}";
dtReturn.Columns.Add("Contract Terms", typeof(string));
dtReturn.Columns.Add("Contract Warranty", typeof(string));
dtReturn.Columns.Add("Contract Sum($)", typeof(decimal));
dtReturn.Columns.Add("Contract Manager", typeof(string));
dtReturn.Columns.Add("Contact Person Name", typeof(string));
dtReturn.Columns.Add("Contact Person Cellphone", typeof(string));
dtReturn.Columns.Add("Contact Person Email", typeof(string));
dtReturn.Columns.Add("Provide Adhoc Maintenance", typeof(string));
dtReturn.Columns.Add("Locations", typeof(string));
dtReturn.Columns["Locations"].ExtendedProperties["Width"] = "500px";
dtReturn.Columns.Add("Type of Services", typeof(string));
dtReturn.Columns["Type of Services"].ExtendedProperties["Width"] = "500px";
dtReturn.Columns.Add("Provide Purchasing Agreement", typeof(string));
dtReturn.Columns.Add("Materials Agreement", typeof(string));
dtReturn.Columns["Materials Agreement"].ExtendedProperties["Width"] = "500px";
dtReturn.Columns.Add("Service Agreement", typeof(string));
dtReturn.Columns["Service Agreement"].ExtendedProperties["Width"] = "800px";
dtReturn.Columns.Add("Vendor Name", typeof(string));
dtReturn.Columns.Add("Vendor Type", typeof(string));
dtReturn.Columns.Add("Vendor Classification", typeof(string));
dtReturn.Columns.Add("Contact Person Fax", typeof(string));
dtReturn.Columns.Add("Contact Person Phone", typeof(string));
dtReturn.Columns.Add("Person 1 To Remind", typeof(string));
dtReturn.Columns.Add("Person 2 To Remind", typeof(string));
dtReturn.Columns.Add("Person 3 To Remind", typeof(string));
dtReturn.Columns.Add("Person 4 To Remind", typeof(string));
dtReturn.Columns.Add("Reminder Days 1", typeof(int));
dtReturn.Columns.Add("Reminder Days 2", typeof(int));
dtReturn.Columns.Add("Reminder Days 3", typeof(int));
dtReturn.Columns.Add("Reminder Days 4", typeof(int));
ExpressionCondition cond = GetAccessibleCondition(parameters.GetString("UserID"),true, TablesLogic.tContract.Locations.HierarchyPath, "");
if (cond == null)
cond = Query.False;
DataTable dt = TablesLogic.tContract.SelectDistinct(TablesLogic.tContract.ObjectID,
TablesLogic.tContract.ObjectName,
TablesLogic.tContract.Description,
TablesLogic.tContract.ContractStartDate,
TablesLogic.tContract.ContractEndDate,
TablesLogic.tContract.Terms,
TablesLogic.tContract.Warranty,
TablesLogic.tContract.ContractSum,
TablesLogic.tContract.ContractManager.ObjectName.As("Manager"),
TablesLogic.tContract.ContactPerson,
TablesLogic.tContract.ContactCellphone,
TablesLogic.tContract.ContactPhone,
TablesLogic.tContract.ContactEmail,
TablesLogic.tContract.ContactFax,
TablesLogic.tContract.Reminder1User.ObjectName.As("User1"),
TablesLogic.tContract.Reminder2User.ObjectName.As("User2"),
TablesLogic.tContract.Reminder3User.ObjectName.As("User3"),
TablesLogic.tContract.Reminder4User.ObjectName.As("User4"),
TablesLogic.tContract.EndReminderDays1,
TablesLogic.tContract.EndReminderDays2,
TablesLogic.tContract.EndReminderDays3,
TablesLogic.tContract.EndReminderDays4,
TablesLogic.tContract.Vendor.ObjectName.As("Vendor"),
TablesLogic.tContract.Vendor.VendorClassification.ObjectName.As("VendorClassification"),
TablesLogic.tContract.ProvideMaintenance,
TablesLogic.tContract.ProvidePricingAgreement)
.Where(TablesLogic.tContract.IsDeleted == 0
& TablesLogic.tContract.ObjectName.Like("%" + parameters.GetString("FILTER_CONTRACTNAME") + "%")
& TablesLogic.tContract.Description.Like("%" + parameters.GetString("FILTER_DESCRIPTION") + "%")
& TablesLogic.tContract.Terms.Like("%" + parameters.GetString("FILTER_TERMS") + "%")
& TablesLogic.tContract.Insurance.Like("%" + parameters.GetString("FILTER_INSURANTCE") + "%")
& TablesLogic.tContract.Warranty.Like("%" + parameters.GetString("FILTER_WARRANTY") + "%")
& TablesLogic.tContract.Vendor.ObjectName.Like("%" + parameters.GetString("FILTER_VENDORNAME") + "%")
& (parameters.GetString("FILTER_CONTRACTSTARTFROM") == "" ? Query.True :
TablesLogic.tContract.ContractStartDate >= parameters.GetDateTime("FILTER_CONTRACTSTARTFROM"))
& (parameters.GetString("FILTER_CONTRACTSTARTTO") == "" ? Query.True :
TablesLogic.tContract.ContractStartDate < parameters.GetDateTime("FILTER_CONTRACTSTARTTO").Value.AddDays(1))
& (parameters.GetString("FILTER_CONTRACTENDFROM") == "" ? Query.True :
TablesLogic.tContract.ContractEndDate >= parameters.GetDateTime("FILTER_CONTRACTENDFROM"))
& (parameters.GetString("FILTER_CONTRACTENDTO") == "" ? Query.True :
TablesLogic.tContract.ContractEndDate < parameters.GetDateTime("FILTER_CONTRACTENDTO").Value.AddDays(1))
& (parameters.GetString("FILTER_CONTRACTSUMFROM") == "" ? Query.True :
TablesLogic.tContract.ContractSum >= parameters.GetDecimal("FILTER_CONTRACTSUMFROM"))
& (parameters.GetString("FILTER_CONTRACTSUMTO") == "" ? Query.True :
TablesLogic.tContract.ContractSum <= parameters.GetDecimal("FILTER_CONTRACTSUMTO"))
& (parameters.GetString("TreeviewID") == "" ? cond :
TablesLogic.tContract.Locations.HierarchyPath.Like(TablesLogic.tLocation.Load(new Guid(parameters.GetString("TreeviewID"))).HierarchyPath + "%"))
& (parameters.GetString("FILTER_CONTRACTEXPIRED") == "" ? Query.True :
TablesLogic.tContract.ContractEndDate <= DateTime.Now.AddMonths(parameters.GetInteger("FILTER_CONTRACTEXPIRED").Value))
& (parameters.GetString("FILTER_SHOWCLOSEDCONTRACT") == "" ? TablesLogic.tContract.CurrentActivity.ObjectName != "CONTRACT_CLOSED" :
Query.True)
& (parameters.GetString("FILTER_VENDORTYPE") == "" ? Query.True : TablesLogic.tContract.Vendor.VendorTypes.ObjectID == parameters.GetString("FILTER_VENDORTYPE"))
& (parameters.GetString("FILTER_VENDORCLASSIFICATION") == "" ? Query.True : TablesLogic.tContract.Vendor.VendorClassificationID == parameters.GetString("FILTER_VENDORCLASSIFICATION")));
foreach (DataRow row in dt.Rows)
{
DataRow returnRow = dtReturn.NewRow();
returnRow["Contract Name"] = row["ObjectName"];
returnRow["Contract Description"] = row["Description"];
returnRow["Contract Start Date"] = row["ContractStartDate"];
returnRow["Contract End Date"] = row["ContractEndDate"];
returnRow["Contract Terms"] = row["Terms"];
returnRow["Contract Warranty"] = row["Warranty"];
returnRow["Contract Sum($)"] = row["ContractSum"];
returnRow["Contract Manager"] = row["Manager"];
returnRow["Contact Person Name"] = row["ContactPerson"];
returnRow["Contact Person Cellphone"] = row["ContactCellphone"];
returnRow["Contact Person Email"] = row["ContactEmail"];
returnRow["Contact Person Fax"] = row["ContactFax"];
returnRow["Contact Person Phone"] = row["ContactPhone"];
returnRow["Person 1 To Remind"] = row["User1"];
returnRow["Person 2 To Remind"] = row["User2"];
returnRow["Person 3 To Remind"] = row["User3"];
returnRow["Person 4 To Remind"] = row["User4"];
returnRow["Reminder Days 1"] = row["EndReminderDays1"];
returnRow["Reminder Days 2"] = row["EndReminderDays2"];
returnRow["Reminder Days 3"] = row["EndReminderDays3"];
returnRow["Reminder Days 4"] = row["EndReminderDays4"];
returnRow["Vendor Name"] = row["Vendor"];
returnRow["Vendor Classification"] = row["VendorClassification"];
OContract contract = TablesLogic.tContract[new Guid(row["ObjectID"].ToString())];
string vendortypestring = "";
foreach (OCode codename in contract.Vendor.VendorTypes)
{
vendortypestring = vendortypestring + codename.ObjectName + ", ";
}
if (vendortypestring != "")
returnRow["Vendor Type"] = vendortypestring.Remove(vendortypestring.Length - 2);
//Retrieve all locations, type of services,. of the contract
if (row["ProvideMaintenance"] != DBNull.Value && row["ProvideMaintenance"].ToString() == "1")
{
returnRow["Provide Adhoc Maintenance"] = Resources.Strings.General_Yes;
string locations = "";
foreach (OLocation loc in contract.Locations)
{
if (locations == "")
locations = loc.Path;
else
locations += ", " + loc.Path;
}
returnRow["Locations"] = locations;
string services = "";
foreach (OCode code in contract.TypeOfServices)
{
if (services == "")
services = code.Path;
else
services += ", " + code.Path;
}
returnRow["Type of Services"] = services;
}
else
returnRow["Provide Adhoc Maintenance"] = Resources.Strings.General_No;
if (row["ProvidePricingAgreement"] != DBNull.Value && row["ProvidePricingAgreement"].ToString() == "1")
{
returnRow["Provide Purchasing Agreement"] = Resources.Strings.General_Yes;
string materials = "";
foreach (OContractPriceMaterial contractMat in contract.ContractPriceMaterials)
{
if (materials == "")
materials = contractMat.Catalogue.Path;
else
materials += ", " + contractMat.Catalogue.Path;
}
returnRow["Materials Agreement"] = materials;
string services = "";
foreach (OContractPriceService contractSev in contract.ContractPriceServices)
{
if (services == "")
services = contractSev.FixedRate.Path;
else
services += ", " + contractSev.FixedRate.Path;
}
returnRow["Service Agreement"] = services;
}
else
returnRow["Provide Purchasing Agreement"] = Resources.Strings.General_No;
dtReturn.Rows.Add(returnRow);
}
return dtReturn;
}
public static DataTable Test(ReportParameters parameters) {
DataTable dtUser = TablesLogic.tUser.Select(TablesLogic.tUser.ObjectID,
TablesLogic.tUser.ObjectName,
TablesLogic.tUser.UserBase.Phone,
TablesLogic.tUser.UserBase.Email)
.Where(parameters.GetString("F_UserName") == "" ? Query.True : TablesLogic.tUser.ObjectName.Like("%" + parameters.GetString("F_UserName") + "%")&
TablesLogic.tUser.IsDeleted==0);
DataTable dt = new DataTable();
dt.Columns.Add("UserName");
dt.Columns.Add("PhoneNumber");
dt.Columns.Add("Email");
dt.Columns.Add("Date", typeof(DateTime));
//List<OUser> listuser = TablesLogic.tUser.LoadList(TablesLogic.tUser.IsDeleted == 0 );
foreach (DataRow row in dtUser.Rows)
{
//DataRow row = dt.NewRow();
//row["UserName"] = u.ObjectName;
//row["PhoneNumber"] = u.UserBase.Phone;
//row["Email"] = u.UserBase.Email;
//row["Date"] = DateTime.Today;
//dt.Rows.Add(row);
//OUser u = TablesLogic.tUser.Load(new Guid(row["ObjectID"].ToString());
dt.Rows.Add(new Object[] { row["ObjectName"].ToString(), row["Phone"].ToString(), row["Email"].ToString(),DateTime.Today });
}
return dt;
}
/// <summary>
/// This build a DataTable according to format of the PO Report
/// </summary>
/// <returns></returns>
public static DataTable BudgetPeriodSummary(ReportParameters parameters)
{
DataTable dt = new DataTable("DataTable");
string id = parameters.GetString("BudgetPeriodID");
OBudgetPeriod budgetPeriod = TablesLogic.tBudgetPeriod.Load(new Guid(id));
//labelBudgetName.Text = BudgetPeriod.Budget.ObjectName;
//labelBudgetPeriodName.Text = BudgetPeriod.ObjectName;
//labelStartDate.Text = BudgetPeriod.StartDate.Value.ToString("dd-MMM-yyyy");
//labelEndDate.Text = BudgetPeriod.EndDate.Value.ToString("dd-MMM-yyyy");
dt = budgetPeriod.GenerateSummaryBudgetView(null);
dt.Columns.Add("Location");
dt.Columns.Add("BudgetName");
dt.Columns.Add("BudgetPeriodName");
dt.Columns.Add("StartDate");
dt.Columns.Add("EndDate");
StringBuilder sb = new StringBuilder();
foreach (OLocation location in budgetPeriod.Budget.ApplicableLocations)
sb.Append(location.Path + ", ");
foreach (DataRow dr in dt.Rows)
{
dr["Location"] = sb.ToString();
dr["AccountName"] = new StringBuilder().Insert(0, " ", Convert.ToInt32(dr["Level"].ToString())).ToString() +
dr["AccountName"].ToString();
dr["BudgetName"] = budgetPeriod.ObjectName;
dr["BudgetPeriodName"] = budgetPeriod.Budget.ObjectName;
dr["StartDate"] = budgetPeriod.StartDate.Value.ToString("dd-MMM-yyyy");
dr["EndDate"] = budgetPeriod.EndDate.Value.ToString("dd-MMM-yyyy");
}
return dt;
}
/// <summary>
/// caller history report
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable CallerHistoryReport(ReportParameters parameters)
{
DataTable table = new DataTable();
table.Columns.Add("Location");
table.Columns.Add("Equipment");
table.Columns.Add("Created Date", typeof(DateTime));
table.Columns.Add("Name");
table.Columns.Add("CellPhone");
table.Columns.Add("Email");
table.Columns.Add("Fax");
table.Columns.Add("Phone");
table.Columns.Add("Priority");
table.Columns.Add("Work Description");
table.Columns.Add("Work Type");
table.Columns.Add("Type of service");
table.Columns.Add("Fault Type");
table.Columns.Add("Cost");
table.Columns.Add("Chargeable Amount");
table.Columns.Add("Status");
table.Columns.Add("Rejection Reason");
//string strLocationHirachyPath = LocationHirachyPath(parameters.GetString("TreeviewID"));
DateTime? FILTER_STARTDATEFROM = parameters.GetDateTime("FILTER_STARTDATEFROM");
DateTime? FILTER_STARTDATETO = parameters.GetDateTime("FILTER_STARTDATETO");
DateTime? FILTER_ENDDATEFROM = parameters.GetDateTime("FILTER_ENDDATEFROM");
DateTime? FILTER_ENDDATETO = parameters.GetDateTime("FILTER_ENDDATETO");
DataTable tempTable = TablesLogic.tWork.Select(
TablesLogic.tWork.ObjectID,
TablesLogic.tWork.LocationID,
TablesLogic.tWork.CreatedDateTime,
TablesLogic.tWork.CallerName,
TablesLogic.tWork.CallerCellPhone,
TablesLogic.tWork.CallerEmail,
TablesLogic.tWork.CallerFax,
TablesLogic.tWork.CallerPhone,
TablesLogic.tWork.Priority,
TablesLogic.tWork.WorkDescription,
TablesLogic.tWork.TypeOfWork.ObjectName,
TablesLogic.tWork.TypeOfService.ObjectName,
TablesLogic.tWork.TypeOfProblem.ObjectName,
TablesLogic.tWork.CurrentActivity.ObjectName)
.Where(TablesLogic.tWork.IsDeleted == 0 &
// & TablesLogic.tWork.Location.HierarchyPath.Like(strLocationHirachyPath + "%")
(parameters.GetString("TreeviewID") == "" ? GetAccessibleCondition(parameters.GetString("UserID"),true, TablesLogic.tWork.Location.HierarchyPath, "") :
TablesLogic.tWork.Location.HierarchyPath.Like(TablesLogic.tLocation.Load(new Guid(parameters.GetString("TreeviewID"))).HierarchyPath + "%"))
& (FILTER_STARTDATEFROM <= DateTime.Parse("1/1/1855") ? Query.True :
TablesLogic.tWork.CreatedDateTime >= parameters.GetDateTime("FILTER_STARTDATEFROM"))
& (FILTER_STARTDATETO <= DateTime.Parse("1/1/1855") ? Query.True :
TablesLogic.tWork.CreatedDateTime < parameters.GetDateTime("FILTER_STARTDATETO").Value.AddDays(1))
& TablesLogic.tWork.CallerName.Like("%" + parameters.GetString("FILTER_CALLERNAME") + "%")
);
foreach (DataRow row in tempTable.Rows)
{
OWork work = TablesLogic.tWork[new Guid(row["ObjectID"].ToString())];
switch (parameters.GetInteger("FILTER_CHARGEABLEAMOUNT"))
{
case 1: if ((parameters.GetDecimal("FILTER_COST") == null ? true : work.TotalActualCost == parameters.GetDecimal("FILTER_COST")) && work.TotalChargeOut < 2000)
table.Rows.Add(new object[] { row[1].ToString()==null?"":LocationPath(new Guid(row[1].ToString())),
row[2].ToString()==""?"":EquipmentPath(new Guid(row[2].ToString())),
row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13],
work.TotalActualCost, work.TotalChargeOut, TranslateWorkStatusToString(row[14].ToString()), row[14].ToString()=="WORK_REJECTED" ? row[15].ToString() : ""});
break;
case 2: if ((parameters.GetDecimal("FILTER_COST") == null ? true : work.TotalActualCost == parameters.GetDecimal("FILTER_COST")) && work.TotalChargeOut <= 5000 && work.TotalChargeOut >= 2000)
table.Rows.Add(new object[] { row[1].ToString()==null?"":LocationPath(new Guid(row[1].ToString())),
row[2].ToString()==""?"":EquipmentPath(new Guid(row[2].ToString())),
row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13],
work.TotalActualCost, work.TotalChargeOut, TranslateWorkStatusToString(row[14].ToString()), row[14].ToString()=="WORK_REJECTED" ? row[15].ToString() : ""});
break;
case 3: if ((parameters.GetDecimal("FILTER_COST") == null ? true : work.TotalActualCost == parameters.GetDecimal("FILTER_COST")) && work.TotalChargeOut > 5000)
table.Rows.Add(new object[] { row[1].ToString()==null?"":LocationPath(new Guid(row[1].ToString())),
row[2].ToString()==""?"":EquipmentPath(new Guid(row[2].ToString())),
row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13],
work.TotalActualCost, work.TotalChargeOut, TranslateWorkStatusToString(row[14].ToString()), row[14].ToString()=="WORK_REJECTED" ? row[15].ToString() : ""});
break;
default: if (parameters.GetDecimal("FILTER_COST") == null ? true : work.TotalActualCost == parameters.GetDecimal("FILTER_COST"))
table.Rows.Add(new object[] { row[1].ToString()==null?"":LocationPath(new Guid(row[1].ToString())),
row[2].ToString()==""?"":EquipmentPath(new Guid(row[2].ToString())),
row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13],
work.TotalActualCost, work.TotalChargeOut, TranslateWorkStatusToString(row[14].ToString()), row[14].ToString()=="WORK_REJECTED" ? row[15].ToString() : ""});
break;
}
}
return table;
}
//TVO customize
#region TVO Report
/// <summary>
/// Datatable for Report that return BudgetSummaryReport
/// </summary>
/// <returns></returns>
public static DataTable BudgetSummaryReport(ReportParameters parameters)
{
using(Connection c= new Connection())
{
DataTable dtReturn = new DataTable();
// declare column
dtReturn.Columns.Add("OriginalAmount", typeof(decimal));
dtReturn.Columns.Add("ReallocatedAmount", typeof(decimal));
dtReturn.Columns.Add("TotalAdjusted", typeof(decimal));
dtReturn.Columns.Add("TotalPendingApproval", typeof(decimal));
dtReturn.Columns.Add("TotalApproved", typeof(decimal));
dtReturn.Columns.Add("DirectExpense", typeof(decimal));
dtReturn.Columns.Add("TotalInvoiced", typeof(decimal));
dtReturn.Columns.Add("TotalPaid", typeof(decimal));
dtReturn.Columns.Add("TotalAvailable", typeof(decimal));
dtReturn.Columns.Add("BudgetName");
dtReturn.Columns.Add("Location");
dtReturn.Columns.Add("FinancialYear");
dtReturn.Columns.Add("AccountNameParent");
dtReturn.Columns.Add("AccountName");
dtReturn.Columns.Add("AccountAccount");
string strCheck = parameters.GetString("Budget");
if (strCheck == "")
{
dtReturn.Rows.Add(new Object[] {
DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value,
DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, "",
"", "", "", "", "" });
return dtReturn;
}
DataTable dt = new DataTable();
OBudgetPeriod budgetPeriod = TablesLogic.tBudgetPeriod[new Guid(strCheck)];
dt = budgetPeriod.GenerateYearlyBudgetView(null);
string applicationLocations = "";
foreach(OLocation location in budgetPeriod.Budget.ApplicableLocations)
applicationLocations += location.Path + "; ";
// dt = OYearlyBudget.BuildTreeViewEffect(dt);
int intdtCount = dt.Rows.Count;
for (int i = 0; i < intdtCount; i++)
{
if (dt.Rows[i]["TotalOpeningBalance"].ToString() != "")
{
OAccount account = TablesLogic.tAccount[new Guid(dt.Rows[i]["AccountID"].ToString())];
dtReturn.Rows.Add(new Object[] {
Decimal.Parse(dt.Rows[i]["TotalOpeningBalance"].ToString()),
Decimal.Parse(dt.Rows[i]["TotalReallocatedAmount"].ToString()),
Decimal.Parse(dt.Rows[i]["TotalBalanceAfterVariation"].ToString()),
Decimal.Parse(dt.Rows[i]["TotalPendingApproval"].ToString()),
Decimal.Parse(dt.Rows[i]["TotalApproved"].ToString()),
Decimal.Parse(dt.Rows[i]["TotalDirectInvoiced"].ToString()),
Decimal.Parse(dt.Rows[i]["TotalInvoiceApproved"].ToString()),
0M,
Decimal.Parse(dt.Rows[i]["TotalAvailableBalance"].ToString()),
budgetPeriod.ObjectName,
applicationLocations,
budgetPeriod.StartDate.Value.Year,
(account.Parent ==null ? account.ObjectName: account.Parent.Path),
account.ObjectName,
account.AccountCode
});
}
}
return dtReturn;
}
}
/// <summary>
/// Total number of works that complies with the SLA, group by type of service. Only applicable for work with SLA applied to
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable GetWorkComplianceReport(ReportParameters parameters)
{
ExpressionCondition basicCond = (parameters.GetString("TreeviewObject") == "LOCATION" || parameters.GetString("TreeviewObject") == "" ?
(parameters.GetString("TreeviewID") == "" ? GetAccessibleCondition(parameters.GetString("UserID"), true, TablesLogic.tWork.Location.HierarchyPath, "") :
TablesLogic.tWork.Location.HierarchyPath.Like(TablesLogic.tLocation.Load(new Guid(parameters.GetString("TreeviewID"))).HierarchyPath + "%")) :
(parameters.GetString("TreeviewID") == "" ? GetAccessibleCondition(parameters.GetString("UserID"), true, TablesLogic.tWork.Equipment.HierarchyPath, "") :
TablesLogic.tWork.Equipment.HierarchyPath.Like(TablesLogic.tEquipment.Load(new Guid(parameters.GetString("TreeviewID"))).HierarchyPath + "%")))
& TablesLogic.tWork.CurrentActivity.ObjectName != "WORK_REJECTED" & TablesLogic.tWork.IsDeleted == 0
//for some works at certain status without type of service and type of work
& TablesLogic.tWork.TypeOfWorkID != null & TablesLogic.tWork.TypeOfServiceID != null
& (parameters.GetString("TypeOfWork") == "" ? Query.True : TablesLogic.tWork.TypeOfWorkID == parameters.GetString("TypeOfWork"))
& (parameters.GetString("TypeofService") == "" ? Query.True : TablesLogic.tWork.TypeOfServiceID == parameters.GetString("TypeofService"));
//retrieve all work with SLA
DataTable allWork = TablesLogic.tWork.Select(TablesLogic.tWork.TypeOfServiceID, TablesLogic.tWork.TypeOfService.ObjectName.As("TypeOfService"),
TablesLogic.tWork.TypeOfWork.ObjectName.As("TypeOfWork"),
TablesLogic.tWork.AcknowledgementDateTimeLimit.Count().As("TotalAck"),
TablesLogic.tWork.ArrivalDateTimeLimit.Count().As("TotalArr"), TablesLogic.tWork.CompletionDateTimeLimit.Count().As("TotalComp"))
.Where(basicCond
//the work must have SLA tied to
& (TablesLogic.tWork.AcknowledgementDateTimeLimit != null | TablesLogic.tWork.ArrivalDateTimeLimit != null | TablesLogic.tWork.CompletionDateTimeLimit != null))
.GroupBy(TablesLogic.tWork.TypeOfServiceID, TablesLogic.tWork.TypeOfService.ObjectName, TablesLogic.tWork.TypeOfWork.ObjectName)
.OrderBy(TablesLogic.tWork.TypeOfService.ObjectName.Asc);
//Number of cases meeting the acknowledgement SLA
DataTable ack = TablesLogic.tWork.Select(TablesLogic.tWork.TypeOfServiceID, TablesLogic.tWork.AcknowledgementDateTime.Count().As("Ack"))
.Where(basicCond
//AcknowledgementDateTime <= AcknowledgementDateTimeLimit
& (TablesLogic.tWork.AcknowledgementDateTime <= TablesLogic.tWork.AcknowledgementDateTimeLimit))
.GroupBy(TablesLogic.tWork.TypeOfServiceID);
//Number of cases meeting the arrival SLA
DataTable arr = TablesLogic.tWork.Select(TablesLogic.tWork.TypeOfServiceID, TablesLogic.tWork.ArrivalDateTime.Count().As("Arr"))
.Where(basicCond
& (TablesLogic.tWork.ArrivalDateTime <= TablesLogic.tWork.ArrivalDateTimeLimit))
.GroupBy(TablesLogic.tWork.TypeOfServiceID);
//Number of cases meeting the complete SLA
DataTable comp = TablesLogic.tWork.Select(TablesLogic.tWork.TypeOfServiceID, TablesLogic.tWork.ActualEndDateTime.Count().As("Comp"))
.Where(basicCond
& (TablesLogic.tWork.ActualEndDateTime <= TablesLogic.tWork.CompletionDateTimeLimit))
.GroupBy(TablesLogic.tWork.TypeOfServiceID);
DataTable returnDt = new DataTable();
returnDt.Columns.Add("TypeOfService");
returnDt.Columns.Add("TypeOfWork");
returnDt.Columns.Add("AckPerc", typeof(decimal));
returnDt.Columns.Add("ArrPerc", typeof(decimal));
returnDt.Columns.Add("CompPerc", typeof(decimal));
foreach (DataRow row in allWork.Rows)
{
decimal totalAckWork = Convert.ToDecimal(row["TotalAck"].ToString());
decimal totalArrWork = Convert.ToDecimal(row["TotalArr"].ToString());
decimal totalCompWork = Convert.ToDecimal(row["TotalComp"].ToString());
DataRow returnRow = returnDt.NewRow();
returnRow["TypeOfService"] = row["TypeOfService"];
returnRow["TypeOfWork"] = row["TypeOfWork"];
//default percentage to 0
returnRow["ArrPerc"] = 0.0M;
returnRow["AckPerc"] = 0.0M;
returnRow["CompPerc"] = 0.0M;
foreach (DataRow details in ack.Rows)
if (details["TypeOfServiceID"].ToString() == row["TypeOfServiceID"].ToString())
{
returnRow["AckPerc"] = (Convert.ToDecimal(details[1].ToString()) / totalAckWork) * 100;
break;
}
foreach (DataRow details in arr.Rows)
if (details["TypeOfServiceID"].ToString() == row["TypeOfServiceID"].ToString())
{
returnRow["ArrPerc"] = (Convert.ToDecimal(details[1].ToString()) / totalArrWork) * 100;
break;
}
foreach (DataRow details in arr.Rows)
if (details["TypeOfServiceID"].ToString() == row["TypeOfServiceID"].ToString())
{
returnRow["CompPerc"] = (Convert.ToDecimal(details[1].ToString()) / totalCompWork) * 100;
break;
}
returnDt.Rows.Add(returnRow);
}
return returnDt;
}
/// <summary>
/// Datatable for Report that return SurveyPlannerResultTotalScore
/// </summary>
/// <returns>DataTable</returns>
public static DataTable SurveyPlannerResultTotalScore(ReportParameters parameters)
{
string F_SurveyGroupID = "";
string F_SurveyPlannerIDs = "";
string F_SurveyPlanner2IDs = "";
if (parameters.GetString("F_SurveyGroupID") != "")
F_SurveyGroupID = parameters.GetString("F_SurveyGroupID");
if (parameters.GetList("F_SurveyPlannerIDs").Count > 0)
{
F_SurveyPlannerIDs = "(";
F_SurveyPlanner2IDs = "(";
for (int i = 0; i < parameters.GetList("F_SurveyPlannerIDs").Count; i++)
{
if (i == ((parameters.GetList("F_SurveyPlannerIDs").Count) - 1))
{
F_SurveyPlannerIDs += "'" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "')";
F_SurveyPlanner2IDs += "''" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "'')";
}
else
{
F_SurveyPlannerIDs += "'" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "',";
F_SurveyPlanner2IDs += "''" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "'',";
}
}
}
if (F_SurveyGroupID == "" || F_SurveyPlannerIDs == "")
{
DataTable ErrorDT = new DataTable();
ErrorDT.Columns.Add("Error Message");
ErrorDT.Rows.Add(new Object[] { "Please select all filters in order to generate the report correctly." });
return ErrorDT;
}
SqlParameter p1 = new SqlParameter("SurveyGroupID", F_SurveyGroupID);
SqlParameter p2 = new SqlParameter("NA", (object)("'N.A.'"));
SqlParameter p3 = new SqlParameter("Remaining", (object)("'...'"));
SqlParameter p4 = new SqlParameter("Percentage", (object)("'%'"));
SqlParameter p5 = new SqlParameter("dash", (object)("'-'"));
DataSet dts = Connection.ExecuteQuery("#database",
@"
-- To generate the columns --
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE (@cols + ',[' + case when len(a.objectname) > 50 then substring(a.objectname,1,50) + '...' else a.objectname end + ']',
'[' + case when len(a.objectname) > 50 then substring(a.objectname,1,50) + '...' else a.objectname end + ']')
FROM SurveyChecklistItem a
LEFT JOIN SurveyResponseTo b on a.SurveyResponseToID = b.ObjectID
LEFT JOIN surveytrade c on b.surveytradeid = c.objectid
WHERE a.SurveyRespondentID = (select top 1 t1.surveyrespondentid
from
surveychecklistitem t1
left join surveyresponseto t2 on t1.surveyresponsetoid = t2.objectid
left join surveytrade t3 on t2.surveytradeid = t3.objectid
where
t1.isdeleted = 0 and t2.isdeleted = 0
and t3.isdeleted = 0
and t1.surveyplannerid in " + F_SurveyPlannerIDs + @"
and t3.surveygroupid = @SurveyGroupID)
AND a.SurveyResponseToID = (select top 1 t2.objectid
from
surveychecklistitem t1
left join surveyresponseto t2 on t1.surveyresponsetoid = t2.objectid
left join surveytrade t3 on t2.surveytradeid = t3.objectid
where
t1.isdeleted = 0
and t2.isdeleted = 0 and t3.isdeleted = 0
and t1.surveyplannerid in " + F_SurveyPlannerIDs + @"
and t3.surveygroupid = @SurveyGroupID)
AND a.SurveyPlannerID in " + F_SurveyPlannerIDs + @"
AND c.SurveyGroupID = @SurveyGroupID
ORDER BY a.stepnumber asc
IF (@cols is not null)
BEGIN
EXECUTE(N'SELECT
*
FROM
(SELECT
case when len(scli.objectname) > 50 then substring(scli.objectname,1,50) + '+@Remaining+' else scli.objectname end as question
, scli.checklistid
, d.objectname as tradename
, c.objectid as contractid
, c.objectnumber as contractnumber
, c.objectname as contractname
, case when c.contractreferencenumbersuffix is not null then (c.contractreferencenumber + '+@dash+' + c.contractreferencenumbersuffix)
else c.contractreferencenumber
end as contractreferencenumber
, c.contractstartdate
, c.contractenddate
, case when (c.objectid is not null and v.objectid is not null)
then v.objectname
else srt.evaluatedpartyname
end as evaluatedpartyname
, srt.surveyid
, sr.objectname as surveyrespondentname
, v.ObjectName as vendorName
, splan.ObjectName as plannerName
, case when scli.checklistitemtype in (1,4) then scli.description
when scli.checklistitemtype in (0,3) then
(select
cast(sum(clr.scorenumerator) as nvarchar(50))
from [surveychecklistitemchecklistresponse] scliclr
left join [checklistresponse] clr on scliclr.checklistresponseid = clr.objectid
where scliclr.surveychecklistitemid = scli.objectid
)
else '+@NA+'
end as score
FROM [surveychecklistitem] scli
LEFT JOIN [surveyplanner] splan on (scli.surveyplannerid = splan.objectid and splan.isdeleted = 0)
LEFT JOIN [survey] s on (scli.surveyid = s.objectid and s.isdeleted = 0)
LEFT JOIN [surveyresponseto] srt on (scli.surveyresponsetoid = srt.objectid and srt.isdeleted = 0)
LEFT JOIN [contract] c on (srt.contractid = c.objectid and srt.contractmandatory = 1 and c.isdeleted = 0)
LEFT JOIN [vendor] v on (c.vendorid = v.objectid and v.isdeleted = 0)
LEFT JOIN [surveytrade] st on (srt.surveytradeid = st.objectid and st.isdeleted = 0)
LEFT JOIN [surveygroup] d on (st.surveygroupid = d.objectid and d.isdeleted = 0)
LEFT JOIN [surveyrespondent] sr on (scli.surveyrespondentid = sr.objectid and sr.isdeleted = 0)
WHERE
scli.isdeleted = 0
and scli.surveyrespondentid in
(select distinct
t1.surveyrespondentid
from
surveychecklistitem t1
left join surveyresponseto t2 on t1.surveyresponsetoid = t2.objectid
left join surveytrade t3 on t2.surveytradeid = t3.objectid
where
t1.isdeleted = 0
and t2.isdeleted = 0 and t3.isdeleted = 0
and t1.surveyplannerid in " + F_SurveyPlanner2IDs + @"
and t3.surveygroupid = '''+@SurveyGroupID+''')
and scli.surveyplannerid in " + F_SurveyPlanner2IDs + @"
and d.objectid = '''+@SurveyGroupID+'''
) p
PIVOT
(
MAX(p.score)
FOR p.question IN
( '+
@cols +' )
) AS pvt'
)
END
",
p1, p2, p3, p4, p5
);
if (dts.Tables.Count > 0)
{
DataTable dt = dts.Tables[0];
dt.Columns[0].ColumnName = "Checklist ObjectID";
dt.Columns[1].ColumnName = "Trade";
dt.Columns[2].ColumnName = "Contract ObjectID";
dt.Columns[3].ColumnName = "Contract Number";
dt.Columns[4].ColumnName = "Contract Name";
dt.Columns[5].ColumnName = "Contract Reference No.";
dt.Columns[6].ColumnName = "Contract Start Date";
dt.Columns[7].ColumnName = "Contract End Date";
dt.Columns[8].ColumnName = "Evaluated Party";
dt.Columns[9].ColumnName = "Survey ObjectID";
dt.Columns[10].ColumnName = "Respondent";
dt.Columns[11].ColumnName = "Vendor Name";
dt.Columns[12].ColumnName = "Survey Planner Name";
int TotalNumberOfQuestions = (dt.Columns.Count - 13);
dt.Columns.Add("Total Score", typeof(decimal));
dt.Columns["Total Score"].ExtendedProperties["DataFormatString"] = "{0:#,##0.00}";
List<OChecklistItem> list = TablesLogic.tChecklistItem.LoadList(
TablesLogic.tChecklistItem.ChecklistID == new Guid(dt.Rows[0]["Checklist ObjectID"].ToString())
,
TablesLogic.tChecklistItem.StepNumber.Asc
);
DataTable dt2 = dt.Clone();
for (int i = 0; i < TotalNumberOfQuestions; i++)
{
if (((OChecklistItem)list[i]).ChecklistType == ChecklistItemType.Choice ||
((OChecklistItem)list[i]).ChecklistType == ChecklistItemType.MultipleSelections)
dt2.Columns[13 + i].DataType = typeof(decimal);
}
foreach (DataRow dr in dt.Rows)
{
dr["Total Score"] = 0M;
for (int i = 0; i < TotalNumberOfQuestions; i++)
{
if (((OChecklistItem)list[i]).ChecklistType == ChecklistItemType.Choice ||
((OChecklistItem)list[i]).ChecklistType == ChecklistItemType.MultipleSelections)
dr["Total Score"] = (decimal)dr["Total Score"] + (dr[13 + i] == DBNull.Value || dr[13 + i].ToString() == "" ? 0 : Convert.ToDecimal(dr[13 + i].ToString()));
}
dt2.ImportRow(dr);
}
dt2.Columns.Remove(dt2.Columns["Checklist ObjectID"]);
dt2.Columns.Remove(dt2.Columns["Contract ObjectID"]);
dt2.Columns.Remove(dt2.Columns["Contract Reference No."]);
dt2.Columns.Remove(dt2.Columns["Survey ObjectID"]);
return dt2;
}
else
{
DataTable ErrorDT = new DataTable();
ErrorDT.Columns.Add("Error Message");
ErrorDT.Rows.Add(new Object[] { "Query returns zero table." });
return ErrorDT;
}
}
/// <summary>
/// Datatable for Report that return SurveyPlannerResultPercentage
/// </summary>
/// <returns>DataTable</returns>
public static DataTable SurveyPlannerResultPercentage(ReportParameters parameters)
{
string F_SurveyGroupID = "";
string F_SurveyPlannerIDs = "";
string F_SurveyPlanner2IDs = "";
if (parameters.GetString("F_SurveyGroupID") != "")
F_SurveyGroupID = parameters.GetString("F_SurveyGroupID");
if (parameters.GetList("F_SurveyPlannerIDs").Count > 0)
{
F_SurveyPlannerIDs = "(";
F_SurveyPlanner2IDs = "(";
for (int i = 0; i < parameters.GetList("F_SurveyPlannerIDs").Count; i++)
{
if (i == ((parameters.GetList("F_SurveyPlannerIDs").Count) - 1))
{
F_SurveyPlannerIDs += "'" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "')";
F_SurveyPlanner2IDs += "''" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "'')";
}
else
{
F_SurveyPlannerIDs += "'" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "',";
F_SurveyPlanner2IDs += "''" + parameters.GetList("F_SurveyPlannerIDs")[i].ToString() + "'',";
}
}
}
if (F_SurveyGroupID == "" || F_SurveyPlannerIDs == "")
{
DataTable ErrorDT = new DataTable();
ErrorDT.Columns.Add("Error Message");
ErrorDT.Rows.Add(new Object[] { "Please select all filters in order to generate the report correctly." });
return ErrorDT;
}
SqlParameter p1 = new SqlParameter("SurveyGroupID", F_SurveyGroupID);
SqlParameter p2 = new SqlParameter("NA", (object)("'N.A.'"));
SqlParameter p3 = new SqlParameter("Remaining", (object)("'...'"));
SqlParameter p4 = new SqlParameter("Percentage", (object)("'%'"));
SqlParameter p5 = new SqlParameter("dash", (object)("'-'"));
DataSet dts = Connection.ExecuteQuery("#database",
@"
-- To generate the columns --
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE (@cols + ',[' + case when len(j.objectname) > 50 then substring(j.objectname,1,50) + '...' else j.objectname end + ']',
'[' + case when len(j.objectname) > 50 then substring(j.objectname,1,50) + '...' else j.objectname end + ']')
FROM (select top 1 a.checklistresponsesetid, a.objectname
FROM SurveyChecklistItem a
LEFT JOIN SurveyResponseTo b on a.SurveyResponseToID = b.ObjectID
LEFT JOIN surveytrade c on b.surveytradeid = c.objectid
WHERE a.SurveyRespondentID = (select top 1 t1.surveyrespondentid
from
surveychecklistitem t1
left join surveyresponseto t2 on t1.surveyresponsetoid = t2.objectid
left join surveytrade t3 on t2.surveytradeid = t3.objectid
where
t1.isdeleted = 0 and t2.isdeleted = 0
and t3.isdeleted = 0
and t1.surveyplannerid in " + F_SurveyPlannerIDs + @"
and t3.surveygroupid = @SurveyGroupID)
AND a.SurveyResponseToID = (select top 1 t2.objectid
from
surveychecklistitem t1
left join surveyresponseto t2 on t1.surveyresponsetoid = t2.objectid
left join surveytrade t3 on t2.surveytradeid = t3.objectid
where
t1.isdeleted = 0
and t2.isdeleted = 0 and t3.isdeleted = 0
and t1.surveyplannerid in " + F_SurveyPlannerIDs + @"
and t3.surveygroupid = @SurveyGroupID)
AND a.SurveyPlannerID in " + F_SurveyPlannerIDs + @"
AND c.SurveyGroupID = @SurveyGroupID
AND a.checklistresponsesetid is not null
ORDER BY a.stepnumber asc) p
LEFT JOIN [checklistresponseset] clrs on (p.checklistresponsesetid = clrs.objectid and clrs.isdeleted = 0)
LEFT JOIN [checklistresponse] j on (clrs.objectid = j.checklistresponsesetid and j.isdeleted = 0)
ORDER BY j.scorenumerator DESC
IF (@cols is not null)
BEGIN
EXECUTE(N'SELECT
*
FROM
(
SELECT
stepnumber, question, tradename, evaluatedpartyname,contractid,
contractNumber,vendorName,plannerName,choicename
,
cast(case when totalselection = 0
then 0
else 100*cast(totalselection as decimal(19,2))/cast(totalrespondent as decimal(19,2))
end as decimal(19,2))
as percentage
FROM
(
SELECT stepnumber, question, tradename, contractid, evaluatedpartyname,choicename,sum(count) as totalselection, totalrespondent,
contractNumber,vendorName,plannerName
FROM
(SELECT
scli.stepnumber as stepnumber,
case when len(scli.objectname) > 50 then substring(scli.objectname,1,50) + '+@Remaining+' else scli.objectname end as question
, d.objectname as tradename
, c.objectid as contractid
, case when (c.objectid is not null and v.objectid is not null)
then v.objectname
else srt.evaluatedpartyname
end as evaluatedpartyname
, c.ObjectNumber as contractNumber
, v.ObjectName as vendorName
, splan.ObjectName as plannerName
,case when scli.checklistitemtype in (1,4) then 0
when scli.checklistitemtype in (0,3) then
(select
sum(clr.scorenumerator)
from [surveychecklistitemchecklistresponse] scliclr
left join [checklistresponse] clr on scliclr.checklistresponseid = clr.objectid
where scliclr.surveychecklistitemid = scli.objectid
)
else 0 end as score,
(select count(objectid) from [surveychecklistitem]
where surveyplannerid = scli.surveyplannerid
and checklistid = scli.checklistid
and stepnumber = scli.stepnumber
and ((evaluatedpartyname = scli.evaluatedpartyname and evaluatedpartyid is null) or
(evaluatedpartyid = scli.evaluatedpartyid and evaluatedpartyid is not null))
) as totalrespondent
, case when scliclr.checklistresponseid is null then 0 else 1 end as count
, isnull(clr.objectname, '' Not Selected '') as choicename
FROM [surveychecklistitem] scli
LEFT JOIN [surveyplanner] splan on (scli.surveyplannerid = splan.objectid and splan.isdeleted = 0)
LEFT JOIN [survey] s on (scli.surveyid = s.objectid and s.isdeleted = 0)
LEFT JOIN [surveyresponseto] srt on (scli.surveyresponsetoid = srt.objectid and srt.isdeleted = 0)
LEFT JOIN [contract] c on (srt.contractid = c.objectid and srt.contractmandatory = 1 and c.isdeleted = 0)
LEFT JOIN [vendor] v on (c.vendorid = v.objectid and v.isdeleted = 0)
LEFT JOIN [surveytrade] st on (srt.surveytradeid = st.objectid and st.isdeleted = 0)
LEFT JOIN [surveygroup] d on (st.surveygroupid = d.objectid and d.isdeleted = 0)
LEFT JOIN [surveyrespondent] sr on (scli.surveyrespondentid = sr.objectid and sr.isdeleted = 0)
LEFT JOIN [checklistresponseset] clrs on (scli.checklistresponsesetid = clrs.objectid and clrs.isdeleted = 0)
LEFT JOIN [checklistresponse] clr on (clrs.objectid = clr.checklistresponsesetid and clr.isdeleted = 0)
LEFT JOIN [surveychecklistitemchecklistresponse] scliclr on (scliclr.surveychecklistitemid = scli.objectid and scliclr.checklistresponseid = clr.objectid)
WHERE
scli.isdeleted = 0
and scli.surveyrespondentid in
(select distinct
t1.surveyrespondentid
from
surveychecklistitem t1
left join surveyresponseto t2 on t1.surveyresponsetoid = t2.objectid
left join surveytrade t3 on t2.surveytradeid = t3.objectid
where
t1.isdeleted = 0
and t2.isdeleted = 0 and t3.isdeleted = 0
and t1.surveyplannerid in " + F_SurveyPlanner2IDs + @"
and t3.surveygroupid = '''+@SurveyGroupID+''')
and scli.surveyplannerid in " + F_SurveyPlanner2IDs + @"
and d.objectid = '''+@SurveyGroupID+'''
) p
GROUP BY p.stepnumber, p.question, p.tradename, p.contractid, p.evaluatedpartyname,p.totalrespondent, p.choicename,p.contractNumber,p.vendorName,p.plannerName
) q
) r
PIVOT
(
SUM(r.percentage)
FOR r.choicename IN
( '+
@cols +' )
) AS pvt
'
)
END
",
p1, p2, p3, p4, p5
);
if (dts.Tables.Count > 0)
{
DataTable dt = dts.Tables[0];
dt.Columns[0].ColumnName = "Step No.";
dt.Columns[1].ColumnName = "Question";
dt.Columns[2].ColumnName = "Trade";
dt.Columns[3].ColumnName = "Evaluated Party";
dt.Columns[4].ColumnName = "Contract Number";
dt.Columns[5].ColumnName = "Vendor Name";
dt.Columns[6].ColumnName = "Survey Planner Name";
return dt;
}
else
{
DataTable ErrorDT = new DataTable();
ErrorDT.Columns.Add("Error Message");
ErrorDT.Rows.Add(new Object[] { "Query returns zero table." });
return ErrorDT;
}
}