public void DownloadFls(int UserInfoIdCheck, int GroupId, DateTime?SubmissionDate)
{
#region DownloadZipFolder
LoginUserDetails objLoginUserDetails = (LoginUserDetails)InsiderTrading.Common.Common.GetSessionValue((string)ConstEnum.SessionValue.UserDetails);
using (NSEGroupSL ObjNSEGroupSL = new NSEGroupSL())
{
List <NSEGroupDocumentMappingDTO> list = ObjNSEGroupSL.Get_All_NSEGroupDocument(objLoginUserDetails.CompanyDBConnectionString, GroupId, UserInfoIdCheck);
string downloadFle = string.Empty;
string directory = ConfigurationManager.AppSettings["Document"];
using (var zipStream = new ZipOutputStream(Response.OutputStream))
{
if (UserInfoIdCheck == 1)
{
foreach (var downloadDt in list)
{
downloadFle = "attachment; filename=" + Convert.ToString(downloadDt.DownloadedDate) + ".zip";
}
}
else
{
downloadFle = "attachment; filename=" + Convert.ToString(SubmissionDate) + ".zip";
}
Response.AddHeader("Content-Disposition", downloadFle);
Response.ContentType = "application/zip";
int recCount = 0;
string docName = string.Empty;
foreach (var docPath in list)
{
if (UserInfoIdCheck == 1)
{
recCount = recCount + 1;
docName = docPath.DocumentName + " " + "(" + recCount + ")" + ".pdf";
}
else
{
docName = docPath.DocumentName;
}
string documentPath = (Path.Combine(directory, Convert.ToString(objLoginUserDetails.CompanyName), docPath.MapToTypeCodeId.ToString(), GroupId.ToString(), docPath.GUID));
byte[] fileBytes = System.IO.File.ReadAllBytes(documentPath);
var fileEntry = new ZipEntry(Path.GetFileName(documentPath).Replace(docPath.GUID, docName))
{
Size = fileBytes.Length,
};
zipStream.PutNextEntry(fileEntry);
zipStream.Write(fileBytes, 0, fileBytes.Length);
}
zipStream.Flush();
zipStream.Close();
}
}
#endregion DownloadZipFolder
}
public void DownloadExcel(int acid, int GroupId)
{
int UserInfoIdCheck = 1;
string exlFilename = string.Empty;
LoginUserDetails objLoginUserDetails = (LoginUserDetails)InsiderTrading.Common.Common.GetSessionValue((string)ConstEnum.SessionValue.UserDetails);
using (NSEGroupSL ObjNSEGroupSL = new NSEGroupSL())
{
List <NSEGroupDocumentMappingDTO> listDownloadDate = ObjNSEGroupSL.Get_All_NSEGroupDocument(objLoginUserDetails.CompanyDBConnectionString, GroupId, UserInfoIdCheck);
foreach (var downloadDt in listDownloadDate)
{
exlFilename = "attachment; filename=Stock Exchange Submission" + " " + Convert.ToString(downloadDt.DownloadedDate) + ".xlsx";
}
}
string sConnectionString = string.Empty;
sConnectionString = objLoginUserDetails.CompanyDBConnectionString;
SqlConnection con = new SqlConnection(sConnectionString);
SqlCommand cmd = new SqlCommand();
con.Open();
DataTable dt = new DataTable();
cmd = new SqlCommand("st_tra_NSEDownloadGroupWiseExcel", con);
cmd.Parameters.AddWithValue("@GroupId", GroupId);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(dt);
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Stock Exchange Submission");
var totalCols = dt.Columns.Count;
var totalRows = dt.Rows.Count;
workSheet.Cells["V1:AA1"].Merge = true;
workSheet.Cells["V1:AA1"].Value = "Trading in derivatives";
workSheet.Cells["V1:AA1"].Style.Border.Top.Style = workSheet.Cells["V1:AA1"].Style.Border.Bottom.Style = workSheet.Cells["V1:AA1"].Style.Border.Left.Style = workSheet.Cells["V1:AA1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
workSheet.Cells["V1:AA1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells["G2:I2"].Merge = true;
workSheet.Cells["G2:I2"].Value = "Securities held prior*";
workSheet.Cells["J2:M2"].Merge = true;
workSheet.Cells["J2:M2"].Value = "Securities Acquired/Disposed";
workSheet.Cells["N2:P2"].Merge = true;
workSheet.Cells["N2:P2"].Value = "Securities held post*";
workSheet.Cells["Q2:R2"].Merge = true;
workSheet.Cells["Q2:R2"].Value = "Date of allotment advice/acquisition of shares/sale of shares*specify*";
workSheet.Cells["S2"].Merge = true;
workSheet.Cells["S2"].Value = "Date of intimation to Company*";
workSheet.Cells["T2"].Merge = true;
workSheet.Cells["T2"].Value = "Mode of Acquisition/Disposal";
workSheet.Cells["U2"].Merge = true;
workSheet.Cells["U2"].Value = "Type of Contract";
workSheet.Cells["V2"].Merge = true;
workSheet.Cells["V2"].Value = "Contract Specification from type of security";
workSheet.Cells["W2:X2"].Merge = true;
workSheet.Cells["W2:X2"].Value = "Buy";
workSheet.Cells["Y2:Z2"].Merge = true;
workSheet.Cells["Y2:Z2"].Value = "Sell";
workSheet.Cells["AA2"].Merge = true;
workSheet.Cells["AA2"].Value = "Exchange on which the trade was Executed*";
workSheet.Cells["AB2"].Merge = true;
workSheet.Cells["AB2"].Value = "Total Value In Aggregate";
string cellRangeHeader = "A2:AB2";
using (ExcelRange rngHeader = workSheet.Cells[cellRangeHeader])
{
rngHeader.Style.WrapText = true;
rngHeader.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rngHeader.Style.Font.Bold = true;
rngHeader.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
rngHeader.Style.Border.Top.Style = rngHeader.Style.Border.Bottom.Style = rngHeader.Style.Border.Left.Style = rngHeader.Style.Border.Right.Style = ExcelBorderStyle.Thin;
}
for (var col = 1; col <= totalCols; col++)
{
workSheet.Cells[3, col].Value = dt.Columns[col - 1].ColumnName;
workSheet.Cells[3, col].Style.Font.Name = "Arial";
workSheet.Cells[3, col].Style.Font.Size = 10;
workSheet.Cells[3, col].Style.Font.Color.SetColor(System.Drawing.Color.Black);
string cellRange = "A3:AB3";
using (ExcelRange rng = workSheet.Cells[cellRange])
{
rng.Style.WrapText = true;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
rng.Style.Font.Bold = true;
rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
rng.Style.Border.Top.Style = rng.Style.Border.Bottom.Style = rng.Style.Border.Left.Style = rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
}
}
int excelRow = 3;
for (var row = 0; row < totalRows; row++)
{
for (var col = 0; col < totalCols; col++)
{
workSheet.Cells[excelRow + 1, col + 1].Value = dt.Rows[row][col].ToString();
workSheet.Cells[excelRow + 1, col + 1].Style.Border.Top.Style = workSheet.Cells[excelRow + 1, col + 1].Style.Border.Bottom.Style = workSheet.Cells[excelRow + 1, col + 1].Style.Border.Left.Style = workSheet.Cells[excelRow + 1, col + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
workSheet.Cells[excelRow + 1, col + 1].Style.WrapText = true;
workSheet.Cells[excelRow + 1, col + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
excelRow++;
}
using (var memoryStream = new MemoryStream())
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", exlFilename);
excel.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}