public static DataSet Get_RotationReport(int CrewID, int Vessel_Manager, int ManningOfficeID, int FleetID, int VESSEL_ID, int RankID, string From_Dt, string To_Dt, string SearchText, int UserID)
{
try
{
return(DAL_Crew_CrewRotation.Get_RotationReport_DL(CrewID, Vessel_Manager, ManningOfficeID, FleetID, VESSEL_ID, RankID, From_Dt, To_Dt, SearchText, UserID));
}
catch
{
throw;
}
}
private static string GetCurrentUserType(int iUserID)
{
return(DAL_Crew_CrewRotation.Get_UserData_DL(iUserID, "USER_TYPE"));
}
public static DataTable Get_SalaryExportReport(int CrewID, string CREW_STATUS, string FROM_JOINDATE, string TO_JOINDATE, int NATIONALITY, int VESSEL_ID, int ManningOfficeID, int RankID, int COC, int FleetID, int Vessel_Manager, string SearchText, int UserID)
{
try
{
DateTime Dt_FromJoningDate = DateTime.Today;
DateTime Dt_ToJoningDate = DateTime.Today;
string UserType = GetCurrentUserType(UserID);
string SQL = "";
SQL = @"SELECT
CURR_VESSEL.Vessel_Name,
CD.ID, CD.Staff_Code,
CD.Staff_Surname,
CD.Staff_Name,
CRW_DTL_BankAccounts.Beneficiary,
CRW_DTL_BankAccounts.Bank_Name,
CRW_DTL_BankAccounts.Bank_Address,
(char(39) + CRW_DTL_BankAccounts.Acc_NO) as Acc_NO,
CRW_DTL_BankAccounts.SwiftCode,
'' as Other,
'' as Basic,
'' as Advance,
'' as Expenses,
'' as ToPay,
V.Sign_On_Date,
V.Est_Sing_Off_Date,
V.Sign_Off_Date
FROM LIB_COMPANY AS MANNING_OFFICES RIGHT OUTER JOIN
(SELECT CRW_DTL_CrewChangeEvent_Members.CrewID, MAX(CRW_DTL_CrewChangeEvent.Event_Date) AS Event_Date
FROM CRW_DTL_CrewChangeEvent INNER JOIN
CRW_DTL_CrewChangeEvent_Members ON CRW_DTL_CrewChangeEvent.PKID = CRW_DTL_CrewChangeEvent_Members.EventID
WHERE (CRW_DTL_CrewChangeEvent.Active_Status = 1) AND (CRW_DTL_CrewChangeEvent_Members.Active_Status = 1) AND
(CRW_DTL_CrewChangeEvent.Event_Status = 1) AND (CRW_DTL_CrewChangeEvent.Event_Date >= GETDATE())
GROUP BY CRW_DTL_CrewChangeEvent_Members.CrewID) AS EV RIGHT OUTER JOIN
(SELECT ID, CrewID, Beneficiary, Bank_Name, Bank_Address, Acc_NO, Default_Acc, Verified, VerifiedBy, Link, Vessel_Code, Created_By, Date_Of_Creatation,
Modified_By, Date_Of_Modification, Deleted_By, Date_Of_Deletion, Active_Status, SwiftCode, ID_Vessel, Office_ID
FROM CRW_DTL_BankAccounts AS CRW_DTL_BankAccounts_1
) AS CRW_DTL_BankAccounts RIGHT OUTER JOIN
CRW_LIB_Crew_Details AS CD ON CRW_DTL_BankAccounts.CrewID = CD.ID LEFT OUTER JOIN
CRW_LIB_Crew_Ranks AS AppliedRank ON CD.Rank_Applied = AppliedRank.ID LEFT OUTER JOIN
LIB_VESSELS INNER JOIN
CRW_LIB_Crew_Ranks AS AssignedRank INNER JOIN
CRW_DTL_Last_VesselAssignment_View ON AssignedRank.ID = CRW_DTL_Last_VesselAssignment_View.Joining_Rank ON
LIB_VESSELS.Vessel_ID = CRW_DTL_Last_VesselAssignment_View.Vessel_ID ON CD.ID = CRW_DTL_Last_VesselAssignment_View.CrewID ON
EV.CrewID = CD.ID ON MANNING_OFFICES.Id = CD.ManningOfficeID LEFT OUTER JOIN
CRW_LIB_Crew_Ranks AS ApprovedRank ON CD.Staff_Rank = ApprovedRank.ID LEFT OUTER JOIN
LIB_Country ON CD.Staff_Nationality = LIB_Country.ID LEFT OUTER JOIN
(SELECT VoyageID, SUM(Amount) AS Amount
FROM ACC_DTL_JOINING_EARN_DEDUCTION
WHERE (Active_Status = 1)
GROUP BY VoyageID) AS salary RIGHT OUTER JOIN
CRW_LIB_Crew_Ranks AS rank RIGHT OUTER JOIN
LIB_VESSELS AS CURR_VESSEL INNER JOIN
(SELECT V1.ID, V1.CrewID, V1.Joining_Date, V1.Sign_On_Date, V1.Joining_Rank, V1.Vessel_ID, V1.Est_Sing_Off_Date, V1.Staff_Code, V1.Sign_Off_Date,
V1.Voyage_Remarks, V1.COC_Modified
FROM CRW_Dtl_Crew_Voyages AS V1 INNER JOIN
(SELECT CrewID, MAX(Sign_On_Date) AS Sign_On_Date
FROM CRW_Dtl_Crew_Voyages
WHERE (Active_Status = 1)
GROUP BY CrewID) AS V ON V1.CrewID = V.CrewID AND V1.Sign_On_Date = V.Sign_On_Date
WHERE (V1.Active_Status = 1) AND (V1.Sign_On_Date IS NOT NULL)) AS V ON CURR_VESSEL.Vessel_ID = V.Vessel_ID INNER JOIN
LIB_COMPANY AS VESSEL_MANAGERS ON CURR_VESSEL.Vessel_Manager = VESSEL_MANAGERS.Id ON rank.ID = V.Joining_Rank ON salary.VoyageID = V.ID ON
CD.ID = V.CrewID
WHERE (CD.ID > 0)";
if (CrewID > 0)
{
SQL += " AND CD.ID = @CrewID";
}
if (CREW_STATUS != "INACTIVE")
{
SQL += " AND (CD.Active_Status = 1) ";
}
if (SearchText != "")
{
SQL += " AND (CD.Staff_Name LIKE @Name ";
SearchText = SearchText.Trim();
string[] arrSrarch = SearchText.Split(' ');
if (arrSrarch.Length >= 1)
{
for (int i = 0; i < arrSrarch.Length; i++)
{
if (arrSrarch[i].Length >= 3)
{
SQL += " OR CD.STAFF_NAME LIKE '%" + arrSrarch[i] + "%' OR CD.STAFF_SURNAME LIKE '%" + arrSrarch[i] + "%' OR CD.STAFF_CODE LIKE '%" + arrSrarch[i] + "%'";
}
}
}
SQL += ")";
}
if (NATIONALITY != 0)
{
SQL += " AND (CD.Staff_Nationality = @Nation) ";
}
if (RankID != 0)
{
SQL += " AND (v.Joining_Rank = @RankID or AssignedRank.ID = @RankID or ApprovedRank.ID = @RankID or AppliedRank.ID = @RankID) ";
}
if (UserType == "MANNING AGENT")
{
SQL += " AND (CD.ManningOfficeID = @ManningOfficeID) ";
if (Vessel_Manager != 0)
{
SQL += " AND (CURR_VESSEL.VESSEL_MANAGER = @Vessel_Manager) ";
}
}
else if (UserType == "OFFICE USER")
{
if (ManningOfficeID == 0)
{
SQL += " AND (CD.ManningOfficeID = (select LIB_USER.CompanyID from LIB_USER where UserID=@UserID ) OR CD.ManningOfficeID IN (SELECT CHILD_COMPANY_ID FROM LIB_COMPANY_RELATIONSHIPS WHERE PARENT_COMPANY_ID=(select LIB_USER.CompanyID from LIB_USER where UserID=@UserID ) AND RELATION=1)) ";
}
else
{
SQL += " AND (CD.ManningOfficeID = @ManningOfficeID) ";
}
if (Vessel_Manager != 0)
{
SQL += " AND (CURR_VESSEL.VESSEL_MANAGER = @Vessel_Manager) ";
}
}
else if (UserType == "VESSEL MANAGER")
{
SQL += " AND (CURR_VESSEL.VESSEL_MANAGER = @Vessel_Manager or CURR_VESSEL.VESSEL_MANAGER =(select LIB_USER.CompanyID from LIB_USER where UserID=@UserID )) ";
SQL += " AND (CD.ManningOfficeID in (select parent_company_id from LIB_COMPANY_RELATIONSHIPS where child_company_id = (select LIB_USER.CompanyID from LIB_USER where UserID=@UserID ) and relation = 2)) ";
}
else if (UserType == "ADMIN")
{
if (ManningOfficeID != 0)
{
SQL += " AND (CD.ManningOfficeID = @ManningOfficeID) ";
}
if (Vessel_Manager != 0)
{
SQL += " AND (CURR_VESSEL.VESSEL_MANAGER = @Vessel_Manager) ";
}
}
if (COC > 0)
{
SQL += " AND (V.Sign_On_Date IS NOT NULL AND V.SIGN_OFF_DATE IS NULL AND (V.EST_SING_OFF_DATE BETWEEN GETDATE() and DATEADD(DD," + COC + ",GETDATE()) ))";
}
switch (CREW_STATUS)
{
case "CURRENT":
SQL += " AND (V.SIGN_ON_DATE IS NOT NULL AND v.SIGN_OFF_DATE IS NULL) ";
if (FROM_JOINDATE != "" && TO_JOINDATE != "")
{
Dt_FromJoningDate = DateTime.Parse(FROM_JOINDATE, iFormatProvider);
Dt_ToJoningDate = DateTime.Parse(TO_JOINDATE, iFormatProvider);
SQL += " AND (v.Joining_Date between @JoinFrmDT and @JoinTODT ) ";
}
if (VESSEL_ID != 0)
{
SQL += " AND (v.Vessel_ID = @Vessel_ID)";
}
if (FleetID != 0)
{
SQL += " AND (CURR_VESSEL.FleetCode = @FleetID)";
}
break;
case "SIGNED OFF":
SQL += " AND (V.Sign_On_Date IS NOT NULL AND V.SIGN_OFF_DATE IS NOT NULL)";
SQL += " AND cd.status_code is null";
break;
case "NO VOYAGE":
SQL += " AND ((V.Sign_On_Date IS NULL AND v.SIGN_OFF_DATE IS NULL) and (CD.MANNINGOFFICESTATUS = 1 AND CD.CrewManagerApproval = 1) AND (SOff.CrewID_UnAssigned IS NULL AND ev.Event_Date IS NULL)) ";
SQL += " AND cd.status_code is null";
break;
case "INACTIVE":
SQL += " AND (CD.ACTIVE_STATUS = 0)";
break;
case "ASSIGNED":
SQL += " AND (SOff.CrewID_UnAssigned IS NOT NULL)";
SQL += " AND cd.status_code is null";
break;
case "PLANNED":
SQL += " AND (EV.Event_Date is not null)";
SQL += " AND cd.status_code is null";
break;
case "DECISION PENDING":
SQL += " AND (isnull(CD.ManningOfficeStatus, 0)= 0 OR isnull(CD.CrewManagerApproval,0) = 0 )";
SQL += " AND cd.status_code is null";
break;
case "NTBR":
SQL += " AND (CD.Status_Code = 'NTBR')";
break;
case "MEDICALLY UNFIT":
SQL += " AND (CD.Status_Code = 'MEDICALLY UNFIT')";
break;
case "REJECTED":
SQL += " AND (CD.CrewManagerApproval = -1)";
SQL += " AND cd.status_code is null";
break;
case "":
if (VESSEL_ID != 0)
{
SQL += " AND (v.Vessel_ID = @Vessel_ID or CRW_DTL_Last_VesselAssignment_View.vessel_id = @Vessel_ID )";
}
if (FleetID != 0)
{
SQL += " AND (CURR_VESSEL.FleetCode = @FleetID or LIB_VESSELS.FLEETCODE=@FleetID)";
}
break;
}
SQL += " ORDER BY rank.Rank_Sort_Order, AssignedRank.Rank_Sort_Order,ApprovedRank.Rank_Sort_Order, Staff_SurName";
SqlParameter[] sqlprm = new SqlParameter[]
{
new SqlParameter("@CrewID", CrewID),
new SqlParameter("@UserID", UserID),
new SqlParameter("@Name", "%" + SearchText + "%"),
new SqlParameter("@JoinFrmDT", Dt_FromJoningDate),
new SqlParameter("@JoinTODT", Dt_ToJoningDate),
new SqlParameter("@Nation", NATIONALITY),
new SqlParameter("@Vessel_ID", VESSEL_ID),
new SqlParameter("@ManningOfficeID", ManningOfficeID),
new SqlParameter("@RankID", RankID),
new SqlParameter("@FleetID", FleetID),
new SqlParameter("@Vessel_Manager", Vessel_Manager)
};
return(DAL_Crew_CrewRotation.ExecuteQuery(SQL, sqlprm));
}
catch
{
throw;
}
}