Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Public Shared Function doSaveUnitBooking(ByVal dbMasterRef As String, ByVal projectRef As String, ByVal psRef As String, ByVal memberRef As String, ByVal agentMemberRef As String, _
- ByVal clusterRef As String, ByVal productRef As String, ByVal unitRef As String, _
- ByVal itemTypeList As String, ByVal termRef As String, ByVal termNo As String, ByVal dtPrice As DataTable, ByVal inputUN As String, _
- ByVal customerName As String, ByVal customerNIK As String, ByVal customerHP As String, _
- ByVal customerPHONE As String, ByVal customerEMAIL As String, ByVal customerADDRESS As String, ByRef projectBookingRef As String) As String
- Dim result As String = ""
- Dim dt As New DataTable
- Dim sqlCmd As New SqlCommand
- Dim sqlCon As New SqlConnection(_conStr)
- Dim sqlTrans As SqlTransaction
- Dim sqlDr As SqlDataReader
- '----ambil koneksi ke database PS
- '----ambil koneksi ke database PS
- Dim projectConnectionString As String = GetProjectConnectionString(dbMasterRef, projectRef)
- Dim projectSqlCmd As New SqlCommand
- Dim projectSqlCon As New SqlConnection(projectConnectionString)
- Dim projectSqlTrans As SqlTransaction
- Dim projectSqlDr As SqlDataReader
- sqlCon.Open()
- sqlTrans = sqlCon.BeginTransaction
- sqlCmd.Transaction = sqlTrans
- sqlCmd.Connection = sqlCon
- projectSqlCon.Open()
- projectSqlTrans = projectSqlCon.BeginTransaction
- projectSqlCmd.Transaction = projectSqlTrans
- projectSqlCmd.Connection = projectSqlCon
- Try
- If Trim(projectConnectionString) = "" Then
- Throw New Exception("Project Connection String not created or not defined")
- End If
- Dim projectPsRef As Integer = 0
- Dim bookingRef As Integer = 0
- '-----update data customer
- '-----update data customer
- If Trim(memberRef) <> String.Empty And Not IsNothing(memberRef) Then
- sqlCmd.Parameters.Clear()
- sqlCmd.Prepare()
- sqlCmd.CommandType = CommandType.Text
- sqlCmd.CommandText = "Update " + _NSMainDB + "..ms_personal " + _
- "Set name = @name, ktpid = @ktpid, hp1 = @hp1, phone1 = @phone1, email1 = @email1, idaddr = @idaddr " + _
- "where psRef = @psRef "
- sqlCmd.Parameters.AddWithValue("@psRef", psRef)
- sqlCmd.Parameters.AddWithValue("@name", customerName)
- sqlCmd.Parameters.AddWithValue("@KTPID", customerNIK)
- sqlCmd.Parameters.AddWithValue("@IDAddr", customerADDRESS)
- sqlCmd.Parameters.AddWithValue("@HP1", customerHP)
- sqlCmd.Parameters.AddWithValue("@phone1", customerPHONE)
- sqlCmd.Parameters.AddWithValue("@email1", customerEMAIL)
- sqlCmd.ExecuteNonQuery()
- End If
- '-----ambil data projectPsRef di mapping
- '-----ambil data projectPsRef di mapping
- sqlCmd.Parameters.Clear()
- sqlCmd.Prepare()
- sqlCmd.CommandType = CommandType.Text
- sqlCmd.CommandText = "select projectPsRef " + _
- "from " + _NSMainDB + "..MS_mappingPersonalProject " + _
- "where dbMasterRef = @dbMasterRef and projectRef = @projectRef and psRef = @psRef "
- sqlCmd.Parameters.AddWithValue("@dbMasterRef", dbMasterRef)
- sqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- sqlCmd.Parameters.AddWithValue("@psRef", psRef)
- sqlDr = sqlCmd.ExecuteReader
- If sqlDr.Read Then
- projectPsRef = sqlDr("projectPsRef")
- End If
- sqlDr.Close()
- If projectPsRef = 0 Then
- '-----Belum teregister di project/mapping project
- '-----Belum teregister di project/mapping project
- Try
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "select isnull(max(psRef),0) + 1 as ref " + _
- "from ps_ms_personal "
- projectSqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- projectSqlCmd.Parameters.AddWithValue("@unitRef", unitRef)
- projectSqlDr = projectSqlCmd.ExecuteReader
- If projectSqlDr.Read Then
- projectPsRef = projectSqlDr("ref")
- End If
- projectSqlDr.Close()
- '-----Ambil data personal dari database
- '-----Ambil data personal dari database
- Dim dtPersonal As New DataTable
- dtPersonal = GetPersonalInfoBooking(psRef, sqlCmd, sqlCon)
- If dtPersonal.Rows.Count <= 0 Then
- Throw New ArgumentException("Your Personal Information not found.")
- End If
- '-----Create PS Code
- '-----Create PS Code
- Dim newPsCode As String = ""
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "select right(cast(year(getdate()) as varchar(4)),2) + right('00' + cast(month(getdate()) as varchar(2)),2) + right('0000' + cast(isnull(max(cast(right(psCode,4) as integer)),0) + 1 as varchar(4)),4) as newPsCode " + _
- "from ps_ms_personal " + _
- "where cast('20' + left(psCode,2) as integer) = year(getdate()) " + _
- " and cast(substring(psCode,3,2) as integer) = month(getdate()) "
- projectSqlDr = projectSqlCmd.ExecuteReader
- If projectSqlDr.Read Then
- newPsCode = projectSqlDr("newPsCode")
- End If
- projectSqlDr.Close()
- '-----Insert personal di PS
- '-----Insert personal di PS
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "insert into ps_ms_personal " + _
- " (psRef, psCode, typeRef, name, sexRef " + _
- " , birthDate, birthPlace, religionRef, occupationRef " + _
- " , maritalStatus, jobTitleRef, gradeRef, nationRef, bloodType " + _
- " , IDCountryCode, IDProvinceCode, IDCityCode, IDAddr, IDPostCode " + _
- " , corresCountryCode, corresProvinceCode, corresCityCode, corresAddr, corresPostCode,taxAddr " + _
- " , KTPID, SIMID, passportID, HP1, HP2, phone1, phone2, fax, email1, email2 " + _
- " , NPWP, CPName, CPHP, CPPhone, CPEmail, noted " + _
- " , field1, field2, field3, inputUN, isActive) " + _
- "select @psRef,@newPsCode " + _
- " , @typeRef, @name, @sexRef " + _
- " , @birthDate, @birthPlace, @religionRef, @occupationRef " + _
- " , @maritalStatus, @jobTitleRef, @gradeRef, @nationRef, @bloodType " + _
- " , @IDCountryCode, @IDProvinceCode, @IDCityCode, @IDAddr, @IDPostCode " + _
- " , @corresCountryCode, @corresProvinceCode, @corresCityCode, @corresAddr, @corresPostCode,@taxAddr " + _
- " , @KTPID, @SIMID, @passportID, @HP1, @HP2, @phone1, @phone2, @fax, @email1, @email2 " + _
- " , @NPWP, @CPName, @CPHP, @CPPhone, @CPEmail, @noted " + _
- " , @field1, @field2, @field3, @inputUN, 1 "
- projectSqlCmd.Parameters.AddWithValue("@psRef", projectPsRef)
- projectSqlCmd.Parameters.AddWithValue("@newPsCode", newPsCode)
- projectSqlCmd.Parameters.AddWithValue("@typeRef", dtPersonal.Rows(0).Item("typeRef"))
- projectSqlCmd.Parameters.AddWithValue("@name", dtPersonal.Rows(0).Item("name"))
- projectSqlCmd.Parameters.AddWithValue("@sexRef", dtPersonal.Rows(0).Item("sexRef"))
- projectSqlCmd.Parameters.AddWithValue("@birthDate", dtPersonal.Rows(0).Item("birthdate"))
- projectSqlCmd.Parameters.AddWithValue("@birthPlace", dtPersonal.Rows(0).Item("birthPlace"))
- projectSqlCmd.Parameters.AddWithValue("@religionRef", dtPersonal.Rows(0).Item("religionRef"))
- projectSqlCmd.Parameters.AddWithValue("@occupationRef", dtPersonal.Rows(0).Item("occupationRef"))
- projectSqlCmd.Parameters.AddWithValue("@maritalStatus", dtPersonal.Rows(0).Item("maritalStatus"))
- projectSqlCmd.Parameters.AddWithValue("@jobTitleRef", dtPersonal.Rows(0).Item("jobTitleRef"))
- projectSqlCmd.Parameters.AddWithValue("@gradeRef", dtPersonal.Rows(0).Item("gradeRef"))
- projectSqlCmd.Parameters.AddWithValue("@nationRef", dtPersonal.Rows(0).Item("nationRef"))
- projectSqlCmd.Parameters.AddWithValue("@bloodType", dtPersonal.Rows(0).Item("bloodType"))
- projectSqlCmd.Parameters.AddWithValue("@IDCountryCode", dtPersonal.Rows(0).Item("IDCountryCode"))
- projectSqlCmd.Parameters.AddWithValue("@IDProvinceCode", dtPersonal.Rows(0).Item("IDProvinceCode"))
- projectSqlCmd.Parameters.AddWithValue("@IDCityCode", dtPersonal.Rows(0).Item("IDCityCode"))
- projectSqlCmd.Parameters.AddWithValue("@IDAddr", dtPersonal.Rows(0).Item("IDAddr"))
- projectSqlCmd.Parameters.AddWithValue("@IDPostCode", dtPersonal.Rows(0).Item("IDPostCode"))
- projectSqlCmd.Parameters.AddWithValue("@corresCountryCode", dtPersonal.Rows(0).Item("corresCountryCode"))
- projectSqlCmd.Parameters.AddWithValue("@corresProvinceCode", dtPersonal.Rows(0).Item("corresProvinceCode"))
- projectSqlCmd.Parameters.AddWithValue("@corresCityCode", dtPersonal.Rows(0).Item("corresCityCode"))
- projectSqlCmd.Parameters.AddWithValue("@corresAddr", dtPersonal.Rows(0).Item("corresAddr"))
- projectSqlCmd.Parameters.AddWithValue("@corresPostCode", dtPersonal.Rows(0).Item("corresPostCode"))
- projectSqlCmd.Parameters.AddWithValue("@taxAddr", dtPersonal.Rows(0).Item("taxAddr"))
- projectSqlCmd.Parameters.AddWithValue("@KTPID", dtPersonal.Rows(0).Item("KTPID"))
- projectSqlCmd.Parameters.AddWithValue("@SIMID", dtPersonal.Rows(0).Item("SIMID"))
- projectSqlCmd.Parameters.AddWithValue("@passportID", dtPersonal.Rows(0).Item("passportID"))
- projectSqlCmd.Parameters.AddWithValue("@HP1", dtPersonal.Rows(0).Item("HP1"))
- projectSqlCmd.Parameters.AddWithValue("@HP2", dtPersonal.Rows(0).Item("HP2"))
- projectSqlCmd.Parameters.AddWithValue("@phone1", dtPersonal.Rows(0).Item("phone1"))
- projectSqlCmd.Parameters.AddWithValue("@phone2", dtPersonal.Rows(0).Item("phone2"))
- projectSqlCmd.Parameters.AddWithValue("@fax", dtPersonal.Rows(0).Item("fax"))
- projectSqlCmd.Parameters.AddWithValue("@email1", dtPersonal.Rows(0).Item("email1"))
- projectSqlCmd.Parameters.AddWithValue("@email2", dtPersonal.Rows(0).Item("email2"))
- projectSqlCmd.Parameters.AddWithValue("@NPWP", dtPersonal.Rows(0).Item("NPWP"))
- projectSqlCmd.Parameters.AddWithValue("@CPName", dtPersonal.Rows(0).Item("CPName"))
- projectSqlCmd.Parameters.AddWithValue("@CPHP", dtPersonal.Rows(0).Item("CPHP"))
- projectSqlCmd.Parameters.AddWithValue("@CPPhone", dtPersonal.Rows(0).Item("CPPhone"))
- projectSqlCmd.Parameters.AddWithValue("@CPEmail", dtPersonal.Rows(0).Item("CPEmail"))
- projectSqlCmd.Parameters.AddWithValue("@noted", dtPersonal.Rows(0).Item("noted"))
- projectSqlCmd.Parameters.AddWithValue("@field1", dtPersonal.Rows(0).Item("field1"))
- projectSqlCmd.Parameters.AddWithValue("@field2", dtPersonal.Rows(0).Item("field2"))
- projectSqlCmd.Parameters.AddWithValue("@field3", dtPersonal.Rows(0).Item("field3"))
- projectSqlCmd.Parameters.AddWithValue("@inputUN", _nataPropertyInputUN)
- projectSqlCmd.ExecuteNonQuery()
- Catch ex As Exception
- Throw ex
- End Try
- '*Setelah berhasil insert personal di PS
- ' -lanjut insert mapping personal di NATA
- sqlCmd.Parameters.Clear()
- sqlCmd.Prepare()
- sqlCmd.CommandType = CommandType.Text
- sqlCmd.CommandText = "insert into " + _NSMainDB + "..MS_mappingPersonalProject(dbMasterRef,projectRef,psRef,projectPsRef) " + _
- "values(@dbMasterRef,@projectRef,@psRef,@projectPsRef) "
- sqlCmd.Parameters.AddWithValue("@dbMasterRef", dbMasterRef)
- sqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- sqlCmd.Parameters.AddWithValue("@psRef", psRef)
- sqlCmd.Parameters.AddWithValue("@projectPsRef", projectPsRef)
- sqlCmd.ExecuteNonQuery()
- End If
- '' CREATE DC
- '' CREATE DC
- Dim isCheckDC As Boolean = True
- Dim memberPsRef As Integer = GetMemberPsRef(agentMemberRef)
- Dim projectMemberPsRef As Integer = 0
- Dim projectSchemeMemberRef As Integer = 0
- If memberPsRef = 0 Then
- isCheckDC = False
- projectMemberPsRef = GetSettingValue(dbMasterRef, projectRef, _settingNoDealCloserPsRef)
- projectSchemeMemberRef = GetSettingValue(dbMasterRef, projectRef, _settingSchemeRef)
- End If
- If isCheckDC Then
- 'STEP Check DC
- 'Check Mapping personal dulu
- ' - Kalau ada berarti personalnya udah di create
- ' - Check mapping sales dengan psRef yang uda tercreate di mapping personal
- ' - Kalau ada gak usah create lagi
- ' - kalau gak ada
- ' - Create deal closer baru di ps
- ' - Create mapping deal closer
- ' - Kalau gak ada di mapping personal
- ' - Create Personalnya dulu di ps
- ' - Create mapping personal
- ' - Create deal closer
- ' - Create mapping deal closer
- '------cek mapping antara PS -> NATA
- '------cek mapping antara PS -> NATA
- sqlCmd.Parameters.Clear()
- sqlCmd.Prepare()
- sqlCmd.CommandType = CommandType.Text
- sqlCmd.CommandText = "select projectPsRef " + _
- "from " + _NSMainDB + "..MS_mappingPersonalProject " + _
- "where dbMasterRef = @dbMasterRef and projectRef = @projectRef and psRef = @psRef "
- sqlCmd.Parameters.AddWithValue("@dbMasterRef", dbMasterRef)
- sqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- sqlCmd.Parameters.AddWithValue("@psRef", memberPsRef)
- sqlDr = sqlCmd.ExecuteReader
- If sqlDr.Read Then
- projectMemberPsRef = sqlDr("projectPsRef")
- End If
- sqlDr.Close()
- If projectMemberPsRef > 0 Then
- '----Personalnya sudah tercreate and udah di mapping dengan data NATA
- '----Personalnya sudah tercreate and udah di mapping dengan data NATA
- '-----Ambil SchemeRef dari mapping member project NATA
- '-----Ambil SchemeRef dari mapping member project NATA
- sqlCmd.Parameters.Clear()
- sqlCmd.Prepare()
- sqlCmd.CommandType = CommandType.Text
- sqlCmd.CommandText = "select schemeRef,projectPsRef " + _
- "from " + _NSMainDB + "..MS_mappingMemberProject " + _
- "where dbMasterRef = @dbMasterRef and projectRef = @projectRef and psRef = @psRef and projectPsRef = @projectPsRef "
- sqlCmd.Parameters.AddWithValue("@dbMasterRef", dbMasterRef)
- sqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- sqlCmd.Parameters.AddWithValue("@projectPsRef", projectMemberPsRef)
- sqlCmd.Parameters.AddWithValue("@psRef", memberPsRef)
- sqlDr = sqlCmd.ExecuteReader
- If sqlDr.Read Then
- projectSchemeMemberRef = sqlDr("schemeRef")
- End If
- sqlDr.Close()
- If projectSchemeMemberRef = 0 Then
- '-----Berarti deal closer belum tercreate
- '-----Berarti deal closer belum tercreate
- Dim memberPsCode As String = ""
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "select psCode " + _
- "from ps_ms_personal " + _
- "where psRef = @psRef "
- projectSqlCmd.Parameters.AddWithValue("@psRef", psRef)
- projectSqlDr = projectSqlCmd.ExecuteReader
- If projectSqlDr.Read Then
- memberPsCode = projectSqlDr("psCode")
- End If
- projectSqlDr.Close()
- 'Dim schemeRef As Integer = GetSettingValue(dtNUPOrder.Rows(0).Item("dbMasterRef"), dtNUPOrder.Rows(0).Item("projectRef"), _settingSchemeRef)
- Dim schemeRef As Integer = getSchemeRef(dbMasterRef, projectRef, getMemberTypeString(memberRef))
- If schemeRef = 0 Then
- schemeRef = GetSettingValue(dbMasterRef, projectRef, _settingSchemeRef)
- End If
- projectSchemeMemberRef = schemeRef
- Dim memberCode = getProjectSchemeDigit(dbMasterRef, projectRef, schemeRef) + memberPsCode
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "insert into com_ms_member " + _
- "(schemeRef, psRef, memberCode, [password], memberLevel " + _
- ", joinDate, bankRef, bankAccNo, bankAccName, bankBranchName " + _
- ", isActive, isManager, inputUN) " + _
- "select @schemeRef, @psRef, @memberCode, @password, @memberLevel " + _
- ", @joinDate, @bankRef, @bankAccNo, @bankAccName, @bankBranchName " + _
- ", @isActive, @isManager, @inputUN"
- projectSqlCmd.Parameters.AddWithValue("@schemeRef", schemeRef)
- projectSqlCmd.Parameters.AddWithValue("@psRef", projectMemberPsRef)
- projectSqlCmd.Parameters.AddWithValue("@memberCode", memberCode)
- projectSqlCmd.Parameters.AddWithValue("@password", "")
- projectSqlCmd.Parameters.AddWithValue("@memberLevel", "")
- projectSqlCmd.Parameters.AddWithValue("@joinDate", DateTime.Now)
- projectSqlCmd.Parameters.AddWithValue("@bankRef", "0")
- projectSqlCmd.Parameters.AddWithValue("@bankAccNo", "")
- projectSqlCmd.Parameters.AddWithValue("@bankAccName", "")
- projectSqlCmd.Parameters.AddWithValue("@bankBranchName", "")
- projectSqlCmd.Parameters.AddWithValue("@isActive", "1")
- projectSqlCmd.Parameters.AddWithValue("@isManager", "0")
- projectSqlCmd.Parameters.AddWithValue("@inputUN", _nataPropertyInputUN)
- projectSqlCmd.ExecuteNonQuery()
- '-----kalo udah insert member di PS lanjut insert mappingMember di NATA
- '-----kalo udah insert member di PS lanjut insert mappingMember di NATA
- sqlCmd.Parameters.Clear()
- sqlCmd.Prepare()
- sqlCmd.CommandType = CommandType.Text
- sqlCmd.CommandText = "insert into " + _NSMainDB + "..MS_mappingMemberProject(dbMasterRef,projectRef,psRef,schemeRef,projectPsRef) " + _
- "values(@dbMasterRef,@projectRef,@psRef,@schemeRef,@projectPsRef) "
- sqlCmd.Parameters.AddWithValue("@dbMasterRef", dbMasterRef)
- sqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- sqlCmd.Parameters.AddWithValue("@psRef", memberPsRef)
- sqlCmd.Parameters.AddWithValue("@schemeRef", projectSchemeMemberRef)
- sqlCmd.Parameters.AddWithValue("@projectPsRef", projectMemberPsRef)
- sqlCmd.ExecuteNonQuery()
- End If
- Else
- 'Personalnya belum tercreate
- 'Create Personal Dulu
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "select isnull(max(psRef),0) + 1 as ref " + _
- "from ps_ms_personal "
- projectSqlDr = projectSqlCmd.ExecuteReader
- If projectSqlDr.Read Then
- projectMemberPsRef = projectSqlDr("ref")
- End If
- projectSqlDr.Close()
- '-----Ambil data personal NATA
- '-----Ambil data personal NATA
- Dim dtPersonal As New DataTable
- dtPersonal = GetPersonalInfoBooking(memberPsRef, sqlCmd, sqlCon)
- If dtPersonal.Rows.Count <= 0 Then
- Throw New ArgumentException("Your Personal Information not found.")
- End If
- '-----Create PS Code
- '-----Create PS Code
- Dim newPsCode As String = ""
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "select right(cast(year(getdate()) as varchar(4)),2) + right('00' + cast(month(getdate()) as varchar(2)),2) + right('0000' + cast(isnull(max(cast(right(psCode,4) as integer)),0) + 1 as varchar(4)),4) as newPsCode " + _
- "from ps_ms_personal " + _
- "where cast('20' + left(psCode,2) as integer) = year(getdate()) " + _
- " and cast(substring(psCode,3,2) as integer) = month(getdate()) "
- projectSqlDr = projectSqlCmd.ExecuteReader
- If projectSqlDr.Read Then
- newPsCode = projectSqlDr("newPsCode")
- End If
- projectSqlDr.Close()
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "insert into ps_ms_personal " + _
- " (psRef, psCode, typeRef, name, sexRef " + _
- " , birthDate, birthPlace, religionRef, occupationRef " + _
- " , maritalStatus, jobTitleRef, gradeRef, nationRef, bloodType " + _
- " , IDCountryCode, IDProvinceCode, IDCityCode, IDAddr, IDPostCode " + _
- " , corresCountryCode, corresProvinceCode, corresCityCode, corresAddr, corresPostCode,taxAddr " + _
- " , KTPID, SIMID, passportID, HP1, HP2, phone1, phone2, fax, email1, email2 " + _
- " , NPWP, CPName, CPHP, CPPhone, CPEmail, noted " + _
- " , field1, field2, field3, inputUN, isActive) " + _
- "select @psRef,@newPsCode " + _
- " , @typeRef, @name, @sexRef " + _
- " , @birthDate, @birthPlace, @religionRef, @occupationRef " + _
- " , @maritalStatus, @jobTitleRef, @gradeRef, @nationRef, @bloodType " + _
- " , @IDCountryCode, @IDProvinceCode, @IDCityCode, @IDAddr, @IDPostCode " + _
- " , @corresCountryCode, @corresProvinceCode, @corresCityCode, @corresAddr, @corresPostCode,@taxAddr " + _
- " , @KTPID, @SIMID, @passportID, @HP1, @HP2, @phone1, @phone2, @fax, @email1, @email2 " + _
- " , @NPWP, @CPName, @CPHP, @CPPhone, @CPEmail, @noted " + _
- " , @field1, @field2, @field3, @inputUN, 1 "
- projectSqlCmd.Parameters.AddWithValue("@psRef", projectMemberPsRef)
- projectSqlCmd.Parameters.AddWithValue("@newPsCode", newPsCode)
- projectSqlCmd.Parameters.AddWithValue("@typeRef", dtPersonal.Rows(0).Item("typeRef"))
- projectSqlCmd.Parameters.AddWithValue("@name", dtPersonal.Rows(0).Item("name"))
- projectSqlCmd.Parameters.AddWithValue("@sexRef", dtPersonal.Rows(0).Item("sexRef"))
- projectSqlCmd.Parameters.AddWithValue("@birthDate", dtPersonal.Rows(0).Item("birthdate"))
- projectSqlCmd.Parameters.AddWithValue("@birthPlace", dtPersonal.Rows(0).Item("birthPlace"))
- projectSqlCmd.Parameters.AddWithValue("@religionRef", dtPersonal.Rows(0).Item("religionRef"))
- projectSqlCmd.Parameters.AddWithValue("@occupationRef", dtPersonal.Rows(0).Item("occupationRef"))
- projectSqlCmd.Parameters.AddWithValue("@maritalStatus", dtPersonal.Rows(0).Item("maritalStatus"))
- projectSqlCmd.Parameters.AddWithValue("@jobTitleRef", dtPersonal.Rows(0).Item("jobTitleRef"))
- projectSqlCmd.Parameters.AddWithValue("@gradeRef", dtPersonal.Rows(0).Item("gradeRef"))
- projectSqlCmd.Parameters.AddWithValue("@nationRef", dtPersonal.Rows(0).Item("nationRef"))
- projectSqlCmd.Parameters.AddWithValue("@bloodType", dtPersonal.Rows(0).Item("bloodType"))
- projectSqlCmd.Parameters.AddWithValue("@IDCountryCode", dtPersonal.Rows(0).Item("IDCountryCode"))
- projectSqlCmd.Parameters.AddWithValue("@IDProvinceCode", dtPersonal.Rows(0).Item("IDProvinceCode"))
- projectSqlCmd.Parameters.AddWithValue("@IDCityCode", dtPersonal.Rows(0).Item("IDCityCode"))
- projectSqlCmd.Parameters.AddWithValue("@IDAddr", dtPersonal.Rows(0).Item("IDAddr"))
- projectSqlCmd.Parameters.AddWithValue("@IDPostCode", dtPersonal.Rows(0).Item("IDPostCode"))
- projectSqlCmd.Parameters.AddWithValue("@corresCountryCode", dtPersonal.Rows(0).Item("corresCountryCode"))
- projectSqlCmd.Parameters.AddWithValue("@corresProvinceCode", dtPersonal.Rows(0).Item("corresProvinceCode"))
- projectSqlCmd.Parameters.AddWithValue("@corresCityCode", dtPersonal.Rows(0).Item("corresCityCode"))
- projectSqlCmd.Parameters.AddWithValue("@corresAddr", dtPersonal.Rows(0).Item("corresAddr"))
- projectSqlCmd.Parameters.AddWithValue("@corresPostCode", dtPersonal.Rows(0).Item("corresPostCode"))
- projectSqlCmd.Parameters.AddWithValue("@taxAddr", dtPersonal.Rows(0).Item("taxAddr"))
- projectSqlCmd.Parameters.AddWithValue("@KTPID", dtPersonal.Rows(0).Item("KTPID"))
- projectSqlCmd.Parameters.AddWithValue("@SIMID", dtPersonal.Rows(0).Item("SIMID"))
- projectSqlCmd.Parameters.AddWithValue("@passportID", dtPersonal.Rows(0).Item("passportID"))
- projectSqlCmd.Parameters.AddWithValue("@HP1", dtPersonal.Rows(0).Item("HP1"))
- projectSqlCmd.Parameters.AddWithValue("@HP2", dtPersonal.Rows(0).Item("HP2"))
- projectSqlCmd.Parameters.AddWithValue("@phone1", dtPersonal.Rows(0).Item("phone1"))
- projectSqlCmd.Parameters.AddWithValue("@phone2", dtPersonal.Rows(0).Item("phone2"))
- projectSqlCmd.Parameters.AddWithValue("@fax", dtPersonal.Rows(0).Item("fax"))
- projectSqlCmd.Parameters.AddWithValue("@email1", dtPersonal.Rows(0).Item("email1"))
- projectSqlCmd.Parameters.AddWithValue("@email2", dtPersonal.Rows(0).Item("email2"))
- projectSqlCmd.Parameters.AddWithValue("@NPWP", dtPersonal.Rows(0).Item("NPWP"))
- projectSqlCmd.Parameters.AddWithValue("@CPName", dtPersonal.Rows(0).Item("CPName"))
- projectSqlCmd.Parameters.AddWithValue("@CPHP", dtPersonal.Rows(0).Item("CPHP"))
- projectSqlCmd.Parameters.AddWithValue("@CPPhone", dtPersonal.Rows(0).Item("CPPhone"))
- projectSqlCmd.Parameters.AddWithValue("@CPEmail", dtPersonal.Rows(0).Item("CPEmail"))
- projectSqlCmd.Parameters.AddWithValue("@noted", dtPersonal.Rows(0).Item("noted"))
- projectSqlCmd.Parameters.AddWithValue("@field1", dtPersonal.Rows(0).Item("field1"))
- projectSqlCmd.Parameters.AddWithValue("@field2", dtPersonal.Rows(0).Item("field2"))
- projectSqlCmd.Parameters.AddWithValue("@field3", dtPersonal.Rows(0).Item("field3"))
- projectSqlCmd.Parameters.AddWithValue("@inputUN", _nataPropertyInputUN)
- projectSqlCmd.ExecuteNonQuery()
- sqlCmd.Parameters.Clear()
- sqlCmd.Prepare()
- sqlCmd.CommandType = CommandType.Text
- sqlCmd.CommandText = "insert into " + _NSMainDB + "..MS_mappingPersonalProject(dbMasterRef,projectRef,psRef,projectPsRef) " + _
- "values(@dbMasterRef,@projectRef,@psRef,@projectPsRef) "
- sqlCmd.Parameters.AddWithValue("@dbMasterRef", dbMasterRef)
- sqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- sqlCmd.Parameters.AddWithValue("@psRef", memberPsRef)
- sqlCmd.Parameters.AddWithValue("@projectPsRef", projectMemberPsRef)
- sqlCmd.ExecuteNonQuery()
- 'iki error seng gawe aku kezeelllll
- 'Create Deal Closer
- 'Dim schemeRef As Integer = GetSettingValue(dtNUPOrder.Rows(0).Item("dbMasterRef"), dtNUPOrder.Rows(0).Item("projectRef"), _settingSchemeRef)
- 'Dim schemeRef As Integer = GetSettingValue(dbMasterRef, projectRef, memberRef)
- Dim schemeRef As Integer = getSchemeRef(dbMasterRef, projectRef, getMemberTypeString(memberRef))
- If schemeRef = 0 Then
- schemeRef = GetSettingValue(dbMasterRef, projectRef, _settingSchemeRef)
- End If
- projectSchemeMemberRef = schemeRef
- Dim projectSchemeDigit As String = getProjectSchemeDigit(dbMasterRef, projectRef, schemeRef)
- 'Dim projectPersonalCode as string = getProjectPersonalCode(dtNUPOrder.Rows(0).Item("dbMasterRef"), dtNUPOrder.Rows(0).Item("projectRef"), projectMemberPsRef)
- Dim memberCode = projectSchemeDigit + newPsCode
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "insert into com_ms_member " + _
- "(schemeRef, psRef, memberCode, [password], memberLevel " + _
- ", joinDate, bankRef, bankAccNo, bankAccName, bankBranchName " + _
- ", isActive, isManager, inputUN) " + _
- "select @schemeRef, @psRef, @memberCode, @password, @memberLevel " + _
- ", @joinDate, @bankRef, @bankAccNo, @bankAccName, @bankBranchName " + _
- ", @isActive, @isManager, @inputUN"
- projectSqlCmd.Parameters.AddWithValue("@schemeRef", schemeRef)
- projectSqlCmd.Parameters.AddWithValue("@psRef", projectMemberPsRef)
- projectSqlCmd.Parameters.AddWithValue("@memberCode", memberCode)
- projectSqlCmd.Parameters.AddWithValue("@password", "")
- projectSqlCmd.Parameters.AddWithValue("@memberLevel", "")
- projectSqlCmd.Parameters.AddWithValue("@joinDate", DateTime.Now)
- projectSqlCmd.Parameters.AddWithValue("@bankRef", "0")
- projectSqlCmd.Parameters.AddWithValue("@bankAccNo", "")
- projectSqlCmd.Parameters.AddWithValue("@bankAccName", "")
- projectSqlCmd.Parameters.AddWithValue("@bankBranchName", "")
- projectSqlCmd.Parameters.AddWithValue("@isActive", "1")
- projectSqlCmd.Parameters.AddWithValue("@isManager", "0")
- projectSqlCmd.Parameters.AddWithValue("@inputUN", _nataPropertyInputUN)
- projectSqlCmd.ExecuteNonQuery()
- sqlCmd.Parameters.Clear()
- sqlCmd.Prepare()
- sqlCmd.CommandType = CommandType.Text
- sqlCmd.CommandText = "insert into " + _NSMainDB + "..MS_mappingMemberProject(dbMasterRef,projectRef,psRef,schemeRef,projectPsRef) " + _
- "values(@dbMasterRef,@projectRef,@psRef,@schemeRef,@projectPsRef) "
- sqlCmd.Parameters.AddWithValue("@dbMasterRef", dbMasterRef)
- sqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- sqlCmd.Parameters.AddWithValue("@psRef", memberPsRef)
- sqlCmd.Parameters.AddWithValue("@schemeRef", projectSchemeMemberRef)
- sqlCmd.Parameters.AddWithValue("@projectPsRef", projectMemberPsRef)
- sqlCmd.ExecuteNonQuery()
- End If
- End If
- '' INSERT BOOKING
- '' INSERT BOOKING
- Try
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "select isnull(max(bookingRef),0) + 1 as ref " + _
- "from tr_booking "
- projectSqlDr = projectSqlCmd.ExecuteReader
- If projectSqlDr.Read Then
- bookingRef = projectSqlDr("ref")
- End If
- projectSqlDr.Close()
- Dim projectPsCode As String = ""
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "select psCode " + _
- "from PS_MS_personal where psRef = @psRef "
- projectSqlCmd.Parameters.AddWithValue("@psRef", projectPsRef)
- projectSqlDr = projectSqlCmd.ExecuteReader
- If projectSqlDr.Read Then
- projectPsCode = projectSqlDr("psCode")
- End If
- projectSqlDr.Close()
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "insert into TR_booking " + _
- " (bookingRef, bookingCode, salesEvent, salesLocation " + _
- " , salesReferral, salesPurpose, psRefCustomer, comSchemeRef " + _
- " , psRefDealCloser, unitRef, bookDate, noted, notedSP " + _
- " , bookStatus, inputUN,bookingSourceRef) " + _
- "values (@bookingRef, @bookingCode, @salesEvent, @salesLocation " + _
- " , @salesReferral, @salesPurpose, @psRefCustomer, @comSchemeRef " + _
- " , @psRefDealCloser, @unitRef, @bookDate, @noted, @notedSP " + _
- " , @bookStatus, @inputUN,@bookingSourceRef)"
- projectSqlCmd.Parameters.AddWithValue("@bookingRef", bookingRef)
- projectSqlCmd.Parameters.AddWithValue("@bookingCode", projectPsCode.ToString() + bookingRef.ToString())
- projectSqlCmd.Parameters.AddWithValue("@salesEvent", GetSettingValue(dbMasterRef, projectRef, _settingSalesEventRef))
- projectSqlCmd.Parameters.AddWithValue("@salesLocation", GetSettingValue(dbMasterRef, projectRef, _settingSalesLocationRef))
- projectSqlCmd.Parameters.AddWithValue("@salesReferral", GetSettingValue(dbMasterRef, projectRef, _settingSalesReferralRef))
- projectSqlCmd.Parameters.AddWithValue("@salesPurpose", GetSettingValue(dbMasterRef, projectRef, _settingSalesPurposeRef))
- projectSqlCmd.Parameters.AddWithValue("@psRefCustomer", projectPsRef)
- projectSqlCmd.Parameters.AddWithValue("@comSchemeRef", projectSchemeMemberRef)
- projectSqlCmd.Parameters.AddWithValue("@psRefDealCloser", projectMemberPsRef)
- projectSqlCmd.Parameters.AddWithValue("@unitRef", unitRef)
- projectSqlCmd.Parameters.AddWithValue("@bookDate", DateTime.Now)
- projectSqlCmd.Parameters.AddWithValue("@noted", "")
- projectSqlCmd.Parameters.AddWithValue("@notedSP", "")
- projectSqlCmd.Parameters.AddWithValue("@bookStatus", _bookStatusOnline)
- projectSqlCmd.Parameters.AddWithValue("@inputUN", _nataPropertyInputUN)
- projectSqlCmd.Parameters.AddWithValue("@bookingSourceRef", _bookingSourceAndroid)
- projectSqlCmd.ExecuteNonQuery()
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "insert into TR_bookingTerm " + _
- " (bookingRef, BFAmt, finSchemeRef, discCalc, dPCalc, addDiscCalc " + _
- " , finStartDueDay, inputUN, logTermRef, logTermName, logTermDesc, logTermNo " + _
- " , logTermNoName, logTermNoDesc, projectRef) " + _
- "select @bookingRef, t.BFAmt, tn.finSchemeRef, tn.discCalc, tn.DPCalc, tn.addDiscCalc " + _
- " , tn.finStartDueDay, @inputUN, t.termRef, t.termName, t.termDesc, tn.termNo " + _
- " , tn.termNoName, tn.termNoDesc, @projectRef " + _
- "from ms_Term t, ms_termNo tn " + _
- "where t.projectRef = tn.projectRef and t.termRef = tn.termRef " + _
- " and t.termRef = @termRef and tn.termNo = @termNo and t.projectRef = @projectRef"
- projectSqlCmd.Parameters.AddWithValue("@bookingRef", bookingRef)
- projectSqlCmd.Parameters.AddWithValue("@termRef", termRef)
- projectSqlCmd.Parameters.AddWithValue("@termNo", termNo)
- projectSqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- projectSqlCmd.Parameters.AddWithValue("@inputUN", _nataPropertyInputUN)
- projectSqlCmd.ExecuteNonQuery()
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "insert into TR_bookingTermAddDisc " + _
- " (bookingRef, addDiscNo, addDiscPct, inputUN) " + _
- " select @bookingRef, addDiscNo, addDiscPct, @inputUN " + _
- " from ms_termNoAddDisc " + _
- " where termRef = @termRef And termNo = @termNo and projectRef = @projectRef " + _
- " order by addDiscNo "
- projectSqlCmd.Parameters.AddWithValue("@bookingRef", bookingRef)
- projectSqlCmd.Parameters.AddWithValue("@termRef", termRef)
- projectSqlCmd.Parameters.AddWithValue("@termNo", termNo)
- projectSqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- projectSqlCmd.Parameters.AddWithValue("@inputUN", _nataPropertyInputUN)
- projectSqlCmd.ExecuteNonQuery()
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "insert into TR_bookingTermDP " + _
- " (bookingRef, DPNo, dueDays,dueMonths, DPPct, DPAmt, inputUN, markAsIns) " + _
- "select @bookingRef, DPNo, dueDays,dueMonths, DPPct, DPAmt, @inputUN, markAsIns " + _
- "from ms_termNoDP " + _
- "where termRef = @termRef And termNo = @termNo and projectRef = @projectRef " + _
- "order by DPNo "
- projectSqlCmd.Parameters.AddWithValue("@bookingRef", bookingRef)
- projectSqlCmd.Parameters.AddWithValue("@termRef", termRef)
- projectSqlCmd.Parameters.AddWithValue("@termNo", termNo)
- projectSqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- projectSqlCmd.Parameters.AddWithValue("@inputUN", _nataPropertyInputUN)
- projectSqlCmd.ExecuteNonQuery()
- Dim strQueryPrice As New StringBuilder
- For i = 0 To dtPrice.Rows.Count - 1
- strQueryPrice.Append("insert into tr_bookingPrice(bookingRef, priceRef, priceType, priceIncVATAmt, taxPct, inputUN) values (@bookingRef, " + dtPrice.Rows(i).Item("priceRef").ToString + ", " + dtPrice.Rows(i).Item("priceType").ToString + ", " + dtPrice.Rows(i).Item("priceIncVATAmt").ToString.Replace(",", ".") + ", " + dtPrice.Rows(i).Item("taxPct").ToString.Replace(",", ".") + ", @inputUN) ")
- Next
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = strQueryPrice.ToString
- projectSqlCmd.Parameters.AddWithValue("@bookingRef", bookingRef)
- projectSqlCmd.Parameters.AddWithValue("@inputUN", _nataPropertyInputUN)
- projectSqlCmd.ExecuteNonQuery()
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "insert into lg_unitStatus (unitRef, unitStatusFr, unitStatusTo, inputUN) " + _
- "select unitRef, unitStatus, @unitStatusTo, @inputUN " + _
- "from ms_unit where unitRef = @unitRef "
- projectSqlCmd.Parameters.AddWithValue("@unitStatusTo", _unitStatusSold)
- projectSqlCmd.Parameters.AddWithValue("@inputUN", _nataPropertyInputUN)
- projectSqlCmd.Parameters.AddWithValue("@unitRef", unitRef)
- projectSqlCmd.ExecuteNonQuery()
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- projectSqlCmd.CommandText = "update ms_unit set unitStatus = @unitStatus where unitRef = @unitRef"
- projectSqlCmd.Parameters.AddWithValue("@unitStatus", _unitStatusSold)
- projectSqlCmd.Parameters.AddWithValue("@unitRef", unitRef)
- projectSqlCmd.ExecuteNonQuery()
- projectSqlCmd.Parameters.Clear()
- projectSqlCmd.Prepare()
- projectSqlCmd.CommandType = CommandType.Text
- If Trim(itemTypeList) = "-" Then
- projectSqlCmd.CommandText = "update ms_unitItem set isBook = '1' where unitRef = @unitRef "
- Else
- projectSqlCmd.CommandText = "update ms_unitItem set isBook = '1' where unitRef = @unitRef and itemType in (" + itemTypeList + ") "
- End If
- projectSqlCmd.Parameters.AddWithValue("@unitRef", unitRef)
- projectSqlCmd.ExecuteNonQuery()
- Catch ex As Exception
- Throw ex
- End Try
- ' MAPPING BOOKING
- sqlCmd.Parameters.Clear()
- sqlCmd.Prepare()
- sqlCmd.CommandType = CommandType.Text
- sqlCmd.CommandText = "select isnull(max(bookingRef),0) + 1 as ref " + _
- "from " + _NSMainDB + "..TR_memberBooking "
- sqlDr = sqlCmd.ExecuteReader
- If sqlDr.Read Then
- result = sqlDr("ref")
- End If
- sqlDr.Close()
- sqlCmd.Parameters.Clear()
- sqlCmd.Prepare()
- sqlCmd.CommandType = CommandType.Text
- sqlCmd.CommandText = "insert into " + _NSMainDB + "..TR_memberBooking(bookingRef,dbMasterRef,projectRef,memberRef,projectBookingRef,bookingOrderCode,agentMemberRef, inputUN, buySource) " + _
- "select @bookingRef,@dbMasterRef,@projectRef,@memberRef,@projectBookingRef, 'B' + right(cast(year(getdate()) As varchar(4)),2) + right('00' + cast(month(getdate()) as varchar(2)),2) + " + _
- " right('0000' + cast(isnull(max(cast(right(bookingOrderCode,4) as integer)),0) + 1 as varchar(4)),4), @agentMemberRef, @inputUN, 2 " + _
- "from " + _NSMainDB + "..TR_memberBooking " + _
- "where cast('20' + substring(bookingOrderCode,2,2) as integer) = year(getdate()) " + _
- " and cast(substring(bookingOrderCode,4,2) as integer) = month(getdate()) "
- projectBookingRef = bookingRef
- sqlCmd.Parameters.AddWithValue("@dbMasterRef", dbMasterRef)
- sqlCmd.Parameters.AddWithValue("@projectRef", projectRef)
- sqlCmd.Parameters.AddWithValue("@bookingRef", result)
- sqlCmd.Parameters.AddWithValue("@memberRef", memberRef)
- sqlCmd.Parameters.AddWithValue("@projectBookingRef", bookingRef)
- sqlCmd.Parameters.AddWithValue("@agentMemberRef", agentMemberRef)
- sqlCmd.Parameters.AddWithValue("@inputUN", inputUN)
- sqlCmd.ExecuteNonQuery()
- sqlTrans.Commit()
- projectSqlTrans.Commit()
- Catch ex As Exception
- sqlTrans.Rollback()
- projectSqlTrans.Rollback()
- result = ex.Message
- End Try
- sqlCmd = Nothing
- projectSqlCmd = Nothing
- sqlCon.Close()
- projectSqlCon.Close()
- Return result
- End Function
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement