public static Inspection SelectById(SQLiteConnection conn, int id)
{
using (SQLiteCommand cmd = new SQLiteCommand(selectById, conn))
{
cmd.Parameters.AddWithValue("@Id", id);
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Inspection im = new Inspection()
{
Id = Convert.ToInt32(reader["rowid"].ToString()),
Vehicle = VehicleDbMapper.SelectById(conn, Convert.ToInt32(reader["VehicleId"])),
InspectionDate = Convert.ToDateTime(reader["InspectionDate"]),
ValidTo = Convert.ToDateTime(reader["ValidTo"]),
InspectionStation = InspectionStationDbMapper.SelectById(conn, Convert.ToInt32(reader["InspectionStationId"])),
ProtocolNumber = reader["ProtocolNumber"].ToString(),
Tachometer = Convert.ToInt32(reader["Tachometer"].ToString()),
Price = Convert.ToDecimal(reader["Price"].ToString()),
Defects = reader["Defects"].ToString()
};
reader.Close();
return(im);
}
}
catch (Exception e)
{
reader?.Close();
Trace.WriteLine($"EXCEPTION: InspectionDbMapper.SelectById: {e.Message}");
}
}
return(null);
}
public static List <Notification> SelectAll(SQLiteConnection conn)
{
List <Notification> result = new List <Notification>();
using (SQLiteCommand cmd = new SQLiteCommand(selectAll, conn))
{
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Notification nm = new Notification()
{
Id = Convert.ToInt32(reader["Id"].ToString()),
Inspection = InspectionDbMapper.SelectById(conn, Convert.ToInt32(reader["InspectionId"])),
Destination = reader["Destination"].ToString(),
SentOn = Convert.ToDateTime(reader["SentOn"]),
Delivered = Convert.ToDateTime(reader["Delivered"])
};
result.Add(nm);
}
}
catch (Exception e)
{
Trace.WriteLine($"EXCEPTION: NotificationDbMapper.SelectAll: {e.Message}");
}
finally
{
reader?.Close();
}
}
return(result);
}
public static Notification SelectById(SQLiteConnection conn, int id)
{
using (SQLiteCommand cmd = new SQLiteCommand(selectById, conn))
{
cmd.Parameters.AddWithValue("@Id", id);
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Notification nm = new Notification()
{
Id = Convert.ToInt32(reader["Id"].ToString()),
Inspection = InspectionDbMapper.SelectById(conn, Convert.ToInt32(reader["InspectionId"])),
Destination = reader["Destination"].ToString(),
SentOn = Convert.ToDateTime(reader["SentOn"]),
Delivered = Convert.ToDateTime(reader["Delivered"])
};
reader.Close();
return(nm);
}
}
catch (Exception e)
{
reader?.Close();
Trace.WriteLine($"EXCEPTION: NotificationDbMapper.SelectById: {e.Message}");
}
}
return(null);
}
public static List <UserAdmin> SelectAll(SQLiteConnection conn)
{
List <UserAdmin> result = new List <UserAdmin>();
using (SQLiteCommand cmd = new SQLiteCommand(selectAll, conn))
{
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
UserAdmin um = new UserAdmin()
{
Id = Convert.ToInt32(reader["rowid"].ToString()),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString(),
Phone = reader["Phone"].ToString(),
Street = reader["Street"].ToString(),
City = reader["City"].ToString(),
Zip = reader["Zip"].ToString(),
Active = Convert.ToBoolean(reader["Active"]),
Login = reader["Login"].ToString(),
Password = reader["Password"].ToString(),
CanManageNotifications = Convert.ToBoolean(reader["CanManageNotifications"]),
CanManageStations = Convert.ToBoolean(reader["CanManageStations"]),
CanManageUsers = Convert.ToBoolean(reader["CanManageUsers"])
};
result.Add(um);
}
}
catch (Exception e)
{
Trace.WriteLine($"EXCEPTION: UserAdminDbMapper.SelectAll: {e.Message}");
}
finally
{
reader?.Close();
}
}
return(result);
}
public static UserAdmin SelectById(SQLiteConnection conn, int id)
{
if (id < 0)
{
return(null);
}
using (SQLiteCommand cmd = new SQLiteCommand(selectById, conn))
{
cmd.Parameters.AddWithValue("@Id", id);
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
UserAdmin um = new UserAdmin()
{
Id = Convert.ToInt32(reader["rowid"].ToString()),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString(),
Phone = reader["Phone"].ToString(),
Street = reader["Street"].ToString(),
City = reader["City"].ToString(),
Zip = reader["Zip"].ToString(),
Active = Convert.ToBoolean(reader["Active"]),
Login = reader["Login"].ToString(),
Password = reader["Password"].ToString(),
CanManageNotifications = Convert.ToBoolean(reader["CanManageNotifications"]),
CanManageStations = Convert.ToBoolean(reader["CanManageStations"]),
CanManageUsers = Convert.ToBoolean(reader["CanManageUsers"])
};
reader.Close();
return(um);
}
}
catch (Exception e)
{
reader?.Close();
Trace.WriteLine($"EXCEPTION: UserAdminDbMapper.SelectById: {e.Message}");
}
}
return(null);
}
private int ExeKeyNums(string key)
{
if (key == null)
{
return(0);
}
var count = 0;
var sql = "select count(" + ProjectInfo.TYPE_COLUMN_KEY + ") from " + ProjectInfo.TYPE_TABLE_NAME_PROJECT + " where " +
ProjectInfo.TYPE_COLUMN_KEY + " = '" + key + "' ;";
SQLiteCommand command = new SQLiteCommand(sql, _dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
count = reader.GetInt32(0);
}
reader?.Close();
return(count);
}
public static List <InspectionStation> SelectAll(SQLiteConnection conn)
{
List <InspectionStation> result = new List <InspectionStation>();
using (SQLiteCommand cmd = new SQLiteCommand(selectAll, conn))
{
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
InspectionStation ism = new InspectionStation()
{
Id = Convert.ToInt32(reader["rowid"].ToString()),
Active = Convert.ToBoolean(reader["Active"]),
StationNumber = reader["StationNumber"].ToString(),
ZipCode = reader["ZipCode"].ToString(),
City = reader["City"].ToString(),
Street = reader["Street"].ToString(),
Company = reader["Company"].ToString(),
Phone = reader["Phone"].ToString(),
Email = reader["Email"].ToString(),
Orp = reader["Orp"].ToString(),
District = reader["District"].ToString(),
Region = reader["Region"].ToString(),
Url = reader["Url"].ToString()
};
result.Add(ism);
}
}
catch (Exception e)
{
Trace.WriteLine($"EXCEPTION: InspectionStationDbMapper.SelectAll: {e.Message}");
}
finally
{
reader?.Close();
}
}
return(result);
}
/// <summary>
/// 执行数据库命令：select from where
/// </summary>
/// <param name="info"></param>
/// <param name="table"></param>
/// <param name="field"></param>
/// <param name="fieldvalue"></param>
/// <returns></returns>
public string SelectInfoByID(string info, string table, string id)
{
//if (IsLock) return "";
string result = "";
string sqltext = $"select {info} from {table} where id ='{id}'";
cmd.CommandText = sqltext;
SQLiteDataReader sr = cmd.ExecuteReader();
if (sr != null)
{
while (sr.Read())
{
result = sr[0].ToString();
}
}
sr?.Close();
return(result);
}
public static List <Notification> GenerateNotifications(SQLiteConnection conn, int days)
{
List <Notification> result = new List <Notification>();
using (SQLiteCommand cmd = new SQLiteCommand(generateNotifications, conn))
{
cmd.Parameters.AddWithValue("@days", days);
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Notification nm = new Notification()
{
Id = -1,
Inspection = InspectionDbMapper.SelectById(conn, Convert.ToInt32(reader["InspectionId"])),
Destination = "",
SentOn = DateTime.Now,
Delivered = DateTime.Now
};
var vehicle = VehicleDbMapper.SelectById(conn, Convert.ToInt32(reader["VehicleId"]));
var boss = UserBossDbMapper.SelectById(conn, vehicle.Boss.Id);
var email = boss.Email;
nm.Destination = email;
result.Add(nm);
}
}
catch (Exception e)
{
Trace.WriteLine($"EXCEPTION: NotificationDbMapper.SelectAll: {e.Message}");
}
finally
{
reader?.Close();
}
}
return(result);
}
public static List <Vehicle> SelectAll(SQLiteConnection conn)
{
List <Vehicle> result = new List <Vehicle>();
using (SQLiteCommand cmd = new SQLiteCommand(selectAll, conn))
{
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Vehicle vm = new Vehicle()
{
Id = Convert.ToInt32(reader["rowid"].ToString()),
VehicleType = VehicleTypeXmlMapper.SelectById(Convert.ToInt32(reader["VehicleTypeId"])),
VehicleBrand = VehicleBrandXmlMapper.SelectById(Convert.ToInt32(reader["VehicleBrandId"])),
Title = reader["Title"].ToString(),
Vin = reader["Vin"].ToString(),
LicensePlate = reader["LicensePlate"].ToString(),
Vintage = Convert.ToInt16(reader["Vintage"]),
PurchasedOn = Convert.ToDateTime(reader["PurchasedOn"]),
Price = Convert.ToDecimal(reader["Price"].ToString()),
Boss = UserBossDbMapper.SelectById(conn, Convert.ToInt32(reader["AdminId"])),
Driver = UserDriverDbMapper.SelectById(conn, Convert.ToInt32(reader["DriverId"]))
};
result.Add(vm);
}
}
catch (Exception e)
{
Trace.WriteLine($"EXCEPTION: VehicleDbMapper.SelectAll: {e.Message}");
}
finally
{
reader?.Close();
}
}
return(result);
}
public static InspectionStation SelectById(SQLiteConnection conn, int id)
{
using (SQLiteCommand cmd = new SQLiteCommand(selectById, conn))
{
cmd.Parameters.AddWithValue("@Id", id);
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
InspectionStation ism = new InspectionStation()
{
Id = Convert.ToInt32(reader["rowid"].ToString()),
Active = Convert.ToBoolean(reader["Active"]),
StationNumber = reader["StationNumber"].ToString(),
ZipCode = reader["ZipCode"].ToString(),
City = reader["City"].ToString(),
Street = reader["Street"].ToString(),
Company = reader["Company"].ToString(),
Phone = reader["Phone"].ToString(),
Email = reader["Email"].ToString(),
Orp = reader["Orp"].ToString(),
District = reader["District"].ToString(),
Region = reader["Region"].ToString(),
Url = reader["Url"].ToString()
};
reader.Close();
return(ism);
}
}
catch (Exception e)
{
reader?.Close();
Trace.WriteLine($"EXCEPTION: InspectionStationDbMapper.SelectById: {e.Message}");
}
}
return(null);
}
public static List <Inspection> SelectAll(SQLiteConnection conn)
{
List <Inspection> result = new List <Inspection>();
using (SQLiteCommand cmd = new SQLiteCommand(selectAll, conn))
{
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Inspection im = new Inspection()
{
Id = Convert.ToInt32(reader["rowid"].ToString()),
Vehicle = VehicleDbMapper.SelectById(conn, Convert.ToInt32(reader["VehicleId"])),
InspectionDate = Convert.ToDateTime(reader["InspectionDate"]),
ValidTo = Convert.ToDateTime(reader["ValidTo"]),
InspectionStation = InspectionStationDbMapper.SelectById(conn, Convert.ToInt32(reader["InspectionStationId"])),
ProtocolNumber = reader["ProtocolNumber"].ToString(),
Tachometer = Convert.ToInt32(reader["Tachometer"].ToString()),
Price = Convert.ToDecimal(reader["Price"].ToString()),
Defects = reader["Defects"].ToString()
};
result.Add(im);
}
}
catch (Exception e)
{
Trace.WriteLine($"EXCEPTION: InspectionDbMapper.SelectAll: {e.Message}");
}
finally
{
reader?.Close();
}
}
return(result);
}
/// <summary>
/// 异步获得影片信息
/// </summary>
/// <param name="movieid"></param>
/// <returns></returns>
public async Task <List <Movie> > SelectMoviesById(string movieid)
{
return(await Task.Run(() =>
{
List <Movie> result = new List <Movie>();
//if (IsLock) return result;
movieid = movieid.Replace("'", "").Replace("%", "");
if (string.IsNullOrEmpty(movieid))
{
cmd.CommandText = "SELECT * FROM movie";
}
else
{
cmd.CommandText = "SELECT * FROM movie where id like '%" + movieid + "%'";
}
SQLiteDataReader sr = cmd.ExecuteReader();
if (sr != null && cn.State == ConnectionState.Open)
{
try
{
while (sr.Read())
{
Movie movie = (Movie)GetDetailMovieFromSQLiteDataReader(sr);
result.Add(movie);
}
}
catch { }
}
if (cn.State == ConnectionState.Open)
{
sr?.Close();
}
return result;
}));
}
public static Vehicle SelectById(SQLiteConnection conn, int id)
{
using (SQLiteCommand cmd = new SQLiteCommand(selectById, conn))
{
cmd.Parameters.AddWithValue("@Id", id);
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Vehicle vm = new Vehicle()
{
Id = Convert.ToInt32(reader["rowid"].ToString()),
VehicleType = VehicleTypeXmlMapper.SelectById(Convert.ToInt32(reader["VehicleTypeId"])),
VehicleBrand = VehicleBrandXmlMapper.SelectById(Convert.ToInt32(reader["VehicleBrandId"])),
Title = reader["Title"].ToString(),
Vin = reader["Vin"].ToString(),
LicensePlate = reader["LicensePlate"].ToString(),
Vintage = Convert.ToInt16(reader["Vintage"]),
PurchasedOn = Convert.ToDateTime(reader["PurchasedOn"]),
Price = Convert.ToDecimal(reader["Price"].ToString()),
Boss = UserBossDbMapper.SelectById(conn, Convert.ToInt32(reader["AdminId"])),
Driver = UserDriverDbMapper.SelectById(conn, Convert.ToInt32(reader["DriverId"]))
};
reader.Close();
return(vm);
}
}
catch (Exception e)
{
reader?.Close();
Trace.WriteLine($"EXCEPTION: VehicleDbMapper.SelectById: {e.Message}");
}
}
return(null);
}
public override DbDataReader ExecuteReader(string sqlString, params DbParameter[] dbParameter)
{
SQLiteConnection conn = new SQLiteConnection();
conn.ConnectionString = ConnectionString;
SQLiteCommand cmd = new SQLiteCommand();
SQLiteDataReader rdr = null;
try
{
//Prepare the command to execute
PrepareCommand(cmd, conn, sqlString, null, CommandType.Text, dbParameter);
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(rdr);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
rdr?.Close();
cmd.Dispose();
conn.Close();
}
return(null);
}
private void SearchRun()
{
///Выполним поиск по необходимым элементам
///
//Определим Биты поиска
bool SerachTHICKNESS = false;
if (textBoxTol.Text.Length > 0)
{
SerachTHICKNESS = true;
}
bool SerachRELMUCH_WIDTH = false;
if (textBoxScanW.Text.Length > 0)
{
SerachRELMUCH_WIDTH = true;
}
bool Serach_MARKA_NAME = false;
if (comboBoxMarka.SelectedValue.ToString() != "-")
{
Serach_MARKA_NAME = true;
}
bool Serach_MARKA_GOST = false;
if (comboBoxStandart.SelectedValue.ToString() != "-")
{
Serach_MARKA_GOST = true;
}
_tblEU = new DataTable("EU");
using (SQLiteConnection connection = new SQLiteConnection())
{
string SQL = @"select printf('%sx%s', EU.RELMUCH_THICKNESS, EU.RELMUCH_WIDTH) 'RAZMER',
EU.MARKA_NAME, EU.MARKA_GOST, EU.RELMUCH_VES, TEHUZ.TEHUZ_NAZ from EU, TEHUZ
WHERE EU.SIGN = '1' AND EU.TEHUZ_KOD = TEHUZ.TEHUZ_KOD AND EU.RPRTTYP_NAME = 'Штрипс'
";
if (SerachTHICKNESS) //Добавляем толщину к поиску
{
SQL += " AND EU.RELMUCH_THICKNESS = '" + textBoxTol.Text + "'";
}
if (SerachRELMUCH_WIDTH) //Добавляем ширину к поиску
{
SQL += " AND EU.RELMUCH_WIDTH = '" + textBoxScanW.Text + "'";
}
if (Serach_MARKA_NAME) //Добавляем марка к поиску
{
SQL += " AND EU.MARKA_NAME = '" + comboBoxMarka.SelectedValue.ToString() + "'";
}
if (Serach_MARKA_GOST) //Добавляем стандарт к поиску
{
SQL += " AND EU.MARKA_GOST = '" + comboBoxStandart.SelectedValue.ToString() + "'";
}
SQL += " ORDER BY EU.TEHUZ_KOD LIMIT 100;";
connection.ConnectionString = "Data Source = " + SqLiteDB.pathDBFull_EU;
SQLiteCommand command = new SQLiteCommand(connection);
SQLiteCommand insert = new SQLiteCommand(SQL, connection);
connection.Open();
SQLiteDataReader reader = insert.ExecuteReader();
_tblEU.Load(reader);
reader.Close();
connection.Close();
command.Dispose();
insert.Dispose();
reader.Dispose();
}
dataGridEu.BeginInvoke(new Action(() =>
{
dataGridEu.DataSource = _tblEU;
}));
if (_tblEU.Rows.Count >= 100)
{
MessageBox.Show("Ограничьте параметры поиска. Показаны первые 100 УЕ", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
}
}
public List <string> SelectLearningStatistics(string NeuroNetName, string SelectionName)
{
List <string> ls = new List <string>(LearningAlgorithmsLibrary.GetAllNamesOfTypesOfAlgorithms());
List <int> topologies = new List <int>();
connector.ConnectToDB();
SQLiteCommand cmd = new SQLiteCommand(connector.connection);
cmd.CommandText = "SELECT NetTopology.ID FROM NetTopology, NeuroNet WHERE NeuroNetID = NeuroNet.ID AND NeuroNet.NAME = '" + Convert.ToString(NeuroNetName) + "'";
SQLiteDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
topologies.Add(Convert.ToInt32(reader[0]));
}
}
catch (SQLiteException ex)
{
MessageBox.Show(ex.Message);
}
reader.Close();
foreach (int item in topologies)
{
cmd.CommandText = "SELECT TypeLA FROM WeightsMatrix, SELECTION " +
"WHERE SELECTIONID = SELECTION.ID AND SELECTION.NAME = '" + SelectionName
+ "' AND NetTopologyID = '" + Convert.ToString(item) + "'";
List <string> bf = new List <string>();
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
bf.Add(Convert.ToString(reader[0]));
}
}
catch (SQLiteException ex)
{
MessageBox.Show(ex.Message);
}
reader.Close();
for (int i = 0; i < ls.Count; i++)
{
bool isConsist = false;
foreach (string idBf in bf)
{
if (String.Compare(idBf, ls[i]) == 0)
{
isConsist = true;
break;
}
}
if (isConsist == false)
{
ls.Remove(ls[i]);
i--;
}
}
}
connector.DisconnectFromDB();
return(ls);
}
void bcr_BarcodeReadQueueTaskEU(object sender, BarcodeReadEventArgs bre)
{
try
{
string EU = bre.strDataBuffer;
LabelEU = EU;
///Тут Алгоритм разбора что мы все-таки считали
///Для начала считаем по-умолчанию что считываем мы только ЕУ и пишем алгоритм
///Открытия формы
if (EU.IndexOf("MX") == 0)
{//
//labelStatus.BeginInvoke(new Action(() =>
//{
// labelStatus.Text = "Считано место хранения: " + EU;
//}));
return;
}
//labelStatus.BeginInvoke(new Action(() =>
//{
// labelStatus.Text = "Label: " + EU;
//}));
//MessageBox.Show(EU);
// GetDataEU(EU);
//Проверим есть ли данная ЕУ в списке
if (ValidateList.CheckEUByListType(listEU, LabelEU))
{
//ЕУ уже в списке
Sound.PlaySoundWarning();
//Vibration.PlayVibration(2000);
return;
}
dataGridEu.BeginInvoke(new Action(() =>
{
dataGridEu.BackColor = Color.White;
}));
//Тут делаем таблицу и выводим инфу
DataRow row1 = _tblEU.NewRow();
row1["Label"] = EU;
Double WEIGHT_EU = 0;
//WebReference.WebSDataBrCode BrServer = new WebReference.WebSDataBrCode();
//BrServer.SoapVersion = System.Web.Services.Protocols.SoapProtocolVersion.Soap12;
//BrServer.Url = set.AdressAppServer;
//DataTable result = BrServer.EU_GetData(EU);
string MarkaEU = "";
using (SQLiteConnection connection = new SQLiteConnection())
{
;//(SQLiteConnection)factory.CreateConnection();
connection.ConnectionString = "Data Source = " + SqLiteDB.pathDBFull_EU;
SQLiteCommand command = new SQLiteCommand(connection);
SQLiteCommand insert = new SQLiteCommand("select * from EU e WHERE e.RELMUCH_LABEL = '" + EU + "';", connection);
connection.Open();
SQLiteDataReader reader = insert.ExecuteReader();
while (reader.Read())
{
//Запроск К БД
MarkaEU = _getReaderByName(reader, "MARKA_NAME");
string sWEIGHT_EU = _getReaderByName(reader, "RELMUCH_VES");
row1["УЕ"] = _getReaderByName(reader, "RPRT_NOM");
row1["Марка"] = MarkaEU;
row1["Размер"] = _getReaderByName(reader, "RELMUCH_THICKNESS") + "х" + _getReaderByName(reader, "RELMUCH_WIDTH");
row1["Вес"] = sWEIGHT_EU;
// row1["S"] = "";
/*
* CREATE TABLE [EU] (
* [RELMUCH_LABEL] char(20) NOT NULL,
* [RELMUCH_PRM] char(20),
* [RELMUCH_VES] char(20),
* [RELMUCH_FVES] char(20),
* [RELMUCH_WIDTH] char(20),
* [RELMUCH_THICKNESS] char(20),
* [RPRT_NOM] char(20),
* [RPRTTYP_NAME] char(20),
* [RPRT_TOL] char(20),
* [RPRT_SHRN] char(20),
* [RPRT_PLVNOM] char(20),
* [MARKA_NAME] char(20),
* [MARKA_GOST] char(20),
* [FACT_STORAGE_CODE] char(20),
* [TEHUZ_LABEL] char(20),
* [FACT_PLACE_NAME] char(20),
* [INTRV_TMBEG] char(20))
* */
try
{
WEIGHT_EU = Double.Parse(sWEIGHT_EU);
}
catch (Exception) { }
}
reader.Close();
connection.Close();
command.Dispose();
insert.Dispose();
reader.Dispose();
}
//MarkaEU = MarkaEU.ToUpper()
////Тут введем проверку на Марку b и потом что то еще
if (MarkaEU.ToUpper() == MarkaRZDN.ToUpper())
{
ScanWeigth -= WEIGHT_EU;
_tblEU.Rows.Add(row1);
WebReference.Relmuch EUT = new WebReference.Relmuch();
EUT.LABEL = EU;
EUT.CODEAUTOMATIC = 5;
listEU.Add(EUT);
}
else if (MarkaRZDNList.IndexOf(MarkaEU.ToUpper()) != -1)
{
//вкошмарим поиск
ScanWeigth -= WEIGHT_EU;
_tblEU.Rows.Add(row1);
WebReference.Relmuch EUT = new WebReference.Relmuch();
EUT.LABEL = EU;
EUT.CODEAUTOMATIC = 5;
listEU.Add(EUT);
}
else
{
OpenNETCF.Media.SystemSounds.Beep.Play();
Thread.Sleep(100);
OpenNETCF.Media.SystemSounds.Beep.Play();
}
dataGridEu.BeginInvoke(new Action(() =>
{
dataGridEu.DataSource = _tblEU;
}));
labelCountScan.BeginInvoke(new Action(() =>
{
labelCountScan.Text = listEU.Count.ToString();
}));
labelMX.BeginInvoke(new Action(() =>
{
//Вычитаем и обновляем тонны
labelMX.Text = "Осталось: " + Math.Round(ScanWeigth, 2).ToString() + " т.";
if (ScanWeigth <= 0)
{
labelMX.ForeColor = Color.White;
}
else
{
labelMX.ForeColor = Color.Tomato;
}
}));
}
catch (Exception exp)
{
CLog.WriteException("WarehousePost.cs", "bcr_BarcodeRead", exp.Message);
//MessageBox.Show(exp.Message);
}
}
public static void runTests()
{
// [ref] http://www.sqlite.org/c3ref/open.html
// [ref] http://sqlite.phxsoftware.com/forums/t/130.aspx
string[] db_files =
{
".\\data\\database\\sqlite3_test1.db",
".\\data\\database\\sqlite3_test2.db",
};
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionStr_))
{
connection.Open();
for (int i = 0; i < db_files.Length; ++i)
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE '{0}' AS attched_db_{1}", db_files[i], i), connection))
cmd.ExecuteNonQuery();
}
#if false
{
string sql = string.Format("SELECT * FROM {0}", colorsTable_);
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
SQLiteDataReader reader = null;
try
{
//reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
Console.WriteLine("ID: {0}, Name: {1}, HexChars: {2}, Description: {3}, HexCode: {4:X}", reader["id"], reader["name"], reader["hexchars"], (reader.IsDBNull(3) ? "null" : reader["description"]), (reader.IsDBNull(4) ? "null" : reader["hexcode"].ToString()));
}
}
finally
{
reader.Close();
}
}
}
{
string sql = string.Format("SELECT * FROM attched_db_{0}.{1}", 0, colorsTable_);
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
Console.WriteLine("ID: {0}, Name: {1}, HexChars: {2}, Description: {3}, HexCode: {4}", reader["id"], reader["name"], reader["hexchars"], (reader.IsDBNull(3) ? "null" : reader["description"]), (reader.IsDBNull(4) ? "null" : string.Format("0x{0:X}", (long)reader["hexcode"])));
}
}
finally
{
reader.Close();
}
}
}
#else
{
#if true
StringBuilder builder = new StringBuilder();
builder.AppendFormat("SELECT * FROM {0}", colorsTable_);
for (int i = 0; i < db_files.Length; ++i)
{
builder.AppendFormat(" UNION SELECT * FROM attched_db_{1}.{0}", colorsTable_, i);
}
string sql = builder.ToString();
#else
string sql = string.Format("SELECT * FROM {0} UNION SELECT * FROM attched_db_{1}.{0}", colorsTable_, 0);
#endif
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
SQLiteDataReader reader = null;
try
{
//reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
Console.WriteLine("ID: {0}, Name: {1}, HexChars: {2}, Description: {3}, HexCode: {4}", reader["id"], reader["name"], reader["hexchars"], (reader.IsDBNull(3) ? "null" : reader["description"]), (reader.IsDBNull(4) ? "null" : string.Format("0x{0:X}", (long)reader["hexcode"])));
}
}
finally
{
reader.Close();
}
}
}
#endif
{
// [ref] http://longweekendmobile.com/2010/05/29/how-to-attach-multiple-sqlite-databases-together/
string sql = string.Format("SELECT name FROM attched_db_{0}.sqlite_master WHERE type='{1}'", 0, colorsTable_);
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
SQLiteDataReader reader = null;
try
{
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
Console.WriteLine(reader["name"]);
}
}
finally
{
reader.Close();
}
}
}
}
}
catch (System.Data.SQLite.SQLiteException e)
{
Console.WriteLine("SQLite error: {0}", e.Message);
}
}
//подія закриття вікна
private void FrmGrading_FormClosing(object sender, FormClosingEventArgs e)
{
//знищення об'єктів БД
dataReader.Close();
command = null;
}
private void InventoryFieldGeneration()
{
SQLiteConnection conn = new SQLiteConnection(DATABASE);
conn.Open();
var command = conn.CreateCommand();
GameColumn.Children.Clear();
Qty.Children.Clear();
Count.Children.Clear();
Price.Children.Clear();
Label gameColLabel = new Label();
Label conName = new Label();
Label platCount = new Label();
Label priceLabel = new Label();
gameColLabel.Content = "Game";
conName.Content = "Console";
platCount.Content = "Quantity Sold";
priceLabel.Content = "Total Value";
gameColLabel.FontWeight = FontWeights.ExtraBold;
conName.FontWeight = FontWeights.ExtraBold;
platCount.FontWeight = FontWeights.ExtraBold;
priceLabel.FontWeight = FontWeights.ExtraBold;
GameColumn.Children.Add(gameColLabel);
Count.Children.Add(conName);
Qty.Children.Add(platCount);
Price.Children.Add(priceLabel);
command.CommandText = "SELECT s.game, s.plat, t.quantity, CASE WHEN s.price < 0 THEN 0"
+ " ELSE s.price END from transactions t "
+ " inner join (SELECT g.name game, b.name plat, b.price, b.game_id, b.platform_id "
+ " FROM games g INNER JOIN(SELECT p.name, m.price, game_id, platform_id from "
+ " multiplat_games m inner join platforms p on p.id = m.platform_id) b on "
+ " g.id = b.game_id) s on s.game_id = t.game_id and s.platform_id = t.platform_id"
+ " where datetime(strftime('%d','now'), 'localtime')-strftime('%d',t.time) < " + timeFrame + " order by Game DESC;";
SQLiteDataReader sdr = command.ExecuteReader();
List <String> tempgameNames = new List <String>();
List <String> tempconsoleNames = new List <String>();
List <int> tempgameQuantity = new List <int>();
List <int> tempgameValue = new List <int>();
List <String> gameNames = new List <String>();
List <String> consoleNames = new List <String>();
List <int> gameQuantity = new List <int>();
List <int> gameValue = new List <int>();
while (sdr.Read())
{
tempgameNames.Add(sdr.GetString(0));
tempconsoleNames.Add(sdr.GetString(1));
tempgameQuantity.Add(sdr.GetInt32(2));
tempgameValue.Add(sdr.GetInt32(3));
}
sdr.Close();
int tempQty = 0;
bool flag = false;
for (int i = 0; i < tempgameNames.Count() - 1; i++)
{
if (i == tempgameNames.Count() - 2)
{
if (tempgameNames[i].Equals(tempgameNames[i + 1]) && tempconsoleNames[i].Equals(tempconsoleNames[i + 1]))
{
tempQty += tempgameQuantity[i] + tempgameQuantity[i + 1];
gameNames.Add(tempgameNames[i]);
consoleNames.Add(tempconsoleNames[i]);
gameQuantity.Add(tempQty);
gameValue.Add((tempgameValue[i] * tempQty));
}
else
{
if (flag)
{
gameQuantity.Add(tempQty);
gameValue.Add((tempgameValue[i] * tempQty));
}
else
{
gameQuantity.Add(tempgameQuantity[i]);
gameValue.Add((tempgameValue[i] * tempgameQuantity[i]));
}
gameNames.Add(tempgameNames[i]);
consoleNames.Add(tempconsoleNames[i]);
gameNames.Add(tempgameNames[i + 1]);
consoleNames.Add(tempconsoleNames[i + 1]);
gameQuantity.Add(tempgameQuantity[i + 1]);
gameValue.Add((tempgameValue[i + 1] * tempgameQuantity[i + 1]));
break;
}
}
else if (tempgameNames[i].Equals(tempgameNames[i + 1]) && tempconsoleNames[i].Equals(tempconsoleNames[i + 1]))
{
flag = true;
tempQty += tempgameQuantity[i] + tempgameQuantity[i + 1];
}
else if (flag)
{
gameNames.Add(tempgameNames[i]);
consoleNames.Add(tempconsoleNames[i]);
gameQuantity.Add(tempQty);
gameValue.Add((tempgameValue[i] * tempQty));
flag = false;
tempQty = 0;
}
else
{
gameNames.Add(tempgameNames[i]);
consoleNames.Add(tempconsoleNames[i]);
gameQuantity.Add(tempgameQuantity[i]);
gameValue.Add((tempgameValue[i] * tempgameQuantity[i]));
}
}
int tmpQ = 0;
int tmpP = 0;
String tmpG;
String tmpC;
for (int k = 0; k < gameNames.Count() - 1; k++)
{
for (int i = 0; i < gameNames.Count() - 1; i++)
{
if (gameQuantity[i + 1] > gameQuantity[i])
{
tmpQ = gameQuantity[i];
tmpP = gameValue[i];
tmpG = gameNames[i];
tmpC = consoleNames[i];
gameQuantity[i] = gameQuantity[i + 1];
gameValue[i] = gameValue[i + 1];
gameNames[i] = gameNames[i + 1];
consoleNames[i] = consoleNames[i + 1];
gameQuantity[i + 1] = tmpQ;
gameValue[i + 1] = tmpP;
gameNames[i + 1] = tmpG;
consoleNames[i + 1] = tmpC;
}
}
}
for (int i = 0; i < gameNames.Count; i++)
{
Label name = new Label();
Label platform = new Label();
Label quantity = new Label();
Label price = new Label();
name.Content = gameNames[i];
platform.Content = consoleNames[i];
quantity.Content = gameQuantity[i];
price.Content = gameValue[i];
GameColumn.Children.Add(name);
Count.Children.Add(platform);
Qty.Children.Add(quantity);
Price.Children.Add(price);
}
conn.Close();
}
// событие кнопки "Расчет"
private void button1_Click_1(object sender, EventArgs e)
{
// подключение к бд
db = new SQLiteConnection("Data Source=" + dbFileName + ";");
sqlCmd = new SQLiteCommand();
if (!File.Exists(dbFileName))
{
SQLiteConnection.CreateFile(dbFileName);
}
db.Open();
sqlCmd.Connection = db;
// чистка таблица расчетов
dataGridView3.Rows.Clear();
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
// получение конечной скидки по имени
sqlCmd.CommandText = "select id from tree where name = @name";
sqlCmd.Parameters.AddWithValue("@name", (string)dataGridView1[0, i].Value);
SQLiteDataReader reader = sqlCmd.ExecuteReader();
try
{
reader.Read();
int id = Convert.ToInt32(reader["id"]);
reader.Close();
sqlCmd.CommandText = "select discount from totaldiscount where id = @id";
sqlCmd.Parameters.AddWithValue("@id", id);
reader = sqlCmd.ExecuteReader();
reader.Read();
double totaldiscount = Convert.ToDouble(reader["discount"]);
reader.Close();
// получение цены
double price = Convert.ToDouble(dataGridView1[1, i].Value);
// расчет
double res = price - price * totaldiscount / 100;
// получение списка скидок
string totalDiscountList = "";
for (int j = 0; j < dataGridView2.Rows.Count; j++)
{
if (id == Convert.ToInt32(dataGridView2[0, j].Value))
{
totalDiscountList = (string)dataGridView2[1, j].Value;
break;
}
}
// список скидок родителей
string discountParent = "";
// скидка в выбраном магазине
string discountMarket = totalDiscountList;
if (totalDiscountList.LastIndexOf('+') > 0)
{
discountParent = totalDiscountList.Substring(0, totalDiscountList.LastIndexOf('+'));
discountMarket = totalDiscountList.Remove(0, totalDiscountList.LastIndexOf('+') + 1);
}
// заполнение таблицы расчетов
dataGridView3.Rows.Add(price, discountMarket, discountParent, res);
}
catch (SQLiteException exc)
{
MessageBox.Show("Error: " + exc);
}
catch (SystemException sexc)
{
MessageBox.Show("Error: Выберите город перед расчетом");
}
}
db.Close();
}
private void importFileToDb(SQLiteConnection conn, string filepath, string table)
{
try
{
Properties.Settings.Default.IMPORTCOLUMNSEPARATOR = detectFileSeparator(new StreamReader(filepath), 5); //separator detection for each file
SQLiteCommand command = new SQLiteCommand("PRAGMA table_info(" + table + ");", conn);
string insertText = "INSERT INTO " + table + " (";
int columnCount = 0;
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
insertText = insertText + '"' + reader[1] + '"' + ',';
columnCount++;
}
reader.Close();
Log("Importing file: " + filepath + " to table: " + table + " columns expected: " + columnCount, true);
SQLiteTransaction trans = conn.BeginTransaction();
command.Transaction = trans;
int rowind = 1;
int rowsWithMissingColumns = 0;
int notifyInterval = 100000;
int notifyRow = notifyInterval;
Stopwatch sw = new Stopwatch();
sw.Start();
CsvReader csv = new CsvReader(new StreamReader(filepath), true, Properties.Settings.Default.IMPORTCOLUMNSEPARATOR);
//int fieldCount = csv.FieldCount;
csv.MissingFieldAction = MissingFieldAction.ReplaceByNull;
string[] headers = csv.GetFieldHeaders();
rowind++;
if (columnCount == 0)
{
textBox.AppendText('\n' + "Table not found, creating table: " + table);
createTableFromFile(conn, filepath, table);
foreach (string header in headers)
{
insertText = insertText + '"' + header + '"' + ',';
columnCount++;
}
}
insertText = insertText.Remove(insertText.Length - 1) + ") values (";
for (int i = 0; i < columnCount; i++)
{
insertText = insertText + "?,";
}
insertText = insertText.Remove(insertText.Length - 1) + ");";
command.CommandText = insertText;
List <SQLiteParameter> parameters = new List <SQLiteParameter>();
for (int i = 0; i < columnCount; i++)
{
parameters.Add(new SQLiteParameter("@P" + i));
}
foreach (SQLiteParameter par in parameters)
{
command.Parameters.Add(par);
}
int colind;
while (csv.ReadNextRecord())
{
colind = 0;
foreach (SQLiteParameter par in parameters)
{
if (csv[colind] == null)
{
rowsWithMissingColumns++;
par.Value = DBNull.Value;
}
else if (csv[colind] == "")
{
par.Value = DBNull.Value;
}
else
{
par.Value = csv[colind];
}
colind++;
}
try
{
command.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
Log(ex.Message.ToString());
colind = 0;
foreach (SQLiteParameter par in parameters)
{
Log(string.Format("{0} = {1};", headers[colind], par.Value));
colind++;
}
}
rowind++;
if (rowind == notifyRow)
{
notifyRow = notifyRow + notifyInterval;
trans.Commit();
Log("Imported " + string.Format("{0:#,0}", rowind) + " rows...");
trans = conn.BeginTransaction();
command.Transaction = trans;
}
}
csv.Dispose();
trans.Commit();
sw.Stop();
Log("File: " + filepath + " with " + string.Format("{0:#,0}", rowind) + " rows (" + rowsWithMissingColumns + " with missing columns) imported to table: " + table + " in: " + sw.Elapsed.Minutes + " Min(s) " + sw.Elapsed.Seconds + " Sec(s)", true);
}
catch (IOException ex)
{
Log(ex.Message.ToString());
}
}
private void Payment_Load(object sender, EventArgs e)
{
DB = new SQLiteConnection("Data Source = TestDB.db; Version=3;"); //Подключение к БД
DB.Open(); //Открытие коннекта
int m = 1;
for (int i = 0; i < m; i++)
{
cmbbxCategories.Items.Clear();
}
/// <summary>
/// Вывод списка
/// категорий в cmbbxCategories
/// </summary>
for (int i = 0; i < m; i++)
{
SQLiteCommand command6 = new SQLiteCommand("Select name_category from category", DB);
SQLiteDataReader sqlReader6 = null;
sqlReader6 = command6.ExecuteReader();
while (sqlReader6.Read())
{
cmbbxCategories.Items.Add(Convert.ToString(sqlReader6["name_category"]));
}
if (sqlReader6 != null)
{
sqlReader6.Close();
}
command6.ExecuteNonQuery();
}
SQLiteCommand command5 = new SQLiteCommand("Select * from employees", DB);//Запрос на вывод всей таблицы employees
SQLiteDataReader sqlReader5 = null;
sqlReader5 = command5.ExecuteReader();
/// <summary>
/// Вывод таблицы
/// employees в dgvEmployees
/// </summary>
while (sqlReader5.Read())
{
SQLiteCommand cmd1 = DB.CreateCommand();
cmd1.CommandText = "select name_category from category where num_category = '" + sqlReader5["category"] + "'";
string cat = cmd1.ExecuteScalar().ToString();
cmd1.ExecuteNonQuery();
dgvEmployees.Rows.Add(Convert.ToInt32(sqlReader5["num_employee"]), sqlReader5["surname"], sqlReader5["firstname"], sqlReader5["middlename"],
cat, sqlReader5["start_date"]);
}
if (sqlReader5 != null)
{
sqlReader5.Close();
}
command5.ExecuteNonQuery();
/// <summary>
/// Вывод списка руководителей и
/// их категорий, суммы з/п подчинённых за 3 месяца,
/// макс., мин. и ср. з/п подчинённых за полгода
/// </summary>
SQLiteCommand command = new SQLiteCommand("SELECT employees.surname || ' '|| employees.firstname || ' ' || employees.middlename AS [SFM], " +
"category.name_category as [categorys], sum(salary.sal) AS[Sum]," +
"(SELECT max(salary.sal) || ' р.- max, ' || min(salary.sal) || ' р.- min, ' || round(avg(salary.sal), 2)||' р.- avg'" +
"WHERE salary.payday BETWEEN @date1 AND @date2) AS [MMF]" +
" FROM category INNER JOIN employees ON category.num_category = employees.category INNER JOIN " +
"leaders ON employees.num_employee = leaders.id_leader INNER JOIN salary ON leaders.id_subordinate = " +
"salary.num_employee WHERE salary.payday BETWEEN @date3 AND @date4 GROUP BY id_leader ORDER BY category.name_category DESC ", DB);
command.Parameters.Add("@date1", DbType.String).Value = DateTime.Today.AddMonths(-6).ToString("yyyy-MM-dd"); //вычетание 6 месяцев из текущей даты
command.Parameters.Add("@date2", DbType.String).Value = DateTime.Now.ToString("yyyy-MM-dd");
command.Parameters.Add("@date3", DbType.String).Value = DateTime.Today.AddMonths(-3).ToString("yyyy-MM-dd"); //вычетание 3 месяцев из текущей даты
command.Parameters.Add("@date4", DbType.String).Value = DateTime.Now.ToString("yyyy-MM-dd");
SQLiteDataReader sqlReader = null;
sqlReader = command.ExecuteReader();
while (sqlReader.Read())
{
dtgvPaySub.Rows.Add(sqlReader["SFM"], sqlReader["categorys"], sqlReader["Sum"], sqlReader["MMF"]);
}
if (sqlReader != null)
{
sqlReader.Close();
}
command.ExecuteNonQuery();
/// <summary>
/// Отображение всех сотрудников компании и
/// их зарплаты за полгода(месяца горизонтально)
/// </summary>
SQLiteCommand command2 = new SQLiteCommand(" select employees.surname||' '||employees.firstname||' '||employees.middlename as 'SFM2'," +
" group_concat((select salary.sal where payday like '%'||strftime('%Y-%m', date('now','start of month','-5 month'))||'%')) as 'tecdate_6'," +
" group_concat((select salary.sal where payday like '%'||strftime('%Y-%m', date('now','start of month','-4 month'))||'%')) as 'tecdate_5'," +
" group_concat((select salary.sal where payday like '%'||strftime('%Y-%m', date('now','start of month','-3 month'))||'%')) as 'tecdate_4'," +
" group_concat((select salary.sal where payday like '%'||strftime('%Y-%m', date('now','start of month','-2 month'))||'%')) as 'tecdate_3'," +
" group_concat((select salary.sal where payday like '%'||strftime('%Y-%m', date('now','start of month','-1 month'))||'%')) as 'tecdate_2'," +
" group_concat((select salary.sal where payday like '%'||strftime('%Y-%m', date('now'))||'%')) as 'tecdate'" +
" from salary inner join employees on salary.num_employee=employees.num_employee group by employees.num_employee", DB);
dgvZpPolGod.Columns[1].HeaderText = DateTime.Today.AddMonths(-5).ToString("yyyy-MM");
dgvZpPolGod.Columns[2].HeaderText = DateTime.Today.AddMonths(-4).ToString("yyyy-MM");
dgvZpPolGod.Columns[3].HeaderText = DateTime.Today.AddMonths(-3).ToString("yyyy-MM");
dgvZpPolGod.Columns[4].HeaderText = DateTime.Today.AddMonths(-2).ToString("yyyy-MM");
dgvZpPolGod.Columns[5].HeaderText = DateTime.Today.AddMonths(-1).ToString("yyyy-MM");
dgvZpPolGod.Columns[6].HeaderText = DateTime.Today.ToString("yyyy-MM");
SQLiteDataReader sqlReader2 = null;
sqlReader2 = command2.ExecuteReader();
while (sqlReader2.Read())
{
dgvZpPolGod.Rows.Add(sqlReader2["SFM2"], sqlReader2["tecdate_6"], sqlReader2["tecdate_5"], sqlReader2["tecdate_4"], sqlReader2["tecdate_3"], sqlReader2["tecdate_2"], sqlReader2["tecdate"]);
}
if (sqlReader2 != null)
{
sqlReader2.Close();
}
command2.ExecuteNonQuery();
///<summary>
///Вывод среднего размера зарплат
///в рамках должности (группы сотрудников)
///</summary>
SQLiteCommand command3 = new SQLiteCommand(" select category.name_category as 'nameCategory',round(avg(salary.sal),2) " +
"as 'avgPay' from salary inner join employees on salary.num_employee=employees.num_employee " +
"inner join category on employees.category = category.num_category group by category.name_category " +
"order by category.name_category desc", DB);
SQLiteDataReader sqlReader3 = null;
sqlReader3 = command3.ExecuteReader();
while (sqlReader3.Read())
{
dtgAvg.Rows.Add(sqlReader3["nameCategory"], sqlReader3["avgPay"]);
}
if (sqlReader3 != null)
{
sqlReader3.Close();
}
command3.ExecuteNonQuery();
}
private void CommitRead()
{
dataReader.Close();
transaction.Commit();
connection.Close();
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.Text == "")
{
MessageBox.Show("Selezionare un prodotto.", "Errore", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
if (numericUpDown1.Text == "0")
{
MessageBox.Show("Selezionare il numero di copie.", "Errore", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
string message = "Stai per stampare " + numericUpDown1.Text + " copie del prodotto: " + comboBox1.Text + "." + "\n" + "Continuare?";
string caption = "Conferma di stampa";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
result = MessageBox.Show(message, caption, buttons);
if (result == System.Windows.Forms.DialogResult.Yes)
{
m_dbConnection = new SQLiteConnection(strConn);
string sql = "SELECT Barcode FROM Prodotti WHERE Nome = '" + comboBox1.Text + "';";
command = new SQLiteCommand(sql, m_dbConnection);
string search = "";
m_dbConnection.Open();
SQLiteDataReader Ricerca = command.ExecuteReader();
while (Ricerca.Read())
{
search = search + Ricerca["Barcode"];
}
Ricerca.Close();
m_dbConnection.Close();
byte[] stampa = new byte[search.Length];
for (int i = 0; i < search.Length; i++)
{
stampa[i] = (byte)search[i];
}
byte[] spazio = { 0x0A, 0x0A, 0x0A, 0x0A, 0x0A, 0x0A, 0x0A };
byte[] code39 = { 0x1D, 0x6B, 0x04 };
byte[] fine = { 0x00 };
byte[] taglio = { 0x1C, 0xC0, 0x34 };
serialPort1.PortName = "COM40";
serialPort1.BaudRate = 115200;
serialPort1.DataBits = 8;
serialPort1.Parity = Parity.None;
serialPort1.StopBits = StopBits.One;
for (int i = 0; i < numericUpDown1.Value; i++)
{
serialPort1.Open();
serialPort1.Write(spazio, 0, spazio.Length);
serialPort1.Write(code39, 0, code39.Length);
serialPort1.Write(stampa, 0, stampa.Length);
serialPort1.Write(fine, 0, fine.Length);
serialPort1.Write(taglio, 0, taglio.Length);
serialPort1.Close();
}
}
}
}
}
public void UpdateGrid()
{
int brojac = -1; //Counter used to count the id of the datagridview field
LoginForm fasdorm1 = new LoginForm();
masterusername = fasdorm1.Getuser();
masterpassword = fasdorm1.Getpassword(); //Get the username and password of the user
dataGridView1.Rows.Clear(); //Clear all the rows
SQLiteConnection dbConnection;
dbConnection =
new SQLiteConnection("Data Source=" + Fajl() + ";Version=3;");
try
{
//Connect to the file
dbConnection.Open();
int idbroj = 1; //promenliva za skladiranje na id brojot od posledniot rekord
string maxid = "SELECT MAX(ID) FROM passwords;"; //komanda za selektiranje na posledniot rekord
string sql = "SELECT * FROM passwords ORDER BY id ";
SQLiteCommand maxidkomanda = new SQLiteCommand(maxid, dbConnection);
SQLiteDataReader reader1 = maxidkomanda.ExecuteReader();
while (reader1.Read())
{
idbroj = reader1.GetInt32(0);
break; //Get the highest ID number from the records, as in the last record entered
}
reader1.Close();
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// if (int.Parse(reader["id"].ToString()) <= idbroj )
// {
if (reader["id"].ToString() != "1") //If the record's ID isn't 1, because the first record is used to store the encrypted password
{
if (reader["visible"].ToString() == "1") //if the record's visible field is set to 1, because the "deleted" record's IDs are set to 0
{
brojac++; //Increment the counter (move to the next row of dataGridView)
dbid = reader["id"].ToString();
dburl = reader["URL"].ToString();
dbname = reader["name"].ToString();
dbusername = reader["username"].ToString();
dbpassword = reader["password"].ToString();
dbnotes = reader["notes"].ToString(); //Get the record's data and turn it into strings
dataGridView1.Rows.Add();
dataGridView1.Rows[brojac].Cells[0].Value = (int.Parse(dbid)).ToString();
dataGridView1.Rows[brojac].Cells[1].Value = Cryptography.Decrypt(dburl, masterpassword);
dataGridView1.Rows[brojac].Cells[2].Value = Cryptography.Decrypt(dbname, masterpassword);
dataGridView1.Rows[brojac].Cells[3].Value = Cryptography.Decrypt(dbusername, masterpassword);
dataGridView1.Rows[brojac].Cells[4].Value = Cryptography.Decrypt(dbpassword, masterpassword);
dataGridView1.Rows[brojac].Cells[5].Value = Cryptography.Decrypt(dbnotes, masterpassword); //Input the record's data into the dataGridView table after it gets decrypted with the password gotten from the login form
}
}
}
dbConnection.Dispose();
dbConnection.Close();
reader.Close(); //Close the connection
}
catch (Exception ex)
{
// MessageBox.Show(ex.Message);
}
}
private void btnCreateRef_Click(object sender, EventArgs e)
{
//перевірка існування шаблону довідки
if (!File.Exists(System.IO.Path.GetFullPath(@"template.docx")))
{
//повідомлення про помилку
MessageBox.Show("Файл шаблону не знайдений!", "Увага!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;//завершення функції
}
//перевірка, що всі оцінки виставлені
if (CheckInputPoints(dataGridView1) == false)
{
return;
}
DialogResult result = MessageBox.Show("Створити довідку?", "Підтвердження створення довідки", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
//збереження файлу
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Microsoft Word(*.docx)|*.docx"; //встановлення фільтру типу файлів
saveFileDialog.Title = "Сохранение документа"; //заголовок вікна
//назва файлу за замовчуванням
saveFileDialog.FileName = "Академічна довідка_" + student.secNameUA + " " + student.firstNameUA[0] + "." + student.petrNameUA[0] + ".";
string initPath = @"Created"; // почтаковий шлях
saveFileDialog.InitialDirectory = Path.GetFullPath(initPath);
saveFileDialog.RestoreDirectory = true;
DialogResult dialogResult;
try
{
//отримання шляху від користувача
dialogResult = saveFileDialog.ShowDialog();
if (dialogResult == DialogResult.Cancel)
{
return;
}
}
catch { return; }
//сортування предметів посемемтрово
SortSemester(allCheckedList);
//створюємо об'єкт додатку ворд
application = new Microsoft.Office.Interop.Word.Application();
Object templatePathObj = null;
if (FrmInputData.templatePath == null)
{
// створюємо шлях файлу
templatePathObj = System.IO.Path.GetFullPath(@"template.docx");
}
else
{
//шлях до заповненного шаблону
templatePathObj = FrmInputData.templatePath;
}
//відкриваємо файл
application.Documents.Open(templatePathObj);
if (FrmInputData.templatePath == null)
{
//заповнення номеру довідки
application.Documents[1].Tables[2].Cell(1, 1).Range.Font.Size = 24;
application.Documents[1].Tables[2].Cell(1, 1).Range.Bold = 0;
application.Documents[1].Bookmarks["numberreference"].Range.Text = "№ " + student.orderNumber;
//ім'я
application.Documents[1].Bookmarks["Name"].Range.Text = student.secNameUA + " " + student.firstNameUA + " " + student.petrNameUA;
application.Documents[1].Bookmarks["Name2"].Range.Text = student.secNameEN + " " + student.firstNameEN + " " + student.petrNameEN;;
//день початку (українське поле)
application.Documents[1].Bookmarks["day1_1"].Range.Text = (student.dateStart.Day < 10) ? "0" + student.dateStart.Day.ToString() :
student.dateStart.Day.ToString();;
//місяць і рік початку (українське поле)
application.Documents[1].Bookmarks["lastday1_1"].Range.Text = (student.dateStart.Month < 10) ? "0" + student.dateStart.Month.ToString() + "." + student.dateStart.Year.ToString() :
student.dateStart.Month.ToString() + "." + student.dateStart.Year.ToString();
//день завершення (українське поле)
application.Documents[1].Bookmarks["day1_2"].Range.Text = (student.dateFinish.Day < 10) ? "0" + student.dateFinish.Day.ToString() :
student.dateFinish.Day.ToString();
//місяць і рік завершення (українське поле)
application.Documents[1].Bookmarks["lastday1_2"].Range.Text = (student.dateFinish.Month < 10) ? "0" + student.dateFinish.Month.ToString() + "." + student.dateFinish.Year.ToString() :
student.dateFinish.Month.ToString() + "." + student.dateFinish.Year.ToString();
//день початку (англійське поле)
application.Documents[1].Bookmarks["day2_1"].Range.Text = (student.dateStart.Day < 10) ? "0" + student.dateStart.Day.ToString() :
student.dateStart.Day.ToString();
//місяць і рік початку (англійське поле)
application.Documents[1].Bookmarks["lastday2_1"].Range.Text = (student.dateStart.Month < 10) ? "0" + student.dateStart.Month.ToString() + "." + student.dateStart.Year.ToString() :
student.dateStart.Month.ToString() + "." + student.dateStart.Year.ToString();
//день завершення (англійське поле)
application.Documents[1].Bookmarks["day2_2"].Range.Text = (student.dateFinish.Day < 10) ? "0" + student.dateFinish.Day.ToString() : student.dateFinish.Day.ToString();
//місяць і рік початку (англійське поле)
application.Documents[1].Bookmarks["lastday2_2"].Range.Text = (student.dateFinish.Month < 10) ? "0" + student.dateFinish.Month.ToString() + "." + student.dateFinish.Year.ToString() :
student.dateFinish.Month.ToString() + "." + student.dateFinish.Year.ToString();
//день народження українське поле
application.Documents[1].Bookmarks["Birth1_1Rus"].Range.Text = (student.dateBirthday.Day < 10) ? "0" + student.dateBirthday.Day.ToString() :
student.dateBirthday.Day.ToString();
//місяць і рік народження українське поле
application.Documents[1].Bookmarks["Birth1_2Rus"].Range.Text = (student.dateBirthday.Month < 10) ? "0" + student.dateBirthday.Month.ToString() + "." + student.dateBirthday.Year.ToString() :
student.dateBirthday.Month.ToString() + "." + student.dateBirthday.Year.ToString();
//день народження англійське поле
application.Documents[1].Bookmarks["Birth2_1En"].Range.Text = (student.dateBirthday.Day < 10) ? "0" + student.dateBirthday.Day.ToString() :
student.dateBirthday.Day.ToString();
//місяць і рік народження англійське поле
application.Documents[1].Bookmarks["Birth2_2En"].Range.Text = (student.dateBirthday.Month < 10) ? "0" + student.dateBirthday.Month.ToString() + "." + student.dateBirthday.Year.ToString() :
student.dateBirthday.Month.ToString() + "." + student.dateBirthday.Year.ToString();
//місце народження
application.Documents[1].Bookmarks["CountryRus"].Range.Text = student.placeBirthUA;
application.Documents[1].Bookmarks["CountryEn"].Range.Text = student.placeBirthEn;
//причина відрахування
application.Documents[1].Bookmarks["ExpelledUa"].Range.Text = student.expelled;
application.Documents[1].Bookmarks["ExpelledEn"].Range.Text = student.expelledEN[student.numexpelled];
//запит на назву спеціальності за кодом
command = new SQLiteCommand("SELECT NameUA, NameEN FROM Specialty WHERE idSpecialty = @id", Program.conn);
//додаваення параметрів
command.Parameters.Add(new SQLiteParameter("@id", student.specNum.ToString()));
dataReader = command.ExecuteReader(); //виконання команди
while (dataReader.Read())
{
//номер спеціальності
application.Documents[1].Bookmarks["CodeRus"].Range.Text = student.specNum.ToString() + " " + dataReader[0].ToString();
application.Documents[1].Bookmarks["CodeEn"].Range.Text = student.specNum.ToString() + " " + dataReader[1].ToString();
}
dataReader.Close();//закриття об'єкту БД
//спеціалізація
command = new SQLiteCommand("SELECT name_ua_spec, name_en_spec FROM specialization WHERE name_ua_spec = @name", Program.conn);
//додаваення параметрів
command.Parameters.Add(new SQLiteParameter("@name", student.specialization));
dataReader = command.ExecuteReader();//виконання команди
while (dataReader.Read())
{
//номер спеціалізації
application.Documents[1].Bookmarks["StudyProgramRus"].Range.Text = dataReader[0].ToString();
application.Documents[1].Bookmarks["StudyProgramEn"].Range.Text = dataReader[1].ToString();
}
//назва навчального закладу
command = new SQLiteCommand("SELECT NameUA, NameEN FROM College", Program.conn);
dataReader = command.ExecuteReader();//виконання команди
while (dataReader.Read())
{
//вставка назви закладу
application.Documents[1].Bookmarks["InstNameRus"].Range.Text = dataReader[0].ToString();
application.Documents[1].Bookmarks["InstNameEn"].Range.Text = dataReader[1].ToString();
}
//назва відділення
command = new SQLiteCommand("SELECT NameUA, NameEN FROM Department WHERE NameUA = @name", Program.conn);
command.Parameters.Add(new SQLiteParameter("@name", student.faculty));
dataReader = command.ExecuteReader();//виконання команди
while (dataReader.Read())
{
//вставка назви відділення
application.Documents[1].Bookmarks["FacultyNameRus"].Range.Text = dataReader[0].ToString();
application.Documents[1].Bookmarks["FacultyNameEn"].Range.Text = dataReader[1].ToString();
}
}
//заповнення таблиці з успішностю
if (liFisrt.Count != 0)
{
GradingBall(liFisrt);
}
if (liSecond.Count != 0)
{
GradingBall(liSecond);
}
if (liThree.Count != 0)
{
GradingBall(liThree);
}
if (liFour.Count != 0)
{
GradingBall(liFour);
}
if (liFive.Count != 0)
{
GradingBall(liFive);
}
if (liSix.Count != 0)
{
GradingBall(liSix);
}
if (liSeven.Count != 0)
{
GradingBall(liSeven);
}
if (liEight.Count != 0)
{
GradingBall(liEight);
}
//вставка в поле номеру наказу
application.Documents[1].Bookmarks["OrderUA"].Range.Text = "№ " + student.orderNumber;
application.Documents[1].Bookmarks["OrderEN"].Range.Text = "№ " + student.orderNumber;
try
{
if (dialogResult == DialogResult.OK) //якщо настиснуто ОК
{
//збереження файлу за шляхом користувача
application.ActiveDocument.SaveAs2(saveFileDialog.FileName);
//закриття додатку ворд
application.ActiveDocument.Close();
application.Quit();
//створюємо об'єкт додатку ворд
application = new Word.Application();
templatePathObj = saveFileDialog.FileName;
//відкриваємо файл
application.Documents.Open(templatePathObj);
//повідомлення про успішність операції
MessageBox.Show("Академічна довідка успішно створена!", "Увага!", MessageBoxButtons.OK, MessageBoxIcon.Information);
application.Visible = true; //відображення документу
//якшо довідка була раніше збережена -- видаляємо
if (createReference2.createReference.frmOpenReference != null && createReference2.createReference.selectId != null)
{
//запит на видалення збережнної довідки, що створена
SQLiteCommand command2 = new SQLiteCommand("DELETE FROM SavedData WHERE idSave = @idSave", Program.conn);
command2.Parameters.Add(new SQLiteParameter("@idSave", createReference2.createReference.selectId));
//виконання
command2.ExecuteNonQuery();
command2.Dispose();
}
//повернення на стартову сторінку
try
{
createReference2.createReference.frmStart.Visible = true;
}
catch
{
createReference2.createReference.frmOpenReference.frmStart.Visible = true;
}
//закриття всіх вікон
createReference2.createReference.Close();
createReference2.Close();
this.Close();
//збір сміття
GC.Collect();
}
else if (dialogResult == DialogResult.Cancel) //якщо скасовано
{
//закриття додатку ворд
application.ActiveDocument.Close();
application.Quit();
}
}
catch { }
}
else if (result == DialogResult.No) //якщо ні
{
return;
}
else if (result == DialogResult.Cancel)
{
return;
} //якщо скасовано
}
private String getSqlResultsAsString(SQLiteCommand command)
{
StringBuilder sb = new StringBuilder();
object val = null;
try
{
SQLiteDataReader reader = command.ExecuteReader();
if (Properties.Settings.Default.EXPORTCOLUMNNAMES)
{
for (int j = 0; j < reader.FieldCount; j++)
{
sb.Append(reader.GetName(j));
if (j != (reader.FieldCount - 1))
{
sb.Append(Properties.Settings.Default.EXPORTSEPARATOR);
}
}
sb.Append('\n');
}
while (reader.Read())
{
for (int j = 0; j < reader.FieldCount; j++)
{
if (reader.IsDBNull(j) == false)
{
try //very slow with try/catch, but only way to get strings out from non- type sqlite columns for now
{
val = reader.GetDouble(j);
}
catch
{
try
{
//Debug.WriteLine("double cast failed on" + reader[j]);
val = reader.GetString(j);
}
catch (SQLiteException ex)
{
Log("Double and String cast failed on value: " + reader[j] + " with following exception:", true);
Log(ex.Message.ToString(), true);
}
}
}
else
{
val = null;
}
sb.Append(val);
if (j != (reader.FieldCount - 1))
{
sb.Append(Properties.Settings.Default.EXPORTSEPARATOR);
}
}
sb.Append('\n');
}
reader.Close();
}
catch (SQLiteException ex)
{
Log("Export sql query failed with following exception:");
Log(ex.Message.ToString());
}
return(sb.ToString());
}
/// <summary>
/// update表中的数据
/// </summary>
/// <param name="tableName"></param>
private static string UpdateTable(string tableName)
{
Dictionary <string, byte[]> dataDic = new Dictionary <string, byte[]>();
List <string> taskCountList = new List <string>();
SQL_GETLIST = SQL_GETLIST.Replace("@table", tableName);
SQL_UPDATE = SQL_UPDATE.Replace("@table", tableName);
SQLiteCommand getList = new SQLiteCommand(SQL_GETLIST, conn);
try
{
reader = getList.ExecuteReader();
while (reader.Read())
{
// 读取每一行并获取UserData的值
string data = Encoding.Default.GetString(reader["UserData"] as byte[]);
// 如果值中包含509或者508则替换后转回字节数组放入dic
if (data.Contains("\"Result\":509") || data.Contains("\"Result\":508"))
{
data = data.Replace("\"Result\":509", "\"Result\":0");
data = data.Replace("\"Result\":508", "\"Result\":0");
dataDic.Add(reader["rowid"].ToString(), Encoding.Default.GetBytes(data));
// 根据LocalTaskId计算任务数量
if (!taskCountList.Contains(reader["LocalTaskId"].ToString()))
{
taskCountList.Add(reader["LocalTaskId"].ToString());
}
}
}
}
catch (Exception ex)
{
throw new Exception("查找任务失败：" + ex.Message);
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
// 任务数和文件数
int taskCount = taskCountList.Count;
int fileCount = 0;
// 新建事务
SQLiteTransaction trans = conn.BeginTransaction();
try
{
foreach (KeyValuePair <string, byte[]> pair in dataDic)
{
SQLiteCommand cmd = new SQLiteCommand(SQL_UPDATE, conn);
cmd.Transaction = trans;
cmd.Parameters.Add(new SQLiteParameter("@data", pair.Value));
cmd.Parameters.Add(new SQLiteParameter("@id", pair.Key));
fileCount += cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
throw new Exception("破解失败！" + ex.Message);
}
if (fileCount == 0)
{
throw new Exception("没有找到可破解的任务。");
}
return("共处理了" + taskCount + "个任务中的" + fileCount + "个文件");
}
