---SQL procedure
--[Get_SendEmailSendTOPAX] '261223-000001','abc.1'
CREATE PROCEDURE [dbo].[Get_SendEmailSendTOPAX]
@PAXUID Varchar(250),
@AgentName Varchar(250)
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
if EXISTS(select 1 from [dbo].[tbl_agent] where agentgalaxyname=@AgentName AND Isactive=1) BEGIn
IF OBJECT_ID('#SendEmailPAX_CS', 'U') IS NOT NULL DROP TABLE #SendEmailPAX_CS;
--DROP TABLE IF EXISTS dbo.#SendEmailPAX_CS;
Create table #SendEmailPAX_CS ( PAXUID Varchar(250), AgentName Varchar(250), BookingID INT, BookingTypeID INT, AgentID INT , Gateway_Type nvarchar(250), Gateway_Type_Name nvarchar(250), Gateway_Email nvarchar(250), AirlineName nvarchar(250), Gateway nvarchar(250), TollFreeNumber nvarchar(250), Subject_TFN varchar(50), ConsentURL_ varchar(50) , GatewayID INT , PAX_Email varchar(50), PAXEmailSend_ID INT, EMail_Subject varchar(250), FinalGateway varchar(250) )
INSERt INTO #SendEmailPAX_CS (PAXUID,AgentName) SELeCT @PAXUID,@AgentName
--For AgentID
UPDATE SEP SET SEP.AgentID = TA.AgentID from #SendEmailPAX_CS SEP INNER JOIN tbl_Agent TA ON TA.AgentGalaxyName=@AgentName
--For BookingID
UPDATE SEP SET SEP.BookingID = TPD.PassengerDetailID , BookingTypeID=TPD.BookingTypeID from #SendEmailPAX_CS SEP INNER JOIN tbl_PassengerDetail TPD ON TPD.PassengerUID=@PAXUID
--Charging gateway Type
UPDATE #SendEmailPAX_CS SET Gateway_Type = APD.Gateway_Type,Gateway_Type_Name=APD.Gateway_Type_Name,PAX_Email=APD.PAX_Email, FinalGateway=( case when #SendEmailPAX_CS.Gateway_Type='Everyware' THEN 'SFC' when #SendEmailPAX_CS.BookingTypeID in (4,1023,1025) THEN 'Cancellation' ELSE APD.Gateway_Type end) FROM (SELECT TOP 1 SUBSTRING(gateway,0, CHARINDEX('-',gateway)) [Gateway_Type],Gateway [Gateway_Type_Name] ,PAX_Email,PNR FROM [dbo].tbl_AuthorizePaymentDetails where pnr=(select cast(BookingID as varchar) from #SendEmailPAX_CS where PAXUID=@PAXUID) ORDER BY ID DESC) AS APD WHERE #SendEmailPAX_CS.BookingID = APD.PNR;
--GET Gateway Details
UPDATE #SendEmailPAX_CS SEt Gateway_Email=MGT.Gateway_Email,Gateway=MGT.Gateway,TollFreeNumber=MGT.TollFreeNumber,AirlineName=MGT.AirlineName,Subject_TFN=MGT.Subject_TFN, ConsentURL_=MGT.ConsentURL from (select top 1 Gateway_Email [Gateway_Email],TravelName [Gateway], AirlineName [AirlineName], REPLACE(REPLACE(TollFreeNumber, CHAR(13), ''), CHAR(10), '') [TollFreeNumber], REPLACE(REPLACE(Subject_TFN, CHAR(13), ''), CHAR(10), '')[Subject_TFN],ConsentURL[ConsentURL] from [dbo].tbl_ManageGateway where Gateway_type=(Select top 1 FinalGateway from #SendEmailPAX_CS where PAXUID=@PAXUID)) AS MGT
Select PAXUID,AgentName,BookingID,BookingTypeID,AgentID,Gateway_Type,Gateway_Type_Name, Gateway_Email,AirlineName,Gateway, TollFreeNumber,Subject_TFN,ConsentURL_, GatewayID,PAX_Email , PAXEmailSend_ID , EMail_Subject , FinalGateway from #SendEmailPAX_CS where PAXUID=@PAXUID
DROP TABLE #SendEmailPAX_CS
END
COMMIT TRANSACTION;
END
--------------------------------------------
CREATE procedure [dbo].[InsertCaseHistory_SendEmail]
@AgentID varchar(250),
@Query nvarchar(MAX),
@PassengerID varchar(250)
AS
BEGIn
SET NOCOUNT ON;
INSERT INTO dbo.tbl_CaseHistory (AgentID,Query,PassengerID,CreationDate,isactive)
VALUES (@AgentID, @Query, @PassengerID,GETDATE(),1)
END
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Net;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Net.Mail;
public partial class SendEmailToPAX_CS : System.Web.UI.Page
{
public string BookingID = "";
public string AgentName = "";
public string Email_PWD = "";
public string EmailFrom = "";
public string PAXUID = "";
public string Mail_Content = "";
public string AgentIds = "";
WebService _obj = new WebService();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
string IPs = _obj.GET_IPAddress_V2();
if (string.IsNullOrEmpty(Request.QueryString["data_id"]) == true || string.IsNullOrEmpty(Request.QueryString["agent"]) == true || string.IsNullOrEmpty(Request.QueryString["emptoken"]) == true)
{
btn_Submit.Enabled = false;
}
else
{
PAXUID = Request.QueryString["data_id"].ToString();
// BookingID=Request.QueryString["Booking_id"].ToString();
AgentName = Request.QueryString["agent"].ToString();
string AgentToken = Request.QueryString["emptoken"].ToString();
try
{
var list = _obj.BindAgentDetails(AgentName, IPs);
foreach (var item in list)
{
string Galaxyname = item.Galaxyname;
string EmpToken = item.EmpToken;
int roleid = item.EmpRoleID;
if (Galaxyname != AgentName || EmpToken != AgentToken)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Session Expires Login back to Continue!!!');window.location ='login.html';", true);
}
if (roleid.ToString() == "3")
{
if (!IsPostBack)
{
GETDATA();
}
else { btn_Submit.Enabled = false; }
}
else
{
if ((Galaxyname == "FD50CC4AE" || Galaxyname == "FDB630C7A"))
{
if (!IsPostBack)
{
GETDATA();
}
else { btn_Submit.Enabled = false; }
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('You are not authorize to Send Email!!!');window.location ='login.html';", true);
}
}
}
}
catch (Exception ex) { PageUtility.MessageBox(this, "Something went wrong with Agent"); }
}
txt_PAX_BookingID.Enabled = false;
txt_PAX_GatewayEmail.Enabled = false;
txt_PAX_TollFreeNumber.Enabled = false;
txt_SubjectTFN.Enabled = false;
}
public void GETDATA()
{
//Get_LastEmailSendTOPAX
//Get_SendEmailSendTOPAX_CS
SqlCommand cmd = new SqlCommand("Get_SendEmailSendTOPAX", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PAXUID", PAXUID);
cmd.Parameters.AddWithValue("@AgentName", AgentName);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt); if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["PAXUID"].ToString() == Request.QueryString["data_id"].ToString())
{
txt_PAX_GatewayEmail.Text = dt.Rows[0]["Gateway_Email"].ToString();
EmailFrom = dt.Rows[0]["Gateway_Email"].ToString();
hdn_GatewayEmail.Value = dt.Rows[0]["Gateway_Email"].ToString();
txt_PAX_TollFreeNumber.Text = dt.Rows[0]["TollFreeNumber"].ToString();
txt_SubjectTFN.Text = dt.Rows[0]["Subject_TFN"].ToString();
txt_PAX_PAXEMAIl.Text = dt.Rows[0]["PAX_Email"].ToString();
txt_PAX_EmailSubject.Text = dt.Rows[0]["EMail_Subject"].ToString();
txt_PAX_BookingID.Text = dt.Rows[0]["BookingID"].ToString();
lbl_Bookingids.Text = dt.Rows[0]["BookingID"].ToString();
hdn_AgentID.Value = dt.Rows[0]["AgentID"].ToString();
}
else
{
PageUtility.MessageBox(this, "SOMETHING WENT WRONG !!");
btn_Submit.Enabled = false;
string close = @"<script type='text/javascript'> window.returnValue = true; window.close(); </script>";
base.Response.Write(close);
}
// txt_ = dt.Rows[0]["AgentID"].ToString(); } lbl_Message.Text=""; }
protected void btn_Submit_Click(object sender, EventArgs e)
{
lbl_Message.Text = "";
if (string.IsNullOrEmpty(txt_PAX_BookingID.Text) == true)
{
PageUtility.MessageBox(this, "Booking ID is missing");
txt_PAX_BookingID.Focus();
}
else if (string.IsNullOrEmpty(txt_PAX_GatewayEmail.Text) == true)
{
PageUtility.MessageBox(this, "Gateway Email missing");
txt_PAX_GatewayEmail.Focus();
}
else if (string.IsNullOrEmpty(txt_PAX_PAXEMAIl.Text) == true)
{
PageUtility.MessageBox(this, "Please Enter PAX Email!");
txt_PAX_PAXEMAIl.Focus();
}
else if (string.IsNullOrEmpty(txt_PAX_EmailSubject.Text) == true)
{
PageUtility.MessageBox(this, "Please Enter Email Subject!");
txt_PAX_EmailSubject.Focus();
}
else if (string.IsNullOrEmpty(txt_DisplayEmail.Text) == true)
{
PageUtility.MessageBox(this, "Please Display Email!");
txt_DisplayEmail.Focus();
}
else if (string.IsNullOrEmpty(txt_SendEmailContent.Text) == true)
{
PageUtility.MessageBox(this, "Please Enter Email Content!");
txt_SendEmailContent.Focus();
}
else
{
//Send Email To PAX
SendEmailToPAX(hdn_GatewayEmail.Value, txt_PAX_EmailSubject.Text, txt_SendEmailContent.Text, txt_PAX_PAXEMAIl.Text, AgentIds, txt_PAX_BookingID.Text, txt_DisplayEmail.Text);
}
}
public string SendEmailToPAX(string EmailFrom, string Email_Subject, string Email_Body, string EmailTO, string AgentID, string BookingID, string PAX_Display_Email)
{
string Email_MSG = "";
string folderpath = "";
string fileNameWithoutExtension = "";
string fileExtension = "";
string File_Name = "";
Attachment Attachment_ = null;
folderpath = Context.Server.MapPath(@"~/SendEmail_CS/" + PAXUID + "/");
try
{
Mail_Content = "<b>Email From : </b>" + EmailFrom + "<br>" + "<b>Email To : </b>" + EmailTO + "<br><br>";
//Email PAssword Cannot be Empty
Email_PWD = GETEMAIl_PWD(EmailFrom);
MailMessage msg = new MailMessage();
msg.To.Add(EmailTO);
msg.Bcc.Add(EmailFrom);
.From = new MailAddress(EmailFrom, PAX_Display_Email);
msg.Subject = Email_Subject;
msg.Body = Email_Body;
msg.IsBodyHtml = true;
msg.BodyEncoding = System.Text.Encoding.UTF8;
if (FileUpload1.HasFile)
{
//Check whether Directory (Folder) exists.
if (!Directory.Exists(folderpath))
{
//If Directory (Folder) does not exists. Create it.
System.IO.Directory.CreateDirectory(folderpath);
//==== Get file name without its extension.
}
foreach (HttpPostedFile uploadedFile in FileUpload1.PostedFiles)
{
if (CheckFileType(FileUpload1.PostedFile.FileName))
{
fileNameWithoutExtension = Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName);
//==== Get file extension.
fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
//..........
File_Name = "Ticket_" + DateTime.Now.ToString("yyyy" + "MM" + "dd" + "HH" + "mm" + "ss") + fileExtension;
uploadedFile.SaveAs(System.IO.Path.Combine(folderpath + File_Name));
// Attachment_ = new System.Net.Mail.Attachment(uploadedFile.InputStream,uploadedFile.FileName);
// here you can attach a file as a mail attachment
Attachment_ = new System.Net.Mail.Attachment(uploadedFile.InputStream, uploadedFile.FileName);
// here you can attach a file as a mail attachment
msg.Attachments.Add(Attachment_);
}
else
{
PageUtility.MessageBox(this, "Only PDF File is allowed");
}
}
}
///file upoload end
using (var smtp30 = new SmtpClient
{
Host = @"smtp.gmail.com",
Port = 587,
EnableSsl = true,
// Timeout=10000,
UseDefaultCredentials = false
})
{
var theCredential20 = new NetworkCredential(EmailFrom, Email_PWD);
smtp30.Credentials = theCredential20;
smtp30.Send(msg);
Email_MSG = "Mail Send";
Email_PWD = "";
msg.Dispose();
smtp30.Dispose();
if (FileUpload1.HasFile || Attachment_ != null) { Attachment_.Dispose(); }
// Attachment_.Dispose();
StringBuilder sb = new StringBuilder();
PageUtility.MessageBox(this, "Email has been Send Successfully");
sb.Append("<div class=\"alert alert-primary\" role=\"alert\">\r\n Email has been Send Successfully!\r\n</div>");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
string Query = "InsertCaseHistory_SendEmail";
cmd.CommandText = Query; cmd.Parameters.AddWithValue("@AgentID", hdn_AgentID.Value);
cmd.Parameters.AddWithValue("@Query", Mail_Content + Email_Body);
cmd.Parameters.AddWithValue("@PassengerID", BookingID);
// cmd.Parameters.AddWithValue("@CreationDate", DateTime.Now.ToString());
try
{
con.Open();
cmd.ExecuteNonQuery();
sb.Append("<div class=\"alert alert-primary\" role=\"alert\">\r\n Case history updated Successfully!\r\n</div>");
con.Close();
}
catch (SqlException ex)
{
PageUtility.MessageBox(this, ex.Message);
sb.Append("<div class=\"alert alert-primary\" role=\"alert\">\r\n Unable to update case history!\r\n</div>");
}
finally
{
if (con != null)
{
con.Dispose();
}
}
//sb.Append("<div class=\"alert alert-primary\" role=\"alert\">\r\n Email has been Send Successfully!\r\n</div>");
////insert casehistory
//bool rslt = _obj.InsertCaseHistoryDetails(AgentID, Mail_Content+Email_Body, BookingID);
//if (rslt)
//{
// sb.Append("<div class=\"alert alert-primary\" role=\"alert\">\r\n Case history updated Successfully!\r\n</div>");
//}
//else
//{
// sb.Append("<div class=\"alert alert-primary\" role=\"alert\">\r\n Unable to update case history!\r\n</div>");
//} lbl_Message.Text = sb.ToString();
//clear Control txt_SendEmailContent.Text="";
}
//If FIle Upload Is empty
btn_Submit.Enabled = false;
}
catch (Exception ex)
{
PageUtility.MessageBox(this, ex.Message);
Email_MSG = "Not Send";
}
//else
//{
//}
// return "Mail has been Send";
return Email_MSG;
}
public string GETEMAIl_PWD(string EmailFrom)
{
string RESULT = "";
SqlCommand cmd = new SqlCommand("GetgatewayEmail", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Email_ID", EmailFrom);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0))
{
RESULT = dt.Rows[0]["Email_PWD"].ToString();
}
return RESULT;
}
static string getString(char[] arr)
{
// string() is a used to
// convert the char array
// to string
string s = new string(arr);
return s;
}
public static class PageUtility
{
public static void MessageBox(System.Web.UI.Page page, string strMsg)
{
//+ character added after strMsg "')"
ScriptManager.RegisterClientScriptBlock(page, page.GetType(), "alertMessage", "alert('" + strMsg + "')", true);
}
}
public bool CheckFileType(string fileName)
{
string ext = Path.GetExtension(fileName); switch (ext.ToLower())
{
case ".pdf": return true; //case ".doc": // return true; //case ".docx": // return true; //case ".xls": // return true; //case ".xlsx": // return true;
default: return false;
}
}
}
