mysql调查问卷数据库设计_调查问卷表结构设计及具体实现

1 from django.core.validators importRegexValidator2 from django.db.models.aggregates importCount3 from django.forms.forms importForm4 from django.http.response importJsonResponse5 from django.shortcuts importrender, HttpResponse,redirect6 from app01 importmodels7 from django.forms importModelForm,fields,widgets8 importjson9 from django.core.exceptions importValidationError10 from django.core.validators importRegexValidator11 #Create your views here.

12 defindex(request):13 Questionnaire_obj =models.Questionnaire.objects.all()14 #查询问卷所属的班级的学生个数

15 for naire inQuestionnaire_obj:16 naire.part_num = models.Answer.objects.filter(queston__in=naire.questions_set.all()).values_list('student_id').distinct().count()17 print(naire.part_num)18 return render(request,"index.html",{"Questionnaire_obj":Questionnaire_obj})19

20 classQuestionForm(ModelForm):21 classMeta:22 model =models.Questions23 fields = ["caption","question_type"]24

25 error_messages ={26 "caption":{"required":"不能为空"}27 }28 widgets ={29 "caption":widgets.Textarea(attrs={"class": "question","rows":0,"cols":0})30 }31

32 classOptionModelForm(ModelForm):33 classMeta:34 model =models.Option35 fields = ["name","score"]36

37 defquestionedit(request,nid):38 #方式一：

39 ##查询当前问卷的所有的问题

40 #que_list = models.Questions.objects.filter(questionnaire_id=nid).all()

41 #question_list = []

42 #if not que_list:

43 #'''新建的问题，还没有创建问题'''

44 #form = QuestionForm()

45 #question_list.append(form)

46 #return render(request,"questionedit.html",{"question_list":question_list})

47 #else:

48 #'''已经创建了问题的'''

49 #for que in que_list:

50 #print(que,"que===")

51 #form = QuestionForm(instance=que)

52 #question_list.append(form)

53 #return render(request,"questionedit.html",{"question_list":question_list})

54

55 #方式二：

56 #查询当前问卷的所有的问题

57 #def inner():

58 #que_list = models.Questions.objects.filter(questionnaire_id=nid).all()

59 #if not que_list:

60 #'''新建的问题，还没有创建问题'''

61 #form = QuestionForm()

62 #yield form

63 #else:

64 #'''已经创建了问题的'''

65 #for que in que_list:

66 #form = QuestionForm(instance=que)

67 #yield form

68 #return render(request,"questionedit.html",{"form":inner()})

69

70

71 #方式三，yield返回的时候吧form作为一个字典的key返回

72 #def inner():

73 #que_list = models.Questions.objects.filter(questionnaire_id=nid).all()

74 #if not que_list:

75 #'''新建的问题，还没有创建问题'''

76 #form = QuestionForm()

77 #yield {"form":form,"obj":None}

78 #else:

79 #'''已经创建了问题的'''

80 #for que in que_list:

81 #print(que)

82 #form = QuestionForm(instance=que)

83 #temp = {"form":form,"obj":que,"option_class":"hide","options":None}

84 #if que.question_type == 2:

85 #'''如果选项类型是单选的时候'''

86 #temp["option_class"] = ""

87 ##如果是单选的时候让显示所有的选项

88 #question_option_list =[]

89 #option_list = models.Option.objects.filter(question=que)

90 #for obj in option_list:

91 #vm = OptionModelForm(instance=obj)

92 #question_option_list.append(vm)

93 #print(question_option_list,"pppppppppppppp")

94 #temp["options"] = question_option_list

95 #yield temp

96 #return render(request, "questionedit.html", {"form": inner()})

97

98 #方式四

99 definner():100 que_list = models.Questions.objects.filter(questionnaire_id=nid).all()101 if notque_list:102 '''新建的问题，还没有创建问题'''

103 form =QuestionForm()104 yield {"form":form,"obj":None,'option_class':"hide","options":None}105 else:106 '''已经创建了问题的'''

107 for que inque_list:108 print(que)109 form = QuestionForm(instance=que)110 temp = {"form":form,"obj":que,"option_class":"hide","options":None}111 if que.question_type == 2:112 '''如果选项类型是单选的时候'''

113 temp["option_class"] = ""

114 #如果是单选的时候让显示所有的选项

115 definner_loop(quee):116 option_list = models.Option.objects.filter(question=quee)117 for v inoption_list:118 yield {"form":OptionModelForm(instance=v),"obj":v}119 temp["options"] =inner_loop(que)120 yieldtemp121 return render(request, "questionedit.html", {"form": inner(),"nid":nid})122

123 defquestionedit2(request,nid):124 definner():125 que_list = models.Questions.objects.filter(questionnaire_id=nid).all()126 if notque_list:127 '''新建的问题，还没有创建问题'''

128 form =QuestionForm()129 yield {"form": form, "obj": None, 'option_class': "hide", "options": None}130 else:131 '''已经创建了问题的'''

132 for que inque_list:133 print(que)134 form = QuestionForm(instance=que)135 temp = {"form": form, "obj": que, "option_class": "hide", "options": None}136 if que.question_type == 2:137 '''如果选项类型是单选的时候'''

138 temp["option_class"] = ""

139

140 #如果是单选的时候让显示所有的选项

141 definner_loop(quee):142 option_list = models.Option.objects.filter(question=quee)143 for v inoption_list:144 yield {"form": OptionModelForm(instance=v), "obj": v}145

146 temp["options"] =inner_loop(que)147 yieldtemp148 return render(request,"questionedit.html",{"form":inner()})149

150

151 defquestionsave(request,nid):152 ret = {"status":True,"msg":None,"data":None}153 try:154 ifrequest.is_ajax():155 #得到新提交的数据

156 data=request.body.decode("utf8")157 post_data_list =json.loads(data)158 #找到所有的问题列表

159 question_list = models.Questions.objects.filter(questionnaire_id=nid)160 #找到用户提交的所有的问题id

161 post_id_list = [i.get("id") for i in post_data_list if i.get("id")]162 #print(post_id_list,"post_id_list") #['1', '2', '1', '2', '1', '2', '1', '2'] post_id_list

163 #找到数据库中的所有的问题id

164 question_id_list = [i.id for i inquestion_list]165 #print(question_id_list,"question_id_list") #[1, 2] question_id_list

166 #数据库中的那些id需要删除(数据库里有前端没有的数据删除)

167 del_id_list =set(question_id_list).difference(post_id_list)168

169 #循环ajax发过来的那些问题列表，

170 for item inpost_data_list:171 #item就是用户传进来的每个问题

172 caption = item.get("caption")173 type_id = item.get("type_id")174 qid = item.get("id")175 options = item.get("options")176 if not qid inquestion_id_list:177 #如果前端传进来的id不在数据库里面，就说明要新增

178 new_question_obj = models.Questions.objects.create(caption=caption,question_type=type_id,questionnaire_id=nid)179 if type_id==2:180 for op inoptions:181 name = op.get("name")182 score = op.get("score")183 models.Option.objects.create(name=name,score=score,question=new_question_obj)184 else:185 #否则说明是要更新

186 models.Questions.objects.filter(id=qid).update(caption=caption,question_type=type_id,questionnaire_id=qid)187 if notoptions:188 #如果没有options就把数据库的options记录给删除了

189 models.Option.objects.filter(id=nid).delete()190 else:191 #如果有先删除原来的后创建新传进来的

192 models.Option.objects.filter(id=nid).delete()193 for op inoptions:194 name = op.get("name")195 score = op.get("score")196 models.Option.objects.create(name=name,score=score,question_id=qid)197 models.Questions.objects.filter(id__in=del_id_list).delete()198 exceptException as e:199 ret['msg'] =str(e)200 ret["status"] =False201 returnJsonResponse(ret)202

203

204 classStudentForm(ModelForm):205 #password = fields.CharField(max_length=8, validators=[RegexValidator("\d+", "密码只能是数字")],

206 #error_messages={"max_length":"8"}

207 #)

208 #这里如果写上password，下面也有了，就会把下面的给覆盖了

209 classMeta:210 model=models.Student211 fields=["name","password"]212

213 error_messages ={214 "name":{"required":"用户名不能为空"},215 "password":{"required":"密码不能为空","max_length":"密码长度不能大于8位"},216 },217 widgets ={218 "password": widgets.PasswordInput(attrs={'placeholder': 'password', 'class': 'form-control'}),219 "name": widgets.TextInput(attrs={'placeholder': 'username', 'class': 'form-control'})220 }221

222

223 defstudent_login(request):224 #obj = models.Student.objects.all().first()

225 #print(obj.id,obj.name)

226 if request.method=="GET":227 form =StudentForm()228 else:229 print("============")230 form = StudentForm(data=request.POST)231 ifform.is_valid():232 print("======",form.cleaned_data)233 user = models.Student.objects.filter(**form.cleaned_data).first()234 ifuser:235 request.session["id"] =user.id236 request.session["user"] =user.name237 class_id = request.session.get("class_id")238 qn_id = request.session.get("qn_id")239 #if class_id==None or qn_id==None:

240 #return redirect("/index/")

241 return redirect('/score/%s/%s'%(class_id,qn_id))242 else:243 return render(request,"student_login.html",{"form":form})244 return render(request, "student_login.html", {"form": form})245

246

247 deffunc(val):248 #参数要有，Form用正则匹配的时候不用加括号，自己就会执行这个函数，去验证

249 if len(val)<15:250 raise ValidationError("字数不能小于15字")251

252 defscore(request,class_id,qn_id):253 #print(class_id,qn_id)

254 student_id = request.session.get("id")255 print(student_id,"student_id")256 request.session["class_id"] =class_id257 request.session["qn_id"] =qn_id258 if notstudent_id:259 return redirect("/student_login/")260 #查看当前用户是否是要评论的班级的学生

261

262 stu1 = models.Student.objects.filter(cls=class_id,id=student_id).count()263 print("stu1",stu1)264 if notstu1:265 return HttpResponse("你还不是这个班的学生呢，你无权访问我们这次问卷")266

267 #当前学生是否已经评论过当前问卷

268 stu2 = models.Answer.objects.filter(student_id=student_id,queston__questionnaire_id=qn_id).count()269 #print(stu2)

270 ifstu2:271 return HttpResponse("你已经答过了，感谢你的参与。无法再进行第二次答卷")272

273 #验证通过以后就开始展示答卷的页面了

274 #展开当前问卷下的所有的问题

275 question_list = models.Questions.objects.filter(questionnaire_id=qn_id)276 question_dict ={}277 for que inquestion_list:278 print(que.id)279 print("asssdsfsfs",models.Option.objects.filter(question_id=que.id).values_list('id', 'name'))280 #que是每一个问题

281 if que.question_type==1: #打分

282 question_dict["val_%s"%que.id] =fields.ChoiceField(283 label=que.caption,284 error_messages={"required":"不能为空"},285 widget =widgets.RadioSelect,286 choices = [(i,i) for i in range(1,11)]287 )288 elif que.question_type==2: #单选

289 question_dict["option_id_%s"%que.id] =fields.ChoiceField(290 label=que.caption,291 error_messages={"required":"不能为空"},292 widget =widgets.RadioSelect,293 choices=models.Option.objects.filter(question_id=que.id).values_list('id', 'name') #拿自己的选项

294 )295

296 else: #评价

297 question_dict["content_%s"%que.id] =fields.CharField(298 label=que.caption,299 error_messages={"required": "不能为空"},300 widget=widgets.Textarea,301 validators=[func,] #这里的func不用加参数

302 )303

304 MyTestForm = type("MyTestForm",(Form,),question_dict) #三个参数分别是：类名，继承的父类，后面是一个字典

305 if request.method =="GET":306 form =MyTestForm()307 return render(request,"score.html",{"question_list":question_list,"form":form})308 else:309 form =MyTestForm(request.POST)310 ifform.is_valid():311 #如果验证成功

312 print(form.cleaned_data,"2222222")313 objs =[]314 for key,v inform.cleaned_data.items():315 print(key,v,"1111111")316 k,qid = key.rsplit('_',1)317 print(k,qid,"2223333")318 answer_dict = {'student_id':student_id,'queston_id':qid,k:v}319

320 objs.append(models.Answer(**answer_dict))321 models.Answer.objects.bulk_create(objs)322 return HttpResponse("感谢你的参与！！")323 return render(request, "score.html", {"question_list": question_list, "form": form})