Π’Π΅Ρ
Π½ΠΎΠ»ΠΎΠ³ΠΈΡΠ΅ΡΠΊΠ°Ρ ΡΠ°ΡΡΡ.
ΠΠ²ΡΠΎΠΌΠ°ΡΠΈΠ·ΠΈΡΠΎΠ²Π°Π½Π½Π°Ρ ΡΠΈΡΡΠ΅ΠΌΠ° Π½Π°ΠΉΠΌΠ° Π½Π° ΡΠ°Π±ΠΎΡΡ ΠΈ ΠΏΠΎΠΈΡΠΊΠ° Π²Π°ΠΊΠ°Π½ΡΠΈΠΉ
Dbo. getCrewMaritimeExpirience (c.id) maritime_expirience_name,. Left Outer Join work_as_team wat On c. work_as_team_id=wat.id. Left Outer Join work_as_team wat On c. work_as_team_id=wat.id. Left Outer Join vessel_types vt On c. vessel_type_id=vt.id. Left Outer Join vessel_types vt On c. vessel_type_id=vt.id. Inner Join crew_experience_length cel On cel. crew_id=c.id. Left Outer Join… Π§ΠΈΡΠ°ΡΡ Π΅ΡΡ >
Π’Π΅Ρ Π½ΠΎΠ»ΠΎΠ³ΠΈΡΠ΅ΡΠΊΠ°Ρ ΡΠ°ΡΡΡ. ΠΠ²ΡΠΎΠΌΠ°ΡΠΈΠ·ΠΈΡΠΎΠ²Π°Π½Π½Π°Ρ ΡΠΈΡΡΠ΅ΠΌΠ° Π½Π°ΠΉΠΌΠ° Π½Π° ΡΠ°Π±ΠΎΡΡ ΠΈ ΠΏΠΎΠΈΡΠΊΠ° Π²Π°ΠΊΠ°Π½ΡΠΈΠΉ (ΡΠ΅ΡΠ΅ΡΠ°Ρ, ΠΊΡΡΡΠΎΠ²Π°Ρ, Π΄ΠΈΠΏΠ»ΠΎΠΌ, ΠΊΠΎΠ½ΡΡΠΎΠ»ΡΠ½Π°Ρ)
Π Π°Π·ΡΠ°Π±ΠΎΡΠΊΠ° ΡΠ΅Ρ Π½ΠΎΠ»ΠΎΠ³ΠΈΠΈ ΠΎΠ±ΡΠ°Π±ΠΎΡΠΊΠΈ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΠΈ
ΠΠ° ΠΎΡΠ½ΠΎΠ²Π΅ Π²Π½Π΅ΡΠ½Π΅Π³ΠΎ ΠΏΡΠΎΠ΅ΠΊΡΠΈΡΠΎΠ²Π°Π½ΠΈΡ Π±ΡΠ»Π° ΡΠ°Π·ΡΠ°Π±ΠΎΡΠ°Π½Π° ΡΠ΅Ρ Π½ΠΎΠ»ΠΎΠ³ΠΈΡ ΠΎΠ±ΡΠ°Π±ΠΎΡΠΊΠΈ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΠΈ.
ΠΠ»Ρ ΡΠ°Π·ΡΠ°Π±ΠΎΡΠΊΠΈ Π±Π°Π·Ρ Π΄Π°Π½Π½ΡΡ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°Π»Π°ΡΡ Π‘Π£ΠΠ MSSQL Server. ΠΠΎΡΡΡΠΏ ΠΊ Π±Π°Π·Π΅ Π΄Π°Π½Π½ΡΡ ΡΠΈΡΡΠ΅ΠΌΡ ΠΎΡΡΡΠ΅ΡΡΠ²Π»ΡΠ΅ΡΡΡ Ρ ΠΊΠ»ΠΈΠ΅Π½ΡΡΠΊΠΈΡ ΠΌΠ΅ΡΡ ΠΏΠΎΡΡΠ΅Π΄ΡΡΠ²ΠΎΠΌ ΠΏΠ΅ΡΠ΅Π΄Π°ΡΠΈ Π·Π°ΠΏΡΠΎΡΠΎΠ² Π½Π° ΡΠ΅ΡΠ²Π΅Ρ ΠΏΡΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠΉ, ΠΊΠΎΡΠΎΡΡΠΉ Π² ΡΠ²ΠΎΡ ΠΎΡΠ΅ΡΠ΅Π΄Ρ ΡΠΎΡΠΌΠΈΡΡΠ΅Ρ Π·Π°ΠΏΡΠΎΡΡ ΠΊ Π±Π°Π·Π΅ Π΄Π°Π½Π½ΡΡ ΡΠ΅ΡΠ΅Π· ΠΈΠ½ΡΠ΅ΡΡΠ΅ΠΉΡ Ρ ΡΠ°Π½ΠΈΠΌΡΡ ΠΏΡΠΎΡΠ΅Π΄ΡΡ, Π½Π°ΠΏΠΈΡΠ°Π½Π½ΡΡ Π½Π° ΡΠ·ΡΠΊΠ΅ SQL. Π Π°Π±ΠΎΡΠ°Ρ Ρ ΡΠ΅ΡΠ²Π΅ΡΠΎΠΌ ΠΏΡΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠΉ, ΠΌΠ°ΡΠΈΠ½Ρ-ΠΊΠ»ΠΈΠ΅Π½ΡΡ ΠΌΠΎΠ³ΡΡ ΠΎΡΡΡΠ΅ΡΡΠ²Π»ΡΡΡ Π·Π°Π½Π΅ΡΠ΅Π½ΠΈΠ΅ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΠΈ Π² Π±Π°Π·Ρ, ΡΠ΅Π΄Π°ΠΊΡΠΈΡΠΎΠ²Π°Π½ΠΈΠ΅ ΠΈ ΡΠ΄Π°Π»Π΅Π½ΠΈΠ΅ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΠΈ, Π° ΡΠ°ΠΊΠΆΠ΅ ΡΡΠ΅Π½ΠΈΠ΅ ΠΈ ΠΏΠΎΠΈΡΠΊ Π½Π΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΡΡ Π΄Π°Π½Π½ΡΡ . ΠΠ»Ρ Π²ΡΠΏΠΎΠ»Π½Π΅Π½ΠΈΡ Π²ΡΡΠ΅ΡΠΊΠ°Π·Π°Π½Π½ΡΡ ΡΡΠ½ΠΊΡΠΈΠΉ Π±ΡΠ»Π° ΡΠ°Π·ΡΠ°Π±ΠΎΡΠ°Π½Π° ΡΠΎΠ²ΠΎΠΊΡΠΏΠ½ΠΎΡΡΡ Ρ ΡΠ°Π½ΠΈΠΌΡΡ ΠΏΡΠΎΡΠ΅Π΄ΡΡ. Π ΠΊΠ°ΡΠ΅ΡΡΠ²Π΅ ΠΏΡΠΈΠΌΠ΅ΡΠ° ΠΏΡΠΈΠ²Π΅Π΄Π΅ΠΌ ΠΏΡΠΎΡΠ΅Π΄ΡΡΡ ΠΏΠΎΠΈΡΠΊΠ° ΡΠΎΠΈΡΠΊΠ°ΡΠ΅Π»Π΅ΠΉ ΡΠ°Π±ΠΎΡΡ:
CREATE procedure sp_crew_search_new2 (.
@strWhere Varchar (4000) = Null,.
@strOrder Varchar (4000) = Null,.
@strPositions Varchar (4000) = Null,.
@numberTop Int,.
@RecCount Int = 0 Output.
).
As.
Declare @AddStr As Varchar (50).
Select @AddStr = Case.
When @numberTop Is Null Then ''.
Else ' top ' + Cast (@numberTop As Varchar (20)).
End.
Declare @strSQL nVarchar (4000).
Declare @c As Int.
Set @strSQL =.
'Select @c=Count (c.id).
From crew c.
Left Outer Join vessel_types vt On c. vessel_type_id=vt.id.
Left Outer Join locations lco On c. curren_location_country_id=lco.id.
Left Outer Join vtop3_pref_pos v3pp On v3pp. crew_id=c.id.
Left Outer Join nationalities nat On nat. id=c.crew_nationality_id.
Left Outer Join work_as_team wat On c. work_as_team_id=wat.id.
Inner Join availabilities a On a. id=c.availability_id.
'.
If @strWhere Is Not Null And @strWhere ''.
Set @strSQL = @strSQL + @strWhere.
Exec sp_executesql @strSQL, N'@c Int Output', @RecCount Output.
Set @strSQL = 'Select ' + @AddStr +.
' c. id,.
c.email,.
c.current_location current_location,.
Case.
When DatePart (mm, c. dob) > DatePart (mm, getdate ()) Then DateDiff (yy, c. dob, getdate ()) — 1.
When DatePart (mm, c. dob) < DatePart (mm, getdate ()) Then DateDiff (yy, c. dob, getdate ()).
Else.
Case.
When DatePart (dd, c. dob) <= DatePart (dd, getdate ()) Then DateDiff (yy, c. dob, getdate ()).
Else DateDiff (yy, c. dob, getdate ()) — 1.
End.
End dob,.
c.employment_comments,.
c.us_vessel_qualification,.
et.last_update_time,.
dbo.getCrewCertLevels (c.id) cert_levels,.
dbo.getCrewEmploymentTypes (c.id) employment_types,.
a.name availability,.
lco.name curent_location_country_name,.
vt.name vessel_type_name,.
dbo.getCrewMaritimeExpirience (c.id) maritime_expirience_name,.
nat.name nationality_name,.
(Select Count (name) count_cvs From crew_files Where file_type=1 And crew_id=c.id) count_cvs,.
(Select Count (name) count_photos From crew_files Where file_type=2 And crew_id=c.id) count_photos,.
(Select count (name) count_certs From crew_files Where file_type=3 And crew_id=c.id) count_certs,.
(Select count (name) count_refs From crew_files Where file_type=4 And crew_id=c.id) count_refs,.
(Select count (name) count_oths From crew_files Where file_type=5 And crew_id=c.id) count_oths,.
wat.name wat_name,.
el.name maritime_exp_len,.
dbo.getCrewVisas (c.id) visas_held,.
dbo.getCrewTargetVesselSizes (c.id) vessel_size,.
dbo.getCrewAdditionalQualifications (c.id) ad_cert,.
dbo.getCrewIndicateLevels (c.id) ind_lev,.
c.cert_comments comment,.
(Select name main_photo From crew_files Where file_type = 2 And crew_id=c.id And create_date = (Select Min (create_date) From crew_files Where file_type = 2 And crew_id=c.id)) main_photo,.
(Select description main_photo_description From crew_files Where file_type = 2 And crew_id=c.id And create_date = (Select Min (create_date) From crew_files Where file_type = 2 And crew_id=c.id)) main_photo_description,.
c.first_name first_name,.
c.middle_name middle_name,.
c.last_name last_name.
From crew c.
Left Outer Join vessel_types vt On c. vessel_type_id=vt.id.
Left Outer Join locations lco On c. curren_location_country_id=lco.id.
Left Outer Join nationalities nat On nat. id=c.crew_nationality_id.
Left Outer Join work_as_team wat On c. work_as_team_id=wat.id.
Inner Join availabilities a On a. id=c.availability_id.
Inner Join (.
Select crew_id, max (crew_event_date) last_update_time From crew_log.
Where crew_event_type_id In (1,2).
Group By crew_id.
) et On et. crew_id=c.id.
Inner Join crew_experience_length cel On cel. crew_id=c.id.
Inner Join experience_lengths el On cel. experience_length_id=el.id.
'.
If @strWhere Is Not Null And @strWhere ''.
Set @strSQL = @strSQL + @strWhere.
If @strOrder Is Not Null And @strOrder ''.
Set @strSQL = @strSQL + @strOrder.
— Print (@strSQL).
Exec (@strSQL).