private void saveButton_Click(object sender, RoutedEventArgs e)
{
OleDbConnection connection = null;
OleDbCommand command = null;
OleDbDataReader reader = null;
string sqlString = "";
try
{
string templateName = getTemplateName();
connection = DatabaseConnection.GetConnection();
connection.Open();
checkIfTemplateNameExists(connection, templateName);
checkIfDirectoryExists(connection, templateName);
// insert template name in doctypes table
sqlString = "insert into doctypes(doctype_name) values(@NAME)";
command = new OleDbCommand(sqlString, connection);
command.Parameters.AddWithValue("@NAME", templateName);
reader = command.ExecuteReader();
if (reader.RecordsAffected != 1)
{
MessageBox.Show("Some error occured and record cannot be added");
return;
}
//done
int documentId = getTemplateDataIdByName(connection, templateName);
int rowCount = _DataTable.Rows.Count;
for (int i = 0; i < rowCount; i++)
{
//Trace.WriteLine(_DataTable.Rows[i]["Field Name"]);
//Trace.WriteLine(_DataTable.Rows[i]["Row"]);
//Trace.WriteLine(_DataTable.Rows[i]["Column"]);
//Trace.WriteLine(_DataTable.Rows[i]["Sheet"]);
string fieldName = _DataTable.Rows[i]["Field Name"].ToString();
int fieldType = (fieldName == "Mode of Transport") ? 0 : 1;
int row = Int32.Parse(_DataTable.Rows[i]["Row"].ToString());
int column = Int32.Parse(_DataTable.Rows[i]["Column"].ToString());
string sheet = _DataTable.Rows[i]["Sheet"].ToString();
sqlString = "insert into fields(doctype_id,field_name,field_type,field_row,field_column,field_sheet,append,isList) values(@1,@2,@3,@4,@5,@6,'False','False')";
command = new OleDbCommand(sqlString, connection);
command.Parameters.AddWithValue("@1", documentId);
command.Parameters.AddWithValue("@2", fieldName);
command.Parameters.AddWithValue("@3", fieldType);
command.Parameters.AddWithValue("@4", row);
command.Parameters.AddWithValue("@5", column);
command.Parameters.AddWithValue("@6", sheet);
reader = command.ExecuteReader();
if (reader.RecordsAffected != 1)
{
throw new DAOException("Some Error occured and records are not added");
}
}
Directory.CreateDirectory(_RootPath + "\\" + templateName);
File.Copy(txtSelectTemplate.Text, _RootPath + "\\" + templateName + "\\" + templateName + ".xlsx");
MessageBox.Show("Information saved and template is created");
connection.Close();
this.Close();
}
catch (Exception ex)
{
MessageBox.Show("Exception in saving : " + ex.Message);
}
}
/// <summary>
/// This generate function do same work as of reportProcessor generate function. But it is different from that generate function, as it will also used to generate multiline report, when taking values from purchase.
/// </summary>
/// <param name="field"></param>
/// <param name="reportName"></param>
/// <param name="saveFileName"></param>
public void GenerateReport(Field field, string reportName, string saveFileName)
{
string documentName = System.IO.Path.GetFileName(System.IO.Path.GetDirectoryName(reportName));
FileStream fileStream = new FileStream(reportName, FileMode.Open, FileAccess.Read);
IWorkbook workbook = null;
string extension = Utility.GetExtension(reportName);
if (extension == ".xlsx")
{
workbook = new XSSFWorkbook(fileStream);
}
else if (extension == ".xls")
{
throw new DAOException(extension + " files are not supported. Please change them to .xlsx file format. \n Thanks");
}
else
{
throw new DAOException("File format " + extension + " not supported.");
}
ISheet sheet = null;
if (sheet == null)
{
sheet = workbook.GetSheet("Invoice");
}
IRow currRow;
ICell cell;
for (int i = 0, rowNum = 20; i < purchases.Count; i++)
{
Purchase p = purchases[i];
currRow = sheet.GetRow(rowNum);
cell = currRow.GetCell(0);
//SetValue(cell, "1", field);
cell.SetCellValue(p.SNo);
string[] strs = p.description.Split('\n');
for (int j = 0; j < strs.Length; j++)
{
currRow = sheet.GetRow(rowNum + j);
cell = currRow.GetCell(1);
cell.SetCellValue(strs[j]);
}
currRow = sheet.GetRow(rowNum);
cell = currRow.GetCell(6);
cell.SetCellValue(p.quantity);
currRow = sheet.GetRow(rowNum);
cell = currRow.GetCell(8);
cell.SetCellValue(p.amount);
int count = p.description.Count(s => s == '\n') + 1;
rowNum += count;
}
currRow = sheet.GetRow(40);
cell = currRow.GetCell(0);
cell.SetCellValue(txtGSTAmountInWords.Text);
currRow = sheet.GetRow(42);
cell = currRow.GetCell(0);
cell.SetCellValue(txtTotalAmountInWords.Text);
currRow = sheet.GetRow(38);
cell = currRow.GetCell(8);
cell.SetCellValue(txtTotalAmount.Text);
currRow = sheet.GetRow(39);
cell = currRow.GetCell(8);
cell.SetCellValue(txtCGST.Text);
currRow = sheet.GetRow(40);
cell = currRow.GetCell(8);
cell.SetCellValue(txtSGST.Text);
currRow = sheet.GetRow(41);
cell = currRow.GetCell(8);
cell.SetCellValue(txtIGST.Text);
currRow = sheet.GetRow(42);
cell = currRow.GetCell(8);
cell.SetCellValue(txtGrandTotal.Text);
OleDbConnection connection = DatabaseConnection.GetConnection();
connection.Open();
OleDbCommand command = new OleDbCommand("select * from qryDocumentFields where doctype_name=@DOCUMENT", connection);
//Trace.WriteLine("Document Name " + documentName);
command.Parameters.AddWithValue("@DOCUMENT", documentName);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (sheet == null)
{
sheet = workbook.GetSheet(reader["field_sheet"].ToString());
}
currRow = sheet.GetRow(int.Parse(reader["field_row"].ToString()) - 1);
cell = currRow.GetCell(int.Parse(reader["field_column"].ToString()) - 1);
//Trace.WriteLine("Field Name "+reader["field_name"]);
SetValue(cell, reader["field_name"].ToString(), field);
}
using (var fileData = new FileStream(saveFileName, FileMode.Create))
{
workbook.Write(fileData);
workbook.Close();
}
}