I am using stored procedure to insert data in the sql server. The problem is sometimes I get duplicate entries. Can anybody tell me what is wrong in the stored procedure that is causing Duplicates????
USE [CARs]
GO
/****** Object: StoredProcedure [dbo].[sp_InsertNewTmpIssue] Script Date: 10/12/2012 13:00:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Rohit Khatavkar
-- Create date: 2010-NOV-27
-- Modified date: 2011-SEP-12
-- Description: Stored Procedure for insert new issue and return the new IssueNumber
-- Lastest Update: Fix Bug ID 10 about duplicate IssueNumber.
-- Cause: @OfficeCode did not get when IssueNumber doesn't existed.
-- Modified date: 2011-MAR-16 Description: Fix Target Languages and Departments missing update
-- Modified date: 2011-APR-08 Description: Add Other Client into tblissue_OtherClient
-- Modified date: 2011-MAY-12 Description: Remove "Tmp" prefix from generate new CAR issue Number
-- Modified date: 2011-MAY-18 Description: Add return Functional Manager and Site Manager Emails
-- Modified date: 2011-MAY-19 Description: Change retrieve Functional Manager Email to get it from tblFunctionalManagerRoles and tblUsers
-- Modified date: 2011-MAY-20 - Description: Add Name with Email Address
-- Modified date: 2011-MAY-26 - Description: Addd CEO Email Address
-- Modified date: 2011-AUG-17 - Description: Fix Duplicate Issue Number when same office users submit with the same Issue Number but o1ne already approved Stage
-- Modified date: 2011-AUG-23 - Description: Expand JobNumber from 50 to 100 characters
-- Modified date: 2011-SEP-12 - Description: Issue 91 - Send notification to ISMS Manager
-- Modified date: 2012-FEB-17 - Description: Send Notification to Site Co-Ordinator(Both Source and Target Office)
-- Modified date: 2012-MAR-09 - Description: Send Notification to Quality Manager(Quality Coordinator)(Both Source and Target Office)
-- =============================================
ALTER PROCEDURE [dbo].[sp_InsertNewTmpIssue]
-- Add the parameters for the stored procedure here
@IssueNumber nvarchar(50),
@OfficeID int,
@IssueDate datetime,
@RaisedDate datetime,
@IssueTypeID int,
@RaisedByID int,
@SrcLangID int,
@IssueStatus int,
@ClientID int,
@OtherClient nvarchar(255),
@ImpactonAccountID int,
@Impact_Financial nvarchar(1024),
@AssignedTo int,
@TargetOfficeID int,
@Vendor nvarchar(255),
@CategoryID int,
@SubCategoryID int,
@TargetDate datetime,
@ProjectNumber nvarchar(80),
@JobNumber nvarchar(100),
@IssueDesc ntext,
@Departments nvarchar(125),
@TgtLangs nvarchar(125),
@RefFilenamesOnly nvarchar(4000),
@LatestIssueNumber nvarchar(50) OUTPUT,
@LatestISID int OUTPUT,
@FunctionalMgrEmails nvarchar(1024) OUTPUT,
@SiteMgrEmails nvarchar(1024) OUTPUT,
@CEOEmails nvarchar(1024) OUTPUT,
@ISMSMgrEmails nvarchar(1024) OUTPUT,
@SITECOEmails nvarchar(1024) OUTPUT,
@QMGREmails nvarchar(1024) OUTPUT,
@REGIONALMGREmails nvarchar(1024) OUTPUT
AS
BEGIN
DECLARE @NewIssueNumber nvarchar(50)
DECLARE @InsertRowCount int
DECLARE @Pos int
DECLARE @Parameters NVARCHAR(4000)
DECLARE @Value NVARCHAR(255)
DECLARE @ISID int
DECLARE @OfficeCode NVARCHAR(50)
DECLARE @SITE_MANAGER int
DECLARE @CEO int
DECLARE @ISMS_MANAGER int
DECLARE @CountIssueNumber int
DECLARE @SITE_COORDINATOR int
DECLARE @QUALITY_COORDINATOR int
DECLARE @REGIONAL_MANAGER int
SET @NewIssueNumber = ''
SET @InsertRowCount = 0
SET @ISID = -1
SET @OfficeCode = ''
SET @SITE_MANAGER = 7
SET @CEO = 8
SET @ISMS_MANAGER = 9
SET @CountIssueNumber = 0
SET @SITE_COORDINATOR = 4
SET @QUALITY_COORDINATOR = 3
SET @LatestIssueNumber = '0'
SET @REGIONAL_MANAGER = 2
BEGIN TRY
-- Change the way to check existing IssueNumber that pass from UI.
SELECT @CountIssueNumber += COUNT(IssueNumber) FROM tbltempIssues WHERE IssueNumber LIKE @IssueNumber
SELECT @CountIssueNumber
SELECT @CountIssueNumber += COUNT(IssueNumber) FROM tblIssues WHERE IssueNumber LIKE @IssueNumber
SELECT @CountIssueNumber
--SELECT IssueNumber FROM tbltempIssues WHERE IssueNumber LIKE @IssueNumber
--IF @@ROWCOUNT > 0
IF @CountIssueNumber > 0
BEGIN
--SELECT @NewIssueNumber = 'Tmp'+Code + '-' + RIGHT('0000' + CAST(tmpMaxID+1 as varchar), 4)
SELECT @NewIssueNumber = Code + '-' + RIGHT('0000' + CAST(tmpMaxID+1 as varchar), 4)
,@OfficeCode = Code
FROM lstOfficeName WITH(NOLOCK)
WHERE NID = @OfficeID
END
ELSE
BEGIN
SET @NewIssueNumber = @IssueNumber
SELECT @OfficeCode = Code
FROM lstOfficeName WITH(NOLOCK)
WHERE NID = @OfficeID
END
INSERT INTO tbltempIssues(
IssueNumber,
OfficeID,
IssueDate,
RaisedDate,
IssueTypeID,
RaisedByID,
SrcLangID,
IssueStatus,
ClientID,
ImpactonAccountID,
Impact_Financial,
AssignedTo,
TargetOfficeID,
Vendor,
CategoryID,
SubCategoryID,
TargetDate,
ProjectNumber,
JobNumber,
IssueDesc
) VALUES(
@NewIssueNumber,
@OfficeID,
@IssueDate,
@RaisedDate,
@IssueTypeID,
@RaisedByID,
@SrcLangID,
@IssueStatus,
@ClientID,
@ImpactonAccountID,
@Impact_Financial,
@AssignedTo,
@TargetOfficeID,
@Vendor,
@CategoryID,
@SubCategoryID,
@TargetDate,
@ProjectNumber,
@JobNumber,
@IssueDesc
)
SET @InsertRowCount = @@ROWCOUNT
SET @ISID = @@Identity
SET @LatestISID = @ISID
--PRINT 'InsertRowCount='+CAST(@InsertRowCount AS NVARCHAR)
END TRY
BEGIN CATCH
END CATCH
IF @InsertRowCount = 1
BEGIN
IF @@ERROR <> 0 GOTO ERR_HANDLER
SET @LatestIssueNumber = (SELECT IssueNumber AS LastID FROM tbltempIssues WHERE ISID = @@Identity)
--------------------------
IF @RefFilenamesOnly <> ''
BEGIN
SET @Parameters = @RefFilenamesOnly
SET @Pos = CHARINDEX('|', @Parameters, 1)
IF REPLACE(@Parameters, '|', '') <> ''
BEGIN
IF @Pos > 0
BEGIN
DELETE FROM tblIssueReferenceFiles WHERE ISID = @ISID
END
WHILE @Pos > 0
BEGIN
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
IF @Value <> ''
BEGIN
--INSERT INTO tblIssueDepartments(IssueNumber, DepartmentID) VALUES (@LatestIssueNumber, @Value)
INSERT INTO tblIssueReferenceFiles(ISID, FilenameOnly) VALUES (@ISID, @Value)
END
SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
SET @Pos = CHARINDEX('|', @Parameters, 1)
END
END
END
--------------------------
SET @Parameters = @Departments
SET @Pos = CHARINDEX(',', @Parameters, 1)
IF REPLACE(@Parameters, ',', '') <> ''
BEGIN
IF @Pos > 0
BEGIN
DELETE FROM tblIssueDepartments WHERE ISID = @ISID
END
WHILE @Pos > 0
BEGIN
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
IF @Value <> ''
BEGIN
INSERT INTO tblIssueDepartments(ISID, DepartmentID) VALUES (@ISID, @Value)
END
SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
SET @Pos = CHARINDEX(',', @Parameters, 1)
END
END
SET @Parameters = @TgtLangs
SET @Pos = CHARINDEX(',', @Parameters, 1)
IF REPLACE(@Parameters, ',', '') <> ''
BEGIN
IF @Pos > 0
BEGIN
DELETE FROM tblIssueTgtLanguages WHERE ISID = @ISID
END
WHILE @Pos > 0
BEGIN
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
IF @Value <> ''
BEGIN
INSERT INTO tblIssueTgtLanguages(ISID, [LangID]) VALUES (@ISID, @Value)
END
SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
SET @Pos = CHARINDEX(',', @Parameters, 1)
END
END
IF @@ERROR <> 0 GOTO ERR_HANDLER
UPDATE lstOfficeName
SET tmpMaxID = tmpMaxID+1
WHERE Code = @OfficeCode
--WHERE NID = @OfficeID
If @@ERROR <> 0 goto ERR_HANDLER
DELETE FROM tblIssues_OtherClient WHERE ISID = @ISID
INSERT INTO tblIssues_OtherClient(ISID, [OtherClientName]) VALUES (@ISID, @OtherClient)
END
ELSE
BEGIN
If @@ERROR <> 0 goto ERR_HANDLER
SET @LatestIssueNumber = '0'
END
SET @FunctionalMgrEmails = ''
SELECT @FunctionalMgrEmails = dbo.f_GetEmailsFromDepartment(@Departments)
SET @SiteMgrEmails = ''
SELECT @SiteMgrEmails = dbo.f_GetEmailsFromUserRole(@SITE_MANAGER, @OfficeID) + ', ' +dbo.f_GetEmailsFromUserRole(@SITE_MANAGER, @TargetOfficeID)
SET @CEOEmails = ''
SELECT @CEOEmails = dbo.f_GetEmailsFromUserRole(@CEO, @OfficeID)
SET @ISMSMgrEmails = ''
SELECT @ISMSMgrEmails = dbo.f_GetEmailsFromUserRole(@ISMS_MANAGER, @OfficeID) + ', ' +dbo.f_GetEmailsFromUserRole(@ISMS_MANAGER, @TargetOfficeID)
SET @SITECOEmails = ''
SELECT @SITECOEmails = dbo.f_GetEmailsFromUserRole(@SITE_COORDINATOR,@OfficeID) + ', ' +dbo.f_GetEmailsFromUserRole(@SITE_COORDINATOR, @TargetOfficeID)
SET @QMGREmails = ''
SELECT @QMGREmails = dbo.f_GetEmailsFromUserRole(@QUALITY_COORDINATOR,@officeID)+ ', ' +dbo.f_GetEmailsFromUserRole(@QUALITY_COORDINATOR,@TargetOfficeID)
SET @REGIONALMGREmails=''
SELECT @REGIONALMGREmails = dbo.f_GetEmailsFromUserRole(@REGIONAL_MANAGER,@officeID)+ ', ' +dbo.f_GetEmailsFromUserRole(@REGIONAL_MANAGER,@TargetOfficeID)
RETURN 0
ERR_HANDLER:
PRINT 'ERROR=' + @@ERROR
RETURN 1
END
******************************************************************************
Front end Code used to call the stored procedure
int iResult = 0;
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["CARsDBConn"].ToString()))
{
try
{
string sOfficeID = lstOrgOffice.SelectedItem.Value;
int i = 0;
String selDepartmentsString = "";
foreach (ListItem lItem in pickerDepartments.toListBox.Items)
{
selDepartmentsString += lItem.Value + ",";
i++;
}
i = 0;
String selTgtLangsString = "";
foreach (ListItem lItem in pickerTgtLangs.toListBox.Items)
{
selTgtLangsString += lItem.Value + ",";
i++;
}
String selFilenamesOnlyString = "";
for (i = 0; i <= (chkBoxLstRef.Items.Count - 1); i++)
{
selFilenamesOnlyString += chkBoxLstRef.Items[i].ToString() + "|";
}
SqlCommand cmd = new SqlCommand("sp_InsertNewTmpIssue", cn);
cmd.CommandTimeout = 300;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@IssueNumber", SqlDbType.NVarChar, 50));
cmd.Parameters["@IssueNumber"].Value = tbIssueNumber.Text;
cmd.Parameters.Add(new SqlParameter("@OfficeID", SqlDbType.Int));
cmd.Parameters["@OfficeID"].Value = lstOrgOffice.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@IssueDate", SqlDbType.DateTime));
//if (tbIssueCreatedDate.Text.Equals(""))
//{
// cmd.Parameters["@IssueDate"].Value = DBNull.Value;
//}
//else
//{
// cmd.Parameters["@IssueDate"].Value = tbIssueCreatedDate.Text;
//}
cmd.Parameters["@IssueDate"].Value = System.DateTime.Now.ToString("yyyy-MM-dd");
cmd.Parameters.Add(new SqlParameter("@RaisedDate", SqlDbType.DateTime));
if (tbRaisedDate.Text.Equals(""))
{
cmd.Parameters["@RaisedDate"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@RaisedDate"].Value = tbRaisedDate.Text;
}
cmd.Parameters.Add(new SqlParameter("@IssueTypeID", SqlDbType.Int));
cmd.Parameters["@IssueTypeID"].Value = lstIssueType.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@RaisedByID", SqlDbType.Int));
//cmd.Parameters["@RaisedByID"].Value = lstRaisedBy.SelectedItem.Value;
cmd.Parameters["@RaisedByID"].Value = hidRaisedByID.Value;
cmd.Parameters.Add(new SqlParameter("@SrcLangID", SqlDbType.Int));
cmd.Parameters["@SrcLangID"].Value = lstSrcLang.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@IssueStatus", SqlDbType.Int));
//cmd.Parameters["@IssueStatus"].Value = lstIssueStatus.SelectedItem.Value;
cmd.Parameters["@IssueStatus"].Value = Int32.Parse(Global.OPEN); // Issue#10 - Change New(0) to Open(1)
cmd.Parameters.Add(new SqlParameter("@ClientID", SqlDbType.Int));
cmd.Parameters["@ClientID"].Value = lstClient.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@OtherClient", SqlDbType.NVarChar, 255));
cmd.Parameters["@OtherClient"].Value = Server.HtmlEncode(tbOtherClient.Text);
cmd.Parameters.Add(new SqlParameter("@ImpactonAccountID", SqlDbType.Int));
cmd.Parameters["@ImpactonAccountID"].Value = lstImpactonAccount.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@Impact_Financial", SqlDbType.NVarChar, 1024));
cmd.Parameters["@Impact_Financial"].Value = tbImpactFinancial.Text;
cmd.Parameters.Add(new SqlParameter("@AssignedTo", SqlDbType.Int));
//cmd.Parameters["@AssignedTo"].Value = lstAssignedTo.SelectedItem.Value;
cmd.Parameters["@AssignedTo"].Value = hidAssignedToID.Value;
cmd.Parameters.Add(new SqlParameter("@TargetOfficeID", SqlDbType.Int));
cmd.Parameters["@TargetOfficeID"].Value = lstTargetOffice.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@Vendor", SqlDbType.NVarChar, 255));
cmd.Parameters["@Vendor"].Value = DBNull.Value;
cmd.Parameters.Add(new SqlParameter("@CategoryID", SqlDbType.Int));
cmd.Parameters["@CategoryID"].Value = lstCategory.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@SubCategoryID", SqlDbType.Int));
cmd.Parameters["@SubCategoryID"].Value = lstSubCategory.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@TargetDate", SqlDbType.DateTime));
if (tbTargetDate.Text.Equals(""))
{
cmd.Parameters["@TargetDate"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@TargetDate"].Value = tbTargetDate.Text;
}
cmd.Parameters.Add(new SqlParameter("@ProjectNumber", SqlDbType.NVarChar, 80));
cmd.Parameters["@ProjectNumber"].Value = tbProjectNumber.Text;
cmd.Parameters.Add(new SqlParameter("@JobNumber", SqlDbType.NVarChar, 100));
cmd.Parameters["@JobNumber"].Value = tbJobNumber.Text;
cmd.Parameters.Add(new SqlParameter("@IssueDesc", SqlDbType.NText));
cmd.Parameters["@IssueDesc"].Value = tbIssueDesc.Text;
// Department and Target Languages Must be Multiple select
cmd.Parameters.Add(new SqlParameter("@Departments", SqlDbType.NVarChar, 125));
cmd.Parameters["@Departments"].Value = selDepartmentsString;
cmd.Parameters.Add(new SqlParameter("@TgtLangs", SqlDbType.NVarChar, 125));
cmd.Parameters["@TgtLangs"].Value = selTgtLangsString;
//cmd.Parameters.Add(new SqlParameter("@RefFilenameOnly", SqlDbType.NVarChar, 128));
//cmd.Parameters["@RefFilenameOnly"].Value = hidRefFilenameOnly.Value;
cmd.Parameters.Add(new SqlParameter("@RefFilenamesOnly", SqlDbType.NVarChar, 4000));
cmd.Parameters["@RefFilenamesOnly"].Value = selFilenamesOnlyString;
// cmd.Parameters.Add(new SqlParameter("@ActFixImmediate", SqlDbType.NVarChar));
// cmd.Parameters["@ActFixImmediate"].Value = tbActFixImmediate.Text;
// cmd.Parameters.Add(new SqlParameter("@RootCauseID", SqlDbType.Int));
// cmd.Parameters["@RootCauseID"].Value = lstRootCauseID.SelectedItem.Value;
// cmd.Parameters.Add(new SqlParameter("@RootCauseDetail", SqlDbType.NVarChar));
// cmd.Parameters["@RootCauseDetail"].Value = tbRootCauseDetail.Text;
// cmd.Parameters.Add(new SqlParameter("@DateClosedbyQM", SqlDbType.DateTime));
// cmd.Parameters["@DateClosedbyQM"].Value = tbDateClosedbyQM.Text;
// cmd.Parameters.Add(new SqlParameter("@ResAssessID", SqlDbType.Int));
// cmd.Parameters["@ResAssessID"].Value = lstResAssessID.SelectedItem.Value;
cmd.Parameters.Add("@LatestIssueNumber", SqlDbType.NVarChar, 50);
cmd.Parameters["@LatestIssueNumber"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@LatestISID", SqlDbType.Int);
cmd.Parameters["@LatestISID"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@FunctionalMgrEmails", SqlDbType.NVarChar, 1024);
cmd.Parameters["@FunctionalMgrEmails"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@SiteMgrEmails", SqlDbType.NVarChar, 1024);
cmd.Parameters["@SiteMgrEmails"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@CEOEmails", SqlDbType.NVarChar, 1024);
cmd.Parameters["@CEOEmails"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@ISMSMgrEmails", SqlDbType.NVarChar, 1024);
cmd.Parameters["@ISMSMgrEmails"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@SITECOEmails", SqlDbType.NVarChar, 1024);
cmd.Parameters["@SITECOEmails"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@QMGREmails", SqlDbType.NVarChar, 1024);
cmd.Parameters["@QMGREmails"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@REGIONALMGREmails", SqlDbType.NVarChar, 1024);
cmd.Parameters["@REGIONALMGREmails"].Direction = ParameterDirection.Output;
//Need to add Emails for general and regional manager .. 30/1/2012
cn.Open();
int iresult = cmd.ExecuteNonQuery();
string LatestIssueNumber = (string)cmd.Parameters["@LatestIssueNumber"].Value;
int LatestISID = (int)cmd.Parameters["@LatestISID"].Value;
string FunctionalMgrEmails = (string)cmd.Parameters["@FunctionalMgrEmails"].Value.ToString().Replace("|", ";");
string SiteMgrEmails = (string)cmd.Parameters["@SiteMgrEmails"].Value.ToString().Replace("|", ";");
string CEOEmails = (string)cmd.Parameters["@CEOEmails"].Value.ToString().Replace("|", ";");
string ISMSMgrEmails = (string)cmd.Parameters["@ISMSMgrEmails"].Value.ToString().Replace("|", ";");
string SITECOEmails = (string)cmd.Parameters["@SITECOEmails"].Value.ToString().Replace("|", ";");
string QMGREmails = (string)cmd.Parameters["@QMGREmails"].Value.ToString().Replace("|", ";");
string REGIONALMGREmails = (string)cmd.Parameters["@REGIONALMGREmails"].Value.ToString().Replace("|", ";");
//Response.Write("<br/>iresult=" + iresult + "<br/>FunctionalMgrEmails!!=[" + FunctionalMgrEmails + "]");
//Response.End();
//Response.Write("<br/>iresult=" + iresult + "<br/>hidRefFilenameOnly.Value!!=" + hidRefFilenameOnly.Value);
if (iresult > 0)
{
OfficeInfo info = (OfficeInfo)hOffices[Int32.Parse(sOfficeID)];
string sOffice = info.GetCode();
// Move attach files to propper issue number location
System.IO.Directory.CreateDirectory(Server.MapPath("..\\attachs\\" + sOffice + "\\" + LatestIssueNumber));
String sFilenamesFull = "";
String sFilenamesOnly = "";
for (i = 0; i <= (chkBoxLstRef.Items.Count - 1); i++)
{
sFilenamesFull = chkBoxLstRef.Items[i].Value;
sFilenamesOnly = chkBoxLstRef.Items[i].ToString();
//Response.Write("<br/>sFilenamesFull=" + sFilenamesFull);
//Response.Write("<br/>target path=" + Server.MapPath("..\\attachs\\" + sOffice + "\\" + LatestIssueNumber) + "\\" + sFilenamesOnly);
FileInfo finfo = new FileInfo(sFilenamesFull);
if (finfo.Exists)
{
System.IO.File.Move(sFilenamesFull, Server.MapPath("..\\attachs\\" + sOffice + "\\" + LatestIssueNumber) + "\\" + sFilenamesOnly);
}
}
//Response.End();
string subPath = sOfficeID;
System.IO.DirectoryInfo dirinfo = new System.IO.DirectoryInfo(Server.MapPath("..\\uploads\\" + subPath));
if (dirinfo.Exists)
{
System.IO.Directory.Delete(Server.MapPath("..\\uploads\\" + subPath), true);
}
if (LatestIssueNumber != tbIssueNumber.Text)
{
string s_message = "Your new Issue Number is [" + LatestIssueNumber + "]!!\\n";
s_message += "You previous Issue Number [" + tbIssueNumber.Text + "] cannot add to the CAR System because it already exist in the system.";
//MessageBox(s_message);
}
tbIssueNumber.Text = LatestIssueNumber;
iResult = 1;
///--------------------Mail for Approve---------------
SmtpMail.SmtpServer = Global.SMTP_SERVER;
MailMessage msgMail = new MailMessage();
//msgMail.Subject = "New issues need to approve";
msgMail.BodyFormat = MailFormat.Html;
//Response.Write("<br/>msgMail.To=" + msgMail.To);
//Response.Write("<br/>msgMail.From="+msgMail.From);
//Response.Write("<br/>msgMail.Subject="+msgMail.Subject);
//Response.End();
string dept = "";
string tgtlang = "";
foreach (ListItem lItem in pickerDepartments.toListBox.Items)
{
dept += lItem.Text + ",";
}
if (dept.Length > 0)
{
dept = dept.Substring(0, dept.Length-1);
}
foreach (ListItem lItem in pickerTgtLangs.toListBox.Items)
{
tgtlang += lItem.Text + ",";
}
if (tgtlang.Length > 0)
{
tgtlang = tgtlang.Substring(0, tgtlang.Length-1);
}
msgMail.From = "no-reply@carsystem.sdl.com";
msgMail.Subject = lstOrgOffice.SelectedItem.Text + " - [" + tbIssueNumber.Text + "] - " + lstCategory.SelectedItem.Text + " - " + lstSubCategory.SelectedItem.Text;
string strBody = "";
// Clear Bcc
//msgMail.Bcc = null;
msgMail.Bcc = "kmakwana@sdl.com;njadhav@sdl.com";
// Send Notification to CEO
if (!CEOEmails.Equals(""))
{
strBody = generateEmailBody(CEO, LatestISID, dept, tgtlang);
msgMail.To = CEOEmails;
sDebugEmails += "- CEO Mail sent! -";
msgMail.Body = strBody;
msgMail.BodyEncoding = System.Text.Encoding.UTF8;
SmtpMail.Send(msgMail);
}
// Send Notification to Functional Manager
if (!FunctionalMgrEmails.Equals(""))
{
strBody = generateEmailBody(FUNCTIONAL_MANAGER, LatestISID, dept, tgtlang);
msgMail.To = FunctionalMgrEmails;
sDebugEmails += "- Functional Manager Mail sent! -";
msgMail.Body = strBody;
msgMail.BodyEncoding = System.Text.Encoding.UTF8;
SmtpMail.Send(msgMail);
}
if (user.FirstName.Equals("nilesh"))
{
msgMail.To = "njadhav@sdl.com";
}
// Send Notification to ISMS Manager
if (!ISMSMgrEmails.Equals("") && lstOrgOffice.SelectedItem.Text.Equals("ISMS"))
{
strBody = generateEmailBody(ISMS_MANAGER, LatestISID, dept, tgtlang);
msgMail.To = ISMSMgrEmails;
sDebugEmails += "- ISMS Manager Mail sent! -";
msgMail.Body = strBody;
msgMail.BodyEncoding = System.Text.Encoding.UTF8;
SmtpMail.Send(msgMail);
}
// Send Notification to Site Coordinator
if (!SITECOEmails.Trim().Equals(""))
{
strBody = generateEmailBody(SITE_COORDINATOR, LatestISID, dept, tgtlang);
msgMail.To = SITECOEmails;
sDebugEmails += "- Site Coordinator Mail sent! -";
msgMail.Body = strBody;
msgMail.BodyEncoding = System.Text.Encoding.UTF8;
SmtpMail.Send(msgMail);
}
// Send Notification to Quality Coordinator
if (!SITECOEmails.Trim().Equals(""))
{
strBody = generateEmailBody(QUALITY_COORDINATOR, LatestISID, dept, tgtlang);
msgMail.To = QMGREmails;
sDebugEmails += "- Quality Manager Mail sent! -";
msgMail.Body = strBody;
msgMail.BodyEncoding = System.Text.Encoding.UTF8;
SmtpMail.Send(msgMail);
}
if (!SITECOEmails.Trim().Equals(""))
{
strBody = generateEmailBody(REGIONAL_MANAGER, LatestISID, dept, tgtlang);
msgMail.To = REGIONALMGREmails;
sDebugEmails += "- Regional Manager Mail sent! -";
msgMail.Body = strBody;
msgMail.BodyEncoding = System.Text.Encoding.UTF8;
SmtpMail.Send(msgMail);
}
if (user.FirstName.Equals("nilesh"))
{
msgMail.To = "njadhav@sdl.com";
}
// Send Notification to Site Manager
if (!SiteMgrEmails.Trim().Equals(""))
{
strBody = generateEmailBody(SITE_MANAGER, LatestISID, dept, tgtlang);
msgMail.To = SiteMgrEmails;
sDebugEmails += "- Site Manager Mail sent! -";
msgMail.Body = strBody;
msgMail.BodyEncoding = System.Text.Encoding.UTF8;
SmtpMail.Send(msgMail);
}
if (user.FirstName.Equals("nilesh"))
{
msgMail.To = "njadhav@sdl.com";
}
else
{
msgMail.To = user.SITE_CO_EMAIL + ", " + user.QUALITY_MANAGER_EMAIL;
}
msgMail.Bcc = "njadhav@sdl.com";
// strBody = generateEmailBody(USER, LatestISID, dept, tgtlang);
// msgMail.Body = strBody;
// msgMail.BodyEncoding = System.Text.Encoding.UTF8;
// SmtpMail.Send(msgMail);
////--------------------------------------
}
}
//catch (SqlException e)
//{
// Response.Write("<br/>error message!!=" + e.Message);
// // if (e.Number == 2627)
// // Message.InnerHtml = "ERROR: A record already exists with" +
// // " the same primary key";
// // else
// // Message.InnerHtml = "ERROR: Could not add record, please " +
// // " ensure the fields are correctly filled out";
// // Message.Style["color"] = "red";
//}
//catch (Exception e)
//{
// Response.Write("<br/>error message!!=" + e.Message);
//}
finally
{
// Close the connection
if (cn != null)
{
cn.Close();
}
}
}
return iResult;