using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace MaintenanceDAL.Query.KeyComponent { public class KeyComponentQB { public const string GET_KEYCOMPONENT = @"SELECT /* ========================= Parent : MKEYCOMPONENT ========================= */ KC.KEYCOMPONENTID AS KeyComponentId, KC.NATURE AS KeyComponentNature, KC.ASSETID AS AssetId, A.ASSETCODE AS AssetCode, A.ASSETNAME AS AssetName, KC.ITEMID AS ItemId, I.ITEMCODE AS ItemCode, I.ITEMNAME AS ItemName, KC.RESOURCEID AS ResourceId, R.RESOURCECODE AS ResourceCode, R.RESOURCENAME AS ResourceName, KC.MACHINEID AS MachineId, M.MACHINECODE AS MachineCode, M.MACHINENAME AS MachineName, KC.LOTID AS LotId, L.LOTNUMBER AS LotLotNumber, KC.REMARKS AS KeyComponentRemarks, KC.SOURCETYPE AS KeyComponentSourceType, KC.SORTORDER AS KeyComponentSortOrder, KC.STATUS AS KeyComponentStatus, KC.VERSION AS KeyComponentVersion, KC.CREATEDBYID AS KeyComponentCreatedById, KC.CREATEDON AS KeyComponentCreatedOn, KC.MODIFIEDBYID AS KeyComponentModifiedById, KC.MODIFIEDON AS KeyComponentModifiedOn, KC.TENANTID AS KeyComponentTenantId, /* ========================= Child : MKEYCOMPONENTDETAIL ========================= */ KCD.KEYCOMPONENTDETAILID AS KeyComponentDetailId, KCD.KEYCOMPONENTID AS KeyComponentId, KCD.SLNO AS KeyComponentDetailSlNo, KCD.NATURE AS KeyComponentDetailNature, KCD.ASSETID AS AssetId, DA.ASSETCODE AS AssetCode, DA.ASSETNAME AS AssetName, KCD.ITEMID AS ItemId, DI.ITEMCODE AS ItemCode, DI.ITEMNAME AS ItemName, KCD.RESOURCEID AS ResourceId, DR.RESOURCECODE AS ResourceCode, DR.RESOURCENAME AS ResourceName, KCD.MACHINEID AS MachineId, DM.MACHINECODE AS MachineCode, DM.MACHINENAME AS MachineName, KCD.DETAILSLNO AS KeyComponentDetailDetailSlNo, KCD.QUANTITY AS KeyComponentDetailQuantity, KCD.REPLACEDON AS KeyComponentDetailReplacedOn, KCD.LOTID AS LotId, DL.LOTNUMBER AS LotLotNumber, KCD.REMARKS AS KeyComponentDetailRemarks FROM MKEYCOMPONENT KC LEFT JOIN MKEYCOMPONENTDETAIL KCD ON KC.KEYCOMPONENTID = KCD.KEYCOMPONENTID -- Parent Lookups LEFT JOIN MASSET A ON KC.ASSETID = A.ASSETID LEFT JOIN MITEM I ON KC.ITEMID = I.ITEMID LEFT JOIN MRESOURCE R ON KC.RESOURCEID = R.RESOURCEID LEFT JOIN MMACHINE M ON KC.MACHINEID = M.MACHINEID LEFT JOIN TLOT L ON KC.LOTID = L.LOTID -- Child Lookups LEFT JOIN MASSET DA ON KCD.ASSETID = DA.ASSETID LEFT JOIN MITEM DI ON KCD.ITEMID = DI.ITEMID LEFT JOIN MRESOURCE DR ON KCD.RESOURCEID = DR.RESOURCEID LEFT JOIN MMACHINE DM ON KCD.MACHINEID = DM.MACHINEID LEFT JOIN TLOT DL ON KCD.LOTID = DL.LOTID WHERE KC.KEYCOMPONENTID = @keycomponentid;"; public const string SAVE_KEYCOMPONENT = @"INSERT INTO MKEYCOMPONENT ( KEYCOMPONENTID, NATURE, ASSETID, RESOURCEID, MACHINEID, ITEMID, LOTID, REMARKS, SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID ) VALUES ( @KeyComponentId, @KeyComponentNature, @AssetId, @ResourceId, @MachineId, @ItemId, @LotId, @KeyComponentRemarks, @KeyComponentSortOrder, @KeyComponentStatus, @KeyComponentVersion, @KeyComponentCreatedById, @KeyComponentCreatedOn, @KeyComponentModifiedById, @KeyComponentModifiedOn, @KeyComponentSourceType, @KeyComponentTenantId );"; public const string SAVE_KEYCOMPONENT_DETAIL = @"INSERT INTO MKEYCOMPONENTDETAIL ( KEYCOMPONENTDETAILID, KEYCOMPONENTID, SLNO, NATURE, ASSETID, RESOURCEID, MACHINEID, ITEMID, DETAILSLNO, QUANTITY, REPLACEDON, LOTID, REMARKS ) VALUES ( @KeyComponentDetailId, @KeyComponentId, @KeyComponentDetailSlNo, @KeyComponentDetailNature, @AssetId, @ResourceId, @MachineId, @ItemId, @KeyComponentDetailDetailSlNo, @KeyComponentDetailQuantity, @KeyComponentDetailReplacedOn, @LotId, @KeyComponentDetailRemarks );"; public const string UPDATE_KEYCOMPONENT = @"UPDATE MKEYCOMPONENT SET NATURE = @KeyComponentNature, ASSETID = @AssetId, RESOURCEID = @ResourceId, MACHINEID = @MachineId, ITEMID = @ItemId, LOTID = @LotId, REMARKS = @KeyComponentRemarks, SORTORDER = @KeyComponentSortOrder, STATUS = @KeyComponentStatus, VERSION = @KeyComponentVersion, MODIFIEDBYID = @KeyComponentModifiedById, MODIFIEDON = @KeyComponentModifiedOn, SOURCETYPE = @KeyComponentSourceType, TENANTID = @KeyComponentTenantId WHERE KEYCOMPONENTID = @KeyComponentId;"; public const string UPDATE_KEYCOMPONENT_DETAIL = @"UPDATE MKEYCOMPONENTDETAIL SET SLNO = @KeyComponentDetailSlNo, NATURE = @KeyComponentDetailNature, ASSETID = @AssetId, RESOURCEID = @ResourceId, MACHINEID = @MachineId, ITEMID = @ItemId, DETAILSLNO = @KeyComponentDetailDetailSlNo, QUANTITY = @KeyComponentDetailQuantity, REPLACEDON = @KeyComponentDetailReplacedOn, LOTID = @LotId, REMARKS = @KeyComponentDetailRemarks WHERE KEYCOMPONENTDETAILID = @KeyComponentDetailId AND KEYCOMPONENTID = @KeyComponentId;"; //Delete the KeyComponent from Parent table using KeyComponentId public const string DELETE_KEYCOMPONENT = @"DELETE FROM MKEYCOMPONENT WHERE KEYCOMPONENTID = @keycomponentid;"; //Delete the keyComponent from child table using keycomponentId public const string DELETE_KEYCOMPONENT_DETAIL = @"DELETE FROM MKEYCOMPONENTDETAIL WHERE KEYCOMPONENTID = @keycomponentid;"; public const string GET_KEYCOMPONENT_COUNT = @"SELECT COUNT(1) FROM dbo.MKEYCOMPONENT WHERE ( @AssetId <> -1 AND ASSETID = @AssetId OR @ItemId <> -1 AND ITEMID = @ItemId OR @MachineId <> -1 AND MACHINEID = @MachineId ) AND KEYCOMPONENTID <> @KeyComponentId;"; public const string GET_SELECTLIST_KEYCOMPONENT = @"SELECT --Parent : MKEYCOMPONENT KC.KEYCOMPONENTID AS KeyComponentId, KC.NATURE AS KeyComponentNature, KC.ASSETID AS AssetId, A.ASSETCODE AS AssetCode, A.ASSETNAME AS AssetName, KC.ITEMID AS ItemId, I.ITEMCODE AS ItemCode, I.ITEMNAME AS ItemName, KC.RESOURCEID AS ResourceId, R.RESOURCECODE AS ResourceCode, R.RESOURCENAME AS ResourceName, KC.MACHINEID AS MachineId, M.MACHINECODE AS MachineCode, M.MACHINENAME AS MachineName, KC.LOTID AS LotId, L.LOTNUMBER AS LotLotNumber, KC.REMARKS AS KeyComponentRemarks, KC.SOURCETYPE AS KeyComponentSourceType, KC.SORTORDER AS KeyComponentSortOrder, KC.STATUS AS KeyComponentStatus, KC.VERSION AS KeyComponentVersion, KC.CREATEDBYID AS KeyComponentCreatedById, KC.CREATEDON AS KeyComponentCreatedOn, KC.MODIFIEDBYID AS KeyComponentModifiedById, KC.MODIFIEDON AS KeyComponentModifiedOn, KC.TENANTID AS KeyComponentTenantId, -- Child : MKEYCOMPONENTDETAIL KCD.KEYCOMPONENTDETAILID AS KeyComponentDetailId, KCD.KEYCOMPONENTID AS KeyComponentId, KCD.SLNO AS KeyComponentDetailSlNo, KCD.NATURE AS KeyComponentDetailNature, KCD.ASSETID AS AssetId, DA.ASSETCODE AS AssetCode, DA.ASSETNAME AS AssetName, KCD.ITEMID AS ItemId, DI.ITEMCODE AS ItemCode, DI.ITEMNAME AS ItemName, KCD.RESOURCEID AS ResourceId, DR.RESOURCECODE AS ResourceCode, DR.RESOURCENAME AS ResourceName, KCD.MACHINEID AS MachineId, DM.MACHINECODE AS MachineCode, DM.MACHINENAME AS MachineName, KCD.DETAILSLNO AS KeyComponentDetailDetailSlNo, KCD.QUANTITY AS KeyComponentDetailQuantity, KCD.REPLACEDON AS KeyComponentDetailReplacedOn, KCD.LOTID AS LotId, DL.LOTNUMBER AS LotLotNumber, KCD.REMARKS AS KeyComponentDetailRemarks FROM MKEYCOMPONENT KC LEFT JOIN MKEYCOMPONENTDETAIL KCD ON KC.KEYCOMPONENTID = KCD.KEYCOMPONENTID -- Parent Lookups LEFT JOIN MASSET A ON KC.ASSETID = A.ASSETID LEFT JOIN MITEM I ON KC.ITEMID = I.ITEMID LEFT JOIN MRESOURCE R ON KC.RESOURCEID = R.RESOURCEID LEFT JOIN MMACHINE M ON KC.MACHINEID = M.MACHINEID LEFT JOIN TLOT L ON KC.LOTID = L.LOTID -- Child Lookups LEFT JOIN MASSET DA ON KCD.ASSETID = DA.ASSETID LEFT JOIN MITEM DI ON KCD.ITEMID = DI.ITEMID LEFT JOIN MRESOURCE DR ON KCD.RESOURCEID = DR.RESOURCEID LEFT JOIN MMACHINE DM ON KCD.MACHINEID = DM.MACHINEID LEFT JOIN TLOT DL ON KCD.LOTID = DL.LOTID ORDER BY KC.KEYCOMPONENTID, KCD.KEYCOMPONENTDETAILID;"; } }