USE [Resources02_Def]
GO


--Asset Due for Return
IF NOT EXISTS ( SELECT nID FROM [dbo].[tblMail]  WHERE tFrom='Asset Due for Return' AND nID<>24)
BEGIN
	--add the mail template
INSERT [dbo].[tblMail] ([nID], [tFrom], [tRecipients], [mCC], [tSubject], [mBodyText], [mAttachments], [bHTMLBody], [bSendIndividually], [nParentType], [bSendToQueue], [mBCC]) VALUES ((SELECT MAX(nID) FROM tblMail) + 1, N'Asset Due for Return', N'', N'', N'Notification: <F:Asset Type> <F:Name> Is Due To Be Returned', N'<table style="background: rgb(209, 219, 236); margin: 0 2%; padding-top:10px; padding-bottom:10px; font-family: Verdana, sans-serif;">	<tbody>		<tr>			<td style="width:10%;">&nbsp;</td>			<td style="width:600px;">			<table style="background-color:white; width:600px; margin:2 0%;">				<tbody>					<tr>						<td>						<div style="background: rgb(109, 183, 242); width:600px; height: 15px; ">&nbsp;</div>						</td>					</tr>					<tr>						<td align="center"><!--[if mso]>
								<img width="100" src="https://www.vizor.cloud/v2assets/images/vizor-top.png" alt="ITlogo" 
		                   	style="width:100px;"/>
								<![endif]--><!--[if !mso]--><img src="https://www.vizor.cloud/v2assets/images/vizor-top.png" style="width:100px" /> <!--[endif]--></td>					</tr>					<tr>						<td colspan="0" style="padding:20px;">						<p style="text-align: center; font-family: verdana,geneva,sans-serif; font-size: 13pt; "><strong>Your &lt;F:Asset Type&gt; &lt;F:Name&gt; is due for return</strong></p>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:55px; font-family:verdana,geneva,sans-serif;color: rgb(102, 102, 102);font-size: 10pt; ">Hello &lt;F:Allocated to Person&gt;,</td>								</tr>								<tr height="1" style="line-height:1px;font-size:1px;mso-line-height-rule: exactly;">									<td height="10" style="line-height:10px;font-size:1px;mso-line-height-rule: exactly;">&nbsp;</td>								</tr>								<tr>									<td style="padding-left:55px; font-family:verdana,geneva,sans-serif;color: rgb(102, 102, 102);font-size: 10pt; ">This is a reminder that your &lt;F:Asset Type&gt; &lt;F:Name&gt; is due for return on <strong style="color:rgb(102, 102, 102);font-family: verdana, geneva, sans-serif; font-size: 10pt;">&lt;F:Return Date&gt;&lt;F:Requested Until Time&gt;.</strong></td>								</tr>								<tr height="1" style="line-height:1px;font-size:1px;mso-line-height-rule: exactly;">									<td height="10" style="line-height:10px;font-size:1px;mso-line-height-rule: exactly;">&nbsp;</td>								</tr>							</tbody>						</table>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:100px;">									<table cellpadding="3" style="clear:both; ;font-size:10pt;font-family:verdana,geneva,sans-serif;">										<tbody>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Name:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Name&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Serial Number:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Serial Number&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Asset Tag:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Asset Tag&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Manufacturer:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Manufacturer&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Model:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Model&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">VIZOR ID:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;Issue Number&gt;</td>											</tr>										</tbody>									</table>									</td>								</tr>							</tbody>						</table>
						<div>&nbsp;</div>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:55px">									<div><span style="font-size: 10pt; font-family:verdana, geneva, sans-serif; color:#666666;">The &lt;F:Asset Type&gt; was provisioned on <strong style="font-family:verdana, geneva, sans-serif; color:#666666;"> &lt;F:Last Assigned Date&gt; </strong> <span style="font-size: 10pt; font-family:verdana, geneva, sans-serif; color:#666666;">at</span> <strong style="font-family:verdana, geneva, sans-serif; color:#666666;">&lt;F:Assigned Time&gt;. </strong> </span></div>									</td>								</tr>							</tbody>						</table>						&nbsp;
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:55px"><span style="font-size: 10pt; font-family: verdana,geneva,sans-serif; color: rgb(102, 102, 102);">Please return the &lt;F:Asset Type&gt;. </span></td>								</tr>							</tbody>						</table>						&nbsp;
						<table cellpadding="0" cellspacing="0">							<tbody>								<tr>									<td style="padding-left:55px;">									<div>									<table cellpadding="0" cellspacing="0" width="100%">										<tbody>											<tr><!--[if mso]>
																<td style="padding-left:100px;">
																	<v:roundrect
																		xmlns:v="urn:schemas-microsoft-com:vml"
																		xmlns:w="urn:schemas-microsoft-com:office:word" href="&lt;base url:&gt;/logon.asp?View=Resources%2FMy%20Assets&amp;nId=&lt;Issue Number&gt;&amp;Qry=27" target="_blank" style="height:62px; v-text-anchor:middle; width:230px;" arcsize="10%" stroke="f" fillcolor="#fbb03b">
																		<w:anchorlock/>
																		<center style="color:white; font-size:14px; font-family:verdana,geneva,sans-serif; font-weight:bold;">
                                                Click to View Details of the &lt;F:Asset Type&gt;
                                                    </center>
																	</v:roundrect>
																</td>
																<![endif]--><!--[if !mso]-->												<td style="padding-left:100px;">												<table cellpadding="0" cellspacing="0">													<tbody>														<tr>															<td bgcolor="#fbb03b" style="-webkit-border-radius: 5px; -moz-border-radius: 5px; border-radius: 5px; display:block;"><a href="&lt;base url:&gt;/logon.asp?View=Resources%2FMy%20Assets&amp;nId=&lt;Issue Number&gt;&amp;Qry=27" style="padding: 14px;width:180px;display: block;text-decoration: none;border:0;text-align: center;font-weight: bold;font-size: 14px;font-family: sans-serif;color: #ffffff;-moz-border-radius: 4px; -webkit-border-radius: 4px; border-radius: 4px;line-height:17px;" target="_blank">Click to View Details of the &lt;F:Asset Type&gt; </a></td>														</tr>													</tbody>												</table>												<!--[endif]--></td>											</tr>										</tbody>									</table>									</div>									</td>								</tr>							</tbody>						</table>						</td>					</tr>				</tbody>			</table>			</td>			<td style="width:10%">&nbsp;</td>		</tr>	</tbody></table>', N'', 1, 0, 3, 0, N'')



END
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) = 10000
	--delete the old one
	DELETE FROM tblMail WHERE tFrom='Asset Due for Return' AND nID=24
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) = 10000
	--change the ID for the notification
	UPDATE tblNotification SET nMailTemplate=(SELECT nID FROM tblMail WHERE tFrom='Asset Due for Return' AND nID<>24) WHERE nMailTemplate=24
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) <> 10000
	--rename the old one
	UPDATE tblMail SET tFrom = 'Orig_' + tFrom WHERE nID=24 AND tFrom NOT Like 'Orig_%'
GO



--Asset Status Requested
IF NOT EXISTS ( SELECT nID FROM [dbo].[tblMail]  WHERE tFrom='Asset Status Requested' AND nID<>28)
BEGIN
	--add the mail template
	INSERT [dbo].[tblMail] ([nID], [tFrom], [tRecipients], [mCC], [tSubject], [mBodyText], [mAttachments], [bHTMLBody], [bSendIndividually], [nParentType], [bSendToQueue], [mBCC]) VALUES ((SELECT MAX(nID) FROM tblMail) + 1, N'Asset Status Requested', N'', N'', N'Notification: <F:Asset Type> <F:Name> Status Is Requested', N'<table style="background: rgb(209, 219, 236); margin: 0 2%; padding-top:10px; padding-bottom:10px; font-family: Verdana, sans-serif;">	<tbody>		<tr>			<td style="width:10%;">&nbsp;</td>			<td style="width:600px;">			<table style="background-color:white; width:600px; margin:2 0%;">				<tbody>					<tr>						<td>						<div style="background: rgb(109, 183, 242); width:600px; height: 15px; ">&nbsp;</div>						</td>					</tr>					<tr>						<td align="center"><!--[if mso]>
																<img width="100" src="https://www.vizor.cloud/v2assets/images/vizor-top.png" alt="ITlogo" 
		                   	style="width:100px;"/>
																<![endif]--><!--[if !mso]--><img src="https://www.vizor.cloud/v2assets/images/vizor-top.png" style="width:100px" /> <!--[endif]--></td>					</tr>					<tr>						<td colspan="0" style="padding:20px;">						<p style="text-align: center; font-family: verdana,geneva,sans-serif; font-size: 13pt;"><strong>Your &lt;F:Name&gt; &lt;F:Asset Type&gt; status is requested</strong></p>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:55px; font-family:verdana,geneva,sans-serif;color: rgb(102, 102, 102);font-size: 10pt;">Your &lt;F:Name&gt; &lt;F:Asset Type&gt; <strong style="color:rgb(102, 102, 102);">needs its status to be set.</strong></td>								</tr>								<tr height="1" style="line-height:1px;font-size:1px;mso-line-height-rule: exactly;">									<td height="10" style="line-height:10px;font-size:1px;mso-line-height-rule: exactly;">&nbsp;</td>								</tr>								<tr>									<td style="padding-left:55px; font-family:verdana,geneva,sans-serif;color: rgb(102, 102, 102);font-size: 10pt;">This &lt;F:Asset Type&gt; was assigned to <strong style="color:rgb(102, 102, 102);">&lt;F:Allocated to Person&gt;.</strong></td>								</tr>								<tr height="1" style="line-height:1px;font-size:1px;mso-line-height-rule: exactly;">									<td height="10" style="line-height:10px;font-size:1px;mso-line-height-rule: exactly;">&nbsp;</td>								</tr>							</tbody>						</table>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:100px;">									<table cellpadding="3" style="clear:both; ;font-size:10pt;font-family:verdana,geneva,sans-serif;">										<tbody>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Name:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Name&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Serial Number:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Serial Number&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Asset Tag:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Asset Tag&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Manufacturer:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Manufacturer&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Model:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Model&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">VIZOR ID:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;Issue Number&gt;</td>											</tr>										</tbody>									</table>									</td>								</tr>							</tbody>						</table>
						<div>&nbsp;</div>
						<table cellpadding="0" cellspacing="0">							<tbody>								<tr>									<td>									<table cellpadding="0" cellspacing="0" width="100%">										<tbody>											<tr><!--[if mso]>
																							<td style="padding-left:140px;">
																								<v:roundrect
																									xmlns:v="urn:schemas-microsoft-com:vml"
																									xmlns:w="urn:schemas-microsoft-com:office:word" href="&lt;base url:&gt;/logon.asp?View=Resources%2FLibrary&amp;nId=&lt;Issue Number&gt;&amp;Qry=10" target="_blank" style="height:50px; v-text-anchor:middle; width:230px;" arcsize="10%" stroke="f" fillcolor="#fbb03b">
																									<w:anchorlock/>
																									<center style="color:white; font-size:15px; font-family:verdana,geneva,sans-serif;font-weight: bold;">
                                                 Click to View Details of the &lt;F:Asset Type&gt;	
                                                    </center>
																								</v:roundrect>
																							</td>
																							<![endif]--><!--[if !mso]-->												<td style="padding-left:140px;">												<table cellpadding="0" cellspacing="0">													<tbody>														<tr>															<td bgcolor="#fbb03b" style="-webkit-border-radius: 5px; -moz-border-radius: 5px; border-radius: 5px; display:block;"><a href="http://&lt;base url:&gt;/logon.asp?View=Resources%2FLibrary&amp;nId=&lt;Issue Number&gt;&amp;Qry=10" style="padding: 14px;width:180px;display: block;text-decoration: none;border:0;text-align: center;font-weight: bold;font-size: 15px;font-family: sans-serif;color: #ffffff;-moz-border-radius: 4px; -webkit-border-radius: 4px; border-radius: 4px;line-height:17px;" target="_blank">Click to View Details of the &lt;F:Asset Type&gt; </a></td>														</tr>													</tbody>												</table>												<!--[endif]--></td>											</tr>										</tbody>									</table>									</td>								</tr>							</tbody>						</table>						</td>					</tr>				</tbody>			</table>			</td>			<td style="width:10%">&nbsp;</td>		</tr>	</tbody></table>', N'', 1, 0, 3, 0, N'')

END
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) = 10000
	--delete the old one
	DELETE FROM tblMail WHERE tFrom='Asset Status Requested' AND nID=28
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) = 10000
	--change the ID for the notification
	UPDATE tblNotification SET nMailTemplate=(SELECT nID FROM tblMail WHERE tFrom='Asset Status Requested' AND nID<>28) WHERE nMailTemplate=28
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) <> 10000
	--rename the old one
	UPDATE tblMail SET tFrom = 'Orig_' + tFrom WHERE nID=28 AND tFrom NOT Like 'Orig_%'
GO


--Security Sensitive Asset Lost
IF NOT EXISTS ( SELECT nID FROM [dbo].[tblMail]  WHERE tFrom='Security Sensitive Asset Lost' AND nID<>26)
BEGIN
	--add the mail template
	INSERT [dbo].[tblMail] ([nID], [tFrom], [tRecipients], [mCC], [tSubject], [mBodyText], [mAttachments], [bHTMLBody], [bSendIndividually], [nParentType], [bSendToQueue], [mBCC]) VALUES ((SELECT MAX(nID) FROM tblMail) + 1, N'Security Sensitive Asset Lost', N'', N'', N'Notification: Security Sensitive <F:Asset Type> <F:Name> Is Marked As Lost ', N'<table style="background: rgb(209, 219, 236); margin: 0 2%; padding-top:10px; padding-bottom:10px; font-family: Verdana, sans-serif;">	<tbody>		<tr>			<td style="width:10%;">&nbsp;</td>			<td style="width:600px;">			<table style="background-color:white; width:600px; margin:2 0%;">				<tbody>					<tr>						<td>						<div style="background: rgb(109, 183, 242); width:600px; height: 15px; ">&nbsp;</div>						</td>					</tr>					<tr>						<td align="center"><!--[if mso]>
								<img width="100" src="https://www.vizor.cloud/v2assets/images/vizor-top.png" alt="ITlogo" 
		                   	style="width:100px;"/>
								<![endif]--><!--[if !mso]--><img src="https://www.vizor.cloud/v2assets/images/vizor-top.png" style="width:100px" /> <!--[endif]--></td>					</tr>					<tr>						<td colspan="0" style="padding:20px;">						<p style="text-align: center; font-family: verdana,geneva,sans-serif; font-size: 13pt;"><strong>Your Security Sensitive &lt;F:Asset Type&gt; &lt;F:Name&gt; has been lost</strong></p>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:55px; font-family:verdana,geneva,sans-serif;color: rgb(102, 102, 102);font-size: 10pt;">Your &lt;F:Asset Type&gt; &lt;F:Name&gt; has been reported as <strong style="color:rgb(102, 102, 102);">lost.</strong></td>								</tr>								<tr height="1" style="line-height:1px;font-size:1px;mso-line-height-rule: exactly;">									<td height="10" style="line-height:10px;font-size:1px;mso-line-height-rule: exactly;">&nbsp;</td>								</tr>								<tr>									<td style="padding-left:55px; font-family:verdana,geneva,sans-serif;color: rgb(102, 102, 102);font-size: 10pt;">This &lt;F:Asset Type&gt; is flagged as Security Sensitive and it was assigned to <strong style="color:rgb(102, 102, 102);">&lt;F:Allocated to Person&gt;.</strong></td>								</tr>								<tr height="1" style="line-height:1px;font-size:1px;mso-line-height-rule: exactly;">									<td height="10" style="line-height:10px;font-size:1px;mso-line-height-rule: exactly;">&nbsp;</td>								</tr>							</tbody>						</table>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:100px;">									<table cellpadding="3" style="clear:both; ;font-size:10pt;font-family:verdana,geneva,sans-serif;">										<tbody>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Name:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Name&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Serial Number:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Serial Number&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Asset Tag:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Asset Tag&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Manufacturer:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Manufacturer&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Model:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Model&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">VIZOR ID:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;Issue Number&gt;</td>											</tr>										</tbody>									</table>									</td>								</tr>							</tbody>						</table>
						<div>&nbsp;</div>
						<table cellpadding="0" cellspacing="0">							<tbody>								<tr>									<td>									<table cellpadding="0" cellspacing="0" width="100%">										<tbody>											<tr><!--[if mso]>
															<td style="padding-left:140px;">
																<v:roundrect
																	xmlns:v="urn:schemas-microsoft-com:vml"
																	xmlns:w="urn:schemas-microsoft-com:office:word" href="&lt;base url:&gt;/logon.asp?View=Resources%2FMy%20Assets&amp;nId=&lt;Issue Number&gt;&amp;Qry=27" target="_blank" style="height:62px; v-text-anchor:middle; width:230px;" arcsize="10%" stroke="f" fillcolor="#fbb03b">
																	<w:anchorlock/>
																	<center style="color:white; font-size:15px; font-family:verdana,geneva,sans-serif;font-weight: bold;">
                                                      Click to View Details of the &lt;F:Asset Type&gt;	
                                                    </center>
																</v:roundrect>
															</td>
															<![endif]--><!--[if !mso]-->												<td style="padding-left:140px;">												<table cellpadding="0" cellspacing="0">													<tbody>														<tr>															<td bgcolor="#fbb03b" style="-webkit-border-radius: 5px; -moz-border-radius: 5px; border-radius: 5px; display:block;"><a href="&lt;base url:&gt;/logon.asp?View=Resources%2FMy%20Assets&amp;nId=&lt;Issue Number&gt;&amp;Qry=27" style="padding: 14px;width:180px;display: block;text-decoration: none;border:0;text-align: center;font-weight: bold;font-size: 15px;font-family: sans-serif;color: #ffffff;-moz-border-radius: 4px; -webkit-border-radius: 4px; border-radius: 4px;line-height:17px;" target="_blank">Click to View Details of the &lt;F:Asset Type&gt; </a></td>														</tr>													</tbody>												</table>												<!--[endif]--></td>											</tr>										</tbody>									</table>									</td>								</tr>							</tbody>						</table>						</td>					</tr>				</tbody>			</table>			</td>			<td style="width:10%">&nbsp;</td>		</tr>	</tbody></table>', N'', 1, 0, 3, 0, N'')

END
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) = 10000
	--delete the old one
	DELETE FROM tblMail WHERE tFrom='Security Sensitive Asset Lost' AND nID=26
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) = 10000
	--change the ID for the notification
	UPDATE tblNotification SET nMailTemplate=(SELECT nID FROM tblMail WHERE tFrom='Security Sensitive Asset Lost' AND nID<>26) WHERE nMailTemplate=26
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) <> 10000
	--rename the old one
	UPDATE tblMail SET tFrom = 'Orig_' + tFrom WHERE nID=26 AND tFrom NOT Like 'Orig_%'
GO


--Asset Lost
IF NOT EXISTS ( SELECT nID FROM [dbo].[tblMail]  WHERE tFrom='Asset Lost' AND nID<>23)
BEGIN
	--add the mail template
	INSERT [dbo].[tblMail] ([nID], [tFrom], [tRecipients], [mCC], [tSubject], [mBodyText], [mAttachments], [bHTMLBody], [bSendIndividually], [nParentType], [bSendToQueue], [mBCC]) VALUES ((SELECT MAX(nID) FROM tblMail) + 1, N'Asset Lost', N'', N'', N'Notification: <F:Asset Type> <F:Name> Marked As Lost', N'<table style="background: rgb(209, 219, 236); margin: 0 2%; padding-top:10px; padding-bottom:10px; font-family: Verdana, sans-serif;">	<tbody>		<tr>			<td style="width:10%;">&nbsp;</td>			<td style="width:600px;">			<table style="background-color:white; width:600px; margin:2 0%;">				<tbody>					<tr>						<td>						<div style="background: rgb(109, 183, 242); width:600px; height: 15px; ">&nbsp;</div>						</td>					</tr>					<tr>						<td align="center"><!--[if mso]>
								<img width="100" src="https://www.vizor.cloud/v2assets/images/vizor-top.png" alt="ITlogo" 
		                   	style="width:100px;"/>
								<![endif]--><!--[if !mso]--><img src="https://www.vizor.cloud/v2assets/images/vizor-top.png" style="width:100px" /> <!--[endif]--></td>					</tr>					<tr>						<td colspan="0" style="padding:20px;">						<p style="text-align: center; font-family: verdana,geneva,sans-serif; font-size: 13pt;"><strong>Your &lt;F:Asset Type&gt; &lt;F:Name&gt; is reported as lost</strong></p>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:55px; font-family:verdana,geneva,sans-serif;color: rgb(102, 102, 102);font-size: 10pt;">Your &lt;F:Asset Type&gt; &lt;F:Name&gt; has been reported as <strong style="color:rgb(102, 102, 102);">lost.</strong></td>								</tr>								<tr height="1" style="line-height:1px;font-size:1px;mso-line-height-rule: exactly;">									<td height="10" style="line-height:10px;font-size:1px;mso-line-height-rule: exactly;">&nbsp;</td>								</tr>								<tr>									<td style="padding-left:55px; font-family:verdana,geneva,sans-serif;color: rgb(102, 102, 102);font-size: 10pt;">This &lt;F:Asset Type&gt; was assigned to <strong style="color:rgb(102, 102, 102);">&lt;F:Allocated to Person&gt;.</strong></td>								</tr>								<tr height="1" style="line-height:1px;font-size:1px;mso-line-height-rule: exactly;">									<td height="10" style="line-height:10px;font-size:1px;mso-line-height-rule: exactly;">&nbsp;</td>								</tr>							</tbody>						</table>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:100px;">									<table cellpadding="3" style="clear:both; ;font-size:10pt;font-family:verdana,geneva,sans-serif;">										<tbody>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Name:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Name&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Serial Number:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Serial Number&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Asset Tag:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Asset Tag&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Manufacturer:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Manufacturer&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Model:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Model&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">VIZOR ID:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;Issue Number&gt;</td>											</tr>										</tbody>									</table>									</td>								</tr>							</tbody>						</table>
						<div>&nbsp;</div>
						<table cellpadding="0" cellspacing="0">							<tbody>								<tr>									<td>									<table cellpadding="0" cellspacing="0" width="100%">										<tbody>											<tr><!--[if mso]>
															<td style="padding-left:140px;">
																<v:roundrect
																	xmlns:v="urn:schemas-microsoft-com:vml"
																	xmlns:w="urn:schemas-microsoft-com:office:word" href="&lt;base url:&gt;/logon.asp?View=Resources%2FMy%20Assets&amp;nId=&lt;Issue Number&gt;&amp;Qry=27" target="_blank" style="height:62px; v-text-anchor:middle; width:230px;" arcsize="10%" stroke="f" fillcolor="#fbb03b">
																	<w:anchorlock/>
																	<center style="color:white; font-size:15px; font-family:verdana,geneva,sans-serif;font-weight: bold;">
                                                      Click to View Details of the &lt;F:Asset Type&gt;	
                                                    </center>
																</v:roundrect>
															</td>
															<![endif]--><!--[if !mso]-->												<td style="padding-left:140px;">												<table cellpadding="0" cellspacing="0">													<tbody>														<tr>															<td bgcolor="#fbb03b" style="-webkit-border-radius: 5px; -moz-border-radius: 5px; border-radius: 5px; display:block;"><a href="&lt;base url:&gt;/logon.asp?View=Resources%2FMy%20Assets&amp;nId=&lt;Issue Number&gt;&amp;Qry=27" style="padding: 14px;width:180px;display: block;text-decoration: none;border:0;text-align: center;font-weight: bold;font-size: 15px;font-family: sans-serif;color: #ffffff;-moz-border-radius: 4px; -webkit-border-radius: 4px; border-radius: 4px;line-height:17px;" target="_blank">Click to View Details of the &lt;F:Asset Type&gt; </a></td>														</tr>													</tbody>												</table>												<!--[endif]--></td>											</tr>										</tbody>									</table>									</td>								</tr>							</tbody>						</table>						</td>					</tr>				</tbody>			</table>			</td>			<td style="width:10%">&nbsp;</td>		</tr>	</tbody></table>', N'', 1, 0, 3, 0, N'')
END
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) = 10000
	--delete the old one
	DELETE FROM tblMail WHERE tFrom='Asset Lost' AND nID=23
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) = 10000
	--change the ID for the notification
	UPDATE tblNotification SET nMailTemplate=(SELECT nID FROM tblMail WHERE tFrom='Asset Lost' AND nID<>23) WHERE nMailTemplate=23
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) <> 10000
	--rename the old one
	UPDATE tblMail SET tFrom = 'Orig_' + tFrom WHERE nID=23 AND tFrom NOT Like 'Orig_%'
GO


--Asset Due for Return
IF NOT EXISTS ( SELECT nID FROM [dbo].[tblMail]  WHERE tFrom='Asset Scrapped' AND nID<>27)
BEGIN
	--add the mail template
	INSERT [dbo].[tblMail] ([nID], [tFrom], [tRecipients], [mCC], [tSubject], [mBodyText], [mAttachments], [bHTMLBody], [bSendIndividually], [nParentType], [bSendToQueue], [mBCC]) VALUES ((SELECT MAX(nID) FROM tblMail) + 1, N'Asset Scrapped', N'', N'', N'Notification: <F:Name> <F:Asset Type> Reported As Scrapped', N'<table style="background: rgb(209, 219, 236); margin: 0 2%; padding-top:10px; padding-bottom:10px; font-family: Verdana, sans-serif;">	<tbody>		<tr>			<td style="width:10%;">&nbsp;</td>			<td style="width:600px;">			<table style="background-color:white; width:600px; margin:2 0%;">				<tbody>					<tr>						<td>						<div style="background: rgb(109, 183, 242); width:600px; height: 15px; ">&nbsp;</div>						</td>					</tr>					<tr>						<td align="center"><!--[if mso]>
								<img width="100" src="https://www.vizor.cloud/v2assets/images/vizor-top.png" alt="ITlogo" 
		                   	style="width:100px;"/>
								<![endif]--><!--[if !mso]--><img src="https://www.vizor.cloud/v2assets/images/vizor-top.png" style="width:100px" /> <!--[endif]--></td>					</tr>					<tr>						<td colspan="0" style="padding:20px;">						<p style="text-align: center; font-family: verdana,geneva,sans-serif; font-size: 13pt;"><span style="font-family: Verdana, sans-serif; font-size: 13pt;"><strong>Your &lt;F:Name&gt; &lt;F:Asset Type&gt; is reported as scrapped</strong> </span></p>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:55px; font-family:verdana,geneva,sans-serif;color: rgb(102, 102, 102);font-size: 10pt;">Your asset &lt;F:Name&gt; &lt;F:Asset Type&gt; has been <strong style="color:rgb(102, 102, 102);">scrapped.</strong></td>								</tr>								<tr height="1" style="line-height:1px;font-size:1px;mso-line-height-rule: exactly;">									<td height="10" style="line-height:10px;font-size:1px;mso-line-height-rule: exactly;">&nbsp;</td>								</tr>								<tr>									<td style="padding-left:55px; font-family:verdana,geneva,sans-serif;color: rgb(102, 102, 102);font-size: 10pt;">This &lt;F:Asset Type&gt; was assigned to <strong style="color:rgb(102, 102, 102);">&lt;F:Allocated to Person&gt;.</strong></td>								</tr>								<tr height="1" style="line-height:1px;font-size:1px;mso-line-height-rule: exactly;">									<td height="10" style="line-height:10px;font-size:1px;mso-line-height-rule: exactly;">&nbsp;</td>								</tr>							</tbody>						</table>
						<table cellpadding="0" cellspacing="0" width="100%">							<tbody>								<tr>									<td style="padding-left:100px;">									<table cellpadding="3" style="clear:both; ;font-size:10pt;font-family:verdana,geneva,sans-serif;">										<tbody>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Name:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Name&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Serial Number:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Serial Number&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Asset Tag:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Asset Tag&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Manufacturer:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Manufacturer&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">Model:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;F:Model&gt;</td>											</tr>											<tr>												<td style="background-color:white; color:rgb(102, 102, 102);">VIZOR ID:</td>												<td style="font-weight:bold; color:rgb(102, 102, 102);">&lt;Issue Number&gt;</td>											</tr>										</tbody>									</table>									</td>								</tr>							</tbody>						</table>						</td>					</tr>				</tbody>			</table>			</td>			<td style="width:10%">&nbsp;</td>		</tr>	</tbody></table>', N'', 1, 0, 3, 0, N'')

END
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) = 10000
	--delete the old one
	DELETE FROM tblMail WHERE tFrom='Asset Scrapped' AND nID=27
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) = 10000
	--change the ID for the notification
	UPDATE tblNotification SET nMailTemplate=(SELECT nID FROM tblMail WHERE tFrom='Asset Scrapped' AND nID<>27) WHERE nMailTemplate=27
GO

IF (SELECT TOP(1) nID FROM Resources01_Dat.dbo.tblNextDefect) <> 10000
	--rename the old one
	UPDATE tblMail SET tFrom = 'Orig_' + tFrom WHERE nID=27 AND tFrom NOT Like 'Orig_%'
GO



