public static Ticket GetPICInBillingTicket(string picid, int ticketID)
{
List<Ticket> data = new List<Ticket>();
foreach (dynamic item in Risque.Utility.SqlHelper.getSqlData(@"
SELECT TOP 1 t.*
FROM Tickets.dbo.LineItems as li join Tickets.dbo.Tickets as t
ON li.TicketID = t.ID
WHERE t.StatusID = 5 and t.ID != " + ticketID + " and li.PICID = '" + picid + "'"))
{
Ticket ticket = new Ticket();
Risque.Utility.SqlHelper.Map<Ticket>(item, ticket);
data.Add(ticket);
}
return data.Count == 0 ? null : data[0];
}
public static Ticket GetPICInActiveTicket(string picid)
{
List<Ticket> data = new List<Ticket>();
foreach (dynamic item in Risque.Utility.SqlHelper.getSqlData("SELECT TOP 1 t.* " +
"FROM Tickets.dbo.LineItems AS li " +
"JOIN Tickets.dbo.Tickets AS t " +
"ON li.TicketID = t.ID " +
"WHERE t.StatusID in (1, 2, 3, 4) AND " +
"li.PICID = '" + picid + "'")) {
Ticket ticket = new Ticket();
Risque.Utility.SqlHelper.Map<Ticket>(item, ticket);
data.Add(ticket);
}
return data.Count == 0 ? null : data[0];
}
/// <summary>
/// Determine where the ticket should go based on the lineitems biling information
/// </summary>
/// <param name="ticket">Current data ticket</param>
/// <param name="accountChangeOnly"></param>
public static void RouteTicket(Ticket ticket, bool accountChangeOnly)
{
bool isStandard = false;
bool isPremium = false;
bool premiumBreak = false; // Condition to break out of the loop
/* If plan has changed from 'Premium' to 'Standard' --> Send to billing
* -- Activate: Does not have a previous billing plan id. Only check if it is standard or premium
* -- Modify: Does have a PreviousBillingPlanID --> Compare the PreviousBillingPlanID to the BillingPlanID ( the one being assinged)
* If the previous plan was premium and the plan being assigned is standard send to billing.
* -- Deactivate: If the LineItem was premium --> Send to Billing.
* -- Repair: Billing should not be changed. Complete the ticket, don't send to billing.
* -- Final Note: If any one lineitem has a premium billing plan the entire ticket is sent to billing.
*/
int arrayLength = ticket.LineItems.Count;
for (var i = 0; i < arrayLength; i++)
{
switch (ticket.LineItems[i].ActionID)
{
case 1: // Activate
{
if (ticket.LineItems[i].BillingPlanID != 16)
{
isPremium = true;
isStandard = false;
premiumBreak = true;
}
else
isStandard = true;
break;
}
case 2: // Modify
{
if (ticket.LineItems[i].PreviousBillingPlanID != 16)
{
isPremium = true;
isStandard = false;
premiumBreak = true;
}
else if (ticket.LineItems[i].BillingPlanID != 16)
{
isPremium = true;
premiumBreak = true;
}
else
isStandard = true;
break;
}
case 3: // Deactivate
{
if (ticket.LineItems[i].BillingPlanID != 16)
{
isPremium = true;
isStandard = false;
premiumBreak = true;
}
else
isStandard = true;
break;
}
case 4: // Repair
{
premiumBreak = true; // Break loop -> Only one lineitem exists with a repair ticket.
isStandard = true; // Repair line items will never get routed to billing.
SetTicketStatus(ticket.Identification, 6);
TicketsMailer.SendMail_TicketApproved(ticket);
//Alert.ShowSuccess("The ticket has been approved and completed.", BrokenRulesDisplay);
break;
}
}
if (premiumBreak)
break;
}
if (isStandard)
{
if (accountChangeOnly) //--Update Interface Account Information if the Ticket is an Account Only Change
{
int arrIndex = ticket.LineItems.Count;
for (var i = 0; i < arrIndex; i++)
{
var currentInterface = Interface.GetByIdentification(ticket.LineItems[i].InterfaceID);
LineItem.SetBilling(ticket.LineItems[i], currentInterface);
}
}
SetTicketStatus(ticket.Identification, 6);
TicketsMailer.SendMail_TicketApproved(ticket);
//Alert.ShowSuccess("The ticket has been approved and completed.", BrokenRulesDisplay);
}
else if (isPremium)
{
/*
* The account number only change. The account has either changed from premium to standard or vice versa
* Create interface object and update the line items.
* If any one lineitem on a ticket is not an account only change it should not be here. Something went wrong.
*/
if (accountChangeOnly) //--Update Interface Account Information if the Ticket is an Account Only Change
{
int arrIndex = ticket.LineItems.Count;
for (var i = 0; i < arrIndex; i++)
{
var currentInterface = Interface.GetByIdentification(ticket.LineItems[i].InterfaceID);
LineItem.SetBilling(ticket.LineItems[i], currentInterface);
}
}
SetTicketStatus(ticket.Identification, 5);
TicketsMailer.SendMail_TicketApproved(ticket);
//Alert.ShowSuccess("The ticket has been approved and sent to billing.", BrokenRulesDisplay);
}
}
private void UpdatePriorityLabel(Ticket ticket)
{
switch (ticket.PriorityID)
{
case 1:
lblTicketPriority.Text = "Standard";
break;
case 2:
lblTicketPriority.Text = "Rush";
break;
case 3:
lblTicketPriority.Text = "Scheduled";
break;
default:
lblTicketPriority.Text = "Unknown";
break;
}
}
/// <summary>
/// syncLine Validates and Saves the current Data Ticket values to the Records Database.
/// </summary>
/// <param name="ticket">Current Overall Ticket</param>
/// <param name="pic">Current Line Item PIC</param>
/// <param name="row">The spefici row pertaining to the line item (List is a repeater)</param>
private void syncLineToRecords(Ticket ticket, LineItem pic, GridViewRow row)
{
var lstItemServices = (ListBox)row.FindControl("lstItemServices");
// Save Changes To Records!
try
{
Link patchLink = null;
Link providerLink = null;
// Current Patch Port
var patchLinkID =
SqlHelper.GetSingleSqlValue(
"declare @intid int;declare @port1 int;select distinct @intid = I.ID from Records.dbo.Interfaces as I join Records.dbo.Devices as D on I.DeviceID = D.ID join Records.dbo.Rooms as R on D.RoomID = R.ID join Records.dbo.Locations as L on R.LocationID = L.ID where L.ShortName = '" +
pic.Picid.Split('-')[0] + "' and InterfaceName = '" + pic.Picid.Split('-')[2] +
"' and I.FriendlyLongName = '" + pic.Picid +
"';Select top 1 @port1 = P.ID from Records.dbo.Interfaces as I join Records.dbo.Ports as P on I.ID = P.InterfaceID where I.ID = @intid order by P.ID desc;Select distinct L.ID as LinkID from Records.dbo.Ports As P left join Records.dbo.Links As L on P.ID = L.SourcePortID where L.SourcePortID = @port1 union Select distinct L.ID as LinkID from Records.dbo.Ports As P left join Records.dbo.Links As L on P.ID = L.DestinationPortID where L.DestinationPortID = @port1",
SqlHelper.GetConnectionString("Records"));
if (!string.IsNullOrEmpty(patchLinkID))
{
patchLink = Link.GetByIdentification(int.Parse(patchLinkID));
if (pic.PatchPortID != null && patchLink.SourcePortID != pic.PatchPortID &&
patchLink.DestinationPortID != pic.PatchPortID)
{
// patch port is different! let's have them load from records so that they can confirm the proper ports through Manage Links.
Alert.ShowError(
"Because port information changed, please verify and/or update the patch and provider information for " +
pic.Picid + " from Manage Links, then press 'Load Records'.", BrokenRulesDisplay);
scrollTop();
return;
}
if ((patchLink.DestinationPortPort.Interface.Ports.Count > 1 &&
patchLink.SourcePortPort.Interface.FriendlyLongName == pic.Picid) ||
(patchLink.SourcePortPort.Interface.Ports.Count > 1 &&
patchLink.DestinationPortPort.Interface.FriendlyLongName == pic.Picid))
{
var providerLinkID = SqlHelper.GetSingleSqlValue(
@"declare @port1 int;Select @port1 = P.ID from Records.dbo.Interfaces as I join Records.dbo.Ports as P on I.ID = P.InterfaceID where I.ID = (select InterfaceID from Records.dbo.Ports where ID = " +
(int)pic.PatchPortID + ") and P.ID != " + (int)pic.PatchPortID +
";Select distinct L.ID as LinkID from Records.dbo.Ports As P left join Records.dbo.Links As L on P.ID = L.SourcePortID where L.SourcePortID = @port1 union Select distinct L.ID as LinkID from Records.dbo.Ports As P left join Records.dbo.Links As L on P.ID = L.DestinationPortID where L.DestinationPortID = @port1",
SqlHelper.GetConnectionString("Records"));
if (!string.IsNullOrEmpty(providerLinkID))
{
providerLink = Link.GetByIdentification(int.Parse(providerLinkID));
}
}
else // This is actually a provider
providerLink = patchLink;
}
// Grab Ports
Port provider = null;
Port patch = null;
if (pic.PatchPortID != null)
patch = Port.GetByIdentification((int)pic.PatchPortID);
// Don't bother grabbing provider for deactivates, we dont need it
if (pic.ActionID < 3)
provider = Port.GetByIdentification((int)pic.ProviderPortID);
var currentInterface = Interface.GetByIdentification(pic.InterfaceID);
// Activate or Modify PIC
if (pic.ActionID < 3)
{
// Lists to save Data
List<int> setTaggedVlans = new List<int>();
List<int> removeVLANs = new List<int>();
List<int> setServices = new List<int>();
List<int> currentServices = new List<int>();
List<int> removeServices = new List<int>();
// Set provider link, set and replicate speed, vlans, services
int? native = null;
foreach (var vlan in pic.LineItemVLANs)
{
// If the VLAN is listed is Native but is not old (current) add it as the new native vlan
if (vlan.IsNative && vlan.IsOld == false)
{
native = vlan.Vlanid;
break;
}
}
// Set Tagged VLANs
foreach (var vlan in pic.LineItemVLANs)
{
if (vlan.Vlanid != native && vlan.IsOld == false)
setTaggedVlans.Add(vlan.Vlanid);
}
// Remove the rest of the vlans.
foreach (var vlan in pic.LineItemVLANs)
{
if (vlan.Vlanid != native && !setTaggedVlans.Contains(vlan.Vlanid) && vlan.IsOld)
removeVLANs.Add(vlan.Vlanid);
}
// Finalize native vlan by keeping it a the existing one.
// This applies only to Modify ticket - activate must have a native vlan.
if (pic.ActionID == 2 || pic.ActionID == 1)
{
foreach (var vlan in currentInterface.InterfaceVLANs)
{
if (native == null && vlan.IsNative)
native = vlan.Vlanid;
}
}
if (pic.ActionID == 1 && native == null)
{
Alert.ShowWarning("A <b>native vlan</b> is required.", BrokenRulesDisplay);
return;
}
if (native == null)
native = 17277;
// Set Services.
foreach (var srv in pic.LineItemServices)
{
if (!srv.IsOld)
setServices.Add(srv.ServiceID);
else
currentServices.Add(srv.ServiceID);
}
// Remove old services.
foreach (var service in currentServices)
{
if (!setServices.Contains(service))
removeServices.Add(service);
}
if (providerLink != null)
{
// Destination Port is the provider.
if (patch != null && providerLink.SourcePortPort.InterfaceID == patch.InterfaceID)
providerLink.DestinationPortID = provider.Identification;
// Source port is the provider.
else if (patch != null && providerLink.DestinationPortPort.InterfaceID == patch.InterfaceID)
providerLink.SourcePortID = provider.Identification;
// Destiintaion Port is the provider.
else if (providerLink.SourcePortPort.InterfaceID == pic.InterfaceID)
providerLink.DestinationPortID = provider.Identification;
// Source port is the provider.
else if (providerLink.DestinationPortPort.InterfaceID == pic.InterfaceID)
providerLink.SourcePortID = provider.Identification;
providerLink = providerLink.Save();
// Set Billing for Activate & Modify
LineItem.SetBilling(pic, currentInterface);
// Replicate Links
var rep = new LinkReplicator();
// Set Speed
rep.ReplicateSpeed(providerLink.SourcePortPort.InterfaceID, providerLink.Identification,
pic.SpeedID);
rep.ReplicateSpeed(providerLink.DestinationPortPort.InterfaceID, providerLink.Identification,
pic.SpeedID);
// Set Services
rep.ReplicateService(providerLink.SourcePortPort.InterfaceID, providerLink.Identification,
setServices, removeServices);
rep.ReplicateService(providerLink.DestinationPortPort.InterfaceID, providerLink.Identification,
setServices, removeServices);
// Set VLAN(s)
rep.ReplicateVLAN(providerLink.SourcePortPort.InterfaceID, providerLink.Identification,
currentInterface.IsTrunk, native, setTaggedVlans, removeVLANs);
rep.ReplicateVLAN(providerLink.DestinationPortPort.InterfaceID, providerLink.Identification,
currentInterface.IsTrunk, native, setTaggedVlans, removeVLANs);
}
else // Create a provider link.
{
// Set Billing for Activate & Modify
LineItem.SetBilling(pic, currentInterface);
createLink(pic, patch, provider, currentInterface, native, setTaggedVlans, setServices);
}
}
else if (pic.ActionID == 3 && providerLink != null)
{
// deactivate
// if link removed, remove provider link and set and replicate speed and vlan to none. otherwise, set and replicate speed to none and vlan to 1
if (pic.IsLinkRemoved == true)
{
// Set billing for deactivate.
LineItem.SetBilling(pic, currentInterface);
Link.RemoveLink(providerLink.Identification, false);
}
else
{
// Set billing for deactivate.
LineItem.SetBilling(pic, currentInterface);
// Instiate Link Replicator
var rep = new LinkReplicator();
// set speeds
rep.ReplicateSpeed(providerLink.SourcePortPort.InterfaceID, providerLink.Identification, null);
rep.ReplicateSpeed(providerLink.DestinationPortPort.InterfaceID, providerLink.Identification,
null);
// current services
List<int> currentServices = new List<int>();
foreach (var cursrv in currentInterface.InterfaceServiceAssignments)
currentServices.Add(cursrv.ServiceID);
// Set services
rep.ReplicateService(providerLink.SourcePortPort.InterfaceID, providerLink.Identification, null,
currentServices);
rep.ReplicateService(providerLink.DestinationPortPort.InterfaceID, providerLink.Identification,
null, currentServices);
// Set VLAN(s) to null. If Link is not removed. Set native VLAN too Cisco Defualt VLAN (1) ID: 17277
rep.ReplicateVLAN(providerLink.SourcePortPort.InterfaceID, providerLink.Identification, false,
17277, null, null);
rep.ReplicateVLAN(providerLink.DestinationPortPort.InterfaceID, providerLink.Identification,
false, 17277, null, null);
}
}
// Mark as Synced and Save.
pic.IsSynced = true;
ticket.Save();
// Remove provider Drop Downs.
var pnlItemProviderSelection = row.FindControl("pnlItemProviderSelection") as Panel;
pnlItemProviderSelection.Visible = false;
SetDone(row, true);
}
catch // Catch with error to load from records again
{
Alert.ShowError(
"Because port information changed, please verify and/or update the patch and provider information for " +
pic.Picid + " from Manage Links, then press 'Load Records'.", BrokenRulesDisplay);
scrollTop();
}
}
private void SetPageVisibilitySettings(Ticket ticket, bool hasAdmin, bool hasAgent, bool hasRestrictedView)
{
if ((!hasAdmin && hasAgent && ticket.StatusID < 2) ||
(!hasAdmin && !hasAgent && ticket.CreatedBy != RisqueContext.CurrentUser.Identification))
{
// ticket agent OR not agent, not admin, not submitter
pnlAddComment.Visible = false;
}
if (!hasAdmin && !hasAgent)
{
// general client
pnlAgentHelper.Visible = false;
pnlInternalStatus.Visible = false;
pnlCommentAddFilters.Visible = false;
pnlCommentFilters.Visible = false;
grdComments.Columns[3].Visible = false;
pnlLineSort.Visible = false;
}
// If the user is Admin or is Agent -- display the Agent information. If not hide it.
if ((hasAdmin || hasAgent) && ticket.AgentName != null)
{
pnlTicketAgent.Visible = true;
lblTicketAgent.Visible = true;
}
else
{
pnlTicketAgent.Visible = false;
lblTicketAgent.Visible = false;
}
if (ticket.StatusID >= 6)
{
pnlAgentHelper.Visible = false;
pnlAddComment.Visible = false;
}
// If User has RestrictedBillingView -- then hide the Settings Columng
if (hasRestrictedView)
{
grdItems.Columns[2].Visible = false;
}
}
private void SetMenuOptions(Ticket ticket, bool hasAdmin, bool hasAgent)
{
// Reset all visibility.
lnkEditTicketDue.Visible = false;
lnkHold.Visible = false;
lnkReleaseHold.Visible = false;
btnComplete.Visible = false;
lnkApprove.Visible = false;
lnkDeny.Visible = false;
lnkAssign.Visible = false;
lnkOpen.Visible = false;
btnGrab.Visible = false;
lnkRelease.Visible = false;
lnkCancel.Visible = false;
txtHoldReason.Text = string.Empty;
chkHoldNotify.Checked = false;
pnlStandardAdditionalContacts.Visible = true;
pnlAdditionalMACContacts.Visible = false;
// Set Ticket Visibility
if (ticket.StatusID == 1) // Status: Pending Verification
{
if (hasAgent) // Has Ticket Agent permissions.
{
pnlAdditionalMACContacts.Visible = true;
pnlStandardAdditionalContacts.Visible = false;
}
if (hasAdmin) // Has Ticket Admin permissions.
{
pnlAdditionalMACContacts.Visible = true;
pnlStandardAdditionalContacts.Visible = false;
lnkAssign.Visible = true;
lnkOpen.Visible = true;
lnkCancel.Visible = true;
if (!ticket.LineItems[0].Action.Name.Equals("Repair"))
{
lnkEditTicketDue.Visible = true;
}
//switch additional contact type
if (!ticket.IsOnHold)
lnkHold.Visible = true;
else
lnkReleaseHold.Visible = true;
}
else if (ticket.CreatedBy == RisqueContext.CurrentUser.Identification)
{
// creator can also cancel at this stage
pnlAdditionalMACContacts.Visible = true;
pnlStandardAdditionalContacts.Visible = false;
lnkCancel.Visible = true;
}
}
else if (ticket.StatusID == 2 && hasAgent) // Status: Unassigned and Opened - (Opened)
{
btnGrab.Visible = true;
if (hasAdmin)
{
lnkAssign.Visible = true;
lnkCancel.Visible = true;
if (!ticket.LineItems[0].Action.Name.Equals("Repair"))
{
lnkEditTicketDue.Visible = true;
}
if (!ticket.IsOnHold)
lnkHold.Visible = true;
else
lnkReleaseHold.Visible = true;
}
}
else if (ticket.StatusID == 3) // Status: In Progress; if admin: show all, otherwise: mine
{
if (hasAdmin)
{
lnkCancel.Visible = true;
lnkAssign.Visible = true;
if (!ticket.LineItems[0].Action.Name.Equals("Repair"))
{
lnkEditTicketDue.Visible = true;
}
if (!ticket.IsOnHold)
lnkHold.Visible = true;
else
lnkReleaseHold.Visible = true;
}
if (ticket.AgentID == RisqueContext.CurrentUser.Identification || hasAdmin)
{
btnComplete.Visible = true;
lnkRelease.Visible = true;
}
}
else if (ticket.StatusID == 4 && hasAdmin) // Status: Completion Pending.
{
lnkApprove.Visible = true;
lnkDeny.Visible = true;
lnkCancel.Visible = true;
if (!ticket.LineItems[0].Action.Name.Equals("Repair"))
{
lnkEditTicketDue.Visible = true;
}
if (!ticket.IsOnHold)
lnkHold.Visible = true;
else
lnkReleaseHold.Visible = true;
}
}
/// <summary>
/// Grab Current LineItem based off of repeater control row. (Generates a unique name)
/// </summary>
/// <param name="ticket"></param>
/// <param name="row"></param>
/// <returns></returns>
private static LineItem getLineItem(Ticket ticket, GridViewRow row)
{
return
ticket.LineItems.GetLineItem(
int.Parse((row.NamingContainer as GridView).DataKeys[row.RowIndex].Value.ToString()));
}
// General ticket info.
private void PopulateTicketGeneralData(Ticket ticket)
{
// contact info
ticket.CreatorName = RisqueContext.CurrentUser.FullName;
ticket.ContactName = txtContactName.Text.Trim();
ticket.ContactPhone = txtContactPhone.Text.Trim();
if (txtContactPhone.Text.Trim() != "" && txtContactPhoneExt.Text.Trim() != "")
ticket.ContactPhone += " ext. " + txtContactPhoneExt.Text.Trim();
ticket.ContactEmail = txtContactEmail.Text.Trim();
// additional contacts
foreach (DataRow contact in Contacts.Rows)
{
AdditionalContact addl = ticket.AdditionalContacts.AddNew();
addl.EmailAddress = contact["Email"].ToString();
}
// status
ticket.StatusID = 1;
ticket.IsOnHold = false;
}