public PICDBCOM(Undersoft.Picatch.Agent.Main form)
{
InitializeComponent();
string connectionString;
// string fileName = "Baza.sdf";
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT * FROM opcje WHERE id = 1";
cmd2.Prepare();
SqlCeDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
// ip_t.Text = dr.GetString(3);
// ufile_t.Text = dr.GetString(4);
// dfile_t.Text = dr.GetString(5);
//bflag = dr.GetBoolean(7);
//ipflag = dr.GetBoolean(8);
serverpcm = dr.GetString(11);
loginpcm = dr.GetString(12);
passwdpcm = dr.GetString(13);
bazapcm = dr.GetString(14);
//skaner_t.Text = dr.GetString(10);
datazmian = dr.GetDateTime(16);
}
cn.Close();
bw.WorkerReportsProgress = true;
bw.WorkerSupportsCancellation = true;
main = form;
bw.DoWork += (bwSender, bwArg) =>
{
//what happens here must not touch the form
//as it's in a different thread
// downloaddata(main);
updatedata(main);
};
bw.RunWorkerAsync();
notifyIcon1.ContextMenuStrip = contextMenuStrip1;
this.otwórzToolStripMenuItem.Click += otwórzToolStripMenuItem_Click;
this.zakończToolStripMenuItem.Click += zakończToolStripMenuItem_Click;
}
public PICTCPAGENT(Undersoft.Picatch.Agent.Main form)
{
InitializeComponent();
string connectionString;
// string fileName = "Baza.sdf";
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT * FROM opcje WHERE id = 1";
cmd2.Prepare();
SqlCeDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
// ip_t.Text = dr.GetString(3);
// ufile_t.Text = dr.GetString(4);
// dfile_t.Text = dr.GetString(5);
//bflag = dr.GetBoolean(7);
//ipflag = dr.GetBoolean(8);
porttcp = dr.GetInt32(9);
//skaner_t.Text = dr.GetString(10);
}
cn.Close();
main = form;
bw.DoWork += (bwSender, bwArg) =>
{
//what happens here must not touch the form
//as it's in a different thread
ReceiveTCP(main);
};
bw.RunWorkerAsync();
notifyIcon1.ContextMenuStrip = contextMenuStrip1;
this.otwórzToolStripMenuItem.Click += otwórzToolStripMenuItem_Click;
this.zakończToolStripMenuItem.Click += zakończToolStripMenuItem_Click;
}
public void ReceiveTCP(Undersoft.Picatch.Agent.Main mainf)
{
System.Globalization.NumberFormatInfo nfi = new System.Globalization.NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
try
{
Listener = new TcpListener(IPAddress.Any, porttcp);
Listener.Start();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
byte[] RecData = new byte[1024];
int RecBytes;
for ( ; ;)
{
if (bw.CancellationPending != true)
{
TcpClient client = null;
NetworkStream netstream = null;
string Status = string.Empty;
bool czyistnieje = false;
try
{
if (!Listener.Pending())
{
}
else
{
client = Listener.AcceptTcpClient();
netstream = client.GetStream();
Status = "Klient połączony\n";
byte[] sendorec = new byte[1];
sendorec[0] = 0;
netstream.Read(sendorec, 0, 1);
if (sendorec[0] == 1)
{
int totalrecbytes = 0;
FileStream Fs = new FileStream("inwent.imp", FileMode.Create, FileAccess.Write);
while ((RecBytes = netstream.Read(RecData, 0, RecData.Length)) > 0)
{
Fs.Write(RecData, 0, RecBytes);
totalrecbytes += RecBytes;
}
Fs.Close();
netstream.Close();
client.Close();
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
string delimeter = ";";
string filename = "inwent.imp";
StreamReader sr = new StreamReader(filename);
string allData = sr.ReadToEnd();
string[] rows = allData.Split("\n".ToCharArray());
allData = "empty";
sr.DiscardBufferedData();
sr.Close();
cn.Open();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "INSERT INTO bufor (dokid, kod, nazwa, cenazk, ilosc, stan, cenasp, vat) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
cmd.Parameters.Add("@d", SqlDbType.Int, 10);
cmd.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmd.Parameters.Add("@n", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@cz", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@i", SqlDbType.Decimal, 10);
cmd.Parameters["i"].Precision = 10;
cmd.Parameters["i"].Scale = 3;
cmd.Parameters.Add("@s", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@cs", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@v", SqlDbType.NVarChar, 10);
cmd.Prepare();
SqlCeCommand cmdh = cn.CreateCommand();
cmdh.CommandText = "INSERT INTO dok (nazwadok, typ, data) VALUES (?, ?, ?)";
cmdh.Parameters.Add("@d", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmdh.Parameters.Add("@n", SqlDbType.DateTime);
cmdh.Prepare();
SqlCeCommand cmdt = cn.CreateCommand();
cmdt.CommandText = "SELECT * From dok";
cmdt.Prepare();
SqlCeDataReader dr = cmdt.ExecuteReader();
string[] testn = new string[10000];
int l = 0;
while (dr.Read())
{
testn[l] = dr.GetString(1);
l += 1;
}
string[] testnazwa = new string[l];
for (int y = 0; y < l; y++)
{
testnazwa[y] = testn[y];
}
int x = 0;
int dokid = 0;
foreach (string r in rows)
{
string[] items = r.Split(delimeter.ToCharArray());
if (x == 0)
{
for (int z = 0; z < testnazwa.Length; z++)
{
if (items[0] == testnazwa[z])
{
DialogResult result = MessageBox.Show("Plik istnieje, Czy zapisać drugą wersję", "Pytanie", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (result == DialogResult.No)
{
czyistnieje = true;
z = testnazwa.Length;
}
z = testnazwa.Length;
}
}
if (items[0] != "" && czyistnieje == false)
{
cmdh.Parameters["@d"].Value = items[0];
cmdh.Parameters["@k"].Value = items[1];
cmdh.Parameters["@n"].Value = Convert.ToDateTime(items[2]);
cmdh.ExecuteNonQuery();
SqlCeCommand cmdi = cn.CreateCommand();
cmdi.CommandText = "Select MAX(id) From dok";
SqlCeDataReader dri = cmdi.ExecuteReader();
while (dri.Read())
{
dokid = dri.GetInt32(0);
}
x += 1;
}
}
else
{
if (items[0] != "" && czyistnieje == false)
{
cmd.Parameters["@d"].Value = dokid;
cmd.Parameters["@k"].Value = items[1];
cmd.Parameters["@n"].Value = items[2];
cmd.Parameters["@cz"].Value = items[3];
cmd.Parameters["@i"].Value = Convert.ToDecimal(items[4], nfi);
cmd.Parameters["@s"].Value = items[5];
cmd.Parameters["@cs"].Value = items[6];
cmd.Parameters["@v"].Value = items[7];
cmd.ExecuteNonQuery();
}
}
}
cn.Close();
if (czyistnieje == false)
{
MessageBox.Show("Odebrano dokument z urządzenia");
senddoc();
}
else
{
MessageBox.Show("Anulowano odbiór dokumentu");
}
}
else if (sendorec[0] == 8)
{
int totalrecbytes = 0;
FileStream Fs = new FileStream("sprawdzarka.imp", FileMode.Create, FileAccess.Write);
while ((RecBytes = netstream.Read(RecData, 0, RecData.Length)) > 0)
{
Fs.Write(RecData, 0, RecBytes);
totalrecbytes += RecBytes;
}
Fs.Close();
netstream.Close();
client.Close();
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
string delimeter = ";";
string filename = "sprawdzarka.exp";
StreamReader sr = new StreamReader(filename, Encoding.GetEncoding(CultureInfo.CurrentCulture.TextInfo.ANSICodePage), true);
string allData = sr.ReadToEnd();
string[] rows = allData.Split("\n".ToCharArray());
allData = "empty";
sr.DiscardBufferedData();
sr.Close();
cn.Open();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "UPDATE dane SET cenapolka = ?, zliczono = ?, bad_cena = ?, bad_stan = ? WHERE kod = ?";
cmd.Parameters.Add("@p", SqlDbType.Decimal, 10);
cmd.Parameters["@p"].Precision = 10;
cmd.Parameters["@p"].Scale = 3;
cmd.Parameters.Add("@z", SqlDbType.Decimal, 10);
cmd.Parameters["@z"].Precision = 10;
cmd.Parameters["@z"].Scale = 3;
cmd.Parameters.Add("@bc", SqlDbType.Bit);
cmd.Parameters.Add("@bs", SqlDbType.Bit);
cmd.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmd.Prepare();
foreach (string r in rows)
{
string[] items = r.Split(delimeter.ToCharArray());
if (items[0] != "")
{
cmd.Parameters["@p"].Value = items[3];
cmd.Parameters["@z"].Value = items[4];
cmd.Parameters["@bc"].Value = items[1];
cmd.Parameters["@bs"].Value = items[2];
cmd.Parameters["@k"].Value = items[0];
cmd.ExecuteNonQuery();
}
}
cn.Close();
MessageBox.Show("Odebrano dokument z urządzenia");
}
else if (sendorec[0] == 2)
{
File.Delete("picatch.exp");
byte[] SendingBuffer = null;
StreamWriter sw = new StreamWriter("picatch.exp", true, Encoding.GetEncoding(CultureInfo.CurrentCulture.TextInfo.ANSICodePage));
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT * FROM dane";
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
sw.WriteLine(dr.GetString(0) + ";" + dr.GetString(1) + ";" + dr.GetString(2) + ";" + dr.GetString(3) + ";" + dr.GetString(4) + ";" + dr.GetString(5) + ";" + dr.GetString(6) + ";" + dr.GetDateTime(12).ToString());
}
sw.Close();
cn.Close();
FileStream Fs = new FileStream("picatch.exp", FileMode.Open, FileAccess.Read);
int NoOfPackets = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(Fs.Length) / Convert.ToDouble(1024)));
int TotalLength = (int)Fs.Length, CurrentPacketLength;
for (int i = 0; i < NoOfPackets; i++)
{
if (TotalLength > 1024)
{
CurrentPacketLength = 1024;
TotalLength = TotalLength - CurrentPacketLength;
}
else
{
CurrentPacketLength = TotalLength;
}
SendingBuffer = new byte[CurrentPacketLength];
Fs.Read(SendingBuffer, 0, CurrentPacketLength);
netstream.Write(SendingBuffer, 0, (int)SendingBuffer.Length);
}
Fs.Close();
netstream.Close();
client.Close();
}
else if (sendorec[0] == 11)
{
int totalrecbytes = 0;
FileStream Fs = new FileStream("inwent.exp", FileMode.Create, FileAccess.Write);
while ((RecBytes = netstream.Read(RecData, 0, RecData.Length)) > 0)
{
Fs.Write(RecData, 0, RecBytes);
totalrecbytes += RecBytes;
}
Fs.Close();
netstream.Close();
client.Close();
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
string delimeter = ";";
StreamReader sr = new StreamReader("inwent.exp", Encoding.UTF8, true);
string allData = sr.ReadToEnd();
string[] rows = allData.Split("\n".ToCharArray());
allData = "empty";
sr.DiscardBufferedData();
sr.Close();
cn.Open();
SqlCeCommand delete = cn.CreateCommand();
delete.CommandText = "DROP TABLE dane";
delete.Prepare();
delete.ExecuteNonQuery();
SqlCeCommand cmd = new SqlCeCommand("CREATE TABLE dane (typ nvarchar (7), kod nvarchar (15), nazwa nvarchar(40), stan nvarchar(10), cenazk nvarchar(10), cenasp nvarchar(10), vat nvarchar(5), devstat nvarchar(10), bad_cena bit, bad_stan bit, cenapolka numeric(6,3), zliczono numeric(10,3), datazmian datetime)", cn);
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO dane (typ, kod, nazwa, stan, cenazk, cenasp, vat) VALUES (?, ?, ?, ?, ?, ?, ?)";
cmd.Parameters.Add("@t", SqlDbType.NVarChar, 7);
cmd.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmd.Parameters.Add("@n", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@s", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@cz", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@cs", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@v", SqlDbType.NVarChar, 5);
cmd.Prepare();
int i = 0;
foreach (string r in rows)
{
i += 1;
string[] items = r.Split(delimeter.ToCharArray());
if (items[0] != "")
{
cmd.Parameters["@t"].Value = items[0];
cmd.Parameters["@k"].Value = items[1];
cmd.Parameters["@n"].Value = items[2].ToString(CultureInfo.CurrentCulture);
cmd.Parameters["@s"].Value = items[3];
cmd.Parameters["@cz"].Value = items[4];
cmd.Parameters["@cs"].Value = items[5];
cmd.Parameters["@v"].Value = items[6];
cmd.ExecuteNonQuery();
}
}
MessageBox.Show("Odebrano nową bazę towarów");
cn.Close();
}
else if (sendorec[0] == 12)
{
byte[] SendingBuffer = null;
FileStream Fs = new FileStream("picatch.imp", FileMode.Open, FileAccess.Read);
int NoOfPackets = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(Fs.Length) / Convert.ToDouble(1024)));
int TotalLength = (int)Fs.Length, CurrentPacketLength; //counter = 0;
for (int i = 0; i < NoOfPackets; i++)
{
if (TotalLength > 1024)
{
CurrentPacketLength = 1024;
TotalLength = TotalLength - CurrentPacketLength;
}
else
{
CurrentPacketLength = TotalLength;
}
SendingBuffer = new byte[CurrentPacketLength];
Fs.Read(SendingBuffer, 0, CurrentPacketLength);
netstream.Write(SendingBuffer, 0, (int)SendingBuffer.Length);
}
// label1.Text = "Wysłano " + Fs.Length.ToString() + " bajtów do urządzenia";
Fs.Close();
netstream.Close();
client.Close();
}
else if (sendorec[0] == 21)
{
byte[] SendingBuffer = null;
setexpstat = false;
StreamWriter sw = new StreamWriter("edi.exp");
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT * FROM edihead WHERE status = 'Nowy'";
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
sw.WriteLine("edihead;" + dr.GetString(1) + ";" + dr.GetString(2) + ";" + dr.GetString(3) + ";" + dr.GetString(4) + ";" + dr.GetString(5) + ";" + dr.GetString(6) + ";" + dr.GetString(7) + ";" + dr.GetString(8) + ";" + dr.GetString(9) + ";" + dr.GetString(10) + ";" + dr.GetString(11) + ";" + dr.GetString(12) + ";" + dr.GetString(13) + ";" + dr.GetString(14) + ";" + dr.GetString(15) + ";" + dr.GetString(16) + ";" + dr.GetString(17) + ";" + dr.GetString(18) + ";" + dr.GetString(19) + ";" + dr.GetString(20) + ";" + dr.GetString(21) + ";" + dr.GetString(22) + ";" + dr.GetString(23) + ";" + dr.GetString(24) + ";" + dr.GetString(25) + ";" + dr.GetString(26) + ";" + dr.GetString(27) + ";" + dr.GetString(28) + ";" + dr.GetString(29) + ";" + dr.GetString(30) + ";" + dr.GetString(31) + ";" + dr.GetString(32) + ";" + (Convert.ToInt32(dr.GetBoolean(33)).ToString()));
}
cmd.CommandText = "SELECT NrDok, Nazwa, kod, Vat, Jm, Asortyment, Sww, PKWiU, Cast(REPLACE(SUM(CONVERT(numeric(10,3), Ilosc)), ',','.') as nvarchar(10)), Cena, Wartosc, IleWOpak, CenaSp, status, complete FROM edibody GROUP BY NrDok, Nazwa, kod, Vat, Jm, Asortyment, Sww, PKWiU, Cena, Wartosc, IleWOpak, CenaSp, status, complete";
dr = cmd.ExecuteReader();
while (dr.Read())
{
sw.WriteLine("edibody;" + dr.GetString(0) + ";" + dr.GetString(1) + ";" + dr.GetString(2) + ";" + dr.GetString(3) + ";" + dr.GetString(4) + ";" + dr.GetString(5) + ";" + dr.GetString(6) + ";" + dr.GetString(7) + ";" + dr.GetString(8) + ";" + dr.GetString(9) + ";" + dr.GetString(10) + ";" + dr.GetString(11) + ";" + dr.GetString(12) + ";" + dr.GetString(13) + ";" + (Convert.ToInt32(dr.GetBoolean(14)).ToString()));
}
cmd.CommandText = "SELECT * FROM ediend";
dr = cmd.ExecuteReader();
while (dr.Read())
{
sw.WriteLine("ediend;" + dr.GetString(1) + ";" + dr.GetString(2) + ";" + dr.GetString(3) + ";" + dr.GetString(4) + ";" + dr.GetString(5) + ";" + (Convert.ToInt32(dr.GetBoolean(6)).ToString()));
}
sw.Close();
cn.Close();
FileStream Fs = new FileStream("edi.exp", FileMode.Open, FileAccess.Read);
int NoOfPackets = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(Fs.Length) / Convert.ToDouble(1024)));
int TotalLength = (int)Fs.Length, CurrentPacketLength;
for (int i = 0; i < NoOfPackets; i++)
{
if (TotalLength > 1024)
{
CurrentPacketLength = 1024;
TotalLength = TotalLength - CurrentPacketLength;
}
else
{
CurrentPacketLength = TotalLength;
}
SendingBuffer = new byte[CurrentPacketLength];
Fs.Read(SendingBuffer, 0, CurrentPacketLength);
netstream.Write(SendingBuffer, 0, (int)SendingBuffer.Length);
}
Fs.Close();
netstream.Close();
client.Close();
}
else if (sendorec[0] == 22)
{
int totalrecbytes = 0;
FileStream Fs = new FileStream("edi.imp", FileMode.Create, FileAccess.Write);
while ((RecBytes = netstream.Read(RecData, 0, RecData.Length)) > 0)
{
Fs.Write(RecData, 0, RecBytes);
totalrecbytes += RecBytes;
}
Fs.Close();
netstream.Close();
client.Close();
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
string delimeter = ";";
string filename = "edi.imp";
StreamReader sr = new StreamReader(filename);
string allData = sr.ReadToEnd();
string[] rows = allData.Split("\r\n".ToCharArray());
allData = "empty";
sr.DiscardBufferedData();
sr.Close();
cn.Open();
SqlCeCommand cmdh = cn.CreateCommand();
cmdh.CommandText = "INSERT INTO fedihead (FileName, TypPolskichLiter, TypDok, NrDok, Data, DataRealizacji, Magazyn, SposobPlatn, TerminPlatn, IndeksCentralny, NazwaWystawcy, AdresWystawcy, KodWystawcy, MiastoWystawcy, UlicaWystawcy, NIPWystawcy, BankWystawcy, KontoWystawcy, TelefonWystawcy, NrWystawcyWSieciSklepow, NazwaOdbiorcy, AdresOdbiorcy, KodOdbiorcy, MiastoOdbiorcy, UlicaOdbiorcy, NIPOdbiorcy, BankOdbiorcy, KontoOdbiorcy, TelefonOdbiorcy, NrOdbiorcyWSieciSklepow, DoZaplaty, status, complete) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
cmdh.Parameters.Add("@0", SqlDbType.NVarChar, 40);
cmdh.Parameters.Add("@1", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@2", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@3", SqlDbType.NVarChar, 30);
cmdh.Parameters.Add("@4", SqlDbType.NVarChar, 30);
cmdh.Parameters.Add("@5", SqlDbType.NVarChar, 30);
cmdh.Parameters.Add("@6", SqlDbType.NVarChar, 30);
cmdh.Parameters.Add("@7", SqlDbType.NVarChar, 10);
cmdh.Parameters.Add("@8", SqlDbType.NVarChar, 10);
cmdh.Parameters.Add("@9", SqlDbType.NVarChar, 10);
cmdh.Parameters.Add("@10", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@11", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@12", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@13", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@14", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@15", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@16", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@17", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@18", SqlDbType.NVarChar, 30);
cmdh.Parameters.Add("@19", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@20", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@21", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@22", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@23", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@24", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@25", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@26", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@27", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@28", SqlDbType.NVarChar, 120);
cmdh.Parameters.Add("@29", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@30", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@31", SqlDbType.NVarChar, 20);
cmdh.Parameters.Add("@32", SqlDbType.Bit);
cmdh.Prepare();
SqlCeCommand cmdb = cn.CreateCommand();
cmdb.CommandText = "INSERT INTO fedibody (NrDok, Nazwa, kod, Vat, Jm, Asortyment, Ilosc, Cena, IleWOpak, CenaSp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
cmdb.Parameters.Add("@1", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@2", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@3", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@4", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@5", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@6", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@7", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@8", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@9", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@10", SqlDbType.NVarChar, 10);
cmdb.Prepare();
//SqlCeCommand cmde = cn.CreateCommand();
//cmde.CommandText = "INSERT INTO ediend (NrDok, Vat, SumaNet, SumaVat, status, complete) VALUES (?, ?, ?, ?, ?, ?)";
//cmde.Parameters.Add("@1", SqlDbType.NVarChar, 30);
//cmde.Parameters.Add("@2", SqlDbType.NVarChar, 20);
//cmde.Parameters.Add("@3", SqlDbType.NVarChar, 20);
//cmde.Parameters.Add("@4", SqlDbType.NVarChar, 20);
//cmde.Parameters.Add("@5", SqlDbType.NVarChar, 20);
//cmde.Parameters.Add("@6", SqlDbType.Bit);
//cmde.Prepare();
foreach (string r in rows)
{
string[] items = r.Split(delimeter.ToCharArray());
if (items[0] != "" && items[0] == "edihead")
{
cmdh.Parameters["@0"].Value = items[1];
cmdh.Parameters["@1"].Value = items[2];
cmdh.Parameters["@2"].Value = items[3];
cmdh.Parameters["@3"].Value = items[4];
cmdh.Parameters["@4"].Value = items[5];
cmdh.Parameters["@5"].Value = items[6];
cmdh.Parameters["@6"].Value = items[7];
cmdh.Parameters["@7"].Value = items[8];
cmdh.Parameters["@8"].Value = items[9];
cmdh.Parameters["@9"].Value = items[10];
cmdh.Parameters["@10"].Value = items[11];
cmdh.Parameters["@11"].Value = items[12];
cmdh.Parameters["@12"].Value = items[13];
cmdh.Parameters["@13"].Value = items[14];
cmdh.Parameters["@14"].Value = items[15];
cmdh.Parameters["@15"].Value = items[16];
cmdh.Parameters["@16"].Value = items[17];
cmdh.Parameters["@17"].Value = items[18];
cmdh.Parameters["@18"].Value = items[19];
cmdh.Parameters["@19"].Value = items[20];
cmdh.Parameters["@20"].Value = items[21];
cmdh.Parameters["@21"].Value = items[22];
cmdh.Parameters["@22"].Value = items[23];
cmdh.Parameters["@23"].Value = items[24];
cmdh.Parameters["@24"].Value = items[25];
cmdh.Parameters["@25"].Value = items[26];
cmdh.Parameters["@26"].Value = items[27];
cmdh.Parameters["@27"].Value = items[28];
cmdh.Parameters["@28"].Value = items[29];
cmdh.Parameters["@29"].Value = items[30];
cmdh.Parameters["@30"].Value = items[31];
cmdh.Parameters["@31"].Value = items[32];
cmdh.Parameters["@32"].Value = items[33];
cmdh.ExecuteNonQuery();
}
if (items[0] != "" && items[0] == "edibody")
{
cmdb.Parameters["@1"].Value = items[1];
cmdb.Parameters["@2"].Value = items[2];
cmdb.Parameters["@3"].Value = items[3];
cmdb.Parameters["@4"].Value = items[4];
cmdb.Parameters["@5"].Value = items[5];
cmdb.Parameters["@6"].Value = items[6];
cmdb.Parameters["@7"].Value = items[7];
cmdb.Parameters["@8"].Value = items[8];
cmdb.Parameters["@9"].Value = items[9];
cmdb.Parameters["@10"].Value = items[10];
cmdb.ExecuteNonQuery();
}
// if (items[0] != "" && items[0] == "ediend")
// {
// cmde.Parameters["@1"].Value = items[1];
// cmde.Parameters["@2"].Value = items[2];
// cmde.Parameters["@3"].Value = items[3];
// cmde.Parameters["@4"].Value = items[4];
//cmde.Parameters["@5"].Value = items[5];
//cmde.Parameters["@6"].Value = byte.Parse(items[6]);
//cmde.ExecuteNonQuery();
// }
}
cn.Close();
}
netstream.Close();
client.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//netstream.Close();
}
}
else
{
Listener.Stop();
}
}
}
public void updatedata(Undersoft.Picatch.Agent.Main mainf)
{
while (running == true)
{
downloaddata();
try
{
Invoke((MethodInvoker)(() => exkom_t.Text += "KONWERTUJE DANE Z BAZY PCMARKET\n"));
System.Globalization.NumberFormatInfo nfi = new System.Globalization.NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
// string connectionString2;
// connectionString2 = "server=" + serverpcm + ";user id=" + loginpcm + ";password="******";Trusted_Connection=no; database=" + bazapcm + ";connection timeout=30";
// pcmn = new SqlConnection(connectionString2);
}
catch (Exception ex)
{
Invoke((MethodInvoker)(() => exkom_t.Text = "BŁĄD KONWERSJI DANYCH Z BAZY PCMARKET\n"));
}
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
string connectionString3;
connectionString3 = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cnn = new SqlCeConnection(connectionString3);
string connectionString2;
connectionString2 = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cnnn = new SqlCeConnection(connectionString2);
int dbtest = 0;
try
{
cnnn.Open();
cn.Open();
cnn.Open();
}
catch (Exception ex)
{
Invoke((MethodInvoker)(() => exkom_t.Text += "Błąd połączenia z bazą\n"));
dbtest = 1;
}
if (dbtest == 0)
{
try
{
SqlCeCommand cmd3 = cn.CreateCommand();
cmd3.CommandText = "INSERT INTO dane (typ, kod, nazwa, stan, cenazk, cenasp, vat, datazmian, cenahurt, cenaoryg) VALUES ('TOW', ?, ?, ?, ?, ?, ?, ?, ?, ?)";
cmd3.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmd3.Parameters.Add("@n", SqlDbType.NVarChar, 40);
cmd3.Parameters.Add("@s", SqlDbType.NVarChar, 10);
cmd3.Parameters.Add("@cz", SqlDbType.NVarChar, 10);
cmd3.Parameters.Add("@cs", SqlDbType.NVarChar, 10);
cmd3.Parameters.Add("@v", SqlDbType.NVarChar, 5);
cmd3.Parameters.Add("@d", SqlDbType.DateTime);
cmd3.Parameters.Add("@ch", SqlDbType.NVarChar, 10);
cmd3.Parameters.Add("@co", SqlDbType.NVarChar, 10);
cmd3.Prepare();
SqlCeCommand cmd4 = cn.CreateCommand();
cmd4.CommandText = "INSERT INTO stany (kod, stan, MagId, Nazwa, datazmian) VALUES (?, ?, ?, ?, ?)";
cmd4.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmd4.Parameters.Add("@i", SqlDbType.Decimal, 10);
cmd4.Parameters["@i"].Precision = 10;
cmd4.Parameters["@i"].Scale = 3;
cmd4.Parameters.Add("@s", SqlDbType.Int, 11);
cmd4.Parameters.Add("@cz", SqlDbType.NVarChar, 10);
cmd4.Parameters.Add("@d", SqlDbType.DateTime);
cmd4.Prepare();
SqlCeCommand cmd10 = cn.CreateCommand();
cmd10.CommandText = "UPDATE dane SET nazwa = ?, stan = ?, cenazk = ?, cenasp = ?, vat = ?, datazmian = ?, cenahurt = ?, cenaoryg = ? WHERE kod = ?";
cmd10.Parameters.Add("@n", SqlDbType.NVarChar, 40);
cmd10.Parameters.Add("@s", SqlDbType.NVarChar, 10);
cmd10.Parameters.Add("@cz", SqlDbType.NVarChar, 10);
cmd10.Parameters.Add("@cs", SqlDbType.NVarChar, 10);
cmd10.Parameters.Add("@v", SqlDbType.NVarChar, 5);
cmd10.Parameters.Add("@d", SqlDbType.DateTime);
cmd10.Parameters.Add("@ch", SqlDbType.NVarChar, 10);
cmd10.Parameters.Add("@co", SqlDbType.NVarChar, 10);
cmd10.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmd10.Prepare();
SqlCeCommand cmd6 = cn.CreateCommand();
cmd6.CommandText = "UPDATE stany SET stan = ?, datazmian = ? WHERE (kod = ?) AND (MagId = ?)";
cmd6.Parameters.Add("@i", SqlDbType.Decimal, 10);
cmd6.Parameters["@i"].Precision = 10;
cmd6.Parameters["@i"].Scale = 3;
cmd6.Parameters.Add("@d", SqlDbType.DateTime);
cmd6.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmd6.Parameters.Add("@m", SqlDbType.Int, 11);
cmd6.Prepare();
SqlCeCommand cmdcheck = cnn.CreateCommand();
cmdcheck.CommandText = "SELECT count(*) FROM dane where kod = ?";
cmdcheck.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmdcheck.Prepare();
SqlCeCommand cmdchecks = cnn.CreateCommand();
cmdchecks.CommandText = "SELECT count(*) FROM stany where kod = ? and MagId = ?";
cmdchecks.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmdchecks.Parameters.Add("@m", SqlDbType.Int, 11);
cmdchecks.Prepare();
SqlCeCommand cmd = cnnn.CreateCommand();
cmd.CommandText = "SELECT kod, nazwa, stan, cenazk, cenasp, vat, datazmian, cenahurt FROM bufordane";
cmd.Prepare();
// SqlCeCommand cmd5 = cnnn.CreateCommand();
// cmd5.CommandText = "SELECT kod, stan, MagId, Nazwa FROM buforstany";
// cmd5.Prepare();
SqlCeDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
string kod = sdr.GetString(0);
string nazwa = sdr.GetString(1);
string stan = sdr.GetString(2); // decimal.Round(sdr.GetDecimal(2), 2).ToString(nfi);
string cenazk = sdr.GetString(3); // decimal.Round(sdr.GetDecimal(3), 2).ToString(nfi);
string cenasp = sdr.GetString(4); // decimal.Round(sdr.GetDecimal(4), 2).ToString(nfi);
string vat = sdr.GetString(5); // Convert.ToString(sdr.GetInt32(5));
DateTime impdatazmian = sdr.GetDateTime(6);
string cenah = sdr.GetString(7);
string cenao = sdr.GetString(8);
try
{
int count = 0;
cmdcheck.Parameters["@k"].Value = kod;
SqlCeDataReader cmdcheckdr = cmdcheck.ExecuteReader();
while (cmdcheckdr.Read())
{
count = cmdcheckdr.GetInt32(0);
}
cmdcheckdr.Close();
if (count > 0)
{
cmd10.Parameters["@n"].Value = (nazwa.Replace("?", "")).Replace(";", "");
cmd10.Parameters["@s"].Value = stan;
cmd10.Parameters["@cz"].Value = cenazk;
cmd10.Parameters["@cs"].Value = cenasp;
cmd10.Parameters["@v"].Value = vat;
cmd10.Parameters["@d"].Value = impdatazmian;
cmd10.Parameters["@ch"].Value = cenah;
cmd10.Parameters["@k"].Value = kod;
cmd10.ExecuteNonQuery();
if (this.exkom_t.InvokeRequired)
{
Invoke((MethodInvoker)(() => exkom_t.Text += kod + ";" + nazwa + ";" + stan + ";" + impdatazmian.ToString() + "; - Zaktualizowano\n"));
Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length));
Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret()));
}
else
{
exkom_t.Text += kod + ";" + nazwa + ";" + stan + ";" + datazmian.ToString() + "; - Zaktualizowano\n";
exkom_t.SelectionStart = exkom_t.Text.Length;
exkom_t.ScrollToCaret();
}
}
else
{
cmd3.Parameters["@k"].Value = kod;
cmd3.Parameters["@n"].Value = (nazwa.Replace("?", "")).Replace(";", "");
cmd3.Parameters["@s"].Value = stan;
cmd3.Parameters["@cz"].Value = cenazk;
cmd3.Parameters["@cs"].Value = cenasp;
cmd3.Parameters["@v"].Value = vat;
cmd3.Parameters["@d"].Value = impdatazmian;
cmd3.Parameters["@ch"].Value = cenah;
cmd3.Parameters["@co"].Value = cenao;
cmd3.ExecuteNonQuery();
if (this.exkom_t.InvokeRequired)
{
Invoke((MethodInvoker)(() => exkom_t.Text += kod + ";" + nazwa + ";" + stan + ";" + impdatazmian.ToString() + "; - Dodano\n"));
Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length));
Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret()));
}
else
{
exkom_t.Text += kod + ";" + nazwa + ";" + stan + ";" + datazmian.ToString() + "; - Dodano\n";
exkom_t.SelectionStart = exkom_t.Text.Length;
exkom_t.ScrollToCaret();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
sdr.Close();
if (this.exkom_t.InvokeRequired)
{
Invoke((MethodInvoker)(() => exkom_t.Text += "Zaktualizowano dane o towarach\n"));
// Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length));
// Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret()));
}
else
{
exkom_t.Text += "Zaktualizowano dane o towarach\n";
// exkom_t.SelectionStart = exkom_t.Text.Length;
// exkom_t.ScrollToCaret();
}
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT Nazwa, MagId FROM magazyn";
cmd2.Prepare();
SqlCeCommand cmd9 = cnn.CreateCommand();
cmd9.CommandText = "SELECT kod, stan, MagId, Nazwa, datazmian FROM buforstany WHERE MagId = @mag ";
cmd9.Parameters.Add("@mag", SqlDbType.Int);
cmd9.Prepare();
// SqlCommand cmd8 = pcmn.CreateCommand();
// cmd8.CommandText = "SELECT KodDod.Kod, Magazyn.Nazwa, Istw.MagId, Istw.StanMag AS Expr1 FROM Towar INNER JOIN Istw ON Towar.TowId = Istw.TowId INNER JOIN Magazyn ON Istw.MagId = Magazyn.MagId INNER JOIN KodDod ON Towar.TowId = KodDod.TowId WHERE (Towar.Aktywny = 1) AND Istw.MagId = @mag ";
// cmd8.Parameters.Add("@mag", SqlDbType.Int);
// cmd8.Prepare();
SqlCeDataReader cedr = cmd2.ExecuteReader();
while (cedr.Read())
{
string magazyn = cedr.GetString(0);
int magid = cedr.GetInt32(1);
cmd9.Parameters["@mag"].Value = magid;
// cmd8.Parameters["@mag"].Value = magid;
SqlCeDataReader szdr = cmd9.ExecuteReader();
while (szdr.Read())
{
int count = 0;
cmdchecks.Parameters["@m"].Value = magid;
cmdchecks.Parameters["@k"].Value = szdr.GetString(0);
SqlCeDataReader cmdchecksdr = cmdchecks.ExecuteReader();
while (cmdchecksdr.Read())
{
count = cmdchecksdr.GetInt32(0);
}
cmdchecksdr.Close();
if (count > 0)
{
cmd6.Parameters["@i"].Value = szdr.GetDecimal(1);
cmd6.Parameters["@k"].Value = szdr.GetString(0);
cmd6.Parameters["@m"].Value = magid;
cmd6.ExecuteNonQuery();
if (this.exkom_t.InvokeRequired)
{
Invoke((MethodInvoker)(() => exkom_t.Text += szdr.GetString(0) + ";" + szdr.GetDecimal(1).ToString() + ";" + magazyn + "; - Zaktualizowano\n"));
Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length));
Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret()));
}
else
{
exkom_t.Text += szdr.GetString(0) + ";" + szdr.GetDecimal(3).ToString() + ";" + magazyn + "; - Zaktualizowano\n";
exkom_t.SelectionStart = exkom_t.Text.Length;
exkom_t.ScrollToCaret();
}
}
else
{
cmd4.Parameters["@k"].Value = szdr.GetString(0);
cmd4.Parameters["@i"].Value = szdr.GetDecimal(3);
cmd4.Parameters["@s"].Value = szdr.GetDecimal(2);
cmd4.Parameters["@cz"].Value = szdr.GetString(1);
cmd4.Parameters["@d"].Value = szdr.GetDateTime(4);
cmd4.ExecuteNonQuery();
if (this.exkom_t.InvokeRequired)
{
Invoke((MethodInvoker)(() => exkom_t.Text += szdr.GetString(0) + ";" + szdr.GetDecimal(3).ToString() + ";" + magazyn + "; - Dodano\n"));
Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length));
Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret()));
}
else
{
exkom_t.Text += szdr.GetString(0) + ";" + szdr.GetDecimal(3).ToString() + ";" + magazyn + "; - Dodano\n";
exkom_t.SelectionStart = exkom_t.Text.Length;
exkom_t.ScrollToCaret();
}
}
}
szdr.Close();
}
cedr.Close();
cn.Close();
cnn.Close();
cnnn.Close();
if (this.exkom_t.InvokeRequired)
{
Invoke((MethodInvoker)(() => exkom_t.Text += "Zaktualizowano dane o stanach\n"));
Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length));
Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret()));
Invoke((MethodInvoker)(() => exkom_t.Text += "Dane aktualne na:" + DateTime.Now.ToString() + "\n"));
}
else
{
exkom_t.Text += "Zaktualizowano dane o stanach\n";
exkom_t.SelectionStart = exkom_t.Text.Length;
exkom_t.ScrollToCaret();
}
}
catch (Exception eg)
{
cnn.Close();
cn.Close();
cnnn.Close();
Invoke((MethodInvoker)(() => exkom_t.Text += eg.ToString() + "\n"));
}
}
Thread.Sleep(10000);
// exkom_t.Text = "";
}
}