# Template: Customer Relationship Management Dashboard

## Next steps

- Navigate to the customer_relationship_management directory: `cd customer_relationship_management`
- If you plan on using this on a different database resource than the demo database, change the `[Demo DB]` resource in `create_touch_point.task.yaml`, `customer_touch_points.task.yaml`, `customers_list_by_stage.task.yaml`, `edit_customer.task.yaml`, and `upgrade_customer_stage.task.yaml` to the name of your own database resource
- Develop your template locally: `airplane dev`
- Deploy your tasks and view: `airplane deploy --yes`
- Visit the docs to learn more about how to build views: https://docs.airplane.dev/views/getting-started


import {
  Button,
  Stack,
  Table,
  Heading,
  Card,
  useComponentState,
  Dialog,
  Column,
  Tabs,
  Form,
  Label,
  Chip,
} from "@airplane/views";
import airplane from "airplane";

const CustomerRelationshipDashboard = () => {
  return (
    <Stack>
      <Stack direction="row" justify="space-between">
        <Heading>CRM</Heading>
        <CreateLeadButton />
      </Stack>
      <Tabs defaultValue="lead" routingKey="tab">
        <Tabs.Tab value="lead" label="Leads">
          <CustomerTable key="lead" stage="lead" />
        </Tabs.Tab>
        <Tabs.Tab value="opportunity" label="Opportunities">
          <CustomerTable key="opp" stage="opportunity" />
        </Tabs.Tab>
        <Tabs.Tab value="customer" label="Customers">
          <CustomerTable key="cust" stage="customer" />
        </Tabs.Tab>
      </Tabs>
    </Stack>
  );
};

/** A list of all customers in the given stage. */
const CustomerTable = ({
  stage,
}: {
  stage: "lead" | "opportunity" | "customer";
}) => {
  const customersTableState = useComponentState();
  const selectedCustomer: CustomerRowType = customersTableState.selectedRow;

  return (
    <Stack>
      <Table
        id={customersTableState.id}
        title="Customers"
        columns={customersCols}
        defaultPageSize={5}
        task={{
          slug: "demo_list_customers_by_stage",
          params: {
            opportunity_stage: stage,
          },
        }}
        rowSelection="single"
        rowActions={{
          slug: "demo_edit_customer",
          label: "Update",
        }}
      />
      {selectedCustomer && (
        <>
          <Stack direction="row" align="start">
            <CustomerCard
              selectedCustomer={selectedCustomer}
              onDelete={() => {
                customersTableState.clearSelection();
              }}
            />
            <Table
              grow
              title="Customer touch points"
              task={{
                slug: "demo_list_customer_touch_points",
                params: {
                  customer_id: selectedCustomer.customer_id,
                },
              }}
              noData="There are no touch points for this user"
              rowActions={{
                slug: "demo_delete_touch_point_for_customer",
                label: "Delete",
                preset: "danger",
                confirm: {
                  title: "Do you want to delete this touch point?",
                  confirmText: "Yes",
                  cancelText: "Cancel",
                  body: "",
                },
              }}
              hiddenColumns={["customer_id"]}
            />
          </Stack>
        </>
      )}
    </Stack>
  );
};

/**
 * Displays details about a customer.
 * Supports customer promotion, deletion, and touch point creation.
 */
const CustomerCard = ({
  selectedCustomer,
  onDelete,
}: {
  selectedCustomer: CustomerRowType;
  onDelete: () => void;
}) => {
  const nextStage = getNextStage(selectedCustomer.opportunity_stage);
  return (
    <Card width="2/5">
      <Stack>
        <Stack direction="row" justify="space-between" align="center">
          <Label size="lg">{selectedCustomer.contact_name}</Label>
          <Chip color="green">{selectedCustomer.contact_title}</Chip>
        </Stack>
        <Label color="gray">{selectedCustomer.phone}</Label>
        <Label color="gray">
          {`${selectedCustomer.address}, ${selectedCustomer.country}`}
        </Label>

        <Stack direction="row" justify="end">
          {nextStage && (
            <Button
              preset="secondary"
              task={{
                slug: "demo_update_customer_stage",
                params: {
                  opportunity_stage: nextStage,
                  customer_id: selectedCustomer.customer_id,
                },
                refetchTasks: {
                  slug: "demo_list_customers_by_stage",
                },
                onSuccess: () => {
                  onDelete();
                },
              }}
              disabled={selectedCustomer.opportunity_stage == "customer"}
              confirm={{
                title: `Do you want to upgrade this customer to ${nextStage}?`,
                body: "",
                confirmText: "Yes",
                cancelText: "Cancel",
              }}
            >
              {`Convert to ${nextStage}`}
            </Button>
          )}
          <CreateTouchPointButton selectedCustomer={selectedCustomer} />
          <Button
            preset="danger"
            task={{
              slug: "demo_delete_customer",
              params: {
                customer_id: selectedCustomer.customer_id,
              },
              onSuccess: () => {
                onDelete();
              },
              refetchTasks: {
                slug: "demo_list_customers_by_stage",
                params: {
                  opportunity_stage: selectedCustomer.opportunity_stage,
                },
              },
            }}
            confirm={{
              title: `Do you want to delete this customer?`,
              body: `You can create another entry at any time`,
              confirmText: "Yes",
              cancelText: "Cancel",
            }}
          >
            Delete
          </Button>
        </Stack>
      </Stack>
    </Card>
  );
};

/** Button that creates a new lead via user input. */
const CreateLeadButton = () => {
  const dialogState = useComponentState();

  return (
    <>
      <Button preset="secondary" onClick={dialogState.open}>
        Create lead
      </Button>

      <Dialog
        id={dialogState.id}
        title="Create lead"
        onClose={dialogState.close}
      >
        <Form task="demo_create_lead" />
      </Dialog>
    </>
  );
};

/** Button that creates a new touch point via user input. */
const CreateTouchPointButton = ({
  selectedCustomer,
}: {
  selectedCustomer: CustomerRowType;
}) => {
  const dialogState = useComponentState();

  return (
    <>
      <Button preset="secondary" onClick={dialogState.open}>
        Add touch point
      </Button>

      <Dialog
        id={dialogState.id}
        title="Add touch point"
        onClose={dialogState.close}
      >
        <Card>
          <Stack>
            <Form
              task={{
                slug: "demo_create_touch_point_for_customer",
                hiddenFields: ["customer_id"],
                fieldOptions: [
                  { slug: "customer_id", value: selectedCustomer.customer_id },
                  {
                    slug: "touch_point_type",
                    allowedValues: ["email", "phone", "video", "in person"],
                  },
                ],
              }}
            />
          </Stack>
        </Card>
      </Dialog>
    </>
  );
};

interface CustomerRowType {
  customer_id: string;
  company_name: string;
  contact_name: string;
  contact_title: string;
  address: string;
  country: string;
  opportunity_stage: string;
  phone: string;
  touch_points: number;
}

const customersCols = [
  { accessor: "company_name", label: "Company name", canEdit: true },
  { accessor: "contact_name", label: "Contact name", canEdit: true },
  { accessor: "phone", label: "Phone number", canEdit: true },
] satisfies Column[];

const getNextStage = (currentStage: string) => {
  switch (currentStage) {
    case "lead":
      return "opportunity";
    case "opportunity":
      return "customer";
    default:
      break;
  }
};

export default airplane.view(
  {
    slug: "demo_customer_relationship",
    name: "Customer relationship management dashboard",
  },
  CustomerRelationshipDashboard
);


customer_relationship_management_dashboard

INSERT INTO
  customers (
    "customer_id",
    "company_name",
    "contact_name",
    "contact_title",
    "country",
    "phone",
    "address",
    "opportunity_stage"
  )
VALUES
  (
    :customer_id,
    :company_name,
    :contact_name,
    :contact_title,
    :country,
    :phone,
    :address,
    :opportunity_stage
  );



# Full reference: https://docs.airplane.dev/tasks/task-definition

slug: demo_create_lead
name: Create a new lead
parameters:
  - slug: customer_id
    name: Customer ID
    type: shorttext
    required: true
  - slug: company_name
    name: Company name
    type: shorttext
    required: true
  - slug: contact_name
    name: Contact name
    type: shorttext
    required: true
  - slug: contact_title
    name: Contact title
    type: shorttext
    required: true
  - slug: country
    name: Country
    type: shorttext
    required: true
  - slug: phone
    name: Phone
    type: shorttext
    required: true
  - slug: address
    name: Address
    type: longtext
    required: true
sql:
  # Replace with your own DB name
  resource: "[Demo DB]"
  entrypoint: create_lead.sql
  queryArgs:
    customer_id: "{{params.customer_id}}"
    company_name: "{{params.company_name}}"
    contact_name: "{{params.contact_name}}"
    contact_title: "{{params.contact_title}}"
    country: "{{params.country}}"
    phone: "{{params.phone}}"
    address: "{{params.address}}"
    opportunity_stage: "lead"


INSERT INTO
  touch_points ("customer_id", "touch_point_type", "created_at")
VALUES
  (
    :customer_id,
    :touch_point_type,
    :created_at
  ) RETURNING customer_id,
  touch_point_type,
  created_at;

# Full reference: https://docs.airplane.dev/tasks/task-definition

slug: demo_create_touch_point_for_customer
name: Create touch point for customer
description: Create and attach touch point for a customer
parameters:
  - slug: customer_id
    name: Customer ID
    type: shorttext
    required: true
  - slug: touch_point_type
    name: Touch point type
    type: shorttext
    required: true
  - slug: created_at
    name: Created at
    type: datetime
    required: true
sql:
  # Replace with your own DB name
  resource: "[Demo DB]"
  entrypoint: create_touch_point_for_customer.sql
  queryArgs:
    customer_id: "{{params.customer_id}}"
    touch_point_type: "{{params.touch_point_type}}"
    created_at: "{{params.created_at}}"



DELETE FROM
  touch_points
WHERE
  customer_id = :customer_id;

DELETE FROM
  order_details
WHERE
  order_id IN(
    SELECT
      order_id
    FROM
      orders
    WHERE
      customer_id = :customer_id
  );

DELETE FROM
  orders
WHERE
  customer_id = :customer_id;

DELETE FROM
  customers
WHERE
  customer_id = :customer_id;



# Full reference: https://docs.airplane.dev/tasks/task-definition

slug: demo_delete_customer
name: Delete customer
parameters:
  - slug: customer_id
    name: Customer ID
    type: shorttext
    required: true

sql:
  # Replace with your own DB name
  resource: "[Demo DB]"
  entrypoint: delete_customer.sql
  queryArgs:
    customer_id: "{{params.customer_id}}"


DELETE FROM
  touch_points
WHERE
  customer_id = :customer_id
  AND touch_point_type = :touch_point_type;

  

# Full reference: https://docs.airplane.dev/tasks/task-definition

slug: demo_delete_touch_point_for_customer
name: Delete touch point for customer
parameters:
  - slug: customer_id
    name: Customer ID
    type: shorttext
    required: true
  - slug: touch_point_type
    name: Touch point
    type: shorttext
    required: true

sql:
  # Replace with your own DB name
  resource: "[Demo DB]"
  entrypoint: delete_touch_point_for_customer.sql
  queryArgs:
    customer_id: "{{params.customer_id}}"
    touch_point_type: "{{params.touch_point_type}}"


UPDATE
  customers
SET
  phone = :phone,
  contact_name = :contact_name,
  country = :country,
  contact_title = :contact_title
WHERE
  customer_id = :customer_id;
  

# Full reference: https://docs.airplane.dev/tasks/task-definition

slug: demo_edit_customer
name: Edit customer
parameters:
  - slug: customer_id
    name: Customer ID
    type: shorttext
    required: true
  - slug: phone
    name: Phone number
    type: shorttext
    required: false
  - slug: contact_name
    name: Contact name
    type: shorttext
    required: false
  - slug: contact_title
    name: Contact title
    type: shorttext
    required: false
  - slug: country
    name: Country
    type: shorttext
    required: false
sql:
  # Replace with your own DB name
  resource: "[Demo DB]"
  entrypoint: edit_customer.sql
  queryArgs:
    customer_id: "{{params.customer_id}}"
    phone: "{{params.phone}}"
    contact_name: "{{params.contact_name}}"
    country: "{{params.country}}"
    contact_title: "{{params.contact_title}}"


SELECT
  customer_id,
  touch_point_type,
  created_at
FROM
  touch_points
WHERE
  customer_id = :customer_id
ORDER BY
  customer_id;


# Full reference: https://docs.airplane.dev/tasks/task-definition

slug: demo_list_customer_touch_points
name: Customers touch points
description: "List all customer touch points"
parameters:
  - slug: customer_id
    name: Customer ID
    type: shorttext
    required: true
sql:
  # Replace with your own DB name
  resource: "[Demo DB]"
  entrypoint: list_customer_touch_points.sql
  queryArgs:
    customer_id: "{{params.customer_id}}"



SELECT
  cus.customer_id,
  cus.company_name,
  cus.contact_name,
  cus.contact_title,
  cus.country,
  cus.phone,
  cus.fax,
  cus.address,
  cus.city,
  cus.opportunity_stage,
  cus.postal_code,
  (
    SELECT
      Count(*)
    FROM
      touch_points
    WHERE
      customer_id = cus.customer_id
  ) as touch_points
FROM
  customers as cus
WHERE
  opportunity_stage = :opportunity_stage
ORDER BY
  customer_id;

# Full reference: https://docs.airplane.dev/tasks/task-definition

slug: demo_list_customers_by_stage
name: List customers
description: "List all customers at a stage"
parameters:
  - slug: opportunity_stage
    name: Opportunity stage
    type: shorttext
    required: false
sql:
  # Replace with your own DB name
  resource: "[Demo DB]"
  entrypoint: list_customers_by_stage.sql
  queryArgs:
    opportunity_stage: "{{params.opportunity_stage}}"



UPDATE
  customers
SET
  opportunity_stage = :opportunity_stage
WHERE
  customer_id = :customer_id;

# Full reference: https://docs.airplane.dev/tasks/task-definition

slug: demo_update_customer_stage
name: Update customer opportunity stage
parameters:
  - slug: customer_id
    name: Customer ID
    type: shorttext
    required: true
  - slug: opportunity_stage
    name: Opportunity name
    type: shorttext
    required: false
sql:
  # Replace with your own DB name
  resource: "[Demo DB]"
  entrypoint: update_customer_stage.sql
  queryArgs:
    customer_id: "{{params.customer_id}}"
    opportunity_stage: "{{params.opportunity_stage}}"


View

Task

Pairs well with

Customer relationship management

View

Task

Pairs well with