Medical_ERP/models/medicine_template.go

package models

import (
	db "Medical_ERP/common/initialize"
	model2 "Medical_ERP/common/model"
	"github.com/beego/beego/v2/core/logs"
	"github.com/go-sql-driver/mysql"
	"strconv"
)

const (
	FieldProductID           = "product_id"           // 品种ID
	FieldEnterpriseID        = "enterprise_id"        // 生产企业ID
	FieldSpecID              = "spec_id"              // 规格ID
	FieldUnitID              = "unit_id"              // 单位id
	FieldDosageFormID        = "dosage_form_id"       // 剂型id
	FieldBatchNumber         = "batch_number"         // 批号
	FieldExpiryDate          = "expiry_date"          // 失效日期
	FieldProducedDate        = "produced_date"        // 生产日期
	FieldApprovalNumber      = "approval_number"      // 批准文号
	FieldQualificationNumber = "qualification_number" // 批签发合格编号

	FieldProductName    = "product_name"
	FieldEnterpriseName = "enterprise_name"
	FieldSpecName       = "spec_name"
	FieldUnitName       = "unit_name"
	FieldDosageFormName = "dosage_form_name"
)

func GetMedicineInfoTableName(deptId int) string {
	return "medicine_info_" + strconv.Itoa(deptId)
}

type MedicineTemplate struct {
	model2.Model
	Type      int    `json:"type" gorm:"size:11;"`        // 数据类型
	Name      string `json:"name" gorm:"size:32;"`        // 标签名称
	FieldName string `json:"field_name" gorm:"size:128;"` // 英语名称
	Text      string `json:"text" gorm:"size:256;"`       // 描述
	Sort      int    `json:"sort" gorm:"size:11;"`        // 排序
	Width     int    `json:"width" gorm:"size:11;"`       // 宽度
	Show      int    `json:"show" gorm:"size:11;"`        // 1-显示 2-隐藏
	State     int    `json:"state" gorm:"size:11;"`       // 1-系统初始化 2-用户添加
	model2.ControlBy
	model2.ModelTime
}

func (e *MedicineTemplate) TableName() string {
	return "medicine_template"
}

func GetInitMedicineTemplateInfo(deptId, CreateBy int) []MedicineTemplate {
	return []MedicineTemplate{
		{Type: 1, Name: "品名", FieldName: FieldProductID, Show: 1, State: 1, Sort: -9, ControlBy: model2.ControlBy{CreateBy: CreateBy, DeptId: deptId}},
		{Type: 2, Name: "生产企业", FieldName: FieldEnterpriseID, Show: 1, State: 1, Sort: -8, ControlBy: model2.ControlBy{CreateBy: CreateBy, DeptId: deptId}},
		{Type: 3, Name: "规格", FieldName: FieldSpecID, Show: 1, State: 1, Sort: -7, ControlBy: model2.ControlBy{CreateBy: CreateBy, DeptId: deptId}},
		{Type: 6, Name: "批号", FieldName: FieldBatchNumber, Show: 1, State: 1, Sort: -6, ControlBy: model2.ControlBy{CreateBy: CreateBy, DeptId: deptId}},
		{Type: 9, Name: "失效日期", FieldName: FieldExpiryDate, Show: 1, State: 1, Sort: -5, ControlBy: model2.ControlBy{CreateBy: CreateBy, DeptId: deptId}},
		{Type: 9, Name: "生产日期", FieldName: FieldProducedDate, Show: 1, State: 1, Sort: -5, ControlBy: model2.ControlBy{CreateBy: CreateBy, DeptId: deptId}},
		{Type: 6, Name: "批准文号", FieldName: FieldApprovalNumber, Show: 1, State: 1, Sort: -4, ControlBy: model2.ControlBy{CreateBy: CreateBy, DeptId: deptId}},
		{Type: 5, Name: "剂型", FieldName: FieldDosageFormID, Show: 1, State: 1, Sort: -3, ControlBy: model2.ControlBy{CreateBy: CreateBy, DeptId: deptId}},
		{Type: 4, Name: "单位", FieldName: FieldUnitID, Show: 1, State: 1, Sort: -2, ControlBy: model2.ControlBy{CreateBy: CreateBy, DeptId: deptId}},
		{Type: 6, Name: "批签发编号", FieldName: FieldQualificationNumber, Show: 1, State: 1, Sort: -1, ControlBy: model2.ControlBy{CreateBy: CreateBy, DeptId: deptId}},
	}
}

func GetInitMedicineTemplateSql(deptId int) string {
	sqlStmt := `
    CREATE TABLE IF NOT EXISTS ` + GetMedicineInfoTableName(deptId) + ` (
        id int primary key auto_increment comment '自增ID',
        product_id INT comment '品名ID',
        enterprise_id INT comment '生产企业ID',
        spec_id INT comment '规格ID',
		unit_id INT comment '单位ID',
		dosage_form_id INT comment '剂型ID',
        batch_number VARCHAR(256) comment '批号',
        expiry_date DATE comment '失效日期',
        produced_date DATE comment '生产日期',
        approval_number VARCHAR(256) comment '批准文号',
        qualification_number VARCHAR(256) comment '批签发编号',
        unit_price DECIMAL(10,2) comment '单价',
        qrcode VARCHAR(256) comment '追溯码'
    );`
	return sqlStmt
}

//purchase_unit_price DECIMAL(10,2) comment '购进单价',
//sales_unit_price DECIMAL(10,2) comment '销售单价'

// 获取插入数据sql
func GetInsertMedicineTemplateSql(deptId, typeId int, columnName string) string {
	var typeStr string
	switch typeId {
	case 1, 2, 3, 4, 5:
		typeStr = " INT"
	case 6:
		typeStr = " VARCHAR(256)"
	case 7:
		typeStr = " INT"
	case 8:
		typeStr = " DECIMAL(10,2)"
	case 9:
		typeStr = " DATE"
	}
	sqlStmt := "ALTER TABLE " + GetMedicineInfoTableName(deptId) + " ADD `" + columnName + "`" + typeStr
	logs.Info("InsertMedicineTemplateSql", sqlStmt)
	return sqlStmt
}

// 获取更新列名sql
func GetUpdateMedicineTemplateSql(deptId, typeId int, oldColumnName, newColumnName string) string {
	var typeStr string
	switch typeId {
	case 1, 2, 3, 4, 5:
		typeStr = " INT"
	case 6:
		typeStr = " VARCHAR(256)"
	case 7:
		typeStr = " INT"
	case 8:
		typeStr = " DECIMAL(10,4)"
	case 9:
		typeStr = " DATETIME"
	}
	sqlStmt := "ALTER TABLE " + GetMedicineInfoTableName(deptId) + " CHANGE `" + oldColumnName + "` `" + newColumnName + "`" + typeStr
	logs.Info("UpdateMedicineTemplateSql", sqlStmt)
	return sqlStmt
}

func GetDeleteMedicineTemplateSql(deptId int, columnName string) string {
	sqlStmt := "ALTER TABLE " + GetMedicineInfoTableName(deptId) + " DROP `" + columnName + "`"
	logs.Info("DeleteMedicineTemplateSql", sqlStmt)
	return sqlStmt
}

//	type	说明			英文名称
//	1		产品名称		ProductID
//	2		生产企业		EnterpriseID
//	3		规格			SpecID
//	4		剂型			DosageFormID
//	5		单位			UnitID
//	6		文本
//	7		数值（整数）
//	8		数值（小数）
//	9		日期

// 批量修改药品信息表结构
func BatchAlterMedicineTemplateTable() {
	// 删除表内字段
	var tableName []string
	err := db.DB.Raw("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'medicine_info_%';\n").
		Scan(&tableName).Error
	if err != nil {
		logs.Error("db error: %s", err)
	}
	tx := db.DB.Begin()

	for _, v := range tableName {
		sql := "ALTER TABLE " + v + " ADD COLUMN qrcode VARCHAR(256)"
		err = tx.Exec(sql).Error
		if err != nil {
			if err.(*mysql.MySQLError).Message == "Duplicate column name 'qrcode'" {
				continue
			}
			tx.Rollback()
			logs.Error("修改表结构失败: %s", err.Error())
			break
		}
	}

	tx.Commit()

}