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