public List <Company> GetUserCompany()
{
List <Company> companyList = new List <Company>();
string sql = @"SELECT
C.CompanyGUID
,C.CompanyName
FROM Company C INNER JOIN CompanyUser CU
ON C.CompanyGUID = CU.CompanyGUID
WHERE CU.UserGUID = @UserGUID";
List <System.Data.SQLite.SQLiteParameter> paramList = new List <System.Data.SQLite.SQLiteParameter>();
paramList.Add(new SQLiteParameter("@UserGUID", UserGUID));
NGSConnector connector = new NGSConnector();
SQLiteDataReader reader = connector.execSQLWithResult(sql, paramList);
while (reader.Read())
{
Company company = new Company();
company.FillData(reader.GetString(0), reader.GetString(1));
companyList.Add(company);
}
reader.Close();
return(companyList);
}
public List <DocumentTemplate> GetDocumentTemplateList()
{
List <DocumentTemplate> documentList = new List <DocumentTemplate>();
string sql = @"SELECT
DocumentTemplateGUID, DocumentName, DocumentText, DocumentHint, DocumentType, DocumentEndDate
FROM DocumentTemplate";
NGSConnector connector = new NGSConnector();
SQLiteDataReader reader = connector.execSQLWithResult(sql);
while (reader.Read())
{
DocumentTemplate u = new DocumentTemplate();
u.FillData(reader.GetString(0)
, reader.GetString(1)
, reader.GetString(2)
, reader.IsDBNull(3) ? "" : reader.GetString(3)
, reader.IsDBNull(4) ? "D" : reader.GetString(4)
, reader.IsDBNull(5) ? "N" : reader.GetString(5)
);
documentList.Add(u);
}
reader.Close();
return(documentList);
}
public bool LoginUser(String userName, String userPassword)
{
bool ret = false;
string sql = @"SELECT
UserGUID
,FirstName
,LastName
,UserName
,UserPassword
FROM User
WHERE UserName = @UserName AND UserPassword = @UserPassword";
List <System.Data.SQLite.SQLiteParameter> paramList = new List <System.Data.SQLite.SQLiteParameter>();
paramList.Add(new SQLiteParameter("@UserName", userName));
paramList.Add(new SQLiteParameter("@UserPassword", GlobalVariables.GetMD5(userPassword)));
NGSConnector connector = new NGSConnector();
SQLiteDataReader reader = connector.execSQLWithResult(sql, paramList);
if (reader.HasRows)
{
ret = true;
FillData(reader);
}
return(ret);
}
public List <DocumentTemplateFlow> GetDocumentTemplateFlowList()
{
List <DocumentTemplateFlow> documentList = new List <DocumentTemplateFlow>();
string sql = @"SELECT
DocumentTemplateFlowGUID, DocumentTemplateFlowName, DocumentTemplateFlowType
FROM DocumentTemplateFlow";
NGSConnector connector = new NGSConnector();
SQLiteDataReader reader = connector.execSQLWithResult(sql);
while (reader.Read())
{
DocumentTemplateFlow u = new DocumentTemplateFlow();
u.FillData(reader.GetString(0)
, reader.GetString(1)
, reader.GetString(2)
);
documentList.Add(u);
}
reader.Close();
return(documentList);
}
public List <DocumentTemplateFlowAssignment> GetDocumentTemplateFlowAssignmentList()
{
List <DocumentTemplateFlowAssignment> documentList = new List <DocumentTemplateFlowAssignment>();
string sql = @"SELECT
DocumentTemplateFlowAssignmentGUID, DocumentTemplateFlowGUID, DocumentTemplateGUID, DocumentPosition
FROM DocumentTemplateFlowAssignment";
NGSConnector connector = new NGSConnector();
SQLiteDataReader reader = connector.execSQLWithResult(sql);
while (reader.Read())
{
DocumentTemplateFlowAssignment u = new DocumentTemplateFlowAssignment();
u.FillData(reader.GetString(0)
, reader.GetString(1)
, reader.GetString(2)
, reader.GetInt32(3)
);
documentList.Add(u);
}
reader.Close();
return(documentList);
}
public List <User> GetListOfUsers()
{
List <User> listOfUsers = new List <User>();
string sql = @"SELECT
UserGUID, FirstName, LastName, UserName, UserPassword
FROM User";
NGSConnector connector = new NGSConnector();
SQLiteDataReader reader = connector.execSQLWithResult(sql);
while (reader.Read())
{
User u = new User();
u.FillData(reader.GetString(0)
, reader.GetString(1)
, reader.GetString(2)
, reader.GetString(3)
, reader.IsDBNull(4) ? "" : reader.GetString(4)
);
listOfUsers.Add(u);
}
reader.Close();
return(listOfUsers);
}
public List <Document> GetDocumentListBySearchPhrase(String searchTxt)
{
List <Document> documentList = new List <Document>();
string sql = @"
select DISTINCT D.*, 'N' as DocumentType from DocumentField DF
INNER JOIN Document D ON DF.CompanyGUID = D.CompanyGUID AND DF.DocumentGUID = D.DocumentGUID
INNER JOIN User U ON U.UserGUID = D.CreatedBy
INNER JOIN User U1 On U1.UserGUID = D.LastModifiedBy
WHERE (DF.FieldValue Like @searchTxt
OR U.FirstName LIKE @searchTxt OR U.LastName LIKE @searchTxt OR U.UserName LIKE @searchTxt
OR U1.FirstName LIKE @searchTxt OR U1.LastName LIKE @searchTxt OR U1.UserName LIKE @searchTxt
OR D.DocumentName LIKE @searchTxt OR D.PrintedDateTime LIKE @searchTxt OR D.CreatedDateTime LIKE @searchTxt OR D.LastModifiedDateTime LIKE @searchTxt
) AND D.CompanyGUID = @CompanyGUID
";
NGSConnector connector = new NGSConnector();
List <System.Data.SQLite.SQLiteParameter> paramList = new List <System.Data.SQLite.SQLiteParameter>();
paramList.Add(new SQLiteParameter("@CompanyGUID", GlobalVariables.CurrentCompany.CompanyGUID));
paramList.Add(new SQLiteParameter("@searchTxt", "%" + searchTxt + "%"));
SQLiteDataReader reader = connector.execSQLWithResult(sql, paramList);
while (reader.Read())
{
Document u = new Document();
u.FillData(reader.GetString(0)
, reader.GetString(1)
, reader.GetString(2)
, reader.GetString(3)
, reader.GetDateTime(4)
, reader.GetString(5)
, reader.GetDateTime(6)
, reader.GetString(7)
, reader.GetDateTime(8)
, reader.IsDBNull(9) ? (DateTime?)null : reader.GetDateTime(9)
);
documentList.Add(u);
}
reader.Close();
return(documentList);
}
public void LoadSettings(String companyGUID)
{
string sql = @"SELECT SettingName, SettingValue FROM Settings WHERE CompanyGUID = @CompanyGUID";
SettingList = new Dictionary <string, string>();
CompanyGUID = companyGUID;
List <System.Data.SQLite.SQLiteParameter> paramList = new List <System.Data.SQLite.SQLiteParameter>();
paramList.Add(new SQLiteParameter("@CompanyGUID", companyGUID));
NGSConnector connector = new NGSConnector();
SQLiteDataReader reader = connector.execSQLWithResult(sql, paramList);
while (reader.Read())
{
SettingList.Add(reader.GetString(0), reader.GetString(1));
}
reader.Close();
connector = null;
}
public void LoadList(string text, string logType)
{
logList.Clear();
string sql = "";
if (logType == "WSZYSTKIE")
{
sql = "SELECT l.LogGUID, l.LogDateTime, l.LogType, l.LogMessage, l.UserGUID, l.CompanyGUID, u.UserName FROM LogTable l INNER JOIN User u ON u.UserGUID = l.UserGUID WHERE l.LogMessage like '%" + text + "%'";
}
else if (logType == "UŻYTKOWNIKA")
{
sql = "SELECT l.LogGUID, l.LogDateTime, l.LogType, l.LogMessage, l.UserGUID, l.CompanyGUID, u.UserName FROM LogTable l INNER JOIN User u ON u.UserGUID = l.UserGUID WHERE l.LogMessage like '%" + text + "%' and lower(logType) = '" + "user" + "'";
}
else if (logType == "SYSTEMOWE")
{
sql = "SELECT l.LogGUID, l.LogDateTime, l.LogType, l.LogMessage, l.UserGUID, l.CompanyGUID, u.UserName FROM LogTable l INNER JOIN User u ON u.UserGUID = l.UserGUID WHERE l.LogMessage like '%" + text + "%' and lower(logType) = '" + "system" + "'";
}
NGSConnector connector = new NGSConnector();
SQLiteDataReader reader = connector.execSQLWithResult(sql);
while (reader.Read())
{
Log l = new Log();
l.FillData(reader.GetString(0)
, reader.GetDateTime(1)
, reader.GetString(2)
, reader.GetString(3)
, reader.GetString(4)
, reader.GetString(5)
, reader.GetString(6));
logList.Add(l);
}
reader.Close();
connector = null;
}
public List <Company> GetListOfCompany()
{
List <Company> companyList = new List <Company>();
string sql = @"SELECT
CompanyGUID
,CompanyName
FROM Company";
NGSConnector connector = new NGSConnector();
SQLiteDataReader reader = connector.execSQLWithResult(sql);
while (reader.Read())
{
Company c = new Company();
c.FillData(reader.GetString(0), reader.GetString(1));
companyList.Add(c);
}
reader.Close();
return(companyList);
}
public String GetFieldValue(String fieldName)
{
string sql = @"SELECT FieldValue FROM DocumentField WHERE CompanyGUID = @CompanyGUID AND DocumentGUID = @DocumentGUID and FieldName = @FieldName";
List <System.Data.SQLite.SQLiteParameter> paramList = new List <System.Data.SQLite.SQLiteParameter>();
paramList.Add(new SQLiteParameter("@CompanyGUID", CompanyGUID));
paramList.Add(new SQLiteParameter("@DocumentGUID", DocumentGUID));
paramList.Add(new SQLiteParameter("@FieldName", fieldName));
NGSConnector connector = new NGSConnector();
SQLiteDataReader reader = connector.execSQLWithResult(sql, paramList);
string retVal = "";
while (reader.Read())
{
retVal = reader.GetString(0);
}
connector = null;
return(retVal);
}
public List <String> GetDocumentTemplateFlowFieldList(string DocumentFlowGUID)
{
List <String> fieldList = new List <String>();
string sql = @"SELECT
FieldName
FROM DocumentTemplateFlowField
WHERE DocumentTemplateFlowGUID = @DocumentTemplateFlowGUID";
NGSConnector connector = new NGSConnector();
List <System.Data.SQLite.SQLiteParameter> paramList = new List <System.Data.SQLite.SQLiteParameter>();
paramList.Add(new SQLiteParameter("@DocumentTemplateFlowGUID", DocumentFlowGUID));
SQLiteDataReader reader = connector.execSQLWithResult(sql, paramList);
while (reader.Read())
{
fieldList.Add(reader.GetString(0));
}
reader.Close();
return(fieldList);
}
public List <RejestrCzynnosci> GetRejestrCzynnosciList(String searchTxt)
{
List <RejestrCzynnosci> rejestrCzynnosciList = new List <RejestrCzynnosci>();
String sql = @"SELECT
RejestrCzynnosciGUID,
NazwaAdministratoraDanych,
WspolAdministratorzy,
InsepktorDanychOsobowych,
NazwaZbioruDanych,
RodzajCzynnosci,
TytulCzynnosci,
CelPrzetwarzania,
OpisKategoriiOsob,
KategorieOdbiorcow,
KategorieDanychOsobowych,
InformarcjeOPrzekazaniuDoPanstwaTrzeciego,
PlanowanyTerminUsuniecia,
OpisTechniczny,
Uwagi,
TypRejestruCzynnosci,
CreatedBy,
CreatedDateTime,
LastModifiedBy,
LastModifiedDateTime,
CompanyGUID
FROM RejestrCzynnosci D
INNER JOIN User U ON U.UserGUID = D.CreatedBy
INNER JOIN User U1 On U1.UserGUID = D.LastModifiedBy
WHERE
(NazwaAdministratoraDanych LIKE @searchTxt OR U.FirstName LIKE @searchTxt OR U.LastName LIKE @searchTxt OR
WspolAdministratorzy LIKE @searchTxt OR U1.FirstName LIKE @searchTxt OR U1.LastName LIKE @searchTxt OR
InsepktorDanychOsobowych LIKE @searchTxt OR
NazwaZbioruDanych LIKE @searchTxt OR
RodzajCzynnosci LIKE @searchTxt OR
TytulCzynnosci LIKE @searchTxt OR
CelPrzetwarzania LIKE @searchTxt OR
OpisKategoriiOsob LIKE @searchTxt OR
KategorieOdbiorcow LIKE @searchTxt OR
KategorieDanychOsobowych LIKE @searchTxt OR
InformarcjeOPrzekazaniuDoPanstwaTrzeciego LIKE @searchTxt OR
PlanowanyTerminUsuniecia LIKE @searchTxt OR
OpisTechniczny LIKE @searchTxt OR
Uwagi LIKE @searchTxt) AND CompanyGUID = @CompanyGUID ";
NGSConnector connector = new NGSConnector();
List <System.Data.SQLite.SQLiteParameter> paramList = new List <System.Data.SQLite.SQLiteParameter>();
paramList.Add(new SQLiteParameter("@CompanyGUID", GlobalVariables.CurrentCompany.CompanyGUID));
paramList.Add(new SQLiteParameter("@searchTxt", "%" + searchTxt + "%"));
SQLiteDataReader reader = connector.execSQLWithResult(sql, paramList);
while (reader.Read())
{
RejestrCzynnosci u = new RejestrCzynnosci();
u.FillData(reader.GetString(0)
, reader.GetString(1)
, reader.GetString(2)
, reader.GetString(3)
, reader.GetString(4)
, reader.GetString(5)
, reader.GetString(6)
, reader.GetString(7)
, reader.GetString(8)
, reader.GetString(9)
, reader.GetString(10)
, reader.GetString(11)
, reader.GetString(12)
, reader.GetString(13)
, reader.GetString(14)
, reader.GetString(15)
, reader.GetString(16)
, reader.GetDateTime(17)
, reader.GetString(18)
, reader.GetDateTime(19)
, reader.GetString(20)
);
rejestrCzynnosciList.Add(u);
}
reader.Close();
return(rejestrCzynnosciList);
}
public List <Magazyn> GetSearchMagazyn(String searchTxt)
{
string sql = @"SELECT KopertaGUID,
NrKoperty,
NrPlomby,
DataOdbioru,
GodzinaOdbioru,
DataKopii,
KopertaNrPro,
Przekazal,
Zwrocil,
NazwaKlienta,
NIP,
AdresKlienta,
KodPocztowy,
Miasto,
KonwojentImie,
KonwojentNazwisko,
DataZwrotu,
CompanyGUID
FROM Magazyn
WHERE CompanyGUID = @CompanyGUID
AND
(NrKoperty LIKE @searchTxt OR
NrPlomby LIKE @searchTxt OR
DataOdbioru LIKE @searchTxt OR
GodzinaOdbioru LIKE @searchTxt OR
DataKopii LIKE @searchTxt OR
KopertaNrPro LIKE @searchTxt OR
Przekazal LIKE @searchTxt OR
Zwrocil LIKE @searchTxt OR
NazwaKlienta LIKE @searchTxt OR
NIP LIKE @searchTxt OR
AdresKlienta LIKE @searchTxt OR
KodPocztowy LIKE @searchTxt OR
Miasto LIKE @searchTxt OR
KonwojentImie LIKE @searchTxt OR
KonwojentNazwisko LIKE @searchTxt OR
DataZwrotu LIKE @searchTxt)
"
;
List <Magazyn> retList = new List <Magazyn>();
List <System.Data.SQLite.SQLiteParameter> paramList = new List <System.Data.SQLite.SQLiteParameter>();
paramList.Add(new SQLiteParameter("@CompanyGUID", GlobalVariables.CurrentCompany.CompanyGUID));
paramList.Add(new SQLiteParameter("@searchTxt", "%" + searchTxt + "%"));
NGSConnector connector = new NGSConnector();
System.Data.SQLite.SQLiteDataReader reader = connector.execSQLWithResult(sql, paramList);
while (reader.Read())
{
Magazyn m = new Magazyn();
m.FillData(
reader.IsDBNull(0) ? "" : reader.GetString(0),
reader.IsDBNull(1) ? "" : reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? "" : reader.GetString(3),
reader.IsDBNull(4) ? "" : reader.GetString(4),
reader.IsDBNull(5) ? "" : reader.GetString(5),
reader.IsDBNull(6) ? "" : reader.GetString(6),
reader.IsDBNull(7) ? "" : reader.GetString(7),
reader.IsDBNull(8) ? "" : reader.GetString(8),
reader.IsDBNull(9) ? "" : reader.GetString(9),
reader.IsDBNull(10) ? "" : reader.GetString(10),
reader.IsDBNull(11) ? "" : reader.GetString(11),
reader.IsDBNull(12) ? "" : reader.GetString(12),
reader.IsDBNull(13) ? "" : reader.GetString(13),
reader.IsDBNull(14) ? "" : reader.GetString(14),
reader.IsDBNull(15) ? "" : reader.GetString(15),
reader.IsDBNull(16) ? "" : reader.GetString(16),
reader.IsDBNull(17) ? "" : reader.GetString(17)
);
retList.Add(m);
}
reader = null;
return(retList);
}
