using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FrameworkDAL.Query.AssetAssignment { public class AssetAssignmentQB { public const string GET_ASSETASSIGNMENT = @"SELECT AA.ASSETASSIGNMENTID AS AssetAssignmentId, AA.ASSIGNMENTNAME AS AssignmentName, AA.DESCRIPTION AS Description, AA.ASSIGNMENTTYPE AS AssignmentType, AA.ASSETTYPEID AS AssetTypeId, AT.ASSETTYPECODE AS AssetTypeCode, AT.ASSETTYPENAME AS AssetTypeName, AA.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, AA.PARTYBRANCHLOCATIONID AS PartyBranchLocationId, PBL.PARTYBRANCHLOCATIONCODE AS PartyBranchLocationCode, PBL.PARTYBRANCHLOCATIONNAME AS PartyBranchLocationName, AA.OUID AS Ouid, OU.ORGANIZATIONUNITNAME AS OrganizationUnitName, OU.ORGANIZATIONUNITCODE AS OrganizationUnitCode, AA.DEPARTMENTID AS DepartmentId, D.DEPARTMENTCODE AS DepartmentCode, D.DEPARTMENTNAME AS DepartmentName, AA.CATEGORYID AS CategoryId, C.ITEMCATEGORYCODE AS CategoryCode, C.ITEMCATEGORYNAME AS CategoryName, AA.SUBCATEGORYID AS SubCategoryId, SC.ITEMSUBCATEGORYCODE AS SubCategoryCode, SC.ITEMSUBCATEGORYNAME AS SubCategoryName, AA.USERGROUPID AS UserGroupId, UG.USERGROUPCODE AS UserGroupCode, UG.USERGROUPNAME AS UserGroupName, AA.CREATEDBYID AS CreatedById, AA.CREATEDON AS CreatedOn, AA.MODIFIEDBYID AS ModifiedById, AA.MODIFIEDON AS ModifiedOn, AA.SORTORDER AS SortOrder, AA.STATUS AS Status, AA.VERSION AS Version, AA.SOURCETYPE AS SourceType, AA.TENANTID AS TenantId FROM MASSETASSIGNMENT AA LEFT JOIN MUSER CU ON CU.USERID = AA.CREATEDBYID LEFT JOIN MUSER MU ON MU.USERID = AA.MODIFIEDBYID LEFT JOIN MASSETTYPE AT ON AT.ASSETTYPEID = AA.ASSETTYPEID LEFT JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = AA.PARTYBRANCHID LEFT JOIN MPARTYBRANCHLOCATION PBL ON PBL.PARTYBRANCHLOCATIONID = AA.PARTYBRANCHLOCATIONID LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = AA.OUID LEFT JOIN MDEPARTMENT D ON D.DEPARTMENTID = AA.DEPARTMENTID LEFT JOIN MITEMCATEGORY C ON C.ITEMCATEGORYID = AA.CATEGORYID LEFT JOIN MITEMSUBCATEGORY SC ON SC.ITEMSUBCATEGORYID = AA.SUBCATEGORYID LEFT JOIN MUSERGROUP UG ON UG.USERGROUPID = AA.USERGROUPID WHERE AA.ASSETASSIGNMENTID = @assetassignmentid;"; public const string SAVE_ASSETASSIGNMENT = @"INSERT INTO MASSETASSIGNMENT ( ASSETASSIGNMENTID, ASSIGNMENTNAME, DESCRIPTION, ASSIGNMENTTYPE, ASSETTYPEID, PARTYBRANCHID, PARTYBRANCHLOCATIONID, OUID, DEPARTMENTID, CATEGORYID, SUBCATEGORYID, USERGROUPID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, SOURCETYPE, TENANTID ) VALUES ( @AssetAssignmentId, @AssignmentName, @Description, @AssignmentType, @AssetTypeId, @PartyBranchId, @PartyBranchLocationId, @Ouid, @DepartmentId, @CategoryId, @SubCategoryId, @UserGroupId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @SourceType, @TenantId );"; public const string UPDATE_ASSETASSIGNMENT = @"UPDATE MASSETASSIGNMENT SET ASSIGNMENTNAME = @AssignmentName, DESCRIPTION = @Description, ASSIGNMENTTYPE = @AssignmentType, ASSETTYPEID = @AssetTypeId, PARTYBRANCHID = @PartyBranchId, PARTYBRANCHLOCATIONID = @PartyBranchLocationId, OUID = @Ouid, DEPARTMENTID = @DepartmentId, CATEGORYID = @CategoryId, SUBCATEGORYID = @SubCategoryId, USERGROUPID = @UserGroupId, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn, SORTORDER = @SortOrder, STATUS = @Status, VERSION = @Version, SOURCETYPE = @SourceType, TENANTID = @TenantId WHERE ASSETASSIGNMENTID = @AssetAssignmentId; "; public const string DELETE_ASSETASSIGNMENT = @"DELETE FROM MASSETASSIGNMENT WHERE ASSETASSIGNMENTID = @assetassignmentid; "; public const string GET_SELECTLIST_ASSETASSIGNMENT = @"WITH PagedAssetAssignment AS ( SELECT AA.ASSETASSIGNMENTID AS AssetAssignmentId, AA.ASSIGNMENTNAME AS AssignmentName, AA.ASSIGNMENTTYPE AS AssignmentType, AA.DESCRIPTION AS Description, ROW_NUMBER() OVER (ORDER BY AA.ASSIGNMENTNAME) AS RowNum FROM MASSETASSIGNMENT AA WHERE AA.STATUS = 1 ) SELECT AssetAssignmentId, AssignmentName, AssignmentType, Description FROM PagedAssetAssignment WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_ASSETASSIGNMENT_LIST = @"SELECT AA.ASSETASSIGNMENTID AS AssetAssignmentId, AA.ASSIGNMENTNAME AS AssignmentName, AA.DESCRIPTION AS Description, AA.ASSIGNMENTTYPE AS AssignmentType, AA.ASSETTYPEID AS AssetTypeId, AT.ASSETTYPECODE AS AssetTypeCode, AT.ASSETTYPENAME AS AssetTypeName, AA.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, AA.PARTYBRANCHLOCATIONID AS PartyBranchLocationId, PBL.PARTYBRANCHLOCATIONCODE AS PartyBranchLocationCode, PBL.PARTYBRANCHLOCATIONNAME AS PartyBranchLocationName, AA.OUID AS Ouid, OU.ORGANIZATIONUNITNAME AS OrganizationUnitName, OU.ORGANIZATIONUNITCODE AS OrganizationUnitCode, AA.DEPARTMENTID AS DepartmentId, D.DEPARTMENTCODE AS DepartmentCode, D.DEPARTMENTNAME AS DepartmentName, AA.CATEGORYID AS CategoryId, C.ITEMCATEGORYCODE AS CategoryCode, C.ITEMCATEGORYNAME AS CategoryName, AA.SUBCATEGORYID AS SubCategoryId, SC.ITEMSUBCATEGORYCODE AS SubCategoryCode, SC.ITEMSUBCATEGORYNAME AS SubCategoryName, AA.USERGROUPID AS UserGroupId, UG.USERGROUPCODE AS UserGroupCode, UG.USERGROUPNAME AS UserGroupName, AA.CREATEDBYID AS CreatedById, AA.CREATEDON AS CreatedOn, AA.MODIFIEDBYID AS ModifiedById, AA.MODIFIEDON AS ModifiedOn, AA.SORTORDER AS SortOrder, AA.STATUS AS Status, AA.VERSION AS Version, AA.SOURCETYPE AS SourceType, AA.TENANTID AS TenantId FROM MASSETASSIGNMENT AA LEFT JOIN MUSER CU ON CU.USERID = AA.CREATEDBYID LEFT JOIN MUSER MU ON MU.USERID = AA.MODIFIEDBYID LEFT JOIN MASSETTYPE AT ON AT.ASSETTYPEID = AA.ASSETTYPEID LEFT JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = AA.PARTYBRANCHID LEFT JOIN MPARTYBRANCHLOCATION PBL ON PBL.PARTYBRANCHLOCATIONID = AA.PARTYBRANCHLOCATIONID LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = AA.OUID LEFT JOIN MDEPARTMENT D ON D.DEPARTMENTID = AA.DEPARTMENTID LEFT JOIN MITEMCATEGORY C ON C.ITEMCATEGORYID = AA.CATEGORYID LEFT JOIN MITEMSUBCATEGORY SC ON SC.ITEMSUBCATEGORYID = AA.SUBCATEGORYID LEFT JOIN MUSERGROUP UG ON UG.USERGROUPID = AA.USERGROUPID ORDER BY AA.ASSETASSIGNMENTID OFFSET CASE WHEN @firstnumber = -1 THEN 0 ELSE @firstnumber END ROWS FETCH NEXT CASE WHEN @maxresult = -1 THEN (SELECT COUNT(1) FROM MASSETASSIGNMENT) ELSE @maxresult END ROWS ONLY; "; } }