private void SpreadExporter_AsyncExportProgressChanged(object sender, ProgressChangedEventArgs e)
{
RdProgressBar.Value1 = e.ProgressPercentage;
RdProgressBar.Update();
RdProgressBar.Refresh();
Application.DoEvents();
}
private void UploadData()
{
try
{
RdLlbMessage.Text = "Uploading Data..";
RdLlbMessage.Update();
Application.DoEvents();
DvBhavCopyFile = new DataView();
DvBhavCopyFile = _DataSet.DefaultViewManager.CreateDataView(DtBhavCopyFile);
DvBhavCopyFile.RowFilter = "isnull(lFileDownloaded,0)=1";
if (DvBhavCopyFile.Count > 0)
{
string StrCsvFileLoation = "";
StrCsvFileLoation = Path.GetTempPath() + @"BhavCopyDataCsvFiles";
if (Directory.Exists(StrCsvFileLoation) == false)
{
Directory.CreateDirectory(StrCsvFileLoation);
}
foreach (DataRowView DrvFiles in DvBhavCopyFile)
{
string zipPath = DrvFiles["cFileLoation"].ToString();
string extractPath = StrCsvFileLoation;//+"\\"+ Path.GetFileName(zipPath);
FastZip z = new FastZip
{
CreateEmptyDirectories = true
};
z.ExtractZip(zipPath, extractPath, "");
}
DtBhavCopyData = new DataTable();
RdProgressBar.Minimum = 1;
RdProgressBar.Maximum = DvBhavCopyFile.Count;
foreach (DataRowView DrvFiles in DvBhavCopyFile)
{
RdProgressBar.Value1 = RdProgressBar.Value1 < DvBhavCopyFile.Count ? RdProgressBar.Value1 + 1 : DvBhavCopyFile.Count;
RdProgressBar.Text = ((RdProgressBar.Value1 * 100) / DvBhavCopyFile.Count).ToString() + " %";
RdProgressBar.Update();
RdProgressBar.Refresh();
RdLlbMessage.Text = "Getting < " + DrvFiles["cFileName"].ToString().Replace(".zip", "") + " > File Data..";
RdLlbMessage.Update();
Application.DoEvents();
ConnectCSV(StrCsvFileLoation, DrvFiles["cFileName"].ToString().Replace(".zip", ""));
}
DataColumn DcCol = new DataColumn("dTIMESTAMP", typeof(DateTime));
DtBhavCopyData.Columns.Add(DcCol);
foreach (DataRowView DrvFilesData in DtBhavCopyData.DefaultView)
{
DrvFilesData.BeginEdit();
string[] StrDateFormat;
StrDateFormat = DrvFilesData["cTIMESTAMP"].ToString().Split('-');
DrvFilesData["dTIMESTAMP"] = StrDateFormat[1] + "/" + StrDateFormat[0] + "/" + StrDateFormat[2];
DrvFilesData.EndEdit();
}
RdProgressBar.ResetText();
RdProgressBar.Refresh();
RdProgressBar.Minimum = 0;
RdProgressBar.Maximum = DvBhavCopyFile.Count;
RdProgressBar.Value1 = 0;
DvBhavCopyFile.Sort = " dFileDate DESC";
using (A3DBhavCopyDataContext dbContxt = new A3DBhavCopyDataContext())
{
using (DbContextTransaction transaction = dbContxt.Database.BeginTransaction())
{
try
{
int iFileID = 0;
foreach (DataRowView DrvFiles in DvBhavCopyFile)
{
string StrFileName = DrvFiles["cFileName"].ToString().Replace(".zip", "");
RdLlbMessage.Text = "Reading < " + StrFileName + " > File Data..";
RdLlbMessage.Update();
Application.DoEvents();
var vBhavCopyHead = dbContxt._MBhavCopyHead.Where(f => f.cFileName == StrFileName).Select(BCH => BCH.iFileID).ToList();
if (vBhavCopyHead != null && vBhavCopyHead.Count > 0)
{
RdLlbMessage.Text = "Deleting existing < " + StrFileName + " > File Data..";
RdLlbMessage.Update();
Application.DoEvents();
foreach (var item in vBhavCopyHead)
{
IList <MClsBhavCopyHead> mClsBhavCopyHeads = dbContxt._MBhavCopyHead.Where(bch => bch.iFileID == item).ToList();
if (mClsBhavCopyHeads != null && mClsBhavCopyHeads.Count > 0)
{
dbContxt._MBhavCopyHead.RemoveRange(mClsBhavCopyHeads);
dbContxt.SaveChanges();
//foreach (var vBhavCopyHeads in mClsBhavCopyHeads)
//{
// dbContxt._MBhavCopyHead.Remove(vBhavCopyHeads);
// dbContxt.SaveChanges();
//}
}
IList <MClsBhavCopyDetails> mClsBhavCopyDetails = dbContxt._MClsBhavCopyDetails.Where(bcd => bcd.iFileID == item).ToList();
if (mClsBhavCopyDetails != null && mClsBhavCopyDetails.Count > 0)
{
dbContxt._MClsBhavCopyDetails.RemoveRange(mClsBhavCopyDetails);
dbContxt.SaveChanges();
//foreach (var vBhavCopyDetails in mClsBhavCopyDetails)
//{
// dbContxt._MClsBhavCopyDetails.Remove(vBhavCopyDetails);
// dbContxt.SaveChanges();
//}
}
}
}
/**********************************************************************************************/
iFileID = 0;
MClsBhavCopyHead _mClsBhavCopyHead = new MClsBhavCopyHead();
_mClsBhavCopyHead.cFileName = DrvFiles["cFileName"].ToString().Replace(".zip", "");
_mClsBhavCopyHead.dFileDate = Convert.ToDateTime(DrvFiles["dFileDate"]);
_mClsBhavCopyHead.dFileUploadDate = DateTime.Now;
dbContxt._MBhavCopyHead.Add(_mClsBhavCopyHead);
dbContxt.SaveChanges();
iFileID = _mClsBhavCopyHead.iFileID;
/**********************************************************************************************/
RdLlbMessage.Text = "Getting all data of < " + StrFileName + " > File ..";
RdLlbMessage.Update();
Application.DoEvents();
DtBhavCopyData.DefaultView.RowFilter = "cFileName='" + DrvFiles["cFileName"].ToString().Replace(".zip", "") + "'";
RdLlbMessage.Text = "Saving data of < " + StrFileName + " > File. Total no of record < " + DtBhavCopyData.DefaultView.Count.ToString() + " >";
RdLlbMessage.Update();
Application.DoEvents();
List <MClsBhavCopyDetails> _mClsBhavCopyDetails = new List <MClsBhavCopyDetails>();
_mClsBhavCopyDetails = (from DataRowView DrvFilesData in DtBhavCopyData.DefaultView
select new MClsBhavCopyDetails()
{
iFileID = iFileID,
cSYMBOL = DrvFilesData["cSYMBOL"].ToString().Trim(),
cSERIES = DrvFilesData["cSERIES"].ToString().Trim(),
cOPEN = DrvFilesData["cOPEN"].ToString().Trim(),
cHIGH = DrvFilesData["cHIGH"].ToString().Trim(),
cLOW = DrvFilesData["cLOW"].ToString().Trim(),
cCLOSE = DrvFilesData["cCLOSE"].ToString().Trim(),
cLAST = DrvFilesData["cLAST"].ToString().Trim(),
cPREVCLOSE = DrvFilesData["cPREVCLOSE"].ToString().Trim(),
cTOTTRDQTY = DrvFilesData["cTOTTRDQTY"].ToString().Trim(),
cTOTTRDVAL = DrvFilesData["cTOTTRDVAL"].ToString().Trim(),
cTIMESTAMP = DrvFilesData["cTIMESTAMP"].ToString().Trim(),
cTOTALTRADES = DrvFilesData["cTOTALTRADES"].ToString().Trim(),
cISIN = DrvFilesData["cISIN"].ToString().Trim(),
dTIMESTAMP = Convert.ToDateTime(DrvFilesData["dTIMESTAMP"])
}).ToList();
dbContxt._MClsBhavCopyDetails.AddRange(_mClsBhavCopyDetails);
//foreach (DataRowView DrvFilesData in DtBhavCopyData.DefaultView)
//{
// RdProgressBar.Value1 = RdProgressBar.Value1 < DtBhavCopyData.DefaultView.Count ? RdProgressBar.Value1 + 1 : DtBhavCopyData.DefaultView.Count;
// RdProgressBar.Text = ((RdProgressBar.Value1 * 100) / DtBhavCopyData.DefaultView.Count).ToString() + " %";
// RdProgressBar.Update();
// RdProgressBar.Refresh();
// Application.DoEvents();
// MClsBhavCopyDetails _mClsBhavCopyDetails = new MClsBhavCopyDetails();
// _mClsBhavCopyDetails.iFileID = iFileID;
// _mClsBhavCopyDetails.cSYMBOL = DrvFilesData["cSYMBOL"].ToString().Trim();
// _mClsBhavCopyDetails.cSERIES = DrvFilesData["cSERIES"].ToString().Trim();
// _mClsBhavCopyDetails.cOPEN = DrvFilesData["cSYMBOL"].ToString().Trim();
// _mClsBhavCopyDetails.cHIGH = DrvFilesData["cHIGH"].ToString().Trim();
// _mClsBhavCopyDetails.cLOW = DrvFilesData["cLOW"].ToString().Trim();
// _mClsBhavCopyDetails.cCLOSE = DrvFilesData["cCLOSE"].ToString().Trim();
// _mClsBhavCopyDetails.cLAST = DrvFilesData["cLAST"].ToString().Trim();
// _mClsBhavCopyDetails.cPREVCLOSE = DrvFilesData["cPREVCLOSE"].ToString().Trim();
// _mClsBhavCopyDetails.cTOTTRDQTY = DrvFilesData["cTOTTRDQTY"].ToString().Trim();
// _mClsBhavCopyDetails.cTOTTRDVAL = DrvFilesData["cTOTTRDVAL"].ToString().Trim();
// _mClsBhavCopyDetails.cTIMESTAMP = DrvFilesData["cTIMESTAMP"].ToString().Trim();
// _mClsBhavCopyDetails.cTOTALTRADES = DrvFilesData["cTOTALTRADES"].ToString().Trim();
// _mClsBhavCopyDetails.cISIN = DrvFilesData["cISIN"].ToString().Trim();
// _mClsBhavCopyDetails.dTIMESTAMP = Convert.ToDateTime(DrvFilesData["dTIMESTAMP"]);
// dbContxt._MClsBhavCopyDetails.Add(_mClsBhavCopyDetails);
//}
DtBhavCopyData.DefaultView.RowFilter = "";
RdProgressBar.Value1 = RdProgressBar.Value1 < DvBhavCopyFile.Count ? RdProgressBar.Value1 + 1 : DvBhavCopyFile.Count;
RdProgressBar.Text = ((RdProgressBar.Value1 * 100) / DvBhavCopyFile.Count).ToString() + " %";
RdProgressBar.Update();
RdProgressBar.Refresh();
Application.DoEvents();
}
dbContxt.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
ClsMessage._IClsMessage.ProjectExceptionMessage(ex);
}
}
}
DtBhavCopyData.AcceptChanges();
RdGrdBhavCopyData.DataSource = DtBhavCopyData;
}
}
catch (Exception ex)
{
throw ex;
}
}
private void PopulateData()
{
try
{
DtBhavCopyData = new DataTable();
DtBhavCopySqlData = new DataTable();
RdGrdReportResult.DataSource = null;
DataColumn DcColBhavCopyData = new DataColumn("cSYMBOL", typeof(string));
DcColBhavCopyData.DefaultValue = "";
DcColBhavCopyData.Caption = "Company Name(Symbol)";
DtBhavCopyData.Columns.Add(DcColBhavCopyData);
DcColBhavCopyData = new DataColumn("cSERIES", typeof(string));
DcColBhavCopyData.DefaultValue = "";
DcColBhavCopyData.Caption = "Company Series(Series)";
DtBhavCopyData.Columns.Add(DcColBhavCopyData);
DcColBhavCopyData = new DataColumn("cSummary", typeof(double));
DcColBhavCopyData.DefaultValue = 0;
DcColBhavCopyData.Caption = "Total Summary";
DtBhavCopyData.Columns.Add(DcColBhavCopyData);
string StrSqlQuery = "";
string StrSqlQueryFilter = "Where ";
RdProgressBar.Minimum = 1;
RdProgressBar.Maximum = DvBhavCopyCompany.Count;
RdProgressBar.Value1 = 1;
RdProgressBar.Text = "Filling Company Details";
DataRow _DataRowBhavCopy;
for (int i = 0; i < DvBhavCopyCompany.Count; i++)
{
_DataRowBhavCopy = DtBhavCopyData.NewRow();
_DataRowBhavCopy["cSYMBOL"] = DvBhavCopyCompany[i]["cSYMBOL"];
_DataRowBhavCopy["cSERIES"] = DvBhavCopyCompany[i]["cSERIES"];
DtBhavCopyData.Rows.Add(_DataRowBhavCopy);
RdProgressBar.Value1 = RdProgressBar.Value1 < DvBhavCopyCompany.Count ? RdProgressBar.Value1 + 1 : DvBhavCopyCompany.Count;
RdProgressBar.Text = " Filling Company Details - " + ((RdProgressBar.Value1 * 100) / DvBhavCopyCompany.Count).ToString() + " %";
RdProgressBar.Update();
RdProgressBar.Refresh();
Application.DoEvents();
}
RdProgressBar.Value1 = 1;
RdProgressBar.Text = "Filling Company Completed";
string StrCompnay = "";
StrCompnay = string.Join("','", DvBhavCopyCompany.ToTable().AsEnumerable().Select(x => x.Field <string>("cSYMBOL")).ToArray());//+ "_" + x.Field<string>("cSERIES")
StrSqlQueryFilter = StrSqlQueryFilter + " Convert(DateTime,dTIMESTAMP,101) >=Convert(DateTime,'" + _DtpFromDate.ToString("yyyy/MM/dd") + "',101) AND Convert(DateTime,dTIMESTAMP,101) <=Convert(DateTime,'" + _DtpToDate.ToString("yyyy/MM/dd") + "',101)";
StrSqlQuery = @"select * from [A3DBhavCopyData].[dbo].[BhavCopyDetails]";
StrSqlQuery = StrSqlQuery + Environment.NewLine + StrSqlQueryFilter + Environment.NewLine + " AND cSYMBOL IN ('" + StrCompnay + "')";//+ '_' + cSERIES
using (var DbContext = new A3DBhavCopyDataContext())
{
var vAtmUpTime = DbContext.Database.SqlQuery <MClsBhavCopyDetails>(StrSqlQuery).ToList();
DtBhavCopySqlData = ClsUtility._IClsUtility.NewTable(string.Concat("BhavCopyData"), vAtmUpTime);
}
double _IDaysInMonth = (_DtpToDate - _DtpFromDate).TotalDays;
ColumnGroupsViewDefinition view = new ColumnGroupsViewDefinition();
GridViewColumnGroup gridViewColumnGroup;
gridViewColumnGroup = new GridViewColumnGroup("Company Details");
gridViewColumnGroup.Rows.Add(new GridViewColumnGroupRow());
gridViewColumnGroup.Rows[0].ColumnNames.Add("cSYMBOL");
gridViewColumnGroup.Rows[0].ColumnNames.Add("cSERIES");
gridViewColumnGroup.Rows[0].ColumnNames.Add("cSummary");
gridViewColumnGroup.IsPinned = true;
gridViewColumnGroup.PinPosition = PinnedColumnPosition.Left;
view.ColumnGroups.Add(gridViewColumnGroup);
for (int i = 0; i <= _IDaysInMonth; i++)
{
DateTime _DtMonthDate = _DtpFromDate.AddDays(Convert.ToDouble(i));
if (_DtMonthDate.DayOfWeek.ToString() == "Sunday" || _DtMonthDate.DayOfWeek.ToString() == "Saturday")
{
continue;
}
DcColBhavCopyData = new DataColumn("cPREVCLOSE_" + _DtMonthDate.ToString("dd-MMM-yyyy"), typeof(double))
{
DefaultValue = 0,
Caption = "Previous Day Closing Value"
};
DtBhavCopyData.Columns.Add(DcColBhavCopyData);
DcColBhavCopyData = new DataColumn("cCLOSE_" + _DtMonthDate.ToString("dd-MMM-yyyy"), typeof(double))
{
DefaultValue = 0,
Caption = "Today Closing Value"
};
DtBhavCopyData.Columns.Add(DcColBhavCopyData);
if (EnuReportType == enumReportType.SortByQty)
{
DcColBhavCopyData = new DataColumn("cTOTTRDQTY_" + _DtMonthDate.ToString("dd-MMM-yyyy"), typeof(double))
{
DefaultValue = 0,
Caption = "Total Trading Quantity"
};
DtBhavCopyData.Columns.Add(DcColBhavCopyData);
}
else
{
DcColBhavCopyData = new DataColumn("cTOTTRDVAL_" + _DtMonthDate.ToString("dd-MMM-yyyy"), typeof(double))
{
DefaultValue = 0,
Caption = "Total Trading Value"
};
DtBhavCopyData.Columns.Add(DcColBhavCopyData);
}
DcColBhavCopyData = new DataColumn("cPriceChange_" + _DtMonthDate.ToString("dd-MMM-yyyy"), typeof(double))
{
DefaultValue = 0,
Caption = "Price Change"
};
DtBhavCopyData.Columns.Add(DcColBhavCopyData);
gridViewColumnGroup = new GridViewColumnGroup(_DtMonthDate.ToString("dd-MMM-yyyy"));
gridViewColumnGroup.Rows.Add(new GridViewColumnGroupRow());
gridViewColumnGroup.Rows[0].ColumnNames.Add("cPREVCLOSE_" + _DtMonthDate.ToString("dd-MMM-yyyy"));
gridViewColumnGroup.Rows[0].ColumnNames.Add("cCLOSE_" + _DtMonthDate.ToString("dd-MMM-yyyy"));
if (EnuReportType == enumReportType.SortByQty)
{
gridViewColumnGroup.Rows[0].ColumnNames.Add("cTOTTRDQTY_" + _DtMonthDate.ToString("dd-MMM-yyyy"));
}
else
{
gridViewColumnGroup.Rows[0].ColumnNames.Add("cTOTTRDVAL_" + _DtMonthDate.ToString("dd-MMM-yyyy"));
}
gridViewColumnGroup.Rows[0].ColumnNames.Add("cPriceChange_" + _DtMonthDate.ToString("dd-MMM-yyyy"));
view.ColumnGroups.Add(gridViewColumnGroup);
}
RdGrdReportResult.ViewDefinition = view;
RdProgressBar.Minimum = 1;
RdProgressBar.Maximum = DtBhavCopyData.DefaultView.Count;
RdProgressBar.Value1 = 1;
RdProgressBar.Text = "Filling Bhav-Copy Data";
double dblSummary = 0;
foreach (DataRowView DrvCompany in DtBhavCopyData.DefaultView)
{
dblSummary = 0;
RdProgressBar.Text = " Filling < " + DrvCompany["cSYMBOL"] + " > Company Data - " + ((RdProgressBar.Value1 * 100) / DtBhavCopyData.DefaultView.Count).ToString() + " %";
RdProgressBar.Update();
RdProgressBar.Refresh();
Application.DoEvents();
DtBhavCopySqlData.DefaultView.RowFilter = "cSYMBOL='" + DrvCompany["cSYMBOL"] + "' AND cSERIES='" + DrvCompany["cSERIES"] + "' ";
foreach (DataRowView DrvBhavCopySqlData in DtBhavCopySqlData.DefaultView)
{
//DrvCompany["cPREVCLOSE"] = DrvBhavCopySqlData["cPREVCLOSE"];
//DrvCompany["cCLOSE"] = DrvBhavCopySqlData["cCLOSE"];
//DrvCompany["cTOTTRDVAL"] = DrvBhavCopySqlData["cTOTTRDVAL"];
if (DtBhavCopyData.Columns.Contains("cPREVCLOSE_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")))
{
DrvCompany["cPREVCLOSE_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")] = DrvBhavCopySqlData["cPREVCLOSE"];
}
if (DtBhavCopyData.Columns.Contains("cCLOSE_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")))
{
DrvCompany["cCLOSE_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")] = DrvBhavCopySqlData["cCLOSE"];
}
if (EnuReportType == enumReportType.SortByQty)
{
if (DtBhavCopyData.Columns.Contains("cTOTTRDQTY_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")))
{
DrvCompany["cTOTTRDQTY_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")] = DrvBhavCopySqlData["cTOTTRDQTY"];
}
}
else
{
if (DtBhavCopyData.Columns.Contains("cTOTTRDVAL_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")))
{
DrvCompany["cTOTTRDVAL_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")] = DrvBhavCopySqlData["cTOTTRDVAL"];
}
}
if (DtBhavCopyData.Columns.Contains("cPriceChange_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")))
{
DrvCompany["cPriceChange_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")] = Math.Round(Convert.ToDouble(DrvBhavCopySqlData["cCLOSE"]) - Convert.ToDouble(DrvBhavCopySqlData["cPREVCLOSE"]), 2);
dblSummary = dblSummary + Convert.ToDouble(DrvCompany["cPriceChange_" + Convert.ToDateTime(DrvBhavCopySqlData["dTIMESTAMP"]).ToString("dd-MMM-yyyy")]);
}
}
DrvCompany["cSummary"] = dblSummary;
DtBhavCopySqlData.DefaultView.RowFilter = "";
RdProgressBar.Value1 = RdProgressBar.Value1 < DvBhavCopyCompany.Count ? RdProgressBar.Value1 + 1 : DvBhavCopyCompany.Count;
}
RdGrdReportResult.DataSource = DtBhavCopyData;
ConditionalFormattingObject _ConditionalFormattingObject = new ConditionalFormattingObject("MyCondition", ConditionTypes.Less, "0", "", false);
//obj.CellBackColor = Color.SkyBlue;
_ConditionalFormattingObject.CellForeColor = Color.Red;
//obj.TextAlignment = ContentAlignment.MiddleRight;
RdGrdReportResult.Columns["cSYMBOL"].PinPosition = PinnedColumnPosition.Left;
RdGrdReportResult.Columns["cSYMBOL"].IsPinned = true;
RdGrdReportResult.Columns["cSERIES"].PinPosition = PinnedColumnPosition.Left;
RdGrdReportResult.Columns["cSERIES"].IsPinned = true;
RdGrdReportResult.Columns["cSummary"].PinPosition = PinnedColumnPosition.Left;
RdGrdReportResult.Columns["cSummary"].IsPinned = true;
RdGrdReportResult.Columns["cSummary"].ConditionalFormattingObjectList.Add(_ConditionalFormattingObject);
foreach (var gridViewDataColumn in RdGrdReportResult.Columns)
{
if (gridViewDataColumn.Name.Contains("cPriceChange_"))
{
gridViewDataColumn.ConditionalFormattingObjectList.Add(_ConditionalFormattingObject);
}
}
RdProgressBar.Value1 = 1;
RdProgressBar.Text = "Done";
ClsMessage._IClsMessage.showMessage("Done!!");
}
catch (Exception ex)
{
ClsMessage._IClsMessage.ProjectExceptionMessage(ex);
}
}