//Changes to this method needs to be applied to TicketsView.LoadToPushToTFS also. public void LoadToPushToTFS(CRMLinkTableItem item) { using (SqlCommand command = new SqlCommand()) { command.CommandText = @"SELECT tfs.* FROM TicketLinkToTFS tfs JOIN Tickets t ON tfs.TicketID = t.TicketID WHERE tfs.SyncWithTFS = 1 AND t.OrganizationID = @OrgID AND tfs.CrmLinkID = @CrmLinkId AND ( tfs.DateModifiedByTFSSync IS NULL OR ( t.DateModified > DATEADD(s, 2, tfs.DateModifiedByTFSSync) AND t.DateModified > @DateModified ) ) ORDER BY t.DateCreated DESC" ; command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@OrgID", item.OrganizationID); command.Parameters.AddWithValue("@DateModified", item.LastLink == null ? new DateTime(1753, 1, 1) : item.LastLinkUtc.Value.AddHours(-1)); command.Parameters.AddWithValue("@CrmLinkId", item.CRMLinkID); command.CommandTimeout = 90; Fill(command); } }
public void LoadModifiedByCRMLinkItem(CRMLinkTableItem item) { using (SqlCommand command = new SqlCommand()) { command.CommandText = @" SELECT a.* FROM Actions a JOIN Tickets t ON a.TicketID = t.TicketID LEFT JOIN TicketStatuses ts ON t.TicketStatusID = ts.TicketStatusID WHERE a.SystemActionTypeID <> 1 AND a.DateModified > @DateModified AND ( a.DateModifiedBySalesForceSync IS NULL OR a.DateModified > DATEADD(s, 2, a.DateModifiedBySalesForceSync) ) AND t.OrganizationID = @OrgID AND ( @DateModified > '1753-01-01' OR ts.IsClosed = 0 ) ORDER BY a.DateCreated DESC "; command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@OrgID", item.OrganizationID); command.Parameters.AddWithValue("@DateModified", item.LastLink == null ? new DateTime(1753, 1, 1) : item.LastLinkUtc.Value.AddHours(-1)); using (SqlConnection connection = new SqlConnection(this.LoginUser.ConnectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); command.Connection = connection; command.Transaction = transaction; command.CommandTimeout = 300; SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); this.Table.Load(reader); } } }
//Changes to this method needs to be applied to TicketsView.LoadToPushToJira also. public void LoadToPushToJira(CRMLinkTableItem item) { using (SqlCommand command = new SqlCommand()) { command.CommandText = @"SELECT j.* FROM TicketLinkToJira j JOIN Tickets t ON j.TicketID = t.TicketID WHERE j.SyncWithJira = 1 AND t.OrganizationID = @OrgID AND j.CrmLinkID = @CrmLinkId AND ( j.DateModifiedByJiraSync IS NULL OR ( t.DateModified > DATEADD(s, 2, j.DateModifiedByJiraSync) AND t.DateModified > @DateModified ) ) ORDER BY t.DateCreated DESC" ; command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@OrgID", item.OrganizationID); command.Parameters.AddWithValue("@DateModified", item.LastLink == null ? new DateTime(1753, 1, 1) : item.LastLinkUtc.Value.AddHours(-1)); command.Parameters.AddWithValue("@CrmLinkId", item.CRMLinkID); //command.CommandText = "TicketLinkToJiraGet"; //command.CommandType = CommandType.StoredProcedure; //command.Parameters.AddWithValue("@organizationId", item.OrganizationID); //command.Parameters.AddWithValue("@dateModified", item.LastLink == null ? new DateTime(1753, 1, 1) : item.LastLinkUtc.Value.AddHours(-1)); //command.Parameters.AddWithValue("@crmLinkId", item.CRMLinkID); //command.Parameters.AddWithValue("@isTicketLinkToJira", 1); command.CommandTimeout = 90; Fill(command); } }
//Changes to this method needs to be applied to ActionLinkToJira.LoadToPushToJira also. public void LoadToPushToTFS(CRMLinkTableItem item, int ticketID) { string actionTypeFilter = "1 = 1"; if (item.ActionTypeIDToPush != null) { actionTypeFilter = "a.ActionTypeID = @actionTypeID"; } using (SqlCommand command = new SqlCommand()) { command.CommandText = @" SELECT a.* FROM Actions a LEFT JOIN ActionLinkToTFS tfs ON a.ActionID = tfs.ActionID WHERE a.SystemActionTypeID <> 1 AND a.TicketID = @ticketID AND " + actionTypeFilter + @" AND ( tfs.DateModifiedByTFSSync IS NULL OR a.DateModified > DATEADD(s, 2, tfs.DateModifiedByTFSSync) ) ORDER BY a.DateCreated ASC "; command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@ticketID", ticketID); command.Parameters.AddWithValue("@DateModified", item.LastLink == null ? new DateTime(1753, 1, 1) : item.LastLinkUtc.Value.AddHours(-1)); command.Parameters.AddWithValue("@actionTypeID", item.ActionTypeIDToPush == null ? -1 : item.ActionTypeIDToPush); Fill(command, "Actions"); } }