private void UpdateClient() { if (dsUsage.Tables.Contains("Client")) { dsUsage.Tables.Remove(dsUsage.Tables["Client"]); } DateTime sDate = pp1.SelectedPeriod; DateTime eDate = sDate.AddMonths(1); SqlDataAdapter daClient = new SqlDataAdapter("Client_Select", cnSselData); daClient.SelectCommand.CommandType = CommandType.StoredProcedure; daClient.SelectCommand.Parameters.AddWithValue("@Action", "All"); daClient.SelectCommand.Parameters.AddWithValue("@sDate", sDate); daClient.SelectCommand.Parameters.AddWithValue("@eDate", eDate); daClient.Fill(dsUsage, "Client"); DataColumn[] pkc = new DataColumn[1]; pkc[0] = dsUsage.Tables["Client"].Columns["ClientID"]; dsUsage.Tables["Client"].PrimaryKey = pkc; if (sDate <= DateTime.Now.Date && eDate > DateTime.Now.Date && !Convert.ToBoolean(Session["Updated"])) { WriteData mWriteData = new WriteData(Provider); string[] types = new string[] { "Tool" }; mWriteData.UpdateTables(BillingCategory.Tool, UpdateDataType.DataClean); Session["Updated"] = false; } }
private void ShowReport() { DateTime sDate = pp1.SelectedPeriod; DateTime eDate = sDate.AddMonths(1); Dictionary <string, DataGrid> grids = new Dictionary <string, DataGrid> { { "DemGender", dgGender }, { "DemRace", dgRace }, { "DemEthnic", dgEthnic }, { "DemDisability", dgDisability }, { "DemCitizen", dgCitizen } }; foreach (var kvp in grids) { DataGrid dg = kvp.Value; dg.DataSource = null; dg.DataBind(); } if (sDate > DateTime.Now.Date) { return; } //draw from RoomData - need to upadte tool first due to apportionment if (sDate <= DateTime.Now && eDate > DateTime.Now && !ContextBase.Updated()) { try { // to handle missing data error WriteData writeData = new WriteData(Provider); writeData.UpdateTables(BillingCategory.Tool | BillingCategory.Room); } catch { } ContextBase.Updated(true); } //room info var dtRoom = DataCommand() .Param("Action", "PassbackRooms") .FillDataTable("dbo.Room_Select"); foreach (var kvp in grids) { string type = kvp.Key; var ds = DataCommand() .Param("Action", "DemReport") .Param("demType", type) .Param("sDate", sDate) .Param("eDate", eDate) .FillDataSet("dbo.Dem_Select"); var dtDemInfo = ds.Tables[0]; var dtDemCat = ds.Tables[1]; var dtDemRep = new DataTable(); dtDemRep.Columns.Add("RoomID", typeof(int)); dtDemRep.Columns.Add("Room", typeof(string)); dtDemRep.Columns.Add("Total", typeof(double)); foreach (DataRow drDemCat in dtDemCat.Rows) { dtDemRep.Columns.Add(drDemCat[type].ToString(), typeof(double)); } foreach (DataRow drRoom in dtRoom.Rows) { DataRow nr = dtDemRep.NewRow(); nr["RoomID"] = drRoom["RoomID"]; nr["Room"] = drRoom["Room"]; dtDemRep.Rows.Add(nr); } foreach (DataRow drDemRep in dtDemRep.Rows) { drDemRep["Total"] = 0.0; foreach (DataRow drDemCat in dtDemCat.Rows) { DataRow[] fdr = dtDemInfo.Select(string.Format("RoomID = {0} AND {1} = '{2}'", drDemRep["RoomID"], type, drDemCat[type])); if (fdr.Length > 0) { drDemRep[drDemCat[type].ToString()] = fdr[0]["Hours"]; drDemRep["Total"] = drDemRep.Field <double>("Total") + fdr[0].Field <double>("Hours"); } else { drDemRep[drDemCat[type].ToString()] = 0.0; } } } dtDemRep.Columns.Remove(dtDemRep.Columns["RoomID"]); DataGrid dg = kvp.Value; dg.DataSource = dtDemRep; dg.DataBind(); ds = null; dtDemRep = null; } }
private void ShowLabAccess() { dgAccess.DataSource = null; dgAccess.DataBind(); ClientPrivilege selectedPriv = cblPriv.Items.CalculatePriv(); if (selectedPriv == 0) { return; } if (pp1.SelectedPeriod > DateTime.Now) { return; } if (pp1.SelectedPeriod <= DateTime.Now && pp1.SelectedPeriod.AddMonths(1) > DateTime.Now && !ContextBase.Updated()) { try { // to handle missing data error WriteData writeData = new WriteData(Provider); writeData.UpdateTables(BillingCategory.Tool | BillingCategory.Room); ContextBase.Updated(true); } catch (Exception ex) { SendEmail.SendDebugEmail(CurrentUser.ClientID, "sselIndReports.AggSumUsage.ShowLabAccess", "An error occurred in WriteData.UpdateTables while trying to display the Aggregate User Summary Report.", ex); ContextBase.Updated(false); } } //get all access data for period // [2021-04-19 jg] This report is only availabe to Administrator privilege so it doesn't make sense to filter to a single org. // var orgId = DBNull.Value; var userOrgId = Convert.ToInt32(rblUserOrg.SelectedValue); DataSet ds = new DataSet(); using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnSselData"].ConnectionString)) using (var cmd = new SqlCommand("sselData.dbo.AggUsage_Select", conn) { CommandType = CommandType.StoredProcedure }) using (var adap = new SqlDataAdapter(cmd)) { cmd.Parameters.AddWithValue("Period", pp1.SelectedPeriod, SqlDbType.DateTime); cmd.Parameters.AddWithValue("Privs", (int)selectedPriv, SqlDbType.Int); if (userOrgId >= 0) { cmd.Parameters.AddWithValue("SelOrg", userOrgId, SqlDbType.Int); } //if (CurrentUser.HasPriv(ClientPrivilege.Executive)) // cmd.Parameters.AddWithValue("OrgID", orgId, SqlDbType.Int); adap.Fill(ds); } var dtRaw = ds.Tables[0]; var dtRoom = ds.Tables[1]; var dtUsage = new DataTable(); dtUsage.Columns.Add("ClientID", typeof(int)); dtUsage.Columns.Add("DisplayName", typeof(string)); dtUsage.Columns.Add("Manager", typeof(string)); dtUsage.Columns.Add("URLdata", typeof(string)); foreach (DataRow dr in dtRoom.Rows) { dtUsage.Columns.Add(EntriesColumn(dr), typeof(double)); if (Convert.ToBoolean(dr["PassbackRoom"])) { dtUsage.Columns.Add(HoursColumn(dr), typeof(double)); } } int dateInt; //used to prevent casual snooping into the indDet page int temp = 0; //store a temporary value to test if it's zero DataRow nr; DataRow[] fdr; foreach (DataRow dr in dtRaw.Rows) { fdr = dtUsage.Select(string.Format("ClientID = {0}", dr["ClientID"])); if (fdr.Length == 0) //add row to table { nr = dtUsage.NewRow(); nr["ClientID"] = dr["ClientID"]; nr["DisplayName"] = dr["DisplayName"]; nr["Manager"] = dr["Manager"]; dateInt = 200 * (pp1.SelectedYear + Convert.ToInt32(dr["ClientID"])) + 3 * pp1.SelectedMonth + (Convert.ToInt32(dr["ClientID"]) % 10); nr["URLdata"] = string.Format("URLdata={0:0000}{1}", dr["ClientID"], dateInt); dtUsage.Rows.Add(nr); } else { nr = fdr[0]; } bool isPassbackRoom = false; DataRow[] rows = dtRoom.Select(string.Format("RoomID = {0}", dr["RoomID"])); isPassbackRoom = Convert.ToBoolean(rows[0]["PassbackRoom"]); //2008-10-22 Sanrine wants to see non antipassback room, now we have DC lab and Organics Bay if (Convert.ToDouble(dr["Hours"]) > 0 && isPassbackRoom) { nr[EntriesColumn(dr)] = dr["Entries"]; nr[HoursColumn(dr)] = dr["Hours"]; } else if (!isPassbackRoom) { //for non Passbackroom, the Hours entry is null, Entries values are still valid temp = Convert.ToInt32(dr["Entries"]); if (temp != 0) { nr[EntriesColumn(dr)] = temp; } } } //this is the footer row nr = dtUsage.NewRow(); nr["ClientID"] = 0; nr["DisplayName"] = "zzzzzzzz"; nr["Manager"] = string.Empty; nr["URLdata"] = string.Empty; foreach (DataRow dr in dtRoom.Rows) { var entriesCol = EntriesColumn(dr); var hoursCol = HoursColumn(dr); var sumEntries = dtUsage.Compute($"SUM({entriesCol})", string.Empty); nr[entriesCol] = sumEntries; if (Convert.ToBoolean(dr["PassbackRoom"])) { var sumHours = dtUsage.Compute($"SUM({hoursCol})", string.Empty); nr[hoursCol] = sumHours; } } dtUsage.Rows.Add(nr); BoundField displayNameCol = new BoundField(); displayNameCol.HeaderStyle.HorizontalAlign = HorizontalAlign.Center; displayNameCol.HeaderStyle.Font.Bold = true; displayNameCol.HeaderText = "Lab User"; displayNameCol.ItemStyle.HorizontalAlign = HorizontalAlign.Center; dgAccess.Columns.Add(displayNameCol); BoundField managerCol = new BoundField(); managerCol.HeaderStyle.HorizontalAlign = HorizontalAlign.Center; managerCol.HeaderStyle.Font.Bold = true; managerCol.ItemStyle.HorizontalAlign = HorizontalAlign.Center; managerCol.HtmlEncode = false; managerCol.HeaderText = "Manager"; managerCol.DataField = "Manager"; dgAccess.Columns.Add(managerCol); foreach (DataRow dr in dtRoom.Rows) { BoundField entryCol = new BoundField(); entryCol.HeaderStyle.Width = Unit.Pixel(120); entryCol.HeaderStyle.HorizontalAlign = HorizontalAlign.Center; entryCol.HeaderStyle.Font.Bold = true; entryCol.ItemStyle.Width = Unit.Pixel(120); entryCol.ItemStyle.HorizontalAlign = HorizontalAlign.Center; entryCol.DataFormatString = "{0:0.#}"; entryCol.HeaderText = dr["Room"].ToString() + " Entries"; entryCol.DataField = EntriesColumn(dr); dgAccess.Columns.Add(entryCol); if (Convert.ToBoolean(dr["PassbackRoom"])) { BoundField hoursCol = new BoundField(); hoursCol.HeaderStyle.Width = Unit.Pixel(120); hoursCol.HeaderStyle.HorizontalAlign = HorizontalAlign.Center; hoursCol.HeaderStyle.Font.Bold = true; hoursCol.ItemStyle.Width = Unit.Pixel(120); hoursCol.ItemStyle.HorizontalAlign = HorizontalAlign.Center; hoursCol.DataFormatString = "{0:0.##}"; hoursCol.HeaderText = dr["Room"].ToString() + " Hours"; hoursCol.DataField = HoursColumn(dr); dgAccess.Columns.Add(hoursCol); } } dtUsage.DefaultView.Sort = "DisplayName"; dgAccess.DataSource = dtUsage; dgAccess.DataBind(); }
private DataTable GetData() { DateTime sDate = pp1.SelectedPeriod; DateTime eDate = sDate.AddMonths(Convert.ToInt32(txtMonths.Text)); //IList<AccountType> allAccountTypes = DA<AccountType>.All(); if (sDate > DateTime.Now.Date) { return(null); } if (sDate <= DateTime.Now.Date && eDate > DateTime.Now.Date && !Convert.ToBoolean(Session["Updated"])) { WriteData writeData = new WriteData(Provider); writeData.UpdateTables(BillingCategory.Tool, UpdateDataType.DataClean); Session["Updated"] = true; } if (eDate > DateTime.Now.Date) { eDate = DateTime.Now.Date.AddDays(1); // otherwise percentages are skewed } totalHours = eDate.Subtract(sDate).TotalHours; // create table to store data bool includeForgiven = rdoCharged.Checked ? chkIncludeForgiven.Checked : true; bool showPercentages = chkShowPercentOfTotal.Checked; hidShowPercentOfTotal.Value = showPercentages.ToString().ToLower(); string sumcol = GetStatsBasedOn(); DataTable dt = GetToolUtilizationData(sumcol, includeForgiven, sDate, eDate, ReportPage.GetSelectedAccountTypes(cblAccountType)); foreach (DataRow dr in dt.Rows) { dr["IdleTime"] = totalHours; for (int i = 1; i <= dt.Columns.Count - 2; i++) { dr["IdleTime"] = Convert.ToDouble(dr["IdleTime"]) - Utility.ConvertTo(dr[i], 0D); } if (Convert.ToDouble(dr["IdleTime"]) < 0.0) { dr["IdleTime"] = 0.0; } } Dictionary <string, double> totalColumns = new Dictionary <string, double>(); activities = DataSession.Query <Activity>().Where(x => x.IsActive).ToArray(); totalColumns.Add("Lab", 0); totalColumns.Add("ProcessTech", 0); totalColumns.Add("ResourceID", 0); foreach (var act in activities) { totalColumns.Add(act.ActivityName, 0); } totalColumns.Add("IdleTime", 0); List <string> keys = new List <string>(totalColumns.Keys); foreach (DataRow row in dt.Rows) { foreach (string k in keys) { double value = Utility.ConvertTo(row[k], 0D); totalColumns[k] += Utility.Round(value, 1); } } rowCount = dt.Rows.Count; totalRow = dt.NewRow(); totalRow["ResourceName"] = "Total"; foreach (KeyValuePair <string, double> kvp in totalColumns) { totalRow[kvp.Key] = kvp.Value; } return(dt); }