protected Database_Insert Ticket_Add_Comment(Int32 sp_typeid, Int32 sp_ticketid, Boolean sp_status, String sp_note)
{
Database_Insert rtrn = new Database_Insert();
#region SQL Connection
using (SqlConnection con = new SqlConnection(sqlStrDE))
{
ghFunctions.Donation_Open_Database(con);
#region SQL Command
using (SqlCommand cmd = new SqlCommand("", con))
{
#region Build cmdText
String cmdText = "";
cmdText = @"
INSERT INTO [dataexchange_ticket].[dbo].[ticket_note]
([typeid]
,[ticketid]
,[authorid]
,[status]
,[note]
,[dateadded]
)
VALUES
(@sp_typeid
,@sp_ticketid
,@sp_authorid
,@sp_status
,@sp_note
,@sp_dateadded)
SELECT SCOPE_IDENTITY()
";
cmdText += "\r";
#endregion Build cmdText
#region SQL Command Config
cmd.CommandTimeout = 600;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
#endregion SQL Command Config
#region SQL Command Parameters
cmd.Parameters.Add("@sp_typeid", SqlDbType.Int).Value = sp_typeid;
cmd.Parameters.Add("@sp_ticketid", SqlDbType.Int).Value = sp_ticketid;
cmd.Parameters.Add("@sp_authorid", SqlDbType.Int).Value = Session["userid"].ToString();
cmd.Parameters.Add("@sp_status", SqlDbType.Bit).Value = sp_status;
cmd.Parameters.Add("@sp_note", SqlDbType.VarChar, 4000).Value = sp_note;
cmd.Parameters.Add("@sp_dateadded", SqlDbType.DateTime).Value = DateTime.UtcNow;
#endregion SQL Command Parameters
#region SQL Command Processing
var chckResults = cmd.ExecuteScalar();
if (chckResults != null && chckResults.ToString() != "0")
{
// We inserted the ticket
rtrn.status = true;
rtrn.identity = Convert.ToInt32(chckResults.ToString());
rtrn.records = 1;
rtrn.response = "Success";
}
else
{
// There was a problem inserting the ticket
rtrn.status = false;
rtrn.response = "Error";
rtrn.message = "Invalid Identity";
}
#endregion SQL Command Processing
}
#endregion SQL Command
}
#endregion SQL Connection
return(rtrn);
}
protected Database_Insert Ticket_Add_Date_End(Int32 sp_ticketid, String dateend)
{
Database_Insert rtrn = new Database_Insert();
#region SQL Connection
using (SqlConnection con = new SqlConnection(sqlStrDE))
{
ghFunctions.Donation_Open_Database(con);
if (dateend.Length > 0)
{
#region SQL Command
using (SqlCommand cmd = new SqlCommand("", con))
{
#region Build cmdText
String cmdText = "";
cmdText = @"
INSERT INTO [dataexchange_ticket].[dbo].[ticket_date]
([ticketid]
,[typeid]
,[date]
,[status]
)
VALUES
(@sp_ticketid
,@sp_typeid
,@sp_date
,@sp_status)
";
cmdText += "\r";
#endregion Build cmdText
#region SQL Command Config
cmd.CommandTimeout = 600;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
#endregion SQL Command Config
#region SQL Command Parameters
cmd.Parameters.Add("@sp_ticketid", SqlDbType.Int).Value = sp_ticketid;
cmd.Parameters.Add("@sp_typeid", SqlDbType.Int).Value = 1010002; // Ticket End
cmd.Parameters.Add("@sp_date", SqlDbType.DateTime).Value = dateend;
cmd.Parameters.Add("@sp_status", SqlDbType.Bit).Value = true;
#endregion SQL Command Parameters
#region SQL Command Processing
int records = cmd.ExecuteNonQuery();
if (records == 1)
{
// We inserted the ticket
rtrn.status = true;
rtrn.records = 1;
rtrn.response = "Success";
}
else
{
// There was a problem inserting the ticket
rtrn.status = false;
rtrn.response = "Error";
rtrn.message = "Problem inserting the Ticket Start Date";
}
#endregion SQL Command Processing
}
#endregion SQL Command
}
}
#endregion SQL Connection
return(rtrn);
}
protected Database_Insert Ticket_Add_Item(Int32 sp_ticketid, Int32 sp_typeid, Int32 sp_itemid)
{
/// Will add or update the priority
/// We need to do an associated log
Database_Insert rtrn = new Database_Insert();
rtrn.response = "Initiated";
if (sp_typeid > 0)
{
#region SQL Connection
using (SqlConnection con = new SqlConnection(sqlStrDE))
{
ghFunctions.Donation_Open_Database(con);
bool doinsert = false;
bool doupdate = false;
#region SQL Command
using (SqlCommand cmd = new SqlCommand("", con))
{
#region Build cmdText
String cmdText = "";
cmdText = @"
IF EXISTS(SELECT TOP 1 1 FROM [dataexchange_ticket].[dbo].[ticket_item] [ti] WHERE [ti].[ticketid] = @sp_ticketid AND [ti].[typeid] = @sp_typeid)
BEGIN
IF EXISTS(SELECT TOP 1 1 FROM [dataexchange_ticket].[dbo].[ticket_item] [ti] WHERE [ti].[ticketid] = @sp_ticketid AND [ti].[typeid] = @sp_typeid AND [ti].[itemid] = @sp_itemid)
BEGIN
SELECT 'SAME' [result], 'No Update No Log' [message]
END
ELSE
BEGIN
SELECT 'UPDATE' [result],'Log the update' [message]
END
END
ELSE
BEGIN
SELECT 'INSERT' [result],'Log the insert' [message]
END
";
cmdText += "\r";
#endregion Build cmdText
#region SQL Command Config
cmd.CommandTimeout = 600;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
#endregion SQL Command Config
#region SQL Command Parameters
cmd.Parameters.Add("@sp_ticketid", SqlDbType.Int).Value = sp_ticketid;
cmd.Parameters.Add("@sp_typeid", SqlDbType.Int).Value = sp_typeid;
cmd.Parameters.Add("@sp_itemid", SqlDbType.Int).Value = sp_itemid;
cmd.Parameters.Add("@sp_authorid", SqlDbType.Int).Value = Session["userid"].ToString();
cmd.Parameters.Add("@sp_dateadded", SqlDbType.DateTime).Value = DateTime.UtcNow;
#endregion SQL Command Parameters
#region SQL Command Processing
var chckResults = cmd.ExecuteScalar();
if (chckResults != null && chckResults.ToString().Length > 0)
{
// We inserted the ticket
rtrn.status = true;
rtrn.records = 1;
rtrn.response = chckResults.ToString();
if (chckResults.ToString() == "UPDATE")
{
doupdate = true;
}
else if (chckResults.ToString() == "INSERT")
{
doinsert = true;
}
}
else
{
// There was a problem inserting the ticket
rtrn.status = false;
rtrn.response = "Error";
rtrn.message = "";
}
#endregion SQL Command Processing
}
#endregion SQL Command
if (doinsert)
{
#region SQL Command
using (SqlCommand cmd = new SqlCommand("", con))
{
#region Build cmdText
String cmdText = "";
cmdText = @"
INSERT INTO [dataexchange_ticket].[dbo].[ticket_item]
([ticketid]
,[typeid]
,[itemid]
,[authorid]
,[dateadded]
)
VALUES
(@sp_ticketid
,@sp_typeid
,@sp_itemid
,@sp_authorid
,@sp_dateadded)
";
cmdText += "\r";
#endregion Build cmdText
#region SQL Command Config
cmd.CommandTimeout = 600;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
#endregion SQL Command Config
#region SQL Command Parameters
cmd.Parameters.Add("@sp_ticketid", SqlDbType.Int).Value = sp_ticketid;
cmd.Parameters.Add("@sp_typeid", SqlDbType.Int).Value = sp_typeid;
cmd.Parameters.Add("@sp_itemid", SqlDbType.Int).Value = sp_itemid;
cmd.Parameters.Add("@sp_authorid", SqlDbType.Int).Value = Session["userid"].ToString();
cmd.Parameters.Add("@sp_dateadded", SqlDbType.DateTime).Value = DateTime.UtcNow;
#endregion SQL Command Parameters
#region SQL Command Processing
int cmdNon = cmd.ExecuteNonQuery();
if (cmdNon > 0)
{
// We inserted the ticket
rtrn.status = true;
rtrn.records = cmdNon;
rtrn.response = "Inserted";
}
else
{
// There was a problem inserting the ticket
rtrn.status = false;
rtrn.response = "Failed";
rtrn.message = "Failed to insert record";
}
#endregion SQL Command Processing
}
#endregion SQL Command
}
if (doupdate)
{
#region SQL Command
using (SqlCommand cmd = new SqlCommand("", con))
{
#region Build cmdText
String cmdText = "";
cmdText = @"
UPDATE [dataexchange_ticket].[dbo].[ticket_item]
SET [itemid] = @sp_itemid
,[authorid] = @sp_authorid
,[dateadded] = @sp_dateadded
WHERE [ticketid] = @sp_ticketid
AND [typeid] = @sp_typeid
";
cmdText += "\r";
#endregion Build cmdText
#region SQL Command Config
cmd.CommandTimeout = 600;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
#endregion SQL Command Config
#region SQL Command Parameters
cmd.Parameters.Add("@sp_ticketid", SqlDbType.Int).Value = sp_ticketid;
cmd.Parameters.Add("@sp_typeid", SqlDbType.Int).Value = sp_typeid;
cmd.Parameters.Add("@sp_itemid", SqlDbType.Int).Value = sp_itemid;
cmd.Parameters.Add("@sp_authorid", SqlDbType.Int).Value = Session["userid"].ToString();
cmd.Parameters.Add("@sp_dateadded", SqlDbType.DateTime).Value = DateTime.UtcNow;
#endregion SQL Command Parameters
#region SQL Command Processing
int cmdNon = cmd.ExecuteNonQuery();
if (cmdNon > 0)
{
// We inserted the ticket
rtrn.status = true;
rtrn.records = cmdNon;
rtrn.response = "Updated";
}
else
{
// There was a problem inserting the ticket
rtrn.status = false;
rtrn.response = "Failed";
rtrn.message = "Failed to update record";
}
#endregion SQL Command Processing
}
#endregion SQL Command
}
}
#endregion SQL Connection
}
return(rtrn);
}
protected Database_Insert Ticket_Add_Simple(Int32 sp_campaignid, String sp_title, String sp_description, String sp_key)
{
Database_Insert rtrn = new Database_Insert();
#region SQL Connection
using (SqlConnection con = new SqlConnection(sqlStrDE))
{
ghFunctions.Donation_Open_Database(con);
#region SQL Command
using (SqlCommand cmd = new SqlCommand("", con))
{
#region Build cmdText
String cmdText = "";
cmdText = @"
INSERT INTO [dataexchange_ticket].[dbo].[ticket]
([campaignid]
,[title]
,[description]
,[key]
)
VALUES
(@sp_campaignid
,@sp_title
,@sp_description
,@sp_key)
SELECT SCOPE_IDENTITY()
";
cmdText += "\r";
#endregion Build cmdText
#region SQL Command Config
cmd.CommandTimeout = 600;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
#endregion SQL Command Config
#region SQL Command Parameters
cmd.Parameters.Add("@sp_campaignid", SqlDbType.Int).Value = sp_campaignid;
cmd.Parameters.Add("@sp_title", SqlDbType.VarChar, 400).Value = sp_title;
cmd.Parameters.Add("@sp_description", SqlDbType.VarChar, 4000).Value = sp_description;
cmd.Parameters.Add("@sp_key", SqlDbType.VarChar, 64).Value = sp_key;
#endregion SQL Command Parameters
#region SQL Command Processing
var chckResults = cmd.ExecuteScalar();
if (chckResults != null && chckResults.ToString() != "0")
{
// We inserted the ticket
rtrn.status = true;
rtrn.identity = Convert.ToInt32(chckResults.ToString());
rtrn.records = 1;
rtrn.response = "Success";
}
else
{
// There was a problem inserting the ticket
rtrn.status = false;
rtrn.response = "Error";
rtrn.message = "Invalid Identity";
}
#endregion SQL Command Processing
}
#endregion SQL Command
}
#endregion SQL Connection
return(rtrn);
}