using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.IO.Compression;
using System.Net;
using System.Windows.Forms;
using System.Xml;
namespace CatalogueOnlineClient
{
public partial class MajAuto : Form
{
Parameters p;
SqlConnection cnx;
SocInfo[] s;
public MajAuto(Parameters p, SocInfo[] s, SqlConnection cnx)
{
InitializeComponent();
this.p = p;
this.s = s;
this.cnx = cnx;
SqlCommand cmd = cnx.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete soc";
cmd.ExecuteNonQuery();
cmd.CommandText = "delete lan";
cmd.ExecuteNonQuery();
SqlParameter pCodsoc = cmd.CreateParameter();
pCodsoc.Direction = ParameterDirection.Input;
pCodsoc.ParameterName = "codsoc";
pCodsoc.Size = 18;
pCodsoc.SqlDbType = SqlDbType.Decimal;
SqlParameter pLibsoc = cmd.CreateParameter();
pLibsoc.Direction = ParameterDirection.Input;
pLibsoc.ParameterName = "libsoc";
pLibsoc.Size = 50;
pLibsoc.SqlDbType = SqlDbType.VarChar;
SqlParameter pCodlan = cmd.CreateParameter();
pCodlan.Direction = ParameterDirection.Input;
pCodlan.ParameterName = "codlan";
pCodlan.Size = 3;
pCodlan.SqlDbType = SqlDbType.VarChar;
SqlParameter pLiblan = cmd.CreateParameter();
pLiblan.Direction = ParameterDirection.Input;
pLiblan.ParameterName = "liblan";
pLiblan.Size = 50;
pLiblan.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pCodsoc);
for (int i = 0, cpti = s.Length; i < cpti; i++)
{
cmd.Parameters.Add(pLibsoc);
cmd.CommandText = "insert into soc (codsoc, libsoc) values (@codsoc, @libsoc)";
pCodsoc.Value = s[i].codsoc;
pLibsoc.Value = s[i].libsoc;
cmd.ExecuteNonQuery();
cmd.Parameters.Remove(pLibsoc);
cmd.Parameters.Add(pCodlan);
cmd.Parameters.Add(pLiblan);
cmd.CommandText = "insert into lan (codsoc, codlan, liblan) values (@codsoc, @codlan, @liblan)";
for (int j = 0, cptj = s[i].laninfo.Length; j < cptj; j++)
{
pCodlan.Value = s[i].laninfo[j].codlan;
pLiblan.Value = s[i].laninfo[j].liblan;
cmd.ExecuteNonQuery();
}
cmd.Parameters.Remove(pCodlan);
cmd.Parameters.Remove(pLiblan);
}
}
private void MajAuto_Load(object sender, EventArgs e)
{
this.Visible = true;
this.Refresh();
string lastUpdate = p["LAST"];
WebClient wc =
new WebClient
();
XmlDocument dom =
new XmlDocument
();
dom.LoadXml(wc.DownloadString(string.Format("http://192.168.0.2/test.php?deb={0}", lastUpdate)));
progressBar1.Maximum = dom.DocumentElement.ChildNodes.Count - 1;
progressBar2.Maximum = dom.DocumentElement.ChildNodes.Count - 1;
foreach (XmlElement el in dom.DocumentElement.ChildNodes)
{
wc.DownloadFile(string.Format("http://192.168.0.2/{0}", el.InnerText), string.Format("./in/{0}", el.InnerText));
progressBar1.PerformStep();
}
foreach (string file in Directory.GetFiles("in", "*.gz" ))
{
// Décompresser le gz dans un ms, puis passer directement dans un ds
FileStream fs =
new FileStream
(file, FileMode.
Open, FileAccess.
Read, FileShare.
Read);
MemoryStream ms =
new MemoryStream
();
GZipStream gz =
new GZipStream
(fs, CompressionMode.
Decompress,
false);
byte[] buffer =
new byte[4096];
while (true)
{
int read_bytes = gz.Read(buffer, 0, buffer.Length);
if (read_bytes == 0)
{
break;
}
ms.Write(buffer, 0, read_bytes);
}
gz.Close();
fs.Close();
File.Delete(file);
DataSet ds =
new DataSet
();
ms.Position = 0;
ds.ReadXml(ms, XmlReadMode.ReadSchema);
string dir = file.Substring(0, file.LastIndexOf('.'));
string tmpCodsoc = file.Substring(file.LastIndexOf("\\SOC" ) + 4);
decimal codsoc = decimal.Parse(tmpCodsoc.Substring(0, tmpCodsoc.IndexOf('_')));
foreach (DataTable dt in ds.Tables)
{
switch (dt.TableName)
{
// Données de base
case "fam":
UpdateFam(codsoc, dt);
break;
case "cri":
UpdateCri(codsoc, dt);
// Libellés
case "lblfam":
UpdateLblFam(codsoc, dt);
break;
case "lblcri":
UpdateLblCri(codsoc, dt);
// Ca chie des bulles
default:
throw new Exception
(string.
Format("Table \"{0}\" non reconnue dans le fichier \"{1}\" !", dt.
TableName, file
)) break;
}
}
progressBar2.PerformStep();
}
this.Close();
}
#region Données de base
private void UpdateFam(decimal codsoc, DataTable dt)
{
SqlCommand cmd = cnx.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete fam where codsoc = @codsoc";
SqlParameter pCodsoc = cmd.CreateParameter();
pCodsoc.Direction = ParameterDirection.Input;
pCodsoc.ParameterName = "codsoc";
pCodsoc.Size = 18;
pCodsoc.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pCodsoc);
pCodsoc.Value = codsoc;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into fam (codsoc, codfam) values (@codsoc, @codfam)";
SqlParameter pCodfam = cmd.CreateParameter();
pCodfam.Direction = ParameterDirection.Input;
pCodfam.ParameterName = "codfam";
pCodfam.Size = 6;
pCodfam.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pCodfam);
foreach (DataRow dr in dt.Rows)
{
pCodfam.Value = (string)dr["CODFAM"];
cmd.ExecuteNonQuery();
}
}
private void UpdateCri(decimal codsoc, DataTable dt)
{
SqlCommand cmd = cnx.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete cri where codsoc = @codsoc";
SqlParameter pCodsoc = cmd.CreateParameter();
pCodsoc.Direction = ParameterDirection.Input;
pCodsoc.ParameterName = "codsoc";
pCodsoc.Size = 18;
pCodsoc.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pCodsoc);
pCodsoc.Value = codsoc;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into cri (codsoc, codcri) values (@codsoc, @codcri)";
SqlParameter pCodfam = cmd.CreateParameter();
pCodfam.Direction = ParameterDirection.Input;
pCodfam.ParameterName = "codcri";
pCodfam.Size = 6;
pCodfam.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pCodfam);
foreach (DataRow dr in dt.Rows)
{
pCodfam.Value = (string)dr["CODCRI"];
cmd.ExecuteNonQuery();
}
}
#endregion
#region Libellés
private void UpdateLblFam(decimal codsoc, DataTable dt)
{
SqlCommand cmd = cnx.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete lbl where codsoc = @codsoc and codtbl = 'fam'";
SqlParameter pCodsoc = cmd.CreateParameter();
pCodsoc.Direction = ParameterDirection.Input;
pCodsoc.ParameterName = "codsoc";
pCodsoc.Size = 18;
pCodsoc.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pCodsoc);
pCodsoc.Value = codsoc;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into lbl (codsoc, codtbl, sigti1, sigti2, codlan, label) values (@codsoc, 'fam', @codfam, '', @codlan, @label)";
SqlParameter pCodfam = cmd.CreateParameter();
pCodfam.Direction = ParameterDirection.Input;
pCodfam.ParameterName = "codfam";
pCodfam.Size = 6;
pCodfam.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pCodfam);
SqlParameter pCodlan = cmd.CreateParameter();
pCodlan.Direction = ParameterDirection.Input;
pCodlan.ParameterName = "codlan";
pCodlan.Size = 3;
pCodlan.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pCodlan);
SqlParameter pLabel = cmd.CreateParameter();
pLabel.Direction = ParameterDirection.Input;
pLabel.ParameterName = "label";
pLabel.Size = 50;
pLabel.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pLabel);
foreach (DataRow dr in dt.Rows)
{
pCodfam.Value = (string)dr["CODFAM"];
pCodlan.Value = (string)dr["CODLAN"];
pLabel.Value = (string)dr["LIBFAM"];
cmd.ExecuteNonQuery();
}
}
private void UpdateLblCri(decimal codsoc, DataTable dt)
{
SqlCommand cmd = cnx.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete lbl where codsoc = @codsoc and codtbl = 'cri'";
SqlParameter pCodsoc = cmd.CreateParameter();
pCodsoc.Direction = ParameterDirection.Input;
pCodsoc.ParameterName = "codsoc";
pCodsoc.Size = 18;
pCodsoc.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pCodsoc);
pCodsoc.Value = codsoc;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into cri (codsoc, codtbl, sigti1, sigti2, codlan, label) values (@codsoc, 'cri', @codcri, '', @codlan, @label)";
SqlParameter pCodfam = cmd.CreateParameter();
pCodfam.Direction = ParameterDirection.Input;
pCodfam.ParameterName = "codcri";
pCodfam.Size = 6;
pCodfam.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pCodfam);
SqlParameter pCodlan = cmd.CreateParameter();
pCodlan.Direction = ParameterDirection.Input;
pCodlan.ParameterName = "codlan";
pCodlan.Size = 3;
pCodlan.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pCodlan);
SqlParameter pLabel = cmd.CreateParameter();
pLabel.Direction = ParameterDirection.Input;
pLabel.ParameterName = "label";
pLabel.Size = 50;
pLabel.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(pLabel);
foreach (DataRow dr in dt.Rows)
{
pCodfam.Value = (string)dr["CODCRI"];
pCodlan.Value = (string)dr["CODLAN"];
pLabel.Value = (string)dr["LIBCRI"];
cmd.ExecuteNonQuery();
}
}
#endregion
}
}