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;
}
}
