Database

Member 10283191 wrote:

I want to get this resutl:

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Table_provider
 ID_Provider
 Name_Provider

 Table_purchase
 ID_Purchase
 Date_Purchase
 ID_Provider
 Amount

 Table_payment
 ID_payment
 Date_payment
 ID_Provider
 Amount

SELECT Provider.Name_Provider. Sum(purchase.Amount), sum(Payment.Amount)

CHill60 wrote:

Personally I would have single transaction table with a transaction type column but each to their own

Quote:

As an invoicing exercise the link between purchase and payment is never designed as 1-1.

ALTER PROCEDURE [dbo].[Usp_Add_LookupRecord] (@LookupTableId int,
    @Type varchar(100), @Description varchar(500), @ForeignKeyId int, @CreatedBy varchar(500), @ModifiedBy varchar(500), @IsValid bit<br />
)
AS
BEGIN
    declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
    declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')

    IF (@LookupTableName='ProgramTypeLKP')
    BEGIN
        IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE [Type]=@Type AND ISNULL(IsValid, 1)=1)
        BEGIN
            INSERT INTO dbo.ProgramTypeLKP ([Type], [Description], CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
            VALUES(LEFT(@Type, 15), LEFT(@Description, 500), GETDATE(), LEFT(@CreatedBy, 500), GETDATE(), LEFT(@ModifiedBy, 500), @IsValid)
            SET @InsertedId=SCOPE_IDENTITY()
        END
        ELSE
        BEGIN
            RETURN 0
        END
    END
    ELSE IF (@LookupTableName='AddressTypeLKP')
    BEGIN
        IF NOT EXISTS(SELECT Top 1 1 FROM dbo.AddressTypeLKP WHERE [Description]=@Type AND ISNULL(IsValid, 1)=1)
        BEGIN
            INSERT INTO dbo.AddressTypeLKP ([Description], FKProgramTypeLKPId, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
            VALUES(LEFT(@Description, 20), @FKProgramTypeLKPId, GETDATE(), LEFT(@CreatedBy, 30), GETDATE(), LEFT(@ModifiedBy, 30), @IsValid)
            SET @InsertedId=SCOPE_IDENTITY()
        END
        ELSE
        BEGIN
            RETURN 0
        END
    END

    RETURN @InsertedId
END

indian143 wrote:

IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE [Type]=@Type AND ISNULL(IsValid, 1)=1)

indian143 wrote:

declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')

var returnValue = new SqlParameter() 
{  
    ParameterName = "@ReturnValue",
    SqlDbType = SqlDbType.Int,
    Direction = System.Data.ParameterDirection.Output  
};  

context.Database.ExecuteSqlCommand("exec dbo.Usp_Add_LookupRecord", returnValue, ...other parameters here...);

int result = (int)returnValue.Value;

int result = context.Database.SqlQuery<int>("exec dbo.Usp_Add_LookupRecord", ... parameters here ...).First();

        public virtual int Usp_Add_LookupRecord(Nullable<int> lookupTableId, string type, string description, Nullable<int> foreignKeyId, string createdBy, string modifiedBy, Nullable<bool> isValid)
        {
            var lookupTableIdParameter = lookupTableId.HasValue ?
                new ObjectParameter("LookupTableId", lookupTableId) :
                new ObjectParameter("LookupTableId", typeof(int));

            var typeParameter = type != null ?
                new ObjectParameter("Type", type) :
                new ObjectParameter("Type", typeof(string));

            var descriptionParameter = description != null ?
                new ObjectParameter("Description", description) :
                new ObjectParameter("Description", typeof(string));

            var foreignKeyIdParameter = foreignKeyId.HasValue ?
                new ObjectParameter("ForeignKeyId", foreignKeyId) :
                new ObjectParameter("ForeignKeyId", typeof(int));

            var createdByParameter = createdBy != null ?
                new ObjectParameter("CreatedBy", createdBy) :
                new ObjectParameter("CreatedBy", typeof(string));

            var modifiedByParameter = modifiedBy != null ?
                new ObjectParameter("ModifiedBy", modifiedBy) :
                new ObjectParameter("ModifiedBy", typeof(string));

            var isValidParameter = isValid.HasValue ?
                new ObjectParameter("IsValid", isValid) :
                new ObjectParameter("IsValid", typeof(bool));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("Usp_Add_LookupRecord", lookupTableIdParameter, typeParameter, descriptionParameter, foreignKeyIdParameter, createdByParameter, modifiedByParameter, isValidParameter);
        }

ALTER PROCEDURE [dbo].[Usp_Add_LookupRecord] (@LookupTableId int,
    @Type varchar(100), @Description varchar(500), @ForeignKeyId int, @CreatedBy varchar(500), @ModifiedBy varchar(500), @IsValid bit<br />
)
AS
BEGIN
    declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
    declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')

    IF (@LookupTableName='ProgramTypeLKP')
    BEGIN
        IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE [Type]=@Type AND ISNULL(IsValid, 1)=1)
        BEGIN
            INSERT INTO dbo.ProgramTypeLKP ([Type], [Description], CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
            VALUES(LEFT(@Type, 15), LEFT(@Description, 500), GETDATE(), LEFT(@CreatedBy, 500), GETDATE(), LEFT(@ModifiedBy, 500), @IsValid)
            SET @InsertedId=SCOPE_IDENTITY()
        END
        ELSE
        BEGIN
            RETURN -3
        END
    END
    ELSE IF (@LookupTableName='AddressTypeLKP')
    BEGIN
        IF NOT EXISTS(SELECT Top 1 1 FROM dbo.AddressTypeLKP WHERE [Description]=@Type AND ISNULL(IsValid, 1)=1)
        BEGIN
            INSERT INTO dbo.AddressTypeLKP ([Description], FKProgramTypeLKPId, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
            VALUES(LEFT(@Description, 20), @FKProgramTypeLKPId, GETDATE(), LEFT(@CreatedBy, 30), GETDATE(), LEFT(@ModifiedBy, 30), @IsValid)
            SET @InsertedId=SCOPE_IDENTITY()
        END
        ELSE
        BEGIN
            RETURN -3
        END
    END

    RETURN @InsertedId
END

public virtual int Usp_Add_LookupRecord(int? lookupTableId, string type, string description, int? foreignKeyId, string createdBy, string modifiedBy, bool? isValid)
{
    var returnValue = new SqlParameter
    {  
        ParameterName = "@ReturnValue",
        SqlDbType = SqlDbType.Int,
        Direction = System.Data.ParameterDirection.Output  
    };
    
    var lookupTableIdParameter = new SqlParameter("LookupTableId", SqlDbType.Int) 
    {
        Value = (object)lookupTableId ?? DBNull.Value
    };

    var typeParameter = new SqlParameter("Type", SqlDbType.VarChar, 100)
    {
        Value = (object)type ?? DBNull.Value
    };

    var descriptionParameter = new SqlParameter("Description", SqlDbType.VarChar, 500)
    {
        Value = (object)description ?? DBNull.Value
    };

    var foreignKeyIdParameter = new SqlParameter("ForeignKeyId", SqlDbType.Int)
    {
        Value = (object)foreignKeyId ?? DBNull.Value
    };

    var createdByParameter = new SqlParameter("CreatedBy", SqlDbType.VarChar, 500)
    {
        Value = (object)createdBy ?? DBNull.Value
    };

    var modifiedByParameter = new SqlParameter("ModifiedBy", SqlDbType.VarChar, 500)
    {
        Value = (object)modifiedBy ?? DBNull.Value
    };

    var isValidParameter = new SqlParameter("IsValid", SqlDbType.Bit)
    {
        Value = (object)isValid ?? DBNull.Value
    };
    
    Database.ExecuteSqlCommand("exec dbo.Usp_Add_LookupRecord", 
        returnValue, 
        lookupTableIdParameter, 
        typeParameter, 
        descriptionParameter, 
        foreignKeyIdParameter, 
        createdByParameter, 
        modifiedByParameter, 
        isValidParameter);

    return (int)returnValue.Value;
}