private void LoadDocumentForView()
{
try
{
string query = @"SELECT Title, AI.Name as Author, Body, DT.Name as DocumentType, DI.CreatedDate, DI.DateUncertain
FROM tbl_DocumentInfo DI WITH(NOLOCK),
tbl_AuthorInfo AI WITH(NOLOCK),
tbl_DocumentType DT WITH (NOLOCK)
WHERE DI.Author = AI.Id
AND DI.DocumentType = DT.Id
AND DI.Id = @documentId";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.Add("@documentId", SqlDbType.BigInt).Value = _documentId;
DataTable dt = DBSupport.ExecuteQueryAndGetDataTable(cmd);
if (dt != null && dt.Rows.Count > 0)
{
_title = dt.Rows[0]["Title"].ToString();
_author = dt.Rows[0]["Author"].ToString();
_documentType = dt.Rows[0]["DocumentType"].ToString();
NepaliDateTime nepaliDate = DateConverter.EnglishToNepali(DateTime.Parse(dt.Rows[0]["CreatedDate"].ToString()));
bool isUncertain = bool.Parse(dt.Rows[0]["DateUncertain"].ToString());
_createdDate = nepaliDate.Year + "-" + nepaliDate.Month.ToString("00") + "-" + nepaliDate.Day.ToString("00") + (isUncertain ? " *" : "");
_body = dt.Rows[0]["Body"].ToString();
AppendText(richTextBoxDocumentContent, _title, new Font("Arial", 14, FontStyle.Bold), HorizontalAlignment.Center);
AppendText(richTextBoxDocumentContent, Environment.NewLine, alignment: HorizontalAlignment.Center);
AppendText(richTextBoxDocumentContent, Environment.NewLine);
AppendText(richTextBoxDocumentContent, _author, new Font("Arial", 12), HorizontalAlignment.Center);
AppendText(richTextBoxDocumentContent, Environment.NewLine, alignment: HorizontalAlignment.Center);
AppendText(richTextBoxDocumentContent, Environment.NewLine);
AppendText(richTextBoxDocumentContent, _body, new Font("Arial", 12), HorizontalAlignment.Left);
AppendText(richTextBoxDocumentContent, Environment.NewLine);
AppendText(richTextBoxDocumentContent, Environment.NewLine);
AppendText(richTextBoxDocumentContent, _createdDate, new Font("Arial", 12), HorizontalAlignment.Left, isUncertain ? Color.Red : Color.Black);
}
else
{
MessageBox.Show("Requested document could not be retrieved from database.");
this.ParentForm.Close();
}
cmd.CommandText = @"SELECT ImageId, ImagePath FROM tbl_DocumentImageAssociation(NOLOCK) DIA, tbl_ImageInfo(NoLock) II
WHERE DIA.ImageId = II.Id AND DIA.DocumentId = @documentId";
dt = DBSupport.ExecuteQueryAndGetDataTable(cmd);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
AddFileToListViewImages(dr["ImagePath"].ToString());
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Could load document. Error : " + ex.Message);
this.ParentForm.Close();
}
}
private void exportSelectedFilesAsSeparateDocxToolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewSelectedRowCollection selectedRows = dataGridViewDocumentSearchResult.SelectedRows;
if (selectedRows.Count > 0)
{
int progress = 0;
int totalCount = selectedRows.Count;
FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog();
try
{
if (DialogResult.OK == folderBrowserDialog.ShowDialog())
{
progressBarExport.Show();
string today = NepaliDateTime.Now.ToDateTimeString();
string saveFileName = string.Empty;
for (int i = selectedRows.Count - 1; i >= 0; i--)
{
try
{
object fileId = selectedRows[i].Cells["ColumnID"].Value;
string query = @"SELECT DI.Title, DI.Body, DI.CreatedDate AS EnglishDate, AI.Name as Author
FROM tbl_DocumentInfo DI WITH (NOLOCK),
tbl_AuthorInfo AI WITH(NOLOCK)
WHERE DI.Author = AI.Id
AND DI.Id = @Id" ;
using (SqlCommand command = new SqlCommand(query))
{
command.Parameters.Add("@Id", SqlDbType.BigInt).Value = fileId;
DataTable dt = DBSupport.ExecuteQueryAndGetDataTable(command);
if (dt != null)
{
string folderPath = Path.Combine(folderBrowserDialog.SelectedPath, today);
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
DateTime engDate = DateTime.Parse(dt.Rows[0]["EnglishDate"].ToString());
NepaliDateTime nepaliDate = DateConverter.EnglishToNepali(engDate);
string filename = nepaliDate.ToString() + "_" + dt.Rows[0]["Title"].ToString() + "_" + Guid.NewGuid().ToString().Replace("-", "");
filename = string.Join("_", filename.Split(Path.GetInvalidFileNameChars()));
saveFileName = Path.Combine(folderPath, filename + ".docx");
using (var fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
{
XWPFDocument newDocument = new XWPFDocument();
AppendInfoToDocument(newDocument, dt.Rows[0]["Title"].ToString(), dt.Rows[0]["Author"].ToString()
, dt.Rows[0]["Body"].ToString(), nepaliDate.ToString());
newDocument.Write(fs);
}
}
}
progressBarExport.Value = ++progress * 100 / totalCount;
progressBarExport.Update();
}
catch (Exception ex)
{
ShowMessageBox("Could not save file: " + saveFileName + Environment.NewLine + " Error : " + ex.Message
, MessageBoxIcon.Error);
}
}
ShowMessageBox("Documents created successfully !");
}
}
catch (Exception ex)
{
ShowMessageBox("Could not save files." + Environment.NewLine + " Error : " + ex.Message
, MessageBoxIcon.Error);
}
finally
{
progressBarExport.Value = 0;
progressBarExport.Hide();
}
}
}
private void Search()
{
long publisher = (long)comboBoxPublisherScope.SelectedValue;
long documentType = (long)comboBoxDocumentTypeScope.SelectedValue;
long author = (long)comboBoxSearchAuthor.SelectedValue;
string whereClause = string.Empty;
if (publisher != 0)
{
whereClause = " Publisher = " + publisher;
}
if (documentType != 0)
{
if (!string.IsNullOrWhiteSpace(whereClause))
{
whereClause += " AND DocumentType =" + documentType;
}
else
{
whereClause = " DocumentType = " + documentType;
}
}
if (author != 0)
{
if (!string.IsNullOrWhiteSpace(whereClause))
{
whereClause += " AND Author =" + author;
}
else
{
whereClause = " Author = " + author;
}
}
highlightTerms = new List <string>();
if (checkBoxSearchTitle.Checked || checkBoxSearchSummary.Checked || checkBoxSearchBody.Checked)
{
string searchText = GetSearchText(ref highlightTerms);
string stringClause = string.Empty;
if (checkBoxSearchTitle.Checked)
{
stringClause = " CONTAINS(Title, N'" + searchText + "') ";
}
if (checkBoxSearchSummary.Checked)
{
if (string.IsNullOrWhiteSpace(stringClause))
{
stringClause = " CONTAINS(Summary, N'" + searchText + "') ";
}
else
{
stringClause += " OR CONTAINS(Summary, N'" + searchText + "') ";
}
}
if (checkBoxSearchBody.Checked)
{
if (string.IsNullOrWhiteSpace(stringClause))
{
stringClause = " CONTAINS(Body, N'" + searchText + "') ";
}
else
{
stringClause += " OR CONTAINS(Body, N'" + searchText + "') ";
}
}
if (string.IsNullOrWhiteSpace(whereClause))
{
whereClause = stringClause;
}
else if (!string.IsNullOrWhiteSpace(stringClause))
{
whereClause += " AND (" + stringClause + ")";
}
}
if (!string.IsNullOrWhiteSpace(whereClause))
{
whereClause = " WHERE " + whereClause;
}
string query = @"SELECT ROW_NUMBER() OVER (ORDER BY DI.CreatedDate DESC, DI.Id DESC) AS SN, DI.Id, AI.Name AS Author, Title, DT.Name as DocumentType, '' AS NepaliDate, CreatedDate AS EnglishDate, DateUncertain
FROM tbl_DocumentInfo DI WITH (NOLOCK)
LEFT JOIN tbl_PublisherInfo PI WITH (NOLOCK) ON DI.Publisher = PI.Id
LEFT JOIN tbl_DocumentType DT WITH (NOLOCK) ON DI.DocumentType = DT.Id
LEFT JOIN tbl_AuthorInfo AI WITH (NOLOCK) ON AI.Id = DI.Author
" + whereClause;
DataTable dt = DBSupport.GetDataTable(query);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
DateTime engDate = DateTime.Parse(dr["EnglishDate"].ToString());
NepaliDateTime nepaliDate = DateConverter.EnglishToNepali(engDate);
bool isUncertain = bool.Parse(dr["DateUncertain"].ToString());
dr["NepaliDate"] = nepaliDate.ToString() + (isUncertain ? " *" : "");
}
labelResultCount.Text = dt.Rows.Count.ToString();
}
else
{
labelResultCount.Text = "0";
}
dataGridViewDocumentSearchResult.DataSource = dt;
dataGridViewDocumentSearchResult.AutoResizeColumns();
}
private void exportAsDocxToolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewSelectedRowCollection selectedRows = dataGridViewDocumentSearchResult.SelectedRows;
if (selectedRows.Count > 0)
{
try
{
SaveFileDialog saveFile = new SaveFileDialog()
{
FileName = "FileFromSearch.docx",
Filter = "Document | *.docx",
DefaultExt = "docx"
};
if (DialogResult.OK == saveFile.ShowDialog())
{
string saveFileName = saveFile.FileName;
if (!Path.GetExtension(saveFile.FileName).Equals("." + saveFile.DefaultExt, StringComparison.InvariantCultureIgnoreCase))
{
saveFileName = saveFile.FileName + "." + saveFile.DefaultExt;
}
using (var fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
{
XWPFDocument newDocument = new XWPFDocument();
for (int i = selectedRows.Count - 1; i >= 0; i--)
{
object fileId = selectedRows[i].Cells["ColumnID"].Value;
string query = @"SELECT DI.Title, DI.Body, DI.CreatedDate AS EnglishDate, AI.Name as Author
FROM tbl_DocumentInfo DI WITH (NOLOCK),
tbl_AuthorInfo AI WITH(NOLOCK)
WHERE DI.Author = AI.Id
AND DI.Id = @Id" ;
using (SqlCommand command = new SqlCommand(query))
{
command.Parameters.Add("@Id", SqlDbType.BigInt).Value = fileId;
DataTable dt = DBSupport.ExecuteQueryAndGetDataTable(command);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
DateTime engDate = DateTime.Parse(dr["EnglishDate"].ToString());
NepaliDateTime nepaliDate = DateConverter.EnglishToNepali(engDate);
AppendInfoToDocument(newDocument, dr["Title"].ToString(), dr["Author"].ToString(), dr["Body"].ToString()
, nepaliDate.ToString());
}
}
}
}
newDocument.Write(fs);
}
ShowMessageBox("Document created successfully !");
}
}
catch (Exception ex)
{
ShowMessageBox("Could not save file. Error : " + ex.Message, MessageBoxIcon.Error);
}
}
else
{
}
}
private void LoadNepaliDate()
{
NepaliDateTime nepaliDate = DateConverter.EnglishToNepali(dateTimePickerCreatedDate.Value);
textBoxNepaliDate.Text = nepaliDate.Year + "." + nepaliDate.Month.ToString("00") + "." + nepaliDate.Day.ToString("00");
}