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;
}
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 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>
/// 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;
}