//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");
}
}