Monday, November 21, 2011
Widget Application Part 3
.column{
width:306px;
margin:14px 0px 14px 14px;
background:#fff;
float:left;
min-height:50px;
}
.column .dragbox{
background:#fff;
position:relative;
/*border:1px solid #ddd;*/
margin:0px 0px 14px 0px;
}
.column .dragbox h2{
margin:0;
font-size:12px;
padding:5px;
color:#000;
font-family:Verdana;
cursor:move;
}
.dragbox-content{
background:#fff;
min-height:0px; margin:0px 0px 0px 0px;
font-family:'Lucida Grande', Verdana; font-size:0.8em; line-height:1.5em;
}
.column .placeholder{
background: #f0f0f0;
/*border:1px dashed #ddd;*/
}
.dragbox h2.collapse{
background:#f0f0f0 url('collapse.png') no-repeat top right;
}
.dragbox h2 .configure{
font-size:11px; font-weight:normal;
margin-right:30px; float:right;
}
.Edit
{
text-decoration:none;
color: #000000;
font-weight: normal;
font-size: 12px;
font-family: Arial;
font-style: normal;
}
.Close
{}
.content-div
{ word-break:break-all;
word-wrap: break-word;
}
.butSmallWidget
{
cursor: pointer;
height: 25px;
width: 56px;
font-family: Arial;
font-size: 12px;
font-weight: bold;
background-color: #000000;
background-image: url( '../images/Widgets/btnGo-back.gif' );
border: 0px;
background-repeat: no-repeat;
}
Widget Application Part 2
Partial Class Widget
Inherits System.Web.UI.Page
Dim objclsDatabaseLayer As New ALTO.clsDatabaseLayer
Dim stqry As String = String.Empty
Public GroupID As Integer
Public userSelectionList As CheckBoxList
Dim FileContents As String = String.Empty
Private glbName As ALTO.GetNameFunctions
Public BackGroundColorImage As String
Public BackGroundColorImageType As Int32
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
AjaxPro.Utility.RegisterTypeForAjax(GetType(CLAS.ClassroomAjaxFunctions))
GroupID = ALTO.Encryption.Utils.DecryptValue(Request.QueryString("GroupId"))
'Set BackGround Color/Image.
Dim dtRecords As DataTable = objclsDatabaseLayer.ExecuteProcedure(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, "DL_GlobalGetAllFields_Condition", "@TableName", "WidgetsHomeBackground", ParameterDirection.Input, "@IDFieldName", "GroupID", ParameterDirection.Input, "@IDFieldValue", ALTO.Encryption.Utils.DecryptValue(Request.QueryString("GroupID")), ParameterDirection.Input, "@OrderBy", "0", ParameterDirection.Input, "@IDFieldCharValue", " ", ParameterDirection.Input)
BackGroundColorImageType = dtRecords.Rows(0).Item("Type")
If IsDBNull(dtRecords.Rows(0).Item("BackGroundColor")) = False Then
BackGroundColorImage = dtRecords.Rows(0).Item("BackGroundColor")
End If
If IsDBNull(dtRecords.Rows(0).Item("BackGroundImagePath")) = False Then
BackGroundColorImage = dtRecords.Rows(0).Item("BackGroundImagePath").ToString().Replace("../", "")
End If
hidWholeColor.Value = dtRecords.Rows(0).Item("WholeBackGroundColor")
LoadCuteDetails()
LoadWidgets()
End Sub
Public Sub LoadCuteDetails()
Dim dtContent As DataTable = objclsDatabaseLayer.ExecuteProcedure(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, _
"DL_GroupHomePageGetContent", _
"@GroupID", GroupID, ParameterDirection.Input)
If dtContent.Rows.Count > 0 Then
FileContents = dtContent.Rows(0)("FileContents").ToString()
'For Documents and Page Hit Report'
Dim objCommonFunction As New ALTO.commonfunction
objCommonFunction.insertDocLog(dtContent.Rows(0)("ContentPageID"), 0, dtContent.Rows(0)("ContentName"), "O", GroupID, "WHP")
'End Here
End If
glbName = New ALTO.GetNameFunctions()
FileContents = Regex.Replace(FileContents, "username", "username", RegexOptions.IgnoreCase)
FileContents = Regex.Replace(FileContents, "firstname", "firstname", RegexOptions.IgnoreCase)
FileContents = Regex.Replace(FileContents, "lastname", "lastname", RegexOptions.IgnoreCase)
FileContents = FileContents.Replace("[username]", glbName.getLoginName(Session("user_id")))
FileContents = FileContents.Replace("[firstname]", glbName.getFirstName(Session("user_id")))
FileContents = FileContents.Replace("[lastname]", glbName.getLastName(Session("user_id")))
divBanner.InnerHtml = FileContents
End Sub
Public Sub LoadWidgets()
Try
Dim stqry As String
stqry = "SELECT DISTINCT ColumnNo FROM Userwidgets WHERE UserId=" & Session("user_id") & " AND GroupId= " & GroupID & " ORDER BY ColumnNo "
Dim dt As DataTable = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, stqry)
Dim strmain As String = String.Empty
Dim i, j As Int32
Dim objDiv As HtmlGenericControl
Dim objDivChild As HtmlGenericControl
'For BackGround Color.
stqry = "SELECT count(*) FROM Userwidgets WHERE IsShow =1 AND UserId=" & Session("user_id") & " AND GroupId= " & GroupID
Dim RecordCount As Int32 = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.ScalerValue, stqry)
For i = 0 To dt.Rows.Count - 1
objDiv = New HtmlGenericControl("div")
objDiv.Attributes.Add("class", "column")
objDiv.ID = dt.Rows(i)("ColumnNo")
If RecordCount > 0 Then
If BackGroundColorImageType = 1 Then
objDiv.Style.Add("background-color", BackGroundColorImage)
tdmain.Style.Add("background-color", BackGroundColorImage)
Else
objDiv.Style.Add("background-color", "transparent")
tdmain.Style.Add("background-image", "url(" & BackGroundColorImage & ")")
End If
Else
divmain.Style.Add("height", "0px")
End If
stqry = "SELECT * FROM Userwidgets WHERE IsShow =1 and ColumnNo ='" & dt.Rows(i)("ColumnNo") & "' AND UserId=" & Session("user_id") & " AND GroupId= " & GroupID & " ORDER BY RowNo "
Dim dtInner As DataTable = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, stqry)
For j = 0 To dtInner.Rows.Count - 1
objDivChild = New HtmlGenericControl("div")
objDivChild.Attributes.Add("class", "dragbox")
objDivChild.ID = dtInner.Rows(j)("Widgetid")
Dim objh2 As New HtmlGenericControl("h2")
Dim objhyp As New HtmlGenericControl("a")
objhyp.InnerHtml = "Edit"
objhyp.Attributes.Add("class", "Edit")
Dim objImg As New System.Web.UI.WebControls.Image
objImg.Attributes.Add("class", "Close")
objImg.Width = 15
objImg.Height = 15
objImg.ImageUrl = "Images/Widgets/wrong_sign.png"
Dim objDivContent As New HtmlGenericControl("div")
objDivContent.Attributes.Add("class", "dragbox-content")
Dim obj As Object
stqry = " Select * FROM widgetDetails WHERE WidgetId=" & dtInner.Rows(j)("WidgetId")
Dim dtwidgetDetails As DataTable = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, stqry)
stqry = " Select FontFamily,FontColor,BackGroundColor,Size,IsBold,IsItalic from widgetstyle"
Dim dtwidgetStyle As DataTable = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, stqry)
If dtInner.Rows(j)("Title") = "My e-Learning Courses" Then
Dim tbl As New HtmlTable()
tbl.Style.Add("width", "306px")
Dim tr As New HtmlTableRow()
Dim tdH2 As New HtmlTableCell()
Dim tdEdit As New HtmlTableCell()
Dim tdClose As New HtmlTableCell()
'tbl.Style.Add("border-bottom", "1px solid #eee")
tdH2.Style.Add("width", "261px")
tdH2.Style.Add("word-break", "break-all")
tdH2.Style.Add("word-wrap", "break-word")
If dtwidgetDetails.Rows(0)("IsHeadingOn") Then
objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading")
objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily"))
objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor"))
tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor"))
objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size"))
objhyp.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor"))
If dtwidgetStyle.Rows(0)("IsBold") Then
objh2.Style.Add("font-weight", "Bold")
Else
objh2.Style.Add("font-weight", "normal")
End If
If dtwidgetStyle.Rows(0)("IsItalic") Then
objh2.Style.Add("font-style", "italic")
Else
objh2.Style.Add("font-style", "normal")
End If
Else
objh2.InnerHtml = ""
End If
Dim objcontent_div As New HtmlGenericControl("div")
objcontent_div.Attributes.Add("class", "content-div")
objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId")
objcontent_div.Style.Add("display", "none")
obj = TryCast(Page.LoadControl("FunctionWidgets/eLearningTables.ascx"), FunctionWidgets_eLearningTables)
objcontent_div.Controls.Add(obj)
Dim objcontent_div1 As New HtmlGenericControl("div")
obj = TryCast(Page.LoadControl("FunctionWidgets/eLearningCourses.ascx"), FunctionWidgets_eLearningCourses)
objcontent_div1.Controls.Add(obj)
objDivChild.ID = dtInner.Rows(j)("WidgetId")
objhyp.ID = "Edit" & dtInner.Rows(j)("WidgetId")
objImg.ID = "Close" & dtInner.Rows(j)("WidgetId")
objhyp.Style.Add("cursor", "pointer")
objImg.Style.Add("cursor", "pointer")
tdH2.Controls.Add(objh2)
tdEdit.Controls.Add(objhyp)
tdClose.Controls.Add(objImg)
tr.Controls.Add(tdH2)
tr.Controls.Add(tdEdit)
tr.Controls.Add(tdClose)
tbl.Controls.Add(tr)
objDivChild.Controls.Add(tbl)
objDivChild.Controls.Add(objDivContent)
objDivContent.Controls.Add(objcontent_div)
objDivContent.Controls.Add(objcontent_div1)
objDiv.Controls.Add(objDivChild)
ElseIf dtInner.Rows(j)("Title") = "Media Monitor" Then
Dim tbl As New HtmlTable()
Dim tr As New HtmlTableRow()
Dim tdH2 As New HtmlTableCell()
Dim tdClose As New HtmlTableCell()
'tbl.Style.Add("border-bottom", "1px solid #eee")
tdH2.Style.Add("width", "281px")
tdH2.Style.Add("word-break", "break-all")
tdH2.Style.Add("word-wrap", "break-word")
If dtwidgetDetails.Rows(0)("IsHeadingOn") Then
objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading")
objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily"))
objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor"))
tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor"))
objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size"))
If dtwidgetStyle.Rows(0)("IsBold") Then
objh2.Style.Add("font-weight", "Bold")
Else
objh2.Style.Add("font-weight", "normal")
End If
If dtwidgetStyle.Rows(0)("IsItalic") Then
objh2.Style.Add("font-style", "italic")
Else
objh2.Style.Add("font-style", "normal")
End If
Else
objh2.InnerHtml = ""
End If
Dim objcontent_div As New HtmlGenericControl("div")
objcontent_div.Attributes.Add("class", "content-div")
objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId")
obj = TryCast(Page.LoadControl("FunctionWidgets/MediaMonitorControl.ascx"), FunctionWidgets_MediaMonitorControl)
objcontent_div.Controls.Add(obj)
objcontent_div.Style.Add("word-break", "keep-all")
objDivChild.ID = dtInner.Rows(j)("WidgetId")
objImg.ID = "Close" & dtInner.Rows(j)("WidgetId")
objImg.Style.Add("cursor", "pointer")
tdH2.Controls.Add(objh2)
tdClose.Controls.Add(objImg)
tr.Controls.Add(tdH2)
tr.Controls.Add(tdClose)
tbl.Controls.Add(tr)
objDivChild.Controls.Add(tbl)
objDivChild.Controls.Add(objDivContent)
objDivContent.Controls.Add(objcontent_div)
objDiv.Controls.Add(objDivChild)
ElseIf dtInner.Rows(j)("Title") = "Clock" Then
Dim tbl As New HtmlTable()
Dim tr As New HtmlTableRow()
Dim tdH2 As New HtmlTableCell()
Dim tdClose As New HtmlTableCell()
'tbl.Style.Add("border-bottom", "1px solid #eee")
tdH2.Style.Add("width", "281px")
tdH2.Style.Add("word-break", "break-all")
tdH2.Style.Add("word-wrap", "break-word")
If dtwidgetDetails.Rows(0)("IsHeadingOn") Then
'objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading")
objh2.ID = "clockID"
objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily"))
objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor"))
tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor"))
objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size"))
If dtwidgetStyle.Rows(0)("IsBold") Then
objh2.Style.Add("font-weight", "Bold")
Else
objh2.Style.Add("font-weight", "normal")
End If
If dtwidgetStyle.Rows(0)("IsItalic") Then
objh2.Style.Add("font-style", "italic")
Else
objh2.Style.Add("font-style", "normal")
End If
Else
objh2.InnerHtml = ""
End If
Dim objcontent_div As New HtmlGenericControl("div")
objcontent_div.Attributes.Add("class", "content-div")
objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId")
obj = TryCast(Page.LoadControl("FunctionWidgets/Clock.ascx"), FunctionWidgets_Clock)
objcontent_div.Controls.Add(obj)
If BackGroundColorImageType = 1 Then
objcontent_div.Style.Add("background-color", BackGroundColorImage)
Else
objcontent_div.Style.Add("background-image", "url(" & BackGroundColorImage & ")")
End If
objDivChild.ID = dtInner.Rows(j)("WidgetId")
objImg.ID = "Close" & dtInner.Rows(j)("WidgetId")
objImg.Style.Add("cursor", "pointer")
tdH2.Controls.Add(objh2)
tdClose.Controls.Add(objImg)
tr.Controls.Add(tdH2)
tr.Controls.Add(tdClose)
tbl.Controls.Add(tr)
objDivChild.Controls.Add(tbl)
objDivChild.Controls.Add(objDivContent)
objDivContent.Controls.Add(objcontent_div)
objDiv.Controls.Add(objDivChild)
ElseIf dtInner.Rows(j)("Title") = "Forums" Then
Dim tbl As New HtmlTable()
Dim tr As New HtmlTableRow()
Dim tdH2 As New HtmlTableCell()
Dim tdClose As New HtmlTableCell()
'tbl.Style.Add("border-bottom", "1px solid #eee")
tdH2.Style.Add("width", "281px")
tdH2.Style.Add("word-break", "break-all")
tdH2.Style.Add("word-wrap", "break-word")
If dtwidgetDetails.Rows(0)("IsHeadingOn") Then
objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading")
objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily"))
objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor"))
tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor"))
objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size"))
If dtwidgetStyle.Rows(0)("IsBold") Then
objh2.Style.Add("font-weight", "Bold")
Else
objh2.Style.Add("font-weight", "normal")
End If
If dtwidgetStyle.Rows(0)("IsItalic") Then
objh2.Style.Add("font-style", "italic")
Else
objh2.Style.Add("font-style", "normal")
End If
Else
objh2.InnerHtml = ""
End If
Dim objcontent_div As New HtmlGenericControl("div")
objcontent_div.Attributes.Add("class", "content-div")
objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId")
obj = TryCast(Page.LoadControl("FunctionWidgets/AccessForumsWidget.ascx"), FunctionWidgets_AccessForumsWidget)
objcontent_div.Controls.Add(obj)
objDivChild.ID = dtInner.Rows(j)("WidgetId")
objImg.ID = "Close" & dtInner.Rows(j)("WidgetId")
objImg.Style.Add("cursor", "pointer")
tdH2.Controls.Add(objh2)
tdClose.Controls.Add(objImg)
tr.Controls.Add(tdH2)
tr.Controls.Add(tdClose)
tbl.Controls.Add(tr)
objDivChild.Controls.Add(tbl)
objDivChild.Controls.Add(objDivContent)
objDivContent.Controls.Add(objcontent_div)
objDiv.Controls.Add(objDivChild)
ElseIf dtInner.Rows(j)("Title") = "Training Events And Programmes" Then
Dim tbl As New HtmlTable()
Dim tr As New HtmlTableRow()
Dim tdH2 As New HtmlTableCell()
Dim tdClose As New HtmlTableCell()
'tbl.Style.Add("border-bottom", "1px solid #eee")
tdH2.Style.Add("width", "281px")
tdH2.Style.Add("word-break", "break-all")
tdH2.Style.Add("word-wrap", "break-word")
If dtwidgetDetails.Rows(0)("IsHeadingOn") Then
objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading")
objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily"))
objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor"))
tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor"))
objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size"))
If dtwidgetStyle.Rows(0)("IsBold") Then
objh2.Style.Add("font-weight", "Bold")
Else
objh2.Style.Add("font-weight", "normal")
End If
If dtwidgetStyle.Rows(0)("IsItalic") Then
objh2.Style.Add("font-style", "italic")
Else
objh2.Style.Add("font-style", "normal")
End If
Else
objh2.InnerHtml = ""
End If
Dim objcontent_div As New HtmlGenericControl("div")
objcontent_div.Attributes.Add("class", "content-div")
objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId")
obj = TryCast(Page.LoadControl("FunctionWidgets/TrainingEventsAndProgrammes.ascx"), FunctionWidgets_TrainingEventsAndProgrammes)
objcontent_div.Controls.Add(obj)
objDivChild.ID = dtInner.Rows(j)("WidgetId")
objImg.ID = "Close" & dtInner.Rows(j)("WidgetId")
objImg.Style.Add("cursor", "pointer")
tdH2.Controls.Add(objh2)
tdClose.Controls.Add(objImg)
tr.Controls.Add(tdH2)
tr.Controls.Add(tdClose)
tbl.Controls.Add(tr)
objDivChild.Controls.Add(tbl)
objDivChild.Controls.Add(objDivContent)
objDivContent.Controls.Add(objcontent_div)
objDiv.Controls.Add(objDivChild)
ElseIf dtInner.Rows(j)("Title") = "Compliance Course" Then
Dim tbl As New HtmlTable()
Dim tr As New HtmlTableRow()
Dim tdH2 As New HtmlTableCell()
Dim tdEdit As New HtmlTableCell()
Dim tdClose As New HtmlTableCell()
'tbl.Style.Add("border-bottom", "1px solid #eee")
tdH2.Style.Add("width", "261px")
tdH2.Style.Add("word-break", "break-all")
tdH2.Style.Add("word-wrap", "break-word")
If dtwidgetDetails.Rows(0)("IsHeadingOn") Then
objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading")
objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily"))
objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor"))
tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor"))
objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size"))
objhyp.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor"))
If dtwidgetStyle.Rows(0)("IsBold") Then
objh2.Style.Add("font-weight", "Bold")
Else
objh2.Style.Add("font-weight", "normal")
End If
If dtwidgetStyle.Rows(0)("IsItalic") Then
objh2.Style.Add("font-style", "italic")
Else
objh2.Style.Add("font-style", "normal")
End If
Else
objh2.InnerHtml = ""
End If
Dim objcontent_div As New HtmlGenericControl("div")
objcontent_div.Attributes.Add("class", "content-div")
objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId")
objcontent_div.Style.Add("display", "none")
obj = TryCast(Page.LoadControl("FunctionWidgets/ComplianceWidgetTables.ascx"), FunctionWidgets_ComplianceWidgetTables_)
objcontent_div.Controls.Add(obj)
Dim objcontent_div1 As New HtmlGenericControl("div")
obj = TryCast(Page.LoadControl("FunctionWidgets/ComplianceWidgetCourses.ascx"), FunctionWidgets_ComplianceWidgetCourses)
objcontent_div1.Controls.Add(obj)
objDivChild.ID = dtInner.Rows(j)("WidgetId")
objhyp.ID = "Edit" & dtInner.Rows(j)("WidgetId")
objImg.ID = "Close" & dtInner.Rows(j)("WidgetId")
objhyp.Style.Add("cursor", "pointer")
objImg.Style.Add("cursor", "pointer")
tdH2.Controls.Add(objh2)
tdEdit.Controls.Add(objhyp)
tdClose.Controls.Add(objImg)
tr.Controls.Add(tdH2)
tr.Controls.Add(tdEdit)
tr.Controls.Add(tdClose)
tbl.Controls.Add(tr)
objDivChild.Controls.Add(tbl)
objDivChild.Controls.Add(objDivContent)
objDivContent.Controls.Add(objcontent_div)
objDivContent.Controls.Add(objcontent_div1)
objDiv.Controls.Add(objDivChild)
Else
Dim tbl As New HtmlTable()
Dim tr As New HtmlTableRow()
Dim tdH2 As New HtmlTableCell()
Dim tdClose As New HtmlTableCell()
'tbl.Style.Add("border-bottom", "1px solid #eee")
tdH2.Style.Add("width", "281px")
tdH2.Style.Add("word-break", "break-all")
tdH2.Style.Add("word-wrap", "break-word")
Dim objcontent_div As New HtmlGenericControl("div")
objcontent_div.ID = "content-div" & dtInner.Rows(j)("WidgetId")
objcontent_div.Attributes.Add("class", "content-div")
'Html Widgets
Dim strHeading As String = String.Empty
Dim strSubHeading As String = String.Empty
Dim strFooter As String = String.Empty
If dtwidgetDetails.Rows(0)("IsHeadingOn") Then
objh2.InnerHtml = dtwidgetDetails.Rows(0)("Heading")
objh2.Style.Add("font-family", dtwidgetStyle.Rows(0)("FontFamily"))
objh2.Style.Add("color", dtwidgetStyle.Rows(0)("FontColor"))
tbl.Style.Add("background-color", dtwidgetStyle.Rows(0)("BackGroundColor"))
objh2.Style.Add("font-size", dtwidgetStyle.Rows(0)("Size"))
If dtwidgetStyle.Rows(0)("IsBold") Then
objh2.Style.Add("font-weight", "Bold")
Else
objh2.Style.Add("font-weight", "normal")
End If
If dtwidgetStyle.Rows(0)("IsItalic") Then
objh2.Style.Add("font-style", "italic")
Else
objh2.Style.Add("font-style", "normal")
End If
Else
objh2.InnerHtml = ""
End If
If dtwidgetDetails.Rows(0)("IsSubHeadingOn") Then
Dim FontWeightStyle As String = String.Empty
If dtwidgetStyle.Rows(1)("IsBold") Then
FontWeightStyle = "font-weight:Bold;"
Else
FontWeightStyle = "font-weight:normal;"
End If
If dtwidgetStyle.Rows(1)("IsItalic") Then
FontWeightStyle = FontWeightStyle & "font-style:italic;"
Else
FontWeightStyle = FontWeightStyle & "font-style:normal;"
End If
strSubHeading = "" & dtwidgetDetails.Rows(0)("SubHeading") & " " & dtwidgetDetails.Rows(0)("Footer") & " " & strHeading & strSubHeading & _
"
"
objcontent_div.InnerHtml = strdtwidgetDetails
objcontent_div.Style.Add("word-break", "keep-all")
'End Here.
objImg.ID = "Close" & dtInner.Rows(j)("WidgetId")
objImg.Style.Add("cursor", "pointer")
objDivChild.ID = dtInner.Rows(j)("WidgetId")
tdH2.Controls.Add(objh2)
tdClose.Controls.Add(objImg)
tr.Controls.Add(tdH2)
tr.Controls.Add(tdClose)
tbl.Controls.Add(tr)
objDivChild.Controls.Add(tbl)
objDivChild.Controls.Add(objDivContent)
objDivContent.Controls.Add(objcontent_div)
objDiv.Controls.Add(objDivChild)
End If
Next
divmain.Controls.Add(objDiv)
Next
ShowWidgetStatusTable()
Catch ex As Exception
End Try
End Sub
Private Sub ShowWidgetStatusTable()
Try
Dim i As Integer
stqry = "SELECT Userwidgets.WidgetId,widgetDetails.Heading,Userwidgets.IsShow FROM Userwidgets" & _
" INNER JOIN widgetDetails ON widgetDetails.WidgetId =Userwidgets.WidgetId WHERE UserId=" & Session("user_id") & " AND GroupId= " & GroupID & " AND Userwidgets.widgetid NOT IN (1,2,3) ORDER BY Userwidgets.RowNo ,Userwidgets.ColumnNo "
Dim dtWidget As DataTable = objclsDatabaseLayer.ExecuteSQL(ALTO.clsDatabaseLayer.DbExecutionType.DataTable, stqry)
If dtWidget.Rows.Count > 0 Then
tdResetHomePage.Style.Add("display", "block")
userSelectionList = New CheckBoxList
userSelectionList.ID = "mylist"
userSelectionList.RepeatDirection = RepeatDirection.Horizontal
userSelectionList.CellPadding = 3
userSelectionList.CellSpacing = 10
userSelectionList.RepeatColumns = "3"
userSelectionList.RepeatLayout = RepeatLayout.Table
For i = 0 To dtWidget.Rows.Count - 1
Dim selectedItem As New ListItem(dtWidget.Rows(i)("Heading"), dtWidget.Rows(i)("WidgetId"))
selectedItem.Selected = CType(dtWidget.Rows(i)("IsShow"), Boolean)
selectedItem.Attributes.Add("ID", dtWidget.Rows(i)("WidgetId"))
userSelectionList.Items.Add(selectedItem)
Next
userSelectionList.Attributes.Add("onclick", "SetCheckedUnchecked();")
checkboxContainer.Controls.Add(userSelectionList)
End If
Catch ex As Exception
End Try
End Sub
Protected Sub btnReset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReset.Click
Response.Redirect("Widget.aspx?GroupId=" & Request.QueryString("GroupId"))
End Sub
End Class
| " & dtwidgetDetails.Rows(0)("BodyContent") & " |
Widget Application Part 1
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Widget.aspx.vb" Inherits="Widget" %>
<%@ Register Src="~/FunctionWidgets/eLearningTables.ascx" TagName="eLearningTablesWidget"
TagPrefix="uc1" %>
<%@ Register Src="~/FunctionWidgets/eLearningCourses.ascx" TagName="eLearningCoursesWidget"
TagPrefix="uc2" %>
<%@ Register Src="~/FunctionWidgets/MediaMonitorControl.ascx" TagName="MediaMonitorControl"
TagPrefix="uc3" %>
<%@ Register Src="~/FunctionWidgets/AccessForumsWidget.ascx" TagName="AccessForumsWidget"
TagPrefix="uc4" %>
<%@ Register Src="~/FunctionWidgets/Clock.ascx" TagName="Clock" TagPrefix="uc5" %>
<%@ Register Src="~/FunctionWidgets/TrainingEventsAndProgrammes.ascx" TagName="TrainingEventsAndProgrammes"
TagPrefix="uc6" %>
<%@ Register Src="~/FunctionWidgets/ComplianceWidgetTables.ascx" TagName="ComplianceWidgetTables"
TagPrefix="uc7" %>
<%@ Register Src="~/FunctionWidgets/ComplianceWidgetCourses.ascx" TagName="ComplianceWidgetCourses"
TagPrefix="uc8" %>
Widget Demo
IE SP
ALTER PROCEDURE [dbo].[ImportEvent]
@XmlDoc NVARCHAR(MAX),
@VendorDeliveryTypeflag TINYINT
AS
BEGIN
DECLARE @xmlHandle INT
DECLARE @tmpCostRRP DECIMAL(15, 2)
DECLARE @DeliveryStatusID INT
DECLARE @ManagerID INT
DECLARE @tmpSeparateCurrency VARCHAR(20)
DECLARE @StartTime DATETIME
DECLARE @EndTime DATETIME
DECLARE @TargetAudience NVARCHAR(100)
CREATE TABLE #tmpTable
(
ID INT IDENTITY,
OperationStatus NVARCHAR(500) COLLATE SQL_Latin1_General_CP1_CI_AS,
Title NVARCHAR(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
EventCode NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
ActivityCode NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CI_AS,
StartDate NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
EndDate NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
StartTime NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
EndTime NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
DeliveryType NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
DeliveryStatus NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
EventStatus NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
TargetAudience NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
MaxClassSize NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
MinClassSize NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
CostRRP NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
DailyOrTotal NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
Currency NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
LocationCode NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
Rooms NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CI_AS,
Checklist NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
InstructorCode NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
TJWorkflowID NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
ManagerID NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
CancellationBand NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
IsMainRoom BIT,
PriceBy NVARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS,
EventChargesMin NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
EventChargesMax NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
DisplayCostRange NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
TimeZoneIndex NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
DisplayCost NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS,
DisplayDiscount NVARCHAR(80) COLLATE SQL_Latin1_General_CP1_CI_AS
)
EXEC sp_xml_preparedocument @xmlhandle OUTPUT, @xmldoc
INSERT INTO #tmpTable
(
Title,
EventCode,
ActivityCode,
StartDate,
EndDate,
StartTime,
EndTime,
DeliveryType,
DeliveryStatus,
EventStatus,
TargetAudience,
MaxClassSize,
MinClassSize,
CostRRP,
DailyOrTotal,
Currency,
LocationCode,
Rooms,
Checklist,
InstructorCode,
TJWorkflowID,
IsMainRoom,
ManagerID,
CancellationBand,
EventChargesMin,
EventChargesMax,
DisplayCostRange,
TimeZoneIndex,
DisplayCost,
DisplayDiscount
)
SELECT Title,
EventCode,
ActivityCode,
StartDate,
EndDate,
StartTime,
EndTime,
DeliveryType,
DeliveryStatus,
EventStatus,
TargetAudience,
MaxClassSize,
MinClassSize,
CostRRP,
DailyOrTotal,
Currency,
LocationCode,
Rooms,
Checklist,
InstructorCode,
TJWorkflowID,
1,
ManagerID,
CancellationBand,
EventChargesMin,
EventChargesMax,
DisplayCostRange,
TimeZoneIndex,
DisplayCost,
DisplayDiscount
FROM OPENXML(@xmlhandle, '/NewDataSet/Table',2) WITH ( Title NVARCHAR(500), EventCode NVARCHAR(100), ActivityCode NVARCHAR(200), StartDate NVARCHAR(80), EndDate NVARCHAR(80), StartTime NVARCHAR(80), EndTime NVARCHAR(80), DeliveryType NVARCHAR(80), DeliveryStatus NVARCHAR(80), EventStatus NVARCHAR(80), TargetAudience NVARCHAR(100), MaxClassSize NVARCHAR(80), MinClassSize NVARCHAR(80), CostRRP NVARCHAR(80), DailyOrTotal NVARCHAR(80), Currency NVARCHAR(80), LocationCode NVARCHAR(100), Rooms NVARCHAR(200), Checklist NVARCHAR(100), InstructorCode NVARCHAR(100), TJWorkflowID NVARCHAR(100), ManagerID NVARCHAR(100), CancellationBand NVARCHAR(100), ISMainRoom BIT, EventChargesMin NVARCHAR(80), EventChargesMax NVARCHAR(80), DisplayCostRange NVARCHAR(80),TimeZoneIndex NVARCHAR(100) ,DisplayCost NVARCHAR(80),DisplayDiscount NVARCHAR(80) )
SET DATEFORMAT dmy
UPDATE #TmpTable
SET EventStatus = 'Active'
WHERE EventStatus = 'active'
UPDATE #TmpTable
SET EventStatus = 'Inactive'
WHERE EventStatus = 'inactive'
UPDATE #TmpTable
SET EventStatus = 'Draft'
WHERE EventStatus = 'draft'
UPDATE #TmpTable
SET DailyOrTotal = 'Daily'
WHERE DailyOrTotal = 'daily'
UPDATE #TmpTable
SET DailyOrTotal = 'Total'
WHERE DailyOrTotal = 'total'
UPDATE #TmpTable
SET DisplayCostRange = 'Yes'
WHERE DisplayCostRange = 'yes'
UPDATE #TmpTable
SET DisplayCostRange = 'No'
WHERE DisplayCostRange = 'no'
UPDATE #TmpTable
SET DisplayCost = 'Yes'
WHERE DisplayCost = 'yes'
UPDATE #TmpTable
SET DisplayCost = 'No'
WHERE DisplayCost = 'no'
UPDATE #TmpTable
SET DisplayDiscount = 'Yes'
WHERE DisplayDiscount = 'yes'
UPDATE #TmpTable
SET DisplayDiscount = 'No'
WHERE DisplayDiscount = 'no'
UPDATE #TmpTable
SET PriceBy = ( SELECT PriceBy
FROM CRActivity
WHERE CRActivity.ActivityCode = #TmpTable.ActivityCode
)
UPDATE #TmpTable
SET OperationStatus = 'Added'
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EventCode = '*'
WHERE EventCode IS NULL
OR RTRIM(LTRIM(EventCode)) = ''
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EventCode = '*'
WHERE EventCode IS NOT NULL
AND LEN(EventCode) > 20
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
ActivityCode = ' ' + ActivityCode + ''
WHERE DeliveryType = 'Public'
AND ActivityCode NOT IN (
SELECT CRActivity.ActivityCode
FROM CRActivity
INNER JOIN CRAssociatedCategory ON CRActivity.CRActivityID = CRAssociatedCategory.ActivityId
AND CRAssociatedCategory.CategoryID IN (
SELECT CRCategoryID
FROM CRCategory
WHERE IsPublic = 1 ) )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
ActivityCode = ' ' + ActivityCode + ''
WHERE ActivityCode NOT IN ( SELECT ActivityCode
FROM crActivity
WHERE ActivityStatus = 'Active' )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
ActivityCode = '*'
WHERE ActivityCode IS NULL
OR RTRIM(LTRIM(ActivityCode)) = ''
--Add by vikas on 11-01-2009
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
ActivityCode = ' ' + ActivityCode + ''
WHERE #TmpTable.ActivityCode NOT IN ( SELECT ActivityCode
FROM TJActivityCost )
AND #TmpTable.PriceBy = 'Company'
--Added by vikas on 12/01/2009
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
ActivityCode = ' ' + ActivityCode + ''
WHERE EventCode IN (
SELECT EventCode
FROM CREvent
WHERE IsProgram = 1
AND CREventID IN (
SELECT EventID
FROM CRDelegateEvent
WHERE DelegateStatus NOT IN ( 4, 11 ) ) )
AND EventCode NOT IN (
SELECT CREvent.EventCode
FROM CREvent
INNER JOIN #TmpTable ON CREvent.EventCode = #TmpTable.EventCode
WHERE [dbo].[CR_getActivityCode](CREvent.CRActivityID) = #TmpTable.ActivityCode )
--Added by hemant gupta for CancellationBand
UPDATE #TmpTable
SET CancellationBand = '*'
WHERE OperationStatus = 'Invalid'
AND LTRIM(RTRIM(CancellationBand)) ='' OR CancellationBand IS NULL
UPDATE #TmpTable
SET CancellationBand = ( SELECT TOP 1
CancellationBandOption
FROM CrActivity
WHERE ActivityCode = #TmpTable.ActivityCode
)
WHERE CancellationBand IS NULL
UPDATE #TmpTable
SET CancellationBand = 'C'
WHERE LTRIM(RTRIM(CancellationBand)) = 'company'
UPDATE #TmpTable
SET CancellationBand = 'V'
WHERE LTRIM(RTRIM(CancellationBand)) = 'vendor'
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
CancellationBand = '' + CancellationBand
+ ''
WHERE LTRIM(RTRIM(CancellationBand)) NOT IN ( 'C', 'V' )
--Ended by hemant gupta for CancellationBand
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
StartDate = '' + StartDate + ''
WHERE ISDATE(StartDate) = 0
AND StartDate <> 'TBC'
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
StartDate = '*'
WHERE StartDate IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EndDate = '' + EndDate + ''
WHERE EndDate IS NOT NULL
AND ISDATE(EndDate) = 0
AND EndDate <> 'TBC'
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EndDate = '' + EndDate + ''
WHERE EndDate IS NOT NULL
AND ISDATE(StartDate) = 1
AND EndDate = 'TBC'
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
StartDate = '' + StartDate + '',
EndDate = '' + EndDate + ''
WHERE EndDate IS NOT NULL
AND OperationStatus <> 'Invalid'
AND StartDate <> 'TBC'
AND EndDate <> 'TBC'
AND DATEDIFF(DAY, CONVERT(DATETIME, StartDate, 103),
CONVERT(DATETIME, EndDate, 103)) < 0
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
StartTime = '*'
WHERE StartTime IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
StartTime = '' + StartTime + ''
WHERE ISDATE(CONVERT(VARCHAR(10), GETDATE(), 105) + ' ' + StartTime) = 0
AND StartTime <> 'TBC'
UPDATE #TmpTable
SET OperationStatus = 'Invalid' ,
StartTime = '' + StartTime + ''
WHERE SUBSTRING(StartTime, LEN(StartTime)-1, 3) not in ('00','15','30','45')
UPDATE #TmpTable
SET OperationStatus = 'Invalid' ,
StartTime = '' + StartTime + ''
WHERE StartDate <> 'TBC'
AND StartTime = 'TBC'
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EndTime = '*'
WHERE EndTime IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EndTime = '' + EndTime + ''
WHERE ISDATE(CONVERT(VARCHAR(10), GETDATE(), 105) + ' ' + EndTime) = 0
AND EndTime <> 'TBC'
UPDATE #TmpTable
SET OperationStatus = 'Invalid' ,
EndTime = '' + EndTime + ''
WHERE SUBSTRING(EndTime, LEN(EndTime)-1, 3) not in ('00','15','30','45')
UPDATE #TmpTable
SET OperationStatus = 'Invalid' ,
EndTime = '' + EndTime + ''
WHERE StartDate <> 'TBC'
AND EndTime = 'TBC'
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
StartTime = '' + StartTime + '',
EndTime = '' + EndTime + ''
WHERE EndDate IS NOT NULL
AND ( StartDate = EndDate
AND StartTime >= EndTime
)
AND StartDate <> 'TBC'
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
TimeZoneIndex = '*'
WHERE TimeZoneIndex IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
#TmpTable.TimeZoneIndex = ' ' + TimeZoneIndex + ''
WHERE TimeZoneIndex IS NOT NULL AND TimeZoneIndex<>'TBC' AND ISNUMERIC(TimeZoneIndex)=0
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
#TmpTable.TimeZoneIndex = ' ' + TimeZoneIndex + ''
WHERE StartDate <> 'TBC'
AND (#TmpTable.TimeZoneIndex = 'TBC' OR #TmpTable.TimeZoneIndex = '-1')
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
TimeZoneIndex = ' ' + TimeZoneIndex + ''
WHERE TimeZoneIndex IS NOT NULL
AND TimeZoneIndex<>'TBC'
AND TimeZoneIndex<>'-1'
AND OperationStatus <> 'Invalid'
AND TimeZoneIndex NOT IN (SELECT [CRTimeZone].[TimeZoneIndex]
FROM [CRTimeZone]
WHERE [CRTimeZone].[TimeZoneIndex]=#TmpTable.TimeZoneIndex
AND [CRTimeZone].[Flag]=1 )
IF @VendorDeliveryTypeflag = 1
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DeliveryType = '' + DeliveryType
+ ''
WHERE DeliveryType NOT IN ( SELECT DeliveryType
FROM CRDeliveryType
WHERE DeliveryType <> 'Closed')
ELSE
BEGIN
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DeliveryType = '' + DeliveryType
+ ''
WHERE DeliveryType NOT IN ( SELECT DeliveryType
FROM CRDeliveryType )
END
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DeliveryType = '*'
WHERE DeliveryType IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DeliveryStatus = '' + DeliveryStatus
+ ''
WHERE DeliveryStatus NOT IN ( SELECT DeliveryStatus
FROM CRDeliveryStatus )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DeliveryStatus = '*'
WHERE DeliveryStatus IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DeliveryStatus = '' + DeliveryStatus
+ ''
WHERE EventCode IN (
SELECT EventCode
FROM CREvent
WHERE IsProgram = 1
AND DeliveryStatusID IN (
SELECT DeliveryStatusID
FROM CRDeliveryStatus
WHERE DeliveryStatus IN ( 'Delivered', 'Complete' ) ) )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EventStatus = '*'
WHERE EventStatus IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EventStatus = '' + EventStatus + ''
WHERE EventStatus NOT IN ( 'Active', 'Inactive', 'Draft' )
--Added by vikas on 08/03/2010
--Can't register status if delegate is registered on the event.
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EventStatus = '' + EventStatus + ''
WHERE EventCode IN (
SELECT EventCode
FROM CREvent
WHERE IsProgram = 1
AND CREventID IN (
SELECT DISTINCT
EventID
FROM CRDelegateEvent
WHERE delegatestatus NOT IN ( 4, 11 ) ) )
AND EventCode NOT IN (
SELECT CREvent.EventCode
FROM CREvent
INNER JOIN #TmpTable ON CREvent.EventCode = #TmpTable.EventCode
WHERE CREvent.EventStatus = #TmpTable.EventStatus )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
TargetAudience = '' + TargetAudience
+ ''
WHERE TargetAudience <> 'ALL'
AND TargetAudience NOT IN ( SELECT GroupName
FROM groups )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
TargetAudience = '*'
WHERE TargetAudience IS NULL
--Added by vikas on 08/03/2010
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
TargetAudience = '' + TargetAudience
+ ''
WHERE #TmpTable.TargetAudience <> 'ALL'
AND EventCode IN (
SELECT EventCode
FROM CREvent
WHERE IsProgram = 1
AND CREventID IN (
SELECT EventID
FROM CRDelegateEvent
WHERE delegatestatus NOT IN ( 4, 11 ) ) )
AND EventCode NOT IN (
SELECT CREvent.EventCode
FROM CREvent
INNER JOIN #TmpTable ON CREvent.EventCode = #TmpTable.EventCode
INNER JOIN CREventTargetAudience ON CREvent.CREventID = CREventTargetAudience.CREventID
WHERE CREventTargetAudience.WorkgroupID = ( SELECT GroupID
FROM Groups
WHERE GroupName = #TmpTable.TargetAudience
)
OR CREventTargetAudience.WorkgroupID = 0 )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
TargetAudience = '' + TargetAudience
+ ''
WHERE #TmpTable.TargetAudience = 'ALL'
AND EventCode IN (
SELECT EventCode
FROM CREvent
WHERE IsProgram = 1
AND CREventID IN (
SELECT EventID
FROM CRDelegateEvent
WHERE DelegateStatus NOT IN ( 4, 11 ) ) )
AND EventCode NOT IN (
SELECT CREvent.EventCode
FROM CREvent
INNER JOIN #TmpTable ON CREvent.EventCode = #TmpTable.EventCode
INNER JOIN CREventTargetAudience ON CREvent.CREventID = CREventTargetAudience.CREventID
WHERE CREventTargetAudience.WorkgroupID = 0 )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
MaxClassSize = '' + MaxClassSize + ''
WHERE ISNUMERIC(MaxClassSize) = 0
AND MaxClassSize IS NOT NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
MinClassSize = '' + MinClassSize + ''
WHERE ISNUMERIC(MinClassSize) = 0
AND MinClassSize IS NOT NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
MaxClassSize = '' + MaxClassSize + ''
WHERE MinClassSize IS NOT NULL
AND MaxClassSize IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
MinClassSize = '' + MinClassSize + ''
WHERE MaxClassSize IS NOT NULL
AND MinClassSize IS NULL
----------------------------------------
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
MaxClassSize = '' + MaxClassSize + '',
MinClassSize = '' + MinClassSize + ''
WHERE ISNUMERIC(MinClassSize) = 1
AND ISNUMERIC(MaxClassSize) = 1
AND CAST(MaxClassSize AS DECIMAL) < CAST(MinClassSize AS DECIMAL)
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
CostRRP = '' + CostRRP + ''
WHERE ISNUMERIC(CostRRP) = 0
AND CostRRP IS NOT NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EventChargesMin = '' + EventChargesMin
+ ''
WHERE ISNUMERIC(EventChargesMin) = 0
AND EventChargesMin IS NOT NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EventChargesMax = '' + EventChargesMax
+ ''
WHERE ISNUMERIC(EventChargesMax) = 0
AND EventChargesMax IS NOT NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
EventChargesMin = '' + EventChargesMin
+ ''
WHERE EventChargesMin > EventChargesMax
AND ISNUMERIC(EventChargesMin) <> 0
AND ISNUMERIC(EventChargesMax) <> 0
AND EventChargesMin IS NOT NULL
AND EventChargesMax IS NOT NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DailyOrTotal = '' + DailyOrTotal + ''
WHERE DailyOrTotal IS NOT NULL
AND DailyOrTotal NOT IN ( 'Daily', 'Total' )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
Currency = '' + Currency + ''
WHERE Currency IS NOT NULL
AND Currency NOT IN ( SELECT Currency
FROM CRCurrency )
--Added by vikas on 08/03/2010
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
Currency = '' + Currency + ''
WHERE Currency IS NOT NULL
AND EventCode IN (
SELECT EventCode
FROM CREvent
WHERE IsProgram = 1
AND CREventID IN (
SELECT DISTINCT
EventID
FROM CRDelegateEvent
WHERE delegatestatus NOT IN ( 4, 11 ) ) )
AND EventCode NOT IN (
SELECT CREvent.EventCode
FROM CREvent
INNER JOIN #TmpTable ON CREvent.EventCode = #TmpTable.EventCode
WHERE CREvent.Currency = #TmpTable.Currency )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
LocationCode = '*'
WHERE LocationCode IS NULL
OR RTRIM(LTRIM(LocationCode)) = ''
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
LocationCode = ' Use TBC '
WHERE LocationCode <> 'TBC'
AND LocationCode NOT IN ( SELECT LocationCode
FROM CRLocation
WHERE LocationStatus = 'Active' )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
LocationCode = '' + LocationCode + ''
WHERE LocationCode <> 'TBC'
AND LocationCode NOT IN (
SELECT DISTINCT
crLocation.LocationCode
FROM crLocation
WHERE crLocation.locationstatus = 'Active'
AND crLocation.CRLocationID <> 0
AND crLocation.CrLocationID IN (
SELECT CrLocationId
FROM CrRooms
WHERE status = 'Active' )
AND ( crLocation.crvendorid = 0
OR crLocation.CRVendorid IN (
SELECT CRVendorActivity.CRVendorActivityid
FROM CRVendorActivity
INNER JOIN CRActivity ON crvendoractivity.vendorcode = cractivity.crvendoractivityID
AND CRActivity.ActivityCode = #TmpTable.ActivityCode )
) )
--Added by vikas on 08/03/2010
-- UPDATE #TmpTable SET OperationStatus='Invalid',LocationCode= ' '+ LocationCode +''
-- WHERE EventCode IN (SELECT EventCode FROM CREvent WHERE IsProgram=1 AND CREventID IN (SELECT EventID FROM CRDelegateEvent WHERE DelegateStatus NOT IN (4,11)))
-- AND EventCode NOT IN (SELECT CREvent.EventCode FROM CREvent INNER JOIN #TmpTable on CREvent.EventCode=#TmpTable.EventCode WHERE (SELECT LocationCode FROM CRLocation WHERE CRLocationID=CREvent.CRLocationID)=#TmpTable.LocationCode)
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
Rooms = ' ' + Rooms + ''
WHERE LocationCode <> 'TBC'
AND Rooms IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
Rooms = ' ' + Rooms + ''
WHERE LocationCode <> 'TBC'
AND ( Rooms IS NULL
OR Rooms NOT IN (
SELECT RoomName
FROM crRooms
WHERE Status = 'Active'
AND CRLocationID IN (
SELECT CRLocationID
FROM CRLocation
WHERE LocationCode = #TmpTable.LocationCode
AND LocationCode IS NOT NULL
AND LocationCode <> 'TBC' ) )
)
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
InstructorCode = ' * '
WHERE InstructorCode IS NULL
OR RTRIM(LTRIM(InstructorCode)) = ''
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
InstructorCode = ' Use TBC '
WHERE InstructorCode <> 'TBC'
AND InstructorCode NOT IN (
SELECT InstructorCode
FROM CRInstructor
WHERE InstructorStatus = 'Active' )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
InstructorCode = '' + InstructorCode
+ ''
WHERE InstructorCode <> 'TBC'
AND InstructorCode NOT IN (
SELECT CRInstructor.InstructorCode
FROM CRInstructor
WHERE CRInstructor.CRInstructorID <> 0
AND CRInstructor.InstructorStatus = 'Active'
AND ( CRInstructor.CRVendorID = 0
OR CRvendorID IN (
SELECT CRVendorActivity.CRVendorActivityID
FROM CRVendorActivity
INNER JOIN CRActivity ON CRVendorActivity.vendorcode = CRActivity.CRVendorActivityID
AND CRActivity.ActivityCode = #TmpTable.ActivityCode )
) )
--Added by vikas on 08/03/2010
-- UPDATE #TmpTable SET OperationStatus='Invalid',InstructorCode= ' '+ InstructorCode +''
-- WHERE EventCode IN (SELECT EventCode FROM CREvent WHERE IsProgram=1 AND CREventID IN (SELECT EventID FROM CRDelegateEvent WHERE DelegateStatus NOT IN (4,11)))
-- AND EventCode NOT IN (SELECT CREvent.EventCode FROM CREvent INNER JOIN #TmpTable on CREvent.EventCode=#TmpTable.EventCode WHERE (SELECT InstructorCode FROM CRInstructor WHERE CRInstructorID=(SELECT CRInstructorID FROM crEventInstructor WHERE crEventInstructor.CREventID=CREvent.CREventID AND )=#TmpTable.InstructorCode)
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
TJWorkflowID = ' * '
WHERE TJWorkflowID IS NULL
OR RTRIM(LTRIM(TJWorkflowID)) = ''
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
TJWorkflowID = '' + TJWorkflowID + ''
WHERE TJWorkflowID NOT IN ( SELECT CAST(TJWorkflowID AS VARCHAR)
FROM TJWorkflow
WHERE IsUsed = 1 ) --and OperationStatus<>'Invalid'
--Added by vikas on 08/03/2010
IF @VendorDeliveryTypeflag <> 1
BEGIN
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
TJWorkflowID = '' + TJWorkflowID + ''
WHERE EventCode IN (
SELECT EventCode
FROM CREvent
WHERE IsProgram = 1
AND CREventID IN (
SELECT EventID
FROM CRDelegateEvent
WHERE DelegateStatus NOT IN ( 4, 11 ) ) )
AND EventCode NOT IN (
SELECT CREvent.EventCode
FROM CREvent
INNER JOIN #TmpTable ON CREvent.EventCode = #TmpTable.EventCode
WHERE CAST(CREvent.TJWorkflowID AS VARCHAR) = #TmpTable.TJWorkflowID )
END
----------vikas 03/08/2010----------------
UPDATE #TmpTable
SET OperationStatus='Invalid',
Checklist= ' '+ Checklist +''
WHERE Checklist IS NOT NULL AND StartDate='TBC'
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
Checklist = ' ' + Checklist + ''
WHERE Checklist IS NOT NULL
AND Checklist NOT IN (SELECT [Name]
FROM [TJChecklist]
WHERE [Name] = #TmpTable.Checklist)
----------End by vikas 03/08/2010----------------
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DeliveryStatus = ''
+ 'Delivered, Cancelled and Completed cannot be updated'
+ ''
WHERE EventCode IN ( SELECT EventCode
FROM CREvent
WHERE EventCode = #TmpTable.EventCode
AND DeliveryStatusid IN ( 3, 4, 5 ) )
UPDATE #TmpTable
SET DisplayCostRange = 'No'
WHERE LTRIM(RTRIM(DisplayCostRange)) = ''
OR DisplayCostRange IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DisplayCostRange = '' + DisplayCostRange
+ ''
WHERE UPPER(DisplayCostRange) NOT IN ( 'YES', 'NO' )
UPDATE #TmpTable
SET DisplayCost = 'No'
WHERE LTRIM(RTRIM(DisplayCost)) = ''
OR DisplayCost IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DisplayCost = '' + DisplayCost
+ ''
WHERE UPPER(DisplayCost) NOT IN ( 'YES', 'NO' )
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DisplayCost = '' + DisplayCost
+ ''
WHERE UPPER(DisplayCost) IN ('YES') and (Select displayCost from cractivity where ActivityCode =#tmpTable.ActivityCode) = 0
UPDATE #TmpTable
SET DisplayDiscount = 'No'
WHERE LTRIM(RTRIM(DisplayDiscount)) = ''
OR DisplayDiscount IS NULL
UPDATE #TmpTable
SET OperationStatus = 'Invalid',
DisplayDiscount = '' + DisplayDiscount
+ ''
WHERE UPPER(DisplayDiscount) NOT IN ( 'YES', 'NO' )
UPDATE #TmpTable
SET OperationStatus = 'Updated'
WHERE EventCode IN ( SELECT EventCode
FROM CREvent
WHERE EventCode = #TmpTable.EventCode
AND IsProgram = 1 )
AND OperationStatus NOT IN ( 'Invalid' )
UPDATE #TmpTable
SET OperationStatus = 'Duplicate'
WHERE ID NOT IN ( SELECT MIN(ID)
FROM #TmpTable
GROUP BY EventCode )
AND EventCode NOT IN ( '', '*' )
DECLARE @EventID INT
DECLARE @ID INT
DECLARE @InstructorCode INT
DECLARE @CheckListId INT
DECLARE @SDate AS DATETIME
DECLARE @EDate AS DATETIME
DECLARE @IID AS INT
DECLARE @RoomID AS INT
DECLARE @TimeID AS INT
DECLARE @Gid AS INT
DECLARE @SEndDate AS DATETIME
DECLARE @SStartDate AS DATETIME
DECLARE @PreActivity AS INT
DECLARE @CurrentActivity AS INT
DECLARE @VendorName AS VARCHAR(200)
DECLARE @OldChecklistID INT
SET @PreActivity = ''
SET @VendorName = ''
DECLARE @EventIDs AS VARCHAR(8000)
SET @EventIDs = ''
DECLARE @Duration AS INT
DECLARE @DurationType VARCHAR(10)
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @EvCode AS NVARCHAR(200)
DECLARE @EvTitile AS NVARCHAR(200)
DECLARE @Title NVARCHAR(500)
DECLARE @ActivityCode NVARCHAR(50)
DECLARE @EventCode NVARCHAR(100)
DECLARE @StDate NVARCHAR(80)
DECLARE @EdDate NVARCHAR(80)
DECLARE @DeliveryType NVARCHAR(80)
DECLARE @DeliveryStatus NVARCHAR(80)
DECLARE @EventStatus NVARCHAR(80)
DECLARE @MaxClassSize NVARCHAR(80)
DECLARE @MinClassSize NVARCHAR(80)
DECLARE @CostRRP NVARCHAR(80)
DECLARE @DailyOrTotal NVARCHAR(80)
DECLARE @Currency NVARCHAR(100)
DECLARE @LocationCode NVARCHAR(100)
DECLARE @CancellationBand NVARCHAR(100)
DECLARE @Checklist NVARCHAR(100)
DECLARE @OldActCode NVARCHAR(100)
DECLARE @NewActCode NVARCHAR(100)
DECLARE @PriceByType NVARCHAR(50)
DECLARE @EventCurrency NVARCHAR(100)
DECLARE @FeeCurrency NVARCHAR(100)
DECLARE @ActCode NVARCHAR(50)
DECLARE @EventChargesMin NVARCHAR(80)
DECLARE @EventChargesMax NVARCHAR(80)
DECLARE @DisplayCostRange NVARCHAR(80)
DECLARE @TJWorkflowID NVARCHAR(50)
DECLARE @TimeZoneIndex NVARCHAR(50)
DECLARE @StTime NVARCHAR(80)
DECLARE @EdTime NVARCHAR(80)
DECLARE @DisplayCost NVARCHAR(80)
DECLARE @DisplayDiscount NVARCHAR(80)
DECLARE crTmp CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR SELECT ID,
ActivityCode
FROM #TmpTable
WHERE OperationStatus = 'Added'
ORDER BY ActivityCode
OPEN crTmp
FETCH NEXT FROM crTmp INTO @ID, @ActCode
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
SET @CheckListId = ( SELECT ChecklistId
FROM tjchecklist,
#TmpTable
WHERE tjchecklist.[name] = #TmpTable.Checklist
AND ID = @ID
)
SELECT @Duration = Duration,
@DurationType = DurationType
FROM CrActivity,
#TmpTable
WHERE CrActivity.ActivityCode = #TmpTable.ActivityCode
AND ID = @ID
SELECT @StartDate = ( CASE WHEN StartDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
ELSE StartDate
END )
FROM #TmpTable
WHERE OperationStatus = 'Added'
AND ID = @ID
IF ( @DurationType = 'Days'
OR @DurationType = 'Day'
)
SELECT @EndDate = ( CASE WHEN StartDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
ELSE CONVERT(DATETIME, DATEADD(DAY, @Duration - 1, @StartDate), 103)
END )
FROM #TmpTable
WHERE OperationStatus = 'Added'
AND ID = @ID
ELSE
SELECT @EndDate = ( CASE WHEN StartDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
ELSE @StartDate
END )
FROM #TmpTable
WHERE OperationStatus = 'Added'
AND ID = @ID
----Added by vikas to insert currency in case of priceBy Deleagate or Company
SELECT @PriceByType = CRActivity.PriceBy,
@FeeCurrency = CRActivity.FeeCurrency
FROM CRActivity
INNER JOIN #TmpTable ON CrActivity.ActivityCode = #TmpTable.ActivityCode
AND #TmpTable.OperationStatus = 'Added'
AND #TmpTable.ID = @ID
IF UPPER(@PriceByType) = 'COMPANY'
BEGIN
SET @EventCurrency = @FeeCurrency
--Set CostRRP=0 in case of activity's PriceBy='Company'
UPDATE #TmpTable
SET Currency = @EventCurrency,
CostRRP = 0
WHERE OperationStatus = 'Added'
AND ID = @ID
END
INSERT INTO CREvent
(
EventCode,
[CRActivityID],
[ActivityType],
[Title],
[StartDate],
[EndDate],
[DeliveryTypeID],
[DeliveryStatusID],
[EventStatus],
[MaxClassSize],
[MinClassSize],
[NoOfInstructor],
[CostRRP],
[DailyOrWeekly],
[Currency],
[CRLocationID],
[EventStartTime],
[EventEndTime],
[CreatedGroupID],
[CreatedDate],
[ModifiedDate],
[IsLocationConflict],
[IsInstructorConflict],
[IsProgram],
CheckListTemplateID,
TJWorkflowID,
DisplayCost,
DisplayTime,
DisplayCostRange,
DisplayDiscount,
CancellationBandOption,
[TimeZoneIndex],
DisplayTimeZone
)
SELECT EventCode,
( SELECT TOP 1
CRActivityID
FROM CrActivity
WHERE ActivityCode = #TmpTable.ActivityCode
),
( SELECT TOP 1
ActivityTypeID
FROM CrActivity
WHERE ActivityCode = #TmpTable.ActivityCode
),
Title,
( CASE WHEN StartDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
ELSE StartDate
END ),
( CASE WHEN EndDate IS NULL THEN @EndDate
ELSE ( CASE WHEN StartDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
ELSE EndDate
END )
END ),
( SELECT DeliveryTypeID
FROM CRDeliveryType
WHERE DeliveryType = #TmpTable.DeliveryType
),
( SELECT DeliveryStatusID
FROM CRDeliveryStatus
WHERE DeliveryStatus = #TmpTable.DeliveryStatus
),
EventStatus,
ISNULL(MaxClassSize, 0),
ISNULL(MinClassSize, 0),
1,
ISNULL(CostRRP, 0),
DailyOrTotal,
Currency,
ISNULL(( SELECT TOP 1
CRLocationID
FROM CRLocation
WHERE LocationCode = #TmpTable.LocationCode
), 0),
( CASE WHEN StartDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
+ ' 00:00:00.000'
ELSE CONVERT(VARCHAR(10), GETDATE(), 105) + ' '
+ StartTime
END ) ,
( CASE WHEN StartDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
+ ' 00:00:00.000'
ELSE CONVERT(VARCHAR(10), GETDATE(), 105) + ' '
+ EndTime
END ) ,
1,
GETDATE(),
GETDATE(),
0,
0,
1,
@CheckListId,
( SELECT TJWorkflow.TJWorkflowID
FROM TJWorkflow
WHERE TJWorkflow.TJWorkflowID = #TmpTable.TJWorkflowID
),
( CASE WHEN #TmpTable.DisplayCost = 'Yes'
THEN 1
ELSE 0
END ),
1,
( CASE WHEN #TmpTable.DisplayCostRange = 'Yes'
THEN 1
ELSE 0
END ),
( CASE WHEN UPPER(#TmpTable.DeliveryType) = 'ON-SITE'
THEN 0
WHEN #TmpTable.DisplayDiscount = 'Yes'
THEN 1
ELSE 0
END ),
CancellationBand,
(CASE WHEN StartDate = 'TBC'
THEN -1
ELSE TimeZoneIndex
END) --1,1 are default value of DisplayCost,DisplayTime,DisplayCostRange,DisplayDiscount fields
,1
FROM #TmpTable
WHERE OperationStatus = 'Added'
AND ID = @ID
SET @EventID = SCOPE_IDENTITY()
SET @CurrentActivity = ( SELECT CRActivityID
FROM CREvent
WHERE CREventID = @EventID
)
--Code For Schedule
IF ( @PreActivity = @CurrentActivity )
SET @EventIDs = @EventIDs + ','
+ CAST(@EventID AS VARCHAR(10))
ELSE
SET @EventIDs = @EventIDs + '#'
+ CAST(@EventID AS VARCHAR(10))
--
SET @PreActivity = ( SELECT CRActivityID
FROM CREvent
WHERE CREventID = @EventID
)
--Add by vikas on 19-02-2010
SELECT @EvTitile = [Title]
FROM CREvent
WHERE CREventID = @EventID
IF @EvTitile IS NULL
OR RTRIM(LTRIM(@EvTitile)) = ''
UPDATE CREvent
SET [Title] = ( SELECT ActivityTitle
FROM CrActivity
WHERE ActivityCode = @ActCode
)
WHERE CREventID = @EventID
SELECT @EventCurrency = [Currency]
FROM CREvent
WHERE CREventID = @EventID
IF @EventCurrency IS NULL
OR RTRIM(LTRIM(@EventCurrency)) = ''
UPDATE CREvent
SET [Currency] = ( SELECT Currency
FROM CRCurrency
WHERE CRCurrencyID = ( SELECT FromCurrencyID
FROM TJCurrencyConversion
WHERE CurrencyConversionID = 1
)
)
WHERE CREventID = @EventID
SELECT @DailyOrTotal = [DailyOrWeekly]
FROM CREvent
WHERE CREventID = @EventID
IF @DailyOrTotal IS NULL
OR RTRIM(LTRIM(@DailyOrTotal)) = ''
UPDATE CREvent
SET [DailyOrWeekly] = 'Total'
WHERE CREventID = @EventID
INSERT INTO CREventTargetAudience
SELECT @EventID,
ISNULL(( SELECT GroupID
FROM Groups
WHERE GroupName = #TmpTable.TargetAudience
), 0)
FROM #TmpTable
WHERE OperationStatus = 'Added'
AND ID = @ID
INSERT INTO CREventRooms
SELECT @EventID,
( SELECT CRRoomID
FROM CRRooms
WHERE RoomName = #TmpTable.Rooms
AND CRLocationID = ( SELECT TOP 1
CRLocationID
FROM CRLocation
WHERE LocationCode = #tmpTable.LocationCode
)
),
IsMainRoom
FROM #TmpTable
WHERE OperationStatus = 'Added'
AND ID = @ID
AND #TmpTable.LocationCode <> 'TBC'
INSERT INTO CREventInstructor
SELECT @EventID,
ISNULL(( SELECT CRInstructor.CRInstructorId
FROM CRInstructor
WHERE CRInstructor.InstructorCode = #TmpTable.InstructorCode
), 0),
1
FROM #TmpTable
WHERE OperationStatus = 'Added'
AND ID = @ID
--Added by vikas to enter RRP and Onsite Cost of event from TJActivityCost
INSERT INTO [TJEventGlobalCost]
(
EventCode,
PublicBuyPrice,
GlobalRRP,
OnsiteBuyPrice,
GlobalOnsite,
FeeForExtra,
CreatedDate
)
SELECT [#TmpTable].EventCode,
[TJActivityCost].PublicBuyPrice,
[TJActivityCost].GlobalRRP,
[TJActivityCost].OnsiteBuyPrice,
[TJActivityCost].GlobalOnsite,
[TJActivityCost].FeeForExtra,
GETDATE()
FROM #TmpTable
INNER JOIN [TJActivityCost] ON #TmpTable.ActivityCode = [TJActivityCost].ActivityCode
WHERE #TmpTable.OperationStatus = 'Added'
AND #TmpTable.ID = @ID
AND #TmpTable.PriceBy = 'Company'
--Add by vikas to enter company cost of event
INSERT INTO TJEventCompanycost
(
EventCode,
CompanyCode,
RRPCost,
OnsiteCost,
CreatedDate
)
SELECT #TmpTable.EventCode,
[TJActivityCompanyCost].CompanyCode,
[TJActivityCompanyCost].RRPCost,
[TJActivityCompanyCost].OnsiteCost,
GETDATE()
FROM #TmpTable
INNER JOIN [TJActivityCompanyCost] ON #TmpTable.ActivityCode = [TJActivityCompanyCost].ActivityCode
WHERE #TmpTable.OperationStatus = 'Added'
AND #TmpTable.ID = @ID
AND #TmpTable.PriceBy = 'Company'
EXEC [TJ_CheckListEventJobsFirstInsert] @EventID, @CheckListID,@CheckListID, 0
---------------------------
--Conflict Code Start here--
---------------------------
SELECT @SDate = StartDate,
@Edate = EndDate,
@StartTime = EventStartTime,
@EndTime = EventEndTime
FROM CREvent
WHERE CrEventID = @EventID
SELECT @IID = CRInstructorId
FROM CREventInstructor
WHERE CREventID = @EventID and IsLead=1
SELECT @RoomID = CRRoomID
FROM CREventRooms
WHERE CREventID = @EventID and IsMainRoom=1
IF @RoomID IS NULL
BEGIN
SET @RoomID = 0
END
IF CONVERT(DATETIME, @SDate, 103) <> CONVERT(DATETIME, '1900-01-01', 103)
BEGIN
EXECUTE CR_ImportConflicts @IID, @RoomID, @SDate,
@EDate, @StartTime, @EndTime, @EventID
END
---------------------------
--Conflict Code End here--
---------------------------
--Inserts the default EventCost
IF @EventID <> 0
BEGIN
----Default Event's Currency Conversion Rate
SELECT @tmpSeparateCurrency = Currency
FROM #TmpTable
WHERE OperationStatus = 'Added'
AND ID = @ID
INSERT INTO TJEventProgramCurrencyConversion
(
FromCurrencyID,
ToCurrencyID,
ConversionRate,
InversionRate,
CREventID,
Currency
)
SELECT FromCurrencyID,
ToCurrencyID,
ConversionRate,
InversionRate,
@EventID,
@tmpSeparateCurrency
FROM TJCurrencyConversion
WHERE CurrencyConversionID <> 1
-----
--comment and change by vikas on 24-02-2010
--Add if condition enter cost in TJEventCost table only when activity of the event is priceby 'Delegate'(Not Company)
IF EXISTS ( SELECT CREventID
FROM CREvent
INNER JOIN CRActivity ON CREvent.CRActivityID = CRActivity.CRActivityID
WHERE CREvent.CREventID = @EventID
AND CRActivity.PriceBy = 'Delegate' )
BEGIN
EXEC [dbo].[TJ_EventCostDefaultInsert] @EventID
UPDATE TJEventCost
SET EventChargeMax = ISNULL(#TmpTable.EventChargesMax, ISNULL(#TmpTable.CostRRP, 0)),
EventChargesMin = ISNULL(#TmpTable.EventChargesMin, ISNULL(#TmpTable.CostRRP, 0))
FROM #TmpTable
WHERE OperationStatus = 'Added'
AND ID = @ID
and CREventID = @EventID -- Added by hemant gupta for issue 0031899
EXEC [dbo].[TJ_EventCostByDelegateUpdate] @EventID,
0, 0
END
END
IF ( @@ERROR = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
FETCH NEXT FROM crTmp INTO @ID, @ActCode
END
CLOSE crTmp
DEALLOCATE crTmp
--Create new Schedule With events
DECLARE @ActivityEventIDs AS VARCHAR(MAX)
IF @EventIDs <> ''
BEGIN
CREATE TABLE #Activity ( ActivityID INT )
DECLARE crTmpSc CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR SELECT item
FROM dbo.CR_FnSplit(@EventIDs, '#')
OPEN crTmpSc
FETCH NEXT FROM crTmpSc INTO @ActivityEventIDs
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #Activity
EXEC
( 'INSERT INTO #Activity SELECT CRActivityID FROM CREvent Where CREventID IN ('
+ @ActivityEventIDs + ')'
)
SELECT @VendorName = VendorName + '_Schedule'
FROM CRVendorActivity
WHERE VendorCode = ( SELECT TOP 1
CRVendorActivityID
FROM CRActivity
WHERE CRActivityID IN (
SELECT ActivityID
FROM #Activity )
)
SET @SStartDate = GETDATE()
SET @SEndDate = CAST('2099-01-01' AS DATETIME)
EXEC [CR_ScheduleCreateWithEvents] @VendorName, '',
@SStartDate, @SEndDate, 'Active', 0,
@ActivityEventIDs
FETCH NEXT FROM crTmpSc INTO @ActivityEventIDs
END
CLOSE crTmpSc
DEALLOCATE crTmpSc
DROP TABLE #Activity
END
DECLARE crTmp1 CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR SELECT ActivityCode,
EventCode,
Title,
StartDate,
EndDate,
DeliveryType,
DeliveryStatus,
EventStatus,
MaxClassSize,
MinClassSize,
CostRRP,
DailyOrTotal,
Currency,
LocationCode,
CancellationBand,
Checklist,
EventChargesMin,
EventChargesMax,
DisplayCostRange,
TJWorkflowID,
StartTime,
EndTime,
TimeZoneIndex ,
DisplayCost,
DisplayDiscount
FROM #tmpTable
WHERE OperationStatus = 'Updated'
OPEN crTmp1
FETCH NEXT FROM crTmp1 INTO @ActivityCode, @EventCode, @Title, @StDate,
@EdDate, @DeliveryType, @DeliveryStatus, @EventStatus,
@MaxClassSize, @MinClassSize, @CostRRP, @DailyOrTotal, @Currency,
@LocationCode, @CancellationBand, @Checklist, @EventChargesMin,
@EventChargesMax, @DisplayCostRange, @TJWorkflowID,@StTime,@EdTime,@TimeZoneIndex ,@DisplayCost,@DisplayDiscount
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
--Old checklist ID
SELECT @OldChecklistID = CheckListTemplateID
FROM CREvent
WHERE CREvent.EventCode = @EventCode
AND IsProgram = 1
--Set End Date
SELECT @Duration = Duration,
@DurationType = DurationType
FROM CrActivity,
#TmpTable
WHERE CrActivity.ActivityCode = #TmpTable.ActivityCode
AND EventCode = @EventCode
SELECT @StartDate = ( CASE WHEN StartDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
ELSE StartDate
END )
FROM #TmpTable
WHERE #TmpTable.EventCode = @EventCode
IF @EdDate IS NULL
AND ( @DurationType = 'Days'
OR @DurationType = 'Day'
)
SELECT @EndDate = ( CASE WHEN StartDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
ELSE CONVERT(DATETIME, DATEADD(DAY, @Duration - 1, @StartDate), 103)
END )
FROM #TmpTable
WHERE #TmpTable.EventCode = @EventCode
ELSE
SELECT @EndDate = ( CASE WHEN StartDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
ELSE @StartDate
END )
FROM #TmpTable
WHERE #TmpTable.EventCode = @EventCode
--Get Old Activity Code
SELECT @OldActCode = ( SELECT ActivityCode
FROM CRActivity
INNER JOIN CREvent ON CRActivity.CRActivityId = CREvent.CRActivityId
WHERE CREvent.EventCode = @EventCode
AND IsProgram = 1
)
--Add by vikas
SELECT @PriceByType = CRActivity.PriceBy,
@FeeCurrency = CRActivity.FeeCurrency
FROM CrActivity
WHERE CrActivity.ActivityCode = @ActivityCode
IF UPPER(@PriceByType) = 'COMPANY'
BEGIN
SET @EventCurrency = @FeeCurrency
SET @Currency = @EventCurrency
UPDATE [#TmpTable]
SET [Currency] = @Currency,
[CostRRP] = 0
WHERE EventCode = @EventCode
END
----Update the event----
UPDATE [CREvent]
SET [CRActivityID] = ( SELECT CRActivityID
FROM CRActivity
WHERE ActivityCode = @ActivityCode
),
[ActivityType] = ( SELECT ActivityTypeID
FROM CRActivity
WHERE ActivityCode = @ActivityCode
),
[Title] = @Title,
[StartDate] = ( CASE WHEN @StDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
ELSE @StDate
END ),
[EndDate] = ( CASE WHEN @EdDate IS NULL THEN @EndDate
ELSE ( CASE WHEN @EdDate = 'TBC'
THEN CONVERT(DATETIME, '1900-01-01', 103)
ELSE @EdDate
END )
END ),
[DeliveryTypeID] = ( SELECT DeliveryTypeID
FROM CRDeliveryType
WHERE DeliveryType = @DeliveryType
),
[DeliveryStatusID] = ( SELECT DeliveryStatusID
FROM CRDeliveryStatus
WHERE DeliveryStatus = @DeliveryStatus
),
[EventStatus] = @EventStatus,
[MaxClassSize] = ISNULL(@MaxClassSize, 0),
[MinClassSize] = ISNULL(@MinClassSize, 0),
[CostRRP] = ISNULL(@CostRRP, 0),
[DailyOrWeekly] = @DailyOrTotal,
[Currency] = @Currency,
[CRLocationID] = ISNULL(( SELECT TOP 1
CRLocationID
FROM CRLocation
WHERE LocationCode = @LocationCode
), 0),
[EventStartTime] = ( CASE WHEN @StDate = 'TBC' --If start date 'TBC' then start and end time will be 00.00
THEN CONVERT(VARCHAR(10), GETDATE(), 105)
+ ' 00:00:00.000'
ELSE CONVERT(VARCHAR(10), GETDATE(), 105)
+ ' ' + @StTime
END ) ,
[EventEndTime] = ( CASE WHEN @StDate = 'TBC'
THEN CONVERT(VARCHAR(10), GETDATE(), 105)
+ ' 00:00:00.000'
ELSE CONVERT(VARCHAR(10), GETDATE(), 105)
+ ' ' + @EdTime
END ) ,
[CreatedGroupID] = 1,
[ModifiedDate] = GETDATE(),
[CancellationBandOption] = @CancellationBand,
[DisplayDiscount] =( CASE WHEN UPPER(@DeliveryType) = 'ON-SITE'
THEN 0
WHEN @DisplayDiscount = 'Yes'
THEN 1
ELSE 0
END ),
[CheckListTemplateID] = ISNULL(( SELECT TOP 1
ChecklistID
FROM TJChecklist
WHERE [Name] = @Checklist
), 0),
[DisplayCostRange] = ( CASE WHEN @DisplayCostRange = 'Yes'
THEN 1
ELSE 0
END ),
[TJWorkflowID] = @TJWorkflowID,
[TimeZoneIndex]=( CASE WHEN @StDate = 'TBC' --If start date 'TBC' then TimeZone Index will be -1
THEN -1
ELSE @TimeZoneIndex
END),
[DisplayCost]=( CASE WHEN @DisplayCost = 'Yes'
THEN 1
ELSE 0
END )
FROM [#TmpTable] AS ImportEvent
WHERE CREvent.EventCode = @EventCode
AND [IsProgram] = 1
--Add by vikas on 19-02-2010
SELECT @EvTitile = [Title]
FROM CREvent
WHERE CREvent.EventCode = @EventCode
AND [IsProgram] = 1
IF @EvTitile IS NULL
OR RTRIM(LTRIM(@EvTitile)) = ''
UPDATE CREvent
SET [Title] = ( SELECT ActivityTitle
FROM CrActivity
WHERE ActivityCode = @ActivityCode
)
WHERE CREvent.EventCode = @EventCode
AND [IsProgram] = 1
------------------------
SELECT @DailyOrTotal = [DailyOrWeekly]
FROM CREvent
WHERE CREvent.EventCode = @EventCode
AND [IsProgram] = 1
IF @DailyOrTotal IS NULL
OR RTRIM(LTRIM(@DailyOrTotal)) = ''
UPDATE CREvent
SET [DailyOrWeekly] = 'Total'
WHERE CREvent.EventCode = @EventCode
AND [IsProgram] = 1
SELECT @EventCurrency = [Currency]
FROM CREvent
WHERE CREvent.EventCode = @EventCode
AND [IsProgram] = 1
IF @EventCurrency IS NULL
OR RTRIM(LTRIM(@EventCurrency)) = ''
UPDATE CREvent
SET [Currency] = ( SELECT Currency
FROM CRCurrency
WHERE CRCurrencyID = ( SELECT FromCurrencyID
FROM TJCurrencyConversion
WHERE CurrencyConversionID = 1
)
)
WHERE CREvent.EventCode = @EventCode
AND [IsProgram] = 1
SELECT @EventID = CREventID
FROM CREvent
WHERE CREvent.EventCode = @EventCode
SELECT @NewActCode = ( SELECT ActivityCode
FROM CRActivity
INNER JOIN CREvent ON CRActivity.CRActivityId = CREvent.CRActivityId
WHERE CREvent.CREventID = @EventID
)
--
DELETE FROM CREventTargetAudience
WHERE CREventID = @EventID
DELETE FROM CREventRooms
WHERE CREventID = @EventID
DELETE FROM CREventInstructor
WHERE CREventID = @EventID
------------------------
INSERT INTO CREventTargetAudience
SELECT @EventID,
ISNULL(( SELECT GroupID
FROM Groups
WHERE GroupName = #TmpTable.TargetAudience
), 0)
FROM #TmpTable
WHERE OperationStatus = 'Updated'
AND EventCode = @EventCode
INSERT INTO CREventRooms
SELECT @EventID,
( SELECT CRRoomID
FROM CRRooms
WHERE RoomName = #TmpTable.Rooms
AND CRLocationID = ( SELECT TOP 1
CRLocationID
FROM CRLocation
WHERE LocationCode = #tmpTable.LocationCode
)
),
IsMainRoom
FROM #TmpTable
WHERE OperationStatus = 'Updated'
AND EventCode = @EventCode
INSERT INTO CREventInstructor
SELECT @EventID,
ISNULL(( SELECT TOP 1
CRInstructor.CRInstructorId
FROM CRInstructor
WHERE CRInstructor.InstructorCode = #TmpTable.InstructorCode
), 0),
1
FROM #TmpTable
WHERE OperationStatus = 'Updated'
AND EventCode = @EventCode
DECLARE @PriceBy NVARCHAR(10)
SELECT @PriceBy = [#TmpTable].PriceBy
FROM #TmpTable
WHERE [#TmpTable].OperationStatus = 'Updated'
AND [#TmpTable].EventCode = @EventCode
IF @PriceBy = 'Delegate'
BEGIN
DELETE FROM TJEventGlobalCost
WHERE EventCode = @EventCode
DELETE FROM TJEventCompanyCost
WHERE EventCode = @EventCode
END
ELSE
BEGIN
IF @OldActCode <> @NewActCode
BEGIN
DELETE FROM TJEventCompanyCost
WHERE EventCode = @EventCode
IF EXISTS ( SELECT EventCode
FROM TJEventGlobalCost
WHERE EventCode = @EventCode )
BEGIN
UPDATE [TJEventGlobalCost]
SET PublicBuyPrice = [TJActivityCost].PublicBuyPrice,
GlobalRRP = [TJActivityCost].GlobalRRP,
OnsiteBuyPrice = [TJActivityCost].OnsiteBuyPrice,
GlobalOnsite = [TJActivityCost].GlobalOnsite,
FeeForExtra = [TJActivityCost].FeeForExtra,
Modifieddate = GETDATE()
FROM #TmpTable
INNER JOIN [TJActivityCost] ON #TmpTable.ActivityCode = [TJActivityCost].ActivityCode
WHERE #TmpTable.OperationStatus = 'Updated'
AND [TJEventGlobalCost].EventCode = @EventCode
AND #TmpTable.PriceBy = 'Company'
END
ELSE
BEGIN
DELETE FROM TJEventGlobalCost
WHERE EventCode = @EventCode
INSERT INTO [TJEventGlobalCost]
(
EventCode,
PublicBuyPrice,
GlobalRRP,
OnsiteBuyPrice,
GlobalOnsite,
FeeForExtra,
CreatedDate
)
SELECT [#TmpTable].EventCode,
[TJActivityCost].PublicBuyPrice,
[TJActivityCost].GlobalRRP,
[TJActivityCost].OnsiteBuyPrice,
[TJActivityCost].GlobalOnsite,
[TJActivityCost].FeeForExtra,
GETDATE()
FROM #TmpTable
INNER JOIN [TJActivityCost] ON #TmpTable.ActivityCode = [TJActivityCost].ActivityCode
WHERE #TmpTable.OperationStatus = 'Updated'
AND #TmpTable.EventCode = @EventCode
AND #TmpTable.PriceBy = 'Company'
END
INSERT INTO TJEventCompanycost
(
EventCode,
CompanyCode,
RRPCost,
OnsiteCost,
CreatedDate
)
SELECT #TmpTable.EventCode,
[TJActivityCompanyCost].CompanyCode,
[TJActivityCompanyCost].RRPCost,
[TJActivityCompanyCost].OnsiteCost,
GETDATE()
FROM #TmpTable
INNER JOIN [TJActivityCompanyCost] ON #TmpTable.ActivityCode = [TJActivityCompanyCost].ActivityCode
WHERE #TmpTable.OperationStatus = 'Updated'
AND #TmpTable.EventCode = @EventCode
AND #TmpTable.PriceBy = 'Company'
END
END
SELECT @CheckListID = CREvent.CheckListTemplateID
FROM CREvent
WHERE CREvent.EventCode = @EventCode
EXEC [TJ_CheckListEventJobsFirstInsert] @EventID, @CheckListID,
@OldChecklistID, 1
----------------------------
--Conflict Code Start here--
----------------------------
--deepankar
SELECT @SDate = StartDate,
@Edate = EndDate,
@StartTime = EventStartTime,
@EndTime = EventEndTime
FROM CREvent
WHERE CrEventID = @EventID
SELECT @IID = CRInstructorId
FROM CREventInstructor
WHERE CREventID = @EventID and IsLead=1
SELECT @RoomID = CRRoomID
FROM CREventRooms
WHERE CREventID = @EventID and IsMainRoom=1
IF @RoomID IS NULL
SET @RoomID = 0
IF CONVERT(DATETIME, @SDate, 103) <> CONVERT(DATETIME, '1900-01-01', 103)
EXECUTE CR_ImportConflicts @IID, @RoomID, @SDate, @EDate, @StartTime, @EndTime, @EventID
--------------------------
--Conflict Code End here--
--------------------------
--Update Eventcosts
--Add if condition enter cost in TJEventCost table only when activity of the event is priceby 'Delegate'(Not Company)
IF EXISTS ( SELECT CREventID
FROM CREvent
INNER JOIN CRActivity ON CREvent.CRActivityID = CRActivity.CRActivityID
WHERE CREvent.CREventID = @EventID
AND CRActivity.PriceBy = 'Delegate' )
BEGIN
IF EXISTS ( SELECT CREventID
FROM TJEventCost
WHERE CREventID = @EventID )
BEGIN
EXEC [dbo].[TJ_EventCostByDelegateUpdate] @EventID,
0, 0
UPDATE TJEventCost
SET EventChargeMax = ISNULL(@EventChargesMax, ISNULL(@CostRRP, 0)),
EventChargesMin = ISNULL(@EventChargesMin, ISNULL(@CostRRP, 0))
WHERE CREventID = @EventID
END
ELSE
BEGIN
EXEC [dbo].[TJ_EventCostDefaultInsert] @EventID
UPDATE TJEventCost
SET EventChargeMax = ISNULL(@EventChargesMax, ISNULL(@CostRRP, 0)),
EventChargesMin = ISNULL(@EventChargesMin, ISNULL(@CostRRP, 0))
WHERE CREventID = @EventID
EXEC [dbo].[TJ_EventCostByDelegateUpdate] @EventID,
0, 0
END
END
--Roll back Workflow
SELECT @DeliveryStatusID = DeliveryStatusID
FROM CRDeliveryStatus
WHERE DeliveryStatus IN ( SELECT DeliveryStatus
FROM #tmpTable
WHERE EventCode = @EventCode )
SELECT @ManagerID = ManagerID
FROM #tmpTable
WHERE EventCode = @EventCode
IF @DeliveryStatusID = 4
BEGIN
EXEC [TJ_CancelEventProgramAndDelegates] @EventID, 1,
@ManagerID
END
IF ( @@ERROR = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
FETCH NEXT FROM crTmp1 INTO @ActivityCode, @EventCode, @Title,
@StDate, @EdDate, @DeliveryType, @DeliveryStatus,
@EventStatus, @MaxClassSize, @MinClassSize, @CostRRP,
@DailyOrTotal, @Currency, @LocationCode, @CancellationBand,
@Checklist, @EventChargesMin, @EventChargesMax,
@DisplayCostRange, @TJWorkflowID,@StTime,@EdTime,@TimeZoneIndex ,@DisplayCost,@DisplayDiscount
END
CLOSE crTmp1
DEALLOCATE crTmp1
UPDATE #TmpTable
SET OperationStatus = 'Invalid'
WHERE OperationStatus = 'Invalid'
--begin
--Added By Dilip Kanade in case of Blank Row or Blank Column.
UPDATE #TmpTable
SET EventCode = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(EventCode))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(EventCode)), 17,
( LEN(LTRIM(RTRIM(EventCode))) - 23 )) )
ELSE EventCode
END = ''
OR EventCode IS NULL
UPDATE #TmpTable
SET ActivityCode = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(ActivityCode))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(ActivityCode)), 17,
( LEN(LTRIM(RTRIM(ActivityCode)))
- 23 )) )
ELSE ActivityCode
END = ''
OR ActivityCode IS NULL
UPDATE #TmpTable
SET StartDate = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(StartDate))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(StartDate)), 17,
( LEN(LTRIM(RTRIM(StartDate))) - 23 )) )
ELSE StartDate
END = ''
OR StartDate IS NULL
UPDATE #TmpTable
SET StartTime = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(StartTime))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(StartTime)), 17,
( LEN(LTRIM(RTRIM(StartTime))) - 23 )) )
ELSE StartTime
END = ''
OR StartTime IS NULL
OR StartTime = ''
UPDATE #TmpTable
SET EndTime = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(EndTime))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(EndTime)), 17,
( LEN(LTRIM(RTRIM(EndTime))) - 23 )) )
ELSE EndTime
END = ''
OR EndTime IS NULL
OR EndTime = ''
UPDATE #TmpTable
SET EventStatus = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(EventStatus))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(EventStatus)), 17,
( LEN(LTRIM(RTRIM(EventStatus)))
- 23 )) )
ELSE EventStatus
END = ''
OR EventStatus IS NULL
UPDATE #TmpTable
SET DeliveryType = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(DeliveryType))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(DeliveryType)), 17,
( LEN(LTRIM(RTRIM(DeliveryType)))
- 23 )) )
ELSE DeliveryType
END = ''
OR DeliveryType IS NULL
UPDATE #TmpTable
SET TargetAudience = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(TargetAudience))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(TargetAudience)), 17,
( LEN(LTRIM(RTRIM(TargetAudience)))
- 23 )) )
ELSE TargetAudience
END = ''
OR TargetAudience IS NULL
UPDATE #TmpTable
SET DeliveryStatus = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(DeliveryStatus))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(DeliveryStatus)), 17,
( LEN(LTRIM(RTRIM(DeliveryStatus)))
- 23 )) )
ELSE DeliveryStatus
END = ''
OR DeliveryStatus IS NULL
UPDATE #TmpTable
SET Rooms = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(Rooms))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(Rooms)), 17,
( LEN(LTRIM(RTRIM(Rooms))) - 23 )) )
ELSE Rooms
END = ''
OR Rooms IS NULL
UPDATE #TmpTable
SET TJWorkflowID = '*'
WHERE OperationStatus = 'Invalid'
AND CASE WHEN LEN(LTRIM(RTRIM(TJWorkflowID))) >= 23
THEN ( SUBSTRING(LTRIM(RTRIM(TJWorkflowID)), 17,
( LEN(LTRIM(RTRIM(TJWorkflowID)))
- 23 )) )
ELSE TJWorkflowID
END = ''
OR TJWorkflowID IS NULL
--end
SELECT ID,
OperationStatus,
Title,
EventCode,
ActivityCode,
StartDate,
( CASE WHEN StartDate = 'TBC' THEN 'TBC'
ELSE EndDate
END ) AS EndDate,
StartTime,
EndTime,
DeliveryType,
DeliveryStatus,
EventStatus,
TargetAudience,
MaxClassSize,
MinClassSize,
CostRRP,
DailyOrTotal,
Currency,
LocationCode,
Rooms,
Checklist,
InstructorCode,
TJWorkflowID,
ManagerID,
IsMainRoom,
( CASE WHEN CancellationBand = 'C' THEN 'Company'
WHEN CancellationBand = 'V' THEN 'Vendor'
ELSE CancellationBand
END ) AS CancellationBand,
EventChargesMin,
EventChargesMax,
DisplayCostRange,
TimeZoneIndex,
DisplayCost,
DisplayDiscount
FROM #tmpTable
SELECT COUNT(OperationStatus) AS CNT
FROM #tmpTable
WHERE OperationStatus = 'Added'
SELECT COUNT(OperationStatus) AS CNT
FROM #tmpTable
WHERE OperationStatus = 'Updated'
DROP TABLE #TmpTable
END
Subscribe to:
Posts (Atom)