UFQDZkub

1using System;
2using System.Collections.Generic;
3using System.Data;
4using System.Globalization;
5using System.IO;
6using System.Security.Cryptography.Xml;
7using System.Text;
8using System.Web;
9using System.Xml;
10
11namespace Logisk.Models
12{
13	public class Versao
14	{
15		/// <summary>
16		/// Versao maior
17		/// </summary>
18		private readonly int mv = 3;
19
20		/// <summary>
21		/// Versao menor
22		/// </summary>
23		private readonly int mm = 0;
24
25		/// <summary>
26		/// Revisão
27		/// </summary>
28		private readonly int rv = 7702;
29
30
31		public int Maior;
32		public int Minor;
33		public int Revisao;
34
35		public Versao ()
36		{
37
38		}
39
40		/// <summary>
41		/// Retorna versao atual do aplicativo
42		/// </summary>
43		/// <returns>
44		/// A <see cref="Versao"/>
45		/// </returns>
46		public Versao Atual ()
47		{
48			Versao vs = new Versao ();
49			vs.Maior = vs.mv;
50			vs.Minor = vs.mm;
51			vs.Revisao = vs.rv;
52			return vs;
53		}
54
55		/// <summary>
56		/// Retorna a representação em string da versão do aplicativo
57		/// </summary>
58		/// <returns>
59		/// A <see cref="System.String"/>
60		/// </returns>
61		public override string ToString ()
62		{
63			return string.Format ("{0}.{1}.{2}", Maior, Minor, Revisao);
64		}
65
66		/// <summary>
67		/// Convert a variavel versao string para o tipo Versao
68		/// </summary>
69		/// <param name="nversao">
70		/// A <see cref="System.String"/>
71		/// </param>
72		/// <returns>
73		/// A <see cref="Versao"/>
74		/// </returns>
75		public Versao ConvertTo (string nversao)
76		{
77			string [] vsv = new string [3];
78			vsv = nversao.Split ('.');
79			Maior = Convert.ToInt32 (vsv [0]);
80			Minor = Convert.ToInt32 (vsv [1]);
81			Revisao = Convert.ToInt32 (vsv [2]);
82			return this;
83		}
84
85		private static bool TesteM (Versao a, Versao b)
86		{
87			if (a.Maior > b.Maior) {
88				return true;
89			} else if (a.Maior < b.Maior) {
90				return false;
91			} else {
92				if (a.Minor > b.Minor) {
93					return true;
94				} else if (a.Minor < b.Minor) {
95					return false;
96				} else {
97					if (a.Revisao > b.Revisao)
98						return true;
99					else
100						return false;
101				}
102			}
103		}
104
105		public static string Print ()
106		{
107			Versao vs = new Versao ();
108			return vs.Atual ().ToString ();
109		}
110
111		public static bool operator > (Versao a, Versao b)
112		{
113			return Versao.TesteM (a, b);
114		}
115
116		public static bool operator < (Versao a, Versao b)
117		{
118			return Versao.TesteM (b, a);
119		}
120
121		public static bool operator >= (Versao a, Versao b)
122		{
123			return (Versao.TesteM (a, b) || a.ToString ().Equals (b.ToString ()));
124		}
125
126		public static bool operator <= (Versao a, Versao b)
127		{
128			return (Versao.TesteM (b, a) || a.ToString ().Equals (b.ToString ()));
129		}
130
131	}
132
133	public class LogiskApp : LogiskDB
134	{
135		public string nomeApp;
136		/// <summary>
137		/// Versao da ultima atualização
138		/// </summary>
139		public Versao vDados;
140		/// <summary>
141		/// Versao do aplicativo
142		/// </summary>
143		public Versao vApp;
144		public string appExt;
145		public string appInt;
146		public string imgApp;
147		public string cssLogin;
148		/// <summary>
149		/// Caminho dos arquivos de atualização da estrutura de dados
150		/// </summary>
151		private string pathEstrutura {
152			get { return String.Format ("{0}estrutura", LogiskApp.GetAppData (), Path.DirectorySeparatorChar); }
153		}
154
155		private string pathDados {
156			get { return String.Format ("{0}dados", LogiskApp.GetAppData (), Path.DirectorySeparatorChar); }
157		}
158
159		private string xfile {
160			get { return String.Format ("{0}appLogisk.xml", LogiskApp.GetAppData (), Path.DirectorySeparatorChar); }
161		}
162		/// <summary>
163		/// Arquivo de licença
164		/// </summary>
165		private XmlDocument xlicInt;
166
167		public LogiskApp ()
168		{
169			//Le versao anterior
170			XmlDocument xdoc = new XmlDocument ();
171			xdoc.PreserveWhitespace = true;
172			xdoc.Load (xfile);
173			Versao vup = new Versao ();
174			vDados = vup.ConvertTo (xdoc.GetElementsByTagName ("versao").Item (0).InnerText);
175			//Le versao do aplicativo
176			Versao vatual = new Versao ();
177			vApp = vatual.Atual ();
178			//Le dados da aplicação
179			nomeApp = xdoc.GetElementsByTagName ("nomeApp").Item (0).InnerText;
180			appExt = xdoc.GetElementsByTagName ("appExt").Item (0).InnerText;
181			appInt = xdoc.GetElementsByTagName ("appInt").Item (0).InnerText;
182			imgApp = "~/" + xdoc.GetElementsByTagName ("imgApp").Item (0).InnerText;
183			cssLogin = "~/" + xdoc.GetElementsByTagName ("cssLogin").Item (0).InnerText;
184			//Seta Valor no web.config
185			/*ConfigurationManager.AppSettings.Set ("appExt", appExt);
186			ConfigurationManager.AppSettings.Set ("appInt", appInt);*/
187		}
188
189		public static string GetAppDataRoot ()
190		{
191			return String.Format ("{0}App_Data{1}", HttpRuntime.AppDomainAppPath, Path.DirectorySeparatorChar);
192		}
193
194		public static string GetAppDataSmallBusiness ()
195		{
196			return String.Format ("{0}App_Data{2}{1}{2}", HttpRuntime.AppDomainAppPath, LogiskUtil.PastaUser, Path.DirectorySeparatorChar);
197		}
198
199		public static string GetAppData ()
200		{
201#if SmallBusiness
202			//if (HttpContext.Current.Session == null || HttpContext.Current.Session ["EMPRESAID"] == null)
203			//	return GetAppDataRoot ();
204			return GetAppDataSmallBusiness ();
205#else
206			return GetAppDataRoot ();
207#endif
208		}
209
210		public static string GetAppLinkData ()
211		{
212#if SmallBusiness
213			return $"../App_Data{Path.DirectorySeparatorChar}{LogiskUtil.PastaUser}{Path.DirectorySeparatorChar}";
214#else
215			return $"../App_Data{Path.DirectorySeparatorChar}";
216#endif
217		}
218
219		private void LeLicenca ()
220		{
221			int emp = LogiskUtil.GetSessionOrCookie ();
222			LeLicenca (emp);
223		}
224
225		private void LeLicenca (int vempresa)
226		{
227			string path = HttpRuntime.AppDomainAppPath;
228			int pos = path.LastIndexOf (appInt);
229			string raizExt;
230			if (pos >= 0)
231				raizExt = path.Substring (0, pos) + appExt;
232			else
233				raizExt = "../" + appExt;
234			string filelic = $"{LogiskApp.GetAppData ()}info{vempresa}.xml";
235			string fileExt = $"{raizExt}{Path.DirectorySeparatorChar}confs{Path.DirectorySeparatorChar}info{vempresa}.xml";
236			if (File.Exists (filelic)) {
237				xlicInt = new XmlDocument ();
238				xlicInt.PreserveWhitespace = true;
239				xlicInt.Load (filelic);
240			} else if (File.Exists (fileExt)) {
241				xlicInt = new XmlDocument ();
242				xlicInt.PreserveWhitespace = true;
243				xlicInt.Load (fileExt);
244			}
245		}
246
247		public bool VerificaAssinatura ()
248		{
249			//Verifica empresa
250			int empresa = LogiskUtil.GetSessionOrCookie ();
251			Empresa emp = Empresa.SelectById<int> (empresa);
252			//Carregar a assinatura
253			DateTime data = new DateTime (2011, 7, 4);
254			TimeSpan span = data - DateTime.Now;
255			if (span.TotalDays > 0)
256				return true;
257			XmlNodeList nodeList = xlicInt.GetElementsByTagName ("Signature");
258			SignedXml signedXml = new SignedXml (xlicInt);
259			signedXml.LoadXml ((XmlElement)nodeList [0]);
260			bool isSigValid = signedXml.CheckSignature ();
261			bool isLicVal = true;
262			if (emp != null) {
263				if (xlicInt.GetElementsByTagName ("CNPJ").Item (0).InnerText.Trim () != emp.CGC_01T.Trim ())
264					isLicVal = false;
265				if (xlicInt.GetElementsByTagName ("IE").Item (0).InnerText.Trim () != emp.INC_ET_01T.Trim ())
266					isLicVal = false;
267			}
268			if (isSigValid && isLicVal) {
269				return true;
270			} else {
271				return false;
272			}
273		}
274
275		public static int TipoContrato {
276			get {
277				List<Empresa> emps = Empresa.Select ();
278				if (emps.Count > 0) {
279					LogiskApp app = new LogiskApp ();
280					app.LeLicenca (emps [0].EMPRESA);
281					if (app.xlicInt == null)
282						return 0;
283					if (app.xlicInt.GetElementsByTagName ("TIPO").Count > 0) {
284						return Convert.ToInt32 (app.xlicInt.GetElementsByTagName ("TIPO").Item (0).InnerText);
285					} else {
286						return 0;
287					}
288				}
289				return 0;
290			}
291		}
292
293		public static int PerfilContrato {
294			get {
295				List<Empresa> emps = Empresa.Select ();
296				if (emps.Count > 0) {
297					LogiskApp app = new LogiskApp ();
298					app.LeLicenca (emps [0].EMPRESA);
299					if (app.xlicInt == null)
300						return 1;
301					if (app.xlicInt.GetElementsByTagName ("PERFIL").Count > 0) {
302						return Convert.ToInt32 (app.xlicInt.GetElementsByTagName ("PERFIL").Item (0).InnerText);
303					}
304				}
305				return 1;
306			}
307		}
308
309		public double Limite (string modulo)
310		{
311			if (modulo == "" || modulo == "opcoes")
312				return 1;
313			LeLicenca ();
314			if (xlicInt != null && xlicInt.GetElementsByTagName (modulo).Count > 0) {
315				DateTime exp = GetDataModulo (modulo);
316				TimeSpan span = exp.Subtract (DateTime.Now);
317				return span.TotalDays;
318			} else
319				return -1;
320		}
321
322		public static int LimiteContrato (string tag) {
323			List<Empresa> emps = Empresa.Select ();
324			if (emps.Count > 0) {
325				LogiskApp app = new LogiskApp ();
326				app.LeLicenca (emps [0].EMPRESA);
327				if (app.xlicInt == null)
328					return 0;
329				if (app.xlicInt.GetElementsByTagName (tag).Count > 0) {
330					return Convert.ToInt32 (app.xlicInt.GetElementsByTagName (tag).Item (0).InnerText);
331				}
332			}
333			return 0;
334		}
335
336		public bool VerificaModulo (string modulo, int empresa)
337		{
338			if(HttpContext.Current.Session != null) {
339				HttpContext.Current.Session ["EMPRESA"] = empresa;
340			}
341		
342			LeLicenca ();
343			bool msg = false;
344			if (xlicInt != null && xlicInt.GetElementsByTagName (modulo).Count > 0) {
345				if (!VerificaAssinatura ())
346					return false;
347				DateTime exp = GetDataModulo (modulo);
348				TimeSpan span = exp.Subtract (DateTime.Now);
349				if (span.TotalDays >= 0)
350					msg = true;
351				else
352					msg = false;
353			}
354			return msg;
355		}
356
357		public bool VerificaModulo (string modulo)
358		{
359			LeLicenca ();
360			bool msg = false;
361			if (xlicInt != null && xlicInt.GetElementsByTagName (modulo).Count > 0) {
362				if (!VerificaAssinatura ())
363					return false;
364				DateTime exp = GetDataModulo (modulo);
365				TimeSpan span = exp.Subtract (DateTime.Now);
366				if (span.TotalDays >= 0)
367					msg = true;
368				else
369					msg = false;
370			}
371			return msg;
372		}
373
374#if SmallBusiness
375		private void ImportaLicenca ()
376		{
377			return;
378		}
379#else
380		private void ImportaLicenca ()
381		{
382			if (!File.Exists (String.Format ("{0}licenca.bin", LogiskApp.GetAppData (), Path.DirectorySeparatorChar)))
383				return;
384			using (LicencaService.LicencaService lic = new LicencaService.LicencaService ()) {
385				int empresa_id = LogiskUtil.GetSessionOrCookie ();
386				Empresa emp = Empresa.SelectById (empresa_id);
387				if (emp.Intervalo () < -1) {
388					string sl = lic.GetXml (emp.CGC_01T, emp.INC_ET_01T);
389					emp.DAT_VL_01T = DateTime.Now;
390					Empresa.Update (emp);
391					if (sl != "" && sl != "E1") {
392						StreamWriter SW;
393						SW = File.CreateText (String.Format ("{0}info{2}.xml", LogiskApp.GetAppData (), Path.DirectorySeparatorChar, emp.EMPRESA));
394						SW.Write (sl);
395						SW.Close ();
396					}
397				}
398			}
399		}
400#endif
401
402		public string Mensagem (string modulo)
403		{
404			LeLicenca ();
405			try {
406				if (xlicInt != null) {
407					DateTime exp = GetDataModulo (modulo);
408					TimeSpan span = exp.Subtract (DateTime.Now);
409					if (span.TotalDays <= 8 && span.TotalDays >= -3) {
410						ImportaLicenca ();
411						LeLicenca ();
412					}
413				} else {
414					ImportaLicenca ();
415					LeLicenca ();
416				}
417			} catch {
418			}
419			//if (!VerificaModulo (modulo))
420			//    return "MODULO NÃO LICENCIADO!";
421			string msg = "";
422			if (xlicInt != null) {
423				DateTime exp = GetDataModulo (modulo);
424				TimeSpan span = exp.Subtract (DateTime.Now);
425				if (!VerificaAssinatura ())
426					return "ARQUIVO DE LICENÇA INVÁLIDO!";
427				if (span.TotalDays <= 7 && span.TotalDays >= 2)
428					msg = String.Format ("SISTEMA EXPIRA EM {0} DIAS", span.Days);
429				else if (span.TotalDays < 2 && span.TotalDays >= 1)
430					msg = String.Format ("SISTEMA EXPIRA EM {0} DIA", span.Days);
431				else if (span.TotalDays >= 0 && span.TotalDays < 1)
432					msg = "SISTEMA EXPIRA HOJE";
433				else if (span.TotalDays < 0)
434					msg = "VERIFICAR LICENÇA DE USO!";
435			} else {
436				msg = "VERIFICAR LICENÇA DE USO!";
437			}
438			return msg;
439		}
440
441		private DateTime GetDataModulo (string modulo)
442		{
443			XmlDocument xmodulo = new XmlDocument ();
444			if (xlicInt.GetElementsByTagName (modulo).Count == 0)
445				return new DateTime (2012, 12, 12);
446			string xmlmod = xlicInt.GetElementsByTagName (modulo).Item (0).OuterXml;
447			xmodulo.LoadXml (xmlmod);
448			DateTime dataexp = Convert.ToDateTime (xmodulo.GetElementsByTagName ("validade").Item (0).InnerText);
449			return dataexp;
450		}
451
452		public static string Versao ()
453		{
454			Versao vs = new Versao ();
455			return vs.Atual ().ToString ();
456		}
457
458		private void AtualizarEstrutura ()
459		{
460			DirectoryInfo dirEstrutura = new DirectoryInfo (pathEstrutura);
461			FileInfo [] filesEstrurua = dirEstrutura.GetFiles ("*.csv");
462			foreach (FileInfo fiE in filesEstrurua) {
463				string ntable = PegarNomeTabela (fiE.Name);
464				if (IsNewVersion (fiE.Name)) {
465					if (TableExists (fiE.Name)) {
466						try {
467							UpTabela (fiE.FullName);
468						} catch (Exception ex) {
469							throw new Exception ("Erro ao atualizar arquivo " + fiE.Name + ". " + ex.Message + "<br/> " + ex.StackTrace);
470						}
471					} else {
472						CreateTable (fiE.FullName);
473					}
474				}
475				if (ntable == "ifsi099t") {
476					CreateTable (fiE.FullName);
477				}
478			}
479		}
480
481		private void ImportarDados ()
482		{
483			DirectoryInfo dirDados = new DirectoryInfo (pathDados);
484			FileInfo [] filesDados = dirDados.GetFiles ("*.csv");
485			foreach (FileInfo fiD in filesDados) {
486				if (IsNewVersion (fiD.Name)) {
487					try {
488						ImportarDados (fiD.FullName);
489					} catch (Exception ex) {
490						throw new Exception ("Erro ao importar dados " + fiD.Name + ". " + ex.Message + "<br/> " + ex.StackTrace);
491					}
492				}
493			}
494		}
495#if !SmallBusiness
496		/// <summary>
497		/// Executa rotina de atualização do Banco de dados a partir dos arquivos de estrutura
498		/// </summary>
499		public void Run ()
500		{
501			if (vApp > vDados) {
502				ExcluirTabelas ();
503				AtualizarEstrutura ();
504				LogiskIndex.RunCheck (this);
505				ImportarDados ();
506				AtualizarDados ();
507				TerminarAtualizacao ();
508			}
509		}
510
511		private void ExcluirTabelas ()
512		{
513			Versao v1 = new Versao ();
514			v1.ConvertTo ("3.0.5967");
515			Versao v2 = new Versao ();
516			v2.ConvertTo ("3.0.6012");
517			if (vDados > v1 && vDados < v2) {
518				using (IDbConnection con = Connection ()) {
519					IDbCommand cmd = con.CreateCommand ();
520					using (cmd) {
521						cmd.CommandText = @"DROP TABLE IF EXISTS ControleExpedicao";
522						con.Open ();
523						cmd.ExecuteNonQuery ();
524						con.Close ();
525					}
526				}
527			}
528		}
529#else
530		public void Run ()
531		{
532
533		}
534#endif
535		private void atualizarCPG ()
536		{
537			//Cria tabela temporária
538			using (IDbConnection con = Connection ()) {
539				IDbCommand cmd = con.CreateCommand ();
540				using (cmd) {
541					cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `tmp05a` (
542					  `EMPRESA` int(11) NOT NULL,
543					  `ID` int(11) NOT NULL AUTO_INCREMENT,
544					  `NFS` varchar(4000) NOT NULL,
545					  `APS` varchar(4000) NOT NULL,
546					  PRIMARY KEY (`EMPRESA`,`ID`)
547					) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";
548					con.Open ();
549					cmd.ExecuteNonQuery ();
550					con.Close ();
551				}
552			}
553			//Insere registros na tabela temporária
554			using (IDbConnection con = Connection ()) {
555				IDbCommand cmd = con.CreateCommand ();
556				using (cmd) {
557					cmd.CommandTimeout = 0;
558					cmd.CommandText = @"INSERT INTO tmp05a SELECT EMPRESA, 0 AS ID, NF, GROUP_CONCAT(CONCAT(EMPRESA, '-', LNC_04A)) AS FIN 
559						FROM (SELECT GROUP_CONCAT(CONCAT(SER_NF_08A,'-', NUM_NF_08A)) AS NF, ifsn004a.* FROM `ifsn004a` GROUP BY `EMPRESA`,`LNC_04A`) AS TAB GROUP BY `EMPRESA_NF`,`SER_NF_08A`,`NUM_NF_08A`
560						";
561					con.Open ();
562					cmd.ExecuteNonQuery ();
563					con.Close ();
564				}
565			}
566			//Insere registros do lanaçamento financeiro
567			using (IDbConnection con = Connection ()) {
568				IDbCommand cmd = con.CreateCommand ();
569				using (cmd) {
570					cmd.CommandTimeout = 0;
571					cmd.CommandText = @"INSERT IGNORE INTO ifsi005a
572						SELECT tmp05a.EMPRESA, tmp05a.ID, '0000-00-00', 0.00, 0.00
573						FROM tmp05a";
574					con.Open ();
575					cmd.ExecuteNonQuery ();
576					con.Close ();
577				}
578			}
579
580			//Executa amarração cpg
581			using (IDbConnection con = Connection ()) {
582				IDbCommand cmd = con.CreateCommand ();
583				using (cmd) {
584					cmd.CommandTimeout = 0;
585					cmd.CommandText = @"UPDATE ifsi004a
586						INNER JOIN (
587						SELECT tab1.EMPRESA, tab1.id, substring_index( substring_index( tab1.APS, ',', tab2.ID ) , ',', -1 ) AS AP
588						FROM tmp05a AS tab1
589						JOIN tmp05a AS tab2 ON tab1.EMPRESA = tab2.EMPRESA
590						AND char_length( tab1.APS ) - char_length( replace( tab1.APS, ',', '' ) ) >= tab2.ID -1
591						) AS tab3 ON tab3.EMPRESA = ifsi004a.EMPRESA
592						AND substring_index(tab3.AP, '-', 1) = ifsi004a.EMPRESA AND substring_index(tab3.AP, '-', -1) = LNC_04A  SET CPG_ID_04A=tab3.ID";
593					con.Open ();
594					cmd.ExecuteNonQuery ();
595					con.Close ();
596				}
597			}
598			//Executa amarração notas
599			using (IDbConnection con = Connection ()) {
600				IDbCommand cmd = con.CreateCommand ();
601				using (cmd) {
602					cmd.CommandTimeout = 0;
603					cmd.CommandText = @"UPDATE ifsi008a 
604						INNER JOIN (
605						SELECT tab1.EMPRESA, tab1.id, substring_index( substring_index( tab1.NFS, ',', tab2.ID
606						) , ',', -1 ) AS NF
607						FROM tmp05a AS tab1
608						JOIN tmp05a AS tab2 ON tab1.EMPRESA=tab2.EMPRESA AND char_length( tab1.NFS ) - char_length( replace( tab1.NFS, ',', '' ) ) >= tab2.ID - 1
609						) AS tab3 ON tab3.EMPRESA=ifsi008a.EMPRESA AND substring_index( tab3.NF, '-', 1 ) = SER_NF_08A AND NUM_NF_08A = substring_index( tab3.NF, '-', -1 )  SET CPG_ID_08A=tab3.id";
610					con.Open ();
611					cmd.ExecuteNonQuery ();
612					con.Close ();
613				}
614			}
615			//Exclui tabela temporária
616			using (IDbConnection con = Connection ()) {
617				IDbCommand cmd = con.CreateCommand ();
618				using (cmd) {
619					cmd.CommandText = @"DROP TABLE tmp05a";
620					con.Open ();
621					cmd.ExecuteNonQuery ();
622					con.Close ();
623				}
624			}
625		}
626
627		private void atualizarCarvao ()
628		{
629			//Migrar essencias
630			using (IDbConnection con = Connection ()) {
631				IDbCommand cmd = con.CreateCommand ();
632				using (cmd) {
633					cmd.CommandText = @"INSERT IGNORE INTO ifsi028t
634						SELECT TIP_CV_01T, DES_CV_01T FROM carv01t
635						";
636					con.Open ();
637					cmd.ExecuteNonQuery ();
638					con.Close ();
639				}
640			}
641			//Migrar guias
642			using (IDbConnection con = Connection ()) {
643				IDbCommand cmd = con.CreateCommand ();
644				using (cmd) {
645					cmd.CommandText = @"INSERT IGNORE INTO ifsi029t
646						SELECT TIP_GU_02T, DES_GU_02T FROM carv02t";
647					con.Open ();
648					cmd.ExecuteNonQuery ();
649					con.Close ();
650				}
651			}
652			//Migrar licencas
653			using (IDbConnection con = Connection ()) {
654				IDbCommand cmd = con.CreateCommand ();
655				using (cmd) {
656					cmd.CommandText = @"INSERT IGNORE INTO ifsi029a
657						SELECT COD_LC_20A, DES_LC_20A, DAT_LC_20A, DAT_VN_20A, TIP_CV_20A, CID_PR_20A, ESS_CV_20A, CON_LC_20A, TIP_LC_20A, QNT_RL_20A, HAB_LC_20A FROM carv20a";
658					con.Open ();
659					cmd.ExecuteNonQuery ();
660					con.Close ();
661				}
662			}
663			//Migrar contratos
664			using (IDbConnection con = Connection ()) {
665				IDbCommand cmd = con.CreateCommand ();
666				using (cmd) {
667					cmd.CommandText = @"INSERT IGNORE INTO ifsi027a
668						SELECT '1' AS EMPRESA, NUM_CT_10A, DES_CT_10A, COD_FR_10A, ' ' AS TIP_CV_10A, ' ' AS COD_LC_10A, DAT_CT_10A, DAT_VN_10A, MTR_TT_10A FROM carv10a";
669					con.Open ();
670					cmd.ExecuteNonQuery ();
671					con.Close ();
672				}
673			}
674			//Migrar movimento
675			using (IDbConnection con = Connection ()) {
676				IDbCommand cmd = con.CreateCommand ();
677				using (cmd) {
678					cmd.CommandText = @"INSERT IGNORE INTO ifsi028a
679						SELECT EMPRESA, SER_NF_01A, NUM_NF_01A, NUM_CC_01A, PRC_NF_01A, TIP_CV_01A, TIP_GU_01A, GUI_NF_01A, DAT_AT_01A, QNT_HR_01A, NFP_AV_01A, DAT_EM_01A, QNT_PR_01A, VAL_NP_01A, BAC_NP_01A, ICM_NP_01A, NUM_TK_01A, VRC_NM_01A, UMD_CV_01A, UMD_NF_01A, MOI_CV_01A, GRA_P1_01A, PES_BR_01A, PES_BL_01A, PES_LQ_01A, QNT_NF_01A, VAL_CV_01A FROM carv01a";
680					con.Open ();
681					cmd.ExecuteNonQuery ();
682					con.Close ();
683				}
684			}
685		}
686
687		private void atualizarCompras ()
688		{
689			//Atualizar relacionamentos da baixa da ordem de compra
690			using (IDbConnection con = Connection ()) {
691				IDbCommand cmd = con.CreateCommand ();
692				using (cmd) {
693					cmd.CommandText = @"UPDATE
694						ifsi018a
695						INNER JOIN ifsi061t ON ifsi018a.EMPRESA = ifsi061t.EMPRESA_NF AND ifsi018a.SER_NF_18A = ifsi061t.SER_NF_18A AND ifsi018a.NUM_NF_18A = ifsi061t.NUM_NF_18A AND ifsi018a.SEQ_IT_18A = ifsi061t.SEQ_IT_18A
696						SET PED_NM_18A = ifsi061t.NUM_OC_61A, PED_IT_18A = ifsi061t.SEQ_IT_61A";
697					con.Open ();
698					cmd.ExecuteNonQuery ();
699					con.Close ();
700				}
701			}
702			//Adiciona indice
703			try {
704				using (IDbConnection con = Connection ()) {
705					IDbCommand cmd = con.CreateCommand ();
706					using (cmd) {
707						cmd.CommandText = @"ALTER TABLE `ifsi018a` ADD INDEX OC( `EMPRESA` , `PED_NM_18A`, `PED_IT_18A` )";
708						con.Open ();
709						cmd.ExecuteNonQuery ();
710						con.Close ();
711					}
712				}
713			} catch {
714			}
715		}
716
717		public bool TesteVersao (string versao)
718		{
719			Versao versao1 = new Versao ();
720			versao1.ConvertTo (versao);
721			if (vDados < versao1 && vApp >= versao1)
722				return true;
723			else
724				return false;
725		}
726
727		private void AtualizarDados ()
728		{
729			if (TesteVersao ("3.0.6621"))
730				atualizarPerfilUsuario ();
731			atualizarEstrutura05a ();
732			if (TesteVersao ("2.0.3612")) {
733				atualizarCarvao ();
734				atualizarCompras ();
735				atualizarCPG ();
736			}
737			atualizarCentroCusto ();
738			atualizarMensagensFiscal ();
739			atualizarFatura ();
740			atualiza_C0N_IN_01B ();
741			atualiza_config_sistema ();
742			atualizaCadTipoDocumento ();
743			atualizarBaixaCPG();
744			atualizarBaixaPagamento();
745			corrigirBaixaCPG();
746		}
747
748		private void atualizaCadTipoDocumento ()
749		{
750			if (TesteVersao ("3.0.7363")) {
751				using (IDbConnection con = Connection ()) {
752					IDbCommand cmd = con.CreateCommand ();
753					using (cmd) {
754						cmd.CommandText = @"UPDATE ifsi006t
755							SET DOC_FS_06T = '03'
756							WHERE DOC_IC_06T = 'N'";
757						con.Open ();
758						cmd.ExecuteNonQuery ();
759						con.Close ();
760					}
761				}
762			}
763		}
764
765		private void atualizarMensagensFiscal()
766		{
767			if (TesteVersao ("3.0.7317")) {
768				using (IDbConnection con = Connection ()) {
769					IDbCommand cmd = con.CreateCommand ();
770					using (cmd) {
771						cmd.CommandText = @"UPDATE ifsi037t
772							SET MS1_NF_37T = REPLACE(CONCAT(
773							IF(MS1_NF_37T != """" , MS1_NF_37T , """"),
774							IF(MS2_NF_37T != """" , CONCAT(""+ \"" \"" +"" , MS2_NF_37T) , """"),
775							IF(MS3_NF_37T != """" , CONCAT(""+ \"" \"" +"" , MS3_NF_37T) , """"),
776							IF(MS4_NF_37T != """" , CONCAT(""+ \"" \"" +"" , MS4_NF_37T) , """"),
777							IF(MS5_NF_37T != """" , CONCAT(""+ \"" \"" +"" , MS5_NF_37T) , """"),
778							IF(MS6_NF_37T != """" , CONCAT(""+ \"" \"" +"" , MS6_NF_37T) , """"),
779							IF(MS7_NF_37T != """" , CONCAT(""+ \"" \"" +"" , MS7_NF_37T) , """"),
780							IF(MS8_NF_37T != """" , CONCAT(""+ \"" \"" +"" , MS8_NF_37T) , """"),
781							IF(MS9_NF_37T != """" , CONCAT(""+ \"" \"" +"" , MS9_NF_37T) , """")
782							),""\""+ \"" \"" +\"""","" ""),
783							MS2_NF_37T = """",
784							MS3_NF_37T = """",
785							MS4_NF_37T = """",
786							MS5_NF_37T = """",
787							MS6_NF_37T = """",
788							MS7_NF_37T = """",
789							MS8_NF_37T = """",
790							MS9_NF_37T = """"
791							WHERE 1";
792						con.Open ();
793						cmd.ExecuteNonQuery ();
794						con.Close ();
795					}
796				}
797			}
798		}
799
800		private void atualizarBaixaCPG ()
801		{
802			if (TesteVersao("3.0.7533")) {
803				using (IDbConnection con = Connection ()) {
804					IDbCommand cmd = con.CreateCommand ();
805					using (cmd) {
806						cmd.CommandText = "UPDATE `ifsi004a` LEFT JOIN `ifsn025a` ON ifsi004a.EMPRESA=ifsn025a.EMPRESA AND ifsi004a.LNC_04A=ifsn025a.LNC_04A SET ifsi004a.COD_BX_04A=ifsn025a.COD_BX_25A";
807						con.Open ();
808						cmd.ExecuteNonQuery ();
809						con.Close ();
810					}
811				}
812			}
813		}
814
815		private void corrigirBaixaCPG ()
816		{
817			if (TesteVersao ("3.0.7584")) {
818				using (IDbConnection con = Connection ()) {
819					IDbCommand cmd = con.CreateCommand ();
820					using (cmd) {
821						cmd.CommandText = "UPDATE `ifsi004a` LEFT JOIN `ifsn025a` ON ifsi004a.EMPRESA=ifsn025a.EMPRESA AND ifsi004a.LNC_04A=ifsn025a.LNC_04A SET ifsi004a.COD_BX_04A=ifsn025a.COD_BX_25A WHERE ifsi004a.COD_BX_04A = 0 AND ifsi004a.VAL_PG_04A <> 0";
822						con.Open ();
823						cmd.ExecuteNonQuery ();
824						con.Close ();
825					}
826				}
827			}
828		}
829
830		private void atualizarBaixaPagamento ()
831		{
832			if (TesteVersao("3.0.7533")) {
833				using (IDbConnection con = Connection ()) {
834					IDbCommand cmd = con.CreateCommand ();
835					using (cmd) {
836						cmd.CommandText = "UPDATE `ifsi025a` LEFT JOIN `ifsn025a` ON ifsi025a.COD_PG_25A=ifsn025a.COD_PG_25A SET ifsi025a.COD_BX_25A=ifsn025a.COD_BX_25A ";
837						con.Open ();
838						cmd.ExecuteNonQuery ();
839						con.Close ();
840					}
841				}
842			}
843		}
844
845		private void atualizarCentroCusto ()
846		{
847			if (TesteVersao("3.0.7273")) {
848				using (IDbConnection con = Connection ()) {
849					IDbCommand cmd = con.CreateCommand ();
850					using (cmd) {
851						cmd.CommandText = "UPDATE ifsi010a SET TIP_AP_10A='E' WHERE MOV_ES_10A='S'";
852						con.Open ();
853						cmd.ExecuteNonQuery ();
854						con.Close ();
855					}
856				}
857			}
858		}
859
860		private void atualizarPerfilUsuario ()
861		{
862			using (IDbConnection con = Connection ()) {
863				IDbCommand cmd = con.CreateCommand ();
864				using (cmd) {
865					cmd.CommandText = "UPDATE ifsi998t SET PER_US_998T='1' WHERE PER_US_998T='0'";
866					con.Open ();
867					cmd.ExecuteNonQuery ();
868					con.Close ();
869				}
870			}
871		}
872
873		private void atualizarEstrutura05a ()
874		{
875			if (TesteVersao ("2.0.3509")) {
876				using (IDbConnection con = Connection ()) {
877					IDbCommand cmd = con.CreateCommand ();
878					using (cmd) {
879						cmd.CommandText = "DROP TABLE IF EXISTS ifsi005a";
880						con.Open ();
881						cmd.ExecuteNonQuery ();
882						con.Close ();
883					}
884				}
885				using (IDbConnection con = Connection ()) {
886					IDbCommand cmd = con.CreateCommand ();
887					using (cmd) {
888						cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `ifsi005a` (
889							  `EMPRESA` int(11) NOT NULL,
890							  `CPG_ID_05A` int(11) NOT NULL AUTO_INCREMENT,
891							  `DAT_LC_05A` date NOT NULL,
892							  `VAL_LC_05A` decimal(10,2) NOT NULL,
893							  `VAL_RT_05A` decimal(10,2) NOT NULL,
894							  PRIMARY KEY (`EMPRESA`,`CPG_ID_05A`)
895							) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Lancamento Financeiro' AUTO_INCREMENT=1";
896						con.Open ();
897						cmd.ExecuteNonQuery ();
898						con.Close ();
899					}
900				}
901			}
902		}
903
904		private void atualizarFatura ()
905		{
906			Logisk.Models.Versao versao = new Models.Versao ();
907			versao.ConvertTo ("3.0.5424");
908			if (vDados < versao && vApp >= versao) {
909				using (IDbConnection con = Connection ()) {
910					IDbCommand cmd = con.CreateCommand ();
911					using (cmd) {
912						cmd.CommandText = "UPDATE ifsi006t INNER JOIN ifsi022t ON ifsi006t.EMPRESA = ifsi022t.EMPRESA SET NOT_FT_06T  = NOT_FT_22T WHERE TIP_SR_06T IN ('0','1')";
913						con.Open ();
914						cmd.ExecuteNonQuery ();
915						con.Close ();
916					}
917				}
918			}	
919		}
920
921		private void atualiza_C0N_IN_01B(){
922			if(TesteVersao ("3.0.5457")){
923				using (IDbConnection con = Connection ()) {
924					IDbCommand cmd = con.CreateCommand ();
925					using (cmd) {
926						cmd.CommandText = "UPDATE ifsi001b SET CON_IN_01B = COD_E1_01B";
927						con.Open ();
928						cmd.ExecuteNonQuery ();
929						con.Close ();
930					}
931				}
932			}
933		}
934
935		private void atualiza_config_sistema(){
936			if(TesteVersao ("3.0.5426")){
937				using (IDbConnection con = Connection ()){
938					IDbCommand cmd = con.CreateCommand ();
939					using (cmd){
940						cmd.CommandText = "INSERT INTO ifsi000a SELECT 1, COL_CL_22T, PCA_PG_22T, TRV_CL_22T, SIT_CL_22T, MAX_FT_22T, NIV_PF_22T, MSK_PF_22T, EMP_CC_22T, DES_PR_22T, 0 FROM ifsi022t WHERE EMPRESA = 1";
941						con.Open ();
942						cmd.ExecuteNonQuery ();
943						con.Close ();
944					}
945				}
946			}
947		}
948
949			/*
950            Logisk.Models.Versao versao4 = new Logisk.Models.Versao ();
951            versao4.ConvertTo ("2.0.2847");
952            Logisk.Models.Versao versao2 = new Logisk.Models.Versao ();
953            versao2.ConvertTo ("2.0.2839");
954            if (vDados > versao1 && vApp >= versao2 && vDados < versao4) {
955                using (IDbConnection con = Connection ()) {
956                    IDbCommand cmd = con.CreateCommand ();
957                    using (cmd) {
958                        cmd.CommandText = @"DELETE ifsi018a FROM ifsi018a 
959                            INNER JOIN ifsi008a ON ifsi008a.EMPRESA=ifsi018a.EMPRESA AND SER_NF_08A=SER_NF_18A AND NUM_NF_08A=NUM_NF_18A 
960                            WHERE NFE_ST_08A RLIKE '[NC]'";
961                        con.Open ();
962                        cmd.ExecuteNonQuery ();
963                        con.Close ();
964                    }
965                }
966                using (IDbConnection con = Connection ()) {
967                    IDbCommand cmd = con.CreateCommand ();
968                    using (cmd) {
969                        cmd.CommandText = @"DELETE ifsi038a FROM ifsi038a 
970                            INNER JOIN ifsi008a ON ifsi008a.EMPRESA=ifsi038a.EMPRESA AND SER_NF_08A=SER_NF_38A AND NUM_NF_08A=NUM_NF_38A 
971                            WHERE NFE_ST_08A RLIKE '[NC]'";
972                        con.Open ();
973                        cmd.ExecuteNonQuery ();
974                        con.Close ();
975                    }
976                }
977                using (IDbConnection con = Connection ()) {
978                    IDbCommand cmd = con.CreateCommand ();
979                    using (cmd) {
980                        cmd.CommandText = @"DELETE ifsi003a FROM ifsi003a
981                            INNER JOIN ifsi008a ON ifsi008a.EMPRESA=ifsi003a.EMPRESA AND SER_NF_08A=SER_NF_03A AND NUM_NF_08A=NUM_NF_03A 
982                            WHERE NFE_ST_08A RLIKE '[NC]'";
983                        con.Open ();
984                        cmd.ExecuteNonQuery ();
985                        con.Close ();
986                    }
987                }
988                using (IDbConnection con = Connection ()) {
989                    IDbCommand cmd = con.CreateCommand ();
990                    using (cmd) {
991                        cmd.CommandText = @"DELETE ifsi035a FROM ifsi035a 
992                            INNER JOIN ifsi008a ON ifsi008a.EMPRESA=ifsi035a.EMPRESA AND SER_NF_08A=SER_NF_35A AND NUM_NF_08A=NUM_NF_35A 
993                            WHERE NFE_ST_08A RLIKE '[NC]'";
994                        con.Open ();
995                        cmd.ExecuteNonQuery ();
996                        con.Close ();
997                    }
998                }
999                using (IDbConnection con = Connection ()) {
1000                    IDbCommand cmd = con.CreateCommand ();
1001                    using (cmd) {
1002                        cmd.CommandText = @"DELETE ifsi014a FROM ifsi014a
1003                            INNER JOIN ifsi008a ON ifsi008a.EMPRESA=ifsi014a.EMPRESA AND SER_NF_08A=SER_FT_14A AND NUM_FT_08A=NUM_FT_14A 
1004                            WHERE NFE_ST_08A RLIKE '[NC]'";
1005                        con.Open ();
1006                        cmd.ExecuteNonQuery ();
1007                        con.Close ();
1008                    }
1009                }
1010                using (IDbConnection con = Connection ()) {
1011                    IDbCommand cmd = con.CreateCommand ();
1012                    using (cmd) {
1013                        cmd.CommandText = @"DELETE ifsi015a FROM ifsi015a
1014                            INNER JOIN ifsi008a ON ifsi008a.EMPRESA=ifsi015a.EMPRESA AND SER_NF_08A=SER_FT_15A AND NUM_FT_08A=NUM_FT_15A 
1015                            WHERE NFE_ST_08A RLIKE '[NC]'";
1016                        con.Open ();
1017                        cmd.ExecuteNonQuery ();
1018                        con.Close ();
1019                    }
1020                }
1021            }
1022            Logisk.Models.Versao versao3 = new Logisk.Models.Versao ();
1023            versao3.ConvertTo ("2.0.2846");
1024            if (vDados > versao1 && vApp >= versao3 && vDados < versao3) {
1025                using (IDbConnection con = Connection ()) {
1026                    IDbCommand cmd = con.CreateCommand ();
1027                    using (cmd) {
1028                        cmd.CommandText = @"UPDATE ifsi018a SET BAC_IC_18A=0 WHERE VAL_IC_18A=0 AND ALQ_IC_18A=0 AND BAC_IC_18A > 0";
1029                        con.Open ();
1030                        cmd.ExecuteNonQuery ();
1031                        con.Close ();
1032                    }
1033                }
1034                using (IDbConnection con = Connection ()) {
1035                    IDbCommand cmd = con.CreateCommand ();
1036                    using (cmd) {
1037                        cmd.CommandText = @"UPDATE ifsi008a SET BAC_IC_08A=0 WHERE VAL_IC_08A=0 AND BAC_IC_08A > 0";
1038                        con.Open ();
1039                        cmd.ExecuteNonQuery ();
1040                        con.Close ();
1041                    }
1042                }
1043                using (IDbConnection con = Connection ()) {
1044                    IDbCommand cmd = con.CreateCommand ();
1045                    using (cmd) {
1046                        cmd.CommandText = @"UPDATE ifsi038a SET VAL_OT_38A=BAC_LC_38A WHERE ALQ_IM_38A=0 AND VAL_IM_38A=0 AND TIP_IM_38A='1' AND SUB_CD_38A!='040' AND BAC_LC_38A > 0 AND VAL_OT_38A=0";
1047                        con.Open ();
1048                        cmd.ExecuteNonQuery ();
1049                        con.Close ();
1050                    }
1051                }
1052                using (IDbConnection con = Connection ()) {
1053                    IDbCommand cmd = con.CreateCommand ();
1054                    using (cmd) {
1055                        cmd.CommandText = @"UPDATE ifsi038a SET VAL_IN_38A=BAC_LC_38A WHERE ALQ_IM_38A=0 AND VAL_IM_38A=0 AND TIP_IM_38A='1' AND SUB_CD_38A='040' AND BAC_LC_38A > 0 AND VAL_IN_38A=0";
1056                        con.Open ();
1057                        cmd.ExecuteNonQuery ();
1058                        con.Close ();
1059                    }
1060                }
1061                using (IDbConnection con = Connection ()) {
1062                    IDbCommand cmd = con.CreateCommand ();
1063                    using (cmd) {
1064                        cmd.CommandText = @"UPDATE ifsi038a SET BAC_LC_38A=0 WHERE ALQ_IM_38A=0 AND VAL_IM_38A=0 AND TIP_IM_38A='1' AND BAC_LC_38A > 0 AND (BAC_LC_38A=VAL_IN_38A OR BAC_LC_38A=VAL_OT_38A)";
1065                        con.Open ();
1066                        cmd.ExecuteNonQuery ();
1067                        con.Close ();
1068                    }
1069                }
1070            }*/
1071		
1072
1073		/// <summary>
1074		/// Grava a versao no arquivo appLogisk.xml
1075		/// </summary>
1076		private void TerminarAtualizacao ()
1077		{
1078			//Le arquivo
1079			XmlDocument xdoc = new XmlDocument ();
1080			xdoc.Load (xfile);
1081			xdoc.PreserveWhitespace = true;
1082			Versao vup = new Versao ();
1083			//Escreve a ultima versao no arquivo em que houve atualização
1084			xdoc.GetElementsByTagName ("versao").Item (0).InnerText = vup.Atual ().ToString ();
1085			xdoc.Save (xfile);
1086			//Grava log de atualização
1087			string texto = "Atualizada versão do sistema da " + vDados.ToString () + " para " + vApp.ToString ();
1088			string data = DateTime.Now.ToString ("yyyy-MM-dd");
1089			string hora = DateTime.Now.ToString ("T");
1090			if (texto.Length > 0) {
1091				IDbConnection con = Connection ();
1092				using (con) {
1093					IDbCommand cmd = con.CreateCommand ();
1094					using (cmd) {
1095						cmd.CommandText = String.Format ("insert into ifsi099t (NOM_US_99T, DAT_US_99T, ACA_US_99T, HOR_US_99T) values ('{0}', '{1}', '{2}', '{3}')", "Sistema", data, texto.Replace (@"\", @"\\").Replace ("'", @"\'"), hora);
1096						con.Open ();
1097						cmd.ExecuteNonQuery ();
1098						con.Close ();
1099					}
1100				}
1101			}
1102			//Atualiza versao no servidor
1103			if (File.Exists (String.Format ("{0}licenca.bin", LogiskApp.GetAppData (), Path.DirectorySeparatorChar))) {
1104				using (LicencaService.LicencaService ls = new Logisk.LicencaService.LicencaService ()) {
1105					List<Empresa> contratante = Empresa.SelectEmpresaContratante ();
1106					int num = 1;
1107					if (contratante != null && contratante.Count == 1)
1108						num = contratante [0].EMPRESA;
1109					Empresa emp = Empresa.SelectById (num);
1110					ls.InformaVersao (emp.CGC_01T, emp.INC_ET_01T, vup.Atual ().ToString ());
1111				}
1112			}
1113		}
1114
1115		/// <summary>
1116		/// Cria uma tabela no banco de dados a partir de um arquivo de uma determinada versao
1117		/// </summary>
1118		/// <param name="file">
1119		/// A <see cref="System.String"/>
1120		/// </param>
1121		private void CreateTable (string file)
1122		{
1123			LogiskTable ntable = new LogiskTable (file);
1124			ntable.Create ();
1125		}
1126
1127		/// <summary>
1128		/// Atualiza uma tabela no banco de dados a partir de um arquivo de uma determinada versão
1129		/// </summary>
1130		/// <param name="file">
1131		/// A <see cref="System.String"/>
1132		/// </param>
1133		private void UpTabela (string file)
1134		{
1135			LogiskTable ntable = new LogiskTable (file);
1136			ntable.CheckColumns ();
1137			ntable.CheckIndex ();
1138		}
1139
1140		private static Versao GetVersionFromCsv (string file)
1141		{
1142			int vini = file.IndexOf ("-v") + 2;
1143			int vend = file.IndexOf (".csv");
1144			string sversao = file.Substring (vini, vend - vini);
1145			Versao vfile = new Versao ();
1146			vfile.ConvertTo (sversao);
1147			return vfile;
1148		}
1149
1150		/// <summary>
1151		/// Verifica de a versao do arquivo de estrutura é maior que a versao do aplicativo
1152		/// </summary>
1153		/// <param name="file">
1154		/// A <see cref="System.String"/>
1155		/// </param>
1156		/// <returns>
1157		/// A <see cref="System.Boolean"/>
1158		/// </returns>
1159		private bool IsNewVersion (string file)
1160		{
1161			Versao vfile = GetVersionFromCsv (file);
1162			if (vfile > vDados && vfile <= vApp) {
1163				return true;
1164			} else {
1165				return false;
1166			}
1167		}
1168
1169		/// <summary>
1170		/// Verifica se uma tabela exite no banco de dados
1171		/// </summary>
1172		/// <param name="file">
1173		/// A <see cref="System.String"/>
1174		/// </param>
1175		/// <returns>
1176		/// A <see cref="System.Boolean"/>
1177		/// </returns>
1178		private bool TableExists (string file)
1179		{
1180			Persistencia.Conexao c = new Persistencia.Conexao ();
1181			string stabela = PegarNomeTabela (file);
1182			IDbConnection con = Connection ();
1183			IDbCommand cmd = con.CreateCommand ();
1184			cmd.CommandText = String.Format ("SHOW TABLES FROM {0} WHERE Tables_in_{0} = '{1}'", c.DataBase, stabela);
1185			bool result = false;
1186			using (con) {
1187				con.Open ();
1188				IDataReader reader = cmd.ExecuteReader ();
1189				if (reader.Read ())
1190					result = true;
1191				con.Close ();
1192			}
1193			return result;
1194		}
1195
1196		private static string PegarNomeTabela (string file)
1197		{
1198			int vini = file.LastIndexOf (Path.DirectorySeparatorChar) + 1;
1199			int vend = file.IndexOf ("-v");
1200			string stabela = file.Substring (vini, vend - vini);
1201			return stabela;
1202		}
1203
1204		//public string tabela = String.Empty;
1205		private string CsvAtual (string tabela)
1206		{
1207			DirectoryInfo dir = new DirectoryInfo (pathEstrutura);
1208			FileInfo [] filesDados = dir.GetFiles ("*.csv");
1209			Versao maior = new Versao ();
1210			Versao atual = new Versao ();
1211			maior.ConvertTo ("1.0.000");
1212			FileInfo ptb = null;
1213			foreach (FileInfo fiD in filesDados) {
1214				if (fiD.Name.IndexOf (tabela) >= 0) {
1215					atual = GetVersionFromCsv (fiD.Name);
1216					if (atual > maior)
1217						ptb = fiD;
1218				}
1219			}
1220
1221			if (ptb != null)
1222				return ptb.FullName;
1223			else
1224				return "";
1225		}
1226
1227		private int NColCsv (string field, string scsv)
1228		{
1229			LogiskTable ltabela = new LogiskTable (scsv);
1230			return ltabela.NCsvCol (field);
1231		}
1232
1233		/// <summary>
1234		/// Cria um data table a partir de um arquivo de importacao
1235		/// </summary>
1236		/// <param name="tabela">
1237		/// A <see cref="System.String"/>
1238		/// </param>
1239		/// <returns>
1240		/// A <see cref="DataTable"/>
1241		/// </returns>
1242		private DataTable TabelaDados (string tabela)
1243		{
1244			DataTable table = new DataTable ();
1245			table.Columns.Clear ();
1246			table.Clear ();
1247			List<Column> read = new List<Column> ();
1248			read.Clear ();
1249			read = Logisk.Models.LogiskApp.Column.ListaColunas (tabela);
1250			foreach (Column row in read) {
1251				DataColumn column = new DataColumn ();
1252				string tipo = row.Type;
1253				bool t1 = tipo.Contains ("int");
1254				bool t2 = tipo.Contains ("varchar");
1255				bool t3 = tipo.Contains ("double");
1256				bool t4 = tipo.Contains ("data");
1257				bool t5 = tipo.Contains ("text");
1258				if (t1 == true)
1259					column.DataType = System.Type.GetType ("System.String");
1260				if (t2 == true)
1261					column.DataType = System.Type.GetType ("System.String");
1262				if (t3 == true)
1263					column.DataType = System.Type.GetType ("System.String");
1264				if (t4 == true)
1265					column.DataType = System.Type.GetType ("System.String");
1266				if (t5 == true)
1267					column.DataType = System.Type.GetType ("System.String");
1268				column.ColumnName = row.Field;
1269				if (!table.Columns.Contains (column.ColumnName))
1270					table.Columns.Add (column);
1271			}
1272			return table;
1273		}
1274
1275		public int contador = 0;
1276		public int gravados = 0;
1277		public int empresaerrada = 0;
1278		public List<String> linhaerro = new List<String> ();
1279
1280		private void Truncate (string tabela)
1281		{
1282			IDbConnection con = Connection ();
1283			IDbCommand cmd = con.CreateCommand ();
1284			cmd.CommandText = String.Format ("TRUNCATE " + tabela);
1285			ExecutaQuery (con, cmd);
1286		}
1287
1288		public void Importar (string local)
1289		{
1290			//Pega o nome da tabela a ser atualizada
1291			int inicio = local.LastIndexOf (Path.DirectorySeparatorChar) + 1;
1292			int fim = local.LastIndexOf ('-');
1293			int tamanho = fim - inicio;
1294			tabela = local.Substring (inicio, tamanho);
1295			DataTable dt = new DataTable ();
1296			string line = String.Empty;
1297			int count = 0;
1298			int incluidos = 0;
1299			//List<Unidades> unidades = Unidades.Select();
1300			StreamReader file = new StreamReader (local);
1301			dt = TabelaDados (tabela);
1302			DataRow linha = null;
1303			linhaerro.Clear ();
1304			List<int> k = new List<int> ();
1305			string csv_atual = CsvAtual (tabela);
1306			while ((line = file.ReadLine ()) != null) {
1307				linha = dt.NewRow ();
1308				if (count == 0) {
1309					if (line.IndexOf ("TRUNCATE_" + tabela) != -1)
1310						Truncate (tabela);
1311				} else {
1312					RemoverAcentos (line);
1313					GetStringNoAccents (line);
1314					bool vazio = false;
1315					string [] col = line.Split (new Char [] { ';' });
1316					for (int j = 0; j < col.Length; j++) {
1317						if (count == 1) {
1318							string ncolatual = Column.NCol (j, tabela);
1319							int ncol = NColCsv (ncolatual, csv_atual);
1320							if (ncol != -1)
1321								k.Add (ncol);
1322						}
1323						if (k [j] >= 0) {
1324							linha [j] = col [k [j]];
1325							string valor = linha.ItemArray [j].ToString ();
1326							string emp = linha.ItemArray [0].ToString ();
1327							int numerolinha = count + 1;
1328							if (valor == "" || emp == "0") {
1329								vazio = true;
1330								if (!linhaerro.Contains (numerolinha.ToString ()))
1331									linhaerro.Add (numerolinha.ToString ());
1332							}
1333						}
1334					}
1335					if (vazio == false) {
1336						dt.Rows.Add (linha);
1337						incluidos++;
1338					}
1339				}
1340				count++;
1341			}
1342			contador = count - 1;
1343			gravados = incluidos;
1344			//Grava ou atualiza na tabela
1345			if (incluidos > 0) {
1346				IDbConnection con = LogiskDB.Connection ();
1347				IDbCommand cmd = con.CreateCommand ();
1348				cmd.CommandText = LogiskDB.Replace (dt, tabela);
1349				ExecutaQuery (con, cmd);
1350				con.Dispose ();
1351			}
1352		}
1353
1354		public void ImportarDados (string local)
1355		{
1356			//Pega o nome da tabela a ser atualizada
1357			int inicio = local.LastIndexOf (Path.DirectorySeparatorChar) + 1;
1358			int fim = local.LastIndexOf ('-');
1359			int tamanho = fim - inicio;
1360			tabela = local.Substring (inicio, tamanho);
1361			DataTable dt = new DataTable ();
1362			string line = String.Empty;
1363			int count = 0;
1364			int incluidos = 0;
1365			StreamReader file = new StreamReader (local);
1366			dt = TabelaDados (tabela);
1367			DataRow linha = null;
1368			linhaerro.Clear ();
1369			List<int> k = new List<int> ();
1370			string csv_atual = CsvAtual (tabela);
1371			while ((line = file.ReadLine ()) != null) {
1372				linha = dt.NewRow ();
1373				if (count == 0) {
1374					if (line.IndexOf ("TRUNCATE_" + tabela) != -1)
1375						Truncate (tabela);
1376				} else {
1377					RemoverAcentos (line);
1378					GetStringNoAccents (line);
1379					//bool vazio = false;
1380					string [] col = line.Split (new Char [] { ';' });
1381					for (int j = 0; j < col.Length; j++) {
1382						if (csv_atual != "") {
1383							if (count == 1) {
1384								string ncolatual = Column.NCol (j, tabela);
1385								k.Add (NColCsv (ncolatual, csv_atual));
1386							}
1387						} else {
1388							if (count == 1) {
1389								k.Add (j);
1390							}
1391						}
1392						if (k [j] >= 0) {
1393							linha [j] = col [k [j]];
1394							/*string valor = linha.ItemArray[j].ToString();
1395							string emp = linha.ItemArray[0].ToString();
1396							int numerolinha = count + 1;
1397							if (valor == "" || emp == "0")
1398							{
1399								vazio = true;
1400								if (!linhaerro.Contains(numerolinha.ToString()))
1401									linhaerro.Add(numerolinha.ToString());
1402							}*/
1403						}
1404					}
1405					dt.Rows.Add (linha);
1406					incluidos++;
1407				}
1408				count++;
1409			}
1410			contador = count - 1;
1411			gravados = incluidos;
1412			//Grava ou atualiza na tabela
1413			if (incluidos > 0) {
1414				IDbConnection con = LogiskDB.Connection ();
1415				IDbCommand cmd = con.CreateCommand ();
1416				cmd.CommandText = LogiskDB.Replace (dt, tabela);
1417				ExecutaQuery (con, cmd);
1418				con.Dispose ();
1419			}
1420		}
1421
1422		public static string GetStringNoAccents (string str)
1423		{
1424			string [] caracteresEspeciais = {
1425				"\\.",
1426				",",
1427				"-",
1428				":",
1429				"\\(",
1430				"\\)",
1431				"ª",
1432				"\\|",
1433				"\\\\",
1434				"°",
1435				"'"
1436			};
1437			for (int i = 0; i < caracteresEspeciais.Length; i++) {
1438				str = str.Replace (caracteresEspeciais [i], "");
1439			}
1440			// Troca os espaços no início por "" 
1441			str = str.Replace ("^\\s+", "");
1442			// Troca os espaços no início por "" 
1443			str = str.Replace ("\\s+$", "");
1444			// Troca os espaços duplicados, tabulações e etc por  " " 
1445			str = str.Replace ("\\s+", " ");
1446
1447			return str;
1448
1449		}
1450
1451		private string RemoverAcentos (string texto)
1452		{
1453			texto = texto.ToUpper ();
1454			string s = texto.Normalize (NormalizationForm.FormKD);
1455			StringBuilder sb = new StringBuilder ();
1456
1457			for (int k = 0; k < s.Length; k++) {
1458				UnicodeCategory uc = CharUnicodeInfo.GetUnicodeCategory (s [k]);
1459				if (uc == UnicodeCategory.SpaceSeparator || uc == UnicodeCategory.UppercaseLetter || uc == UnicodeCategory.DecimalDigitNumber) {
1460					sb.Append (s [k]);
1461				}
1462			}
1463			return sb.ToString ().ToUpper ();
1464		}
1465
1466		public class Column : LogiskDB
1467		{
1468			private static List<Column> colunas = new List<Column> ();
1469
1470			/// <summary>
1471			/// Nome do Campo
1472			/// </summary>
1473			public string Field {
1474				get { return args ["field"].ToString (); }
1475				set { args ["field"] = value; }
1476			}
1477
1478			/// <summary>
1479			/// Tipo do Campo
1480			/// </summary>
1481			public string Type {
1482				get { return args ["type"].ToString (); }
1483				set { args ["type"] = value; }
1484			}
1485
1486			public Column ()
1487			{
1488
1489			}
1490
1491			public Column (string [] coluna)
1492			{
1493				Field = coluna [0].Trim ();
1494				Type = coluna [1].Trim ();
1495			}
1496
1497			public Column (IDataReader reader) : this ()
1498			{
1499				ReadData (reader);
1500			}
1501
1502			public static string NCol (int i, string tabela)
1503			{
1504				List<Column> cols = ListaColunas (tabela);
1505				return cols [i].Field;
1506			}
1507
1508			public static List<Column> ListaColunas (string tabela)
1509			{
1510				IDbConnection con = Connection ();
1511				IDbCommand cmd = con.CreateCommand ();
1512				cmd.CommandText = String.Format ("show columns from " + tabela + "");
1513				colunas.Clear ();
1514				using (con) {
1515					con.Open ();
1516					IDataReader reader = cmd.ExecuteReader ();
1517					while (reader.Read ()) {
1518						colunas.Add (new Column (reader));
1519					}
1520					con.Close ();
1521				}
1522				return colunas;
1523			}
1524		}
1525	}
1526}