RaymondYounan
commented
9 years ago 1- I need to change the type of the column from bool into int and link it to the table lkupLogicalAnswer 2- Done 3- Update the following Procedure as follows: ALTER Procedure [dbo].[SearchTeachers](@q nvarchar%2810%29, @cul nvarchar%285%29) As Begin select T.TeacherID as [tid], T.TeacherCivilStatusNationalID as [civilId], P.PersonFirstName_ar_iq as [firstNameAr], P.PersonFirstName_en_us as [firstNameEn], P.PersonFirstName_ku_iq as [firstNameKu], P.PersonLastName_ar_iq as [lastNameAr], P.PersonLastName_en_us as [lastNameEn], P.PersonLastName_ku_iq as [lastNameKu], P.PersonFatherFirstName_ar_iq as [fatherNameAr], P.PersonFatherFirstName_en_us as [fatherNameEn], P.PersonFatherFirstName_ku_iq as [fatherNameKu] from Teacher T Inner Join Person P on T.PersonID = P.PersonID where P.NativeLanguageID in (select L.LanguageID from TeacherLanguageProficiency L where L.TeacherID = T.TeacherID and LanguageProficiencyIsTeachingLanguage = 1) and ((@cul like 'ar-iq' and ((lower(PersonFirstName_ar_iq) like '%' + @q + '%') or (lower(PersonLastName_ar_iq) like '%' + @q + '%') or (lower(PersonFatherFirstName_ar_iq) like '%' + @q + '%'))) or (@cul like 'en-us' and ((lower(PersonFirstName_en_us) like '%' + lower(@q) + '%') or (lower(PersonLastName_en_us) like '%' + lower(@q) + '%') or (lower(PersonFatherFirstName_en_us) like '%' + lower(@q) + '%'))) or (T.TeacherCivilStatusNationalID like '%' + @q + '%' )) End;
DrJoeHage
Every Teacher has one native language defined in his Person profile. Also, every teacher has a profile for language proficiency for one or more languages. However, the Teacher may teach in one or more languages depending on how his or her contract is defined.
The following changes must be made:
CHANGE 1 I added the field LanguageProficiencyIsTeachingLanguage as a BIT the TeacherLanguageProficiency table with a setting of ALLOW NULLs. It should remain this way.
CHANGE 2 Upon the creation of a new Teacher record, we should save automatically a record for the Teacher in the TeacherLanguageProficiency table, marking his/her Native Language as selected in the Teacher form, as a Proficiency with the level "Level 5 - Native or Bilingual Proficiency" corresponding to LanguageProficiencyLevelID=6. This will eliminate a manual step for data entry.
CHANGE 3 When assigning a Teacher to a Section, we should only display those offered in the Language that the Teacher is able to teach based on the value of the LanguageProficiencyIsTeachingLanguage field in the TeacherLanguageProficiency table.