Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Friend Class frmImportarDadosLivreSef
	Inherits System.Windows.Forms.Form
	Private istrNmArq As String
	
	Private Sub cmdArquivo_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdArquivo.Click
		Dim lstrAux As String
		Me.dialArqOpen.FileName = My.Application.Info.DirectoryPath & "\" & istrNmArq
		Me.dialArqOpen.ShowDialog()
		Call MontaNomeArquivo()
	End Sub
	
	Private Sub cmdImporta_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdImporta.Click
		Dim llngPeriodo As Integer
		Dim llngLinhas As Integer
		Dim lstrCpfCnpj As String
		Dim lstrInscEst As String
		Dim lstrRazSoci As String
		Dim lstrCgcCons As String
		Dim lstrLogrado As String
		Dim ldblPrMedio As Double
		'
		Dim llngConsId As Integer
		Dim llngUnidId As Integer
		Dim lstrZona As String
		Dim lstrLivro As String
		Dim lstrConta As String
		Dim llngIdCCEE As Integer
		'Controle Arquivo
		Dim lstrLin As String
		Dim lintARQ As Short
		Dim lblnCon As Boolean
		Dim lblnExi As Boolean
		'
		Dim lstrSql As String
		Dim lrstAux As ADODB.Recordset
		lrstAux = New ADODB.Recordset
		On Error GoTo ErroArquivo
		llngPeriodo = CInt(VB6.Format(Me.dtpPeriodo.value, "YYYYMM"))
		If Trim(Me.txtArquivo.Text) = "" Then
			Call MsgBox("Você deve selecionar um local e o nome do arquivo que deseja gravar!" & vbCrLf & "Ex: C:\" & gstrEmpresa_CNPJ & "_" & VB6.Format(Me.dtpPeriodo.value, "YYMM") & Me.dialArqOpen.DefaultExt, MsgBoxStyle.Critical, gstrTituloMensagem)
		Else
			llngLinhas = PegaNumLinhas((Me.txtArquivo.Text))
			If llngLinhas > 0 Then
				If Not VerificaPeriodoArquivo(llngPeriodo, istrNmArq) Then
					Call MsgBox("O período do arquivo não esta igual ao período informado!", MsgBoxStyle.Critical, gstrTituloMensagem)
				Else
					lblnCon = True
					If VerificaSeExiste(llngPeriodo, istrNmArq) Then
						lblnCon = (MsgBox("Este arquivo deste período já foi carregado. Deseja sobrescrever?", MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2, gstrTituloMensagem) = MsgBoxResult.Yes)
					End If
					If lblnCon Then
						Call LigDes(False)
						' ----- Abre arquivo externo
						lintARQ = FreeFile
						FileOpen(lintARQ, Me.txtArquivo.Text, OpenMode.Input)
						Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, llngLinhas)
						While Not EOF(lintARQ)
							lstrLin = LineInput(lintARQ)
							lstrLin = Trim(lstrLin)
							lstrCpfCnpj = Mid(lstrLin, 1, 14) 'CNPJ do Ponto de Consumo, 14, de 1 até 14, Numérico
							lstrInscEst = Mid(lstrLin, 15, 9) 'IE do Ponto de Consumo, 9, de 15 até 23, Numérico
							lstrRazSoci = Mid(lstrLin, 24, 50) 'Denominação/Razão Social, 50, de 24 até 73, Alfanumérico
							lstrLogrado = Mid(lstrLin, 74, 250) 'Endereço Completo, 250, de 74 até 323, Alfanumérico
							ldblPrMedio = CDbl(IIf(IsNumeric(Mid(lstrLin, 324, 13)), Mid(lstrLin, 324, 13), 0)) / 2 'Preço Médio por MWh, 13, de 324 até 336, Numérico 11V2
							lstrSql = ""
							lstrSql = lstrSql & "SELECT "
							lstrSql = lstrSql & "       `consreg`, "
							lstrSql = lstrSql & "       `cgc`, "
							lstrSql = lstrSql & "       `nome` "
							lstrSql = lstrSql & "  FROM `consum` "
							lstrSql = lstrSql & " WHERE `cgc` = '" & lstrCpfCnpj & "' "
							Call Conectar(lstrSql, lrstAux)
							With lrstAux
								If .State = ADODB.ObjectStateEnum.adStateOpen Then
									If .RecordCount > 0 Then
										'UPGRADE_WARNING: Use of Null/IsNull() detected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="2EED02CB-5C0E-4DC1-AE94-4FAA3A30F51A"'
										llngConsId = CInt(IIf(IsDbNull(.Fields("consreg").value), 0, .Fields("consreg").value))
									End If
									Call .Close()
								End If
							End With
							'Pega cons_id pelo CNPJ, e o unid_id pelo cons_id
							If llngConsId > 0 Then
								lstrSql = ""
								lstrSql = lstrSql & "SELECT "
								lstrSql = lstrSql & "       `unid_id`, "
								lstrSql = lstrSql & "       `cons_id`, "
								lstrSql = lstrSql & "       `nome`, "
								lstrSql = lstrSql & "       `cpfcnpj` "
								lstrSql = lstrSql & "  FROM `unida` "
								lstrSql = lstrSql & " WHERE `cons_id` = " & llngConsId & " "
								Call Conectar(lstrSql, lrstAux)
								With lrstAux
									If .State = ADODB.ObjectStateEnum.adStateOpen Then
										If .RecordCount > 0 Then
											'UPGRADE_WARNING: Use of Null/IsNull() detected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="2EED02CB-5C0E-4DC1-AE94-4FAA3A30F51A"'
											llngUnidId = CInt(IIf(IsDbNull(.Fields("unid_id").value), 0, .Fields("unid_id").value))
										End If
										Call .Close()
									End If
								End With
							End If
							lblnExi = False
							'Verifica se o registro já existe para o consumidor individualmente
							lstrSql = ""
							lstrSql = lstrSql & "SELECT "
							lstrSql = lstrSql & "       `referencia`, "
							lstrSql = lstrSql & "       `cnpj`, "
							lstrSql = lstrSql & "       `cons_id`, "
							lstrSql = lstrSql & "       `unid_id` "
							lstrSql = lstrSql & "  FROM `precomediolivre` "
							lstrSql = lstrSql & " WHERE `referencia` = " & llngPeriodo & " "
							lstrSql = lstrSql & "   AND `cons_id` = " & llngConsId & " "
							lstrSql = lstrSql & "   AND `unid_id` = " & llngUnidId & " "
							Call Conectar(lstrSql, lrstAux)
							With lrstAux
								If .State = ADODB.ObjectStateEnum.adStateOpen Then
									lblnExi = (.RecordCount > 0)
									Call .Close()
								End If
							End With
							If lblnExi Then
								lstrSql = "UPDATE "
							Else
								lstrSql = "INSERT INTO "
							End If
							lstrSql = lstrSql & "`precomediolivre` SET "
							lstrSql = lstrSql & "`referencia`      = " & llngPeriodo & ", "
							lstrSql = lstrSql & "`cnpj`            = " & CInt(lstrCpfCnpj) & ", "
							lstrSql = lstrSql & "`ie`              = '" & lstrInscEst & "', "
							lstrSql = lstrSql & "`nome`            = '" & Replace(lstrRazSoci, "'", "\'") & "', "
							lstrSql = lstrSql & "`logradouro`      = '" & Replace(lstrLogrado, "'", "\'") & "', "
							lstrSql = lstrSql & "`preco_medio_mwh` = " & Replace(CStr(ldblPrMedio), ",", ".") & ", "
							lstrSql = lstrSql & "`nomearquivo`     = '" & Replace(lstrLogrado, "'", "\'") & "', "
							lstrSql = lstrSql & "`cons_id`         = " & llngConsId & ", "
							lstrSql = lstrSql & "`unid_id`         = " & llngUnidId & " "
							If lblnExi Then
								lstrSql = lstrSql & " WHERE `referencia` = " & llngPeriodo & ", "
								lstrSql = lstrSql & "   AND `cnpj`       = " & CInt(lstrCpfCnpj) & ", "
								lstrSql = lstrSql & "   AND `cons_id`    = " & llngConsId & " "
								lstrSql = lstrSql & "   AND `unid_id`    = " & llngUnidId & " "
							End If
							Call Executar(lstrSql)
							Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
						End While
					End If
				End If
			End If
		End If
ErroArquivo: 
		If Err.Number <> 0 Then
			Call MsgBox("Ocorreu um erro no processo: " & Err.Description, MsgBoxStyle.Exclamation, gstrTituloMensagem)
		End If
		FileClose(lintARQ)
		'UPGRADE_NOTE: Object lrstAux may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
		lrstAux = Nothing
	End Sub
	
	Private Sub cmdFechar_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdFechar.Click
		Call Me.Close()
	End Sub
	
	Private Sub dtpPeriodo_Change(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles dtpPeriodo.Change
		Call MontaNomeArquivo()
	End Sub
	
	Private Sub dtpPeriodo_ClickEvent(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles dtpPeriodo.ClickEvent
		Call dtpPeriodo_Change(dtpPeriodo, New System.EventArgs())
	End Sub
	
	Private Sub frmImportarDadosLivreSef_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		Dim lstrSql As String
		Call CenterForm(Me)
		istrNmArq = "PME-" & VB6.Format(gstrEmpresa_CNPJ, New String("0", 14)) & "_" & VB6.Format(Me.dtpPeriodo.value, "YYMM") & "." & Me.dialArqOpen.DefaultExt
		'UPGRADE_WARNING: CommonDialog variable was not upgraded Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="671167DC-EA81-475D-B690-7A40C7BF4A23"'
		With Me.dialArq
			.DefaultExt = "TXT"
			'UPGRADE_WARNING: Filter has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="9B7D5ADD-D8FE-4819-A36C-6DEDAF088CC7"'
			.Filter = "*." & .DefaultExt
			.Title = "Arquivo de Importação da Portaria SEF nº 342/12, Art. 6º, inciso II"
			.FileName = My.Application.Info.DirectoryPath & "\" & istrNmArq
			Me.txtArquivo.Text = .FileName
		End With
		Me.dtpPeriodo.value = BuscaDataBD
		Call MontaNomeArquivo()
		lstrSql = ""
		lstrSql = lstrSql & "CREATE TABLE IF NOT EXISTS `precomediolivre`("
		lstrSql = lstrSql & "`referencia`      int(11)      NOT NULL DEFAULT 0,"
		lstrSql = lstrSql & "`cnpj`            bigint(14)   NOT NULL DEFAULT 0,"
		lstrSql = lstrSql & "`ie`              varchar(25)  NULL DEFAULT '',"
		lstrSql = lstrSql & "`nome`            varchar(50)  NULL DEFAULT '',"
		lstrSql = lstrSql & "`logradouro`      varchar(250) NULL DEFAULT '',"
		lstrSql = lstrSql & "`preco_medio_mwh` double(11,2) NULL DEFAULT 0.00,"
		lstrSql = lstrSql & "`nomearquivo`     varchar(200) NULL DEFAULT '',"
		lstrSql = lstrSql & "`cons_id`         int(11)      NOT NULL DEFAULT 0,"
		lstrSql = lstrSql & "`unid_id`         int(11)      NOT NULL DEFAULT 0,"
		lstrSql = lstrSql & "PRIMARY KEY (`referencia`,`cnpj`),"
		lstrSql = lstrSql & "KEY `idx_cons_id` (`cons_id`),"
		lstrSql = lstrSql & "KEY `idx_unid_id` (`unid_id`),"
		lstrSql = lstrSql & "KEY `idx_nomearquivo` (`nomearquivo`)"
		lstrSql = lstrSql & ") ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT"
		Call Executar(lstrSql)
	End Sub
	
	Private Sub MontaNomeArquivo()
		Dim lstrAux As String
		istrNmArq = "PME-" & VB6.Format(gstrEmpresa_CNPJ, New String("0", 14)) & "_" & VB6.Format(Me.dtpPeriodo.value, "YYMM") & "." & Me.dialArqOpen.DefaultExt
		'
		lstrAux = StrReverse(Me.dialArqOpen.FileName)
		lstrAux = Mid(lstrAux, CInt(InStr(1, lstrAux, "\")))
		lstrAux = StrReverse(lstrAux) & istrNmArq
		Me.dialArqOpen.FileName = lstrAux
		Me.txtArquivo.Text = Me.dialArqOpen.FileName
	End Sub
	
	Private Sub LigDes(ByVal ablnModo As Boolean)
		Me.dtpPeriodo.Enabled = ablnModo
		Me.cmdArquivo.Enabled = ablnModo
		Me.cmdImporta.Enabled = ablnModo
		Me.cmdFechar.Enabled = ablnModo
	End Sub
	
	Private Function PegaNumLinhas(ByRef astrArquivo As String) As Integer
		Dim lintARQ As Short
		Dim llngLin As Integer
		Dim lstrAux As String
		On Error GoTo ErroPegaNumLinhas
		llngLin = 0
		lintARQ = FreeFile
		FileOpen(lintARQ, astrArquivo, OpenMode.Input)
		While Not EOF(lintARQ)
			lstrAux = LineInput(lintARQ)
			llngLin = llngLin + 1
			System.Windows.Forms.Application.DoEvents()
		End While
ErroPegaNumLinhas: 
		If Err.Number <> 0 Then
			Call MsgBox("Erro: " & Err.Description, MsgBoxStyle.Information, gstrTituloMensagem)
			llngLin = 0
		End If
		FileClose(lintARQ)
		PegaNumLinhas = llngLin
	End Function
	
	Private Function VerificaSeExiste(ByVal alngPeriodo As Integer, ByVal astrArquivo As String) As Boolean
		'1 - Verificar se o arquivo ja foi carregado alguma vez
		'2 - Verificar se o mês e ano já foi carregado alguma vez
		Dim lblnRet As Boolean
		Dim lstrSql As String
		Dim lrstAux As ADODB.Recordset
		lrstAux = New ADODB.Recordset
		lstrSql = ""
		lstrSql = lstrSql & "SELECT "
		lstrSql = lstrSql & "       `referencia`, "
		lstrSql = lstrSql & "       `cnpf`, "
		lstrSql = lstrSql & "       `cons_id`, "
		lstrSql = lstrSql & "       `unid_id`, "
		lstrSql = lstrSql & "       `nomearquivo` "
		lstrSql = lstrSql & "  FROM `precomediolivre` "
		lstrSql = lstrSql & " WHERE `referencia` = " & alngPeriodo & " "
		lstrSql = lstrSql & "   AND `nomearquivo` = '" & astrArquivo & "' "
		Call Conectar(lstrSql, lrstAux)
		With lrstAux
			If .State = ADODB.ObjectStateEnum.adStateOpen Then
				lblnRet = (.RecordCount > 0)
			End If
		End With
		VerificaSeExiste = lblnRet
		'UPGRADE_NOTE: Object lrstAux may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
		lrstAux = Nothing
	End Function
	
	Private Function VerificaPeriodoArquivo(ByVal alngPeriodo As Integer, ByVal astrArquivo As String) As Boolean
		Dim lstrAux As String
		Dim llngAux As Integer
		lstrAux = VB.Left(astrArquivo, CInt(Len(astrArquivo)) - 4) '.TXT
		llngAux = CInt(VB.Right(lstrAux, 4))
		VerificaPeriodoArquivo = (CInt(VB.Right(CStr(alngPeriodo), 4)) = llngAux)
	End Function
End Class