old-svijetlastrana/data/datamysql/organization.go

package datamysql

import (
	"database/sql"
	"fmt"

	"bitbucket.org/nemt/nemt-portal-api/domain/entity"
	"bitbucket.org/nemt/nemt-portal-api/infra/errors"
	uuid "github.com/satori/go.uuid"
)

// rideRepo maps methods to database
type organizationRepo struct {
	conn executor
}

func newOrganizationRepo(conn executor) *organizationRepo {
	return &organizationRepo{
		conn: conn,
	}
}

func (c *organizationRepo) getProfileQuery(user entity.User) (query string, where string, err error) {
	if len(user.Profiles) > 0 {
		for _, p := range user.Profiles {
			switch p.Key {
			case "AD", "BCBSIAD", "BDCAD", "PLANAD":
				switch p.Organization.Type.Key {
				case "techsupport", "bcbsi", "bcbsa":
					return
				case "plan":
					query = ` INNER JOIN       tab_provider                            e
                                ON                  e.provider_id                   =       a.organization_reference_id
                               AND                  b.organization_type_key         =       'plan'
                         LEFT JOIN       tab_organization                        f
                                ON                  f.organization_id               =       a.organization_parent_id `
					where = fmt.Sprintf(`	AND (a.organization_uuid = '%s' OR f.organization_uuid = '%s') `, p.Organization.UUID, p.Organization.UUID)
				case "provider":
					query = ` INNER JOIN       tab_provider                            e
                                ON                  e.provider_id                   =       a.organization_reference_id
                               AND                  b.organization_type_key         =       'provider'
                         LEFT JOIN       tab_organization                        f
                                ON                  f.organization_id               =       a.organization_parent_id `
					where = fmt.Sprintf(`	AND (a.organization_uuid = '%s' OR f.organization_uuid = '%s') `, p.Organization.UUID, p.Organization.UUID)
					return
				}
			case "SP", "SPT":
				switch p.Organization.Type.Key {
				case "techsupport", "bcbsi", "bcbsa":
					return
				case "plan":
					query = ` INNER JOIN       tab_provider                            e
                                ON                  e.provider_id                   =       a.organization_reference_id
                               AND                  b.organization_type_key         =       'plan'
                         LEFT JOIN       tab_organization                        f
                                ON                  f.organization_id               =       a.organization_parent_id `
					where = fmt.Sprintf(`	AND (a.organization_uuid = '%s' OR f.organization_uuid = '%s') `, p.Organization.UUID, p.Organization.UUID)
				case "provider":
					query = ` INNER JOIN       tab_provider                            e
                                ON                  e.provider_id                   =       a.organization_reference_id
                               AND                  b.organization_type_key         =       'provider'
                         LEFT JOIN       tab_organization                        f
                                ON                  f.organization_id               =       a.organization_parent_id `
					where = fmt.Sprintf(`	AND (a.organization_uuid = '%s' OR f.organization_uuid = '%s') `, p.Organization.UUID, p.Organization.UUID)
					return
				}
			}
		}

		if query == "" && where == "" {
			return "", "", fmt.Errorf("Invalid Query")
		} else {
			return
		}
	} else {
		return "", "", fmt.Errorf("User has no profile to search")
	}
}

func (c *organizationRepo) getQuery() string {
	const (
		query = `SELECT 
										a.organization_id,
										a.organization_uuid,
										b.organization_type_id,
										b.organization_type,
										b.organization_type_key,
										a.organization_name,
										a.organization_description,
										a.organization_reference_id,
										a.organization_parent_id,
										(a.main_organization = b'1') main_organization,
										a.create_at,
										a.update_at,
										(a.active = b'1') active,
										(a.suspended = b'1') suspended,
										(a.blocked = b'1') blocked,
										c.user_id created_user_id,
										c.user_uuid created_user_uuid,
										c.name created_user_name,
										d.user_id updated_user_id,
										d.user_uuid updated_user_uuid,
										d.name updated_user_name
							FROM
														tab_organization        a
						INNER JOIN     tab_organization_type   b
								ON          a.organization_type_id  =   b.organization_type_id 
						INNER JOIN		 tab_user								 c
								ON					c.user_id 						=		a.created_user
						INNER JOIN		 tab_user								 d
								ON					d.user_id 						=		a.updated_user `
	)

	return query
}
func (c *organizationRepo) getAddressQuery() string {
	const query = `SELECT 
														a.organization_address_id, 
														a.organization_address_uuid, 
														a.internal_id,
														a.name,
														a.address,
														a.desc,
														a.lat,
														a.long,
														a.created,
														a.created_user_id,
														c.user_uuid created_user_uuid,
														c.name created_user_name,
														a.updated,
														a.updated_user_id,
														d.user_uuid updated_user_uuid,
														d.name updated_user_name,
														(a.active = b'1') active
									FROM      tab_organization_address         a 
						INNER JOIN      tab_organization    b
										ON      b.organization_id = a.organization_id
						INNER JOIN      tab_user            c
										ON      c.user_id         = a.created_user_id
						INNER JOIN      tab_user            d
										ON      d.user_id         = a.updated_user_id `

	return query
}

func (c *organizationRepo) getContactQuery() string {
	const (
		query = `SELECT 
														a.organization_contact_id, 
														a.organization_contact_uuid, 
														b.contact_type_id, 
														b.key               contact_type_key, 
														b.name              contact_type_name,
														a.contact,
														a.contact_name,
														a.contact_desc,
														a.created,
														a.created_user_id,
														d.user_uuid created_user_uuid,
														d.name created_user_name,
														a.updated,
														a.updated_user_id,
														e.user_uuid updated_user_uuid,
														e.name updated_user_name,
														(a.active = b'1') active
									FROM      tab_organization_contact         a 
						INNER JOIN      tab_contact_type    b 
										ON      a.contact_type_id = b.contact_type_id 
						INNER JOIN      tab_organization    c
										ON      c.organization_id = a.organization_id
						INNER JOIN      tab_user            d
										ON      d.user_id         = a.created_user_id
						INNER JOIN      tab_user            e
										ON      e.user_id         = a.updated_user_id `
	)

	return query
}

func (c *organizationRepo) getTypeQuery() string {
	const (
		query = `SELECT
										organization_type_id,
										organization_type,
										organization_type_key,
										description,
										create_at,
										update_at
							FROM
										tab_organization_type `
	)

	return query
}

// parseSet parses a result set result to an entity array
func (c *organizationRepo) parseSet(rows *sql.Rows, err error) ([]entity.Organization, error) {
	if err != nil {
		return nil, errors.Wrap(err)
	}
	result := make([]entity.Organization, 0)
	for rows.Next() {
		entity, err := c.parseEntity(rows)
		if err != nil {
			return nil, errors.Wrap(err)
		}
		result = append(result, entity)
	}
	return result, nil
}

// parseEntity parses a result to an entity
func (c *organizationRepo) parseEntity(row scanner) (retVal entity.Organization, err error) {
	err = row.Scan(
		&retVal.ID, &retVal.UUID, &retVal.Type.ID, &retVal.Type.Name, &retVal.Type.Key, &retVal.Name, &retVal.Description, &retVal.ReferenceID, &retVal.ParentID, &retVal.Main, &retVal.Created, &retVal.Updated, &retVal.Active, &retVal.Suspended, &retVal.Blocked, &retVal.Author.ID, &retVal.Author.UUID, &retVal.Author.Name, &retVal.LastEditor.ID, &retVal.LastEditor.UUID, &retVal.LastEditor.Name)

	return retVal, errors.Wrap(err)
}

// parseSet parses a result set result to an entity array
func (c *organizationRepo) parseContactSet(rows *sql.Rows, err error) ([]entity.OrganizationContact, error) {
	if err != nil {
		return nil, errors.Wrap(err)
	}
	result := make([]entity.OrganizationContact, 0)
	for rows.Next() {
		entity, err := c.parseContactEntity(rows)
		if err != nil {
			return nil, errors.Wrap(err)
		}
		result = append(result, entity)
	}
	return result, nil
}

// parseEntity parses a result to an entity
func (c *organizationRepo) parseContactEntity(row scanner) (retVal entity.OrganizationContact, err error) {
	err = row.Scan(
		&retVal.ID, &retVal.UUID, &retVal.Type.ID, &retVal.Type.Key, &retVal.Type.Value, &retVal.Contact, &retVal.Name, &retVal.Description, &retVal.Created, &retVal.CreatedUser.ID, &retVal.CreatedUser.UUID, &retVal.CreatedUser.Name, &retVal.Updated, &retVal.UpdatedUser.ID, &retVal.UpdatedUser.UUID, &retVal.UpdatedUser.Name, &retVal.Active)

	return retVal, errors.Wrap(err)
}

// parseSet parses a result set result to an entity array
func (c *organizationRepo) parseAddressSet(rows *sql.Rows, err error) ([]entity.OrganizationAddress, error) {
	if err != nil {
		return nil, errors.Wrap(err)
	}
	result := make([]entity.OrganizationAddress, 0)
	for rows.Next() {
		entity, err := c.parseAddressEntity(rows)
		if err != nil {
			return nil, errors.Wrap(err)
		}
		result = append(result, entity)
	}
	return result, nil
}

// parseEntity parses a result to an entity
func (c *organizationRepo) parseAddressEntity(row scanner) (retVal entity.OrganizationAddress, err error) {
	err = row.Scan(
		&retVal.ID, &retVal.UUID, &retVal.InternalID, &retVal.Name, &retVal.Address, &retVal.Description, &retVal.Latitude, &retVal.Longitude, &retVal.Created, &retVal.CreatedUser.ID, &retVal.CreatedUser.UUID, &retVal.CreatedUser.Name, &retVal.Updated, &retVal.UpdatedUser.ID, &retVal.UpdatedUser.UUID, &retVal.UpdatedUser.Name, &retVal.Active)

	return retVal, errors.Wrap(err)
}

// parseSet parses a result set result to an entity array
func (c *organizationRepo) parseTypeSet(rows *sql.Rows, err error) ([]entity.OrganizationType, error) {
	if err != nil {
		return nil, errors.Wrap(err)
	}
	result := make([]entity.OrganizationType, 0)
	for rows.Next() {
		entity, err := c.parseTypeEntity(rows)
		if err != nil {
			return nil, errors.Wrap(err)
		}
		result = append(result, entity)
	}
	return result, nil
}

// parseEntity parses a result to an entity
func (c *organizationRepo) parseTypeEntity(row scanner) (retVal entity.OrganizationType, err error) {
	err = row.Scan(
		&retVal.ID, &retVal.Name, &retVal.Key, &retVal.Description, &retVal.Created, &retVal.Updated)

	return retVal, errors.Wrap(err)
}

func (c *organizationRepo) GetAllTypes() ([]entity.OrganizationType, error) {
	return c.parseTypeSet(c.conn.Query(c.getTypeQuery()))
}

func (c *organizationRepo) GetTypeByKey(key string) (entity.OrganizationType, error) {
	return c.parseTypeEntity(c.conn.QueryRow(c.getTypeQuery()+" WHERE b.organization_type_key=?", key))
}

func (c *organizationRepo) GetByType(organizationTypeKey string, user entity.User) ([]entity.Organization, error) {
	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return nil, err
	}

	if organizationTypeKey == "" {
		return c.parseSet(c.conn.Query(c.getQuery() + query + " WHERE 1 = 1 " + where))
	} else {
		return c.parseSet(c.conn.Query(c.getQuery()+query+" WHERE b.organization_type_key = ? "+where, organizationTypeKey))
	}
}

func (c *organizationRepo) GetByName(name string, searchType string, user entity.User) ([]entity.Organization, error) {
	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return nil, err
	}

	finalQuery := c.getQuery() + query + " WHERE a.organization_name LIKE ? "
	switch searchType {
	case "parent":
		finalQuery += " AND a.organization_parent_id > 0 "
	case "child":
		finalQuery += " AND a.organization_parent_id = 0 "
	}

	finalQuery += where
	name = "%" + name + "%"

	return c.parseSet(c.conn.Query(finalQuery, name))
}

func (c *organizationRepo) GetByUUID(organizationUUID string, user entity.User) (entity.Organization, error) {
	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return entity.Organization{}, err
	}

	query = c.getQuery() + query + " WHERE a.organization_uuid = ? " + where

	return c.parseEntity(c.conn.QueryRow(query, organizationUUID))
}

func (c *organizationRepo) GetByID(organizationID int64, user entity.User) (entity.Organization, error) {
	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return entity.Organization{}, err
	}

	query = c.getQuery() + query + " WHERE a.organization_id = ? " + where

	return c.parseEntity(c.conn.QueryRow(query, organizationID))
}

func (c *organizationRepo) GetChildsByID(organizationID int64, user entity.User) ([]entity.Organization, error) {
	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return nil, err
	}

	query = c.getQuery() + query + " WHERE a.organization_parent_id = ? " + where

	return c.parseSet(c.conn.Query(query, organizationID))
}

func (c *organizationRepo) GetContactsByOrganizationUUID(organizationUUID string) ([]entity.OrganizationContact, error) {
	return c.parseContactSet(c.conn.Query(c.getContactQuery()+" WHERE a.active = 1 AND c.organization_uuid = ? ", organizationUUID))
}

func (c *organizationRepo) GetContactsByOrganizationID(organizationID int64) ([]entity.OrganizationContact, error) {
	return c.parseContactSet(c.conn.Query(c.getContactQuery()+" WHERE a.active = 1 AND c.organization_id = ? ", organizationID))
}

func (c *organizationRepo) GetContactsByUUID(contactUUID string) (entity.OrganizationContact, error) {
	return c.parseContactEntity(c.conn.QueryRow(c.getContactQuery()+" WHERE a.organization_contact_uuid = ? ", contactUUID))
}

func (c *organizationRepo) GetAddressByOrganizationUUID(organizationUUID string) ([]entity.OrganizationAddress, error) {
	return c.parseAddressSet(c.conn.Query(c.getAddressQuery()+" WHERE a.active = 1 AND b.organization_uuid = ? ", organizationUUID))
}

func (c *organizationRepo) GetAddressByOrganizationID(organizationID int64) ([]entity.OrganizationAddress, error) {
	return c.parseAddressSet(c.conn.Query(c.getAddressQuery()+" WHERE a.active = 1 AND b.organization_id = ? ", organizationID))
}

func (c *organizationRepo) GetAddressByUUID(contactUUID string) (entity.OrganizationAddress, error) {
	return c.parseAddressEntity(c.conn.QueryRow(c.getAddressQuery()+" WHERE a.organization_address_uuid = ? ", contactUUID))
}

func (c *organizationRepo) SetParentOrganization(organizationID int64, parentOrganizationID int64) error {
	const (
		query = `UPDATE tab_organization SET organization_parent_id = ? WHERE organization_id = ?;`
	)

	_, err := c.conn.Exec(query, parentOrganizationID, organizationID)
	if err != nil {
		return err
	} else {
		return nil
	}
}

func (c *organizationRepo) InactivateOrganizationAddress(address entity.OrganizationAddress, user entity.User) error {
	const (
		query = "UPDATE tab_organization_address SET active = 0, updated = CURRENT_TIMESTAMP, updated_user_id = ? WHERE organization_id = ? and organization_address_uuid = ?"
	)

	if address.Organization == nil {
		return errors.NewNotFoundError()
	}

	organization, err := c.GetByUUID(address.Organization.UUID, user)
	if err != nil {
		return err
	}

	_, err = c.conn.Exec(query, address.UpdatedUser.ID, organization.ID, address.UUID)
	if err != nil {
		return err
	} else {
		return nil
	}
}

func (c *organizationRepo) SetOrganizationAddress(address entity.OrganizationAddress, user entity.User) (entity.OrganizationAddress, error) {
	const (
		query = "INSERT INTO tab_organization_address(organization_address_uuid, organization_id, internal_id, `name`, address, `desc`, lat, `long`, created_user_id, updated_user_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
	)

	if address.Organization == nil {
		return entity.OrganizationAddress{}, errors.NewNotFoundError()
	}

	organization, err := c.GetByUUID(address.Organization.UUID, user)
	if err != nil {
		return entity.OrganizationAddress{}, err
	}

	UUID, _ := uuid.NewV4()
	result, err := c.conn.Exec(query, UUID.String(), organization.ID, address.InternalID, address.Name, address.Address, address.Description, address.Latitude, address.Longitude, address.CreatedUser.ID, address.UpdatedUser.ID)
	if err != nil {
		return entity.OrganizationAddress{}, err
	}

	address.ID, err = result.LastInsertId()
	if err != nil {
		return address, err
	}

	return address, nil
}

func (c *organizationRepo) InactivateOrganizationContact(contact entity.OrganizationContact, user entity.User) error {
	const (
		query = "UPDATE tab_organization_contact SET active = 0, updated = CURRENT_TIMESTAMP, updated_user_id = ? WHERE organization_id = ? and organization_contact_uuid = ?"
	)

	if contact.Organization == nil {
		return errors.NewNotFoundError()
	}

	organization, err := c.GetByUUID(contact.Organization.UUID, user)
	if err != nil {
		return err
	}

	_, err = c.conn.Exec(query, contact.UpdatedUser.ID, organization.ID, contact.UUID)
	if err != nil {
		return err
	} else {
		return nil
	}
}

func (c *organizationRepo) SetOrganizationContact(contact entity.OrganizationContact, user entity.User) (entity.OrganizationContact, error) {
	const (
		selectQuery = "SELECT a.contact_type_id, a.name, a.key FROM tab_contact_type a WHERE a.key = ?"
		query       = "INSERT INTO tab_organization_contact(organization_contact_uuid, organization_id, contact_type_id, contact, contact_name, contact_desc, created_user_id, updated_user_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?);"
	)

	if contact.Organization == nil {
		return entity.OrganizationContact{}, errors.NewNotFoundError()
	}

	organization, err := c.GetByUUID(contact.Organization.UUID, user)
	if err != nil {
		return entity.OrganizationContact{}, err
	}

	row := c.conn.QueryRow(selectQuery, contact.Type.Key)
	err = row.Scan(&contact.Type.ID, &contact.Type.Value, &contact.Type.Key)
	if err != nil {
		return entity.OrganizationContact{}, err
	}

	UUID, _ := uuid.NewV4()
	result, err := c.conn.Exec(query, UUID.String(), organization.ID, contact.Type.ID, contact.Contact, contact.Name, contact.Description, contact.CreatedUser.ID, contact.UpdatedUser.ID)
	if err != nil {
		return entity.OrganizationContact{}, err
	}

	contact.ID, err = result.LastInsertId()
	if err != nil {
		return contact, err
	}

	return contact, nil
}

func (c *organizationRepo) AddOrganization(organization entity.Organization, user entity.User) (entity.Organization, error) {
	const (
		queryOrgType   = "SELECT a.organization_type_id FROM tab_organization_type a WHERE a.organization_type_key = ?;"
		queryParentOrg = "SELECT a.organization_id FROM tab_organization a WHERE a.organization_uuid = ?;"
		query          = "INSERT INTO tab_organization(organization_uuid, organization_type_id, organization_name, organization_description, organization_reference_id, organization_parent_id, main_organization, created_user, updated_user) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);"
	)

	row := c.conn.QueryRow(queryOrgType, organization.Type.Key)
	err := row.Scan(&organization.Type.ID)
	if err != nil {
		return entity.Organization{}, err
	}

	if organization.Parent != nil && organization.Parent.UUID != "" {
		row := c.conn.QueryRow(queryParentOrg, organization.Parent.UUID)
		err := row.Scan(&organization.Parent.ID)
		if err != nil {
			return entity.Organization{}, err
		}
		organization.ParentID = organization.Parent.ID
	}

	UUID, _ := uuid.NewV4()
	organization.UUID = UUID.String()
	result, err := c.conn.Exec(query, organization.UUID, organization.Type.ID, organization.Name, organization.Description, organization.ReferenceID, organization.ParentID, false, organization.Author.ID, organization.LastEditor.ID)
	if err != nil {
		return entity.Organization{}, err
	}

	ID, err := result.LastInsertId()
	if err != nil {
		return entity.Organization{}, err
	}

	organization.ID = ID
	if len(organization.Addresses) > 0 {
		for i, a := range organization.Addresses {
			a.Organization = &organization
			address, err := c.SetOrganizationAddress(a, user)
			if err != nil {
				fmt.Println("Error to save addresses")
				return entity.Organization{}, err
			}
			organization.Addresses[i] = address
		}
	}

	if len(organization.Contacts) > 0 {
		for i, ct := range organization.Contacts {
			ct.Organization = &organization
			contact, err := c.SetOrganizationContact(ct, user)
			if err != nil {
				fmt.Println("Error to save contacts")
				return entity.Organization{}, err
			}
			organization.Contacts[i] = contact
		}
	}

	return organization, nil
}