Can you pls. do code review? I am attaching .cs file (renamed to .txt).
I am writing description of each function here.
1. private void FileBrowse_Click(object sender, EventArgs e):
This function is used for selecting .txt file with validation as .txt file has particular format.
2. StartProcess():
This function starts the process after clicking button "Start process" from UI and selects particular word templates. There are five different types of templates which has bookmark in it. This function calls sub-function named "GenerateXMLDocs(,,,).
3. private bool GenerateXMLDocs(object FileName ,object InputCSVFile,string CSVFileName)
This function generates xml and associated .doc file. The main purpose of this function is to get the
values from .txt file and plug those value one by one to bookmark in the word template and generate
.xml file also for each .doc file.
There are some other small functions in the file which is used to get the values to generate xml file and set the flag in database once process is completed for particular record.
Pls. review the code. I want to know that what is the code quality level? is it best or good or avg or below avg or really bad coding.? i am attaching .txt file which is basically .cs file.
Spoiler
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MSAccess=Microsoft.Office.Interop.Access;
using Microsoft.Office.Interop.Word;
using System.Data.OleDb;
using System.IO;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using JLib.Logs.WinAppLogs;
using JLib;
using System.Threading;
namespace TRADevelopment
{
public partial class TRAMain : Form
{
private const int CP_NOCLOSE_BUTTON = 0x200;
string connStr = "";
string CSVFilename = "";
bool ProcessDone = true;
string OriginalFile = "";
long FilesCompleted = 0;
bool StopClicked = false;
string StrTPUDOC = "";// @"""" + "T" + @"""";
string StrMatchReqYN = ""; //@"""" + "N" + @"""";
string StrProcessReqYN = "";//@"""" + "N" + @"""";
string StrMaySatisfySuspense = "";// @"""" + "" + @"""";
string StrDocType = "";// @"""" + "" + @"""";
string StrUIDoc = "";// @"""" + "F" + @"""";
string StrDocSource = @"""" + "SCAN" + @"""";
string StrIndexerID = @"""" + "TRASystem" + @"""";
string StrStartUnit = "";
string StrSSN = "";
string StrProjectID = "";
string StrClaimantName = "";
AppLogs log, logSuccess;
public TRAMain()
{
InitializeComponent();
//Log File Generation
log = new AppLogs("TRA System Event LOGS ..", " TRA System Started at :" + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt"), ConfigurationSettings.AppSettings["EventLogFilePath"].ToString(), true, int.Parse(ConfigurationSettings.AppSettings["LogSuccessFileSize"]), 10, true);
logSuccess = new AppLogs("TRA System Success LOGS", "TRA System Started at :" + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt"), ConfigurationSettings.AppSettings["SuccessLogFilePath"].ToString(), true, int.Parse(ConfigurationSettings.AppSettings["LogSuccessFileSize"]), 10, true);
}
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
}
private void FileBrowse_Click(object sender, EventArgs e)
{
DialogResult Browse = openFileDialog.ShowDialog();
if (Browse==DialogResult.OK)
{
if ((Path.GetExtension(openFileDialog.FileName.ToString()) != ".txt"))
{
MessageBox.Show("Pls. Select Proper File!!");
txtFileName.Focus();
return;
}
bool IsFileProcessed = CheckFileAlreadyProcessed(Path.GetFileName(openFileDialog.FileName.ToString()));
//Checking that file already processed or not
if (IsFileProcessed == true)
{
MessageBox.Show("Selected File Already Processed, Pls. select file which never processed");
txtFileName.Focus();
return;
}
FileStream fin;
FileStream fout;
txtFileName.Text = openFileDialog.FileName.ToString();
if (txtFileName.Text.Contains("-")==true)
{
OriginalFile = txtFileName.Text;
}
else if(txtFileName.Text.Contains("_")==true)
{
OriginalFile = txtFileName.Text;
}
string DestinationFilename=txtFileName.Text.ToString().Replace('.', '_');
DestinationFilename = DestinationFilename.Replace('-', '_') + ".txt";
//Create Copy of New file with exact same data as Original file has some invalid characters in name.
fin = new FileStream(txtFileName.Text.ToString(), FileMode.Open);
fout = new FileStream(DestinationFilename, FileMode.Create);
int i;
do
{
i = fin.ReadByte();
if (i != -1) fout.WriteByte((byte)i);
} while (i != -1);
fin.Close();
fout.Close();
txtFileName.Text = DestinationFilename;
CSVFilename = Path.GetFileName(DestinationFilename);
}
}
private void BtnGenerateDocs_Click(object sender, EventArgs e)
{
}
/// <summary>
/// This method is generating .XML & .doc files. These xml and .doc files are generated from .csv file
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnGenerateDocsFromTemplate_Click(object sender, EventArgs e)
{
StartProcess();
}
protected override CreateParams CreateParams
{
get
{
CreateParams myCp = base.CreateParams;
myCp.ClassStyle = myCp.ClassStyle | CP_NOCLOSE_BUTTON;
return myCp;
}
}
// Start Process
private void StartProcess()
{
if (txtFileName.Text == "")
{
MessageBox.Show("Pls. Select Flat File from the Input Folder !!");
txtFileName.Focus();
return;
}
BtnStop.Enabled = true;
StopClicked = false;
int RecordInserted = AddFileNameToDB(Path.GetFileName(openFileDialog.FileName.ToString()));
if (RecordInserted != 1)
{
MessageBox.Show("Failed to Insert Flat File For Log into database !!, Pls. Check Flat File Name");
txtFileName.Focus();
return;
}
lblStatusValue.Visible = true;
FilesCompleted = 0;
object InputTemplateFileName = "";
//For Trade(Training)
if ((CSVFilename.ToString().Contains("Trade") == true))
{
InputTemplateFileName = ConfigurationSettings.AppSettings["InputTemplateFolderPath"] + "Training.Doc";
}
//For Reviewed
else if ((CSVFilename.ToString().Contains("Reviewed") == true))
{
InputTemplateFileName = ConfigurationSettings.AppSettings["InputTemplateFolderPath"] + "Reviewed.doc";
}
// For Issuance
else if ((CSVFilename.ToString().Contains("Issued") == true))
{
InputTemplateFileName = ConfigurationSettings.AppSettings["InputTemplateFolderPath"] + "Issued.doc";
}
// For Revoked
else if ((CSVFilename.ToString().Contains("Revoked") == true))
{
InputTemplateFileName = ConfigurationSettings.AppSettings["InputTemplateFolderPath"] + "Revoked.doc";
}
// For Reopen
else if ((CSVFilename.ToString().Contains("WAIVERS_REOPENED") == true))
{
InputTemplateFileName = ConfigurationSettings.AppSettings["InputTemplateFolderPath"] + "WAIVERS_REOPENED.doc";
}
else
{
MessageBox.Show("Template is Not Available for Selected .txt file, Pls. check Templates folder");
}
try
{
BtnGenerateDocsFromTemplate.Enabled = false;
BtnExit.Enabled = false;
BtnStop.Enabled = true;
//Generate .DOCS & .XMLS
ProcessDone = GenerateXMLDocs(InputTemplateFileName, txtFileName.Text.ToString().Trim(), CSVFilename);
}
catch (Exception Exc)
{
log.Logs("BtnGenerateDocsFromTemplate_Click Function : ", DateTime.Now.ToString() + " File : " + Path.GetFileName(openFileDialog.FileName.ToString()) + Exc.Source + "" + Exc.Message);
}
if (ProcessDone == false)
{
int RecordUpdated = UpdateTRABatchLogToDB(Path.GetFileName(openFileDialog.FileName.ToString()), "FAILED");
log.Logs("BtnGenerateDocsFromTemplate_Click Function : ", DateTime.Now.ToString() + " File : " + Path.GetFileName(openFileDialog.FileName.ToString()));
MessageBox.Show("Process has NOT completed");
}
else if (ProcessDone == true)
{
int RecordUpdated = UpdateTRABatchLogToDB(Path.GetFileName(openFileDialog.FileName.ToString()), "SUCCESS");
if (RecordUpdated != 1)
{
//As there is an error occured so clean up the output folder
CleanUptheOutputFolder();
MessageBox.Show("Log has NOT updated Correctly!!");
}
else
{
//As process completed properly , finally convert all the .txt files to .XML files
ConvertTxtToXML();
lblPlswait.Text = "Process Completed.";
lblPlswait.Refresh();
BtnGenerateDocsFromTemplate.Enabled = true;
BtnExit.Enabled = true;
BtnStop.Enabled = false;
txtFileName.Text = "";
MessageBox.Show("TRA Documents Generated Successfully!!");
}
}
}
//This function generates .XML files and .doc files
private bool GenerateXMLDocs(object FileName ,object InputCSVFile,string CSVFileName)
{
log.Logs("Generate XMLDocs Function Started : ", DateTime.Now.ToString() + " File : " + InputCSVFile.ToString());
logSuccess.Logs("Generate XMLDocs Function Started : ","", DateTime.Now.ToString() + " File : " + InputCSVFile.ToString());
string sql_select = "";
string StrInputCSVFile = Path.GetDirectoryName(InputCSVFile.ToString()) + "\\";
string textFileFolder = (new System.IO.FileInfo(StrInputCSVFile)).DirectoryName;
//Following code is the way to read .csv file with faster and efficient way rather than fill dataset
using (StreamReader readFile = new StreamReader(InputCSVFile.ToString()))
{
logSuccess.Logs("GenerateXMLDocs Function : ","", "File : "+ InputCSVFile.ToString() );
Word._Application oWord;
Word._Document MyWordDoc;
object missing = Type.Missing;
object Destination = "";
string XMLFilePath = "";
object Obj_Null = System.Reflection.Missing.Value;
object DoSaveChanges = Word.WdSaveOptions.wdSaveChanges;
List<string[]> parsedData = new List<string[]>();
string line;
string[] row;
while ((line = readFile.ReadLine()) != null)
{
if (StopClicked==true)
{
DialogResult Result = MessageBox.Show("Once you stop the Process, You need to restart all over again,Do you want to Stop Process?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (Result==DialogResult.Yes)
{
DirectoryInfo InputDir = new DirectoryInfo(Path.GetDirectoryName(InputCSVFile.ToString()) + "\\");
string GetFileType = CSVFileName.Substring(0, 5);
GetFileType = GetFileType + "*.TXT";
FileInfo[] FlatFileType = InputDir.GetFiles(GetFileType);
readFile.Close();
foreach (FileInfo InputFileType in FlatFileType)
{
if (InputFileType.Name.Contains("-") == false)
{
InputFileType.Delete();
//Reset the form , so user can select template again.
lblStatusValue.Text = "";
lblPlswait.Text = "";
BtnExit.Enabled = true;
BtnGenerateDocsFromTemplate.Enabled = true;
BtnStop.Enabled = true;
txtFileName.Text = "";
}
}
//Clear all the .txt/.doc files which already generated
CleanUptheOutputFolder();
//As batch is NOT finished successfully it means failed or stopped
UpdateTRABatchLogToDB(Path.GetFileName(openFileDialog.FileName.ToString()), "FAILED");
return false;
break;
}
else if (Result==DialogResult.No)
{
StopClicked = false;
BtnStop.Enabled = true;
BtnGenerateDocsFromTemplate.Enabled = false;
continue;
}
}
lblPlswait.Visible = true;
lblPlswait.Text = "Process Started... Pls. Wait.........";
if (line.Length==0 && readFile.EndOfStream == true)
{
break;
}
try
{
//Reading line by line from the flat file
//Reading Current Row
row = line.Split('|');
DataSet GetClaimantInfoDS;
DataSet GetDocumentTypeInfo;
string version = @"""1.0""";
string GenerateXMLString = "";
GenerateXMLString= "<?xml version =" + version + "?>";
Destination = "";
XMLFilePath = "";
GenerateXMLString = GenerateXMLString + "<import>";
if ((CSVFilename.ToString().Contains("Trade") == true))
{
//For TRAINING
Destination = ConfigurationSettings.AppSettings["OutputFolderPath"] + "TRADE";
XMLFilePath = ConfigurationSettings.AppSettings["OutputFolderPath"] + "TRADE";
GetDocumentTypeInfo = GetInfoFromDocumentType("ETA858");
SetDocumentTypeInfo(GetDocumentTypeInfo);
}
else if ((CSVFilename.ToString().Contains("Reviewed") == true))
{
//For Reviwed
Destination = ConfigurationSettings.AppSettings["OutputFolderPath"] + "REVIEWED";
XMLFilePath = ConfigurationSettings.AppSettings["OutputFolderPath"] + "REVIEWED";
GetDocumentTypeInfo = GetInfoFromDocumentType("TAA4 REVIEW");
SetDocumentTypeInfo(GetDocumentTypeInfo);
//if (GetDocumentTypeInfo.Tables[0].Rows.Count == 1)
//{
// StrTPUDOC = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["TPUDOC"].ToString().Trim() + @"""";
// StrMatchReqYN = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["MATCHREQYN"].ToString().Trim() + @"""";
// StrProcessReqYN = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["PROCESSREQYN"].ToString().Trim() + @"""";
// StrMaySatisfySuspense = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["MaySatisfySuspense"].ToString().Trim() + @"""";
// StrUIDoc = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["UIDoc"].ToString().Trim() + @"""";
// StrDocType = GetDocumentTypeInfo.Tables[0].Rows[0]["FORMID"].ToString().Trim(); //"ETA858";
// StrStartUnit = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["STARTWORKSTEP"].ToString().Trim() + @""""; // @"""" + "TPU" + @"""";
//}
//StrDocType = "TAA4";
//StrStartUnit = @"""" + "REPOSITORY" + @"""";
}
else if ((CSVFilename.ToString().Contains("Issued") == true))
{
//For Issued
Destination = ConfigurationSettings.AppSettings["OutputFolderPath"] + "ISSUED";
XMLFilePath = ConfigurationSettings.AppSettings["OutputFolderPath"] + "ISSUED";
GetDocumentTypeInfo = GetInfoFromDocumentType("TAA4 ISSUANCE");
SetDocumentTypeInfo(GetDocumentTypeInfo);
//StrDocType = "TAA4";
//StrStartUnit = @"""" + "TPU" + @"""";
}
else if ((CSVFilename.ToString().Contains("Revoked") == true))
{
//For Revoked
Destination = ConfigurationSettings.AppSettings["OutputFolderPath"] + "REVOKED";
XMLFilePath = ConfigurationSettings.AppSettings["OutputFolderPath"] + "REVOKED";
GetDocumentTypeInfo = GetInfoFromDocumentType("TAA4 REVOKED");
SetDocumentTypeInfo(GetDocumentTypeInfo);
//StrDocType = "TAA4";
//StrStartUnit = @"""" + "TPU" + @"""";
}
//For Re-open
else if ((CSVFilename.ToString().Contains("WAIVERS_REOPENED") == true))
{
//For Revoked
Destination = ConfigurationSettings.AppSettings["OutputFolderPath"] + "WAIVERS_REOPENED";
XMLFilePath = ConfigurationSettings.AppSettings["OutputFolderPath"] + "WAIVERS_REOPENED";
GetDocumentTypeInfo = GetInfoFromDocumentType("TAA4 REOPEN");
SetDocumentTypeInfo(GetDocumentTypeInfo);
//StrDocType = "TAA4";
//StrStartUnit = @"""" + "TPU" + @"""";
}
else
{
return false;
}
oWord = new Word.Application();
oWord.Visible = false;
MyWordDoc = oWord.Documents.Add(ref FileName, ref missing, ref missing, ref missing);
//For Trade(Training) or Reviewed template
if ((CSVFilename.ToString().Contains("Trade") == true) || (CSVFilename.ToString().Contains("Reviewed") == true) | (CSVFilename.ToString().Contains("Issued") == true) | (CSVFilename.ToString().Contains("Revoked") == true) | (CSVFilename.ToString().Contains("WAIVERS_REOPENED") == true))
{
StrSSN = row[4].ToString().Trim();
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""SSN""" + " value= " + @"""" + row[4].ToString().Trim() + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""TPU_SSN""" + " value= " + @"""" + row[4].ToString().Trim() + @"""" + "/>";
StrProjectID = row[5].ToString().Trim();
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""TPUPROJECTID""" + " value= " + @"""" + row[5].ToString().Trim() + @"""" + "/>";
StrClaimantName = row[2].ToString().Trim() + " " + row[3].ToString().Trim() + " " + row[1].ToString().Trim();
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""CLAIMANTNAME""" + " value= " + @"""" + StrClaimantName + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""TPU_CLAIMANTNAME""" + " value= " + @"""" + StrClaimantName + @"""" + "/>";
}
//Get some information from ClaimantInfo table based on current SSN+ TPU Project ID
GetClaimantInfoDS = GetClaimantInfo(StrSSN, StrProjectID);
if (GetClaimantInfoDS.Tables[0].Rows.Count==1)
{
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""LONUMBER""" + " value= " + @"""" + GetClaimantInfoDS.Tables[0].Rows[0]["LONUMBER"].ToString().Trim() + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""TPU_LONUMBER""" + " value= " + @"""" + GetClaimantInfoDS.Tables[0].Rows[0]["LONUMBER"].ToString().Trim() + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""CWE""" + " value= " + @"""" + GetClaimantInfoDS.Tables[0].Rows[0]["CWE"].ToString().Trim() + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""WBA""" + " value= " + @"""" + GetClaimantInfoDS.Tables[0].Rows[0]["WBA"].ToString().Trim() + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""TYPEPAY""" + " value= " + @"""" + GetClaimantInfoDS.Tables[0].Rows[0]["TYPEPAY"].ToString().Trim() + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""BASICADDITIONAL""" + " value= " + @"""" + GetClaimantInfoDS.Tables[0].Rows[0]["BASICADDITIONAL"].ToString().Trim() + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""WAIVERTRAINING""" + " value= " + @"""" + GetClaimantInfoDS.Tables[0].Rows[0]["WAIVERTRAINING"].ToString().Trim() + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""WAIVERREVIEWEDDATE""" + " value= " + @"""" + GetClaimantInfoDS.Tables[0].Rows[0]["WAIVERREVIEWEDDATE"].ToString().Trim() + @"""" + "/>";
}
else
{
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""LONUMBER""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""TPU_LONUMBER""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""CWE""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""WBA""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""TYPEPAY""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""BASICADDITIONAL""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""WAIVERTRAINING""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""WAIVERREVIEWEDDATE""" + " value= " + @"""" + "" + @"""" + "/>";
}
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""FORMNUMBER""" + " value= " + @"""" + StrDocType + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""DOCSOURCE""" + " value= " + StrDocSource + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""INDEXERID""" + " value= " + StrIndexerID + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""BATCHID""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""BYE""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""BYE_YMD""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""STARTUNIT""" + " value=" + StrStartUnit +"/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""TPUDOC""" + " value= " + StrTPUDOC + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""MATCHREQYN""" + " value= " + StrMatchReqYN + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""PROCESSREQYN""" + " value= " + StrProcessReqYN + "/>";
if (CSVFilename.ToString().Contains("Trade")) StrMaySatisfySuspense = @"""" + "Y" + @"""";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""MAYSATISFYSUSPENSE""" + " value= " + StrMaySatisfySuspense + "/>";
string StrDocumentName = "";
StrDocumentName = row[4].ToString() + StrDocType.Trim() + String.Format("{0:MMddyy}", DateTime.Now) + " " + string.Format("{0:HHMMss}", DateTime.Now) ;
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""DOCUMENTNAME""" + " value= " + @"""" + StrDocumentName + @"""" + "/>";
string StrWorkItemName = "";
//Workitemname
StrWorkItemName = row[4].ToString() + " " + "TPU" + " " + row[5].ToString() + " " + StrDocType;
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""WORKITEMNAME""" + " value= " + @"""" + StrWorkItemName + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""PROGRAMTYPE""" + " value= " + @"""" + "TPU" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""DOCUMENTTYPE""" + " value= " + @"""" + StrDocType + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""UIDOC""" + " value= " + StrUIDoc + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""WTMS1""" + " value= " + @"""" + "" + @"""" + "/>";
//Finally three blank fields as required by TPU.xml
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""EFFECTIVEDATE_YMD""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""EFFECTIVEDATE_BYE""" + " value= " + @"""" + "" + @"""" + "/>";
GenerateXMLString = GenerateXMLString + "<attribute name=" + @"""WORKSTEPDECISION""" + " value= " + @"""" + "" + @"""" + "/>";
//Loop thru each and every bookmarks on one document and
//set Value according to retrived data from .csv file
foreach (Word.Bookmark oBM in MyWordDoc.Bookmarks)
{
oBM.Select();
// For Trade (Training) template
if ((CSVFilename.ToString().Contains("Trade") == true))
{
//Client Name
if (oBM.Name.ToString() == "ClientNameBK2") oBM.Range.Text = row[2].ToString().Trim() + " " + row[3].ToString().Trim() + " " + row[1].ToString().Trim();
//Office Name
if (oBM.Name.ToString() == "OfficeNameBK1") oBM.Range.Text = row[0].ToString().Trim();
//SSN
if (oBM.Name.ToString() == "SSNBK3") oBM.Range.Text = string.Format("{0:0##-##-####}", Int32.Parse(StrSSN.Trim()));
//Petition
if (oBM.Name.ToString() == "PetitionBK4") oBM.Range.Text = StrProjectID;
//Bona Fide Application Date
if (oBM.Name.ToString() == "BFADBK5") oBM.Range.Text = ConvertToCorrectDateTime(row[6].ToString().Trim());
//Activity Code
if (oBM.Name.ToString() == "ACBK6") oBM.Range.Text = row[7].ToString().Trim();
//Training Provider
if (oBM.Name.ToString() == "TraPrvdBK7") oBM.Range.Text = row[8].ToString().Trim();
//Course Name
if (oBM.Name.ToString() == "CourseNameBK8") oBM.Range.Text = row[9].ToString().Trim();
//Projected Start Date
if (oBM.Name.ToString() == "PrjStartDateBK9") oBM.Range.Text = ConvertToCorrectDateTime(row[10].ToString().Trim());
//Actual Start Date
if (oBM.Name.ToString() == "ActStartDateBK10") oBM.Range.Text = ConvertToCorrectDateTime(row[11].ToString().Trim());
//Projected End Date
if (oBM.Name.ToString() == "PrjEndDateBK11") oBM.Range.Text = ConvertToCorrectDateTime(row[12].ToString().Trim());
//Actual End Date
if (oBM.Name.ToString() == "ActEndDateBK12") oBM.Range.Text = ConvertToCorrectDateTime(row[13].ToString().Trim());
//Training Completed
if (oBM.Name.ToString() == "TRCompletedBK13") oBM.Range.Text = row[14].ToString().Trim();
//Weeks Remedial
if (oBM.Name.ToString() == "WeeksRemedialBK14") oBM.Range.Text = row[15].ToString().Trim();
//Weeks Occupational
if (oBM.Name.ToString() == "WeeksOccptnalBK15") oBM.Range.Text = row[16].ToString().Trim();
//Create Date
if (oBM.Name.ToString() == "CreateDateBK16") oBM.Range.Text = ConvertToCorrectDateTime(row[17].ToString().Trim());
//Edit Date
if (oBM.Name.ToString() == "EditDateBK17") oBM.Range.Text = ConvertToCorrectDateTime(row[18].ToString().Trim());
//Staff Member Name
if (oBM.Name.ToString() == "StaffMemNameBK18") oBM.Range.Text = row[19].ToString().Trim() + " " + row[20].ToString().Trim();
//Staff Phone NO
if (oBM.Name.ToString() == "StaffPhNoBK19") oBM.Range.Text = row[21].ToString().Trim();
//Staff E-Mail Address
if (oBM.Name.ToString() == "StaffEMailAdBK20") oBM.Range.Text = row[22].ToString().Trim();
//Date Received
if (oBM.Name.ToString() == "DateReceivedBK21") oBM.Range.Text = String.Format("{0:dddd, MMMM d, yyyy}", DateTime.Now);
//Comments
if (oBM.Name.ToString() == "CommentsBK22") oBM.Range.Text = row[23].ToString().Trim();
}
//For Reviewed template
else if ((CSVFilename.ToString().Contains("Reviewed") == true) ||
(CSVFilename.ToString().Contains("Issued") == true) ||
(CSVFilename.ToString().Contains("Revoked") == true) ||
(CSVFilename.ToString().Contains("WAIVERS_REOPENED") == true)
)
{
//Client Name
if (oBM.Name.ToString() == "ClientNameBK2") oBM.Range.Text = row[2].ToString().Trim() + " " + row[3].ToString().Trim() + " " + row[1].ToString().Trim();
//Office Name
if (oBM.Name.ToString() == "OfficeNameBK1") oBM.Range.Text = row[0].ToString().Trim();
//SSN
if (oBM.Name.ToString() == "SSNBK3") oBM.Range.Text = string.Format("{0:0##-##-####}", Int32.Parse(StrSSN.Trim()));
//Petition
if (oBM.Name.ToString() == "PetitionBK4") oBM.Range.Text = StrProjectID.Trim();
//Company Name
if (oBM.Name.ToString() == "CompanyName5") oBM.Range.Text = row[6].ToString().Trim();
//Narrative Reason
if (oBM.Name.ToString() == "NarrativeReason") oBM.Range.Text = row[8].ToString().Trim();
//Date Received
if (oBM.Name.ToString() == "DateReceivedBK21") oBM.Range.Text = String.Format("{0:dddd, MMMM d, yyyy}", DateTime.Now);
if ((CSVFilename.ToString().Contains("Reviewed") == true) || (CSVFilename.ToString().Contains("REVIEWED") == true))
{
//Review Date
if (oBM.Name.ToString() == "ReviewDate6") oBM.Range.Text = ConvertToCorrectDateTime(row[7].ToString().Trim());
//Staff Phone NO
if (oBM.Name.ToString() == "StaffPhNoBK19") oBM.Range.Text = row[14].ToString().Trim();
//Staff Member Name
if (oBM.Name.ToString() == "StaffMemNameBK18") oBM.Range.Text = row[13].ToString().Trim() + " " + row[12].ToString().Trim();
//ExtendedThru date
if (oBM.Name.ToString() == "ExtendedThrDate") oBM.Range.Text = ConvertToCorrectDateTime(row[9].ToString().Trim());
//Create Date
if (oBM.Name.ToString() == "CreateDateBK16") oBM.Range.Text = ConvertToCorrectDateTime(row[10].ToString().Trim());
//Edit Date
if (oBM.Name.ToString() == "EditDateBK17") oBM.Range.Text = ConvertToCorrectDateTime(row[11].ToString().Trim());
//Staff E-Mail Address
if (oBM.Name.ToString() == "StaffEMailAdBK20") oBM.Range.Text = row[15].ToString().Trim();
}
// Issued
else if ((CSVFilename.ToString().Contains("Issued") == true) )
{
//Separation Date
if (oBM.Name.ToString() == "SeparationDate") oBM.Range.Text = ConvertToCorrectDateTime(row[7].ToString().Trim());
if (oBM.Name.ToString() == "IssuanceDate") oBM.Range.Text = ConvertToCorrectDateTime(row[9].ToString().Trim());
//ExtendedThru date
if (oBM.Name.ToString() == "ExtendedThrDate") oBM.Range.Text = ConvertToCorrectDateTime(row[10].ToString().Trim());
//Create Date
if (oBM.Name.ToString() == "CreateDateBK16") oBM.Range.Text = ConvertToCorrectDateTime(row[11].ToString().Trim());
//Edit Date
if (oBM.Name.ToString() == "EditDateBK17") oBM.Range.Text = ConvertToCorrectDateTime(row[12].ToString().Trim());
//Staff Phone NO
if (oBM.Name.ToString() == "StaffPhNoBK19") oBM.Range.Text = row[15].ToString().Trim();
//Staff Member Name
if (oBM.Name.ToString() == "StaffMemNameBK18") oBM.Range.Text = row[13].ToString().Trim() + " " + row[14].ToString().Trim();
//Staff E-Mail Address
if (oBM.Name.ToString() == "StaffEMailAdBK20") oBM.Range.Text = row[16].ToString().Trim();
}
// Revoked
else if ((CSVFilename.ToString().Contains("Revoked") == true))
{
//Review Date
if (oBM.Name.ToString() == "ReviewDate6") oBM.Range.Text = ConvertToCorrectDateTime(row[7].ToString().Trim());
//Staff Phone NO
if (oBM.Name.ToString() == "StaffPhNoBK19") oBM.Range.Text = row[13].ToString().Trim();
//Staff Member Name
if (oBM.Name.ToString() == "StaffMemNameBK18") oBM.Range.Text = row[12].ToString().Trim() + " " + row[11].ToString().Trim();
//Create Date
if (oBM.Name.ToString() == "CreateDateBK16") oBM.Range.Text = ConvertToCorrectDateTime(row[9].ToString().Trim());
//Edit Date
if (oBM.Name.ToString() == "EditDateBK17") oBM.Range.Text = ConvertToCorrectDateTime(row[10].ToString().Trim());
//Staff E-Mail Address
if (oBM.Name.ToString() == "StaffEMailAdBK20") oBM.Range.Text = row[14].ToString().Trim();
}
//Re-open
else if ((CSVFilename.ToString().Contains("WAIVERS_REOPENED") == true))
{
//Review Date
if (oBM.Name.ToString() == "ReviewDate6") oBM.Range.Text = ConvertToCorrectDateTime(row[7].ToString().Trim());
//Staff Phone NO
if (oBM.Name.ToString() == "StaffPhNoBK19") oBM.Range.Text = row[13].ToString().Trim();
//Staff Member Name
if (oBM.Name.ToString() == "StaffMemNameBK18") oBM.Range.Text = row[12].ToString().Trim() + " " + row[11].ToString().Trim();
//ExtendedThru date
if (oBM.Name.ToString() == "ExtendedThrDate") oBM.Range.Text = ConvertToCorrectDateTime(row[8].ToString().Trim());
//Create Date
if (oBM.Name.ToString() == "CreateDateBK16") oBM.Range.Text = ConvertToCorrectDateTime(row[9].ToString().Trim());
//Edit Date
if (oBM.Name.ToString() == "EditDateBK17") oBM.Range.Text = ConvertToCorrectDateTime(row[10].ToString().Trim());
//Staff E-Mail Address
if (oBM.Name.ToString() == "StaffEMailAdBK20") oBM.Range.Text = row[14].ToString().Trim();
}
}
}
//Close the xml tag and generate xml
GenerateXMLString = GenerateXMLString + "</import>";
if (line.Length != 0)
{
XMLFilePath = XMLFilePath + StrDocumentName.Replace(" ", "") + ".TXT";
Destination = Destination + StrDocumentName.Replace(" ", "") + ".DOC";
FileStream XMLFile = new FileStream(XMLFilePath, FileMode.Create, FileAccess.ReadWrite);
StreamWriter SW = new StreamWriter(XMLFile);
SW.Write(GenerateXMLString);
SW.Flush();
SW.Close();
XMLFile.Close();
logSuccess.Logs("GenerateXMLDocs Function : ","", " Document Generated Successfully For : --> SSN # " + StrSSN + ", Petition ID #" + StrProjectID + " ,Document Name # " + StrDocumentName);
FilesCompleted = FilesCompleted+1;
lblStatusValue.Text = "Current Status : No. of Documents Generated : " + FilesCompleted;
lblStatusValue.Refresh();
System.Windows.Forms.Application.DoEvents();
BtnStop.Focus();
}
//Close application properly otherwise it will create memory leak
MyWordDoc.SaveAs(ref Destination, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);
oWord.Quit(ref DoSaveChanges, ref missing, ref missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWord);
}
catch (Exception Ex)
{
//Delete each and every .txt file as there is error in the process from the output directory otherwise
// this will create mess
CleanUptheOutputFolder();
log.Logs("GenerateXMLDoc Function : ", Ex.Message + "" + Ex.Source);
return false;
}
}
//Once File curson is at end position of the file than convert all the .TXT file to .XML file , so import
//Service will suck it in if any error while generating .txt file than delete all the .txt/.doc files from the output
//folder and give msg to user about error.
if (readFile.EndOfStream == true)
{
//Now Move Selected Flat File from current location to completed batch location
//so in future user will NOT select again by mistake.
string currentdatetime = DateTime.Now.ToShortDateString().Replace("/", "") + DateTime.Now.Hour + DateTime.Now.Minute;
DirectoryInfo NewDir;
NewDir = new DirectoryInfo(ConfigurationSettings.AppSettings["CompletedBatchPath"].ToString().Trim() + currentdatetime + "\\");
if (NewDir.Exists==false)
{
NewDir.Create();
if (OriginalFile!="")
{
try
{
MoveFlatFiles(InputCSVFile.ToString(), CSVFilename, readFile, NewDir);
}
catch (Exception Ex)
{
log.Logs("GenerateXMLDoc Function : ", Ex.Message + "" + Ex.Source);
return false;
}
}
}
else
{
if (OriginalFile != "")
{
try
{
MoveFlatFiles(InputCSVFile.ToString(), CSVFilename, readFile, NewDir);
}
catch (Exception Ex)
{
log.Logs("GenerateXMLDoc Function : ", Ex.Message + "" + Ex.Source);
return false;
}
}
}
}
return true;
}
}
//Set Common information about documenttype
private void SetDocumentTypeInfo(DataSet GetDocumentTypeInfo)
{
if (GetDocumentTypeInfo.Tables[0].Rows.Count == 1)
{
StrTPUDOC = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["TPUDOC"].ToString().Trim() + @"""";
StrMatchReqYN = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["MATCHREQYN"].ToString().Trim() + @"""";
StrProcessReqYN = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["PROCESSREQYN"].ToString().Trim() + @"""";
StrMaySatisfySuspense = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["MaySatisfySuspense"].ToString().Trim() + @"""";
StrUIDoc = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["UIDoc"].ToString().Trim() + @"""";
StrDocType = GetDocumentTypeInfo.Tables[0].Rows[0]["FORMID"].ToString().Trim(); //"ETA858";
StrStartUnit = @"""" + GetDocumentTypeInfo.Tables[0].Rows[0]["STARTWORKSTEP"].ToString().Trim() + @""""; // @"""" + "TPU" + @"""";
}
else if(GetDocumentTypeInfo.Tables[0].Rows.Count>1)
{
MessageBox.Show("Document Type table Contains more than 1 Row for Selected Document Type, Pls. Check DocumentType table ");
}
else
{
MessageBox.Show("Pls. Check DocumentType table for this documenttype ");
}
}
//Convert all .txt files to .XML files
private void ConvertTxtToXML()
{
try
{
DirectoryInfo DirInfo = new DirectoryInfo(ConfigurationSettings.AppSettings["OutputFolderPath"]);
FileInfo[] txtFiles = DirInfo.GetFiles("*.TXT");
string filepath = ConfigurationSettings.AppSettings["OutputFolderPath"].ToString().Trim();
//Convert Each .TXT file to .XML by copying it first and then delete .txt files
foreach (FileInfo file in txtFiles)
{
file.CopyTo(Path.ChangeExtension(file.FullName, ".XML"), true);
file.Delete();
}
}
catch (Exception Exc)
{
log.Logs("ConvertTxtToXML Function : ", Exc.Message + "" + Exc.Source);
}
}
//Clean UP the out put folder as there is an error.
private void CleanUptheOutputFolder()
{
try
{
DirectoryInfo DirInfo = new DirectoryInfo(ConfigurationSettings.AppSettings["OutputFolderPath"]);
FileInfo[] txtFiles = DirInfo.GetFiles("*.TXT");
foreach (FileInfo file in txtFiles)
{
//Delete associated .DOC FILES TOO
string AssociatedDoc = Path.GetFileNameWithoutExtension(file.FullName) + ".DOC";
//Delete .TXT file
file.Delete();
//Delete associated .DOC file
File.Delete(DirInfo + AssociatedDoc);
}
}
catch (Exception Ex)
{
log.Logs("CleanUptheOutputFolder Function : ", Ex.Message + "" + Ex.Source);
}
}
//Move the flat files Once process is done successfully.
private void MoveFlatFiles(string InputCSVFile, string CSVFileName, StreamReader readFile, DirectoryInfo NewDir)
{
try
{
DirectoryInfo InputDir = new DirectoryInfo(Path.GetDirectoryName(InputCSVFile.ToString()) + "\\");
string GetFileType = CSVFileName.Substring(0, 5);
GetFileType = GetFileType + "*.TXT";
FileInfo[] FlatFileType = InputDir.GetFiles(GetFileType);
readFile.Close();
foreach (FileInfo InputFileType in FlatFileType)
{
if (InputFileType.Name.Contains("-")==true)
{
InputFileType.MoveTo(NewDir + "\\" + InputFileType);
}
else
{
InputFileType.Delete();
}
}
}
catch (Exception Ex)
{
log.Logs("MoveFlatFiles Function : ", Ex.Message + "" + Ex.Source);
}
}
/// <summary>
/// Get some of the Data from claimant info table
/// </summary>
/// <returns></returns>
public DataSet GetClaimantInfo(string SSN, string ProjectID)
{
DataSet GetClaimantInfoDS = new DataSet();
string ConnectionString = ConfigurationSettings.AppSettings["WorkFlowConnString"];
try
{
SqlConnection Conn = new SqlConnection(ConnectionString);
string SelectClaimantInfo = "Select * from ClaimantInfo(nolock) where ProgramType = 'TPU' AND SSN='" + SSN + "'" + " AND ProjectID='" + ProjectID + "'";
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
SqlDataAdapter ObjDA = new SqlDataAdapter(SelectClaimantInfo, Conn);
ObjDA.Fill(GetClaimantInfoDS);
}
catch (Exception Ex)
{
log.Logs("GetClaimantInfo Function : ", Ex.Message + "" + Ex.Source);
MessageBox.Show("Failed to Get Matching Records at Claimantinfo Table");
}
return GetClaimantInfoDS;
}
/// <summary>
/// Get StartUnit related information from Document type table
/// </summary>
/// <returns></returns>
public DataSet GetInfoFromDocumentType(string DocumentType)
{
DataSet GetDocumentTypeInfoDS = new DataSet();
string ConnectionString = ConfigurationSettings.AppSettings["WorkFlowConnString"];
try
{
SqlConnection Conn = new SqlConnection(ConnectionString);
string SelectDocumentTypeInfo = "Select * from Documenttype(nolock) where TPUDOC = 'T' AND DOCUMENTTYPE='" + DocumentType.Trim() + "'";
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
SqlDataAdapter ObjDA = new SqlDataAdapter(SelectDocumentTypeInfo, Conn);
ObjDA.Fill(GetDocumentTypeInfoDS);
}
catch (Exception Ex)
{
log.Logs("GetInfoFromDocumentType Function : ", Ex.Message + "" + Ex.Source);
MessageBox.Show("Failed to Get Matching Records at DocumentType Table");
}
return GetDocumentTypeInfoDS;
}
//Adding filename to database for tracing.
private int AddFileNameToDB(string TRAFileName)
{
int BatchStatus = 0;
string ConnectionString = ConfigurationSettings.AppSettings["COLDDBConnString"];
int numberOfRows=0;
try
{
SqlConnection mySqlConnection = new SqlConnection(ConnectionString);
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText = "INSERT INTO TRABatchLOG (TRAFileName,BatchProcessStartTime,BatchStatus) VALUES (" + "'" + TRAFileName.Trim() + "'" + "," + "'" + DateTime.Now.ToString() + "'" + "," + BatchStatus + ")";
if (mySqlConnection.State != ConnectionState.Open)
{
mySqlConnection.Open();
}
numberOfRows = mySqlCommand.ExecuteNonQuery();
}
catch (Exception Ex)
{
log.Logs("AddFileNametoDB Function : ", Ex.Message + "" + Ex.Source);
MessageBox.Show("Failed to Insert Records at TRABatchLOG table");
}
return numberOfRows;
}
//First check that Flat file is already processed or NOT.
private bool CheckFileAlreadyProcessed(string SelectedFlatFile)
{
DataSet DSCheckProcessed = new DataSet();
string ConnectionString = ConfigurationSettings.AppSettings["COLDDBConnString"];
SqlConnection mySqlConnection = new SqlConnection(ConnectionString);
try
{
string SelectClaimantInfo = "Select * from TRABatchLOG(nolock) where TRAFileName=" + "'" + SelectedFlatFile.Trim() + "'" + " AND BATCHSTATUS=2 ";
if (mySqlConnection.State != ConnectionState.Open)
{
mySqlConnection.Open();
}
SqlDataAdapter ObjDA = new SqlDataAdapter(SelectClaimantInfo, mySqlConnection);
ObjDA.Fill(DSCheckProcessed);
}
catch (Exception Ex)
{
log.Logs("CheckFileAlreadyProcessed Function : ", Ex.Message + "" + Ex.Source);
MessageBox.Show("Failed to Check Batch existance at TRABatchLOG table");
}
if (DSCheckProcessed.Tables[0].Rows.Count > 0)
{
return true;
}
else
{
return false;
}
}
//Updating TRABatch Log table once batch is done/failed
private int UpdateTRABatchLogToDB(string TRAFileName,string Flag)
{
int BatchStatus = 0;
int numberOfRows=0;
try
{
if (Flag == "SUCCESS")
{
BatchStatus = 2;
}
else if (Flag == "FAILED")
{
BatchStatus = 3;
}
string ConnectionString = ConfigurationSettings.AppSettings["COLDDBConnString"];
SqlConnection mySqlConnection = new SqlConnection(ConnectionString);
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText = "UPDATE TRABatchLOG SET BatchStatus =" + BatchStatus + "," + "BatchProcessEndTime = " + "'" + DateTime.Now.ToString() + "'" + "WHERE TRAFILENAME =" + "'" + TRAFileName.Trim() + "' and BATCHSTATUS=0 ";
if (mySqlConnection.State != ConnectionState.Open)
{
mySqlConnection.Open();
}
numberOfRows = mySqlCommand.ExecuteNonQuery();
}
catch (Exception Exc)
{
log.Logs("UpdateTRABatchLogToDB Function : ", Exc.Message + "" + Exc.Source);
MessageBox.Show("Failed to Update TRABatchLOG table");
}
return numberOfRows;
}
//This function converts all the given date to correct format
private string ConvertToCorrectDateTime(string GivenDate)
{
string CorrectDate="";
if (GivenDate.Length>0)
{
int theMonth = System.Convert.ToInt32(GivenDate.Substring(0, 2));
int theDay = System.Convert.ToInt32(GivenDate.Substring(2, 2));
int theYear = System.Convert.ToInt32(GivenDate.Substring(4, 4));
CorrectDate = theMonth + "-" + theDay + "-" + theYear;
CorrectDate = String.Format("{0:MM-dd-yyyy}", DateTime.Parse(CorrectDate));
}
else
{
CorrectDate = "";
}
return CorrectDate;
}
private void TRAMain_Load(object sender, EventArgs e)
{
}
private void BtnExit_Click(object sender, EventArgs e)
{
System.Windows.Forms.Application.Exit();
}
public void BtnStop_Click(object sender, EventArgs e)
{
BtnStop.Enabled = false;
BtnGenerateDocsFromTemplate.Enabled = true;
StopClicked = true;
BtnExit.Enabled = true;
}
private void TRAMain_FormClosing(object sender, FormClosingEventArgs e)
{
}
}
}
MOD EDIT: Moved code into post. Don't ATTACH your code, post it IN CODE TAGS, as I have done, within the message.
Where the code is so large, I have wrapped it in spoiler tags.

New Topic/Question
Reply




MultiQuote







|