using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.Query.MaterialAccount
{
    public class MaterialAccountQB
    {
        public const string GET_MATERIALACCOUNT = @"SELECT
                                                       --MATERIAL ACCOUNT (Parent)
                                                    MA.MATERIALACCOUNTID              AS MaterialAccountId,
                                                    MA.MATERIALACCOUNTCODE            AS MaterialAccountCode,
                                                    MA.MATERIALACCOUNTNAME            AS MaterialAccountName,
                                                    MA.ISTRANSACTIONTYPEBASED         AS MaterialAccountIsTransactionTypeBased,

                                                    MA.STOCKACCOUNTID                 AS StockAccountId,
                                                    SA.ACCOUNTCODE                    AS StockAccountCode,
                                                    SA.ACCOUNTNAME                    AS StockAccountName,

                                                    MA.CONSUMPTIONACCOUNTID           AS ConsumptionAccountId,
                                                    CA.ACCOUNTCODE                    AS ConsumptionAccountCode,
                                                    CA.ACCOUNTNAME                    AS ConsumptionAccountName,

                                                    MA.COSTOFGOODSACCOUNTID           AS CostOfGoodsAccountId,
                                                    CGA.ACCOUNTCODE                   AS CostOfGoodsAccountCode,
                                                    CGA.ACCOUNTNAME                   AS CostOfGoodsAccountName,

                                                    MA.WIPACCOUNTID                   AS WipAccountId,
                                                    WA.ACCOUNTCODE                    AS WipAccountCode,
                                                    WA.ACCOUNTNAME                    AS WipAccountName,

                                                    MA.WIPSTOCKACCOUNTID              AS WipStockAccountId,
                                                    WSA.ACCOUNTCODE                   AS WipStockAccountCode,
                                                    WSA.ACCOUNTNAME                   AS WipStockAccountName,

                                                    MA.COGMACCOUNTID                  AS CogmAccountId,
                                                    CMA.ACCOUNTCODE                   AS CogmAccountCode,
                                                    CMA.ACCOUNTNAME                   AS CogmAccountName,

                                                    MA.COSTCENTERID                   AS CostCenterId,
                                                    CC.COSTCENTERCODE                 AS CostCenterCode,
                                                    CC.COSTCENTERNAME                 AS CostCenterName,

                                                    MA.SORTORDER                      AS MaterialAccountSortOrder,
                                                    MA.STATUS                         AS MaterialAccountStatus,
                                                    MA.VERSION                        AS MaterialAccountVersion,
                                                    MA.CREATEDBYID                    AS MaterialAccountCreatedById,
                                                    MA.CREATEDON                      AS MaterialAccountCreatedOn,
                                                    CB.USERNAME                       AS MaterialAccountCreatedByName,
                                                    MA.MODIFIEDBYID                   AS MaterialAccountModifiedById,
                                                    MA.MODIFIEDON                     AS MaterialAccountModifiedOn,
                                                    MB.USERNAME                       AS MaterialAccountModifiedByName,
                                                    MA.SOURCETYPE                     AS SourceType,
                                                    MA.TENANTID                       AS TenantId,

                                                                           --MATERIAL ACCOUNT DETAIL (Child)
                                                                        MAD.MATERIALACCOUNTDETAILID       AS MaterialAccountDetailId,
                                                                        MAD.MATERIALACCOUNTID             AS MaterialAccountId,
                                                                        MA.MATERIALACCOUNTCODE            AS MaterialAccountCode,
                                                                        MA.MATERIALACCOUNTNAME            AS MaterialAccountName,
                                                                        MAD.SLNO                          AS MaterialAccountDetailSlNo,

                                                                        MAD.TRANSACTIONTYPEID             AS TransactionTypeId,
                                                                        TT.TRANSACTIONTYPECODE            AS TransactionTypeCode,
                                                                        TT.TRANSACTIONTYPENAME            AS TransactionTypeName,
                                                                        TT.TYPE                           AS TransactionTypeType,
                                                                        TT.TRANSACTIONTYPE                AS TransactionTypeTranscType,

                                                                        MAD.SALESACCOUNTID                AS SalesAccountId,
                                                                        SA2.ACCOUNTCODE                   AS SalesAccountCode,
                                                                        SA2.ACCOUNTNAME                   AS SalesAccountName,

                                                                        MAD.SALESRETURNACCOUNTID          AS SalesReturnAccountId,
                                                                        SRA.ACCOUNTCODE                   AS SalesReturnAccountCode,
                                                                        SRA.ACCOUNTNAME                   AS SalesReturnAccountName,

                                                                        MAD.PURCHASEACCOUNTID             AS PurchaseAccountId,
                                                                        PA.ACCOUNTCODE                    AS PurchaseAccountCode,
                                                                        PA.ACCOUNTNAME                    AS PurchaseAccountName,

                                                                        MAD.PURCHASERETURNACCOUNTID       AS PurchaseReturnAccountId,
                                                                        PRA.ACCOUNTCODE                   AS PurchaseReturnAccountCode,
                                                                        PRA.ACCOUNTNAME                   AS PurchaseReturnAccountName,

                                                                        MAD.COSTCENTERID                  AS CostCenterId,
                                                                        CC2.COSTCENTERCODE                AS CostCenterCode,
                                                                        CC2.COSTCENTERNAME                AS CostCenterName

                                                FROM MMATERIALACCOUNT MA
                                                LEFT JOIN MMATERIALACCOUNTDETAIL MAD ON MA.MATERIALACCOUNTID = MAD.MATERIALACCOUNTID
                                                LEFT JOIN MACCOUNT SA   ON MA.STOCKACCOUNTID = SA.ACCOUNTID
                                                LEFT JOIN MACCOUNT CA   ON MA.CONSUMPTIONACCOUNTID = CA.ACCOUNTID
                                                LEFT JOIN MACCOUNT CGA  ON MA.COSTOFGOODSACCOUNTID = CGA.ACCOUNTID
                                                LEFT JOIN MACCOUNT WA   ON MA.WIPACCOUNTID = WA.ACCOUNTID
                                                LEFT JOIN MACCOUNT WSA  ON MA.WIPSTOCKACCOUNTID = WSA.ACCOUNTID
                                                LEFT JOIN MACCOUNT CMA  ON MA.COGMACCOUNTID = CMA.ACCOUNTID
                                                LEFT JOIN MUSER    CB   ON MA.CREATEDBYID = CB.USERID
                                                LEFT JOIN MUSER    MB   ON MA.MODIFIEDBYID = MB.USERID
                                                LEFT JOIN MACCOUNT SA2  ON MAD.SALESACCOUNTID = SA2.ACCOUNTID
                                                LEFT JOIN MACCOUNT SRA  ON MAD.SALESRETURNACCOUNTID = SRA.ACCOUNTID
                                                LEFT JOIN MACCOUNT PA   ON MAD.PURCHASEACCOUNTID = PA.ACCOUNTID
                                                LEFT JOIN MACCOUNT PRA  ON MAD.PURCHASERETURNACCOUNTID = PRA.ACCOUNTID
                                                LEFT JOIN MTRANSACTIONTYPE TT ON MAD.TRANSACTIONTYPEID = TT.TRANSACTIONTYPEID
                                                LEFT JOIN MCOSTCENTER CC ON MA.COSTCENTERID = CC.COSTCENTERID
                                                LEFT JOIN MCOSTCENTER CC2 ON MAD.COSTCENTERID = CC2.COSTCENTERID
                                                WHERE MA.MATERIALACCOUNTID = @materialaccountid;";

        public const string SAVE_MATERIALACCOUNT = @"INSERT INTO MMATERIALACCOUNT
                                                (
                                                    MATERIALACCOUNTID,
                                                    MATERIALACCOUNTCODE,
                                                    MATERIALACCOUNTNAME,
                                                    ISTRANSACTIONTYPEBASED,
                                                    STOCKACCOUNTID,
                                                    CONSUMPTIONACCOUNTID,
                                                    COSTOFGOODSACCOUNTID,
                                                    WIPACCOUNTID,
                                                    WIPSTOCKACCOUNTID,
                                                    COGMACCOUNTID,
                                                    COSTCENTERID,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    SOURCETYPE,
                                                    TENANTID
                                                )
                                                VALUES
                                                (
                                                    @MaterialAccountId,
                                                    @MaterialAccountCode,
                                                    @MaterialAccountName,
                                                    @MaterialAccountIsTransactionTypeBased,
                                                    @StockAccountId,
                                                    @ConsumptionAccountId,
                                                    @CostOfGoodsAccountId,
                                                    @WipAccountId,
                                                    @WipStockAccountId,
                                                    @CogmAccountId,
                                                    @CostCenterId,
                                                    @MaterialAccountSortOrder,
                                                    @MaterialAccountStatus,
                                                    @MaterialAccountVersion,
                                                    @MaterialAccountCreatedById,
                                                    @MaterialAccountCreatedOn,
                                                    @MaterialAccountModifiedById,
                                                    @MaterialAccountModifiedOn,
                                                    @SourceType,
                                                    @TenantId
                                                );";

        public const string SAVE_MATERIALACCOUNT_DETAIL = @"INSERT INTO MMATERIALACCOUNTDETAIL
                                                            (
                                                                MATERIALACCOUNTDETAILID,
                                                                MATERIALACCOUNTID,
                                                                SLNO,
                                                                TRANSACTIONTYPEID,
                                                                SALESACCOUNTID,
                                                                SALESRETURNACCOUNTID,
                                                                PURCHASEACCOUNTID,
                                                                PURCHASERETURNACCOUNTID,
                                                                COSTCENTERID
                                                            )
                                                            VALUES
                                                            (
                                                                @MaterialAccountDetailId,
                                                                @MaterialAccountId,
                                                                @MaterialAccountDetailSlNo,
                                                                @TransactionTypeId,
                                                                @SalesAccountId,
                                                                @SalesReturnAccountId,
                                                                @PurchaseAccountId,
                                                                @PurchaseReturnAccountId,
                                                                @CostCenterId
                                                            );";

        public const string UPDATE_MATERIALACCOUNT = @"UPDATE MMATERIALACCOUNT
                                                        SET
                                                            MATERIALACCOUNTCODE          = @MaterialAccountCode,
                                                            MATERIALACCOUNTNAME          = @MaterialAccountName,
                                                            ISTRANSACTIONTYPEBASED       = @MaterialAccountIsTransactionTypeBased,
                                                            STOCKACCOUNTID               = @StockAccountId,
                                                            CONSUMPTIONACCOUNTID         = @ConsumptionAccountId,
                                                            COSTOFGOODSACCOUNTID         = @CostOfGoodsAccountId,
                                                            WIPACCOUNTID                 = @WipAccountId,
                                                            WIPSTOCKACCOUNTID            = @WipStockAccountId,
                                                            COGMACCOUNTID                = @CogmAccountId,
                                                            COSTCENTERID                 = @CostCenterId,
                                                            SORTORDER                    = @MaterialAccountSortOrder,
                                                            STATUS                       = @MaterialAccountStatus,
                                                            VERSION                      = @MaterialAccountVersion,
                                                            MODIFIEDBYID                 = @MaterialAccountModifiedById,
                                                            MODIFIEDON                   = @MaterialAccountModifiedOn,
                                                            SOURCETYPE                   = @SourceType,
                                                            TENANTID                     = @TenantId
                                                        WHERE
                                                            MATERIALACCOUNTID            = @MaterialAccountId;";

        public const string UPDATE_MATERIALACCOUNT_DETAIL = @"UPDATE MMATERIALACCOUNTDETAIL
                                                    SET
                                                        MATERIALACCOUNTID        = @MaterialAccountId,
                                                        SLNO                     = @MaterialAccountDetailSlNo,
                                                        TRANSACTIONTYPEID        = @TransactionTypeId,
                                                        SALESACCOUNTID           = @SalesAccountId,
                                                        SALESRETURNACCOUNTID     = @SalesReturnAccountId,
                                                        PURCHASEACCOUNTID        = @PurchaseAccountId,
                                                        PURCHASERETURNACCOUNTID  = @PurchaseReturnAccountId,
                                                        COSTCENTERID             = @CostCenterId
                                                    WHERE
                                                        MATERIALACCOUNTDETAILID  = @MaterialAccountDetailId;";

        public const string DELETE_MATERIALACCOUNT = @"DELETE FROM MMATERIALACCOUNT 
                                                        WHERE MATERIALACCOUNTID = @materialaccountid;";

        public const string DELETE_MATERIALACCOUNT_DETAIL = @"DELETE FROM MMATERIALACCOUNTDETAIL
                                                                WHERE MATERIALACCOUNTID = @materialaccountid;";


        public const string GET_MATERIALACCOUNT_COUNT = @"SELECT COUNT(1)
                                                    FROM dbo.MMATERIALACCOUNT
                                                    WHERE
                                                        (
                                                            @MaterialAccountId <> -1 AND MATERIALACCOUNTID = @MaterialAccountId
                                                        )
                                                        AND MATERIALACCOUNTID <> @MaterialAccountId";

        public const string GET_SELECTLIST_MATERIALACCOUNT = @"SELECT
                                                            MA.MATERIALACCOUNTID               AS Id,
                                                            MA.MATERIALACCOUNTCODE             AS Code,
                                                            MA.MATERIALACCOUNTNAME             AS Name
                                                    FROM MMATERIALACCOUNT MA;";

    }
}