Exemplo n.º 1
0
def category(request, c_name):
    if c_name == 'favicon.ico':
       return Response()
    user = request.client_user_object
    user_id = user.user_id
    args = json.dumps(request.args)
    categories = (session.query(Category)
                  .join(
          AppFamilyPermission,
          Category.category_id
          == AppFamilyPermission.category_id)
                  .filter(AppFamilyPermission.user_id == user_id)
                  .order_by(Category.category_display_order.asc()))
    sub_categories = (session.query(SubCategory)
                      .join(
          AppPermission,
          SubCategory.subcategory_id
          == AppPermission.subcategory_id)
                      .filter(AppPermission.user_id == user_id))
    breadcrumbs = (session.query(Category)
                   .filter(Category.category_name==c_name).all()[0])
    return render_template(
       'category.html',
       categories=categories,
       sub_categories=sub_categories,
       breadcrumbs=breadcrumbs,
       category_name=c_name,
       user=user,
       args=args)
Exemplo n.º 2
0
def setpermissions3(request):
   user = request.client_user_object
   user_id = user.user_id
   if request.method == 'POST':
      selectedsubcategory_ids = request.form.getlist('subcategory')
      session.query(AppPermission).filter(AppPermission.user_id==user_id).delete()
      for selectedsubcategory_id in selectedsubcategory_ids:
         selectedsubcategory = session.query(SubCategory).filter(SubCategory.subcategory_id==selectedsubcategory_id)
         newapppermission = AppPermission(user_id, selectedsubcategory_id)
         session.add(newapppermission)
      session.commit()
   else:
      return redirect(url_for('setpermissions'))
   return render_template('setpermissions3.html')
Exemplo n.º 3
0
def sub_category_proportion_chart_data_source(request, sc_name):
   user = request.client_user_object
   user_id = user.user_id
   org = request.client_organization_object
   org_namespace_name = org.organization_namespace_name
   this_sub_category = (session.query(SubCategory)
                        .filter(SubCategory.subcategory_name==sc_name)
                        .all()[0])
   sc_id = this_sub_category.subcategory_id
   issue_filter = request.args.get('filter')
   if not issue_filter:
       issue_filter = ''
   query = """SELECT (CASE WHEN iqp_problem_count_recent.problem_count > 0
                      THEN 'Issue' ELSE 'No_Issue' END) as issue_or_not,
                      COUNT(iqp_scenarios.scn_id) as issue_or_not_count
               FROM iqp_scenarios
               JOIN {namespace_name}.iqp_problem_count_recent
                    ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id)
               WHERE (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature')
                    OR '{filter_type}' IN ('Stats', 'N','Feature'))
               AND (iqp_scenarios.scn_type = '{filter_type}'
                    OR '{filter_type}' = '')
               AND iqp_scenarios.subcategory_id = '{sc_id}'
               GROUP BY issue_or_not
               """.format(
       namespace_name=org_namespace_name,
       sc_id=sc_id,
       filter_type=issue_filter)
   s = text(query)
   rs = session.execute(s).fetchall()
   data = ([[str(item['issue_or_not']),
              int(item['issue_or_not_count'])]
             for item in rs])
   result = json.dumps(data)
   return Response(result, mimetype='application/json')
Exemplo n.º 4
0
def scenario_trend_chart_data_source(request, s_name):
   user = request.client_user_object
   user_id = user.user_id
   org = request.client_organization_object
   org_namespace_name = org.organization_namespace_name
   s_name = str(s_name)
   s_name = escape(s_name)
   for scenario in session.query(Scenario).filter(Scenario.scn_name==s_name).all():
      this_scenario = scenario
   s_id = this_scenario.scn_id
   query = """SELECT iqp_problem_count.problem_time, iqp_problem_count.problem_count
              FROM {namespace_name}.iqp_problem_count
              WHERE iqp_problem_count.scn_id = '{s_id}'
              ORDER BY iqp_problem_count.problem_time DESC LIMIT 100 OFFSET 0""".format(
      namespace_name=org_namespace_name,
      s_id=s_id)
   s = text(query)
   rs = session.execute(s).fetchall()
   data = []
   for item in rs:
      data.append(
         [int(item['problem_time']*1000),
          int(item['problem_count'])])
   result = json.dumps(data)
   return Response(result, mimetype='application/json')
Exemplo n.º 5
0
def upload_query_result_count(request, scn_id):
   user = request.client_user_object
   user_id = user.user_id
   org = request.client_organization_object
   org_namespace_name = org.organization_namespace_name
   this_scenario = session.query(Scenario).filter(Scenario.scn_id==scn_id).all()[0]
   scn_name = this_scenario.scn_name
   scn_name_lower = scn_name.lower()
   if request.method == 'POST':
      query_result_count = request.form.get('query_result_count')

      ## decode JSON
      count_decoded = json.loads(query_result_count)
      
      ## insert count data into problem count table
      row_count = count_decoded['rowCount']
      new_count_query = """INSERT INTO {namespace_name}.iqp_problem_count
(Problem_Time, Scn_ID, Problem_Count)
VALUES ((select extract(epoch from now())), {scn_id}, {row_count})
""".format(namespace_name=org_namespace_name, scn_id=scn_id, row_count=row_count)
      new_count_s = text(new_count_query)
      session.execute(new_count_s)
      
      ## commit all changes to db
      session.commit()
   return render_template('welcome.html')
Exemplo n.º 6
0
def scenario_main_chart_options_data_source(request, s_name):
    user = request.client_user_object
    user_id = user.user_id
    org = request.client_organization_object
    org_namespace_name = org.organization_namespace_name
    s_name_lower = s_name.lower()
    for scenario in session.query(Scenario).filter(Scenario.scn_name==s_name).all():
       this_scenario = scenario
    s_id = this_scenario.scn_id
    data = {}
    query = """select frequent_column_name
                      from scenario_clicks_count
                      where scn_id = {sid} order by
                      frequency_number desc limit 5;""".format(sid = s_id)
    s = text(query)
    rs = session.execute(s).fetchall()
    data["most-five"] = [[str(item['frequent_column_name'])] for item in rs]
    query2 = """select column_name from INFORMATION_SCHEMA.COLUMNS
                where column_name not in (select frequent_column_name
                from scenario_clicks_count
                        order by frequency_number
                        desc limit 5)
                and table_name = '{scen_name_lower}'
                and table_schema = '{namespace_name}'
                    order by column_name;""".format(
       namespace_name=org_namespace_name,
       scen_name_lower=s_name_lower.lower())
    s2 = text(query2)
    rs2 = session.execute(s2).fetchall()
    data["others"] = [[str(item['column_name'])] for item in rs2]
    result = json.dumps(data)
    return Response(result, mimetype='application/json')
Exemplo n.º 7
0
def setpermissions2(request):
   user = request.client_user_object
   user_id = user.user_id
   if request.method == 'POST':
      selectedcategory_ids = request.form.getlist('category')
      session.query(AppFamilyPermission).filter(AppFamilyPermission.user_id==user_id).delete()
      for selectedcategory_id in selectedcategory_ids:
         selectedcategory = session.query(Category).filter(Category.category_id==selectedcategory_id)
         newappfamilypermission = AppFamilyPermission(user_id, selectedcategory_id)
         session.add(newappfamilypermission)
      session.commit()

      categories = session.query(Category).filter(Category.category_id.in_(selectedcategory_ids)).order_by(Category.category_display_order.asc()).all()
      subcategories = session.query(SubCategory, Category).join(Category, Category.category_id == SubCategory.category_id).all()
   else:
      return redirect(url_for('setpermissions'))
   return render_template('setpermissions2.html', categories=categories, subcategories=subcategories)
Exemplo n.º 8
0
 def client_organization_object(self):
     if not self.client_user_object:
         return None
     else:
         org_id = self.client_user_object.user_organization_id
         org_result = session.query(Organization).filter(Organization.organization_id==org_id).all()
         org_object = None
         for org in org_result:
             org_object = org
         return org_object
Exemplo n.º 9
0
 def wrapper(*args, **kwargs):
    request = args[0]
    user = request.client_user_object
    if not user:
       return redirect(url_for('login'))
    else:
       user_id = user.user_id
       subcategory_name = kwargs['sc_name']
       subcategoryquery = session.query(SubCategory).filter(SubCategory.subcategory_name==subcategory_name).all()
       if len(subcategoryquery) == 0:
          return redirect(url_for('overview'))
       else:
          subcategory_id = subcategoryquery[0].subcategory_id
          ## check user permission here
          apppermisionquery = session.query(AppPermission).filter(AppPermission.user_id==user_id).filter(AppPermission.subcategory_id==subcategory_id).all()
          if len(apppermisionquery) == 0:
             return redirect(url_for('setpermissions'))
          else:
             result = fn(*args, **kwargs)
             return result
Exemplo n.º 10
0
 def wrapper(*args, **kwargs):
    request = args[0]
    user = request.client_user_object
    if not user:
       return redirect(url_for('login'))
    else:
       user_id = user.user_id
       category_name = kwargs['c_name']
       categoryquery = session.query(Category).filter(Category.category_name==category_name).all()
       if len(categoryquery) == 0:
          return redirect(url_for('overview'))
       else:
          category_id = categoryquery[0].category_id
          ## check user permission here
          appfamilypermisionquery = session.query(AppFamilyPermission).filter(AppFamilyPermission.user_id==user_id).filter(AppFamilyPermission.category_id==category_id).all()
          if len(appfamilypermisionquery) == 0:
             return redirect(url_for('setpermissions'))
          else:
             result = fn(*args, **kwargs)
             return result
Exemplo n.º 11
0
def scenario_main_chart_data_source(request, s_name):
   user = request.client_user_object
   user_id = user.user_id
   org = request.client_organization_object
   org_namespace_name = org.organization_namespace_name
   groupby = request.args.get('groupby')
   data = {}
   if groupby:
      query = """SELECT {group_by}, COUNT(*) as groupsum
                 FROM {namespace_name}.{scen_name}
                 GROUP BY {group_by}""".format(
                         namespace_name=org_namespace_name,
                         scen_name=s_name,
                         group_by=groupby) 
      s = text(query)
      rs = session.execute(s).fetchall()
      data['groupby'] = groupby
      data['data'] = [[str(item[groupby]), int(item['groupsum'])] for item in rs]
   else:
      scenario = session.query(Scenario).filter(Scenario.scn_name==s_name).all()[0]
      scenario_id = scenario.scn_id
      s_name_lower = s_name.lower()
      query1 = """select frequent_column_name
                  from scenario_clicks_count where scn_id = {s_id}
                  order by frequency_number desc limit 1;""".format(s_id=scenario_id)
      s1 = text(query1)
      mostly_used = session.execute(s1).fetchall()
      if mostly_used:
          query = """SELECT {mostly_used}, COUNT(*) as groupsum
                     FROM {namespace_name}.{scen_name}
                     GROUP BY {mostly_used}""".format(
             namespace_name=org_namespace_name,
             scen_name=s_name,
             mostly_used=mostly_used[0][0])
      else:
          query = """SELECT COUNT(*) as groupsum
                     FROM {namespace_name}.{scen_name}""".format(
             namespace_name=org_namespace_name,
             scen_name=s_name) 
      s = text(query)
      rs = session.execute(s).fetchall()
      if mostly_used:
          data['groupby'] = mostly_used[0][0]
          data['data'] = ([[str(item[mostly_used[0][0]]),
                            int(item['groupsum'])]
                           for item in rs])
      else:
          data['groupby'] = 'All Rows'
          data['data'] = ([[str('All Rows'),
                            int(item['groupsum'])]
                           for item in rs])
   result = json.dumps(data)
   return Response(result, mimetype='application/json')
Exemplo n.º 12
0
def overview(request):
    user = request.client_user_object
    user_id = user.user_id
    args = json.dumps(request.args)
    categories = (session.query(Category)
                  .join(
          AppFamilyPermission,
          Category.category_id
          == AppFamilyPermission.category_id)
                  .filter(AppFamilyPermission.user_id == user_id)
                  .order_by(Category.category_display_order.asc()))
    sub_categories = (session.query(SubCategory)
                      .join(
          AppPermission,
          SubCategory.subcategory_id
          == AppPermission.subcategory_id)
                      .filter(AppPermission.user_id == user_id))
    return render_template(
       'overview.html',
       categories=categories,
       sub_categories=sub_categories,
       user=user,
       args=args)
Exemplo n.º 13
0
def upload_query_result_data(request, scn_id):
   user = request.client_user_object
   user_id = user.user_id
   org = request.client_organization_object
   org_namespace_name = org.organization_namespace_name
   this_scenario = session.query(Scenario).filter(Scenario.scn_id==scn_id).all()[0]
   scn_name = this_scenario.scn_name
   scn_name_lower = scn_name.lower()
   if request.method == 'POST':
      query_result_data = request.form.get('query_result_data')

      ## decode JSON
      data_decoded = json.loads(query_result_data)

      ## get column names for table
      column_names_query = """SELECT DISTINCT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '{scn_name_lower}'
AND table_schema = '{namespace_name}'
""".format(namespace_name=org_namespace_name, scn_name_lower=scn_name_lower)
      column_names_s = text(column_names_query)
      column_names_result = session.execute(column_names_s).fetchall()
      column_names = [row[0] for row in column_names_result]

      if len(column_names) > 0:
         ## insert all data into the new table
         def columnValueIfExists(columnName):
            if columnName not in field_values:
               return "null"
            elif field_values[columnName] == '\x00':
               return "null"
            else:
               return "'" + field_values[columnName].replace("'", "''") + "'"
         rows = data_decoded['rows']
         for row in rows:
            field_values = row['fieldValues']
            column_names_string = ','.join([columnName for columnName in column_names])
            column_values_string = ','.join([columnValueIfExists(columnName) for columnName in column_names])
            
            ## make insert statement
            insert_query = """INSERT INTO {namespace_name}.{scn_name_lower}""".format(namespace_name=org_namespace_name, scn_name_lower=scn_name_lower)
            insert_query = insert_query + """({column_names_string}) VALUES""".format(column_names_string=column_names_string)
            insert_query = insert_query + "(" + column_values_string + ")"
            insert_s = text(insert_query)
            session.execute(insert_s)

         ## commit all changes to db
         session.commit()
   return render_template('welcome.html')
Exemplo n.º 14
0
 def client_user_object(self):
     if 'user_id' not in self.client_session:
         return None
     else:
         user_id = self.client_session['user_id']
         user_result = session.query(User).filter(User.user_id==user_id).all()
         user_object = None
         for user in user_result:
             user_object = user
         if not user_object:
             del self.client_session["user_id"]
             self.client_session.modified
             return None
         else:
             return user_object
Exemplo n.º 15
0
def login(request):
   c = request.client_session
   if request.method == 'POST':
      email = request.form.get('email')
      email = escape(email)
      password = request.form.get('password')
      password = escape(password)
      hashedpassword = hashlib.md5(password).hexdigest()
      userlogin = session.query(User).filter(User.user_email==email).filter(User.user_password==hashedpassword).all()
      loggedinuser = None
      for user in userlogin:
         loggedinuser = user
      if loggedinuser:
         c["user_id"] = loggedinuser.user_id
         c.modified
         return redirect(url_for('overview'))
   return render_template('login.html')
Exemplo n.º 16
0
def category_main_chart_data_source(request, c_name):
    user = request.client_user_object
    user_id = user.user_id
    org = request.client_organization_object
    org_namespace_name = org.organization_namespace_name
    this_category = (session.query(Category)
                     .filter(Category.category_name==c_name)
                     .all()[0])
    c_id = this_category.category_id
    issue_filter = request.args.get('filter')
    if not issue_filter:
       issue_filter = ''
    query = """SELECT iqp_subcategories.subcategory_name,
                    COUNT(iqp_scenarios.scn_name) issue_count,
                    SUM(problem_count) as problemsum
                    FROM iqp_subcategories
                    JOIN iqp_scenarios ON (iqp_subcategories.subcategory_id
                        = iqp_scenarios.subcategory_id)
                    JOIN {namespace_name}.iqp_problem_count_recent
                        ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id)
                    JOIN app_permissions ON (iqp_subcategories.subcategory_id
                        = app_permissions.subcategory_id)
                    WHERE iqp_problem_count_recent.problem_count > 0
                    AND (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature')
                        OR '{filter_type}' IN ('Stats', 'N','Feature'))
                    AND (iqp_scenarios.scn_type = '{filter_type}'
                        OR '{filter_type}' = '')
                    AND iqp_subcategories.category_id = '{c_id}'
                    AND app_permissions.user_id = '{user_id}'
                    GROUP BY iqp_subcategories.subcategory_name
                    """.format(namespace_name=org_namespace_name,
                               c_id=c_id,
                               filter_type=issue_filter,
                               user_id=user_id)
    s = text(query)
    rs = session.execute(s).fetchall()
    data = ([[str(item['subcategory_name']),
            int(item['issue_count']),
            int(item['problemsum'])]
            for item in rs])
    result = json.dumps(data)
    return Response(result, mimetype='application/json')
Exemplo n.º 17
0
def upload_query_result_structure(request, scn_id):
   user = request.client_user_object
   user_id = user.user_id
   org = request.client_organization_object
   org_namespace_name = org.organization_namespace_name
   this_scenario = session.query(Scenario).filter(Scenario.scn_id==scn_id).all()[0]
   scn_name = this_scenario.scn_name
   scn_name_lower = scn_name.lower()
   if request.method == 'POST':

      def columnTypeIfExists(dataType):
         if dataType not in oracleToPostgresDataTypes:
            return "varchar"
         else:
            return oracleToPostgresDataTypes[dataType]

      ## get get argument
      query_result_structure = request.form.get('query_result_structure')

      ## decode JSON back into python list
      structure_decoded = json.loads(query_result_structure)

      ## drop old table in user namespace if it exists
      droptable_query = """DROP TABLE IF EXISTS {namespace_name}.{scn_name_lower}
""".format(namespace_name=org_namespace_name, scn_name_lower=scn_name_lower)
      droptable_s = text(droptable_query)
      session.execute(droptable_s)

      ## create new table in user namespace
      columns = structure_decoded['columns']
      column_definition_string = ','.join([column['name'] + ' ' + columnTypeIfExists(column['type']) for column in columns])
      newtable_query = """CREATE TABLE {namespace_name}.{scn_name_lower} (
""".format(namespace_name=org_namespace_name, scn_name_lower=scn_name_lower)
      newtable_query = newtable_query + column_definition_string + """)"""
      newtable_s = text(newtable_query)
      session.execute(newtable_s)

      ## commit all changes to db
      session.commit()
   return render_template('welcome.html')
Exemplo n.º 18
0
def generateDataTables(request,sc_id,issue_filter):
    data = {
    "sEcho": int(request.args["sEcho"]), # we should echo it with the same no, else datatable will not genrate a table
    "iTotalDisplayRecords": 10,
    }
    
    columns=['name', 'description' , 'current', 'stats_total', 'stats_percentage', 'trend', 'refreshtime']
    rowdata = []
    rowCounter = 0
    fromRow = 0
    toRow = 5
    sortByColumn = 0
    sortDir = "asc"
    
    if (request.args['iDisplayStart'] and request.args['iDisplayLength'] != '-1' ): # set the from and to
        fromRow = int(request.args['iDisplayStart'])
        toRow = int(fromRow + int(request.args['iDisplayLength']))
        
    # ordering
    if(request.args['iSortCol_0']):
        sortByColumn = int(request.args['iSortCol_0'])
        if(request.args['sSortDir_0']):
            sortDir = str(request.args['sSortDir_0'])
        
        
    if(request.args['sSearch']):
        jsonData = generateTablesWithSearch(request.args['sSearch'],data,sortByColumn,sortDir,columns)
    else: 
        for row in session.query(Scenario.scn_name.label("name"),Scenario.scn_short_description.label("desc"),Scenario.scn_type.label("type")).order_by(columns[sortByColumn])[fromRow:toRow]:
            rowdata.append([])
            rowdata[rowCounter].append(row.name)
            rowdata[rowCounter].append(row.desc)
            rowdata[rowCounter].append(row.type)
            rowCounter += 1
        jsonData = (rowdata,rowCounter)
        
    data["aaData"] = jsonData[0]
    data["iTotalRecords"] = jsonData[1]
    return json.dumps(data)
Exemplo n.º 19
0
def generateTablesWithSearch(search,data,sortByColumn,sortDir,columns):
    rowdata = []
    rowCounter = 0
    fromRow = 0
    toRow = 5
    aFilter1 = "lower(iqp_scenarios.scn_name) LIKE '%"+search+"%'"
    aFilter2 = "lower(iqp_scenarios.scn_short_description) LIKE '%"+search+"%'"
    aFilter3 = "lower(iqp_scenarios.scn_type) LIKE '%"+search+"%'"
    
    for row in session.query(Scenario.scn_name.label("name"),Scenario.scn_short_description.label("desc"),Scenario.scn_type.label("type")).filter(or_(aFilter1,aFilter2,aFilter3)).order_by(columns[sortByColumn])[fromRow:toRow]:
        rowdata.append([])
        rowdata[rowCounter].append(row.name)
        rowdata[rowCounter].append(row.desc)
        rowdata[rowCounter].append(row.type)
        rowCounter += 1
        
    return (rowdata,rowCounter) # here rowCounter will be the no of rows
    
        
    
    
    
Exemplo n.º 20
0
def register(request):
   c = request.client_session
   if request.method == 'POST':
      email = request.form.get('email')
      email = escape(email)
      password = request.form.get('password')
      password = escape(password)
      confirmpassword = request.form.get('confirmpassword')
      confirmpassword = escape(confirmpassword)
      firstname = request.form.get('firstname')
      firstname = escape(firstname)
      lastname = request.form.get('lastname')
      lastname = escape(lastname)
      companyname = request.form.get('companyname')
      companyname = escape(companyname)
      if password == confirmpassword:
         ## assign organizatio or create new one
         this_organization_result = session.query(Organization).filter(Organization.organization_name==companyname).all()
         if len(this_organization_result)>0:
            this_organization = this_organization_result[0]
            this_organization_id = this_organization.organization_id
         else:
            org_uuid = uuid.uuid1().hex
            namespace_name = companyname.strip().replace(' ', '') + org_uuid
            namespace_name = namespace_name.lower()
            new_organization = Organization(companyname, namespace_name)
            session.add(new_organization)
            query = """CREATE SCHEMA {u_namespace_name}
""".format(u_namespace_name=namespace_name)
            s = text(query)
            session.execute(s)
         ## create count table in user namespace
            query = """CREATE TABLE {u_namespace_name}.iqp_problem_count (
Problem_Time integer,
Scn_ID integer references IQP_Scenarios(Scn_ID),
Problem_Count integer,
PRIMARY KEY (Problem_Time, Scn_ID)
)
""".format(u_namespace_name=namespace_name)
            s = text(query)
            session.execute(s)
         ## create recent problem count view
            query = """CREATE VIEW {u_namespace_name}.IQP_Problem_Count_Recent AS
SELECT recent.problem_time, fulltable.scn_id, fulltable.problem_count
FROM {u_namespace_name}.iqp_problem_count fulltable
JOIN (
SELECT MAX(t1.problem_time) as problem_time, t1.scn_id
FROM {u_namespace_name}.iqp_problem_count t1
GROUP BY scn_id) recent
ON recent.problem_time = fulltable.problem_time
AND recent.scn_id = fulltable.scn_id
""".format(u_namespace_name=namespace_name)
            s = text(query)
            session.execute(s)
            
         ## create previous problem count view
            query = """CREATE VIEW {u_namespace_name}.IQP_Problem_Count_Prev AS
SELECT prev.problem_time, fulltable.scn_id, fulltable.problem_count
FROM {u_namespace_name}.iqp_problem_count fulltable
JOIN (
SELECT MAX(fulltable.problem_time) as problem_time, fulltable.scn_id
FROM {u_namespace_name}.iqp_problem_count fulltable
JOIN (
SELECT MAX(t1.problem_time) as problem_time, t1.scn_id
FROM {u_namespace_name}.iqp_problem_count t1
GROUP BY scn_id) prev
ON prev.scn_id = fulltable.scn_id
WHERE prev.problem_time > fulltable.problem_time
GROUP BY fulltable.scn_id
) prev
ON prev.problem_time = fulltable.problem_time
AND prev.scn_id = fulltable.scn_id
""".format(u_namespace_name=namespace_name)
            s = text(query)
            session.execute(s)
            this_organization = new_organization
            ## commit changes
            session.commit()
            this_organization_id = this_organization.organization_id
         
         ## create new user
         hashedpassword = hashlib.md5(password).hexdigest()
         new_user = User(email, hashedpassword, '0', firstname, lastname, this_organization_id, '0')
         session.add(new_user)
         ## commit changes
         session.commit()
         ## set user id in cookie
         c["user_id"] = new_user.user_id
         c.modified
         return redirect(url_for('overview'))
   return render_template('register.html')
Exemplo n.º 21
0
def get_scenario_query(request, scn_id):
   this_scenario = session.query(Scenario).filter(Scenario.scn_id==scn_id).all()[0]
   #data = [n for n in range(20)]
   data = [this_scenario.scn_query]
   result = json.dumps(data)
   return Response(result, mimetype='application/json')
Exemplo n.º 22
0
def get_all_scenarios(request):
   all_scenarios = session.query(Scenario).all()
   #data = [n for n in range(20)]
   data = [scenario.scn_id for scenario in all_scenarios]
   result = json.dumps(data)
   return Response(result, mimetype='application/json')
Exemplo n.º 23
0
def scenario(request, c_name, sc_name, s_name):
    user = request.client_user_object
    user_id = user.user_id
    org = request.client_organization_object
    org_namespace_name = org.organization_namespace_name
    args = json.dumps(request.args)
    categories = (session.query(Category)
                  .join(
          AppFamilyPermission,
          Category.category_id
          == AppFamilyPermission.category_id)
                  .filter(AppFamilyPermission.user_id == user_id)
                  .order_by(Category.category_display_order.asc()))
    sub_categories = (session.query(SubCategory)
                      .join(
          AppPermission,
          SubCategory.subcategory_id
          == AppPermission.subcategory_id)
                      .filter(AppPermission.user_id == user_id))
    breadcrumbs = (session.query(Category, SubCategory, Scenario)
                   .join(SubCategory, Category.category_id
                         == SubCategory.category_id)
                   .join(Scenario, SubCategory.subcategory_id
                         == Scenario.subcategory_id)
                   .filter(Category.category_name==c_name)
                   .filter(SubCategory.subcategory_name==sc_name)
                   .filter(Scenario.scn_name==s_name).all()[0])
    scenario = (session.query(Scenario)
                .filter(Scenario.scn_name==s_name)
                .all()[0])
    scn_short_des = scenario.scn_short_description
    scenario_id = scenario.scn_id
    s_name_lower = s_name.lower()
    groupby = request.args.get('groupby')
    if groupby:
       clicks_count_query = (
          """select insert_clicks({s_id},'{name}');"""
          .format(s_id = scenario_id,name = groupby))
       count_query = text(clicks_count_query)
       insert = session.execute(count_query).fetchall()
       session.commit()
       
    query1 = (
       """select frequent_column_name from scenario_clicks_count
          where scn_id = {s_id} order by frequency_number
          desc limit 5;""".format(s_id = scenario_id))
    s1 = text(query1)
    scenario_data_column_names_ordered1 = session.execute(s1).fetchall()
    query2 = (""" select column_name from INFORMATION_SCHEMA.COLUMNS
                  where column_name not in (
                    select frequent_column_name from scenario_clicks_count
                        where scn_id = {s_id} order by frequency_number desc limit 5)
                    and table_name = '{scen_name_lower}'
                    and table_schema = '{namespace_name}' order by column_name;"""
              .format(
          namespace_name=org_namespace_name,
          scen_name_lower=s_name_lower.lower(),
          s_id=scenario_id))
    s2 = text(query2)
    scenario_data_column_names_ordered2 = session.execute(s2).fetchall()
    return render_template(
       'scenario.html',
       categories=categories,
       sub_categories=sub_categories,
       breadcrumbs=breadcrumbs,
       scn_des=scn_short_des,
       scenario_name=s_name,
       scenario_data_column_names1=scenario_data_column_names_ordered1,
       scenario_data_column_names2=scenario_data_column_names_ordered2,
       user=user,
       args=args)
Exemplo n.º 24
0
def exportSubcategoryTable(request,sc_name):
    import csv
    #Response.headers['Content-Type'] = "application/CSV"
    #Response.headers['Content-Disposition'] = 'attachment; filename= sample.csv'
    d = Headers()
    #write the headers
    #d.add("Pragma", "public")
    #d.add("Expires","0")
    #d.add("Cache-Control", must-revalidate, post-check=0, pre-check=0")
    #d.add('Content-Type', "application/force-download")
    #d.add("Content-Type","application/octet-stream")
    d.add("Content-Type","application/octet-stream")
    d.add('Content-Disposition', 'attachment;filename=iqpgenerated.csv')
    headers = ["Scenario","Current Count","Total Count","Percentage of Total","Trend","Last Refreshed"]
    ofile  = open(_CSV_FILE_PATH, "wb")
    
    #write column names first
    writer = csv.writer(ofile, delimiter=',',quotechar='"', quoting=csv.QUOTE_ALL)
    writer.writerow(headers)
    
    user = request.client_user_object
    user_id = user.user_id
    org = request.client_organization_object
    org_namespace_name = org.organization_namespace_name
    this_sub_category = session.query(SubCategory).filter(SubCategory.subcategory_name==sc_name).all()[0]
    sc_id = this_sub_category.subcategory_id
    issue_filter = request.args.get('filter')
    if not issue_filter:
        issue_filter = ''
    query = """SELECT iqp_scenarios.scn_name as name,
                      iqp_scenarios.scn_short_description as description,
                      iqp_problem_count_recent.problem_count as current,
                      COALESCE(iqp_problem_count_prev.problem_count, 0) as prev,
                      (iqp_problem_count_recent.problem_count
                        - COALESCE(iqp_problem_count_prev.problem_count, 0)) as trend,
                      iqp_problem_count_recent.problem_time as refreshtime,
                      COALESCE(problem_count_stats.problem_count,999999999) as stats_total,
                      (iqp_problem_count_recent.problem_count
                        / COALESCE(problem_count_stats.problem_count,999999999)) as stats_percentage
               FROM iqp_scenarios
               LEFT JOIN {namespace_name}.iqp_problem_count_recent
                    ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id)
               LEFT JOIN {namespace_name}.iqp_problem_count_prev
                    ON (iqp_scenarios.scn_id = iqp_problem_count_prev.scn_id)
               LEFT JOIN {namespace_name}.iqp_problem_count_recent problem_count_stats
                    ON (iqp_scenarios.scn_totals_scn_id = problem_count_stats.scn_id)
               WHERE iqp_scenarios.subcategory_id = '{sc_id}'
               AND iqp_problem_count_recent.problem_count > 0
               AND (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature')
                    OR '{filter_type}' IN ('Stats', 'N','Feature'))
               AND (iqp_scenarios.scn_type = '{filter_type}' OR '{filter_type}' = '')""".format(
                       namespace_name=org_namespace_name,
                       sc_id=sc_id,
                       filter_type=issue_filter)
    s = text(query)
    rs = session.execute(s).fetchall()
    data = ([[str(item['description']),
              int(item['current']),
              int(item['stats_total']),
              float(item['stats_percentage']),
              int(item['trend']),
              int(item['refreshtime'])]
             for item in rs])
    #tableData = json.loads(request.args['tableData'])
    #write table data
    for eachRow in data:
       writer.writerow(eachRow)
    return Response(
       open(_CSV_FILE_PATH, 'r'),
       headers = d)
Exemplo n.º 25
0
def sub_category_table_data_source(request,sc_name):
    user = request.client_user_object
    user_id = user.user_id
    org = request.client_organization_object
    org_namespace_name = org.organization_namespace_name
    this_sub_category = (session.query(SubCategory)
                         .filter(SubCategory.subcategory_name==sc_name)
                         .all()[0])
    sc_id = this_sub_category.subcategory_id
    issue_filter = request.args.get('filter')
    sortkey = request.args['sortname']
    sortDir = request.args['sortorder']
    limit = int(request.args['rp'])
    offset = int((int(request.args['page']) - 1) * limit)
    data = None
    if not issue_filter:
       issue_filter = ''
    if issue_filter == ('Stats' or 'Features'):
        query = """SELECT iqp_scenarios.scn_name as name,
                     iqp_scenarios.scn_short_description as description,
                     iqp_problem_count_recent.problem_count as current,
                     iqp_problem_count_recent.problem_time as refreshtime,
                     COALESCE(problem_count_stats.problem_count,999999999)
                             as stats_total,
                     (100*iqp_problem_count_recent.problem_count
                         / COALESCE(problem_count_stats.problem_count,999999999))
                             as stats_percentage
                     FROM iqp_scenarios
                     LEFT JOIN {namespace_name}.iqp_problem_count_recent
                         ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id)
                     LEFT JOIN {namespace_name}.iqp_problem_count_prev
                         ON (iqp_scenarios.scn_id = iqp_problem_count_prev.scn_id)
                     LEFT JOIN {namespace_name}.iqp_problem_count_recent problem_count_stats
                         ON (iqp_scenarios.scn_totals_scn_id = problem_count_stats.scn_id)
                     WHERE iqp_scenarios.subcategory_id = '{sc_id}'
                         AND iqp_problem_count_recent.problem_count > 0
                         AND (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature')
                             OR '{filter_type}' IN ('Stats', 'N','Feature'))
                         AND (iqp_scenarios.scn_type = '{filter_type}' OR '{filter_type}' = '')
                         ORDER BY {sortby} {dir} limit {limit} offset {offset}
                     """.format(
           namespace_name=org_namespace_name,
           sc_id=sc_id,
           filter_type=issue_filter,
           sortby=sortkey,
           dir=sortDir,
           limit=limit,
           offset = offset)
        s = text(query)
        rs = session.execute(s).fetchall()
        data = ([[str(item['name']),
                  str(item['description']),
                  int(item['current']),
                  int(item['refreshtime'])]
                 for item in rs])
    else:
       query = """SELECT iqp_scenarios.scn_name as name,
                          iqp_scenarios.scn_short_description as description,
                          iqp_problem_count_recent.problem_count as current,
                          COALESCE(iqp_problem_count_prev.problem_count, 0) as prev,
                          (iqp_problem_count_recent.problem_count
                             - COALESCE(iqp_problem_count_prev.problem_count, 0)) as trend,
                          iqp_problem_count_recent.problem_time as refreshtime,
                          COALESCE(problem_count_stats.problem_count,999999999) as stats_total,
                          (100*iqp_problem_count_recent.problem_count
                             / COALESCE(problem_count_stats.problem_count,999999999))
                                 as stats_percentage
                   FROM iqp_scenarios
                   LEFT JOIN {namespace_name}.iqp_problem_count_recent
                     ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id)
                   LEFT JOIN {namespace_name}.iqp_problem_count_prev
                     ON (iqp_scenarios.scn_id = iqp_problem_count_prev.scn_id)
                   LEFT JOIN {namespace_name}.iqp_problem_count_recent problem_count_stats
                     ON (iqp_scenarios.scn_totals_scn_id = problem_count_stats.scn_id)
                   WHERE iqp_scenarios.subcategory_id = '{sc_id}'
                   AND iqp_problem_count_recent.problem_count > 0
                   AND (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature')
                     OR '{filter_type}' IN ('Stats', 'N','Feature'))
                   AND (iqp_scenarios.scn_type = '{filter_type}' OR '{filter_type}' = '')
                   ORDER BY {sortby} {dir} limit {limit} offset {offset}""".format(namespace_name=org_namespace_name,
                                                                                   sc_id=sc_id,
                                                                                   filter_type=issue_filter,
                                                                                   sortby=sortkey,
                                                                                   dir=sortDir,
                                                                                   limit=limit,
                                                                                   offset=offset)
       s = text(query)
       rs = session.execute(s).fetchall()
       data = ([[str(item['name']),
                 str(item['description']),
                 int(item['current']),
                 int(item['stats_total']),
                 float(item['stats_percentage']),
                 int(item['trend']),
                 int(item['refreshtime'])]
                     for item in rs])
    countQuery = """SELECT count(*)
                    FROM iqp_scenarios
                    LEFT JOIN {namespace_name}.iqp_problem_count_recent
                        ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id)
                    LEFT JOIN {namespace_name}.iqp_problem_count_prev
                        ON (iqp_scenarios.scn_id = iqp_problem_count_prev.scn_id)
                    LEFT JOIN {namespace_name}.iqp_problem_count_recent problem_count_stats
                        ON (iqp_scenarios.scn_totals_scn_id = problem_count_stats.scn_id)
                    WHERE iqp_scenarios.subcategory_id = '{sc_id}'
                    AND iqp_problem_count_recent.problem_count > 0
                    AND (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature')
                        OR '{filter_type}' IN ('Stats', 'N','Feature'))
                    AND (iqp_scenarios.scn_type = '{filter_type}'
                        OR '{filter_type}' = '')
                    """.format(namespace_name=org_namespace_name,
                               sc_id=sc_id,
                               filter_type=issue_filter)
    cs = text(countQuery)
    rs2 = session.execute(cs).fetchall()
    jsond = {"total": rs2[0][0], "page": request.args['page'], "rows": []}
    for row in data:
       eachRow = {}
       eachRow["cell"] = row
       jsond["rows"].append(eachRow)
       del eachRow
    result = json.dumps(jsond)
    return Response(result, mimetype='application/json')
Exemplo n.º 26
0
def setpermissions(request):
   user = request.client_user_object
   user_id = user.user_id
   categories = session.query(Category).order_by(Category.category_display_order.asc())
   return render_template('setpermissions.html', categories=categories)