public List <FileItDocument> GetDocumentsByIds(List <string> ids, bool IncludeThumbs, bool includeDeleted)
{
var result = new List <FileItDocument>();
var sql = "SELECT * FROM " + CABINETID + "_IMAGES";
var whereClause = "";
if (ids.Any())
{
ids.ForEach(i =>
{
whereClause += whereClause.Length == 0 ? " WHERE " : " AND ";
whereClause += "FILENAME='" + i + "'";
});
}
if (!includeDeleted)
{
whereClause += whereClause.Length == 0 ? " WHERE " : " AND ";
whereClause += "DELETED='N'";
}
sql = sql + whereClause;
var docs = FireBirdHelper.GenericSelect(sql, CabinetConnectionString);
foreach (DataRow dr in docs.Rows)
{
result.Add(new FileItDocument(dr, OSTOREPATH, IncludeThumbs));
}
return(result);
}
public List <FileItDocument> GetDocuments(List <FileItDocumentLookup> lookups, bool IncludeThumbs)
{
var result = new List <FileItDocument>();
var sql = "SELECT * FROM " + CABINETID + "_IMAGES";
var whereClause = "";
if (lookups.Any())
{
lookups.ForEach(l =>
{
whereClause += whereClause.Length == 0 ? " WHERE " : "";
whereClause += l.ToWhereClause();
});
}
sql = sql + whereClause;
var docs = FireBirdHelper.GenericSelect(sql, CabinetConnectionString);
foreach (DataRow dr in docs.Rows)
{
result.Add(new FileItDocument(dr, OSTOREPATH, IncludeThumbs));
}
return(result);
}
public bool DeleteDocument(string documentId)
{
var result = false;
var sql = "UPDATE " + CABINETID + "_IMAGES SET DELETED='Y' WHERE FILENAME='" + documentId + "'";
result = FireBirdHelper.GenericCommand(sql, this.CabinetConnectionString);
return(result);
}
public bool Add(FileItDocument doc)
{
ExceptionHelper.LogDebug("In Add Cabinet Document...");
var result = false;
StringBuilder sql = new StringBuilder();
int fileSize = 501;
//add the document to the database
sql.Append("INSERT INTO " + CABINETID + "_IMAGES ");
sql.Append("(");
sql.Append("FILENAME, DELETED, PAGENUMBER, INDEXEDON, EXTENSION, ARCHIVED, FILESIZE, PUBLIC, VERSION_NO, VERSIONID, USERID, STATIONID, BATCHNO");
doc.Indexes.ToList().ForEach(i =>
{
sql.Append(", INDEX" + i.Key.ToString());
});
sql.Append(") VALUES (");
sql.Append("'" + doc.FILENAME.Replace("'", "''") + "',");
sql.Append("'" + (doc.DELETED ? "Y" : "N") + "',");
sql.Append("'" + "1" + "',");
sql.Append("'" + doc.INDEXEDON.ToString("MM/dd/yyyy") + "',");
sql.Append("'" + doc.EXTENSION.ToString().Replace("'", "''") + "',");
sql.Append("'" + doc.ARCHIVED.ToString().Replace("'", "''") + "',");
sql.Append("'" + fileSize.ToString() + "',");
sql.Append("'" + (doc.PUBLIC ? "Y" : "N") + "',");
sql.Append("'" + doc.VERSION_NO.ToString().Replace("'", "''") + "',");
sql.Append("'" + doc.VERSIONID.ToString().Replace("'", "''") + "',");
sql.Append("'" + doc.USERID.ToString().Replace("'", "''") + "',");
sql.Append("'" + doc.STATIONID.ToString().Replace("'", "''") + "',");
sql.Append("'" + doc.BATCHNO.ToString().Replace("'", "''") + "'");
ExceptionHelper.LogDebug(sql.ToString());
doc.Indexes.ToList().ForEach(i =>
{
//i.Value.ToString().Replace("'", "''")
sql.Append(", " + FireBirdHelper.FormatValue(i.Value));
});
sql.Append(")");
//var t = sql.ToString();
ExceptionHelper.LogDebug("SQL Insert:" + sql.ToString());
if (FireBirdHelper.GenericCommand(sql.ToString(), CabinetConnectionString))
{
//save the file
var folderPath = doc.AddImageFileFolderPath(OSTOREPATH);
//make sure there are no extra . in the name
var filePath = System.IO.Path.Combine(folderPath, doc.FILENAME + (doc.EXTENSION.IndexOf('.') > -1 ? "": ".") + doc.EXTENSION);
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
FileHelper.Base64ToFile(filePath, doc.ImageBase64);
result = true;
}
return(result);
}
private void CreateTables()
{
var cabSQL = "CREATE TABLE " + CABINETID + "_IMAGES" + " (" +
" FILENAME VARCHAR(8) NOT NULL," +
" EXTENSION VARCHAR(4) NOT NULL," +
" ARCHIVED VARCHAR(3)," +
" INDEXEDON TIMESTAMP NOT NULL," +
" PAGENUMBER SMALLINT NOT NULL," +
" FILESIZE INTEGER," +
" PixelType VARCHAR(10)," +
" PixelDepth VARCHAR(10)," +
" Resolution VARCHAR(10)," +
" PixelSizeX VARCHAR(25)," +
" PixelSizeY VARCHAR(25)," +
" Orientation VARCHAR(10)," +
" Storageid VARCHAR(1) DEFAULT 'A', " +
" Side VARCHAR(1) DEFAULT 'A', " +
" PUBLIC VARCHAR(1) DEFAULT 'N', " +
" VERSION_NO INTEGER DEFAULT 0, " +
" CHECKEDOUT VARCHAR(1) DEFAULT 'N', " +
" VERSIONID VARCHAR(20) DEFAULT '', " +
" ALTSTORAGE VARCHAR(1) DEFAULT '', " +
" DELETED VARCHAR(1) DEFAULT 'N', " +
" USERID VARCHAR(20) DEFAULT '', " +
" STATIONID VARCHAR(20) DEFAULT '', "+
" BATCHNO VARCHAR(20) DEFAULT '' ";
//get template definition
var idx = 1;
Template.TemplateDefinitions.ToList().ForEach(td =>
{
switch (td.DATATYPE.ToLower())
{
case "date":
cabSQL += ", INDEX" + idx + " DATE";
break;
case "number":
cabSQL += ", INDEX" + idx + " INTEGER";
break;
default:
cabSQL += ", INDEX" + idx + " VARCHAR(" + td.MAXLENGTH + ") NOT NULL";
break;
}
idx++;
});
//now add primary key
cabSQL += ", Primary Key(FILENAME) );";
FireBirdHelper.GenericCommand(cabSQL, CabinetConnectionString);
}
