/// <summary>
/// Datatable for Report that return SurveyPlannerResultTotalScore
/// </summary>
/// <returns>DataTable</returns>
public static DataTable SurveyPlannerResultTotalScoreCCL(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 == "")
if (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
WHERE a.SurveyRespondentID = (select top 1 t1.surveyrespondentid
from
surveychecklistitem t1
where
t1.isdeleted = 0
and t1.surveyplannerid in " + F_SurveyPlannerIDs + @")
AND a.SurveyPlannerID in " + F_SurveyPlannerIDs + @"
ORDER BY a.stepnumber asc
IF (@cols is not null)
BEGIN
EXECUTE(N'SELECT
scli.objectname as question
, scli.objectid as surveychecklistitemid
, scli.checklistid
, sr.objectname as surveyrespondentname
, 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 [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
where
t1.isdeleted = 0
and t1.surveyplannerid in " + F_SurveyPlanner2IDs + @")
and scli.surveyplannerid in " + F_SurveyPlanner2IDs + @"
and s.Status <> 0
'
)
END
",
p2, p3, p4, p5
);
DataSet dts2 = Connection.ExecuteQuery("#database",
@"
SELECT a.objectname
FROM SurveyChecklistItem a
WHERE a.SurveyRespondentID = (select top 1 t1.surveyrespondentid
from
surveychecklistitem t1
where
t1.isdeleted = 0
and t1.surveyplannerid in " + F_SurveyPlannerIDs + @")
AND a.SurveyPlannerID in " + F_SurveyPlannerIDs + @"
ORDER BY a.stepnumber asc
");
if (dts.Tables.Count > 0)
{
DataTable t = dts.Tables[0];
DataTable dt = GetInversedDataTable(dts.Tables[0], "question", "surveyrespondentname", "score", "", false);
dt.Columns[0].ColumnName = "Respondent";
//dt.Columns.Add("Checklist ObjectID");
//dt.Columns.Add("Respondent");
//dt.Columns.Add("Survey Planner Name");
List<Guid?> checkListID = new List<Guid?>();
foreach (DataRow r in t.Rows)
{
OSurveyChecklistItem scli = TablesLogic.tSurveyChecklistItem[new Guid(r[1].ToString())];
//r["Checklist ObjectID"] = scli.ChecklistID.Value.ToString();
//r["Respondent"] = scli.SurveyRespondent.ObjectName;
//r["Survey Planner Name"] = scli.SurveyPlanner.ObjectName;
if (!checkListID.Contains(scli.ChecklistID.Value))
checkListID.Add(scli.ChecklistID.Value);
}
int TotalNumberOfQuestions = (dt.Columns.Count - 1);
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.In(checkListID)
,
TablesLogic.tChecklistItem.StepNumber.Asc
);
DataTable dt2 = dt.Clone();
foreach (OChecklistItem i in list)
{
if (i.ChecklistType == ChecklistItemType.Choice || i.ChecklistType == ChecklistItemType.MultipleSelections)
dt2.Columns[i.ObjectName].DataType = typeof(decimal);
}
//for (int i = 0; i < TotalNumberOfQuestions; i++)
//{
// if (((OChecklistItem)list[i]).ChecklistType == ChecklistItemType.Choice ||
// ((OChecklistItem)list[i]).ChecklistType == ChecklistItemType.MultipleSelections)
// dt2.Columns[1 + i].DataType = typeof(decimal);
//}
foreach (DataRow dr in dt.Rows)
{
dr["Total Score"] = 0M;
foreach (OChecklistItem i in list)
{
//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[1 + i] == DBNull.Value || dr[1 + i].ToString() == "" ? 0 : Convert.ToDecimal(dr[1 + i].ToString()));
//}
if (i.ChecklistType == ChecklistItemType.Choice ||
i.ChecklistType == ChecklistItemType.MultipleSelections)
dr["Total Score"] = (decimal)dr["Total Score"] + (dr[i.ObjectName] == DBNull.Value || dr[i.ObjectName].ToString() == "" ? 0 : Convert.ToDecimal(dr[i.ObjectName].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;
}
}
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>
/// 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>
/// for testing purpose: populate report filter value.
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable GetReportTemplates(ReportParameters parameters)
{
return TablesLogic.tReportTemplate.Select(
TablesLogic.tReportTemplate.ObjectID,
TablesLogic.tReportTemplate.ObjectName)
.Where
(parameters.GetList("REPORTID") == null ? Query.True :
TablesLogic.tReportTemplate.Report.ObjectID.In(parameters.GetList("REPORTID")))
.OrderBy(TablesLogic.tReportTemplate.ObjectName.Asc);
}
/// <summary>
/// For testing rdl report.
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable GetReportAndReportTemplate(ReportParameters parameters)
{
List<object> reportList = parameters.GetList("REPORTID");
DataTable dt = new DataTable();
dt.Columns.Add("ReportName");
dt.Columns.Add("ReportType");
dt.Columns.Add("TemplateName");
dt.Columns.Add("TemplateType");
dt.Columns.Add("TemplateFileName");
DataTable table = TablesLogic.tReport.Select(
TablesLogic.tReport.ReportName,
TablesLogic.tReport.ReportType,
TablesLogic.tReport.ReportTemplate.ObjectName.As("TemplateName"),
TablesLogic.tReport.ReportTemplate.TemplateType,
TablesLogic.tReport.ReportTemplate.ObjectNumber.As("TemplateFileName"))
.Where(
TablesLogic.tReport.ObjectID.In(reportList) &
TablesLogic.tReport.IsDeleted == 0)
.OrderBy(
TablesLogic.tReport.ReportName.Asc,
TablesLogic.tReport.ReportTemplate.ObjectName.Asc);
foreach (DataRow row in table.Rows)
dt.Rows.Add(new Object[] {
row["ReportName"].ToString(),
row["ReportType"].ToString(),
(row["TemplateName"] == null ? "" : row["TemplateName"].ToString()),
(row["TemplateType"] == null ? "" : row["TemplateType"].ToString()),
(row["TemplateFileName"] == null ? "" : row["TemplateFileName"].ToString())});
return dt;
}
/// <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;
}
}
