public DataTable GetProfileList(int OrganisationID)
{
//string connectionString = ConfigurationSettings.AppSettings["ConnectionString"] + "password="******"password"] + ";";
//string strSQL = @"select ProfileID, ProfileName from tblProfile where OrganisationID=" + OrganisationID;
//DataTable dtProfileList = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, strSQL).Tables[0];
//return dtProfileList;
using (StoredProcedure sp = new StoredProcedure("prcEvent_GetProfileList",
StoredProcedure.CreateInputParam("@OrganisationID", SqlDbType.Int, OrganisationID)
))
{
DataTable dt = sp.ExecuteTable();
return(dt);
}
}
} //GetEmailReportDistinctUsers
/// <summary>
/// This method returns a datatable of users and their email addresses based on an organisation
/// ID and a comma seperated list of user id's
/// </summary>
/// <param name="organisationID">A single Organisation ID</param>
/// <param name="userIDs">A comma seperated list of user ID's</param>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Peter Kneale 1/03/2004
/// Changes:
/// </remarks>
/// <returns>DataTable containing user details and email addresses</returns>
public DataTable GetEmailReportToAdministrators(int organisationID, string unitIDs, string courseIDs, int classificationID, int courseModuleStatus, DateTime quizDateFrom, DateTime quizDateTo, int inclInactive)
{
using (StoredProcedure sp = new StoredProcedure("prcReport_EmailReportToAdministrators",
StoredProcedure.CreateInputParam("@organisationID", SqlDbType.Int, organisationID),
StoredProcedure.CreateInputParam("@unitIDs", SqlDbType.VarChar, -1, unitIDs),
StoredProcedure.CreateInputParam("@courseIDs", SqlDbType.VarChar, 8000, courseIDs),
StoredProcedure.CreateInputParam("@classificationID", SqlDbType.Int, classificationID),
StoredProcedure.CreateInputParam("@courseModuleStatus", SqlDbType.Int, courseModuleStatus),
StoredProcedure.CreateInputParam("@quizDateFrom", SqlDbType.DateTime, quizDateFrom),
StoredProcedure.CreateInputParam("@quizDateTo", SqlDbType.DateTime, quizDateTo),
StoredProcedure.CreateInputParam("@includeInactive", SqlDbType.Int, inclInactive)
))
{
return(sp.ExecuteTable());
}
} //GetEmailReportToAdministrators
} // GetUsersAssignedToPolicy
public DataTable GetUserAndPoliciesForAdmins(string AdminIDs, string PolicyIDs, string UnitIDs, string strAccepted, DateTime acceptedDateFrom, DateTime acceptedDateTo, int organisationID)
{
using (StoredProcedure sp = new StoredProcedure("prcPolicy_GetUserAndPoliciesForAdmins",
StoredProcedure.CreateInputParam("@admin_ids", SqlDbType.VarChar, 8000, AdminIDs),
StoredProcedure.CreateInputParam("@policy_ids", SqlDbType.VarChar, 8000, PolicyIDs),
StoredProcedure.CreateInputParam("@unit_ids", SqlDbType.VarChar, -1, UnitIDs),
StoredProcedure.CreateInputParam("@accepted", SqlDbType.VarChar, 20, strAccepted),
StoredProcedure.CreateInputParam("@acceptedDateFrom", SqlDbType.DateTime, acceptedDateFrom),
StoredProcedure.CreateInputParam("@acceptedDateTo", SqlDbType.DateTime, acceptedDateTo),
StoredProcedure.CreateInputParam("@OrgID", SqlDbType.Int, 8000, organisationID)
))
{
return(sp.ExecuteTable());
}
}// GetUserAndPoliciesForAdmins
private void LoadData()
{
DataTable dtTZDaylight;
using (StoredProcedure sp = new StoredProcedure("prcGetTimezoneDaylightRules",
StoredProcedure.CreateInputParam("@TimezoneId", SqlDbType.Int, this.tzId),
StoredProcedure.CreateInputParam("@RuleID", SqlDbType.Int, this.ruleId)))
{
dtTZDaylight = sp.ExecuteTable();
}
if (dtTZDaylight.Rows.Count > 0)
{
this.txtTimezone.Text = ViewState["WrittenName"].ToString();
this.txtFirstYearStart.Text = dtTZDaylight.Rows[0]["StartYear"].ToString();
this.txtLastYearStart.Text = dtTZDaylight.Rows[0]["EndYear"].ToString();
int offset;
int timeStart;
int weekdayStart;
int weekStart;
int monthStart;
int timeEnd;
int weekdayEnd;
int weekEnd;
int monthEnd;
Int32.TryParse(dtTZDaylight.Rows[0]["Offset"].ToString(), out offset);
this.txtOffset.Text = ConvertMinstoString(offset);
Int32.TryParse(dtTZDaylight.Rows[0]["TimeStart"].ToString(), out timeStart);
this.txtTimeStart.Text = ConvertMinstoString(timeStart);
Int32.TryParse(dtTZDaylight.Rows[0]["WeekdayStart"].ToString(), out weekdayStart);
this.drpWeekdayStart.SelectedValue = weekdayStart.ToString();
Int32.TryParse(dtTZDaylight.Rows[0]["WeekStart"].ToString(), out weekStart);
this.drpWeekStart.SelectedValue = weekStart.ToString();
Int32.TryParse(dtTZDaylight.Rows[0]["MonthStart"].ToString(), out monthStart);
this.drpMonthStart.SelectedValue = monthStart.ToString();
Int32.TryParse(dtTZDaylight.Rows[0]["TimeEnd"].ToString(), out timeEnd);
this.txtTimeEnd.Text = ConvertMinstoString(timeEnd);
Int32.TryParse(dtTZDaylight.Rows[0]["WeekdayEnd"].ToString(), out weekdayEnd);
this.drpWeekdayEnd.SelectedValue = weekdayEnd.ToString();
Int32.TryParse(dtTZDaylight.Rows[0]["WeekEnd"].ToString(), out weekEnd);
this.drpWeekEnd.SelectedValue = weekEnd.ToString();
Int32.TryParse(dtTZDaylight.Rows[0]["MonthEnd"].ToString(), out monthEnd);
this.drpMonthEnd.SelectedValue = monthEnd.ToString();
}
}
} // LoadData
private void LoadTimeZone()
{
List <string> tzList = new List <string>();
DataTable dtTzTable = new DataTable();
using (StoredProcedure sp = new StoredProcedure("prcTimeZone_GetList"))
{
dtTzTable = sp.ExecuteTable();
}
tzList = (from tzRow in dtTzTable.AsEnumerable()
select tzRow.Field <string>("FLB_Name")).ToList();
listTimeZone.DataSource = tzList;
listTimeZone.DataBind();
int selectedIndex = tzList.FindIndex(delegate(string s) { return(s.Contains("(UTC+10:00) Canberra, Melbourne, Sydney")); });
listTimeZone.SelectedValue = listTimeZone.Items[selectedIndex].Value;
}
} // GetUserMashup
public DataTable GetAdminMashup(int organisationID, string unitIDs, string courseIDs, string userCourseCsv, string adminIDs, int classificationID, int courseModuleStatus, DateTime quizDateFrom, DateTime quizDateTo, int inclInactive)
{
using (StoredProcedure sp = new StoredProcedure("prcCourse_AdminMashup",
StoredProcedure.CreateInputParam("@organisationID", SqlDbType.Int, organisationID),
StoredProcedure.CreateInputParam("@unitIDs", SqlDbType.VarChar, 8000, unitIDs),
StoredProcedure.CreateInputParam("@courseIDs", SqlDbType.VarChar, 8000, courseIDs),
StoredProcedure.CreateInputParam("@input_csv", SqlDbType.VarChar, 8000, userCourseCsv),
StoredProcedure.CreateInputParam("@adminids", SqlDbType.VarChar, 8000, adminIDs),
StoredProcedure.CreateInputParam("@classificationID", SqlDbType.Int, classificationID),
StoredProcedure.CreateInputParam("@courseModuleStatus", SqlDbType.Int, courseModuleStatus),
StoredProcedure.CreateInputParam("@quizDateFrom", SqlDbType.DateTime, quizDateFrom),
StoredProcedure.CreateInputParam("@quizDateTo", SqlDbType.DateTime, quizDateTo),
StoredProcedure.CreateInputParam("@includeInactive", SqlDbType.Int, inclInactive)
))
{
return(sp.ExecuteTable());
}
} // GetUserMashup
} // GetUsersAssignedToPolicy
public DataTable GetAdminMashup(int organisationID, string unitIDs, string policyIDs, string userPolicyCsv, string adminIDs, int classificationID, int PolicyStatus, DateTime acceptedDateFrom, DateTime acceptedDateTo, int inclInactive)
{
using (StoredProcedure sp = new StoredProcedure("prcPolicy_AdminMashup",
StoredProcedure.CreateInputParam("@organisationID", SqlDbType.Int, organisationID),
StoredProcedure.CreateInputParam("@unitIDs", SqlDbType.VarChar, -1, unitIDs),
StoredProcedure.CreateInputParam("@policyIDs", SqlDbType.VarChar, 8000, policyIDs),
StoredProcedure.CreateInputParam("@input_csv", SqlDbType.VarChar, 8000, userPolicyCsv),
StoredProcedure.CreateInputParam("@adminids", SqlDbType.VarChar, 8000, adminIDs),
StoredProcedure.CreateInputParam("@classificationID", SqlDbType.Int, classificationID),
StoredProcedure.CreateInputParam("@policyStatus", SqlDbType.Int, PolicyStatus),
StoredProcedure.CreateInputParam("@acceptedDateFrom", SqlDbType.DateTime, acceptedDateFrom),
StoredProcedure.CreateInputParam("@acceptedDateTo", SqlDbType.DateTime, acceptedDateTo),
StoredProcedure.CreateInputParam("@includeInactive", SqlDbType.Int, inclInactive)
))
{
return(sp.ExecuteTable());
}
} // GetUserMashup
public DataTable PolicyUserSearch(int OrganisationID, int PolicyID, string ParentUnitIDs, string FirstName, string LastName, string UserName, string Email, string ExternalID, int UserID, string DisplayType)
{
using (StoredProcedure spPolicyUserSearch = new StoredProcedure("prcPolicy_UserSearch",
StoredProcedure.CreateInputParam("@organisationID", SqlDbType.Int, OrganisationID),
StoredProcedure.CreateInputParam("@PolicyID", SqlDbType.Int, PolicyID),
StoredProcedure.CreateInputParam("@parentUnitIDs", SqlDbType.NVarChar, ParentUnitIDs),
StoredProcedure.CreateInputParam("@firstName", SqlDbType.NVarChar, FirstName),
StoredProcedure.CreateInputParam("@lastName", SqlDbType.NVarChar, LastName),
StoredProcedure.CreateInputParam("@userName", SqlDbType.NVarChar, UserName),
StoredProcedure.CreateInputParam("@Email", SqlDbType.NVarChar, Email),
StoredProcedure.CreateInputParam("@ExternalID", SqlDbType.NVarChar, ExternalID),
StoredProcedure.CreateInputParam("@adminUserID", SqlDbType.NVarChar, UserID),
StoredProcedure.CreateInputParam("@Type", SqlDbType.NVarChar, DisplayType)))
{
DataTable dtPolicyUserSearch = spPolicyUserSearch.ExecuteTable();
return(dtPolicyUserSearch);
}
}
public void ExportDataSet(string SP_Name, string Param1, string Param2, string Param3, string CurrentCultureName, ExportType FileType, String FileName)
{
using (StoredProcedure sp = new StoredProcedure(SP_Name,
StoredProcedure.CreateInputParam("@Param1", SqlDbType.NVarChar, 4000, Param1),
StoredProcedure.CreateInputParam("@Param2", SqlDbType.NVarChar, 4000, Param2),
StoredProcedure.CreateInputParam("@Param3", SqlDbType.NVarChar, 4000, Param3),
StoredProcedure.CreateInputParam("@CurrentCultureName", SqlDbType.NVarChar, 4000, CurrentCultureName)
))
{
DataTable dt = sp.ExecuteTable();
System.Text.StringBuilder builder = new System.Text.StringBuilder();
if (FileType == ExportType.CSV)
{
using (StoredProcedure sph = new StoredProcedure("prcGridExport_Headers",
StoredProcedure.CreateInputParam("@LangInterfaceName", SqlDbType.NVarChar, 4000, "/Reporting/PeriodicReport.aspx"),
StoredProcedure.CreateInputParam("@CurrentCultureName", SqlDbType.NVarChar, 40, CurrentCultureName)
))
{
DataTable dth = sph.ExecuteTable();
string headerToExport = string.Empty;
foreach (DataColumn col in dth.Columns)
{
headerToExport += (char)34 + dth.Rows[0][col].ToString() + (char)34 + (char)44;
}
headerToExport.Remove(headerToExport.Length - 1, 1);
headerToExport = headerToExport + Environment.NewLine + Environment.NewLine;
builder.Append(headerToExport);
}
string body = string.Empty;
foreach (DataRow row in dt.Rows)
{
foreach (object obj in row.ItemArray)
{
body = body + obj.ToString() + (char)44;
}
body.Remove(body.Length - 1, 1);
body = body + Environment.NewLine;
}
builder.Append(body);
builder.Append(Environment.NewLine);
builder.Append(Environment.NewLine);
Response.ContentType = "application/ms-excel;charset=UTF-8;";
// Response.ContentType = "application/text; charset=UTF-8";
// Response.ContentType = "application/text; charset=Unicode";
// Response.ContentEncoding = new UnicodeEncoding();
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Response.ContentType = "application/ms-word";
string extension = ".CSV";
Response.AddHeader("content-disposition", "attachment;filename=" + FileName + extension);
Response.Charset = "UTF-8";
Response.Write('\uFEFF');
Response.Write(builder.ToString());
Response.Flush();
Response.End();
}
if (FileType == ExportType.Excel)
{
//Create a dummy GridView
GridView GridView1 = new GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = dt;
GridView1.DataBind();
Response.Clear();
Response.Buffer = true;
string extension = ".XLS";
Response.AddHeader("content-disposition", "attachment;filename=" + FileName + extension);
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
for (int i = 0; i < GridView1.Rows.Count; i++)
{
//Apply text style to each Row
GridView1.Rows[i].Attributes.Add("class", "textmode");
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
}
