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);
}
}
/// <summary>
/// Load a list of person placemark objects from the given report ID. The
/// list is constrained to the start and count parameters.
/// </summary>
/// <param name="reportid">The Arena Report to generate a list of people from.</param>
/// <param name="start">The member index to start loading from.</param>
/// <param name="count">The maximum number of people to load, pass Int32.MaxValue for complete load.</param>
/// <returns>A list of PersonPlacemark objects.</returns>
public List <PersonPlacemark> PersonPlacemarksInReport(int reportid, int start, int count)
{
List <PersonPlacemark> people = new List <PersonPlacemark>();
SqlDataReader rdr;
ListReport report;
if (PersonFieldOperationAllowed(PersonFields.Profile_Name, OperationType.View) == false)
{
return(people);
}
report = new ListReport(reportid);
rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(report.Query);
people = PersonPlacemarksFromReader(rdr, start, count);
rdr.Close();
return(people);
}
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 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);
}
