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