public cSetTime(int id, string connStr)
{
ID = -1;
db db = new db(connStr);
DbDataReader dbRdr;
try
{
if (!db.dbOpen())
{
throw new Exception();
}
dbRdr = db.getReader("SELECT * FROM ATTENDANCE WHERE ID = " + id);
dbRdr.Read();
setTime(dbRdr);
dbRdr.Close();
}
catch (Exception ex)
{
MessageBox.Show("Ошибка чтения из базы данных: " + ex.Message, "Ошибка базы данных");
id = -1;
}
finally { db.dbClose(); }
}
static public DataSet getPerson(string connStr)
{
string query = "SELECT a.PERSONID, (a.NAME ||' '|| a.FIRSTNAME ||' '|| a.SECONDNAME) FIO FROM PERSON a";
db db = new db(connStr);
DataSet ds = new DataSet();
DataTable table = new DataTable();
db.dbOpen();
table.Load(db.getReader(query));
ds.Tables.Add(table);
db.dbClose();
return(ds);
}
public bool save(string connStr)
{
db db = new db(connStr);
if (!db.dbOpen())
{
return(false);
}
string saveQuery = "";
if (ID < 0)
{
saveQuery = getQuery("i");
}
else
{
saveQuery = getQuery("u");
}
if (!db.executeParamQuery(saveQuery,
new DictionaryEntry("CTRLAREAID", CTRLAREAID),
new DictionaryEntry("CARDID", CARDID),
new DictionaryEntry("PERSONID", PERSONID),
new DictionaryEntry("ADATETIME", ADATETIME),
new DictionaryEntry("IS_ENTRANCE", IS_ENTRANCE),
new DictionaryEntry("COMMENT", COMMENT)
))
{
db.dbClose(); return(false);
}
if (ID < 0)
{
// ID = Convert.ToInt32(db.executeScalar("SELECT LAST_INSERT_ID()"));
}
db.dbClose();
return(true);
}
/*public void getTime(int cardid)
* {
* db db;
* DbDataReader dbRdr;
*
*
* cViewSetTime STL;
* SetTimeArr.Clear();
* string query = "SELECT a.id, a.CTRLAREAID, a.CARDID, a.PERSONID, a.ADATETIME, a.IS_ENTRANCE, a.COMMENT, (p.name || ' ' || p.FIRSTNAME || ' ' || p.SECONDNAME) as FIO "+
* "FROM ATTENDANCE a "+
* "INNER JOIN PERSON p on a.CARDID = p.PERSONID "+
* "WHERE CARDID = "+cardid +
* "order by a.id";
* db = new db();
* try
* {
* if (!db.dbOpen()) throw new Exception("Ошибка подключения к БД.");
* dbRdr = db.getReader(query);
* while (dbRdr.Read())
* {
* STL = new cViewSetTime();
* STL.setViewSetTime(dbRdr);
* SetTimeArr.Add(STL);
* }
* dbRdr.Close();
* db.dbClose();
* }
* catch (Exception ex)
* {
* MessageBox.Show("Ошибка чтения из базы данных: " + ex.Message, "Ошибка базы данных");
* }
* finally { db.dbClose(); }
* }
*/
public DataSet getTimeE(int cardid, DateTime?startDate, DateTime?endDate, string connStr)
{
string query = "SELECT a.id, a.CTRLAREAID, a.CARDID, a.PERSONID, a.ADATETIME, a.IS_ENTRANCE, a.COMMENT, (p.name || ' ' || p.FIRSTNAME || ' ' || p.SECONDNAME) as FIO " +
"FROM ATTENDANCE a " +
"INNER JOIN PERSON p ON a.PERSONID = p.PERSONID " +
"INNER JOIN CARD c ON p.PERSONID = c.PERSONID_IMP " +
"WHERE c.PERSONID_IMP = " + cardid;
if (startDate != null && endDate != null)
{
query += " AND (ADATETIME BETWEEN " + String.Format("'{0}' ", startDate.Value) + "AND " + String.Format("'{0}' ) ", endDate.Value.AddHours(23.59));
}
query += "ORDER BY a.ADATETIME DESC";
db db = new db(connStr);
DataSet ds = new DataSet();
DataTable table = new DataTable();
db.dbOpen();
table.Load(db.getReader(query));
ds.Tables.Add(table);
db.dbClose();
return(ds);
}