static FreshDeskTickets FreshDeskDataTabletoTransactions(DataRow datat)
{
var trans = new FreshDeskTickets()
{
TicketID = Int32.Parse(datat["Ticket Id"].ToString()),
Subject = CheckStringEmptyorNull(datat["Subject"].ToString()),
Description = CheckStringEmptyorNull(datat["Description"].ToString()),
Status = CheckStringEmptyorNull(datat["Status"].ToString()),
Priority = CheckStringEmptyorNull(datat["Priority"].ToString()),
Source = CheckStringEmptyorNull(datat["Source"].ToString()),
Type = CheckStringEmptyorNull(datat["Type"].ToString()),
Company = CheckStringEmptyorNull(datat["Company"].ToString()),
RequesterName = CheckStringEmptyorNull(datat["Requester Name"].ToString()),
RequesterEmail = CheckStringEmptyorNull(datat["Requester Email"].ToString()),
RequesterPhone = CheckStringEmptyorNull(datat["Requester Phone"].ToString()),
FacebookProfileID = CheckStringEmptyorNull(datat["Facebook Profile Id"].ToString()),
Agent = CheckStringEmptyorNull(datat["Agent"].ToString()),
Group = CheckStringEmptyorNull(datat["Group"].ToString()),
CreatedTime = CheckStringEmptyorNull(datat["Created Time"].ToString()),
DueByTime = CheckStringEmptyorNull(datat["Due by Time"].ToString()),
ResolvedTime = CheckStringEmptyorNull(datat["Resolved Time"].ToString()),
ClosedTime = CheckStringEmptyorNull(datat["Closed Time"].ToString()),
LastUpdatedTime = CheckStringEmptyorNull(datat["Last Updated Time"].ToString()),
InitialResponseTime = CheckStringEmptyorNull(datat["Initial Response Time"].ToString()),
AgentInteractions = CheckStringEmptyorNull(datat["Agent interactions"].ToString()),
CustomerInteractions = CheckStringEmptyorNull(datat["Customer interactions"].ToString()),
ResolutionStatus = CheckStringEmptyorNull(datat["Resolution Status"].ToString()),
FirstResponseStatus = CheckStringEmptyorNull(datat["First Response Status"].ToString()),
Tags = CheckStringEmptyorNull(datat["Tags"].ToString()),
SurveyResult = CheckStringEmptyorNull(datat["Survey Result"].ToString()),
Product = CheckStringEmptyorNull(datat["Product"].ToString()),
MarketRegion = CheckStringEmptyorNull(datat["Market/Region"].ToString()),
LocationNumber = CheckStringEmptyorNull(datat["Location #"].ToString()),
CompanyToBill = CheckStringEmptyorNull(datat["Company to Bill"].ToString()),
EntityType = CheckStringEmptyorNull(datat["Entity Type"].ToString()),
Entity = CheckStringEmptyorNull(datat["Entity"].ToString()),
BillByPriority = CheckStringEmptyorNull(datat["Bill by Priority"].ToString()),
URLReferenceLink = CheckStringEmptyorNull(datat["URL Reference Link"].ToString()),
DelegateTeam = CheckStringEmptyorNull(datat["Delegate Team"].ToString()),
Delegate = CheckStringEmptyorNull(datat["Delegate"].ToString()),
InventoryUsed = CheckStringEmptyorNull(datat["Inventory Used"].ToString())
};
return(trans);
}
public static void UpsertFreshDeskTickets(FreshDeskTickets trans)
{
string FreshDeskupsertCommand = @"IF NOT EXISTS (SELECT 1 FROM [freshdesk].[Tickets] WHERE [Ticket_Id] = @TicketID)
BEGIN
INSERT INTO [freshdesk].[Tickets]
([Ticket_Id]
,[Subject]
,[Description]
,[Status]
,[Priority]
,[Source]
,[Type]
,[Company]
,[Requester_Name]
,[Requester_Email]
,[Requester_Phone]
,[Facebook_Profile_Id]
,[Agent]
,[Group]
,[Created_Time]
,[Due_by_Time]
,[Resolved_Time]
,[Closed_Time]
,[Last_Updated_Time]
,[Initial_Response_Time]
,[Agent_interactions]
,[Customer_interactions]
,[Resolution_Status]
,[First_Response_Status]
,[Tags]
,[Survey_Result]
,[Product]
,[Market_Region]
,[Location#]
,[Company_to_Bill]
,[Entity_Type]
,[Entity]
,[Bill_by_Priority]
,[URL_Reference_Link]
,[Delegate_Team]
,[Delegate]
,[Inventory_Used]
)
VALUES
(@TicketID
,@Subject
,@Description
,@Status
,@Priority
,@Source
,@Type
,@Company
,@RequesterName
,@RequesterEmail
,@RequesterPhone
,@FacebookProfileID
,@Agent
,@Group
,@CreatedTime
,@DueByTime
,@ResolvedTime
,@ClosedTime
,@LastUpdatedTime
,@InitialResponseTime
,@AgentInteractions
,@CustomerInteractions
,@ResolutionStatus
,@FirstResponseStatus
,@Tags
,@SurveyResult
,@Product
,@MarketRegion
,@LocationNumber
,@CompanyToBill
,@EntityType
,@Entity
,@BillByPriority
,@URLReferenceLink
,@DelegateTeam
,@Delegate
,@InventoryUsed
)
END
ELSE
BEGIN
UPDATE [freshdesk].[Tickets]
SET [Subject] = @Subject
,[Description] = @Description
,[Status] = @Status
,[Priority] = @Priority
,[Source] = @Source
,[Type] = @Type
,[Company] = @Company
,[Requester_Name] = @RequesterName
,[Requester_Email] = @RequesterEmail
,[Requester_Phone] = @RequesterPhone
,[Facebook_Profile_Id] = @FacebookProfileID
,[Agent] = @Agent
,[Group] = @Group
,[Created_Time] = @CreatedTime
,[Due_by_Time] = @DueByTime
,[Resolved_Time] = @ResolvedTime
,[Closed_Time] = @ClosedTime
,[Last_Updated_Time] = @LastUpdatedTime
,[Initial_Response_Time] = @InitialResponseTime
,[Agent_interactions] = @AgentInteractions
,[Customer_interactions] = @CustomerInteractions
,[Resolution_Status] = @ResolutionStatus
,[First_Response_Status] = @FirstResponseStatus
,[Tags] = @Tags
,[Survey_Result] = @SurveyResult
,[Product] = @Product
,[Market_Region] = @MarketRegion
,[Location#] = @LocationNumber
,[Company_to_Bill] = @CompanyToBill
,[Entity_Type] = @EntityType
,[Entity] = @Entity
,[Bill_by_Priority] = @BillByPriority
,[URL_Reference_Link] = @URLReferenceLink
,[Delegate_Team] = @DelegateTeam
,[Delegate] = @Delegate
,[Inventory_Used] = @InventoryUsed
,[record_updated_date] = @RecordUpdatedAt
WHERE [Ticket_Id] = @TicketID
END";
try
{
using (var connection = new SqlConnection(SQLServerConnectionString))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(FreshDeskupsertCommand, connection))
{
cmd.Parameters.Add("@TicketID", SqlDbType.Int).Value = trans.TicketID;
cmd.Parameters.Add("@Subject", SqlDbType.NVarChar).Value = trans.Subject ?? (object)DBNull.Value;
cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = trans.Description ?? (object)DBNull.Value;
cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = trans.Status ?? (object)DBNull.Value;
cmd.Parameters.Add("@Priority", SqlDbType.NVarChar).Value = trans.Priority ?? (object)DBNull.Value;
cmd.Parameters.Add("@Source", SqlDbType.NVarChar).Value = trans.Source ?? (object)DBNull.Value;
cmd.Parameters.Add("@Type", SqlDbType.NVarChar).Value = trans.Type ?? (object)DBNull.Value;
cmd.Parameters.Add("@Company", SqlDbType.NVarChar).Value = trans.Company ?? (object)DBNull.Value;
cmd.Parameters.Add("@RequesterName", SqlDbType.NVarChar).Value = trans.RequesterName ?? (object)DBNull.Value;
cmd.Parameters.Add("@RequesterEmail", SqlDbType.NVarChar).Value = trans.RequesterEmail ?? (object)DBNull.Value;
cmd.Parameters.Add("@RequesterPhone", SqlDbType.NVarChar).Value = trans.RequesterPhone ?? (object)DBNull.Value;
cmd.Parameters.Add("@FacebookProfileID", SqlDbType.NVarChar).Value = trans.FacebookProfileID ?? (object)DBNull.Value;
cmd.Parameters.Add("@Agent", SqlDbType.NVarChar).Value = trans.Agent ?? (object)DBNull.Value;
cmd.Parameters.Add("@Group", SqlDbType.NVarChar).Value = trans.Group ?? (object)DBNull.Value;
cmd.Parameters.Add("@CreatedTime", SqlDbType.DateTime2).Value = trans.CreatedTime ?? (object)DBNull.Value;
cmd.Parameters.Add("@DueByTime", SqlDbType.DateTime2).Value = trans.DueByTime ?? (object)DBNull.Value;
cmd.Parameters.Add("@ResolvedTime", SqlDbType.DateTime2).Value = trans.ResolvedTime ?? (object)DBNull.Value;
cmd.Parameters.Add("@ClosedTime", SqlDbType.DateTime2).Value = trans.ClosedTime ?? (object)DBNull.Value;
cmd.Parameters.Add("@LastUpdatedTime", SqlDbType.DateTime2).Value = trans.LastUpdatedTime ?? (object)DBNull.Value;
cmd.Parameters.Add("@InitialResponseTime", SqlDbType.DateTime2).Value = trans.InitialResponseTime ?? (object)DBNull.Value;
cmd.Parameters.Add("@AgentInteractions", SqlDbType.NVarChar).Value = trans.AgentInteractions ?? (object)DBNull.Value;
cmd.Parameters.Add("@CustomerInteractions", SqlDbType.NVarChar).Value = trans.CustomerInteractions ?? (object)DBNull.Value;
cmd.Parameters.Add("@ResolutionStatus", SqlDbType.NVarChar).Value = trans.ResolutionStatus ?? (object)DBNull.Value;
cmd.Parameters.Add("@FirstResponseStatus", SqlDbType.NVarChar).Value = trans.FirstResponseStatus ?? (object)DBNull.Value;
cmd.Parameters.Add("@Tags", SqlDbType.NVarChar).Value = trans.Tags ?? (object)DBNull.Value;
cmd.Parameters.Add("@SurveyResult", SqlDbType.NVarChar).Value = trans.SurveyResult ?? (object)DBNull.Value;
cmd.Parameters.Add("@Product", SqlDbType.NVarChar).Value = trans.Product ?? (object)DBNull.Value;
cmd.Parameters.Add("@MarketRegion", SqlDbType.NVarChar).Value = trans.MarketRegion ?? (object)DBNull.Value;
cmd.Parameters.Add("@LocationNumber", SqlDbType.NVarChar).Value = trans.LocationNumber ?? (object)DBNull.Value;
cmd.Parameters.Add("@CompanyToBill", SqlDbType.NVarChar).Value = trans.CompanyToBill ?? (object)DBNull.Value;
cmd.Parameters.Add("@EntityType", SqlDbType.NVarChar).Value = trans.EntityType ?? (object)DBNull.Value;
cmd.Parameters.Add("@Entity", SqlDbType.NVarChar).Value = trans.Entity ?? (object)DBNull.Value;
cmd.Parameters.Add("@BillByPriority", SqlDbType.NVarChar).Value = trans.BillByPriority ?? (object)DBNull.Value;
cmd.Parameters.Add("@URLReferenceLink", SqlDbType.NVarChar).Value = trans.URLReferenceLink ?? (object)DBNull.Value;
cmd.Parameters.Add("@DelegateTeam", SqlDbType.NVarChar).Value = trans.DelegateTeam ?? (object)DBNull.Value;
cmd.Parameters.Add("@Delegate", SqlDbType.NVarChar).Value = trans.Delegate ?? (object)DBNull.Value;
cmd.Parameters.Add("@InventoryUsed", SqlDbType.NVarChar).Value = trans.InventoryUsed ?? (object)DBNull.Value;
cmd.Parameters.Add("@RecordUpdatedAt", SqlDbType.DateTime2).Value = DateTime.UtcNow;
var resultCount = cmd.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", resultCount);
}
}
}
catch (Exception ex)
{
Console.WriteLine("Insert Freshdesk Tickets error");
Console.WriteLine(ex);
}
}
