private void ApplyFilter(int start) { if (DisplayTotal == 0) { return; } var max = start + PageSize; var rowList = new List <DataRow>(); for (int i = start; i < max; i++) { if (i < _cachedMerchandises.Rows.Count) { rowList.Add(_cachedMerchandises.Rows[i]); } } if (rowList.Count == 0) { PageNumber--; ApplyFilter(start - PageSize); } else { Merchandise = rowList.CopyToDataTable(); } }
private void button5_Click(object sender, EventArgs e) { DataTable temdt = (DataTable)dataGridView1.DataSource; List <DataRow> ccc = new List <DataRow>(); int count = temdt.Rows.Count; for (int i = 0; i < count; i++) { string filepath = temdt.Rows[i]["地址"].ToString(); if (!File.Exists(filepath)) { ccc.Add(temdt.Rows[i]); DbHelperSQL.ExecuteSql("delete from paperwork where 文件名='" + temdt.Rows[i][1].ToString() + "';"); } } if (ccc.Count() > 0) { get_datatable(); rf_filter(); excelMethod.SaveDataTableToExcel(ccc.CopyToDataTable()); } MessageBox.Show("清理完毕"); }
protected void bt1ExportExcle_OnClick(object sender, ImageClickEventArgs e) { //var daneshId = Convert.ToInt32(Session["DaneshId"]); //var allAcceptedRequest = _requestHandler.GetAllAccepetedDefenceRequests(daneshId ); var allAcceptedRequest = _requestHandler.GetStudentDefenceListForPazhoohesh_Report(1, drpTerms.SelectedValue?.ToString()); //i=execl 2=text report var list = allAcceptedRequest; var lstRows = new List <DataRow>(); switch (Convert.ToInt32(Session["drpDefenceType"].ToString())) { case 1: //دفاع بررسی نشده lstRows = list.AsEnumerable().Where(w => string.IsNullOrEmpty(w.Field <bool?>("DefenceHasDone").ToString())).ToList(); break; case 2: //دفاع برگزار نشده lstRows = list.AsEnumerable().Where(w => !string.IsNullOrEmpty(w.Field <bool?>("DefenceHasDone").ToString()) && w.Field <bool?>("DefenceHasDone") == false).ToList(); break; case 3: //دفاع برگزار شده lstRows = list.AsEnumerable().Where(w => !string.IsNullOrEmpty(w.Field <bool?>("DefenceHasDone").ToString()) && w.Field <bool?>("DefenceHasDone") == true && (w.Field <bool>("ChkPaymentDavar1") == false || w.Field <bool>("ChkPaymentDavar2") == false)) .ToList(); break; case 4: //همه دفاع ها lstRows = allAcceptedRequest.AsEnumerable().ToList(); break; default: break; } if (lstRows.Count() > 0) { var dt = lstRows.CopyToDataTable(); try { var pck = new OfficeOpenXml.ExcelPackage(); var ws = pck.Workbook.Worksheets.Add("ProfInfoList"); ws.Cells["A1"].LoadFromDataTable(dt, true); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=DefenceList.xlsx"); Response.BinaryWrite(pck.GetAsByteArray()); } catch (Exception ex) { //log error //throw exx } Response.End(); } }
public static DataTable GetSearchDataTable(string searchText, DataTable dtSource) { DataTable dt = new DataTable(); searchText = DataConvert.ToString(searchText); if (searchText != "") { DataRow[] drs = dtSource.Select(" name like '%" + searchText.ToUpper() + "%'"); List <DataRow> resultList = new List <DataRow>(); resultList.AddRange(drs); var parentIds = drs.Select(m => m.Field <string>("parentId")).Distinct().ToList(); if (parentIds.Count > 0) { var parentList = DistinctParent(parentIds, dtSource); resultList.AddRange(parentList); resultList = resultList.Distinct().ToList(); } if (resultList.Count > 0) { dt = resultList.CopyToDataTable(); } } else { return(dtSource); } return(dt); }
/// <summary> /// O método salva os dados do planejamento anual. /// </summary> /// <param name="ltPlanejamento">Lista de dados do planejamento anual.</param> /// <param name="ltHabilidade">Lista de orientações curriculares da turma.</param> /// <param name="ltDiagnostico">Lista de orientações anteriores.</param> /// <param name="banco"></param> /// <returns></returns> public static bool SalvaPlanejamentoTurmaDisciplina ( List <CLS_TurmaDisciplinaPlanejamento> ltPlanejamento, List <CLS_PlanejamentoOrientacaoCurricular> ltHabilidade, List <CLS_PlanejamentoOrientacaoCurricularDiagnostico> ltDiagnostico, TalkDBTransaction banco, bool sincronizacaoDiarioClasse = false ) { bool retorno = true; retorno &= sincronizacaoDiarioClasse ? ltPlanejamento.Aggregate(true, (salvou, entity) => salvou & CLS_TurmaDisciplinaPlanejamentoBO.SaveSincronizacaoDiarioClasse(entity, banco)) : ltPlanejamento.Aggregate(true, (salvou, entity) => salvou & CLS_TurmaDisciplinaPlanejamentoBO.Save(entity, banco)); #region Verifica se algum plano de aula nao pode ser desplanejado //Seleciona as habilidades que seram salvas como nao planejadas List <CLS_PlanejamentoOrientacaoCurricular> ltHabilidadeNaoPlanejadas = ltHabilidade.Where(p => !p.poc_planejado).ToList(); //Carrega as habilidades que estao ligadas a uma aula List <sOrientacoesCurricularesPorDisciplinaBimestreComAulasPlanejadas> listOrientacoesComAula = new List <sOrientacoesCurricularesPorDisciplinaBimestreComAulasPlanejadas>(); ltHabilidadeNaoPlanejadas.Select(p => p.tud_id).Distinct().ToList().ForEach(tud_id => { listOrientacoesComAula.AddRange(CLS_TurmaAulaOrientacaoCurricularBO.AulasPlanejadasSelecionaPorDisciplina(tud_id)); }); //Verifica se tem alguma aula nao planejada para aquele bimestre que esteja ligada a uma aula. var lAux = ( from aula in listOrientacoesComAula join habilidade in ltHabilidadeNaoPlanejadas on new { aula.tud_id, aula.tpc_id, aula.ocr_id } equals new { habilidade.tud_id, habilidade.tpc_id, habilidade.ocr_id } select aula ).Distinct().ToList(); if (lAux.Any()) { throw new ValidationException("Não é possível desplanejar uma habilidade que já tenha sido planejada para uma aula."); } #endregion // Salva os dados na tabela CLS_PlanejamentoOrientacaoCurricular. DataTable dtPlanejamentoOrientacaoCurricular = CLS_PlanejamentoOrientacaoCurricular.TipoTabela_PlanejamentoOrientacaoCurricular(); if (ltHabilidade.Any()) { List <DataRow> ltDrPlanejamentoOrientacaoCurricular = (from CLS_PlanejamentoOrientacaoCurricular planejamentoOrientacaoCurricular in ltHabilidade select PlanejamentoOrientacaoCurricularToDataRow(planejamentoOrientacaoCurricular, dtPlanejamentoOrientacaoCurricular.NewRow())).ToList(); dtPlanejamentoOrientacaoCurricular = ltDrPlanejamentoOrientacaoCurricular.CopyToDataTable(); retorno &= SalvarEmLote(dtPlanejamentoOrientacaoCurricular, banco); } // Salva os dados na tabela CLS_PlanejamentoOrientacaoCurricularDiagnostico. retorno &= CLS_PlanejamentoOrientacaoCurricularDiagnosticoBO.SalvarEmLote(ltDiagnostico, banco); return(retorno); }
private void HienDanhMucKhachHang() { try { List <string> lstDieuKien = new List <string>(); KhachHangProcess khProcess = new KhachHangProcess(); machinhanh = lstSourceChiNhanh.ElementAt(cmbChiNhanh.SelectedIndex).KeywordStrings.First(); //lstDieuKien.Add(ClientInformation.MaDonViGiaoDich); lstDieuKien.Add(machinhanh); lstDieuKien.Add("NULL"); lstDieuKien.Add("NULL"); lstPopup = new List <DataRow>(); PopupProcess popupProcess = new PopupProcess(); popupProcess.getPopupInformation("POPUP_DS_KHACHHANG_BAOCAO", lstDieuKien); SimplePopupResponse simplePopupResponse = ClientInformation.SimplePopup; ucPopup popup = new ucPopup(true, simplePopupResponse, false); popup.DuLieuTraVe = new ucPopup.LayDuLieu(LayDuLieuTuPopup); Window win = new Window(); win.Content = popup; win.Title = LLanguage.SearchResourceByKey(simplePopupResponse.PopupTitle); win.WindowStartupLocation = WindowStartupLocation.CenterScreen; win.ShowDialog(); if (lstPopup.Count > 0) { DataTable dt = new DataTable(); dt = lstPopup.CopyToDataTable(); if (!LObject.IsNullOrEmpty(dt) && dt.Rows.Count > 0) { if (dt.Columns.Contains("STT")) { dt.Columns.Remove("STT"); } if (LObject.IsNullOrEmpty(dtKhachHang)) { dtKhachHang = new DataTable(); } else { dt.Merge(dtKhachHang); } var result = dt.AsEnumerable().Select(x => x).Distinct(System.Data.DataRowComparer.Default).ToList(); dtKhachHang = result.CopyToDataTable(); grKhachHang.ItemsSource = null; grKhachHang.ItemsSource = dtKhachHang; } } } catch (Exception ex) { LLogging.WriteLog(ex.TargetSite.Name, LLogging.LogType.ERR, ex); } finally { Mouse.OverrideCursor = Cursors.Arrow; } }
/// <summary> /// Copies a <see cref="List{T}"/> of <see cref="DataRow"/>s to a <see cref="DataTable"/> /// but returns an empty table matching the schema of the <paramref name="sourceTable"/> /// if the list is empty. /// </summary> /// <param name="rows">The <see cref="DataRow"/> list.</param> /// <param name="sourceTable">The source <see cref="DataTable"/>.</param> /// <returns></returns> public static DataTable CopyToDataTable_Safe(this List <DataRow> rows, DataTable sourceTable) { if (rows.Any()) { return(rows.CopyToDataTable()); } return(sourceTable.Clone()); }
/// <summary> /// Metodo per il riempimento del DataGridView2 per i PANEL Type Identifier /// </summary> /// public void SetDataGridViewPTI() { // Ottiene la lista dei Panel Type identifier _listPTI = m_Handler.ListPTI; // La trasforma in una lista anonima var stringslist = _listPTI .Select(arr => new { PanelTypeIdentifier = arr[0], Qty = arr[1], Colors = arr[2] }).ToArray(); // Crea un DataTable (utile per fare poi l'ordinamento per colonne) DataTable dataTable2 = new DataTable(); dataTable2.Columns.Add(new DataColumn { ColumnName = "PanelTypeIdentifier", DataType = typeof(String) }); dataTable2.Columns.Add(new DataColumn { ColumnName = "Qty", DataType = typeof(String) }); dataTable2.Columns.Add(new DataColumn { ColumnName = "Colors", DataType = typeof(String) }); List <DataRow> list = new List <DataRow>(); foreach (var x in stringslist) { var row = dataTable2.NewRow(); row.SetField <string>("PanelTypeIdentifier", x.PanelTypeIdentifier); row.SetField <string>("Qty", x.Qty); row.SetField <string>("Colors", x.Colors); list.Add(row); } dataTable2 = list.CopyToDataTable(); // Riempie il DataGridView dataGridView2.DataSource = dataTable2; // Colora il background della colonna Colors con il colore corrispondente for (int i = 0; i < (dataGridView2.RowCount); i++) { dataGridView2.Rows[i].Cells[2].Style.BackColor = Color.FromName(dataGridView2.Rows[i].Cells[2].Value.ToString()); } }
internal DataTable GetDataForSelectedCompanies(Query posted) { var list = new List <DataRow>(); string _connection = "Server={3};Database={2};User Id={0};Password={1};"; try { foreach (Company item in posted.SelectedCompanies) { dt = new DataTable(); _dbConnectionString = string.Format(_connection, posted.UserName, posted.Password, item.CompanyDatabase, item.SQLServer); con = new SqlConnection { ConnectionString = _dbConnectionString }; using (con = new SqlConnection { ConnectionString = _dbConnectionString }) { using (sqlcmd = new SqlCommand { CommandText = posted.QueryText, Connection = con, CommandType = CommandType.Text }) { using (da = new SqlDataAdapter { SelectCommand = sqlcmd }) { da.Fill(dt); } } } list.AddRange(dt.AsEnumerable().ToList()); } return(list.CopyToDataTable()); } catch (Exception e) { return(list.CopyToDataTable()); } }
public static DataTable InOrder(DataTable data, double trainPercent) { List <DataRow> drows = new List <DataRow>(); int count = data.Rows.Count; int trainCount = count * (int)trainPercent / 100; for (int i = 0; i < trainCount; i++) { drows.Add(data.Rows[i]); TrainIndex.Add(i); } dtTrain = drows.CopyToDataTable(); return(dtTrain); }
public DataTable NotNormalset(DataTable dt, List <int> list) { List <DataRow> drows = new List <DataRow>(); for (int i = 0; i < list.Count; i++) { drows.Add(dt.Rows[list[i]]); } DataTable dtt = new DataTable(); if (drows.Count != 0) { dtt = drows.CopyToDataTable(); } return(dtt); }
static void Main(string[] args) { DataTable dt = new DataTable(); dt.Columns.Add("AdId", typeof(int)); dt.Columns.Add("AdName", typeof(string)); dt.Columns.Add("AdUrl", typeof(string)); dt.Columns.Add("Credits", typeof(int)); dt.Rows.Add(new object[] { 1, "Ad1", "abc.com", 10 }); dt.Rows.Add(new object[] { 2, "Ad2", "def.com", 40 }); dt.Rows.Add(new object[] { 3, "Ad3", "fgi.com", 30 }); dt.Rows.Add(new object[] { 4, "Ad4", "xyz.com", 40 }); Random rand = new Random(); List <DataRow> randomRows = dt.AsEnumerable().Select(x => new { row = x, rand = rand.Next() }).OrderBy(x => x.rand).Select(x => x.row).ToList(); DataTable dt2 = randomRows.CopyToDataTable(); }
public static DataTable InOrder(DataTable data, double testPercent) { List <DataRow> drows = new List <DataRow>(); int count = data.Rows.Count; int trainCount = count * (int)(100 - testPercent) / 100; int testCount = count - trainCount; for (int i = 0; i < testCount; i++) { drows.Add(data.Rows[trainCount + i]); TestIndex.Add(trainCount + i); } if (drows.Count != 0) { dtTest = drows.CopyToDataTable(); } return(dtTest); }
/// <summary> /// Salva os dados do diagnóstico em lote. /// </summary> /// <param name="ltDiagnostico">Lista de dados do diagnóstico.</param> /// <param name="banco">Transação.</param> /// <returns>True em caso de sucesso.</returns> public static bool SalvarEmLote(List <CLS_PlanejamentoOrientacaoCurricularDiagnostico> ltDiagnostico, TalkDBTransaction banco = null) { DataTable dtPlanejamentoOrientacaoCurricularDiagnostico = CLS_PlanejamentoOrientacaoCurricularDiagnostico.TipoTabela_PlanejamentoOrientacaoCurricularDiagnostico(); if (ltDiagnostico.Any()) { List <DataRow> ltDrPlanejamentoOrientacaoCurricularDiagnostico = (from CLS_PlanejamentoOrientacaoCurricularDiagnostico planejamentoOrientacaoCurricularDiagnostico in ltDiagnostico select PlanejamentoOrientacaoCurricularToDataRow(planejamentoOrientacaoCurricularDiagnostico, dtPlanejamentoOrientacaoCurricularDiagnostico.NewRow())).ToList(); dtPlanejamentoOrientacaoCurricularDiagnostico = ltDrPlanejamentoOrientacaoCurricularDiagnostico.CopyToDataTable(); return(banco == null ? new CLS_PlanejamentoOrientacaoCurricularDiagnosticoDAO().SalvarEmLote(dtPlanejamentoOrientacaoCurricularDiagnostico) : new CLS_PlanejamentoOrientacaoCurricularDiagnosticoDAO { _Banco = banco }.SalvarEmLote(dtPlanejamentoOrientacaoCurricularDiagnostico)); } return(true); }
public static DataTable Random(DataTable data, double trainPercent) { List <DataRow> drows = new List <DataRow>(); int count = data.Rows.Count; int trainCount = count * (int)trainPercent / 100; Random r = new Random(); for (int i = 0; i < trainCount; i++) { int k = r.Next(0, count); if (TrainIndex.Contains(k)) { i--; continue; } TrainIndex.Add(k); drows.Add(data.Rows[k]); } dtTrain = drows.CopyToDataTable(); return(dtTrain); }
public void SearchResult(string SearchParameter) { if (lstData != null) { if (SearchParameter != "") { if (lstData != null) { List <DataRow> dt2 = lstData.FindAll(a => a.ItemArray[0].ToString() == SearchParameter || a.ItemArray[1].ToString().ToLower() == SearchParameter.ToLower()); GridView1.DataSource = dt2.CopyToDataTable(); GridView1.DataBind(); } } else { GridView1.DataSource = lstData.CopyToDataTable(); GridView1.DataBind(); } } }
public DataTable GetProductsWithProductId(List <int> ProductID) { DataTable dt = new DataTable(); if (ProductID.Count > 0) { List <DataRow> lstRow = new List <DataRow>(); DataTable dt1 = new DataTable(); foreach (int i in ProductID) { SqlParameter[] parameters = new SqlParameter[1]; parameters[0] = DataAccessLayer.DataAccess.AddParameter("@ProductID", i, System.Data.SqlDbType.Int, 20); dt1 = DataAccessLayer.DataAccess.ExecuteDTByProcedure("SP_GetCartProduct", parameters); DataRow dr; dr = dt1.Rows[0]; lstRow.Add(dr); } dt = lstRow.CopyToDataTable(); } return(dt); }
private void DisplayDetailForRow(int rowIndex) { var id = _table.Rows[rowIndex][_keyIndex].ToString(); var check = _comparerResults.FirstOrDefault(o => o.Key.Equals(id)); var list = new List <DataRow>(); list.Add(_table.Rows[rowIndex]); if (check != null) { if (check.Duplicates.Any()) { list.AddRange(check.Duplicates); } dataGridView2.DataSource = list.CopyToDataTable(); foreach (DataGridViewColumn dc in dataGridView2.Columns) { if (dc.ValueType == typeof(DateTime)) { dc.DefaultCellStyle.Format = "MM/dd/yyyy HH:mm:ss"; } } } }
public static DataTable Random(DataTable data, double testPercent) { List <DataRow> drows = new List <DataRow>(); int count = data.Rows.Count; int trainCount = count * (int)(100 - testPercent) / 100; int testCount = count - trainCount; Random r = new Random(); for (int i = 0; i < testCount; i++) { int k = r.Next(0, count); if (Trainset.TrainIndex.Contains(k)) { i--; continue; } drows.Add(data.Rows[k]); TestIndex.Add(k); } if (drows.Count != 0) { dtTest = drows.CopyToDataTable(); } return(dtTest); }
private void PageExport() { SaveFileDialog sfd = MakeSaveFileDialog(); if (sfd.ShowDialog() == true) { DataTable dt = null; if (Filtering_row != null) { List <DataRow> ItemsSource = new List <DataRow>(); int start_idx = idx_page * cnt_page; for (int i = start_idx; i < start_idx + cnt_page && i < Filtering_row.Length; i++) { ItemsSource.Add(Filtering_row[i]); } if (ItemsSource.Count > 0) { dt = ItemsSource.CopyToDataTable(); } } Export(sfd.FileName, dt); } }
public ActionResult Result(int?page, int?totalPage) { if (Session["tempTable"] != null) { DataTable dt = (DataTable)Session["tempTable"]; IEnumerable <DataRow> enumerableDt = new List <DataRow>(); if (dt != null) { //DataSet 做分頁動作 if (dt.Rows.Count > 0) { enumerableDt = dt.AsEnumerable() .Skip(((page ?? 1) - 1) * pageRow) .Take(pageRow); } if (totalPage == null) { totalPage = (dt.Rows.Count % pageRow == 0) ? (int)(dt.Rows.Count / pageRow) : (int)(dt.Rows.Count / pageRow) + 1; //總頁數 } ViewBag.TotalPage = totalPage; ViewBag.TotalRow = dt.Rows.Count; ViewBag.NowPage = page ?? 1; // 現在的頁數 null:為第一頁 } DataTable tableNew = dt; if (enumerableDt.Any()) // 先確認有DataRow 否則會Exception { tableNew = enumerableDt.CopyToDataTable(); } return(View(tableNew)); } return(View()); }
/// <summary> /// Metodo di restituisce i valori del DataGrid /// </summary> /// public void FillDataGrid() { // Riempie il DataGridView List <string[]> lista = m_Handler.ElementList; var stringslist = lista .Select(arr => new { Number = arr[0], Id = arr[1], Instance = arr[2], Category = arr[3], Type = arr[4], Family = arr[5], UnitTypeIdentifier = arr[6], PanelTypeIdentifier = arr[7] }).ToArray(); // Crea un DataTable (utile per fare poi l'ordinamento per colonne) DataTable dataTable = new DataTable(); dataTable.Columns.Add(new DataColumn { ColumnName = "Number", DataType = typeof(String) }); dataTable.Columns.Add(new DataColumn { ColumnName = "Id", DataType = typeof(String) }); dataTable.Columns.Add(new DataColumn { ColumnName = "Instance", DataType = typeof(String) }); dataTable.Columns.Add(new DataColumn { ColumnName = "Category", DataType = typeof(String) }); dataTable.Columns.Add(new DataColumn { ColumnName = "Type", DataType = typeof(String) }); dataTable.Columns.Add(new DataColumn { ColumnName = "Family", DataType = typeof(String) }); dataTable.Columns.Add(new DataColumn { ColumnName = "UnitTypeIdentifier", DataType = typeof(String) }); dataTable.Columns.Add(new DataColumn { ColumnName = "PanelTypeIdentifier", DataType = typeof(String) }); List <DataRow> list = new List <DataRow>(); foreach (var x in stringslist) { var row = dataTable.NewRow(); row.SetField <string>("Number", x.Number); row.SetField <string>("Id", x.Id); row.SetField <string>("Instance", x.Instance); row.SetField <string>("Category", x.Category); row.SetField <string>("Type", x.Type); row.SetField <string>("Family", x.Family); row.SetField <string>("UnitTypeIdentifier", x.UnitTypeIdentifier); row.SetField <string>("PanelTypeIdentifier", x.PanelTypeIdentifier); list.Add(row); } dataTable = list.CopyToDataTable(); // Riempie il DataGRidView dataGridView1.DataSource = dataTable; elementTextBox.Text = Convert.ToString(m_Handler.Count[0]); instanceTextBox.Text = Convert.ToString(m_Handler.Count[1]); categorieTextBox.Text = Convert.ToString(m_Handler.Count[2]); typeTextBox.Text = Convert.ToString(m_Handler.Count[3]); famiglieTextBox.Text = Convert.ToString(m_Handler.Count[4]); uiTextBox.Text = Convert.ToString(m_Handler.Count[5]); ptiTextBox.Text = Convert.ToString(m_Handler.Count[6]); }
public void CreateTrainTest() { if (edtTrainPercent.Text == "" && edtTestPercent.Text == "") { MessageBox.Show("Please, enter percentage of train and test set.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (edtInterval.Checked && edtIncrementx.Text == "") { MessageBox.Show("Please, enter incremental value of x.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (edtInterval.Checked && edtIncrementy.Text == "") { MessageBox.Show("Please, enter incremental value of y.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (edtInterval.Checked == false && edtInOrder.Checked == false && edtRandom.Checked == false) { MessageBox.Show("Please, select creation type of train and test set.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } #region Clear dtTrain.Rows.Clear(); dtTest.Rows.Clear(); dtTrain.Columns.Clear(); dtTest.Columns.Clear(); #endregion Clear #region Create train and test set columns dtTrain.Columns.Add("input1", typeof(string)); dtTrain.Columns.Add("input2", typeof(string)); dtTrain.Columns.Add("input3", typeof(string)); dtTrain.Columns.Add("output", typeof(string)); dtTest.Columns.Add("input1", typeof(string)); dtTest.Columns.Add("input2", typeof(string)); dtTest.Columns.Add("input3", typeof(string)); dtTest.Columns.Add("output", typeof(string)); #endregion Create train and test set columns int count = dtData.Rows.Count; #region InOrder if (edtInOrder.Checked) { double trainpercent = double.Parse(edtTrainPercent.Text); double textpercent = double.Parse(edtTrainPercent.Text); int trainCount = count * (int)trainpercent / 100; int testCount = count - trainCount; List <DataRow> drows = new List <DataRow>(); List <DataRow> drowss = new List <DataRow>(); for (int i = 0; i < trainCount; i++) { drows.Add(dtData.Rows[i]); } dtTrain = drows.CopyToDataTable(); for (int i = 0; i < testCount; i++) { drowss.Add(dtData.Rows[trainCount + i]); } dtTest = drowss.CopyToDataTable(); } #endregion InOrder #region Random else if (edtRandom.Checked) { double trainpercent = double.Parse(edtTrainPercent.Text); double textpercent = double.Parse(edtTestPercent.Text); Random r = new Random(); List <int> TrainIndex = new List <int>(); List <DataRow> drows = new List <DataRow>(); int trainCount = count * (int)trainpercent / 100; int testCount = count - trainCount; for (int i = 0; i < trainCount; i++) { int k = r.Next(0, count); if (TrainIndex.Contains(k)) { i--; continue; } TrainIndex.Add(k); drows.Add(dtData.Rows[k]); } dtTrain = drows.CopyToDataTable(); List <DataRow> drowss = new List <DataRow>(); for (int i = 0; i < testCount; i++) { int k = r.Next(0, count); if (TrainIndex.Contains(k)) { i--; continue; } drowss.Add(dtData.Rows[k]); } dtTest = drowss.CopyToDataTable(); } #endregion Random #region Interval else { double addx = double.Parse(edtIncrementx.Text); double timesx = Math.Round(addx / double.Parse(edtIntervalx.Text), 0); double addy = double.Parse(edtIncrementy.Text); double timesy = Math.Round(timesx * double.Parse(edtIntervaly.Text), 0); List <int> TrainIndex = new List <int>(); List <DataRow> drows = new List <DataRow>(); for (int i = 0; i < count; i += (int)timesx) { drows.Add(dtData.Rows[i]); TrainIndex.Add(i); } dtTrain = drows.CopyToDataTable(); List <DataRow> drowss = new List <DataRow>(); for (int i = 0; i < count; i++) { if (TrainIndex.Contains(i)) { continue; } drowss.Add(dtData.Rows[i]); } dtTest = drowss.CopyToDataTable(); } #endregion Interval tabMain.SelectedIndex = 1; grdTrain.DataSource = dtTrain; tls.MyGridStyle(grdTrain); grdTest.DataSource = dtTest; tls.MyGridStyle(grdTest); WriteParameter(); }
private void RefreshDataView(int _idx_page, int _cnt_page) { try { //string filter_page = _idx_page * _cnt_page + " < no and no <= " + (_idx_page + 1) * _cnt_page; //if(bFiltering) //{ // //current_table.DefaultView.RowFilter = filter_page + filter; //} //else //{ // //DataView dv = (from t in current_table.AsEnumerable() // // //where r.IsMatch(t.Field<string>("SOME_COL")) // // select t).AsDataView(); // //dataGrid.ItemsSource = current_table.AsEnumerable().Take(10).CopyToDataTable().DefaultView; // DataView dv = (from t in current_table.AsEnumerable() // select t).AsDataView(); // //current_table.DefaultView.RowFilter = filter_page; // //ObservableCollection<object> ItemsSource = new ObservableCollection<object>(); //// for(int i = 0; i < cnt_page; i++) //// { //// ItemsSource.Add(_newTable.Rows[idx_page * cnt_page + i]); //// } //// dataGrid.ItemsSource = ItemsSource.CopyToDataTable().DefaultView; //} if (dataGrid == null || Current_table == null) { return; } dataGrid.ItemsSource = new DataView(); Filtering_row = Current_table.Select(); for (int i = 0; i < (int)Filter_Idx.MemberCount && Filtering_row.Length > 0; i++) { if (filter_string[i] == null || filter_string[i] == "") { continue; } Filtering_row = Filtering_row.CopyToDataTable().Select(filter_string[i]); //current_table.DefaultView.RowFilter = filter; //_newTable = current_table.DefaultView.ToTable(); } List <DataRow> ItemsSource = new List <DataRow>(); int start_idx = idx_page * cnt_page; for (int i = start_idx; i < start_idx + cnt_page && i < Filtering_row.Length; i++) { ItemsSource.Add(Filtering_row[i]); } if (ItemsSource.Count > 0) { dataGrid.ItemsSource = ItemsSource.CopyToDataTable().DefaultView; } } catch (Exception e) { Log.PrintError(e.Message + " [ " + e.StackTrace + " ]", "RefreshDataView"); } if (Filtering_row == null) { return; } label_inform_page.Content = _idx_page * _cnt_page + " ~ " + (_idx_page + 1) * _cnt_page; label_total_count.Content = Filtering_row.Length; }
/// <summary> /// 刷新数据 --- 组合权限列表 /// </summary> /// <auth>Yanqiao.Cai</auth> /// <date>2013-01-06</date> private void SearchRightData() { try { DataRow drow = gridView_user.GetDataRow(gridView_user.FocusedRowHandle); if (null == drow) { this.gridControl_groupRight.DataSource = new DataTable(); return; } //查询所有组合 DataTable dt = DS_SqlService.GetDiseaseGroups(); //添加组合病种显示列 dt.Columns.Add("DiseasesGroup"); if (null != dt && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { dr["DiseasesGroup"] = DS_BaseService.GetDiseaseStringByGroupID(int.Parse(dr["ID"].ToString())); } } //添加组合权限列 dt.Columns.Add(new DataColumn("FLAG", typeof(bool))); DataRow[] listArry = dt.Select(" 1=1 "); if (allSource.Rows.Count == 0) { allSource = listArry.Length == 0 ? new DataTable() : listArry.CopyToDataTable(); } //筛选条件 string searchStr = this.txt_searchRight.Text.ToUpper();//.Replace("'","''"); checkedList.Clear(); notCheckedList.Clear(); //此用户权限内的组合ID集合 List <string> idList = DS_BaseService.GetDiseaseGroupIDsByUserID(drow["USERID"].ToString().Trim()); if (null == idList || idList.Count == 0) {//此用户没有组合权限 foreach (DataRow dr in listArry) { dr["FLAG"] = false; } notCheckedList = listArry.Where(p => p["NAME"].ToString().ToUpper().Contains(searchStr) || p["PY"].ToString().ToUpper().Contains(searchStr) || p["WB"].ToString().ToUpper().Contains(searchStr) || p["DiseasesGroup"].ToString().ToUpper().Contains(searchStr)).OrderBy(q => q["NAME"]).ToList(); } else { //已勾选项 不过滤 var checkedEnu = listArry.Where(p => null != p["ID"] && idList.Contains(p["ID"].ToString().Trim())).OrderBy(q => q["NAME"]); foreach (DataRow dr in checkedEnu) { dr["FLAG"] = true; checkedList.Add(dr); } //对未勾选项进行过滤 var notCheckedEnu = listArry.Where(p => null != p["ID"] && !idList.Contains(p["ID"].ToString().Trim()) && (p["NAME"].ToString().ToUpper().Contains(searchStr) || p["PY"].ToString().ToUpper().Contains(searchStr) || p["WB"].ToString().ToUpper().Contains(searchStr) || p["DiseasesGroup"].ToString().ToUpper().Contains(searchStr))).OrderBy(q => q["NAME"]); foreach (DataRow dr in notCheckedEnu) { dr["FLAG"] = false; notCheckedList.Add(dr); } } List <DataRow> unionList = checkedList.Union(notCheckedList).ToList(); DataTable endDt = (null == unionList || unionList.Count() == 0) ? new DataTable() : unionList.CopyToDataTable(); defaultSource = endDt.Copy(); this.gridControl_groupRight.DataSource = endDt; } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 过滤数据 --- 病种列表 /// </summary> /// 注:已勾选项 不过滤 /// <auth>Yanqiao.Cai</auth> /// <date>2013-01-06</date> private void SearchData() { try { string searchStr = string.IsNullOrEmpty(this.txt_search.Text) ? "" : this.txt_search.Text.ToUpper().Replace("'", "''"); //过滤后的所有病种数据集 List <DataRow> fillterList = allSource.Select(" 1=1 ").Where(p => p["ICD"].ToString().ToUpper().Contains(searchStr) || p["NAME"].ToString().ToUpper().Contains(searchStr) || p["PY"].ToString().ToUpper().Contains(searchStr) || p["WB"].ToString().ToUpper().Contains(searchStr)).OrderBy(q => q["NAME"]).ToList(); //除去已勾选的数据集 if (null != fillterList && fillterList.Count() > 0) { foreach (DataRow drow in checkedList) { if (fillterList.Any(p => p["MARKID"].ToString() == drow["MARKID"].ToString())) { fillterList.Remove(drow); } } } //剩余数据集为过滤后的未勾选数据集 notCheckedList = fillterList; //设置gridview数据集 List <DataRow> unionList = checkedList.Union(notCheckedList).ToList(); this.gridControl_disease.DataSource = (null == unionList || unionList.Count() == 0) ? new DataTable() : unionList.CopyToDataTable(); } catch (Exception ex) { throw new Exception(ex.Message); } }
string SearchItemCode(string sItemCode, string sDecription, string sItemTyp, string sItemStart, int iPageIndex, bool isStore) { string sJson = string.Empty; DataTable dtResult = new DataTable(); DataTable dt = new DataTable(); dt = PurchaseRequestCommon.GetActiveItemCode();/////得到所有的可用的ItemCode数据 EnumerableRowCollection<DataRow> drColle = from dr in dt.AsEnumerable() where (string.IsNullOrEmpty(sItemCode) || AsString(dr["Title"]).StartsWith(sItemCode, StringComparison.CurrentCultureIgnoreCase)) && (string.IsNullOrEmpty(sDecription) || AsString(dr["Description"]).Contains(sDecription)) && (isStore ? AsString(dr["ItemScope"]).Trim().Equals(sItemTyp, StringComparison.CurrentCultureIgnoreCase) : true) //QO,PB...... && (AsString(dr["Title"]).StartsWith(sItemStart, StringComparison.CurrentCultureIgnoreCase) || AsString(dr["Title"]).StartsWith("X", StringComparison.CurrentCultureIgnoreCase))//E,C select dr; DataTable dt10 = drColle.CopyToDataTable(); int iCount = dt10.Rows.Count; if (iCount > 0) { int iCurrentRow = (iPageIndex - 1) * iPageSize; List<DataRow> listDr = new List<DataRow>(); int iMaxRowIndex = iCurrentRow + iPageSize; if (iCount < iPageSize)///当结果行数少于每页显示的行数 { iMaxRowIndex = iCount; } if (iMaxRowIndex > iCount)//请求的最后一页所要求的行数大于所在行数。 { iMaxRowIndex = iCount; } for (int i = iCurrentRow; i < iMaxRowIndex; i++) { listDr.Add(dt10.Rows[i]); } dtResult = listDr.CopyToDataTable(); sJson = PurchaseRequestCommon.DataTableToJson("ItemCode", dtResult, iCount); } return sJson; }
private void bindGrid() { try { int iNoOfDays = 0; if (rbLessMonth.Checked == true) { iNoOfDays = 30; } else if (rbOneWeek.Checked == true) { iNoOfDays = -7; } else if (rbTwoWeek.Checked == true) { iNoOfDays = -14; } else if (rbThreeWeek.Checked == true) { iNoOfDays = -21; } int iUserID = ((UserDetails)Session[clsConstant.TOKEN]).UserID; #region flag value set //Loan</asp //TA</asp:L //Project</ //PipeLline //Portfolio //AAA</asp: string flagtype = ""; if (ddlFieldType.SelectedItem.Text == "Project") { flagtype = "P"; } if (ddlFieldType.SelectedItem.Text == "Loan") { flagtype = "L"; } if (ddlFieldType.SelectedItem.Text == "TA") { flagtype = "T"; } if (ddlFieldType.SelectedItem.Text == "PipeLline") { flagtype = "PI"; } if (ddlFieldType.SelectedItem.Text == "Portfolio") { flagtype = "PO"; } if (ddlFieldType.SelectedItem.Text == "AAA") { flagtype = "A"; } #endregion //SqlParameter[] param = new SqlParameter[] { new SqlParameter("@iUserID", iUserID), // new SqlParameter("@DocName", txtDocName.Text), // new SqlParameter("@DocID", drpDocID.SelectedValue), // This value use to create problem in stored procedure now resolved // new SqlParameter("@LoanNo", (ddlFieldTypeNo.SelectedValue=="" ?"0": ddlFieldTypeNo.SelectedValue)), // new SqlParameter("@DocCat", ddlDocCat.SelectedValue), // new SqlParameter("@DocType",ddlDocType.SelectedValue), // new SqlParameter("@SendToName",ddlSendTo.SelectedValue), // new SqlParameter("@TaNo", (ddlFieldTypeNo.SelectedValue=="" ?"0": ddlFieldTypeNo.SelectedValue)), // new SqlParameter("@DueDate", txtDueDate.Text), // new SqlParameter("@DocSendDate", txtDocSendDate.Text), // new SqlParameter("@DocSendFrom", txtFromDate.Text), // new SqlParameter("@DocSendTo", txtToDate.Text), // new SqlParameter("@Days",iNoOfDays), // new SqlParameter("@Project","0"), // new SqlParameter("@AgencyID",drpAgency.SelectedValue), // added by sachin // new SqlParameter("@State", drpState.SelectedValue) //}; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@iUserID", iUserID), new SqlParameter("@DocName", txtDocName.Text), new SqlParameter("@DocID", drpDocID.SelectedValue), // This value use to create problem in stored procedure now resolved //new SqlParameter("@LoanNo", (ddlFieldTypeNo.SelectedValue=="" ?"0": ddlFieldTypeNo.SelectedValue)), new SqlParameter("@DocCat", ddlDocCat.SelectedValue), new SqlParameter("@DocType", ddlDocType.SelectedValue), new SqlParameter("@SendToName", ddlSendTo.SelectedValue), //new SqlParameter("@TaNo", (ddlFieldTypeNo.SelectedValue=="" ?"0": ddlFieldTypeNo.SelectedValue)), new SqlParameter("@DueDate", txtDueDate.Text), new SqlParameter("@DocSendDate", txtDocSendDate.Text), new SqlParameter("@DocSendFrom", txtFromDate.Text), new SqlParameter("@DocSendTo", txtToDate.Text), new SqlParameter("@Days", iNoOfDays), //new SqlParameter("@Project","0"), new SqlParameter("@AgencyID", drpAgency.SelectedValue), // added by sachin new SqlParameter("@State", drpState.SelectedValue), new SqlParameter("@fieldtype", (ddlFieldTypeNo.SelectedValue == "" ?"0": ddlFieldTypeNo.SelectedValue)), new SqlParameter("@flagfieldtype", flagtype), }; // ds = objCommon.getGridviewData(clsConstant.SP_SELECT_DOCUMENT_LIST1, param); ds = objCommon.getGridviewData("sp_selectDocumentList_NewMDB", param); dt = new DataTable(); dt = ds.Tables[0]; #region ADD MORE IN ROWS IF MORE THAN ONE USER IS ATTAHCED var group = dt.AsEnumerable().GroupBy(myVar => myVar["iDocSendID"]); DataRow dr; List <DataRow> drCollection = new List <DataRow>(); int countCheck; foreach (var g in group) { countCheck = 0; if (g.Count() > 1) // FOR MULITPLE ROWS in GROUP { dr = g.FirstOrDefault(); bool bSkip = false; string str = dr["Name"].ToString(); foreach (var h in g) { if (bSkip) { if (countCheck < 5) { if (str.ToString().Trim() != h["Name"].ToString().Trim()) { dr["Name"] += " , " + h["Name"].ToString(); } str = h["Name"].ToString(); } else { dr["Name"] += " , " + ".... More "; break; } } bSkip = true; countCheck++; } } else // FOR SINGLE RECORD { dr = g.SingleOrDefault(); } drCollection.Add(dr); } if (drCollection.Count != 0) { dt = drCollection.CopyToDataTable(); } #endregion grdDocSendList.DataSource = dt; Session[clsConstant.SESS_TABLE] = dt; grdDocSendList.DataBind(); foreach (GridViewRow row in grdDocSendList.Rows) { HyperLink ObjHyp = new HyperLink(); ObjHyp = (HyperLink)row.FindControl("lnkView"); IDataItemContainer ObjIcont = (IDataItemContainer)ObjHyp.NamingContainer; int index = ObjIcont.DataItemIndex; string E = dt.Rows[index][14].ToString(); if (E != "") { grdDocSendList.Rows[row.RowIndex].Cells[6].Text += grdDocSendList.Rows[row.RowIndex].Cells[6].Text != "" ? "<img src='../../Images/Icons/attachment.png'></img>" : ""; } } } catch (Exception ex) { logger.Error(ex); } }
private void openFile_Click(object sender, EventArgs e) { using (OpenFileDialog dialog = new OpenFileDialog()) { if (dialog.ShowDialog() == DialogResult.OK) // Test result. { #region Read the xls file fileName.Text = dialog.FileName; //var s = ConfigurationManager.ConnectionStrings["xlsx"].ConnectionString; var connStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES'", dialog.FileName); //Excel.Application xlApp = new Excel.Application(); //Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(dialog.FileName, 0, false); //Excel.Worksheet xlWorkSheet = xlWorkbook.Worksheets[2]; dataTable = new DataTable(); var dtColumns = ReportColumnMap.NewDtColumnDict(); var sheetName = "ALL$"; try { using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); // Filter the columns read by intended hardcoded columns var colNameArrayStr = string.Join(",", dtColumns.Keys.Select(k => "'" + k + "'")); var filterStr = string.Format(@"[TABLE_NAME]='{0}' AND [COLUMN_NAME] IN({1})", sheetName, colNameArrayStr); var schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null)?.Select(filterStr).ToList().OrderBy(r => r["ORDINAL_POSITION"]); var colMapped = schema?.Select(r => (string)r["COLUMN_NAME"]).ToList(); colNameArrayStr = string.Join(",", colMapped?.Select(m => "[" + m + "]")); // Select only the intended columns OleDbCommand comm = new OleDbCommand(string.Format("select {0} from [{1}] where PositionId > 0", colNameArrayStr, sheetName), conn); OleDbDataAdapter adapter = new OleDbDataAdapter() { SelectCommand = comm }; // Populate the datatable with mapped columns in order read colMapped?.ForEach(c => dataTable.Columns.Add(dtColumns[c])); adapter.Fill(dataTable); } } catch (Exception ex) { Console.WriteLine(ex.ToString()); } #endregion //var interestedPos = new[] { 3581874, 3581875, 3581876, 3581884, 3581885, 3581886, 3583129, 3583130, // 3583131, 4260687, 4395271, 4969299, 4969300, 4969301, 5096217, 5172317, 5172318, 5172319, 5219314, // 5269777, 5275676, 9957149, 10014344, 10014357, 10107257, 10107392, 10526409, 10526410, 10526413, // 10766562, 10796052, 10796055, 10811540, 10811614, 10811769, 10886711, 10982243, 11003554, 11003555, // 11003556, 11005210, 11005211, 11005212, 11005213, 11005214, 11005215, 11005219, 11005220, 11005221, // 11007979, 11007980, 11007981, 11007982, 11007983, 11007984, 11031540, 11031554 }; var interestedPos = new[] { 4260687 }; var startingDate = dataTable.AsEnumerable().Min(d => d["EodDate"]); var posGroup = dataTable.AsEnumerable().GroupBy(r => (int)r["PositionId"]) .OrderBy(g => g.Key) .OrderBy(g => g.Select(dtRow => dtRow["Region"]).First()) .Where(p => interestedPos.Contains(p.Key)); //var posGroup = dataTable.AsEnumerable().GroupBy(r => (int)r["PositionId"]) // .OrderBy(g => g.Key) // .OrderBy(g => g.Select(dtRow => dtRow["Region"]).First()); dataTableExport = new DataTable(); var dtExColumns = ReportColumnMap.NewDtColumnDict(); dataTableExport.Columns.AddRange(dtExColumns.Values.ToArray()); //var rowArray = posGroup.SelectMany(g => g).ToList(); //dataTableExport = rowArray.CopyToDataTable(); dataTableAnalysed = new DataTable(); var analysisColumns = ReportColumnMap.NewAnalysisDtColumns(); dataTableAnalysed.Columns.AddRange(analysisColumns); #region Load pfs data var load = new PfsDataLoad(); var summarizedRows = new List <DataRow>(); var analysedRows = new List <DataRow>(); foreach (var pos in posGroup) { if ((string)pos.FirstOrDefault()["Region"] == "APAC") { continue; } if (pos.Any(p => p["Reason"] != DBNull.Value && (string)p["Reason"] == "CB/FUT")) { pos.ToList().ForEach(p => p["Reason"] = "CB/FUT"); summarizedRows.AddRange(pos); continue; } var eodDates = pos.GroupBy(r => (int)r["EodDate"]).ToList(); var eodDuplicates = eodDates.Where(d => d.Count() > 1).ToList(); var eodInconsistent = eodDuplicates.Where(d => d.ToList()[0]["Residual"] != d.ToList()[1]["Residual"]).ToList(); eodInconsistent.ForEach(d => d.ToList().ForEach(n => n["Comments"] = "Inconsistent residuals")); foreach (var eod in eodDates) { try { if (eodInconsistent.Any(d => d.Key == eod.Key)) { summarizedRows.AddRange(eodInconsistent[eod.Key]); continue; } var dtRow = eod.First(); var itemArrayAnalysed = new object[] { dtRow["Region"], dtRow["EodDate"], dtRow["PositionNode"], dtRow["PositionId"], dtRow["Delta_T_2"], dtRow["Quantity_T_1"], dtRow["Quantity_T_2"], dtRow["TradeQuantity"], dtRow["Theta"], dtRow["IntradayTradingPLUSD"], dtRow["Residual"], dtRow["AbsRes"], false, // Db_Inconsistency "", // Qty_Inconsistency "", // Avg_Inconsistency "", // Rls_Inconsistency "", // Comm_Inconsistency "", // Div_Inconsistency "", // Coupon_Inconsistency true, // MarkingOK "", // UCF_CT DBNull.Value, // UCF_Amt DBNull.Value, // UCF_Reset "", // SuddenUCF_CT DBNull.Value, // SuddenUCF_USDAmt "", // SuddenRCF_CT DBNull.Value, // SuddenRCF_USDAmt false //Cancelled }; var dtRowAnalysed = dataTableAnalysed.NewRow(); dtRowAnalysed.ItemArray = itemArrayAnalysed; PfsPaaDAL.Region region; if (!Enum.TryParse((string)dtRow["Region"], out region)) { continue; } var t_1 = eod.Key; var t_2 = Utility.DateToRover8(Utility.AddBusinessDays(Utility.Rover8ToDate(t_1), -1, null)); var tday = new Dictionary <int, string> { { t_1, "T-1" }, { t_2, "T-2" } }; load.ShowHist(region: region, date: t_1, posId: (int)dtRow["PositionId"], goBack: 2, showFixes: false); var inconsistency = load.ConsistencyDataSet.Where(pl => (!pl.Value.IsOk) && (pl.Key == t_1 || pl.Key == t_2)); var marking = load.PaaDataSet.Where(paa => (paa.Value.MOK == "M") && (paa.Key == t_1 || paa.Key == t_2)); var swap_T_1 = load.PfsDataSet[t_1]; var swap_T_2 = load.PfsDataSet[t_2]; var ucf = swap_T_1.UnrealisedCF; var sddUcf = swap_T_1.UcfSudden; var sddRcf = swap_T_1.RcfSudden; // Inconsistency columns if (inconsistency.Any()) { dtRow["Reason"] = Utility.AttachString(dtRow["Reason"], "Db inconsistency"); dtRowAnalysed["Db_Inconsistency"] = true; foreach (var t in inconsistency) { // TODO : PfsDbConsistencyData var types = new Dictionary <int, string> { { 0, t.Value.QtyOk == "" ? "" : "Qty" }, { 1, t.Value.AvgOk == "" ? "" : "AvgCost" }, { 2, t.Value.RlsdOk == "" ? "" : "RealisedCF" }, { 3, t.Value.CommOk == "" ? "" : "Commision" }, { 4, t.Value.DivOk == "" ? "" : "Dividend" }, { 5, t.Value.CouponOk == "" ? "" : "Coupon" } }.Where(x => !string.IsNullOrEmpty(x.Value)); var iQty = dataTableAnalysed.Columns.IndexOf("Qty_Inconsistency"); types.ToList().ForEach(x => { dtRow["SubReason"] = Utility.AttachString(dtRow["SubReason"], x.Value); dtRowAnalysed[iQty + x.Key] = Utility.AttachString(dtRowAnalysed[iQty + x.Key], tday[t.Key]); }); } } // Marking error column if (marking.Count() > 0) { dtRow["Reason"] = Utility.AttachString(dtRow["Reason"], "Marking error"); dtRowAnalysed["MarkingOK"] = false; } // UCF columns if (ucf.Length > 0) { double ucfAmt = 0; foreach (var u in ucf) { dtRowAnalysed["UCF_CT"] = Utility.AttachString(dtRowAnalysed["UCF_CT"], Enum.GetName(typeof(ShortCashFlowTypeEnum), u.ShortCashFlowType)); ucfAmt += u.Amount; } dtRowAnalysed["UCF_Amt"] = ucfAmt; } // UCF_Reset column var ucfReset = ucf.FirstOrDefault(u => u.ShortCashFlowType == ShortCashFlowTypeEnum.Reset)?.FinAmt; dtRowAnalysed["UCF_Reset"] = (object)ucfReset ?? DBNull.Value; // SudddenUCF columns if (sddUcf.Count() > 0) { //var usdAmt = Convert.ToDouble(Utility.DBNullToNull(dtRowAnalysed["SuddenUCF_USDAmt"]) ?? 0.0); double usdAmt = 0; foreach (var s in sddUcf) { dtRowAnalysed["SuddenUCF_CT"] = Utility.AttachString(dtRowAnalysed["SuddenUCF_CT"], Enum.GetName(typeof(ShortCashFlowTypeEnum), s.ShortCashFlowType)); usdAmt += s.USDAmt; } dtRowAnalysed["SuddenUCF_USDAmt"] = usdAmt; } // SuddenRCF columns if (sddRcf.Count() > 0) { //var usdAmt = Convert.ToDouble(Utility.DBNullToNull(dtRowAnalysed["SuddenRCF_USDAmt"]) ?? 0.0); double usdAmt = 0; foreach (var s in sddRcf) { dtRowAnalysed["SuddenRCF_CT"] = Utility.AttachString(dtRowAnalysed["SuddenRCF_CT"], s.CashType); usdAmt += s.USDAmt; } dtRowAnalysed["SuddenRCF_USDAmt"] = usdAmt; } // Cancelled column if (swap_T_1.TQ == 0 && (swap_T_2.TQ + swap_T_1.IdayTQ == 0) && swap_T_1.SettQty == 0 && swap_T_1.SettPrice == 0) { dtRow["Reason"] = Utility.AttachString(dtRow["Reason"], "Closed out position"); dtRowAnalysed["Cancelled"] = true; } summarizedRows.Add(dtRow); analysedRows.Add(dtRowAnalysed); } catch (Exception ex) { summarizedRows.Add(eod.First()); Console.WriteLine(ex.ToString()); } } } #endregion #region Export report //dataTableExport.Rows.Clear(); dataTableAnalysed = analysedRows.CopyToDataTable(); dataTableExport = summarizedRows.CopyToDataTable(); dataGrid.DataSource = dataTableExport; dataGrid2.DataSource = dataTableAnalysed; //var outputPath = @"N:\Issues\High Residual\"; //var file = "ResSummary.GLOBAL.20161220_20170120.auto.xlsx"; //FileInfo newFile = new FileInfo(outputPath + file); //ExportToExcel(newFile, dataTableExport, "All_Summary"); //ExportToExcel(newFile, dataTableAnalysed, "PFS_PAA_Analysis"); //var ds = new DataSet(); //ds.Tables.Add(dataTableExport); // DataSetHelper.CreateWorkbook(outputPath + "ResSummaryAll.20161220_20170120.xlsx", ds); #endregion } } }
public DataTable GetProductsWithProductId(List<int> ProductID) { DataTable dt=new DataTable(); if (ProductID.Count > 0) { List<DataRow> lstRow = new List<DataRow>(); DataTable dt1 = new DataTable(); foreach (int i in ProductID) { SqlParameter[] parameters = new SqlParameter[1]; parameters[0] = DataAccessLayer.DataAccess.AddParameter("@ProductID", i, System.Data.SqlDbType.Int, 20); dt1 = DataAccessLayer.DataAccess.ExecuteDTByProcedure("SP_GetCartProduct", parameters); DataRow dr; dr = dt1.Rows[0]; lstRow.Add(dr); } dt = lstRow.CopyToDataTable(); } return dt; }
/// <summary> /// フィルター /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnFilter_Click(object sender, EventArgs e) { try { if (btnFilter.Text == LBL_FILTER_ON) { var labelNameList = historyDispDt.AsEnumerable() .Select(s => s[HistoryDispColumns.Label].ToString()) .Distinct().ToList(); var dt = Util.CreateHistoryFilterDataTable(); foreach (var labelName in labelNameList) { var dr = dt.NewRow(); dr[HistoryFilterColumns.LabelName] = labelName; dr[HistoryFilterColumns.Checked] = true; dt.Rows.Add(dr); } using (var form = new frmSelectFilter()) { form.FilterDt = dt; form.ShowDialog(); // 表示にチェックがあるもののみを取得 var showLabelNameList = form.FilterDt.AsEnumerable() .Where(w => (bool)w[HistoryFilterColumns.Checked]) .Select(s => s[HistoryFilterColumns.LabelName].ToString()).ToList(); List <DataRow> showRows = new List <DataRow>(); foreach (var labelName in showLabelNameList) { var rows = historyDispDt.AsEnumerable() .Where(w => w[HistoryDispColumns.Label].Equals(labelName)).ToList(); if (rows.Count > 0) { showRows.AddRange(rows); } } if (showRows.Count != historyDispDt.Rows.Count) { // フィルタリング dgvHistoryList.DataSource = showRows.CopyToDataTable(); // ボタン表示切替 btnFilter.Text = LBL_FILTER_OFF; } } } else { // ボタン表示切替 btnFilter.Text = LBL_FILTER_ON; var dt = (DataTable)dgvHistoryList.DataSource; dt.PrimaryKey = new DataColumn[] { dt.Columns[HistoryDispColumns.Id] }; historyDispDt.Merge(dt); historyDispDt.AcceptChanges(); dgvHistoryList.DataSource = historyDispDt; } } catch (Exception ex) { CommonUtil.PutMessage(CommonEnums.MessageType.Error, ex.Message); } }
/// <summary> /// 刷新数据 - 死亡索引卡 /// </summary> private void RefreshDeathCardData() { try { string sqlStr = @" select i.noofinpat,i.patid,i.name,i.py,i.wb,i.sexid,i.age,i.agestr,i.outbed, case when i.status in(1502,1503) then '出院' else '在院' end as statusname,i.admitdate,i.inwarddate, i.admitdept,d1.name as admitdeptname,i.outhosdate,i.outwarddate,i.outhosdept, (sp.provincename || sc.cityname || sd.districtname || i.xzzdetailaddress) as xzzaddressunion, i.xzzaddress,d2.name as outhosdeptname,dia.diagnosis_code,dia.diagnosis_name, to_char(to_date(i.deathdatetime,'yyyy-MM-dd HH24:mi:ss'),'yyyy-MM-dd') as deathdate, to_char(to_date(i.deathdatetime,'yyyy-MM-dd HH24:mi:ss'),'HH24:mi:ss') as deathtime, oth.emergency_times, i.status from inpatient i left join department d1 on i.admitdept=d1.id left join department d2 on i.outhosdept=d2.id left join iem_mainpage_basicinfo_2012 bas on i.noofinpat=bas.noofinpat left join iem_mainpage_diagnosis_2012 dia on bas.iem_mainpage_no=dia.iem_mainpage_no left join iem_mainpage_other_2012 oth on bas.iem_mainpage_no=oth.iem_mainpage_no left join s_province sp on i.xzzproviceid=sp.provinceid left join s_city sc on i.xzzcityid=sc.cityid left join s_district sd on i.xzzdistrictid=sd.districtid where 1=1 "; //string sqlStr = " select i.noofinpat,i.patid,i.name,i.py,i.wb,i.sexid,i.age,i.agestr,i.outbed,case when i.status in(1502,1503) then '出院' else '在院' end as statusname,i.admitdate,i.inwarddate,i.admitdept,d1.name as admitdeptname,i.outhosdate,i.outwarddate,i.outhosdept,(sp.provincename || sc.cityname || sd.districtname || i.xzzdetailaddress) as xzzaddressunion,i.xzzaddress,d2.name as outhosdeptname,dia.diagnosis_code,dia.diagnosis_name,i.deathdatetime deathdate,i.deathdatetime as deathtime,oth.emergency_times from inpatient i left join department d1 on i.admitdept=d1.id left join department d2 on i.outhosdept=d2.id left join iem_mainpage_basicinfo_2012 bas on i.noofinpat=bas.noofinpat left join iem_mainpage_diagnosis_2012 dia on bas.iem_mainpage_no=dia.iem_mainpage_no left join s_province sp on i.xzzproviceid=sp.provinceid left join s_city sc on i.xzzcityid=sc.cityid left join s_district sd on i.xzzdistrictid=sd.districtid where 1=1 "; List <OracleParameter> paramList = new List <OracleParameter>(); if (!string.IsNullOrEmpty(date_InHosBegin.Text)) {///入院开始时间 sqlStr += " and i.inwarddate >= @inwarddatebegin "; OracleParameter param1 = new OracleParameter("inwarddatebegin", OracleType.VarChar); param1.Value = date_InHosBegin.DateTime.ToString("yyyy-MM-dd 00:00:00"); paramList.Add(param1); } if (!string.IsNullOrEmpty(date_InHosEnd.Text)) {///入院结束时间 sqlStr += " and i.inwarddate <= @inwarddateend "; OracleParameter param2 = new OracleParameter("inwarddateend", OracleType.VarChar); param2.Value = date_InHosEnd.DateTime.ToString("yyyy-MM-dd 23:59:59"); paramList.Add(param2); } if (radio_HosState.SelectedIndex == 1) {///在院状态 sqlStr += " and i.status not in(1502,1503) "; } else { string outHosSql = string.Empty; if (!string.IsNullOrEmpty(date_OutHosBegin.Text)) {///出院开始时间 outHosSql += " and i.outwarddate >= @outwarddatebegin "; OracleParameter param3 = new OracleParameter("outwarddatebegin", OracleType.VarChar); param3.Value = date_OutHosBegin.DateTime.ToString("yyyy-MM-dd 00:00:00"); paramList.Add(param3); } if (!string.IsNullOrEmpty(date_OutHosEnd.Text)) {///出院结束时间 outHosSql += " and i.outwarddate <= @outwarddateend "; OracleParameter param4 = new OracleParameter("outwarddateend", OracleType.VarChar); param4.Value = date_OutHosEnd.DateTime.ToString("yyyy-MM-dd 23:59:59"); paramList.Add(param4); } if (!string.IsNullOrEmpty(outHosSql)) { if (radio_HosState.SelectedIndex == 0) {///全部 sqlStr += " and (i.status not in(1502,1503) or (i.status in(1502,1503) " + outHosSql + ") ) "; } else if (radio_HosState.SelectedIndex == 2) {///出院状态 Modified By wwj 2013-08-07 sqlStr += outHosSql + " and i.status in(1502,1503) "; } } } if (!string.IsNullOrEmpty(lookUpEditorDepartment.CodeValue.ToString()) && lookUpEditorDepartment.CodeValue.ToString() != "0000") {///科室 sqlStr += " and i.outhosdept = @outhosdept "; OracleParameter param5 = new OracleParameter("outhosdept", OracleType.VarChar); param5.Value = lookUpEditorDepartment.CodeValue.ToString(); paramList.Add(param5); } if (!string.IsNullOrEmpty(txt_Patid.Text.Trim())) {///住院号 string patid = DS_Common.FilterSpecialCharacter(txt_Patid.Text.Trim()); sqlStr += " and i.patid like '%" + patid + "%' "; } sqlStr += " and bas.outhostype='5' and bas.valide=1 and dia.diagnosis_type_id=7 and dia.valide=1 order by i.name,i.outwarddate,dia.order_value"; DS_Common.InitializeImage_XB(repositoryItemImageXB, imageListBrxb); DataTable dt = DS_SqlHelper.ExecuteDataTable(sqlStr, paramList, CommandType.Text); SetDataTableAllNotSelect(dt); if (null != dt && dt.Rows.Count > 0) { var rowList = dt.AsEnumerable().GroupBy(p => p["noofinpat"]); List <DataRow> resultList = new List <DataRow>(); foreach (var keyAndValue in rowList) { resultList.Add(keyAndValue.FirstOrDefault()); } gridControlMain.DataSource = resultList.CopyToDataTable(); lbl_TotalCount.Text = "共" + resultList.Count() + "条记录"; } else { gridControlMain.DataSource = dt.Clone(); lbl_TotalCount.Text = "共0条记录"; } } catch (Exception ex) { throw ex; } }