public static bool IsPersonOnBoard(string boardId, string personId)
{
string query = "SELECT * FROM [boards] WHERE board_id=@boardId AND person_id=@personId";
if (String.IsNullOrEmpty(boardId) || String.IsNullOrEmpty(personId))
{
return false;
}
using (SqlCeConnection conn = new SqlCeConnection())
{
conn.ConnectionString = CommonState.ConnectionString;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
{
cmd.CommandText = query;
cmd.Parameters.Add("@boardId", boardId);
cmd.Parameters.Add("@personId", personId);
cmd.Prepare();
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
long last = (long)reader["person_lastactivity"];
if (last > (CommonState.EpochTime - Configure.CONNECTOIN_TIMEOUT))
{
return true;
}
}
}
}
}
return false;
}
public static void ImportWinners()
{
using (FileStream fs = new FileStream(@"Y:\LDYC\Winners.txt", FileMode.Open, FileAccess.Read)) {
string[] headers;
using (StreamReader reader = new StreamReader(fs)) {
headers = reader.ReadLine().Split('\t');
using (SqlCeConnection conn = Connection) {
conn.Open();
using (SqlCeTransaction trans = conn.BeginTransaction()) {
using (SqlCeCommand comm = new SqlCeCommand()) {
comm.CommandText = @"INSERT INTO tblWinners(fldTrophyID, fldClassID, fldYear,
fldSailNumber, fldHelm, fldOwner, fldCreated, fldCrew, fldNotes) VALUES(@fldTrophyID, @fldClassID, @fldYear,
@fldSailNumber, @fldHelm, @fldOwner, @fldCreated, '', '');
";
comm.Parameters.Add("fldTrophyID", SqlDbType.Int);
comm.Parameters.Add("fldClassID", SqlDbType.Int);
comm.Parameters.Add("fldYear", SqlDbType.SmallInt);
comm.Parameters.Add("fldSailNumber", SqlDbType.NVarChar);
comm.Parameters.Add("fldHelm", SqlDbType.NVarChar);
comm.Parameters.Add("fldOwner", SqlDbType.NVarChar);
comm.Parameters.AddWithValue("fldCreated", DateTime.Now);
comm.Connection = conn;
comm.Transaction = trans;
comm.Prepare();
string line;
while ((line = reader.ReadLine()) != null) {
string[] parts = line.Split('\t');
for (int i = 0; i < headers.Length; i++) {
comm.Parameters[headers[i]].Value = parts[i];
}
comm.ExecuteNonQuery();
}
}
trans.Commit();
}
}
}
}
}
public Form7_1_2_1()
{
//
// Required for Windows Form Designer support
//
string connectionString;
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())
{
transfer = dr.GetString(1);
com = dr.GetString(2);
ip = dr.GetString(3);
ufile = dr.GetString(4);
dfile = dr.GetString(5);
bdll = dr.GetString(6);
bflag = dr.GetBoolean(7);
ipflag = dr.GetBoolean(8);
port = dr.GetInt32(9);
skaner = dr.GetString(10);
}
cn.Close();
ufile = "sprawdzarka.exp";
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Повертає одиницю виміру по ідентифікатору
/// </summary>
/// <param name="id">Ідентифікатор одиниці виміру</param>
/// <returns>Екземпляр одиниці виміру</returns>
public IUnitsModel GetById(int id)
{
UnitsModel unit = new UnitsModel();
using (var db = new SqlCeConnection(connectionString))
{
try
{
db.Open();
}
catch (SqlCeException)
{
throw new Exception("Немає підключення до бази даних.");
}
string query = "select Id, Name, Notes from Units where Id=@Id";
using (SqlCeCommand cmd = new SqlCeCommand(query, db))
{
cmd.Prepare();
cmd.Parameters.AddWithValue("@Id", id);
try
{
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
unit.Id = Convert.ToInt32(reader["Id"]);
unit.Name = reader["Name"].ToString();
unit.Notes = reader["Notes"].ToString();
}
}
}
catch (SqlCeException)
{
throw new Exception("Помилка отримання одиниці виміру з бази даних.");
}
}
}
return(unit);
}
public void checklastimport()
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
try
{
cn.Open();
}
catch (Exception ex)
{
cn.Close();
Invoke((MethodInvoker)(() => exkom_t.Text += "Błąd połączenia z bazą\n"));
}
SqlCeCommand lastdatecmd = cn.CreateCommand();
lastdatecmd.CommandText = "SELECT MAX(datazmian) AS lastdate FROM dane ";
lastdatecmd.Prepare();
SqlCeDataReader lastdatedr = lastdatecmd.ExecuteReader();
while (lastdatedr.Read())
{
if (!lastdatedr.IsDBNull(0))
{
lastimport = lastdatedr.GetDateTime(0);
}
else
{
lastimport = Convert.ToDateTime("1900-01-01 00:00:00");
}
}
cn.Close();
}
public IProductsModel GetById(int id)
{
ProductsModel product = new ProductsModel();
using (var db = new SqlCeConnection(connectionString))
{
db.Open();
string query = "select Id, SupplierId, CategoryId, GroupId, UnitId, NameWebStore, CodeWebStore, " +
"CodeSupplier, PriceWebStore, PriceSupplier, p.Available, LinkWebStore, LinkSupplier, Notes " +
"from Products where Id=@Id";
using (SqlCeCommand cmd = new SqlCeCommand(query, db))
{
cmd.Prepare();
cmd.Parameters.AddWithValue("@Id", id);
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
product.Id = Convert.ToInt32(reader["Id"]);
product.SupplierId = Convert.ToInt32(reader["SupplierId"]);
product.CategoryId = Convert.ToInt32(reader["CategoryId"]);
product.GroupId = Convert.ToInt32(reader["GroupId"]);
product.UnitId = Convert.ToInt32(reader["UnitId"]);
product.NameWebStore = reader["NameWebStore"].ToString();
product.NameSupplier = reader["NameSupplier"].ToString();
product.CodeWebStore = reader["CodeWebStore"].ToString();
product.CodeSupplier = reader["CodeSupplier"].ToString();
product.PriceWebStore = Convert.ToDecimal(reader["PriceWebStore"]);
product.PriceSupplier = Convert.ToDecimal(reader["PriceSupplier"]);
product.Available = reader["Available"].ToString();
product.LinkWebStore = reader["LinkWebStore"].ToString();
product.LinkSupplier = reader["LinkSupplier"].ToString();
product.Notes = reader["Notes"].ToString();
}
}
}
db.Close();
}
return(product);
}
public Form71(int licence)
{
//
// Required for Windows Form Designer support
//
//lic = licence;
string connectionString;
lic = licence;
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())
{
transfer = dr.GetString(1);
com = dr.GetString(2);
ip = dr.GetString(3);
ufile = dr.GetString(4);
dfile = dr.GetString(5);
bdll = dr.GetString(6);
bflag = dr.GetBoolean(7);
ipflag = dr.GetBoolean(8);
port = dr.GetInt32(9);
skaner = dr.GetString(10);
}
cn.Close();
InitializeComponent();
this.Height = Screen.PrimaryScreen.Bounds.Height;
this.Width = Screen.PrimaryScreen.Bounds.Width;
Update();
}
public Info()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
string connectionString;
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())
{
serial = dr.GetString(11);
}
serial_t.Text = serial;
licence = GetDeviceID().Substring(1, 10);
deviceid.Text = licence;
this.Height = Screen.PrimaryScreen.Bounds.Height;
this.Width = Screen.PrimaryScreen.Bounds.Width;
Update();
//
// TODO: Add any constructor code after InitializeComponent call
//
cn.Close();
}
private void rezlicz_b_Click(object sender, System.EventArgs e)
{
DialogResult result = MessageBox.Show("Czy napewno chcesz zatwierdziæ", "Pytanie", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (result == DialogResult.Yes)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'Ok', complete = 1 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Parameters["@a"].Value = ebid;
cmdc.Prepare();
cmdc.ExecuteNonQuery();
cn.Close();
}
this.Close();
}
/// <summary>
/// Оновлює групу
/// </summary>
/// <param name="groupsModel">Екземпляр групи</param>
public void Update(IGroupsModel groupsModel)
{
using (var db = new SqlCeConnection(connectionString))
{
try
{
db.Open();
}
catch (SqlCeException)
{
throw new Exception("Немає підключення до бази даних.");
}
var sqlQuery = "update Groups set Name=@Name, Number=@Number, Identifier=@Identifier, AncestorNumber=@AncestorNumber, " +
"AncestorIdentifier=@AncestorIdentifier, ProductType=@ProductType, Link=@Link, Notes=@Notes where Id=@Id";
var cmd = new SqlCeCommand(sqlQuery, db);
cmd.Prepare();
cmd.Parameters.AddWithValue("@Name", groupsModel.Name);
cmd.Parameters.AddWithValue("@Number", groupsModel.Number);
cmd.Parameters.AddWithValue("@Identifier", groupsModel.Identifier);
cmd.Parameters.AddWithValue("@AncestorNumber", groupsModel.AncestorNumber);
cmd.Parameters.AddWithValue("@AncestorIdentifier", groupsModel.AncestorIdentifier);
cmd.Parameters.AddWithValue("@ProductType", groupsModel.ProductType);
cmd.Parameters.AddWithValue("@Link", groupsModel.Link);
cmd.Parameters.AddWithValue("@Notes", groupsModel.Notes);
cmd.Parameters.AddWithValue("@Id", groupsModel.Id);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw new Exception("Помилка оновлення групи в базі даних.");
}
}
}
public void Add(IGroupsModel model)
{
string sqlQuery = "insert into Groups(Name, Number, Identifier, AncestorNumber, AncestorIdentifier, ProductType, Link, Notes) " +
"values(@Name, @Number, @Identifier, @AncestorNumber, @AncestorIdentifier, @ProductType, @Link, @Notes)";
using (var db = new SqlCeConnection(connectionString))
{
db.Open();
var cmd = new SqlCeCommand(sqlQuery, db);
cmd.Prepare();
cmd.Parameters.AddWithValue("@Name", model.Name);
cmd.Parameters.AddWithValue("@Number", model.Number);
cmd.Parameters.AddWithValue("@Identifier", model.Identifier);
cmd.Parameters.AddWithValue("@AncestorNumber", model.AncestorNumber);
cmd.Parameters.AddWithValue("@AncestorIdentifier", model.AncestorIdentifier);
cmd.Parameters.AddWithValue("@ProductType", model.ProductType);
cmd.Parameters.AddWithValue("@Link", model.Link);
cmd.Parameters.AddWithValue("@Notes", model.Notes);
cmd.ExecuteNonQuery();
db.Close();
}
}
private void confirm_send()
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM dok", cn);
DataTable table = new DataTable();
da.Fill(table);
string index = table.Rows[rownum][0].ToString();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "UPDATE dok SET send = -1 WHERE id = ?";
cmd.Parameters.Add("@k", SqlDbType.Int);
cmd.Parameters["@k"].Value = int.Parse(index);
cmd.Prepare();
cmd.ExecuteNonQuery();
cn.Close();
}
/// <summary>
/// ToDoデータの更新
/// </summary>
/// <param name="data">ToDoデータ</param>
public void UpdateTodo(TodoManager.DataType data)
{
try
{
using (SqlCeTransaction trans = this.con.BeginTransaction())
{
using (SqlCeCommand cmd = this.con.CreateCommand())
{
string sql = @"UPDATE [ToDo] "
+ "SET deadline = @deadline, contents = @contents, tobedetermined = @tobedetermined, updatetime = @updatetime "
+ "WHERE id = @id;";
cmd.CommandText = sql;
cmd.Parameters.Add("deadline", System.Data.DbType.DateTime);
cmd.Parameters.Add("contents", System.Data.DbType.String);
cmd.Parameters.Add("tobedetermined", System.Data.DbType.Int32);
cmd.Parameters.Add("updatetime", System.Data.DbType.DateTime);
cmd.Parameters.Add("id", System.Data.DbType.Int32);
cmd.Parameters["deadline"].Value = data.Deadline.ToString("yyyy-MM-dd");
cmd.Parameters["contents"].Value = data.Contents;
cmd.Parameters["tobedetermined"].Value = data.ToBeDetermined;
cmd.Parameters["updatetime"].Value = DateTime.Now.ToString("yyyy-MM-dd");
cmd.Parameters["id"].Value = data.Id;
cmd.Prepare();
cmd.ExecuteNonQuery();
trans.Commit();
}
}
}
catch
{
throw;
}
}
public void Update(IGroupsModel model)
{
var sqlQuery = "update Groups set Name=@Name, Number=@Number, Identifier=@Identifier, AncestorNumber=@AncestorNumber, " +
"AncestorIdentifier=@AncestorIdentifier, ProductType=@ProductType, Link=@Link, Notes=@Notes where Id=@Id";
using (var db = new SqlCeConnection(connectionString))
{
db.Open();
var cmd = new SqlCeCommand(sqlQuery, db);
cmd.Prepare();
cmd.Parameters.AddWithValue("@Name", model.Name);
cmd.Parameters.AddWithValue("@Number", model.Number);
cmd.Parameters.AddWithValue("@Identifier", model.Identifier);
cmd.Parameters.AddWithValue("@AncestorNumber", model.AncestorNumber);
cmd.Parameters.AddWithValue("@AncestorIdentifier", model.AncestorIdentifier);
cmd.Parameters.AddWithValue("@ProductType", model.ProductType);
cmd.Parameters.AddWithValue("@Link", model.Link);
cmd.Parameters.AddWithValue("@Notes", model.Notes);
cmd.Parameters.AddWithValue("@Id", model.Id);
cmd.ExecuteNonQuery();
db.Close();
}
}
public void Save()
{
String insert = "";
SqlCeCommand comm = new SqlCeCommand();
insert = "INSERT INTO Users (fname, lname, type, username, password) VALUES (@Fname, @Lname, @Type, @Username, @Password)";
comm.CommandText = insert;
comm.Connection = _29thStreet_Cafe_Sales_Inventory_System.Connector.db.getConnection();
comm.Parameters.Add("@Fname", this.Fname);
comm.Parameters.Add("@Lname", this.Lname);
comm.Parameters.Add("@Type", this.Type);
comm.Parameters.Add("@Username", this.Username);
comm.Parameters.Add("@Password", this.Password);
comm.Prepare();
try { comm.ExecuteNonQuery(); }
catch (SqlCeException ex) {
System.Windows.Forms.MessageBox.Show("" + ex.Message);
}
comm.Dispose();
Console.WriteLine("Successfully Added!");
}
/// <summary>
/// Додає групу
/// </summary>
/// <param name="groupsModel">Екземпляр групи</param>
public void Add(IGroupsModel groupsModel)
{
using (var db = new SqlCeConnection(connectionString))
{
try
{
db.Open();
}
catch (SqlCeException)
{
throw new Exception("Немає підключення до бази даних.");
}
string sqlQuery = "insert into Groups(Name, Number, Identifier, AncestorNumber, AncestorIdentifier, ProductType, Link, Notes) " +
"values(@Name, @Number, @Identifier, @AncestorNumber, @AncestorIdentifier, @ProductType, @Link, @Notes)";
var cmd = new SqlCeCommand(sqlQuery, db);
cmd.Prepare();
cmd.Parameters.AddWithValue("@Name", groupsModel.Name);
cmd.Parameters.AddWithValue("@Number", groupsModel.Number);
cmd.Parameters.AddWithValue("@Identifier", groupsModel.Identifier);
cmd.Parameters.AddWithValue("@AncestorNumber", groupsModel.AncestorNumber);
cmd.Parameters.AddWithValue("@AncestorIdentifier", groupsModel.AncestorIdentifier);
cmd.Parameters.AddWithValue("@ProductType", groupsModel.ProductType);
cmd.Parameters.AddWithValue("@Link", groupsModel.Link);
cmd.Parameters.AddWithValue("@Notes", groupsModel.Notes);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlCeException)
{
throw new Exception("Помилка створення групи в базі даних.");
}
}
}
private void pcmarket_schema_import()
{
string[] filePaths = Directory.GetFiles(dfile, "*.txt");
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
foreach (string r in filePaths)
{
StreamReader sr = new StreamReader(r, Encoding.Default);
DataTable edihead = new DataTable("edihead");
//edihead.Locale = CultureInfo.CurrentCulture;
edihead.Columns.Add("FileName", typeof(String));
edihead.Columns.Add("TypPolskichLiter", typeof(String));
edihead.Columns.Add("TypDok", typeof(String));
edihead.Columns.Add("NrDok", typeof(String));
edihead.Columns.Add("Data", typeof(String));
edihead.Columns.Add("DataRealizacji", typeof(String));
edihead.Columns.Add("Magazyn", typeof(String));
edihead.Columns.Add("SposobPlatn", typeof(String));
edihead.Columns.Add("TerminPlatn", typeof(String));
edihead.Columns.Add("IndeksCentralny", typeof(String));
edihead.Columns.Add("NazwaWystawcy", typeof(String));
edihead.Columns.Add("AdresWystawcy", typeof(String));
edihead.Columns.Add("KodWystawcy", typeof(String));
edihead.Columns.Add("MiastoWystawcy", typeof(String));
edihead.Columns.Add("UlicaWystawcy", typeof(String));
edihead.Columns.Add("NIPWystawcy", typeof(String));
edihead.Columns.Add("BankWystawcy", typeof(String));
edihead.Columns.Add("KontoWystawcy", typeof(String));
edihead.Columns.Add("TelefonWystawcy", typeof(String));
edihead.Columns.Add("NrWystawcyWSieciSklepow", typeof(String));
edihead.Columns.Add("NazwaOdbiorcy", typeof(String));
edihead.Columns.Add("AdresOdbiorcy", typeof(String));
edihead.Columns.Add("KodOdbiorcy", typeof(String));
edihead.Columns.Add("MiastoOdbiorcy", typeof(String));
edihead.Columns.Add("UlicaOdbiorcy", typeof(String));
edihead.Columns.Add("NIPOdbiorcy", typeof(String));
edihead.Columns.Add("BankOdbiorcy", typeof(String));
edihead.Columns.Add("KontoOdbiorcy", typeof(String));
edihead.Columns.Add("TelefonOdbiorcy", typeof(String));
edihead.Columns.Add("NrOdbiorcyWSieciSklepow", typeof(String));
edihead.Columns.Add("DoZaplaty", typeof(String));
edihead.Columns.Add("status", typeof(String));
edihead.Columns.Add("complete", typeof(Boolean));
DataTable edibody = new DataTable("edibody");
// edibody.Locale = CultureInfo.CurrentCulture;
edibody.Columns.Add("NrDok", typeof(String));
edibody.Columns.Add("Nazwa", typeof(String));
edibody.Columns.Add("Kod", typeof(String));
edibody.Columns.Add("Vat", typeof(String));
edibody.Columns.Add("Jm", typeof(String));
edibody.Columns.Add("Asortyment", typeof(String));
edibody.Columns.Add("Sww", typeof(String));
edibody.Columns.Add("PKWiU", typeof(String));
edibody.Columns.Add("Ilosc", typeof(String));
edibody.Columns.Add("Cena", typeof(String));
edibody.Columns.Add("Wartosc", typeof(String));
edibody.Columns.Add("IleWOpak", typeof(String));
edibody.Columns.Add("CenaSp", typeof(String));
edibody.Columns.Add("status", typeof(String));
edibody.Columns.Add("complete", typeof(Boolean));
DataTable ediend = new DataTable("ediend");
// ediend.Locale = CultureInfo.CurrentCulture;
ediend.Columns.Add("NrDok", typeof(String));
ediend.Columns.Add("Vat", typeof(String));
ediend.Columns.Add("SumaNet", typeof(String));
ediend.Columns.Add("SumaVat", typeof(String));
ediend.Columns.Add("status", typeof(String));
ediend.Columns.Add("complete", typeof(Boolean));
string typdok = "";
string Numerdok = "";
DataRow row = edihead.NewRow();
row["FileName"] = Path.GetFileName(r);
row["status"] = "Nowy";
row["complete"] = false;
string line;
while ((line = sr.ReadLine()) != null)
{
string[] items = line.Split(':');
//make sure it has 3 items
if (items[0] == "TypPolskichLiter")
{
row["TypPolskichLiter"] = items[1];
}
if (items[0] == "TypDok")
{
row["TypDok"] = items[1];
typdok = items[1];
}
if (items[0] == "NrDok")
{
row["NrDok"] = items[1];
Numerdok = items[1];
}
if (items[0] == "Data")
{
row["Data"] = items[1];
}
if (items[0] == "DataRealizacji")
{
row["DataRealizacji"] = items[1];
}
if (items[0] == "Magazyn")
{
row["Magazyn"] = items[1];
}
if (items[0] == "SposobPlatn")
{
row["SposobPlatn"] = items[1];
}
if (items[0] == "TerminPlatn")
{
row["TerminPlatn"] = items[1];
}
if (items[0] == "IndeksCentralny")
{
row["IndeksCentralny"] = items[1];
}
if (items[0] == "NazwaWystawcy")
{
row["NazwaWystawcy"] = items[1];
}
if (items[0] == "AdresWystawcy")
{
row["AdresWystawcy"] = items[1];
}
if (items[0] == "KodWystawcy")
{
row["KodWystawcy"] = items[1];
}
if (items[0] == "MiastoWystawcy")
{
row["MiastoWystawcy"] = items[1];
}
if (items[0] == "UlicaWystawcy")
{
row["UlicaWystawcy"] = items[1];
}
if (items[0] == "NIPWystawcy")
{
row["NIPWystawcy"] = items[1];
}
if (items[0] == "BankWystawcy")
{
row["BankWystawcy"] = items[1];
}
if (items[0] == "KontoWystawcy")
{
row["KontoWystawcy"] = items[1];
}
if (items[0] == "TelefonWystawcy")
{
row["TelefonWystawcy"] = items[1];
}
if (items[0] == "NrWystawcyWSieciSklepow")
{
row["NrWystawcyWSieciSklepow"] = items[1];
}
if (items[0] == "NazwaOdbiorcy")
{
row["NazwaOdbiorcy"] = items[1];
}
if (items[0] == "AdresOdbiorcy")
{
row["AdresOdbiorcy"] = items[1];
}
if (items[0] == "KodOdbiorcy")
{
row["KodOdbiorcy"] = items[1];
}
if (items[0] == "MiastoOdbiorcy")
{
row["MiastoOdbiorcy"] = items[1];
}
if (items[0] == "UlicaOdbiorcy")
{
row["UlicaOdbiorcy"] = items[1];
}
if (items[0] == "NIPOdbiorcy")
{
row["NIPOdbiorcy"] = items[1];
}
if (items[0] == "BankOdbiorcy")
{
row["BankOdbiorcy"] = items[1];
}
if (items[0] == "KontoOdbiorcy")
{
row["KontoOdbiorcy"] = items[1];
}
if (items[0] == "TelefonOdbiorcy")
{
row["TelefonOdbiorcy"] = items[1];
}
if (items[0] == "NrOdbiorcyWSieciSklepow")
{
row["NrOdbiorcyWSieciSklepow"] = items[1];
}
if (items[0] == "DoZaplaty")
{
row["DoZaplaty"] = items[1];
}
if (items[0] == "Linia")
{
char[] delim = new char[2];
delim[0] = '{';
delim[1] = '}';
string[] linie = items[1].Split(delim);
DataRow row1 = edibody.NewRow();
row1["NrDok"] = Numerdok;
row1["Nazwa"] = linie[1];
row1["Kod"] = linie[3];
row1["Vat"] = linie[5];
row1["Jm"] = linie[7];
row1["Asortyment"] = linie[9];
row1["Sww"] = linie[11];
row1["PKWiU"] = linie[13];
row1["Ilosc"] = linie[15];
row1["Cena"] = linie[17];
row1["Wartosc"] = linie[19];
row1["IleWOpak"] = linie[21];
row1["CenaSp"] = linie[23];
row1["status"] = "Nowy";
row1["complete"] = false;
edibody.Rows.Add(row1);
}
if (items[0] == "Stawka")
{
char[] delim = new char[2];
delim[0] = '{';
delim[1] = '}';
string[] linie = items[1].Split(delim);
DataRow row2 = ediend.NewRow();
row2["NrDok"] = Numerdok;
row2["Vat"] = linie[1];
row2["SumaNet"] = linie[3];
row2["SumaVat"] = linie[5];
row2["status"] = "Nowy";
row2["complete"] = false;
ediend.Rows.Add(row2);
}
}
string testdokeh = "";
int flagaimp = 0;
SqlCeCommand testnrdok = cn.CreateCommand();
testnrdok.CommandText = "SELECT NrDok From edihead";
testnrdok.Prepare();
SqlCeDataReader sdr = testnrdok.ExecuteReader();
while (sdr.Read())
{
if (sdr.IsDBNull(0) != true)
{
testdokeh = sdr.GetString(0);
if (testdokeh == Numerdok)
{
flagaimp = 1;
}
}
}
if (typdok != transfer)
{
flagaimp = 1;
}
if (flagaimp != 1)
{
edihead.Rows.Add(row);
SqlCeBulkCopy bulkcopy = new SqlCeBulkCopy(connectionString);
bulkcopy.DestinationTableName = edihead.TableName;
try
{
bulkcopy.WriteToServer(edihead);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
bulkcopy.DestinationTableName = edibody.TableName;
try
{
bulkcopy.WriteToServer(edibody);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
bulkcopy.DestinationTableName = ediend.TableName;
try
{
bulkcopy.WriteToServer(ediend);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
sr.Close();
if (Directory.Exists(dfile + "\\usunięte\\") != true)
{
Directory.CreateDirectory(dfile + "\\usunięte\\");
}
if (File.Exists(dfile + "\\usunięte\\" + Path.GetFileName(r)) != false)
{
File.Delete(dfile + "\\usunięte\\" + Path.GetFileName(r));
}
File.Move(r, dfile + "\\usunięte\\" + Path.GetFileName(r));
}
}
cn.Close();
}
public static void KillPersonOnBoard(string boardId, string personId)
{
if (String.IsNullOrEmpty(boardId) || String.IsNullOrEmpty(personId))
return;
string query = String.
Format("SELECT * FROM [boards] WHERE {0}=@boardId AND {1}=@personId", COL_BID, COL_PID);
string delete = String.
Format("DELETE FROM [boards] WHERE {0}=@boardId AND {1}=@personId", COL_BID, COL_PID);
using (SqlCeConnection conn = new SqlCeConnection())
{
conn.ConnectionString = CommonState.ConnectionString;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
{
cmd.CommandText = query;
cmd.Parameters.Add("@boardId", boardId);
cmd.Parameters.Add("@personId", personId);
cmd.Prepare();
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
cmd.CommandText = delete;
cmd.ExecuteNonQuery();
}
}
}
}
}
public string unesiStavkeUskladeRobe(DataTable dataTable)
{
if (classSQL.remoteConnectionString == "")
{
if (classSQL.connection.State.ToString() == "Closed")
{
classSQL.connection.Open();
}
SqlCeCommand sqlCeCommand = classSQL.connection.CreateCommand();
try
{
sqlCeCommand.CommandText = @"INSERT INTO usklada_robe_stavke(usklada_id,roba_id,nova_kolicina,stara_kolicina) VALUES(@usklada_id,@roba_id,@nova_kolicina,@stara_kolicina";
sqlCeCommand.Parameters.Add("@usklada_id", SqlDbType.Int);
sqlCeCommand.Parameters.Add("@roba_id", SqlDbType.Int);
sqlCeCommand.Parameters.Add("@nova_kolicina", SqlDbType.Float);
sqlCeCommand.Parameters.Add("@stara_kolicina", SqlDbType.Float);
sqlCeCommand.Prepare();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
sqlCeCommand.Parameters["@usklada_id"].Value = dataTable.Rows[i]["@usklada_id"].ToString();
sqlCeCommand.Parameters["@roba_id"].Value = dataTable.Rows[i]["@roba_id"].ToString();
sqlCeCommand.Parameters["@nova_kolicina"].Value = float.Parse(dataTable.Rows[i]["@nova_kolicina"].ToString());
sqlCeCommand.Parameters["@stara_kolicina"].Value = float.Parse(dataTable.Rows[i]["@stara_kolicina"].ToString());
sqlCeCommand.ExecuteNonQuery();
}
}
catch (SqlCeException ex)
{
classSQL.connection.Close();
return(ex.ToString());
}
finally
{
classSQL.connection.Close();
}
return("");
}
else
{
if (classSQL.remoteConnection.State.ToString() == "Closed")
{
classSQL.remoteConnection.Open();
}
NpgsqlCommand npgsqlCommand = classSQL.remoteConnection.CreateCommand();
try
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
string CommandText = @"INSERT INTO usklada_robe_stavke(usklada_id,roba_id,nova_kolicina,stara_kolicina)
VALUES(" + int.Parse(dataTable.Rows[i]["usklada_id"].ToString()) + ","
+ int.Parse(dataTable.Rows[i]["roba_id"].ToString()) + ","
+ float.Parse(dataTable.Rows[i]["nova_kolicina"].ToString()) + ","
+ float.Parse(dataTable.Rows[i]["stara_kolicina"].ToString());
NpgsqlCommand comm = new NpgsqlCommand(CommandText, classSQL.remoteConnection);
comm.ExecuteNonQuery();
}
}
catch (NpgsqlException ex)
{
classSQL.connection.Close();
return(ex.ToString());
}
finally
{
classSQL.connection.Close();
}
return("");
}
}
private void AddCommand(SqlCeCommand cmd)
{
cmd.Prepare();
_commands[cmd.CommandText] = cmd;
}
private void FindIndex()
{
string kodbuf = kod_t.Text;
int wagaflag = 0;
string czywag = kodbuf.Substring(0, 2);
string waga = "";
string kodwag = "";
string kodwag2 = "";
if (czywag == "27" || czywag == "28" || czywag == "29")
{
if (kodbuf.Length == 13)
{
waga = kodbuf.Substring(kodbuf.Length - 6, 5);
kodwag = kodbuf.Substring(0, 6);
kodwag2 = kodbuf.Substring(2, 4);
wagaflag = 1;
}
}
//int rowqty = 0;
kod_t.Text = "SZUKAM TOWARU W BAZIE";
kod_t.Refresh();
//SqlCeCommand cmd2 = cn.CreateCommand();
//cmd2.CommandText = "SELECT kod, COUNT(nazwa) FROM dane WHERE kod = ? GROUP BY kod";
//cmd2.Parameters.Add("@k", SqlDbType.NVarChar, 20);
//cmd2.Parameters["@k"].Value = kodbuf;
//cmd2.Prepare();
//SqlCeDataReader dr1 = cmd2.ExecuteReader();
//while (dr1.Read())
//{
// rowqty = dr1.GetInt32(1);
//}
//if (rowqty > 0)
//{
if (wagaflag == 0)
{
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT kod, nazwa, stan, cenazk, cenasp, vat FROM dane WHERE kod = ?";
cmd.Parameters.Add("@k", SqlDbType.NVarChar, 20);
cmd.Parameters["@k"].Value = kodbuf;
cmd.Prepare();
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
nazwa_t.Text = dr.GetString(1);
stan_t.Text = dr.GetString(2);
cena_t.Text = dr.GetString(3);
cenasp_t.Text = dr.GetString(4);
vat_t.Text = dr.GetString(5);
}
cmd.Dispose();
dr.Dispose();
string zliczono = "0";
cmd = cn.CreateCommand();
cmd.CommandText = "SELECT kod, dokid, ilosc FROM bufor WHERE kod = ? and dokid = ?";
cmd.Parameters.Add("@k", SqlDbType.NVarChar, 15);
cmd.Parameters.Add("@d", SqlDbType.Int, 10);
cmd.Parameters["@k"].Value = kodbuf;
cmd.Parameters["@d"].Value = int.Parse(index);
cmd.Prepare();
dr = cmd.ExecuteReader();
while (dr.Read())
{
zliczono = ((decimal.Parse(zliczono) + dr.GetSqlDecimal(2)).ToString());;
}
zliczono_t.Text = zliczono;
kod_t.Text = kodbuf;
ilosc_t.Focus();
}
else if (wagaflag == 1)
{
string like = "kod LIKE '" + kodwag + ".......'";
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT kod, nazwa, stan, cenazk, cenasp, vat FROM dane WHERE " + like;
cmd.Prepare();
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
nazwa_t.Text = dr.GetString(1);
stan_t.Text = dr.GetString(2);
cena_t.Text = dr.GetString(3);
cenasp_t.Text = dr.GetString(4);
vat_t.Text = dr.GetString(5);
ilosc_t.Text = (int.Parse(waga.Substring(0, 2))).ToString() + "." + waga.Substring(2, 3);
}
cmd.Dispose();
dr.Dispose();
string zliczono = "0";
cmd = cn.CreateCommand();
cmd.CommandText = "SELECT kod, dokid, ilosc FROM bufor WHERE dokid = ? and " + like;
cmd.Parameters.Add("@d", SqlDbType.Int, 10);
cmd.Parameters["@d"].Value = int.Parse(index);
cmd.Prepare();
dr = cmd.ExecuteReader();
while (dr.Read())
{
zliczono = ((decimal.Parse(zliczono) + dr.GetSqlDecimal(2)).ToString());;
}
zliczono_t.Text = zliczono;
kod_t.Text = kodwag;
ilosc_t.Focus();
}
if (nazwa_t.Text == null || nazwa_t.Text == "")
{
DialogResult dialog = MessageBox.Show("Nie znaleziono kodu towaru czy? dodaæ - Tak, dodaæ bez nazwy - Anuluj, Nie dodawaæ - Nie", "Brak towaru", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (dialog == DialogResult.Yes)
{
kod_t.Text = kodbuf;
nazwa_t.ReadOnly = false;
cena_t.ReadOnly = false;
nazwa_t.Focus();
cena_t.Text = "0";
cenasp_t.Text = "0";
stan_t.Text = "0";
vat_t.Text = "0";
if (wagaflag == 1)
{
ilosc_t.Text = waga.Substring(0, 2) + "." + waga.Substring(2, 3);
}
}
else if (dialog == DialogResult.No)
{
kod_t.Text = null;
kod_t.Focus();
}
else if (dialog == DialogResult.Cancel)
{
nazwa_t.ReadOnly = true;
cena_t.ReadOnly = true;
kod_t.Text = kodbuf;
if (wagaflag == 1)
{
ilosc_t.Text = waga.Substring(0, 2) + "." + waga.Substring(2, 3);
}
ilosc_t.Focus();
cena_t.Text = "0";
cenasp_t.Text = "0";
stan_t.Text = "0";
vat_t.Text = "0";
}
}
}
public static void KeepPersonAliveOnBoard(string boardId, string personId)
{
string query = String.
Format("SELECT * FROM [boards] WHERE {0}=@boardId AND {1}=@personId", COL_BID, COL_PID);
string update = String.
Format("UPDATE [boards] SET {0}=@last WHERE {1}=@boardId AND {2}=@personId", COL_LAST, COL_BID, COL_PID);
string add = String.
Format("INSERT INTO [boards] ({0}, {1}, {2}) VALUES (@boardId, @personId, @last)", COL_BID, COL_PID, COL_LAST);
string updatePerson = String.
Format("UPDATE [persons] SET {0}=@last WHERE {1}=@personId", COL_LAST, COL_PID);
using (SqlCeConnection conn = new SqlCeConnection())
{
conn.ConnectionString = CommonState.ConnectionString;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
{
cmd.CommandText = query;
cmd.Parameters.Add("@boardId", boardId);
cmd.Parameters.Add("@personId", personId);
cmd.Parameters.Add("@last", CommonState.EpochTime);
cmd.Prepare();
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
cmd.CommandText = update;
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = add;
cmd.ExecuteNonQuery();
}
}
// now update person table
cmd.CommandText = updatePerson;
cmd.ExecuteNonQuery();
}
}
}
public void downloaddata()
{
try
{
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);
try
{
pcmn.Open();
}
catch (Exception ex)
{
Invoke((MethodInvoker)(() => exkom_t.Text += "Błąd połączenia z bazą\n"));
}
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand lastdatecmd = cn.CreateCommand();
lastdatecmd.CommandText = "SELECT MAX(datazmian) AS lastdate FROM dane ";
lastdatecmd.Prepare();
SqlCeDataReader lastdatedr = lastdatecmd.ExecuteReader();
while (lastdatedr.Read())
{
if (!lastdatedr.IsDBNull(0))
{
lastimport = lastdatedr.GetDateTime(0);
}
else
{
lastimport = Convert.ToDateTime("1900-01-01 00:00:00");
}
}
SqlCeCommand cmd3 = cn.CreateCommand();
cmd3.CommandText = "INSERT INTO bufordane (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 buforstany (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 delete = cn.CreateCommand();
delete.CommandText = "DROP TABLE bufordane";
delete.Prepare();
delete.ExecuteNonQuery();
SqlCeCommand cmd2 = new SqlCeCommand("CREATE TABLE bufordane (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, cenahurt nvarchar(10), cenaoryg nvarchar(10))", cn);
cmd2.ExecuteNonQuery();
delete = cn.CreateCommand();
delete.CommandText = "DROP TABLE buforstany";
delete.Prepare();
delete.ExecuteNonQuery();
cmd2 = cn.CreateCommand();
cmd2.CommandText = "CREATE TABLE buforstany (id int identity not null primary key, kod nvarchar(15), stan numeric(10,3), MagId int, Nazwa nvarchar(50), datazmian datetime)";
cmd2.Prepare();
cmd2.ExecuteNonQuery();
SqlCommand cmd = pcmn.CreateCommand();
cmd.CommandText = "SELECT Towar.Kod, Towar.Nazwa, SUM(Istw.StanMag) AS Expr1, Towar.CenaEw, Towar.CenaDet * (Towar.Stawka / 100 + 100) / 100 AS CenaD, Towar.Stawka / 100 AS Vat, Towar.Zmiana, Towar.CenaHurt * (Towar.Stawka / 100 + 100) / 100 AS CenaH, Towar.Opis2 AS CenaO FROM Towar INNER JOIN Istw ON Towar.TowId = Istw.TowId GROUP BY Towar.Kod, Towar.Nazwa, Towar.CenaEw, Towar.CenaDet * (Towar.Stawka / 100 + 100) / 100, Towar.Stawka / 100, Towar.Zmiana, Towar.Aktywny, Towar.CenaHurt * (Towar.Stawka / 100 + 100) / 100, Towar.Opis2 HAVING (Towar.Aktywny = 1) AND (Towar.Zmiana > @data) ORDER BY Towar.Zmiana";
cmd.Parameters.Add("@data", SqlDbType.DateTime);
cmd.Prepare();
cmd.Parameters["@data"].Value = lastimport;
SqlCommand cmd5 = pcmn.CreateCommand();
cmd5.CommandText = "SELECT KodDod.Kod, Towar.Nazwa, SUM(Istw.StanMag) AS Expr1, Towar.CenaEw, Towar.CenaDet * (Towar.Stawka / 100 + 100) / 100 AS CenaD, Towar.Stawka / 100 AS Vat, Towar.Zmiana, Towar.CenaHurt * (Towar.Stawka / 100 + 100) / 100 AS CenaH, Towar.Opis2 AS CenaO FROM Towar INNER JOIN Istw ON Towar.TowId = Istw.TowId INNER JOIN KodDod ON Towar.TowId = KodDod.TowId GROUP BY KodDod.Kod, Towar.Nazwa, Towar.CenaEw, Towar.CenaDet * (Towar.Stawka / 100 + 100) / 100, Towar.Stawka / 100, Towar.Zmiana, Towar.Aktywny, Towar.CenaHurt * (Towar.Stawka / 100 + 100) / 100, Towar.Opis2 HAVING (Towar.Aktywny = 1) AND (Towar.Zmiana > @data) ORDER BY Towar.Zmiana";
cmd5.Parameters.Add("@data", SqlDbType.DateTime);
cmd5.Prepare();
cmd5.Parameters["@data"].Value = lastimport;
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
// MessageBox.Show(sdr.GetString(0));
string kod = sdr.GetString(0);
string nazwa = sdr.GetString(1);
string stan = decimal.Round(sdr.GetDecimal(2), 2).ToString(nfi);
string cenazk = decimal.Round(sdr.GetDecimal(3), 2).ToString(nfi);
string cenasp = decimal.Round(sdr.GetDecimal(4), 2).ToString(nfi);
string vat = Convert.ToString(sdr.GetInt32(5));
DateTime impdatazmiany = sdr.GetDateTime(6);
string cenah = decimal.Round(sdr.GetDecimal(7), 2).ToString(nfi);
string cenao = sdr.GetString(7);
try
{
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 = impdatazmiany;
cmd3.Parameters["@ch"].Value = cenah;
cmd3.Parameters["@co"].Value = cenao;
cmd3.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
sdr.Close();
sdr = cmd5.ExecuteReader();
while (sdr.Read())
{
string kod = sdr.GetString(0);
string nazwa = sdr.GetString(1);
string stan = decimal.Round(sdr.GetDecimal(2), 2).ToString(nfi);
string cenazk = decimal.Round(sdr.GetDecimal(3), 2).ToString(nfi);
string cenasp = decimal.Round(sdr.GetDecimal(4), 2).ToString(nfi);
string vat = Convert.ToString(sdr.GetInt32(5));
DateTime impdatazmiany = sdr.GetDateTime(6);
string cenah = decimal.Round(sdr.GetDecimal(7), 2).ToString(nfi);
string cenao = sdr.GetString(7);
try
{
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 = impdatazmiany;
cmd3.Parameters["@ch"].Value = cenah;
cmd3.Parameters["@co"].Value = cenao;
cmd3.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
sdr.Close();
cmd2.CommandText = "SELECT Nazwa, MagId FROM magazyn";
cmd2.Prepare();
SqlCommand cmd9 = pcmn.CreateCommand();
cmd9.CommandText = "SELECT Towar.Kod, Magazyn.Nazwa, Istw.MagId, Istw.StanMag, Towar.Zmiana AS Expr1 FROM Towar INNER JOIN Istw ON Towar.TowId = Istw.TowId INNER JOIN Magazyn ON Istw.MagId = Magazyn.MagId WHERE (Towar.Aktywny = 1) AND Istw.MagId = @mag AND (Towar.Zmiana > @data) ORDER BY Towar.Zmiana";
cmd9.Parameters.Add("@mag", SqlDbType.Int);
cmd9.Parameters.Add("@data", SqlDbType.DateTime);
cmd9.Prepare();
cmd9.Parameters["@data"].Value = lastimport;
SqlCommand cmd8 = pcmn.CreateCommand();
cmd8.CommandText = "SELECT KodDod.Kod, Magazyn.Nazwa, Istw.MagId, Istw.StanMag, Towar.Zmiana 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 AND (Towar.Zmiana > @data) ORDER BY Towar.Zmiana";
cmd8.Parameters.Add("@mag", SqlDbType.Int);
cmd8.Parameters.Add("@data", SqlDbType.DateTime);
cmd8.Prepare();
cmd8.Parameters["@data"].Value = lastimport;
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;
SqlDataReader szdr = cmd9.ExecuteReader();
while (szdr.Read())
{
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();
}
szdr.Close();
szdr = cmd8.ExecuteReader();
while (szdr.Read())
{
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();
}
szdr.Close();
}
cedr.Close();
// datazmian = DateTime.Now;
// SqlCeCommand updatedata = cn.CreateCommand();
// updatedata.CommandText = "UPDATE opcje SET datazmian = ? WHERE id = 1";
// updatedata.Parameters.Add("@data", SqlDbType.DateTime);
//// updatedata.Prepare();
// updatedata.Parameters["@data"].Value = datazmian;
// updatedata.ExecuteNonQuery();
cn.Close();
pcmn.Close();
Invoke((MethodInvoker)(() => exkom_t.Text = "Dane pobrane z pc-market\n"));
}
catch
{
Invoke((MethodInvoker)(() => exkom_t.Text += "Błąd połączenia z bazą\n"));
}
}
private void InsertIntoDatabase(Tuple <int, Grid> toSave)
{
//BattleId, NumberLines, NumberColumns, IsGridShifted, MoveNumber,
//PawnLocations, NextPlayerToPlay, CurrentMessage, LastMove
try
{
using (DbCommand cmd = MyConnection.CreateCommand())
{
cmd.CommandText = SqlGridInsert;
Grid grid = toSave.Item2;
SqlCeCommand ceCmd = cmd as SqlCeCommand;
if (ceCmd != null)
{
ceCmd.Parameters.Add(new SqlCeParameter("@BattleId", SqlDbType.Int));
ceCmd.Parameters.Add(new SqlCeParameter("@NumberLines", SqlDbType.SmallInt));
ceCmd.Parameters.Add(new SqlCeParameter("@NumberColumns", SqlDbType.SmallInt));
ceCmd.Parameters.Add(new SqlCeParameter("@IsGridShifted", SqlDbType.NChar));
ceCmd.Parameters.Add(new SqlCeParameter("@MoveNumber", SqlDbType.SmallInt));
ceCmd.Parameters.Add(new SqlCeParameter("@PawnLocations", SqlDbType.NVarChar));
ceCmd.Parameters.Add(new SqlCeParameter("@NextPlayerToPlay", SqlDbType.SmallInt));
ceCmd.Parameters.Add(new SqlCeParameter("@CurrentMessage", SqlDbType.NVarChar));
ceCmd.Parameters.Add(new SqlCeParameter("@LastMove", SqlDbType.NVarChar));
ceCmd.Parameters["@IsGridShifted"].Size = 5;
ceCmd.Parameters["@PawnLocations"].Size = 500;
ceCmd.Parameters["@CurrentMessage"].Size = 100;
ceCmd.Parameters["@LastMove"].Size = 100;
ceCmd.Prepare();
ceCmd.Parameters["@BattleId"].Value = toSave.Item1;
ceCmd.Parameters["@NumberLines"].Value = grid.NumberLines;
ceCmd.Parameters["@NumberColumns"].Value = grid.NumberColumns;
ceCmd.Parameters["@IsGridShifted"].Value = grid.IsGridShiftedToDb();
ceCmd.Parameters["@MoveNumber"].Value = grid.MoveNumber;
ceCmd.Parameters["@PawnLocations"].Value = grid.PawnLocations.PawnLocationsToDb();
ceCmd.Parameters["@NextPlayerToPlay"].Value = grid.NextPlayerToPlay;
ceCmd.Parameters["@CurrentMessage"].Value = grid.CurrentMessage.CurrentMessageToDb();
ceCmd.Parameters["@LastMove"].Value = grid.LastMove.LastMoveToDb();
}
else
{
SqlCommand sqlCmd = cmd as SqlCommand;
if (sqlCmd != null)
{
sqlCmd.Parameters.Add("@BattleId", SqlDbType.Int);
sqlCmd.Parameters.Add("@NumberLines", SqlDbType.SmallInt);
sqlCmd.Parameters.Add("@NumberColumns", SqlDbType.SmallInt);
sqlCmd.Parameters.Add("@IsGridShifted", SqlDbType.NChar);
sqlCmd.Parameters.Add("@MoveNumber", SqlDbType.SmallInt);
sqlCmd.Parameters.Add("@PawnLocations", SqlDbType.NVarChar);
sqlCmd.Parameters.Add("@NextPlayerToPlay", SqlDbType.SmallInt);
sqlCmd.Parameters.Add("@CurrentMessage", SqlDbType.NVarChar);
sqlCmd.Parameters.Add("@LastMove", SqlDbType.NVarChar);
sqlCmd.Parameters["@IsGridShifted"].Size = 5;
sqlCmd.Parameters["@PawnLocations"].Size = 500;
sqlCmd.Parameters["@CurrentMessage"].Size = 100;
sqlCmd.Parameters["@LastMove"].Size = 100;
sqlCmd.Parameters["@BattleId"].Value = toSave.Item1;
sqlCmd.Parameters["@NumberLines"].Value = grid.NumberLines;
sqlCmd.Parameters["@NumberColumns"].Value = grid.NumberColumns;
sqlCmd.Parameters["@IsGridShifted"].Value = grid.IsGridShiftedToDb();
sqlCmd.Parameters["@MoveNumber"].Value = grid.MoveNumber;
sqlCmd.Parameters["@PawnLocations"].Value = grid.PawnLocations.PawnLocationsToDb();
sqlCmd.Parameters["@NextPlayerToPlay"].Value = grid.NextPlayerToPlay;
sqlCmd.Parameters["@CurrentMessage"].Value = grid.CurrentMessage.CurrentMessageToDb();
sqlCmd.Parameters["@LastMove"].Value = grid.LastMove.LastMoveToDb();
sqlCmd.Prepare();
}
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
catch (Exception e)
{
throw new DAOException(String.Format("Error while creating new battle : {0}", e.Message));
}
}
public static List<string> ListPersonOnBoard(string boardId)
{
string query = String.Format("SELECT * FROM [boards] WHERE {0}=@boardId AND {1} > {2}",
COL_BID, COL_LAST, CommonState.EpochTime - Configure.CONNECTOIN_TIMEOUT);
string query2 =
"SELECT persons.person_name " +
"FROM persons INNER JOIN " +
" boards ON boards.person_id = persons.person_id " +
"WHERE boards.board_id=@boardId " +
" AND boards.person_lastactivity > " +
(CommonState.EpochTime - Configure.CONNECTOIN_TIMEOUT).ToString();
List<string> list = new List<string>();
using (SqlCeConnection conn = new SqlCeConnection())
{
conn.ConnectionString = CommonState.ConnectionString;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
{
cmd.CommandText = query2;
cmd.Parameters.Add("@boardId", boardId);
cmd.Prepare();
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add((string)reader[COL_PNAME]);
}
}
}
}
return list;
}
//Insert
public static string InsertStavke(DataTable DT)
{
if (classSQL.remoteConnectionString == "")
{
if (classSQL.connection.State.ToString() == "Closed")
{
classSQL.connection.Open();
}
SqlCeCommand nonqueryCommand = classSQL.connection.CreateCommand();
try
{
nonqueryCommand.CommandText = "INSERT INTO kalkulacija_stavke (kolicina, fak_cijena,rabat,prijevoz,carina,marza_postotak,porez,posebni_porez,broj,sifra,vpc,id_skladiste,porez_potrosnja) VALUES (@kolicina, @fak_cijena,@rabat,@prijevoz,@carina,@marza_postotak,@porez,@posebni_porez,@broj,@sifra,@vpc,@id_skladiste,@porez_potrosnja)";
nonqueryCommand.Parameters.Add("@kolicina", SqlDbType.NVarChar, 20);
nonqueryCommand.Parameters.Add("@fak_cijena", SqlDbType.Money, 8);
nonqueryCommand.Parameters.Add("@rabat", SqlDbType.NVarChar, 20);
nonqueryCommand.Parameters.Add("@prijevoz", SqlDbType.Money, 8);
nonqueryCommand.Parameters.Add("@carina", SqlDbType.Money, 8);
nonqueryCommand.Parameters.Add("@marza_postotak", SqlDbType.NVarChar, 20);
nonqueryCommand.Parameters.Add("@porez", SqlDbType.NVarChar, 20);
nonqueryCommand.Parameters.Add("@posebni_porez", SqlDbType.Money, 8);
nonqueryCommand.Parameters.Add("@broj", SqlDbType.BigInt, 8);
nonqueryCommand.Parameters.Add("@sifra", SqlDbType.NVarChar, 20);
nonqueryCommand.Parameters.Add("@vpc", SqlDbType.Money, 8);
nonqueryCommand.Parameters.Add("@id_skladiste", SqlDbType.Int);
nonqueryCommand.Parameters.Add("@porez_potrosnja", SqlDbType.Decimal);
nonqueryCommand.Prepare();
for (int i = 0; i < DT.Rows.Count; i++)
{
nonqueryCommand.Parameters["@kolicina"].Value = DT.Rows[i]["kolicina"];
nonqueryCommand.Parameters["@fak_cijena"].Value = DT.Rows[i]["fak_cijena"];
nonqueryCommand.Parameters["@rabat"].Value = DT.Rows[i]["rabat"];
nonqueryCommand.Parameters["@prijevoz"].Value = DT.Rows[i]["prijevoz"];
nonqueryCommand.Parameters["@carina"].Value = DT.Rows[i]["carina"];
nonqueryCommand.Parameters["@marza_postotak"].Value = DT.Rows[i]["marza_postotak"];
nonqueryCommand.Parameters["@porez"].Value = DT.Rows[i]["porez"];
nonqueryCommand.Parameters["@posebni_porez"].Value = DT.Rows[i]["posebni_porez"];
nonqueryCommand.Parameters["@broj"].Value = DT.Rows[i]["broj"];
nonqueryCommand.Parameters["@sifra"].Value = DT.Rows[i]["sifra"];
nonqueryCommand.Parameters["@vpc"].Value = DT.Rows[i]["vpc"];
nonqueryCommand.Parameters["@id_skladiste"].Value = DT.Rows[i]["id_skladiste"];
nonqueryCommand.Parameters["@porez_potrosnja"].Value = DT.Rows[i]["porez_potrosnja"];
nonqueryCommand.ExecuteNonQuery();
}
}
catch (SqlCeException ex)
{
classSQL.connection.Close();
return(ex.ToString());
}
finally
{
classSQL.connection.Close();
}
return("");
}
else
{
if (classSQL.remoteConnection.State.ToString() == "Closed")
{
classSQL.remoteConnection.Open();
}
NpgsqlCommand nonqueryCommand = classSQL.remoteConnection.CreateCommand();
try
{
for (int i = 0; i < DT.Rows.Count; i++)
{
string sql = "INSERT INTO kalkulacija_stavke (kolicina, fak_cijena,rabat,prijevoz,carina,marza_postotak,porez,posebni_porez,broj,sifra,vpc,id_skladiste,porez_potrosnja) VALUES " +
" (" +
"'" + DT.Rows[i]["kolicina"].ToString() + "'," +
"'" + DT.Rows[i]["fak_cijena"].ToString().Replace(".", ",") + "'," +
"'" + DT.Rows[i]["rabat"].ToString() + "'," +
"'" + DT.Rows[i]["prijevoz"].ToString().Replace(".", ",") + "'," +
"'" + DT.Rows[i]["carina"].ToString().Replace(".", ",") + "'," +
"'" + DT.Rows[i]["marza_postotak"].ToString() + "'," +
"'" + DT.Rows[i]["porez"].ToString() + "'," +
"'" + DT.Rows[i]["posebni_porez"].ToString().Replace(".", ",") + "'," +
"'" + DT.Rows[i]["broj"].ToString() + "'," +
"'" + DT.Rows[i]["sifra"].ToString() + "'," +
"'" + DT.Rows[i]["vpc"].ToString().Replace(",", ".") + "'," +
"'" + DT.Rows[i]["id_skladiste"].ToString().Replace(".", ",") + "'," +
"'" + DT.Rows[i]["porez_potrosnja"].ToString().Replace(",", ".") + "'" +
")";
NpgsqlCommand comm = new NpgsqlCommand(sql, classSQL.remoteConnection);
comm.ExecuteNonQuery();
}
}
catch (NpgsqlException ex)
{
classSQL.connection.Close();
return(ex.ToString());
}
finally
{
classSQL.connection.Close();
}
return("");
}
}
// Deletes the database (if it exists) and creates a new empty one.
public static bool CreateDatabase()
{
Trace.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
try
{
if (!Directory.Exists(DatabaseFolder))
{
Directory.CreateDirectory(DatabaseFolder);
}
else
{
File.Delete(DatabasePath);
}
SqlCeEngine engine = new SqlCeEngine(connString);
engine.CreateDatabase();
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return(false);
}
bool success = true;
using (SqlCeConnection conn = new SqlCeConnection(connString))
{
conn.Open();
using (SqlCeCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = @"CREATE TABLE Accounts (AccountName NVARCHAR(50) PRIMARY KEY, EmailAddress NVARCHAR(50), ImapLoginName NVARCHAR(50), ImapLoginPassword NVARCHAR(50), ImapServerName NVARCHAR(50), ImapPortNumber INT, SmtpLoginName NVARCHAR(50), SmtpLoginPassword NVARCHAR(50), SmtpServerName NVARCHAR(50), SmtpPortNumber INT);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE Mailboxes (AccountName NVARCHAR(50) REFERENCES Accounts(AccountName) ON DELETE CASCADE ON UPDATE CASCADE, Path NVARCHAR(100), Separator NVARCHAR(5), UidNext INT, UidValidity INT, FlagString NVARCHAR(500), PRIMARY KEY (AccountName, Path));";
cmd.ExecuteNonQuery();
// NVARCHAR would not work for the Recipient column because some email messages have many recipients. I've seen messages with 'Recipient' header containing up to 26000+ characters.
cmd.CommandText = @"CREATE TABLE Messages (AccountName NVARCHAR(50), MailboxPath NVARCHAR(100), Uid INT, Subject NVARCHAR(500), DateString NVARCHAR(500), Sender NVARCHAR(500), Recipient NTEXT, FlagString NVARCHAR(500), HasAttachment BIT, Body NTEXT, PRIMARY KEY (AccountName, MailboxPath, Uid), FOREIGN KEY (AccountName, MailboxPath) REFERENCES Mailboxes(AccountName, Path) ON DELETE CASCADE ON UPDATE CASCADE);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE DbInfo (EntryName NVARCHAR(50) PRIMARY KEY, EntryValue NVARCHAR(500));";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE Contacts (AccountName NVARCHAR(50), EmailAddress NVARCHAR(50));";
cmd.ExecuteNonQuery();
cmd.CommandText = @"INSERT INTO DbInfo VALUES('SchemaVersion', @SchemaVersion);";
cmd.Parameters.AddWithValue("@SchemaVersion", schemaVersion);
cmd.Prepare();
if (cmd.ExecuteNonQuery() != 1)
{
success = false;
}
}
catch (Exception ex)
{
Debug.WriteLine("Unable to create database.\n\n" + ex.Message);
success = false;
}
finally
{
conn.Close();
}
}
}
return(success);
}
/// <summary>
/// Prepare the SQL-statements for every static SQL-Command.
/// </summary>
private void PrepareStatements()
{
// Creates the commands.
DoesNewsItemExistCmd1 = DatabaseConnection.CreateCommand();
DoesNewsItemExistCmd2 = DatabaseConnection.CreateCommand();
GetIdfCmd1 = DatabaseConnection.CreateCommand();
GetIdfCmd2 = DatabaseConnection.CreateCommand();
StoreTermsCmd1 = DatabaseConnection.CreateCommand();
StoreTermsCmd2 = DatabaseConnection.CreateCommand();
FindCategoryCmd1 = DatabaseConnection.CreateCommand();
FindCategoryCmd2 = DatabaseConnection.CreateCommand();
FindCategoryCmd3 = DatabaseConnection.CreateCommand();
StoreTfValuesCmd = DatabaseConnection.CreateCommand();
GetNewsSourceIdCmd = DatabaseConnection.CreateCommand();
StoreNewsItemCmd1 = DatabaseConnection.CreateCommand();
StoreNewsItemCmd2 = DatabaseConnection.CreateCommand();
StoreNewsItemCmd3 = DatabaseConnection.CreateCommand();
AddNewsSourceCmd = DatabaseConnection.CreateCommand();
GetUniqueTermCountCmd = DatabaseConnection.CreateCommand();
GetNewsSourcesCmd1 = DatabaseConnection.CreateCommand();
GetNewsSourcesCmd2 = DatabaseConnection.CreateCommand();
RemoveNewsSourceCmd1 = DatabaseConnection.CreateCommand();
RemoveNewsSourceCmd2 = DatabaseConnection.CreateCommand();
SetNewsInterestingStatusCmd = DatabaseConnection.CreateCommand();
SetNewsReadStatusCmd = DatabaseConnection.CreateCommand();
SetReadingSpeedCmd = DatabaseConnection.CreateCommand();
UpdateNewsSourceCmd = DatabaseConnection.CreateCommand();
UpdateIdfValuesCmd = DatabaseConnection.CreateCommand();
GetReadingSpeedCmd = DatabaseConnection.CreateCommand();
GetTfIdfVectorCmd = DatabaseConnection.CreateCommand();
MarkCategoryInterestingCmd = DatabaseConnection.CreateCommand();
AddCategoryCmd1 = DatabaseConnection.CreateCommand();
AddCategoryCmd2 = DatabaseConnection.CreateCommand();
RemoveCategoryCmd1 = DatabaseConnection.CreateCommand();
RemoveCategoryCmd2 = DatabaseConnection.CreateCommand();
RemoveCategoryCmd3 = DatabaseConnection.CreateCommand();
GetCategoriesCmd = DatabaseConnection.CreateCommand();
UpdateTermCountTableCmd = DatabaseConnection.CreateCommand();
// Prepare DoesNewsItemExistCmd statements.
DoesNewsItemExistCmd1.CommandText = "SELECT id FROM news WHERE guid=?";
DoesNewsItemExistCmd1.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
DoesNewsItemExistCmd1.Parameters["p1"].Size = NEWS_GUID_SIZE;
DoesNewsItemExistCmd1.Prepare();
DoesNewsItemExistCmd2.CommandText = "DELETE FROM news_term_joins WHERE news_id=?";
DoesNewsItemExistCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
DoesNewsItemExistCmd2.Prepare();
// Prepare GetIdfCmd statements.
GetIdfCmd1.CommandText = "SELECT COUNT(id) FROM news";
GetIdfCmd1.Prepare();
GetIdfCmd2.CommandText = "SELECT COUNT(news_id) FROM news_term_joins WHERE term_id=?";
GetIdfCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
GetIdfCmd2.Prepare();
// Prepare StoreTermsCmd statements.
StoreTermsCmd1.CommandText = "INSERT INTO terms (term, idf, created_at, updated_at) " +
"VALUES (?, ?, ?, ?)";
StoreTermsCmd1.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
StoreTermsCmd1.Parameters["p1"].Size = TERMS_TERM_SIZE;
StoreTermsCmd1.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Float));
StoreTermsCmd1.Parameters.Add(new SqlCeParameter("p3", SqlDbType.DateTime));
StoreTermsCmd1.Parameters.Add(new SqlCeParameter("p4", SqlDbType.DateTime));
StoreTermsCmd1.Prepare();
StoreTermsCmd2.CommandText = "UPDATE terms SET term=?, idf=?, updated_at=? "
+ "WHERE id=?";
StoreTermsCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
StoreTermsCmd2.Parameters["p1"].Size = TERMS_TERM_SIZE;
StoreTermsCmd2.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Float));
StoreTermsCmd2.Parameters.Add(new SqlCeParameter("p3", SqlDbType.DateTime));
StoreTermsCmd2.Parameters.Add(new SqlCeParameter("p4", SqlDbType.Int));
StoreTermsCmd2.Prepare();
// Prepare FindCategoryCmd statements.
FindCategoryCmd1.CommandText = "SELECT id, name FROM categories";
FindCategoryCmd1.Prepare();
FindCategoryCmd2.CommandText = "SELECT COUNT(id) FROM news WHERE category_id=?";
FindCategoryCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
FindCategoryCmd2.Prepare();
FindCategoryCmd3.CommandText =
"SELECT terms.term, term_counts.interesting_count, " +
"term_counts.uninteresting_count, term_counts.unknown_count " +
"FROM term_counts " +
"INNER JOIN terms ON term_counts.term_id=terms.id " +
"WHERE category_id=?";
FindCategoryCmd3.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
FindCategoryCmd3.Prepare();
// Prepare StoreTfValuesCmd statements.
StoreTfValuesCmd.CommandText = "INSERT INTO news_term_joins(news_id, term_id, tf, median_index, " +
"created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)";
StoreTfValuesCmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
StoreTfValuesCmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Int));
StoreTfValuesCmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.Int));
StoreTfValuesCmd.Parameters.Add(new SqlCeParameter("p4", SqlDbType.Int));
StoreTfValuesCmd.Parameters.Add(new SqlCeParameter("p5", SqlDbType.DateTime));
StoreTfValuesCmd.Parameters.Add(new SqlCeParameter("p6", SqlDbType.DateTime));
StoreTfValuesCmd.Prepare();
// Prepare GetNewsSourceIdCmd statements.
GetNewsSourceIdCmd.CommandText = "SELECT id FROM news_sources WHERE name=?";
GetNewsSourceIdCmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
GetNewsSourceIdCmd.Parameters["p1"].Size = NEWS_SOURCES_NAME_SIZE;
GetNewsSourceIdCmd.Prepare();
// Prepare StoreNewsItemCmd statements.
StoreNewsItemCmd1.CommandText = "UPDATE news SET " +
"guid=?, title=?, summary=?, author=?, publisher=?, " +
"publisher_date=?, url=?, category_id=?, " +
"news_source_id=?, term_count=?, updated_at=? " + "WHERE id=?";
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
StoreNewsItemCmd1.Parameters["p1"].Size = NEWS_GUID_SIZE;
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p2", SqlDbType.NVarChar));
StoreNewsItemCmd1.Parameters["p2"].Size = NEWS_TITLE_SIZE;
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p3", SqlDbType.NText));
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p4", SqlDbType.NVarChar));
StoreNewsItemCmd1.Parameters["p4"].Size = NEWS_AUTHOR_SIZE;
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p5", SqlDbType.NVarChar));
StoreNewsItemCmd1.Parameters["p5"].Size = NEWS_PUBLISHER_SIZE;
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p6", SqlDbType.DateTime));
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p7", SqlDbType.NVarChar));
StoreNewsItemCmd1.Parameters["p7"].Size = NEWS_URL_SIZE;
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p8", SqlDbType.Int));
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p9", SqlDbType.Int));
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p10", SqlDbType.Int));
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p11", SqlDbType.DateTime));
StoreNewsItemCmd1.Parameters.Add(new SqlCeParameter("p12", SqlDbType.Int));
StoreNewsItemCmd1.Prepare();
StoreNewsItemCmd2.CommandText = "INSERT INTO news(" +
"guid, title, summary, author, publisher, publisher_date, " +
"is_read, url, category_id, news_source_id, term_count, created_at, updated_at) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
StoreNewsItemCmd2.Parameters["p1"].Size = NEWS_GUID_SIZE;
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p2", SqlDbType.NVarChar));
StoreNewsItemCmd2.Parameters["p2"].Size = NEWS_TITLE_SIZE;
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p3", SqlDbType.NText));
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p4", SqlDbType.NVarChar));
StoreNewsItemCmd2.Parameters["p4"].Size = NEWS_AUTHOR_SIZE;
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p5", SqlDbType.NVarChar));
StoreNewsItemCmd2.Parameters["p5"].Size = NEWS_PUBLISHER_SIZE;
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p6", SqlDbType.DateTime));
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p7", SqlDbType.Bit));
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p8", SqlDbType.NVarChar));
StoreNewsItemCmd2.Parameters["p8"].Size = NEWS_URL_SIZE;
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p9", SqlDbType.Int));
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p10", SqlDbType.Int));
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p11", SqlDbType.Int));
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p12", SqlDbType.DateTime));
StoreNewsItemCmd2.Parameters.Add(new SqlCeParameter("p13", SqlDbType.DateTime));
StoreNewsItemCmd2.Prepare();
StoreNewsItemCmd3.CommandText = "SELECT TOP(1) id FROM news WHERE guid=?";
StoreNewsItemCmd3.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
StoreNewsItemCmd3.Parameters["p1"].Size = NEWS_GUID_SIZE;
StoreNewsItemCmd3.Prepare();
// Prepare AddNewsSourceCmd statements.
AddNewsSourceCmd.CommandText = "INSERT INTO news_sources (name, url, is_blocked, " +
"content_expiration_time, created_at, updated_at) VALUES " +
"(?, ?, ?, ?, ?, ?)";
AddNewsSourceCmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
AddNewsSourceCmd.Parameters["p1"].Size = NEWS_SOURCES_NAME_SIZE;
AddNewsSourceCmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.NVarChar));
AddNewsSourceCmd.Parameters["p1"].Size = NEWS_SOURCES_URL_SIZE;
AddNewsSourceCmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.Bit));
AddNewsSourceCmd.Parameters.Add(new SqlCeParameter("p4", SqlDbType.DateTime));
AddNewsSourceCmd.Parameters.Add(new SqlCeParameter("p5", SqlDbType.DateTime));
AddNewsSourceCmd.Parameters.Add(new SqlCeParameter("p6", SqlDbType.DateTime));
AddNewsSourceCmd.Prepare();
// Prepare GetUniqueTermCountCmd statements.
GetUniqueTermCountCmd.CommandText = "SELECT COUNT(id) FROM terms";
GetUniqueTermCountCmd.Prepare();
// Prepare GetNewsSourcesCmd statements.
GetNewsSourcesCmd1.CommandText = "SELECT TOP(?) id, name, url, is_blocked, content_expiration_time " +
"FROM news_sources";
GetNewsSourcesCmd1.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
GetNewsSourcesCmd1.Prepare();
GetNewsSourcesCmd2.CommandText = "SELECT id, name, url, is_blocked, content_expiration_time " +
"FROM news_sources ORDER BY id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
GetNewsSourcesCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
GetNewsSourcesCmd2.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Int));
GetNewsSourcesCmd2.Prepare();
// Prepare RemoveNewsSourceCmd statements.
RemoveNewsSourceCmd1.CommandText = "UPDATE news SET news_source_id=? WHERE news_source_id=?";
RemoveNewsSourceCmd1.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
RemoveNewsSourceCmd1.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Int));
RemoveNewsSourceCmd1.Prepare();
RemoveNewsSourceCmd2.CommandText = "DELETE FROM news_sources WHERE id=?";
RemoveNewsSourceCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
RemoveNewsSourceCmd2.Prepare();
// Prepare SetNewsInterestingStatusCmd statements.
SetNewsInterestingStatusCmd.CommandText =
"UPDATE news SET user_found_interesting=?, updated_at=? " +
"WHERE id=?";
SetNewsInterestingStatusCmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Bit));
SetNewsInterestingStatusCmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.DateTime));
SetNewsInterestingStatusCmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.Int));
SetNewsInterestingStatusCmd.Prepare();
// Prepare SetNewsReadStatusCmd statements.
SetNewsReadStatusCmd.CommandText = "UPDATE news SET is_read=?, updated_at=? " +
"WHERE id=?";
SetNewsReadStatusCmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Bit));
SetNewsReadStatusCmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.DateTime));
SetNewsReadStatusCmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.Int));
SetNewsReadStatusCmd.Prepare();
// Prepare SetReadingSpeedCmd statements.
SetReadingSpeedCmd.CommandText = "UPDATE user_info SET reading_speed=?, updated_at=?";
SetReadingSpeedCmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
SetReadingSpeedCmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.DateTime));
SetReadingSpeedCmd.Prepare();
// Prepare UpdateNewsSourceCmd statements.
UpdateNewsSourceCmd.CommandText = "UPDATE news_sources SET name=?, url=?, is_blocked=?, " +
"content_expiration_time=?, updated_at=? " +
"WHERE id=?";
UpdateNewsSourceCmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
UpdateNewsSourceCmd.Parameters["p1"].Size = NEWS_SOURCES_NAME_SIZE;
UpdateNewsSourceCmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.NVarChar));
UpdateNewsSourceCmd.Parameters["p1"].Size = NEWS_SOURCES_URL_SIZE;
UpdateNewsSourceCmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.Bit));
UpdateNewsSourceCmd.Parameters.Add(new SqlCeParameter("p4", SqlDbType.DateTime));
UpdateNewsSourceCmd.Parameters.Add(new SqlCeParameter("p5", SqlDbType.DateTime));
UpdateNewsSourceCmd.Parameters.Add(new SqlCeParameter("p6", SqlDbType.Int));
UpdateNewsSourceCmd.Prepare();
// Prepare UpdateIdfValuesCmd statements.
UpdateIdfValuesCmd.CommandText = "SELECT id, term FROM terms";
UpdateIdfValuesCmd.Prepare();
// Prepare GetReadingSpeedCmd statements.
GetReadingSpeedCmd.CommandText = "SELECT TOP(1) reading_speed FROM user_info";
GetReadingSpeedCmd.Prepare();
// Prepare GetTfIdfVectorCmd statements.
GetTfIdfVectorCmd.CommandText = "SELECT c.t_count, t.id, nt.tf, t.idf " +
"FROM news_term_joins nt " +
"INNER JOIN terms t ON t.id = nt.term_id " +
"CROSS JOIN ( SELECT MAX( id ) AS t_count FROM terms ) c " +
"WHERE nt.news_id = ? ";
GetTfIdfVectorCmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
GetTfIdfVectorCmd.Prepare();
// Prepare MarkCategoryInterestingCmd statements.
MarkCategoryInterestingCmd.CommandText =
"UPDATE news SET user_found_interesting=?, updated_at=? " +
"WHERE category_id=?";
MarkCategoryInterestingCmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
MarkCategoryInterestingCmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.DateTime));
MarkCategoryInterestingCmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.Int));
MarkCategoryInterestingCmd.Prepare();
// Prepare AddCategoryCmd statements.
AddCategoryCmd1.CommandText =
"INSERT INTO categories (name, created_at, updated_at) VALUES" +
"(?, ?, ?)";
AddCategoryCmd1.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
AddCategoryCmd1.Parameters["p1"].Size = CATEGORIES_NAME_SIZE;
AddCategoryCmd1.Parameters.Add(new SqlCeParameter("p2", SqlDbType.DateTime));
AddCategoryCmd1.Parameters.Add(new SqlCeParameter("p3", SqlDbType.DateTime));
AddCategoryCmd1.Prepare();
AddCategoryCmd2.CommandText = "SELECT @@IDENTITY";
AddCategoryCmd2.Prepare();
// Prepare RemoveCategoryCmd statements.
RemoveCategoryCmd1.CommandText =
"DELETE FROM news_term_joins WHERE news_id IN " +
"(SELECT id FROM news WHERE category_id=?)";
RemoveCategoryCmd1.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
RemoveCategoryCmd1.Prepare();
RemoveCategoryCmd2.CommandText = "DELETE FROM news WHERE category_id=?";
RemoveCategoryCmd2.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Int));
RemoveCategoryCmd2.Prepare();
RemoveCategoryCmd3.CommandText = "DELETE FROM categories WHERE id=?";
RemoveCategoryCmd3.Parameters.Add(new SqlCeParameter("p3", SqlDbType.Int));
RemoveCategoryCmd3.Prepare();
// Prepare GetCategoriesCmd statements.
GetCategoriesCmd.CommandText = "SELECT id, name FROM categories";
GetCategoriesCmd.Prepare();
UpdateTermCountTableCmd.CommandText = "INSERT INTO term_counts " +
"(term_id, category_id, interesting_count, uninteresting_count, unknown_count) " +
"SELECT news_term_joins.term_id, n.category_id, " +
"SUM(CASE WHEN user_found_interesting = 1 THEN news_term_joins.tf ELSE 0 END) " +
"AS i_occurrences, " +
"SUM(CASE WHEN user_found_interesting = 0 THEN news_term_joins.tf ELSE 0 END) " +
"AS ni_occurrences, " +
"SUM(CASE WHEN user_found_interesting IS NULL THEN news_term_joins.tf ELSE 0 END) " +
"AS unk_occurrences " +
"FROM news_term_joins INNER JOIN " +
"news AS n ON n.id = news_term_joins.news_id " +
"GROUP BY n.category_id, news_term_joins.term_id";
UpdateTermCountTableCmd.Prepare();
}
private static Person FindPersonByACol(string colName, string colVal)
{
if (String.IsNullOrEmpty(colName) || String.IsNullOrEmpty(colVal))
{
return null;
}
string query = String.Format("SELECT * FROM [persons] WHERE {0}=@colVal", colName);
Person p = null;
using (SqlCeConnection conn = new SqlCeConnection())
{
conn.ConnectionString = CommonState.ConnectionString;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
{
cmd.CommandText = query;
cmd.Parameters.Add("@colVal", colVal);
cmd.Prepare();
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
p = new Person();
p.Id = (string)reader[COL_ID];
p.Name = (string)reader[COL_NAME];
p.LastActivity = (long)reader[COL_LAST];
}
}
}
}
return p;
}
private static bool DeletePersonByACol(string colName, string colVal)
{
string delete = String.Format("DELETE FROM [persons] WHERE {0}=@colVal", colName);
using (SqlCeConnection conn = new SqlCeConnection())
{
conn.ConnectionString = CommonState.ConnectionString;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
{
cmd.CommandText = delete;
cmd.Parameters.Add("@colVal", colVal);
cmd.Prepare();
cmd.ExecuteNonQuery();
// TODO: check if success
}
}
return true;
}
public static bool SignUp(string name, string id)
{
using (SqlCeConnection conn = new SqlCeConnection())
{
string add = String.Format("INSERT INTO [persons] ({0}, {1}, {2}) VALUES (@name, @id, @last)",
COL_NAME, COL_ID, COL_LAST);
conn.ConnectionString = CommonState.ConnectionString;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
{
cmd.CommandText = add;
cmd.Parameters.Add("@id", id);
cmd.Parameters.Add("@name", name);
cmd.Parameters.Add("@last", CommonState.EpochTime);
cmd.Prepare();
cmd.ExecuteNonQuery();
// TODO: check if success
}
}
return true;
}
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 = "";
}
}
private void pcmarket_schema_export()
{
StreamWriter sw = new StreamWriter(@ufile + "\\picedi_" + DateTime.Now.Ticks.ToString() + ".txt");
string index = Convert.ToString(selectedRow.Cells[0].Value);
string nrdok = Convert.ToString(selectedRow.Cells[4].Value);
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT * FROM fedihead WHERE id = ?";
cmd.Parameters.Add("@d", SqlDbType.Int);
cmd.Prepare();
cmd.Parameters["@d"].Value = int.Parse(index);
SqlCeDataReader dr = cmd.ExecuteReader();
System.Globalization.NumberFormatInfo nfi = new System.Globalization.NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
while (dr.Read())
{
sw.WriteLine("TypPolskichLiter:" + dr.GetString(2));
sw.WriteLine("TypDok:" + dr.GetString(3));
sw.WriteLine("NrDok:" + dr.GetString(4));
sw.WriteLine("Data:" + dr.GetString(5));
sw.WriteLine("DataRealizacji:" + dr.GetString(6));
sw.WriteLine("Magazyn:" + dr.GetString(7));
sw.WriteLine("SposobPlatn:" + dr.GetString(8));
sw.WriteLine("TerminPlatn:" + dr.GetString(9));
sw.WriteLine("IndeksCentralny:" + dr.GetString(10));
sw.WriteLine("NazwaWystawcy:" + dr.GetString(11));
sw.WriteLine("AdresWystawcy:" + dr.GetString(12));
sw.WriteLine("KodWystawcy:" + dr.GetString(13));
sw.WriteLine("MiastoWystawcy:" + dr.GetString(14));
sw.WriteLine("UlicaWystawcy:" + dr.GetString(15));
sw.WriteLine("NIPWystawcy:" + dr.GetString(16));
sw.WriteLine("BankWystawcy:" + dr.GetString(17));
sw.WriteLine("KontoWystawcy:" + dr.GetString(18));
sw.WriteLine("TelefonWystawcy:" + dr.GetString(19));
sw.WriteLine("NrWystawcyWSieciSklepow:" + dr.GetString(20));
sw.WriteLine("NazwaOdbiorcy:" + dr.GetString(21));
sw.WriteLine("AdresOdbiorcy:" + dr.GetString(22));
sw.WriteLine("KodOdbiorcy:" + dr.GetString(23));
sw.WriteLine("MiastoOdbiorcy:" + dr.GetString(24));
sw.WriteLine("UlicaOdbiorcy:" + dr.GetString(25));
sw.WriteLine("NIPOdbiorcy:" + dr.GetString(26));
sw.WriteLine("BankOdbiorcy:" + dr.GetString(27));
sw.WriteLine("KontoOdbiorcy:" + dr.GetString(28));
sw.WriteLine("TelefonOdbiorcy:" + dr.GetString(29));
sw.WriteLine("NrOdbiorcyWSieciSklepow:" + dr.GetString(30));
sw.WriteLine("DoZaplaty:" + dr.GetString(31));
}
cmd.Dispose();
dr.Dispose();
cmd = cn.CreateCommand();
cmd.CommandText = "SELECT * FROM fedibody WHERE NrDok = ?";
cmd.Parameters.Add("@d", SqlDbType.NVarChar, 30);
cmd.Prepare();
cmd.Parameters["@d"].Value = nrdok;
dr = cmd.ExecuteReader();
while (dr.Read())
{
sw.WriteLine("Linia:Nazwa{" + dr.GetString(2) + "}Kod{" + dr.GetString(3) + "}Vat{" + dr.GetString(4) + "}Jm{" + dr.GetString(5) + "}Asortyment{" + dr.GetString(6) + "}Sww{}PKWiU{}Ilosc{" + dr.GetString(9) + "}Cena{" + dr.GetString(10) + "}Wartosc{}IleWOpak{" + dr.GetString(12) + "}CenaSp{" + dr.GetString(13) + "}");
}
cn.Close();
sw.Close();
}
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();
}
}
}
private SqlCeCommand DoCreateCommandUpdateHistory()
{
lock (_connProfile)
{
SqlCeCommand cmd = new SqlCeCommand(PracticeStrings.SQL_UPDATE_HISTORY, _connProfile);
cmd.Parameters.Add(new SqlCeParameter("VocabularyId", SqlDbType.UniqueIdentifier));
cmd.Parameters.Add(new SqlCeParameter("MeaningId", SqlDbType.UniqueIdentifier));
cmd.Parameters.Add(new SqlCeParameter("ActionId", SqlDbType.NVarChar));
cmd.Prepare();
return cmd;
}
}
private void WriteLine()
{
string stop = "start";
if (lic == 0)
{
int demo = 0;
SqlCeCommand licek = cn.CreateCommand();
licek.CommandText = "Select Count(id) From bufor";
SqlCeDataReader dr = licek.ExecuteReader();
while (dr.Read())
{
demo = dr.GetInt32(0);
}
if (demo >= 5)
{
stop = "stop";
}
}
if (stop == "stop")
{
MessageBox.Show("Wersja Demo pozwala na wprowadzenie 5 pozycji");
}
else
{
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("@csp", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@v", SqlDbType.NVarChar, 10);
cmd.Parameters["@d"].Value = int.Parse(index);
cmd.Parameters["@k"].Value = kod_t.Text;
cmd.Parameters["@n"].Value = nazwa_t.Text;
cmd.Parameters["@cz"].Value = cena_t.Text;
cmd.Parameters["@i"].Value = ilosc_t.Text;
cmd.Parameters["@s"].Value = stan_t.Text;
cmd.Parameters["@csp"].Value = cenasp_t.Text;
cmd.Parameters["@v"].Value = vat_t.Text;
cmd.Prepare();
cmd.ExecuteNonQuery();
kod_t.Text = null;
nazwa_t.Text = null;
cena_t.Text = null;
ilosc_t.Text = null;
zliczono_t.Text = null;
stan_t.Text = null;
cenasp_t.Text = null;
vat_t.Text = null;
kod_t.Focus();
}
}
private void button1_Click(object sender, RoutedEventArgs e)
{
int recordCount = 10000;
using (PopupForm p = new PopupForm())
{
p.X_NotifyStr = "Record Count";
if (p.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
int outValue;
if (!int.TryParse(p.X_Result, out outValue))
{
return;
}
recordCount = outValue;
}
else
{
return;
}
}
string testDb = DateTime.Now.Second.ToString() + "test.sdf";
string testTable = "testTable";
if (!App.MainEngineer.CreateDatabase(new LoginInfo_SSCE()
{
DbName = testDb
}))
{
return;
}
App.MainEngineer.Open(new CoreEA.LoginInfo.LoginInfo_SSCE()
{
DbName = testDb, Pwd = "", IsEncrypted = false
});
if (!App.MainEngineer.IsOpened)
{
throw new Exception("Can't Open");
}
//List<CreateTableArgs> argsList=new List<CreateTableArgs>();
//CreateTableArgs args=new CreateTableArgs();
//args.allowNulls = false;
//args.dataLength = 0;
//args.dataType="int";
//args.fieldName="id";
//args.isUnique = false;
//args.isPrimaryKey = false;
//argsList.Add(args);
BaseTableSchema tableSchame = new BaseTableSchema();
tableSchame.Columns.Add(new BaseColumnSchema()
{
ColumnName = "id", ColumnType = "int"
});
try
{
App.MainEngineer.CreateTable(tableSchame);
string sqlCmd = string.Empty;
SqlCeConnection conn = new SqlCeConnection(string.Format("Data source={0}", testDb));
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = conn;
conn.Open();
Stopwatch watch = new Stopwatch();
watch.Start();
for (int i = 0; i < recordCount; i++)
{
sqlCmd = string.Format("insert into {0} values ({1})", testTable, i);
cmd.CommandText = sqlCmd;
cmd.ExecuteNonQuery();
}
watch.Stop();
long sqlDirectTime = watch.ElapsedMilliseconds;
watch.Reset();
watch.Start();
cmd.CommandText = string.Format("INSERT INTO {0} VALUES (?)", testTable);
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Prepare();
for (int i = 0; i < recordCount; i++)
{
cmd.Parameters[0].Value = i;
cmd.ExecuteNonQuery();
}
watch.Stop();
long sqlParaTime = watch.ElapsedMilliseconds;
watch.Reset();
watch.Start();
cmd.CommandText = testTable;
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet st = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
SqlCeUpdatableRecord rec = st.CreateRecord();
for (int i = 0; i < recordCount; i++)
{
rec.SetInt32(0, i);
st.Insert(rec);
}
watch.Stop();
long sqlceResultSetTime = watch.ElapsedMilliseconds;
//watch.Start();
//cmd.CommandText = testTable;
//cmd.CommandType = CommandType.TableDirect;
//SqlCeResultSet st = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
//SqlCeUpdatableRecord rec =
//for (int i = 0; i < recordCount; i++)
//{
// rec.SetInt32(0, i);
// st.Insert(rec);
//}
//watch.Stop();
long sqlceUpdateResultSetTime = 100;// watch.ElapsedMilliseconds;
cmd.Dispose();
conn.Close();
MessageBox.Show(string.Format("Test Result is \r\nDirect sql command used {0} \r\nUse parameters used{1}\r\nUse SqlceResultSet used{2}\r\nUpdate Sqlce ResultSet{3}\r\n", sqlDirectTime, sqlParaTime, sqlceResultSetTime, sqlceUpdateResultSetTime));
}
catch (Exception ee)
{
ProcessException.DisplayErrors(ee);
}
}
private void WriteLine()
{
// try
// {
if (czydodac == 1)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmdb = cn.CreateCommand();
cmdb.CommandText = "INSERT INTO fedibody (NrDok, Nazwa, kod, Vat, Jm, Asortyment, Ilosc, Cena, IleWOpak, CenaSp, ebid, Wymagane) VALUES (@a, @b, @c, @d, @e, @f, @g, @h, @i, @j, @k, @l)";
cmdb.Parameters.Add("@a", SqlDbType.NVarChar, 30);
cmdb.Parameters.Add("@b", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@c", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@d", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@e", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@f", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@g", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@h", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@i", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@j", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@k", SqlDbType.Int, 10);
cmdb.Parameters.Add("@l", SqlDbType.NVarChar, 10);
cmdb.Parameters["@a"].Value = indeks;
cmdb.Parameters["@b"].Value = nazwa_t.Text;
cmdb.Parameters["@c"].Value = kod_t.Text;
cmdb.Parameters["@d"].Value = vat_t.Text;
cmdb.Parameters["@e"].Value = jm_t.Text;
cmdb.Parameters["@f"].Value = asorcik1;
cmdb.Parameters["@g"].Value = ilosc_t.Text;
cmdb.Parameters["@h"].Value = cena_t.Text;
cmdb.Parameters["@i"].Value = wopak_t.Text;
cmdb.Parameters["@j"].Value = cenasp_t.Text;
cmdb.Parameters["@k"].Value = ebid;
cmdb.Parameters["@l"].Value = wymagane_t.Text;
cmdb.Prepare();
cmdb.ExecuteNonQuery();
SqlCeCommand cmd1 = cn.CreateCommand();
cmd1.CommandText = "SELECT kod, NrDok, Ilosc FROM fedibody WHERE kod = ? and NrDok = ?";
cmd1.Parameters.Add("@k", SqlDbType.NVarChar, 30);
cmd1.Parameters.Add("@d", SqlDbType.NVarChar, 30);
cmd1.Parameters["@k"].Value = kodzik;
cmd1.Parameters["@d"].Value = indeks;
cmd1.Prepare();
zliczono = "0";
SqlCeDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
zliczono = ((decimal.Parse(zliczono) + decimal.Parse(dr1.GetString(2))).ToString());
}
if (decimal.Parse(zliczono) >= decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'Ok', complete = 1 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Parameters["@a"].Value = ebid;
cmdc.Prepare();
cmdc.ExecuteNonQuery();
}
else if (decimal.Parse(zliczono) < decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'W trakcie', complete = 0 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Parameters["@a"].Value = ebid;
cmdc.Prepare();
cmdc.ExecuteNonQuery();
}
int toclose = 0;
SqlCeCommand cmd2 = cn.CreateCommand();
cmd2.CommandText = "SELECT count(id), complete FROM edibody WHERE NrDok = ? and complete = 0 GROUP BY complete";
cmd2.Parameters.Add("@d", SqlDbType.NVarChar, 30);
cmd2.Parameters["@d"].Value = indeks;
cmd2.Prepare();
SqlCeDataReader dr2 = cmd2.ExecuteReader();
while (dr2.Read())
{
toclose = dr2.GetInt32(0);
}
if (toclose == 0)
{
SqlCeCommand cmdf = cn.CreateCommand();
cmdf.CommandText = "UPDATE edihead SET status = 'OK', complete = 1 WHERE NrDok = ?";
cmdf.Parameters.Add("@a", SqlDbType.NVarChar, 30);
cmdf.Parameters["@a"].Value = indeks;
cmdf.Prepare();
cmdf.ExecuteNonQuery();
}
cn.Close();
form17.Loaddata();
this.Close();
}
else if (czydodac == 0)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmdb = cn.CreateCommand();
cmdb.CommandText = "INSERT INTO edibody (NrDok, Nazwa, kod, Vat, Jm, Asortyment, Ilosc, Cena, IleWOpak, CenaSp, status, complete) VALUES (@a, @b, @c, @d, @e, @f, @g, @a0, @a2, @3, @4, @5)";
cmdb.Parameters.Add("@a", SqlDbType.NVarChar, 30);
cmdb.Parameters.Add("@b", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@c", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@d", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@e", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@f", SqlDbType.NVarChar, 120);
cmdb.Parameters.Add("@g", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@a0", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@a2", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@a3", SqlDbType.NVarChar, 10);
cmdb.Parameters.Add("@a4", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@a5", SqlDbType.Bit);
cmdb.Parameters["@a"].Value = Convert.ToString(indeks);
cmdb.Parameters["@b"].Value = nazwa_t.Text;
cmdb.Parameters["@c"].Value = kod_t.Text;
cmdb.Parameters["@d"].Value = vat_t.Text;
cmdb.Parameters["@e"].Value = jm_t.Text;
cmdb.Parameters["@f"].Value = asorcik1;
cmdb.Parameters["@g"].Value = ilosc_t.Text;
cmdb.Parameters["@a0"].Value = cena_t.Text;
cmdb.Parameters["@a2"].Value = wopak_t.Text;
cmdb.Parameters["@a3"].Value = cenasp_t.Text;
cmdb.Parameters["@a4"].Value = "Nowy";
cmdb.Parameters["@a5"].Value = byte.Parse("0");
cmdb.Prepare();
cmdb.ExecuteNonQuery();
cn.Close();
form17.Loaddata();
this.Close();
}
else if (czydodac == 2)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmdb = cn.CreateCommand();
cmdb.CommandText = "Update edibody set Ilosc = ? WHERE id = ?";
cmdb.Parameters.Add("@a", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@b", SqlDbType.Int, 10);
cmdb.Prepare();
cmdb.Parameters["@a"].Value = ilosc_t.Text;
cmdb.Parameters["@b"].Value = ebid;
cmdb.ExecuteNonQuery();
SqlCeCommand cmd1 = cn.CreateCommand();
cmd1.CommandText = "SELECT kod, NrDok, Ilosc FROM fedibody WHERE kod = ? and NrDok = ?";
cmd1.Parameters.Add("@k", SqlDbType.NVarChar, 30);
cmd1.Parameters.Add("@d", SqlDbType.NVarChar, 30);
cmd1.Parameters["@k"].Value = kodzik;
cmd1.Parameters["@d"].Value = indeks;
cmd1.Prepare();
zliczono = "0";
SqlCeDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
zliczono = ((decimal.Parse(zliczono) + decimal.Parse(dr1.GetString(2))).ToString());
}
if (decimal.Parse(zliczono) >= decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'Ok', complete = 1 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Prepare();
cmdc.Parameters["@a"].Value = ebid;
cmdc.ExecuteNonQuery();
}
else if (decimal.Parse(zliczono) < decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'W trakcie', complete = 0 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Prepare();
cmdc.Parameters["@a"].Value = ebid;
cmdc.ExecuteNonQuery();
}
cn.Close();
form17.Loaddata();
this.Close();
}
else if (czydodac == 3)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
SqlCeCommand cmdb = cn.CreateCommand();
cmdb.CommandText = "Update fedibody set Ilosc = ? WHERE id = ?";
cmdb.Parameters.Add("@a", SqlDbType.NVarChar, 20);
cmdb.Parameters.Add("@b", SqlDbType.Int, 10);
cmdb.Prepare();
cmdb.Parameters["@a"].Value = ilosc_t.Text;
cmdb.Parameters["@b"].Value = ebid;
cmdb.ExecuteNonQuery();
SqlCeCommand cmd1 = cn.CreateCommand();
cmd1.CommandText = "SELECT kod, NrDok, Ilosc FROM fedibody WHERE kod = ? and NrDok = ?";
cmd1.Parameters.Add("@k", SqlDbType.NVarChar, 20);
cmd1.Parameters.Add("@d", SqlDbType.NVarChar, 20);
cmd1.Parameters["@k"].Value = kodzik;
cmd1.Parameters["@d"].Value = indeks;
cmd1.Prepare();
zliczono = "0";
SqlCeDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
zliczono = ((decimal.Parse(zliczono) + decimal.Parse(dr1.GetString(2))).ToString());
}
if (decimal.Parse(zliczono) >= decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'Ok', complete = 1 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Prepare();
cmdc.Parameters["@a"].Value = ebid;
cmdc.ExecuteNonQuery();
}
else if (decimal.Parse(zliczono) < decimal.Parse(wymagane_t.Text))
{
SqlCeCommand cmdc = cn.CreateCommand();
cmdc.CommandText = "UPDATE edibody SET status = 'W trakcie', complete = 0 WHERE id = ?";
cmdc.Parameters.Add("@a", SqlDbType.Int, 10);
cmdc.Prepare();
cmdc.Parameters["@a"].Value = ebid;
cmdc.ExecuteNonQuery();
}
cn.Close();
form17.Loaddata();
this.Close();
}
}
private void Deleterow(int deleteflag)
{
string connectionString;
connectionString = "DataSource=Baza.sdf; Password=matrix1";
SqlCeConnection cn = new SqlCeConnection(connectionString);
cn.Open();
if (deleteflag == 1)
{
int index = dataGrid1.CurrentCell.RowNumber;
SqlCeDataAdapter db = new SqlCeDataAdapter("SELECT * FROM edihead WHERE complete = 0", cn);
DataTable table1 = new DataTable();
db.Fill(table1);
string dataindex = table1.Rows[index][0].ToString();
string dokindex = table1.Rows[index][4].ToString();
db.DeleteCommand = new SqlCeCommand("DELETE FROM edihead WHERE id = ?", cn);
db.DeleteCommand.Parameters.Add("@k", SqlDbType.Int, 10);
db.DeleteCommand.Parameters["@k"].Value = int.Parse(dataindex);
db.DeleteCommand.ExecuteNonQuery();
SqlCeCommand da = new SqlCeCommand("DELETE FROM edibody WHERE NrDok = ?", cn);
da.Parameters.Add("@k", SqlDbType.NVarChar, 30);
da.Parameters["@k"].Value = dokindex;
da.Prepare();
da.ExecuteNonQuery();
SqlCeCommand dc = new SqlCeCommand("DELETE FROM ediend WHERE NrDok = ?", cn);
dc.Parameters.Add("@k", SqlDbType.NVarChar, 30);
dc.Parameters["@k"].Value = dokindex;
dc.Prepare();
dc.ExecuteNonQuery();
SqlCeCommand dx = new SqlCeCommand("DELETE FROM fedibody WHERE NrDok = ?", cn);
dx.Parameters.Add("@k", SqlDbType.NVarChar, 30);
dx.Parameters["@k"].Value = dokindex;
dx.Prepare();
dx.ExecuteNonQuery();
}
if (deleteflag == 2)
{
int index = dataGrid2.CurrentCell.RowNumber;
SqlCeDataAdapter db = new SqlCeDataAdapter("SELECT * FROM edihead WHERE complete = 1", cn);
DataTable table1 = new DataTable();
db.Fill(table1);
string dataindex = table1.Rows[index][0].ToString();
string dokindex = table1.Rows[index][4].ToString();
db.DeleteCommand = new SqlCeCommand("DELETE FROM edihead WHERE id = ?", cn);
db.DeleteCommand.Parameters.Add("@k", SqlDbType.Int, 10);
db.DeleteCommand.Parameters["@k"].Value = int.Parse(dataindex);
db.DeleteCommand.ExecuteNonQuery();
SqlCeCommand da = new SqlCeCommand("DELETE FROM edibody WHERE NrDok = ?", cn);
da.Parameters.Add("@k", SqlDbType.NVarChar, 30);
da.Parameters["@k"].Value = dokindex;
da.Prepare();
da.ExecuteNonQuery();
SqlCeCommand dc = new SqlCeCommand("DELETE FROM ediend WHERE NrDok = ?", cn);
dc.Parameters.Add("@k", SqlDbType.NVarChar, 30);
dc.Parameters["@k"].Value = dokindex;
dc.Prepare();
dc.ExecuteNonQuery();
SqlCeCommand dx = new SqlCeCommand("DELETE FROM fedibody WHERE NrDok = ?", cn);
dx.Parameters.Add("@k", SqlDbType.NVarChar, 30);
dx.Parameters["@k"].Value = dokindex;
dx.Prepare();
dx.ExecuteNonQuery();
}
cn.Close();
}
//Update
public static string UpdateStavke(DataTable DT)
{
if (classSQL.remoteConnectionString == "")
{
if (classSQL.connection.State.ToString() == "Closed")
{
classSQL.connection.Open();
}
SqlCeCommand nonqueryCommand = classSQL.connection.CreateCommand();
try
{
nonqueryCommand.CommandText = "UPDATE kalkulacija_stavke SET kolicina=@kolicina, fak_cijena=@fak_cijena,rabat=@rabat,prijevoz=@prijevoz,carina=@carina,marza_postotak=@marza_postotak,porez=@porez,posebni_porez=@posebni_porez,broj=@broj,sifra=@sifra,vpc=@vpc,id_skladiste=@id_skladiste,porez_potrosnja=@porez_potrosnja WHERE broj=@where_broj AND id_skladiste=@id_skladiste_staro AND sifra=@sifra";
nonqueryCommand.Parameters.Add("@kolicina", SqlDbType.NVarChar, 20);
nonqueryCommand.Parameters.Add("@fak_cijena", SqlDbType.Money, 8);
nonqueryCommand.Parameters.Add("@rabat", SqlDbType.NVarChar, 20);
nonqueryCommand.Parameters.Add("@prijevoz", SqlDbType.Money, 8);
nonqueryCommand.Parameters.Add("@carina", SqlDbType.Money, 8);
nonqueryCommand.Parameters.Add("@marza_postotak", SqlDbType.NVarChar, 20);
nonqueryCommand.Parameters.Add("@porez", SqlDbType.NVarChar, 20);
nonqueryCommand.Parameters.Add("@posebni_porez", SqlDbType.Money, 8);
nonqueryCommand.Parameters.Add("@broj", SqlDbType.BigInt, 8);
nonqueryCommand.Parameters.Add("@where_broj", SqlDbType.BigInt, 8);
nonqueryCommand.Parameters.Add("@sifra", SqlDbType.NVarChar, 20);
nonqueryCommand.Parameters.Add("@vpc", SqlDbType.Money, 8);
nonqueryCommand.Parameters.Add("@id_skladiste", SqlDbType.Int);
nonqueryCommand.Parameters.Add("@id_skladiste_staro", SqlDbType.Int);
nonqueryCommand.Parameters.Add("@porez_potrosnja", SqlDbType.Int);
nonqueryCommand.Prepare();
for (int i = 0; i < DT.Rows.Count; i++)
{
nonqueryCommand.Parameters["@kolicina"].Value = DT.Rows[i]["kolicina"];
nonqueryCommand.Parameters["@fak_cijena"].Value = DT.Rows[i]["fak_cijena"];
nonqueryCommand.Parameters["@rabat"].Value = DT.Rows[i]["rabat"];
nonqueryCommand.Parameters["@prijevoz"].Value = DT.Rows[i]["prijevoz"];
nonqueryCommand.Parameters["@carina"].Value = DT.Rows[i]["carina"];
nonqueryCommand.Parameters["@marza_postotak"].Value = DT.Rows[i]["marza_postotak"];
nonqueryCommand.Parameters["@porez"].Value = DT.Rows[i]["porez"];
nonqueryCommand.Parameters["@posebni_porez"].Value = DT.Rows[i]["posebni_porez"];
nonqueryCommand.Parameters["@broj"].Value = DT.Rows[i]["broj"];
nonqueryCommand.Parameters["@where_broj"].Value = DT.Rows[i]["where_broj"];
nonqueryCommand.Parameters["@sifra"].Value = DT.Rows[i]["sifra"];
nonqueryCommand.Parameters["@vpc"].Value = DT.Rows[i]["vpc"];
nonqueryCommand.Parameters["@id_skladiste"].Value = DT.Rows[i]["id_skladiste"];
nonqueryCommand.Parameters["@id_skladiste_staro"].Value = DT.Rows[i]["id_skladiste_staro"];
nonqueryCommand.Parameters["@porez_potrosnja"].Value = DT.Rows[i]["porez_potrosnja"];
nonqueryCommand.ExecuteNonQuery();
}
}
catch (SqlCeException ex)
{
classSQL.connection.Close();
return(ex.ToString());
}
finally
{
classSQL.connection.Close();
}
return("");
}
else
{
if (classSQL.remoteConnection.State.ToString() == "Closed")
{
classSQL.remoteConnection.Open();
}
NpgsqlCommand nonqueryCommand = classSQL.remoteConnection.CreateCommand();
try
{
for (int i = 0; i < DT.Rows.Count; i++)
{
string sql = "UPDATE kalkulacija_stavke SET " +
" kolicina='" + DT.Rows[i]["kolicina"].ToString() + "'," +
" fak_cijena='" + DT.Rows[i]["fak_cijena"].ToString().Replace(".", ",") + "'," +
" rabat='" + DT.Rows[i]["rabat"].ToString() + "'," +
" prijevoz='" + DT.Rows[i]["prijevoz"].ToString().Replace(".", ",") + "'," +
" carina='" + DT.Rows[i]["carina"].ToString().Replace(".", ",") + "'," +
" vpc='" + DT.Rows[i]["vpc"].ToString().Replace(",", ".") + "'," +
" marza_postotak='" + DT.Rows[i]["marza_postotak"].ToString() + "'," +
" porez='" + DT.Rows[i]["porez"].ToString() + "'," +
" posebni_porez='" + DT.Rows[i]["posebni_porez"].ToString().Replace(".", ",") + "'," +
" broj='" + DT.Rows[i]["broj"].ToString() + "'," +
" sifra='" + DT.Rows[i]["sifra"].ToString() + "'," +
" id_skladiste='" + DT.Rows[i]["id_skladiste"].ToString() + "'," +
" porez_potrosnja='" + DT.Rows[i]["porez_potrosnja"].ToString() + "'" +
" WHERE broj='" + DT.Rows[i]["where_broj"].ToString() + "' AND id_skladiste='" + DT.Rows[i]["id_skladiste_staro"].ToString() + "' AND sifra='" + DT.Rows[i]["sifra"].ToString() + "'";
NpgsqlCommand comm = new NpgsqlCommand(sql, classSQL.remoteConnection);
comm.ExecuteNonQuery();
}
}
catch (SqlCeException ex)
{
classSQL.connection.Close();
return(ex.ToString());
}
finally
{
classSQL.connection.Close();
}
return("");
}
}
private void FindIndex()
{
string kodbuf = search_t.Text;
search_t.Text = "SZUKAM TOWARÓW W BAZIE";
search_t.Refresh();
if (kodbuf != "" && comboBox1.Text != "" && comboBox1.Text != null)
{
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT dane.kod, dane.nazwa, stany.stan, magazyn.Nazwa AS magazyn, dane.cenasp, dane.stan as calystan, dane.vat, dane.cenazk, dane.cenahurt, dane.cenaoryg FROM magazyn INNER JOIN stany ON magazyn.MagId = stany.MagId INNER JOIN dane ON stany.kod = dane.kod where (magazyn.Nazwa = ?) AND (dane.kod = ?)";
cmd.Parameters.Add("@m", SqlDbType.NVarChar, 20);
cmd.Parameters.Add("@k", SqlDbType.NVarChar, 20);
cmd.Prepare();
cmd.Parameters["@m"].Value = comboBox1.Text;
cmd.Parameters["@k"].Value = kodbuf;
int test = 0;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
System.Globalization.NumberFormatInfo nfi = new System.Globalization.NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
test = 1;
string stan = dr.GetString(2);
string calystan = dr.GetString(5);
dokstan = dr.GetString(5);
cenasp = dr.GetString(4);
nazwa = dr.GetString(1);
kod = dr.GetString(0);
cenazk = dr.GetString(7);
vat = dr.GetString(6);
cenah = dr.GetString(8);
cenao = dr.GetString(9);
if (type == 0)
{
label2.Text = kod;
label3.Text = nazwa;
label4.Text = "Detal(brutto): " + cenasp;
label5.Text = "Stan: " + stan;
}
else if (type == 1)
{
label2.Text = kod;
label3.Text = nazwa;
label4.Text = "Hurt(net) " + cenah + "Original(brut) " + cenao;
label5.Text = "Stan: " + stan;
}
if (Convert.ToDecimal(stan) > 0)
{
this.BackColor = Color.Green;
label5.Text += " - OK !!!!!";
}
else if (Convert.ToDecimal(calystan) > 0)
{
this.BackColor = Color.MidnightBlue;
label2.Text += "\n TOWAR JEST NA INNYM MAGAZYNIE";
}
else
{
this.BackColor = Color.DarkRed;
label2.Text += "\n BRAK TOWARU";
}
SqlCeDataAdapter db = new SqlCeDataAdapter("SELECT stany.kod, stany.stan, magazyn.Nazwa AS magazyn FROM magazyn INNER JOIN stany ON magazyn.MagId = stany.MagId", cn);
DataTable table2 = new DataTable();
db.SelectCommand = new SqlCeCommand("SELECT magazyn.Nazwa AS Magazyn, stany.stan As Stan FROM magazyn INNER JOIN stany ON magazyn.MagId = stany.MagId where (stany.kod = ?)", cn);
db.SelectCommand.Parameters.Add("@k", SqlDbType.NVarChar, 15);
db.SelectCommand.Parameters["@k"].Value = kodbuf;
db.SelectCommand.ExecuteNonQuery();
db.Fill(table2);
if (table2.Rows.Count != 0)
{
dataGrid1.DataSource = table2.DefaultView;
}
}
}
dr.Close();
if (test == 0)
{
label2.Text = "NIE ZNALEZIONO TOWARU O PODANYM KODZIE !!!!";
this.BackColor = Color.DarkOrange;
search_t.Text = null;
label3.Text = null;
label4.Text = null;
label5.Text = null;
}
}
else
{
MessageBox.Show("Nie wybra³eœ magazynu lub nie poda³eœ kodu kreskowego towaru");
}
search_t.Text = "";
search_t.Focus();
dataGrid1.Refresh();
}
