VIDA SIMPLES - CONTROLE DE PROPOSTA

<% Set Database = Server.CreateObject("ADODB.Connection") Database.Open StringConexao wID_PROPOSTA=request.item("Proposta") wID_CLIENTE=request.item("Cliente") wID_PLANO=request.item("Plano") wCERTIFICADO=request.item("Certificado") wTitular=request.item("Titular") wID_Empresa=request.item("Empresa") wID_Vendedor=request.item("ID_Vendedor") wVendedor=request.item("Vendedor") wAlt=request.item("Alteracao") if wTitular = "" then wTitular="N" end if wCA_USUARIO="Admin" wSQL="SELECT RESULTADO, RDESCRICAO FROM VERIFICA_IDADE_PLANO(" & wID_CLIENTE & "," & wID_PLANO & ")" Set TabVerificaIdade = Database.Execute(wSQL) if TabVerificaIdade("RESULTADO") < 1 then response.redirect("Reg_Idade_Invalida.asp?MSG=" & TabVerificaIdade("RDESCRICAO")) else if wAlt = "S" then wSQL="DELETE FROM PROPOSTAS_CLIENTES WHERE ID_PROPOSTA=" & wID_PROPOSTA & " AND ID_CLIENTE = " & wID_CLIENTE Set TabProposta = Database.Execute(wSQL) end if if wCERTIFICADO = "" then wCERTIFICADO = request.item("CPF_Certificado") wCERTIFICADO = replace(wCERTIFICADO, ".", "") wCERTIFICADO = replace(wCERTIFICADO, "-", "") wCERTIFICADO = replace(wCERTIFICADO, "/", "") end if wSQL="INSERT INTO PROPOSTAS_CLIENTES (ID_PROPOSTA, ID_CLIENTE, ID_PLANO, CERTIFICADO, TITULAR, CA_USUARIO)" wSQL=wSQL & " VALUES (" & wID_PROPOSTA & "," & wID_CLIENTE & "," & wID_PLANO & ",'" & wCERTIFICADO & "','" & wTitular & "','" & wCA_USUARIO & "')" Set TabProposta = Database.Execute(wSQL) 'Retirado essa função 'wSQL="DELETE FROM PROPOSTAS_PARCELAS WHERE ID_PROPOSTA=" & wID_PROPOSTA & " AND DT_RECEBIMENTO IS NULL" wSQL="SELECT SUM(P.VLR_MENSAL) AS VALOR FROM PROPOSTAS_CLIENTES PC " wSQL= wSQL & " LEFT JOIN PLANOS P ON P.ID = PC.ID_PLANO WHERE PC.ID_PROPOSTA = " & wID_PROPOSTA Set TabParcela = Database.Execute(wSQL) wVALOR=FormatarValor(TabParcela("VALOR")) wSQL="SELECT P.DT_VENCIMENTO FROM PROPOSTAS P WHERE P.ID=" & wID_PROPOSTA Set TabParcela = Database.Execute(wSQL) wDT_VENCIMENTO=TabParcela("DT_VENCIMENTO") wSQL=" SELECT * FROM PROC_VERIFICA_PARCELA_BOL(" & wID_PROPOSTA & ")" Set TabExcluiParcela = Database.Execute(wSQL) wSQL="SELECT PLA.QT_PARCELAS FROM PLANOS PLA WHERE PLA.ID = " & wID_PLANO Set TabQTParcela = Database.Execute(wSQL) for i=1 to TabQTParcela("QT_PARCELAS") wID_PARCELA=i wSQL="INSERT INTO PROPOSTAS_PARCELAS (ID_PROPOSTA,ID_PARCELA,DT_VENCIMENTO,VALOR,CA_USUARIO) " wSQL=wSQL & " VALUES (" & wID_PROPOSTA & "," & wID_PARCELA & ",'" & replace(wDT_VENCIMENTO, "/", ".") & "'," & wVALOR & ",'" & wCA_USUARIO & "')" Set TabParcela = Database.Execute(wSQL) wDT_VENCIMENTO=DateAdd("m", 1, wDT_VENCIMENTO) Next 'Busca se há captalização if wAlt <> "S" then wSQL="SELECT SP.ID, SP.VLR_CIA, SP.VLR_SORTEIO, SP.QT_SORTEIO FROM SEGURADORAS_PLANOS SP INNER JOIN PLANOS_PLANOS_SEG PS ON PS.ID_PLANO_SEG = SP.ID" wSQL=wSQL & " AND PS.ID_PLANO = " & wID_PLANO wSQL=wSQL & " WHERE SP.CAPTALIZACAO = 'S'" Set TabCap = Database.Execute(wSQL) While NOT TabCap.EoF wID_Plano=TabCap("ID") wSQL="SELECT FIRST 1 PT.ID_PLANO, PT.TICKET" wSQL=wSQL & " FROM SEGURADORAS_PLANOS_TICKETS PT" wSQL=wSQL & " WHERE PT.DT_LIBERADO < CURRENT_DATE" wSQL=wSQL & " AND ID_PLANO = " & wID_Plano Set TabTicket = Database.Execute(wSQL) if not TabTicket.EOF then wSQL="UPDATE SEGURADORAS_PLANOS_TICKETS PT SET DT_LIBERADO = CURRENT_DATE + ((" wSQL=wSQL & " SELECT SP.DIA_LIB_TICKET" wSQL=wSQL & " FROM SEGURADORAS_PLANOS SP" wSQL=wSQL & " WHERE SP.ID = PT.ID_PLANO) * " & TabCap("QT_SORTEIO") & ")" wSQL=wSQL & " , STATUS = 'C', AT_USUARIO = CURRENT_DATE" wSQL=wSQL & " WHERE ID_PLANO = " & wID_Plano wSQL=wSQL & " AND TICKET = " & TabTicket("TICKET") Set TabTicketUp = Database.Execute(wSQL) wSQL=" INSERT INTO TICKETS_PROPOSTAS (ID_EMPRESA, ID_PROPOSTA, " wSQL=wSQL & " ID_PLANO, ID_TICKET, ID_CLIENTE, INI_VIGENCIA, FIM_VIGENCIA, " wSQL=wSQL & " ID_VENDEDOR, VLR_SORTEIO, VLR_TICKET, QT_SORTEIO) " wSQL=wSQL & " VALUES ( " wSQL=wSQL & wID_EMPRESA & "," & wID_PROPOSTA & "," & wID_PLANO & "," & TabTicket("TICKET") & "," wSQL=wSQL & wID_CLIENTE & "," & " CURRENT_DATE + 31 " & "," & " CURRENT_DATE + ((" wSQL=wSQL & " SELECT SP.DIA_LIB_TICKET" wSQL=wSQL & " FROM SEGURADORAS_PLANOS SP" wSQL=wSQL & " WHERE SP.ID = " & wID_PLANO & ") * " & TabCap("QT_SORTEIO") & ")," wSQL=wSQL & wVendedor & "," & FormatarValor(TabCap ("VLR_SORTEIO")) & "," & FormatarValor(TabCap("VLR_CIA")) & "," wSQL=wSQL & TabCap ("QT_SORTEIO") wSQL=wSQL & " )" Set TabTicketUp = Database.Execute(wSQL) Dim wCont wCont = 1 wQt = TabCap("QT_SORTEIO") While wCont <= wQt wSQL="INSERT INTO TICKETS (ID_PLANO, TICKET, MES, ANO, ID_EMPRESA, ID_CLIENTE," wSQL=wSQL & " ID_VENDEDOR, VL_SORTEIO, VL_TICKET," wSQL=wSQL & " DT_CADASTRO, QT_SORTEIO)" wSQL=wSQL & " VALUES (" & wID_PLANO & "," & TabTicket("TICKET") wSQL=wSQL & " , EXTRACT(MONTH FROM (CURRENT_DATE + " & (31 * wCont) & " )), " wSQL=wSQL & " EXTRACT(YEAR FROM (CURRENT_DATE + " & (31 * wCont) & " )), " wSQL=wSQL & " " & wID_EMPRESA & "," & wID_CLIENTE & "," wSQL=wSQL & " " & wVendedor & "," & FormatarValor(TabCap ("VLR_SORTEIO")) & "," & FormatarValor(TabCap("VLR_CIA")) & "," wSQL=wSQL & " CURRENT_DATE, " & TabCap ("QT_SORTEIO") & ")" Set TabTicketInsert = Database.Execute(wSQL) wCont=wCont + 1 Wend end if TabCap.MoveNext Wend end if response.redirect("Cad_Propostas_Detalhes.asp?TProposta=" & wID_PROPOSTA & "&Acao=C") end if %>

<%=wSQL%> Proposta - Clientes - Confirmação