DentPal - CONNECT

the tables structures¶

-- Table: public.PatientLedgerEntries

-- DROP TABLE IF EXISTS public."PatientLedgerEntries";

CREATE TABLE IF NOT EXISTS public."PatientLedgerEntries"
(
    "Id" uuid NOT NULL,
    "PatientId" uuid NOT NULL,
    "EntryDate" timestamp without time zone NOT NULL,
    "EntryType" integer NOT NULL,
    "Description" text COLLATE pg_catalog."default",
    "Amount" numeric NOT NULL,
    "Category" integer NOT NULL,
    "SourceId" text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
    "ExtraProperties" text COLLATE pg_catalog."default" NOT NULL,
    "ConcurrencyStamp" character varying(40) COLLATE pg_catalog."default" NOT NULL,
    "CreationTime" timestamp without time zone NOT NULL,
    "CreatorId" uuid,
    "LastModificationTime" timestamp without time zone,
    "LastModifierId" uuid,
    "TenantId" uuid,
    "ClinicId" uuid,
    "PMSSourceSystem" integer NOT NULL,
    CONSTRAINT "PK_PatientLedgerEntries" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."PatientLedgerEntries"
    OWNER to postgres;
-- Index: IX_PatientLedgerEntry_TenantId_ClinicId_SourceId_EntryType_PMSS

-- DROP INDEX IF EXISTS public."IX_PatientLedgerEntry_TenantId_ClinicId_SourceId_EntryType_PMSS";

CREATE UNIQUE INDEX IF NOT EXISTS "IX_PatientLedgerEntry_TenantId_ClinicId_SourceId_EntryType_PMSS"
    ON public."PatientLedgerEntries" USING btree
    ("TenantId" ASC NULLS LAST, "ClinicId" ASC NULLS LAST, "SourceId" COLLATE pg_catalog."default" ASC NULLS LAST, "EntryType" ASC NULLS LAST, "PMSSourceSystem" ASC NULLS LAST)
    TABLESPACE pg_default;

-- Table: public.PatientProviders

-- DROP TABLE IF EXISTS public."PatientProviders";

CREATE TABLE IF NOT EXISTS public."PatientProviders"
(
    "Id" uuid NOT NULL,
    "PMSProviderId" character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "ProviderType" integer NOT NULL,
    "FirstName" character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "LastName" character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "Abbreviation" character varying(20) COLLATE pg_catalog."default",
    "PMSSpecialtyCode" character varying(50) COLLATE pg_catalog."default",
    "PMSSpecialtyName" character varying(100) COLLATE pg_catalog."default",
    "ExtraProperties" text COLLATE pg_catalog."default" NOT NULL,
    "ConcurrencyStamp" character varying(40) COLLATE pg_catalog."default" NOT NULL,
    "CreationTime" timestamp without time zone NOT NULL,
    "CreatorId" uuid,
    "LastModificationTime" timestamp without time zone,
    "LastModifierId" uuid,
    "TenantId" uuid,
    "ClinicId" uuid,
    "PMSSourceSystem" integer NOT NULL,
    CONSTRAINT "PK_PatientProviders" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."PatientProviders"
    OWNER to postgres;
-- Index: IX_Provider_TenantId_ClinicId_PMSProviderId_PMSSourceSystem

-- DROP INDEX IF EXISTS public."IX_Provider_TenantId_ClinicId_PMSProviderId_PMSSourceSystem";

CREATE UNIQUE INDEX IF NOT EXISTS "IX_Provider_TenantId_ClinicId_PMSProviderId_PMSSourceSystem"
    ON public."PatientProviders" USING btree
    ("TenantId" ASC NULLS LAST, "ClinicId" ASC NULLS LAST, "PMSProviderId" COLLATE pg_catalog."default" ASC NULLS LAST, "PMSSourceSystem" ASC NULLS LAST)
    TABLESPACE pg_default;

-- Table: public.Patients

-- DROP TABLE IF EXISTS public."Patients";

CREATE TABLE IF NOT EXISTS public."Patients"
(
    "Id" uuid NOT NULL,
    "TenantId" uuid,
    "FirstName" character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "LastName" character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "BirthDate" timestamp without time zone NOT NULL,
    "SSN" character varying(15) COLLATE pg_catalog."default",
    "PMSSourceSystem" integer NOT NULL,
    "ExtraProperties" text COLLATE pg_catalog."default" NOT NULL,
    "ConcurrencyStamp" character varying(40) COLLATE pg_catalog."default" NOT NULL,
    "CreationTime" timestamp without time zone NOT NULL,
    "CreatorId" uuid,
    "LastModificationTime" timestamp without time zone,
    "LastModifierId" uuid,
    "ClinicId" uuid,
    "MiddleName" character varying(100) COLLATE pg_catalog."default",
    "PMSPatientId" character varying(100) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
    "PreferredName" character varying(100) COLLATE pg_catalog."default",
    "Status" integer NOT NULL DEFAULT 0,
    "Gender" integer NOT NULL DEFAULT 0,
    "MaritalStatus" integer NOT NULL DEFAULT 0,
    "ProviderId" uuid,
    CONSTRAINT "PK_Patients" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Patients"
    OWNER to postgres;
-- Index: IX_Patients_TenantId_ClinicId_PMSPatientId_PMSSourceSystem

-- DROP INDEX IF EXISTS public."IX_Patients_TenantId_ClinicId_PMSPatientId_PMSSourceSystem";

CREATE UNIQUE INDEX IF NOT EXISTS "IX_Patients_TenantId_ClinicId_PMSPatientId_PMSSourceSystem"
    ON public."Patients" USING btree
    ("TenantId" ASC NULLS LAST, "ClinicId" ASC NULLS LAST, "PMSPatientId" COLLATE pg_catalog."default" ASC NULLS LAST, "PMSSourceSystem" ASC NULLS LAST)
    TABLESPACE pg_default;

-- Table: public.PatientProcedures

-- DROP TABLE IF EXISTS public."PatientProcedures";

CREATE TABLE IF NOT EXISTS public."PatientProcedures"
(
    "Id" uuid NOT NULL,
    "PMSProcedureId" character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "PatientId" uuid NOT NULL,
    "PMSProcCode" character varying(20) COLLATE pg_catalog."default" NOT NULL,
    "ProcedureDate" timestamp without time zone NOT NULL,
    "ToothNum" character varying(10) COLLATE pg_catalog."default",
    "Surf" character varying(10) COLLATE pg_catalog."default",
    "Fee" numeric NOT NULL,
    "ExtraProperties" text COLLATE pg_catalog."default" NOT NULL,
    "ConcurrencyStamp" character varying(40) COLLATE pg_catalog."default" NOT NULL,
    "CreationTime" timestamp without time zone NOT NULL,
    "CreatorId" uuid,
    "LastModificationTime" timestamp without time zone,
    "LastModifierId" uuid,
    "TenantId" uuid,
    "ClinicId" uuid,
    "PMSSourceSystem" integer NOT NULL,
    "AppointmentId" uuid,
    "Status" integer,
    "Descript" text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
    "ProviderId" uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'::uuid,
    "Dx" integer,
    "DxName" character varying(50) COLLATE pg_catalog."default",
    CONSTRAINT "PK_PatientProcedures" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."PatientProcedures"
    OWNER to postgres;
-- Index: IX_PatientProcedure_TenantId_ClinicId_PMSProcedureId_PMSSourceS

-- DROP INDEX IF EXISTS public."IX_PatientProcedure_TenantId_ClinicId_PMSProcedureId_PMSSourceS";

CREATE UNIQUE INDEX IF NOT EXISTS "IX_PatientProcedure_TenantId_ClinicId_PMSProcedureId_PMSSourceS"
    ON public."PatientProcedures" USING btree
    ("TenantId" ASC NULLS LAST, "ClinicId" ASC NULLS LAST, "PMSProcedureId" COLLATE pg_catalog."default" ASC NULLS LAST, "PMSSourceSystem" ASC NULLS LAST)
    TABLESPACE pg_default;

-- Table: public.PatientPaymentPlans

-- DROP TABLE IF EXISTS public."PatientPaymentPlans";

CREATE TABLE IF NOT EXISTS public."PatientPaymentPlans"
(
    "Id" uuid NOT NULL,
    "PMSPaymentPlanID" character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "PatientId" uuid NOT NULL,
    "GuarantorId" uuid NOT NULL,
    "StartDate" timestamp without time zone NOT NULL,
    "TotalPrincipal" numeric NOT NULL,
    "APR" numeric NOT NULL,
    "Frequency" character varying(30) COLLATE pg_catalog."default",
    "InstallmentCount" integer NOT NULL,
    "Status" character varying(20) COLLATE pg_catalog."default",
    "Note" text COLLATE pg_catalog."default",
    "ExtraProperties" text COLLATE pg_catalog."default" NOT NULL,
    "ConcurrencyStamp" character varying(40) COLLATE pg_catalog."default" NOT NULL,
    "CreationTime" timestamp without time zone NOT NULL,
    "CreatorId" uuid,
    "LastModificationTime" timestamp without time zone,
    "LastModifierId" uuid,
    "TenantId" uuid,
    "ClinicId" uuid,
    "PMSSourceSystem" integer NOT NULL,
    CONSTRAINT "PK_PatientPaymentPlans" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."PatientPaymentPlans"
    OWNER to postgres;
-- Index: IX_PatientPaymentPlan_TenantId_ClinicId_PMSPaymentPlanID_PMSSou

-- DROP INDEX IF EXISTS public."IX_PatientPaymentPlan_TenantId_ClinicId_PMSPaymentPlanID_PMSSou";

CREATE UNIQUE INDEX IF NOT EXISTS "IX_PatientPaymentPlan_TenantId_ClinicId_PMSPaymentPlanID_PMSSou"
    ON public."PatientPaymentPlans" USING btree
    ("TenantId" ASC NULLS LAST, "ClinicId" ASC NULLS LAST, "PMSPaymentPlanID" COLLATE pg_catalog."default" ASC NULLS LAST, "PMSSourceSystem" ASC NULLS LAST)
    TABLESPACE pg_default;

-- Table: public.PatientPaymentPlanCharges

-- DROP TABLE IF EXISTS public."PatientPaymentPlanCharges";

CREATE TABLE IF NOT EXISTS public."PatientPaymentPlanCharges"
(
    "Id" uuid NOT NULL,
    "PMSPaymentPlanChargeID" character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "PaymentPlanId" uuid NOT NULL,
    "ChargeDate" timestamp without time zone NOT NULL,
    "PrincipalAmount" numeric NOT NULL,
    "InterestAmount" numeric NOT NULL,
    "ProviderId" uuid NOT NULL,
    "ProcedureId" uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'::uuid,
    "Note" text COLLATE pg_catalog."default",
    "ExtraProperties" text COLLATE pg_catalog."default" NOT NULL,
    "ConcurrencyStamp" character varying(40) COLLATE pg_catalog."default" NOT NULL,
    "CreationTime" timestamp without time zone NOT NULL,
    "CreatorId" uuid,
    "LastModificationTime" timestamp without time zone,
    "LastModifierId" uuid,
    "TenantId" uuid,
    "ClinicId" uuid,
    "PMSSourceSystem" integer NOT NULL,
    "PatientId" uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'::uuid,
    CONSTRAINT "PK_PatientPaymentPlanCharges" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."PatientPaymentPlanCharges"
    OWNER to postgres;
-- Index: IX_PatientPaymentPlanCharge_TenantId_ClinicId_PMSPaymentPlanCha

-- DROP INDEX IF EXISTS public."IX_PatientPaymentPlanCharge_TenantId_ClinicId_PMSPaymentPlanCha";

CREATE UNIQUE INDEX IF NOT EXISTS "IX_PatientPaymentPlanCharge_TenantId_ClinicId_PMSPaymentPlanCha"
    ON public."PatientPaymentPlanCharges" USING btree
    ("TenantId" ASC NULLS LAST, "ClinicId" ASC NULLS LAST, "PMSPaymentPlanChargeID" COLLATE pg_catalog."default" ASC NULLS LAST, "PMSSourceSystem" ASC NULLS LAST)
    TABLESPACE pg_default;

Enums If Required

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

namespace DentPal.Patients;


public enum ContactTypeEnum
{
    Email = 0,
    Mobile = 1,
    HomePhone = 2
}

public enum PatientGuarantorRolesEnum
{
    Billing = 0,
    Guardian = 1,
    Emergency = 2,
    Other = 3
}


public enum PatientLedgerEntryTypeEnum
{
    None = 0,
    Procedure = 1,
    Payment = 2,
    InsurancePayment = 3,
    Adjustment = 4,
    Refund = 5,
    WriteOff = 6
}


public enum TreatmentAccountingLedgerEntryTypeDto
{

    None = 0,
    Procedure = 1,
    Payment = 2,
    InsurancePayment = 3,
    Adjustment = 4,
    Refund = 5,
    WriteOff = 6,
    paymentPlan = 7,
    paymentPlanCharge = 8,
}
public enum PatientLedgerCategoryEnum
{
    None = 0,
    Charge = 1,
    Payment = 2,
    Adjustment = 3,
    IsurancePayment = 4
}
public enum TreatmentAccountingLedgerCategoryDto
{

    None = 0,
    Charge = 1,
    Payment = 2,
    Adjustment = 3,
    IsurancePayment = 4,
    PaymentPlan = 5,
}

public enum PatientProviderTypeEnum
{
    Dentist = 1,
    Hygienist = 2,
    Assistant = 3,
    Other = 4

}

public enum GenderEnum
{
    Unknown = 0,
    Male = 1,
    Female = 2,
    Other = 3,
}
public enum MaritalStatusEnum
{
    Uknown = 0,
    Single = 1,
    Married = 2,
    Widowed = 3,
    Divorced = 4,
    Child = 5
}

public enum ProcedureStatusEnum
{
    /// <summary>
    /// Used when the procedure status is not known or doesn't match any predefined state.
    /// </summary>
    Unknown,                // fallback for unexpected values
    /// <summary>
    /// The procedure is planned for the future but not yet completed.
    /// </summary>
    TreatmentPlanned,         // "TP", "P", "PL"

    /// <summary>
    /// The procedure was completed at this clinic.
    /// </summary>
    Completed,                // "C", "CM"

    /// <summary>
    /// The procedure was done in the past by the current provider.
    /// </summary>
    ExistingCurrentProvider,  // "EC", "EX" (same provider)

    /// <summary>
    /// The procedure was done in the past by another provider.
    /// </summary>
    ExistingOtherProvider,    // "EO", "EX" (other provider)

    /// <summary>
    /// The patient was referred to an external provider for this procedure.
    /// </summary>
    ReferredOut,              // "R", "RFO"

    /// <summary>
    /// The procedure was deleted or voided in the chart.
    /// </summary>
    Deleted,                  // "D", "DEL"

    /// <summary>
    /// A condition was observed, but no procedure is planned yet.
    /// </summary>
    Condition,                // "Cn", "CON"

    /// <summary>
    /// The procedure was part of an old or replaced treatment plan.
    /// </summary>
    TreatmentPlanInactive,    // "TPi"

    /// <summary>
    /// The procedure was canceled by the clinic or patient before being completed.
    /// </summary>
    Canceled                 // "CA"


}


public enum PatientRelationshipEnum
{
    Self = 0,
    Spouse = 1,
    Father = 2,
    Mother = 3,
    Guardian = 4,
    Sibling = 5,
    Child = 6,
    Other = 7
}

public enum PatientStatusEnum
{
    Active = 0,
    Inactive = 1,
    Deceased = 2
}

Make this Feature as similar to the Images and Perio Chart

add the paysplit and family modules table support for the carrier and payments