//////////////////////////////////////////ROM File Upload ////////////////////////////////////////////////////
public async Task <DataTable> Cutodian_ROMUpload_Details(FJC_ROMUpload fjc_ROMUpload, string Token)
{
//validation job
//Here Validate bulk file
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
dt1 = await RegisterCustodianROM(fjc_ROMUpload.event_id, fjc_ROMUpload.doc_id, Token, 0);
ManageErrorUploads dd = new ManageErrorUploads();
dt2 = dd.Manage_ROM_ErrorUploads(dt1, FolderPaths.ProcessType.Custodian_ROMUpload, fjc_ROMUpload.doc_id, fjc_ROMUpload.event_id, Token);
return(dt2);
}
//////////////////////////////////////////ROM File Upload ////////////////////////////////////////////////////
public async Task <DataTable> ROMUpload_Details(FJC_ROMUpload fjc_ROMUpload, string Token)
{
DataTable dtUserType = new DataTable();
dtUserType = await(new ManageFileUpload()).GetUserDetailsByTokenID(Token);
switch (fjc_ROMUpload.upload_type.ToLower())
{
//////////////////////////////ROM Intimation////////////////////////
case "intimation":
DataTable dt1 = new DataTable();
dt1 = await RegisterROM_Intimation(fjc_ROMUpload.event_id, fjc_ROMUpload.doc_id, Token, 0); // GetROMUpload_Details(Token);
if (dt1.Rows.Count > 0)
{
Validate_ROM val_ROM = new Validate_ROM();
bool isValid = val_ROM.Validate_File(dt1.Rows[0]["url"].ToString(), Token, fjc_ROMUpload.event_id, dt1.Rows[0]["upload_id"].ToString(), "Yes");
if (isValid)
{
// return await BulkUpload_ROM_Intimation(fjc_ROMUpload.event_id ,fjc_ROMUpload.doc_id, Token,dt1.Rows[0]["upload_id"].ToString());
DataTable dt = new DataTable();
dt = BulkUpload_ROM_Intimation(fjc_ROMUpload.event_id, fjc_ROMUpload.doc_id, Token, dt1.Rows[0]["upload_id"].ToString());
//return await InsertBulkFileUpload(fjc_ROMUpload.event_id ,fjc_ROMUpload.doc_id, Token,dt2.Rows[0]["upload_id"].ToString());
if (dt.Columns.Contains("Error_Num"))
{
ManageErrorUploads _err = new ManageErrorUploads();
DataTable dtSecROMErr = new DataTable();
//Below here return error datatble for second ROM upload file
switch (dtUserType.Rows[0]["type"])
{
case "Issuer Company":
dtSecROMErr = _err.Manage_ROM_ErrorUploads(dt, FolderPaths.ProcessType.Company_ROMUpload, 0, fjc_ROMUpload.event_id, Token);
break;
case "RTA":
dtSecROMErr = _err.Manage_ROM_ErrorUploads(dt, FolderPaths.ProcessType.RTA_ROMUpload, 0, fjc_ROMUpload.event_id, Token);
break;
}
if (dtSecROMErr.Rows.Count > 0)
{
DataTable dtget = new DataTable();
//Below here Register ROM for Error file for second ROM upload file
return(dtget = await RegisterROM_Intimation(fjc_ROMUpload.event_id, Convert.ToInt32(dtSecROMErr.Rows[0]["doc_id"]), Token, 0, "SecondROM", dt1.Rows[0]["upload_id"].ToString()));
}
else
{
return(null);
}
}
else
{
DataSet ds = new DataSet();
DataTable _dt = new DataTable();
_dt.TableName = "Table2";
_dt.Columns.Add("Remark");
ds.Tables.Add(_dt);
_dt.Rows.Add("Process Completed");
return(ds.Tables[0]);
}
}
else
{
return(null); // throw new CustomException.InvalidFileRejected();
}
}
else
{
return(null); //throw new CustomException.InvalidFileNotUploaded();
}
break;
//////////////////Normal ROM Upload ////////////////////////
case "notice":
//Here Validate bulk file
DataTable dt2 = new DataTable();
dt2 = await RegisterROM(fjc_ROMUpload.event_id, fjc_ROMUpload.doc_id, Token, 0); // GetROMUpload_Details(Token);
if (dt2.Rows.Count > 0)
{
Validate_ROM val_ROM = new Validate_ROM();
bool isValid = val_ROM.Validate_File(dt2.Rows[0]["url"].ToString(), Token, fjc_ROMUpload.event_id, dt2.Rows[0]["upload_id"].ToString(), "No"); //("C:\\evoting\\RTA\\ROM\\2020-09-18\\20200918-112617505-CDSL_FileUpload_128_ROM_ANUH_PHARMA_LTD_13220201.txt");//(dt1.Rows[0]["url"].ToString());
if (isValid)
{
//return await InsertBulkFileUpload(fjc_ROMUpload.event_id ,fjc_ROMUpload.doc_id, Token,dt2.Rows[0]["upload_id"].ToString());
DataTable dt = new DataTable();
dt = InsertBulkFileUpload(fjc_ROMUpload.event_id, fjc_ROMUpload.doc_id, Token, dt2.Rows[0]["upload_id"].ToString());
//return await InsertBulkFileUpload(fjc_ROMUpload.event_id ,fjc_ROMUpload.doc_id, Token,dt2.Rows[0]["upload_id"].ToString());
if (dt.Columns.Contains("Error_Num"))
{
ManageErrorUploads _err = new ManageErrorUploads();
DataTable dtSecROMErr = new DataTable();
//Below here return error datatble for second ROM upload file
switch (dtUserType.Rows[0]["type"])
{
case "Issuer Company":
dtSecROMErr = _err.Manage_ROM_ErrorUploads(dt, FolderPaths.ProcessType.Company_ROMUpload, 0, fjc_ROMUpload.event_id, Token);
break;
case "RTA":
dtSecROMErr = _err.Manage_ROM_ErrorUploads(dt, FolderPaths.ProcessType.RTA_ROMUpload, 0, fjc_ROMUpload.event_id, Token);
break;
}
if (dtSecROMErr.Rows.Count > 0)
{
DataTable dtget = new DataTable();
//Below here Register ROM for Error file for second ROM upload file
return(dtget = await RegisterROM(fjc_ROMUpload.event_id, Convert.ToInt32(dtSecROMErr.Rows[0]["doc_id"]), Token, 0, "SecondROM", dt2.Rows[0]["upload_id"].ToString()));
}
else
{
return(null);
}
}
else
{
DataSet ds = new DataSet();
DataTable _dt = new DataTable();
_dt.TableName = "Table1";
_dt.Columns.Add("Remark");
_dt.Rows.Add("Process Completed");
ds.Tables.Add(_dt);
return(ds.Tables[0]);
}
}
else
{
return(null); //throw new CustomException.InvalidFileRejected();
}
}
else
{
return(null); //throw new CustomException.InvalidFileNotUploaded();
}
break;
default:
throw new CustomException.InvalidActivity();
}
}
/////////////////////////////Export to excel/////////////////////
public async Task <DataTable> exportToExcel(string Token, int event_id)
{
DataSet ds = new DataSet();
ds = await getExcelData(Token, event_id);
//if(ds.Tables[0].Rows.Count>0)
if (!ds.Tables[1].Columns.Contains("Error_Message"))
{
if (ds.Tables[0].Rows.Count > 0)
{
//Name of File
string fileName = "Scrutinizer_Evoting_Reports.xlsx";
using (XLWorkbook wb = new XLWorkbook())
{
var summarysheet = wb.Worksheets.Add("Summary Report");
//Add DataTable in Details Report worksheet
var summarysheet1 = wb.Worksheets.Add(ds.Tables[0], "Details Report");
//wb.Worksheets.Add(ds.Tables[0],"Details Report");
summarysheet1.Style.Font.FontColor = XLColor.Black;
summarysheet1.SetAutoFilter(false);
summarysheet1.Tables.FirstOrDefault().Theme = XLTableTheme.None;
summarysheet1.Rows(1, 1).Style.Font.Bold = true;
summarysheet1.Columns().Width = 20;
summarysheet1.Columns().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
//Add Records for Summary Report worksheet
var col1 = summarysheet.Column("A");
var colB = summarysheet.Column("B");
var colC = summarysheet.Column("C");
//col1.Style.Font.Bold = true;
col1.Style.Font.FontColor = XLColor.Black;
summarysheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.Gray;
col1.Width = 60;
colB.Width = 20;
summarysheet.Range("A3:B3").Style.Fill.BackgroundColor = XLColor.Gray;
colC.Width = 30;
var secondsheetstyle = summarysheet.Cell(1, 1).SetValue("Report Generation Date and Time : " + ds.Tables[1].Rows[0]["report_generated_date"].ToString()).Style.Font.Bold = true;
summarysheet.Cell(3, 1).SetValue("EVSN").Style.Font.Bold = true;
summarysheet.Cell(3, 2).SetValue("ISIN").Style.Font.Bold = true;
summarysheet.Cell(4, 1).SetValue(ds.Tables[1].Rows[0]["EVSN"].ToString());
summarysheet.Cell(4, 2).SetValue(ds.Tables[1].Rows[0]["ISIN"].ToString());
// Voting Start Date and Time : 11-06-2020 09:00
// Voting End Date and Time : 14-06-2020 17:00
// Meeting Date and Start Time :15-06-2020 11:00
// Voting Finalisation Date and Time: 15-06-2020 12:05
summarysheet.Cell(6, 1).SetValue("Voting Start Date and Time : " + ds.Tables[1].Rows[0]["Voting Start Date and Time"].ToString());
summarysheet.Cell(7, 1).SetValue("Voting End Date and Time : " + ds.Tables[1].Rows[0]["Voting End Date and Time"].ToString());
summarysheet.Cell(8, 1).SetValue("Meeting Date and Start Time : " + ds.Tables[1].Rows[0]["Meeting Date and Start Time"].ToString());
summarysheet.Cell(9, 1).SetValue("Voting Finalisation Date and Time: " + ds.Tables[1].Rows[0]["Voting Finalisation Date and Time"].ToString());
//Row start from ROW 11
int rowint = 11;
summarysheet.Cell(rowint, 1).SetValue("Res. No.").Style.Font.Bold = true;
summarysheet.Cell(rowint, 2).SetValue("Yes Count").Style.Font.Bold = true;
summarysheet.Cell(rowint, 3).SetValue("Yes (%)").Style.Font.Bold = true;
summarysheet.Cell(rowint, 4).SetValue("No Count").Style.Font.Bold = true;
summarysheet.Cell(rowint, 5).SetValue("No (%)").Style.Font.Bold = true;
summarysheet.Cell(rowint, 6).SetValue("TotalCount").Style.Font.Bold = true;
summarysheet.Cell(rowint, 7).SetValue("Total").Style.Font.Bold = true;
summarysheet.Range("A11:G11").Style.Fill.BackgroundColor = XLColor.Gray;
//summarysheet.Cell(ds.Tables[2].Rows.Count, ds.Tables[2].Columns.Count).SetValue(ds.Tables[2]);
int a = 12;
foreach (DataRow dr in ds.Tables[2].Rows)
{
//Res. No. Yes Count Yes (%) No Count No (%) TotalCount Total
summarysheet.Cell(a, 1).SetValue(dr["Res. No."].ToString());
summarysheet.Cell(a, 2).SetValue(dr["Yes Count"].ToString());
summarysheet.Cell(a, 3).SetValue(dr["Yes (%)"].ToString());
summarysheet.Cell(a, 4).SetValue(dr["No Count"].ToString());
summarysheet.Cell(a, 5).SetValue(dr["No (%)"].ToString());
summarysheet.Cell(a, 6).SetValue(dr["TotalCount"].ToString());
summarysheet.Cell(a, 7).SetValue(dr["Total"].ToString());
a++;
}
using (MemoryStream memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
//Return xlsx Excel File
// File(memoryStream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
//Save To Folder
byte[] bytes = memoryStream.ToArray();
memoryStream.Close();
//convert byte to pdf and save
string actPath = FolderPaths.Scrutinizer.Scrutinizer_Reports();//@"C:\evoting\Agreement\";
string pdffilename = System.DateTime.Now.ToString("yyyyMMdd-HHmmssfff") + fileName;
System.IO.File.WriteAllBytes(Path.Combine(actPath, pdffilename), bytes);
string filePath = Path.Combine(actPath, pdffilename);
//Saving PDF to Folder and database
if (filePath.Length > 0 && filePath != null)
{
Dictionary <string, object> dictfileDnld = new Dictionary <string, object>();
dictfileDnld.Add("@File_Name", pdffilename);
dictfileDnld.Add("@File_Path", filePath);
dictfileDnld.Add("@token", Token);
dictfileDnld.Add("@event_id", event_id);
dictfileDnld.Add("@flag", 0);
DataSet ds2 = new DataSet();
ds2 = await AppDBCalls.GetDataSet("Evote_SpExcelFile_Download", dictfileDnld);
return(Reformatter.Validate_DataTable(ds2.Tables[0]));
}
else
{
return(null);//throw new CustomException.InvalidFileRejected();
}
}
}
}
else
{
return(null);//throw new CustomException.InvalidFileRejected();
}
}
else
{
ManageErrorUploads _err = new ManageErrorUploads();
return(_err.Manage_ROM_ErrorUploads(ds.Tables[1], FolderPaths.ProcessType.Scrutinizer_Reports, 0, event_id, Token));
// string filenamewithdatetime;
// //File name with time stamp and Event no
// filenamewithdatetime = System.DateTime.Now.ToString("yyyyMMdd-HHmmssfff") + "-Scrutinizer_Error.txt";
// //Return Full file path to save to database
// string default_path = FolderPaths.Scrutinizer.Scrutinizer_FileError() + "\\" + filenamewithdatetime ;
// //////////
// //-Start-Error file created
// if (!File.Exists(default_path))
// {
// FileStream fs = File.Create(default_path);
// fs.Flush();
// fs.Close();
// }
// //-End-Error file created
// StringBuilder bs = new StringBuilder();
// foreach (DataRow row in ds.Tables[1].Rows)
// {
// foreach (DataColumn column in ds.Tables[1].Columns)
// {
// bs.Append(column.ColumnName.Replace("Error_Num", "Error Number").Replace("Error_Line", ", Error Line").Replace("Error_Message"," Error Descritpion") + ": ").Append(row[column].ToString());
// }
// bs.AppendLine();
// }
// File.WriteAllText(default_path, bs.ToString());
// /////////
// //Saving file details to Database
// DataSet ds1 = new DataSet();
// if (default_path != null)
// {
// Dictionary<string, object> dictfileDnld = new Dictionary<string, object>();
// dictfileDnld.Add("@File_Name", filenamewithdatetime);
// dictfileDnld.Add("@File_Path", default_path);
// dictfileDnld.Add("@token", Token);
// dictfileDnld.Add("@event_id",event_id);
// dictfileDnld.Add("@flag",1);
// ds1 = await AppDBCalls.GetDataSet("Evote_SpExcelFile_Download", dictfileDnld);
// }
// return ds1.Tables[0];
}
}