using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FMDAL.Query.Asset { public static class AssetQB { public const string GetAsset = @"SELECT A.ASSETID AS AssetId, A.ASSETCODE AS AssetCode, A.ASSETNAME AS AssetName, A.ASSETTYPEID AS AssetTypeId, AT.ASSETTYPECODE AS AssetTypeCode, AT.ASSETTYPENAME AS AssetTypeName, A.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, PB.PARTYBRANCHSHORTNAME AS PartyBranchShortName, A.PARTYBRANCHLOCATIONID AS PartyBranchLocationId, L.PARTYBRANCHLOCATIONCODE AS LocationCode, L.PARTYBRANCHLOCATIONNAME AS LocationName, A.CATEGORYID AS CategoryId, C.ITEMCATEGORYCODE AS CategoryCode, C.ITEMCATEGORYNAME AS CategoryName, A.SUBCATEGORYID AS SubCategoryId, SC.ITEMSUBCATEGORYCODE AS SubCategoryCode, SC.ITEMSUBCATEGORYNAME AS SubCategoryName, A.MODELID AS ModelId, M.MODELCODE AS ModelCode, M.MODELNAME AS ModelName, M.SHORTNAME AS ModelShortName, M.ITEMBRANDID AS BrandId, B.ITEMBRANDCODE AS BrandCode, B.ITEMBRANDNAME AS BrandName, A.YOM AS AssetYOM, A.CAPACITY AS AssetCapacity, A.CAPACITYUOMID AS UOMId, U.UOMCODE AS UOMCode, U.UOMNAME AS UOMName, A.ITEMID AS ItemId, I.ITEMCODE AS ItemCode, I.ITEMNAME AS ItemName, I.ITEMSHORTNAME AS ItemShortName, A.RUNNINGSTATUS AS AssetRunningStatus, A.ISCRITICAL AS AssetIsCritical, A.SERVICESTATUS AS AssetServiceStatus, A.SERVICEFROMDATE AS AssetServiceFromDate, A.SERVICETODATE AS AssetServiceToDate, A.SERVICEPROVIDERID AS ServiceProviderId, SP.PARTYCODE AS ServiceProviderCode, SP.PARTYNAME AS ServiceProviderName, A.SUPPLIERID AS SupplierId, S.PARTYCODE AS SupplierCode, S.PARTYNAME AS SupplierName, A.BILLNUMBER AS AssetBillNumber, A.BILLDATE AS AssetBillDate, A.VALUEOFASSETINBILL AS AssetValueofAssetInBill, A.PURCHASEDATE AS AssetPurchaseDate, A.INSTALLATIONDATE AS AssetInstallationDate, A.ASSETSTATUS AS AssetAssetOStatus, A.ENERGYMETERINGTYPE AS AssetEnergyMeteringType, A.METERID AS MeterId, MT.METERNAME AS MeterName, A.METERSHAREID AS MeterShareId, NULL AS MeterShareCode, -- not in mapping NULL AS MeterShareName, -- not in mapping A.ASSETVALUE AS AssetAssetValue, A.MFRSLNO AS AssetMfrSlNo, NULL AS AssetTypeAsset, -- not in mapping A.PARAMETERMAPID AS ParameterMapId, PM.PARAMETERMAPCODE AS ParameterMapCode, PM.PARAMETERMAPNAME AS ParameterMapName, A.SORTORDER AS AssetSortOrder, A.STATUS AS AssetStatus, A.VERSION AS AssetVersion, A.CREATEDBYID AS AssetCreatedById, A.CREATEDON AS AssetCreatedOn, A.MODIFIEDBYID AS AssetModifiedById, U_CREATED.USERNAME AS AssetCreatedByName, -- not in mapping A.MODIFIEDON AS AssetModifiedOn, U_MODIFIED.USERNAME AS AssetModifiedByName, -- not in mapping A.SOURCETYPE AS SourceType, A.TYPE AS Type, A.ASSETBATCH AS AssetBatch, A.OUID AS OUID, OU.ORGANIZATIONUNITNAME AS OUName, OU.ORGANIZATIONUNITCODE AS OUCode, A.DESCRIPTION AS Description, A.QUANTITY AS Quantity, A.INCHARGEID AS InchargeId, E.EMPLOYEECODE AS InchargeCode, E.EMPLOYEENAME AS InchargeName, A.DEPARTMENTID AS DepartmentId, D.DEPARTMENTCODE AS DepartmentCode, D.DEPARTMENTNAME AS DepartmentName, A.ASSETWIPACCOUNTID AS AssetWIPAccountId, AW.ACCOUNTCODE AS AssetWipAccountCode, AW.ACCOUNTNAME AS AssetWipAccountName, A.ASSETACCOUNTID AS AssetAccountId, AA.ACCOUNTCODE AS AssetAccountCode, AA.ACCOUNTNAME AS AssetAccountName, A.DEPRECIATIONACCOUNTID AS DepreciationAccountId, DA.ACCOUNTCODE AS DepreciationAccountCode, DA.ACCOUNTNAME AS DepreciationAccountName, A.ACCUMULATEDDEPRECIATIONACCOUNTID AS AccumulatedDepreciationAccountId, ADA.ACCOUNTCODE AS AccumulatedDepreciationAccountCode, ADA.ACCOUNTNAME AS AccumulatedDepreciationAccountName, A.PURCHASEORDERNUMBER AS AssetPurchaseOrderNumber, A.PURCHASEORDERVALUE AS AssetPurchaseOrderValue, A.THUMBNAIL AS AssetThumbNail, -- RAM (Returnable Asset Management) A.CURRENTBINID AS CurrentBinId, A.CURRENTLOCATIONID AS CurrentLocationId, A.CURRENTCUSTODIANTYPE AS CurrentCustodianType, A.CURRENTCUSTODIANID AS CurrentCustodianId, A.CURRENTASSETSTATEID AS CurrentAssetStateId, A.LASTMOVEMENTON AS LastMovementOn, A.CYCLECOUNT AS CycleCount, -- AssetParameterSetDetail DTL.ASSETPARAMETERSETDETAILID AS AssetParameterSetDetailId, DTL.ASSETID AS AssetId, A.ASSETCODE AS AssetCode, A.ASSETNAME AS AssetName, DTL.SLNO AS AssetParameterSetDetailSlNo, DTL.PARAMETERID AS ParameterId, P.PARAMETERCODE AS ParameterCode, P.PARAMETERNAME AS ParameterName, DTL.UOMID AS UOMId, U2.UOMCODE AS UOMCode, U2.UOMNAME AS UOMName, DTL.MINVALUE AS AssetParameterSetDetailMinValue, DTL.MAXVALUE AS AssetParameterSetDetailMaxValue, DTL.FIELDMIN AS AssetParameterSetDetailFieldMin, DTL.FIELDMAX AS AssetParameterSetDetailFieldMax, DTL.ACTIVITYID AS ActivityId, ACT.ACTIVITYCODE AS ActivityCode, ACT.ACTIVITYNAME AS ActivityName FROM MASSET A LEFT JOIN MASSETTYPE AT ON AT.ASSETTYPEID = A.ASSETTYPEID LEFT JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = A.PARTYBRANCHID LEFT JOIN MPARTYBRANCHLOCATION L ON L.PARTYBRANCHLOCATIONID = A.PARTYBRANCHLOCATIONID LEFT JOIN MITEMCATEGORY C ON C.ITEMCATEGORYID = A.CATEGORYID LEFT JOIN MITEMSUBCATEGORY SC ON SC.ITEMSUBCATEGORYID = A.SUBCATEGORYID LEFT JOIN MMODEL M ON M.MODELID = A.MODELID LEFT JOIN MITEMBRAND B ON B.ITEMBRANDID = M.ITEMBRANDID LEFT JOIN MUOM U ON U.UOMID = A.CAPACITYUOMID LEFT JOIN MITEM I ON I.ITEMID = A.ITEMID LEFT JOIN MPARTY SP ON SP.PARTYID = A.SERVICEPROVIDERID LEFT JOIN MPARTY S ON S.PARTYID = A.SUPPLIERID LEFT JOIN MMETER MT ON MT.METERID = A.METERID LEFT JOIN MPARAMETERMAP PM ON PM.PARAMETERMAPID = A.PARAMETERMAPID LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = A.OUID LEFT JOIN MEMPLOYEE E ON E.EMPLOYEEID = A.INCHARGEID LEFT JOIN MDEPARTMENT D ON D.DEPARTMENTID = A.DEPARTMENTID LEFT JOIN MACCOUNT AW ON AW.ACCOUNTID = A.ASSETWIPACCOUNTID LEFT JOIN MACCOUNT AA ON AA.ACCOUNTID = A.ASSETACCOUNTID LEFT JOIN MACCOUNT DA ON DA.ACCOUNTID = A.DEPRECIATIONACCOUNTID LEFT JOIN MACCOUNT ADA ON ADA.ACCOUNTID = A.ACCUMULATEDDEPRECIATIONACCOUNTID LEFT JOIN MUSER U_CREATED ON A.CREATEDBYID = U_CREATED.USERID LEFT JOIN MUSER U_MODIFIED ON A.MODIFIEDBYID = U_MODIFIED.USERID -- child table LEFT JOIN MASSETPARAMETERSETDETAIL DTL ON DTL.ASSETID = A.ASSETID LEFT JOIN MPARAMETER P ON P.PARAMETERID = DTL.PARAMETERID LEFT JOIN MUOM U2 ON U2.UOMID = DTL.UOMID LEFT JOIN MACTIVITY ACT ON ACT.ACTIVITYID = DTL.ACTIVITYID WHERE A.ASSETID = @assetid;"; // CriteriaBuilder's ApplyCriteria wraps this in a derived table and strips table-alias // prefixes from the WHERE clause, so criteria FieldNames resolve against the OUTER // SELECT aliases exposed here — not the raw MASSET/MASSETTYPE column names. AssetTypeId // is exposed as an extra alias since no existing picklist column covers it. FE criteria // using the full names AssetId/AssetCode/AssetName are remapped onto the existing // Id/Code/Name aliases in AssetDAL.GetSelectListAsset — no need to duplicate those // columns here. public const string GET_SELECTLIST_ASSET = @"SELECT A.ASSETID AS Id, A.ASSETCODE AS Code, A.ASSETNAME AS Name, A.ASSETBATCH AS AssetBatch, B.LIFE AS DepLife, A.THUMBNAIL AS ThumbNail, A.ASSETTYPEID AS AssetTypeId FROM MASSET A JOIN MASSETTYPE B ON A.ASSETTYPEID = B.ASSETTYPEID WHERE 1=1 {DYNAMIC_WHERE}"; // Column list below is confirmed against the existing, already-working GetAsset SELECT // (this repo currently has no live gb5-schema MCP access and MASSET has no schema-registry // entry, so this SELECT is the verification source of truth per CLAUDE.md's offline fallback). public const string SAVE_ASSET = @"INSERT INTO MASSET ( ASSETID, ASSETCODE, ASSETNAME, ASSETTYPEID, PARTYBRANCHID, PARTYBRANCHLOCATIONID, CATEGORYID, SUBCATEGORYID, MODELID, BRANDID, YOM, CAPACITY, CAPACITYUOMID, ITEMID, RUNNINGSTATUS, ISCRITICAL, SERVICESTATUS, SERVICEFROMDATE, SERVICETODATE, SERVICEPROVIDERID, SUPPLIERID, BILLNUMBER, BILLDATE, VALUEOFASSETINBILL, PURCHASEDATE, INSTALLATIONDATE, ASSETSTATUS, ENERGYMETERINGTYPE, METERID, METERSHAREID, ASSETVALUE, MFRSLNO, PARAMETERMAPID, SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TYPE, ASSETBATCH, OUID, DESCRIPTION, QUANTITY, INCHARGEID, DEPARTMENTID, ASSETWIPACCOUNTID, ASSETACCOUNTID, DEPRECIATIONACCOUNTID, ACCUMULATEDDEPRECIATIONACCOUNTID, PURCHASEORDERNUMBER, PURCHASEORDERVALUE, THUMBNAIL, CURRENTBINID, CURRENTLOCATIONID, CURRENTCUSTODIANTYPE, CURRENTCUSTODIANID, CURRENTASSETSTATEID, LASTMOVEMENTON, CYCLECOUNT) VALUES ( @AssetId, @AssetCode, @AssetName, @AssetTypeId, @PartyBranchId, @LocationId, @CategoryId, @SubCategoryId, @ModelId, @BrandId, @AssetYOM, @AssetCapacity, @UOMId, @ItemId, @AssetRunningStatus, @AssetIsCritical, @AssetServiceStatus, @AssetServiceFromDate, @AssetServiceToDate, @ServiceProviderId, @SupplierId, @AssetBillNumber, @AssetBillDate, @AssetValueofAssetInBill, @AssetPurchaseDate, @AssetInstallationDate, @AssetAssetOStatus, @AssetEnergyMeteringType, @MeterId, @MeterShareId, @AssetAssetValue, @AssetMfrSlNo, @ParameterMapId, @AssetSortOrder, @AssetStatus, @AssetVersion, @AssetCreatedById, @AssetCreatedOn, @AssetModifiedById, @AssetModifiedOn, @SourceType, @Type, @AssetBatch, @OUId, @Description, @Quantity, @InchargeId, @DepartmentId, @AssetWipAccountId, @AssetAccountId, @DepreciationAccountId, @AccumulatedDepreciationAccountId, @AssetPurchaseOrderNumber, @AssetPurchaseOrderValue, @AssetThumbNail, @CurrentBinId, @CurrentLocationId, @CurrentCustodianType, @CurrentCustodianId, @CurrentAssetStateId, @LastMovementOn, @CycleCount);"; public const string UPDATE_ASSET = @"UPDATE MASSET SET ASSETCODE = @AssetCode, ASSETNAME = @AssetName, ASSETTYPEID = @AssetTypeId, PARTYBRANCHID = @PartyBranchId, PARTYBRANCHLOCATIONID = @LocationId, CATEGORYID = @CategoryId, SUBCATEGORYID = @SubCategoryId, MODELID = @ModelId, BRANDID = @BrandId, YOM = @AssetYOM, CAPACITY = @AssetCapacity, CAPACITYUOMID = @UOMId, ITEMID = @ItemId, RUNNINGSTATUS = @AssetRunningStatus, ISCRITICAL = @AssetIsCritical, SERVICESTATUS = @AssetServiceStatus, SERVICEFROMDATE = @AssetServiceFromDate, SERVICETODATE = @AssetServiceToDate, SERVICEPROVIDERID = @ServiceProviderId, SUPPLIERID = @SupplierId, BILLNUMBER = @AssetBillNumber, BILLDATE = @AssetBillDate, VALUEOFASSETINBILL = @AssetValueofAssetInBill, PURCHASEDATE = @AssetPurchaseDate, INSTALLATIONDATE = @AssetInstallationDate, ASSETSTATUS = @AssetAssetOStatus, ENERGYMETERINGTYPE = @AssetEnergyMeteringType, METERID = @MeterId, METERSHAREID = @MeterShareId, ASSETVALUE = @AssetAssetValue, MFRSLNO = @AssetMfrSlNo, PARAMETERMAPID = @ParameterMapId, SORTORDER = @AssetSortOrder, STATUS = @AssetStatus, VERSION = VERSION + 1, MODIFIEDBYID = @AssetModifiedById, MODIFIEDON = @AssetModifiedOn, SOURCETYPE = @SourceType, TYPE = @Type, ASSETBATCH = @AssetBatch, OUID = @OUId, DESCRIPTION = @Description, QUANTITY = @Quantity, INCHARGEID = @InchargeId, DEPARTMENTID = @DepartmentId, ASSETWIPACCOUNTID = @AssetWipAccountId, ASSETACCOUNTID = @AssetAccountId, DEPRECIATIONACCOUNTID = @DepreciationAccountId, ACCUMULATEDDEPRECIATIONACCOUNTID = @AccumulatedDepreciationAccountId, PURCHASEORDERNUMBER = @AssetPurchaseOrderNumber, PURCHASEORDERVALUE = @AssetPurchaseOrderValue, THUMBNAIL = @AssetThumbNail, CURRENTBINID = @CurrentBinId, CURRENTLOCATIONID = @CurrentLocationId, CURRENTCUSTODIANTYPE = @CurrentCustodianType, CURRENTCUSTODIANID = @CurrentCustodianId, CURRENTASSETSTATEID = @CurrentAssetStateId, LASTMOVEMENTON = @LastMovementOn, CYCLECOUNT = @CycleCount WHERE ASSETID = @AssetId;"; // Duplicate-batch-uniqueness rule (RAM/migration design note): the same ASSETBATCH must not be // reused for the same ASSETTYPEID across active assets. Shared by FM.SaveAsset and (once its own // schema is confirmed) FAM's TFam save — see AssetBLL.IsDuplicateAssetBatch. // Narrow snapshot-only update — never rewrite the whole MASSET row for a scan event. public const string UPDATE_ASSET_SNAPSHOT = @"UPDATE MASSET SET CURRENTLOCATIONID = @CurrentLocationId, CURRENTCUSTODIANTYPE = @CurrentCustodianType, CURRENTCUSTODIANID = @CurrentCustodianId, CURRENTASSETSTATEID = @CurrentAssetStateId, LASTMOVEMENTON = @LastMovementOn, CYCLECOUNT = @CycleCount WHERE ASSETID = @AssetId;"; // Soft delete — MASSET is a financial/depreciation register, never hard-deleted. // STATUS=2 matches the established GB5 soft-delete convention (e.g. MMETER.DELETE_METER). public const string DELETE_ASSET = @"UPDATE MASSET SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn WHERE ASSETID = @AssetId;"; public const string CHECK_DUPLICATE_ASSET_BATCH = @" SELECT COUNT(1) FROM MASSET A WHERE A.ASSETTYPEID = @AssetTypeId AND A.ASSETBATCH = @AssetBatch AND A.ASSETID <> @ExcludeAssetId AND A.STATUS = 1;"; // MASSETPARAMETERSETDETAIL — child table of MASSET, no audit/status columns // (confirmed via the existing GetAsset SELECT's DTL alias, same as MASSETTYPEACTIVITY). public const string GET_ASSETPARAMETERSETDETAIL_LIST_BY_ASSET = @"SELECT DTL.ASSETPARAMETERSETDETAILID AS AssetParameterSetDetailId, DTL.ASSETID AS AssetId, DTL.SLNO AS AssetParameterSetDetailSlNo, DTL.PARAMETERID AS ParameterId, DTL.UOMID AS UOMId, DTL.MINVALUE AS AssetParameterSetDetailMinValue, DTL.MAXVALUE AS AssetParameterSetDetailMaxValue, DTL.FIELDMIN AS AssetParameterSetDetailFieldMin, DTL.FIELDMAX AS AssetParameterSetDetailFieldMax, DTL.ACTIVITYID AS ActivityId FROM MASSETPARAMETERSETDETAIL DTL WHERE DTL.ASSETID = @AssetId ORDER BY DTL.SLNO;"; public const string SAVE_ASSETPARAMETERSETDETAIL = @"INSERT INTO MASSETPARAMETERSETDETAIL ( ASSETPARAMETERSETDETAILID, ASSETID, SLNO, PARAMETERID, UOMID, MINVALUE, MAXVALUE, FIELDMIN, FIELDMAX, ACTIVITYID) VALUES ( @AssetParameterSetDetailId, @AssetId, @AssetParameterSetDetailSlNo, @ParameterId, @UOMId, @AssetParameterSetDetailMinValue, @AssetParameterSetDetailMaxValue, @AssetParameterSetDetailFieldMin, @AssetParameterSetDetailFieldMax, @ActivityId);"; public const string UPDATE_ASSETPARAMETERSETDETAIL = @"UPDATE MASSETPARAMETERSETDETAIL SET SLNO = @AssetParameterSetDetailSlNo, PARAMETERID = @ParameterId, UOMID = @UOMId, MINVALUE = @AssetParameterSetDetailMinValue, MAXVALUE = @AssetParameterSetDetailMaxValue, FIELDMIN = @AssetParameterSetDetailFieldMin, FIELDMAX = @AssetParameterSetDetailFieldMax, ACTIVITYID = @ActivityId WHERE ASSETPARAMETERSETDETAILID = @AssetParameterSetDetailId;"; public const string DELETE_ASSETPARAMETERSETDETAIL = @"DELETE FROM MASSETPARAMETERSETDETAIL WHERE ASSETPARAMETERSETDETAILID = @AssetParameterSetDetailId;"; } }