private void ShowEdit()
{
ArrayList paramList = new ArrayList();
string[] selectedLocations = SelectedTemplateLocations.Split(',');
pnlOverview.Visible = false;
pnlEdit.Visible = true;
//
// Open the data reader
//
paramList.Add(new SqlParameter("OccurrenceTypeID", Convert.ToInt32(Page.Request.Params["Type"])));
SqlDataReader reader = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(
"orgn_sp_get_locationByOccurrenceTypeID", paramList);
cblLocations.Items.Clear();
while (reader.Read())
{
String title, value;
bool enabled;
title = String.Format("{0} - {1}", reader["building_name"], reader["location_name"]);
value = reader["location_id"].ToString();
enabled = (selectedLocations.Contains(value) || selectedLocations.Length == 0);
cblLocations.Items.Add(new ListItem(title, value));
cblLocations.Items[cblLocations.Items.Count - 1].Selected = enabled;
}
}
public void dgLocation_Close(object sender, CommandEventArgs e)
{
StringBuilder sb = new StringBuilder();
//
// Run the function to get the active occurrences for this location.
//
SqlDataReader reader = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(
String.Format("SELECT * FROM cust_hdc_checkin_func_active_occurrencesByLocation({0})", e.CommandArgument));
while (reader.Read())
{
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(reader[0].ToString());
}
hfCloseOccurrenceIDs.Value = sb.ToString();
lbCloseError.Text = "";
pnlCloseOccurrence.Visible = true;
pnlDataFilter.Visible = false;
pnlLocationGrid.Visible = false;
pnlOccurrenceGrid.Visible = false;
pnlTotalAttendance.Visible = false;
dgClose_Bind();
}
void LoadCurrentOwners()
{
string currentValue = ddlCurrentOwner.SelectedValue;
ddlCurrentOwner.Items.Clear();
ddlCurrentOwner.Items.Add(new ListItem("", "-1"));
// Load Current Owners
string query = @"
SELECT DISTINCT
P.person_id,
P.last_name + ', ' + P.nick_name AS person_name
FROM core_profile T
LEFT OUTER JOIN evnt_event_profile E on E.profile_id = T.profile_id
INNER JOIN core_person P ON P.person_id = T.owner_id
WHERE (T.profile_type in (1,2) OR
(T.profile_type = 4 and E.[end] > getdate())
)
ORDER BY person_name";
SqlDataReader rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(query);
while (rdr.Read())
{
ListItem li = new ListItem(rdr["person_name"].ToString(), rdr["person_id"].ToString());
li.Selected = li.Value == currentValue;
ddlCurrentOwner.Items.Add(li);
}
rdr.Close();
LoadTags(ddlCurrentOwner.SelectedValue);
}
public override bool PerformAction(Assignment assignment, Person currentPerson)
{
try
{
ArrayList lst = new ArrayList();
lst.Add(new SqlParameter("@AssignmentId", assignment != null ? assignment.AssignmentId : -1));
lst.Add(new SqlParameter("@PersonId", currentPerson != null ? currentPerson.PersonID : -1));
bool result = true;
SqlDataReader rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(StoredProcSetting, lst);
if (rdr.Read())
{
try { result = (bool)rdr["result"]; }
catch { }
}
rdr.Close();
if (result)
{
// Because the Assignment Object's ProcessState method saves the assignment object before reading any
// changes that this action may have made, every property on the passed in object should be updated
// prior to returning (since we don't really know what properties the SQL Proc may have updated)
Assignment newAssignment = new Assignment(assignment.AssignmentId);
assignment.Description = newAssignment.Description;
assignment.DueDate = newAssignment.DueDate;
assignment.FieldValues = newAssignment.FieldValues;
assignment.PriorityId = newAssignment.PriorityId;
assignment.RequesterPersonId = newAssignment.RequesterPersonId;
assignment.ResolutionText = newAssignment.ResolutionText;
assignment.ResolvedDate = newAssignment.ResolvedDate;
assignment.StateId = newAssignment.StateId;
assignment.Title = newAssignment.Title;
assignment.WorkerPersonId = newAssignment.WorkerPersonId;
}
return(result);
}
catch (System.Exception ex)
{
assignment.AddNote("Exception", ex.Message, false, null, "ExecuteSQLProc");
return(false);
}
}
void LoadTags(string ownerId)
{
ddlTag.Items.Clear();
if (ownerId != "" && ownerId != "-1")
{
// Load Tags
string query = @"
SELECT DISTINCT
T.profile_id,
T.profile_type,
dbo.cust_ccv_profile_path(T.profile_id) as profile_path
FROM core_profile T
LEFT OUTER JOIN evnt_event_profile E on E.profile_id = T.profile_id
WHERE (T.profile_type in (1,2) OR
(T.profile_type = 4 and E.[end] > getdate())
)
AND T.owner_id = " + ownerId + @"
ORDER BY T.profile_type, profile_path";
SqlDataReader rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(query);
while (rdr.Read())
{
string profileName = rdr["profile_path"].ToString();
switch ((int)rdr["profile_type"])
{
case 1: profileName = "[Ministry] " + profileName; break;
case 2: profileName = "[Serving] " + profileName; break;
case 4: profileName = "[Event] " + profileName; break;
}
ddlTag.Items.Add(new ListItem(profileName, rdr["profile_id"].ToString()));
}
rdr.Close();
}
}
protected void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
// Load Staff Names
ddlNewOwner.Items.Add(new ListItem("", "-1"));
string query = @"
SELECT DISTINCT
P.person_id,
P.last_name + ', ' + P.nick_name AS person_name
FROM core_person P
WHERE P.staff_member = 1
ORDER BY person_name";
SqlDataReader rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(query);
while (rdr.Read())
{
ddlNewOwner.Items.Add(new ListItem(rdr["person_name"].ToString(), rdr["person_id"].ToString()));
}
rdr.Close();
LoadCurrentOwners();
}
}
private DataTable GetOverviewData()
{
Hashtable table;
StringBuilder sb;
Dictionary <String, Hashtable> overview = new Dictionary <String, Hashtable>();
ArrayList paramList = new ArrayList(), ids;
//
// Open the data reader
//
paramList.Add(new SqlParameter("TypeID", Convert.ToInt32(Page.Request.Params["Type"])));
SqlDataReader reader = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(
"cust_hdc_checkin_sp_templateLocationsByTypeID", paramList);
while (reader.Read())
{
if (overview.ContainsKey(reader["occurrence_type_template_id"].ToString()) == false)
{
table = new Hashtable();
overview.Add(reader["occurrence_type_template_id"].ToString(), table);
table["template_id"] = reader["occurrence_type_template_id"];
table["location_names"] = new StringBuilder();
table["location_ids"] = new ArrayList();
table["occurrence_freq_type"] = reader["occurrence_freq_type"];
table["freq_qualifier"] = reader["freq_qualifier"];
table["start_time"] = reader["start_time"];
table["check_in_start"] = reader["check_in_start"];
table["schedule_name"] = reader["schedule_name"];
}
else
{
table = overview[reader["occurrence_type_template_id"].ToString()];
}
sb = (StringBuilder)table["location_names"];
ids = (ArrayList)table["location_ids"];
if (sb.Length == 0)
{
sb.Append(reader["location_name"]);
}
else
{
sb.AppendFormat(",{0}", reader["location_name"]);
}
ids.Add(reader["location_id"].ToString());
}
DataTable dt = new DataTable("hdc_customTable");
dt.Columns.Add("template_id");
dt.Columns.Add("location_names");
dt.Columns.Add("location_ids");
dt.Columns.Add("occurrence_freq_type");
dt.Columns.Add("occurrence_freq_type_name");
dt.Columns.Add("freq_qualifier");
dt.Columns.Add("freq_qualifier_name");
dt.Columns.Add("start_time");
dt.Columns.Add("check_in_start");
dt.Columns.Add("schedule_name");
foreach (KeyValuePair <String, Hashtable> kvp in overview)
{
DataRow row = dt.NewRow();
table = (Hashtable)kvp.Value;
row["template_id"] = table["template_id"];
row["location_names"] = table["location_names"].ToString();
row["location_ids"] = String.Join(",", (string[])((ArrayList)table["location_ids"]).ToArray(typeof(string)));
row["occurrence_freq_type"] = table["occurrence_freq_type"];
row["occurrence_freq_type_name"] = "";
row["freq_qualifier"] = table["freq_qualifier"];
row["freq_qualifier_name"] = "";
row["start_time"] = table["start_time"];
row["check_in_start"] = table["check_in_start"];
row["schedule_name"] = table["schedule_name"];
if (row["occurrence_freq_type"].ToString() == "1")
{
row["occurrence_freq_type_name"] = "Weekly";
if (row["freq_qualifier"].ToString() == "0")
{
row["freq_qualifier_name"] = "Sunday";
}
else if (row["freq_qualifier"].ToString() == "1")
{
row["freq_qualifier_name"] = "Monday";
}
else if (row["freq_qualifier"].ToString() == "2")
{
row["freq_qualifier_name"] = "Tuesday";
}
else if (row["freq_qualifier"].ToString() == "3")
{
row["freq_qualifier_name"] = "Wednesday";
}
else if (row["freq_qualifier"].ToString() == "4")
{
row["freq_qualifier_name"] = "Thursday";
}
else if (row["freq_qualifier"].ToString() == "5")
{
row["freq_qualifier_name"] = "Friday";
}
else if (row["freq_qualifier"].ToString() == "6")
{
row["freq_qualifier_name"] = "Saturday";
}
}
dt.Rows.Add(row);
}
return(dt);
}
private void ShowView()
{
Address address = new Address(Int32.Parse(AddressIDSetting));
phMap.Controls.Clear();
Page.ClientScript.RegisterStartupScript(typeof(string), "VirtualEarth", "<script src=\"http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=5\"></script>", false);
System.Web.UI.WebControls.Panel pnlMap = new System.Web.UI.WebControls.Panel();
pnlMap.ID = "pnlMap";
pnlMap.Style.Add("position", "relative");
pnlMap.Style.Add("width", MapWidthSetting + "px");
pnlMap.Style.Add("height", MapHeightSetting + "px");
phMap.Controls.Add(pnlMap);
StringBuilder sbVEScript = new StringBuilder();
sbVEScript.Append("var map = null;\n");
sbVEScript.Append("window.onload = function() {LoadMyMap();};\n");
sbVEScript.Append("\nfunction LoadMyMap(){\n");
sbVEScript.AppendFormat("\tmap = new VEMap('{0}');\n", pnlMap.ClientID);
sbVEScript.Append("\tmap.LoadMap();\n\n");
sbVEScript.Append("\tmap.AttachEvent('onclick', mapClick);\n\n");
//sbVEScript.Append("\tmap.ClearInfoBoxStyles();\n\n");
//sbVEScript.Append("\tvar points = new Array(\n");
//for (int i = 0; i < Area.Coordinates.Count; i++)
//{
// AreaCoordinate coord = Area.Coordinates[i];
// sbVEScript.AppendFormat("\t\tnew VELatLong({0}, {1})", coord.Latitude.ToString(), coord.Longitude.ToString());
// if (i < Area.Coordinates.Count - 1)
// sbVEScript.Append(",\n");
//}
//sbVEScript.Append("\n\t);\n");
//sbVEScript.Append("\tvar shape = new VEShape(VEShapeType.Polygon, points);\n");
//sbVEScript.Append("\tmap.SetMapView(points);\n");
//sbVEScript.Append("\tshape.SetLineColor(new VEColor(255,0,0,1));\n");
//sbVEScript.Append("\tshape.SetLineWidth(2);\n");
//sbVEScript.Append("\tshape.SetFillColor(new VEColor(236,183,49,.3));\n");
//sbVEScript.Append("\tshape.HideIcon();\n");
//sbVEScript.AppendFormat("\tshape.SetTitle('{0}');\n", Area.Name);
//sbVEScript.Append("\n\tmap.AddShape(shape);\n");
sbVEScript.AppendFormat("\n\tshape = new VEShape(VEShapeType.Pushpin, new VELatLong({0}, {1}));\n",
address.Latitude.ToString(),
address.Longitude.ToString());
sbVEScript.Append("\tshape.SetCustomIcon('images/map/pin_blue.png');\n");
sbVEScript.Append("\tshape.SetTitle(\"Center\");\n");
sbVEScript.Append("\tmap.AddShape(shape);\n");
double maxLatitude = double.MinValue;
double maxLongitude = double.MinValue;
double minLatitude = double.MinValue;
double minLongitude = double.MinValue;
ArrayList lst = new ArrayList();
lst.Add(new SqlParameter("@TargetAddressID", address.AddressID));
SqlDataReader rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader("cust_sp_target_ccv_location_members", lst);
while (rdr.Read())
{
double latitude = (double)rdr["Latitude"];
double longitude = (double)rdr["Longitude"];
if (maxLatitude == double.MinValue || maxLatitude < latitude)
{
maxLatitude = latitude;
}
if (maxLongitude == double.MinValue || maxLongitude < longitude)
{
maxLongitude = longitude;
}
if (minLatitude == double.MinValue || minLatitude > latitude)
{
minLatitude = latitude;
}
if (minLongitude == double.MinValue || minLongitude > longitude)
{
minLongitude = longitude;
}
sbVEScript.AppendFormat("\n\tshape = new VEShape(VEShapeType.Pushpin, new VELatLong({0}, {1}));\n",
latitude.ToString(),
longitude.ToString());
sbVEScript.AppendFormat("\tshape.SetCustomIcon('images/map/{0}');\n",
rdr["pin_icon"].ToString());
sbVEScript.AppendFormat("\tshape.SetTitle(\"{0}\");\n", rdr["family_name"].ToString());
sbVEScript.AppendFormat("\tshape.SetDescription('{0}');\n", BuildDetailPanel(rdr));
sbVEScript.AppendFormat("\tshape.SetMoreInfoURL('default.aspx?page=7&guid={0}');\n", rdr["person_guid"].ToString());
sbVEScript.Append("\tmap.AddShape(shape);\n");
}
rdr.Close();
sbVEScript.Append("\tvar allPoints = new Array(\n");
sbVEScript.AppendFormat("\t\tnew VELatLong({0}, {1}),\n", minLatitude.ToString(), minLongitude.ToString());
sbVEScript.AppendFormat("\t\tnew VELatLong({0}, {1}))\n", maxLatitude.ToString(), maxLongitude.ToString());
sbVEScript.Append("\tmap.SetMapView(allPoints);\n");
//sbVEScript.Append("\tmap.ZoomIn();\n");
sbVEScript.Append("}\n");
sbVEScript.Append("\nfunction mapClick(e){\n");
sbVEScript.Append("\tvar shape = map.GetShapeByID(e.elementID);\n");
sbVEScript.Append("\twindow.location = shape.GetMoreInfoURL();\n");
sbVEScript.Append("}\n");
Page.ClientScript.RegisterStartupScript(typeof(string), "LoadMap", sbVEScript.ToString(), true);
}
/// <summary>
/// Retrieve all the CDR records that have not been loaded into the phone_cdr table
/// yet. This method is called by the ArenaPbxCdr Agent.
/// </summary>
/// <returns>A collection of CDR records that are new since the last run.</returns>
public CDRCollection GetCDRRecords()
{
CDRCollection cdrRecords = new CDRCollection();
SqlDataReader rdr;
//
// Execute the stored procedure. The stored procedure merges the two tables
// (the phone_cdr table in Arena and the cdr table in Asterisk) and returns
// a reader with only the CDR records from Asterisk that do not exist in Arena.
//
rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader("cust_asterisk_sp_get_cdr_records");
while (rdr.Read())
{
CDR cdr = new CDR();
//
// If the source channel is a SIP device (we do not yet use IAX) then
// we need to strip out just the device name (extension number). Asterisk
// provides this in a "SIP/268-293fab239" format.
//
string srcChannel = rdr["channel"].ToString();
if (srcChannel.ToUpper().StartsWith("SIP/"))
{
cdr.Source = srcChannel.Substring(4);
cdr.Source = cdr.Source.Substring(0, cdr.Source.IndexOf('-'));
}
else
{
cdr.Source = rdr["src"].ToString();
}
//
// If the destination channel is a SIP device (we do not yet use IAX) then
// we need to strip out just the device name (extension number). Asterisk
// provides this in a "SIP/268-293fab239" format.
//
string dstChannel = rdr["dstchannel"].ToString();
if (dstChannel.ToUpper().StartsWith("SIP/"))
{
cdr.Destination = dstChannel.Substring(4);
cdr.Destination = cdr.Destination.Substring(0, cdr.Destination.IndexOf('-'));
}
else
{
cdr.Destination = rdr["dst"].ToString();
}
//
// If the destination begins is 7 or more characters and does not begin with
// a 9, then prepend the 9. Some of our phone calls have the 9, some do not.
// Make sure they are all the same.
// Next if it is a long distance call (e.g. 917605552732) strip out the 1 since
// Arena does not use it.
//
if (cdr.Destination.Length >= 7 && cdr.Destination[0] != '9')
{
cdr.Destination = "9" + cdr.Destination; // Prepend a 9 for outward calls that don't have it.
}
if (cdr.Destination.Length > 7 && cdr.Destination.Substring(0, 2) == "91")
{
cdr.Destination = "9" + cdr.Destination.Substring(2); // Strip out the 1 for long distance
}
//
// Get the CallerID as identified by Asterisk.
//
cdr.CallerID = rdr["clid"].ToString();
//
// Get the time the call began.
//
if (!rdr.IsDBNull(rdr.GetOrdinal("start")))
{
cdr.CallStart = (DateTime)rdr["start"];
}
//
// Get the time the call was answered (our system does not use this so the
// stored procedure sets this to null).
//
if (!rdr.IsDBNull(rdr.GetOrdinal("answer")))
{
cdr.Answered = (DateTime)rdr["answer"];
}
//
// Get the time the call was ended.
//
if (!rdr.IsDBNull(rdr.GetOrdinal("end")))
{
cdr.CallEnd = (DateTime)rdr["end"];
}
//
// Get the duration of the call. As of Asterisk 1.6 the duration and billable
// seconds is now a floating point, so it might return 129.4 seconds. Convert
// to a whole number.
//
if (!rdr.IsDBNull(rdr.GetOrdinal("duration")))
{
cdr.Duration = Convert.ToInt32(rdr["duration"]);
}
//
// Get the billable duration of the call.
//
if (!rdr.IsDBNull(rdr.GetOrdinal("billsec")))
{
cdr.BillSeconds = Convert.ToInt32(rdr["billsec"]);
}
//
// The disposition is the "state" of the call.
//
if (!rdr.IsDBNull(rdr.GetOrdinal("disposition")))
{
switch (rdr["disposition"].ToString())
{
case "ANSWERED": cdr.Disposition = CDR_Disposition.Answered; break;
case "NO ANSWER": cdr.Disposition = CDR_Disposition.No_Answer; break;
case "BUSY": cdr.Disposition = CDR_Disposition.Busy; break;
case "FAILED": cdr.Disposition = CDR_Disposition.Failed; break;
}
}
//
// Save the foreign key so that our stored procedure can find the
// original CDR record later.
//
cdr.ForeignKey = rdr["uniqueid"].ToString();
//
// Add the CDR record to the collection. The agent worker will then match
// the CDR record to the person records associated with the call.
//
cdrRecords.Add(cdr);
}
rdr.Close();
return(cdrRecords);
}
