public void insertData(DataTable dtData, string folderName, bool allTovar) { if (isError) { throw new Exception("Couldn't find shop settings"); } EnumerableRowCollection <DataRow> rowCollect = dtData.AsEnumerable().Where(r => r.Field <int>("isInsert") == 0); if (rowCollect.Count() > 0) { newTovar(rowCollect.CopyToDataTable().Copy(), folderName, true, true); foreach (DataRow row in rowCollect) { Task <DataTable> task = Config.hCntMain.setInsertGoods((int)row["id"]); task.Wait(); } } rowCollect = dtData.AsEnumerable().Where(r => r.Field <int>("isInsert") == 1); if (rowCollect.Count() > 0) { newTovar(rowCollect.CopyToDataTable().Copy(), folderName, false, true); } }
private void button28_Click(object sender, EventArgs e) { // <SnippetQueryDataView2> DataTable products = dataSet.Tables["Product"]; // Query for red colored products. EnumerableRowCollection <DataRow> redProductsQuery = from product in products.AsEnumerable() where product.Field <string>("Color") == "Red" orderby product.Field <decimal>("ListPrice") select product; // Create a table and view from the query. DataTable redProducts = redProductsQuery.CopyToDataTable <DataRow>(); DataView view = new DataView(redProducts); // Mark a row as deleted. redProducts.Rows[0].Delete(); // Modify product price. redProducts.Rows[1]["ListPrice"] = 20.00; redProducts.Rows[2]["ListPrice"] = 30.00; view.RowStateFilter = DataViewRowState.ModifiedCurrent | DataViewRowState.Deleted; // Query for the modified and deleted rows. IEnumerable <DataRowView> modifiedDeletedQuery = from DataRowView rowView in view select rowView; dataGridView2.DataSource = modifiedDeletedQuery.ToList(); // </SnippetQueryDataView2> }
private void init_postVsDeps() { Task <DataTable> task = Config.hCntMain.getPostVsDeps(); task.Wait(); dtPostVsDeps = task.Result; if (UserSettings.User.StatusCode.ToLower().Equals("ркв")) { DataTable dtTmp = dtPostVsDeps.Clone(); foreach (DataRow row in dtDepsLinkToDep.Rows) { EnumerableRowCollection <DataRow> rowToReport = task.Result.AsEnumerable().Where(r => r.Field <int>("id_Departments") == (int)row["id_DepartmentsView"]); if (rowToReport.Count() > 0) { dtTmp.Merge(rowToReport.CopyToDataTable()); } } dtPostVsDeps = dtTmp.Copy(); } setFilter(); dgvData.DataSource = dtPostVsDeps; }
protected void btnFiltrar_Click(object sender, EventArgs e) { msnMessage.HideMessage(); if (txtFilterGrid.Text != String.Empty) { llenarGrillar(); DataTable data = (DataTable)Session[DSApplication]; EnumerableRowCollection <DataRow> filteredRows = data.AsEnumerable().Where(x => x.Field <string>("apl_nombre").ToUpper().Contains(txtFilterGrid.Text.Trim().ToUpper())); if (filteredRows.Count() > 0) { DataTable dataFiltrado = filteredRows.CopyToDataTable(); Session[DSApplication] = dataFiltrado; dataFiltrado.DefaultView.Sort = "apl_nombre"; GridApplication.DataSource = dataFiltrado; GridApplication.DataBind(); } else { msnMessage.LoadMessage("Palabra no encontrAQUARELLA.", UserControl.ucMessage.MessageType.Information); } } else { msnMessage.LoadMessage("Digite una palabra para filtrar por nombre", UserControl.ucMessage.MessageType.Information); llenarGrillar(); } }
internal ObservableCollection <dhModule> GetUserSubMenu(dhDBnames objDBNames, dhAppPreference objAppPreference, dhUsers objuser, dhModule objdhModule) { DataTable temp = new DataTable(); blModule ObjblModule = new blModule(); dhModule objModule = new dhModule(); ObservableCollection <dhModule> MenuModule = new ObservableCollection <dhModule>(); if ((objAppPreference != null) && (objAppPreference.VEnableModules != "") && (objuser != null)) { #region "Allowed Menu" objModule.AllowdModule = objAppPreference.VEnableModules; dsGeneral.dtPosModuleDataTable dtm = ObjblModule.GetModule(objDBNames, objModule); EnumerableRowCollection <dsGeneral.dtPosModuleRow> result2 = from r in dtm.AsEnumerable() where (r.Field <int>("iModuleParentID") == objdhModule.IModuleID) && (r.Field <bool>("bIsActive") == true) select r; // if there are any allowed int CountRow = result2.Count <dsGeneral.dtPosModuleRow>(); if (CountRow > 0) { DataTable dtResult2 = result2.CopyToDataTable(); ObservableCollection <dhModule> sequence = ReflectionUtility.DataTableToObservableCollection <dhModule>(dtResult2); if (sequence.Count > 0) { foreach (dhModule Module in sequence) { string strDisplayName = Module.VDisplayName; if (objuser.VAllowdModule == null) { if (objuser.VUserType == "Super") { objuser.VAllowdModule = objAppPreference.VEnableModules; } else { throw new ApplicationException("Disable User or have no rights."); } } List <string> RootIds = objuser.VAllowdModule.Split(',').ToList <string>(); string RootAllowdUserID = RootIds.Distinct <string>().Cast <string>().Where(i => i.Equals(Module.IModuleID.ToString())).SingleOrDefault(); List <string> RootEnableModulesIds = objAppPreference.VEnableModules.Split(',').ToList <string>(); string RootAppEnableModulesId = RootEnableModulesIds.Distinct <string>().Cast <string>().Where(i => i.Equals(Module.IModuleID.ToString())).SingleOrDefault(); if ((RootAllowdUserID == null) || (RootAppEnableModulesId == null)) { continue; } MenuModule.Add(Module); } }// end of dhModule sequence } #endregion } return(MenuModule); }
private void getSection() { lObject.Visible = cmbObject.Visible = true; lBuilding.Visible = cmbBuilding.Visible = true; lFloor.Visible = cmbFloor.Visible = true; dgvData.Visible = true; if (cmbObject.SelectedValue == null) { return; } if (cmbBuilding.SelectedValue == null) { return; } if (cmbFloor.SelectedValue == null) { return; } cmbBuilding.SelectedIndex = 0; cmbFloor.SelectedIndex = 0; int id_object = (int)cmbObject.SelectedValue; int id_building = (int)cmbBuilding.SelectedValue; int id_floor = (int)cmbFloor.SelectedValue; Task <DataTable> task = Config.hCntMain.getSections(); task.Wait(); if (task.Result == null || task.Result.Rows.Count == 0) { return; } EnumerableRowCollection <DataRow> rowCollect = task.Result.AsEnumerable() .Where(r => (r.Field <int>("id_Building") == id_building || id_building == 0) && r.Field <int>("id_ObjectLease") == id_object && (r.Field <int>("id_Floor") == id_floor || id_floor == 0) && r.Field <bool>("isActive")); if (rowCollect.Count() > 0) { dtData = rowCollect.CopyToDataTable(); setFilter(); dgvData.DataSource = dtData; //cmbComby.DataSource = rowCollect.CopyToDataTable(); //cmbComby.DisplayMember = "cName"; //cmbComby.ValueMember= "id"; //cmbComby.SelectedIndex = -1; } }
private void addHeaderDoc(int idTypeDoc, TreeNode parentNode, bool ViewArchive) { EnumerableRowCollection <DataRow> rowCollect = dtDocuments.AsEnumerable() .Where(r => r.Field <int>("id_TypeDoc") == idTypeDoc && r.Field <string>("namePost").ToLower().Contains(tbNamePosts.Text.ToLower()) && r.Field <string>("nameDoc").ToLower().Contains(tbNameDocuments.Text.ToLower()) && new List <int> { 2, 3 }.Contains(r.Field <int>("id_Status"))); if (rowCollect.Count() > 0) { TreeNode nodeActive = new TreeNode(); nodeActive.Text = "Действующие"; nodeActive.ImageIndex = trvPost.ImageList.Images.IndexOfKey("check"); nodeActive.SelectedImageIndex = trvPost.ImageList.Images.IndexOfKey("arrow"); addPostDoc(nodeActive, rowCollect.CopyToDataTable()); parentNode.Nodes.Add(nodeActive); } if (ViewArchive) { rowCollect = dtDocuments.AsEnumerable() .Where(r => r.Field <int>("id_TypeDoc") == idTypeDoc && r.Field <string>("namePost").ToLower().Contains(tbNamePosts.Text.ToLower()) && r.Field <string>("nameDoc").ToLower().Contains(tbNameDocuments.Text.ToLower()) && new List <int> { 4 }.Contains(r.Field <int>("id_Status"))); if (rowCollect.Count() > 0) { TreeNode nodeArhive = new TreeNode(); nodeArhive.Text = "Архив"; nodeArhive.ImageIndex = trvPost.ImageList.Images.IndexOfKey("archive"); nodeArhive.SelectedImageIndex = trvPost.ImageList.Images.IndexOfKey("arrow"); addPostDoc(nodeArhive, rowCollect.CopyToDataTable()); parentNode.Nodes.Add(nodeArhive); } } }
public static void LoadViews(this DropDownList ddl) { EnumerableRowCollection <DataRow> FilteredDt = Entities.Reporting.ReportingTool.GetViews().AsEnumerable().Where(x => x.Field <string>("Name").ToUpper().Contains("REPORT")); DataTable dt = FilteredDt != null?FilteredDt.CopyToDataTable() : null; ddl.Items.Clear(); ddl.Items.Add(new ListItem("--select--", "0")); if (dt != null) { foreach (DataRow item in dt.Rows) { ddl.Items.Add(new ListItem(item["Name"].ToString(), item["Object_Id"].ToString())); } } }
private void init_depsCombobox() { DataTable dtDeps = null; Task <DataTable> task = Config.hCntMain.getDeps(true); task.Wait(); if (task.Result != null && task.Result.Rows.Count > 0) { dtDeps = task.Result.Copy(); task = null; } if (UserSettings.User.StatusCode.ToLower().Equals("ркв")) { if (dtDepsLinkToDep == null) { EnumerableRowCollection <DataRow> rowCollect = dtDeps.AsEnumerable().Where(r => r.Field <Int16>("id") == 0); dtDeps = rowCollect.CopyToDataTable(); } else { DataTable dtTmp = dtDeps.Clone(); EnumerableRowCollection <DataRow> rowCollect = dtDeps.AsEnumerable().Where(r => r.Field <Int16>("id") == 0); dtTmp.Merge(rowCollect.CopyToDataTable()); foreach (DataRow row in dtDepsLinkToDep.Rows) { rowCollect = dtDeps.AsEnumerable().Where(r => r.Field <Int16>("id") == (int)row["id_DepartmentsView"]); if (rowCollect.Count() > 0) { dtTmp.ImportRow(rowCollect.First()); } } dtTmp.DefaultView.Sort = "isMain asc, name asc"; dtDeps = dtTmp.DefaultView.ToTable().Copy(); } } cmbDeps.DataSource = dtDeps; cmbDeps.DisplayMember = "name"; cmbDeps.ValueMember = "id"; }
private void get_typeDoc() { Task <DataTable> task = Config.hCntMain.getTypeDoc(true); task.Wait(); DataTable dtTypeDoc = null; if (task.Result != null && task.Result.Rows.Count > 0) { EnumerableRowCollection <DataRow> rowCollect = task.Result.AsEnumerable().Where(r => r.Field <bool>("isActive") && r.Field <bool>("ViewAdd")); if (rowCollect.Count() > 0) { dtTypeDoc = rowCollect.CopyToDataTable(); } } cmbTypeDoc.DataSource = dtTypeDoc; cmbTypeDoc.DisplayMember = "cName"; cmbTypeDoc.ValueMember = "id"; }
public void updatePeriod() { EnumerableRowCollection <DataRow> rowCollcect = frmLPeriod.dtData.AsEnumerable().Where(r => r.Field <bool>("isSelect")); if (rowCollcect.Count() > 0) { dgvPeriod.DataSource = rowCollcect.CopyToDataTable(); } else { dgvPeriod.DataSource = frmLPeriod.dtData.Clone(); } createPeridoCombobox(); paintGraphic(); //frmLPeriod.Hide(); if (this.WindowState != FormWindowState.Normal) { this.Show(); } }
private void txtSearch_TextChanged(object sender, System.Windows.Controls.TextChangedEventArgs e) { currentPage = 1; EnumerableRowCollection <DataRow> filtered = null; if (!Equals(dtExcel, null)) { filtered = dtExcel.AsEnumerable() .Where( r => r.Field <String>("Prenom").Contains(txtSearch.Text.Trim()) || r.Field <String>("Prenom1").Contains(txtSearch.Text.Trim()) || r.Field <String>("Nom").Contains(txtSearch.Text.Trim()) || r.Field <String>("Organization").Contains(txtSearch.Text.Trim()) || r.Field <String>("Fonction").Contains(txtSearch.Text.Trim()) || r.Field <String>("Mobile").Contains(txtSearch.Text.Trim()) || r.Field <String>("Mobile1").Contains(txtSearch.Text.Trim()) || r.Field <String>("Fax").Contains(txtSearch.Text.Trim()) || r.Field <String>("URL").Contains(txtSearch.Text.Trim()) || r.Field <String>("Email").Contains(txtSearch.Text.Trim()) || r.Field <String>("Address").Contains(txtSearch.Text.Trim()) ); } if (!Equals(filtered, null) && filtered.Count() > 0) { dtFiltered = filtered.CopyToDataTable(); ShowData(currentPage, dtFiltered); } else { if (!Equals(dtFiltered, null)) { dtFiltered.Clear(); ShowData(currentPage, dtFiltered); } } }
protected void repStoreStock_ItemDataBound(object sender, RepeaterItemEventArgs e) { if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) { int storeId = (e.Item.DataItem as StoresInfo).StoreId; EnumerableRowCollection <DataRow> source = from a in this.dtSkuStocks.AsEnumerable() where a.Field <int>("StoreId") == storeId && !string.IsNullOrEmpty(a.Field <string>("SKUContent")) select a; if (source.Count() > 0) { DataTable dataSource = source.CopyToDataTable(); Repeater repeater = e.Item.FindControl("repStocks") as Repeater; repeater.DataSource = dataSource; repeater.DataBind(); } else { this.divempty.Visible = true; this.divlist.Visible = false; } } }
protected void rptCategories_ItemDataBound(object sender, RepeaterItemEventArgs e) { #region Big Categories //this section requires the categories to be ordered by the "sort" column if (!(Request.QueryString["menu"] == "PapaJohns")) { if (e.Item.ItemIndex == 0) { e.Item.FindControl("plhBigCategoryStart").Visible = true; currentBigCategory = ((DataRowView)e.Item.DataItem)["food_type_meal"].ToString(); } else if (currentBigCategory != ((DataRowView)e.Item.DataItem)["food_type_meal"].ToString() && ((DataRowView)e.Item.DataItem)["food_type_meal"].ToString() == "A") { // sort column makes sure this is done at last item being bound e.Item.FindControl("plhBigCategoryEnd").Visible = true; e.Item.FindControl("plhNormalHeader").Visible = false; e.Item.FindControl("plhBigHeader").Visible = true; currentBigCategory = ((DataRowView)e.Item.DataItem)["food_type_meal"].ToString(); } else if (currentBigCategory != ((DataRowView)e.Item.DataItem)["food_type_meal"].ToString()) { e.Item.FindControl("plhBigCategoryMiddle").Visible = true; currentBigCategory = ((DataRowView)e.Item.DataItem)["food_type_meal"].ToString(); } } #endregion Repeater rpt = ((Repeater)e.Item.FindControl("rptFood")); EnumerableRowCollection <DataRow> selectedRows = MenuData[0].AsEnumerable().Where(row => row["food_type_id_fk"].ToString() == ((DataRowView)e.Item.DataItem)["food_type_id_pk"].ToString()) .OrderBy(row => row["food_name"].ToString()); if (selectedRows.Count() > 0) { rpt.DataSource = selectedRows.CopyToDataTable(); rpt.DataBind(); } }
private void BuildAllowdModuleCheckBox() { StackPanel OuterStock = new StackPanel(); //OuterStock. = new Thickness(0, 5, 15, -25); dhModule objModule = new dhModule(); // if (objUser != null) // { #region "Allowed Menu" // objModule.AllowdModule = Globalized.objAppPreference.VEnableModules; dsGeneral.dtPosModuleDataTable dtm = iFacede.GetModule(Globalized.ObjDbName, objModule); EnumerableRowCollection <dsGeneral.dtPosModuleRow> result2 = from r in dtm.AsEnumerable() where (r.Field <int>("iModuleParentID") == 0) select r; int CountRow = result2.Count <dsGeneral.dtPosModuleRow>(); if (CountRow > 0) { DataTable dtResult2 = result2.CopyToDataTable(); ObservableCollection <dhModule> sequence = ReflectionUtility.DataTableToObservableCollection <dhModule>(dtResult2); if (sequence.Count > 0) { foreach (dhModule Module in sequence) { GroupBox objGroup = new GroupBox(); objGroup.Header = Module.VDisplayName; objGroup.Margin = new Thickness(0, 0, 0, 15); #region "Grid" Grid grid = new Grid(); // grid.Width = 400; // Create column definitions. ColumnDefinition columnDefinition1 = new ColumnDefinition(); ColumnDefinition columnDefinition2 = new ColumnDefinition(); columnDefinition1.Width = new GridLength(200); columnDefinition2.Width = new GridLength(200); // Attached definitions to grid. grid.ColumnDefinitions.Add(columnDefinition1); grid.ColumnDefinitions.Add(columnDefinition2); CheckBox cb = new CheckBox(); cb.Name = Module.VModuleName.ToString() + Module.IModuleID.ToString(); cb.Content = Module.VDisplayName.ToString(); // cb.Width = 100; cb.Margin = new Thickness(5, 5, 5, 5); cb.DataContext = Module; Binding myBinding = new Binding("BIsActive"); myBinding.Source = Module; cb.SetBinding(CheckBox.IsCheckedProperty, myBinding); cb.Tag = Module; cb.AddHandler(CheckBox.CheckedEvent, new RoutedEventHandler(DynamicCheckBox_Checked)); cb.AddHandler(CheckBox.UncheckedEvent, new RoutedEventHandler(DynamicCheckBox_Unchecked)); RowDefinition rowDefinition1 = new RowDefinition(); rowDefinition1.Height = new GridLength(30); grid.RowDefinitions.Add(rowDefinition1); grid.Children.Add(cb); Grid.SetColumn(cb, 0); Grid.SetRow(cb, 0); #endregion "Grid" EnumerableRowCollection <dsGeneral.dtPosModuleRow> Filter = from r2 in dtm.AsEnumerable() where (r2.Field <int>("iModuleParentID") == Module.IModuleID) select r2; int CountRowInner = result2.Count <dsGeneral.dtPosModuleRow>(); if (CountRowInner > 0) { DataTable dtFilter; if (Filter.Count() > 0) { dtFilter = Filter.CopyToDataTable(); } else { dtFilter = new DataTable(); } ObservableCollection <dhModule> sequencefilter = ReflectionUtility.DataTableToObservableCollection <dhModule>(dtFilter); if (sequencefilter.Count > 0) { // int Bool = 1; int row = 1; int col = 0; RowDefinition rowDefinition = new RowDefinition(); rowDefinition.Height = new GridLength(30); grid.RowDefinitions.Add(rowDefinition); #region "Child Module" foreach (dhModule SubModule in sequencefilter) { //for (int i = 0; i < sequencefilter.Count ; i++) //{ CheckBox NestedCb = new CheckBox(); //NestedStp.Orientation = Orientation.Horizontal; NestedCb.Name = "Sub" + SubModule.VModuleName.ToString() + SubModule.IModuleID.ToString(); NestedCb.Content = SubModule.VDisplayName.ToString(); NestedCb.Margin = new Thickness(35, 5, 5, 5); if (col == 2) { row = row + 1; col = 0; RowDefinition rowDefinition3 = new RowDefinition(); rowDefinition3.Height = new GridLength(30); grid.RowDefinitions.Add(rowDefinition3); } NestedCb.DataContext = Module; Binding mySubBinding = new Binding("BIsActive"); mySubBinding.Source = SubModule; NestedCb.SetBinding(CheckBox.IsCheckedProperty, mySubBinding); NestedCb.Tag = SubModule; grid.Children.Add(NestedCb); Grid.SetColumn(NestedCb, col); Grid.SetRow(NestedCb, row); col += 1; } #endregion "Child Module" objGroup.Content = grid; } OuterStock.Children.Add(objGroup); } } RightsGrid.Children.Add(OuterStock); } } #endregion "Allowed Menu" // } }
private void loadData(dhAccount objAccount, Boolean ShowResultCount = false) { dsGeneral.dtPosAccountsDataTable dtAccounts = iFacede.GetAccount(Globalized.ObjDbName, objAccount); EnumerableRowCollection <dsGeneral.dtPosAccountsRow> Filter = null; sequence.Clear(); sequence = ReflectionUtility.DataTableToObservableCollection <dhAccount>(dtAccounts); //if (Globalized.ObjCurrentUser.VUserType == "Super") //{ // Filter = from r in dtAccounts.AsEnumerable() // where (r.Field<int>("VUserType") == Module.IModuleID) && (r.Field<bool>("bIsActive") == true) // select r; //} //if (Globalized.ObjCurrentUser.VUserType == "Owner") //{ // Filter = from r in dtAccounts.AsEnumerable() // where (r.Field<string>("VUserType") == "Admin") || (r.Field<string>("VUserType") == "AccountIn") || (r.Field<string>("VUserType") == "Owner") // select r; //} //if (Globalized.ObjCurrentUser.VUserType == "Admin") //{ // Filter = from r in dtAccounts.AsEnumerable() // where ((r.Field<string>("VUserType") == "Admin") && (r.Field<int>("iAccountid") == Globalized.ObjCurrentUser.IAccountid) ) || (r.Field<string>("VUserType") == "AccountIn") // select r; //} //if (Globalized.ObjCurrentUser.VUserType == "AccountIn") //{ // Filter = from r in dtAccounts.AsEnumerable() // where (r.Field<string>("VUserType") == "AccountIn") && (r.Field<int>("iAccountid") == Globalized.ObjCurrentUser.IAccountid) // select r; //} if (Filter != null) { int CountRowInner = Filter.Count <dsGeneral.dtPosAccountsRow>(); if (CountRowInner > 0) { DataTable dtFilter = Filter.CopyToDataTable(); sequence = ReflectionUtility.DataTableToObservableCollection <dhAccount>(dtFilter); } else { sequence = null; } } AccountList.ItemsSource = sequence; //pageControl.PageContract = null; //pageControl.PageContract = this; // pageControl_PreviewPageChange(null, null); // Globalized.ShowMsg(lblErrorMsg); //if (Globalized.lastMsg != null) //{ // //System.Windows.Forms.MessageBox.Show( ); // lblErrorMsg.Content = Globalized.lastMsg; // lblErrorMsg.Visibility = System.Windows.Visibility.Visible; // Globalized.lastMsg = null; //} //else //{ // lblErrorMsg.Content = ""; // lblErrorMsg.Visibility = System.Windows.Visibility.Hidden; //} // show msg for local search if ((ShowResultCount) && (sequence != null)) { String msg = String.Format(" {0} Search Results Found", sequence.Count); //pageControl.ReLoad(); Globalized.setException(msg, lblErrorMsg, DataHolders.MsgType.Info); Globalized.ShowMsg(lblErrorMsg); } else { if (sequence == null) { String msg = String.Format(" {0} Search Results Found", 0); Globalized.setException(msg, lblErrorMsg, DataHolders.MsgType.Info); Globalized.ShowMsg(lblErrorMsg); } if (ShowResultCount == false) { lblErrorMsg.Visibility = Visibility.Hidden; } } }
private void getData() { int id_object = (int)cmbObject.SelectedValue; new Task(() => { Config.DoOnUIThread(() => { fBlocker.SaveControlsEnabledState(this); fBlocker.SetControlsEnabled(this, false); fWait = new Nwuram.Framework.UI.Forms.frmLoad(); fWait.TopMost = false; fWait.Owner = this; fWait.TextWait = "Загружаю данные из базы!"; fWait.Show(); dgvData.DataSource = null; }, this); Task <DataTable> task = Config.hCntMain.GetListOwe(id_object); task.Wait(); dtData = task.Result; if (dtData == null || dtData.Rows.Count == 0) { Config.DoOnUIThread(() => { dgvData.DataSource = null; fWait.Dispose(); fBlocker.RestoreControlEnabledState(this); }, this); return; } dtData.Columns.Add("SummaPaymentFine_1", typeof(decimal)); dtData.Columns.Add("SummaFine_1", typeof(decimal)); dtData.Columns.Add("SummaPenny_1", typeof(decimal)); dtData.Columns.Add("PrcPenny_1", typeof(decimal)); dtData.Columns.Add("SummaPaymentFine_1_filter", typeof(decimal)); dtData.Columns.Add("SummaPaymentFine_2", typeof(decimal)); dtData.Columns.Add("SummaFine_2", typeof(decimal)); dtData.Columns.Add("SummaPenny_2", typeof(decimal)); dtData.Columns.Add("PrcPenny_2", typeof(decimal)); task = Config.hCntMain.GetListOweAdditionalData(1, id_object); task.Wait(); if (task.Result != null && task.Result.Rows.Count > 0) { initDateType1(task.Result); } DateTableType1 = dtData.Copy(); dtData.Clear(); task = Config.hCntMain.GetListOweAdditionalData(2, id_object); task.Wait(); if (task.Result != null && task.Result.Rows.Count > 0) { EnumerableRowCollection <DataRow> rowsCOl = task.Result.AsEnumerable().Where(r => r.Field <decimal>("PrcPenny_2") > 0); if (rowsCOl.Count() > 0) { DateTableType2 = rowsCOl.CopyToDataTable(); } //DateTableType2 = task.Result.AsEnumerable().Where(r => r.Field<decimal>("PrcPenny_2") > 0).CopyToDataTable(); //foreach (DataRow row in task.Result.Rows) //{ // EnumerableRowCollection<DataRow> rowCollect = dtData.AsEnumerable().Where(r => r.Field<int>("id") == (int)row["id"]); // if (rowCollect.Count() > 0) // { // rowCollect.First()["SummaPaymentFine_2"] = row["SummaPaymentFine"]; // rowCollect.First()["SummaFine_2"] = row["SummaFine"]; // rowCollect.First()["SummaPenny_2"] = row["SummaPenny"]; // rowCollect.First()["PrcPenny_2"] = Math.Round((decimal)row["PrcPenny"], 0); // } //} } Config.DoOnUIThread(() => { ChangeTableForType(); fWait.Dispose(); fBlocker.RestoreControlEnabledState(this); setFilter(); dgvData.DataSource = dtData; }, this); }).Start(); }
protected DataTable GetSearchResults() { OdbcConnectionClass3 jicsConn = helper.CONNECTION_JICS; DataTable dtResults = new DataTable(); Exception exResults = null; #region Dynamically build search SQL //Get the offices and task which are active for the current year/session DataTable dtOfficeTask = ciHelper.GetOfficeAndTask(); string sqlSelect = "", sqlFrom = "", sqlWhere = ""; //Loop through each task foreach (DataRow dr in dtOfficeTask.Rows) { string viewColumn = dr["ViewColumn"].ToString(); string tableAlias = viewColumn.Replace("_", ""); //Using ToTitleCase() to capitalize each word in the column alias TextInfo textinfo = new CultureInfo("en-US", false).TextInfo; string columnAlias = textinfo.ToTitleCase(viewColumn.Replace('_', ' ')); //Build SELECT portion of SQL statment sqlSelect = String.Format("{0}, {1}.TaskStatus AS '{2}'", sqlSelect, tableAlias, columnAlias); //Build JOINS for SQL statement sqlFrom = String.Format(@"{0} LEFT JOIN CI_StudentProgress {1} ON U.ID = {1}.UserID AND {1}.TaskID = (SELECT TaskID FROM CI_OfficeTask WHERE ViewColumn = '{2}') AND {1}.Yr = {3} AND {1}.Sess = '{4}'" , sqlFrom, tableAlias, viewColumn, helper.ACTIVE_YEAR, helper.ACTIVE_SESSION); //Get the collection of radio buttons which correspond to the current task List <RadioButton> radioForTask = GetRadioGroup(tblOffices, String.Format("Task{0}", viewColumn)); //Was a radio button other than "Any" selected for this task? RadioButton selectedRadio = radioForTask.FirstOrDefault(rb => rb.Checked == true && !rb.ID.EndsWith("*")); if (radioForTask.Contains(selectedRadio)) { //Because some columns address multiple statuses (Y/W, N/P, etc), turn each status into an item in a list and format it for the SQL statement string status = ""; List <string> statusList = selectedRadio.ID.Split('_').Last().Select(chr => chr.ToString()).ToList(); foreach (string stat in statusList) { status = String.Format("{0}{1}'{2}'", status, String.IsNullOrWhiteSpace(status) ? "" : ",", stat); } sqlWhere = String.Format("{0} AND {1}.TaskStatus IN ({2})", sqlWhere, tableAlias, status); } } #endregion string sqlResults = String.Format(@" SELECT CAST(CAST(U.HostID AS INT) AS VARCHAR(10)) AS HostID, U.LastName AS 'Last Name', U.FirstName AS 'First Name', U.Email, '' AS 'Admit Year', '' AS 'Admit Term', '' AS ClassCode, '' AS AcademicStanding{0} FROM CI_StudentMetaData SMD INNER JOIN FWK_User U ON SMD.UserID = U.ID {1} WHERE SMD.ActiveYear = {3} AND SMD.ActiveSession = '{4}' AND SMD.IsActive = 1 {2} ORDER BY U.LastName, U.FirstName, U.Email ", sqlSelect, sqlFrom, sqlWhere, helper.ACTIVE_YEAR, helper.ACTIVE_SESSION); if (PortalUser.Current.IsSiteAdmin) { ciHelper.FormatException("Formatted SQL for faceted search", new Exception("No problem, just testing log"), null, null, null, LogEventType.Error, LogScreen.FacetSearch, sqlResults, helper.ACTIVE_YEAR, helper.ACTIVE_SESSION); this.ParentPortlet.ShowFeedback(FeedbackType.Message, sqlResults); } try { dtResults = jicsConn.ConnectToERP(sqlResults, ref exResults); if (exResults != null) { throw exResults; } //////////////////////////////////////////////////////////////////// #region Faceted Search - Standing if (!String.IsNullOrWhiteSpace(this.ddlStanding.SelectedValue)) { OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_SP; DataTable dtStanding = null; Exception exStanding = null; string sqlStanding = String.Format("EXECUTE PROCEDURE ci_admin_facetedsearch_newstudent({0}, '{1}', '{2}')", helper.ACTIVE_YEAR, helper.ACTIVE_SESSION, this.ddlStanding.SelectedValue); // string sqlStanding = String.Format(@" // SELECT // TRIM(host_id) AS id // FROM // jenzcst_rec // WHERE // status_code {0} IN ('PFF','PTR') // GROUP BY // id // ", (this.ddlStanding.SelectedValue == "N" ? "NOT" : "")); try { dtStanding = cxConn.ConnectToERP(sqlStanding, ref exStanding); if (exStanding != null) { throw exStanding; } if (dtStanding != null && dtStanding.Rows.Count > 0) { List <string> standingIDs = dtStanding.AsEnumerable().Select(standing => standing.Field <int>("student_id").ToString()).ToList(); var filteredRows = from row in dtResults.AsEnumerable() where standingIDs.Contains(row.Field <string>("HostID")) select row; dtResults = filteredRows == null || filteredRows.Count() == 0 ? new DataTable() : filteredRows.CopyToDataTable(); } } catch (Exception ex) { //this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while filtering facet search based on standing", ex, null, true)); this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while filtering facet search based on standing", ex, null, null, null, LogEventType.Error, LogScreen.FacetSearch, sqlStanding)); } finally { if (cxConn.IsNotClosed()) { cxConn.Close(); } } } #endregion #region Faceted Search - Athletics List <ListItem> selectedSports = lbAthletics.Items.Cast <ListItem>().Where(item => item.Selected == true).ToList(); List <string> athleteIDs = new List <string>() { }; if (selectedSports.Count > 0) { OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_SP; foreach (ListItem sport in selectedSports) { DataTable dtAthletics = null; Exception exAthletics = null; string sqlAthletics = String.Format("EXECUTE PROCEDURE ci_admin_facetedsearch_athletics_roster(?)"); List <OdbcParameter> paramAthletics = new List <OdbcParameter>() { new OdbcParameter("involve_code", sport.Value) }; try { dtAthletics = cxConn.ConnectToERP(sqlAthletics, ref exAthletics, paramAthletics); if (exAthletics != null) { throw exAthletics; } if (dtAthletics != null && dtAthletics.Rows.Count > 0) { athleteIDs = athleteIDs.Union <string>(dtAthletics.AsEnumerable().Select(row => row.Field <string>("id")).ToList()).ToList(); } } catch (Exception ex) { ciHelper.FormatException("An error occurred while building list of athlete IDs for the faceted search.", ex, null, null, null, LogEventType.Error, LogScreen.FacetSearch, sqlAthletics); } } if (cxConn.IsNotClosed()) { cxConn.Close(); } var filteredRows = from row in dtResults.AsEnumerable() where athleteIDs.Contains(row.Field <string>("HostID")) select row; dtResults = filteredRows == null || filteredRows.Count() == 0 ? new DataTable() : filteredRows.CopyToDataTable(); } // if (selectedSports.Count > 0) // { // string athleticsList = String.Format("'{0}'", String.Join("','", selectedSports.Select(li => li.Value).ToList())); // OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_LIVE; // DataTable dtAthletics = null; // Exception exAthletics = null; // try // { // string sqlAthletics = String.Format(@" // SELECT // TRIM(IR.id::varchar(10)) AS id // FROM // involve_rec IR INNER JOIN invl_table IT ON TRIM(IR.invl) = TRIM(IT.invl) // AND IT.sanc_sport = 'Y' // WHERE // TODAY BETWEEN IR.beg_date AND NVL(IR.end_date, TODAY) // AND // IT.invl IN ({0}) // GROUP BY // IR.id // ", athleticsList); // dtAthletics = cxConn.ConnectToERP(sqlAthletics, ref exAthletics); // if (exAthletics != null) { throw exAthletics; } // List<string> athleteIDs = dtAthletics.AsEnumerable().Select(athlete => athlete.Field<string>("id")).ToList(); // var filteredRows = from row in dtResults.AsEnumerable() // where athleteIDs.Contains(row.Field<string>("HostID")) // select row; // dtResults = filteredRows == null || filteredRows.Count() == 0 ? new DataTable() : filteredRows.CopyToDataTable(); // } // catch (Exception ex) // { // this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while filtering facet search based on athletics", ex, null, true)); // } // finally // { // if (cxConn.IsNotClosed()) { cxConn.Close(); } // } // } #endregion #region Faceted Search - Residency List <ListItem> selectedResidency = cblResidency.Items.Cast <ListItem>().Where(li => li.Selected).ToList(); if (selectedResidency.Count > 0) { OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_LIVE; DataTable dtResidency = null; Exception exResidency = null; string residencyList = String.Format("'{0}'", String.Join("','", selectedResidency.Select(li => li.Value).ToList())); string sqlResidency = String.Format(@" SELECT TRIM(SSR.id::varchar(10)) AS id FROM stu_serv_rec SSR WHERE SSR.yr = {0} AND SSR.sess = '{1}' AND SSR.intend_hsg IN ({2}) ", helper.ACTIVE_YEAR, helper.ACTIVE_SESSION, residencyList); try { dtResidency = cxConn.ConnectToERP(sqlResidency, ref exResidency); if (exResidency != null) { throw exResidency; } List <string> residentIDs = dtResidency.AsEnumerable().Select(res => res.Field <string>("id")).ToList(); var filteredRows = from row in dtResults.AsEnumerable() where residentIDs.Contains(row.Field <string>("HostID")) select row; dtResults = filteredRows == null || filteredRows.Count() == 0 ? new DataTable() : filteredRows.CopyToDataTable(); } catch (Exception ex) { this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException(String.Format("<p>Error while filtering facet search based on residency</p><p>{0}</p>", sqlResidency), ex, null, true)); } finally { if (cxConn.IsNotClosed()) { cxConn.Close(); } } } #endregion #region Faceted Search - Grad Candidacy if (!String.IsNullOrWhiteSpace(this.ddlGradCandidacy.SelectedValue)) { OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_SP; DataTable dtGrad = null; Exception exGrad = null; try { //string sqlGrad = String.Format("SELECT student_id FROM cc_stg_undergrad_candidacy WHERE datecreated >= TO_DATE('{0}', '%Y-%m-%d')", helper.START_DATE); string sqlGrad = String.Format("EXECUTE PROCEDURE ci_admin_facetedsearch_undergradcandidacy('{0}')", helper.START_DATE); dtGrad = cxConn.ConnectToERP(sqlGrad, ref exGrad); if (exGrad != null) { throw exGrad; } if (dtGrad != null && dtGrad.Rows.Count > 0) { //The "student_id" field coming back from CX is an int so it needs to be re-cast as a string for the LINQ comparisons below List <string> gradIDs = dtGrad.AsEnumerable().Select(grad => grad.Field <int>("student_id").ToString()).ToList(); //Generally "filteredRows" would be of type "var" but because it is initialized with null and used in one of the two branches, a data type must be specified EnumerableRowCollection <DataRow> filteredRows = null; if (this.ddlGradCandidacy.SelectedValue == "Y") { filteredRows = from row in dtResults.AsEnumerable() where gradIDs.Contains(row.Field <string>("HostID")) select row; } else { filteredRows = from row in dtResults.AsEnumerable() where !gradIDs.Contains(row.Field <string>("HostID")) select row; } dtResults = filteredRows == null || filteredRows.Count() == 0 ? new DataTable() : filteredRows.CopyToDataTable(); } } catch (Exception ex) { this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while filtering search on graduation candidacy.", ex, null, true)); } finally { if (cxConn.IsNotClosed()) { cxConn.Close(); } } } #endregion #region Include Additional Fields if (dtResults != null && dtResults.Rows.Count > 0) { //Loop through all rows in the recordset. Processing occurs at this point because the recordset is in its smallest state since all filtering has been completed. for (int ii = 0; ii < dtResults.Rows.Count; ii++) { //Establish database connection OdbcConnectionClass3 cxSpConn = helper.CONNECTION_CX_SP; //Initialize query variables DataTable dtPER = null; Exception exPER = null; string sqlPER = String.Format(@"EXECUTE PROCEDURE ci_admin_facetedsearch_extrafields({0})", dtResults.Rows[ii]["HostID"].ToString()); try { dtPER = cxSpConn.ConnectToERP(sqlPER, ref exPER); if (exPER != null) { throw exPER; } if (dtPER != null && dtPER.Rows.Count > 0) { DataRow dr = dtPER.Rows[0]; dtResults.Rows[ii]["Admit Year"] = dr["adm_yr"].ToString(); dtResults.Rows[ii]["Admit Term"] = dr["adm_sess"].ToString(); dtResults.Rows[ii]["ClassCode"] = dr["cl"].ToString(); dtResults.Rows[ii]["AcademicStanding"] = dr["acad_stat"].ToString(); } } catch (Exception ex) { //ciHelper.FormatException("Could not load program enrollment data in faceted search.", ex); ciHelper.FormatException("Could not load program enrollment data in faceted search.", ex, null, null, null, LogEventType.Error, LogScreen.FacetSearch, sqlPER); } finally { //Always close database connection if (cxSpConn.IsNotClosed()) { cxSpConn.Close(); } } } } #endregion //////////////////////////////////////////////////////////////////// } catch (Exception ex) { //this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("An exception occurred while running the faceted search.", ex)); this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("An exception occurred while running the faceted search.", ex, null, null, null, LogEventType.Error, LogScreen.FacetSearch)); } finally { if (jicsConn.IsNotClosed()) { jicsConn.Close(); } } return(dtResults); }
private void btnAdd_Click(object sender, EventArgs e) { if (!validateToSave()) { return; } EnumerableRowCollection <DataRow> rowColl = dtGroups.AsEnumerable().Where(r => (r.Field <bool>("selected") && !r.Field <bool>("isAdded"))); if (rowColl.Count() > 0) { dictonaryCategory.frmNeedCatGrp frm = new dictonaryCategory.frmNeedCatGrp() { dtGroups = rowColl.CopyToDataTable(), id_dep = int.Parse(cbDeps.SelectedValue.ToString()) }; frm.ShowDialog(); if (!frm.isAdd) { return; } } string selectedGroups = ""; foreach (DataRow dr in dtGroups.Rows) { if (dr["selected"].ToString() == "True") { selectedGroups += dr["id"].ToString() + ","; } } if (selectedGroups.Length == 0) { MessageBox.Show("Должна быть выбрана хотя бы одна группа", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } selectedGroups = selectedGroups.Substring(0, selectedGroups.Length - 1); Thread t = new Thread(new ParameterizedThreadStart(setData)); t.IsBackground = true; t.Start(selectedGroups); ShowForm(); /* Task<DataTable> task = Config.hCntMain.SetTovarByGroup(selectedGroups, dtpRealiz.Value, * decimal.Parse(tbMin.Text), decimal.Parse(tbMax.Text), decimal.Parse(tbStep.Text), decimal.Parse(tbDefault.Text), * tbPriceSuffix.Text, tbQuantitySuffix.Text, int.Parse(cbDeps.SelectedValue.ToString())); * * * * task.Wait();*/ // MessageBox.Show("Товары добавлены", "Информация", MessageBoxButtons.OK, MessageBoxIcon.Information); /*while (!(t.ThreadState == ThreadState.Aborted)) * { * * }*/ /* while (t.IsAlive) * { * Console.WriteLine("поток запущен"); * }*/ // dgvGroups_Init(); //CloseForm(); /* #region LOG * Logging.StartFirstLevel(1587); * Logging.Comment("Начало добавление новых товаров"); * Logging.Comment("Дата реализации: " + dtpRealiz.Value.ToString() + ", " + * "Отдел id: " + cbDeps.SelectedValue.ToString() + ", наименование: " + cbDeps.Text); * foreach (DataRow dr in dtGroups.Rows) * { * if (dr["selected"].ToString() == "True") * Logging.Comment("id инв. группы: " + dr["id"].ToString() + ", наименование группы: " + dr["cname"].ToString()); * } * if (dtAddedRows!=null && dtAddedRows.Rows.Count >0) * { * foreach (DataRow dr in dtAddedRows.Rows) * Logging.Comment($"id_tovar: {dr["id_tovar"]}, ean: {dr["ean"]}, Наименование: {dr["cname"]}"); * } * Logging.StopFirstLevel();*/ }
protected void Page_PreRender(object sender, EventArgs e) { string menu = "PG"; this.plhCreateYourOwnPizza.Visible = false; if (Request.QueryString["menu"] == "PapaJohns") { menu = "PJ"; this.Title = "Menu | Papa John's"; this.plhCreateYourOwnPizza.Visible = true; } // must select where either "PG" or "PJ" and manage food type meals (PG only) DataTable categories = MenuData[1]; //add a sort column categories.Columns.Add("sort"); categories.Columns.Add("sort2"); //put values in the sort column (these are for PG foods, look up the DB data) foreach (DataRow row in categories.Rows) { if (row["food_type_meal"].ToString() == "B") { row["sort"] = "1"; } else if (row["food_type_meal"].ToString() == "L") { row["sort"] = "2"; } else if (row["food_type_meal"].ToString() == "A") { row["sort"] = "3"; } switch (row["food_type_name"].ToString()) { case "Desserts": row["sort2"] = "16"; break; case "Breakfast Sandwiches": row["sort2"] = "03"; break; case "Breakfast Sides": row["sort2"] = "05"; break; case "Bakery": row["sort2"] = "04"; break; case "Beverages": row["sort2"] = "01"; break; case "Burgers": row["sort2"] = "06"; break; case "Soups & Salads": row["sort2"] = "11"; break; case "Sandwiches": row["sort2"] = "07"; break; case "Paninis": row["sort2"] = "08"; break; case "Quesadillas": row["sort2"] = "10"; break; case "Wraps": row["sort2"] = "09"; break; case "Appetizers": row["sort2"] = "02"; break; case "Ice Cream": row["sort2"] = "15"; break; case "Classic Pizzas": row["sort2"] = "12"; break; case "Create Your Own Pizza": row["sort2"] = "17"; break; case "Specialty Pizzas": row["sort2"] = "13"; break; case "Papa Johns Sides": row["sort2"] = "14"; break; } } //create the data used ie. SELECT ft.*, CASE ... END AS sort FROM food_type WHERE food_type_vendor = :menu AND food_type_name = 'Create Your Own Pizza' ORDER BY sort EnumerableRowCollection <DataRow> selectedRows = categories.AsEnumerable().Where(row => row["food_type_vendor"].ToString() == menu && row["food_type_name"].ToString() != "Create Your Own Pizza") .OrderBy(row => row["sort"].ToString() + " " + row["sort2"].ToString()); //bind data to repeater if (selectedRows.Count() > 0) { this.rptCategories.DataSource = selectedRows.CopyToDataTable(); this.rptCategories.DataBind(); } // make temp detail table with added column of foodIDs DataTable detailsAndCorrespondingFoodIDs = MenuData[3]; detailsAndCorrespondingFoodIDs.Columns.Add("FoodIDs"); detailsAndCorrespondingFoodIDs.Columns.Add("sort"); // find foodIDs corresponding to a details (yes the backward relationship) // do this because we need to match the food clicked with what details correspond and show them later foreach (DataRow row in detailsAndCorrespondingFoodIDs.Rows) { row["FoodIDs"] = " "; // preceeding space used to match foreach (DataRow row2 in MenuData[2].Rows) { if (row["detail_id_pk"].ToString() == row2["detail_id_fk"].ToString()) { row["FoodIDs"] += row2["food_id_fk"].ToString() + " "; // following space used to match } } // order the details // 'extras' make their parents sort row["sort"] = row["group_name"].ToString(); if (row["group_name"].ToString().Contains("X")) { foreach (DataRow row2 in detailsAndCorrespondingFoodIDs.Rows) { if (row2["detail_id_pk"].ToString() == row["group_name"].ToString().Substring(0, row["group_name"].ToString().IndexOf("X")) && string.IsNullOrEmpty(row2["group_name"].ToString())) { row2["sort"] = row["group_name"].ToString(); } } } } // use temp detail table with added column of foodIDs if (detailsAndCorrespondingFoodIDs.Rows.Count > 0) { this.rptDetailList.DataSource = detailsAndCorrespondingFoodIDs.AsEnumerable().OrderBy(row => row["sort"]).CopyToDataTable(); this.rptDetailList.DataBind(); } }
private void btPrint_Click(object sender, EventArgs e) { int id_typeDoc = (int)cmbTypeDoc.SelectedValue; EnumerableRowCollection <DataRow> rowCollect = dtPostVsDeps.AsEnumerable().Where(r => r.Field <bool>("isSelect")); if (!chbAllDepsAndPosts.Checked && rowCollect.Count() == 0) { MessageBox.Show("Необходимо выбрать должность и отдел!", "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } Task <DataTable> task = Config.hCntMain.getReportInformationUser(id_typeDoc); task.Wait(); if (task.Result == null || task.Result.Rows.Count == 0) { MessageBox.Show("Нет данных для отчёта!", "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } DataTable dtReport = task.Result.Clone(); if (!chbAllDepsAndPosts.Checked) { foreach (DataRow row in rowCollect) { EnumerableRowCollection <DataRow> rowToReport = task.Result.AsEnumerable().Where(r => r.Field <int>("id_Departments") == (int)row["id_Departments"] && r.Field <int>("id_Posts") == (int)row["id_Posts"]); if (rowToReport.Count() > 0) { dtReport.Merge(rowToReport.CopyToDataTable()); } } } else if (UserSettings.User.StatusCode.ToLower().Equals("ркв")) { foreach (DataRow row in dtDepsLinkToDep.Rows) { EnumerableRowCollection <DataRow> rowToReport = task.Result.AsEnumerable().Where(r => r.Field <int>("id_Departments") == (int)row["id_DepartmentsView"]); if (rowToReport.Count() > 0) { dtReport.Merge(rowToReport.CopyToDataTable()); } } } else { dtReport = task.Result.Copy(); } if (dtReport.Rows.Count == 0) { MessageBox.Show("Нет данных для отчёта!", "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } Nwuram.Framework.ToExcelNew.ExcelUnLoad report = new Nwuram.Framework.ToExcelNew.ExcelUnLoad(); int indexRow = 1; int maxColumn = 5; report.Merge(indexRow, 1, indexRow, maxColumn); report.AddSingleValue("Документы, ожидающие ознакомления", indexRow, 1); report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumn); report.SetFontBold(indexRow, 1, indexRow, maxColumn); report.SetFontSize(indexRow, 1, indexRow, maxColumn, 16); indexRow++; report.Merge(indexRow, 1, indexRow, maxColumn); report.AddSingleValue($"Выгрузил:{Nwuram.Framework.Settings.User.UserSettings.User.FullUsername}", indexRow, 1); report.SetCellAlignmentToRight(indexRow, 1, indexRow, maxColumn); indexRow++; report.Merge(indexRow, 1, indexRow, maxColumn); report.AddSingleValue($"Дата выгрузки:{DateTime.Now.ToShortDateString()} {DateTime.Now.ToShortTimeString()}", indexRow, 1); report.SetCellAlignmentToRight(indexRow, 1, indexRow, maxColumn); indexRow++; indexRow++; report.AddSingleValue("№", indexRow, 1); report.AddSingleValue("Отдел", indexRow, 2); report.AddSingleValue("Должность", indexRow, 3); report.AddSingleValue("Наименование документа", indexRow, 4); report.AddSingleValue("Дата отправки на ознакомление", indexRow, 5); report.SetBorders(indexRow, 1, indexRow, maxColumn); report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumn); report.SetWrapText(indexRow, 1, indexRow, maxColumn); report.SetFontBold(indexRow, 1, indexRow, maxColumn); report.SetColumnWidth(indexRow, 1, indexRow, 1, 6); report.SetColumnWidth(indexRow, 2, indexRow, 2, 25); report.SetColumnWidth(indexRow, 3, indexRow, 3, 25); report.SetColumnWidth(indexRow, 4, indexRow, 4, 50); report.SetColumnWidth(indexRow, 5, indexRow, 5, 20); indexRow++; var groupDeps = dtReport.AsEnumerable().GroupBy(r => new { id_Departments = r.Field <int>("id_Departments"), nameDep = r.Field <string>("nameDep") }) .Select(s => new { s.Key.id_Departments, s.Key.nameDep }); int npp = 1; foreach (var gDep in groupDeps) { int startIndexGroupDeps = indexRow; var groupPost = dtReport.AsEnumerable() .Where(r => r.Field <int>("id_Departments") == gDep.id_Departments) .GroupBy(r => new { id_Posts = r.Field <int>("id_Posts"), namePost = r.Field <string>("namePost") }) .Select(s => new { s.Key.id_Posts, s.Key.namePost }); foreach (var gPost in groupPost) { int startIndexGroupPost = indexRow; rowCollect = dtReport.AsEnumerable() .Where(r => r.Field <int>("id_Departments") == gDep.id_Departments && r.Field <int>("id_Posts") == gPost.id_Posts); foreach (DataRow row in rowCollect) { report.AddSingleValue($"{npp}", indexRow, 1); report.AddSingleValue($"{row["cName"]}", indexRow, 4); report.AddSingleValue($"{((DateTime)row["DateEdit"]).ToShortDateString()}", indexRow, 5); report.SetCellAlignmentToCenter(indexRow, 4, indexRow, 5); report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 1); report.SetWrapText(indexRow, 4, indexRow, 4); report.SetBorders(indexRow, 1, indexRow, 5); indexRow++; npp++; } report.Merge(startIndexGroupPost, 3, indexRow - 1, 3); report.AddSingleValue($"{gPost.namePost}", startIndexGroupPost, 3); report.SetCellAlignmentToCenter(startIndexGroupPost, 3, startIndexGroupPost, 3); report.SetCellAlignmentToJustify(startIndexGroupPost, 3, startIndexGroupPost, 3); report.SetWrapText(startIndexGroupPost, 3, startIndexGroupPost, 3); report.SetBorders(startIndexGroupPost, 3, startIndexGroupPost, 3); } report.Merge(startIndexGroupDeps, 2, indexRow - 1, 2); report.AddSingleValue($"{gDep.nameDep.Trim()}", startIndexGroupDeps, 2); report.SetCellAlignmentToCenter(startIndexGroupDeps, 2, startIndexGroupDeps, 2); report.SetCellAlignmentToJustify(startIndexGroupDeps, 2, startIndexGroupDeps, 2); report.SetWrapText(startIndexGroupDeps, 2, startIndexGroupDeps, 2); report.SetBorders(startIndexGroupDeps, 2, startIndexGroupDeps, 2); } report.Show(); }
public static void createReport(int id_hardware, DateTime startDate, DateTime endDate, string strType, int type, List <string> listFilter) { DataTable dtReport = new DataTable(); if (id_hardware != 0) { dtReport = readSQL.getReportHistory(id_hardware, null, null, null); } else { dtReport = readSQL.getReportHistory(id_hardware, startDate, endDate, type); } if (dtReport == null) { MessageBox.Show("Нет данных для выгрузки!", "Информирование");; return; } if (dtReport.Rows.Count == 0) { MessageBox.Show("Нет данных для выгрузки!", "Информирование");; return; } if (listFilter != null) { EnumerableRowCollection <DataRow> rowsFind = dtReport.AsEnumerable().Where(r => listFilter.Contains(r.Field <string>("cName").Trim())); if (rowsFind.Count() == 0) { MessageBox.Show("Нет данных для выгрузки!", "Информирование");; return; } dtReport = rowsFind.CopyToDataTable(); } Nwuram.Framework.ToExcelNew.ExcelUnLoad report = new Nwuram.Framework.ToExcelNew.ExcelUnLoad(); int indexRow = 1; string[] listTitle = new string[] { "Инв. номер", @"Оборудование\Комплектующее", @"Наименование оборуд.\комплект.", @"Дата добавления оборудования", "Параметр", "Значение До", "Значение после", "Время изменения", "Кто изменил" }; report.Merge(indexRow, 1, indexRow, listTitle.Length); report.AddSingleValue("Отчёт по изменению в оборудовании", indexRow, 1); report.SetFontSize(indexRow, 1, indexRow, 1, 16); report.SetFontBold(indexRow, 1, indexRow, 1); indexRow++; if (id_hardware == 0) { report.Merge(indexRow, 1, indexRow, listTitle.Length); report.AddSingleValue("Период с " + startDate.ToShortDateString() + " по " + endDate.ToShortDateString() + "", indexRow, 1); indexRow++; report.Merge(indexRow, 1, indexRow, listTitle.Length); report.AddSingleValue("Условие: " + strType, indexRow, 1); indexRow++; } report.Merge(indexRow, 1, indexRow, listTitle.Length); report.AddSingleValue("Дата и время выгрузки: " + DateTime.Now.ToString(), indexRow, 1); indexRow++; report.Merge(indexRow, 1, indexRow, listTitle.Length); report.AddSingleValue("Выгрузил: " + Nwuram.Framework.Settings.User.UserSettings.User.FullUsername, indexRow, 1); indexRow++; indexRow++; int startPos = indexRow; for (int i = 0; i < listTitle.Length; i++) { report.AddSingleValue(listTitle[i], indexRow, i + 1); report.SetBorders(indexRow, i + 1, indexRow, i + 1); report.SetCellAlignmentToCenter(indexRow, i + 1, indexRow, i + 1); } indexRow++; //EnumerableRowCollection<DataRow> var rowCollect = from hosp in dtReport.AsEnumerable() group hosp by new { id_jHardware = hosp["id_jHardware"], id_Creator = hosp["id_Creator"], DateCreate = hosp["DateCreate"] } into grp orderby grp.Key.id_jHardware ascending, grp.Key.DateCreate ascending select new { id_jHardware = grp.Key.id_jHardware, id_Creator = grp.Key.id_Creator, DateCreate = grp.Key.DateCreate, }; foreach (var rC in rowCollect) { DataRow[] row = dtReport.Select(string.Format("id_jHardware = {0} and id_Creator = {1} AND DateCreate = '{2}'", rC.id_jHardware, rC.id_Creator, rC.DateCreate)); report.Merge(indexRow, 1, indexRow + row.Count() - 1, 1); report.Merge(indexRow, 2, indexRow + row.Count() - 1, 2); report.Merge(indexRow, 3, indexRow + row.Count() - 1, 3); report.Merge(indexRow, 8, indexRow + row.Count() - 1, 8); report.Merge(indexRow, 9, indexRow + row.Count() - 1, 9); report.AddSingleValue(row[0]["InventoryNumber"].ToString(), indexRow, 1); report.AddSingleValue(row[0]["TypeComponentsHardware"].ToString(), indexRow, 2); report.AddSingleValue(row[0]["nameOb"].ToString(), indexRow, 3); report.AddSingleValue(row[0]["DateCreate"].ToString(), indexRow, 8); report.AddSingleValue(row[0]["FIO"].ToString(), indexRow, 9); report.SetCellAlignmentToJustify(indexRow, 1, indexRow, 9); report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 1); report.SetCellAlignmentToCenter(indexRow, 2, indexRow, 2); report.SetCellAlignmentToCenter(indexRow, 3, indexRow, 3); report.SetCellAlignmentToCenter(indexRow, 8, indexRow, 8); report.SetCellAlignmentToCenter(indexRow, 9, indexRow, 9); foreach (DataRow r in row) { report.AddSingleValue(r["DateCreateHardWare"].ToString(), indexRow, 4); report.AddSingleValue(r["cName"].ToString(), indexRow, 5); report.AddSingleValue(r["valueOld"].ToString(), indexRow, 6); report.AddSingleValue(r["valueNew"].ToString(), indexRow, 7); report.SetCellAlignmentToCenter(indexRow, 4, indexRow, 4); if ((bool)r["isDeleteRow"]) { report.SetCellColor(indexRow, 1, indexRow, listTitle.Length, Color.Green); } report.SetBorders(indexRow, 1, indexRow, listTitle.Length); indexRow++; } } indexRow++; report.SetCellColor(indexRow, 1, indexRow, 1, Color.Green); report.AddSingleValue("Удалённые записи", indexRow, 2); /* * foreach (DataRow r in dtReport.Rows) * { * report.AddSingleValue(r["InventoryNumber"].ToString(), indexRow, 1); * report.AddSingleValue(r["TypeComponentsHardware"].ToString(), indexRow, 2); * report.AddSingleValue(r["nameOb"].ToString(), indexRow, 3); * report.AddSingleValue(r["cName"].ToString(), indexRow, 4); * report.AddSingleValue(r["valueOld"].ToString(), indexRow, 5); * report.AddSingleValue(r["valueNew"].ToString(), indexRow, 6); * report.AddSingleValue(r["DateCreate"].ToString(), indexRow, 7); * report.AddSingleValue(r["FIO"].ToString(), indexRow, 8); * report.SetBorders(indexRow, 1, indexRow, listTitle.Length); * indexRow++; * } */ report.SetColumnAutoSize(startPos, 1, indexRow, listTitle.Length); report.Show(); }
private void get_data() { btMOLChange.Enabled = false; int indexRow = 0; if (dgvData.CurrentRow != null && dgvData.CurrentRow.Index != -1) { indexRow = dgvData.CurrentRow.Index; } DataGridViewColumn oldCol = dgvData.SortedColumn; ListSortDirection direction = ListSortDirection.Ascending; if (oldCol != null) { if (dgvData.SortOrder == System.Windows.Forms.SortOrder.Ascending) { direction = ListSortDirection.Ascending; } else { direction = ListSortDirection.Descending; } } if (dtData != null && isStatusFirst) { EnumerableRowCollection <DataRow> rowCollect = dtData.AsEnumerable() .Where(r => r.Field <bool>("isSelect")); if (rowCollect.Count() > 0) { dtSelected = rowCollect.CopyToDataTable(); } } dgvData.AutoGenerateColumns = false; dtData = readSQL.getListHardWare(); if (isStatusFirst && dtSelected != null && dtData != null && dtData.Rows.Count > 0 && dtSelected.Rows.Count > 0) { foreach (DataRow row in dtSelected.Rows) { EnumerableRowCollection <DataRow> rowCollect = dtData.AsEnumerable() .Where(r => r.Field <int>("id") == (int)row["id"]); if (rowCollect.Count() > 0) { rowCollect.First()["isSelect"] = true; } } dtData.DefaultView.RowFilter = ""; dtData.DefaultView.Sort = "isSelect desc"; dtData = dtData.DefaultView.ToTable().Copy(); dtSelected = null; } genResponsible(); filter(); dgvData.DataSource = dtData; dgvData_SelectionChanged(null, null); if (oldCol != null) { dgvData.Sort(oldCol, direction); oldCol.HeaderCell.SortGlyphDirection = direction == ListSortDirection.Ascending ? System.Windows.Forms.SortOrder.Ascending : System.Windows.Forms.SortOrder.Descending; } try { if (dgvData.Rows.Count > 0) { dgvData.Rows[indexRow].Selected = true; dgvData.CurrentCell = dgvData.Rows[indexRow].Cells[cName.Index]; } } catch (Exception ex) { } }