/// <summary>
/// Create new stored procedure to check a pecific existing, for example: Editable, Revisable, ...
/// </summary>
/// <param name="storedProcedureName"></param>
/// <param name="parameterString"></param>
/// <param name="queryArray"></param>
public static void CreateProcedureToCheckExisting(string storedProcedureName, string[] queryArray)
{
string queryString = "";
queryString = " @FindIdentityID int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @ExistIdentityID Int " + "\r\n";
queryString = queryString + " SET @ExistIdentityID = -1 " + "\r\n";
if (queryArray != null)
{
foreach (string subQuery in queryArray)
{
queryString = queryString + " DECLARE CursorLocal CURSOR LOCAL FOR " + subQuery + "\r\n";
queryString = queryString + " OPEN CursorLocal " + "\r\n";
queryString = queryString + " FETCH NEXT FROM CursorLocal INTO @ExistIdentityID " + "\r\n";
queryString = queryString + " CLOSE CursorLocal " + "\r\n";
queryString = queryString + " DEALLOCATE CursorLocal " + "\r\n";
queryString = queryString + " IF @ExistIdentityID != -1 RETURN @ExistIdentityID " + "\r\n";
}
}
queryString = queryString + " RETURN @ExistIdentityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
SQLDatabase.CreateStoredProcedure(storedProcedureName, queryString);
}
private static void RestoreProcedure()
{
string queryString = "";
/// <summary>
/// Get UserID Information of UserID at ChangeDate
/// </summary>
queryString = " @UserID Int, @ChangeDate DateTime " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " IF @UserID = 0 " + "\r\n";
queryString = queryString + " SELECT ListEmployee.EmployeeID AS UserID, 1 AS UserOrganizationID " + "\r\n";
queryString = queryString + " FROM ListEmployee " + "\r\n";
queryString = queryString + " ELSE " + "\r\n";
queryString = queryString + " SELECT TOP 1 ListEmployee.EmployeeID AS UserID, 1 AS UserOrganizationID " + "\r\n";
queryString = queryString + " FROM ListEmployee " + "\r\n";
queryString = queryString + " WHERE ListEmployee.EmployeeID = @UserID " + "\r\n";
queryString = queryString + " END " + "\r\n";
SQLDatabase.CreateStoredProcedure("SPGetUserInformation", queryString);
/// <summary>
/// Get the top level permission for a specific UserID on a specific TaskID
/// </summary>
queryString = " @UserID Int, @TaskID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " SELECT ISNULL(MAX(ACLEditable), 0) AS MaxACLEditable FROM PublicAccessControl " + "\r\n";
queryString = queryString + " WHERE UserID = @UserID AND TaskID = @TaskID " + "\r\n";
SQLDatabase.CreateStoredProcedure("SPMaxACLEditable", queryString);
/// <summary>
/// Get the permission for a specific UserID on a specific TaskID ON A SPECIFIC UserOrganizationID
/// </summary>
//VIEC GET EDITABLE: CO 2 TRUONG HOP
// '//A. Maintenance List (DANH MUC THAM KHAO): UserOrganizationID = 0 => CO QUYEN HAY KHONG MA THOI, BOI VI MAU TIN TRONG DANH SACH THAM KHAO LA CUA CHUNG => DO DO KHONG CAN DEN UserOrganizationID
// '//B. Transaction Data (CAC MAU TIN GIAO DICH - CAC MAU TIN CO CHU SO HUU), KHI DO CO 2 TINH HUONG:
// '//B.1 TINH HUONG 1: MAU TIN DA SAVE: (TUC LA DA CO CHU SO HUU - CO UserOrganizationID): NGUOI DANG TRUY CAP CO QUYEN EDIT TREN MAU TIN CO CHU SO HUU HAY KHONG?
// '//B2. TINH HUONG 2:
// '//B.2: NGUOI DUNG CO QUYEN EDITABLE TREN TASKID, NHUNG KHONG BIET CO QUYEN TREN MOT DON VI CU THE UserOrganizationID HAY KHONG?
// '//B.2: THEM VAO DO, TAI THOI DIEM EDIT, CHUA XAC DINH MAU TIN THUOC VE AI, KHI DO UserOrganizationID = 0,
// '//B.2: DO DO CUNG CHI XAC DINH DUA TREN QUYEN EDITABLE CUA TASKID CUA UserID HIEN HANH MA THOI
// '//B.2: KHI SAVE (HOAC HAY HON LA KHI CHON CHU SO HUU - VI DU: CHON UserID TRONG QUOTATION) TA MOI XAC DINH DUOC UserOrganizationID
// '//B.2: DEN LUC NAY VIEC XAC DINH QUYEN EDITABLE LA CU THE ROI, DO DO NEU KHONG CO QUYEN EDITABLE CHO MOT DON VI CU THE => THI SE KHONG CHO SAVE
queryString = " @UserID Int, @TaskID Int, @UserOrganizationID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " IF @UserOrganizationID > 0 " + "\r\n"; // CHI CO T/H B.1
queryString = queryString + " SELECT ISNULL(MAX(ACLEditable), 0) AS MaxACLEditable FROM PublicAccessControl " + "\r\n";
queryString = queryString + " WHERE UserID = @UserID AND TaskID = @TaskID AND UserOrganizationID = @UserOrganizationID " + "\r\n";
queryString = queryString + " ELSE " + "\r\n"; //AP DUNG CHO TRUONG T/H A + T/H B.2
queryString = queryString + " SELECT ISNULL(MAX(ACLEditable), 0) AS MaxACLEditable FROM PublicAccessControl " + "\r\n";
queryString = queryString + " WHERE UserID = @UserID AND TaskID = @TaskID " + "\r\n";
SQLDatabase.CreateStoredProcedure("SPUserACLEditable", queryString);
}
