package bigquery import ( "errors" "fmt" "log" "reflect" "strconv" "time" googlebq "cloud.google.com/go/bigquery" "cloud.google.com/go/civil" "github.com/pingcap/crm-bot/model" ) type IModel interface { TableName() string PrimaryId() string } type CRMAccount struct { CustomerId string Name string Alias string Level string Industry string Type string Region string CreateTime googlebq.NullDateTime UpdateTime googlebq.NullDateTime AccountID string Segmentation string TenantCreatedTime googlebq.NullDateTime TenantStatus string TiDBCloudPortalID string FY22IB bool CloudProductionCustomer string InternalUser bool } func (acc CRMAccount) TableName() string { return "crm_account" } func (acc CRMAccount) PrimaryId() string { return "CustomerId" } type PingcapSalesforceOpportunityFieldHistory struct { Id string `gorm:"primaryKey" json:Id"` NewValue googlebq.NullString `json:"NewValue" gorm:"type:varchar(255)"` OldValue googlebq.NullString `json:"OldValue" gorm:"type:varchar(255)"` CreatedDate googlebq.NullDateTime `gorm:"type:datetime" json:"CreatedDate"` OpportunityId string `json:"OpportunityId"` CreatedById string `json:"CreatedById"` IsDeleted bool `json:"IsDeleted"` Field string `json:"Field"` } func (pofh PingcapSalesforceOpportunityFieldHistory) TableName() string { return "salesforce_oppo_field_history" } func (pofh PingcapSalesforceOpportunityFieldHistory) PrimaryId() string { return "Id" } type PingcapSalesforceLead struct { Id string `gorm:"primaryKey;column:Id"` Country string `gorm:"type:varchar(255);column:Country"` CreatedBy string `gorm:"type:varchar(255);column:CreatedBy"` InternalUser bool `gorm:"type:varchar(255);column:InternalUser"` LastModifiedBy string `gorm:"type:varchar(255);column:LastModifiedBy"` LastTransferDate googlebq.NullDate `gorm:"type:date;column:LastTransferDate"` LeadCurrency string `gorm:"type:varchar(255);column:LeadCurrency"` LeadLifecycleStage string `gorm:"type:varchar(255);column:LeadLifecycleStage"` LeadOwner string `gorm:"type:varchar(255);column:LeadOwner"` LeadSource string `gorm:"type:varchar(255);column:LeadSource"` LeadStatus string `gorm:"type:varchar(255);column:LeadStatus"` Region string `gorm:"type:varchar(255);column:Region"` RegistrationStatus string `gorm:"type:varchar(255);column:RegistrationStatus"` Segmentation string `gorm:"type:varchar(255);column:Segmentation"` TenantStatus string `gorm:"type:varchar(255);column:TenantStatus"` TimeZone string `gorm:"type:varchar(255);column:TimeZone"` TiDBCloudPortalID string `gorm:"type:varchar(255);column:TiDBCloudPortalID"` TenantCreatedTime googlebq.NullDateTime `gorm:"type:date;column:TenantCreatedTime"` CreatedDate googlebq.NullDate `gorm:"type:date;column:CreatedDate"` PersonLifecycleStage string `gorm:"type:varchar(255);column:PersonLifecycleStage"` PersonStatus string `gorm:"type:varchar(255);column:PersonStatus"` SuspectStageDate googlebq.NullDate `gorm:"type:date;column:SuspectStageDate"` LeadStageDate googlebq.NullDate `gorm:"type:date;column:LeadStageDate"` MqlStageDate googlebq.NullDate `gorm:"type:date;column:MQLStageDate"` SalStageDate googlebq.NullDate `gorm:"type:date;column:SALStageDate"` SqlStageDate googlebq.NullDate `gorm:"type:date;column:SQLStageDate"` CloudRegistrationSource string `gorm:"type:varchar(255);column:CloudRegistrationSource"` OriginalUtmSource string `gorm:"type:varchar(255);column:OriginalUtmSource"` OriginalUtmMedium string `gorm:"type:varchar(255);column:OriginalUtmMedium"` OriginalUtmCampaign string `gorm:"type:varchar(255);column:OriginalUtmCampaign"` OriginalPersonSource string `gorm:"type:varchar(255);column:OriginalPersonSource"` OriginalPersonSourceDetail string `gorm:"type:varchar(255);column:OriginalPersonSourceDetail"` ConnectionType string `gorm:"type:varchar(255);column:ConnectionType"` UtmCampaign string `gorm:"type:varchar(255);column:UtmCampaign"` WebsiteReferrerUrl string `gorm:"type:varchar(255);column:WebsiteReferrerUrl"` } func (psl PingcapSalesforceLead) TableName() string { return "salesforce_lead" } func (psl PingcapSalesforceLead) PrimaryId() string { return "Id" } type PingcapSalesforceOpportunity struct { Id string `gorm:"primaryKey;column:Id"` ACVAmount googlebq.NullFloat64 `gorm:"column:ACVAmount"` AllowClose string `gorm:"type:varchar(255);column:AllowClose"` CloseDate googlebq.NullDate `gorm:"type:date;column:CloseDate"` Contract string `gorm:"type:varchar(255);column:Contract"` ContractArchivedDate googlebq.NullDate `gorm:"type:date;column:ContractArchivedDate"` CreatedBy string `gorm:"type:varchar(255);column:CreatedBy"` DiscoveryCompleted string `gorm:"type:varchar(255);column:DiscoveryCompleted"` ExchangeRate googlebq.NullFloat64 `gorm:"column:ExchangeRate"` ExpectedRevenue googlebq.NullFloat64 `gorm:"column:ExpectedRevenue"` FiscalPeriod string `gorm:"type:varchar(255);column:FiscalPeriod"` ForecastCategory string `gorm:"type:varchar(255);column:ForecastCategory"` LastModifiedBy string `gorm:"type:varchar(255);column:LastModifiedBy"` LeadSource string `gorm:"type:varchar(255);column:LeadSource"` LighthouseLogo string `gorm:"type:varchar(255);column:LighthouseLogo"` LossReason string `gorm:"type:varchar(255);column:LossReason"` NetFees googlebq.NullFloat64 `gorm:"column:NetFees"` OpportunityCurrency string `gorm:"type:varchar(255);column:OpportunityCurrency"` OpportunityNumber string `gorm:"type:varchar(255);column:OpportunityNumber"` OpportunityOwner string `gorm:"type:varchar(255);column:OpportunityOwner"` OpportunityScore string `gorm:"type:varchar(255);column:OpportunityScore"` OrderArchivedDate googlebq.NullDate `gorm:"type:date;column:OrderArchivedDate"` OriginalOrder string `gorm:"type:varchar(255);column:OriginalOrder"` PriceBook string `gorm:"type:varchar(255);column:PriceBook"` PrimaryCampaignSource string `gorm:"type:varchar(255);column:PrimaryCampaignSource"` Private string `gorm:"type:varchar(255);column:Private"` Probability googlebq.NullFloat64 `gorm:"column:Probability"` ProductFamily string `gorm:"type:varchar(255);column:ProductFamily"` Quantity googlebq.NullFloat64 `gorm:"column:Quantity"` TotalOpportunityQuantity googlebq.NullFloat64 `gorm:"column:TotalOpportunityQuantity"` Region string `gorm:"type:varchar(255);column:Region"` ROIAnalysisCompleted string `gorm:"type:varchar(255);column:ROIAnalysisCompleted"` Stage string `gorm:"type:varchar(255);column:Stage"` SyncedQuote string `gorm:"type:varchar(255);column:SyncedQuote"` TechnicalScenario string `gorm:"type:varchar(255);column:TechnicalScenario"` AccountId string `gorm:"type:varchar(255);column:AccountId"` AccountNumber string `gorm:"type:varchar(255);column:AccountNumber"` OpportunityName string `gorm:"type:varchar(255);column:OpportunityName"` OpportunityRecordType string `gorm:"type:varchar(255);column:OpportunityRecordType"` CreatedDate googlebq.NullDate `gorm:"type:date;column:CreatedDate"` } func (pso PingcapSalesforceOpportunity) TableName() string { return "salesforce_oppo" } func (pso PingcapSalesforceOpportunity) PrimaryId() string { return "Id" } type PingcapSalesforceOrder struct { Id string `gorm:"primaryKey;column:Id"` AccountCountryCode string `gorm:"type:varchar(255);column:AccountCountryCode"` AccountID string `gorm:"type:varchar(255);column:AccountID"` AccountNumber string `gorm:"type:varchar(255);column:AccountNumber"` ActivatedBy string `gorm:"type:varchar(255);column:ActivatedBy"` ActivatedDate googlebq.NullDate `gorm:"type:datetime;column:ActivatedDate"` ACVAmount float64 `gorm:"column:ACVAmount"` ApplicableLaw string `gorm:"type:varchar(255);column:ApplicableLaw"` Archived string `gorm:"type:varchar(255);column:Archived"` ArchivedOrderForm string `gorm:"type:varchar(255);column:ArchivedOrderForm"` CashCollected float64 `gorm:"column:CashCollected"` CashUncollected float64 `gorm:"column:CashUncollected"` ContractAutoNumber string `gorm:"type:varchar(255);column:ContractAutoNumber"` ContractExpirationDate googlebq.NullDate `gorm:"type:date;column:ContractExpirationDate"` ContractNumber string `gorm:"type:varchar(255);column:ContractNumber"` CreatedBy string `gorm:"type:varchar(255);column:CreatedBy"` DeferredRevenue float64 `gorm:"column:DeferredRevenue"` DMOwner string `gorm:"type:varchar(255);column:DMOwner"` GrossMargin float64 `gorm:"column:GrossMargin"` LastModifiedBy string `gorm:"type:varchar(255);column:LastModifiedBy"` MonthlyCost float64 `gorm:"column:MonthlyCost"` NetFees float64 `gorm:"column:NetFees"` NonStandardOrderForm string `gorm:"type:varchar(255);column:Non_StandardOrderForm"` NonStd_OF_Typ string `gorm:"type:varchar(255);column:Non_Std_OF_Type"` NonStandardUploadUrl string `gorm:"type:varchar(255);column:NonStandardUploadUrl"` Opportunity string `gorm:"type:varchar(255);column:Opportunity"` OrderAmount float64 `gorm:"column:OrderAmount"` OrderCurrency string `gorm:"type:varchar(255);column:OrderCurrency"` OrderEndDate googlebq.NullDate `gorm:"type:date;column:OrderEndDate"` OrderEndDate1 googlebq.NullDate `gorm:"type:date;column:OrderEndDate1"` OrderNumberName string `gorm:"type:varchar(255);column:OrderNumberName"` OrderNumber string `gorm:"type:varchar(255);column:OrderNumber"` OrderNumberC string `gorm:"type:varchar(255);column:OrderNumberC"` OrderOwner string `gorm:"type:varchar(255);column:OrderOwner"` OrderRecordType string `gorm:"type:varchar(255);column:OrderRecordType"` OrderReferenceNumber string `gorm:"type:varchar(255);column:OrderReferenceNumber"` OrderStartDate googlebq.NullDate `gorm:"type:date;column:OrderStartDate"` OrderType string `gorm:"type:varchar(255);column:OrderType"` OriginalOrder string `gorm:"type:varchar(255);column:OriginalOrder"` PaymentFrequency string `gorm:"type:varchar(255);column:PaymentFrequency"` PaymentTerm string `gorm:"type:varchar(255);column:PaymentTerm"` PODate googlebq.NullDate `gorm:"type:date;column:PODate"` PriceBook string `gorm:"type:varchar(255);column:PriceBook"` ProductFamily string `gorm:"type:varchar(255);column:ProductFamily"` Quote string `gorm:"type:varchar(255);column:Quote"` RecognizedRevenue float64 `gorm:"column:RecognizedRevenue"` ReductionOrder string `gorm:"type:varchar(255);column:ReductionOrder"` Status string `gorm:"type:varchar(255);column:Status"` Tax float64 `gorm:"column:Tax"` TCVAmount float64 `gorm:"column:TCVAmount"` TotalFees float64 `gorm:"column:TotalFees"` Region string `gorm:"type:varchar(255);column:Region"` AccountName string `gorm:"type:varchar(255);column:AccountName"` } func (pso PingcapSalesforceOrder) TableName() string { return "salesforce_order" } func (pso PingcapSalesforceOrder) PrimaryId() string { return "Id" } type PingcapSalesforceRevrec struct { RevRecNumber string `json:"RevRecNumber" gorm:"primaryKey"` OrderNumber string `json:"OrderNumber"` AmountUSD float64 `json:"AmountUSD"` Date googlebq.NullDate `json:"Date"` FiscalPeriod string `json:"FiscalPeriod"` Order string `json:"Order"` Product string `json:"Product"` Status string `json:"Status"` AccountName string `json:"AccountName"` AccountId string `json:"AccountId"` } func (pso PingcapSalesforceRevrec) TableName() string { return "salesforce_recrev" } func (pso PingcapSalesforceRevrec) PrimaryId() string { return "RevRecNumber" } type PingcapSalesforceTask struct { Id string `json:"Id" gorm:"primaryKey;column:Id"` Subject string `json:"Subject" ` Comments string `json:"Comments"` CreatedDate googlebq.NullDate `json:"CreatedDate"` RelatedTo string `json:"RelatedTo"` CreatedBy string `json:"CreatedBy"` } func (pso PingcapSalesforceTask) TableName() string { return "salesforce_task" } func (pso PingcapSalesforceTask) PrimaryId() string { return "Id" } type PingcapSalesforceCollection struct { Id string `json:"Collection Number" gorm:"primaryKey"` AccountId string `json:"Account ID"` Date googlebq.NullDate `json:"Collections Date"` Amount float64 `json:"Collections Amount"` Currency string `json:"Currency"` OrderNumber string `json:"Order Number"` Status string `json:"Collections Status"` AccountName string `json:"AccountName"` } func (pso PingcapSalesforceCollection) TableName() string { return "salesforce_collection" } func (pso PingcapSalesforceCollection) PrimaryId() string { return "Id" } type PingcapSalesforcePoc struct { PoCNumber string `json:"PoCNumber" gorm:"primaryKey"` ApprovalStatus string `json:"ApprovalStatus"` PoCStatus string `json:"PoCStatus"` Region string `json:"Region"` Account string `json:"Account"` Opportunity string `json:"Opportunity"` InternalUser string `json:"Internal User"` RecordType string `json:"Record Type"` CreateDate googlebq.NullDate `json:"CreateDate"` TiDBCloudAccountIDtorunThisPoC string `json:"TiDBCloudAccountIDtorunThisPoC" gorm:"column:tidb_cloud_accountid_to_runthispoc"` } func (pso PingcapSalesforcePoc) TableName() string { return "salesforce_poc" } func (pso PingcapSalesforcePoc) PrimaryId() string { return "PoCNumber" } func ModelConvert(srcs interface{}, destModelType reflect.Type) (interface{}, error) { srcsValue := reflect.ValueOf(srcs) if srcsValue.Kind() != reflect.Slice { return nil, errors.New("srcs must be slice") } log.Println(srcsValue.Len()) dest := reflect.MakeSlice(destModelType, srcsValue.Len(), srcsValue.Len()) for i := 0; i < srcsValue.Len(); i++ { srcModelValue := srcsValue.Index(i) destModelValue := dest.Index(i) for fieldIdx := 0; fieldIdx < destModelValue.NumField(); fieldIdx++ { srcFieldType := srcModelValue.Field(fieldIdx).Type() destFieldType := destModelValue.Field(fieldIdx).Type() if srcFieldType.Name() == destFieldType.Name() { destModelValue.Field(fieldIdx).Set(srcModelValue.Field(fieldIdx)) } else { if srcFieldType.Name() == "string" && srcModelValue.Field(fieldIdx).String() != "" { switch destFieldType.Name() { case "bool": destFieldValue, _ := strconv.ParseBool(srcModelValue.Field(fieldIdx).String()) destModelValue.Field(fieldIdx).Set(reflect.ValueOf(destFieldValue)) case "NullDateTime": datetime, err := time.Parse("2006-01-02T15:04:05.000+0000", srcModelValue.Field(fieldIdx).String()) if err != nil { log.Println("time.Parse err: ", err) break } destFieldValue := googlebq.NullDateTime{} destFieldValue.Valid = true destFieldValue.DateTime = civil.DateTimeOf(datetime) destModelValue.Field(fieldIdx).Set(reflect.ValueOf(destFieldValue)) case "NullDate": srcDate := srcModelValue.Field(fieldIdx).String() var datetime time.Time var err error if len(srcDate) <= 10 { datetime, err = time.Parse("2006-01-02", srcModelValue.Field(fieldIdx).String()) } else { datetime, err = time.Parse("2006-01-02T15:04:05.000+0000", srcModelValue.Field(fieldIdx).String()) } if err != nil { log.Println("time.Parse err: ", err) break } destFieldValue := googlebq.NullDate{} destFieldValue.Valid = true destFieldValue.Date = civil.DateOf(datetime) destModelValue.Field(fieldIdx).Set(reflect.ValueOf(destFieldValue)) default: log.Fatal("Unsupported dest field type -> ", destFieldType.Name()) } } else { val, err := typeConvertToBigQuery(srcModelValue.Field(fieldIdx).Interface(), destModelValue.Field(fieldIdx).Interface()) if err != nil { log.Fatal("[error] typeConvert ", err, srcModelValue.Field(fieldIdx).Interface()) } if val != nil { destModelValue.Field(fieldIdx).Set(reflect.ValueOf(val)) } } } } } return dest.Convert(destModelType).Interface(), nil } func typeConvertToBigQuery(src interface{}, dst interface{}) (interface{}, error) { switch src := src.(type) { case string: if src == "" { return nil, nil } switch dst := dst.(type) { case googlebq.NullString: return googlebq.NullString{src, true}, nil case string: return src, nil case bool: destFieldValue, _ := strconv.ParseBool(src) return destFieldValue, nil case googlebq.NullDateTime: datetime, err := time.Parse("2006-01-02T15:04:05.000+0000", src) if err != nil { log.Println("time.Parse err: ", err) break } destFieldValue := googlebq.NullDateTime{} destFieldValue.Valid = true destFieldValue.DateTime = civil.DateTimeOf(datetime) return destFieldValue, nil case googlebq.NullDate: var datetime time.Time var err error if len(src) <= 10 { datetime, err = time.Parse("2006-01-02", src) } else { datetime, err = time.Parse("2006-01-02T15:04:05.000+0000", src) } if err != nil { log.Println("time.Parse err: ", err) break } destFieldValue := googlebq.NullDate{} destFieldValue.Valid = true destFieldValue.Date = civil.DateOf(datetime) return destFieldValue, nil case nil: log.Println("typeConvertToBigQuery dst is nil ", dst) return nil, nil default: return nil, errors.New(fmt.Sprint("unsupported conversion from model.string to ", dst)) } case float64: switch dst := dst.(type) { case googlebq.NullString: return googlebq.NullString{strconv.FormatFloat(src, 'f', -1, 64), true}, nil case googlebq.NullFloat64: return googlebq.NullFloat64{src, true}, nil default: return nil, errors.New(fmt.Sprint("unsupported conversion from model.float64 to ", dst)) } case model.JsonDate: if !src.NullTime.Valid { return nil, nil } switch dst := dst.(type) { case googlebq.NullDate: dst.Date = civil.DateOf(src.NullTime.Time) dst.Valid = src.NullTime.Valid return dst, nil case googlebq.NullDateTime: dst.DateTime = civil.DateTimeOf(src.NullTime.Time) dst.Valid = src.NullTime.Valid return dst, nil default: return nil, errors.New(fmt.Sprint("unsupported conversion from model.JsonDate to ", dst)) } case model.JsonDateTime: if !src.NullTime.Valid { return nil, nil } switch dst := dst.(type) { case googlebq.NullDate: dst.Date = civil.DateOf(src.NullTime.Time) dst.Valid = src.NullTime.Valid return dst, nil case googlebq.NullDateTime: dst.DateTime = civil.DateTimeOf(src.NullTime.Time) dst.Valid = src.NullTime.Valid return dst, nil default: return nil, errors.New(fmt.Sprint("unsupported conversion from model.JsonDateTime to ", dst)) } case nil: return nil, nil case bool: switch dst := dst.(type) { case googlebq.NullString: return googlebq.NullString{strconv.FormatBool(src), true}, nil default: return nil, errors.New(fmt.Sprint("unsupported conversion from model.bool to ", dst)) } default: return nil, errors.New(fmt.Sprint("unsupported conversion ", src, dst)) } return nil, nil } type GCSBucketInfo struct { Bucket string `yaml:"bucket"` Prefix string `yaml:"prefix"` DataSet string `yaml:"dataset"` } type AWSNonprodBillingGather struct { AccountId string Month googlebq.NullString Cost googlebq.NullFloat64 } func (anbg AWSNonprodBillingGather) TableName() string { return "aws_nonprod_billing_gather" }